1 package GOSA::DBsqlite;
3 use strict;
4 use warnings;
5 use Carp;
6 use DBI;
7 use Data::Dumper;
8 use GOSA::GosaSupportDaemon;
9 use Time::HiRes qw(usleep);
10 use Fcntl qw/:DEFAULT :flock/; # import LOCK_* constants
12 my $col_names = {};
14 sub new {
15 my $class = shift;
16 my $db_name = shift;
18 my $lock = $db_name.".si.lock";
19 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
20 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1});
21 my $sth = $dbh->prepare("pragma integrity_check");
22 $sth->execute();
23 my @ret = $sth->fetchall_arrayref();
24 if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
25 &main::daemon_log("DEBUG: Database image $db_name is ok", 7);
26 } else {
27 &main::daemon_log("ERROR: Database image $db_name is malformed, creating new database.", 1);
28 $sth->finish();
29 $dbh->disconnect();
30 unlink($db_name);
31 $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1});
32 }
33 $self->{dbh} = $dbh;
34 $self->{db_name} = $db_name;
35 $self->{db_lock} = $lock;
36 bless($self,$class);
37 return($self);
38 }
41 sub lock {
42 my $self = shift;
43 if(not ref $self->{db_lock_handle}) {
44 sysopen($self->{db_lock_handle}, $self->{db_lock}, O_RDWR|O_CREAT, 0600) or &main::daemon_log("0 ERROR: Opening the lockfile for database ".$self->{db_name}." failed with $!", 1);
45 }
46 flock($self->{db_lock_handle}, LOCK_EX);
47 seek($self->{db_lock_handle}, 0, 2);
48 &main::daemon_log("0 DEBUG: Got lock for database ".$self->{db_name}, 7);
49 return;
50 }
53 sub unlock {
54 my $self = shift;
55 if(not ref $self->{db_lock_handle}) {
56 &main::daemon_log("0 BIG ERROR: Lockfile for database ".$self->{db_name}."got closed within critical section!", 1);
57 }
58 flock($self->{db_lock_handle}, LOCK_UN);
59 &main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 7);
60 return;
61 }
64 sub create_table {
65 my $self = shift;
66 my $table_name = shift;
67 my $col_names_ref = shift;
68 my @col_names;
69 my @col_names_creation;
70 foreach my $col_name (@$col_names_ref) {
71 # Save full column description for creation of database
72 push(@col_names_creation, $col_name);
73 my @t = split(" ", $col_name);
74 my $column_name = $t[0];
75 # Save column name internally for select_dbentry
76 push(@col_names, $column_name);
77 }
79 $col_names->{ $table_name } = \@col_names;
80 my $col_names_string = join(", ", @col_names_creation);
81 $self->lock();
82 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
83 eval {
84 my $res = $self->{dbh}->do($sql_statement);
85 };
86 if($@) {
87 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
88 eval {
89 $self->{dbh}->do("ANALYZE");
90 };
91 if($@) {
92 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
93 }
94 eval {
95 $self->{dbh}->do("VACUUM");
96 };
97 if($@) {
98 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
99 }
100 eval {
101 my $res = $self->{dbh}->do($sql_statement);
102 };
103 if($@) {
104 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
105 }
106 }
107 $self->unlock();
109 return 0;
110 }
113 sub add_dbentry {
114 my $self = shift;
115 my $arg = shift;
116 my $res = 0; # default value
118 # if dbh not specified, return errorflag 1
119 my $table = $arg->{table};
120 if( not defined $table ) {
121 return 1 ;
122 }
124 # if timestamp is not provided, add timestamp
125 if( not exists $arg->{timestamp} ) {
126 $arg->{timestamp} = &get_time;
127 }
129 # check primkey and run insert or update
130 my $primkeys = $arg->{'primkey'};
131 my $prim_statement="";
132 if( 0 != @$primkeys ) { # more than one primkey exist in list
133 my @prim_list;
134 foreach my $primkey (@$primkeys) {
135 if( not exists $arg->{$primkey} ) {
136 return (3, "primkey '$primkey' has no value for add_dbentry");
137 }
138 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
139 }
140 $prim_statement = "WHERE ".join(" AND ", @prim_list);
142 # check wether primkey is unique in table, otherwise return errorflag
143 my $sql_statement = "SELECT * FROM $table $prim_statement";
144 $self->lock();
145 eval {
146 my $sth = $self->{dbh}->prepare($sql_statement) or &main::daemon_log("0 ERROR: Preparation of statement '$sql_statement' failed with $!", 1);
147 $sth->execute() or &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $!", 1);
148 $res = @{ $sth->fetchall_arrayref() };
149 };
150 if($@) {
151 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
152 $self->{dbh}->do("ANALYZE");
153 eval {
154 my $sth = $self->{dbh}->prepare($sql_statement) or &main::daemon_log("0 ERROR: Preparation of statement '$sql_statement' failed with $!", 1);
155 $sth->execute() or &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $!", 1);
156 $res = @{ $sth->fetchall_arrayref() };
157 };
158 if($@) {
159 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
160 }
161 }
162 $self->unlock();
163 }
165 # primkey is unique or no primkey specified -> run insert
166 if ($res == 0) {
167 # fetch column names of table
168 my $col_names = &get_table_columns($self, $table);
170 my $create_id=0;
171 foreach my $col_name (@{$col_names}) {
172 if($col_name eq "id" && (! exists $arg->{$col_name})) {
173 #&main::daemon_log("0 DEBUG: id field found without value! Creating autoincrement statement!", 7);
174 $create_id=1;
175 }
176 }
178 # assign values to column name variables
179 my @col_list;
180 my @val_list;
181 foreach my $col_name (@{$col_names}) {
182 # use function parameter for column values
183 if (exists $arg->{$col_name}) {
184 push(@col_list, "'".$col_name."'");
185 push(@val_list, "'".$arg->{$col_name}."'");
186 }
187 }
189 my $sql_statement;
190 if($create_id==1) {
191 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
192 } else {
193 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
194 }
195 my $db_res;
196 $self->lock();
197 eval {
198 my $sth = $self->{dbh}->prepare($sql_statement) or &main::daemon_log("0 ERROR: Preparation of statement '$sql_statement' failed with $!", 1);
199 $db_res = $sth->execute() or &main::daemon("0 ERROR: Execution of statement '$sql_statement' failed with $!", 1);
200 };
201 if($@) {
202 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
203 eval {
204 $self->{dbh}->do("ANALYZE");
205 };
206 if($@) {
207 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
208 }
209 eval {
210 $self->{dbh}->do("VACUUM");
211 };
212 if($@) {
213 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
214 }
215 eval {
216 my $sth = $self->{dbh}->prepare($sql_statement) or &main::daemon_log("0 ERROR: Preparation of statement '$sql_statement' failed with $!", 1);
217 $db_res = $sth->execute() or &main::daemon("0 ERROR: Execution of statement '$sql_statement' failed with $!", 1);
218 };
219 if($@) {
220 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
221 }
222 }
223 $self->unlock();
225 if( $db_res != 1 ) {
226 return (4, $sql_statement);
227 }
229 # entry already exists -> run update
230 } else {
231 my @update_l;
232 while( my ($pram, $val) = each %{$arg} ) {
233 if( $pram eq 'table' ) { next; }
234 if( $pram eq 'primkey' ) { next; }
235 push(@update_l, "$pram='$val'");
236 }
237 my $update_str= join(", ", @update_l);
238 $update_str= " SET $update_str";
240 my $sql_statement= "UPDATE $table $update_str $prim_statement";
241 my $db_res = &update_dbentry($self, $sql_statement );
242 }
244 return 0;
245 }
248 sub update_dbentry {
249 my ($self, $sql)= @_;
250 my $db_answer= &exec_statement($self, $sql);
251 return $db_answer;
252 }
255 sub del_dbentry {
256 my ($self, $sql)= @_;;
257 my $db_res= &exec_statement($self, $sql);
258 return $db_res;
259 }
262 sub get_table_columns {
263 my $self = shift;
264 my $table = shift;
265 my @column_names;
267 if(exists $col_names->{$table}) {
268 @column_names = @{$col_names->{$table}};
269 } else {
270 my @res;
271 foreach my $column ( &exec_statement ( "pragma table_info('$table')" ) ) {
272 push(@column_names, @$column[1]);
273 }
274 }
275 return \@column_names;
277 }
280 sub select_dbentry {
281 my ($self, $sql)= @_;
282 my $error= 0;
283 my $answer= {};
284 my $db_answer= &exec_statement($self, $sql);
285 my @column_list;
287 # fetch column list of db and create a hash with column_name->column_value of the select query
288 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
289 my $selected_cols = $1;
290 my $table = $2;
292 # all columns are used for creating answer
293 if ($selected_cols eq '*') {
294 @column_list = @{ &get_table_columns($self, $table) };
296 # specific columns are used for creating answer
297 } else {
298 # remove all blanks and split string to list of column names
299 $selected_cols =~ s/ //g;
300 @column_list = split(/,/, $selected_cols);
301 }
303 # create answer
304 my $hit_counter = 0;
305 my $list_len = @column_list;
306 foreach my $hit ( @{$db_answer} ){
307 $hit_counter++;
308 for ( my $i = 0; $i < $list_len; $i++) {
309 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
310 }
311 }
313 return $answer;
314 }
317 sub show_table {
318 my $self = shift;
319 my $table_name = shift;
321 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
322 my $res= &exec_statement($self, $sql_statement);
323 my @answer;
324 foreach my $hit (@{$res}) {
325 push(@answer, "hit: ".join(', ', @{$hit}));
326 }
328 return join("\n", @answer);
329 }
332 sub exec_statement {
333 my $self = shift;
334 my $sql_statement = shift;
335 my @db_answer;
337 $self->lock();
338 eval {
339 my $sth = $self->{dbh}->prepare($sql_statement) or &main::daemon_log("0 ERROR: Preparation of statement '$sql_statement' failed with $!", 1);
340 my $res = $sth->execute() or &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $!", 1);
341 @db_answer = @{$sth->fetchall_arrayref()};
342 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
343 };
344 if($@) {
345 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
346 eval {
347 $self->{dbh}->do("ANALYZE");
348 };
349 if($@) {
350 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
351 }
352 eval {
353 $self->{dbh}->do("VACUUM");
354 };
355 if($@) {
356 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
357 }
358 eval {
359 my $sth = $self->{dbh}->prepare($sql_statement) or &main::daemon_log("0 ERROR: Preparation of statement '$sql_statement' failed with $!", 1);
360 $sth->execute() or &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $!", 1);
361 @db_answer = @{$sth->fetchall_arrayref()};
362 };
363 if($@) {
364 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
365 }
366 }
367 $self->unlock();
368 # TODO : maybe an error handling and an erro feedback to invoking function
369 #my $error = @$self->{dbh}->err;
370 #if ($error) {
371 # my $error_string = @$self->{dbh}->errstr;
372 #}
374 return \@db_answer;
375 }
378 sub exec_statementlist {
379 my $self = shift;
380 my $sql_list = shift;
381 my @db_answer;
383 foreach my $sql (@$sql_list) {
384 if(defined($sql) && length($sql) > 0) {
385 # Obtain a new lock for each statement to not block the db for a too long time
386 $self->lock();
387 eval {
388 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
389 push @db_answer, @answer;
390 };
391 if($@) {
392 $self->{dbh}->do("ANALYZE");
393 eval {
394 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
395 push @db_answer, @answer;
396 };
397 if($@) {
398 &main::daemon_log("ERROR: $sql failed with $@", 1);
399 }
400 }
401 $self->unlock();
402 } else {
403 next;
404 }
405 }
407 return \@db_answer;
408 }
411 sub count_dbentries {
412 my ($self, $table)= @_;
413 my $error= 0;
414 my $count= -1;
416 my $sql_statement= "SELECT count() FROM $table";
417 my $db_answer= &select_dbentry($self, $sql_statement);
418 if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
419 $count = $db_answer->{1}->{'count()'};
420 }
422 return $count;
423 }
426 sub move_table {
427 my ($self, $from, $to) = @_;
429 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
430 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
432 $self->lock();
433 eval {
434 $self->{dbh}->do($sql_statement_drop);
435 };
436 if($@) {
437 eval {
438 $self->{dbh}->do("ANALYZE");
439 };
440 if($@) {
441 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
442 }
443 eval {
444 $self->{dbh}->do("VACUUM");
445 };
446 if($@) {
447 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
448 }
449 eval {
450 $self->{dbh}->do($sql_statement_drop);
451 };
452 if($@) {
453 &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1);
454 }
455 }
457 eval {
458 $self->{dbh}->do($sql_statement_alter);
459 };
460 if($@) {
461 eval {
462 $self->{dbh}->do("ANALYZE");
463 };
464 if($@) {
465 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
466 }
467 eval {
468 $self->{dbh}->do("VACUUM");
469 };
470 if($@) {
471 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
472 }
473 eval {
474 $self->{dbh}->do($sql_statement_alter);
475 };
476 if($@) {
477 &main::daemon_log("ERROR: $sql_statement_alter failed with $@", 1);
478 }
479 }
480 $self->unlock();
482 return;
483 }
486 1;