From 3cb767f2477c88d8d774cc90db1292cb6f4e1c87 Mon Sep 17 00:00:00 2001 From: janw Date: Wed, 1 Apr 2009 08:27:48 +0000 Subject: [PATCH] Merge Statements. Recreate table if 2nd try after analyze fails. Check if filehandle is open before locking. git-svn-id: https://oss.gonicus.de/repositories/gosa/branches/2.6-lhm@13574 594d385d-05f5-0310-b6e9-bd551577e9d8 --- trunk/gosa-si/gosa-si-server | 3 +- trunk/gosa-si/modules/DBsqlite.pm | 115 +++++++++++++++++++++++++----- 2 files changed, 98 insertions(+), 20 deletions(-) diff --git a/trunk/gosa-si/gosa-si-server b/trunk/gosa-si/gosa-si-server index b3b48e670..7d4397398 100755 --- a/trunk/gosa-si/gosa-si-server +++ b/trunk/gosa-si/gosa-si-server @@ -1791,8 +1791,7 @@ sub watch_for_new_jobs { my $res_2 = $job_db->exec_statement( $sql_statement_2 ); if(defined($res_2) and defined @{$res_2}[0]) { # Set status from goto-activation to 'waiting' and update timestamp - $job_db->exec_statement("UPDATE $job_queue_tn SET status='waiting' WHERE macaddress LIKE '$macaddress' AND headertag = 'trigger_action_reinstall'"); - $job_db->exec_statement("UPDATE $job_queue_tn SET timestamp='".&calc_timestamp(&get_time(), 'plus', 30)."' WHERE macaddress LIKE '$macaddress' AND headertag = 'trigger_action_reinstall'"); + $job_db->exec_statement("UPDATE $job_queue_tn SET status='waiting', timestamp='".&calc_timestamp(&get_time(), 'plus', 30)."' WHERE macaddress LIKE '$macaddress' AND headertag = 'trigger_action_reinstall'"); } } next; diff --git a/trunk/gosa-si/modules/DBsqlite.pm b/trunk/gosa-si/modules/DBsqlite.pm index 161f15413..b75a66b81 100644 --- a/trunk/gosa-si/modules/DBsqlite.pm +++ b/trunk/gosa-si/modules/DBsqlite.pm @@ -44,13 +44,13 @@ sub lock { &main::daemon_log("0 ERROR: GOSA::DBsqlite::lock was called static! Statement was '$self'!", 1); return; } - if(not ref $self->{db_lock_handle}) { + if(not ref $self->{db_lock_handle} or not fileno $self->{db_lock_handle}) { 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); } my $lock_result = flock($self->{db_lock_handle}, LOCK_EX); if($lock_result==1) { seek($self->{db_lock_handle}, 0, 2); - &main::daemon_log("0 DEBUG: Acquired lock for database ".$self->{db_name}, 7); + &main::daemon_log("0 DEBUG: Acquired lock for database ".$self->{db_name}, 8); } else { &main::daemon_log("0 ERROR: Could not acquire lock for database ".$self->{db_name}, 1); } @@ -68,7 +68,7 @@ sub unlock { &main::daemon_log("0 BIG ERROR: Lockfile for database ".$self->{db_name}."got closed within critical section!", 1); } flock($self->{db_lock_handle}, LOCK_UN); - &main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 7); + &main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 8); return; } @@ -173,7 +173,7 @@ sub add_dbentry { my $sth = $self->{dbh}->prepare($sql_statement); $db_res = $sth->execute(); $sth->finish(); - &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 7); + &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9); $success = 1; }; if($@) { @@ -187,7 +187,7 @@ sub add_dbentry { my $sth = $self->{dbh}->prepare($sql_statement); $db_res = $sth->execute(); $sth->finish(); - &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 7); + &main::daemon_log("0 DEBUG: Execution of statement '$sql_statement' succeeded!", 9); $success = 1; }; if($@) { @@ -232,6 +232,8 @@ sub add_dbentry { return 0; } + + sub update_dbentry { my ($self, $sql)= @_; if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') { @@ -269,7 +271,7 @@ sub get_table_columns { } } else { my @res; - foreach my $column ( $self->exec_statement ( "pragma table_info('$table')" ) ) { + foreach my $column ( @{ $self->exec_statement ( "pragma table_info('$table')" ) } ) { push(@column_names, @$column[1]); } } @@ -338,14 +340,95 @@ sub show_table { } +sub recreate_database { + my $self = shift; + if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') { + &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1); + return; + } + + my $table_content; + + # Query all tables + eval { + my $sth = $self->{dbh}->prepare("select name from sqlite_master where type='table';"); + $sth->execute(); + my ($tables) = @{$sth->fetchall_arrayref()}; + foreach my $table (@$tables) { + if(defined($col_names->{$table})) { + # Schema definition for table exists, recreation is possible + my @column_names; + foreach my $column (@{$col_names->{$table}}) { + push @column_names, ($1) if $column =~ /(.*?)\s.*/; + } + my $column_query = join(',',@column_names); + my $sql = "SELECT $column_query FROM $table"; + my $sth = $self->{dbh}->prepare($sql); + $sth->execute(); + while (my @row = $sth->fetchrow_array()) { + push @{$table_content->{$table}}, @row; + } + $sth->finish; + } + } + + # Delete the database file + $self->{dbh}->disconnect(); + unlink($self->{db_name}); + + # Create a new database file + my $dbh = DBI->connect("dbi:SQLite:dbname=".$self->{db_name}, "", "", {RaiseError => 1, AutoCommit => 1}); + $self->{dbh} = $dbh; + + # Fill with contents + foreach my $table (@$tables) { + # Create schema + my $sql = "CREATE TABLE IF NOT EXISTS $table (".join(", ", @{$col_names->{$table}}).")"; + my $sth = $self->{dbh}->prepare($sql); + $sth->execute(); + + # Insert Dump + if(defined($table_content->{$table})) { + &main::daemon_log("0 DEBUG: Filling table ".$self->{db_name}.".$table with dump.", 7); + my %insert_hash; + my $i=0; + foreach my $row ($table_content->{$table}) { + foreach my $column (@{$col_names->{$table}}) { + my $column_name = $1 if $column =~ /(.*?)\s.*/; + $insert_hash{$column_name} = defined(@$row[$i])?@$row[$i]:undef; + $i++; + } + my @values; + my $column_query = join(",",keys %insert_hash); + foreach my $column(keys %insert_hash) { + push @values, $insert_hash{$column}; + } + my $value_query = join("', '", @values); + my $sql = "INSERT INTO $table ($column_query) VALUES ('$value_query')"; + my $sth = $self->{dbh}->prepare($sql); + $sth->execute; + } + } else { + &main::daemon_log("0 DEBUG: Table ".$self->{db_name}.".$table was empty.", 7); + } + } + }; + if($@) { + print STDERR Dumper($@); + } + + return; +} + + sub exec_statement { my $self = shift; my $sql_statement = shift; - if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') { &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1); return; } + if(not defined($sql_statement) or length($sql_statement) == 0) { &main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1); return; @@ -362,7 +445,7 @@ sub exec_statement { @db_answer = @{$sth->fetchall_arrayref()}; $sth->finish(); $success=1; - &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7); + &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9); }; if($@) { eval { @@ -374,24 +457,19 @@ sub exec_statement { $self->unlock(); return \@db_answer ; } - + # 2nd chance eval { - DBI->trace(6) if($main::verbose >= 7); + usleep(200); my $sth = $self->{dbh}->prepare($sql_statement); my $res = $sth->execute(); @db_answer = @{$sth->fetchall_arrayref()}; $sth->finish(); - DBI->trace(0); $success=1; - &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7); + &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9); }; if($@) { - eval { - $self->{dbh}->do("ANALYZE"); - $self->{dbh}->do("VACUUM"); - }; - DBI->trace(0); + $self->recreate_database(); } if($success) { $self->unlock(); @@ -400,13 +478,14 @@ sub exec_statement { # 3rd chance eval { + usleep(200); DBI->trace(6) if($main::verbose >= 7); my $sth = $self->{dbh}->prepare($sql_statement); my $res = $sth->execute(); @db_answer = @{$sth->fetchall_arrayref()}; $sth->finish(); DBI->trace(0); - &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7); + &main::daemon_log("0 DEBUG: $sql_statement succeeded.", 9); }; if($@) { DBI->trace(0); -- 2.30.2