structure.sql 43.7 KB
Newer Older
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
1
-- core/sql/structure/core.postgresql.sql
SNA's avatar
SNA committed
2

3
4
5
6
7
8
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

9
10
--DROP PROCEDURAL LANGUAGE IF EXISTS plpgsql CASCADE;
--CREATE PROCEDURAL LANGUAGE plpgsql;
11
12
13
14
15

SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

16
17
CREATE EXTENSION unaccent;

18
19
CREATE TABLE actions
(
20
  id serial NOT NULL,
21
22
23
24
25
  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,
  action_page character varying(255),
26
  component CHARACTER VARYING (128),
27
  history character(1) NOT NULL DEFAULT 'N'::bpchar,
28
  parameters jsonb NOT NULL DEFAULT '{}',
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
  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,
  fingerprint_mode character varying(32) DEFAULT NULL::character varying,
  CONSTRAINT docserver_types_pkey PRIMARY KEY (docserver_type_id)
)
WITH (OIDS=FALSE);

CREATE TABLE docservers
(
Damien's avatar
Damien committed
46
  id serial,
47
48
49
  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,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
50
  is_readonly character(1) NOT NULL DEFAULT 'N'::bpchar,
51
52
53
54
55
  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,
  creation_date timestamp without time zone NOT NULL,
  coll_id character varying(32) NOT NULL DEFAULT 'coll_1'::character varying,
Damien's avatar
Damien committed
56
57
  CONSTRAINT docservers_pkey PRIMARY KEY (docserver_id),
  CONSTRAINT docservers_id_key UNIQUE (id)
58
59
60
61
62
63
64
)
WITH (OIDS=FALSE);

CREATE SEQUENCE doctypes_type_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
Giovannoni Laurent's avatar
Giovannoni Laurent committed
65
  START 500
66
67
68
69
70
71
72
73
74
75
  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,
76
77
  retention_final_disposition character varying(255) DEFAULT NULL,
  retention_rule character varying(15) DEFAULT NULL,
78
  action_current_use character varying(255) DEFAULT NULL,
79
  duration_current_use integer,
80
81
82
  process_delay INTEGER NOT NULL,
  delay1 INTEGER NOT NULL,
  delay2 INTEGER NOT NULL,
