· 8 years ago · Aug 31, 2018, 07:50 AM
1create table site_audit(
2 id integer not null,
3 event_id integer not null,
4 expected_auditor_id_utilisateur integer,
5 performer_id_entreprise integer not null,
6 status character varying(255),
7 loading boolean,
8 delivery boolean,
9 language character varying(255),
10 url character varying(255),
11 html text,
12 planning_date date,
13 creation_date date,
14 tdl_seen boolean default false,
15 tdl_trashed boolean default false,
16 constraint pk_site_audit_id primary key (id),
17 constraint fk_site_audit_event foreign key (event_id) references event_exploitation(id),
18 constraint fk_site_audit_expected_auditor foreign key (expected_auditor_id_utilisateur) references utilisateur(id_utilisateur),
19 constraint fk_site_audit_performer foreign key (performer_id_entreprise) references entreprise(id_entreprise)
20);
21
22CREATE SEQUENCE site_audit_id_seq
23 START WITH 1
24 INCREMENT BY 1
25 NO MINVALUE
26 NO MAXVALUE
27 CACHE 1;
28
29ALTER SEQUENCE site_audit_id_seq OWNED BY site_audit.id;
30
31ALTER TABLE ONLY site_audit ALTER COLUMN id SET DEFAULT nextval('site_audit_id_seq'::regclass);
32
33create table site_audit_record(
34 id_action integer not null,
35 libelle text,
36 type_action character varying(255),
37 date_action date,
38 auteur_id_utilisateur integer not null,
39 site_audit_id integer not null,
40 constraint pk_site_audit_record primary key (id_action),
41 constraint fk_site_audit_record_auteur foreign key (auteur_id_utilisateur) references utilisateur(id_utilisateur),
42 constraint fk_site_audit_record_site_audit foreign key (site_audit_id) references site_audit(id)
43);
44
45CREATE SEQUENCE site_audit_record_id_seq
46 START WITH 1
47 INCREMENT BY 1
48 NO MINVALUE
49 NO MAXVALUE
50 CACHE 1;
51
52ALTER SEQUENCE site_audit_record_id_seq OWNED BY site_audit_record.id_action;
53
54ALTER TABLE ONLY site_audit_record ALTER COLUMN id_action SET DEFAULT nextval('site_audit_record_id_seq'::regclass);
55
56create table quality_survey(
57 id integer not null,
58 devis_id_devis integer not null,
59 status character varying(255),
60 language character varying(255),
61 url character varying(255),
62 html text,
63 creation_date date,
64 constraint pk_quality_survey_id primary key (id),
65 constraint fk_quality_survey_devis foreign key (devis_id_devis) references devi(id_devis)
66);
67
68CREATE SEQUENCE quality_survey_id_seq
69 START WITH 1
70 INCREMENT BY 1
71 NO MINVALUE
72 NO MAXVALUE
73 CACHE 1;
74
75ALTER SEQUENCE quality_survey_id_seq OWNED BY quality_survey.id;
76
77ALTER TABLE ONLY quality_survey ALTER COLUMN id SET DEFAULT nextval('quality_survey_id_seq'::regclass);
78
79create table quality_survey_record(
80 id_action integer not null,
81 libelle text,
82 type_action character varying(255),
83 date_action date,
84 auteur_id_utilisateur integer not null,
85 quality_survey_id integer not null,
86 constraint pk_quality_survey_record primary key (id_action),
87 constraint fk_quality_survey_record_auteur foreign key (auteur_id_utilisateur) references utilisateur(id_utilisateur),
88 constraint fk_quality_survey_record_site_audit foreign key (quality_survey_id) references quality_survey(id)
89);
90
91CREATE SEQUENCE quality_survey_record_id_seq
92 START WITH 1
93 INCREMENT BY 1
94 NO MINVALUE
95 NO MAXVALUE
96 CACHE 1;
97
98ALTER SEQUENCE quality_survey_id_seq OWNED BY quality_survey_record.id_action;
99
100ALTER TABLE ONLY quality_survey_record ALTER COLUMN id_action SET DEFAULT nextval('quality_survey_record_id_seq'::regclass);
101
102
103create table column_group(
104 id integer not null,
105 wording character varying(255),
106 object_type character varying(255),
107 language character varying(255),
108 enabled boolean,
109 position integer,
110 constraint pk_column_group primary key (id)
111);
112
113CREATE SEQUENCE column_group_id_seq
114 START WITH 1
115 INCREMENT BY 1
116 NO MINVALUE
117 NO MAXVALUE
118 CACHE 1;
119
120ALTER SEQUENCE column_group_id_seq OWNED BY column_group.id;
121
122ALTER TABLE ONLY column_group ALTER COLUMN id SET DEFAULT nextval('column_group_id_seq'::regclass);
123
124
125
126create table value_type(
127 id integer not null,
128 wording character varying(255),
129 value_type character varying(255),
130 values text,
131 object_type character varying(255),
132 language character varying(255),
133 principal boolean,
134 enabled boolean,
135 constraint pk_value_type primary key (id)
136);
137
138CREATE SEQUENCE value_type_id_seq
139 START WITH 1
140 INCREMENT BY 1
141 NO MINVALUE
142 NO MAXVALUE
143 CACHE 1;
144
145ALTER SEQUENCE value_type_id_seq OWNED BY value_type.id;
146
147ALTER TABLE ONLY value_type ALTER COLUMN id SET DEFAULT nextval('value_type_id_seq'::regclass);
148
149create table heading(
150 id integer not null,
151 wording character varying(255),
152 object_type character varying(255),
153 language character varying(255),
154 enabled boolean,
155 position integer,
156 constraint pk_heading primary key (id)
157);
158
159CREATE SEQUENCE heading_id_seq
160 START WITH 1
161 INCREMENT BY 1
162 NO MINVALUE
163 NO MAXVALUE
164 CACHE 1;
165
166ALTER SEQUENCE heading_id_seq OWNED BY heading.id;
167
168ALTER TABLE ONLY heading ALTER COLUMN id SET DEFAULT nextval('heading_id_seq'::regclass);
169
170
171create table line(
172 id integer not null,
173 wording character varying(255),
174 object_type character varying(255),
175 language character varying(255),
176 heading_id integer,
177 enabled boolean,
178 position integer,
179 value_type_id integer not null,
180 constraint pk_line primary key (id),
181 constraint fk_line_heading foreign key (heading_id) references heading(id),
182 constraint fk_line_value_type foreign key (value_type_id) references value_type(id)
183);
184
185CREATE SEQUENCE line_id_seq
186 START WITH 1
187 INCREMENT BY 1
188 NO MINVALUE
189 NO MAXVALUE
190 CACHE 1;
191
192ALTER SEQUENCE line_id_seq OWNED BY line.id;
193
194ALTER TABLE ONLY line ALTER COLUMN id SET DEFAULT nextval('line_id_seq'::regclass);
195
196
197create table line_column_group(
198 line_id integer not null,
199 column_group_id integer not null,
200 constraint pk_line_column_group primary key (line_id,column_group_id),
201 constraint fk_line_column_group_line foreign key (line_id) references line(id),
202 constraint fk_line_column_group_column_group foreign key (column_group_id) references column_group(id)
203);
204
205create table site_audit_line(
206 id integer not null,
207 site_audit_id integer not null,
208 line_id integer not null,
209 column_group_id integer,
210 number_value integer,
211 string_value character varying(255),
212 text_value text,
213 constraint pk_site_audit_line primary key (id),
214 constraint fk_site_audit_line_site_audit_id foreign key (site_audit_id) references site_audit(id),
215 constraint fk_site_audit_line_line foreign key (line_id) references line(id),
216 constraint fk_site_audit_line_column_group foreign key (column_group_id) references column_group(id)
217);
218
219create table quality_survey_line(
220 id integer not null,
221 quality_survey_id integer not null,
222 line_id integer not null,
223 column_group_id integer,
224 number_value integer,
225 string_value character varying(255),
226 text_value text,
227 constraint pk_quality_survey_line primary key (id),
228 constraint fk_quality_survey_line_quality_survey_id foreign key (quality_survey_id) references quality_survey(id),
229 constraint fk_quality_survey_line_line foreign key (line_id) references line(id),
230 constraint fk_quality_survey_line_column_group foreign key (column_group_id) references column_group(id)
231);
232
233
234CREATE SEQUENCE site_audit_line_id_seq
235 START WITH 1
236 INCREMENT BY 1
237 NO MINVALUE
238 NO MAXVALUE
239 CACHE 1;
240
241ALTER SEQUENCE site_audit_line_id_seq OWNED BY site_audit_line.id;
242
243ALTER TABLE ONLY site_audit_line ALTER COLUMN id SET DEFAULT nextval('site_audit_line_id_seq'::regclass);
244
245CREATE SEQUENCE quality_survey_line_id_seq
246 START WITH 1
247 INCREMENT BY 1
248 NO MINVALUE
249 NO MAXVALUE
250 CACHE 1;
251
252ALTER SEQUENCE quality_survey_line_id_seq OWNED BY quality_survey_line.id;
253
254ALTER TABLE ONLY quality_survey_line ALTER COLUMN id SET DEFAULT nextval('quality_survey_line_id_seq'::regclass);
255
256------------------------------------------------------------------------
257alter table user_permission add column base_user_permission_id integer;
258alter table user_permission add constraint fk_user_permission_base_user_permission foreign key (base_user_permission_id) references user_permission(id);
259
260------------------------------------------------------------------------
261
262
263alter table bloc add column language character varying(255) default 'FR';
264alter table layout add column language character varying(255) default 'FR';
265
266alter table user_espace_client add column lang character varying(255) default 'FR';
267
268alter table user_espace_client add column lang character varying(255) default 'FR';
269alter table site_audit_record add column action_time bigint;
270alter table quality_survey_record add column action_time bigint;
271
272alter table value_type add column insufficient character varying(255);
273
274alter table site_audit add column devis_id_devis integer;
275alter table site_audit add constraint fk_site_audit_devis foreign key (devis_id_devis) references devi(id_devis);
276alter table site_audit add constraint uc_site_audit_devis unique (devis_id_devis);
277alter table devi add constraint uc_devis_programmation_principale unique (programmation_principale_idprogrammation);
278--------------------------------------------------------------------------------
279
280ALTER TABLE programmation
281ADD CONSTRAINT uc_programmation_devis UNIQUE (devis_id_devis);
282
283----------------------------------------------------------------------------------
284
285alter table agence add column folder_name character varying(255);
286update agence set folder_name=nom;
287
288-----------------------------------------------------------------------------------------
289
290create table mission_sheet(
291 id integer not null,
292 beginning_date date,
293 ending_date date,
294 truck_idvehiculedemenagement integer not null,
295 language character varying(255),
296 url character varying(255),
297 creationDate date,
298 constraint pk_mission_sheet primary key (id),
299 constraint fk_mission_sheet_camion foreign key (truck_idvehiculedemenagement) references vehicule_demenagement(idvehiculedemenagement)
300);
301
302CREATE SEQUENCE mission_sheet_id_seq
303 START WITH 1
304 INCREMENT BY 1
305 NO MINVALUE
306 NO MAXVALUE
307 CACHE 1;
308
309ALTER SEQUENCE mission_sheet_id_seq OWNED BY mission_sheet.id;
310
311ALTER TABLE ONLY mission_sheet ALTER COLUMN id SET DEFAULT nextval('mission_sheet_id_seq'::regclass);
312
313create table mission_sheet_chiefs(
314 mission_sheet_id integer not null,
315 demenageur_iddemenageur integer not null,
316 constraint pk_mission_sheet_chiefs primary key (mission_sheet_id,demenageur_iddemenageur),
317 constraint fk_mission_sheet_chiefs_mission_sheet foreign key (mission_sheet_id) references mission_sheet(id),
318 constraint fk_mission_sheet_chiefs_demenageur foreign key (demenageur_iddemenageur) references demenageur(iddemenageur)
319);
320
321create table mission_sheet_movers(
322 mission_sheet_id integer not null,
323 demenageur_iddemenageur integer not null,
324 constraint pk_mission_sheet_movers primary key (mission_sheet_id,demenageur_iddemenageur),
325 constraint fk_mission_sheet_movers_mission_sheet foreign key (mission_sheet_id) references mission_sheet(id),
326 constraint fk_mission_sheet_movers_demenageur foreign key (demenageur_iddemenageur) references demenageur(iddemenageur)
327);
328
329create table mission_sheet_events(
330 mission_sheet_id integer not null,
331 event_exploitation_id integer not null,
332 constraint pk_mission_sheet_events primary key (mission_sheet_id,event_exploitation_id),
333 constraint fk_mission_sheet_events_mission_sheet foreign key (mission_sheet_id) references mission_sheet(id),
334 constraint fk_mission_sheet_events_event foreign key (event_exploitation_id) references event_exploitation(id)
335);
336
337alter table quality_survey add column mode character varying (255)[];
338update quality_survey set mode='{"Chargement","Livraison"}';
339
340alter table container add column url character varying (255);
341
342alter table agence add column document_urls character varying (255)[];
343
344alter table operation add column mode character varying (255);
345
346alter table line add column required boolean default true;
347
348
349
350create table deposit_record(
351 id_action integer not null,
352 libelle text,
353 type_action character varying(255),
354 date_action date,
355 auteur_id_utilisateur integer not null,
356 depot_idepot integer not null,
357 constraint pk_deposit_record primary key (id_action),
358 constraint fk_deposit_record_auteur foreign key (auteur_id_utilisateur) references utilisateur(id_utilisateur),
359 constraint fk_deposit_record_deposit foreign key (depot_idepot) references depot(idepot)
360);
361
362CREATE SEQUENCE deposit_record_id_seq
363 START WITH 1
364 INCREMENT BY 1
365 NO MINVALUE
366 NO MAXVALUE
367 CACHE 1;
368
369ALTER SEQUENCE deposit_record_id_seq OWNED BY deposit_record.id_action;
370
371ALTER TABLE ONLY deposit_record ALTER COLUMN id_action SET DEFAULT nextval('deposit_record_id_seq'::regclass);
372
373
374alter table fiche add column submission_id character varying (255);
375alter table fiche add column distribution_id character varying (255);
376alter table raw_sheet add column submission_id character varying (255);
377alter table raw_sheet add column distribution_id character varying (255);
378
379
380
381create table storage_unit_billing_month(
382 id integer not null,
383 month integer,
384 year integer,
385 bill_idfacture integer not null,
386 constraint pk_storage_unit_billing_month primary key (id),
387 constraint fk_storage_unit_billing_month_bill foreign key (bill_idfacture) references facture(idfacture)
388);
389
390CREATE SEQUENCE storage_unit_billing_month_id_seq
391 START WITH 1
392 INCREMENT BY 1
393 NO MINVALUE
394 NO MAXVALUE
395 CACHE 1;
396
397ALTER SEQUENCE storage_unit_billing_month_id_seq OWNED BY storage_unit_billing_month.id;
398
399ALTER TABLE ONLY storage_unit_billing_month ALTER COLUMN id SET DEFAULT nextval('storage_unit_billing_month_id_seq'::regclass);
400
401
402
403create table purchase_order(
404 id integer not null,
405 number character varying(255),
406 quote_id_devis integer,
407 provider_id_fournisseur integer,
408 user_id_utilisateur integer,
409 payment_terms_id integer,
410 lastname character varying(255),
411 firstname character varying(255),
412 street_number character varying(255),
413 street character varying(255),
414 postal_code character varying(255),
415 city character varying(255),
416 complement character varying(255),
417 department character varying(255),
418 region character varying(255),
419 country character varying(255),
420 language character varying(255),
421 url character varying(255),
422 html text,
423 creation_date date,
424 constraint pk_purchase_order primary key (id),
425 constraint fk_purchase_order_devis foreign key (quote_id_devis) references devi(id_devis),
426 constraint fk_purchase_order_provider foreign key (provider_id_fournisseur) references fournisseur(id_fournisseur),
427 constraint fk_purchase_order_user foreign key (user_id_utilisateur) references utilisateur(id_utilisateur),
428 constraint fk_purchase_order_payment_terms foreign key (payment_terms_id) references modalite(id)
429);
430
431CREATE SEQUENCE purchase_order_id_seq
432 START WITH 1
433 INCREMENT BY 1
434 NO MINVALUE
435 NO MAXVALUE
436 CACHE 1;
437
438ALTER SEQUENCE purchase_order_id_seq OWNED BY purchase_order.id;
439
440ALTER TABLE ONLY purchase_order ALTER COLUMN id SET DEFAULT nextval('purchase_order_id_seq'::regclass);
441
442
443create table post(
444 id integer not null,
445 wording text,
446 reference character varying(255),
447 price double precision,
448 number double precision,
449 total double precision,
450 purchase_order_id integer,
451 constraint pk_post primary key (id),
452 constraint fk_post_purchase_order foreign key (purchase_order_id) references purchase_order(id)
453);
454
455CREATE SEQUENCE post_id_seq
456 START WITH 1
457 INCREMENT BY 1
458 NO MINVALUE
459 NO MAXVALUE
460 CACHE 1;
461
462ALTER SEQUENCE post_id_seq OWNED BY post.id;
463
464ALTER TABLE ONLY post ALTER COLUMN id SET DEFAULT nextval('post_id_seq'::regclass);
465
466alter table real_cost add column purchase_order_id integer;
467alter table real_cost add constraint fk_real_cost_purchase_order foreign key (purchase_order_id) references purchase_order(id);
468
469
470/*RENAME IDS*/
471alter table devi rename id_devis to id;
472select table_name,column_name from information_schema.columns where column_name like '%_id_devis';
473alter table adresse_demenagement rename devis_id_devis to devis_id;
474alter table demande_stationnement rename devi_id_devis to devi_id;
475alter table devi_champs_devi rename devi_id_devis to devi_id;
476alter table devi_client rename devi_id_devis to devi_id;
477alter table devi_cout_client rename devi_id_devis to devi_id;
478alter table devi_etape rename devis_id_devis to devis_id;
479alter table devi_poste_client rename devi_id_devis to devi_id;
480alter table devi_service_devi rename devi_id_devis to devi_id;
481alter table devi_statut rename devi_id_devis to devi_id;
482alter table devis_action_historique rename devis_id_devis to devis_id;
483alter table enqinter rename devi_id_devis to devi_id;
484alter table enqnat rename devi_id_devis to devi_id;
485alter table event rename devis_id_devis to devis_id;
486alter table event_exploitation rename devis_id_devis to devis_id;
487alter table lettre_voiture rename devi_id_devis to devi_id;
488alter table offrelot rename devi_id_devis to devi_id;
489alter table offrelot rename devis_transfert_id_devis to devis_transfert_id;
490alter table ordre_mission rename devi_id_devis to devi_id;
491alter table programmation rename devis_id_devis to devis_id;
492alter table purchase_order rename quote_id_devis to quote_id;
493alter table quality_survey rename devis_id_devis to devis_id;
494alter table quote_specific_field rename devis_id_devis to devis_id;
495alter table real_cost rename quote_id_devis to quote_id;
496alter table site_audit rename devis_id_devis to devis_id;
497alter table sous_traitance_devis rename devis_id_devis to devis_id;
498alter table supplier_bill_devi rename devi_id_devis to devi_id;
499alter table vehicule_devis rename devi_id_devis to devi_id;
500alter table visite rename devis_id_devis to devis_id;
501
502
503alter table utilisateur add column sheet_only boolean;
504
505
506
507
508create table damage(
509 id integer not null,
510 claim_type_id integer,
511 precisions text,
512 event_avarie_id_event_avarie integer,
513 sortie_avarie_id_sortie_avarie integer,
514 constraint pk_damage primary key (id),
515 constraint fk_damage_claim_type foreign key (claim_type_id) references claim_type(id),
516 constraint fk_damage_event_avarie foreign key (event_avarie_id_event_avarie) references event_avarie(id_event_avarie),
517 constraint fk_damage_sortie_avarie foreign key (sortie_avarie_id_sortie_avarie) references sortie_avarie(id_sortie_avarie)
518);
519
520CREATE SEQUENCE damage_id_seq
521 START WITH 1
522 INCREMENT BY 1
523 NO MINVALUE
524 NO MAXVALUE
525 CACHE 1;
526
527ALTER SEQUENCE damage_id_seq OWNED BY damage.id;
528
529ALTER TABLE ONLY damage ALTER COLUMN id SET DEFAULT nextval('damage_id_seq'::regclass);
530
531create table damage_type_avarie(
532 damage_id integer not null,
533 type_avarie_id_type_avarie integer,
534 constraint pk_damage_type_avarie primary key (damage_id,type_avarie_id_type_avarie),
535 constraint fk_damage_type_avarie_damage foreign key (damage_id) references damage(id),
536 constraint fk_damage_type_avarie_type_avarie foreign key (type_avarie_id_type_avarie) references type_avarie(id_type_avarie)
537);
538
539create table damage_cause_avarie(
540 damage_id integer not null,
541 cause_avarie_id_cause_avarie integer,
542 constraint pk_damage_cause_avarie primary key (damage_id,cause_avarie_id_cause_avarie),
543 constraint fk_damage_cause_avarie_damage foreign key (damage_id) references damage(id),
544 constraint fk_damage_cause_avarie_cause_avarie foreign key (cause_avarie_id_cause_avarie) references cause_avarie(id_cause_avarie)
545);
546
547
548
549alter table agence add column sheet_only boolean default false;
550
551update user_espace_client set lang='FR';
552alter table user_espace_client alter column lang set default 'FR';
553
554
555alter table service_apres_vente add column user_id_utilisateur integer;
556alter table service_apres_vente add constraint fk_service_apres_vente_user foreign key (user_id_utilisateur) references utilisateur(id_utilisateur);
557
558
559alter table facture add column is_interfaced boolean default false;
560alter table avoir add column is_interfaced boolean default false;
561alter table encaissement add column is_interfaced boolean default false;
562
563
564
565create table storage_unit_container_type(
566 id integer not null,
567 wording character varying(255),
568 volume double precision,
569 price double precision,
570 constraint pk_storage_unit_container_type primary key (id)
571);
572
573CREATE SEQUENCE storage_unit_container_type_id_seq
574 START WITH 1
575 INCREMENT BY 1
576 NO MINVALUE
577 NO MAXVALUE
578 CACHE 1;
579
580ALTER SEQUENCE storage_unit_container_type_id_seq OWNED BY storage_unit_container_type.id;
581
582ALTER TABLE ONLY storage_unit_container_type ALTER COLUMN id SET DEFAULT nextval('storage_unit_container_type_id_seq'::regclass);
583
584alter table gardemeuble add column container_type_id integer;
585
586alter table gardemeuble add constraint fk_gardemeuble_container_type foreign key (container_type_id) references storage_unit_container_type(id);
587
588
589
590
591
592/*
593alter table quality_survey_line rename column number_value to integer_value;
594alter table quality_survey_line add column double_value double precision;
595alter table site_audit_line rename column number_value to integer_value;
596alter table site_audit_line add column double_value double precision;
597update value_type set value_type='double' where value_type='number';
598update quality_survey_line set double_value=integer_value::double precision where integer_value is not null;
599update site_audit_line set double_value=integer_value::double precision where integer_value is not null;*/
600
601
602alter table utilisateur add column national_commission double precision default 0.0;
603alter table utilisateur add column export_commission double precision default 0.0;
604
605
606
607alter table value_type add column help text default '';
608
609
610
611--------------------------------------EXPLOITATION--------------------------------------
612
613drop table if exists route;
614drop table if exists moving_address;
615drop table if exists pack_action_address;
616drop table if exists pack_heavy_weight;
617drop table if exists pack;
618drop table if exists pack_action;
619drop table if exists heavy_weight;
620drop table if exists exploitation_phase;
621
622create table exploitation_phase(
623 id integer not null,
624 wording character varying(255),
625 planning character varying(255),
626 agency_id_entreprise integer not null,
627 constraint pk_exploitation_phase primary key (id),
628 constraint fk_exploitation_phase_agency foreign key (agency_id_entreprise) references agence(id_entreprise)
629);
630
631CREATE SEQUENCE exploitation_phase_id_seq
632 START WITH 1
633 INCREMENT BY 1
634 NO MINVALUE
635 NO MAXVALUE
636 CACHE 1;
637
638ALTER SEQUENCE exploitation_phase_id_seq OWNED BY exploitation_phase.id;
639
640ALTER TABLE ONLY exploitation_phase ALTER COLUMN id SET DEFAULT nextval('exploitation_phase_id_seq'::regclass);
641
642create table heavy_weight(
643 id integer not null,
644 wording character varying(255),
645 average_volume double precision,
646 average_weight double precision,
647 agency_id_entreprise integer not null,
648 constraint pk_heavy_weight primary key (id),
649 constraint fk_heavy_weight_agency foreign key (agency_id_entreprise) references agence(id_entreprise)
650);
651
652CREATE SEQUENCE heavy_weight_id_seq
653 START WITH 1
654 INCREMENT BY 1
655 NO MINVALUE
656 NO MAXVALUE
657 CACHE 1;
658
659ALTER SEQUENCE heavy_weight_id_seq OWNED BY heavy_weight.id;
660
661ALTER TABLE ONLY heavy_weight ALTER COLUMN id SET DEFAULT nextval('heavy_weight_id_seq'::regclass);
662
663create table pack_action(
664 id integer not null,
665 wording character varying(255),
666 icon character varying(255),
667 agency_id_entreprise integer not null,
668 constraint pk_pack_action primary key (id),
669 constraint fk_pack_action_agency foreign key (agency_id_entreprise) references agence(id_entreprise)
670);
671
672CREATE SEQUENCE pack_action_id_seq
673 START WITH 1
674 INCREMENT BY 1
675 NO MINVALUE
676 NO MAXVALUE
677 CACHE 1;
678
679ALTER SEQUENCE pack_action_id_seq OWNED BY pack_action.id;
680
681ALTER TABLE ONLY pack_action ALTER COLUMN id SET DEFAULT nextval('pack_action_id_seq'::regclass);
682
683create table pack(
684 id integer not null,
685 quote_id integer not null,
686 wording character varying(255),
687 volume double precision,
688 weight double precision,
689 constraint pk_pack primary key (id),
690 constraint fk_pack_quote foreign key (quote_id) references quote(id)
691);
692
693CREATE SEQUENCE pack_id_seq
694 START WITH 1
695 INCREMENT BY 1
696 NO MINVALUE
697 NO MAXVALUE
698 CACHE 1;
699
700ALTER SEQUENCE pack_id_seq OWNED BY pack.id;
701
702ALTER TABLE ONLY pack ALTER COLUMN id SET DEFAULT nextval('pack_id_seq'::regclass);
703
704create table pack_heavy_weight(
705 pack_id integer not null,
706 heavy_weight_id integer not null,
707 constraint pk_pack_heavy_weight primary key (pack_id,heavy_weight_id),
708 constraint fk_pack_heavy_weight_pack foreign key (pack_id) references pack(id),
709 constraint fk_pack_heavy_weight_heavy_weight foreign key (heavy_weight_id) references heavy_weight(id)
710);
711
712
713create table pack_action_address(
714 id integer not null,
715 pack_id integer not null,
716 address_id integer not null,
717 pack_action_id integer not null,
718 constraint pk_pack_action_address primary key (id),
719 constraint fk_pack_action_address_pack foreign key (pack_id) references pack(id),
720 constraint fk_pack_action_address_address foreign key (address_id) references address(id),
721 constraint fk_pack_action_address_pack_action foreign key (pack_action_id) references pack_action(id)
722);
723
724CREATE SEQUENCE pack_action_address_id_seq
725 START WITH 1
726 INCREMENT BY 1
727 NO MINVALUE
728 NO MAXVALUE
729 CACHE 1;
730
731ALTER SEQUENCE pack_action_address_id_seq OWNED BY pack_action_address.id;
732
733ALTER TABLE ONLY pack_action_address ALTER COLUMN id SET DEFAULT nextval('pack_action_address_id_seq'::regclass);
734
735
736create table moving_address(
737 id integer not null,
738 address_idadresse integer not null,
739 title character varying(255),
740 parking boolean,
741 furniture_hoist boolean,
742 transshipment boolean,
743 window_passage boolean,
744 elevator character varying(255),
745 floor integer,
746 portage integer,
747 internal_instructions text,
748 loading_supplement text,
749 access text,
750 housing_type character varying(255),
751 quote_id integer not null,
752 constraint pk_moving_address primary key (id),
753 constraint fk_moving_address_address foreign key (address_idadresse) references adresse(idadresse),
754 constraint fk_moving_address_quote foreign key (quote_id) references quote(id)
755);
756
757CREATE SEQUENCE moving_address_id_seq
758 START WITH 1
759 INCREMENT BY 1
760 NO MINVALUE
761 NO MAXVALUE
762 CACHE 1;
763
764ALTER SEQUENCE moving_address_id_seq OWNED BY moving_address.id;
765
766ALTER TABLE ONLY moving_address ALTER COLUMN id SET DEFAULT nextval('moving_address_id_seq'::regclass);
767
768
769create table route(
770 id integer not null,
771 departure_id integer not null,
772 arrival_id integer not null,
773 distance integer,
774 constraint pk_route primary key (id),
775 constraint fk_route_departure foreign key (departure_id) references moving_address(id),
776 constraint fk_route_arrival foreign key (arrival_id) references moving_address(id)
777);
778
779CREATE SEQUENCE route_id_seq
780 START WITH 1
781 INCREMENT BY 1
782 NO MINVALUE
783 NO MAXVALUE
784 CACHE 1;
785
786ALTER SEQUENCE route_id_seq OWNED BY route.id;
787
788ALTER TABLE ONLY route ALTER COLUMN id SET DEFAULT nextval('route_id_seq'::regclass);
789
790
791alter table quote add column agency_id_entreprise integer;
792alter table quote add constraint fk_quote_agency foreign key (agency_id_entreprise) references agence(id_entreprise);
793
794
795---------------------------------------------------------------------------------------------------------------------------
796
797
798drop table if exists route;
799drop table if exists airport;
800drop table if exists warehouse;
801drop table if exists port;
802drop table if exists train_station;
803drop table if exists moving_address;
804drop table if exists country;
805drop table if exists country_name;
806drop table if exists city;
807drop table if exists city_name;
808drop table if exists administrative_area;
809drop table if exists city_administrative_area;
810drop table if exists postal_code;
811drop table if exists city_postal_code;
812drop table if exists address;
813drop table if exists moving_address;
814
815create table lang(
816 id integer not null,
817 value character varying(255),
818 constraint pk_lang primary key (id)
819);
820
821CREATE SEQUENCE lang_id_seq
822 START WITH 1
823 INCREMENT BY 1
824 NO MINVALUE
825 NO MAXVALUE
826 CACHE 1;
827
828ALTER SEQUENCE lang_id_seq OWNED BY lang.id;
829
830ALTER TABLE ONLY lang ALTER COLUMN id SET DEFAULT nextval('lang_id_seq'::regclass);
831
832insert into lang (value) values('FR');
833insert into lang (value) values('EN');
834insert into lang (value) values('ES');
835
836create table country(
837 id integer not null,
838 alpha2 character varying(255),
839 alpha3 character varying(255),
840 code integer,
841 postal_code_regex character varying(255),
842 constraint pk_country primary key (id)
843);
844
845CREATE SEQUENCE country_id_seq
846 START WITH 1
847 INCREMENT BY 1
848 NO MINVALUE
849 NO MAXVALUE
850 CACHE 1;
851
852ALTER SEQUENCE country_id_seq OWNED BY country.id;
853
854ALTER TABLE ONLY country ALTER COLUMN id SET DEFAULT nextval('country_id_seq'::regclass);
855
856create table country_name(
857 country_id integer not null,
858 lang_id integer not null,
859 value character varying(255),
860 constraint pk_country_name primary key (country_id,lang_id),
861 constraint fk_country_name_country foreign key (country_id) references country(id),
862 constraint fk_country_name_lang foreign key (lang_id) references lang(id)
863);
864
865create table city(
866 id integer not null,
867 place_id character varying(255),
868 country_id integer,
869 constraint pk_city primary key (id),
870 constraint fk_city_country foreign key (country_id) references country(id)
871);
872
873CREATE SEQUENCE city_id_seq
874 START WITH 1
875 INCREMENT BY 1
876 NO MINVALUE
877 NO MAXVALUE
878 CACHE 1;
879
880ALTER SEQUENCE city_id_seq OWNED BY city.id;
881
882ALTER TABLE ONLY city ALTER COLUMN id SET DEFAULT nextval('city_id_seq'::regclass);
883
884create table city_name(
885 city_id integer not null,
886 lang_id integer not null,
887 value character varying(255),
888 constraint pk_city_name primary key (city_id,lang_id),
889 constraint fk_city_name_city foreign key (city_id) references city(id),
890 constraint fk_city_name_lang foreign key (lang_id) references lang(id)
891);
892
893create table administrative_area(
894 id integer not null,
895 level integer,
896 place_id character varying(255),
897 country_id integer,
898 constraint pk_administrative_area primary key (id),
899 constraint fk_administrative_area_country foreign key (country_id) references country(id)
900);
901
902CREATE SEQUENCE administrative_area_id_seq
903 START WITH 1
904 INCREMENT BY 1
905 NO MINVALUE
906 NO MAXVALUE
907 CACHE 1;
908
909ALTER SEQUENCE administrative_area_id_seq OWNED BY city.id;
910
911ALTER TABLE ONLY administrative_area ALTER COLUMN id SET DEFAULT nextval('administrative_area_id_seq'::regclass);
912
913create table administrative_area_name(
914 administrative_area_id integer not null,
915 lang_id integer not null,
916 value character varying(255),
917 constraint pk_administrative_area_name primary key (administrative_area_id,lang_id),
918 constraint fk_administrative_area_name_administrative_area foreign key (administrative_area_id) references administrative_area(id),
919 constraint fk_administrative_area_name_lang foreign key (lang_id) references lang(id)
920);
921
922create table city_administrative_area(
923 city_id integer not null,
924 administrative_area_id integer not null,
925 constraint pk_city_administrative_area primary key (city_id,administrative_area_id),
926 constraint fk_city_administrative_area_city foreign key (city_id) references city(id),
927 constraint fk_city_administrative_area_administrative_area foreign key (administrative_area_id) references administrative_area(id)
928);
929
930create table address(
931 id integer not null,
932 number character varying(255),
933 street character varying(255),
934 complement text,
935 postal_code character varying(255),
936 nickname character varying(255),
937 city_id integer,
938 longitude double precision,
939 latitude double precision,
940 constraint pk_address primary key (id),
941 constraint fk_address_city foreign key (city_id) references city(id)
942);
943
944CREATE SEQUENCE address_id_seq
945 START WITH 1
946 INCREMENT BY 1
947 NO MINVALUE
948 NO MAXVALUE
949 CACHE 1;
950
951ALTER SEQUENCE address_id_seq OWNED BY address.id;
952
953ALTER TABLE ONLY address ALTER COLUMN id SET DEFAULT nextval('address_id_seq'::regclass);
954
955create table person(
956 id integer not null,
957 lastname character varying(255),
958 firstname character varying(255),
959 civility integer,
960 phone_number text,
961 email character varying(255),
962 address_id integer,
963 constraint pk_person primary key (id),
964 constraint fk_person_address foreign key (address_id) references address(id)
965);
966
967CREATE SEQUENCE person_id_seq
968 START WITH 1
969 INCREMENT BY 1
970 NO MINVALUE
971 NO MAXVALUE
972 CACHE 1;
973
974ALTER SEQUENCE person_id_seq OWNED BY person.id;
975
976ALTER TABLE ONLY person ALTER COLUMN id SET DEFAULT nextval('person_id_seq'::regclass);
977
978
979create table moving_address(
980 id integer not null,
981 address_id integer not null,
982 title character varying(255),
983 parking boolean,
984 furniture_hoist boolean,
985 transshipment boolean,
986 window_passage boolean,
987 elevator character varying(255),
988 floor integer,
989 portage integer,
990 internal_instructions text,
991 loading_supplement text,
992 access text,
993 housing_type character varying(255),
994 quote_id integer not null,
995 constraint pk_moving_address primary key (id),
996 constraint fk_moving_address_address foreign key (address_id) references address(id),
997 constraint fk_moving_address_quote foreign key (quote_id) references quote(id)
998);
999
1000CREATE SEQUENCE moving_address_id_seq
1001 START WITH 1
1002 INCREMENT BY 1
1003 NO MINVALUE
1004 NO MAXVALUE
1005 CACHE 1;
1006
1007ALTER SEQUENCE moving_address_id_seq OWNED BY moving_address.id;
1008
1009ALTER TABLE ONLY moving_address ALTER COLUMN id SET DEFAULT nextval('moving_address_id_seq'::regclass);
1010
1011
1012create table route(
1013 id integer not null,
1014 departure_id integer not null,
1015 arrival_id integer not null,
1016 distance integer,
1017 constraint pk_route primary key (id),
1018 constraint fk_route_departure foreign key (departure_id) references moving_address(id),
1019 constraint fk_route_arrival foreign key (arrival_id) references moving_address(id)
1020);
1021
1022CREATE SEQUENCE route_id_seq
1023 START WITH 1
1024 INCREMENT BY 1
1025 NO MINVALUE
1026 NO MAXVALUE
1027 CACHE 1;
1028
1029ALTER SEQUENCE route_id_seq OWNED BY route.id;
1030
1031ALTER TABLE ONLY route ALTER COLUMN id SET DEFAULT nextval('route_id_seq'::regclass);
1032
1033create table airport(
1034 id integer not null,
1035 address_id integer not null,
1036 constraint pk_airport primary key (id),
1037 constraint fk_airport_address foreign key (address_id) references address(id)
1038);
1039
1040CREATE SEQUENCE airport_id_seq
1041 START WITH 1
1042 INCREMENT BY 1
1043 NO MINVALUE
1044 NO MAXVALUE
1045 CACHE 1;
1046
1047ALTER SEQUENCE airport_id_seq OWNED BY airport.id;
1048
1049ALTER TABLE ONLY airport ALTER COLUMN id SET DEFAULT nextval('airport_id_seq'::regclass);
1050
1051create table airport_wording(
1052 airport_id integer not null,
1053 lang_id integer not null,
1054 value character varying(255),
1055 constraint pk_airport_wording primary key (airport_id,lang_id),
1056 constraint fk_airport_wording_airport foreign key (airport_id) references airport(id),
1057 constraint fk_airport_wording_lang foreign key (lang_id) references lang(id)
1058);
1059
1060create table harbor(
1061 id integer not null,
1062 address_id integer not null,
1063 constraint pk_harbor primary key (id),
1064 constraint fk_harbor_address foreign key (address_id) references address(id)
1065);
1066
1067CREATE SEQUENCE harbor_id_seq
1068 START WITH 1
1069 INCREMENT BY 1
1070 NO MINVALUE
1071 NO MAXVALUE
1072 CACHE 1;
1073
1074ALTER SEQUENCE harbor_id_seq OWNED BY harbor.id;
1075
1076ALTER TABLE ONLY harbor ALTER COLUMN id SET DEFAULT nextval('harbor_id_seq'::regclass);
1077
1078create table harbor_wording(
1079 harbor_id integer not null,
1080 lang_id integer not null,
1081 value character varying(255),
1082 constraint pk_harbor_wording primary key (harbor_id,lang_id),
1083 constraint fk_harbor_wording_harbor foreign key (harbor_id) references harbor(id),
1084 constraint fk_harbor_wording_lang foreign key (lang_id) references lang(id)
1085);
1086
1087create table warehouse(
1088 id integer not null,
1089 address_id integer not null,
1090 constraint pk_warehouse primary key (id),
1091 constraint fk_warehouse_address foreign key (address_id) references address(id)
1092);
1093
1094CREATE SEQUENCE warehouse_id_seq
1095 START WITH 1
1096 INCREMENT BY 1
1097 NO MINVALUE
1098 NO MAXVALUE
1099 CACHE 1;
1100
1101ALTER SEQUENCE warehouse_id_seq OWNED BY warehouse.id;
1102
1103ALTER TABLE ONLY warehouse ALTER COLUMN id SET DEFAULT nextval('warehouse_id_seq'::regclass);
1104
1105create table warehouse_wording(
1106 warehouse_id integer not null,
1107 lang_id integer not null,
1108 value character varying(255),
1109 constraint pk_warehouse_wording primary key (warehouse_id,lang_id),
1110 constraint fk_warehouse_wording_harbor foreign key (warehouse_id) references warehouse(id),
1111 constraint fk_warehouse_wording_lang foreign key (lang_id) references lang(id)
1112);
1113
1114create table train_station(
1115 id integer not null,
1116 address_id integer not null,
1117 constraint pk_train_station primary key (id),
1118 constraint fk_train_station_address foreign key (address_id) references address(id)
1119);
1120
1121CREATE SEQUENCE train_station_id_seq
1122 START WITH 1
1123 INCREMENT BY 1
1124 NO MINVALUE
1125 NO MAXVALUE
1126 CACHE 1;
1127
1128ALTER SEQUENCE train_station_id_seq OWNED BY train_station.id;
1129
1130ALTER TABLE ONLY train_station ALTER COLUMN id SET DEFAULT nextval('train_station_id_seq'::regclass);
1131
1132create table train_station_wording(
1133 train_station_id integer not null,
1134 lang_id integer not null,
1135 value character varying(255),
1136 constraint pk_train_station_wording primary key (train_station_id,lang_id),
1137 constraint fk_train_station_wording_train_station foreign key (train_station_id) references train_station(id),
1138 constraint fk_train_station_wording_lang foreign key (lang_id) references lang(id)
1139);
1140
1141alter table city add column town_hall_contact_id integer;
1142alter table city add constraint fk_city_town_hall_contact foreign key (town_hall_contact_id) references person(id);
1143
1144create table postal_code(
1145 id integer not null,
1146 postal_code character varying(255),
1147 country_id integer,
1148 constraint pk_postal_code primary key (id),
1149 constraint fk_postal_code_country foreign key (country_id) references country(id)
1150);
1151
1152CREATE SEQUENCE postal_code_id_seq
1153 START WITH 1
1154 INCREMENT BY 1
1155 NO MINVALUE
1156 NO MAXVALUE
1157 CACHE 1;
1158
1159ALTER SEQUENCE postal_code_id_seq OWNED BY postal_code.id;
1160
1161ALTER TABLE ONLY postal_code ALTER COLUMN id SET DEFAULT nextval('postal_code_id_seq'::regclass);
1162
1163create table city_postal_code(
1164 city_id integer not null,
1165 postal_code_id integer not null,
1166 constraint pk_city_postal_code primary key (city_id,postal_code_id),
1167 constraint fk_city_postal_code_city foreign key (city_id) references city(id),
1168 constraint fk_city_postal_code_postal_code foreign key (postal_code_id) references postal_code(id)
1169);
1170
1171
1172create table country_lang(
1173 country_id integer not null,
1174 lang_id integer not null,
1175 constraint pk_country_lang primary key (country_id,lang_id),
1176 constraint fk_country_lang_country foreign key (country_id) references country(id),
1177 constraint fk_country_lang_lang foreign key (lang_id) references lang(id)
1178);
1179
1180alter table ville add column city_id integer;
1181alter table ville add constraint fk_ville_city foreign key (city_id) references city(id);
1182
1183
1184
1185-------------------------------------------------------------------------------------------------------------
1186drop table if exists statistic_condition;
1187drop table if exists statistic_filter;
1188drop table if exists statistic_group;
1189drop table if exists statistic_column;
1190drop table if exists modular_statistic;
1191
1192create table modular_statistic(
1193 id integer not null,
1194 wording character varying(255),
1195 concerned_object character varying(255),
1196 display_type character varying(255),
1197 path_properties character varying(255),
1198 constraint pk_modular_statistic primary key (id)
1199);
1200
1201CREATE SEQUENCE modular_statistic_id_seq
1202 START WITH 1
1203 INCREMENT BY 1
1204 NO MINVALUE
1205 NO MAXVALUE
1206 CACHE 1;
1207
1208ALTER SEQUENCE modular_statistic_id_seq OWNED BY modular_statistic.id;
1209
1210ALTER TABLE ONLY modular_statistic ALTER COLUMN id SET DEFAULT nextval('modular_statistic_id_seq'::regclass);
1211
1212create table statistic_filter(
1213 id integer not null,
1214 wording character varying(255),
1215 field character varying(255),
1216 field_type character varying(255),
1217 filter_type character varying(255),
1218 operator character varying(255),
1219 values character varying(255)[],
1220 multiple boolean,
1221 option_value_field character varying(255),
1222 option_text_field character varying(255),
1223 path_properties character varying(255),
1224 rank integer,
1225 statistic_id integer,
1226 constraint pk_statistic_filter primary key (id),
1227 constraint fk_statistic_filter_modular_statistic foreign key (statistic_id) references modular_statistic(id)
1228);
1229
1230CREATE SEQUENCE statistic_filter_id_seq
1231 START WITH 1
1232 INCREMENT BY 1
1233 NO MINVALUE
1234 NO MAXVALUE
1235 CACHE 1;
1236
1237ALTER SEQUENCE statistic_filter_id_seq OWNED BY statistic_filter.id;
1238
1239ALTER TABLE ONLY statistic_filter ALTER COLUMN id SET DEFAULT nextval('statistic_filter_id_seq'::regclass);
1240
1241create table statistic_group(
1242 id integer not null,
1243 wording character varying(255),
1244 value character varying(255),
1245 text character varying(255),
1246 rank integer,
1247 statistic_id integer,
1248 constraint pk_statistic_group primary key (id),
1249 constraint fk_statistic_group_modular_statistic foreign key (statistic_id) references modular_statistic(id)
1250);
1251
1252CREATE SEQUENCE statistic_group_id_seq
1253 START WITH 1
1254 INCREMENT BY 1
1255 NO MINVALUE
1256 NO MAXVALUE
1257 CACHE 1;
1258
1259ALTER SEQUENCE statistic_group_id_seq OWNED BY statistic_group.id;
1260
1261ALTER TABLE ONLY statistic_group ALTER COLUMN id SET DEFAULT nextval('statistic_group_id_seq'::regclass);
1262
1263create table statistic_column(
1264 id integer not null,
1265 wording character varying(255),
1266 field character varying(255),
1267 operator character varying(255),
1268 formula character varying(255),
1269 generatedCode character varying(255),
1270 rank integer,
1271 statistic_id integer,
1272 constraint pk_statistic_column primary key (id),
1273 constraint fk_statistic_column_modular_statistic foreign key (statistic_id) references modular_statistic(id)
1274);
1275
1276CREATE SEQUENCE statistic_column_id_seq
1277 START WITH 1
1278 INCREMENT BY 1
1279 NO MINVALUE
1280 NO MAXVALUE
1281 CACHE 1;
1282
1283ALTER SEQUENCE statistic_column_id_seq OWNED BY statistic_column.id;
1284
1285ALTER TABLE ONLY statistic_column ALTER COLUMN id SET DEFAULT nextval('statistic_column_id_seq'::regclass);
1286
1287create table statistic_condition(
1288 id integer not null,
1289 field character varying(255),
1290 condition character varying(255),
1291 values character varying(255)[],
1292 column_id integer,
1293 filter_id integer,
1294 constraint pk_statistic_condition primary key (id),
1295 constraint fk_statistic_condition_column foreign key (column_id) references statistic_column(id),
1296 constraint fk_statistic_condition_filter foreign key (filter_id) references statistic_filter(id)
1297);
1298
1299CREATE SEQUENCE statistic_condition_id_seq
1300 START WITH 1
1301 INCREMENT BY 1
1302 NO MINVALUE
1303 NO MAXVALUE
1304 CACHE 1;
1305
1306ALTER SEQUENCE statistic_condition_id_seq OWNED BY statistic_condition.id;
1307
1308ALTER TABLE ONLY statistic_condition ALTER COLUMN id SET DEFAULT nextval('statistic_condition_id_seq'::regclass);
1309
1310
1311create table displayed_table_column(
1312 id integer not null,
1313 wording character varying(255),
1314 text character varying(255),
1315 rank integer,
1316 statistic_id integer,
1317 constraint pk_displayed_table_column primary key (id),
1318 constraint fk_displayed_table_column_modular_statistic foreign key (statistic_id) references modular_statistic(id)
1319);
1320
1321CREATE SEQUENCE displayed_table_column_id_seq
1322 START WITH 1
1323 INCREMENT BY 1
1324 NO MINVALUE
1325 NO MAXVALUE
1326 CACHE 1;
1327
1328ALTER SEQUENCE displayed_table_column_id_seq OWNED BY displayed_table_column.id;
1329
1330ALTER TABLE ONLY displayed_table_column ALTER COLUMN id SET DEFAULT nextval('displayed_table_column_id_seq'::regclass);