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 $lock = $db_name;
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});
22 my $sth = $dbh->prepare("pragma integrity_check");
23 $sth->execute();
24 my @ret = $sth->fetchall_arrayref();
25 $sth->finish();
26 if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
27 &main::daemon_log("DEBUG: Database image $db_name is ok", 7);
28 } else {
29 &main::daemon_log("ERROR: Database image $db_name is malformed, creating new database.", 1);
30 $dbh->disconnect();
31 unlink($db_name);
32 $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1});
33 }
34 $self->{dbh} = $dbh;
35 $self->{db_name} = $db_name;
36 $self->{db_lock} = $lock;
37 bless($self,$class);
38 return($self);
39 }
42 sub lock {
43 my $self = shift;
44 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
45 &main::daemon_log("0 ERROR: GOSA::DBsqlite::lock was called static! Statement was '$self'!", 1);
46 return;
47 }
48 if(not ref $self->{db_lock_handle}) {
49 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);
50 }
51 my $lock_result = flock($self->{db_lock_handle}, LOCK_EX);
52 if($lock_result==1) {
53 seek($self->{db_lock_handle}, 0, 2);
54 &main::daemon_log("0 DEBUG: Acquired lock for database ".$self->{db_name}, 9);
55 } else {
56 &main::daemon_log("0 ERROR: Could not acquire lock for database ".$self->{db_name}, 1);
57 }
58 return;
59 }
62 sub unlock {
63 my $self = shift;
64 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
65 &main::daemon_log("0 ERROR: GOSA::DBsqlite::unlock was called static! Statement was '$self'!", 1);
66 return;
67 }
68 if(not ref $self->{db_lock_handle}) {
69 &main::daemon_log("0 BIG ERROR: Lockfile for database ".$self->{db_name}."got closed within critical section!", 1);
70 }
71 flock($self->{db_lock_handle}, LOCK_UN);
72 &main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 9);
73 return;
74 }
77 sub create_table {
78 my $self = shift;
79 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
80 &main::daemon_log("0 ERROR: GOSA::DBsqlite::create_table was called static! Statement was '$self'!", 1);
81 return;
82 }
83 my $table_name = shift;
84 my $col_names_ref = shift;
85 my $index_names_ref = shift || undef;
86 my @col_names;
87 my @col_names_creation;
88 foreach my $col_name (@$col_names_ref) {
89 # Save full column description for creation of database
90 push(@col_names_creation, $col_name);
91 my @t = split(" ", $col_name);
92 my $column_name = $t[0];
93 # Save column name internally for select_dbentry
94 push(@col_names, $column_name);
95 }
97 $col_names->{ $table_name } = \@col_names;
98 my $col_names_string = join(", ", @col_names_creation);
99 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
100 my $res = $self->exec_statement($sql_statement);
102 # Add indices
103 if(defined($index_names_ref) and ref($index_names_ref) eq 'ARRAY') {
104 foreach my $index_name (@$index_names_ref) {
105 $self->exec_statement("CREATE ".(($index_name eq 'id')?'UNIQUE':'')." INDEX IF NOT EXISTS $index_name on $table_name ($index_name);");
106 }
107 }
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 $res = @{ $self->exec_statement($sql_statement) };
145 }
147 # primkey is unique or no primkey specified -> run insert
148 if ($res == 0) {
149 # fetch column names of table
150 my $col_names = &get_table_columns($self, $table);
152 my $create_id=0;
153 foreach my $col_name (@{$col_names}) {
154 if($col_name eq "id" && (! exists $arg->{$col_name})) {
155 $create_id=1;
156 }
157 }
158 # assign values to column name variables
159 my @col_list;
160 my @val_list;
161 foreach my $col_name (@{$col_names}) {
162 # use function parameter for column values
163 if (exists $arg->{$col_name}) {
164 push(@col_list, "'".$col_name."'");
165 push(@val_list, "'".$arg->{$col_name}."'");
166 }
167 }
169 my $sql_statement;
170 if($create_id==1) {
171 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
172 } else {
173 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
174 }
175 my $db_res;
176 my $success=0;
177 $self->lock();
178 eval {
179 my $sth = $self->{dbh}->prepare($sql_statement);
180 $db_res = $sth->execute();
181 $sth->finish();
182 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9);
183 $success = 1;
184 };
185 if($@) {
186 eval {
187 $self->{dbh}->do("ANALYZE");
188 $self->{dbh}->do("VACUUM");
189 };
190 }
191 if($success==0) {
192 eval {
193 my $sth = $self->{dbh}->prepare($sql_statement);
194 $db_res = $sth->execute();
195 $sth->finish();
196 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9);
197 $success = 1;
198 };
199 if($@) {
200 eval {
201 $self->{dbh}->do("ANALYZE");
202 $self->{dbh}->do("VACUUM");
203 };
204 }
205 }
206 if($success==0) {
207 eval {
208 my $sth = $self->{dbh}->prepare($sql_statement);
209 $db_res = $sth->execute();
210 $sth->finish();
211 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9);
212 $success = 1;
213 };
214 if($@) {
215 &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
216 }
217 }
218 $self->unlock();
220 if( $db_res != 1 ) {
221 return (4, $sql_statement);
222 }
224 # entry already exists -> run update
225 } else {
226 my @update_l;
227 while( my ($pram, $val) = each %{$arg} ) {
228 if( $pram eq 'table' ) { next; }
229 if( $pram eq 'primkey' ) { next; }
230 push(@update_l, "$pram='$val'");
231 }
232 my $update_str= join(", ", @update_l);
233 $update_str= " SET $update_str";
235 my $sql_statement= "UPDATE $table $update_str $prim_statement";
236 my $db_res = &update_dbentry($self, $sql_statement );
237 }
239 return 0;
240 }
241 sub update_dbentry {
242 my ($self, $sql)= @_;
243 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
244 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
245 return;
246 }
247 my $db_answer= $self->exec_statement($sql);
248 return $db_answer;
249 }
252 sub del_dbentry {
253 my ($self, $sql)= @_;;
254 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
255 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
256 return;
257 }
258 my $db_res= $self->exec_statement($sql);
259 return $db_res;
260 }
263 sub get_table_columns {
264 my $self = shift;
265 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
266 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
267 return;
268 }
269 my $table = shift;
270 my @column_names;
272 if(exists $col_names->{$table}) {
273 @column_names = @{$col_names->{$table}};
274 } else {
275 foreach my $column ( @{$self->exec_statement ( "pragma table_info('$table')" )} ) {
276 push(@column_names, @$column[1]);
277 }
278 }
279 return \@column_names;
281 }
284 sub select_dbentry {
285 my ($self, $sql)= @_;
286 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
287 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
288 return;
289 }
290 my $error= 0;
291 my $answer= {};
292 my $db_answer= $self->exec_statement($sql);
293 my @column_list;
295 # fetch column list of db and create a hash with column_name->column_value of the select query
296 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
297 my $selected_cols = $1;
298 my $table = $2;
300 # all columns are used for creating answer
301 if ($selected_cols eq '*') {
302 @column_list = @{ $self->get_table_columns($table) };
304 # specific columns are used for creating answer
305 } else {
306 # remove all blanks and split string to list of column names
307 $selected_cols =~ s/ //g;
308 @column_list = split(/,/, $selected_cols);
309 }
311 # create answer
312 my $hit_counter = 0;
313 my $list_len = @column_list;
314 foreach my $hit ( @{$db_answer} ){
315 $hit_counter++;
316 for ( my $i = 0; $i < $list_len; $i++) {
317 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
318 }
319 }
321 return $answer;
322 }
325 sub show_table {
326 my $self = shift;
327 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
328 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
329 return;
330 }
331 my $table_name = shift;
333 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
334 my $res= $self->exec_statement($sql_statement);
335 my @answer;
336 foreach my $hit (@{$res}) {
337 push(@answer, "hit: ".join(', ', @{$hit}));
338 }
340 return join("\n", @answer);
341 }
344 sub exec_statement {
345 my $self = shift;
346 my $sql_statement = shift;
348 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
349 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
350 return;
351 }
352 if(not defined($sql_statement) or length($sql_statement) == 0) {
353 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
354 return;
355 }
357 my @db_answer;
358 my $success= 0;
359 $self->lock();
360 # Give three chances to the sqlite database
361 # 1st chance
362 eval {
363 my $sth = $self->{dbh}->prepare($sql_statement);
364 my $res = $sth->execute();
365 @db_answer = @{$sth->fetchall_arrayref()};
366 $sth->finish();
367 $success=1;
368 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
369 };
370 if($@) {
371 eval {
372 $self->{dbh}->do("ANALYZE");
373 $self->{dbh}->do("VACUUM");
374 };
375 }
376 if($success) {
377 $self->unlock();
378 return \@db_answer ;
379 }
381 # 2nd chance
382 eval {
383 DBI->trace(6) if($main::verbose >= 7);
384 my $sth = $self->{dbh}->prepare($sql_statement);
385 my $res = $sth->execute();
386 @db_answer = @{$sth->fetchall_arrayref()};
387 $sth->finish();
388 DBI->trace(0);
389 $success=1;
390 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
391 };
392 if($@) {
393 eval {
394 $self->{dbh}->do("ANALYZE");
395 $self->{dbh}->do("VACUUM");
396 };
397 DBI->trace(0);
398 }
399 if($success) {
400 $self->unlock();
401 return \@db_answer ;
402 }
404 # 3rd chance
405 eval {
406 DBI->trace(6) if($main::verbose >= 7);
407 my $sth = $self->{dbh}->prepare($sql_statement);
408 my $res = $sth->execute();
409 @db_answer = @{$sth->fetchall_arrayref()};
410 $sth->finish();
411 DBI->trace(0);
412 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9);
413 };
414 if($@) {
415 DBI->trace(0);
416 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
417 }
418 # TODO : maybe an error handling and an erro feedback to invoking function
419 #my $error = @$self->{dbh}->err;
420 #if ($error) {
421 # my $error_string = @$self->{dbh}->errstr;
422 #}
424 $self->unlock();
425 return \@db_answer;
426 }
429 sub exec_statementlist {
430 my $self = shift;
431 my $sql_list = shift;
432 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
433 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
434 return;
435 }
436 my @db_answer;
438 foreach my $sql_statement (@$sql_list) {
439 if(defined($sql_statement) && length($sql_statement) > 0) {
440 push @db_answer, $self->exec_statement($sql_statement);
441 } else {
442 next;
443 }
444 }
446 return \@db_answer;
447 }
450 sub count_dbentries {
451 my ($self, $table)= @_;
452 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
453 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
454 return;
455 }
456 my $error= 0;
457 my $count= -1;
459 my $sql_statement= "SELECT count() FROM $table";
460 my $db_answer= $self->select_dbentry($sql_statement);
461 if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
462 $count = $db_answer->{1}->{'count()'};
463 }
465 return $count;
466 }
469 sub move_table {
470 my ($self, $from, $to) = @_;
471 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
472 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
473 return;
474 }
476 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
477 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
478 my $success = 0;
480 $self->lock();
481 eval {
482 $self->{dbh}->begin_work();
483 $self->{dbh}->do($sql_statement_drop);
484 $self->{dbh}->do($sql_statement_alter);
485 $self->{dbh}->commit();
486 $success = 1;
487 };
488 if($@) {
489 $self->{dbh}->rollback();
490 eval {
491 $self->{dbh}->do("ANALYZE");
492 };
493 if($@) {
494 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
495 }
496 eval {
497 $self->{dbh}->do("VACUUM");
498 };
499 if($@) {
500 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
501 }
502 }
504 if($success == 0) {
505 eval {
506 $self->{dbh}->begin_work();
507 $self->{dbh}->do($sql_statement_drop);
508 $self->{dbh}->do($sql_statement_alter);
509 $self->{dbh}->commit();
510 $success = 1;
511 };
512 if($@) {
513 $self->{dbh}->rollback();
514 eval {
515 $self->{dbh}->do("ANALYZE");
516 };
517 if($@) {
518 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
519 }
520 eval {
521 $self->{dbh}->do("VACUUM");
522 };
523 if($@) {
524 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
525 }
526 }
527 }
529 if($success == 0) {
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 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
540 }
541 }
543 &main::daemon_log("0 INFO: GOSA::DBsqlite::move_table: Operation successful!", 7);
544 $self->unlock();
546 return;
547 }
550 1;