618dd00c7869a624b9ea9a955ed835afbb068e26
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 while(&lock_exists($self,'create_table')) {
72 print STDERR "Lock in create_table\n";
73 }
74 &create_lock($self,'create_table');
75 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
76 $self->{dbh}->do($sql_statement);
77 &remove_lock($self,'create_table');
78 return 0;
79 }
83 sub add_dbentry {
85 my $self = shift;
86 my $arg = shift;
88 while(&lock_exists($self,'add_dbentry')) {
89 print STDERR "Lock in add_dbentry\n";
90 }
91 &create_lock($self,'add_dbentry');
92 # if dbh not specified, return errorflag 1
93 my $table = $arg->{table};
94 if (not defined $table) {
95 &remove_lock($self,'add_dbentry');
96 return 1;
97 }
99 # specify primary key in table
100 if (not exists $arg->{primkey}) {
101 &remove_lock($self,'add_dbentry');
102 return 2;
103 }
104 my $primkey = $arg->{primkey};
106 # if primkey is id, fetch max id from table and give new job id= max(id)+1
107 if ($primkey eq 'id') {
108 my $id;
109 my $sql_statement = "SELECT MAX(id) FROM $table";
110 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
111 if( defined $max_id) {
112 $id = $max_id + 1;
113 } else {
114 $id = 1;
115 }
116 $arg->{id} = $id;
117 }
119 # check wether value to primary key is specified
120 if ( not exists $arg->{ $primkey } ) {
121 &remove_lock($self,'add_dbentry');
122 return 3;
123 }
125 # if timestamp is not provided, add timestamp
126 if( not exists $arg->{timestamp} ) {
127 $arg->{timestamp} = &get_time;
128 }
130 # check wether primkey is unique in table, otherwise return errorflag 3
131 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
132 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
133 if ($res == 0) {
134 # fetch column names of table
135 my $col_names = &get_table_columns("",$table);
137 # assign values to column name variables
138 my @add_list;
139 foreach my $col_name (@{$col_names}) {
140 # use function parameter for column values
141 if (exists $arg->{$col_name}) {
142 push(@add_list, $arg->{$col_name});
143 }
144 }
146 my $sql_statement = "BEGIN TRANSACTION; INSERT INTO $table VALUES ('".join("', '", @add_list)."'); COMMIT;";
147 my $db_res = $self->{dbh}->do($sql_statement);
148 if( $db_res != 1 ) {
149 &remove_lock($self,'add_dbentry');
150 return 1;
151 } else {
152 &remove_lock($self,'add_dbentry');
153 return 0;
154 }
156 } else {
157 my $update_hash = { table=>$table };
158 $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ];
159 $update_hash->{update} = [ {} ];
160 while( my ($pram, $val) = each %{$arg} ) {
161 if( $pram eq 'table' ) { next; }
162 if( $pram eq 'primkey' ) { next; }
163 $update_hash->{update}[0]->{$pram} = [$val];
164 }
165 my $db_res = &update_dbentry( $self, $update_hash );
166 if( $db_res != 1 ) {
167 &remove_lock($self,'add_dbentry');
168 return 1;
169 } else {
170 &remove_lock($self,'add_dbentry');
171 return 0;
172 }
174 }
175 &remove_lock($self,'add_dbentry');
176 }
179 # error-flags
180 # 1 no table ($table) defined
181 # 2 no restriction parameter ($restric_pram) defined
182 # 3 no restriction value ($restric_val) defined
183 # 4 column name not known in table
184 # 5 no column names to change specified
185 sub update_dbentry {
186 my $self = shift;
187 my $arg = shift;
189 while(&lock_exists($self,'update_dbentry')) {
190 print STDERR "Lock in update_dbentry\n";
191 }
192 &create_lock($self,'update_dbentry');
193 # check completeness of function parameter
194 # extract table statement from arg hash
195 my $table = $arg->{table};
196 if (not defined $table) {
197 &remove_lock($self,'update_dbentry');
198 return 1;
199 } else {
200 delete $arg->{table};
201 }
203 # extract where parameter from arg hash
204 my $where_statement = "";
205 if( exists $arg->{where} ) {
206 my $where_hash = @{ $arg->{where} }[0];
207 if( 0 < keys %{ $where_hash } ) {
208 my @where_list;
209 while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
210 my $statement;
211 if( $rest_pram eq 'timestamp' ) {
212 $statement = "$rest_pram<'@{ $rest_val }[0]'";
213 } else {
214 $statement = "$rest_pram='@{ $rest_val }[0]'";
215 }
216 push( @where_list, $statement );
217 }
218 $where_statement .= "WHERE ".join('AND ', @where_list);
219 }
220 }
222 # extract update parameter from arg hash
223 my $update_hash = @{ $arg->{update} }[0];
224 my $update_statement = "";
225 if( 0 < keys %{ $update_hash } ) {
226 my @update_list;
227 while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
228 my $statement = "$rest_pram='@{ $rest_val }[0]'";
229 push( @update_list, $statement );
230 }
231 $update_statement .= join(', ', @update_list);
232 }
234 my $sql_statement = "BEGIN TRANSACTION; UPDATE $table SET $update_statement $where_statement; COMMIT;";
235 my $db_answer = $self->{dbh}->do($sql_statement);
236 &remove_lock($self,'update_dbentry');
237 return $db_answer;
238 }
241 sub del_dbentry {
242 my $self = shift;
243 my $arg = shift;
245 while(&lock_exists($self,'del_dbentry')) {
246 print STDERR "Lock in del_dbentry\n";
247 }
248 &create_lock($self,'del_dbentry');
249 # check completeness of function parameter
250 # extract table statement from arg hash
251 my $table = $arg->{table};
252 if (not defined $table) {
253 &remove_lock($self,'del_dbentry');
254 return 1;
255 } else {
256 delete $arg->{table};
257 }
259 # collect select statements
260 my @del_list;
261 while (my ($pram, $val) = each %{$arg}) {
262 if ( $pram eq 'timestamp' ) {
263 push(@del_list, "$pram < '$val'");
264 } else {
265 push(@del_list, "$pram = '$val'");
266 }
267 }
269 my $where_statement;
270 if( not @del_list ) {
271 $where_statement = "";
272 } else {
273 $where_statement = "WHERE ".join(' AND ', @del_list);
274 }
276 my $sql_statement = "BEGIN TRANSACTION; DELETE FROM $table $where_statement; COMMIT;";
277 my $db_res = $self->{dbh}->do($sql_statement);
279 &remove_lock($self,'del_dbentry');
280 return $db_res;
281 }
284 sub get_table_columns {
285 my $self = shift;
286 my $table = shift;
288 my @column_names = @{$col_names->{$table}};
289 return \@column_names;
291 }
293 sub select_dbentry {
294 my $self = shift;
295 my $arg = shift;
297 while(&lock_exists($self,'select_dbentry')) {
298 print STDERR "Lock in select_dbentry\n";
299 }
300 &create_lock($self,'select_dbentry');
302 # check completeness of function parameter
303 # extract table statement from arg hash
304 my $table = $arg->{table};
305 if (not defined $table) {
306 &remove_lock($self,'select_dbentry');
307 return 1;
308 } else {
309 delete $arg->{table};
310 }
312 # collect select statements
313 my @select_list;
314 my $sql_statement;
315 while (my ($pram, $val) = each %{$arg}) {
316 if ( $pram eq 'timestamp' ) {
317 push(@select_list, "$pram < '$val'");
318 } else {
319 push(@select_list, "$pram = '$val'");
320 }
321 }
323 if (@select_list == 0) {
324 $sql_statement = "SELECT ROWID, * FROM '$table'";
325 } else {
326 $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list);
327 }
329 # query db
330 my $query_answer = $self->{dbh}->selectall_arrayref($sql_statement);
332 # fetch column list of db and create a hash with column_name->column_value of the select query
333 my $column_list = &get_table_columns($self, $table);
334 my $list_len = @{ $column_list } ;
335 my $answer = {};
336 my $hit_counter = 0;
339 foreach my $hit ( @{ $query_answer }) {
340 $hit_counter++;
341 $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit };
342 for ( my $i = 0; $i < $list_len; $i++) {
343 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
344 }
345 }
347 &remove_lock($self,'select_dbentry');
348 return $answer;
349 }
352 sub show_table {
353 my $self = shift;
354 my $table_name = shift;
355 while(&lock_exists($self,'show_table')) {
356 print STDERR "Lock in show_table\n";
357 }
358 &create_lock($self,'show_table');
359 my @res = @{$self->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")};
360 my @answer;
361 foreach my $hit (@res) {
362 push(@answer, "hit: ".join(', ', @{$hit}));
363 }
364 &remove_lock($self,'show_table');
365 return join("\n", @answer);
366 }
369 sub exec_statement {
370 my $self = shift;
371 my $sql_statement = shift;
372 while(&lock_exists($self,'exec_statement')) {
373 print STDERR "Lock in exec_statement\n";
374 }
375 &create_lock($self,'exec_statement');
376 my @res = @{$self->{dbh}->selectall_arrayref($sql_statement)};
377 &remove_locK;
378 return \@res;
379 }
381 sub get_time {
382 my ($seconds, $minutes, $hours, $monthday, $month,
383 $year, $weekday, $yearday, $sommertime) = localtime(time);
384 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
385 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
386 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
387 $month+=1;
388 $month = $month < 10 ? $month = "0".$month : $month;
389 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
390 $year+=1900;
391 return "$year$month$monthday$hours$minutes$seconds";
392 }
395 1;