83
  process_mode CHARACTER VARYING(256) NOT NULL,
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
  CONSTRAINT doctypes_pkey PRIMARY KEY (type_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,
101
  user_id INTEGER,
102
103
104
105
  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,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
106
  event_id character varying(50),
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
  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
(
133
  id character varying(255) NOT NULL,
Cyril Vazquez's avatar
Cyril Vazquez committed
134
  description TEXT,
135
  param_value_string TEXT DEFAULT NULL::character varying,
136
137
138
139
140
141
142
143
144
145
  param_value_int integer,
  param_value_date timestamp without time zone,
  CONSTRAINT parameters_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE SEQUENCE security_security_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
146
  START 600
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
  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,
  CONSTRAINT security_pkey PRIMARY KEY (security_id)
)
WITH (OIDS=FALSE);

CREATE TABLE status
(
162
  identifier serial,
163
164
165
166
167
168
169
170
171
172
173
  id character varying(10) NOT NULL,
  label_status character varying(50) NOT NULL,
  is_system character(1) NOT NULL DEFAULT 'Y'::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);

174
175
176
177
178
179
180
181
CREATE TABLE status_images
(
  id serial,
  image_name character varying(128) NOT NULL,
  CONSTRAINT status_images_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

182
183
CREATE TABLE usergroup_content
(
184
185
186
  user_id INTEGER NOT NULL,
  group_id INTEGER NOT NULL,
  "role" character varying(255),
187
188
189
190
191
192
  CONSTRAINT usergroup_content_pkey PRIMARY KEY (user_id, group_id)
)
WITH (OIDS=FALSE);

CREATE TABLE usergroups
(
Damien's avatar
Damien committed
193
  id serial NOT NULL,
194
  group_id character varying(32) NOT NULL,
Damien's avatar
Damien committed
195
196
197
  group_desc character varying(255),
  can_index boolean NOT NULL DEFAULT FALSE,
  indexation_parameters jsonb NOT NULL DEFAULT '{"actions" : [], "entities" : [], "keywords" : []}',
Damien's avatar
Damien committed
198
199
  CONSTRAINT usergroups_pkey PRIMARY KEY (group_id),
  CONSTRAINT usergroups_id_key UNIQUE (id)
200
201
202
203
204
205
206
)
WITH (OIDS=FALSE);

CREATE TABLE usergroups_services
(
  group_id character varying NOT NULL,
  service_id character varying NOT NULL,
207
  parameters jsonb,
208
209
210
211
  CONSTRAINT usergroups_services_pkey PRIMARY KEY (group_id, service_id)
)
WITH (OIDS=FALSE);

212
213
CREATE TYPE users_modes AS ENUM ('standard', 'rest', 'root_visible', 'root_invisible');

214
215
CREATE TABLE users
(
Damien's avatar
Damien committed
216
  id serial NOT NULL,
217
  user_id character varying(128) NOT NULL,
218
219
220
  "password" character varying(255) DEFAULT NULL::character varying,
  firstname character varying(255) DEFAULT NULL::character varying,
  lastname character varying(255) DEFAULT NULL::character varying,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
221
  phone character varying(32) DEFAULT NULL::character varying,
222
  mail character varying(255) DEFAULT NULL::character varying,
223
  initials character varying(32) DEFAULT NULL::character varying,
224
  preferences jsonb NOT NULL DEFAULT '{"documentEdition" : "java"}',
225
  status character varying(10) NOT NULL DEFAULT 'OK'::character varying,
226
  password_modification_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
227
  mode users_modes NOT NULL DEFAULT 'standard',
228
  refresh_token jsonb NOT NULL DEFAULT '[]',
229
  reset_token text,
Damien's avatar
Damien committed
230
231
  failed_authentication INTEGER DEFAULT 0,
  locked_until TIMESTAMP without time zone,
232
233
  authorized_api jsonb NOT NULL DEFAULT '[]',
  external_id jsonb DEFAULT '{}',
234
  feature_tour jsonb NOT NULL DEFAULT '[]',
Damien's avatar
Damien committed
235
236
  CONSTRAINT users_pkey PRIMARY KEY (user_id),
  CONSTRAINT users_id_key UNIQUE (id)
237
238
)
WITH (OIDS=FALSE);
239
240


Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
241
-- modules/attachments/sql/structure/attachments.postgresql.sql
242

243
244
245
246
247
248
249
250
251
252
253
254
255

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,
  format character varying(50) NOT NULL,
Damien's avatar
Damien committed
256
  typist INTEGER,
257
  creation_date timestamp without time zone NOT NULL,
258
  modification_date timestamp without time zone DEFAULT NOW(),
259
  modified_by INTEGER,
260
261
262
263
264
265
266
267
268
  identifier character varying(255) DEFAULT NULL::character varying,
  relation bigint,
  docserver_id character varying(32) NOT NULL,
  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) DEFAULT NULL::character varying,
  validation_date timestamp without time zone,
269
  effective_date timestamp without time zone,
270
  work_batch bigint,
271
272
  origin character varying(50) DEFAULT NULL::character varying,
  res_id_master bigint,
Damien's avatar
Damien committed
273
  origin_id INTEGER,
274
  attachment_type character varying(255) DEFAULT NULL::character varying,
275
276
  recipient_id integer,
  recipient_type character varying(256),
Damien's avatar
Damien committed
277
  in_signature_book boolean DEFAULT FALSE,
278
  in_send_attach boolean DEFAULT FALSE,
Alex ORLUC's avatar
Alex ORLUC committed
279
  signatory_user_serial_id int,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
280
  fulltext_result character varying(10) DEFAULT NULL::character varying,
Damien's avatar
Damien committed
281
  external_id jsonb DEFAULT '{}',
282
  original_filename character varying(255) DEFAULT NULL::character varying,
283
284
285
  CONSTRAINT res_attachments_pkey PRIMARY KEY (res_id)
)
WITH (OIDS=FALSE);
286

