65d896bb4499872ed9ba39809a71fa423f8d8bb1
1 package GOSA::DBsqlite;
4 use strict;
5 use warnings;
6 use DBI;
7 use Data::Dumper;
8 use threads;
9 use Time::HiRes qw(usleep);
13 my $col_names = {};
15 sub new {
16 my $class = shift;
17 my $db_name = shift;
19 my $lock='/tmp/gosa_si_lock';
20 my $_lock = $db_name;
21 $_lock =~ tr/\//_/;
22 $lock.=$_lock;
23 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
24 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name");
25 $self->{dbh} = $dbh;
26 $self->{db_name} = $db_name;
27 $self->{db_lock} = $lock;
28 bless($self,$class);
30 return($self);
31 }
33 sub lock_exists : locked {
34 my $self=shift;
35 my $funcname=shift;
36 my $lock = $self->{db_lock};
37 my $result=(-f $lock);
38 if($result) {
39 #print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n";
40 usleep 100;
41 }
42 return $result;
43 }
45 sub create_lock : locked {
46 my $self=shift;
47 my $funcname=shift;
48 #print STDERR "(".((defined $funcname)?$funcname:"").") Erzeuge Lock (PID ".$$.") ".($self->{db_lock})."\n";
50 my $lock = $self->{db_lock};
51 while( -f $lock ) {
52 #print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n";
53 sleep 1;
54 }
56 open($self->{db_lock_handle},'>',$self->{db_lock});
57 }
59 sub remove_lock : locked {
60 my $self=shift;
61 my $funcname=shift;
62 #print STDERR "(".((defined $funcname)?$funcname:"").") Entferne Lock (PID ".$$.") ".$self->{db_lock}."\n";
63 close($self->{db_lock_handle});
64 unlink($self->{db_lock});
65 }
68 sub create_table {
69 my $self = shift;
70 my $table_name = shift;
71 my $col_names_ref = shift;
72 $col_names->{ $table_name } = $col_names_ref;
73 my $col_names_string = join(', ', @{$col_names_ref});
74 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
75 &create_lock($self,'create_table');
76 $self->{dbh}->do($sql_statement);
77 &remove_lock($self,'create_table');
78 return 0;
79 }
82 sub add_dbentry {
83 my $self = shift;
84 my $arg = shift;
86 # if dbh not specified, return errorflag 1
87 my $table = $arg->{table};
88 if( not defined $table ) {
89 return 1 ;
90 }
92 # specify primary key in table
93 if (not exists $arg->{primkey}) {
94 return 2;
95 }
96 my $primkey = $arg->{primkey};
98 # if primkey is id, fetch max id from table and give new job id= max(id)+1
99 if ($primkey eq 'id') {
100 my $id;
101 my $sql_statement = "SELECT MAX(CAST(id AS INTEGER)) FROM $table";
102 &create_lock($self,'add_dbentry');
103 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
104 &remove_lock($self,'add_dbentry');
105 if( defined $max_id) {
106 $id = $max_id + 1;
107 } else {
108 $id = 1;
109 }
110 $arg->{id} = $id;
111 }
113 # if timestamp is not provided, add timestamp
114 if( not exists $arg->{timestamp} ) {
115 $arg->{timestamp} = &get_time;
116 }
118 # check wether primkey is unique in table, otherwise return errorflag
119 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
120 &create_lock($self,'add_dbentry');
121 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
122 &remove_lock($self,'add_dbentry');
123 if ($res == 0) {
124 # primekey is unique
126 # fetch column names of table
127 my $col_names = &get_table_columns("",$table);
129 # assign values to column name variables
130 my @add_list;
131 foreach my $col_name (@{$col_names}) {
132 # use function parameter for column values
133 if (exists $arg->{$col_name}) {
134 push(@add_list, $arg->{$col_name});
135 }
136 }
138 my $sql_statement = "INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
140 print STDERR $sql_statement."\n";
142 &create_lock($self,'add_dbentry');
143 my $db_res = $self->{dbh}->do($sql_statement);
144 &remove_lock($self,'add_dbentry');
145 if( $db_res != 1 ) {
146 return 4;
147 }
149 } else {
150 # entry already exists, so update it
151 my $where_str= " WHERE $primkey='".$arg->{$primkey}."'";
153 my @update_l;
154 while( my ($pram, $val) = each %{$arg} ) {
155 if( $pram eq 'table' ) { next; }
156 if( $pram eq 'primkey' ) { next; }
157 push(@update_l, "$pram='$val'");
158 }
159 my $update_str= join(", ", @update_l);
160 $update_str= " SET $update_str";
162 my $sql_statement= "UPDATE $table $update_str $where_str";
163 my $db_res = &update_dbentry($self, $sql_statement );
165 }
167 return 0;
168 }
170 sub update_dbentry {
171 my ($self, $sql)= @_;
172 my $db_answer= &exec_statement($self, $sql);
173 return $db_answer;
174 }
177 sub del_dbentry {
178 my ($self, $sql)= @_;;
179 my $db_res= &exec_statement($self, $sql);
180 return $db_res;
181 }
184 sub get_table_columns {
185 my $self = shift;
186 my $table = shift;
187 my @column_names;
189 if(exists $col_names->{$table}) {
190 @column_names = @{$col_names->{$table}};
191 } else {
192 &create_lock($self,'get_table_columns');
193 my @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
194 &remove_lock($self,'get_table_columns');
195 foreach my $column (@res) {
196 push(@column_names, @$column[1]);
197 }
198 }
199 return \@column_names;
201 }
204 sub select_dbentry {
205 my ($self, $sql)= @_;
206 my $error= 0;
207 my $answer= {};
209 my $db_answer= &exec_statement($self, $sql);
211 # fetch column list of db and create a hash with column_name->column_value of the select query
212 $sql =~ /FROM ([\S]*?)( |$)/g;
213 my $table = $1;
214 my $column_list = &get_table_columns($self, $table);
215 my $list_len = @{ $column_list } ;
216 my $hit_counter = 0;
217 foreach my $hit ( @{ $db_answer }) {
218 $hit_counter++;
219 for ( my $i = 0; $i < $list_len; $i++) {
220 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
221 }
222 }
224 return $answer;
225 }
228 sub show_table {
229 my $self = shift;
230 my $table_name = shift;
231 #&create_lock($self,'show_table');
232 #my @res = @{$self->{dbh}->selectall_arrayref( "SELECT * FROM $table_name ORDER BY timestamp")};
233 #&remove_lock($self,'show_table');
235 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
236 print STDERR $sql_statement."\n";
237 my $res= &exec_statement($self, $sql_statement);
239 my @answer;
240 foreach my $hit (@{$res}) {
241 push(@answer, "hit: ".join(', ', @{$hit}));
242 }
243 return join("\n", @answer);
244 }
247 sub exec_statement {
248 my $self = shift;
249 my $sql_statement = shift;
251 &create_lock($self,'exec_statement');
252 my @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
253 &remove_lock($self, 'exec_statement');
255 return \@db_answer;
256 }
259 sub count_dbentries {
260 my ($self, $table)= @_;
261 my $error= 0;
262 my $answer= -1;
264 my $sql_statement= "SELECT * FROM $table";
265 my $db_answer= &select_dbentry($self, $sql_statement);
267 my $count = keys(%{$db_answer});
268 return $count;
269 }
271 sub get_time {
272 my ($seconds, $minutes, $hours, $monthday, $month,
273 $year, $weekday, $yearday, $sommertime) = localtime(time);
274 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
275 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
276 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
277 $month+=1;
278 $month = $month < 10 ? $month = "0".$month : $month;
279 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
280 $year+=1900;
281 return "$year$month$monthday$hours$minutes$seconds";
282 }
285 1;