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

#!/usr/bin/perl

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

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,
    'a|attributes=s' => \my $attributes_file,
    'no-commit'      => \my $nocommit,
    'v|verbose'      => \my $verbose,
    'h|help'         => sub { pod2usage(1); },
) or pod2usage();

$attributes_file ||= "$DATADIR/attributes.csv";

=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.

=item -a|--attribute cvs_file

The attribute file description to use

=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;
            },
        ],
    },
    {
        ver => 7,
        sql => [
            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;
            },
        ],
    },

);

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";

}

print "Loading attribute from: $attributes_file\n";

open my $fh, "<:encoding(utf8)", $attributes_file or die "$attributes_file: $!"; 
 
my $csv = Text::CSV_XS->new(); 
 
while (my $row = $csv->getline($fh)) { 
    my ($otype, $attribute, $comment) = @$row; 
    if ($labase->is_registered_attribute($otype, $attribute)) { 
    } else { 
        $labase->register_attribute($otype, $attribute, $comment) 
            or die "Error, aborting\n"; 
        print "Attr. $attribute for object type $otype registred\n"; 
    } 
} 
 
$csv->eof or do { 
    $csv->error_diag(); 
    die "Cannot load attribute\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";