Giovannoni Laurent's avatar
Giovannoni Laurent committed
287
288
CREATE TABLE adr_attachments
(
289
  id serial NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
290
  res_id bigint NOT NULL,
291
  type character varying(32) NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
292
  docserver_id character varying(32) NOT NULL,
293
294
  path character varying(255) NOT NULL,
  filename character varying(255) NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
295
  fingerprint character varying(255) DEFAULT NULL::character varying,
296
297
  CONSTRAINT adr_attachments_pkey PRIMARY KEY (id),
  CONSTRAINT adr_attachments_unique_key UNIQUE (res_id, type)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
298
299
300
)
WITH (OIDS=FALSE);

301

Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
302
-- modules/basket/sql/structure/basket.postgresql.sql
303

304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
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
(
319
  id serial NOT NULL,
320
321
322
323
324
  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,
325
  is_visible character(1) NOT NULL DEFAULT 'Y'::bpchar,
326
  enabled character(1) NOT NULL DEFAULT 'Y'::bpchar,
Florian Azizian's avatar
Florian Azizian committed
327
  basket_order integer,
Damien's avatar
Damien committed
328
  color character varying(16),
329
  basket_res_order character varying(255) NOT NULL DEFAULT 'res_id desc',
330
  flag_notif character varying(1),
331
332
  CONSTRAINT baskets_pkey PRIMARY KEY (coll_id, basket_id),
  CONSTRAINT baskets_unique_key UNIQUE (id)
333
334
335
)
WITH (OIDS=FALSE);

Giovannoni Laurent's avatar
Giovannoni Laurent committed
336
337
338
CREATE TABLE basket_persistent_mode
(
  res_id bigint,
339
  user_id INTEGER not null,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
340
341
342
343
344
345
  is_persistent character varying(1)
)
WITH (
  OIDS=FALSE
);

346
347
348
CREATE TABLE res_mark_as_read
(
  res_id bigint,
349
  user_id INTEGER NOT NULL,
350
351
352
353
354
355
  basket_id character varying(32)
)
WITH (
  OIDS=FALSE
);

356
357
CREATE TABLE groupbasket
(
Damien's avatar
Damien committed
358
  id serial NOT NULL,
359
360
  group_id character varying(32) NOT NULL,
  basket_id character varying(32) NOT NULL,
Damien's avatar
Damien committed
361
  list_display json DEFAULT '[]',
362
  list_event character varying(255) DEFAULT 'documentDetails' NOT NULL,
363
  list_event_data jsonb,
Damien's avatar
Damien committed
364
365
  CONSTRAINT groupbasket_pkey PRIMARY KEY (group_id, basket_id),
  CONSTRAINT groupbasket_unique_key UNIQUE (id)
366
367
368
)
WITH (OIDS=FALSE);

Damien's avatar
Damien committed
369
CREATE TABLE redirected_baskets
370
(
Damien's avatar
Damien committed
371
372
373
374
375
376
377
id serial NOT NULL,
actual_user_id INTEGER NOT NULL,
owner_user_id INTEGER NOT NULL,
basket_id character varying(255) NOT NULL,
group_id INTEGER NOT NULL,
CONSTRAINT redirected_baskets_pkey PRIMARY KEY (id),
CONSTRAINT redirected_baskets_unique_key UNIQUE (owner_user_id, basket_id, group_id)
378
379
)
WITH (OIDS=FALSE);
380

Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
381
-- modules/entities/sql/structure/entities.postgresql.sql
382

383
384
385

CREATE TABLE entities
(
Damien's avatar
Damien committed
386
  id serial NOT NULL,
387
388
389
  entity_id character varying(32) NOT NULL,
  entity_label character varying(255),
  short_label character varying(50),
390
  entity_full_name text,
391
392
393
394
395
396
397
398
399
400
401
  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),
402
  ldap_id character varying(255),
403
  producer_service character varying(255),
Damien's avatar
Damien committed
404
  folder_import character varying(64),
405
  external_id jsonb DEFAULT '{}',
Damien's avatar
Damien committed
406
407
  CONSTRAINT entities_pkey PRIMARY KEY (entity_id),
  CONSTRAINT entities_folder_import_unique_key UNIQUE (folder_import)
