[881] | 1 | #!/usr/bin/perl |
---|
| 2 | |
---|
| 3 | use strict; |
---|
| 4 | use warnings; |
---|
| 5 | use LATMOS::Accounts; |
---|
| 6 | use Getopt::Long; |
---|
| 7 | use Pod::Usage; |
---|
| 8 | use Text::CSV_XS; |
---|
| 9 | |
---|
| 10 | my $DATADIR = '@DATADIR@'; |
---|
| 11 | |
---|
| 12 | =head1 NAME |
---|
| 13 | |
---|
[1185] | 14 | la-sql-upgrade - Update SQL base schema |
---|
[881] | 15 | |
---|
| 16 | =head1 SYNOPSIS |
---|
| 17 | |
---|
[1185] | 18 | la-sql-upgrade [options] [name] |
---|
[881] | 19 | |
---|
| 20 | =cut |
---|
| 21 | |
---|
| 22 | GetOptions( |
---|
| 23 | 'c|config=s' => \my $config, |
---|
| 24 | 'b|base=s' => \my $base, |
---|
| 25 | 'a|attributes=s' => \my $attributes_file, |
---|
[920] | 26 | 'no-commit' => \my $nocommit, |
---|
| 27 | 'v|verbose' => \my $verbose, |
---|
[1225] | 28 | 'h|help' => sub { pod2usage(1); }, |
---|
[881] | 29 | ) or pod2usage(); |
---|
| 30 | |
---|
| 31 | $attributes_file ||= "$DATADIR/attributes.csv"; |
---|
| 32 | |
---|
| 33 | =head1 OPTIONS |
---|
| 34 | |
---|
| 35 | =over 4 |
---|
| 36 | |
---|
| 37 | =item -c|--config configdir |
---|
| 38 | |
---|
| 39 | Use this configuration directory instead of the default one. |
---|
| 40 | |
---|
| 41 | =item -b|--base basename |
---|
| 42 | |
---|
| 43 | Query this specific base instead of the default one. |
---|
| 44 | |
---|
[1185] | 45 | =item -a|--attribute cvs_file |
---|
| 46 | |
---|
| 47 | The attribute file description to use |
---|
| 48 | |
---|
[881] | 49 | =back |
---|
| 50 | |
---|
| 51 | =cut |
---|
| 52 | |
---|
[920] | 53 | $| = 1; # autoflush |
---|
| 54 | |
---|
[881] | 55 | my $LA = LATMOS::Accounts->new($config, noacl => 1); |
---|
| 56 | my $labase = $LA->base($base); |
---|
| 57 | |
---|
| 58 | my $dbi = $labase->db; |
---|
| 59 | |
---|
| 60 | my $rev = $labase->get_global_value('schema_version') || 1; |
---|
| 61 | |
---|
| 62 | my @updates = ( |
---|
| 63 | { |
---|
| 64 | ver => 3, |
---|
| 65 | sql => [ |
---|
[947] | 66 | q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT |
---|
[881] | 67 | nethost_is_single_ip}, |
---|
[947] | 68 | q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON nethost}, |
---|
| 69 | q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON netzone}, |
---|
| 70 | q{DROP FUNCTION IF EXISTS check_zone_name_unity()}, |
---|
| 71 | q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT |
---|
| 72 | nethost_is_single_ip}, |
---|
| 73 | q{INSERT INTO settings(varname, val) VALUES ('schema_version', 3)}, |
---|
[881] | 74 | ], |
---|
| 75 | }, |
---|
[920] | 76 | { |
---|
| 77 | ver => 4, |
---|
| 78 | sql => [ |
---|
| 79 | q{ |
---|
| 80 | CREATE OR REPLACE FUNCTION nethost_sort_fields() |
---|
[947] | 81 | RETURNS trigger AS |
---|
| 82 | $BODY$BEGIN |
---|
[920] | 83 | |
---|
[947] | 84 | IF (TG_OP='INSERT') then |
---|
| 85 | IF (new.attr='ip') THEN |
---|
| 86 | insert into nethost_attributes_ips VALUES (new.*); |
---|
| 87 | RETURN NULL; |
---|
| 88 | END IF; |
---|
[920] | 89 | |
---|
[947] | 90 | IF (new.attr='macaddr') THEN |
---|
| 91 | insert into nethost_attributes_macs VALUES (new.*); |
---|
| 92 | RETURN NULL; |
---|
| 93 | END IF; |
---|
[920] | 94 | |
---|
[947] | 95 | IF (new.attr='owner') THEN |
---|
| 96 | insert into nethost_attributes_users VALUES (new.*); |
---|
| 97 | RETURN NULL; |
---|
| 98 | END IF; |
---|
[920] | 99 | |
---|
[947] | 100 | IF (new.attr='user') THEN |
---|
| 101 | insert into nethost_attributes_users VALUES (new.*); |
---|
| 102 | RETURN NULL; |
---|
| 103 | END IF; |
---|
| 104 | end if; |
---|
[920] | 105 | |
---|
| 106 | |
---|
[947] | 107 | if (TG_OP='DELETE') THEN |
---|
| 108 | RETURN old; |
---|
| 109 | else |
---|
| 110 | RETURN new; |
---|
| 111 | end if; |
---|
| 112 | END;$BODY$ |
---|
[920] | 113 | LANGUAGE plpgsql VOLATILE |
---|
| 114 | COST 100; |
---|
| 115 | }, |
---|
| 116 | ], |
---|
| 117 | }, |
---|
[947] | 118 | { |
---|
| 119 | ver => 5, |
---|
| 120 | sql => [ |
---|
| 121 | q{ |
---|
| 122 | CREATE OR REPLACE VIEW address_attributes AS |
---|
| 123 | ( ( SELECT address."user" AS value, 'user' AS attr, address.rev AS attr_key, address.ikey AS okey |
---|
| 124 | FROM address |
---|
| 125 | UNION ALL |
---|
| 126 | SELECT address.name AS value, 'name' AS attr, address.rev AS attr_key, address.ikey AS okey |
---|
| 127 | FROM address ) |
---|
| 128 | UNION ALL |
---|
| 129 | SELECT address_attributes.value, address_attributes.attr, address_attributes.attr_key, address_attributes.okey |
---|
| 130 | FROM address_attributes_base address_attributes ) |
---|
| 131 | UNION ALL |
---|
| 132 | SELECT '1' AS value, 'active' AS attr, address.rev AS attr_key, address.ikey AS okey |
---|
| 133 | FROM "user" JOIN address ON "user".name = address."user" |
---|
| 134 | WHERE "user".exported AND address.exported and |
---|
| 135 | ("user".expire IS NULL or "user".expire > now()) |
---|
| 136 | } |
---|
| 137 | ], |
---|
| 138 | }, |
---|
| 139 | { |
---|
| 140 | ver => 6, |
---|
| 141 | sql => [ |
---|
| 142 | q{ |
---|
| 143 | CREATE TABLE request |
---|
| 144 | ( |
---|
| 145 | id serial NOT NULL, |
---|
| 146 | "create" timestamp with time zone NOT NULL DEFAULT now(), |
---|
| 147 | name text NOT NULL, |
---|
| 148 | object text, |
---|
| 149 | apply timestamp with time zone NOT NULL DEFAULT now(), |
---|
| 150 | done timestamp with time zone, |
---|
| 151 | "user" text, |
---|
| 152 | CONSTRAINT request_pkey PRIMARY KEY (id ), |
---|
| 153 | CONSTRAINT request_name_fkey FOREIGN KEY (name) |
---|
| 154 | REFERENCES accreq (name) MATCH SIMPLE |
---|
| 155 | ON UPDATE CASCADE ON DELETE CASCADE, |
---|
| 156 | CONSTRAINT request_user_fkey FOREIGN KEY ("user") |
---|
| 157 | REFERENCES "user" (name) MATCH SIMPLE |
---|
| 158 | ON UPDATE CASCADE ON DELETE SET NULL |
---|
| 159 | ) |
---|
| 160 | }, |
---|
| 161 | q{ |
---|
| 162 | CREATE INDEX fki_request_name_fkey ON request |
---|
| 163 | USING btree (name ) |
---|
| 164 | }, |
---|
| 165 | q{ |
---|
| 166 | CREATE INDEX fki_request_user_fkey ON request |
---|
| 167 | USING btree ("user" ) |
---|
| 168 | }, |
---|
| 169 | q{ |
---|
| 170 | CREATE TABLE request_attributes |
---|
| 171 | ( |
---|
| 172 | reqid bigint, |
---|
| 173 | attribute text NOT NULL, |
---|
| 174 | value text, |
---|
| 175 | CONSTRAINT request_id_fkey FOREIGN KEY (reqid) |
---|
| 176 | REFERENCES request (id) MATCH SIMPLE |
---|
| 177 | ON UPDATE CASCADE ON DELETE CASCADE |
---|
| 178 | ) |
---|
| 179 | }, |
---|
| 180 | q{ |
---|
| 181 | CREATE INDEX fki_request_id_fkey ON request_attributes |
---|
| 182 | USING btree (reqid ) |
---|
| 183 | }, |
---|
| 184 | q{ |
---|
| 185 | delete from accreq_attributes_list |
---|
| 186 | } |
---|
| 187 | ], |
---|
[981] | 188 | }, |
---|
[983] | 189 | { |
---|
| 190 | ver => 7, |
---|
| 191 | sql => [ |
---|
| 192 | q{ |
---|
| 193 | ALTER TABLE request ADD COLUMN automated boolean NOT NULL DEFAULT false; |
---|
| 194 | }, |
---|
| 195 | q{ |
---|
| 196 | ALTER TABLE request ADD COLUMN objrev bigint; |
---|
| 197 | }, |
---|
[1074] | 198 | q{ |
---|
| 199 | ALTER TABLE aliases ADD COLUMN description text; |
---|
| 200 | }, |
---|
| 201 | q{ |
---|
| 202 | ALTER TABLE revaliases ADD COLUMN description text; |
---|
| 203 | }, |
---|
[983] | 204 | ], |
---|
| 205 | }, |
---|
[1186] | 206 | { |
---|
| 207 | ver => 7, |
---|
| 208 | sql => [ |
---|
| 209 | q{ |
---|
| 210 | CREATE OR REPLACE FUNCTION group_sort_fields() |
---|
| 211 | RETURNS trigger AS |
---|
| 212 | $BODY$BEGIN |
---|
| 213 | |
---|
| 214 | IF (TG_OP='INSERT') then |
---|
| 215 | IF (new.attr='managedBy' |
---|
| 216 | OR new.attr='managedAlsoBy' |
---|
| 217 | OR new.attr='member' |
---|
| 218 | OR new.attr='memberUID') THEN |
---|
| 219 | insert into group_attributes_users VALUES (new.*); |
---|
| 220 | RETURN NULL; |
---|
| 221 | END IF; |
---|
| 222 | |
---|
| 223 | IF (new.attr='sutype') THEN |
---|
| 224 | insert into group_attributes_sutypes VALUES (new.*); |
---|
| 225 | RETURN NULL; |
---|
| 226 | END IF; |
---|
| 227 | end if; |
---|
| 228 | |
---|
| 229 | |
---|
| 230 | IF (TG_OP = 'UPDATE') THEN |
---|
| 231 | IF (new.attr='gidNumber') then |
---|
| 232 | update "group" set gidnumber = new.value::integer where |
---|
| 233 | "group".ikey = new.okey; |
---|
| 234 | RETURN NULL; |
---|
| 235 | END IF; |
---|
| 236 | END IF; |
---|
| 237 | |
---|
| 238 | IF (TG_OP = 'DELETE') THEN |
---|
| 239 | IF (old.attr='exported') then |
---|
| 240 | update "group" set exported = false where "group".ikey = old.okey; |
---|
| 241 | return null; |
---|
| 242 | end if; |
---|
| 243 | else |
---|
| 244 | IF (new.attr='exported') then |
---|
| 245 | update "group" set exported = true where "group".ikey = new.okey; |
---|
| 246 | RETURN NULL; |
---|
| 247 | end if; |
---|
| 248 | END IF; |
---|
| 249 | |
---|
| 250 | if (TG_OP='DELETE') THEN |
---|
| 251 | RETURN old; |
---|
| 252 | else |
---|
| 253 | RETURN new; |
---|
| 254 | end if; |
---|
| 255 | END;$BODY$ |
---|
| 256 | LANGUAGE plpgsql VOLATILE |
---|
| 257 | COST 100; |
---|
| 258 | }, |
---|
| 259 | ], |
---|
| 260 | }, |
---|
| 261 | |
---|
[881] | 262 | ); |
---|
| 263 | |
---|
[981] | 264 | my @objects = ( |
---|
| 265 | { |
---|
| 266 | name => 'dpmt', |
---|
| 267 | otype => 'sutype', |
---|
| 268 | attrs => { |
---|
| 269 | description => 'Department', |
---|
| 270 | }, |
---|
| 271 | }, |
---|
| 272 | { |
---|
| 273 | name => 'contrattype', |
---|
| 274 | otype => 'sutype', |
---|
| 275 | attrs => { |
---|
| 276 | description => 'Contract', |
---|
| 277 | }, |
---|
| 278 | }, |
---|
| 279 | ); |
---|
| 280 | |
---|
[881] | 281 | $dbi->rollback; |
---|
| 282 | |
---|
| 283 | foreach my $maj (@updates) { |
---|
| 284 | if ($rev >= $maj->{ver}) { |
---|
| 285 | next; |
---|
| 286 | } |
---|
| 287 | print "\n"; |
---|
| 288 | print 'Switching to schema revision: ' . $maj->{ver} . "\n"; |
---|
| 289 | foreach my $sql (@{ $maj->{sql} }) { |
---|
| 290 | { |
---|
| 291 | my $sqlv = $sql; |
---|
| 292 | $sqlv =~ s/^/ /mg; |
---|
[920] | 293 | if ($verbose) { |
---|
| 294 | warn ' >' . $sqlv . "\n"; |
---|
| 295 | } else { |
---|
| 296 | print 'x'; |
---|
| 297 | } |
---|
[881] | 298 | } |
---|
[920] | 299 | $dbi->do($sql) or die "Erreur :\\" . $dbi->errstr . "\n"; |
---|
[881] | 300 | } |
---|
[920] | 301 | |
---|
| 302 | print "\n"; |
---|
| 303 | |
---|
| 304 | print 'Updating schema_version to ' . $maj->{ver} . "\n"; |
---|
| 305 | |
---|
[881] | 306 | $dbi->do( |
---|
| 307 | 'UPDATE settings SET val = ? where varname = ?', |
---|
| 308 | undef, |
---|
| 309 | $maj->{ver}, 'schema_version' |
---|
| 310 | ); |
---|
[920] | 311 | print "Done\n\n"; |
---|
[881] | 312 | |
---|
| 313 | } |
---|
| 314 | |
---|
| 315 | print "Loading attribute from: $attributes_file\n"; |
---|
| 316 | |
---|
| 317 | open my $fh, "<:encoding(utf8)", $attributes_file or die "$attributes_file: $!"; |
---|
| 318 | |
---|
| 319 | my $csv = Text::CSV_XS->new(); |
---|
| 320 | |
---|
| 321 | while (my $row = $csv->getline($fh)) { |
---|
| 322 | my ($otype, $attribute, $comment) = @$row; |
---|
[947] | 323 | if ($labase->is_registered_attribute($otype, $attribute)) { |
---|
[881] | 324 | } else { |
---|
| 325 | $labase->register_attribute($otype, $attribute, $comment) |
---|
| 326 | or die "Error, aborting\n"; |
---|
| 327 | print "Attr. $attribute for object type $otype registred\n"; |
---|
| 328 | } |
---|
| 329 | } |
---|
| 330 | |
---|
| 331 | $csv->eof or do { |
---|
| 332 | $csv->error_diag(); |
---|
| 333 | die "Cannot load attribute\n" |
---|
| 334 | }; |
---|
| 335 | |
---|
[981] | 336 | foreach (@objects) { |
---|
| 337 | if (!$labase->get_object($_->{otype}, $_->{name})) { |
---|
| 338 | printf("Creatting object %s/%s\n", $_->{otype}, $_->{name}); |
---|
| 339 | $labase->create_object($_->{otype}, $_->{name}, %{$_->{attrs} || {}}) |
---|
| 340 | or die sprintf("cannot create %s/%s\n", $_->{otype}, $_->{name}); |
---|
| 341 | |
---|
| 342 | } |
---|
| 343 | } |
---|
| 344 | |
---|
[920] | 345 | if ($nocommit) { |
---|
| 346 | $dbi->rollback; |
---|
| 347 | } else { |
---|
| 348 | $dbi->commit; |
---|
| 349 | } |
---|
[881] | 350 | print "Process terminated successfully\n"; |
---|