1 package GOSA::DBsqlite;
3 use strict;
4 use warnings;
5 use Carp;
6 use DBI;
7 use Data::Dumper;
8 use GOSA::GosaSupportDaemon;
9 use Time::HiRes qw(usleep);
10 use Fcntl ':flock'; # import LOCK_* constants
12 my $col_names = {};
14 sub new {
15 my $class = shift;
16 my $db_name = shift;
18 my $lock = $db_name.".si.lock";
19 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
20 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1});
21 my $sth = $dbh->prepare("pragma integrity_check");
22 $sth->execute();
23 my @ret = $sth->fetchall_arrayref();
24 if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
25 &main::daemon_log("DEBUG: Database image $db_name is ok", 7);
26 } else {
27 &main::daemon_log("ERROR: Database image $db_name is malformed, creating new database.", 1);
28 $sth->finish();
29 $dbh->disconnect();
30 unlink($db_name);
31 $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1});
32 }
33 $self->{dbh} = $dbh;
34 $self->{db_name} = $db_name;
35 $self->{db_lock} = $lock;
36 bless($self,$class);
37 return($self);
38 }
41 sub lock {
42 my $self = shift;
43 open($self->{db_lock_handle}, ">>".($self->{db_lock})) unless ref $self->{db_lock_handle};
44 flock($self->{db_lock_handle},LOCK_EX);
45 seek($self->{db_lock_handle}, 0, 2);
46 }
49 sub unlock {
50 my $self = shift;
51 flock($self->{db_lock_handle},LOCK_UN);
52 }
55 sub create_table {
56 my $self = shift;
57 my $table_name = shift;
58 my $col_names_ref = shift;
59 my @col_names;
60 my @col_names_creation;
61 foreach my $col_name (@$col_names_ref) {
62 # Save full column description for creation of database
63 push(@col_names_creation, $col_name);
64 my @t = split(" ", $col_name);
65 my $column_name = $t[0];
66 # Save column name internally for select_dbentry
67 push(@col_names, $column_name);
68 }
70 $col_names->{ $table_name } = \@col_names;
71 my $col_names_string = join(", ", @col_names_creation);
72 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
73 $self->lock();
74 eval {
75 my $res = $self->{dbh}->do($sql_statement);
76 };
77 if($@) {
78 $self->{dbh}->do("ANALYZE");
79 eval {
80 my $res = $self->{dbh}->do($sql_statement);
81 };
82 if($@) {
83 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
84 }
85 }
86 $self->unlock();
88 return 0;
89 }
92 sub add_dbentry {
93 my $self = shift;
94 my $arg = shift;
95 my $res = 0; # default value
97 # if dbh not specified, return errorflag 1
98 my $table = $arg->{table};
99 if( not defined $table ) {
100 return 1 ;
101 }
103 # if timestamp is not provided, add timestamp
104 if( not exists $arg->{timestamp} ) {
105 $arg->{timestamp} = &get_time;
106 }
108 # check primkey and run insert or update
109 my $primkeys = $arg->{'primkey'};
110 my $prim_statement="";
111 if( 0 != @$primkeys ) { # more than one primkey exist in list
112 my @prim_list;
113 foreach my $primkey (@$primkeys) {
114 if( not exists $arg->{$primkey} ) {
115 return (3, "primkey '$primkey' has no value for add_dbentry");
116 }
117 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
118 }
119 $prim_statement = "WHERE ".join(" AND ", @prim_list);
121 # check wether primkey is unique in table, otherwise return errorflag
122 my $sql_statement = "SELECT * FROM $table $prim_statement";
123 $self->lock();
124 eval {
125 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
126 };
127 if($@) {
128 $self->{dbh}->do("ANALYZE");
129 eval {
130 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
131 };
132 if($@) {
133 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
134 }
135 }
136 $self->unlock();
138 }
140 # primkey is unique or no primkey specified -> run insert
141 if ($res == 0) {
142 # fetch column names of table
143 my $col_names = &get_table_columns($self, $table);
145 my $create_id=0;
146 foreach my $col_name (@{$col_names}) {
147 if($col_name eq "id" && (! exists $arg->{$col_name})) {
148 #&main::daemon_log("0 DEBUG: id field found without value! Creating autoincrement statement!", 7);
149 $create_id=1;
150 }
151 }
153 # assign values to column name variables
154 my @col_list;
155 my @val_list;
156 foreach my $col_name (@{$col_names}) {
157 # use function parameter for column values
158 if (exists $arg->{$col_name}) {
159 push(@col_list, "'".$col_name."'");
160 push(@val_list, "'".$arg->{$col_name}."'");
161 }
162 }
164 my $sql_statement;
165 if($create_id==1) {
166 $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
167 } else {
168 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
169 }
170 my $db_res;
171 $self->lock();
172 eval {
173 $db_res = $self->{dbh}->do($sql_statement);
174 };
175 if($@) {
176 $self->{dbh}->do("ANALYZE");
177 eval {
178 $db_res = $self->{dbh}->do($sql_statement);
179 };
180 if($@) {
181 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
182 }
183 }
184 $self->unlock();
186 if( $db_res != 1 ) {
187 return (4, $sql_statement);
188 }
190 # entry already exists -> run update
191 } else {
192 my @update_l;
193 while( my ($pram, $val) = each %{$arg} ) {
194 if( $pram eq 'table' ) { next; }
195 if( $pram eq 'primkey' ) { next; }
196 push(@update_l, "$pram='$val'");
197 }
198 my $update_str= join(", ", @update_l);
199 $update_str= " SET $update_str";
201 my $sql_statement= "UPDATE $table $update_str $prim_statement";
202 my $db_res = &update_dbentry($self, $sql_statement );
203 }
205 return 0;
206 }
209 sub update_dbentry {
210 my ($self, $sql)= @_;
211 my $db_answer= &exec_statement($self, $sql);
212 return $db_answer;
213 }
216 sub del_dbentry {
217 my ($self, $sql)= @_;;
218 my $db_res= &exec_statement($self, $sql);
219 return $db_res;
220 }
223 sub get_table_columns {
224 my $self = shift;
225 my $table = shift;
226 my @column_names;
228 if(exists $col_names->{$table}) {
229 @column_names = @{$col_names->{$table}};
230 } else {
231 my @res;
232 $self->lock();
233 eval {
234 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
235 };
236 if($@) {
237 $self->{dbh}->do("ANALYZE");
238 eval {
239 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
240 };
241 if($@) {
242 &main::daemon_log("ERROR: pragma table_info('$table') failed with $@", 1);
243 }
244 }
245 $self->unlock();
247 foreach my $column (@res) {
248 push(@column_names, @$column[1]);
249 }
250 }
251 return \@column_names;
253 }
256 sub select_dbentry {
257 my ($self, $sql)= @_;
258 my $error= 0;
259 my $answer= {};
260 my $db_answer= &exec_statement($self, $sql);
261 my @column_list;
263 # fetch column list of db and create a hash with column_name->column_value of the select query
264 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
265 my $selected_cols = $1;
266 my $table = $2;
268 # all columns are used for creating answer
269 if ($selected_cols eq '*') {
270 @column_list = @{ &get_table_columns($self, $table) };
272 # specific columns are used for creating answer
273 } else {
274 # remove all blanks and split string to list of column names
275 $selected_cols =~ s/ //g;
276 @column_list = split(/,/, $selected_cols);
277 }
279 # create answer
280 my $hit_counter = 0;
281 my $list_len = @column_list;
282 foreach my $hit ( @{$db_answer} ){
283 $hit_counter++;
284 for ( my $i = 0; $i < $list_len; $i++) {
285 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
286 }
287 }
289 return $answer;
290 }
293 sub show_table {
294 my $self = shift;
295 my $table_name = shift;
297 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
298 my $res= &exec_statement($self, $sql_statement);
299 my @answer;
300 foreach my $hit (@{$res}) {
301 push(@answer, "hit: ".join(', ', @{$hit}));
302 }
304 return join("\n", @answer);
305 }
308 sub exec_statement {
309 my $self = shift;
310 my $sql_statement = shift;
311 my @db_answer;
313 $self->lock();
314 eval {
315 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
316 };
317 if($@) {
318 $self->{dbh}->do("ANALYZE");
319 eval {
320 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
321 };
322 if($@) {
323 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
324 }
325 }
326 $self->unlock();
327 # TODO : maybe an error handling and an erro feedback to invoking function
328 #my $error = @$self->{dbh}->err;
329 #if ($error) {
330 # my $error_string = @$self->{dbh}->errstr;
331 #}
333 return \@db_answer;
334 }
337 sub exec_statementlist {
338 my $self = shift;
339 my $sql_list = shift;
340 my @db_answer;
342 foreach my $sql (@$sql_list) {
343 if(defined($sql) && length($sql) > 0) {
344 # Obtain a new lock for each statement to not block the db for a too long time
345 $self->lock();
346 eval {
347 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
348 push @db_answer, @answer;
349 };
350 if($@) {
351 $self->{dbh}->do("ANALYZE");
352 eval {
353 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
354 push @db_answer, @answer;
355 };
356 if($@) {
357 &main::daemon_log("ERROR: $sql failed with $@", 1);
358 }
359 }
360 $self->unlock();
361 } else {
362 next;
363 }
364 }
366 return \@db_answer;
367 }
370 sub count_dbentries {
371 my ($self, $table)= @_;
372 my $error= 0;
373 my $answer= -1;
375 my $sql_statement= "SELECT * FROM $table";
376 my $db_answer= &select_dbentry($self, $sql_statement);
378 my $count = keys(%{$db_answer});
379 return $count;
380 }
383 sub move_table {
384 my ($self, $from, $to) = @_;
386 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
387 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
389 $self->lock();
390 eval {
391 $self->{dbh}->do($sql_statement_drop);
392 };
393 if($@) {
394 $self->{dbh}->do("ANALYZE");
395 eval {
396 $self->{dbh}->do($sql_statement_drop);
397 };
398 if($@) {
399 &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1);
400 }
401 }
403 eval {
404 $self->{dbh}->do($sql_statement_alter);
405 };
406 if($@) {
407 $self->{dbh}->do("ANALYZE");
408 eval {
409 $self->{dbh}->do($sql_statement_alter);
410 };
411 if($@) {
412 &main::daemon_log("ERROR: $sql_statement_alter failed with $@", 1);
413 }
414 }
415 $self->unlock();
417 return;
418 }
421 1;