1 package GOSA::DBsqlite;
3 use strict;
4 use warnings;
6 use Carp;
7 use DBI;
8 use GOSA::GosaSupportDaemon;
9 use Time::HiRes qw(usleep);
10 use Data::Dumper;
11 use Fcntl qw/:DEFAULT :flock/; # import LOCK_* constants
13 our $col_names = {};
15 sub new {
16 my $class = shift;
17 my $db_name = shift;
19 my $lock = $db_name.".si.lock";
20 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
21 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1, PrintError => 0});
23 $self->{dbh} = $dbh;
24 $self->{db_name} = $db_name;
25 $self->{db_lock} = $lock;
26 bless($self,$class);
28 my $sth = $self->{dbh}->prepare("pragma integrity_check");
29 $sth->execute();
30 my @ret = $sth->fetchall_arrayref();
31 $sth->finish();
32 if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
33 &main::daemon_log("0 DEBUG: Database disk image '".$self->{db_name}."' is ok.", 74);
34 } else {
35 &main::daemon_log("0 ERROR: Database disk image '".$self->{db_name}."' is malformed, creating new database!", 1);
36 $self->{dbh}->disconnect() or &main::daemon_log("0 ERROR: Could not disconnect from database '".$self->{db_name}."'!", 1);
37 $self->{dbh}= undef;
38 unlink($db_name);
39 }
40 return($self);
41 }
44 sub connect {
45 my $self = shift;
46 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
47 &main::daemon_log("0 ERROR: GOSA::DBsqlite::connect was called static! Argument was '$self'!", 1);
48 return;
49 }
51 $self->{dbh} = DBI->connect("dbi:SQLite:dbname=".$self->{db_name}, "", "", {PrintError => 0, RaiseError => 1, AutoCommit => 1}) or
52 &main::daemon_log("0 ERROR: Could not connect to database '".$self->{db_name}."'!", 1);
54 return;
55 }
58 sub disconnect {
59 my $self = shift;
60 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
61 &main::daemon_log("0 ERROR: GOSA::DBsqlite::disconnect was called static! Argument was '$self'!", 1);
62 return;
63 }
65 eval {
66 $self->{dbh}->disconnect();
67 };
68 if($@) {
69 &main::daemon_log("ERROR: Could not disconnect from database '".$self->{db_name}."'!", 1);
70 }
72 $self->{dbh}= undef;
74 return;
75 }
78 sub lock {
79 my $self = shift;
80 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
81 &main::daemon_log("0 ERROR: GOSA::DBsqlite::lock was called static! Argument was '$self'!", 1);
82 return;
83 }
85 if(not ref $self->{db_lock_handle} or not fileno $self->{db_lock_handle}) {
86 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);
87 }
88 get_lock:
89 my $lock_result = flock($self->{db_lock_handle}, LOCK_EX | LOCK_NB);
90 if(not $lock_result) {
91 &main::daemon_log("0 ERROR: Could not acquire lock for database ".$self->{db_name}, 1);
92 usleep(250+rand(500));
93 goto get_lock;
94 } else {
95 seek($self->{db_lock_handle}, 0, 2);
96 &main::daemon_log("0 DEBUG: Acquired lock for database ".$self->{db_name}, 74);
97 $self->connect();
98 }
99 return;
100 }
103 sub unlock {
104 my $self = shift;
105 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
106 &main::daemon_log("0 ERROR: GOSA::DBsqlite::unlock was called static! Argument was '$self'!", 1);
107 return;
108 }
109 if(not ref $self->{db_lock_handle}) {
110 &main::daemon_log("0 BIG ERROR: Lockfile for database ".$self->{db_name}."got closed within critical section!", 1);
111 }
112 flock($self->{db_lock_handle}, LOCK_UN);
113 &main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 74);
114 $self->disconnect();
115 return;
116 }
119 sub create_table {
120 my $self = shift;
121 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
122 &main::daemon_log("0 ERROR: GOSA::DBsqlite::create_table was called static! Statement was '$self'!", 1);
123 return;
124 }
125 my $table_name = shift;
126 my $col_names_ref = shift;
127 my $index_names_ref = shift || undef;
128 my @col_names;
129 my @col_names_creation;
130 foreach my $col_name (@$col_names_ref) {
131 push(@col_names, $col_name);
132 }
133 $col_names->{ $table_name } = \@col_names;
134 my $col_names_string = join(", ", @col_names);
136 # Not activated yet
137 # Check schema
138 if($self->check_schema($table_name, $col_names_ref)) {
139 $self->exec_statement("DROP TABLE $table_name");
140 &main::daemon_log("WARNING: Schema of table $table_name has changed! Table will be recreated!", 3);
141 }
143 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
144 my $res = $self->exec_statement($sql_statement);
146 # Add indices
147 if(defined($index_names_ref) and ref($index_names_ref) eq 'ARRAY') {
148 foreach my $index_name (@$index_names_ref) {
149 $self->exec_statement("CREATE ".(($index_name eq 'id')?'UNIQUE':'')." INDEX IF NOT EXISTS $index_name on $table_name ($index_name);");
150 }
151 }
153 return 0;
154 }
157 sub check_schema {
158 my $self = shift;
159 my $table_name = shift;
160 my $col_names_ref = shift; # ['id INTEGER PRIMARY KEY', 'timestamp VARCHAR(14) DEFAULT \'none\'', ... ]
161 my $col_names_length = @$col_names_ref;
163 my $sql = "PRAGMA table_info($table_name)";
164 my $res = $self->exec_statement($sql); # [ ['0', 'id', 'INTEGER', '0', undef, '1' ], ['1', 'timestamp', 'VARCHAR(14)', '0', '\'none\'', '0'], ... ]
165 my $db_table_length = @$res;
167 # Tabel does not exists, so no differences
168 if ($db_table_length == 0)
169 {
170 return 0;
171 }
175 # The number of columns is diffrent
176 if ($col_names_length != $db_table_length)
177 {
178 return 1;
179 }
181 # The column name and column type to not match
182 for (my $i=0; $i < $db_table_length; $i++)
183 {
184 my @col_names_list = split(" ", @$col_names_ref[$i]);
185 if (($col_names_list[0] ne @{@$res[$i]}[1]) || ($col_names_list[1] ne @{@$res[$i]}[2]))
186 {
187 return 1;
188 }
189 }
192 return 0;
193 }
197 sub add_dbentry {
198 my $self = shift;
199 my $arg = shift;
200 my $res = 0; # default value
202 # if dbh not specified, return errorflag 1
203 my $table = $arg->{table};
204 if( not defined $table ) {
205 return 1 ;
206 }
208 # if timestamp is not provided, add timestamp
209 if( not exists $arg->{timestamp} ) {
210 $arg->{timestamp} = &get_time;
211 }
213 # check primkey and run insert or update
214 my $primkeys = $arg->{'primkey'};
215 my $prim_statement="";
216 if( 0 != @$primkeys ) { # more than one primkey exist in list
217 my @prim_list;
218 foreach my $primkey (@$primkeys) {
219 if( not exists $arg->{$primkey} ) {
220 return (3, "primkey '$primkey' has no value for add_dbentry");
221 }
222 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
223 }
224 $prim_statement = "WHERE ".join(" AND ", @prim_list);
226 # check wether primkey is unique in table, otherwise return errorflag
227 my $sql_statement = "SELECT * FROM $table $prim_statement";
228 $res = @{ $self->exec_statement($sql_statement) };
229 }
231 # primkey is unique or no primkey specified -> run insert
232 if ($res == 0) {
233 # fetch column names of table
234 my $col_names = &get_table_columns($self, $table);
236 my $create_id=0;
237 foreach my $col_name (@{$col_names}) {
238 if($col_name eq "id" && (! exists $arg->{$col_name})) {
239 $create_id=1;
240 }
241 }
242 # assign values to column name variables
243 my @col_list;
244 my @val_list;
245 foreach my $col_name (@{$col_names}) {
246 # use function parameter for column values
247 if (exists $arg->{$col_name}) {
248 push(@col_list, "'".$col_name."'");
249 push(@val_list, "'".$arg->{$col_name}."'");
250 }
251 }
253 my $sql_statement;
254 if($create_id==1) {
255 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
256 } else {
257 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
258 }
259 my $db_res;
260 my $success=0;
261 $self->lock();
262 eval {
263 my $sth = $self->{dbh}->prepare($sql_statement);
264 $db_res = $sth->execute();
265 $sth->finish();
266 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
267 $success = 1;
268 };
269 if($@) {
270 eval {
271 $self->{dbh}->do("ANALYZE");
272 $self->{dbh}->do("VACUUM");
273 };
274 }
275 if($success==0) {
276 eval {
277 my $sth = $self->{dbh}->prepare($sql_statement);
278 $db_res = $sth->execute();
279 $sth->finish();
280 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
281 $success = 1;
282 };
283 if($@) {
284 eval {
285 $self->{dbh}->do("ANALYZE");
286 $self->{dbh}->do("VACUUM");
287 };
288 }
289 }
290 if($success==0) {
291 eval {
292 my $sth = $self->{dbh}->prepare($sql_statement);
293 $db_res = $sth->execute();
294 $sth->finish();
295 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
296 $success = 1;
297 };
298 if($@) {
299 &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
300 }
301 }
302 $self->unlock();
304 if( $db_res != 1 ) {
305 return (4, $sql_statement);
306 }
308 # entry already exists -> run update
309 } else {
310 my @update_l;
311 while( my ($pram, $val) = each %{$arg} ) {
312 if( $pram eq 'table' ) { next; }
313 if( $pram eq 'primkey' ) { next; }
314 push(@update_l, "$pram='$val'");
315 }
316 my $update_str= join(", ", @update_l);
317 $update_str= " SET $update_str";
319 my $sql_statement= "UPDATE $table $update_str $prim_statement";
320 my $db_res = &update_dbentry($self, $sql_statement );
321 }
323 return 0;
324 }
327 sub update_dbentry {
328 my ($self, $sql)= @_;
329 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
330 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
331 return;
332 }
333 my $db_answer= $self->exec_statement($sql);
334 return $db_answer;
335 }
338 sub del_dbentry {
339 my ($self, $sql)= @_;;
340 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
341 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
342 return;
343 }
344 my $db_res= $self->exec_statement($sql);
345 return $db_res;
346 }
349 sub get_table_columns {
350 my $self = shift;
351 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
352 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
353 return;
354 }
355 my $table = shift;
356 my @column_names;
358 if(exists $col_names->{$table}) {
359 foreach my $col_name (@{$col_names->{$table}}) {
360 push @column_names, ($1) if $col_name =~ /^(.*?)\s.*$/;
361 }
362 } else {
363 my @res;
364 foreach my $column ( @{ $self->exec_statement ( "pragma table_info('$table')" ) } ) {
365 push(@column_names, @$column[1]);
366 }
367 }
369 return \@column_names;
370 }
373 sub select_dbentry {
374 my ($self, $sql)= @_;
375 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
376 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
377 return;
378 }
379 my $error= 0;
380 my $answer= {};
381 my $db_answer= $self->exec_statement($sql);
382 my @column_list;
384 # fetch column list of db and create a hash with column_name->column_value of the select query
385 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
386 my $selected_cols = $1;
387 my $table = $2;
389 # all columns are used for creating answer
390 if ($selected_cols eq '*') {
391 @column_list = @{ $self->get_table_columns($table) };
393 # specific columns are used for creating answer
394 } else {
395 # remove all blanks and split string to list of column names
396 $selected_cols =~ s/ //g;
397 @column_list = split(/,/, $selected_cols);
398 }
400 # create answer
401 my $hit_counter = 0;
402 my $list_len = @column_list;
403 foreach my $hit ( @{$db_answer} ){
404 $hit_counter++;
405 for ( my $i = 0; $i < $list_len; $i++) {
406 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
407 }
408 }
410 return $answer;
411 }
414 sub show_table {
415 my $self = shift;
416 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
417 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
418 return;
419 }
420 my $table_name = shift;
422 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
423 my $res= $self->exec_statement($sql_statement);
424 my @answer;
425 foreach my $hit (@{$res}) {
426 push(@answer, "hit: ".join(', ', @{$hit}));
427 }
429 return join("\n", @answer);
430 }
433 sub exec_statement {
434 my $self = shift;
435 my $sql_statement = shift;
436 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
437 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
438 return;
439 }
441 if(not defined($sql_statement) or length($sql_statement) == 0) {
442 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
443 return;
444 }
446 my @db_answer;
447 my $success= 0;
448 $self->lock();
449 # Give three chances to the sqlite database
450 # 1st chance
451 eval {
452 my $sth = $self->{dbh}->prepare($sql_statement);
453 my $res = $sth->execute();
454 @db_answer = @{$sth->fetchall_arrayref()};
455 $sth->finish();
456 $success=1;
457 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
458 };
459 if($@) {
460 eval {
461 $self->{dbh}->do("ANALYZE");
462 $self->{dbh}->do("VACUUM");
463 $self->{dbh}->do("pragma integrity_check");
464 };
465 }
466 if($success) {
467 $self->unlock();
468 return \@db_answer ;
469 }
471 # 2nd chance
472 eval {
473 usleep(200);
474 my $sth = $self->{dbh}->prepare($sql_statement);
475 my $res = $sth->execute();
476 @db_answer = @{$sth->fetchall_arrayref()};
477 $sth->finish();
478 $success=1;
479 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
480 };
481 if($@) {
482 eval {
483 $self->{dbh}->do("ANALYZE");
484 $self->{dbh}->do("VACUUM");
485 $self->{dbh}->do("pragma integrity_check");
486 };
487 }
488 if($success) {
489 $self->unlock();
490 return \@db_answer ;
491 }
493 # 3rd chance
494 eval {
495 usleep(200);
496 DBI->trace(6) if($main::verbose >= 7);
497 my $sth = $self->{dbh}->prepare($sql_statement);
498 my $res = $sth->execute();
499 @db_answer = @{$sth->fetchall_arrayref()};
500 $sth->finish();
501 DBI->trace(0);
502 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
503 };
504 if($@) {
505 DBI->trace(0);
506 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
507 }
508 # TODO : maybe an error handling and an erro feedback to invoking function
509 #my $error = @$self->{dbh}->err;
510 #if ($error) {
511 # my $error_string = @$self->{dbh}->errstr;
512 #}
514 $self->unlock();
515 return \@db_answer;
516 }
519 sub exec_statementlist {
520 my $self = shift;
521 my $sql_list = shift;
522 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
523 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
524 return;
525 }
526 my @db_answer;
528 foreach my $sql_statement (@$sql_list) {
529 if(defined($sql_statement) && length($sql_statement) > 0) {
530 push @db_answer, $self->exec_statement($sql_statement);
531 } else {
532 next;
533 }
534 }
536 return \@db_answer;
537 }
540 sub count_dbentries {
541 my ($self, $table)= @_;
542 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
543 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
544 return;
545 }
546 my $error= 0;
547 my $count= -1;
549 my $sql_statement= "SELECT count() FROM $table";
550 my $db_answer= $self->select_dbentry($sql_statement);
551 if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
552 $count = $db_answer->{1}->{'count()'};
553 }
555 return $count;
556 }
559 sub move_table {
560 my ($self, $from, $to) = @_;
561 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
562 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
563 return;
564 }
566 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
567 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
568 my $success = 0;
570 $self->lock();
571 eval {
572 $self->{dbh}->begin_work();
573 $self->{dbh}->do($sql_statement_drop);
574 $self->{dbh}->do($sql_statement_alter);
575 $self->{dbh}->commit();
576 $success = 1;
577 };
578 if($@) {
579 $self->{dbh}->rollback();
580 eval {
581 $self->{dbh}->do("ANALYZE");
582 };
583 if($@) {
584 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
585 }
586 eval {
587 $self->{dbh}->do("VACUUM");
588 };
589 if($@) {
590 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
591 }
592 }
594 if($success == 0) {
595 eval {
596 $self->{dbh}->begin_work();
597 $self->{dbh}->do($sql_statement_drop);
598 $self->{dbh}->do($sql_statement_alter);
599 $self->{dbh}->commit();
600 $success = 1;
601 };
602 if($@) {
603 $self->{dbh}->rollback();
604 eval {
605 $self->{dbh}->do("ANALYZE");
606 };
607 if($@) {
608 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
609 }
610 eval {
611 $self->{dbh}->do("VACUUM");
612 };
613 if($@) {
614 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
615 }
616 }
617 }
619 if($success == 0) {
620 eval {
621 $self->{dbh}->begin_work();
622 $self->{dbh}->do($sql_statement_drop);
623 $self->{dbh}->do($sql_statement_alter);
624 $self->{dbh}->commit();
625 $success = 1;
626 };
627 if($@) {
628 $self->{dbh}->rollback();
629 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
630 }
631 }
633 &main::daemon_log("0 INFO: GOSA::DBsqlite::move_table: Operation successful!", 7);
634 $self->unlock();
636 return;
637 }
640 1;