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;
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, PrintError => 0});
21 my $sth = $dbh->prepare("pragma integrity_check");
22 $sth->execute();
23 my @ret = $sth->fetchall_arrayref();
24 $sth->finish();
25 if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
26 &main::daemon_log("DEBUG: Database image $db_name is ok", 7);
27 } else {
28 &main::daemon_log("ERROR: Database image $db_name is malformed, creating new database.", 1);
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 defined($self) or ref($self) ne 'GOSA::DBsqlite') {
44 &main::daemon_log("0 ERROR: GOSA::DBsqlite::lock was called static! Statement was '$self'!", 1);
45 return;
46 }
47 if(not ref $self->{db_lock_handle}) {
48 sysopen($self->{db_lock_handle}, $self->{db_lock}, O_RDWR) or &main::daemon_log("0 ERROR: Opening the database ".$self->{db_name}." failed with $!", 1);
49 }
50 my $lock_result = flock($self->{db_lock_handle}, LOCK_EX);
51 if($lock_result==1) {
52 seek($self->{db_lock_handle}, 0, 2);
53 &main::daemon_log("0 DEBUG: Acquired lock for database ".$self->{db_name}, 8);
54 } else {
55 &main::daemon_log("0 ERROR: Could not acquire lock for database ".$self->{db_name}, 1);
56 }
57 return;
58 }
61 sub unlock {
62 my $self = shift;
63 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
64 &main::daemon_log("0 ERROR: GOSA::DBsqlite::unlock was called static! Statement was '$self'!", 1);
65 return;
66 }
67 if(not ref $self->{db_lock_handle}) {
68 &main::daemon_log("0 BIG ERROR: Lockfile for database ".$self->{db_name}."got closed within critical section!", 1);
69 }
70 flock($self->{db_lock_handle}, LOCK_UN);
71 &main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 8);
72 return;
73 }
76 sub create_table {
77 my $self = shift;
78 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
79 &main::daemon_log("0 ERROR: GOSA::DBsqlite::create_table was called static! Statement was '$self'!", 1);
80 return;
81 }
82 my $table_name = shift;
83 my $col_names_ref = shift;
84 my $index_names_ref = shift || undef;
85 my @col_names;
86 my @col_names_creation;
87 foreach my $col_name (@$col_names_ref) {
88 push(@col_names, $col_name);
89 }
91 $col_names->{ $table_name } = \@col_names;
92 my $col_names_string = join(", ", @col_names);
93 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
94 my $res = $self->exec_statement($sql_statement);
96 # Add indices
97 if(defined($index_names_ref) and ref($index_names_ref) eq 'ARRAY') {
98 foreach my $index_name (@$index_names_ref) {
99 $self->exec_statement("CREATE ".(($index_name eq 'id')?'UNIQUE':'')." INDEX IF NOT EXISTS $index_name on $table_name ($index_name);");
100 }
101 }
103 return 0;
104 }
107 sub add_dbentry {
108 my $self = shift;
109 my $arg = shift;
110 my $res = 0; # default value
112 # if dbh not specified, return errorflag 1
113 my $table = $arg->{table};
114 if( not defined $table ) {
115 return 1 ;
116 }
118 # if timestamp is not provided, add timestamp
119 if( not exists $arg->{timestamp} ) {
120 $arg->{timestamp} = &get_time;
121 }
123 # check primkey and run insert or update
124 my $primkeys = $arg->{'primkey'};
125 my $prim_statement="";
126 if( 0 != @$primkeys ) { # more than one primkey exist in list
127 my @prim_list;
128 foreach my $primkey (@$primkeys) {
129 if( not exists $arg->{$primkey} ) {
130 return (3, "primkey '$primkey' has no value for add_dbentry");
131 }
132 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
133 }
134 $prim_statement = "WHERE ".join(" AND ", @prim_list);
136 # check wether primkey is unique in table, otherwise return errorflag
137 my $sql_statement = "SELECT * FROM $table $prim_statement";
138 $res = @{ $self->exec_statement($sql_statement) };
139 }
141 # primkey is unique or no primkey specified -> run insert
142 if ($res == 0) {
143 # fetch column names of table
144 my $col_names = &get_table_columns($self, $table);
146 my $create_id=0;
147 foreach my $col_name (@{$col_names}) {
148 if($col_name eq "id" && (! exists $arg->{$col_name})) {
149 $create_id=1;
150 }
151 }
152 # assign values to column name variables
153 my @col_list;
154 my @val_list;
155 foreach my $col_name (@{$col_names}) {
156 # use function parameter for column values
157 if (exists $arg->{$col_name}) {
158 push(@col_list, "'".$col_name."'");
159 push(@val_list, "'".$arg->{$col_name}."'");
160 }
161 }
163 my $sql_statement;
164 if($create_id==1) {
165 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
166 } else {
167 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
168 }
169 my $db_res;
170 my $success=0;
171 $self->lock();
172 eval {
173 my $sth = $self->{dbh}->prepare($sql_statement);
174 $db_res = $sth->execute();
175 $sth->finish();
176 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9);
177 $success = 1;
178 };
179 if($@) {
180 eval {
181 $self->{dbh}->do("ANALYZE");
182 $self->{dbh}->do("VACUUM");
183 };
184 }
185 if($success==0) {
186 eval {
187 my $sth = $self->{dbh}->prepare($sql_statement);
188 $db_res = $sth->execute();
189 $sth->finish();
190 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9);
191 $success = 1;
192 };
193 if($@) {
194 eval {
195 $self->{dbh}->do("ANALYZE");
196 $self->{dbh}->do("VACUUM");
197 };
198 }
199 }
200 if($success==0) {
201 eval {
202 my $sth = $self->{dbh}->prepare($sql_statement);
203 $db_res = $sth->execute();
204 $sth->finish();
205 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 7);
206 $success = 1;
207 };
208 if($@) {
209 &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
210 }
211 }
212 $self->unlock();
214 if( $db_res != 1 ) {
215 return (4, $sql_statement);
216 }
218 # entry already exists -> run update
219 } else {
220 my @update_l;
221 while( my ($pram, $val) = each %{$arg} ) {
222 if( $pram eq 'table' ) { next; }
223 if( $pram eq 'primkey' ) { next; }
224 push(@update_l, "$pram='$val'");
225 }
226 my $update_str= join(", ", @update_l);
227 $update_str= " SET $update_str";
229 my $sql_statement= "UPDATE $table $update_str $prim_statement";
230 my $db_res = &update_dbentry($self, $sql_statement );
231 }
233 return 0;
234 }
237 sub update_dbentry {
238 my ($self, $sql)= @_;
239 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
240 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
241 return;
242 }
243 my $db_answer= $self->exec_statement($sql);
244 return $db_answer;
245 }
248 sub del_dbentry {
249 my ($self, $sql)= @_;;
250 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
251 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
252 return;
253 }
254 my $db_res= $self->exec_statement($sql);
255 return $db_res;
256 }
259 sub get_table_columns {
260 my $self = shift;
261 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
262 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
263 return;
264 }
265 my $table = shift;
266 my @column_names;
268 if(exists $col_names->{$table}) {
269 foreach my $col_name (@{$col_names->{$table}}) {
270 push @column_names, ($1) if $col_name =~ /^(.*?)\s.*$/;
271 }
272 } else {
273 my @res;
274 foreach my $column ( $self->exec_statement ( "pragma table_info('$table')" ) ) {
275 push(@column_names, @$column[1]);
276 }
277 }
279 return \@column_names;
280 }
283 sub select_dbentry {
284 my ($self, $sql)= @_;
285 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
286 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
287 return;
288 }
289 my $error= 0;
290 my $answer= {};
291 my $db_answer= $self->exec_statement($sql);
292 my @column_list;
294 # fetch column list of db and create a hash with column_name->column_value of the select query
295 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
296 my $selected_cols = $1;
297 my $table = $2;
299 # all columns are used for creating answer
300 if ($selected_cols eq '*') {
301 @column_list = @{ $self->get_table_columns($table) };
303 # specific columns are used for creating answer
304 } else {
305 # remove all blanks and split string to list of column names
306 $selected_cols =~ s/ //g;
307 @column_list = split(/,/, $selected_cols);
308 }
310 # create answer
311 my $hit_counter = 0;
312 my $list_len = @column_list;
313 foreach my $hit ( @{$db_answer} ){
314 $hit_counter++;
315 for ( my $i = 0; $i < $list_len; $i++) {
316 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
317 }
318 }
320 return $answer;
321 }
324 sub show_table {
325 my $self = shift;
326 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
327 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
328 return;
329 }
330 my $table_name = shift;
332 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
333 my $res= $self->exec_statement($sql_statement);
334 my @answer;
335 foreach my $hit (@{$res}) {
336 push(@answer, "hit: ".join(', ', @{$hit}));
337 }
339 return join("\n", @answer);
340 }
343 sub recreate_database {
344 my $self = shift;
345 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
346 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
347 return;
348 }
350 my $table_content;
352 # Query all tables
353 eval {
354 my $sth = $self->{dbh}->prepare("select name from sqlite_master where type='table';");
355 $sth->execute();
356 my ($tables) = @{$sth->fetchall_arrayref()};
357 foreach my $table (@$tables) {
358 if(defined($col_names->{$table})) {
359 # Schema definition for table exists, recreation is possible
360 my @column_names;
361 foreach my $column (@{$col_names->{$table}}) {
362 push @column_names, ($1) if $column =~ /(.*?)\s.*/;
363 }
364 my $column_query = join(',',@column_names);
365 my $sql = "SELECT $column_query FROM $table";
366 my $sth = $self->{dbh}->prepare($sql);
367 $sth->execute();
368 while (my @row = $sth->fetchrow_array()) {
369 push @{$table_content->{$table}}, @row;
370 }
371 $sth->finish;
372 }
373 }
375 # Delete the database file
376 $self->{dbh}->disconnect();
377 unlink($self->{db_name});
379 # Create a new database file
380 my $dbh = DBI->connect("dbi:SQLite:dbname=".$self->{db_name}, "", "", {RaiseError => 1, AutoCommit => 1});
381 $self->{dbh} = $dbh;
383 # Fill with contents
384 foreach my $table (@$tables) {
385 # Create schema
386 my $sql = "CREATE TABLE IF NOT EXISTS $table (".join(", ", @{$col_names->{$table}}).")";
387 my $sth = $self->{dbh}->prepare($sql);
388 $sth->execute();
390 # Insert Dump
391 if(defined($table_content->{$table})) {
392 &main::daemon_log("0 DEBUG: Filling table ".$self->{db_name}.".$table with dump.", 7);
393 my %insert_hash;
394 my $i=0;
395 foreach my $row ($table_content->{$table}) {
396 foreach my $column (@{$col_names->{$table}}) {
397 my $column_name = $1 if $column =~ /(.*?)\s.*/;
398 $insert_hash{$column_name} = defined(@$row[$i])?@$row[$i]:undef;
399 $i++;
400 }
401 my @values;
402 my $column_query = join(",",keys %insert_hash);
403 foreach my $column(keys %insert_hash) {
404 push @values, $insert_hash{$column};
405 }
406 my $value_query = join("', '", @values);
407 my $sql = "INSERT INTO $table ($column_query) VALUES ('$value_query')";
408 my $sth = $self->{dbh}->prepare($sql);
409 $sth->execute;
410 }
411 } else {
412 &main::daemon_log("0 DEBUG: Table ".$self->{db_name}.".$table was empty.", 7);
413 }
414 }
415 };
416 if($@) {
417 print STDERR Dumper($@);
418 }
420 return;
421 }
424 sub exec_statement {
425 my $self = shift;
426 my $sql_statement = shift;
427 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
428 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
429 return;
430 }
432 if(not defined($sql_statement) or length($sql_statement) == 0) {
433 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
434 return;
435 }
437 my @db_answer;
438 my $success= 0;
439 $self->lock();
440 # Give three chances to the sqlite database
441 # 1st chance
442 eval {
443 my $sth = $self->{dbh}->prepare($sql_statement);
444 my $res = $sth->execute();
445 @db_answer = @{$sth->fetchall_arrayref()};
446 $sth->finish();
447 $success=1;
448 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
449 };
450 if($@) {
451 eval {
452 $self->{dbh}->do("ANALYZE");
453 $self->{dbh}->do("VACUUM");
454 };
455 }
456 if($success) {
457 $self->unlock();
458 return \@db_answer ;
459 }
461 # 2nd chance
462 eval {
463 usleep(200);
464 my $sth = $self->{dbh}->prepare($sql_statement);
465 my $res = $sth->execute();
466 @db_answer = @{$sth->fetchall_arrayref()};
467 $sth->finish();
468 $success=1;
469 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
470 };
471 if($@) {
472 $self->recreate_database();
473 }
474 if($success) {
475 $self->unlock();
476 return \@db_answer ;
477 }
479 # 3rd chance
480 eval {
481 usleep(200);
482 DBI->trace(6) if($main::verbose >= 7);
483 my $sth = $self->{dbh}->prepare($sql_statement);
484 my $res = $sth->execute();
485 @db_answer = @{$sth->fetchall_arrayref()};
486 $sth->finish();
487 DBI->trace(0);
488 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
489 };
490 if($@) {
491 DBI->trace(0);
492 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
493 }
494 # TODO : maybe an error handling and an erro feedback to invoking function
495 #my $error = @$self->{dbh}->err;
496 #if ($error) {
497 # my $error_string = @$self->{dbh}->errstr;
498 #}
500 $self->unlock();
501 return \@db_answer;
502 }
505 sub exec_statementlist {
506 my $self = shift;
507 my $sql_list = shift;
508 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
509 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
510 return;
511 }
512 my @db_answer;
514 foreach my $sql_statement (@$sql_list) {
515 if(defined($sql_statement) && length($sql_statement) > 0) {
516 push @db_answer, $self->exec_statement($sql_statement);
517 } else {
518 next;
519 }
520 }
522 return \@db_answer;
523 }
526 sub count_dbentries {
527 my ($self, $table)= @_;
528 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
529 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
530 return;
531 }
532 my $error= 0;
533 my $count= -1;
535 my $sql_statement= "SELECT count() FROM $table";
536 my $db_answer= $self->select_dbentry($sql_statement);
537 if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
538 $count = $db_answer->{1}->{'count()'};
539 }
541 return $count;
542 }
545 sub move_table {
546 my ($self, $from, $to) = @_;
547 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
548 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
549 return;
550 }
552 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
553 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
554 my $success = 0;
556 $self->lock();
557 eval {
558 $self->{dbh}->begin_work();
559 $self->{dbh}->do($sql_statement_drop);
560 $self->{dbh}->do($sql_statement_alter);
561 $self->{dbh}->commit();
562 $success = 1;
563 };
564 if($@) {
565 $self->{dbh}->rollback();
566 eval {
567 $self->{dbh}->do("ANALYZE");
568 };
569 if($@) {
570 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
571 }
572 eval {
573 $self->{dbh}->do("VACUUM");
574 };
575 if($@) {
576 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
577 }
578 }
580 if($success == 0) {
581 eval {
582 $self->{dbh}->begin_work();
583 $self->{dbh}->do($sql_statement_drop);
584 $self->{dbh}->do($sql_statement_alter);
585 $self->{dbh}->commit();
586 $success = 1;
587 };
588 if($@) {
589 $self->{dbh}->rollback();
590 eval {
591 $self->{dbh}->do("ANALYZE");
592 };
593 if($@) {
594 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
595 }
596 eval {
597 $self->{dbh}->do("VACUUM");
598 };
599 if($@) {
600 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
601 }
602 }
603 }
605 if($success == 0) {
606 eval {
607 $self->{dbh}->begin_work();
608 $self->{dbh}->do($sql_statement_drop);
609 $self->{dbh}->do($sql_statement_alter);
610 $self->{dbh}->commit();
611 $success = 1;
612 };
613 if($@) {
614 $self->{dbh}->rollback();
615 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
616 }
617 }
619 &main::daemon_log("0 INFO: GOSA::DBsqlite::move_table: Operation successful!", 7);
620 $self->unlock();
622 return;
623 }
626 1;