Changeset 947 for LATMOS-Accounts

Timestamp:

04/26/12 18:58:34 (12 years ago)

Author:

nanardon

Message:

• réellement modifier la-sql-upgrade

File:

• LATMOS-Accounts/bin/la-sql-upgrade.in (modified) (4 diffs)

LATMOS-Accounts/bin/la-sql-upgrade.in

@@ -59 +59 @@
         ver => 3,
         sql => [
-        q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT
+            q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT
             nethost_is_single_ip},
-        q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON nethost},
-        q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON netzone},
-        q{DROP FUNCTION IF EXISTS check_zone_name_unity()},
-        q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT
-          nethost_is_single_ip},
-        q{INSERT INTO settings(varname, val) VALUES ('schema_version', 3)},
+            q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON nethost},
+            q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON netzone},
+            q{DROP FUNCTION IF EXISTS check_zone_name_unity()},
+            q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT
+            nethost_is_single_ip},
+            q{INSERT INTO settings(varname, val) VALUES ('schema_version', 3)},
         ],
     },
@@ -74 +74 @@
             q{
             CREATE OR REPLACE FUNCTION nethost_sort_fields()
-              RETURNS trigger AS
-              $BODY$BEGIN
-
-              IF (TG_OP='INSERT') then
-              IF (new.attr='ip') THEN
-              insert into nethost_attributes_ips VALUES (new.*);
-              RETURN NULL;
-              END IF;
-
-              IF (new.attr='macaddr') THEN
-              insert into nethost_attributes_macs VALUES (new.*);
-              RETURN NULL;
-              END IF;
-
-              IF (new.attr='owner') THEN
-              insert into nethost_attributes_users VALUES (new.*);
-              RETURN NULL;
-              END IF;
-
-              IF (new.attr='user') THEN
-              insert into nethost_attributes_users VALUES (new.*);
-              RETURN NULL;
-              END IF;
-              end if;
-
-
-              if (TG_OP='DELETE') THEN
-              RETURN old;
-              else
-              RETURN new;
-              end if;
-              END;$BODY$
+            RETURNS trigger AS
+            $BODY$BEGIN
+
+            IF (TG_OP='INSERT') then
+            IF (new.attr='ip') THEN
+            insert into nethost_attributes_ips VALUES (new.*);
+            RETURN NULL;
+            END IF;
+
+            IF (new.attr='macaddr') THEN
+            insert into nethost_attributes_macs VALUES (new.*);
+            RETURN NULL;
+            END IF;
+
+            IF (new.attr='owner') THEN
+            insert into nethost_attributes_users VALUES (new.*);
+            RETURN NULL;
+            END IF;
+
+            IF (new.attr='user') THEN
+            insert into nethost_attributes_users VALUES (new.*);
+            RETURN NULL;
+            END IF;
+            end if;
+
+
+            if (TG_OP='DELETE') THEN
+            RETURN old;
+            else
+            RETURN new;
+            end if;
+            END;$BODY$
             LANGUAGE plpgsql VOLATILE
             COST 100;
@@ -111 +111 @@
         ],
     },
+    {
+        ver => 5,
+        sql => [
+            q{
+            CREATE OR REPLACE VIEW address_attributes AS 
+            ( ( SELECT address."user" AS value, 'user' AS attr, address.rev AS attr_key, address.ikey AS okey
+            FROM address
+            UNION ALL 
+            SELECT address.name AS value, 'name' AS attr, address.rev AS attr_key, address.ikey AS okey
+            FROM address )
+            UNION ALL
+            SELECT address_attributes.value, address_attributes.attr, address_attributes.attr_key, address_attributes.okey
+            FROM address_attributes_base address_attributes )
+            UNION ALL
+            SELECT '1' AS value, 'active' AS attr, address.rev AS attr_key, address.ikey AS okey
+            FROM "user" JOIN address ON "user".name = address."user"
+            WHERE "user".exported AND address.exported and 
+            ("user".expire IS NULL or "user".expire > now())
+            }
+        ],
+    },
+    {
+        ver => 6,
+        sql => [
+            q{
+            CREATE TABLE request
+            (
+                id serial NOT NULL,
+                "create" timestamp with time zone NOT NULL DEFAULT now(),
+                name text NOT NULL,
+                object text,
+                apply timestamp with time zone NOT NULL DEFAULT now(),
+                done timestamp with time zone,
+                "user" text,
+                CONSTRAINT request_pkey PRIMARY KEY (id ),
+                CONSTRAINT request_name_fkey FOREIGN KEY (name)
+                REFERENCES accreq (name) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE,
+                CONSTRAINT request_user_fkey FOREIGN KEY ("user")
+                REFERENCES "user" (name) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE SET NULL
+            )
+            },
+            q{
+            CREATE INDEX fki_request_name_fkey ON request
+            USING btree (name )
+            },
+            q{
+            CREATE INDEX fki_request_user_fkey ON request
+            USING btree ("user" )
+            },
+            q{
+            CREATE TABLE request_attributes
+            (
+                reqid bigint,
+                attribute text NOT NULL,
+                value text,
+                CONSTRAINT request_id_fkey FOREIGN KEY (reqid)
+                REFERENCES request (id) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+            )
+            },
+            q{
+            CREATE INDEX fki_request_id_fkey ON request_attributes
+            USING btree (reqid )
+            },
+            q{
+            delete from accreq_attributes_list
+            }
+        ],
+    }
 );
 
@@ -155 +226 @@
 while (my $row = $csv->getline($fh)) { 
     my ($otype, $attribute, $comment) = @$row; 
-    if ($labase->attribute($otype, $attribute)) { 
+    if ($labase->is_registered_attribute($otype, $attribute)) { 
     } else { 
         $labase->register_attribute($otype, $attribute, $comment)