Changeset 1490 for trunk/LATMOS-Accounts/bin/la-sql-upgrade.in

Timestamp:

12/01/15 09:25:48 (9 years ago)

Author:

nanardon

Message:

Add filter based aliases (dynamics)

File:

• trunk/LATMOS-Accounts/bin/la-sql-upgrade.in (modified) (1 diff)

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

@@ -799 +799 @@
                 USING btree
                   (lastday NULLS LAST);
-        ],
-    },
+            },
+        ],
+    },
+    {
+        ver => 16,
+        sql => [
+            q{
+            ALTER TABLE aliases
+              ADD CONSTRAINT aliases_ikey_uniq UNIQUE(ikey);
+            CREATE OR REPLACE FUNCTION aliases_attr_update_ref()
+            RETURNS trigger AS
+            $BODY$begin
+
+            IF (TG_OP != 'INSERT') then
+            update "aliases" set date = now() where "aliases".ikey = old.okey;
+            end if;
+            IF (TG_OP != 'DELETE') then
+            update "aliases"  set date = now() where "aliases".ikey = new.okey;
+            end if;
+
+            IF (TG_OP = 'DELETE') then
+            return old;
+            ELSE
+            return new;
+            END IF;
+
+            END;$BODY$
+            LANGUAGE plpgsql VOLATILE
+            COST 100;
+            },
+            q{
+            CREATE TABLE aliases_attributes_base
+            (
+            CONSTRAINT aliases_attributes_base_pkey PRIMARY KEY (attr_key),
+            CONSTRAINT aliases_fkey FOREIGN KEY (okey)
+            REFERENCES aliases (ikey) MATCH SIMPLE
+            ON UPDATE CASCADE ON DELETE CASCADE
+            )
+            INHERITS (attributes);
+            CREATE INDEX aliases_attributes_base_attr_fkey
+            ON aliases_attributes_base
+            USING btree
+            (attr);
+            CREATE INDEX aliases_attr_value_idx
+            ON aliases_attributes_base
+            USING btree
+            (value);
+            CREATE TRIGGER aliases_attr_update
+            AFTER INSERT OR UPDATE OR DELETE
+            ON aliases_attributes_base
+            FOR EACH ROW
+            EXECUTE PROCEDURE aliases_attr_update_ref();
+            },
+            q{
+            CREATE OR REPLACE VIEW aliases_attributes AS 
+            SELECT "user".name AS value,
+            'user'::text AS attr,
+            aliases.rev AS attr_key,
+            aliases.ikey AS okey
+            FROM aliases
+            JOIN "user" ON aliases.name = "user".name OR aliases.forward = ARRAY["user".name]
+            union all
+               select value, attr, attr_key, okey from aliases_attributes_base
+            UNION ALL
+                SELECT "aliases".name AS value,
+                'name'::text AS attr,
+                "aliases".rev AS attr_key,
+                "aliases".ikey AS okey
+                FROM "aliases";
+
+           CREATE OR REPLACE RULE aliases_attributes_insert AS
+           ON INSERT TO aliases_attributes DO INSTEAD  INSERT INTO aliases_attributes_base (value, attr, okey)
+           VALUES (new.value, new.attr, new.okey);
+
+           CREATE OR REPLACE RULE aliases_attributes_update AS
+           ON UPDATE TO aliases_attributes DO INSTEAD  UPDATE aliases_attributes_base SET value = new.value, attr = new.attr, okey = new.okey
+           WHERE aliases_attributes_base.attr_key = old.attr_key AND aliases_attributes_base.attr = old.attr;
+
+           CREATE OR REPLACE RULE aliases_atttributes_delete AS
+           ON DELETE TO aliases_attributes DO INSTEAD  DELETE FROM aliases_attributes_base
+           WHERE aliases_attributes_base.attr_key = old.attr_key AND aliases_attributes_base.attr = old.attr;
+            }
+
+        ],
+    }
 );