data/pgsql/v3.3.0-dev.sql

--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.3.0-dev
--
-- @package openfoncier
-- @version SVN : $Id$
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- OPENMAIRIE-EXEMPLE v4.4.0
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- bugs visibility dans om_sig_map

ALTER TABLE om_sig_map_wms ALTER visibility DROP NOT NULL;

--------------------------------------------------------------------------------
-- GESTION DES REQUETES POUR LES ETATS ET LETTRES TYPE
--------------------------------------------------------------------------------
-- Création de la table
CREATE TABLE om_requete(
    id integer NOT NULL,
    code character varying(50) NOT NULL,
    libelle character varying(100) NOT NULL,
    description character varying(200),
    requete text,
    merge_fields text
);
-- Création de la séquence
CREATE SEQUENCE om_requete_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
-- Clé primaire
ALTER TABLE ONLY om_requete
    ADD CONSTRAINT om_requete_pkey PRIMARY KEY (id);
-- Récupération de toutes les requêtes existantes dans les tables om_etat et
-- om_lettretype
insert into om_requete (id, code, libelle, requete)
(
select nextval('om_requete_seq'), '-', 'Requête SQL', lib1
from (select om_sql as lib1, om_sql
      from om_lettretype group by om_sql
      union
      select om_sql as lib1, om_sql
      from om_etat group by om_sql
      order by lib1) as sql1
group by lib1
);
update om_etat set om_sql=(select om_requete.id from om_requete where om_requete.requete=om_etat.om_sql);
update om_lettretype set om_sql=(select om_requete.id from om_requete where om_requete.requete=om_lettretype.om_sql);
alter table om_etat alter column om_sql TYPE integer USING om_sql::integer;
alter table om_lettretype alter column om_sql TYPE integer USING om_sql::integer;
-- Clés étrangères
ALTER TABLE ONLY om_etat
    ADD CONSTRAINT om_etat_om_requete_fkey FOREIGN KEY (om_sql) REFERENCES om_requete(id);
ALTER TABLE ONLY om_lettretype
    ADD CONSTRAINT om_lettretype_om_requete_fkey FOREIGN KEY (om_sql) REFERENCES om_requete(id);
-- Permission
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_requete',
(select om_profil from om_profil where libelle='ADMINISTRATEUR TECHNIQUE'));
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- GESTION DES LOGOS POUR LES ETATS ET LETTRES TYPE
--------------------------------------------------------------------------------
-- Création de la table
CREATE TABLE om_logo (
    om_logo integer,
    id character varying(50) NOT NULL,
    libelle character varying(100) NOT NULL,
    description character varying(200),
    fichier character varying(100) NOT NULL,
    resolution integer,
    actif boolean,
    om_collectivite integer NOT NULL
);
-- Création de la séquence
CREATE SEQUENCE om_logo_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
-- Clé primaire
ALTER TABLE ONLY om_logo
    ADD CONSTRAINT om_logo_pkey PRIMARY KEY (om_logo);
-- Clé étrangère
ALTER TABLE ONLY om_logo
    ADD CONSTRAINT om_logo_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
