source: trunk/LATMOS-Accounts/bin/la-sql-upgrade.in @ 1375

#!/usr/bin/perl

use strict;
use warnings;
use LATMOS::Accounts;
use Getopt::Long;
use Pod::Usage;

my $DATADIR = '@DATADIR@';

=head1 NAME

    la-sql-upgrade - Update SQL base schema

=head1 SYNOPSIS

    la-sql-upgrade [options] [name]

=cut

GetOptions(
    'c|config=s'     => \my $config,
    'b|base=s'       => \my $base,
    'no-commit'      => \my $nocommit,
    'v|verbose'      => \my $verbose,
    'h|help'         => sub { pod2usage(1); },
) or pod2usage();

=head1 OPTIONS

=over 4

=item -c|--config configdir

Use this configuration directory instead of the default one.

=item -b|--base basename

Query this specific base instead of the default one.

=back

=cut

$| = 1; # autoflush

my $LA = LATMOS::Accounts->new($config, noacl => 1);
my $labase = $LA->base($base);

my $dbi = $labase->db;

my $rev = $labase->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
            );
            ALTER TABLE service_attributes_list
            OWNER TO latmos;

            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
            );
            ALTER TABLE service_attributes
            OWNER TO latmos;

            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
            }
        ],
    },
);

my @objects = (
    {
        name => 'dpmt',
        otype => 'sutype',
        attrs => {
            description => 'Department',
        },
    },
    {
        name => 'contrattype',
        otype => 'sutype',
        attrs => {
            description => 'Contract',
        },
    },
);

$dbi->rollback;

foreach my $maj (@updates) {
    if ($rev >= $maj->{ver}) {
        next;
    }
    print "\n";
    print 'Switching to schema revision: ' . $maj->{ver} . "\n";
    foreach my $sql (@{ $maj->{sql} }) {
        {
            my $sqlv = $sql;
            $sqlv =~ s/^/  /mg;
            if ($verbose) {
                warn '  >' . $sqlv . "\n";
            } else {
                print 'x';
            }
        }
        $dbi->do($sql) or die "Erreur :\\" . $dbi->errstr . "\n";
    }

    print "\n";

    print 'Updating schema_version to ' . $maj->{ver} . "\n";

    $dbi->do(
        'UPDATE settings SET val = ? where varname = ?',
        undef,
        $maj->{ver}, 'schema_version'
    );
    print "Done\n\n";

}

foreach my $otype ($labase->list_supported_objects) {
    foreach my $attribute ($labase->list_canonical_fields($otype, 'r')) {
        my $attr = $labase->attribute($otype, $attribute);
        $attr->{inline} and next;
        $attr->{managed} and next;

        if ($labase->is_registered_attribute($otype, $attribute)) {
        } else {
            $labase->register_attribute($otype, $attribute, $attr->{comment})
                or die "Error, aborting\n";
            print "Attr. $attribute for object type $otype registred\n";
        }
    }
} 
 
foreach (@objects) {
    if (!$labase->get_object($_->{otype}, $_->{name})) {
        printf("Creatting object %s/%s\n", $_->{otype}, $_->{name});
        $labase->create_object($_->{otype}, $_->{name}, %{$_->{attrs} || {}})
            or die sprintf("cannot create %s/%s\n", $_->{otype}, $_->{name});

    }
}

if ($nocommit) {
    $dbi->rollback;
} else {
    $dbi->commit;
}
print "Process terminated successfully\n";