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 | |
---|
14 | la-sql-attreg - Tools to register attributes in LA SQL base |
---|
15 | |
---|
16 | =head1 SYNOPSIS |
---|
17 | |
---|
18 | la-sql-attreg [options] name |
---|
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, |
---|
26 | 'no-commit' => \my $nocommit, |
---|
27 | 'v|verbose' => \my $verbose, |
---|
28 | ) or pod2usage(); |
---|
29 | |
---|
30 | $attributes_file ||= "$DATADIR/attributes.csv"; |
---|
31 | |
---|
32 | =head1 OPTIONS |
---|
33 | |
---|
34 | =over 4 |
---|
35 | |
---|
36 | =item -c|--config configdir |
---|
37 | |
---|
38 | Use this configuration directory instead of the default one. |
---|
39 | |
---|
40 | =item -b|--base basename |
---|
41 | |
---|
42 | Query this specific base instead of the default one. |
---|
43 | |
---|
44 | =back |
---|
45 | |
---|
46 | =cut |
---|
47 | |
---|
48 | $| = 1; # autoflush |
---|
49 | |
---|
50 | my $LA = LATMOS::Accounts->new($config, noacl => 1); |
---|
51 | my $labase = $LA->base($base); |
---|
52 | |
---|
53 | my $dbi = $labase->db; |
---|
54 | |
---|
55 | my $rev = $labase->get_global_value('schema_version') || 1; |
---|
56 | |
---|
57 | my @updates = ( |
---|
58 | { |
---|
59 | ver => 3, |
---|
60 | sql => [ |
---|
61 | q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT |
---|
62 | nethost_is_single_ip}, |
---|
63 | q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON nethost}, |
---|
64 | q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON netzone}, |
---|
65 | q{DROP FUNCTION IF EXISTS check_zone_name_unity()}, |
---|
66 | q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT |
---|
67 | nethost_is_single_ip}, |
---|
68 | q{INSERT INTO settings(varname, val) VALUES ('schema_version', 3)}, |
---|
69 | ], |
---|
70 | }, |
---|
71 | { |
---|
72 | ver => 4, |
---|
73 | sql => [ |
---|
74 | q{ |
---|
75 | CREATE OR REPLACE FUNCTION nethost_sort_fields() |
---|
76 | RETURNS trigger AS |
---|
77 | $BODY$BEGIN |
---|
78 | |
---|
79 | IF (TG_OP='INSERT') then |
---|
80 | IF (new.attr='ip') THEN |
---|
81 | insert into nethost_attributes_ips VALUES (new.*); |
---|
82 | RETURN NULL; |
---|
83 | END IF; |
---|
84 | |
---|
85 | IF (new.attr='macaddr') THEN |
---|
86 | insert into nethost_attributes_macs VALUES (new.*); |
---|
87 | RETURN NULL; |
---|
88 | END IF; |
---|
89 | |
---|
90 | IF (new.attr='owner') THEN |
---|
91 | insert into nethost_attributes_users VALUES (new.*); |
---|
92 | RETURN NULL; |
---|
93 | END IF; |
---|
94 | |
---|
95 | IF (new.attr='user') THEN |
---|
96 | insert into nethost_attributes_users VALUES (new.*); |
---|
97 | RETURN NULL; |
---|
98 | END IF; |
---|
99 | end if; |
---|
100 | |
---|
101 | |
---|
102 | if (TG_OP='DELETE') THEN |
---|
103 | RETURN old; |
---|
104 | else |
---|
105 | RETURN new; |
---|
106 | end if; |
---|
107 | END;$BODY$ |
---|
108 | LANGUAGE plpgsql VOLATILE |
---|
109 | COST 100; |
---|
110 | }, |
---|
111 | ], |
---|
112 | }, |
---|
113 | { |
---|
114 | ver => 5, |
---|
115 | sql => [ |
---|
116 | q{ |
---|
117 | CREATE OR REPLACE VIEW address_attributes AS |
---|
118 | ( ( SELECT address."user" AS value, 'user' AS attr, address.rev AS attr_key, address.ikey AS okey |
---|
119 | FROM address |
---|
120 | UNION ALL |
---|
121 | SELECT address.name AS value, 'name' AS attr, address.rev AS attr_key, address.ikey AS okey |
---|
122 | FROM address ) |
---|
123 | UNION ALL |
---|
124 | SELECT address_attributes.value, address_attributes.attr, address_attributes.attr_key, address_attributes.okey |
---|
125 | FROM address_attributes_base address_attributes ) |
---|
126 | UNION ALL |
---|
127 | SELECT '1' AS value, 'active' AS attr, address.rev AS attr_key, address.ikey AS okey |
---|
128 | FROM "user" JOIN address ON "user".name = address."user" |
---|
129 | WHERE "user".exported AND address.exported and |
---|
130 | ("user".expire IS NULL or "user".expire > now()) |
---|
131 | } |
---|
132 | ], |
---|
133 | }, |
---|
134 | { |
---|
135 | ver => 6, |
---|
136 | sql => [ |
---|
137 | q{ |
---|
138 | CREATE TABLE request |
---|
139 | ( |
---|
140 | id serial NOT NULL, |
---|
141 | "create" timestamp with time zone NOT NULL DEFAULT now(), |
---|
142 | name text NOT NULL, |
---|
143 | object text, |
---|
144 | apply timestamp with time zone NOT NULL DEFAULT now(), |
---|
145 | done timestamp with time zone, |
---|
146 | "user" text, |
---|
147 | CONSTRAINT request_pkey PRIMARY KEY (id ), |
---|
148 | CONSTRAINT request_name_fkey FOREIGN KEY (name) |
---|
149 | REFERENCES accreq (name) MATCH SIMPLE |
---|
150 | ON UPDATE CASCADE ON DELETE CASCADE, |
---|
151 | CONSTRAINT request_user_fkey FOREIGN KEY ("user") |
---|
152 | REFERENCES "user" (name) MATCH SIMPLE |
---|
153 | ON UPDATE CASCADE ON DELETE SET NULL |
---|
154 | ) |
---|
155 | }, |
---|
156 | q{ |
---|
157 | CREATE INDEX fki_request_name_fkey ON request |
---|
158 | USING btree (name ) |
---|
159 | }, |
---|
160 | q{ |
---|
161 | CREATE INDEX fki_request_user_fkey ON request |
---|
162 | USING btree ("user" ) |
---|
163 | }, |
---|
164 | q{ |
---|
165 | CREATE TABLE request_attributes |
---|
166 | ( |
---|
167 | reqid bigint, |
---|
168 | attribute text NOT NULL, |
---|
169 | value text, |
---|
170 | CONSTRAINT request_id_fkey FOREIGN KEY (reqid) |
---|
171 | REFERENCES request (id) MATCH SIMPLE |
---|
172 | ON UPDATE CASCADE ON DELETE CASCADE |
---|
173 | ) |
---|
174 | }, |
---|
175 | q{ |
---|
176 | CREATE INDEX fki_request_id_fkey ON request_attributes |
---|
177 | USING btree (reqid ) |
---|
178 | }, |
---|
179 | q{ |
---|
180 | delete from accreq_attributes_list |
---|
181 | } |
---|
182 | ], |
---|
183 | }, |
---|
184 | { |
---|
185 | ver => 7, |
---|
186 | sql => [ |
---|
187 | q{ |
---|
188 | ALTER TABLE request ADD COLUMN automated boolean NOT NULL DEFAULT false; |
---|
189 | }, |
---|
190 | q{ |
---|
191 | ALTER TABLE request ADD COLUMN objrev bigint; |
---|
192 | }, |
---|
193 | ], |
---|
194 | }, |
---|
195 | ); |
---|
196 | |
---|
197 | my @objects = ( |
---|
198 | { |
---|
199 | name => 'dpmt', |
---|
200 | otype => 'sutype', |
---|
201 | attrs => { |
---|
202 | description => 'Department', |
---|
203 | }, |
---|
204 | }, |
---|
205 | { |
---|
206 | name => 'contrattype', |
---|
207 | otype => 'sutype', |
---|
208 | attrs => { |
---|
209 | description => 'Contract', |
---|
210 | }, |
---|
211 | }, |
---|
212 | ); |
---|
213 | |
---|
214 | $dbi->rollback; |
---|
215 | |
---|
216 | foreach my $maj (@updates) { |
---|
217 | if ($rev >= $maj->{ver}) { |
---|
218 | next; |
---|
219 | } |
---|
220 | print "\n"; |
---|
221 | print 'Switching to schema revision: ' . $maj->{ver} . "\n"; |
---|
222 | foreach my $sql (@{ $maj->{sql} }) { |
---|
223 | { |
---|
224 | my $sqlv = $sql; |
---|
225 | $sqlv =~ s/^/ /mg; |
---|
226 | if ($verbose) { |
---|
227 | warn ' >' . $sqlv . "\n"; |
---|
228 | } else { |
---|
229 | print 'x'; |
---|
230 | } |
---|
231 | } |
---|
232 | $dbi->do($sql) or die "Erreur :\\" . $dbi->errstr . "\n"; |
---|
233 | } |
---|
234 | |
---|
235 | print "\n"; |
---|
236 | |
---|
237 | print 'Updating schema_version to ' . $maj->{ver} . "\n"; |
---|
238 | |
---|
239 | $dbi->do( |
---|
240 | 'UPDATE settings SET val = ? where varname = ?', |
---|
241 | undef, |
---|
242 | $maj->{ver}, 'schema_version' |
---|
243 | ); |
---|
244 | print "Done\n\n"; |
---|
245 | |
---|
246 | } |
---|
247 | |
---|
248 | print "Loading attribute from: $attributes_file\n"; |
---|
249 | |
---|
250 | open my $fh, "<:encoding(utf8)", $attributes_file or die "$attributes_file: $!"; |
---|
251 | |
---|
252 | my $csv = Text::CSV_XS->new(); |
---|
253 | |
---|
254 | while (my $row = $csv->getline($fh)) { |
---|
255 | my ($otype, $attribute, $comment) = @$row; |
---|
256 | if ($labase->is_registered_attribute($otype, $attribute)) { |
---|
257 | } else { |
---|
258 | $labase->register_attribute($otype, $attribute, $comment) |
---|
259 | or die "Error, aborting\n"; |
---|
260 | print "Attr. $attribute for object type $otype registred\n"; |
---|
261 | } |
---|
262 | } |
---|
263 | |
---|
264 | $csv->eof or do { |
---|
265 | $csv->error_diag(); |
---|
266 | die "Cannot load attribute\n" |
---|
267 | }; |
---|
268 | |
---|
269 | foreach (@objects) { |
---|
270 | if (!$labase->get_object($_->{otype}, $_->{name})) { |
---|
271 | printf("Creatting object %s/%s\n", $_->{otype}, $_->{name}); |
---|
272 | $labase->create_object($_->{otype}, $_->{name}, %{$_->{attrs} || {}}) |
---|
273 | or die sprintf("cannot create %s/%s\n", $_->{otype}, $_->{name}); |
---|
274 | |
---|
275 | } |
---|
276 | } |
---|
277 | |
---|
278 | if ($nocommit) { |
---|
279 | $dbi->rollback; |
---|
280 | } else { |
---|
281 | $dbi->commit; |
---|
282 | } |
---|
283 | print "Process terminated successfully\n"; |
---|