gosa-si/modules/DBsqlite.pm

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