trunk/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}) {
  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}, 7);
  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}, 7);
  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!", 7);
 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!", 7);
 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 sub update_dbentry {
 236         my ($self, $sql)= @_;
 237         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 238                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
 239                 return;
 240         }
 241         my $db_answer= $self->exec_statement($sql);
 242         return $db_answer;
 243 }
 244
 245
 246 sub del_dbentry {
 247         my ($self, $sql)= @_;;
 248         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 249                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
 250                 return;
 251         }
 252         my $db_res= $self->exec_statement($sql);
 253         return $db_res;
 254 }
 255
 256
 257 sub get_table_columns {
 258         my $self = shift;
 259         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 260                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
 261                 return;
 262         }
 263         my $table = shift;
 264         my @column_names;
 265
 266         if(exists $col_names->{$table}) {
 267                 foreach my $col_name (@{$col_names->{$table}}) {
 268                         push @column_names, ($1) if $col_name =~ /^(.*?)\s.*$/;
 269                 }
 270         } else {
 271                 my @res;
 272                 foreach my $column ( $self->exec_statement ( "pragma table_info('$table')" ) ) {
 273                         push(@column_names, @$column[1]);
 274                 }
 275         }
 276
 277         return \@column_names;
 278 }
 279
 280
 281 sub select_dbentry {
 282         my ($self, $sql)= @_;
 283         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 284                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
 285                 return;
 286         }
 287         my $error= 0;
 288         my $answer= {};
 289         my $db_answer= $self->exec_statement($sql);
 290         my @column_list;
 291
 292         # fetch column list of db and create a hash with column_name->column_value of the select query
 293         $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
 294         my $selected_cols = $1;
 295         my $table = $2;
 296
 297         # all columns are used for creating answer
 298         if ($selected_cols eq '*') {
 299                 @column_list = @{ $self->get_table_columns($table) };
 300
 301                 # specific columns are used for creating answer
 302         } else {
 303                 # remove all blanks and split string to list of column names
 304                 $selected_cols =~ s/ //g;
 305                 @column_list = split(/,/, $selected_cols);
 306         }
 307
 308         # create answer
 309         my $hit_counter = 0;
 310         my $list_len = @column_list;
 311         foreach my $hit ( @{$db_answer} ){
 312                 $hit_counter++;
 313                 for ( my $i = 0; $i < $list_len; $i++) {
 314                         $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
 315                 }
 316         }
 317
 318         return $answer;
 319 }
 320
 321
 322 sub show_table {
 323         my $self = shift;
 324         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 325                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
 326                 return;
 327         }
 328         my $table_name = shift;
 329
 330         my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
 331         my $res= $self->exec_statement($sql_statement);
 332         my @answer;
 333         foreach my $hit (@{$res}) {
 334                 push(@answer, "hit: ".join(', ', @{$hit}));
 335         }
 336
 337         return join("\n", @answer);
 338 }
 339
 340
 341 sub exec_statement {
 342         my $self = shift;
 343         my $sql_statement = shift;
 344
 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         if(not defined($sql_statement) or length($sql_statement) == 0) {
 350                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
 351                 return;
 352         }
 353
 354         my @db_answer;
 355         my $success= 0;
 356         $self->lock();
 357         # Give three chances to the sqlite database
 358         # 1st chance
 359         eval {
 360                 my $sth = $self->{dbh}->prepare($sql_statement);
 361                 my $res = $sth->execute();
 362                 @db_answer = @{$sth->fetchall_arrayref()};
 363                 $sth->finish();
 364                 $success=1;
 365                 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
 366         };
 367         if($@) {
 368                 eval {
 369                         $self->{dbh}->do("ANALYZE");
 370                         $self->{dbh}->do("VACUUM");
 371                 };
 372         }
 373         if($success) {
 374                 $self->unlock();
 375                 return \@db_answer ;
 376         }
 377
 378         # 2nd chance
 379         eval {
 380                 DBI->trace(6) if($main::verbose >= 7);
 381                 my $sth = $self->{dbh}->prepare($sql_statement);
 382                 my $res = $sth->execute();
 383                 @db_answer = @{$sth->fetchall_arrayref()};
 384                 $sth->finish();
 385                 DBI->trace(0);
 386                 $success=1;
 387                 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
 388         };
 389         if($@) {
 390                 eval {
 391                         $self->{dbh}->do("ANALYZE");
 392                         $self->{dbh}->do("VACUUM");
 393                 };
 394                 DBI->trace(0);
 395         }
 396         if($success) {
 397                 $self->unlock();
 398                 return \@db_answer ;
 399         }
 400
 401         # 3rd chance
 402         eval {
 403                 DBI->trace(6) if($main::verbose >= 7);
 404                 my $sth = $self->{dbh}->prepare($sql_statement);
 405                 my $res = $sth->execute();
 406                 @db_answer = @{$sth->fetchall_arrayref()};
 407                 $sth->finish();
 408                 DBI->trace(0);
 409                 &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
 410         };
 411         if($@) {
 412                 DBI->trace(0);
 413                 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
 414         }
 415         # TODO : maybe an error handling and an erro feedback to invoking function
 416         #my $error = @$self->{dbh}->err;
 417         #if ($error) {
 418         #       my $error_string = @$self->{dbh}->errstr;
 419         #}
 420
 421         $self->unlock();
 422         return \@db_answer;
 423 }
 424
 425
 426 sub exec_statementlist {
 427         my $self = shift;
 428         my $sql_list = shift;
 429         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 430                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
 431                 return;
 432         }
 433         my @db_answer;
 434
 435         foreach my $sql_statement (@$sql_list) {
 436                 if(defined($sql_statement) && length($sql_statement) > 0) {
 437                         push @db_answer, $self->exec_statement($sql_statement);
 438                 } else {
 439                         next;
 440                 }
 441         }
 442
 443         return \@db_answer;
 444 }
 445
 446
 447 sub count_dbentries {
 448         my ($self, $table)= @_;
 449         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 450                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
 451                 return;
 452         }
 453         my $error= 0;
 454         my $count= -1;
 455
 456         my $sql_statement= "SELECT count() FROM $table";
 457         my $db_answer= $self->select_dbentry($sql_statement);
 458         if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
 459                 $count = $db_answer->{1}->{'count()'};
 460         }
 461
 462         return $count;
 463 }
 464
 465
 466 sub move_table {
 467         my ($self, $from, $to) = @_;
 468         if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
 469                 &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
 470                 return;
 471         }
 472
 473         my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
 474         my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
 475         my $success = 0;
 476
 477         $self->lock();
 478         eval {
 479                 $self->{dbh}->begin_work();
 480                 $self->{dbh}->do($sql_statement_drop);
 481                 $self->{dbh}->do($sql_statement_alter);
 482                 $self->{dbh}->commit();
 483                 $success = 1;
 484         };
 485         if($@) {
 486                 $self->{dbh}->rollback();
 487                 eval {
 488                         $self->{dbh}->do("ANALYZE");
 489                 };
 490                 if($@) {
 491                         &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
 492                 }
 493                 eval {
 494                         $self->{dbh}->do("VACUUM");
 495                 };
 496                 if($@) {
 497                         &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
 498                 }
 499         }
 500
 501         if($success == 0) {
 502                 eval {
 503                         $self->{dbh}->begin_work();
 504                         $self->{dbh}->do($sql_statement_drop);
 505                         $self->{dbh}->do($sql_statement_alter);
 506                         $self->{dbh}->commit();
 507                         $success = 1;
 508                 };
 509                 if($@) {
 510                         $self->{dbh}->rollback();
 511                         eval {
 512                                 $self->{dbh}->do("ANALYZE");
 513                         };
 514                         if($@) {
 515                                 &main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
 516                         }
 517                         eval {
 518                                 $self->{dbh}->do("VACUUM");
 519                         };
 520                         if($@) {
 521                                 &main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
 522                         }
 523                 }
 524         }
 525
 526         if($success == 0) {
 527                 eval {
 528                         $self->{dbh}->begin_work();
 529                         $self->{dbh}->do($sql_statement_drop);
 530                         $self->{dbh}->do($sql_statement_alter);
 531                         $self->{dbh}->commit();
 532                         $success = 1;
 533                 };
 534                 if($@) {
 535                         $self->{dbh}->rollback();
 536                         &main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
 537                 }
 538         }
 539
 540         &main::daemon_log("0 INFO: GOSA::DBsqlite::move_table: Operation successful!", 7);
 541         $self->unlock();
 542
 543         return;
 544 }
 545
 546
 547 1;