X-Git-Url: https://git.tokkee.org/?a=blobdiff_plain;f=gosa-si%2Fmodules%2FDBsqlite.pm;h=8eba13d478cf28edeaadfc914db033ef716cfda2;hb=8d32c2811e256aeea583ef2f04654d6b5e56220f;hp=031702871c87002e54d15614811c90fec72aff8f;hpb=f603bb9c16d96324fc43082ec4966f36b32a8107;p=gosa.git diff --git a/gosa-si/modules/DBsqlite.pm b/gosa-si/modules/DBsqlite.pm index 031702871..8eba13d47 100644 --- a/gosa-si/modules/DBsqlite.pm +++ b/gosa-si/modules/DBsqlite.pm @@ -5,19 +5,22 @@ use strict; use warnings; use DBI; use Data::Dumper; +use GOSA::GosaSupportDaemon; use threads; use Time::HiRes qw(usleep); + my $col_names = {}; sub new { my $class = shift; my $db_name = shift; - my $lock='/tmp/gosa_si_lock'; - my $_lock = $db_name; - $_lock =~ tr/\//_/; - $lock.=$_lock; + my $lock = $db_name.".lock"; + # delete existing lock - instance should be running only once + if(stat($lock)) { + 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"); $self->{dbh} = $dbh; @@ -34,7 +37,7 @@ sub lock_exists : locked { my $lock = $self->{db_lock}; my $result=(-f $lock); if($result) { - #print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n"; + #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock found", 8); usleep 100; } return $result; @@ -43,12 +46,12 @@ sub lock_exists : locked { sub create_lock : locked { my $self=shift; my $funcname=shift; - #print STDERR "(".((defined $funcname)?$funcname:"").") Erzeuge Lock (PID ".$$.") ".($self->{db_lock})."\n"; + #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Creating Lock (PID ".$$.") ".($self->{db_lock}),8); my $lock = $self->{db_lock}; while( -f $lock ) { - #print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n"; - sleep 1; + #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock found",8); + usleep 100; } open($self->{db_lock_handle},'>',$self->{db_lock}); @@ -57,24 +60,31 @@ sub create_lock : locked { sub remove_lock : locked { my $self=shift; my $funcname=shift; - #print STDERR "(".((defined $funcname)?$funcname:"").") Entferne Lock (PID ".$$.") ".$self->{db_lock}."\n"; + #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Removing Lock (PID ".$$.") ".$self->{db_lock}, 8); close($self->{db_lock_handle}); unlink($self->{db_lock}); } + sub create_table { my $self = shift; my $table_name = shift; my $col_names_ref = shift; + my @col_names; + foreach my $col_name (@$col_names_ref) { + my @t = split(" ", $col_name); + $col_name = $t[0]; + push(@col_names, $col_name); + } + $col_names->{ $table_name } = $col_names_ref; - my $col_names_string = join(', ', @{$col_names_ref}); - my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )"; + my $col_names_string = join("', '", @col_names); + my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( '$col_names_string' )"; &create_lock($self,'create_table'); $self->{dbh}->do($sql_statement); &remove_lock($self,'create_table'); return 0; } - sub add_dbentry { @@ -89,141 +99,90 @@ sub add_dbentry { # specify primary key in table if (not exists $arg->{primkey}) { - return 2; + return (2, "a hash key 'primkey' with at least an empty list as value is necessary for add_dbentry"); } - 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(CAST(id AS INTEGER)) FROM $table"; - &create_lock($self,'add_dbentry'); - my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0]; - &remove_lock($self,'add_dbentry'); - if( defined $max_id) { - $id = $max_id + 1; - } else { - $id = 1; + my $primkeys = $arg->{'primkey'}; + my $prim_statement=""; + if( 0 != @$primkeys ) { + my @prim_list; + foreach my $primkey (@$primkeys) { + if($primkey eq 'id') { + # if primkey is id, fetch max id from table and give new job id= max(id)+1 + my $sql_statement = "SELECT MAX(CAST(id AS INTEGER)) FROM $table"; + &create_lock($self,'add_dbentry'); + my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0]; + &remove_lock($self,'add_dbentry'); + my $id; + if( defined $max_id) { + $id = $max_id + 1; + } else { + $id = 1; + } + $arg->{id} = $id; + } + if( not exists $arg->{$primkey} ) { + return (3, "primkey '$primkey' has no value for add_dbentry"); + } + push(@prim_list, "$primkey='".$arg->{$primkey}."'"); } - $arg->{id} = $id; + $prim_statement = "WHERE ".join(" AND ", @prim_list); } - - # 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; } # check wether primkey is unique in table, otherwise return errorflag - my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'"; + my $sql_statement = "SELECT * FROM $table $prim_statement"; &create_lock($self,'add_dbentry'); my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) }; &remove_lock($self,'add_dbentry'); + if ($res == 0) { + # primekey is unique + # fetch column names of table - my $col_names = &get_table_columns("",$table); + my $col_names = &get_table_columns($self, $table); # assign values to column name variables - my @add_list; + my @col_list; + my @val_list; foreach my $col_name (@{$col_names}) { - # use function parameter for column values + # use function parameter for column values if (exists $arg->{$col_name}) { - push(@add_list, $arg->{$col_name}); + push(@col_list, $col_name); + push(@val_list, $arg->{$col_name}); } } - my $sql_statement = "INSERT INTO $table VALUES ('".join("', '", @add_list)."')"; - print STDERR $sql_statement; + my $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES ('".join("', '", @val_list)."')"; &create_lock($self,'add_dbentry'); my $db_res = $self->{dbh}->do($sql_statement); &remove_lock($self,'add_dbentry'); if( $db_res != 1 ) { - return 4; - } else { - return 0; - } + return (4, $sql_statement); + } } else { - my $update_hash = { table=>$table }; - $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ]; - $update_hash->{update} = [ {} ]; + # entry already exists, so update it + my @update_l; 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( $self, $update_hash ); - if( $db_res != 1 ) { - return 5; - } else { - return 0; + 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 $self = 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"; -# &create_lock($self,'update_dbentry'); -# my $db_answer = $self->{dbh}->do($sql_statement); -# &remove_lock($self,'update_dbentry'); -# return $db_answer; -#} sub update_dbentry { my ($self, $sql)= @_; my $db_answer= &exec_statement($self, $sql); @@ -249,6 +208,7 @@ sub get_table_columns { &create_lock($self,'get_table_columns'); my @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")}; &remove_lock($self,'get_table_columns'); + foreach my $column (@res) { push(@column_names, @$column[1]); } @@ -260,15 +220,16 @@ sub get_table_columns { sub select_dbentry { my ($self, $sql)= @_; - + my $error= 0; + my $answer= {}; + my $db_answer= &exec_statement($self, $sql); # fetch column list of db and create a hash with column_name->column_value of the select query - $sql =~ /FROM ([\S]*?) /g; + $sql =~ /FROM ([\S]*?)( |$)/g; my $table = $1; my $column_list = &get_table_columns($self, $table); my $list_len = @{ $column_list } ; - my $answer = {}; my $hit_counter = 0; foreach my $hit ( @{ $db_answer }) { $hit_counter++; @@ -284,11 +245,12 @@ sub select_dbentry { sub show_table { my $self = shift; my $table_name = shift; - &create_lock($self,'show_table'); - my @res = @{$self->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")}; - &remove_lock($self,'show_table'); + + 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); @@ -307,18 +269,46 @@ sub exec_statement { } -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; + + &create_lock($self,'exec_statement'); + foreach my $sql (@$sql_list) { + @db_answer = @{$self->{dbh}->selectall_arrayref($sql)}; + } + &remove_lock($self, 'exec_statement'); + + 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"; + &create_lock($self,'move_table'); + my $db_res = $self->{dbh}->do($sql_statement_drop); + $db_res = $self->{dbh}->do($sql_statement_alter); + &remove_lock($self,'move_table'); + + return; +} + + 1;