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);
11 my $col_names = {};
13 sub new {
14 my $class = shift;
15 my $db_name = shift;
17 my $lock='/tmp/gosa_si_lock';
18 my $_lock = $db_name;
19 $_lock =~ tr/\//_/;
20 $lock.=$_lock;
21 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
22 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name");
23 $self->{dbh} = $dbh;
24 $self->{db_name} = $db_name;
25 $self->{db_lock} = $lock;
26 bless($self,$class);
28 return($self);
29 }
31 sub lock_exists : locked {
32 my $self=shift;
33 my $funcname=shift;
34 my $lock = $self->{db_lock};
35 my $result=(-f $lock);
36 if($result) {
37 #print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n";
38 usleep 100;
39 }
40 return $result;
41 }
43 sub create_lock : locked {
44 my $self=shift;
45 my $funcname=shift;
46 #print STDERR "(".((defined $funcname)?$funcname:"").") Erzeuge Lock (PID ".$$.") ".($self->{db_lock})."\n";
48 my $lock = $self->{db_lock};
49 while( -f $lock ) {
50 #print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n";
51 sleep 1;
52 }
54 open($self->{db_lock_handle},'>',$self->{db_lock});
55 }
57 sub remove_lock : locked {
58 my $self=shift;
59 my $funcname=shift;
60 #print STDERR "(".((defined $funcname)?$funcname:"").") Entferne Lock (PID ".$$.") ".$self->{db_lock}."\n";
61 close($self->{db_lock_handle});
62 unlink($self->{db_lock});
63 }
65 sub create_table {
66 my $self = shift;
67 my $table_name = shift;
68 my $col_names_ref = shift;
69 $col_names->{ $table_name } = $col_names_ref;
70 my $col_names_string = join(', ', @{$col_names_ref});
71 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
72 &create_lock($self,'create_table');
73 $self->{dbh}->do($sql_statement);
74 &remove_lock($self,'create_table');
75 return 0;
76 }
80 sub add_dbentry {
81 my $self = shift;
82 my $arg = shift;
84 # if dbh not specified, return errorflag 1
85 my $table = $arg->{table};
86 if( not defined $table ) {
87 return 1 ;
88 }
90 # specify primary key in table
91 if (not exists $arg->{primkey}) {
92 return 2;
93 }
94 my $primkey = $arg->{primkey};
96 # if primkey is id, fetch max id from table and give new job id= max(id)+1
97 if ($primkey eq 'id') {
98 my $id;
99 my $sql_statement = "SELECT MAX(CAST(id AS INTEGER)) FROM $table";
100 &create_lock($self,'add_dbentry');
101 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
102 &remove_lock($self,'add_dbentry');
103 if( defined $max_id) {
104 $id = $max_id + 1;
105 } else {
106 $id = 1;
107 }
108 $arg->{id} = $id;
109 }
111 # check wether value to primary key is specified
112 if ( not exists $arg->{ $primkey } ) {
113 return 3;
114 }
116 # if timestamp is not provided, add timestamp
117 if( not exists $arg->{timestamp} ) {
118 $arg->{timestamp} = &get_time;
119 }
121 # check wether primkey is unique in table, otherwise return errorflag
122 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
123 &create_lock($self,'add_dbentry');
124 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
125 &remove_lock($self,'add_dbentry');
126 if ($res == 0) {
127 # fetch column names of table
128 my $col_names = &get_table_columns("",$table);
130 # assign values to column name variables
131 my @add_list;
132 foreach my $col_name (@{$col_names}) {
133 # use function parameter for column values
134 if (exists $arg->{$col_name}) {
135 push(@add_list, $arg->{$col_name});
136 }
137 }
139 my $sql_statement = "INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
140 print STDERR $sql_statement;
141 &create_lock($self,'add_dbentry');
142 my $db_res = $self->{dbh}->do($sql_statement);
143 &remove_lock($self,'add_dbentry');
144 if( $db_res != 1 ) {
145 return 4;
146 } else {
147 return 0;
148 }
150 } else {
151 my $update_hash = { table=>$table };
152 $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ];
153 $update_hash->{update} = [ {} ];
154 while( my ($pram, $val) = each %{$arg} ) {
155 if( $pram eq 'table' ) { next; }
156 if( $pram eq 'primkey' ) { next; }
157 $update_hash->{update}[0]->{$pram} = [$val];
158 }
159 my $db_res = &update_dbentry( $self, $update_hash );
160 if( $db_res != 1 ) {
161 return 5;
162 } else {
163 return 0;
164 }
166 }
167 }
170 # error-flags
171 # 1 no table ($table) defined
172 # 2 no restriction parameter ($restric_pram) defined
173 # 3 no restriction value ($restric_val) defined
174 # 4 column name not known in table
175 # 5 no column names to change specified
176 sub update_dbentry {
177 my $self = shift;
178 my $arg = shift;
181 # check completeness of function parameter
182 # extract table statement from arg hash
183 my $table = $arg->{table};
184 if (not defined $table) {
185 return 1;
186 } else {
187 delete $arg->{table};
188 }
190 # extract where parameter from arg hash
191 my $where_statement = "";
192 if( exists $arg->{where} ) {
193 my $where_hash = @{ $arg->{where} }[0];
194 if( 0 < keys %{ $where_hash } ) {
195 my @where_list;
196 while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
197 my $statement;
198 if( $rest_pram eq 'timestamp' ) {
199 $statement = "$rest_pram<'@{ $rest_val }[0]'";
200 } else {
201 $statement = "$rest_pram='@{ $rest_val }[0]'";
202 }
203 push( @where_list, $statement );
204 }
205 $where_statement .= "WHERE ".join('AND ', @where_list);
206 }
207 }
209 # extract update parameter from arg hash
210 my $update_hash = @{ $arg->{update} }[0];
211 my $update_statement = "";
212 if( 0 < keys %{ $update_hash } ) {
213 my @update_list;
214 while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
215 my $statement = "$rest_pram='@{ $rest_val }[0]'";
216 push( @update_list, $statement );
217 }
218 $update_statement .= join(', ', @update_list);
219 }
221 my $sql_statement = "UPDATE $table SET $update_statement $where_statement";
222 &create_lock($self,'update_dbentry');
223 my $db_answer = $self->{dbh}->do($sql_statement);
224 &remove_lock($self,'update_dbentry');
225 return $db_answer;
226 }
229 sub del_dbentry {
230 my $self = shift;
231 my $arg = shift;
234 # check completeness of function parameter
235 # extract table statement from arg hash
236 my $table = $arg->{table};
237 if (not defined $table) {
238 return 1;
239 } else {
240 delete $arg->{table};
241 }
243 # collect select statements
244 my @del_list;
245 while (my ($pram, $val) = each %{$arg}) {
246 if ( $pram eq 'timestamp' ) {
247 push(@del_list, "$pram < '$val'");
248 } else {
249 push(@del_list, "$pram = '$val'");
250 }
251 }
253 my $where_statement;
254 if( not @del_list ) {
255 $where_statement = "";
256 } else {
257 $where_statement = "WHERE ".join(' AND ', @del_list);
258 }
260 my $sql_statement = "DELETE FROM $table $where_statement";
261 &create_lock($self,'del_dbentry');
262 my $db_res = $self->{dbh}->do($sql_statement);
263 &remove_lock($self,'del_dbentry');
264 return $db_res;
265 }
268 sub get_table_columns {
269 my $self = shift;
270 my $table = shift;
271 my @column_names;
273 if(exists $col_names->{$table}) {
274 @column_names = @{$col_names->{$table}};
275 } else {
276 &create_lock($self,'get_table_columns');
277 my @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
278 &remove_lock($self,'get_table_columns');
279 foreach my $column (@res) {
280 push(@column_names, @$column[1]);
281 }
282 }
283 return \@column_names;
285 }
287 sub select_dbentry {
288 my $self = shift;
289 my $arg = shift;
292 # check completeness of function parameter
293 # extract table statement from arg hash
294 my $table = $arg->{table};
295 if (not defined $table) {
296 return 1;
297 } else {
298 delete $arg->{table};
299 }
301 # collect select statements
302 my @select_list;
303 my $sql_statement;
304 while (my ($pram, $val) = each %{$arg}) {
305 if ( $pram eq 'timestamp' ) {
306 push(@select_list, "$pram < '$val'");
307 } else {
308 push(@select_list, "$pram = '$val'");
309 }
310 }
312 if (@select_list == 0) {
313 $sql_statement = "SELECT * FROM '$table'";
314 } else {
315 $sql_statement = "SELECT * FROM '$table' WHERE ".join(' AND ', @select_list);
316 }
318 # query db
319 &create_lock($self,'select_dbentry');
320 my $query_answer = $self->{dbh}->selectall_arrayref($sql_statement);
321 &remove_lock($self,'select_dbentry');
323 # fetch column list of db and create a hash with column_name->column_value of the select query
324 my $column_list = &get_table_columns($self, $table);
325 my $list_len = @{ $column_list } ;
326 my $answer = {};
327 my $hit_counter = 0;
329 foreach my $hit ( @{ $query_answer }) {
330 $hit_counter++;
331 for ( my $i = 0; $i < $list_len; $i++) {
332 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
333 }
334 }
336 return $answer;
337 }
340 sub show_table {
341 my $self = shift;
342 my $table_name = shift;
343 &create_lock($self,'show_table');
344 my @res = @{$self->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")};
345 &remove_lock($self,'show_table');
346 my @answer;
347 foreach my $hit (@res) {
348 push(@answer, "hit: ".join(', ', @{$hit}));
349 }
350 return join("\n", @answer);
351 }
354 sub exec_statement {
355 my $self = shift;
356 my $sql_statement = shift;
357 &create_lock($self,'exec_statement');
358 my @res = @{$self->{dbh}->selectall_arrayref($sql_statement)};
359 &remove_lock($self, 'exec_statement');
360 return \@res;
361 }
363 sub get_time {
364 my ($seconds, $minutes, $hours, $monthday, $month,
365 $year, $weekday, $yearday, $sommertime) = localtime(time);
366 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
367 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
368 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
369 $month+=1;
370 $month = $month < 10 ? $month = "0".$month : $month;
371 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
372 $year+=1900;
373 return "$year$month$monthday$hours$minutes$seconds";
374 }
377 1;