Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
-- *************************************************************************--
-- --
-- --
-- THIS SCRIPT IS USE TO PASS FROM MAARCH 1.6 TO MAARCH 1.7 --
-- --
-- --
-- *************************************************************************--
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;
/* MIGRATION NOUVEL STRUCT MOTS CLES*/
DROP SEQUENCE IF EXISTS tag_id_seq CASCADE;
CREATE SEQUENCE tag_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 7
CACHE 1;
ALTER TABLE tags DROP COLUMN IF EXISTS tag_id;
ALTER TABLE tags ADD tag_id bigint NOT NULL DEFAULT nextval('tag_id_seq'::regclass);
ALTER TABLE tags DROP COLUMN IF EXISTS entity_id_owner;
ALTER TABLE tags ADD entity_id_owner character varying(32);
DROP SEQUENCE IF EXISTS tmp_tag_id_seq;
CREATE SEQUENCE tmp_tag_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 7
CACHE 1;
DROP TABLE IF EXISTS tmp_tags;
CREATE TABLE tmp_tags
(
tag_id bigint NOT NULL DEFAULT nextval('tmp_tag_id_seq'::regclass),
tag_label character varying(255) NOT NULL
)
WITH (
OIDS=FALSE
);
INSERT INTO tmp_tags (tag_label)
SELECT distinct(lower(tag_label)) from tags;
DROP TABLE IF EXISTS tag_res;
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
);
DO $$
BEGIN
BEGIN
ALTER TABLE tags ADD res_id bigint;
EXCEPTION
WHEN duplicate_column THEN RAISE NOTICE 'column res_id already exists in tags. skipping...';
END;
END;
$$;
INSERT INTO tag_res (res_id,tag_id)
SELECT tags.res_id, tmp_tags.tag_id FROM tags, tmp_tags WHERE tmp_tags.tag_label = lower(tags.tag_label) AND tags.res_id IS NOT NULL;
TRUNCATE TABLE tags;
ALTER TABLE tags DROP CONSTRAINT IF EXISTS tagsjoin_pkey;
ALTER TABLE tags DROP COLUMN IF EXISTS res_id;
INSERT INTO tags (tag_label, coll_id, tag_id)
SELECT tag_label, 'letterbox_coll', tag_id FROM tmp_tags;
DROP TABLE IF EXISTS tmp_tags;
DROP SEQUENCE IF EXISTS tmp_tag_id_seq;
DROP TABLE IF EXISTS tags_entities;
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
);
DROP TABLE IF EXISTS seda;
CREATE TABLE seda
(
"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,
PRIMARY KEY ("message_id")
)
WITH (
OIDS=FALSE
);
DROP TABLE IF EXISTS unit_identifier;
CREATE TABLE unit_identifier
(
"message_id" text NOT NULL,
"tablename" text NOT NULL,
"res_id" text NOT NULL
);
ALTER TABLE doctypes DROP COLUMN IF EXISTS retention_final_disposition;
ALTER TABLE doctypes ADD COLUMN retention_final_disposition character varying(255) NOT NULL DEFAULT 'destruction';
ALTER TABLE doctypes DROP COLUMN IF EXISTS retention_rule;
ALTER TABLE doctypes ADD COLUMN retention_rule character varying(15) NOT NULL DEFAULT 'P10Y';
ALTER TABLE entities DROP COLUMN IF EXISTS archival_agency;
ALTER TABLE entities ADD COLUMN archival_agency character varying(255);
ALTER TABLE entities DROP COLUMN IF EXISTS archival_agreement;
ALTER TABLE entities ADD COLUMN archival_agreement character varying(255);
DELETE FROM docservers where docserver_id = 'FASTHD_ATTACH';
INSERT INTO docservers (docserver_id, docserver_type_id, device_label, is_readonly, enabled, size_limit_number, actual_size_number, path_template, ext_docserver_info, chain_before, chain_after, creation_date, closing_date, coll_id, priority_number, docserver_location_id, adr_priority_number)
VALUES ('FASTHD_ATTACH', 'FASTHD', 'Fast internal disc bay for attachments', 'N', 'Y', 50000000000, 1, '/opt/maarch/docservers/manual_attachments/', NULL, NULL, NULL, '2011-01-13 14:47:49.197164', NULL, 'attachments_coll', 2, 'NANTERRE', 3);
ALTER TABLE basket_persistent_mode ALTER COLUMN user_id TYPE character varying(128);
ALTER TABLE res_mark_as_read ALTER COLUMN user_id TYPE character varying(128);
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
-- EXPORT SEDA
DROP TABLE IF EXISTS seda;
CREATE TABLE seda
(
"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,
PRIMARY KEY ("message_id")
)
WITH (
OIDS=FALSE
);
DROP TABLE IF EXISTS unit_identifier;
CREATE TABLE unit_identifier
(
"message_id" text NOT NULL,
"tablename" text NOT NULL,
"res_id" text NOT NULL
);
--EXPORT SEDA DATAS
DELETE FROM USERGROUPS WHERE GROUP_ID = 'ARCHIVISTE';
INSERT INTO USERGROUPS VALUES ('ARCHIVISTE', 'Archiviste', 'N', 'N', 'N', 'N', 'N', 'Y');
DELETE FROM USERGROUPS_SERVICES WHERE GROUP_ID = 'ARCHIVISTE';
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'add_thesaurus_to_res');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'adv_search_mlb');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'export_seda_view');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'fileplan');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'my_contacts_menu');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'put_doc_in_fileplan');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'sendmail');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'tag_view');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'view_baskets');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'view_doc_history');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'view_technical_infos');
INSERT INTO USERGROUPS_SERVICES (group_id, service_id) Values ('ARCHIVISTE', 'avis_documents');
DELETE FROM SECURITY WHERE GROUP_ID = 'ARCHIVISTE';
INSERT INTO SECURITY (group_id, coll_id, where_clause, maarch_comment, can_insert, can_update, can_delete, rights_bitmask, mr_start_date, mr_stop_date, where_target)
VALUES ('ARCHIVISTE', 'letterbox_coll', '1=1', 'Tous les courriers','N','N','N', 24, NULL, NULL, 'DOC');
DELETE FROM USERS WHERE USER_ID = 'aarc';
INSERT INTO USERS (user_id, password, firstname, lastname, mail, enabled, change_password, status, loginmode)
VALUES ('aarc', '65d1d802c2c5e7e9035c5cef3cfc0902b6d0b591bfa85977055290736bbfcdd7e19cb7cfc9f980d0c815bbf7fe329a4efd8da880515ba520b22c0aa3a96514cc', 'Alfred', 'ARC', 'info@maarch.org', 'Y', 'N', 'OK', 'standard');
DELETE FROM USERS_ENTITIES WHERE USER_ID = 'aarc';
INSERT INTO USERS_ENTITIES (user_id, entity_id, user_role, primary_entity)
VALUES ('aarc', 'VILLE', '', 'Y');
DELETE FROM USERGROUP_CONTENT WHERE USER_ID = 'aarc';
INSERT INTO USERGROUP_CONTENT (user_id, group_id, primary_group, role)
VALUES ('aarc', 'ARCHIVISTE', 'Y','');
DELETE FROM STATUS WHERE ID = 'EXP_SEDA';
INSERT INTO STATUS (id, label_status, is_system, is_folder_status, img_filename, maarch_module, can_be_searched, can_be_modified)
VALUES ('EXP_SEDA', 'A exporter au format SEDA', 'Y', 'N', 'fm-letter-status-acla', 'apps', 'Y', 'Y');
DELETE FROM ACTIONS WHERE id = 418;
INSERT INTO ACTIONS (id, keyword, label_action, id_status, is_system, is_folder_action, enabled, action_page, history, origin, create_id, category_id)
VALUES (418, '', 'Exporter SEDA', '_NOSTATUS_', 'N', 'N', 'Y', 'export_seda', 'Y', 'export_seda', 'N', NULL);
DELETE FROM ACTIONS WHERE id = 419;
INSERT INTO ACTIONS (id, keyword, label_action, id_status, is_system, is_folder_action, enabled, action_page, history, origin, create_id, category_id)
VALUES (419, '', 'Proposer export SEDA', 'EXP_SEDA', 'N', 'N', 'Y', '', 'Y', 'apps', 'N', NULL);
DELETE FROM BASKETS WHERE BASKET_ID = 'AExporterSeda';
INSERT INTO BASKETS (basket_id, basket_name, basket_desc, basket_clause, coll_id, is_visible, is_folder_basket, enabled, basket_order)
VALUES ('AExporterSeda', 'Courriers à exporter SEDA', 'Courriers à exporter SEDA', 'status=''EXP_SEDA''', 'letterbox_coll', 'Y', 'N', 'Y',300);
DELETE FROM GROUPBASKET WHERE BASKET_ID = 'AExporterSeda';
INSERT INTO GROUPBASKET (group_id, basket_id, sequence, redirect_basketlist, redirect_grouplist, result_page, can_redirect, can_delete, can_insert, list_lock_clause, sublist_lock_clause)
VALUES ('ARCHIVISTE', 'AExporterSeda', 1, NULL, NULL, 'list_with_attachments','N', 'N', 'N', NULL, NULL);
DELETE FROM ACTIONS_GROUPBASKETS WHERE id_action = 418;
INSERT INTO ACTIONS_GROUPBASKETS (id_action, where_clause, group_id, basket_id, used_in_basketlist, used_in_action_page, default_action_list)
VALUES (418, '', 'ARCHIVISTE', 'AExporterSeda', 'Y', 'N', 'N');
DELETE FROM ACTIONS_GROUPBASKETS WHERE id_action = 419;
INSERT INTO ACTIONS_GROUPBASKETS (id_action, where_clause, group_id, basket_id, used_in_basketlist, used_in_action_page, default_action_list)
VALUES (419, '', 'RESP_COURRIER', 'MyBasket', 'N', 'Y', 'N');
INSERT INTO ACTIONS_GROUPBASKETS (id_action, where_clause, group_id, basket_id, used_in_basketlist, used_in_action_page, default_action_list)
VALUES (419, '', 'RESPONSABLE', 'MyBasket', 'N', 'Y', 'N');
UPDATE ENTITIES SET BUSINESS_ID = 'org_987654321_Versant';
UPDATE ENTITIES SET ARCHIVAL_AGENCY = 'org_123456789_Archives';
UPDATE ENTITIES SET ARCHIVAL_AGREEMENT = 'MAARCH_LES_BAINS_ACTES';
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
/*************DIS UPDATE***************/
DROP SEQUENCE IF EXISTS allowed_ip_id_seq CASCADE;
CREATE SEQUENCE allowed_ip_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
DROP TABLE IF EXISTS allowed_ip;
CREATE TABLE allowed_ip
(
id integer NOT NULL DEFAULT nextval('allowed_ip_id_seq'::regclass),
ip character varying(50) NOT NULL,
CONSTRAINT allowed_ip_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
DROP SEQUENCE IF EXISTS user_signatures_seq CASCADE;
CREATE SEQUENCE user_signatures_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
DROP TABLE IF EXISTS user_signatures;
CREATE TABLE user_signatures
(
id bigint NOT NULL DEFAULT nextval('user_signatures_seq'::regclass),
user_id character varying(128) 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);
ALTER TABLE users DROP COLUMN IF EXISTS ra_code;
ALTER TABLE users ADD ra_code character varying(255);
ALTER TABLE users DROP COLUMN IF EXISTS ra_expiration_date;
ALTER TABLE users ADD ra_expiration_date timestamp without time zone;
UPDATE parameters SET param_value_int = '170' WHERE id = 'database_version';