f4e442e41e6a66ffd795b6d6c42a1ac3a8569310
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}, 7);
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}, 7);
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 }
110 return 0;
111 }
114 sub add_dbentry {
115 my $self = shift;
116 my $arg = shift;
117 my $res = 0; # default value
119 # if dbh not specified, return errorflag 1
120 my $table = $arg->{table};
121 if( not defined $table ) {
122 return 1 ;
123 }
125 # if timestamp is not provided, add timestamp
126 if( not exists $arg->{timestamp} ) {
127 $arg->{timestamp} = &get_time;
128 }
130 # check primkey and run insert or update
131 my $primkeys = $arg->{'primkey'};
132 my $prim_statement="";
133 if( 0 != @$primkeys ) { # more than one primkey exist in list
134 my @prim_list;
135 foreach my $primkey (@$primkeys) {
136 if( not exists $arg->{$primkey} ) {
137 return (3, "primkey '$primkey' has no value for add_dbentry");
138 }
139 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
140 }
141 $prim_statement = "WHERE ".join(" AND ", @prim_list);
143 # check wether primkey is unique in table, otherwise return errorflag
144 my $sql_statement = "SELECT * FROM $table $prim_statement";
145 $res = @{ $self->exec_statement($sql_statement) };
146 }
148 # primkey is unique or no primkey specified -> run insert
149 if ($res == 0) {
150 # fetch column names of table
151 my $col_names = &get_table_columns($self, $table);
153 my $create_id=0;
154 foreach my $col_name (@{$col_names}) {
155 if($col_name eq "id" && (! exists $arg->{$col_name})) {
156 $create_id=1;
157 }
158 }
159 # assign values to column name variables
160 my @col_list;
161 my @val_list;
162 foreach my $col_name (@{$col_names}) {
163 # use function parameter for column values
164 if (exists $arg->{$col_name}) {
165 push(@col_list, "'".$col_name."'");
166 push(@val_list, "'".$arg->{$col_name}."'");
167 }
168 }
170 my $sql_statement;
171 if($create_id==1) {
172 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
173 } else {
174 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
175 }
176 my $db_res;
177 my $success=0;
178 $self->lock();
179 eval {
180 my $sth = $self->{dbh}->prepare_cached($sql_statement);
181 $db_res = $sth->execute();
182 $sth->finish();
183 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 7);
184 $success = 1;
185 };
186 if($@) {
187 eval {
188 $self->{dbh}->do("ANALYZE");
189 $self->{dbh}->do("VACUUM");
190 };
191 }
192 if($success==0) {
193 eval {
194 my $sth = $self->{dbh}->prepare_cached($sql_statement);
195 $db_res = $sth->execute();
196 $sth->finish();
197 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 7);
198 $success = 1;
199 };
200 if($@) {
201 eval {
202 $self->{dbh}->do("ANALYZE");
203 $self->{dbh}->do("VACUUM");
204 };
205 }
206 }
207 if($success==0) {
208 eval {
209 my $sth = $self->{dbh}->prepare_cached($sql_statement);
210 $db_res = $sth->execute();
211 $sth->finish();
212 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 7);
213 $success = 1;
214 };
215 if($@) {
216 &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
217 }
218 }
219 $self->unlock();
221 if( $db_res != 1 ) {
222 return (4, $sql_statement);
223 }
225 # entry already exists -> run update
226 } else {
227 my @update_l;
228 while( my ($pram, $val) = each %{$arg} ) {
229 if( $pram eq 'table' ) { next; }
230 if( $pram eq 'primkey' ) { next; }
231 push(@update_l, "$pram='$val'");
232 }
233 my $update_str= join(", ", @update_l);
234 $update_str= " SET $update_str";
236 my $sql_statement= "UPDATE $table $update_str $prim_statement";
237 my $db_res = &update_dbentry($self, $sql_statement );
238 }
240 return 0;
241 }
242 sub update_dbentry {
243 my ($self, $sql)= @_;
244 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
245 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
246 return;
247 }
248 my $db_answer= $self->exec_statement($sql);
249 return $db_answer;
250 }
253 sub del_dbentry {
254 my ($self, $sql)= @_;;
255 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
256 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
257 return;
258 }
259 my $db_res= $self->exec_statement($sql);
260 return $db_res;
261 }
264 sub get_table_columns {
265 my $self = shift;
266 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
267 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
268 return;
269 }
270 my $table = shift;
271 my @column_names;
273 if(exists $col_names->{$table}) {
274 @column_names = @{$col_names->{$table}};
275 } else {
276 my @res;
277 foreach my $column ( $self->exec_statement ( "pragma table_info('$table')" ) ) {
278 push(@column_names, @$column[1]);
279 }
280 }
281 return \@column_names;
283 }
286 sub select_dbentry {
287 my ($self, $sql)= @_;
288 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
289 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
290 return;
291 }
292 my $error= 0;
293 my $answer= {};
294 my $db_answer= $self->exec_statement($sql);
295 my @column_list;
297 # fetch column list of db and create a hash with column_name->column_value of the select query
298 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
299 my $selected_cols = $1;
300 my $table = $2;
302 # all columns are used for creating answer
303 if ($selected_cols eq '*') {
304 @column_list = @{ $self->get_table_columns($table) };
306 # specific columns are used for creating answer
307 } else {
308 # remove all blanks and split string to list of column names
309 $selected_cols =~ s/ //g;
310 @column_list = split(/,/, $selected_cols);
311 }
313 # create answer
314 my $hit_counter = 0;
315 my $list_len = @column_list;
316 foreach my $hit ( @{$db_answer} ){
317 $hit_counter++;
318 for ( my $i = 0; $i < $list_len; $i++) {
319 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
320 }
321 }
323 return $answer;
324 }
327 sub show_table {
328 my $self = shift;
329 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
330 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
331 return;
332 }
333 my $table_name = shift;
335 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
336 my $res= $self->exec_statement($sql_statement);
337 my @answer;
338 foreach my $hit (@{$res}) {
339 push(@answer, "hit: ".join(', ', @{$hit}));
340 }
342 return join("\n", @answer);
343 }
346 sub exec_statement {
347 my $self = shift;
348 my $sql_statement = shift;
350 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
351 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
352 return;
353 }
354 if(not defined($sql_statement) or length($sql_statement) == 0) {
355 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
356 return;
357 }
359 my @db_answer;
360 my $success= 0;
361 $self->lock();
362 # Give three chances to the sqlite database
363 # 1st chance
364 eval {
365 my $sth = $self->{dbh}->prepare_cached($sql_statement);
366 my $res = $sth->execute();
367 @db_answer = @{$sth->fetchall_arrayref()};
368 $sth->finish();
369 $success=1;
370 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
371 };
372 if($@) {
373 eval {
374 $self->{dbh}->do("ANALYZE");
375 $self->{dbh}->do("VACUUM");
376 };
377 }
378 if($success) {
379 $self->unlock();
380 return \@db_answer ;
381 }
383 # 2nd chance
384 eval {
385 DBI->trace(2) if($main::verbose >= 7);
386 my $sth = $self->{dbh}->prepare_cached($sql_statement);
387 my $res = $sth->execute();
388 @db_answer = @{$sth->fetchall_arrayref()};
389 $sth->finish();
390 DBI->trace(0);
391 $success=1;
392 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
393 };
394 if($@) {
395 eval {
396 $self->{dbh}->do("ANALYZE");
397 $self->{dbh}->do("VACUUM");
398 };
399 DBI->trace(0);
400 }
401 if($success) {
402 $self->unlock();
403 return \@db_answer ;
404 }
406 # 3rd chance
407 eval {
408 DBI->trace(2) if($main::verbose >= 7);
409 my $sth = $self->{dbh}->prepare_cached($sql_statement);
410 my $res = $sth->execute();
411 @db_answer = @{$sth->fetchall_arrayref()};
412 $sth->finish();
413 DBI->trace(0);
414 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
415 };
416 if($@) {
417 DBI->trace(0);
418 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
419 }
420 # TODO : maybe an error handling and an erro feedback to invoking function
421 #my $error = @$self->{dbh}->err;
422 #if ($error) {
423 # my $error_string = @$self->{dbh}->errstr;
424 #}
426 $self->unlock();
427 return \@db_answer;
428 }
431 sub exec_statementlist {
432 my $self = shift;
433 my $sql_list = shift;
434 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
435 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
436 return;
437 }
438 my @db_answer;
440 foreach my $sql_statement (@$sql_list) {
441 if(defined($sql_statement) && length($sql_statement) > 0) {
442 push @db_answer, $self->exec_statement($sql_statement);
443 } else {
444 next;
445 }
446 }
448 return \@db_answer;
449 }
452 sub count_dbentries {
453 my ($self, $table)= @_;
454 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
455 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
456 return;
457 }
458 my $error= 0;
459 my $count= -1;
461 my $sql_statement= "SELECT count() FROM $table";
462 my $db_answer= $self->select_dbentry($sql_statement);
463 if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
464 $count = $db_answer->{1}->{'count()'};
465 }
467 return $count;
468 }
471 sub move_table {
472 my ($self, $from, $to) = @_;
473 if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
474 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
475 return;
476 }
478 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
479 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
481 &main::daemon_log("INFO move_table called successfully! Exiting!",1 );
483 $self->lock();
484 eval {
485 $self->{dbh}->begin_work();
486 $self->{dbh}->do($sql_statement_drop);
487 $self->{dbh}->do($sql_statement_alter);
488 $self->{dbh}->commit();
489 };
490 if($@) {
491 $self->{dbh}->rollback();
492 eval {
493 $self->{dbh}->do("ANALYZE");
494 };
495 if($@) {
496 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
497 }
498 eval {
499 $self->{dbh}->do("VACUUM");
500 };
501 if($@) {
502 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
503 }
504 }
506 eval {
507 $self->{dbh}->begin_work();
508 $self->{dbh}->do($sql_statement_drop);
509 $self->{dbh}->do($sql_statement_alter);
510 $self->{dbh}->commit();
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 }
528 eval {
529 $self->{dbh}->begin_work();
530 $self->{dbh}->do($sql_statement_drop);
531 $self->{dbh}->do($sql_statement_alter);
532 $self->{dbh}->commit();
533 };
534 if($@) {
535 $self->{dbh}->rollback();
536 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
537 }
538 $self->unlock();
540 return;
541 }
544 1;