-- Récupération de toutes les requêtes existantes dans les tables om_etat et
-- om_lettretype
insert into om_logo (om_logo, id, libelle, fichier, actif, om_collectivite)
(
select nextval('om_logo_seq'), lib1, lib1, lib1, true, 1
from (select logo as lib1, logo
      from om_lettretype group by logo
      union
      select logo as lib1, logo
      from om_etat group by logo
      order by lib1) as sql1
group by lib1
);
--update om_etat set logo=(select om_logo.id from om_logo where om_logo.fichier=om_etat.logo);
--update om_lettretype set logo=(select om_logo.id from om_logo where om_logo.fichier=om_lettretype.logo);
--alter table om_etat alter column logo TYPE integer USING logo::integer;
--alter table om_lettretype alter column logo TYPE integer USING logo::integer;
-- Clés étrangères
--ALTER TABLE ONLY om_etat
--    ADD CONSTRAINT om_etat_om_logo_fkey FOREIGN KEY (logo) REFERENCES om_logo(id);
--ALTER TABLE ONLY om_lettretype
--    ADD CONSTRAINT om_lettretype_om_logo_fkey FOREIGN KEY (logo) REFERENCES om_logo(id);
-- Permission
INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_logo',
(select om_profil from om_profil where libelle='ADMINISTRATEUR TECHNIQUE'));
--------------------------------------------------------------------------------
alter table om_etat drop column footerfont;
alter table om_etat drop column footerattribut;
alter table om_etat drop column footertaille;
alter table om_etat alter column sousetat drop not null;
alter table om_etat alter column logo drop not null;
alter table om_lettretype alter column logo drop not null;
ALTER TABLE om_etat ALTER COLUMN libelle TYPE character varying(100);
ALTER TABLE om_sousetat ALTER COLUMN libelle TYPE character varying(100);
ALTER TABLE om_lettretype ALTER COLUMN libelle TYPE character varying(100);


--------------------------------------------------------------------------------


ALTER TABLE bible ADD CONSTRAINT
    bible_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type (dossier_autorisation_type);


