Code

Added missing comments
[gosa.git] / gosa-si / modules / DBsqlite.pm
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});
21         
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         }
49                 
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;
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;
133         my $col_names_string = join(", ", @col_names);
134         
135         # Not activated yet
136         # Check schema
137         if($self->check_schema($table_name, $col_names_ref)) {
138                 $self->exec_statement("DROP TABLE $table_name");
139                 &main::daemon_log("WARNING: Schema of table $table_name has changed! Table will be recreated!", 3);
140         }
142         my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )"; 
143         my $res = $self->exec_statement($sql_statement);
144         
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;
156 sub check_schema {
157         my $self = shift;
158         my $table_name = shift;
159         my $col_names_ref = shift;   # ['id INTEGER PRIMARY KEY', 'timestamp VARCHAR(14) DEFAULT \'none\'', ... ]
160         my $col_names_length = @$col_names_ref;
162         my $sql = "PRAGMA table_info($table_name)";
163         my $res = $self->exec_statement($sql);   # [ ['0', 'id', 'INTEGER', '0', undef, '1' ], ['1', 'timestamp', 'VARCHAR(14)', '0', '\'none\'', '0'], ... ]
164         my $db_table_length = @$res;
166         # Tabel does not exists, so no differences
167         if ($db_table_length == 0)
168         {
169                 return 0;
170         }
174         # The number of columns is diffrent
175         if ($col_names_length != $db_table_length) 
176         {
177                 return 1;
178         }
180         # The column name and column type to not match
181         for (my $i=0; $i < $db_table_length; $i++)
182         {
183                 my @col_names_list = split(" ", @$col_names_ref[$i]);
184                 if (($col_names_list[0] ne @{@$res[$i]}[1]) || ($col_names_list[1] ne @{@$res[$i]}[2]))
185                 {
186                         return 1;
187                 }
188         }
191         return 0;
196 sub add_dbentry {
197         my $self = shift;
198         my $arg = shift;
199         my $res = 0;   # default value
201         # if dbh not specified, return errorflag 1
202         my $table = $arg->{table};
203         if( not defined $table ) {
204                 return 1 ;
205         }
207         # if timestamp is not provided, add timestamp   
208         if( not exists $arg->{timestamp} ) {
209                 $arg->{timestamp} = &get_time;
210         }
212         # check primkey and run insert or update
213         my $primkeys = $arg->{'primkey'};
214         my $prim_statement="";
215         if( 0 != @$primkeys ) {   # more than one primkey exist in list
216                 my @prim_list;
217                 foreach my $primkey (@$primkeys) {
218                         if( not exists $arg->{$primkey} ) {
219                                 return (3, "primkey '$primkey' has no value for add_dbentry");
220                         }
221                         push(@prim_list, "$primkey='".$arg->{$primkey}."'");
222                 }
223                 $prim_statement = "WHERE ".join(" AND ", @prim_list);
225                 # check wether primkey is unique in table, otherwise return errorflag
226                 my $sql_statement = "SELECT * FROM $table $prim_statement";
227                 $res = @{ $self->exec_statement($sql_statement) };
228         }
230         # primkey is unique or no primkey specified -> run insert
231         if ($res == 0) {
232                 # fetch column names of table
233                 my $col_names = &get_table_columns($self, $table);
235                 my $create_id=0;
236                 foreach my $col_name (@{$col_names}) {
237                         if($col_name eq "id" && (! exists $arg->{$col_name})) {
238                                 $create_id=1;
239                         }
240                 }
241                 # assign values to column name variables
242                 my @col_list;
243                 my @val_list;
244                 foreach my $col_name (@{$col_names}) {
245                         # use function parameter for column values
246                         if (exists $arg->{$col_name}) {
247                                 push(@col_list, "'".$col_name."'");
248                                 push(@val_list, "'".$arg->{$col_name}."'");
249                         }
250                 }
252                 my $sql_statement;
253                 if($create_id==1) {
254                         $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
255                 } else {
256                         $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
257                 }
258                 my $db_res;
259                 my $success=0;
260                 $self->lock();
261                 eval {
262                         my $sth = $self->{dbh}->prepare($sql_statement);
263                         $db_res = $sth->execute();
264                         $sth->finish();
265                         &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
266                         $success = 1;
267                 };
268                 if($@) {
269                         eval {
270                                 $self->{dbh}->do("ANALYZE");
271                                 $self->{dbh}->do("VACUUM");
272                         };
273                 }
274                 if($success==0) {
275                         eval {
276                                 my $sth = $self->{dbh}->prepare($sql_statement);
277                                 $db_res = $sth->execute();
278                                 $sth->finish();
279                                 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
280                                 $success = 1;
281                         };
282                         if($@) {
283                                 eval {
284                                         $self->{dbh}->do("ANALYZE");
285                                         $self->{dbh}->do("VACUUM");
286                                 };
287                         }
288                 }
289                 if($success==0) {
290                         eval {
291                                 my $sth = $self->{dbh}->prepare($sql_statement);
292                                 $db_res = $sth->execute();
293                                 $sth->finish();
294                                 &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 74);
295                                 $success = 1;
296                         };
297                         if($@) {
298                                 &main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
299                         }
300                 }
301                 $self->unlock();
303                 if( $db_res != 1 ) {
304                         return (4, $sql_statement);
305                 }
307                 # entry already exists -> run update
308         } else  {
309                 my @update_l;
310                 while( my ($pram, $val) = each %{$arg} ) {
311                         if( $pram eq 'table' ) { next; }
312                         if( $pram eq 'primkey' ) { next; }
313                         push(@update_l, "$pram='$val'");
314                 }
315                 my $update_str= join(", ", @update_l);
316                 $update_str= " SET $update_str";
318                 my $sql_statement= "UPDATE $table $update_str $prim_statement";
319                 my $db_res = &update_dbentry($self, $sql_statement );
320         }
322         return 0;
326 sub update_dbentry {
327         my ($self, $sql)= @_;
328         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
329                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
330                 return;
331         }
332         my $db_answer= $self->exec_statement($sql); 
333         return $db_answer;
337 sub del_dbentry {
338         my ($self, $sql)= @_;;
339         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
340                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
341                 return;
342         }
343         my $db_res= $self->exec_statement($sql);
344         return $db_res;
348 sub get_table_columns {
349         my $self = shift;
350         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
351                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
352                 return;
353         }
354         my $table = shift;
355         my @column_names;
357         if(exists $col_names->{$table}) {
358                 foreach my $col_name (@{$col_names->{$table}}) {
359                         push @column_names, ($1) if $col_name =~ /^(.*?)\s.*$/;
360                 }
361         } else {
362                 my @res;
363                 foreach my $column ( @{ $self->exec_statement ( "pragma table_info('$table')" ) } ) {
364                         push(@column_names, @$column[1]);
365                 }
366         }
368         return \@column_names;
372 sub select_dbentry {
373         my ($self, $sql)= @_;
374         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
375                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
376                 return;
377         }
378         my $error= 0;
379         my $answer= {};
380         my $db_answer= $self->exec_statement($sql); 
381         my @column_list;
383         # fetch column list of db and create a hash with column_name->column_value of the select query
384         $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
385         my $selected_cols = $1;
386         my $table = $2;
388         # all columns are used for creating answer
389         if ($selected_cols eq '*') {
390                 @column_list = @{ $self->get_table_columns($table) };    
392                 # specific columns are used for creating answer
393         } else {
394                 # remove all blanks and split string to list of column names
395                 $selected_cols =~ s/ //g;          
396                 @column_list = split(/,/, $selected_cols);
397         }
399         # create answer
400         my $hit_counter = 0;
401         my $list_len = @column_list;
402         foreach my $hit ( @{$db_answer} ){
403                 $hit_counter++;
404                 for ( my $i = 0; $i < $list_len; $i++) {
405                         $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
406                 }
407         }
409         return $answer;  
413 sub show_table {
414         my $self = shift;
415         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
416                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
417                 return;
418         }
419         my $table_name = shift;
421         my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
422         my $res= $self->exec_statement($sql_statement);
423         my @answer;
424         foreach my $hit (@{$res}) {
425                 push(@answer, "hit: ".join(', ', @{$hit}));
426         }
428         return join("\n", @answer);
432 sub exec_statement {
433         my $self = shift;
434         my $sql_statement = shift;
435         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
436                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
437                 return;
438         }
440         if(not defined($sql_statement) or length($sql_statement) == 0) {
441                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
442                 return;
443         }
445         my @db_answer;
446         my $success= 0;
447         $self->lock();
448         # Give three chances to the sqlite database
449         # 1st chance
450         eval {
451                 my $sth = $self->{dbh}->prepare($sql_statement);
452                 my $res = $sth->execute();
453                 @db_answer = @{$sth->fetchall_arrayref()};
454                 $sth->finish();
455                 $success=1;
456                 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
457         };
458         if($@) {
459                 eval {
460                         $self->{dbh}->do("ANALYZE");
461                         $self->{dbh}->do("VACUUM");
462                         $self->{dbh}->do("pragma integrity_check");
463                 };
464         }
465         if($success) {
466                 $self->unlock();
467                 return \@db_answer ;
468         }
470         # 2nd chance
471         eval {
472                 usleep(200);
473                 my $sth = $self->{dbh}->prepare($sql_statement);
474                 my $res = $sth->execute();
475                 @db_answer = @{$sth->fetchall_arrayref()};
476                 $sth->finish();
477                 $success=1;
478                 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
479         };
480         if($@) {
481                 eval {
482                         $self->{dbh}->do("ANALYZE");
483                         $self->{dbh}->do("VACUUM");
484                         $self->{dbh}->do("pragma integrity_check");
485                 };
486         }
487         if($success) {
488                 $self->unlock();
489                 return \@db_answer ;
490         }
492         # 3rd chance
493         eval {
494                 usleep(200);
495                 DBI->trace(6) if($main::verbose >= 7);
496                 my $sth = $self->{dbh}->prepare($sql_statement);
497                 my $res = $sth->execute();
498                 @db_answer = @{$sth->fetchall_arrayref()};
499                 $sth->finish();
500                 DBI->trace(0);
501                 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 74);
502         };
503         if($@) {
504                 DBI->trace(0);
505                 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
506         }
507         # TODO : maybe an error handling and an erro feedback to invoking function
508         #my $error = @$self->{dbh}->err;
509         #if ($error) {
510         #       my $error_string = @$self->{dbh}->errstr;
511         #}
513         $self->unlock();
514         return \@db_answer;
518 sub exec_statementlist {
519         my $self = shift;
520         my $sql_list = shift;
521         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
522                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
523                 return;
524         }
525         my @db_answer;
527         foreach my $sql_statement (@$sql_list) {
528                 if(defined($sql_statement) && length($sql_statement) > 0) {
529                         push @db_answer, $self->exec_statement($sql_statement);
530                 } else {
531                         next;
532                 }
533         }
535         return \@db_answer;
539 sub count_dbentries {
540         my ($self, $table)= @_;
541         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
542                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
543                 return;
544         }
545         my $error= 0;
546         my $count= -1;
548         my $sql_statement= "SELECT count() FROM $table";
549         my $db_answer= $self->select_dbentry($sql_statement); 
550         if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
551                 $count = $db_answer->{1}->{'count()'};
552         }
554         return $count;
558 sub move_table {
559         my ($self, $from, $to) = @_;
560         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
561                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
562                 return;
563         }
565         my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
566         my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
567         my $success = 0;
569         $self->lock();
570         eval {
571                 $self->{dbh}->begin_work();
572                 $self->{dbh}->do($sql_statement_drop);
573                 $self->{dbh}->do($sql_statement_alter);
574                 $self->{dbh}->commit();
575                 $success = 1;
576         };
577         if($@) {
578                 $self->{dbh}->rollback();
579                 eval {
580                         $self->{dbh}->do("ANALYZE");
581                 };
582                 if($@) {
583                         &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
584                 }
585                 eval {
586                         $self->{dbh}->do("VACUUM");
587                 };
588                 if($@) {
589                         &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
590                 }
591         }
593         if($success == 0) {
594                 eval {
595                         $self->{dbh}->begin_work();
596                         $self->{dbh}->do($sql_statement_drop);
597                         $self->{dbh}->do($sql_statement_alter);
598                         $self->{dbh}->commit();
599                         $success = 1;
600                 };
601                 if($@) {
602                         $self->{dbh}->rollback();
603                         eval {
604                                 $self->{dbh}->do("ANALYZE");
605                         };
606                         if($@) {
607                                 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
608                         }
609                         eval {
610                                 $self->{dbh}->do("VACUUM");
611                         };
612                         if($@) {
613                                 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
614                         }
615                 }
616         }
617         
618         if($success == 0) {
619                 eval {
620                         $self->{dbh}->begin_work();
621                         $self->{dbh}->do($sql_statement_drop);
622                         $self->{dbh}->do($sql_statement_alter);
623                         $self->{dbh}->commit();
624                         $success = 1;
625                 };
626                 if($@) {
627                         $self->{dbh}->rollback();
628                         &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
629                 }
630         }
632         &main::daemon_log("0 INFO: GOSA::DBsqlite::move_table: Operation successful!", 7);
633         $self->unlock();
635         return;
636
639 1;