408
409
410
)
WITH (OIDS=FALSE);

Cyril Vazquez's avatar
Cyril Vazquez committed
411
412
413
414
415
416
417
418

CREATE SEQUENCE listinstance_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

419
420
CREATE TABLE listinstance
(
Cyril Vazquez's avatar
Cyril Vazquez committed
421
  listinstance_id BIGINT NOT NULL DEFAULT nextval('listinstance_id_seq'::regclass),
422
423
  res_id bigint NOT NULL,
  "sequence" bigint NOT NULL,
424
  item_id INTEGER,
425
426
  item_type character varying(255) NOT NULL,
  item_mode character varying(50) NOT NULL,
427
  added_by_user INTEGER,
Cyril Vazquez's avatar
Cyril Vazquez committed
428
  viewed bigint,
Cyril Vazquez's avatar
Cyril Vazquez committed
429
  difflist_type character varying(50),
430
  process_date timestamp without time zone,
431
  process_comment character varying(255),
Damien's avatar
Damien committed
432
  signatory boolean default false,
433
  requested_signature boolean default false,
434
  delegate INTEGER,
Cyril Vazquez's avatar
Cyril Vazquez committed
435
  CONSTRAINT listinstance_pkey PRIMARY KEY (listinstance_id)
436
437
438
)
WITH (OIDS=FALSE);

439
CREATE TABLE difflist_types 
Cyril Vazquez's avatar
Cyril Vazquez committed
440
(
441
442
443
444
445
446
  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)
Cyril Vazquez's avatar
Cyril Vazquez committed
447
448
449
450
451
)
WITH (
    OIDS=FALSE
);

452
453
CREATE TABLE users_entities
(
454
  user_id INTEGER NOT NULL,
455
456
457
458
459
460
461
462
463
464
465
  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
466
  START 600
467
468
469
470
471
472
473
474
475
476
477
478
479
480
  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);
481

Damien's avatar
Damien committed
482
CREATE TABLE users_email_signatures
483
(
484
485
  id serial NOT NULL,
  user_id INTEGER NOT NULL,
486
487
488
489
  html_body text NOT NULL,
  title character varying NOT NULL,
  CONSTRAINT email_signatures_pkey PRIMARY KEY (id)
)
490
WITH (OIDS=FALSE);
491

492
/* FOLDERS */
493
494
CREATE TABLE folders
(
495
496
497
498
499
  id serial NOT NULL,
  label character varying(255) NOT NULL,
  public boolean NOT NULL,   
  user_id INTEGER NOT NULL,
  parent_id INTEGER,
Florian Azizian's avatar
Florian Azizian committed
500
  level INTEGER NOT NULL,
501
  CONSTRAINT folders_pkey PRIMARY KEY (id)
502
503
504
)
WITH (OIDS=FALSE);

505
506
507
508
509
510
511
512
513
514
515
516
517
518
CREATE TABLE resources_folders
(
  id serial NOT NULL,
  folder_id INTEGER NOT NULL,
  res_id INTEGER NOT NULL,
  CONSTRAINT resources_folders_pkey PRIMARY KEY (id),
  CONSTRAINT resources_folders_unique_key UNIQUE (folder_id, res_id)
)
WITH (OIDS=FALSE);

CREATE TABLE entities_folders
(
  id serial NOT NULL,
  folder_id INTEGER NOT NULL,
519
  entity_id INTEGER,
520
  edition boolean NOT NULL,
521
  keyword character varying(255),
522
  CONSTRAINT entities_folders_pkey PRIMARY KEY (id),
523
  CONSTRAINT entities_folders_unique_key UNIQUE (folder_id, entity_id, keyword)
524
525
)
WITH (OIDS=FALSE);
526

527
528
529
530
531
532
533
534
535
536
CREATE TABLE users_pinned_folders
(
  id serial NOT NULL,
  folder_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  CONSTRAINT users_pinned_folders_pkey PRIMARY KEY (id),
  CONSTRAINT users_pinned_folders_unique_key UNIQUE (folder_id, user_id)
)
WITH (OIDS=FALSE);

Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
537
538
539
540
-- modules/life_cycle/sql/structure/life_cycle.postgresql.sql

