Something went wrong on our end
-
Cyril Vazquez authoredCyril Vazquez authored
structure.sql 135.33 KiB
-- core/sql/structure/core.postgresql.sql
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--DROP PROCEDURAL LANGUAGE IF EXISTS plpgsql CASCADE;
--CREATE PROCEDURAL LANGUAGE plpgsql;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE SEQUENCE actions_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 400
CACHE 1;
CREATE TABLE actions
(
id integer NOT NULL DEFAULT nextval('actions_id_seq'::regclass),
keyword character varying(32) NOT NULL DEFAULT ''::bpchar,
label_action character varying(255),
id_status character varying(10),
is_system character(1) NOT NULL DEFAULT 'N'::bpchar,
is_folder_action character(1) NOT NULL DEFAULT 'N'::bpchar,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
action_page character varying(255),
history character(1) NOT NULL DEFAULT 'N'::bpchar,
origin character varying(255) NOT NULL DEFAULT 'apps'::bpchar,
create_id character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT actions_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE docserver_types
(
docserver_type_id character varying(32) NOT NULL,
docserver_type_label character varying(255) DEFAULT NULL::character varying,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
is_container character(1) NOT NULL DEFAULT 'N'::bpchar,
container_max_number integer NOT NULL DEFAULT (0)::integer,
is_compressed character(1) NOT NULL DEFAULT 'N'::bpchar,
compression_mode character varying(32) DEFAULT NULL::character varying,
is_meta character(1) NOT NULL DEFAULT 'N'::bpchar,
meta_template character varying(32) DEFAULT NULL::character varying,
is_logged character(1) NOT NULL DEFAULT 'N'::bpchar,
log_template character varying(32) DEFAULT NULL::character varying,
is_signed character(1) NOT NULL DEFAULT 'N'::bpchar,
fingerprint_mode character varying(32) DEFAULT NULL::character varying,
CONSTRAINT docserver_types_pkey PRIMARY KEY (docserver_type_id)
)
WITH (OIDS=FALSE);
CREATE TABLE docservers
(
docserver_id character varying(32) NOT NULL DEFAULT '1'::character varying,
docserver_type_id character varying(32) NOT NULL,
device_label character varying(255) DEFAULT NULL::character varying,
is_readonly character(1) NOT NULL DEFAULT 'N'::bpchar,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
size_limit_number bigint NOT NULL DEFAULT (0)::bigint,
actual_size_number bigint NOT NULL DEFAULT (0)::bigint,
path_template character varying(255) NOT NULL,
ext_docserver_info character varying(255) DEFAULT NULL::character varying, chain_before character varying(32) DEFAULT NULL::character varying,
chain_after character varying(32) DEFAULT NULL::character varying,
creation_date timestamp without time zone NOT NULL,
closing_date timestamp without time zone,
coll_id character varying(32) NOT NULL DEFAULT 'coll_1'::character varying,
priority_number integer NOT NULL DEFAULT 10,
docserver_location_id character varying(32) NOT NULL,
adr_priority_number integer NOT NULL DEFAULT 1,
CONSTRAINT docservers_pkey PRIMARY KEY (docserver_id)
)
WITH (OIDS=FALSE);
CREATE TABLE docserver_locations
(
docserver_location_id character varying(32) NOT NULL,
ipv4 character varying(255) DEFAULT NULL::character varying,
ipv6 character varying(255) DEFAULT NULL::character varying,
net_domain character varying(32) DEFAULT NULL::character varying,
mask character varying(255) DEFAULT NULL::character varying,
net_link character varying(255) DEFAULT NULL::character varying,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT docserver_locations_pkey PRIMARY KEY (docserver_location_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE doctypes_type_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 400
CACHE 1;
CREATE TABLE doctypes
(
coll_id character varying(32) NOT NULL DEFAULT ''::character varying,
type_id integer NOT NULL DEFAULT nextval('doctypes_type_id_seq'::regclass),
description character varying(255) NOT NULL DEFAULT ''::character varying,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
doctypes_first_level_id integer,
doctypes_second_level_id integer,
primary_retention character varying(50) DEFAULT NULL,
secondary_retention character varying(50) DEFAULT NULL,
CONSTRAINT doctypes_pkey PRIMARY KEY (type_id)
)
WITH (OIDS=FALSE);
CREATE TABLE ext_docserver
(
doc_id character varying(255) NOT NULL,
path character varying(255) NOT NULL,
CONSTRAINT ext_docserver_pkey PRIMARY KEY (doc_id)
)
WITH (OIDS=FALSE);
CREATE TABLE groupsecurity
(
group_id character varying(32) NOT NULL,
resgroup_id character varying(32) NOT NULL,
can_view character(1) NOT NULL,
can_add character(1) NOT NULL,
can_delete character(1) NOT NULL,
CONSTRAINT groupsecurity_pkey PRIMARY KEY (group_id, resgroup_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE history_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1 CACHE 1;
CREATE TABLE history
(
id bigint NOT NULL DEFAULT nextval('history_id_seq'::regclass),
table_name character varying(32) DEFAULT NULL::character varying,
record_id character varying(255) DEFAULT NULL::character varying,
event_type character varying(32) NOT NULL,
user_id character varying(128) NOT NULL,
event_date timestamp without time zone NOT NULL,
info text,
id_module character varying(50) NOT NULL DEFAULT 'admin'::character varying,
remote_ip character varying(32) DEFAULT NULL,
event_id character varying(50),
CONSTRAINT history_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE history_batch_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE history_batch
(
id bigint NOT NULL DEFAULT nextval('history_batch_id_seq'::regclass),
module_name character varying(32) DEFAULT NULL::character varying,
batch_id bigint DEFAULT NULL::bigint,
event_date timestamp without time zone NOT NULL,
total_processed bigint DEFAULT NULL::bigint,
total_errors bigint DEFAULT NULL::bigint,
info text,
CONSTRAINT history_batch_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE parameters
(
id character varying(255) NOT NULL,
description TEXT,
param_value_string character varying(255) DEFAULT NULL::character varying,
param_value_int integer,
param_value_date timestamp without time zone,
CONSTRAINT parameters_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE resgroup_content
(
coll_id character varying(32) NOT NULL,
res_id bigint NOT NULL,
resgroup_id character varying(32) NOT NULL,
"sequence" integer NOT NULL,
CONSTRAINT resgroup_content_pkey PRIMARY KEY (coll_id, res_id, resgroup_id)
)
WITH (OIDS=FALSE);
CREATE TABLE resgroups
(
resgroup_id character varying(32) NOT NULL,
resgroup_desc character varying(255) NOT NULL,
created_by character varying(255) NOT NULL,
creation_date timestamp without time zone NOT NULL,
CONSTRAINT resgroups_pkey PRIMARY KEY (resgroup_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE security_security_id_seq INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 500
CACHE 1;
CREATE TABLE "security"
(
security_id bigint NOT NULL DEFAULT nextval('security_security_id_seq'::regclass),
group_id character varying(32) NOT NULL,
coll_id character varying(32) NOT NULL,
where_clause text,
maarch_comment text,
can_insert character(1) NOT NULL DEFAULT 'N'::bpchar,
can_update character(1) NOT NULL DEFAULT 'N'::bpchar,
can_delete character(1) NOT NULL DEFAULT 'N'::bpchar,
rights_bitmask integer NOT NULL DEFAULT 0,
mr_start_date timestamp without time zone DEFAULT NULL,
mr_stop_date timestamp without time zone DEFAULT NULL,
where_target character varying(15) DEFAULT 'DOC'::character varying,
CONSTRAINT security_pkey PRIMARY KEY (security_id)
)
WITH (OIDS=FALSE);
CREATE TABLE status
(
id character varying(10) NOT NULL,
label_status character varying(50) NOT NULL,
is_system character(1) NOT NULL DEFAULT 'Y'::bpchar,
is_folder_status character(1) NOT NULL default 'N'::bpchar,
img_filename character varying(255),
maarch_module character varying(255) NOT NULL DEFAULT 'apps'::character varying,
can_be_searched character(1) NOT NULL DEFAULT 'Y'::bpchar,
can_be_modified character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT status_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE TABLE usergroup_content
(
user_id character varying(128) NOT NULL,
group_id character varying(32) NOT NULL,
primary_group character(1) NOT NULL,
"role" character varying(255) DEFAULT NULL::character varying,
CONSTRAINT usergroup_content_pkey PRIMARY KEY (user_id, group_id)
)
WITH (OIDS=FALSE);
CREATE TABLE usergroups
(
group_id character varying(32) NOT NULL,
group_desc character varying(255) DEFAULT NULL::character varying,
administrator character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_right1 character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_right2 character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_right3 character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_right4 character(1) NOT NULL DEFAULT 'N'::bpchar,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT usergroups_pkey PRIMARY KEY (group_id)
)
WITH (OIDS=FALSE);
CREATE TABLE usergroups_services
(
group_id character varying NOT NULL,
service_id character varying NOT NULL,
CONSTRAINT usergroups_services_pkey PRIMARY KEY (group_id, service_id)
)
WITH (OIDS=FALSE);
CREATE TABLE users
(
user_id character varying(128) NOT NULL,
"password" character varying(255) DEFAULT NULL::character varying,
firstname character varying(255) DEFAULT NULL::character varying,
lastname character varying(255) DEFAULT NULL::character varying,
phone character varying(32) DEFAULT NULL::character varying,
mail character varying(255) DEFAULT NULL::character varying,
department character varying(50) DEFAULT NULL::character varying,
custom_t1 character varying(50) DEFAULT '0'::character varying,
custom_t2 character varying(50) DEFAULT NULL::character varying,
custom_t3 character varying(50) DEFAULT NULL::character varying,
cookie_key character varying(255) DEFAULT NULL::character varying,
cookie_date timestamp without time zone,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
change_password character(1) NOT NULL DEFAULT 'Y'::bpchar,
delay_number integer DEFAULT NULL,
status character varying(10) NOT NULL DEFAULT 'OK'::character varying,
loginmode character varying(50) DEFAULT NULL::character varying,
docserver_location_id character varying(32) DEFAULT NULL::character varying,
CONSTRAINT users_pkey PRIMARY KEY (user_id)
)
WITH (OIDS=FALSE);
-- modules/advanced_physical_archive/sql/structure/advanced_physical_archive.postgresql.sql
CREATE SEQUENCE arbox_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 10
CACHE 1;
CREATE TABLE ar_boxes (
arbox_id bigint NOT NULL DEFAULT nextval('arbox_id_seq'::regclass),
title character varying(255) default NULL::character varying,
subject character varying(255) default NULL::character varying,
description text ,
entity_id character varying(32) default NULL::character varying,
arcontainer_id integer NOT NULL,
status character varying(3) default NULL::character varying,
creation_date timestamp without time zone,
retention_time timestamp without time zone,
custom_t1 character varying(3) default NULL::character varying,
custom_n1 integer default NULL,
custom_f1 numeric default NULL,
custom_d1 timestamp without time zone,
custom_t2 character varying(3) default NULL::character varying,
custom_n2 integer default NULL,
custom_f2 numeric default NULL,
custom_d2 timestamp without time zone,
custom_t3 character varying(50) default NULL::character varying,
custom_n3 integer default NULL,
custom_f3 numeric default NULL,
custom_d3 timestamp without time zone,
custom_t4 character varying(50) default NULL::character varying,
custom_n4 integer default NULL,
custom_f4 numeric default NULL,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) default NULL::character varying,
custom_n5 integer default NULL,
custom_f5 numeric default NULL,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) default NULL::character varying,
custom_t7 character varying(255) default NULL::character varying,
custom_t8 character varying(255) default NULL::character varying,
custom_t9 character varying(255) default NULL::character varying,
custom_t10 character varying(255) default NULL::character varying,
custom_t11 character varying(255) default NULL::character varying, CONSTRAINT ar_boxes_pkey PRIMARY KEY (arbox_id)
)
WITH (OIDS=FALSE);
CREATE TABLE ar_containers (
arcontainer_id integer NOT NULL,
arcontainer_desc character varying(255) default NULL,
status character varying(3) default NULL,
ctype_id character varying(32) default NULL,
position_id bigint default NULL,
creation_date timestamp without time zone,
entity_id character varying(32) NOT NULL,
retention_time timestamp without time zone,
custom_t1 character varying(50) default NULL,
custom_n1 integer default NULL,
custom_f1 numeric default NULL,
custom_d1 timestamp without time zone,
custom_t2 character varying(3) default NULL,
custom_n2 integer default NULL,
custom_f2 numeric default NULL,
custom_d2 timestamp without time zone,
CONSTRAINT ar_containers_pkey PRIMARY KEY (arcontainer_id)
)
WITH (OIDS=FALSE);
CREATE TABLE ar_container_types (
ctype_id character varying(32) NOT NULL,
ctype_desc character varying(255) NOT NULL,
size_x float NOT NULL default '0',
size_y float NOT NULL default '0',
size_z float NOT NULL default '0',
CONSTRAINT ar_container_types_pkey PRIMARY KEY (ctype_id)
)
WITH (OIDS=FALSE);
CREATE TABLE ar_deposits (
deposit_id bigint NOT NULL,
deposit_label character varying(255) NOT NULL,
deposit_desc text NOT NULL,
flg_closed smallint NOT NULL,
closing_date timestamp without time zone NOT NULL,
creation_date timestamp without time zone NOT NULL,
user_id character varying(128) NOT NULL,
CONSTRAINT ar_deposits_pkey PRIMARY KEY (deposit_id)
)
WITH (OIDS=FALSE);
CREATE TABLE ar_header (
header_id bigserial NOT NULL,
creation_date timestamp without time zone NOT NULL,
ctype_id character varying(32) NOT NULL default '0',
year_1 integer NOT NULL default '0',
year_2 integer NOT NULL default '0',
site_id character varying(32) NOT NULL default '0',
destruction_date timestamp without time zone,
allow_transmission_date timestamp without time zone,
weight integer default NULL,
reservation_id bigint default NULL,
deposit_id bigint default NULL,
header_desc text ,
entity_id character varying(32) default NULL,
arnature_id character varying(32) default NULL,
arbox_id integer default NULL,
arcontainer_id integer default NULL,
CONSTRAINT ar_header_pkey PRIMARY KEY (header_id)
)
WITH (OIDS=FALSE);
CREATE TABLE ar_natures (
arnature_id character varying(32) NOT NULL, arnature_desc character varying(255) default NULL,
arnature_retention integer NOT NULL,
entity_id character varying(32) default NULL,
enabled character varying(1) default NULL,
CONSTRAINT ar_natures_pkey PRIMARY KEY (arnature_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE position_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 200
CACHE 1;
CREATE TABLE ar_positions (
position_id bigint NOT NULL DEFAULT nextval('position_id_seq'::regclass),
site_id character varying(32) NOT NULL,
pos_row character varying(32) NOT NULL,
pos_col integer NOT NULL,
pos_level integer NOT NULL,
pos_max_uc integer NOT NULL,
pos_available_uc integer NOT NULL,
CONSTRAINT ar_positions_pkey PRIMARY KEY (position_id)
)
WITH (OIDS=FALSE);
CREATE TABLE ar_sites (
site_id character varying(32) NOT NULL default '0',
site_desc character varying(255) NOT NULL,
entity_id character varying(32) default NULL,
CONSTRAINT ar_sites_pkey PRIMARY KEY (site_id)
)
WITH (OIDS=FALSE);
CREATE TABLE res_apa (
res_id serial NOT NULL ,
title character varying(255) default NULL ,
subject text ,
description text ,
publisher character varying(255) default NULL ,
contributor character varying(255) default NULL ,
type_id integer default NULL ,
format character varying(50) default NULL ,
typist character varying(128) default NULL ,
creation_date timestamp without time zone NOT NULL ,
author character varying(255) default NULL ,
author_name text ,
identifier character varying(255) default NULL ,
source character varying(255) default NULL ,
doc_language character varying(50) default NULL ,
relation integer default NULL ,
coverage character varying(255) default NULL ,
doc_date timestamp without time zone default NULL ,
docserver_id character varying(32) default NULL ,
folders_system_id integer default NULL ,
arbox_id character varying(32) default NULL ,
path character varying(255) default NULL ,
filename character varying(255) default NULL ,
offset_doc character varying(255) default NULL ,
logical_adr character varying(255) default NULL ,
fingerprint character varying(255) default NULL ,
filesize integer default NULL ,
is_paper char(1) default NULL ,
page_count integer default NULL ,
scan_date timestamp without time zone default NULL ,
scan_user character varying(50) default NULL ,
scan_location character varying(255) default NULL ,
scan_wkstation character varying(255) default NULL ,
scan_batch character varying(50) default NULL , burn_batch character varying(50) default NULL ,
scan_postmark character varying(50) default NULL ,
envelop_id integer default NULL ,
status character varying(3) default NULL ,
destination character varying(50) default NULL ,
approver character varying(50) default NULL ,
validation_date timestamp without time zone default NULL ,
work_batch integer default NULL ,
origin character varying(50) default NULL ,
is_ingoing char(1) default NULL ,
priority smallint default NULL ,
arbatch_id character varying(32) default NULL ,
fulltext_result character varying(10) DEFAULT NULL,
ocr_result character varying(10) DEFAULT NULL,
converter_result character varying(10) DEFAULT NULL,
custom_t1 text default NULL,
custom_n1 integer default NULL ,
custom_f1 numeric default NULL ,
custom_d1 timestamp without time zone default NULL ,
custom_t2 character varying(255) default NULL ,
custom_n2 integer default NULL ,
custom_f2 numeric default NULL ,
custom_d2 timestamp without time zone default NULL ,
custom_t3 character varying(255) default NULL ,
custom_n3 integer default NULL ,
custom_f3 numeric default NULL ,
custom_d3 timestamp without time zone default NULL ,
custom_t4 character varying(255) default NULL ,
custom_n4 integer default NULL ,
custom_f4 numeric default NULL ,
custom_d4 timestamp without time zone default NULL ,
custom_t5 character varying(255) default NULL ,
custom_n5 integer default NULL ,
custom_f5 numeric default NULL ,
custom_d5 timestamp without time zone default NULL ,
custom_t6 character varying(255) default NULL ,
custom_d6 timestamp without time zone default NULL ,
custom_t7 character varying(255) default NULL ,
custom_d7 timestamp without time zone default NULL ,
custom_t8 character varying(255) default NULL ,
custom_d8 timestamp without time zone default NULL ,
custom_t9 character varying(255) default NULL ,
custom_d9 timestamp without time zone default NULL ,
custom_t10 character varying(255) default NULL ,
custom_d10 timestamp without time zone default NULL ,
custom_t11 character varying(255) default NULL ,
custom_t12 character varying(255) default NULL ,
custom_t13 character varying(255) default NULL ,
custom_t14 character varying(255) default NULL ,
custom_t15 character varying(255) default NULL ,
tablename character varying(32) default 'res_apa',
initiator character varying(50) default NULL ,
dest_user character varying(128) default NULL ,
video_batch integer default NULL ,
video_time timestamp NULL default NULL ,
video_user character varying(128) default NULL ,
video_date timestamp without time zone,
CONSTRAINT res_apa_pkey PRIMARY KEY (res_id)
)
WITH (OIDS=FALSE);
-- modules/attachments/sql/structure/attachments.postgresql.sql
CREATE SEQUENCE res_attachment_res_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1 CACHE 1;
CREATE TABLE res_attachments
(
res_id bigint NOT NULL DEFAULT nextval('res_attachment_res_id_seq'::regclass),
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint ,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL::character varying,
ocr_result character varying(10) DEFAULT NULL::character varying,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) DEFAULT NULL::character varying,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
coll_id character varying(32) NOT NULL,
res_id_master bigint,
CONSTRAINT res_attachments_pkey PRIMARY KEY (res_id)
)
WITH (OIDS=FALSE);
-- modules/autofoldering/sql/structure/autofoldering.postgresql.sql
CREATE TABLE af_security
(
af_security_id bigint NOT NULL,
af_security_label character varying(255) NOT NULL,
group_id character varying(50) NOT NULL,
tree_id character varying(50) NOT NULL,
where_clause text NOT NULL, start_date timestamp without time zone,
stop_date timestamp without time zone,
CONSTRAINT af_security_pkey PRIMARY KEY (af_security_id)
)
WITH (OIDS=FALSE);
-- Filled during autofoldering load
-- If you create your on table for a new tree
-- It is very important to respect the order of fields : DO NOT PUT IDS IN THE END OF THE TABLE!!!
CREATE TABLE af_view_year_target
(
level1 character varying(255) NOT NULL , -- Pays / Country : custom_t3
level1_id integer NOT NULL,
level2 character(4) , -- Année / Year : date_part('year', doc_date)
level2_id integer NOT NULL,
level3 character varying(255) , -- Client / Customer : custom_t4
level3_id integer NOT NULL,
CONSTRAINT af_view_year_target_pkey PRIMARY KEY (level1, level2, level3)
)
WITH (OIDS=FALSE);
CREATE TABLE af_view_customer_target
(
level1 character varying(255) NOT NULL , -- 1ère lettre client / Customer 1st letter : substring(custom_t4, 1, 1)
level1_id integer NOT NULL,
level2 character varying(255) , -- Client / Customer : custom_t4
level2_id integer NOT NULL,
level3 character(4) , -- Année / Year : date_part('year', doc_date)
level3_id integer NOT NULL,
CONSTRAINT af_view_customer_target_pkey PRIMARY KEY (level1, level2, level3)
)
WITH (OIDS=FALSE);
-- modules/basket/sql/structure/basket.postgresql.sql
CREATE TABLE actions_groupbaskets
(
id_action bigint NOT NULL,
where_clause text,
group_id character varying(32) NOT NULL,
basket_id character varying(32) NOT NULL,
used_in_basketlist character(1) NOT NULL DEFAULT 'Y'::bpchar,
used_in_action_page character(1) NOT NULL DEFAULT 'Y'::bpchar,
default_action_list character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT actions_groupbaskets_pkey PRIMARY KEY (id_action, group_id, basket_id)
)
WITH (OIDS=FALSE);
CREATE TABLE baskets
(
coll_id character varying(32) NOT NULL,
basket_id character varying(32) NOT NULL,
basket_name character varying(255) NOT NULL,
basket_desc character varying(255) NOT NULL,
basket_clause text NOT NULL,
is_generic character varying(6) NOT NULL DEFAULT 'N'::character varying,
is_visible character(1) NOT NULL DEFAULT 'Y'::bpchar,
is_folder_basket character (1) NOT NULL default 'N'::bpchar,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT baskets_pkey PRIMARY KEY (coll_id, basket_id)
)
WITH (OIDS=FALSE);
CREATE TABLE basket_persistent_mode
(
res_id bigint,
user_id character varying(32),
is_persistent character varying(1)
)WITH (
OIDS=FALSE
);
CREATE TABLE groupbasket
(
group_id character varying(32) NOT NULL,
basket_id character varying(32) NOT NULL,
"sequence" integer NOT NULL DEFAULT 0,
redirect_basketlist character varying(2048) DEFAULT NULL::character varying,
redirect_grouplist character varying(2048) DEFAULT NULL::character varying,
result_page character varying(255) DEFAULT 'show_list1.php'::character varying,
can_redirect character(1) NOT NULL DEFAULT 'N'::bpchar,
can_delete character(1) NOT NULL DEFAULT 'N'::bpchar,
can_insert character(1) NOT NULL DEFAULT 'N'::bpchar,
list_lock_clause text,
sublist_lock_clause text,
CONSTRAINT groupbasket_pkey PRIMARY KEY (group_id, basket_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE user_abs_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE user_abs
(
system_id bigint NOT NULL DEFAULT nextval('user_abs_seq'::regclass),
user_abs character varying(128) NOT NULL,
new_user character varying(128) NOT NULL,
basket_id character varying(255) NOT NULL,
basket_owner character varying(255),
is_virtual character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT user_abs_pkey PRIMARY KEY (system_id)
)
WITH (OIDS=FALSE);
-- modules/cases/sql/structure/cases.postgresql.sql
CREATE SEQUENCE case_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE cases
(
case_id integer NOT NULL DEFAULT nextval('case_id_seq'::regclass),
case_label character varying(255) NOT NULL DEFAULT ''::bpchar,
case_description character varying(255),
case_type character varying(32),
case_closing_date timestamp without time zone,
case_last_update_date timestamp without time zone NOT NULL,
case_creation_date timestamp without time zone NOT NULL,
case_typist character varying(128) NOT NULL DEFAULT ''::bpchar,
case_parent integer,
case_custom_t1 character varying(255),
case_custom_t2 character varying(255),
case_custom_t3 character varying(255),
case_custom_t4 character varying(255),
CONSTRAINT cases_pkey PRIMARY KEY (case_id)
);
CREATE TABLE cases_res
( case_id integer NOT NULL,
res_id integer NOT NULL,
CONSTRAINT cases_res_pkey PRIMARY KEY (case_id,res_id)
);
-- modules/entities/sql/structure/entities.postgresql.sql
CREATE TABLE entities
(
entity_id character varying(32) NOT NULL,
entity_label character varying(255),
short_label character varying(50),
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
adrs_1 character varying(255),
adrs_2 character varying(255),
adrs_3 character varying(255),
zipcode character varying(32),
city character varying(255),
country character varying(255),
email character varying(255),
business_id character varying(32),
parent_entity_id character varying(32),
entity_type character varying(64),
CONSTRAINT entities_pkey PRIMARY KEY (entity_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE listinstance_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE listinstance
(
listinstance_id BIGINT NOT NULL DEFAULT nextval('listinstance_id_seq'::regclass),
coll_id character varying(50) NOT NULL,
res_id bigint NOT NULL,
listinstance_type character varying(50) DEFAULT 'DOC'::character varying,
"sequence" bigint NOT NULL,
item_id character varying(128) NOT NULL,
item_type character varying(255) NOT NULL,
item_mode character varying(50) NOT NULL,
added_by_user character varying(128) NOT NULL,
added_by_entity character varying(50) NOT NULL,
visible character varying(1) NOT NULL DEFAULT 'Y'::bpchar,
viewed bigint,
difflist_type character varying(50),
CONSTRAINT listinstance_pkey PRIMARY KEY (listinstance_id)
)
WITH (OIDS=FALSE);
CREATE TABLE listmodels
(
coll_id character varying(50) NOT NULL,
object_id character varying(50) NOT NULL,
object_type character varying(255) NOT NULL,
"sequence" bigint NOT NULL,
item_id character varying(128) NOT NULL,
item_type character varying(255) NOT NULL,
item_mode character varying(50) NOT NULL,
listmodel_type character varying(50) DEFAULT 'DOC'::character varying,
description character varying(255),
visible character varying(1) NOT NULL DEFAULT 'Y'::bpchar
)WITH (OIDS=FALSE);
CREATE TABLE difflist_types
(
difflist_type_id character varying(50) NOT NULL,
difflist_type_label character varying(100) NOT NULL,
difflist_type_roles TEXT,
allow_entities character varying(1) NOT NULL DEFAULT 'N'::bpchar,
is_system character varying(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT "difflist_types_pkey" PRIMARY KEY (difflist_type_id)
)
WITH (
OIDS=FALSE
);
CREATE TABLE users_entities
(
user_id character varying(128) NOT NULL,
entity_id character varying(32) NOT NULL,
user_role character varying(255),
primary_entity character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT users_entities_pkey PRIMARY KEY (user_id, entity_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE groupbasket_redirect_system_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 500
CACHE 1;
CREATE TABLE groupbasket_redirect
(
system_id integer NOT NULL DEFAULT nextval('groupbasket_redirect_system_id_seq'::regclass),
group_id character varying(32) NOT NULL,
basket_id character varying(32) NOT NULL,
action_id int NOT NULL,
entity_id character varying(32),
keyword character varying(255),
redirect_mode character varying(32) NOT NULL,
CONSTRAINT groupbasket_redirect_pkey PRIMARY KEY (system_id)
)
WITH (OIDS=FALSE);
-- modules/folder/sql/structure/folder.postgresql.sql
CREATE SEQUENCE folders_system_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 10000
CACHE 1;
CREATE TABLE folders
(
folders_system_id bigint NOT NULL DEFAULT nextval('folders_system_id_seq'::regclass),
folder_id character varying(255) NOT NULL,
foldertype_id integer,
parent_id bigint DEFAULT (0)::bigint,
folder_name character varying(255) DEFAULT NULL::character varying,
subject character varying(255) DEFAULT NULL::character varying,
description character varying(255) DEFAULT NULL::character varying,
author character varying(255) DEFAULT NULL::character varying,
typist character varying(255) DEFAULT NULL::character varying,
status character varying(50) NOT NULL DEFAULT 'FOLDNEW'::character varying,
folder_level smallint DEFAULT (1)::smallint,
creation_date timestamp without time zone NOT NULL,
destination character varying(50) DEFAULT NULL, dest_user character varying(128) DEFAULT NULL,
folder_out_id bigint,
video_status character varying(10) DEFAULT NULL,
video_user character varying(128) DEFAULT NULL,
is_frozen character(1) NOT NULL DEFAULT 'N',
custom_t1 character varying(255) DEFAULT NULL::character varying,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying,
custom_n4 bigint,
custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying,
custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying,
custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_d11 timestamp without time zone,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_d12 timestamp without time zone,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_d13 timestamp without time zone,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_d14 timestamp without time zone,
custom_t15 character varying(255) DEFAULT NULL::character varying,
is_complete character(1) DEFAULT 'N'::bpchar,
is_folder_out character(1) DEFAULT 'N'::bpchar,
last_modified_date timestamp without time zone,
CONSTRAINT folders_pkey PRIMARY KEY (folders_system_id)
)
WITH (OIDS=FALSE);
CREATE TABLE folders_out (
folder_out_id serial NOT NULL,
folder_system_id integer NOT NULL,
last_name character varying(255) NOT NULL,
first_name character varying(255) NOT NULL,
last_name_folder_out character varying(255) NOT NULL,
first_name_folder_out character varying(255) NOT NULL,
put_out_pattern character varying(255) NOT NULL,
put_out_date timestamp without time zone NOT NULL,
return_date timestamp without time zone NOT NULL,
return_flag character(1) NOT NULL default 'N'::bpchar,
CONSTRAINT folders_out_pkey PRIMARY KEY (folder_out_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE foldertype_id_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807 START 200
CACHE 1;
CREATE TABLE foldertypes
(
foldertype_id bigint NOT NULL DEFAULT nextval('foldertype_id_id_seq'::regclass),
foldertype_label character varying(255) NOT NULL,
maarch_comment text,
retention_time character varying(50),
custom_d1 character varying(10) DEFAULT '0000000000'::character varying,
custom_f1 character varying(10) DEFAULT '0000000000'::character varying,
custom_n1 character varying(10) DEFAULT '0000000000'::character varying,
custom_t1 character varying(10) DEFAULT '0000000000'::character varying,
custom_d2 character varying(10) DEFAULT '0000000000'::character varying,
custom_f2 character varying(10) DEFAULT '0000000000'::character varying,
custom_n2 character varying(10) DEFAULT '0000000000'::character varying,
custom_t2 character varying(10) DEFAULT '0000000000'::character varying,
custom_d3 character varying(10) DEFAULT '0000000000'::character varying,
custom_f3 character varying(10) DEFAULT '0000000000'::character varying,
custom_n3 character varying(10) DEFAULT '0000000000'::character varying,
custom_t3 character varying(10) DEFAULT '0000000000'::character varying,
custom_d4 character varying(10) DEFAULT '0000000000'::character varying,
custom_f4 character varying(10) DEFAULT '0000000000'::character varying,
custom_n4 character varying(10) DEFAULT '0000000000'::character varying,
custom_t4 character varying(10) DEFAULT '0000000000'::character varying,
custom_d5 character varying(10) DEFAULT '0000000000'::character varying,
custom_f5 character varying(10) DEFAULT '0000000000'::character varying,
custom_n5 character varying(10) DEFAULT '0000000000'::character varying,
custom_t5 character varying(10) DEFAULT '0000000000'::character varying,
custom_d6 character varying(10) DEFAULT '0000000000'::character varying,
custom_t6 character varying(10) DEFAULT '0000000000'::character varying,
custom_d7 character varying(10) DEFAULT '0000000000'::character varying,
custom_t7 character varying(10) DEFAULT '0000000000'::character varying,
custom_d8 character varying(10) DEFAULT '0000000000'::character varying,
custom_t8 character varying(10) DEFAULT '0000000000'::character varying,
custom_d9 character varying(10) DEFAULT '0000000000'::character varying,
custom_t9 character varying(10) DEFAULT '0000000000'::character varying,
custom_d10 character varying(10) DEFAULT '0000000000'::character varying,
custom_t10 character varying(10) DEFAULT '0000000000'::character varying,
custom_t11 character varying(10) DEFAULT '0000000000'::character varying,
custom_t12 character varying(10) DEFAULT '0000000000'::character varying,
custom_t13 character varying(10) DEFAULT '0000000000'::character varying,
custom_t14 character varying(10) DEFAULT '0000000000'::character varying,
custom_t15 character varying(10) DEFAULT '0000000000'::character varying,
coll_id character varying(32),
CONSTRAINT foldertypes_pkey PRIMARY KEY (foldertype_id)
)
WITH (OIDS=FALSE);
CREATE TABLE foldertypes_doctypes
(
foldertype_id integer NOT NULL,
doctype_id integer NOT NULL,
CONSTRAINT foldertypes_doctypes_pkey PRIMARY KEY (foldertype_id, doctype_id)
)
WITH (OIDS=FALSE);
CREATE TABLE foldertypes_doctypes_level1
(
foldertype_id integer NOT NULL,
doctypes_first_level_id integer NOT NULL,
CONSTRAINT foldertypes_doctypes_level1_pkey PRIMARY KEY (foldertype_id, doctypes_first_level_id)
)
WITH (OIDS=FALSE);
CREATE TABLE foldertypes_indexes
(
foldertype_id bigint NOT NULL,
field_name character varying(255) NOT NULL,
mandatory character(1) NOT NULL DEFAULT 'N'::bpchar, CONSTRAINT foldertypes_indexes_pkey PRIMARY KEY (foldertype_id, field_name)
)
WITH (OIDS=FALSE);
-- modules/full_text/sql/structure/full_text.postgresql.sql
CREATE TABLE fulltext
(
coll_id character varying(32) NOT NULL,
res_id bigint NOT NULL,
text_type character varying(10) NOT NULL DEFAULT 'CON'::character varying,
fulltext_content text,
CONSTRAINT coll_id_res_id PRIMARY KEY (coll_id, res_id)
)
WITH (
OIDS=FALSE
);
-- modules/life_cycle/sql/structure/life_cycle.postgresql.sql
CREATE TABLE lc_policies
(
policy_id character varying(32) NOT NULL,
policy_name character varying(255) NOT NULL,
policy_desc character varying(255) NOT NULL,
CONSTRAINT lc_policies_pkey PRIMARY KEY (policy_id)
)
WITH (OIDS = FALSE);
CREATE TABLE lc_cycles
(
policy_id character varying(32) NOT NULL,
cycle_id character varying(32) NOT NULL,
cycle_desc character varying(255) NOT NULL,
sequence_number integer NOT NULL,
where_clause text,
break_key character varying(255) DEFAULT NULL,
validation_mode character varying(32) NOT NULL,
CONSTRAINT lc_cycle_pkey PRIMARY KEY (policy_id, cycle_id)
)
WITH (OIDS = FALSE);
CREATE TABLE lc_cycle_steps
(
policy_id character varying(32) NOT NULL,
cycle_id character varying(32) NOT NULL,
cycle_step_id character varying(32) NOT NULL,
cycle_step_desc character varying(255) NOT NULL,
docserver_type_id character varying(32) NOT NULL,
is_allow_failure character(1) NOT NULL DEFAULT 'N'::bpchar,
step_operation character varying(32) NOT NULL,
sequence_number integer NOT NULL,
is_must_complete character(1) NOT NULL DEFAULT 'N'::bpchar,
preprocess_script character varying(255) DEFAULT NULL,
postprocess_script character varying(255) DEFAULT NULL,
CONSTRAINT lc_cycle_steps_pkey PRIMARY KEY (policy_id, cycle_id, cycle_step_id, docserver_type_id)
)
WITH (OIDS = FALSE);
CREATE TABLE lc_stack
(
policy_id character varying(32) NOT NULL,
cycle_id character varying(32) NOT NULL,
cycle_step_id character varying(32) NOT NULL,
coll_id character varying(32) NOT NULL,
res_id bigint NOT NULL,
cnt_retry integer DEFAULT NULL, status character(1) NOT NULL,
CONSTRAINT lc_stack_pkey PRIMARY KEY (policy_id, cycle_id, cycle_step_id, res_id)
)
WITH (OIDS = FALSE);
-- modules/notes/sql/structure/notes.postgresql.sql
CREATE SEQUENCE notes_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 20
CACHE 1;
CREATE TABLE notes
(
id bigint NOT NULL DEFAULT nextval('notes_seq'::regclass),
identifier bigint NOT NULL,
tablename character varying(50),
user_id character varying(128) NOT NULL,
date_note date NOT NULL,
note_text text NOT NULL,
coll_id character varying(50),
CONSTRAINT notes_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE notes_entities_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 20
CACHE 1;
CREATE TABLE note_entities
(
id bigint NOT NULL DEFAULT nextval('notes_entities_id_seq'::regclass),
note_id bigint NOT NULL,
item_id character varying(50),
CONSTRAINT note_entities_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
-- modules/notes/sql/structure/notifications.postgresql.sql
CREATE SEQUENCE notifications_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 100
CACHE 1;
CREATE TABLE notifications
(
notification_sid bigint NOT NULL DEFAULT nextval('notifications_seq'::regclass),
notification_id character varying(50) NOT NULL,
description character varying(255),
is_enabled character varying(1) NOT NULL default 'Y'::bpchar,
event_id character varying(255) NOT NULL,
notification_mode character varying(30) NOT NULL,
template_id bigint,
rss_url_template text,
diffusion_type character varying(50) NOT NULL,
diffusion_properties character varying(255), attachfor_type character varying(50),
attachfor_properties character varying(255),
CONSTRAINT notifications_pkey PRIMARY KEY (notification_sid)
)
WITH (
OIDS=FALSE
);
CREATE SEQUENCE notif_event_stack_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
-- DROP TABLE notif_event_stack
CREATE TABLE notif_event_stack
(
event_stack_sid bigint NOT NULL DEFAULT nextval('notif_event_stack_seq'::regclass),
notification_sid bigint NOT NULL,
table_name character varying(50) NOT NULL,
record_id character varying(50) NOT NULL,
user_id character varying(128) NOT NULL,
event_info character varying(255) NOT NULL,
event_date timestamp without time zone NOT NULL,
exec_date timestamp without time zone,
exec_result character varying(50),
CONSTRAINT notif_event_stack_pkey PRIMARY KEY (event_stack_sid)
)
WITH (
OIDS=FALSE
);
CREATE SEQUENCE notif_email_stack_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
-- DROP TABLE notif_email_stack
CREATE TABLE notif_email_stack
(
email_stack_sid bigint NOT NULL DEFAULT nextval('notif_email_stack_seq'::regclass),
sender character varying(255) NOT NULL,
reply_to character varying(255),
recipient character varying(2000) NOT NULL,
cc character varying(2000),
bcc character varying(2000),
subject character varying(255),
html_body text,
text_body text,
charset character varying(50) NOT NULL,
attachments character varying(2000),
module character varying(50) NOT NULL,
exec_date timestamp without time zone,
exec_result character varying(50),
CONSTRAINT notif_email_stack_pkey PRIMARY KEY (email_stack_sid)
)
WITH (
OIDS=FALSE
);
CREATE SEQUENCE notif_rss_stack_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1 CACHE 1;
CREATE TABLE notif_rss_stack
(
rss_stack_sid bigint NOT NULL DEFAULT nextval('notif_rss_stack_seq'::regclass),
rss_user_id character varying(128) NOT NULL,
rss_event_stack_sid bigint NOT NULL,
rss_event_url text,
CONSTRAINT notif_rss_stack_pkey PRIMARY KEY (rss_stack_sid )
)
WITH (
OIDS=FALSE
);
-- modules/physical_archive/sql/structure/physical_archive.postgresql.sql
create or replace function update_the_db() returns void as
$$
begin
if not exists(select * from information_schema.tables where table_name = 'ar_boxes') then
CREATE TABLE ar_boxes (
arbox_id serial NOT NULL,
title character varying(255) DEFAULT NULL,
subject character varying(255) DEFAULT NULL,
description text ,
entity_id character varying(32) DEFAULT NULL,
arcontainer_id integer NOT NULL,
status character varying(3) DEFAULT NULL,
creation_date timestamp without time zone DEFAULT NULL,
retention_time character varying(50) DEFAULT NULL,
custom_t1 character varying(3) DEFAULT NULL,
custom_n1 integer,
custom_f1 numeric,
custom_d1 timestamp without time zone DEFAULT NULL,
custom_t2 character varying(3) DEFAULT NULL,
custom_n2 integer,
custom_f2 numeric,
custom_d2 timestamp without time zone DEFAULT NULL,
custom_t3 character varying(50) DEFAULT NULL,
custom_n3 integer,
custom_f3 numeric,
custom_d3 timestamp without time zone DEFAULT NULL,
custom_t4 character varying(50) DEFAULT NULL,
custom_n4 integer,
custom_f4 numeric,
custom_d4 timestamp without time zone DEFAULT NULL,
custom_t5 character varying(255) DEFAULT NULL,
custom_n5 integer,
custom_f5 numeric,
custom_d5 timestamp without time zone DEFAULT NULL,
custom_t6 character varying(255) DEFAULT NULL,
custom_t7 character varying(255) DEFAULT NULL,
custom_t8 character varying(255) DEFAULT NULL,
custom_t9 character varying(255) DEFAULT NULL,
custom_t10 character varying(255) DEFAULT NULL,
custom_t11 character varying(255) DEFAULT NULL,
CONSTRAINT ar_boxes_pkey PRIMARY KEY (arbox_id)
) ;
end if;
end;
$$
language 'plpgsql';
select update_the_db();
drop function update_the_db();
create or replace function update_the_db() returns void as
$$
begin
if not exists(select * from information_schema.tables where table_name = 'ar_containers') then
CREATE TABLE ar_containers
(
arcontainer_id serial NOT NULL ,
arcontainer_desc character varying(255) DEFAULT NULL,
status character varying(3) DEFAULT NULL,
ctype_id character varying(32) DEFAULT NULL,
position_id bigint DEFAULT NULL,
creation_date timestamp without time zone DEFAULT NULL,
entity_id character varying(32) DEFAULT NULL,
retention_time character varying(50) DEFAULT NULL,
custom_t1 character varying(50) DEFAULT NULL,
custom_n1 integer,
custom_f1 numeric,
custom_d1 timestamp without time zone DEFAULT NULL,
custom_t2 character varying(3) DEFAULT NULL,
custom_n2 integer,
custom_f2 numeric,
custom_d2 timestamp without time zone DEFAULT NULL,
CONSTRAINT ar_containers_pkey PRIMARY KEY (arcontainer_id)
) ;
end if;
end;
$$
language 'plpgsql';
select update_the_db();
drop function update_the_db();
CREATE TABLE ar_batch
(
arbatch_id serial NOT NULL ,
title character varying(255) DEFAULT NULL,
subject character varying(255) DEFAULT NULL,
description text,
arbox_id bigint,
status character varying(3) DEFAULT NULL,
creation_date timestamp without time zone DEFAULT NULL,
retention_time character varying(50) DEFAULT NULL,
custom_t1 character varying(3) DEFAULT NULL,
custom_n1 integer,
custom_f1 numeric,
custom_d1 timestamp without time zone DEFAULT NULL,
custom_t2 character varying(3) DEFAULT NULL,
custom_n2 integer,
custom_f2 numeric,
custom_d2 timestamp without time zone DEFAULT NULL,
custom_t3 character varying(50) DEFAULT NULL,
custom_n3 integer,
custom_f3 numeric,
custom_d3 timestamp without time zone DEFAULT NULL,
custom_t4 character varying(50) DEFAULT NULL,
custom_n4 integer,
custom_f4 numeric,
custom_d4 timestamp without time zone DEFAULT NULL,
custom_t5 character varying(255) DEFAULT NULL,
custom_n5 integer,
custom_f5 numeric,
custom_d5 timestamp without time zone DEFAULT NULL,
custom_t6 character varying(255) DEFAULT NULL,
custom_t7 character varying(255) DEFAULT NULL,
custom_t8 character varying(255) DEFAULT NULL, custom_t9 character varying(255) DEFAULT NULL,
custom_t10 character varying(255) DEFAULT NULL,
custom_t11 character varying(255) DEFAULT NULL,
CONSTRAINT ar_batch_pkey PRIMARY KEY (arbatch_id)
) ;
-- modules/postindexing/sql/structure/postindexing.postgresql.sql
-- modules/reports/sql/structure/reports.postgresql.sql
CREATE TABLE usergroups_reports
(
group_id character varying(32) NOT NULL,
report_id character varying(50) NOT NULL,
CONSTRAINT usergroups_reports_pkey PRIMARY KEY (group_id, report_id)
)
WITH (OIDS=FALSE);
-- modules/templates/sql/structure/templates.postgresql.sql
CREATE SEQUENCE templates_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 100
CACHE 1;
CREATE SEQUENCE templates_association_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 100
CACHE 1;
CREATE TABLE templates
(
template_id bigint NOT NULL DEFAULT nextval('templates_seq'::regclass),
template_label character varying(255) DEFAULT NULL::character varying,
template_comment character varying(255) DEFAULT NULL::character varying,
template_content text,
template_type character varying(32) NOT NULL DEFAULT 'HTML'::character varying,
template_path character varying(255),
template_file_name character varying(255),
template_style character varying(255),
template_datasource character varying(32),
CONSTRAINT templates_pkey PRIMARY KEY (template_id)
)
WITH (OIDS=FALSE);
CREATE TABLE templates_association
(
system_id bigint NOT NULL DEFAULT nextval('templates_association_seq'::regclass),
template_id bigint NOT NULL,
what character varying(255) NOT NULL,
value_field character varying(255) NOT NULL,
maarch_module character varying(255) NOT NULL DEFAULT 'apps'::character varying,
CONSTRAINT templates_association_pkey PRIMARY KEY (system_id)
)
WITH (
OIDS=FALSE
);
CREATE TABLE templates_doctype_ext
( template_id bigint DEFAULT NULL,
type_id integer NOT NULL,
is_generated character(1) NOT NULL DEFAULT 'N'::bpchar
)
WITH (OIDS=FALSE);
-- apps/maarch_entreprise/sql/structure/apps.postgresql.sql
CREATE SEQUENCE contact_id_seq
INCREMENT 1
MINVALUE 14
MAXVALUE 9223372036854775807
START 200
CACHE 1;
CREATE TABLE contacts (
contact_id bigint NOT NULL DEFAULT nextval('contact_id_seq'::regclass),
lastname character varying(255),
firstname character varying(255),
society character varying(255),
function character varying(255),
address_num character varying(32) ,
address_street character varying(255),
address_complement character varying(255),
address_town character varying(255),
address_postal_code character varying(255),
address_country character varying(255),
email character varying(255),
phone character varying(20),
other_data text ,
is_corporate_person character(1) NOT NULL DEFAULT 'Y'::bpchar,
user_id character varying(128),
title character varying(255),
business_id character varying(255),
ref_identifier character varying(255),
acc_number character varying(50),
entity_id character varying(32),
contact_type character varying(255) NOT NULL DEFAULT 'letter'::character varying,
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
is_private character varying(1) NOT NULL DEFAULT 'N'::character varying,
CONSTRAINT contacts_pkey PRIMARY KEY (contact_id)
) WITH (OIDS=FALSE);
CREATE SEQUENCE query_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 10
CACHE 1;
CREATE TABLE saved_queries (
query_id bigint NOT NULL DEFAULT nextval('query_id_seq'::regclass),
user_id character varying(128) default NULL,
query_name character varying(255) NOT NULL,
creation_date timestamp without time zone NOT NULL,
created_by character varying(128) NOT NULL,
query_type character varying(50) NOT NULL,
query_txt text NOT NULL,
last_modification_date timestamp without time zone,
CONSTRAINT saved_queries_pkey PRIMARY KEY (query_id)
) WITH (OIDS=FALSE);
CREATE SEQUENCE doctypes_first_level_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 200
CACHE 1;
CREATE TABLE doctypes_first_level
(
doctypes_first_level_id integer NOT NULL DEFAULT nextval('doctypes_first_level_id_seq'::regclass),
doctypes_first_level_label character varying(255) NOT NULL,
css_style character varying(255),
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT doctypes_first_level_pkey PRIMARY KEY (doctypes_first_level_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE doctypes_second_level_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 200
CACHE 1;
CREATE TABLE doctypes_second_level
(
doctypes_second_level_id integer NOT NULL DEFAULT nextval('doctypes_second_level_id_seq'::regclass),
doctypes_second_level_label character varying(255) NOT NULL,
doctypes_first_level_id integer NOT NULL,
css_style character varying(255),
enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT doctypes_second_level_pkey PRIMARY KEY (doctypes_second_level_id)
)
WITH (OIDS=FALSE);
-- Table: rp_history
CREATE SEQUENCE rp_history_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE rp_history
(
system_id bigint NOT NULL DEFAULT nextval('rp_history_id_seq'::regclass),
table_name character varying(32) NOT NULL,
rp_cycle bigint NOT NULL,
start_res_id bigint NOT NULL,
stop_res_id bigint NOT NULL,
start_date timestamp without time zone NOT NULL,
stop_date timestamp without time zone NOT NULL,
res_count bigint NOT NULL,
fail_count bigint NOT NULL,
CONSTRAINT rp_history_pkey PRIMARY KEY (system_id)
)
WITH (
OIDS=FALSE
);
CREATE TABLE tags
(
tag_label character varying(50) NOT NULL,
coll_id character varying(50) NOT NULL,
res_id bigint NOT NULL,
CONSTRAINT tagsjoin_pkey PRIMARY KEY (tag_label, coll_id, res_id )
)
WITH (OIDS=FALSE);
CREATE SEQUENCE res_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 100
CACHE 1;
CREATE TABLE res_x
(
res_id bigint NOT NULL DEFAULT nextval('res_id_seq'::regclass),
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint NOT NULL,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL,
ocr_result character varying(10) DEFAULT NULL,
converter_result character varying(10) DEFAULT NULL,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) NOT NULL,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
arbatch_id bigint DEFAULT NULL,
policy_id character varying(32) DEFAULT NULL::character varying,
cycle_id character varying(32) DEFAULT NULL::character varying,
cycle_date timestamp without time zone,
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_t1 text,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying, custom_n4 bigint,
custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying,
custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying,
custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_t15 character varying(255) DEFAULT NULL::character varying,
tablename character varying(32) DEFAULT 'res_x'::character varying,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
video_batch integer DEFAULT NULL,
video_time integer DEFAULT NULL,
video_user character varying(128) DEFAULT NULL,
video_date timestamp without time zone,
esign_proof_id character varying(255),
esign_proof_content text,
esign_content text,
esign_date timestamp without time zone,
CONSTRAINT res_x_pkey PRIMARY KEY (res_id)
)
WITH (OIDS=FALSE);
CREATE TABLE adr_x
(
res_id bigint NOT NULL,
docserver_id character varying(32) NOT NULL,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
adr_priority integer NOT NULL,
CONSTRAINT adr_x_pkey PRIMARY KEY (res_id, docserver_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE res_id_mlb_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 100
CACHE 1;
CREATE TABLE res_letterbox
(
res_id bigint NOT NULL DEFAULT nextval('res_id_mlb_seq'::regclass),
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint NOT NULL,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL, ocr_result character varying(10) DEFAULT NULL,
converter_result character varying(10) DEFAULT NULL,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) NOT NULL,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
arbatch_id bigint DEFAULT NULL,
policy_id character varying(32) DEFAULT NULL::character varying,
cycle_id character varying(32) DEFAULT NULL::character varying,
cycle_date timestamp without time zone,
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_t1 text,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying,
custom_n4 bigint,
custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying,
custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying, custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_t15 character varying(255) DEFAULT NULL::character varying,
tablename character varying(32) DEFAULT 'res_letterbox'::character varying,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
video_batch integer DEFAULT NULL,
video_time integer DEFAULT NULL,
video_user character varying(128) DEFAULT NULL,
video_date timestamp without time zone,
esign_proof_id character varying(255),
esign_proof_content text,
esign_content text,
esign_date timestamp without time zone,
CONSTRAINT res_letterbox_pkey PRIMARY KEY (res_id)
)
WITH (OIDS=FALSE);
CREATE SEQUENCE res_linked_mlb_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 171
CACHE 1;
CREATE TABLE res_linked
(
id bigint NOT NULL DEFAULT nextval('res_linked_mlb_seq'::regclass),
res_parent bigint NOT NULL,
res_child bigint NOT NULL,
coll_id character varying(50) NOT NULL,
CONSTRAINT res_linked_primary PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE TABLE mlb_coll_ext (
res_id bigint NOT NULL,
category_id character varying(50) NOT NULL,
exp_contact_id integer default NULL,
exp_user_id character varying(128) default NULL,
dest_contact_id integer default NULL,
dest_user_id character varying(128) default NULL,
nature_id character varying(50),
alt_identifier character varying(255) default NULL,
admission_date timestamp without time zone,
answer_type_bitmask character varying(7) default NULL,
other_answer_desc character varying(255) DEFAULT NULL::character varying,
process_limit_date timestamp without time zone default NULL,
process_notes text,
closing_date timestamp without time zone default NULL,
alarm1_date timestamp without time zone default NULL,
alarm2_date timestamp without time zone default NULL,
flag_notif char(1) default 'N'::character varying ,
flag_alarm1 char(1) default 'N'::character varying ,
flag_alarm2 char(1) default 'N'::character varying
)WITH (OIDS=FALSE);
CREATE SEQUENCE res_id_version_letterbox_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 100
CACHE 1;
CREATE TABLE res_version_letterbox
(
res_id bigint NOT NULL DEFAULT nextval('res_id_version_letterbox_seq'::regclass),
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint NOT NULL,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL::character varying,
ocr_result character varying(10) DEFAULT NULL::character varying,
converter_result character varying(10) DEFAULT NULL::character varying,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) NOT NULL,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
arbatch_id bigint,
policy_id character varying(32),
cycle_id character varying(32),
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_t1 text,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying,
custom_n4 bigint, custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying,
custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying,
custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_t15 character varying(255) DEFAULT NULL::character varying,
tablename character varying(32) DEFAULT 'res_version_letterbox'::character varying,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
video_batch integer,
video_time integer,
video_user character varying(128) DEFAULT NULL::character varying,
video_date timestamp without time zone,
cycle_date timestamp without time zone,
coll_id character varying(32) NOT NULL,
res_id_master bigint,
CONSTRAINT res_version_letterbox_pkey PRIMARY KEY (res_id)
)
WITH (
OIDS=FALSE
);
CREATE SEQUENCE res_id_version_x_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 100
CACHE 1;
CREATE TABLE res_version_x
(
res_id bigint NOT NULL DEFAULT nextval('res_id_version_x_seq'::regclass),
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint NOT NULL,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL::character varying,
ocr_result character varying(10) DEFAULT NULL::character varying,
converter_result character varying(10) DEFAULT NULL::character varying,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying, path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) NOT NULL,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
arbatch_id bigint,
policy_id character varying(32),
cycle_id character varying(32),
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_t1 text,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying,
custom_n4 bigint,
custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying,
custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying,
custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_t15 character varying(255) DEFAULT NULL::character varying,
tablename character varying(32) DEFAULT 'res_version_x'::character varying,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
video_batch integer,
video_time integer,
video_user character varying(128) DEFAULT NULL::character varying, video_date timestamp without time zone,
cycle_date timestamp without time zone,
coll_id character varying(32) NOT NULL,
res_id_master bigint,
CONSTRAINT res_version_x_pkey PRIMARY KEY (res_id)
)
WITH (
OIDS=FALSE
);
CREATE TABLE mlb_doctype_ext (
type_id bigint NOT NULL,
process_delay bigint NOT NULL DEFAULT '21',
delay1 bigint NOT NULL DEFAULT '14',
delay2 bigint NOT NULL DEFAULT '1',
CONSTRAINT type_id PRIMARY KEY (type_id)
)
WITH (OIDS=FALSE);
CREATE TABLE doctypes_indexes
(
type_id bigint NOT NULL,
coll_id character varying(32) NOT NULL,
field_name character varying(255) NOT NULL,
mandatory character(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT doctypes_indexes_pkey PRIMARY KEY (type_id, coll_id, field_name)
)
WITH (OIDS=FALSE);
CREATE TABLE groupbasket_status
(
system_id serial NOT NULL,
group_id character varying(32) NOT NULL,
basket_id character varying(32) NOT NULL,
action_id integer NOT NULL,
status_id character varying(32),
CONSTRAINT groupbasket_status_pkey PRIMARY KEY (system_id)
)
WITH (
OIDS=FALSE
);
CREATE TABLE groupbasket_difflist_types
(
system_id serial NOT NULL,
group_id character varying(32) NOT NULL,
basket_id character varying(32) NOT NULL,
action_id integer NOT NULL,
difflist_type_id character varying(50) NOT NULL,
CONSTRAINT groupbasket_difflist_types_pkey PRIMARY KEY (system_id )
)
WITH (
OIDS=FALSE
);
CREATE TABLE groupbasket_difflist_roles
(
system_id serial NOT NULL,
group_id character varying(32) NOT NULL,
basket_id character varying(32) NOT NULL,
action_id integer NOT NULL,
difflist_role_id character varying(50) NOT NULL,
CONSTRAINT groupbasket_difflist_roles_pkey PRIMARY KEY (system_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- ---- RECORDS MANAGEMENT V1.0 DATABASE SCHEMA --
-- --
-- ************************************************************************* --
-- ************************************************************************* --
-- DATA TABLES --
-- ************************************************************************* --
-- ************************************************************************* --
-- MESSAGES - IOS --
-- ************************************************************************* --
-- Records Management Messages
DROP TABLE IF EXISTS rm_ios CASCADE;
CREATE TABLE rm_ios
(
io_id SERIAL NOT NULL,
io_type character varying(100) NOT NULL,
io_status character varying(10) NOT NULL,
docserver_id character varying(32) NOT NULL DEFAULT 'IOS',
io_path character varying(512),
io_filename character varying(255),
date timestamp without time zone NOT NULL,
reply_code character varying(50),
operation_date timestamp without time zone default null,
related_identifier character varying(100),
identifier character varying(100) NOT NULL,
reference_identifier character varying(100),
CONSTRAINT "rm_ios_pkey" PRIMARY KEY (io_id)
)
WITH (
OIDS=FALSE
);
-- Message comments
DROP TABLE IF EXISTS rm_comments CASCADE;
CREATE TABLE rm_comments
(
comment_id SERIAL,
io_id bigint NOT NULL,
comment text NOT NULL,
date timestamp without time zone,
author character varying(255),
CONSTRAINT "rm_comments_pkey" PRIMARY KEY (comment_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- ARCHIVES AND OBJECTS --
-- ************************************************************************* --
-- Archives and ArchiveObjects
DROP TABLE IF EXISTS rm_items CASCADE;
CREATE TABLE rm_items
(
item_id SERIAL,
item_type character varying(50),
parent_item_id bigint,
schedule_id bigint,
archival_agency_item_identifier character varying(100),
archival_agreement character varying(100),
archival_profile character varying(100),
description_language text NOT NULL default 'fra',
name text NOT NULL,
originating_agency_item_identifier character varying(100), service_level text,
transferring_agency_item_identifier character varying(100),
CONSTRAINT "rm_items_pkey" PRIMARY KEY (item_id)
)
WITH (
OIDS=FALSE
);
-- Archives and ArchiveObjects ContentDescription
DROP TABLE IF EXISTS rm_content_descriptions CASCADE;
CREATE TABLE rm_content_descriptions
(
item_id bigint NOT NULL,
description text,
description_level character varying(50) NOT NULL DEFAULT 'recordgrp',
file_plan_position text,
language text NOT NULL DEFAULT 'fra',
latest_date date,
oldest_date date,
other_descriptive_data text,
CONSTRAINT "rm_content_descriptions_pkey" PRIMARY KEY (item_id)
)
WITH (
OIDS=FALSE
);
-- ContentDescription CustodialHistory
DROP TABLE IF EXISTS rm_custodial_history CASCADE;
CREATE TABLE rm_custodial_history
(
item_id bigint NOT NULL,
"when" date,
custodial_history_item text NOT NULL,
CONSTRAINT "rm_custodial_history_pkey" PRIMARY KEY (item_id)
)
WITH (
OIDS=FALSE
);
-- Archives and ArchiveObjects Appraisal Rules
DROP TABLE IF EXISTS rm_appraisal_rules CASCADE;
CREATE TABLE rm_appraisal_rules
(
appraisal_rule_id SERIAL,
parent_id bigint NOT NULL,
parent_type character varying(50) NOT NULL,
code character varying(50),
duration integer,
start_date date,
CONSTRAINT "rm_appraisal_rules_pkey" PRIMARY KEY (appraisal_rule_id)
)
WITH (
OIDS=FALSE
);
-- Archives and ArchiveObjects Access Restriction Rules
DROP TABLE IF EXISTS rm_access_restriction_rules CASCADE;
CREATE TABLE rm_access_restriction_rules
(
access_restriction_rule_id SERIAL,
parent_id bigint NOT NULL,
parent_type character varying(50) NOT NULL,
code character varying(50),
start_date date,
CONSTRAINT "rm_access_restriction_rules_pkey" PRIMARY KEY (access_restriction_rule_id)
)
WITH (
OIDS=FALSE
);
-- Documents
DROP TABLE IF EXISTS rm_documents CASCADE;
CREATE TABLE rm_documents
(
res_id SERIAL,
coll_id character varying(32),
docserver_id character varying(32) NOT NULL,
path character varying(255) DEFAULT NULL,
filename character varying(255) DEFAULT NULL,
type_id bigint NOT NULL,
item_id bigint,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
offset_doc character varying(255) DEFAULT NULL,
logical_adr character varying(255) DEFAULT NULL,
policy_id character varying(32) DEFAULT NULL,
cycle_id character varying(32) DEFAULT NULL,
cycle_date timestamp without time zone,
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
coverage character varying(255) DEFAULT NULL::character varying,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
archival_agency_document_identifier character varying(100),
copy character varying(1),
creation_date timestamp without time zone,
doc_date timestamp without time zone,
description text,
fingerprint character varying(64),
issue timestamp without time zone,
doc_language text not null default 'fra'::bpchar,
originating_agency_document_identifier character varying(100),
subject text,
receipt timestamp without time zone,
response timestamp without time zone,
filesize bigint default 0,
unit_code character varying(10) default 'A99',
status character varying(50),
submission timestamp without time zone,
transferring_agency_document_identifier character varying(100),
content_type character varying(10) DEFAULT 'CDO',
CONSTRAINT "rm_documents_pkey" PRIMARY KEY (res_id)
)
WITH (
OIDS=FALSE
);
DROP TABLE IF EXISTS adr_rm CASCADE;
CREATE TABLE adr_rm
(
res_id bigint NOT NULL,
docserver_id character varying(32) NOT NULL,
path character varying(255) DEFAULT NULL::character varying, filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
adr_priority integer NOT NULL,
CONSTRAINT adr_rm_pkey PRIMARY KEY (res_id, docserver_id)
)
WITH (OIDS=FALSE);
-- Archives and ArchiveObjects Keywords
DROP TABLE IF EXISTS rm_keywords CASCADE;
CREATE TABLE rm_keywords
(
keyword_id SERIAL,
item_id bigint,
keyword_content text NOT NULL,
role character varying(50),
keyword_reference character varying(50),
keyword_type character varying(50),
CONSTRAINT "rm_keywords_pkey" PRIMARY KEY (keyword_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- ORGANIZATIONS --
-- ************************************************************************* --
-- Organizations
DROP TABLE IF EXISTS rm_organizations CASCADE;
CREATE TABLE rm_organizations
(
organization_id SERIAL,
parent_id bigint NOT NULL, -- Id of parent
parent_type character varying(50) NOT NULL, -- ArchiveTransfer, Archive, ArchiveObject, ArchiveTransferReply
role character varying(50) NOT NULL, -- TransferringAgency, ArchivalAgency, OriginatingAgency, Repository, ControlAuthority
entity_id character varying(32), -- Entity_id if related to maarch entity
business_type character varying(50),
description character varying(255),
identification character varying(100) NOT NULL,
legal_classification character varying(50),
name text,
CONSTRAINT "rm_organizations_pkey" PRIMARY KEY (organization_id)
)
WITH (
OIDS=FALSE
);
-- Addresses
DROP TABLE IF EXISTS rm_addresses CASCADE;
CREATE TABLE rm_addresses
(
address_id SERIAL,
parent_id bigint NOT NULL,
parent_type character varying(50), -- TransferringAgency, Contact...
entity_id character varying(32), -- Used if refAddress
user_id character varying(128), -- Used if refAddress
block_name character varying(255),
building_name character varying(255),
building_number character varying(255),
city_name character varying(255),
city_sub_division_name character varying(255),
country character varying(50),
floor_identification character varying(255),
postcode character varying(50),
post_office_box character varying(255),
room_identification character varying(255), street_name character varying(255),
CONSTRAINT "rm_addresses_pkey" PRIMARY KEY (address_id)
)
WITH (
OIDS=FALSE
);
-- Contacts
DROP TABLE IF EXISTS rm_contacts CASCADE;
CREATE TABLE rm_contacts
(
contact_id SERIAL,
organization_id bigint NOT NULL,
user_id character varying(128), -- User_id of related to Maarch user
department_name character varying(255),
identification character varying(100),
person_name character varying(255),
responsibility character varying(255),
CONSTRAINT "rm_contacts_pkey" PRIMARY KEY (contact_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- RELATIONS --
-- ************************************************************************* --
-- Relation between ios and archives
DROP TABLE IF EXISTS rm_io_archives_relations CASCADE;
CREATE TABLE rm_io_archives_relations
(
io_id bigint NOT NULL,
item_id bigint NOT NULL,
CONSTRAINT "rm_io_archives_relations_pkey" PRIMARY KEY (io_id, item_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- ENTITIES --
-- ************************************************************************* --
DROP TABLE IF EXISTS rm_entities CASCADE;
CREATE TABLE rm_entities
(
entity_id character varying(32) NOT NULL,
is_archival_agency character varying(1) NOT NULL DEFAULT 'N'::bpchar,
is_originating_agency character varying(1) NOT NULL DEFAULT 'N'::bpchar,
is_transferring_agency character varying(1) NOT NULL DEFAULT 'N'::bpchar,
is_repository character varying(1) NOT NULL DEFAULT 'N'::bpchar,
is_control_authority character varying(1) NOT NULL DEFAULT 'N'::bpchar,
rm_entity_type character varying(50) NOT NULL DEFAULT 'Collectivité'::bpchar,
parallel_forms_of_names text,
other_normalized_names text,
other_names text,
oldest_date date,
latest_date date,
history text,
places text,
legal_status text,
activities text,
mandates text,
structure text,
context text,
record_id character varying(100), institution_id character varying(255),
rules text,
status character varying(255),
detail_level character varying(255),
maintenance_dates text,
description_language character varying(100),
sources text,
maintenance_notes text,
CONSTRAINT "rm_entities_pkey" PRIMARY KEY (entity_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- AGREEMENTS --
-- ************************************************************************* --
DROP TABLE IF EXISTS rm_agreements CASCADE;
CREATE TABLE rm_agreements
(
agreement_id SERIAL,
identifier character varying(100) NOT NULL,
description character varying(255) NOT NULL,
comment TEXT,
archival_profile character varying(50) NOT NULL,
archival_entity_id character varying(100) NOT NULL,
transferring_entity_id character varying(100) NOT NULL,
begin_date date NOT NULL,
end_date date NOT NULL,
coll_id character varying(50) NOT NULL,
allowed_file_types TEXT NOT NULL,
transfer_max_size bigint NOT NULL DEFAULT 100000,
transfer_max_item integer NOT NULL DEFAULT 300,
transfer_count integer,
transfer_count_period character varying(20) DEFAULT 'MONTH'::bpchar,
transfer_total_size bigint NOT NULL DEFAULT 100000000,
is_enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
CONSTRAINT "rm_agreements_pkey" PRIMARY KEY (agreement_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- SCHEDULE --
-- ************************************************************************* --
DROP TABLE IF EXISTS rm_schedule CASCADE;
CREATE TABLE rm_schedule
(
type_id bigint not null,
appraisal_code character varying(50) NOT NULL,
appraisal_duration integer NOT NULL,
access_restriction_code character varying(50) NOT NULL,
service_level character varying(50),
notes text,
CONSTRAINT "rm_schedule_pkey" PRIMARY KEY (type_id)
)
WITH (
OIDS=FALSE
);
-- ************************************************************************* --
-- VUES --
-- ************************************************************************* --
-- Entities to organizations
DROP VIEW IF EXISTS rm_ref_organizations CASCADE;
CREATE OR REPLACE VIEW rm_ref_organizations AS
SELECT
entities.entity_id, null as business_type,
null as description,
business_id as identification,
null as legal_classification,
entity_label as name,
null as organization_id,
null as parent_id,
null as parent_type,
'TransferringAgency' as role
FROM entities
JOIN rm_entities ON entities.entity_id = rm_entities.entity_id
WHERE business_id != '' AND is_transferring_agency = 'Y'
UNION
SELECT
entities.entity_id,
null as business_type,
null as description,
business_id as identification,
null as legal_classification,
entity_label as name,
null as organization_id,
null as parent_id,
null as parent_type,
'ArchivalAgency' as role
FROM entities
JOIN rm_entities ON entities.entity_id = rm_entities.entity_id
WHERE business_id != '' AND is_archival_agency = 'Y'
UNION
SELECT
entities.entity_id,
null as business_type,
null as description,
business_id as identification,
null as legal_classification,
entity_label as name,
null as organization_id,
null as parent_id,
null as parent_type,
'OriginatingAgency' as role
FROM entities
JOIN rm_entities ON entities.entity_id = rm_entities.entity_id
WHERE business_id != '' AND business_id != '' AND is_originating_agency = 'Y'
UNION
SELECT
entities.entity_id,
null as business_type,
null as description,
business_id as identification,
null as legal_classification,
entity_label as name,
null as organization_id,
null as parent_id,
null as parent_type,
'Repository' as role
FROM entities
JOIN rm_entities ON entities.entity_id = rm_entities.entity_id
WHERE business_id != '' AND is_repository = 'Y'
UNION
SELECT
entities.entity_id,
null as business_type,
null as description,
business_id as identification,
null as legal_classification,
entity_label as name,
null as organization_id,
null as parent_id,
null as parent_type,
'ControlAuthority' as role
FROM entities JOIN rm_entities ON entities.entity_id = rm_entities.entity_id
WHERE business_id != '' AND is_control_authority = 'Y';
-- Entities/contacts to addresses
DROP VIEW IF EXISTS rm_ref_addresses CASCADE;
CREATE OR REPLACE VIEW rm_ref_addresses AS
SELECT
null as address_id,
null as parent_id,
null as parent_type,
entity_id,
'*' as user_id,
adrs_1 as street_name,
adrs_2 as block_name,
adrs_3 as post_office_box,
zipcode as postcode,
city as city_name,
null as building_name,
null as building_number,
null as city_sub_division_name,
null as country,
null as floor_identification,
null as room_identification
FROM entities
WHERE adrs_1 != '' OR adrs_1 != '' OR adrs_2 != '' OR adrs_3 != '' OR city != ''
UNION
SELECT
null as address_id,
null as parent_id,
null as parent_type,
entities.entity_id,
users.user_id,
adrs_1 as street_name,
adrs_2 as block_name,
adrs_3 as post_office_box,
zipcode as postcode,
city as city_name,
entity_label as room_identification,
null as building_name,
null as building_number,
null as city_sub_division_name,
null as country,
null as floor_identification
FROM users
LEFT JOIN users_entities ON users.user_id = users_entities.user_id
LEFT JOIN entities ON users_entities.entity_id = entities.entity_id
WHERE adrs_1 != '' OR adrs_1 != '' OR adrs_2 != '' OR adrs_3 != '' OR city != '';
-- Users to Contacts
DROP VIEW IF EXISTS rm_ref_contacts;
CREATE OR REPLACE VIEW rm_ref_contacts AS
SELECT
users.user_id,
NULL::bpchar AS contact_id,
NULL::bpchar AS organization_id,
NULL::bpchar AS identification,
entities.entity_label AS department_name,
users_entities.user_role AS responsibility,
(users.firstname::text || ' '::text) || users.lastname::text AS person_name
FROM users
JOIN users_entities ON users.user_id::text = users_entities.user_id::text
JOIN entities ON users_entities.entity_id::text = entities.entity_id::text;
-- RM_IOS
DROP VIEW IF EXISTS rm_ios_view;
CREATE OR REPLACE VIEW rm_ios_view AS
SELECT
rm_ios.*, ArchivalAgency.entity_id AS archival_agency_entity_id,
ArchivalContact.user_id AS archival_user_id,
RequestingAgency.entity_id AS requesting_agency_entity_id,
RequestingContact.user_id AS requesting_user_id,
rm_comments.comment AS comment,
Archives.name as archive_name,
count(ArchiveObjects) as nb_archive_objects
FROM rm_ios
LEFT JOIN rm_organizations AS ArchivalAgency ON ArchivalAgency.parent_id = rm_ios.io_id AND ArchivalAgency.role = 'ArchivalAgency'
LEFT JOIN rm_contacts AS ArchivalContact ON ArchivalContact.organization_id =
(
SELECT organization_id
FROM rm_contacts
WHERE rm_contacts.organization_id = ArchivalAgency.organization_id
ORDER BY contact_id
LIMIT 1
)
LEFT JOIN rm_organizations AS RequestingAgency ON RequestingAgency.parent_id = rm_ios.io_id AND RequestingAgency.role IN ('TransferringAgency', 'RequestingAgency')
LEFT JOIN rm_contacts AS RequestingContact ON RequestingContact.organization_id =
(
SELECT organization_id
FROM rm_contacts
WHERE rm_contacts.organization_id = RequestingAgency.organization_id
ORDER BY contact_id
LIMIT 1
)
LEFT JOIN rm_io_archives_relations IOArchives ON IOArchives.io_id = rm_ios.io_id
LEFT JOIN rm_items AS Archives ON Archives.item_id = IOArchives.item_id
LEFT JOIN rm_items AS ArchiveObjects ON ArchiveObjects.parent_item_id = Archives.item_id
LEFT JOIN rm_comments ON rm_comments.io_id =
(
SELECT io_id
FROM rm_comments
WHERE rm_comments.io_id = rm_ios.io_id
ORDER BY comment_id
LIMIT 1
)
GROUP BY
rm_ios.io_id,
rm_ios.io_type,
rm_ios.io_status,
rm_ios.docserver_id,
rm_ios.io_path,
rm_ios.io_filename,
rm_ios.date,
rm_ios.reply_code,
rm_ios.operation_date,
rm_ios.related_identifier,
rm_ios.identifier,
rm_ios.reference_identifier,
ArchivalAgency.entity_id,
RequestingAgency.entity_id,
ArchivalContact.user_id,
RequestingContact.user_id,
Archives.Name,
rm_comments.comment;
-- RES_VIEW_RM
DROP VIEW IF EXISTS rm_documents_view;
CREATE OR REPLACE VIEW rm_documents_view AS
SELECT
rm_documents.*,
rm_items.archival_agency_item_identifier,
rm_items.description_language,
rm_items.name,
rm_items.item_type,
rm_items.originating_agency_item_identifier, rm_items.service_level,
rm_items.transferring_agency_item_identifier,
rm_content_descriptions.description as content_description,
rm_content_descriptions.description_level,
rm_content_descriptions.file_plan_position,
rm_content_descriptions.language,
rm_content_descriptions.latest_date,
rm_content_descriptions.oldest_date,
rm_content_descriptions.other_descriptive_data,
rm_custodial_history.custodial_history_item,
doctypes.description as type_label,
doctypes_first_level.doctypes_first_level_id,
doctypes_first_level.doctypes_first_level_label,
doctypes_first_level.css_style as doctype_first_level_style,
doctypes_second_level.doctypes_second_level_id,
doctypes_second_level.doctypes_second_level_label,
doctypes_second_level.css_style as doctype_second_level_style,
file_plan_position.folders_system_id,
originating_agency.identification as originating_agency_identification,
originating_agency.entity_id as originating_agency_entity_id,
originating_agency.name as originating_agency_name,
rm_contacts.department_name as dest_user,
rm_appraisal_rules.code as appraisal_code,
rm_appraisal_rules.duration as appraisal_duration,
rm_appraisal_rules.start_date as appraisal_start_date,
rm_access_restriction_rules.code as access_restriction_code,
rm_access_restriction_rules.start_date as access_restriction_start_date
FROM rm_documents
LEFT JOIN rm_items on rm_items.item_id = rm_documents.item_id
LEFT JOIN rm_content_descriptions on rm_items.item_id = rm_content_descriptions.item_id
LEFT JOIN rm_custodial_history on rm_items.item_id = rm_custodial_history.item_id
LEFT JOIN doctypes on rm_documents.type_id = doctypes.type_id
LEFT JOIN doctypes_first_level ON doctypes.doctypes_first_level_id = doctypes_first_level.doctypes_first_level_id
LEFT JOIN doctypes_second_level ON doctypes.doctypes_second_level_id = doctypes_second_level.doctypes_second_level_id
LEFT JOIN rm_organizations AS originating_agency ON originating_agency.organization_id =
(
SELECT organization_id
FROM rm_organizations
WHERE rm_organizations.parent_id = rm_items.item_id AND rm_organizations.role = 'OriginatingAgency'
ORDER BY organization_id
LIMIT 1
)
LEFT JOIN rm_contacts on originating_agency.organization_id = rm_contacts.organization_id and contact_id =
(
SELECT contact_id
FROM rm_contacts
WHERE rm_contacts.organization_id = originating_agency.organization_id
ORDER BY contact_id
LIMIT 1
)
LEFT JOIN rm_appraisal_rules ON rm_appraisal_rules.parent_id = rm_items.item_id
LEFT JOIN rm_access_restriction_rules ON rm_access_restriction_rules.parent_id = rm_items.item_id
LEFT JOIN folders file_plan_position ON rm_content_descriptions.file_plan_position = file_plan_position.folder_id AND foldertype_id = '101'
LEFT JOIN folders schedule ON rm_items.schedule_id = schedule.folders_system_id
WHERE item_type = 'ArchiveObject';
-- log collection
-- res_log
DROP TABLE IF EXISTS res_log CASCADE;CREATE TABLE res_log
(
res_id SERIAL,
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint NOT NULL,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL,
ocr_result character varying(10) DEFAULT NULL,
converter_result character varying(10) DEFAULT NULL,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) NOT NULL,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
arbatch_id bigint DEFAULT NULL,
policy_id character varying(32) DEFAULT NULL::character varying,
cycle_id character varying(32) DEFAULT NULL::character varying,
cycle_date timestamp without time zone,
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_t1 text,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying,
custom_n4 bigint, custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying,
custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying,
custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_t15 character varying(255) DEFAULT NULL::character varying,
tablename character varying(32) DEFAULT 'res_log'::character varying,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
video_batch integer DEFAULT NULL,
video_time integer DEFAULT NULL,
video_user character varying(128) DEFAULT NULL,
video_date timestamp without time zone,
esign_proof_id character varying(255),
esign_proof_content text,
esign_content text,
esign_date timestamp without time zone,
CONSTRAINT res_log_pkey PRIMARY KEY (res_id)
)
WITH (OIDS=FALSE);
DROP TABLE IF EXISTS adr_log;
CREATE TABLE adr_log
(
res_id bigint NOT NULL,
docserver_id character varying(32) NOT NULL,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
adr_priority integer NOT NULL,
CONSTRAINT adr_log_pkey PRIMARY KEY (res_id, docserver_id)
)
WITH (OIDS=FALSE);
DROP VIEW IF EXISTS res_view_log;
CREATE OR REPLACE VIEW res_view_log AS
select * from res_log;
-- ************************************************************************* --
-- --
-- BUSINESS COLLECTION --
-- --
-- ************************************************************************* --
DROP TABLE IF EXISTS res_business CASCADE;
CREATE TABLE res_business
(
res_id SERIAL,
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint NOT NULL, format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL,
ocr_result character varying(10) DEFAULT NULL,
converter_result character varying(10) DEFAULT NULL,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) NOT NULL,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
arbatch_id bigint DEFAULT NULL,
policy_id character varying(32) DEFAULT NULL::character varying,
cycle_id character varying(32) DEFAULT NULL::character varying,
cycle_date timestamp without time zone,
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_t1 text,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying,
custom_n4 bigint,
custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying, custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying,
custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_t15 character varying(255) DEFAULT NULL::character varying,
tablename character varying(32) DEFAULT 'res_business'::character varying,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
video_batch integer DEFAULT NULL,
video_time integer DEFAULT NULL,
video_user character varying(128) DEFAULT NULL,
video_date timestamp without time zone,
esign_proof_id character varying(255),
esign_proof_content text,
esign_content text,
esign_date timestamp without time zone,
CONSTRAINT res_business_pkey PRIMARY KEY (res_id)
)
WITH (OIDS=FALSE);
DROP TABLE IF EXISTS adr_business;
CREATE TABLE adr_business
(
res_id bigint NOT NULL,
docserver_id character varying(32) NOT NULL,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying,
offset_doc character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
adr_priority integer NOT NULL,
CONSTRAINT adr_business_pkey PRIMARY KEY (res_id, docserver_id)
)
WITH (OIDS=FALSE);
DROP TABLE IF EXISTS res_version_business;
CREATE TABLE res_version_business
(
res_id serial,
title character varying(255) DEFAULT NULL::character varying,
subject text,
description text,
publisher character varying(255) DEFAULT NULL::character varying,
contributor character varying(255) DEFAULT NULL::character varying,
type_id bigint NOT NULL,
format character varying(50) NOT NULL,
typist character varying(128) NOT NULL,
creation_date timestamp without time zone NOT NULL,
fulltext_result character varying(10) DEFAULT NULL::character varying,
ocr_result character varying(10) DEFAULT NULL::character varying,
converter_result character varying(10) DEFAULT NULL::character varying,
author character varying(255) DEFAULT NULL::character varying,
author_name text,
identifier character varying(255) DEFAULT NULL::character varying,
source character varying(255) DEFAULT NULL::character varying,
doc_language character varying(50) DEFAULT NULL::character varying,
relation bigint,
coverage character varying(255) DEFAULT NULL::character varying,
doc_date timestamp without time zone,
docserver_id character varying(32) NOT NULL,
folders_system_id bigint,
arbox_id character varying(32) DEFAULT NULL::character varying,
path character varying(255) DEFAULT NULL::character varying,
filename character varying(255) DEFAULT NULL::character varying, offset_doc character varying(255) DEFAULT NULL::character varying,
logical_adr character varying(255) DEFAULT NULL::character varying,
fingerprint character varying(255) DEFAULT NULL::character varying,
filesize bigint,
is_paper character(1) DEFAULT NULL::bpchar,
page_count integer,
scan_date timestamp without time zone,
scan_user character varying(50) DEFAULT NULL::character varying,
scan_location character varying(255) DEFAULT NULL::character varying,
scan_wkstation character varying(255) DEFAULT NULL::character varying,
scan_batch character varying(50) DEFAULT NULL::character varying,
burn_batch character varying(50) DEFAULT NULL::character varying,
scan_postmark character varying(50) DEFAULT NULL::character varying,
envelop_id bigint,
status character varying(10) NOT NULL,
destination character varying(50) DEFAULT NULL::character varying,
approver character varying(50) DEFAULT NULL::character varying,
validation_date timestamp without time zone,
work_batch bigint,
origin character varying(50) DEFAULT NULL::character varying,
is_ingoing character(1) DEFAULT NULL::bpchar,
priority smallint,
arbatch_id bigint,
policy_id character varying(32),
cycle_id character varying(32),
is_multi_docservers character(1) NOT NULL DEFAULT 'N'::bpchar,
is_frozen character(1) NOT NULL DEFAULT 'N'::bpchar,
custom_t1 text,
custom_n1 bigint,
custom_f1 numeric,
custom_d1 timestamp without time zone,
custom_t2 character varying(255) DEFAULT NULL::character varying,
custom_n2 bigint,
custom_f2 numeric,
custom_d2 timestamp without time zone,
custom_t3 character varying(255) DEFAULT NULL::character varying,
custom_n3 bigint,
custom_f3 numeric,
custom_d3 timestamp without time zone,
custom_t4 character varying(255) DEFAULT NULL::character varying,
custom_n4 bigint,
custom_f4 numeric,
custom_d4 timestamp without time zone,
custom_t5 character varying(255) DEFAULT NULL::character varying,
custom_n5 bigint,
custom_f5 numeric,
custom_d5 timestamp without time zone,
custom_t6 character varying(255) DEFAULT NULL::character varying,
custom_d6 timestamp without time zone,
custom_t7 character varying(255) DEFAULT NULL::character varying,
custom_d7 timestamp without time zone,
custom_t8 character varying(255) DEFAULT NULL::character varying,
custom_d8 timestamp without time zone,
custom_t9 character varying(255) DEFAULT NULL::character varying,
custom_d9 timestamp without time zone,
custom_t10 character varying(255) DEFAULT NULL::character varying,
custom_d10 timestamp without time zone,
custom_t11 character varying(255) DEFAULT NULL::character varying,
custom_t12 character varying(255) DEFAULT NULL::character varying,
custom_t13 character varying(255) DEFAULT NULL::character varying,
custom_t14 character varying(255) DEFAULT NULL::character varying,
custom_t15 character varying(255) DEFAULT NULL::character varying,
tablename character varying(32) DEFAULT 'res_version_business'::character varying,
initiator character varying(50) DEFAULT NULL::character varying,
dest_user character varying(128) DEFAULT NULL::character varying,
video_batch integer,
video_time integer,
video_user character varying(128) DEFAULT NULL::character varying,
video_date timestamp without time zone,
cycle_date timestamp without time zone, coll_id character varying(32) NOT NULL,
res_id_master bigint,
CONSTRAINT res_version_business_pkey PRIMARY KEY (res_id)
)
WITH (
OIDS=FALSE
);
DROP TABLE IF EXISTS business_coll_ext CASCADE;
CREATE TABLE business_coll_ext (
res_id bigint NOT NULL,
category_id character varying(50) NOT NULL,
contact_id integer default NULL,
currency character varying(10) default NULL,
net_sum float default NULL,
tax_sum float default NULL,
total_sum float default NULL,
process_limit_date timestamp without time zone default NULL,
closing_date timestamp without time zone default NULL,
alarm1_date timestamp without time zone default NULL,
alarm2_date timestamp without time zone default NULL,
flag_notif char(1) default 'N'::character varying ,
flag_alarm1 char(1) default 'N'::character varying ,
flag_alarm2 char(1) default 'N'::character varying
)WITH (OIDS=FALSE);
DROP TABLE IF EXISTS invoice_types CASCADE;
CREATE TABLE invoice_types (
invoice_type_id character varying(50) NOT NULL,
invoice_type_name character varying(255) NOT NULL,
invoice_movement char(2) default 'DR'::character varying
)WITH (OIDS=FALSE);
-- sendmail module
CREATE TABLE sendmail
(
email_id serial NOT NULL,
coll_id character varying(32) NOT NULL,
res_id bigint NOT NULL,
user_id character varying(128) NOT NULL,
to_list character varying(255) NOT NULL,
cc_list character varying(255) DEFAULT NULL,
cci_list character varying(255) DEFAULT NULL,
email_object character varying(255) DEFAULT NULL,
email_body text,
is_res_master_attached character varying(1) NOT NULL DEFAULT 'Y',
res_version_id_list character varying(255) DEFAULT NULL,
res_attachment_id_list character varying(255) DEFAULT NULL,
note_id_list character varying(255) DEFAULT NULL,
is_html character varying(1) NOT NULL DEFAULT 'Y',
email_status character varying(1) NOT NULL DEFAULT 'D',
creation_date timestamp without time zone NOT NULL,
send_date timestamp without time zone DEFAULT NULL,
CONSTRAINT sendmail_pkey PRIMARY KEY (email_id )
);
--VIEWS
--view for demo
DROP VIEW IF EXISTS res_view;
CREATE VIEW res_view AS
SELECT r.tablename, r.is_multi_docservers, r.res_id, r.title, r.subject, r.page_count, r.identifier, r.doc_date, r.type_id,
d.description AS type_label, d.doctypes_first_level_id, dfl.doctypes_first_level_label, dfl.css_style as doctype_first_level_style,
d.doctypes_second_level_id, dsl.doctypes_second_level_label, dsl.css_style as doctype_second_level_style,
r.format, r.typist, r.creation_date, r.relation, r.docserver_id,
r.folders_system_id, r.path, r.filename, r.fingerprint, r.offset_doc, r.filesize, r.status,
r.work_batch, r.arbatch_id, r.arbox_id, r.is_paper, r.scan_date, r.scan_user,r.scan_location,r.scan_wkstation,
r.scan_batch,r.doc_language,r.description,r.source,r.initiator,r.destination,r.dest_user,r.policy_id,r.cycle_id,r.cycle_date,
r.custom_t1 AS doc_custom_t1, r.custom_t2 AS doc_custom_t2, r.custom_t3 AS doc_custom_t3,
r.custom_t4 AS doc_custom_t4, r.custom_t5 AS doc_custom_t5, r.custom_t6 AS doc_custom_t6,
r.custom_t7 AS doc_custom_t7, r.custom_t8 AS doc_custom_t8, r.custom_t9 AS doc_custom_t9, r.custom_t10 AS doc_custom_t10, r.custom_t11 AS doc_custom_t11, r.custom_t12 AS doc_custom_t12,
r.custom_t13 AS doc_custom_t13, r.custom_t14 AS doc_custom_t14, r.custom_t15 AS doc_custom_t15,
r.custom_d1 AS doc_custom_d1, r.custom_d2 AS doc_custom_d2, r.custom_d3 AS doc_custom_d3,
r.custom_d4 AS doc_custom_d4, r.custom_d5 AS doc_custom_d5, r.custom_d6 AS doc_custom_d6,
r.custom_d7 AS doc_custom_d7, r.custom_d8 AS doc_custom_d8, r.custom_d9 AS doc_custom_d9,
r.custom_d10 AS doc_custom_d10, r.custom_n1 AS doc_custom_n1, r.custom_n2 AS doc_custom_n2,
r.custom_n3 AS doc_custom_n3, r.custom_n4 AS doc_custom_n4, r.custom_n5 AS doc_custom_n5,
r.custom_f1 AS doc_custom_f1, r.custom_f2 AS doc_custom_f2, r.custom_f3 AS doc_custom_f3,
r.custom_f4 AS doc_custom_f4, r.custom_f5 AS doc_custom_f5, r.is_frozen as res_is_frozen
FROM doctypes d, doctypes_first_level dfl, doctypes_second_level dsl, res_x r
WHERE r.type_id = d.type_id
AND d.doctypes_first_level_id = dfl.doctypes_first_level_id
AND d.doctypes_second_level_id = dsl.doctypes_second_level_id;
--view for business
DROP VIEW IF EXISTS res_view_business;
CREATE VIEW res_view_business AS
SELECT r.tablename, r.is_multi_docservers, r.res_id, r.type_id,
d.description AS type_label, d.doctypes_first_level_id,
d.doctypes_second_level_id, r.format, r.typist,
r.creation_date, r.relation, r.docserver_id, r.folders_system_id,
f.folder_id, f.is_frozen as folder_is_frozen, r.path, r.filename,
r.fingerprint, r.offset_doc, r.filesize,
r.status, r.work_batch, r.arbatch_id, r.arbox_id, r.page_count, r.is_paper,
r.doc_date, r.scan_date, r.scan_user, r.scan_location, r.scan_wkstation,
r.scan_batch, r.doc_language, r.description, r.source, r.author,
r.custom_t1 AS doc_custom_t1, r.custom_t2 AS doc_custom_t2,
r.custom_t3 AS doc_custom_t3, r.custom_t4 AS doc_custom_t4,
r.custom_t5 AS doc_custom_t5, r.custom_t6 AS doc_custom_t6,
r.custom_t7 AS doc_custom_t7, r.custom_t8 AS doc_custom_t8,
r.custom_t9 AS doc_custom_t9, r.custom_t10 AS doc_custom_t10,
r.custom_t11 AS doc_custom_t11, r.custom_t12 AS doc_custom_t12,
r.custom_t13 AS doc_custom_t13, r.custom_t14 AS doc_custom_t14,
r.custom_t15 AS doc_custom_t15, r.custom_d1 AS doc_custom_d1,
r.custom_d2 AS doc_custom_d2, r.custom_d3 AS doc_custom_d3,
r.custom_d4 AS doc_custom_d4, r.custom_d5 AS doc_custom_d5,
r.custom_d6 AS doc_custom_d6, r.custom_d7 AS doc_custom_d7,
r.custom_d8 AS doc_custom_d8, r.custom_d9 AS doc_custom_d9,
r.custom_d10 AS doc_custom_d10, r.custom_n1 AS doc_custom_n1,
r.custom_n2 AS doc_custom_n2, r.custom_n3 AS doc_custom_n3,
r.custom_n4 AS doc_custom_n4, r.custom_n5 AS doc_custom_n5,
r.custom_f1 AS doc_custom_f1, r.custom_f2 AS doc_custom_f2,
r.custom_f3 AS doc_custom_f3, r.custom_f4 AS doc_custom_f4,
r.custom_f5 AS doc_custom_f5, f.foldertype_id,
f.custom_t1 AS fold_custom_t1, f.custom_t2 AS fold_custom_t2,
f.custom_t3 AS fold_custom_t3, f.custom_t4 AS fold_custom_t4,
f.custom_t5 AS fold_custom_t5, f.custom_t6 AS fold_custom_t6,
f.custom_t7 AS fold_custom_t7, f.custom_t8 AS fold_custom_t8,
f.custom_t9 AS fold_custom_t9, f.custom_t10 AS fold_custom_t10,
f.custom_t11 AS fold_custom_t11, f.custom_t12 AS fold_custom_t12,
f.custom_t13 AS fold_custom_t13, f.custom_t14 AS fold_custom_t14,
f.custom_t15 AS fold_custom_t15, f.custom_d1 AS fold_custom_d1,
f.custom_d2 AS fold_custom_d2, f.custom_d3 AS fold_custom_d3,
f.custom_d4 AS fold_custom_d4, f.custom_d5 AS fold_custom_d5,
f.custom_d6 AS fold_custom_d6, f.custom_d7 AS fold_custom_d7,
f.custom_d8 AS fold_custom_d8, f.custom_d9 AS fold_custom_d9,
f.custom_d10 AS fold_custom_d10, f.custom_n1 AS fold_custom_n1,
f.custom_n2 AS fold_custom_n2, f.custom_n3 AS fold_custom_n3,
f.custom_n4 AS fold_custom_n4, f.custom_n5 AS fold_custom_n5,
f.custom_f1 AS fold_custom_f1, f.custom_f2 AS fold_custom_f2,
f.custom_f3 AS fold_custom_f3, f.custom_f4 AS fold_custom_f4,
f.custom_f5 AS fold_custom_f5, f.is_complete AS fold_complete,
f.status AS fold_status, f.subject AS fold_subject,
f.parent_id AS fold_parent_id, f.folder_level, f.folder_name,
f.creation_date AS fold_creation_date, r.initiator, r.destination,
r.dest_user, busi.category_id, busi.contact_id, busi.currency,
busi.net_sum, busi.tax_sum, busi.total_sum,
busi.process_limit_date, busi.closing_date, busi.alarm1_date, busi.alarm2_date,
busi.flag_notif, busi.flag_alarm1, busi.flag_alarm2, r.video_user, r.video_time,
r.video_batch, r.subject, r.identifier, r.title, r.priority, en.entity_label, cont.email AS contact_email,
cont.firstname AS contact_firstname, cont.lastname AS contact_lastname,
cont.society AS contact_society, list.item_id AS dest_user_from_listinstance, list.viewed,
r.is_frozen as res_is_frozen, COALESCE(att.count_attachment, 0::bigint) AS count_attachment
FROM doctypes d, res_business r
LEFT JOIN (SELECT res_attachments.res_id_master, coll_id, count(res_attachments.res_id_master) AS count_attachment
FROM res_attachments GROUP BY res_attachments.res_id_master, coll_id) att ON (r.res_id = att.res_id_master and att.coll_id = 'business_coll')
LEFT JOIN entities en ON ((r.destination)::text = (en.entity_id)::text)
LEFT JOIN folders f ON ((r.folders_system_id = f.folders_system_id))
LEFT JOIN business_coll_ext busi ON (busi.res_id = r.res_id)
LEFT JOIN contacts cont ON (busi.contact_id = cont.contact_id)
LEFT JOIN listinstance list ON ((r.res_id = list.res_id)
AND ((list.item_mode)::text = 'dest'::text))
WHERE r.type_id = d.type_id;
-- view for letterbox
DROP VIEW IF EXISTS res_view_letterbox;
CREATE VIEW res_view_letterbox AS
SELECT r.tablename, r.is_multi_docservers, r.res_id, r.type_id,
d.description AS type_label, d.doctypes_first_level_id,
dfl.doctypes_first_level_label, dfl.css_style as doctype_first_level_style,
d.doctypes_second_level_id, dsl.doctypes_second_level_label,
dsl.css_style as doctype_second_level_style, r.format, r.typist,
r.creation_date, r.relation, r.docserver_id, r.folders_system_id,
f.folder_id, f.is_frozen as folder_is_frozen, r.path, r.filename, r.fingerprint, r.offset_doc, r.filesize,
r.status, r.work_batch, r.arbatch_id, r.arbox_id, r.page_count, r.is_paper,
r.doc_date, r.scan_date, r.scan_user, r.scan_location, r.scan_wkstation,
r.scan_batch, r.doc_language, r.description, r.source, r.author,
r.custom_t1 AS doc_custom_t1, r.custom_t2 AS doc_custom_t2,
r.custom_t3 AS doc_custom_t3, r.custom_t4 AS doc_custom_t4,
r.custom_t5 AS doc_custom_t5, r.custom_t6 AS doc_custom_t6,
r.custom_t7 AS doc_custom_t7, r.custom_t8 AS doc_custom_t8,
r.custom_t9 AS doc_custom_t9, r.custom_t10 AS doc_custom_t10,
r.custom_t11 AS doc_custom_t11, r.custom_t12 AS doc_custom_t12,
r.custom_t13 AS doc_custom_t13, r.custom_t14 AS doc_custom_t14,
r.custom_t15 AS doc_custom_t15, r.custom_d1 AS doc_custom_d1,
r.custom_d2 AS doc_custom_d2, r.custom_d3 AS doc_custom_d3,
r.custom_d4 AS doc_custom_d4, r.custom_d5 AS doc_custom_d5,
r.custom_d6 AS doc_custom_d6, r.custom_d7 AS doc_custom_d7,
r.custom_d8 AS doc_custom_d8, r.custom_d9 AS doc_custom_d9,
r.custom_d10 AS doc_custom_d10, r.custom_n1 AS doc_custom_n1,
r.custom_n2 AS doc_custom_n2, r.custom_n3 AS doc_custom_n3,
r.custom_n4 AS doc_custom_n4, r.custom_n5 AS doc_custom_n5,
r.custom_f1 AS doc_custom_f1, r.custom_f2 AS doc_custom_f2,
r.custom_f3 AS doc_custom_f3, r.custom_f4 AS doc_custom_f4,
r.custom_f5 AS doc_custom_f5, f.foldertype_id, ft.foldertype_label,
f.custom_t1 AS fold_custom_t1, f.custom_t2 AS fold_custom_t2,
f.custom_t3 AS fold_custom_t3, f.custom_t4 AS fold_custom_t4,
f.custom_t5 AS fold_custom_t5, f.custom_t6 AS fold_custom_t6,
f.custom_t7 AS fold_custom_t7, f.custom_t8 AS fold_custom_t8,
f.custom_t9 AS fold_custom_t9, f.custom_t10 AS fold_custom_t10,
f.custom_t11 AS fold_custom_t11, f.custom_t12 AS fold_custom_t12,
f.custom_t13 AS fold_custom_t13, f.custom_t14 AS fold_custom_t14,
f.custom_t15 AS fold_custom_t15, f.custom_d1 AS fold_custom_d1,
f.custom_d2 AS fold_custom_d2, f.custom_d3 AS fold_custom_d3,
f.custom_d4 AS fold_custom_d4, f.custom_d5 AS fold_custom_d5,
f.custom_d6 AS fold_custom_d6, f.custom_d7 AS fold_custom_d7,
f.custom_d8 AS fold_custom_d8, f.custom_d9 AS fold_custom_d9,
f.custom_d10 AS fold_custom_d10, f.custom_n1 AS fold_custom_n1,
f.custom_n2 AS fold_custom_n2, f.custom_n3 AS fold_custom_n3,
f.custom_n4 AS fold_custom_n4, f.custom_n5 AS fold_custom_n5,
f.custom_f1 AS fold_custom_f1, f.custom_f2 AS fold_custom_f2,
f.custom_f3 AS fold_custom_f3, f.custom_f4 AS fold_custom_f4,
f.custom_f5 AS fold_custom_f5, f.is_complete AS fold_complete,
f.status AS fold_status, f.subject AS fold_subject,
f.parent_id AS fold_parent_id, f.folder_level, f.folder_name,
f.creation_date AS fold_creation_date, r.initiator, r.destination,
r.dest_user, mlb.category_id, mlb.exp_contact_id, mlb.exp_user_id,
mlb.dest_user_id, mlb.dest_contact_id, mlb.nature_id, mlb.alt_identifier,
mlb.admission_date, mlb.answer_type_bitmask, mlb.other_answer_desc, mlb.process_limit_date, mlb.closing_date, mlb.alarm1_date, mlb.alarm2_date,
mlb.flag_notif, mlb.flag_alarm1, mlb.flag_alarm2, r.video_user, r.video_time,
r.video_batch, r.subject, r.identifier, r.title, r.priority, mlb.process_notes,
ca.case_id, ca.case_label, ca.case_description, en.entity_label,
cont.contact_id AS contact_id, cont.email AS contact_email,
cont.firstname AS contact_firstname, cont.lastname AS contact_lastname,
cont.society AS contact_society, u.lastname AS user_lastname,
u.firstname AS user_firstname, list.item_id AS dest_user_from_listinstance, list.viewed,
r.is_frozen as res_is_frozen, COALESCE(att.count_attachment, 0::bigint) AS count_attachment
FROM doctypes d, doctypes_first_level dfl, doctypes_second_level dsl,
(((((((((((ar_batch a RIGHT JOIN res_letterbox r ON ((r.arbatch_id = a.arbatch_id)))
LEFT JOIN (SELECT res_attachments.res_id_master, count(res_attachments.res_id_master) AS count_attachment
FROM res_attachments GROUP BY res_attachments.res_id_master) att ON (r.res_id = att.res_id_master))
LEFT JOIN entities en ON (((r.destination)::text = (en.entity_id)::text)))
LEFT JOIN folders f ON ((r.folders_system_id = f.folders_system_id)))
LEFT JOIN cases_res cr ON ((r.res_id = cr.res_id)))
LEFT JOIN mlb_coll_ext mlb ON ((mlb.res_id = r.res_id)))
LEFT JOIN foldertypes ft ON (((f.foldertype_id = ft.foldertype_id)
AND ((f.status)::text <> 'DEL'::text))))
LEFT JOIN cases ca ON ((cr.case_id = ca.case_id)))
LEFT JOIN contacts cont ON (((mlb.exp_contact_id = cont.contact_id)
OR (mlb.dest_contact_id = cont.contact_id))))
LEFT JOIN users u ON ((((mlb.exp_user_id)::text = (u.user_id)::text)
OR ((mlb.dest_user_id)::text = (u.user_id)::text))))
LEFT JOIN listinstance list ON (((r.res_id = list.res_id)
AND ((list.item_mode)::text = 'dest'::text))))
WHERE (((r.type_id = d.type_id) AND
(d.doctypes_first_level_id = dfl.doctypes_first_level_id))
AND (d.doctypes_second_level_id = dsl.doctypes_second_level_id));
--view for postindexing
DROP VIEW IF EXISTS view_postindexing;
CREATE VIEW view_postindexing AS
SELECT res_view_letterbox.video_user, (users.firstname::text || ' '::text) || users.lastname::text AS user_name, res_view_letterbox.video_batch, res_view_letterbox.video_time, count(res_view_letterbox.res_id) AS count_documents, res_view_letterbox.folders_system_id, (folders.folder_id::text || ' / '::text) || folders.folder_name::text AS folder_full_label, folders.video_status
FROM res_view_letterbox
LEFT JOIN users ON res_view_letterbox.video_user::text = users.user_id::text
LEFT JOIN folders ON folders.folders_system_id = res_view_letterbox.folders_system_id
WHERE res_view_letterbox.video_batch IS NOT NULL
GROUP BY res_view_letterbox.video_user, (users.firstname::text || ' '::text) || users.lastname::text, res_view_letterbox.video_batch, res_view_letterbox.video_time, res_view_letterbox.folders_system_id, (folders.folder_id::text || ' / '::text) || folders.folder_name::text, folders.video_status;
--view for APA
DROP VIEW IF EXISTS res_view_apa;
CREATE VIEW res_view_apa AS
select * from res_apa;
--views for autofoldering
-- Resource view used to fill af_target, we exclude from res_x the branches already in af_target table
DROP VIEW IF EXISTS af_view_year_view;
CREATE VIEW af_view_year_view AS
SELECT r.custom_t3 AS level1, date_part('year', r.doc_date) AS level2, r.custom_t4 AS level3,
r.res_id, r.creation_date, r.status -- for where clause
FROM res_x r
WHERE NOT (EXISTS (SELECT t.level1, t.level2, t.level3
FROM af_view_year_target t
WHERE r.custom_t3::text = t.level1::text AND cast(date_part('year', r.doc_date) as character) = t.level2 AND r.custom_t4 = t.level3));
DROP VIEW IF EXISTS af_view_customer_view;
CREATE VIEW af_view_customer_view AS
SELECT substring(r.custom_t4, 1, 1) AS level1, r.custom_t4 AS level2, date_part('year', r.doc_date) AS level3,
r.res_id, r.creation_date, r.status -- for where clause
FROM res_x r
WHERE status <> 'DEL' and date_part('year', doc_date) is not null
AND NOT (EXISTS (SELECT t.level1, t.level2, t.level3
FROM af_view_customer_target t
WHERE substring(r.custom_t4, 1, 1)::text = t.level1::text AND r.custom_t4::text = t.level2::text
AND cast(date_part('year', r.doc_date) as character) = t.level3)) ;
-- View used to display trees
DROP VIEW IF EXISTS af_view_year_target_view;
CREATE VIEW af_view_year_target_view ASSELECT af.level1, af.level1_id, af.level1 as level1_label, af.level2, af.level2_id, af.level2 as level2_label, af.level3, af.level3_id, af.level3 as level3_label
FROM af_view_year_target af;
DROP VIEW IF EXISTS af_view_customer_target_view;
CREATE VIEW af_view_customer_target_view AS
SELECT af.level1, af.level1_id, af.level1 as level1_label, af.level2, af.level2_id, af.level2 as level2_label, af.level3, af.level3_id, af.level3 as level3_label
FROM af_view_customer_target af ;
-- View folders
DROP VIEW IF EXISTS view_folders;
CREATE VIEW view_folders AS
SELECT folders.folders_system_id, folders.folder_id, folders.foldertype_id, foldertypes.foldertype_label, (folders.folder_id::text || ':'::text) || folders.folder_name::text AS folder_full_label, folders.parent_id, folders.folder_name, folders.subject, folders.description, folders.author, folders.typist, folders.status, folders.folder_level, folders.creation_date, folders.destination, folders.dest_user, folders.folder_out_id, folders.custom_t1, folders.custom_n1, folders.custom_f1, folders.custom_d1, folders.custom_t2, folders.custom_n2, folders.custom_f2, folders.custom_d2, folders.custom_t3, folders.custom_n3, folders.custom_f3, folders.custom_d3, folders.custom_t4, folders.custom_n4, folders.custom_f4, folders.custom_d4, folders.custom_t5, folders.custom_n5, folders.custom_f5, folders.custom_d5, folders.custom_t6, folders.custom_d6, folders.custom_t7, folders.custom_d7, folders.custom_t8, folders.custom_d8, folders.custom_t9, folders.custom_d9, folders.custom_t10, folders.custom_d10, folders.custom_t11, folders.custom_d11, folders.custom_t12, folders.custom_d12, folders.custom_t13, folders.custom_d13, folders.custom_t14, folders.custom_d14, folders.custom_t15, folders.is_complete, folders.is_folder_out, folders.last_modified_date, folders.video_status, COALESCE(r.count_document, 0::bigint) AS count_document
FROM foldertypes, folders
LEFT JOIN (SELECT res_letterbox.folders_system_id, count(res_letterbox.folders_system_id) AS count_document
FROM res_letterbox
GROUP BY res_letterbox.folders_system_id) r ON r.folders_system_id = folders.folders_system_id
WHERE folders.foldertype_id = foldertypes.foldertype_id;