X-Git-Url: https://git.tokkee.org/?a=blobdiff_plain;f=gosa-si%2Fmodules%2FDBsqlite.pm;h=556b31ef7e96878077fe6793d4c0a0a73973aa3b;hb=67c1e7245360178883e824c3f7519d0d44863a8f;hp=21d7200711fdf77b3a7046b171f4f59c602c6c92;hpb=33e38b5c7bcdd1a8828f640c055401c6bf3b707b;p=gosa.git diff --git a/gosa-si/modules/DBsqlite.pm b/gosa-si/modules/DBsqlite.pm index 21d720071..556b31ef7 100644 --- a/gosa-si/modules/DBsqlite.pm +++ b/gosa-si/modules/DBsqlite.pm @@ -5,307 +5,373 @@ use strict; use warnings; use DBI; use Data::Dumper; +use GOSA::GosaSupportDaemon; +use Time::HiRes qw(usleep); +my $col_names = {}; sub new { - my $object = shift; + my $class = shift; my $db_name = shift; - my $obj_ref = {}; - bless($obj_ref,$object); - my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name"); - $obj_ref->{dbh} = $dbh; - - return($obj_ref); + my $lock = $db_name.".si.lock"; + # delete existing lock - instance should be running only once + if(stat($lock)) { + &main::daemon_log("DEBUG: Removed existing lock $lock.", 7); + unlink($lock); + } + my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef}; + my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1}); + $self->{dbh} = $dbh; + $self->{db_name} = $db_name; + $self->{db_lock} = $lock; + bless($self,$class); + + return($self); } sub create_table { - my $object = shift; + my $self = shift; my $table_name = shift; my $col_names_ref = shift; -# unshift(@{$col_names_ref}, "id INTEGER PRIMARY KEY AUTOINCREMENT"); - my $col_names_string = join(', ', @{$col_names_ref}); - my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )"; - $object->{dbh}->do($sql_statement); - return 0; -} - - - -sub add_dbentry { - - my $obj = shift; - my $arg = shift; - - # if dbh not specified, return errorflag 1 - my $table = $arg->{table}; - if (not defined $table) { - return 1; + my @col_names; + foreach my $col_name (@$col_names_ref) { + my @t = split(" ", $col_name); + $col_name = $t[0]; + push(@col_names, $col_name); } - # specify primary key in table - if (not exists $arg->{primkey}) { - return 2; - } - my $primkey = $arg->{primkey}; - - # if primkey is id, fetch max id from table and give new job id= max(id)+1 - if ($primkey eq 'id') { - my $id; - my $sql_statement = "SELECT MAX(id) FROM $table"; - my $max_id = @{ @{ $obj->{dbh}->selectall_arrayref($sql_statement) }[0] }[0]; - if( defined $max_id) { - $id = $max_id + 1; - } else { - $id = 1; - } - $arg->{id} = $id; - } + $col_names->{ $table_name } = $col_names_ref; + my $col_names_string = join("', '", @col_names); + my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( '$col_names_string' )"; + eval { + my $res = $self->{dbh}->do($sql_statement); + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + } + eval { + my $res = $self->{dbh}->do($sql_statement); + }; + if($@) { + &main::daemon_log("ERROR: $sql_statement failed with $@", 1); + } - # check wether value to primary key is specified - if ( not exists $arg->{ $primkey } ) { - return 3; - } - - # if timestamp is not provided, add timestamp - if( not exists $arg->{timestamp} ) { - $arg->{timestamp} = &get_time; - } + return 0; +} - # check wether primkey is unique in table, otherwise return errorflag 3 - my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'"; - my $res = @{ $obj->{dbh}->selectall_arrayref($sql_statement) }; - if ($res == 0) { - # fetch column names of table - my $col_names = $obj->get_table_columns($table); - - # assign values to column name variables - my @add_list; - foreach my $col_name (@{$col_names}) { - # use function parameter for column values - if (exists $arg->{$col_name}) { - push(@add_list, $arg->{$col_name}); - } - } - - my $sql_statement = " INSERT INTO $table VALUES ('".join("', '", @add_list)."')"; - my $db_res = $obj->{dbh}->do($sql_statement); - if( $db_res != 1 ) { - return 1; - } else { - return 0; - } - } else { - my $update_hash = { table=>$table }; - $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ]; - $update_hash->{update} = [ {} ]; - while( my ($pram, $val) = each %{$arg} ) { - if( $pram eq 'table' ) { next; } - if( $pram eq 'primkey' ) { next; } - $update_hash->{update}[0]->{$pram} = [$val]; - } - my $db_res = &update_dbentry( $obj, $update_hash ); - if( $db_res != 1 ) { - return 1; - } else { - return 0; - } - - } +sub add_dbentry { + my $self = shift; + my $arg = shift; + my $res = 0; # default value + + # if dbh not specified, return errorflag 1 + my $table = $arg->{table}; + if( not defined $table ) { + return 1 ; + } + + # if timestamp is not provided, add timestamp + if( not exists $arg->{timestamp} ) { + $arg->{timestamp} = &get_time; + } + + # check primkey and run insert or update + my $primkeys = $arg->{'primkey'}; + my $prim_statement=""; + if( 0 != @$primkeys ) { # more than one primkey exist in list + my @prim_list; + foreach my $primkey (@$primkeys) { + if( not exists $arg->{$primkey} ) { + return (3, "primkey '$primkey' has no value for add_dbentry"); + } + push(@prim_list, "$primkey='".$arg->{$primkey}."'"); + } + $prim_statement = "WHERE ".join(" AND ", @prim_list); + + # check wether primkey is unique in table, otherwise return errorflag + my $sql_statement = "SELECT * FROM $table $prim_statement"; + eval { + $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) }; + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + eval { + $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) }; + }; + if($@) { + &main::daemon_log("ERROR: $sql_statement failed with $@", 1); + } + } + + } + + # primkey is unique or no primkey specified -> run insert + if ($res == 0) { + # fetch column names of table + my $col_names = &get_table_columns($self, $table); + + my $create_id=0; + foreach my $col_name (@{$col_names}) { + if($col_name eq "id" && (! exists $arg->{$col_name})) { + &main::daemon_log("DEBUG: id field found without value! Creating autoincrement statement!"); + $create_id=1; + } + } + + # assign values to column name variables + my @col_list; + my @val_list; + foreach my $col_name (@{$col_names}) { + # use function parameter for column values + if (exists $arg->{$col_name}) { + push(@col_list, "'".$col_name."'"); + push(@val_list, "'".$arg->{$col_name}."'"); + } + } + + my $sql_statement; + if($create_id==1) { + $sql_statement = "INSERT INTO $table ('id', ".join(", ", @col_list).") VALUES ((select coalesce(max(id), 0)+1 from $table), ".join(", ", @val_list).")"; + } else { + $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")"; + } + my $db_res; + eval { + $db_res = $self->{dbh}->do($sql_statement); + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + eval { + $db_res = $self->{dbh}->do($sql_statement); + }; + if($@) { + &main::daemon_log("ERROR: $sql_statement failed with $@", 1); + } + } + + if( $db_res != 1 ) { + return (4, $sql_statement); + } + + # entry already exists -> run update + } else { + my @update_l; + while( my ($pram, $val) = each %{$arg} ) { + if( $pram eq 'table' ) { next; } + if( $pram eq 'primkey' ) { next; } + push(@update_l, "$pram='$val'"); + } + my $update_str= join(", ", @update_l); + $update_str= " SET $update_str"; + + my $sql_statement= "UPDATE $table $update_str $prim_statement"; + my $db_res = &update_dbentry($self, $sql_statement ); + } + + return 0; } -# error-flags -# 1 no table ($table) defined -# 2 no restriction parameter ($restric_pram) defined -# 3 no restriction value ($restric_val) defined -# 4 column name not known in table -# 5 no column names to change specified sub update_dbentry { - my $obj = shift; - my $arg = shift; - - # check completeness of function parameter - # extract table statement from arg hash - my $table = $arg->{table}; - if (not defined $table) { - return 1; - } else { - delete $arg->{table}; - } - - # extract where parameter from arg hash - my $where_statement = ""; - if( exists $arg->{where} ) { - my $where_hash = @{ $arg->{where} }[0]; - if( 0 < keys %{ $where_hash } ) { - my @where_list; - while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) { - my $statement; - if( $rest_pram eq 'timestamp' ) { - $statement = "$rest_pram<'@{ $rest_val }[0]'"; - } else { - $statement = "$rest_pram='@{ $rest_val }[0]'"; - } - push( @where_list, $statement ); - } - $where_statement .= "WHERE ".join('AND ', @where_list); - } - } - - # extract update parameter from arg hash - my $update_hash = @{ $arg->{update} }[0]; - my $update_statement = ""; - if( 0 < keys %{ $update_hash } ) { - my @update_list; - while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) { - my $statement = "$rest_pram='@{ $rest_val }[0]'"; - push( @update_list, $statement ); - } - $update_statement .= join(', ', @update_list); - } - - my $sql_statement = "UPDATE $table SET $update_statement $where_statement"; - my $db_answer = $obj->{dbh}->do($sql_statement); + my ($self, $sql)= @_; + my $db_answer= &exec_statement($self, $sql); return $db_answer; -} +} sub del_dbentry { - my $obj = shift; - my $arg = shift; - - # check completeness of function parameter - # extract table statement from arg hash - my $table = $arg->{table}; - if (not defined $table) { - return 1; - } else { - delete $arg->{table}; - } - - # collect select statements - my @del_list; - while (my ($pram, $val) = each %{$arg}) { - if ( $pram eq 'timestamp' ) { - push(@del_list, "$pram < '$val'"); - } else { - push(@del_list, "$pram = '$val'"); - } - } - - my $where_statement; - if( not @del_list ) { - $where_statement = ""; - } else { - $where_statement = "WHERE ".join(' AND ', @del_list); - } - - my $sql_statement = "DELETE FROM $table $where_statement"; - my $db_res = $obj->{dbh}->do($sql_statement); - + my ($self, $sql)= @_;; + my $db_res= &exec_statement($self, $sql); return $db_res; } sub get_table_columns { - my $obj = shift; + my $self = shift; my $table = shift; + my @column_names; + + if(exists $col_names->{$table}) { + @column_names = @{$col_names->{$table}}; + } else { + my @res; + eval { + @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")}; + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + eval { + @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")}; + }; + if($@) { + &main::daemon_log("ERROR: pragma table_info('$table') failed with $@", 1); + } + } + + foreach my $column (@res) { + push(@column_names, @$column[1]); + } + } + return \@column_names; - my @columns; - my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")}; - foreach my $column (@res) { - push(@columns, @$column[1]); - } - - return \@columns; } + sub select_dbentry { - my $obj = shift; - my $arg = shift; - - # check completeness of function parameter - # extract table statement from arg hash - my $table = $arg->{table}; - if (not defined $table) { - return 1; - } else { - delete $arg->{table}; - } + my ($self, $sql)= @_; + my $error= 0; + my $answer= {}; + my $db_answer= &exec_statement($self, $sql); + my @column_list; - # collect select statements - my @select_list; - my $sql_statement; - while (my ($pram, $val) = each %{$arg}) { - if ( $pram eq 'timestamp' ) { - push(@select_list, "$pram < '$val'"); - } else { - push(@select_list, "$pram = '$val'"); - } - } + # fetch column list of db and create a hash with column_name->column_value of the select query + $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g; + my $selected_cols = $1; + my $table = $2; - if (@select_list == 0) { - $sql_statement = "SELECT ROWID, * FROM '$table'"; + # all columns are used for creating answer + if ($selected_cols eq '*') { + @column_list = @{ &get_table_columns($self, $table) }; + + # specific columns are used for creating answer } else { - $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list); + # remove all blanks and split string to list of column names + $selected_cols =~ s/ //g; + @column_list = split(/,/, $selected_cols); } - # query db - my $query_answer = $obj->{dbh}->selectall_arrayref($sql_statement); - - # fetch column list of db and create a hash with column_name->column_value of the select query - my $column_list = &get_table_columns($obj, $table); - my $list_len = @{ $column_list } ; - my $answer = {}; + # create answer my $hit_counter = 0; - - - foreach my $hit ( @{ $query_answer }) { + my $list_len = @column_list; + foreach my $hit ( @{$db_answer} ){ $hit_counter++; - $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit }; for ( my $i = 0; $i < $list_len; $i++) { - $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i]; + $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i]; } } + return $answer; } sub show_table { - my $obj = shift; + my $self = shift; my $table_name = shift; - my @res = @{$obj->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")}; + + my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp"; + my $res= &exec_statement($self, $sql_statement); my @answer; - foreach my $hit (@res) { + foreach my $hit (@{$res}) { push(@answer, "hit: ".join(', ', @{$hit})); } + return join("\n", @answer); } sub exec_statement { - my $obj = shift; + my $self = shift; my $sql_statement = shift; - my @res = @{$obj->{dbh}->selectall_arrayref($sql_statement)}; - return \@res; + my @db_answer; + + eval { + @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)}; + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + eval { + @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)}; + }; + if($@) { + &main::daemon_log("ERROR: $sql_statement failed with $@", 1); + } + } + + return \@db_answer; } -sub get_time { - my ($seconds, $minutes, $hours, $monthday, $month, - $year, $weekday, $yearday, $sommertime) = localtime(time); - $hours = $hours < 10 ? $hours = "0".$hours : $hours; - $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes; - $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds; - $month+=1; - $month = $month < 10 ? $month = "0".$month : $month; - $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday; - $year+=1900; - return "$year$month$monthday$hours$minutes$seconds"; + +sub exec_statementlist { + my $self = shift; + my $sql_list = shift; + my @db_answer; + + foreach my $sql (@$sql_list) { + if(defined($sql) && length($sql) > 0) { + eval { + my @answer = @{$self->{dbh}->selectall_arrayref($sql)}; + push @db_answer, @answer; + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + eval { + my @answer = @{$self->{dbh}->selectall_arrayref($sql)}; + push @db_answer, @answer; + }; + if($@) { + &main::daemon_log("ERROR: $sql failed with $@", 1); + } + } + } else { + next; + } + } + + return \@db_answer; +} + + +sub count_dbentries { + my ($self, $table)= @_; + my $error= 0; + my $answer= -1; + + my $sql_statement= "SELECT * FROM $table"; + my $db_answer= &select_dbentry($self, $sql_statement); + + my $count = keys(%{$db_answer}); + return $count; } +sub move_table { + my ($self, $from, $to) = @_; + + my $sql_statement_drop = "DROP TABLE IF EXISTS $to"; + my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to"; + + eval { + $self->{dbh}->do($sql_statement_drop); + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + eval { + $self->{dbh}->do($sql_statement_drop); + }; + if($@) { + &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1); + } + } + + eval { + $self->{dbh}->do($sql_statement_alter); + }; + if($@) { + $self->{dbh}->do("ANALYZE"); + eval { + $self->{dbh}->do($sql_statement_alter); + }; + if($@) { + &main::daemon_log("ERROR: $sql_statement_alter failed with $@", 1); + } + } + + return; +} + + 1;