CREATE TABLE lc_policies
(
Giovannoni Laurent's avatar
Giovannoni Laurent committed
541
   policy_id character varying(32) NOT NULL,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
542
543
544
   policy_name character varying(255) NOT NULL,
   policy_desc character varying(255) NOT NULL,
   CONSTRAINT lc_policies_pkey PRIMARY KEY (policy_id)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
545
)
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
546
547
548
549
550
551
WITH (OIDS = FALSE);


CREATE TABLE lc_cycles
(
   policy_id character varying(32) NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
552
   cycle_id character varying(32) NOT NULL,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
553
554
   cycle_desc character varying(255) NOT NULL,
   sequence_number integer NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
555
   where_clause text,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
556
   break_key character varying(255) DEFAULT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
557
   validation_mode character varying(32) NOT NULL,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
558
   CONSTRAINT lc_cycle_pkey PRIMARY KEY (policy_id, cycle_id)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
559
)
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
560
561
562
563
564
WITH (OIDS = FALSE);

CREATE TABLE lc_cycle_steps
(
   policy_id character varying(32) NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
565
566
   cycle_id character varying(32) NOT NULL,
   cycle_step_id character varying(32) NOT NULL,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
567
568
   cycle_step_desc character varying(255) NOT NULL,
   docserver_type_id character varying(32) NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
569
   is_allow_failure character(1) NOT NULL DEFAULT 'N'::bpchar,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
570
571
   step_operation character varying(32) NOT NULL,
   sequence_number integer NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
572
   is_must_complete character(1) NOT NULL DEFAULT 'N'::bpchar,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
573
   preprocess_script character varying(255) DEFAULT NULL,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
574
575
   postprocess_script character varying(255) DEFAULT NULL,
   CONSTRAINT lc_cycle_steps_pkey PRIMARY KEY (policy_id, cycle_id, cycle_step_id, docserver_type_id)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
576
)
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
577
578
579
580
581
WITH (OIDS = FALSE);

CREATE TABLE lc_stack
(
   policy_id character varying(32) NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
582
583
   cycle_id character varying(32) NOT NULL,
   cycle_step_id character varying(32) NOT NULL,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
584
   coll_id character varying(32) NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
585
586
   res_id bigint NOT NULL,
   cnt_retry integer DEFAULT NULL,
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
587
   status character(1) NOT NULL,
588
589
   work_batch bigint,
   regex character varying(32),
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
590
   CONSTRAINT lc_stack_pkey PRIMARY KEY (policy_id, cycle_id, cycle_step_id, res_id)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
591
)
Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
592
593
WITH (OIDS = FALSE);

594
595
CREATE TABLE notes
(
Damien's avatar
Damien committed
596
  id serial,
597
  identifier bigint NOT NULL,
Florian Azizian's avatar
Florian Azizian committed
598
  user_id bigint NOT NULL,
Damien's avatar
Damien committed
599
  creation_date timestamp without time zone NOT NULL,
600
601
602
603
  note_text text NOT NULL,
  CONSTRAINT notes_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
604
605


SNA's avatar
SNA committed
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
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);


Giovannoni Laurent's avatar
Giovannoni Laurent committed
624
625

-- modules/notes/sql/structure/notifications.postgresql.sql
626
627
628
629
CREATE SEQUENCE notifications_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
Giovannoni Laurent's avatar
Giovannoni Laurent committed
630
  START 100
631
632
633
634
635
636
637
  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),
Cyril Vazquez's avatar
Cyril Vazquez committed
638
  is_enabled character varying(1) NOT NULL default 'Y'::bpchar,
639
640
641
642
  event_id character varying(255) NOT NULL,
  notification_mode character varying(30) NOT NULL,
  template_id bigint,
  diffusion_type character varying(50) NOT NULL,
643
  diffusion_properties text,
644
  attachfor_type character varying(50),
Giovannoni Laurent's avatar
Giovannoni Laurent committed
645
  attachfor_properties character varying(2048),
646
647
648
649
650
651
  CONSTRAINT notifications_pkey PRIMARY KEY (notification_sid)
)
WITH (
  OIDS=FALSE
);

