structure.sql

  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);

DROP SEQUENCE IF EXISTS contact_communication_id_seq CASCADE;
CREATE SEQUENCE contact_communication_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

DROP TABLE IF EXISTS contact_communication;
CREATE TABLE contact_communication
(
  id bigint NOT NULL DEFAULT nextval('contact_communication_id_seq'::regclass),
  contact_id bigint NOT NULL,
  type character varying(255) NOT NULL,
  value character varying(255) NOT NULL,
  CONSTRAINT contact_communication_pkey PRIMARY KEY (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);

CREATE TABLE tags
(
  id serial NOT NULL,
  label character varying(128) NOT NULL,
  entity_id_owner character varying(32),
  CONSTRAINT tags_id_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE tag_res
(
  res_id bigint NOT NULL,
  tag_id bigint NOT NULL,
  CONSTRAINT tag_res_pkey PRIMARY KEY (res_id,tag_id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE tags_entities
(
  tag_id bigint,
  entity_id character varying(32),
  CONSTRAINT tags_entities_pkey PRIMARY KEY (tag_id,entity_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),
  subject text,
  type_id bigint NOT NULL,
  format character varying(50),
  typist INTEGER NOT NULL,
  creation_date timestamp without time zone NOT NULL,
  modification_date timestamp without time zone DEFAULT NOW(),
  doc_date timestamp without time zone,
  docserver_id character varying(32),
  path character varying(255) DEFAULT NULL::character varying,
  filename character varying(255) DEFAULT NULL::character varying,
  fingerprint character varying(255) DEFAULT NULL::character varying,
  filesize bigint,
  status character varying(10),
  destination character varying(50) DEFAULT NULL::character varying,
  work_batch bigint,
  origin character varying(50) DEFAULT NULL::character varying,
  priority character varying(16),
  policy_id character varying(32) DEFAULT NULL::character varying,
  cycle_id character varying(32) DEFAULT NULL::character varying,
  initiator character varying(50) DEFAULT NULL::character varying,
  dest_user character varying(128) DEFAULT NULL::character varying,
  locker_user_id INTEGER DEFAULT NULL,
  locker_time timestamp without time zone,
  confidentiality character(1),
  fulltext_result character varying(10) DEFAULT NULL::character varying,
  external_id jsonb DEFAULT '{}',
  external_link character varying(255) DEFAULT NULL::character varying,
  departure_date timestamp without time zone,
  opinion_limit_date timestamp without time zone default NULL,
  department_number_id text,
  barcode text,
  external_signatory_book_id integer,
  category_id character varying(32)  NOT NULL,
  exp_contact_id integer,
  exp_user_id character varying(128),
  dest_contact_id integer,
  dest_user_id character varying(128),
  alt_identifier character varying(255),
  admission_date timestamp without time zone,
  process_limit_date timestamp without time zone,
  closing_date timestamp without time zone,
  alarm1_date timestamp without time zone,
  alarm2_date timestamp without time zone,
  flag_alarm1 char(1) default 'N'::character varying,
  flag_alarm2 char(1) default 'N'::character varying,
  is_multicontacts char(1),
  address_id bigint,
  model_id integer NOT NULL,
  CONSTRAINT res_letterbox_pkey PRIMARY KEY  (res_id)
)
WITH (OIDS=FALSE);

CREATE TABLE adr_letterbox
(
  id serial NOT NULL,
  res_id bigint NOT NULL,
  type character varying(32) NOT NULL,
  docserver_id character varying(32) NOT NULL,
  path character varying(255) NOT NULL,
  filename character varying(255) NOT NULL,
  fingerprint character varying(255) DEFAULT NULL::character varying,
  CONSTRAINT adr_letterbox_pkey PRIMARY KEY (id),
  CONSTRAINT adr_letterbox_unique_key UNIQUE (res_id, type)
)
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 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 user_signatures
(
  id serial,
  user_serial_id integer NOT NULL,
  signature_label character varying(255) DEFAULT NULL::character varying,
  signature_path character varying(255) DEFAULT NULL::character varying,
  signature_file_name character varying(255) DEFAULT NULL::character varying,
  fingerprint character varying(255) DEFAULT NULL::character varying,
  CONSTRAINT user_signatures_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE priorities
(
  id character varying(16) NOT NULL,
  label character varying(128) NOT NULL,
  color character varying(128) NOT NULL,
  delays integer NOT NULL,
  "order" integer,
  CONSTRAINT priorities_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

DROP TABLE IF EXISTS actions_categories;
CREATE TABLE actions_categories
(
  action_id bigint NOT NULL,
  category_id character varying(255) NOT NULL,
  CONSTRAINT actions_categories_pkey PRIMARY KEY (action_id,category_id)
);

DROP SEQUENCE IF EXISTS listinstance_history_id_seq;
CREATE SEQUENCE listinstance_history_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

DROP TABLE IF EXISTS listinstance_history;
CREATE TABLE listinstance_history
(
listinstance_history_id bigint NOT NULL DEFAULT nextval('listinstance_history_id_seq'::regclass),
coll_id character varying(50) NOT NULL,
res_id bigint NOT NULL,
user_id INTEGER NOT NULL,
updated_date timestamp without time zone NOT NULL,
CONSTRAINT listinstance_history_pkey PRIMARY KEY (listinstance_history_id)
)
WITH ( OIDS=FALSE );

DROP SEQUENCE IF EXISTS listinstance_history_details_id_seq;
CREATE SEQUENCE listinstance_history_details_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

DROP TABLE IF EXISTS listinstance_history_details;
CREATE TABLE listinstance_history_details
(
listinstance_history_details_id bigint NOT NULL DEFAULT nextval('listinstance_history_details_id_seq'::regclass),
listinstance_history_id bigint NOT NULL,
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,
visible character varying(1) NOT NULL DEFAULT 'Y'::bpchar,
viewed bigint,
difflist_type character varying(50),
process_date timestamp without time zone,
process_comment character varying(255),
CONSTRAINT listinstance_history_details_pkey PRIMARY KEY (listinstance_history_details_id)
) WITH ( OIDS=FALSE );

/* SHIPPING TEMPLATES */
DROP TABLE IF EXISTS shipping_templates;
CREATE TABLE shipping_templates
(
id serial NOT NULL,
label character varying(64) NOT NULL,
description character varying(255) NOT NULL,
options json DEFAULT '{}',
fee json DEFAULT '{}',
entities jsonb DEFAULT '{}',
account json DEFAULT '{}',
CONSTRAINT shipping_templates_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE shippings
(
id serial NOT NULL,
user_id INTEGER NOT NULL,
attachment_id INTEGER NOT NULL,
options json DEFAULT '{}',
fee FLOAT NOT NULL,
recipient_entity_id INTEGER NOT NULL,
account_id character varying(64) NOT NULL,
creation_date timestamp without time zone NOT NULL,
CONSTRAINT shippings_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

--VIEWS
-- view for letterbox
CREATE OR REPLACE VIEW res_view_letterbox AS
SELECT r.res_id,
       r.type_id,
       r.policy_id,
       r.cycle_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.modification_date,
       r.docserver_id,
       r.path,
       r.filename,
       r.fingerprint,
       r.filesize,
       r.status,
       r.work_batch,
       r.doc_date,
       r.external_id,
       r.external_link,
       r.departure_date,
       r.opinion_limit_date,
       r.department_number_id,
       r.barcode,
       r.external_signatory_book_id,
       r.initiator,
       r.destination,
       r.dest_user,
       r.confidentiality,
       r.category_id,
       r.exp_contact_id,
       r.exp_user_id,
       r.dest_user_id,
       r.dest_contact_id,
       r.address_id,
       r.alt_identifier,
       r.admission_date,
       r.process_limit_date,
       r.closing_date,
       r.alarm1_date,
       r.alarm2_date,
       r.flag_alarm1,
       r.flag_alarm2,
       r.is_multicontacts,
       r.subject,
       r.priority,
       r.locker_user_id,
       r.locker_time,
       en.entity_label,
       en.entity_type AS entitytype,
       cont.contact_id,
       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
FROM doctypes d,
     doctypes_first_level dfl,
     doctypes_second_level dsl,
     res_letterbox r
         LEFT JOIN entities en ON r.destination::text = en.entity_id::text
         LEFT JOIN contacts_v2 cont ON r.exp_contact_id = cont.contact_id OR r.dest_contact_id = cont.contact_id
         LEFT JOIN users u ON r.exp_user_id::text = u.user_id::text OR r.dest_user_id::text = u.user_id::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 contacts_v2
DROP VIEW IF EXISTS view_contacts;
CREATE OR REPLACE VIEW view_contacts AS 
 SELECT c.contact_id, c.contact_type, c.is_corporate_person, c.society, c.society_short, c.firstname AS contact_firstname
, c.lastname AS contact_lastname, c.title AS contact_title, c.function AS contact_function, c.other_data AS contact_other_data
, c.user_id AS contact_user_id, c.entity_id AS contact_entity_id, c.creation_date, c.update_date, c.enabled AS contact_enabled, ca.id AS ca_id
, ca.contact_purpose_id, ca.departement, ca.firstname, ca.lastname, ca.title, ca.function, ca.occupancy
, ca.address_num, ca.address_street, ca.address_complement, ca.address_town, ca.address_postal_code, ca.address_country
, ca.phone, ca.email, ca.website, ca.salutation_header, ca.salutation_footer, ca.other_data, ca.user_id, ca.entity_id, ca.is_private, ca.enabled, ca.external_id
, cp.label as contact_purpose_label, ct.label as contact_type_label
   FROM contacts_v2 c
   RIGHT JOIN contact_addresses ca ON c.contact_id = ca.contact_id
   LEFT JOIN contact_purposes cp ON ca.contact_purpose_id = cp.id
   LEFT JOIN contact_types ct ON c.contact_type = ct.id;

CREATE FUNCTION order_alphanum(text) RETURNS text AS $$
  SELECT regexp_replace(regexp_replace(regexp_replace(regexp_replace($1,
    E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'),
      E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'),
        E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'),
          E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g');
$$ LANGUAGE SQL;


CREATE TABLE message_exchange
(
  message_id text NOT NULL,
  schema text,
  type text NOT NULL,
  status text NOT NULL,
  
  date timestamp NOT NULL,
  reference text NOT NULL,
  
  account_id text,
  sender_org_identifier text NOT NULL,
  sender_org_name text,
  recipient_org_identifier text NOT NULL,
  recipient_org_name text,

  archival_agreement_reference text,
  reply_code text,
  operation_date timestamp,
  reception_date timestamp,
  
  related_reference text,
  request_reference text,
  reply_reference text,
  derogation boolean,
  
  data_object_count integer,
  size numeric,
  
  data text,
  
  active boolean,
  archived boolean,
  
  res_id_master numeric default NULL,

  docserver_id character varying(32) DEFAULT NULL,
  path character varying(255) DEFAULT NULL,
  filename character varying(255) DEFAULT NULL,
  fingerprint character varying(255) DEFAULT NULL,
  filesize bigint,
  file_path text default NULL,

  PRIMARY KEY ("message_id")
)
WITH (
  OIDS=FALSE
);

CREATE TABLE unit_identifier
(
  message_id text NOT NULL,
  tablename text NOT NULL,
  res_id text NOT NULL,
  disposition text default NULL
);

DROP TABLE IF EXISTS users_baskets_preferences;
CREATE TABLE users_baskets_preferences
(
  id serial NOT NULL,
  user_serial_id integer NOT NULL,
  group_serial_id integer NOT NULL,
  basket_id character varying(32) NOT NULL,
  display boolean NOT NULL,
  color character varying(16),
  CONSTRAINT users_baskets_preferences_pkey PRIMARY KEY (id),
  CONSTRAINT users_baskets_preferences_key UNIQUE (user_serial_id, group_serial_id, basket_id)
)
WITH (OIDS=FALSE);


-- convert working table
DROP TABLE IF EXISTS convert_stack;
CREATE TABLE convert_stack
(
  coll_id character varying(32) NOT NULL,
  res_id bigint NOT NULL,
  convert_format character varying(32) NOT NULL DEFAULT 'pdf'::character varying,
  cnt_retry integer,
  status character(1) NOT NULL,
  work_batch bigint,
  regex character varying(32),
  CONSTRAINT convert_stack_pkey PRIMARY KEY (coll_id, res_id, convert_format)
)
WITH (OIDS=FALSE);

CREATE TABLE password_rules
(
  id serial,
  label character varying(64) NOT NULL,
  "value" integer NOT NULL,
  enabled boolean DEFAULT FALSE NOT NULL,
  CONSTRAINT password_rules_pkey PRIMARY KEY (id),
  CONSTRAINT password_rules_label_key UNIQUE (label)
)
WITH (OIDS=FALSE);

CREATE TABLE password_history
(
  id serial,
  user_serial_id INTEGER NOT NULL,
  password character varying(255) NOT NULL,
  CONSTRAINT password_history_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE contacts_filling
(
  id serial NOT NULL,
  enable boolean NOT NULL,
  rating_columns text NOT NULL,
  first_threshold int NOT NULL,
  second_threshold int NOT NULL,
  CONSTRAINT contacts_filling_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

/* Sender/Recipient */
DROP TABLE IF EXISTS resource_contacts;
CREATE TABLE resource_contacts
(
  id serial NOT NULL,
  res_id int NOT NULL,
  item_id int NOT NULL,
  type character varying(32) NOT NULL,
  mode character varying(32) NOT NULL,
  CONSTRAINT resource_contacts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE configurations
(
id serial NOT NULL,
service character varying(64) NOT NULL,
value json DEFAULT '{}' NOT NULL,
CONSTRAINT configuration_pkey PRIMARY KEY (id),
CONSTRAINT configuration_unique_key UNIQUE (service)
)
WITH (OIDS=FALSE);

CREATE TABLE emails
(
id serial NOT NULL,
user_id INTEGER NOT NULL,
sender json DEFAULT '{}' NOT NULL,
recipients json DEFAULT '[]' NOT NULL,
cc json DEFAULT '[]' NOT NULL,
cci json DEFAULT '[]' NOT NULL,
object character varying(256),
body text,
document json,
is_html boolean NOT NULL DEFAULT TRUE,
status character varying(16) NOT NULL,
message_exchange_id text,
creation_date timestamp without time zone NOT NULL,
send_date timestamp without time zone,
CONSTRAINT emails_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE exports_templates
(
id serial NOT NULL,
user_id INTEGER NOT NULL,
delimiter character varying(3),
format character varying(3) NOT NULL,
data json DEFAULT '[]' NOT NULL,
CONSTRAINT exports_templates_pkey PRIMARY KEY (id),
CONSTRAINT exports_templates_unique_key UNIQUE (user_id, format)
)
WITH (OIDS=FALSE);

CREATE TABLE acknowledgement_receipts
(
id serial NOT NULL,
res_id INTEGER NOT NULL,
type CHARACTER VARYING(16) NOT NULL,
format CHARACTER VARYING(8) NOT NULL,
user_id INTEGER NOT NULL,
contact_address_id INTEGER NOT NULL,
creation_date timestamp without time zone NOT NULL,
send_date timestamp without time zone,
docserver_id CHARACTER VARYING(128) NOT NULL,
path CHARACTER VARYING(256) NOT NULL,
filename CHARACTER VARYING(256) NOT NULL,
fingerprint CHARACTER VARYING(256) NOT NULL,
CONSTRAINT acknowledgment_receipts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE custom_fields
(
    id serial NOT NULL,
    label character varying(256) NOT NULL,
    type character varying(256) NOT NULL,
    values jsonb,
    CONSTRAINT custom_fields_pkey PRIMARY KEY (id),
    CONSTRAINT custom_fields_unique_key UNIQUE (label)
)
WITH (OIDS=FALSE);

CREATE TABLE resources_custom_fields
(
    id serial NOT NULL,
    res_id INTEGER NOT NULL,
    custom_field_id INTEGER NOT NULL,
    value jsonb NOT NULL,
    CONSTRAINT resources_custom_fields_pkey PRIMARY KEY (id),
    CONSTRAINT resources_custom_fields_unique_key UNIQUE (res_id, custom_field_id)
)
WITH (OIDS=FALSE);

CREATE TABLE indexing_models
(
    id SERIAL NOT NULL,
    label character varying(256) NOT NULL,
    category character varying(256) NOT NULL,
    "default" BOOLEAN NOT NULL,
    owner INTEGER NOT NULL,
    private BOOLEAN NOT NULL,
    master INTEGER DEFAULT NULL,
    enabled BOOLEAN DEFAULT TRUE NOT NULL,
    CONSTRAINT indexing_models_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE indexing_models_fields
(
    id SERIAL NOT NULL,
    model_id INTEGER NOT NULL,
    identifier text NOT NULL,
    mandatory BOOLEAN NOT NULL,
    default_value json,
    unit text NOT NULL,
    CONSTRAINT indexing_models_fields_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);