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 ':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.".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});
  21         my $sth = $dbh->prepare("pragma integrity_check");
  22      $sth->execute();
  23         my @ret = $sth->fetchall_arrayref();
  24         if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
  25                 &main::daemon_log("DEBUG: Database image $db_name is ok", 7);
  26         } else {
  27                 &main::daemon_log("ERROR: Database image $db_name is malformed, creating new database.", 1);
  28                 $sth->finish();
  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         open($self->{db_lock_handle}, ">>".($self->{db_lock})) unless ref $self->{db_lock_handle};
  44         flock($self->{db_lock_handle},LOCK_EX);
  45         seek($self->{db_lock_handle}, 0, 2);
  46 }
  47
  48
  49 sub unlock {
  50         my $self = shift;
  51         flock($self->{db_lock_handle},LOCK_UN);
  52 }
  53
  54
  55 sub create_table {
  56         my $self = shift;
  57         my $table_name = shift;
  58         my $col_names_ref = shift;
  59         my @col_names;
  60         my @col_names_creation;
  61         foreach my $col_name (@$col_names_ref) {
  62                 # Save full column description for creation of database
  63                 push(@col_names_creation, $col_name);
  64                 my @t = split(" ", $col_name);
  65                 $col_name = $t[0];
  66                 # Save column name internally for select_dbentry
  67                 push(@col_names, $col_name);
  68         }
  69
  70         $col_names->{ $table_name } = $col_names_ref;
  71         my $col_names_string = join(", ", @col_names_creation);
  72         my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
  73         $self->lock();
  74         eval {
  75                 my $res = $self->{dbh}->do($sql_statement);
  76         };
  77         if($@) {
  78                 $self->{dbh}->do("ANALYZE");
  79                 eval {
  80                         my $res = $self->{dbh}->do($sql_statement);
  81                 };
  82                 if($@) {
  83                         &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
  84                 }
  85         }
  86         $self->unlock();
  87
  88         return 0;
  89 }
  90
  91
  92 sub add_dbentry {
  93         my $self = shift;
  94         my $arg = shift;
  95         my $res = 0;   # default value
  96
  97         # if dbh not specified, return errorflag 1
  98         my $table = $arg->{table};
  99         if( not defined $table ) {
 100                 return 1 ;
 101         }
 102
 103         # if timestamp is not provided, add timestamp
 104         if( not exists $arg->{timestamp} ) {
 105                 $arg->{timestamp} = &get_time;
 106         }
 107
 108         # check primkey and run insert or update
 109         my $primkeys = $arg->{'primkey'};
 110         my $prim_statement="";
 111         if( 0 != @$primkeys ) {   # more than one primkey exist in list
 112                 my @prim_list;
 113                 foreach my $primkey (@$primkeys) {
 114                         if( not exists $arg->{$primkey} ) {
 115                                 return (3, "primkey '$primkey' has no value for add_dbentry");
 116                         }
 117                         push(@prim_list, "$primkey='".$arg->{$primkey}."'");
 118                 }
 119                 $prim_statement = "WHERE ".join(" AND ", @prim_list);
 120
 121                 # check wether primkey is unique in table, otherwise return errorflag
 122                 my $sql_statement = "SELECT * FROM $table $prim_statement";
 123                 $self->lock();
 124                 eval {
 125                         $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
 126                 };
 127                 if($@) {
 128                         $self->{dbh}->do("ANALYZE");
 129                         eval {
 130                                 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
 131                         };
 132                         if($@) {
 133                                 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
 134                         }
 135                 }
 136                 $self->unlock();
 137
 138         }
 139
 140         # primkey is unique or no primkey specified -> run insert
 141         if ($res == 0) {
 142                 # fetch column names of table
 143                 my $col_names = &get_table_columns($self, $table);
 144
 145                 my $create_id=0;
 146                 foreach my $col_name (@{$col_names}) {
 147                         if($col_name eq "id" && (! exists $arg->{$col_name})) {
 148                                 #&main::daemon_log("0 DEBUG: id field found without value! Creating autoincrement statement!", 7);
 149                                 $create_id=1;
 150                         }
 151                 }
 152
 153                 # assign values to column name variables
 154                 my @col_list;
 155                 my @val_list;
 156                 foreach my $col_name (@{$col_names}) {
 157                         # use function parameter for column values
 158                         if (exists $arg->{$col_name}) {
 159                                 push(@col_list, "'".$col_name."'");
 160                                 push(@val_list, "'".$arg->{$col_name}."'");
 161                         }
 162                 }
 163
 164                 my $sql_statement;
 165                 if($create_id==1) {
 166                         $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
 167                 } else {
 168                         $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
 169                 }
 170                 my $db_res;
 171                 $self->lock();
 172                 eval {
 173                         $db_res = $self->{dbh}->do($sql_statement);
 174                 };
 175                 if($@) {
 176                         $self->{dbh}->do("ANALYZE");
 177                         eval {
 178                                 $db_res = $self->{dbh}->do($sql_statement);
 179                         };
 180                         if($@) {
 181                                 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
 182                         }
 183                 }
 184                 $self->unlock();
 185
 186                 if( $db_res != 1 ) {
 187                         return (4, $sql_statement);
 188                 }
 189
 190                 # entry already exists -> run update
 191         } else  {
 192                 my @update_l;
 193                 while( my ($pram, $val) = each %{$arg} ) {
 194                         if( $pram eq 'table' ) { next; }
 195                         if( $pram eq 'primkey' ) { next; }
 196                         push(@update_l, "$pram='$val'");
 197                 }
 198                 my $update_str= join(", ", @update_l);
 199                 $update_str= " SET $update_str";
 200
 201                 my $sql_statement= "UPDATE $table $update_str $prim_statement";
 202                 my $db_res = &update_dbentry($self, $sql_statement );
 203         }
 204
 205         return 0;
 206 }
 207
 208
 209 sub update_dbentry {
 210         my ($self, $sql)= @_;
 211         my $db_answer= &exec_statement($self, $sql);
 212         return $db_answer;
 213 }
 214
 215
 216 sub del_dbentry {
 217         my ($self, $sql)= @_;;
 218         my $db_res= &exec_statement($self, $sql);
 219         return $db_res;
 220 }
 221
 222
 223 sub get_table_columns {
 224         my $self = shift;
 225         my $table = shift;
 226         my @column_names;
 227
 228         if(exists $col_names->{$table}) {
 229                 @column_names = @{$col_names->{$table}};
 230         } else {
 231                 my @res;
 232                 $self->lock();
 233                 eval {
 234                         @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
 235                 };
 236                 if($@) {
 237                         $self->{dbh}->do("ANALYZE");
 238                         eval {
 239                                 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
 240                         };
 241                         if($@) {
 242                                 &main::daemon_log("ERROR: pragma table_info('$table') failed with $@", 1);
 243                         }
 244                 }
 245                 $self->unlock();
 246
 247                 foreach my $column (@res) {
 248                         push(@column_names, @$column[1]);
 249                 }
 250         }
 251         return \@column_names;
 252
 253 }
 254
 255
 256 sub select_dbentry {
 257         my ($self, $sql)= @_;
 258         my $error= 0;
 259         my $answer= {};
 260         my $db_answer= &exec_statement($self, $sql);
 261         my @column_list;
 262
 263         # fetch column list of db and create a hash with column_name->column_value of the select query
 264         $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
 265         my $selected_cols = $1;
 266         my $table = $2;
 267
 268         # all columns are used for creating answer
 269         if ($selected_cols eq '*') {
 270                 @column_list = @{ &get_table_columns($self, $table) };
 271
 272                 # specific columns are used for creating answer
 273         } else {
 274                 # remove all blanks and split string to list of column names
 275                 $selected_cols =~ s/ //g;
 276                 @column_list = split(/,/, $selected_cols);
 277         }
 278
 279         # create answer
 280         my $hit_counter = 0;
 281         my $list_len = @column_list;
 282         foreach my $hit ( @{$db_answer} ){
 283                 $hit_counter++;
 284                 for ( my $i = 0; $i < $list_len; $i++) {
 285                         $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
 286                 }
 287         }
 288
 289         return $answer;
 290 }
 291
 292
 293 sub show_table {
 294         my $self = shift;
 295         my $table_name = shift;
 296
 297         my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
 298         my $res= &exec_statement($self, $sql_statement);
 299         my @answer;
 300         foreach my $hit (@{$res}) {
 301                 push(@answer, "hit: ".join(', ', @{$hit}));
 302         }
 303
 304         return join("\n", @answer);
 305 }
 306
 307
 308 sub exec_statement {
 309         my $self = shift;
 310         my $sql_statement = shift;
 311         my @db_answer;
 312
 313         $self->lock();
 314         eval {
 315                 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
 316         };
 317         if($@) {
 318                 $self->{dbh}->do("ANALYZE");
 319                 eval {
 320                         @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
 321                 };
 322                 if($@) {
 323                         &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
 324                 }
 325         }
 326         $self->unlock();
 327         # TODO : maybe an error handling and an erro feedback to invoking function
 328         #my $error = @$self->{dbh}->err;
 329         #if ($error) {
 330         #       my $error_string = @$self->{dbh}->errstr;
 331         #}
 332
 333         return \@db_answer;
 334 }
 335
 336
 337 sub exec_statementlist {
 338         my $self = shift;
 339         my $sql_list = shift;
 340         my @db_answer;
 341
 342         foreach my $sql (@$sql_list) {
 343                 if(defined($sql) && length($sql) > 0) {
 344                         # Obtain a new lock for each statement to not block the db for a too long time
 345                         $self->lock();
 346                         eval {
 347                                 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
 348                                 push @db_answer, @answer;
 349                         };
 350                         if($@) {
 351                                 $self->{dbh}->do("ANALYZE");
 352                                 eval {
 353                                         my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
 354                                         push @db_answer, @answer;
 355                                 };
 356                                 if($@) {
 357                                         &main::daemon_log("ERROR: $sql failed with $@", 1);
 358                                 }
 359                         }
 360                         $self->unlock();
 361                 } else {
 362                         next;
 363                 }
 364         }
 365
 366         return \@db_answer;
 367 }
 368
 369
 370 sub count_dbentries {
 371         my ($self, $table)= @_;
 372         my $error= 0;
 373         my $answer= -1;
 374
 375         my $sql_statement= "SELECT * FROM $table";
 376         my $db_answer= &select_dbentry($self, $sql_statement);
 377
 378         my $count = keys(%{$db_answer});
 379         return $count;
 380 }
 381
 382
 383 sub move_table {
 384         my ($self, $from, $to) = @_;
 385
 386         my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
 387         my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
 388
 389         eval {
 390                 $self->{dbh}->do($sql_statement_drop);
 391         };
 392         if($@) {
 393                 $self->{dbh}->do("ANALYZE");
 394                 eval {
 395                         $self->{dbh}->do($sql_statement_drop);
 396                 };
 397                 if($@) {
 398                         &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1);
 399                 }
 400         }
 401
 402         eval {
 403                 $self->{dbh}->do($sql_statement_alter);
 404         };
 405         if($@) {
 406                 $self->{dbh}->do("ANALYZE");
 407                 eval {
 408                         $self->{dbh}->do($sql_statement_alter);
 409                 };
 410                 if($@) {
 411                         &main::daemon_log("ERROR: $sql_statement_alter failed with $@", 1);
 412                 }
 413         }
 414
 415         return;
 416 }
 417
 418
 419 1;