Giovannoni Laurent's avatar
Giovannoni Laurent committed
652

Cyril Vazquez's avatar
Cyril Vazquez committed
653
CREATE SEQUENCE notif_event_stack_seq
Giovannoni Laurent's avatar
Giovannoni Laurent committed
654
655
656
657
658
659
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

660
 -- DROP TABLE notif_event_stack
Cyril Vazquez's avatar
Cyril Vazquez committed
661
CREATE TABLE notif_event_stack
Giovannoni Laurent's avatar
Giovannoni Laurent committed
662
(
663
  event_stack_sid bigint NOT NULL DEFAULT nextval('notif_event_stack_seq'::regclass),
Cyril Vazquez's avatar
Cyril Vazquez committed
664
  notification_sid bigint NOT NULL,
Cyril Vazquez's avatar
Cyril Vazquez committed
665
  table_name character varying(50) NOT NULL,
666
  record_id character varying(128) NOT NULL,
667
  user_id integer NOT NULL,
Cyril Vazquez's avatar
Cyril Vazquez committed
668
  event_info character varying(255) NOT NULL,
Cyril Vazquez's avatar
Cyril Vazquez committed
669
670
671
  event_date timestamp without time zone NOT NULL,
  exec_date timestamp without time zone,
  exec_result character varying(50),
672
  CONSTRAINT notif_event_stack_pkey PRIMARY KEY (event_stack_sid)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
673
674
675
676
677
)
WITH (
  OIDS=FALSE
);

Cyril Vazquez's avatar
Cyril Vazquez committed
678
CREATE SEQUENCE notif_email_stack_seq
Giovannoni Laurent's avatar
Giovannoni Laurent committed
679
680
681
682
683
684
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

685
 -- DROP TABLE notif_email_stack
Cyril Vazquez's avatar
Cyril Vazquez committed
686
CREATE TABLE notif_email_stack
Giovannoni Laurent's avatar
Giovannoni Laurent committed
687
(
688
  email_stack_sid bigint NOT NULL DEFAULT nextval('notif_email_stack_seq'::regclass),
Giovannoni Laurent's avatar
Giovannoni Laurent committed
689
  reply_to character varying(255),
690
691
692
  recipient text NOT NULL,
  cc text,
  bcc text,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
693
694
  subject character varying(255),
  html_body text,
695
  attachments text,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
696
697
  exec_date timestamp without time zone,
  exec_result character varying(50),
698
  CONSTRAINT notif_email_stack_pkey PRIMARY KEY (email_stack_sid)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
699
700
701
702
703
)
WITH (
  OIDS=FALSE
);

Jean-Louis ERCOLANI's avatar
Jean-Louis ERCOLANI committed
704
-- modules/templates/sql/structure/templates.postgresql.sql
705

706
707
708
709
CREATE SEQUENCE templates_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
Giovannoni Laurent's avatar
Giovannoni Laurent committed
710
  START 110
711
712
713
714
  CACHE 1;

CREATE TABLE templates
(
715
  template_id bigint NOT NULL DEFAULT nextval('templates_seq'::regclass),
716
717
718
  template_label character varying(255) DEFAULT NULL::character varying,
  template_comment character varying(255) DEFAULT NULL::character varying,
  template_content text,
719
  template_type character varying(32) NOT NULL DEFAULT 'HTML'::character varying,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
720
  template_path character varying(255),
Giovannoni Laurent's avatar
Giovannoni Laurent committed
721
  template_file_name character varying(255),
722
  template_style character varying(255),
723
  template_datasource character varying(32),
Giovannoni Laurent's avatar
Giovannoni Laurent committed
724
  template_target character varying(255),
725
  template_attachment_type character varying(255) DEFAULT NULL::character varying,
726
  subject character varying(255),
727
  CONSTRAINT templates_pkey PRIMARY KEY (template_id)
728
729
730
731
732
)
WITH (OIDS=FALSE);

CREATE TABLE templates_association
(
Damien's avatar
Damien committed
733
  id serial,
734
735
  template_id bigint NOT NULL,
  value_field character varying(255) NOT NULL,
Damien's avatar
Damien committed
736
  CONSTRAINT templates_association_pkey PRIMARY KEY (id)
737
)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
738
739
740
741
WITH (
  OIDS=FALSE
);