1	--------------------------------------------------------------------------------
2	-- Script de mise à jour vers la version v3.3.0-dev
3	--
4	-- @package openfoncier
5	-- @version SVN : $Id$
6	--------------------------------------------------------------------------------
7
8	--------------------------------------------------------------------------------
9	--------------------------------------------------------------------------------
10	-- OPENMAIRIE-EXEMPLE v4.4.0
11	--------------------------------------------------------------------------------
12	--------------------------------------------------------------------------------
13	-- bugs visibility dans om_sig_map
14
15	ALTER TABLE om_sig_map_wms ALTER visibility DROP NOT NULL;
16
17	--------------------------------------------------------------------------------
18	-- GESTION DES REQUETES POUR LES ETATS ET LETTRES TYPE
19	--------------------------------------------------------------------------------
20	-- Création de la table
21	CREATE TABLE om_requete(
22	id integer NOT NULL,
23	code character varying(50) NOT NULL,
24	libelle character varying(100) NOT NULL,
25	description character varying(200),
26	requete text,
27	merge_fields text
28	);
29	-- Création de la séquence
30	CREATE SEQUENCE om_requete_seq
31	START WITH 1
32	INCREMENT BY 1
33	NO MINVALUE
34	NO MAXVALUE
35	CACHE 1;
36	-- Clé primaire
37	ALTER TABLE ONLY om_requete
38	ADD CONSTRAINT om_requete_pkey PRIMARY KEY (id);
39	-- Récupération de toutes les requêtes existantes dans les tables om_etat et
40	-- om_lettretype
41	insert into om_requete (id, code, libelle, requete)
42	(
43	select nextval('om_requete_seq'), '-', 'Requête SQL', lib1
44	from (select om_sql as lib1, om_sql
45	from om_lettretype group by om_sql
46	union
47	select om_sql as lib1, om_sql
48	from om_etat group by om_sql
49	order by lib1) as sql1
50	group by lib1
51	);
52	update om_etat set om_sql=(select om_requete.id from om_requete where om_requete.requete=om_etat.om_sql);
53	update om_lettretype set om_sql=(select om_requete.id from om_requete where om_requete.requete=om_lettretype.om_sql);
54	alter table om_etat alter column om_sql TYPE integer USING om_sql::integer;
55	alter table om_lettretype alter column om_sql TYPE integer USING om_sql::integer;
56	-- Clés étrangères
57	ALTER TABLE ONLY om_etat
58	ADD CONSTRAINT om_etat_om_requete_fkey FOREIGN KEY (om_sql) REFERENCES om_requete(id);
59	ALTER TABLE ONLY om_lettretype
60	ADD CONSTRAINT om_lettretype_om_requete_fkey FOREIGN KEY (om_sql) REFERENCES om_requete(id);
61	-- Permission
62	INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_requete',
63	(select om_profil from om_profil where libelle='ADMINISTRATEUR TECHNIQUE'));
64	--------------------------------------------------------------------------------
65
66	--------------------------------------------------------------------------------
67	-- GESTION DES LOGOS POUR LES ETATS ET LETTRES TYPE
68	--------------------------------------------------------------------------------
69	-- Création de la table
70	CREATE TABLE om_logo (
71	om_logo integer,
72	id character varying(50) NOT NULL,
73	libelle character varying(100) NOT NULL,
74	description character varying(200),
75	fichier character varying(100) NOT NULL,
76	resolution integer,
77	actif boolean,
78	om_collectivite integer NOT NULL
79	);
80	-- Création de la séquence
81	CREATE SEQUENCE om_logo_seq
82	START WITH 1
83	INCREMENT BY 1
84	NO MINVALUE
85	NO MAXVALUE
86	CACHE 1;
87	-- Clé primaire
88	ALTER TABLE ONLY om_logo
89	ADD CONSTRAINT om_logo_pkey PRIMARY KEY (om_logo);
90	-- Clé étrangère
91	ALTER TABLE ONLY om_logo
92	ADD CONSTRAINT om_logo_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
93	-- Récupération de toutes les requêtes existantes dans les tables om_etat et
94	-- om_lettretype
95	insert into om_logo (om_logo, id, libelle, fichier, actif, om_collectivite)
96	(
97	select nextval('om_logo_seq'), lib1, lib1, lib1, true, 1
98	from (select logo as lib1, logo
99	from om_lettretype group by logo
100	union
101	select logo as lib1, logo
102	from om_etat group by logo
103	order by lib1) as sql1
104	group by lib1
105	);
106	--update om_etat set logo=(select om_logo.id from om_logo where om_logo.fichier=om_etat.logo);
107	--update om_lettretype set logo=(select om_logo.id from om_logo where om_logo.fichier=om_lettretype.logo);
108	--alter table om_etat alter column logo TYPE integer USING logo::integer;
109	--alter table om_lettretype alter column logo TYPE integer USING logo::integer;
110	-- Clés étrangères
111	--ALTER TABLE ONLY om_etat
112	-- ADD CONSTRAINT om_etat_om_logo_fkey FOREIGN KEY (logo) REFERENCES om_logo(id);
113	--ALTER TABLE ONLY om_lettretype
114	-- ADD CONSTRAINT om_lettretype_om_logo_fkey FOREIGN KEY (logo) REFERENCES om_logo(id);
115	-- Permission
116	INSERT INTO om_droit VALUES (nextval('om_droit_seq'), 'om_logo',
117	(select om_profil from om_profil where libelle='ADMINISTRATEUR TECHNIQUE'));
118	--------------------------------------------------------------------------------
119	alter table om_etat drop column footerfont;
120	alter table om_etat drop column footerattribut;
121	alter table om_etat drop column footertaille;
122	alter table om_etat alter column sousetat drop not null;
123	alter table om_etat alter column logo drop not null;
124	alter table om_lettretype alter column logo drop not null;
125	ALTER TABLE om_etat ALTER COLUMN libelle TYPE character varying(100);
126	ALTER TABLE om_sousetat ALTER COLUMN libelle TYPE character varying(100);
127	ALTER TABLE om_lettretype ALTER COLUMN libelle TYPE character varying(100);
128
129
130	--------------------------------------------------------------------------------
131
132
133
134
135	ALTER TABLE bible ADD CONSTRAINT
136	bible_dossier_autorisation_type_fkey FOREIGN KEY (dossier_autorisation_type) REFERENCES dossier_autorisation_type (dossier_autorisation_type);
137
138