3b702376ab3a0e0464377db31b624dd05d6172b0
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 chmod(0640, $db_name);
27 chown($main::root_uid, $main::adm_gid, $db_name);
29 $self->{dbh} = $dbh;
30 $self->{db_name} = $db_name;
31 $self->{db_lock} = $lock;
33 bless($self,$class);
34 return($self);
35 }
38 sub create_table {
39 my $self = shift;
40 my $table_name = shift;
41 my $col_names_ref = shift;
42 my @col_names;
43 foreach my $col_name (@$col_names_ref) {
44 my @t = split(" ", $col_name);
45 $col_name = $t[0];
46 push(@col_names, $col_name);
47 }
49 $col_names->{ $table_name } = $col_names_ref;
50 my $col_names_string = join("', '", @col_names);
51 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( '$col_names_string' )";
52 eval {
53 my $res = $self->{dbh}->do($sql_statement);
54 };
55 if($@) {
56 $self->{dbh}->do("ANALYZE");
57 }
58 eval {
59 my $res = $self->{dbh}->do($sql_statement);
60 };
61 if($@) {
62 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
63 }
65 return 0;
66 }
69 sub add_dbentry {
70 my $self = shift;
71 my $arg = shift;
72 my $res = 0; # default value
74 # if dbh not specified, return errorflag 1
75 my $table = $arg->{table};
76 if( not defined $table ) {
77 return 1 ;
78 }
80 # if timestamp is not provided, add timestamp
81 if( not exists $arg->{timestamp} ) {
82 $arg->{timestamp} = &get_time;
83 }
85 # check primkey and run insert or update
86 my $primkeys = $arg->{'primkey'};
87 my $prim_statement="";
88 if( 0 != @$primkeys ) { # more than one primkey exist in list
89 my @prim_list;
90 foreach my $primkey (@$primkeys) {
91 if( not exists $arg->{$primkey} ) {
92 return (3, "primkey '$primkey' has no value for add_dbentry");
93 }
94 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
95 }
96 $prim_statement = "WHERE ".join(" AND ", @prim_list);
98 # check wether primkey is unique in table, otherwise return errorflag
99 my $sql_statement = "SELECT * FROM $table $prim_statement";
100 eval {
101 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
102 };
103 if($@) {
104 $self->{dbh}->do("ANALYZE");
105 eval {
106 $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
107 };
108 if($@) {
109 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
110 }
111 }
113 }
115 # primkey is unique or no primkey specified -> run insert
116 if ($res == 0) {
117 # fetch column names of table
118 my $col_names = &get_table_columns($self, $table);
120 my $create_id=0;
121 foreach my $col_name (@{$col_names}) {
122 if($col_name eq "id" && (! exists $arg->{$col_name})) {
123 #&main::daemon_log("0 DEBUG: id field found without value! Creating autoincrement statement!", 7);
124 $create_id=1;
125 }
126 }
128 # assign values to column name variables
129 my @col_list;
130 my @val_list;
131 foreach my $col_name (@{$col_names}) {
132 # use function parameter for column values
133 if (exists $arg->{$col_name}) {
134 push(@col_list, "'".$col_name."'");
135 push(@val_list, "'".$arg->{$col_name}."'");
136 }
137 }
139 my $sql_statement;
140 if($create_id==1) {
141 $sql_statement = "INSERT INTO $table ('id', ".join(", ", @col_list).") VALUES ((select coalesce(max(id), 0)+1 from $table), ".join(", ", @val_list).")";
142 } else {
143 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
144 }
145 my $db_res;
146 eval {
147 $db_res = $self->{dbh}->do($sql_statement);
148 };
149 if($@) {
150 $self->{dbh}->do("ANALYZE");
151 eval {
152 $db_res = $self->{dbh}->do($sql_statement);
153 };
154 if($@) {
155 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
156 }
157 }
159 if( $db_res != 1 ) {
160 return (4, $sql_statement);
161 }
163 # entry already exists -> run update
164 } else {
165 my @update_l;
166 while( my ($pram, $val) = each %{$arg} ) {
167 if( $pram eq 'table' ) { next; }
168 if( $pram eq 'primkey' ) { next; }
169 push(@update_l, "$pram='$val'");
170 }
171 my $update_str= join(", ", @update_l);
172 $update_str= " SET $update_str";
174 my $sql_statement= "UPDATE $table $update_str $prim_statement";
175 my $db_res = &update_dbentry($self, $sql_statement );
176 }
178 return 0;
179 }
182 sub update_dbentry {
183 my ($self, $sql)= @_;
184 my $db_answer= &exec_statement($self, $sql);
185 return $db_answer;
186 }
189 sub del_dbentry {
190 my ($self, $sql)= @_;;
191 my $db_res= &exec_statement($self, $sql);
192 return $db_res;
193 }
196 sub get_table_columns {
197 my $self = shift;
198 my $table = shift;
199 my @column_names;
201 if(exists $col_names->{$table}) {
202 @column_names = @{$col_names->{$table}};
203 } else {
204 my @res;
205 eval {
206 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
207 };
208 if($@) {
209 $self->{dbh}->do("ANALYZE");
210 eval {
211 @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
212 };
213 if($@) {
214 &main::daemon_log("ERROR: pragma table_info('$table') failed with $@", 1);
215 }
216 }
218 foreach my $column (@res) {
219 push(@column_names, @$column[1]);
220 }
221 }
222 return \@column_names;
224 }
227 sub select_dbentry {
228 my ($self, $sql)= @_;
229 my $error= 0;
230 my $answer= {};
231 my $db_answer= &exec_statement($self, $sql);
232 my @column_list;
234 # fetch column list of db and create a hash with column_name->column_value of the select query
235 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
236 my $selected_cols = $1;
237 my $table = $2;
239 # all columns are used for creating answer
240 if ($selected_cols eq '*') {
241 @column_list = @{ &get_table_columns($self, $table) };
243 # specific columns are used for creating answer
244 } else {
245 # remove all blanks and split string to list of column names
246 $selected_cols =~ s/ //g;
247 @column_list = split(/,/, $selected_cols);
248 }
250 # create answer
251 my $hit_counter = 0;
252 my $list_len = @column_list;
253 foreach my $hit ( @{$db_answer} ){
254 $hit_counter++;
255 for ( my $i = 0; $i < $list_len; $i++) {
256 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
257 }
258 }
260 return $answer;
261 }
264 sub show_table {
265 my $self = shift;
266 my $table_name = shift;
268 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
269 my $res= &exec_statement($self, $sql_statement);
270 my @answer;
271 foreach my $hit (@{$res}) {
272 push(@answer, "hit: ".join(', ', @{$hit}));
273 }
275 return join("\n", @answer);
276 }
279 sub exec_statement {
280 my $self = shift;
281 my $sql_statement = shift;
282 my @db_answer;
284 eval {
285 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
286 };
287 if($@) {
288 $self->{dbh}->do("ANALYZE");
289 eval {
290 @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
291 };
292 if($@) {
293 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
294 }
295 }
296 # TODO : maybe an error handling and an erro feedback to invoking function
297 #my $error = @$self->{dbh}->err;
298 #if ($error) {
299 # my $error_string = @$self->{dbh}->errstr;
300 #}
302 return \@db_answer;
303 }
306 sub exec_statementlist {
307 my $self = shift;
308 my $sql_list = shift;
309 my @db_answer;
311 foreach my $sql (@$sql_list) {
312 if(defined($sql) && length($sql) > 0) {
313 eval {
314 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
315 push @db_answer, @answer;
316 };
317 if($@) {
318 $self->{dbh}->do("ANALYZE");
319 eval {
320 my @answer = @{$self->{dbh}->selectall_arrayref($sql)};
321 push @db_answer, @answer;
322 };
323 if($@) {
324 &main::daemon_log("ERROR: $sql failed with $@", 1);
325 }
326 }
327 } else {
328 next;
329 }
330 }
332 return \@db_answer;
333 }
336 sub count_dbentries {
337 my ($self, $table)= @_;
338 my $error= 0;
339 my $answer= -1;
341 my $sql_statement= "SELECT * FROM $table";
342 my $db_answer= &select_dbentry($self, $sql_statement);
344 my $count = keys(%{$db_answer});
345 return $count;
346 }
349 sub move_table {
350 my ($self, $from, $to) = @_;
352 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
353 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
355 eval {
356 $self->{dbh}->do($sql_statement_drop);
357 };
358 if($@) {
359 $self->{dbh}->do("ANALYZE");
360 eval {
361 $self->{dbh}->do($sql_statement_drop);
362 };
363 if($@) {
364 &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1);
365 }
366 }
368 eval {
369 $self->{dbh}->do($sql_statement_alter);
370 };
371 if($@) {
372 $self->{dbh}->do("ANALYZE");
373 eval {
374 $self->{dbh}->do($sql_statement_alter);
375 };
376 if($@) {
377 &main::daemon_log("ERROR: $sql_statement_alter failed with $@", 1);
378 }
379 }
381 return;
382 }
385 1;