1 package GOSA::DBsqlite;
4 use strict;
5 use warnings;
6 use DBI;
7 use Data::Dumper;
9 my $col_names = {};
10 my $checking=0;
12 sub new {
13 my $class = shift;
14 my $db_name = shift;
16 my $lock='/tmp/gosa_si_lock';
17 my $_lock = $db_name;
18 $_lock =~ tr/\//_/;
19 $lock.=$_lock;
20 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
21 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name");
22 $self->{dbh} = $dbh;
23 $self->{db_name} = $db_name;
24 $self->{db_lock} = $lock;
25 bless($self,$class);
27 return($self);
28 }
30 sub lock_exists {
31 while($checking) {
32 sleep 1;
33 }
34 $checking=1;
35 my $self=shift;
36 my $funcname=shift;
37 my $lock = $self->{db_lock};
38 my $result=(-f $lock);
39 if($result) {
40 print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n";
41 sleep 2;
42 }
43 $checking=0;
44 return $result;
45 }
47 sub create_lock {
48 my $self=shift;
49 my $funcname=shift;
50 print STDERR "(".((defined $funcname)?$funcname:"").") Erzeuge Lock (PID ".$$.") ".($self->{db_lock})."\n";
51 open($self->{db_lock_handle},'>',$self->{db_lock});
52 }
54 sub remove_lock {
55 my $self=shift;
56 my $funcname=shift;
57 print STDERR "(".((defined $funcname)?$funcname:"").") Entferne Lock (PID ".$$.") ".$self->{db_lock}."\n";
58 close($self->{db_lock_handle});
59 unlink($self->{db_lock});
60 }
62 sub create_table {
63 my $self = shift;
64 my $table_name = shift;
65 my $col_names_ref = shift;
66 $col_names->{ $table_name } = $col_names_ref;
67 my $col_names_string = join(', ', @{$col_names_ref});
68 while(&lock_exists($self,'create_table')) {
69 print STDERR "Lock in create_table\n";
70 }
71 &create_lock($self,'create_table');
72 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
73 $self->{dbh}->do($sql_statement);
74 &remove_lock($self,'create_table');
75 return 0;
76 }
80 sub add_dbentry {
82 my $self = shift;
83 my $arg = shift;
85 while(&lock_exists($self,'add_dbentry')) {
86 print STDERR "Lock in add_dbentry\n";
87 }
88 &create_lock($self,'add_dbentry');
89 # if dbh not specified, return errorflag 1
90 my $table = $arg->{table};
91 if (not defined $table) {
92 &remove_lock($self,'add_dbentry');
93 return 1;
94 }
96 # specify primary key in table
97 if (not exists $arg->{primkey}) {
98 &remove_lock($self,'add_dbentry');
99 return 2;
100 }
101 my $primkey = $arg->{primkey};
103 # if primkey is id, fetch max id from table and give new job id= max(id)+1
104 if ($primkey eq 'id') {
105 my $id;
106 my $sql_statement = "SELECT MAX(id) FROM $table";
107 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
108 if( defined $max_id) {
109 $id = $max_id + 1;
110 } else {
111 $id = 1;
112 }
113 $arg->{id} = $id;
114 }
116 # check wether value to primary key is specified
117 if ( not exists $arg->{ $primkey } ) {
118 &remove_lock($self,'add_dbentry');
119 return 3;
120 }
122 # if timestamp is not provided, add timestamp
123 if( not exists $arg->{timestamp} ) {
124 $arg->{timestamp} = &get_time;
125 }
127 # check wether primkey is unique in table, otherwise return errorflag 3
128 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
129 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
130 if ($res == 0) {
131 # fetch column names of table
132 my $col_names = &get_table_columns("",$table);
134 # assign values to column name variables
135 my @add_list;
136 foreach my $col_name (@{$col_names}) {
137 # use function parameter for column values
138 if (exists $arg->{$col_name}) {
139 push(@add_list, $arg->{$col_name});
140 }
141 }
143 my $sql_statement = "BEGIN TRANSACTION; INSERT INTO $table VALUES ('".join("', '", @add_list)."'); COMMIT;";
144 my $db_res = $self->{dbh}->do($sql_statement);
145 if( $db_res != 1 ) {
146 &remove_lock($self,'add_dbentry');
147 return 1;
148 } else {
149 &remove_lock($self,'add_dbentry');
150 return 0;
151 }
153 } else {
154 my $update_hash = { table=>$table };
155 $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ];
156 $update_hash->{update} = [ {} ];
157 while( my ($pram, $val) = each %{$arg} ) {
158 if( $pram eq 'table' ) { next; }
159 if( $pram eq 'primkey' ) { next; }
160 $update_hash->{update}[0]->{$pram} = [$val];
161 }
162 my $db_res = &update_dbentry( $self, $update_hash );
163 if( $db_res != 1 ) {
164 &remove_lock($self,'add_dbentry');
165 return 1;
166 } else {
167 &remove_lock($self,'add_dbentry');
168 return 0;
169 }
171 }
172 &remove_lock($self,'add_dbentry');
173 }
176 # error-flags
177 # 1 no table ($table) defined
178 # 2 no restriction parameter ($restric_pram) defined
179 # 3 no restriction value ($restric_val) defined
180 # 4 column name not known in table
181 # 5 no column names to change specified
182 sub update_dbentry {
183 my $self = shift;
184 my $arg = shift;
186 while(&lock_exists($self,'update_dbentry')) {
187 print STDERR "Lock in update_dbentry\n";
188 }
189 &create_lock($self,'update_dbentry');
190 # check completeness of function parameter
191 # extract table statement from arg hash
192 my $table = $arg->{table};
193 if (not defined $table) {
194 &remove_lock($self,'update_dbentry');
195 return 1;
196 } else {
197 delete $arg->{table};
198 }
200 # extract where parameter from arg hash
201 my $where_statement = "";
202 if( exists $arg->{where} ) {
203 my $where_hash = @{ $arg->{where} }[0];
204 if( 0 < keys %{ $where_hash } ) {
205 my @where_list;
206 while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
207 my $statement;
208 if( $rest_pram eq 'timestamp' ) {
209 $statement = "$rest_pram<'@{ $rest_val }[0]'";
210 } else {
211 $statement = "$rest_pram='@{ $rest_val }[0]'";
212 }
213 push( @where_list, $statement );
214 }
215 $where_statement .= "WHERE ".join('AND ', @where_list);
216 }
217 }
219 # extract update parameter from arg hash
220 my $update_hash = @{ $arg->{update} }[0];
221 my $update_statement = "";
222 if( 0 < keys %{ $update_hash } ) {
223 my @update_list;
224 while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
225 my $statement = "$rest_pram='@{ $rest_val }[0]'";
226 push( @update_list, $statement );
227 }
228 $update_statement .= join(', ', @update_list);
229 }
231 my $sql_statement = "BEGIN TRANSACTION; UPDATE $table SET $update_statement $where_statement; COMMIT;";
232 my $db_answer = $self->{dbh}->do($sql_statement);
233 &remove_lock($self,'update_dbentry');
234 return $db_answer;
235 }
238 sub del_dbentry {
239 my $self = shift;
240 my $arg = shift;
242 while(&lock_exists($self,'del_dbentry')) {
243 print STDERR "Lock in del_dbentry\n";
244 }
245 &create_lock($self,'del_dbentry');
246 # check completeness of function parameter
247 # extract table statement from arg hash
248 my $table = $arg->{table};
249 if (not defined $table) {
250 &remove_lock($self,'del_dbentry');
251 return 1;
252 } else {
253 delete $arg->{table};
254 }
256 # collect select statements
257 my @del_list;
258 while (my ($pram, $val) = each %{$arg}) {
259 if ( $pram eq 'timestamp' ) {
260 push(@del_list, "$pram < '$val'");
261 } else {
262 push(@del_list, "$pram = '$val'");
263 }
264 }
266 my $where_statement;
267 if( not @del_list ) {
268 $where_statement = "";
269 } else {
270 $where_statement = "WHERE ".join(' AND ', @del_list);
271 }
273 my $sql_statement = "BEGIN TRANSACTION; DELETE FROM $table $where_statement; COMMIT;";
274 my $db_res = $self->{dbh}->do($sql_statement);
276 &remove_lock($self,'del_dbentry');
277 return $db_res;
278 }
281 sub get_table_columns {
282 my $self = shift;
283 my $table = shift;
285 my @column_names = @{$col_names->{$table}};
286 return \@column_names;
288 }
290 sub select_dbentry {
291 my $self = shift;
292 my $arg = shift;
294 while(&lock_exists($self,'select_dbentry')) {
295 print STDERR "Lock in select_dbentry\n";
296 }
297 &create_lock($self,'select_dbentry');
299 # check completeness of function parameter
300 # extract table statement from arg hash
301 my $table = $arg->{table};
302 if (not defined $table) {
303 &remove_lock($self,'select_dbentry');
304 return 1;
305 } else {
306 delete $arg->{table};
307 }
309 # collect select statements
310 my @select_list;
311 my $sql_statement;
312 while (my ($pram, $val) = each %{$arg}) {
313 if ( $pram eq 'timestamp' ) {
314 push(@select_list, "$pram < '$val'");
315 } else {
316 push(@select_list, "$pram = '$val'");
317 }
318 }
320 if (@select_list == 0) {
321 $sql_statement = "SELECT ROWID, * FROM '$table'";
322 } else {
323 $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list);
324 }
326 # query db
327 my $query_answer = $self->{dbh}->selectall_arrayref($sql_statement);
329 # fetch column list of db and create a hash with column_name->column_value of the select query
330 my $column_list = &get_table_columns($self, $table);
331 my $list_len = @{ $column_list } ;
332 my $answer = {};
333 my $hit_counter = 0;
336 foreach my $hit ( @{ $query_answer }) {
337 $hit_counter++;
338 $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit };
339 for ( my $i = 0; $i < $list_len; $i++) {
340 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
341 }
342 }
344 &remove_lock($self,'select_dbentry');
345 return $answer;
346 }
349 sub show_table {
350 my $self = shift;
351 my $table_name = shift;
352 while(&lock_exists($self,'show_table')) {
353 print STDERR "Lock in show_table\n";
354 }
355 &create_lock($self,'show_table');
356 my @res = @{$self->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")};
357 my @answer;
358 foreach my $hit (@res) {
359 push(@answer, "hit: ".join(', ', @{$hit}));
360 }
361 &remove_lock($self,'show_table');
362 return join("\n", @answer);
363 }
366 sub exec_statement {
367 my $self = shift;
368 my $sql_statement = shift;
369 while(&lock_exists($self,'exec_statement')) {
370 print STDERR "Lock in exec_statement\n";
371 }
372 &create_lock($self,'exec_statement');
373 my @res = @{$self->{dbh}->selectall_arrayref($sql_statement)};
374 &remove_locK;
375 return \@res;
376 }
378 sub get_time {
379 my ($seconds, $minutes, $hours, $monthday, $month,
380 $year, $weekday, $yearday, $sommertime) = localtime(time);
381 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
382 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
383 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
384 $month+=1;
385 $month = $month < 10 ? $month = "0".$month : $month;
386 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
387 $year+=1900;
388 return "$year$month$monthday$hours$minutes$seconds";
389 }
392 1;