Index: trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql.pm
===================================================================
--- trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql.pm	(revision 2082)
+++ trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql.pm	(revision 2083)
@@ -18,5 +18,5 @@
 our $VERSION = (q$Rev$ =~ /^Rev: (\d+) /)[0];
 
-sub SCHEMA_VERSION { 11 };
+sub SCHEMA_VERSION { 26 };
 
 =head1 NAME
@@ -98,4 +98,16 @@
         $self->log(LA_DEBUG, 'New connection to DB');
 
+        {
+            my $sv = $self->get_global_value('schema_version') || 1;
+            if ($sv < SCHEMA_VERSION) {
+                require LATMOS::Accounts::Bases::Sql::upgrade;
+                if ($self->SchemaUpgrade()) {
+                    $self->commit;
+                } else {
+                    $self->rollback;
+                    return;
+                }
+            }
+        }
 
         foreach my $otype ($self->list_supported_objects) {
Index: trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/upgrade.pm
===================================================================
--- trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/upgrade.pm	(revision 2083)
+++ trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/upgrade.pm	(revision 2083)
@@ -0,0 +1,1231 @@
+package LATMOS::Accounts::Bases::Sql::upgrade;
+
+package LATMOS::Accounts::Bases::Sql;
+
+use strict;
+use warnings;
+use LATMOS::Accounts::Log;
+
+=head1 NAME
+
+    LATMOS::Accounts::Bases::Sql::upgrade
+
+=head1 DESCRIPTION
+
+Upgrade the SQL schema for SQL Base
+
+=head1 FUNCTIONS
+
+=cut
+
+sub _SqlSchemaUpgrade {
+    my ($self) = @_;
+
+    my $rev = $self->get_global_value('schema_version') || 1;
+
+    my @updates = (
+        {
+            ver => 3,
+            sql => [
+                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)},
+            ],
+        },
+        {
+            ver => 4,
+            sql => [
+                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$
+                LANGUAGE plpgsql VOLATILE
+                COST 100;
+                },
+            ],
+        },
+        {
+            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
+                }
+            ],
+        },
+        {
+            ver => 7,
+            sql => [
+                q{
+                ALTER TABLE request ADD COLUMN automated boolean NOT NULL DEFAULT false;
+                },
+                q{
+                ALTER TABLE request ADD COLUMN objrev bigint;
+                },
+                q{
+                ALTER TABLE aliases ADD COLUMN description text;
+                },
+                q{
+                ALTER TABLE revaliases ADD COLUMN description text;
+                },
+                q{
+                CREATE OR REPLACE FUNCTION group_sort_fields()
+                RETURNS trigger AS
+                $BODY$BEGIN
+
+                IF (TG_OP='INSERT') then
+                IF (new.attr='managedBy'
+                OR  new.attr='managedAlsoBy'
+                OR  new.attr='member'
+                OR  new.attr='memberUID') THEN
+                insert into group_attributes_users VALUES (new.*);
+                RETURN NULL;
+                END IF;
+
+                IF (new.attr='sutype') THEN
+                insert into group_attributes_sutypes VALUES (new.*);
+                RETURN NULL;
+                END IF;
+                end if;
+
+
+                IF (TG_OP = 'UPDATE') THEN
+                IF (new.attr='gidNumber') then
+                update "group" set gidnumber = new.value::integer where
+                "group".ikey = new.okey;
+                RETURN NULL;
+                END IF;
+                END IF;
+
+                IF (TG_OP = 'DELETE') THEN
+                IF (old.attr='exported') then
+                update "group" set exported = false where "group".ikey = old.okey;
+                return null;
+                end if;
+                else
+                IF (new.attr='exported') then
+                update "group" set exported = true where "group".ikey = new.okey;
+                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;
+                },
+            ],
+        },
+        {
+            ver => 8,
+            sql => [
+                q{
+                CREATE TABLE objectslogs
+                (
+                logkey SERIAL NOT NULL,
+                ikey bigint NOT NULL,
+                irev bigint,
+                otype text NOT NULL,
+                name text NOT NULL,
+                changetype text NOT NULL,
+                username text NOT NULL,
+                message text NOT NULL,
+                logdate timestamp with time zone NOT NULL DEFAULT now(),
+                CONSTRAINT objectlogs_pkey PRIMARY KEY (logkey)
+                );},
+                q{
+                CREATE INDEX objectlogs_name_idx
+                ON objectslogs
+                USING btree
+                (name);
+                },
+                q{
+                CREATE INDEX objectslogs_ikey_idx
+                ON objectslogs
+                USING btree
+                (ikey);
+                },
+                q{
+                CREATE INDEX objectslogs_otype_idx
+                ON objectslogs
+                USING btree
+                (otype);
+                }
+            ],
+        },
+        {
+            ver => 9,
+            sql => [
+                q{
+                CREATE TABLE nethost_attributes_nethosts
+                (
+                CONSTRAINT nethost_attributes_nethosts_pkey PRIMARY KEY (attr_key),
+                CONSTRAINT nethost_attr_nethosts_fkey FOREIGN KEY (okey)
+                REFERENCES nethost (ikey) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE,
+                CONSTRAINT nethost_attr_nethosts_nethosts FOREIGN KEY ("value")
+                REFERENCES nethost (name) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (nethost_attributes);
+
+                CREATE INDEX fki_nethost_attr_nethosts_fkey
+                ON nethost_attributes_nethosts
+                USING btree
+                (okey);
+
+                CREATE TRIGGER nethost_attr_nethosts_update
+                AFTER INSERT OR UPDATE OR DELETE
+                ON nethost_attributes_nethosts
+                FOR EACH ROW
+                EXECUTE PROCEDURE nethost_attr_update_ref();
+
+
+                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;
+
+                IF (new.attr='related') THEN
+                insert into nethost_attributes_nethosts 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;
+
+                }
+            ],
+        },
+        {
+            ver => 10,
+            sql => [
+                q{
+                CREATE OR REPLACE FUNCTION service_attr_update_ref()
+                RETURNS trigger AS
+                $BODY$begin
+
+                IF (TG_OP != 'INSERT') then
+                update "service" set date = now() where "service".ikey = old.okey;
+                end if;
+                IF (TG_OP != 'DELETE') then
+                update "service"  set date = now() where "service".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 service
+                (
+                "start" date,
+                "end" date,
+                CONSTRAINT service_pkey PRIMARY KEY (name),
+                CONSTRAINT service_ikey_uniq UNIQUE (ikey)
+                )
+                INHERITS (objects);
+                },
+                q{
+                CREATE TABLE service_attributes_list
+                (
+                ikey integer NOT NULL DEFAULT nextval('ikey_seq'::regclass),
+                canonical text NOT NULL,
+                description text,
+                CONSTRAINT service_attributes_list_pkey PRIMARY KEY (ikey),
+                CONSTRAINT g_attr_l_service_uniq UNIQUE (canonical)
+                )
+                INHERITS (revisions, attributes_list)
+                WITH (
+                OIDS=FALSE
+                );
+
+                CREATE TABLE service_attributes
+                (
+                CONSTRAINT service_attributes_pkey PRIMARY KEY (attr_key),
+                CONSTRAINT service_attr_users_okey_fkey FOREIGN KEY (okey)
+                REFERENCES service (ikey) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE,
+                CONSTRAINT service_attributes_attr_fkey FOREIGN KEY (attr)
+                REFERENCES service_attributes_list (canonical) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE NO ACTION
+                )
+                INHERITS (attributes)
+                WITH (
+                OIDS=FALSE
+                );
+
+                CREATE INDEX fki_service_attributes_attr_fkey
+                ON nethost_attributes
+                USING btree
+                (attr);
+
+                CREATE INDEX service_attr_value_idx
+                ON nethost_attributes
+                USING btree
+                (value);
+
+                CREATE TRIGGER serivce_attr_update
+                AFTER INSERT OR UPDATE OR DELETE
+                ON service_attributes
+                FOR EACH ROW
+                EXECUTE PROCEDURE service_attr_update_ref();
+                },
+                q{
+                CREATE TABLE service_attributes_users
+                (
+                CONSTRAINT service_attributes_user_pkey PRIMARY KEY (attr_key),
+                CONSTRAINT service_attr_users_okey_fkey FOREIGN KEY (okey)
+                REFERENCES service (ikey) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE,
+                CONSTRAINT service_attributes_users_attr_fkey FOREIGN KEY (attr)
+                REFERENCES service_attributes_list (canonical) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE NO ACTION,
+                CONSTRAINT service_attributes_users_user_fkey FOREIGN KEY (value)
+                REFERENCES "user" (name) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE,
+                CONSTRAINT service_attributes_users_uniq UNIQUE (okey, attr, value)
+                )
+                INHERITS (service_attributes);
+
+                CREATE INDEX fki_service_attr_users_okey_fkey
+                ON service_attributes_users
+                USING btree
+                (okey);
+
+                CREATE INDEX fki_service_attributes_users_attr_fkey
+                ON service_attributes_users
+                USING btree
+                (attr);
+
+                CREATE INDEX fki_service_attributes_users_user_fkey
+                ON service_attributes_users
+                USING btree
+                (value);
+
+                CREATE TRIGGER service_attributes_users_update_ref
+                AFTER INSERT OR UPDATE OR DELETE
+                ON service_attributes_users
+                FOR EACH ROW
+                EXECUTE PROCEDURE service_attr_update_ref();
+
+                CREATE OR REPLACE FUNCTION service_sort_fields()
+                RETURNS trigger AS
+                $BODY$BEGIN
+
+                if (TG_OP='INSERT' or TG_OP='UPDATE') THEN
+                IF (new.attr='manager') THEN
+                insert into service_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;
+
+                CREATE TRIGGER service_sort_field_tg
+                BEFORE INSERT OR UPDATE OR DELETE
+                ON service_attributes
+                FOR EACH ROW
+                EXECUTE PROCEDURE service_sort_fields();
+
+                CREATE TRIGGER service_rev_tg
+                BEFORE INSERT OR UPDATE OR DELETE
+                ON service
+                FOR EACH ROW
+                EXECUTE PROCEDURE rev_tg_f();
+                },
+            ],
+        },
+        {
+            ver => 11,
+            sql => [
+                q{
+                ALTER TABLE "user" ADD COLUMN endcircuit timestamp with time zone
+                }
+            ],
+        },
+        {
+            ver => 12,
+            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;
+                },
+                q{
+                CREATE OR REPLACE FUNCTION rev_tg_aliases_f()
+                RETURNS trigger AS
+                $BODY$begin
+
+                IF (TG_OP = 'DELETE') THEN
+                update "user" set rev = nextval('revisions_rev_seq'::regclass), "date" = now() where "name" = ANY (old.forward);
+                return old;
+                ELSE
+                IF (TG_OP = 'UPDATE') THEN
+                update "user" set rev = nextval('revisions_rev_seq'::regclass), "date" = now() where "name" = ANY (old.forward);
+                END IF;
+                update "user" set rev = nextval('revisions_rev_seq'::regclass), "date" = now() where "name" = ANY (new.forward);
+                return new;
+                END IF;
+
+                end;$BODY$
+                LANGUAGE plpgsql VOLATILE
+                COST 100;
+
+                CREATE TRIGGER aliases_user_tg
+                AFTER INSERT OR UPDATE OR DELETE
+                ON aliases
+                FOR EACH ROW
+                EXECUTE PROCEDURE rev_tg_aliases_f();
+                },
+            ],
+        },
+        {
+            ver => 13,
+            sql => [
+                q{
+                CREATE TABLE "stat"
+                (
+                name text NOT NULL,
+                CONSTRAINT stat_pkey PRIMARY KEY (name),
+                CONSTRAINT stat_okey_uniq UNIQUE (ikey)
+                )
+                INHERITS (objects);
+                CREATE INDEX stat_exported_idx
+                ON stat USING btree (exported);
+                CREATE UNIQUE INDEX stat_rev_idx
+                ON stat USING btree (rev);
+
+                CREATE OR REPLACE FUNCTION stat_attr_update_ref()
+                RETURNS trigger AS
+                $BODY$begin
+
+                IF (TG_OP != 'INSERT') then
+                update "stat" set date = now() where "stat".ikey = old.okey;
+                end if;
+                IF (TG_OP != 'DELETE') then
+                update "stat"  set date = now() where "stat".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 stat_attributes
+                (
+                CONSTRAINT stat_attributes_pkey PRIMARY KEY (attr_key),
+                CONSTRAINT stat FOREIGN KEY (okey)
+                REFERENCES stat (ikey) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (attributes);
+                CREATE INDEX stat_attributes_attr_fkey
+                ON stat_attributes
+                USING btree
+                (attr);
+                CREATE INDEX stat_attr_value_idx
+                ON stat_attributes
+                USING btree
+                (value);
+                CREATE TRIGGER stat_attr_update
+                AFTER INSERT OR UPDATE OR DELETE
+                ON stat_attributes
+                FOR EACH ROW
+                EXECUTE PROCEDURE stat_attr_update_ref();
+                },
+                q{
+                CREATE TABLE stat_attributes_list
+                (
+                CONSTRAINT stat_attr_list_pkey PRIMARY KEY (ikey),
+                CONSTRAINT stat_attr_l_c_uniq UNIQUE (canonical)
+                )
+                INHERITS (revisions, attributes_list);
+                },
+                q{
+                CREATE TABLE statsentry
+                (
+                okey integer NOT NULL,
+                id bigint NOT NULL DEFAULT nextval('revisions_rev_seq'::regclass),
+                tstamp timestamp with time zone NOT NULL DEFAULT now(),
+                CONSTRAINT stat FOREIGN KEY (okey)
+                REFERENCES stat (ikey) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE,
+                CONSTRAINT statsentry_pkey PRIMARY KEY (id)
+                );
+                CREATE TABLE statvalues
+                (
+                sid bigint NOT NULL,
+                value text NOT NULL,
+                count bigint NOT NULL,
+                CONSTRAINT statvalues_pkey PRIMARY KEY (sid, value),
+                CONSTRAINT statvalues_fkey FOREIGN KEY (sid)
+                REFERENCES statsentry (id) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                },
+            ],
+        },
+        {
+            ver => 14,
+            sql => [
+                q{
+                ALTER TABLE objects ADD COLUMN createdby text;
+                },
+                q{
+                ALTER TABLE objects ADD COLUMN modifiedby text;
+                },
+            ],
+        },
+        {
+            ver => 15,
+            sql => [
+                q{
+                CREATE TABLE "employment"
+                (
+                name text NOT NULL,
+                "user" text NOT NULL,
+                firstday date NOT NULL default now(),
+                lastday  date,
+                CONSTRAINT employment_pkey PRIMARY KEY (name),
+                CONSTRAINT employment_okey_uniq UNIQUE (ikey),
+                CONSTRAINT employment_user_fkey FOREIGN KEY ("user")
+                REFERENCES "user" (name) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (objects);
+                CREATE INDEX employment_exported_idx
+                ON employment USING btree (exported);
+                CREATE UNIQUE INDEX employment_rev_idx
+                ON employment USING btree (rev);
+
+                CREATE OR REPLACE FUNCTION employment_attr_update_ref()
+                RETURNS trigger AS
+                $BODY$begin
+
+                IF (TG_OP != 'INSERT') then
+                update "employment" set date = now() where "employment".ikey = old.okey;
+                end if;
+                IF (TG_OP != 'DELETE') then
+                update "employment"  set date = now() where "employment".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 employment_attributes
+                (
+                CONSTRAINT employment_attributes_pkey PRIMARY KEY (attr_key),
+                CONSTRAINT employment FOREIGN KEY (okey)
+                REFERENCES employment (ikey) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (attributes);
+                CREATE INDEX employment_attributes_attr_fkey
+                ON employment_attributes
+                USING btree
+                (attr);
+                CREATE INDEX employment_attr_value_idx
+                ON employment_attributes
+                USING btree
+                (value);
+                CREATE TRIGGER employment_attr_update
+                AFTER INSERT OR UPDATE OR DELETE
+                ON employment_attributes
+                FOR EACH ROW
+                EXECUTE PROCEDURE employment_attr_update_ref();
+                },
+                q{
+                CREATE TABLE employment_attributes_list
+                (
+                CONSTRAINT employment_attr_list_pkey PRIMARY KEY (ikey),
+                CONSTRAINT employment_attr_l_c_uniq UNIQUE (canonical)
+                )
+                INHERITS (revisions, attributes_list);
+                },
+                q{
+                CREATE INDEX employment_firstday_idx
+                ON employment
+                USING btree
+                (firstday NULLS FIRST);
+                CREATE INDEX employment_lastday_idx
+                ON employment
+                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;
+                }
+
+            ],
+        },
+        {
+            ver => 17,
+            sql => [
+                q{
+                CREATE TABLE passwordreset
+                (
+                -- HÃ©ritÃ©(e) from table revisions:  rev integer NOT NULL DEFAULT nextval('revisions_rev_seq'::regclass),
+                -- HÃ©ritÃ©(e) from table revisions:  date timestamp with time zone NOT NULL DEFAULT now(),
+                -- HÃ©ritÃ©(e) from table revisions:  "create" timestamp with time zone NOT NULL DEFAULT now(),
+                -- HÃ©ritÃ©(e) from table revisions:  ikey integer NOT NULL DEFAULT nextval('ikey_seq'::regclass),
+                "user" text NOT NULL,
+                id text NOT NULL,
+                CONSTRAINT passwordreset_pkey PRIMARY KEY (id),
+                CONSTRAINT password_reset_user_fkey FOREIGN KEY ("user")
+                REFERENCES "user" (name) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (revisions)
+                WITH (
+                OIDS=FALSE
+                );
+                },
+            ],
+        },
+        {
+            ver => 18,
+            sql => [
+                q{
+                ALTER TABLE employment
+                ADD CHECK (lastday is null or firstday <= lastday);
+                }
+            ],
+        },
+        {
+            ver => 19,
+            sql => [
+                q{
+                ALTER TABLE "nethost" ADD COLUMN expire timestamp with time zone;
+                }
+            ],
+        },
+        {
+            ver => 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"
+                ;
+
+                }
+            ],
+        },
+        {
+            ver => 21,
+            sql => [
+                'ALTER TABLE objects ADD COLUMN oalias text',
+                'ALTER TABLE objects
+                ADD COLUMN nodelete boolean NOT NULL DEFAULT false',
+                'ALTER TABLE objects
+                ADD COLUMN internobject boolean NOT NULL DEFAULT false',
+            ],
+        },
+        {
+            ver => 22,
+            sql => [
+                'CREATE INDEX fki_employment
+                ON employment_attributes
+                USING btree (okey);'
+            ],
+        },
+        {
+            ver => 23,
+            sql => [
+                'ALTER TABLE address ADD COLUMN expire timestamp with time zone'
+            ],
+        },
+        {
+            ver => 24,
+            sql => [
+                    q{
+                    CREATE OR REPLACE FUNCTION user_sort_fields()
+                    RETURNS trigger AS
+                    $BODY$DECLARE
+                    rec RECORD;
+                    BEGIN
+
+                    IF (TG_OP='UPDATE' or TG_OP='INSERT') THEN
+
+                    IF (new.attr='locked') THEN
+                    IF (TG_OP = 'INSERT') THEN
+                    new.value = now()::text;
+                    ELSIF (TG_OP='UPDATE') THEN
+                    new.value = old.value;
+                    END IF;
+                    END IF;
+
+                    IF (new.attr='nickname') THEN
+                    PERFORM 1 from "user" where "user".ikey = new.okey
+                        and new.value = "user".name;
+                    IF NOT FOUND THEN
+                    select aliases.name into rec from aliases join "user"
+                    on array["user".name] = aliases.forward
+                    where "user".ikey = new.okey and aliases.name = new.value;
+                    IF NOT FOUND THEN
+                    insert into aliases ("name", "forward", "description", exported)
+                    select new.value, array["user".name], 'Forward for ' || "user".name || ' nickname', "user".exported from "user" where "user".ikey = new.okey;
+                    END IF;
+                    END IF;
+                    END IF;
+
+                    IF (new.attr='uidNumber') THEN
+                    update "user" set uidnumber = new.value::integer where ikey = new.okey;
+                    RETURN NULL;
+                    END IF;
+
+                    IF (new.attr='gidNumber') THEN
+                    update "user" set gidnumber = new.value::integer where ikey = new.okey;
+                    RETURN null;
+                    END IF;
+
+                    IF (new.attr='expire') THEN
+                    update "user" set expire = new.value::timestamp where ikey = new.okey;
+                    RETURN NULL;
+                    END IF;
+
+                    IF (new.attr='exported') THEN
+                    update "user" set exported = true where ikey = new.okey;
+                    RETURN NULL;
+                    END IF;
+
+                    END IF;
+
+                    IF (TG_OP = 'DELETE') THEN
+
+                    IF (old.attr='expire') THEN
+                    update "user" set expire = NULL where ikey = old.okey;
+                    RETURN NULL;
+                    END IF;
+
+                    IF (old.attr='exported') THEN
+                    update "user" set exported = false where ikey = old.okey;
+                    RETURN NULL;
+                    END IF;
+
+                    END IF;
+
+
+                    IF (TG_OP='INSERT') THEN
+                    IF (new.attr='manager') THEN
+                    insert into user_attributes_users VALUES (new.*);
+                    RETURN NULL;
+                    END IF;
+
+                    IF (new.attr='site') THEN
+                    insert into user_attributes_site VALUES (new.*);
+                    RETURN NULL;
+                    END IF;
+
+                    IF (new.attr='department') THEN
+                    insert into user_attributes_groups VALUES (new.*);
+                    RETURN NULL;
+                    END IF;
+
+                    IF (new.attr='contratType') THEN
+                    insert into user_attributes_groups VALUES (new.*);
+                    RETURN NULL;
+                    END IF;
+
+                    IF (new.attr='jobType') THEN
+                    insert into user_attributes_groups 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;
+                },
+            ],
+        },
+        {
+            ver => 25,
+            sql => [
+                'ALTER TABLE objects ADD COLUMN oaliascache text',
+            ],
+        },
+        {
+            ver => 26,
+            sql => [ ],
+        },
+    );
+
+    my $dbi = $self->{_db};
+
+    foreach my $maj (@updates) {
+        if ($rev >= $maj->{ver}) {
+            next;
+        }
+        $self->log(LA_NOTICE, 'Switching to schema revision: ' . $maj->{ver} . "\n");
+        foreach my $sql (@{ $maj->{sql} }) {
+            {
+                my $sqlv = $sql;
+                $sqlv =~ s/^/  /mg;
+            }
+            my $err = $dbi->do($sql);
+            defined($err) or do {
+                $self->log(LA_ERR, "Erreur :\\" . $dbi->errstr . "\n");
+                return;
+            };
+        }
+
+        $self->log(LA_INFO, 'Updating schema_version to ' . $maj->{ver} . "\n");
+
+        $dbi->do(
+            'UPDATE settings SET val = ? where varname = ?',
+            undef,
+            $maj->{ver}, 'schema_version'
+        );
+    }
+
+    return 1;
+}
+
+sub _CreateInternalObjects {
+    my ($self) = @_;
+
+    my $dbi = $self->{_db};
+
+    my @objects = (
+        {
+            name => 'dpmt',
+            otype => 'sutype',
+            attrs => {
+                description => 'Department',
+            },
+        },
+        {
+            name => 'contrattype',
+            otype => 'sutype',
+            attrs => {
+                description => 'Contract',
+            },
+        },
+        {
+            name => '-useralias',
+            otype => 'group',
+            attrs => {
+                description => 'Internal group for user alias object',
+                gidnumber => -1,
+                unexported => 1,
+            },
+            intern => 1,
+        },
+    );
+
+    my $setnodel  = $dbi->prepare('UPDATE objects SET nodelete     = true where name = ?');
+    my $setintern = $dbi->prepare('UPDATE objects SET internobject = true where name = ?');
+
+    foreach (@objects) {
+        if (!$self->GetRawObject($_->{otype}, $_->{name})) {
+            printf("Creating object %s/%s\n", $_->{otype}, $_->{name});
+            $self->create_object($_->{otype}, $_->{name}, %{$_->{attrs} || {}})
+                or die sprintf("cannot create %s/%s\n", $_->{otype}, $_->{name});
+
+        }
+
+        $setnodel->execute($_->{name});
+        $setintern->execute($_->{name}) if ($_->{intern});
+    }
+
+    return 1;
+}
+
+=head2 SchemaUpgrade
+
+Upgrade the SQL schema
+
+=cut
+
+sub SchemaUpgrade {
+    my ($self) = @_;
+
+    return $self->temp_switch_unexported(
+        sub {
+            if($self->_SqlSchemaUpgrade) {
+                $self->log(LA_DEBUG, "upgrade::_SqlSchemaUpgrade done");
+            } else {
+                $self->{_db}->rollback;
+                return;
+            }
+                
+            if ($self->_CreateInternalObjects) {
+                $self->log(LA_DEBUG, "upgrade::_CreateInternalObjects done");
+            } else {
+                $self->{_db}->rollback;
+                return;
+            }
+
+            return 1;
+        }, 1
+    );
+
+}
+
+1;