742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
CREATE TABLE contacts
(
    id SERIAL NOT NULL,
    civility CHARACTER VARYING(256),
    firstname CHARACTER VARYING(256),
    lastname CHARACTER VARYING(256),
    company CHARACTER VARYING(256),
    department CHARACTER VARYING(256),
    function CHARACTER VARYING(256),
    address_number CHARACTER VARYING(256),
    address_street CHARACTER VARYING(256),
    address_additional1 CHARACTER VARYING(256),
    address_additional2 CHARACTER VARYING(256),
    address_postcode CHARACTER VARYING(256),
    address_town CHARACTER VARYING(256),
    address_country CHARACTER VARYING(256),
    email CHARACTER VARYING(256),
    phone CHARACTER VARYING(256),
    communication_means jsonb,
    notes text,
    creator INTEGER NOT NULL,
    creation_date TIMESTAMP without time zone NOT NULL DEFAULT NOW(),
    modification_date TIMESTAMP without time zone,
    enabled boolean NOT NULL DEFAULT TRUE,
766
    custom_fields jsonb DEFAULT '{}',
767
768
769
770
    external_id jsonb DEFAULT '{}',
    CONSTRAINT contacts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
771

772
CREATE TABLE contacts_parameters
773
(
774
775
776
777
778
779
780
781
782
    id SERIAL NOT NULL,
    identifier text NOT NULL,
    mandatory boolean NOT NULL DEFAULT FALSE,
    filling boolean NOT NULL DEFAULT FALSE,
    searchable boolean NOT NULL DEFAULT FALSE,
    displayable boolean NOT NULL DEFAULT FALSE,
    CONSTRAINT contacts_parameters_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
783

784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
CREATE TABLE contacts_groups
(
  id serial,
  label character varying(32) NOT NULL,
  description character varying(255) NOT NULL,
  public boolean NOT NULL,
  owner integer NOT NULL,
  CONSTRAINT contacts_groups_pkey PRIMARY KEY (id),
  CONSTRAINT contacts_groups_key UNIQUE (label, owner)
)
WITH (OIDS=FALSE);

CREATE TABLE contacts_groups_lists
(
  id serial,
  contacts_groups_id integer NOT NULL,
800
  contact_id integer NOT NULL,
801
  CONSTRAINT contacts_groups_lists_pkey PRIMARY KEY (id),
802
  CONSTRAINT contacts_groups_lists_key UNIQUE (contacts_groups_id, contact_id)
803
804
805
)
WITH (OIDS=FALSE);

806
807
808
809
CREATE TABLE search_templates (
  id serial,
  user_id integer NOT NULL,
  label character varying(255) NOT NULL,
810
  creation_date timestamp without time zone NOT NULL,
811
812
  query json NOT NULL,
  CONSTRAINT search_templates_pkey PRIMARY KEY (id)
813
814
815
816
817
818
) WITH (OIDS=FALSE);

CREATE SEQUENCE doctypes_first_level_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
Giovannoni Laurent's avatar
Giovannoni Laurent committed
819
  START 200
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
  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
Giovannoni Laurent's avatar
Giovannoni Laurent committed
836
  START 200
837
838
839
840
841
842
843
844
845
846
847
848
849
  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);

Giovannoni Laurent's avatar
Giovannoni Laurent committed
850
851
CREATE TABLE tags
(
852
  id serial NOT NULL,
853
  label character varying(128) NOT NULL,
854
855
  description text,
  parent_id INT,
856
857
  creation_date timestamp DEFAULT NOW(),
  links jsonb  DEFAULT '[]',
858
  usage text,
859
  CONSTRAINT tags_id_pkey PRIMARY KEY (id)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
860
861
862
)
WITH (OIDS=FALSE);

863
864
865
CREATE TABLE resources_tags
(
    id SERIAL NOT NULL,
866
867
868
869
    res_id INT NOT NULL,
    tag_id INT NOT NULL,
    CONSTRAINT resources_tags_id_pkey PRIMARY KEY (id),
    CONSTRAINT resources_tags_unique_key UNIQUE (res_id, tag_id)
870
871
872
)
WITH (OIDS=FALSE);

873
874
875
876
877
878
879
880
881
882
883
884
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,
885
  format character varying(50),
886
  typist INTEGER NOT NULL,
887
  creation_date timestamp without time zone NOT NULL,
888
  modification_date timestamp without time zone DEFAULT NOW(),
889
  doc_date timestamp without time zone,
890
  docserver_id character varying(32),
891
892
893
894
  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,
Damien's avatar
Damien committed
895
  status character varying(10),
896
  destination character varying(50) DEFAULT NULL::character varying,
897
  work_batch bigint,
898
  origin character varying(50) DEFAULT NULL::character varying,
Damien's avatar
Damien committed
899
  priority character varying(16),
SNA's avatar
SNA committed
900
901
  policy_id character varying(32) DEFAULT NULL::character varying,
  cycle_id character varying(32) DEFAULT NULL::character varying,
902
  initiator character varying(50) DEFAULT NULL::character varying,
903
  dest_user INTEGER,
Damien's avatar
Damien committed
904
  locker_user_id INTEGER DEFAULT NULL,
905
  locker_time timestamp without time zone,
906
  confidentiality character(1),
Giovannoni Laurent's avatar
Giovannoni Laurent committed
907
  fulltext_result character varying(10) DEFAULT NULL::character varying,
Damien's avatar
Damien committed
908
  external_id jsonb DEFAULT '{}',
909
  departure_date timestamp without time zone,
910
  opinion_limit_date timestamp without time zone default NULL,
911
  barcode text,
912
913
914
915
916
917
918
919
920
  category_id character varying(32)  NOT NULL,
  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,
921
922
  model_id INTEGER NOT NULL,
  version INTEGER NOT NULL,
Damien's avatar
Damien committed
923
  integrations jsonb DEFAULT '{}' NOT NULL,
924
  custom_fields jsonb,
925
  linked_resources jsonb NOT NULL DEFAULT '[]',
926
927
  retention_frozen boolean DEFAULT FALSE NOT NULL,
  binding boolean,
928
  original_filename character varying(255) DEFAULT NULL::character varying,
929
930
931
932
  CONSTRAINT res_letterbox_pkey PRIMARY KEY  (res_id)
)
WITH (OIDS=FALSE);

