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 $self->{dbh} = $dbh;
22 $self->{db_name} = $db_name;
23 $self->{db_lock} = $lock;
24 bless($self,$class);
25 return($self);
26 }
29 sub lock {
30 my $self = shift;
31 open($self->{db_lock_handle}, ">>".($self->{db_lock})) unless ref $self->{db_lock_handle};
32 flock($self->{db_lock_handle},LOCK_EX);
33 seek($self->{db_lock_handle}, 0, 2);
34 }
37 sub unlock {
38 my $self = shift;
39 flock($self->{db_lock_handle},LOCK_UN);
40 }
43 sub create_table {
44 my $self = shift;
45 my $table_name = shift;
46 my $col_names_ref = shift;
47 my @col_names;
48 foreach my $col_name (@$col_names_ref) {
49 my @t = split(" ", $col_name);
50 $col_name = $t[0];
51 push(@col_names, $col_name);
52 }
54 $col_names->{ $table_name } = $col_names_ref;
55 my $col_names_string = join("', '", @col_names);
56 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( '$col_names_string' )";
57 $self->lock();
58 eval {
59 my $res = $self->{dbh}->do($sql_statement);
60 };
61 if($@) {
62 $self->{dbh}->do("ANALYZE");
63 eval {
64 my $res = $self->{dbh}->do($sql_statement);
65 };
66 if($@) {
67 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
68 }
69 }
70 $self->unlock();
72 return 0;
73 }
76 sub add_dbentry {
77 my $self = shift;
78 my $arg = shift;
79 my $res = 0; # default value
81 # if dbh not specified, return errorflag 1
82 my $table = $arg->{table};
83 if( not defined $table ) {
84 return 1 ;
85 }
87 # if timestamp is not provided, add timestamp
88 if( not exists $arg->{timestamp} ) {
89 $arg->{timestamp} = &get_time;
90 }
92 # check primkey and run insert or update
93 my $primkeys = $arg->{'primkey'};
94 my $prim_statement="";
95 if( 0 != @$primkeys ) { # more than one primkey exist in list
96 my @prim_list;
97 foreach my $primkey (@$primkeys) {
98 if( not exists $arg->{$primkey} ) {
99 return (3, "primkey '$primkey' has no value for add_dbentry");
100 }
101 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
102 }
103 $prim_statement = "WHERE ".join(" AND ", @prim_list);
105 # check wether primkey is unique in table, otherwise return errorflag
106 my $sql_statement = "SELECT * FROM $table $prim_statement";
107 $self->lock();
108 eval {
109 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
110 };
111 if($@) {
112 $self->{dbh}->do("ANALYZE");
113 eval {
114 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
115 };
116 if($@) {
117 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
118 }
119 }
120 $self->unlock();
122 }
124 # primkey is unique or no primkey specified -> run insert
125 if ($res == 0) {
126 # fetch column names of table
127 my $col_names = &get_table_columns($self, $table);
129 my $create_id=0;
130 foreach my $col_name (@{$col_names}) {
131 if($col_name eq "id" && (! exists $arg->{$col_name})) {
132 #&main::daemon_log("0 DEBUG: id field found without value! Creating autoincrement statement!", 7);
133 $create_id=1;
134 }
135 }
137 # assign values to column name variables
138 my @col_list;
139 my @val_list;
140 foreach my $col_name (@{$col_names}) {
141 # use function parameter for column values
142 if (exists $arg->{$col_name}) {
143 push(@col_list, "'".$col_name."'");
144 push(@val_list, "'".$arg->{$col_name}."'");
145 }
146 }
148 my $sql_statement;
149 if($create_id==1) {
150 $sql_statement = "INSERT INTO $table ('id', ".join(", ", @col_list).") VALUES ((select coalesce(max(id), 0)+1 from $table), ".join(", ", @val_list).")";
151 } else {
152 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
153 }
154 my $db_res;
155 $self->lock();
156 eval {
157 $db_res = $self->{dbh}->do($sql_statement);
158 };
159 if($@) {
160 $self->{dbh}->do("ANALYZE");
161 eval {
162 $db_res = $self->{dbh}->do($sql_statement);
163 };
164 if($@) {
165 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
166 }
167 }
168 $self->unlock();
170 if( $db_res != 1 ) {
171 return (4, $sql_statement);
172 }
174 # entry already exists -> run update
175 } else {
176 my @update_l;
177 while( my ($pram, $val) = each %{$arg} ) {
178 if( $pram eq 'table' ) { next; }
179 if( $pram eq 'primkey' ) { next; }
180 push(@update_l, "$pram='$val'");
181 }
182 my $update_str= join(", ", @update_l);
183 $update_str= " SET $update_str";
185 my $sql_statement= "UPDATE $table $update_str $prim_statement";
186 my $db_res = &update_dbentry($self, $sql_statement );
187 }
189 return 0;
190 }
193 sub update_dbentry {
194 my ($self, $sql)= @_;
195 my $db_answer= &exec_statement($self, $sql);
196 return $db_answer;
197 }
200 sub del_dbentry {
201 my ($self, $sql)= @_;;
202 my $db_res= &exec_statement($self, $sql);
203 return $db_res;
204 }
207 sub get_table_columns {
208 my $self = shift;
209 my $table = shift;
210 my @column_names;
212 if(exists $col_names->{$table}) {
213 @column_names = @{$col_names->{$table}};
214 } else {
215 my @res;
216 $self->lock();
217 eval {
218 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
219 };
220 if($@) {
221 $self->{dbh}->do("ANALYZE");
222 eval {
223 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
224 };
225 if($@) {
226 &main::daemon_log("ERROR: pragma table_info('$table') failed with $@", 1);
227 }
228 }
229 $self->unlock();
231 foreach my $column (@res) {
232 push(@column_names, @$column[1]);
233 }
234 }
235 return \@column_names;
237 }
240 sub select_dbentry {
241 my ($self, $sql)= @_;
242 my $error= 0;
243 my $answer= {};
244 my $db_answer= &exec_statement($self, $sql);
245 my @column_list;
247 # fetch column list of db and create a hash with column_name->column_value of the select query
248 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
249 my $selected_cols = $1;
250 my $table = $2;
252 # all columns are used for creating answer
253 if ($selected_cols eq '*') {
254 @column_list = @{ &get_table_columns($self, $table) };
256 # specific columns are used for creating answer
257 } else {
258 # remove all blanks and split string to list of column names
259 $selected_cols =~ s/ //g;
260 @column_list = split(/,/, $selected_cols);
261 }
263 # create answer
264 my $hit_counter = 0;
265 my $list_len = @column_list;
266 foreach my $hit ( @{$db_answer} ){
267 $hit_counter++;
268 for ( my $i = 0; $i < $list_len; $i++) {
269 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
270 }
271 }
273 return $answer;
274 }
277 sub show_table {
278 my $self = shift;
279 my $table_name = shift;
281 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
282 my $res= &exec_statement($self, $sql_statement);
283 my @answer;
284 foreach my $hit (@{$res}) {
285 push(@answer, "hit: ".join(', ', @{$hit}));
286 }
288 return join("\n", @answer);
289 }
292 sub exec_statement {
293 my $self = shift;
294 my $sql_statement = shift;
295 my @db_answer;
297 $self->lock();
298 eval {
299 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
300 };
301 if($@) {
302 $self->{dbh}->do("ANALYZE");
303 eval {
304 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
305 };
306 if($@) {
307 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
308 }
309 }
310 $self->unlock();
311 # TODO : maybe an error handling and an erro feedback to invoking function
312 #my $error = @$self->{dbh}->err;
313 #if ($error) {
314 # my $error_string = @$self->{dbh}->errstr;
315 #}
317 return \@db_answer;
318 }
321 sub exec_statementlist {
322 my $self = shift;
323 my $sql_list = shift;
324 my @db_answer;
326 foreach my $sql (@$sql_list) {
327 if(defined($sql) && length($sql) > 0) {
328 # Obtain a new lock for each statement to not block the db for a too long time
329 $self->lock();
330 eval {
331 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
332 push @db_answer, @answer;
333 };
334 if($@) {
335 $self->{dbh}->do("ANALYZE");
336 eval {
337 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
338 push @db_answer, @answer;
339 };
340 if($@) {
341 &main::daemon_log("ERROR: $sql failed with $@", 1);
342 }
343 }
344 $self->unlock();
345 } else {
346 next;
347 }
348 }
350 return \@db_answer;
351 }
354 sub count_dbentries {
355 my ($self, $table)= @_;
356 my $error= 0;
357 my $answer= -1;
359 my $sql_statement= "SELECT * FROM $table";
360 my $db_answer= &select_dbentry($self, $sql_statement);
362 my $count = keys(%{$db_answer});
363 return $count;
364 }
367 sub move_table {
368 my ($self, $from, $to) = @_;
370 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
371 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
373 eval {
374 $self->{dbh}->do($sql_statement_drop);
375 };
376 if($@) {
377 $self->{dbh}->do("ANALYZE");
378 eval {
379 $self->{dbh}->do($sql_statement_drop);
380 };
381 if($@) {
382 &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1);
383 }
384 }
386 eval {
387 $self->{dbh}->do($sql_statement_alter);
388 };
389 if($@) {
390 $self->{dbh}->do("ANALYZE");
391 eval {
392 $self->{dbh}->do($sql_statement_alter);
393 };
394 if($@) {
395 &main::daemon_log("ERROR: $sql_statement_alter failed with $@", 1);
396 }
397 }
399 return;
400 }
403 1;