Changeset 1796 for trunk/LATMOS-Accounts/bin

Timestamp:

06/28/16 11:44:21 (8 years ago)

Author:

nanardon

Message:

Add status attribute to user object

File:

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

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

@@ -112 +112 @@
         sql => [
             q{
-            CREATE OR REPLACE VIEW address_attributes AS 
+            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 
+            UNION ALL
             SELECT address.name AS value, 'name' AS attr, address.rev AS attr_key, address.ikey AS okey
             FROM address )
@@ -124 +124 @@
             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 
+            WHERE "user".exported AND address.exported and
             ("user".expire IS NULL or "user".expire > now())
             }
@@ -523 +523 @@
         sql => [
             q{
-            CREATE OR REPLACE VIEW user_attributes AS 
+            CREATE OR REPLACE VIEW user_attributes AS
             (        (        (        (        (        (        (        (        (        (        (
             SELECT "user".uidnumber::text AS value, 'uidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user"
-            UNION ALL 
+            UNION ALL
             SELECT "user".gidnumber::text AS value, 'gidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user")
-            UNION ALL 
+            UNION ALL
             SELECT "user".name AS value, 'name' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user")
-            UNION ALL 
+            UNION ALL
             SELECT "user".expire::text AS value, 'expire' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user"
             WHERE "user".expire IS NOT NULL)
-            UNION ALL 
+            UNION ALL
             SELECT '1'::text AS value, 'exported' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "user"
             WHERE "user".exported = true)
-            UNION ALL 
+            UNION ALL
             SELECT address_attributes_site.value, 'allsite' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "user"
@@ -543 +543 @@
             JOIN address_attributes_site ON address_attributes_site.okey = address.ikey
             WHERE address_attributes_site.attr = 'site'::text AND address.exported = true)
-            UNION ALL 
+            UNION ALL
             SELECT "group".name AS value, 'memberOf' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "user"
@@ -549 +549 @@
             JOIN "group" ON group_attributes.okey = "group".ikey
             WHERE group_attributes.attr = 'memberUID'::text)
-            UNION ALL 
+            UNION ALL
             SELECT user_attributes.value, user_attributes.attr, user_attributes.attr_key, user_attributes.okey
             FROM user_attributes_base user_attributes)
-            UNION ALL 
+            UNION ALL
             SELECT "group".name AS value, 'departments' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "group"
@@ -559 +559 @@
             JOIN "user" ON "user".name = group_attributes_users.value
             WHERE group_attributes_sutypes.value = 'dpmt'::text AND group_attributes_sutypes.attr = 'sutype'::text)
-            UNION ALL 
+            UNION ALL
             SELECT "group".name AS value, 'cells' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "group"
@@ -566 +566 @@
             JOIN "user" ON "user".name = group_attributes_users.value
             WHERE group_attributes_sutypes.value = 'cell'::text AND group_attributes_sutypes.attr = 'sutype'::text)
-            UNION ALL 
+            UNION ALL
             SELECT "group".name AS value, 'managedObjects' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "user"
@@ -572 +572 @@
             JOIN "group" ON group_attributes.okey = "group".ikey
             WHERE group_attributes.attr = 'managedBy'::text)
-            UNION ALL 
+            UNION ALL
             SELECT justify_interval(now() - "user".expire)::text AS value, 'expired' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "user"
             WHERE "user".expire <= now())
-            UNION ALL 
+            UNION ALL
             SELECT 1::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "user"
             WHERE ("user".expire IS NULL OR "user".expire >= now()) AND "user".exported = true
-            UNION ALL 
+            UNION ALL
             SELECT 0::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
             FROM "user"
-            WHERE ("user".expire IS NOT NULL and "user".expire <= now()) or "user".exported = false; 
+            WHERE ("user".expire IS NOT NULL and "user".expire <= now()) or "user".exported = false;
             },
             q{
@@ -853 +853 @@
             },
             q{
-            CREATE OR REPLACE VIEW aliases_attributes AS 
+            CREATE OR REPLACE VIEW aliases_attributes AS
             SELECT "user".name AS value,
             'user'::text AS attr,
@@ -922 +922 @@
             q{
             ALTER TABLE "nethost" ADD COLUMN expire timestamp with time zone;
+            }
+        ],
+    },
+    {
+        var => 20,
+        sql => [
+            q{
+            CREATE OR REPLACE VIEW user_attributes AS
+                SELECT "user".uidnumber::text AS value, 'uidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+            UNION ALL
+                SELECT "user".gidnumber::text AS value, 'gidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+            UNION ALL
+                SELECT "user".name AS value, 'name' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+            UNION ALL
+                SELECT "user".expire::text AS value, 'expire' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                WHERE "user".expire IS NOT NULL
+            UNION ALL
+                SELECT '1'::text AS value, 'exported' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                WHERE "user".exported = true
+            UNION ALL
+                SELECT address_attributes_site.value, 'allsite' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                JOIN address ON address."user" = "user".name
+                JOIN address_attributes_site ON address_attributes_site.okey = address.ikey
+                WHERE address_attributes_site.attr = 'site'::text AND address.exported = true
+            UNION ALL
+                SELECT "group".name AS value, 'memberOf' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                JOIN group_attributes ON group_attributes.value = "user".name
+                JOIN "group" ON group_attributes.okey = "group".ikey
+                WHERE group_attributes.attr = 'memberUID'::text
+            UNION ALL
+                SELECT user_attributes.value, user_attributes.attr, user_attributes.attr_key, user_attributes.okey
+                FROM user_attributes_base user_attributes
+            UNION ALL
+                SELECT "group".name AS value, 'departments' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "group"
+                JOIN group_attributes_sutypes ON "group".ikey = group_attributes_sutypes.okey
+                JOIN group_attributes_users ON "group".ikey = group_attributes_users.okey AND group_attributes_users.attr = 'memberUID'::text
+                JOIN "user" ON "user".name = group_attributes_users.value
+                WHERE group_attributes_sutypes.value = 'dpmt'::text AND group_attributes_sutypes.attr = 'sutype'::text
+            UNION ALL
+                SELECT "group".name AS value, 'cells' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "group"
+                JOIN group_attributes_sutypes ON "group".ikey = group_attributes_sutypes.okey
+                JOIN group_attributes_users ON "group".ikey = group_attributes_users.okey AND group_attributes_users.attr = 'memberUID'::text
+                JOIN "user" ON "user".name = group_attributes_users.value
+                WHERE group_attributes_sutypes.value = 'cell'::text AND group_attributes_sutypes.attr = 'sutype'::text
+            UNION ALL
+                SELECT "group".name AS value, 'managedObjects' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                JOIN group_attributes ON group_attributes.value = "user".name
+                JOIN "group" ON group_attributes.okey = "group".ikey
+                WHERE group_attributes.attr = 'managedBy'::text
+            UNION ALL
+                SELECT justify_interval(now() - "user".expire)::text AS value, 'expired' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                WHERE "user".expire <= now()
+            UNION ALL
+                SELECT 1::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                WHERE ("user".expire IS NULL OR "user".expire >= now()) AND "user".exported = true
+            UNION ALL
+                SELECT 0::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+                WHERE "user".expire IS NOT NULL AND "user".expire <= now() OR "user".exported = false
+            UNION ALL
+                SELECT CASE WHEN "user".exported = false THEN 'unexported'
+                WHEN "user".expire IS NOT NULL AND "user".expire <= now() THEN 'expired'
+                ELSE 'active' END AS value, 'status' AS attr, "user".rev AS attr_key, "user".ikey AS okey
+                FROM "user"
+            ;
+
             }
         ],
@@ -992 +1070 @@
         }
     }
-} 
- 
+}
+
 foreach (@objects) {
     if (!$labase->get_object($_->{otype}, $_->{name})) {