1 package GOSA::DBsqlite;
3 use strict;
4 use warnings;
5 use Carp;
6 use DBI;
7 use GOSA::GosaSupportDaemon;
8 use Time::HiRes qw(usleep);
9 use Data::Dumper;
10 use Fcntl qw/:DEFAULT :flock/; # import LOCK_* constants
12 our $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, PrintError => 0});
22 $self->{dbh} = $dbh;
23 $self->{db_name} = $db_name;
24 $self->{db_lock} = $lock;
25 bless($self,$class);
27 my $sth = $self->{dbh}->prepare("pragma integrity_check");
28 $sth->execute();
29 my @ret = $sth->fetchall_arrayref();
30 $sth->finish();
31 if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
32 &main::daemon_log("0 DEBUG: Database disk image '".$self->{db_name}."' is ok.", 7);
33 } else {
34 &main::daemon_log("0 ERROR: Database disk image '".$self->{db_name}."' is malformed, creating new database!", 1);
35 $self->{dbh}->disconnect() or &main::daemon_log("0 ERROR: Could not disconnect from database '".$self->{db_name}."'!", 1);
36 $self->{dbh}= undef;
37 unlink($db_name);
38 }
39 return($self);
40 }
43 sub connect {
44 my $self = shift;
45 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
46 &main::daemon_log("0 ERROR: GOSA::DBsqlite::connect was called static! Argument was '$self'!", 1);
47 return;
48 }
50 $self->{dbh} = DBI->connect("dbi:SQLite:dbname=".$self->{db_name}, "", "", {PrintError => 0, RaiseError => 1, AutoCommit => 1}) or
51 &main::daemon_log("0 ERROR: Could not connect to database '".$self->{db_name}."'!", 1);
53 return;
54 }
57 sub disconnect {
58 my $self = shift;
59 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
60 &main::daemon_log("0 ERROR: GOSA::DBsqlite::disconnect was called static! Argument was '$self'!", 1);
61 return;
62 }
64 eval {
65 $self->{dbh}->disconnect();
66 };
67 if($@) {
68 &main::daemon_log("ERROR: Could not disconnect from database '".$self->{db_name}."'!", 1);
69 }
71 $self->{dbh}= undef;
73 return;
74 }
77 sub lock {
78 my $self = shift;
79 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
80 &main::daemon_log("0 ERROR: GOSA::DBsqlite::lock was called static! Argument was '$self'!", 1);
81 return;
82 }
84 if(not ref $self->{db_lock_handle} or not fileno $self->{db_lock_handle}) {
85 sysopen($self->{db_lock_handle}, $self->{db_lock}, O_RDWR | O_CREAT, 0600) or &main::daemon_log("0 ERROR: Opening the database ".$self->{db_name}." failed with $!", 1);
86 }
87 get_lock:
88 my $lock_result = flock($self->{db_lock_handle}, LOCK_EX | LOCK_NB);
89 if(not $lock_result) {
90 &main::daemon_log("0 ERROR: Could not acquire lock for database ".$self->{db_name}, 1);
91 usleep(250+rand(500));
92 goto get_lock;
93 } else {
94 seek($self->{db_lock_handle}, 0, 2);
95 &main::daemon_log("0 DEBUG: Acquired lock for database ".$self->{db_name}, 8);
96 $self->connect();
97 }
98 return;
99 }
102 sub unlock {
103 my $self = shift;
104 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
105 &main::daemon_log("0 ERROR: GOSA::DBsqlite::unlock was called static! Argument was '$self'!", 1);
106 return;
107 }
108 if(not ref $self->{db_lock_handle}) {
109 &main::daemon_log("0 BIG ERROR: Lockfile for database ".$self->{db_name}."got closed within critical section!", 1);
110 }
111 flock($self->{db_lock_handle}, LOCK_UN);
112 &main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 8);
113 $self->disconnect();
114 return;
115 }
118 sub create_table {
119 my $self = shift;
120 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
121 &main::daemon_log("0 ERROR: GOSA::DBsqlite::create_table was called static! Statement was '$self'!", 1);
122 return;
123 }
124 my $table_name = shift;
125 my $col_names_ref = shift;
126 my $index_names_ref = shift || undef;
127 my @col_names;
128 my @col_names_creation;
129 foreach my $col_name (@$col_names_ref) {
130 push(@col_names, $col_name);
131 }
132 $col_names->{ $table_name } = \@col_names;
134 # Not activated yet
135 # Check schema
136 # if($self->check_schema($table_name)) {
137 # $self->exec_statement("DROP TABLE $table_name");
138 # &main::daemon_log("WARNING: Schema of table $table_name has changed! Table will be recreated!", 3);
139 # }
141 my $col_names_string = join(", ", @col_names);
142 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
143 my $res = $self->exec_statement($sql_statement);
145 # Add indices
146 if(defined($index_names_ref) and ref($index_names_ref) eq 'ARRAY') {
147 foreach my $index_name (@$index_names_ref) {
148 $self->exec_statement("CREATE ".(($index_name eq 'id')?'UNIQUE':'')." INDEX IF NOT EXISTS $index_name on $table_name ($index_name);");
149 }
150 }
152 return 0;
153 }
156 # sub check_schema {
157 # my $self = shift;
158 # my $result = 1;
159 # if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
160 # &main::daemon_log("0 ERROR: GOSA::DBsqlite::check_schema was called static! Argument was '$self'!", 1);
161 # return $result;
162 # }
163 #
164 # my $table_name = shift || undef;
165 # my $table_columns;
166 #
167 # if($table_name and $col_names->{$table_name}) {
168 # # Query the table_info from database
169 # foreach my $column ( @{ $self->exec_statement ( "pragma table_info('$table_name')" ) } ) {
170 # my $column_name = @$column[1];
171 # my $data_type = @$column[2];
172 # $table_columns->{$column_name}= $data_type;
173 # }
174 #
175 # foreach my $column (@{$col_names->{$table_name}}) {
176 # my ($column_name, $datatype) = split(/\s/, $column);
177 # if(exists($table_columns->{$column_name})) {
178 # if(lc $datatype eq lc $table_columns->{$column_name}) {
179 # next;
180 # } else {
181 # &main::daemon_log("WARNING: Column '$column_name' has wrong datatype!", 1);
182 # $result = 0;
183 # }
184 # } else {
185 # $result = 0;
186 # &main::daemon_log("WARNING: Column '$column_name' is missing!", 1);
187 # #&main::daemon_log("DEBUG Columns are not equal! Column '".$self->{db_name}.".$column_name' is missing!", 0);
188 # #my $sql_statement = "ALTER TABLE $table_name ADD COLUMN $column_name $datatype";
189 # #$self->exec_statement($sql_statement);
190 # # The ALTER TABLE statement sucks completely in SQLite, so just recreate the table
191 # }
192 # }
193 # }
194 #
195 # return $result;
196 # }
199 sub add_dbentry {
200 my $self = shift;
201 my $arg = shift;
202 my $res = 0; # default value
204 # if dbh not specified, return errorflag 1
205 my $table = $arg->{table};
206 if( not defined $table ) {
207 return 1 ;
208 }
210 # if timestamp is not provided, add timestamp
211 if( not exists $arg->{timestamp} ) {
212 $arg->{timestamp} = &get_time;
213 }
215 # check primkey and run insert or update
216 my $primkeys = $arg->{'primkey'};
217 my $prim_statement="";
218 if( 0 != @$primkeys ) { # more than one primkey exist in list
219 my @prim_list;
220 foreach my $primkey (@$primkeys) {
221 if( not exists $arg->{$primkey} ) {
222 return (3, "primkey '$primkey' has no value for add_dbentry");
223 }
224 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
225 }
226 $prim_statement = "WHERE ".join(" AND ", @prim_list);
228 # check wether primkey is unique in table, otherwise return errorflag
229 my $sql_statement = "SELECT * FROM $table $prim_statement";
230 $res = @{ $self->exec_statement($sql_statement) };
231 }
233 # primkey is unique or no primkey specified -> run insert
234 if ($res == 0) {
235 # fetch column names of table
236 my $col_names = &get_table_columns($self, $table);
238 my $create_id=0;
239 foreach my $col_name (@{$col_names}) {
240 if($col_name eq "id" && (! exists $arg->{$col_name})) {
241 $create_id=1;
242 }
243 }
244 # assign values to column name variables
245 my @col_list;
246 my @val_list;
247 foreach my $col_name (@{$col_names}) {
248 # use function parameter for column values
249 if (exists $arg->{$col_name}) {
250 push(@col_list, "'".$col_name."'");
251 push(@val_list, "'".$arg->{$col_name}."'");
252 }
253 }
255 my $sql_statement;
256 if($create_id==1) {
257 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
258 } else {
259 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
260 }
261 my $db_res;
262 my $success=0;
263 $self->lock();
264 eval {
265 my $sth = $self->{dbh}->prepare($sql_statement);
266 $db_res = $sth->execute();
267 $sth->finish();
268 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9);
269 $success = 1;
270 };
271 if($@) {
272 eval {
273 $self->{dbh}->do("ANALYZE");
274 $self->{dbh}->do("VACUUM");
275 };
276 }
277 if($success==0) {
278 eval {
279 my $sth = $self->{dbh}->prepare($sql_statement);
280 $db_res = $sth->execute();
281 $sth->finish();
282 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9);
283 $success = 1;
284 };
285 if($@) {
286 eval {
287 $self->{dbh}->do("ANALYZE");
288 $self->{dbh}->do("VACUUM");
289 };
290 }
291 }
292 if($success==0) {
293 eval {
294 my $sth = $self->{dbh}->prepare($sql_statement);
295 $db_res = $sth->execute();
296 $sth->finish();
297 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 7);
298 $success = 1;
299 };
300 if($@) {
301 &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
302 }
303 }
304 $self->unlock();
306 if( $db_res != 1 ) {
307 return (4, $sql_statement);
308 }
310 # entry already exists -> run update
311 } else {
312 my @update_l;
313 while( my ($pram, $val) = each %{$arg} ) {
314 if( $pram eq 'table' ) { next; }
315 if( $pram eq 'primkey' ) { next; }
316 push(@update_l, "$pram='$val'");
317 }
318 my $update_str= join(", ", @update_l);
319 $update_str= " SET $update_str";
321 my $sql_statement= "UPDATE $table $update_str $prim_statement";
322 my $db_res = &update_dbentry($self, $sql_statement );
323 }
325 return 0;
326 }
329 sub update_dbentry {
330 my ($self, $sql)= @_;
331 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
332 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
333 return;
334 }
335 my $db_answer= $self->exec_statement($sql);
336 return $db_answer;
337 }
340 sub del_dbentry {
341 my ($self, $sql)= @_;;
342 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
343 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
344 return;
345 }
346 my $db_res= $self->exec_statement($sql);
347 return $db_res;
348 }
351 sub get_table_columns {
352 my $self = shift;
353 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
354 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
355 return;
356 }
357 my $table = shift;
358 my @column_names;
360 if(exists $col_names->{$table}) {
361 foreach my $col_name (@{$col_names->{$table}}) {
362 push @column_names, ($1) if $col_name =~ /^(.*?)\s.*$/;
363 }
364 } else {
365 my @res;
366 foreach my $column ( @{ $self->exec_statement ( "pragma table_info('$table')" ) } ) {
367 push(@column_names, @$column[1]);
368 }
369 }
371 return \@column_names;
372 }
375 sub select_dbentry {
376 my ($self, $sql)= @_;
377 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
378 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
379 return;
380 }
381 my $error= 0;
382 my $answer= {};
383 my $db_answer= $self->exec_statement($sql);
384 my @column_list;
386 # fetch column list of db and create a hash with column_name->column_value of the select query
387 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
388 my $selected_cols = $1;
389 my $table = $2;
391 # all columns are used for creating answer
392 if ($selected_cols eq '*') {
393 @column_list = @{ $self->get_table_columns($table) };
395 # specific columns are used for creating answer
396 } else {
397 # remove all blanks and split string to list of column names
398 $selected_cols =~ s/ //g;
399 @column_list = split(/,/, $selected_cols);
400 }
402 # create answer
403 my $hit_counter = 0;
404 my $list_len = @column_list;
405 foreach my $hit ( @{$db_answer} ){
406 $hit_counter++;
407 for ( my $i = 0; $i < $list_len; $i++) {
408 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
409 }
410 }
412 return $answer;
413 }
416 sub show_table {
417 my $self = shift;
418 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
419 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
420 return;
421 }
422 my $table_name = shift;
424 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
425 my $res= $self->exec_statement($sql_statement);
426 my @answer;
427 foreach my $hit (@{$res}) {
428 push(@answer, "hit: ".join(', ', @{$hit}));
429 }
431 return join("\n", @answer);
432 }
435 sub exec_statement {
436 my $self = shift;
437 my $sql_statement = shift;
438 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
439 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
440 return;
441 }
443 if(not defined($sql_statement) or length($sql_statement) == 0) {
444 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
445 return;
446 }
448 my @db_answer;
449 my $success= 0;
450 $self->lock();
451 # Give three chances to the sqlite database
452 # 1st chance
453 eval {
454 my $sth = $self->{dbh}->prepare($sql_statement);
455 my $res = $sth->execute();
456 @db_answer = @{$sth->fetchall_arrayref()};
457 $sth->finish();
458 $success=1;
459 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
460 };
461 if($@) {
462 eval {
463 $self->{dbh}->do("ANALYZE");
464 $self->{dbh}->do("VACUUM");
465 $self->{dbh}->do("pragma integrity_check");
466 };
467 }
468 if($success) {
469 $self->unlock();
470 return \@db_answer ;
471 }
473 # 2nd chance
474 eval {
475 usleep(200);
476 my $sth = $self->{dbh}->prepare($sql_statement);
477 my $res = $sth->execute();
478 @db_answer = @{$sth->fetchall_arrayref()};
479 $sth->finish();
480 $success=1;
481 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
482 };
483 if($@) {
484 eval {
485 $self->{dbh}->do("ANALYZE");
486 $self->{dbh}->do("VACUUM");
487 $self->{dbh}->do("pragma integrity_check");
488 };
489 }
490 if($success) {
491 $self->unlock();
492 return \@db_answer ;
493 }
495 # 3rd chance
496 eval {
497 usleep(200);
498 DBI->trace(6) if($main::verbose >= 7);
499 my $sth = $self->{dbh}->prepare($sql_statement);
500 my $res = $sth->execute();
501 @db_answer = @{$sth->fetchall_arrayref()};
502 $sth->finish();
503 DBI->trace(0);
504 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
505 };
506 if($@) {
507 DBI->trace(0);
508 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
509 }
510 # TODO : maybe an error handling and an erro feedback to invoking function
511 #my $error = @$self->{dbh}->err;
512 #if ($error) {
513 # my $error_string = @$self->{dbh}->errstr;
514 #}
516 $self->unlock();
517 return \@db_answer;
518 }
521 sub exec_statementlist {
522 my $self = shift;
523 my $sql_list = shift;
524 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
525 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
526 return;
527 }
528 my @db_answer;
530 foreach my $sql_statement (@$sql_list) {
531 if(defined($sql_statement) && length($sql_statement) > 0) {
532 push @db_answer, $self->exec_statement($sql_statement);
533 } else {
534 next;
535 }
536 }
538 return \@db_answer;
539 }
542 sub count_dbentries {
543 my ($self, $table)= @_;
544 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
545 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
546 return;
547 }
548 my $error= 0;
549 my $count= -1;
551 my $sql_statement= "SELECT count() FROM $table";
552 my $db_answer= $self->select_dbentry($sql_statement);
553 if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
554 $count = $db_answer->{1}->{'count()'};
555 }
557 return $count;
558 }
561 sub move_table {
562 my ($self, $from, $to) = @_;
563 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
564 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
565 return;
566 }
568 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
569 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
570 my $success = 0;
572 $self->lock();
573 eval {
574 $self->{dbh}->begin_work();
575 $self->{dbh}->do($sql_statement_drop);
576 $self->{dbh}->do($sql_statement_alter);
577 $self->{dbh}->commit();
578 $success = 1;
579 };
580 if($@) {
581 $self->{dbh}->rollback();
582 eval {
583 $self->{dbh}->do("ANALYZE");
584 };
585 if($@) {
586 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
587 }
588 eval {
589 $self->{dbh}->do("VACUUM");
590 };
591 if($@) {
592 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
593 }
594 }
596 if($success == 0) {
597 eval {
598 $self->{dbh}->begin_work();
599 $self->{dbh}->do($sql_statement_drop);
600 $self->{dbh}->do($sql_statement_alter);
601 $self->{dbh}->commit();
602 $success = 1;
603 };
604 if($@) {
605 $self->{dbh}->rollback();
606 eval {
607 $self->{dbh}->do("ANALYZE");
608 };
609 if($@) {
610 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
611 }
612 eval {
613 $self->{dbh}->do("VACUUM");
614 };
615 if($@) {
616 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
617 }
618 }
619 }
621 if($success == 0) {
622 eval {
623 $self->{dbh}->begin_work();
624 $self->{dbh}->do($sql_statement_drop);
625 $self->{dbh}->do($sql_statement_alter);
626 $self->{dbh}->commit();
627 $success = 1;
628 };
629 if($@) {
630 $self->{dbh}->rollback();
631 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
632 }
633 }
635 &main::daemon_log("0 INFO: GOSA::DBsqlite::move_table: Operation successful!", 7);
636 $self->unlock();
638 return;
639 }
642 1;