Giovannoni Laurent's avatar
Giovannoni Laurent committed
933
934
CREATE TABLE adr_letterbox
(
Damien's avatar
Damien committed
935
  id serial NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
936
  res_id bigint NOT NULL,
Damien's avatar
Damien committed
937
  type character varying(32) NOT NULL,
938
  version INTEGER NOT NULL,
Giovannoni Laurent's avatar
Giovannoni Laurent committed
939
  docserver_id character varying(32) NOT NULL,
Damien's avatar
Damien committed
940
941
  path character varying(255) NOT NULL,
  filename character varying(255) NOT NULL,
942
  fingerprint character varying(255) DEFAULT NULL,
Damien's avatar
Damien committed
943
  CONSTRAINT adr_letterbox_pkey PRIMARY KEY (id),
944
  CONSTRAINT adr_letterbox_unique_key UNIQUE (res_id, type, version)
Giovannoni Laurent's avatar
Giovannoni Laurent committed
945
946
947
)
WITH (OIDS=FALSE);

948
949
950
951
952
953
954
955
956
957
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);

958
959
CREATE TABLE user_signatures
(
960
961
  id serial,
  user_serial_id integer NOT NULL,
962
963
964
965
966
967
968
969
  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);

970
971
972
973
974
CREATE TABLE priorities
(
  id character varying(16) NOT NULL,
  label character varying(128) NOT NULL,
  color character varying(128) NOT NULL,
975
  delays integer NOT NULL,
976
  "order" integer,
977
978
979
980
  CONSTRAINT priorities_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

981
982
983
984
985
986
987
988
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)
);

989
990
991
992
993
994
995
996
997
998
999
1000
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),
For faster browsing, not all history is shown. View entire blame