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.", 74);
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}, 74);
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}, 74);
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 add_dbentry {
157 my $self = shift;
158 my $arg = shift;
159 my $res = 0; # default value
161 # if dbh not specified, return errorflag 1
162 my $table = $arg->{table};
163 if( not defined $table ) {
164 return 1 ;
165 }
167 # if timestamp is not provided, add timestamp
168 if( not exists $arg->{timestamp} ) {
169 $arg->{timestamp} = &get_time;
170 }
172 # check primkey and run insert or update
173 my $primkeys = $arg->{'primkey'};
174 my $prim_statement="";
175 if( 0 != @$primkeys ) { # more than one primkey exist in list
176 my @prim_list;
177 foreach my $primkey (@$primkeys) {
178 if( not exists $arg->{$primkey} ) {
179 return (3, "primkey '$primkey' has no value for add_dbentry");
180 }
181 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
182 }
183 $prim_statement = "WHERE ".join(" AND ", @prim_list);
185 # check wether primkey is unique in table, otherwise return errorflag
186 my $sql_statement = "SELECT * FROM $table $prim_statement";
187 $res = @{ $self->exec_statement($sql_statement) };
188 }
190 # primkey is unique or no primkey specified -> run insert
191 if ($res == 0) {
192 # fetch column names of table
193 my $col_names = &get_table_columns($self, $table);
195 my $create_id=0;
196 foreach my $col_name (@{$col_names}) {
197 if($col_name eq "id" && (! exists $arg->{$col_name})) {
198 $create_id=1;
199 }
200 }
201 # assign values to column name variables
202 my @col_list;
203 my @val_list;
204 foreach my $col_name (@{$col_names}) {
205 # use function parameter for column values
206 if (exists $arg->{$col_name}) {
207 push(@col_list, "'".$col_name."'");
208 push(@val_list, "'".$arg->{$col_name}."'");
209 }
210 }
212 my $sql_statement;
213 if($create_id==1) {
214 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
215 } else {
216 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
217 }
218 my $db_res;
219 my $success=0;
220 $self->lock();
221 eval {
222 my $sth = $self->{dbh}->prepare($sql_statement);
223 $db_res = $sth->execute();
224 $sth->finish();
225 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
226 $success = 1;
227 };
228 if($@) {
229 eval {
230 $self->{dbh}->do("ANALYZE");
231 $self->{dbh}->do("VACUUM");
232 };
233 }
234 if($success==0) {
235 eval {
236 my $sth = $self->{dbh}->prepare($sql_statement);
237 $db_res = $sth->execute();
238 $sth->finish();
239 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
240 $success = 1;
241 };
242 if($@) {
243 eval {
244 $self->{dbh}->do("ANALYZE");
245 $self->{dbh}->do("VACUUM");
246 };
247 }
248 }
249 if($success==0) {
250 eval {
251 my $sth = $self->{dbh}->prepare($sql_statement);
252 $db_res = $sth->execute();
253 $sth->finish();
254 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
255 $success = 1;
256 };
257 if($@) {
258 &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
259 }
260 }
261 $self->unlock();
263 if( $db_res != 1 ) {
264 return (4, $sql_statement);
265 }
267 # entry already exists -> run update
268 } else {
269 my @update_l;
270 while( my ($pram, $val) = each %{$arg} ) {
271 if( $pram eq 'table' ) { next; }
272 if( $pram eq 'primkey' ) { next; }
273 push(@update_l, "$pram='$val'");
274 }
275 my $update_str= join(", ", @update_l);
276 $update_str= " SET $update_str";
278 my $sql_statement= "UPDATE $table $update_str $prim_statement";
279 my $db_res = &update_dbentry($self, $sql_statement );
280 }
282 return 0;
283 }
286 sub update_dbentry {
287 my ($self, $sql)= @_;
288 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
289 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
290 return;
291 }
292 my $db_answer= $self->exec_statement($sql);
293 return $db_answer;
294 }
297 sub del_dbentry {
298 my ($self, $sql)= @_;;
299 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
300 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
301 return;
302 }
303 my $db_res= $self->exec_statement($sql);
304 return $db_res;
305 }
308 sub get_table_columns {
309 my $self = shift;
310 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
311 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
312 return;
313 }
314 my $table = shift;
315 my @column_names;
317 if(exists $col_names->{$table}) {
318 foreach my $col_name (@{$col_names->{$table}}) {
319 push @column_names, ($1) if $col_name =~ /^(.*?)\s.*$/;
320 }
321 } else {
322 my @res;
323 foreach my $column ( @{ $self->exec_statement ( "pragma table_info('$table')" ) } ) {
324 push(@column_names, @$column[1]);
325 }
326 }
328 return \@column_names;
329 }
332 sub select_dbentry {
333 my ($self, $sql)= @_;
334 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
335 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
336 return;
337 }
338 my $error= 0;
339 my $answer= {};
340 my $db_answer= $self->exec_statement($sql);
341 my @column_list;
343 # fetch column list of db and create a hash with column_name->column_value of the select query
344 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
345 my $selected_cols = $1;
346 my $table = $2;
348 # all columns are used for creating answer
349 if ($selected_cols eq '*') {
350 @column_list = @{ $self->get_table_columns($table) };
352 # specific columns are used for creating answer
353 } else {
354 # remove all blanks and split string to list of column names
355 $selected_cols =~ s/ //g;
356 @column_list = split(/,/, $selected_cols);
357 }
359 # create answer
360 my $hit_counter = 0;
361 my $list_len = @column_list;
362 foreach my $hit ( @{$db_answer} ){
363 $hit_counter++;
364 for ( my $i = 0; $i < $list_len; $i++) {
365 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
366 }
367 }
369 return $answer;
370 }
373 sub show_table {
374 my $self = shift;
375 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
376 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
377 return;
378 }
379 my $table_name = shift;
381 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
382 my $res= $self->exec_statement($sql_statement);
383 my @answer;
384 foreach my $hit (@{$res}) {
385 push(@answer, "hit: ".join(', ', @{$hit}));
386 }
388 return join("\n", @answer);
389 }
392 sub exec_statement {
393 my $self = shift;
394 my $sql_statement = shift;
395 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
396 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
397 return;
398 }
400 if(not defined($sql_statement) or length($sql_statement) == 0) {
401 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
402 return;
403 }
405 my @db_answer;
406 my $success= 0;
407 $self->lock();
408 # Give three chances to the sqlite database
409 # 1st chance
410 eval {
411 my $sth = $self->{dbh}->prepare($sql_statement);
412 my $res = $sth->execute();
413 @db_answer = @{$sth->fetchall_arrayref()};
414 $sth->finish();
415 $success=1;
416 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
417 };
418 if($@) {
419 eval {
420 $self->{dbh}->do("ANALYZE");
421 $self->{dbh}->do("VACUUM");
422 $self->{dbh}->do("pragma integrity_check");
423 };
424 }
425 if($success) {
426 $self->unlock();
427 return \@db_answer ;
428 }
430 # 2nd chance
431 eval {
432 usleep(200);
433 my $sth = $self->{dbh}->prepare($sql_statement);
434 my $res = $sth->execute();
435 @db_answer = @{$sth->fetchall_arrayref()};
436 $sth->finish();
437 $success=1;
438 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
439 };
440 if($@) {
441 eval {
442 $self->{dbh}->do("ANALYZE");
443 $self->{dbh}->do("VACUUM");
444 $self->{dbh}->do("pragma integrity_check");
445 };
446 }
447 if($success) {
448 $self->unlock();
449 return \@db_answer ;
450 }
452 # 3rd chance
453 eval {
454 usleep(200);
455 DBI->trace(6) if($main::verbose >= 7);
456 my $sth = $self->{dbh}->prepare($sql_statement);
457 my $res = $sth->execute();
458 @db_answer = @{$sth->fetchall_arrayref()};
459 $sth->finish();
460 DBI->trace(0);
461 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
462 };
463 if($@) {
464 DBI->trace(0);
465 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
466 }
467 # TODO : maybe an error handling and an erro feedback to invoking function
468 #my $error = @$self->{dbh}->err;
469 #if ($error) {
470 # my $error_string = @$self->{dbh}->errstr;
471 #}
473 $self->unlock();
474 return \@db_answer;
475 }
478 sub exec_statementlist {
479 my $self = shift;
480 my $sql_list = shift;
481 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
482 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
483 return;
484 }
485 my @db_answer;
487 foreach my $sql_statement (@$sql_list) {
488 if(defined($sql_statement) && length($sql_statement) > 0) {
489 push @db_answer, $self->exec_statement($sql_statement);
490 } else {
491 next;
492 }
493 }
495 return \@db_answer;
496 }
499 sub count_dbentries {
500 my ($self, $table)= @_;
501 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
502 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
503 return;
504 }
505 my $error= 0;
506 my $count= -1;
508 my $sql_statement= "SELECT count() FROM $table";
509 my $db_answer= $self->select_dbentry($sql_statement);
510 if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
511 $count = $db_answer->{1}->{'count()'};
512 }
514 return $count;
515 }
518 sub move_table {
519 my ($self, $from, $to) = @_;
520 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
521 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
522 return;
523 }
525 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
526 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
527 my $success = 0;
529 $self->lock();
530 eval {
531 $self->{dbh}->begin_work();
532 $self->{dbh}->do($sql_statement_drop);
533 $self->{dbh}->do($sql_statement_alter);
534 $self->{dbh}->commit();
535 $success = 1;
536 };
537 if($@) {
538 $self->{dbh}->rollback();
539 eval {
540 $self->{dbh}->do("ANALYZE");
541 };
542 if($@) {
543 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
544 }
545 eval {
546 $self->{dbh}->do("VACUUM");
547 };
548 if($@) {
549 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
550 }
551 }
553 if($success == 0) {
554 eval {
555 $self->{dbh}->begin_work();
556 $self->{dbh}->do($sql_statement_drop);
557 $self->{dbh}->do($sql_statement_alter);
558 $self->{dbh}->commit();
559 $success = 1;
560 };
561 if($@) {
562 $self->{dbh}->rollback();
563 eval {
564 $self->{dbh}->do("ANALYZE");
565 };
566 if($@) {
567 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
568 }
569 eval {
570 $self->{dbh}->do("VACUUM");
571 };
572 if($@) {
573 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
574 }
575 }
576 }
578 if($success == 0) {
579 eval {
580 $self->{dbh}->begin_work();
581 $self->{dbh}->do($sql_statement_drop);
582 $self->{dbh}->do($sql_statement_alter);
583 $self->{dbh}->commit();
584 $success = 1;
585 };
586 if($@) {
587 $self->{dbh}->rollback();
588 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
589 }
590 }
592 &main::daemon_log("0 INFO: GOSA::DBsqlite::move_table: Operation successful!", 7);
593 $self->unlock();
595 return;
596 }
599 1;