1 package GOSA::DBsqlite;
4 use strict;
5 use warnings;
6 use DBI;
7 use Data::Dumper;
8 use GOSA::GosaSupportDaemon;
9 use Time::HiRes qw(usleep);
12 my $col_names = {};
14 sub new {
15 my $class = shift;
16 my $db_name = shift;
18 my $lock = $db_name.".si.lock";
19 # delete existing lock - instance should be running only once
20 if(stat($lock)) {
21 &main::daemon_log("DEBUG: Removed existing lock $lock.", 7);
22 unlink($lock);
23 }
24 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
25 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1});
26 $self->{dbh} = $dbh;
27 $self->{db_name} = $db_name;
28 $self->{db_lock} = $lock;
30 bless($self,$class);
31 return($self);
32 }
35 sub create_table {
36 my $self = shift;
37 my $table_name = shift;
38 my $col_names_ref = shift;
39 my @col_names;
40 foreach my $col_name (@$col_names_ref) {
41 my @t = split(" ", $col_name);
42 $col_name = $t[0];
43 push(@col_names, $col_name);
44 }
46 $col_names->{ $table_name } = $col_names_ref;
47 my $col_names_string = join("', '", @col_names);
48 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( '$col_names_string' )";
49 eval {
50 my $res = $self->{dbh}->do($sql_statement);
51 };
52 if($@) {
53 $self->{dbh}->do("ANALYZE");
54 }
55 eval {
56 my $res = $self->{dbh}->do($sql_statement);
57 };
58 if($@) {
59 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
60 }
62 return 0;
63 }
66 sub add_dbentry {
67 my $self = shift;
68 my $arg = shift;
69 my $res = 0; # default value
71 # if dbh not specified, return errorflag 1
72 my $table = $arg->{table};
73 if( not defined $table ) {
74 return 1 ;
75 }
77 # if timestamp is not provided, add timestamp
78 if( not exists $arg->{timestamp} ) {
79 $arg->{timestamp} = &get_time;
80 }
82 # check primkey and run insert or update
83 my $primkeys = $arg->{'primkey'};
84 my $prim_statement="";
85 if( 0 != @$primkeys ) { # more than one primkey exist in list
86 my @prim_list;
87 foreach my $primkey (@$primkeys) {
88 if( not exists $arg->{$primkey} ) {
89 return (3, "primkey '$primkey' has no value for add_dbentry");
90 }
91 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
92 }
93 $prim_statement = "WHERE ".join(" AND ", @prim_list);
95 # check wether primkey is unique in table, otherwise return errorflag
96 my $sql_statement = "SELECT * FROM $table $prim_statement";
97 eval {
98 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
99 };
100 if($@) {
101 $self->{dbh}->do("ANALYZE");
102 eval {
103 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
104 };
105 if($@) {
106 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
107 }
108 }
110 }
112 # primkey is unique or no primkey specified -> run insert
113 if ($res == 0) {
114 # fetch column names of table
115 my $col_names = &get_table_columns($self, $table);
117 my $create_id=0;
118 foreach my $col_name (@{$col_names}) {
119 if($col_name eq "id" && (! exists $arg->{$col_name})) {
120 #&main::daemon_log("0 DEBUG: id field found without value! Creating autoincrement statement!", 7);
121 $create_id=1;
122 }
123 }
125 # assign values to column name variables
126 my @col_list;
127 my @val_list;
128 foreach my $col_name (@{$col_names}) {
129 # use function parameter for column values
130 if (exists $arg->{$col_name}) {
131 push(@col_list, "'".$col_name."'");
132 push(@val_list, "'".$arg->{$col_name}."'");
133 }
134 }
136 my $sql_statement;
137 if($create_id==1) {
138 $sql_statement = "INSERT INTO $table ('id', ".join(", ", @col_list).") VALUES ((select coalesce(max(id), 0)+1 from $table), ".join(", ", @val_list).")";
139 } else {
140 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
141 }
142 my $db_res;
143 eval {
144 $db_res = $self->{dbh}->do($sql_statement);
145 };
146 if($@) {
147 $self->{dbh}->do("ANALYZE");
148 eval {
149 $db_res = $self->{dbh}->do($sql_statement);
150 };
151 if($@) {
152 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
153 }
154 }
156 if( $db_res != 1 ) {
157 return (4, $sql_statement);
158 }
160 # entry already exists -> run update
161 } else {
162 my @update_l;
163 while( my ($pram, $val) = each %{$arg} ) {
164 if( $pram eq 'table' ) { next; }
165 if( $pram eq 'primkey' ) { next; }
166 push(@update_l, "$pram='$val'");
167 }
168 my $update_str= join(", ", @update_l);
169 $update_str= " SET $update_str";
171 my $sql_statement= "UPDATE $table $update_str $prim_statement";
172 my $db_res = &update_dbentry($self, $sql_statement );
173 }
175 return 0;
176 }
179 sub update_dbentry {
180 my ($self, $sql)= @_;
181 my $db_answer= &exec_statement($self, $sql);
182 return $db_answer;
183 }
186 sub del_dbentry {
187 my ($self, $sql)= @_;;
188 my $db_res= &exec_statement($self, $sql);
189 return $db_res;
190 }
193 sub get_table_columns {
194 my $self = shift;
195 my $table = shift;
196 my @column_names;
198 if(exists $col_names->{$table}) {
199 @column_names = @{$col_names->{$table}};
200 } else {
201 my @res;
202 eval {
203 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
204 };
205 if($@) {
206 $self->{dbh}->do("ANALYZE");
207 eval {
208 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
209 };
210 if($@) {
211 &main::daemon_log("ERROR: pragma table_info('$table') failed with $@", 1);
212 }
213 }
215 foreach my $column (@res) {
216 push(@column_names, @$column[1]);
217 }
218 }
219 return \@column_names;
221 }
224 sub select_dbentry {
225 my ($self, $sql)= @_;
226 my $error= 0;
227 my $answer= {};
228 my $db_answer= &exec_statement($self, $sql);
229 my @column_list;
231 # fetch column list of db and create a hash with column_name->column_value of the select query
232 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
233 my $selected_cols = $1;
234 my $table = $2;
236 # all columns are used for creating answer
237 if ($selected_cols eq '*') {
238 @column_list = @{ &get_table_columns($self, $table) };
240 # specific columns are used for creating answer
241 } else {
242 # remove all blanks and split string to list of column names
243 $selected_cols =~ s/ //g;
244 @column_list = split(/,/, $selected_cols);
245 }
247 # create answer
248 my $hit_counter = 0;
249 my $list_len = @column_list;
250 foreach my $hit ( @{$db_answer} ){
251 $hit_counter++;
252 for ( my $i = 0; $i < $list_len; $i++) {
253 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
254 }
255 }
257 return $answer;
258 }
261 sub show_table {
262 my $self = shift;
263 my $table_name = shift;
265 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
266 my $res= &exec_statement($self, $sql_statement);
267 my @answer;
268 foreach my $hit (@{$res}) {
269 push(@answer, "hit: ".join(', ', @{$hit}));
270 }
272 return join("\n", @answer);
273 }
276 sub exec_statement {
277 my $self = shift;
278 my $sql_statement = shift;
279 my @db_answer;
281 eval {
282 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
283 };
284 if($@) {
285 $self->{dbh}->do("ANALYZE");
286 eval {
287 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
288 };
289 if($@) {
290 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
291 }
292 }
293 # TODO : maybe an error handling and an erro feedback to invoking function
294 #my $error = @$self->{dbh}->err;
295 #if ($error) {
296 # my $error_string = @$self->{dbh}->errstr;
297 #}
299 return \@db_answer;
300 }
303 sub exec_statementlist {
304 my $self = shift;
305 my $sql_list = shift;
306 my @db_answer;
308 foreach my $sql (@$sql_list) {
309 if(defined($sql) && length($sql) > 0) {
310 eval {
311 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
312 push @db_answer, @answer;
313 };
314 if($@) {
315 $self->{dbh}->do("ANALYZE");
316 eval {
317 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
318 push @db_answer, @answer;
319 };
320 if($@) {
321 &main::daemon_log("ERROR: $sql failed with $@", 1);
322 }
323 }
324 } else {
325 next;
326 }
327 }
329 return \@db_answer;
330 }
333 sub count_dbentries {
334 my ($self, $table)= @_;
335 my $error= 0;
336 my $answer= -1;
338 my $sql_statement= "SELECT * FROM $table";
339 my $db_answer= &select_dbentry($self, $sql_statement);
341 my $count = keys(%{$db_answer});
342 return $count;
343 }
346 sub move_table {
347 my ($self, $from, $to) = @_;
349 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
350 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
352 eval {
353 $self->{dbh}->do($sql_statement_drop);
354 };
355 if($@) {
356 $self->{dbh}->do("ANALYZE");
357 eval {
358 $self->{dbh}->do($sql_statement_drop);
359 };
360 if($@) {
361 &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1);
362 }
363 }
365 eval {
366 $self->{dbh}->do($sql_statement_alter);
367 };
368 if($@) {
369 $self->{dbh}->do("ANALYZE");
370 eval {
371 $self->{dbh}->do($sql_statement_alter);
372 };
373 if($@) {
374 &main::daemon_log("ERROR: $sql_statement_alter failed with $@", 1);
375 }
376 }
378 return;
379 }
382 1;