031702871c87002e54d15614811c90fec72aff8f
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 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
72 &create_lock($self,'create_table');
73 $self->{dbh}->do($sql_statement);
74 &remove_lock($self,'create_table');
75 return 0;
76 }
80 sub add_dbentry {
81 my $self = shift;
82 my $arg = shift;
84 # if dbh not specified, return errorflag 1
85 my $table = $arg->{table};
86 if( not defined $table ) {
87 return 1 ;
88 }
90 # specify primary key in table
91 if (not exists $arg->{primkey}) {
92 return 2;
93 }
94 my $primkey = $arg->{primkey};
96 # if primkey is id, fetch max id from table and give new job id= max(id)+1
97 if ($primkey eq 'id') {
98 my $id;
99 my $sql_statement = "SELECT MAX(CAST(id AS INTEGER)) FROM $table";
100 &create_lock($self,'add_dbentry');
101 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
102 &remove_lock($self,'add_dbentry');
103 if( defined $max_id) {
104 $id = $max_id + 1;
105 } else {
106 $id = 1;
107 }
108 $arg->{id} = $id;
109 }
111 # check wether value to primary key is specified
112 if ( not exists $arg->{ $primkey } ) {
113 return 3;
114 }
116 # if timestamp is not provided, add timestamp
117 if( not exists $arg->{timestamp} ) {
118 $arg->{timestamp} = &get_time;
119 }
121 # check wether primkey is unique in table, otherwise return errorflag
122 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
123 &create_lock($self,'add_dbentry');
124 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
125 &remove_lock($self,'add_dbentry');
126 if ($res == 0) {
127 # fetch column names of table
128 my $col_names = &get_table_columns("",$table);
130 # assign values to column name variables
131 my @add_list;
132 foreach my $col_name (@{$col_names}) {
133 # use function parameter for column values
134 if (exists $arg->{$col_name}) {
135 push(@add_list, $arg->{$col_name});
136 }
137 }
139 my $sql_statement = "INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
140 print STDERR $sql_statement;
141 &create_lock($self,'add_dbentry');
142 my $db_res = $self->{dbh}->do($sql_statement);
143 &remove_lock($self,'add_dbentry');
144 if( $db_res != 1 ) {
145 return 4;
146 } else {
147 return 0;
148 }
150 } else {
151 my $update_hash = { table=>$table };
152 $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ];
153 $update_hash->{update} = [ {} ];
154 while( my ($pram, $val) = each %{$arg} ) {
155 if( $pram eq 'table' ) { next; }
156 if( $pram eq 'primkey' ) { next; }
157 $update_hash->{update}[0]->{$pram} = [$val];
158 }
159 my $db_res = &update_dbentry( $self, $update_hash );
160 if( $db_res != 1 ) {
161 return 5;
162 } else {
163 return 0;
164 }
166 }
167 }
170 # error-flags
171 # 1 no table ($table) defined
172 # 2 no restriction parameter ($restric_pram) defined
173 # 3 no restriction value ($restric_val) defined
174 # 4 column name not known in table
175 # 5 no column names to change specified
176 #sub update_dbentry {
177 # my $self = shift;
178 # my $arg = shift;
179 #
180 #
181 # # check completeness of function parameter
182 # # extract table statement from arg hash
183 # my $table = $arg->{table};
184 # if (not defined $table) {
185 # return 1;
186 # } else {
187 # delete $arg->{table};
188 # }
189 #
190 # # extract where parameter from arg hash
191 # my $where_statement = "";
192 # if( exists $arg->{where} ) {
193 # my $where_hash = @{ $arg->{where} }[0];
194 # if( 0 < keys %{ $where_hash } ) {
195 # my @where_list;
196 # while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
197 # my $statement;
198 # if( $rest_pram eq 'timestamp' ) {
199 # $statement = "$rest_pram<'@{ $rest_val }[0]'";
200 # } else {
201 # $statement = "$rest_pram='@{ $rest_val }[0]'";
202 # }
203 # push( @where_list, $statement );
204 # }
205 # $where_statement .= "WHERE ".join('AND ', @where_list);
206 # }
207 # }
208 #
209 # # extract update parameter from arg hash
210 # my $update_hash = @{ $arg->{update} }[0];
211 # my $update_statement = "";
212 # if( 0 < keys %{ $update_hash } ) {
213 # my @update_list;
214 # while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
215 # my $statement = "$rest_pram='@{ $rest_val }[0]'";
216 # push( @update_list, $statement );
217 # }
218 # $update_statement .= join(', ', @update_list);
219 # }
220 #
221 # my $sql_statement = "UPDATE $table SET $update_statement $where_statement";
222 # &create_lock($self,'update_dbentry');
223 # my $db_answer = $self->{dbh}->do($sql_statement);
224 # &remove_lock($self,'update_dbentry');
225 # return $db_answer;
226 #}
227 sub update_dbentry {
228 my ($self, $sql)= @_;
229 my $db_answer= &exec_statement($self, $sql);
230 return $db_answer;
231 }
234 sub del_dbentry {
235 my ($self, $sql)= @_;;
236 my $db_res= &exec_statement($self, $sql);
237 return $db_res;
238 }
241 sub get_table_columns {
242 my $self = shift;
243 my $table = shift;
244 my @column_names;
246 if(exists $col_names->{$table}) {
247 @column_names = @{$col_names->{$table}};
248 } else {
249 &create_lock($self,'get_table_columns');
250 my @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
251 &remove_lock($self,'get_table_columns');
252 foreach my $column (@res) {
253 push(@column_names, @$column[1]);
254 }
255 }
256 return \@column_names;
258 }
261 sub select_dbentry {
262 my ($self, $sql)= @_;
264 my $db_answer= &exec_statement($self, $sql);
266 # fetch column list of db and create a hash with column_name->column_value of the select query
267 $sql =~ /FROM ([\S]*?) /g;
268 my $table = $1;
269 my $column_list = &get_table_columns($self, $table);
270 my $list_len = @{ $column_list } ;
271 my $answer = {};
272 my $hit_counter = 0;
273 foreach my $hit ( @{ $db_answer }) {
274 $hit_counter++;
275 for ( my $i = 0; $i < $list_len; $i++) {
276 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
277 }
278 }
280 return $answer;
281 }
284 sub show_table {
285 my $self = shift;
286 my $table_name = shift;
287 &create_lock($self,'show_table');
288 my @res = @{$self->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")};
289 &remove_lock($self,'show_table');
290 my @answer;
291 foreach my $hit (@res) {
292 push(@answer, "hit: ".join(', ', @{$hit}));
293 }
294 return join("\n", @answer);
295 }
298 sub exec_statement {
299 my $self = shift;
300 my $sql_statement = shift;
302 &create_lock($self,'exec_statement');
303 my @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
304 &remove_lock($self, 'exec_statement');
306 return \@db_answer;
307 }
310 sub get_time {
311 my ($seconds, $minutes, $hours, $monthday, $month,
312 $year, $weekday, $yearday, $sommertime) = localtime(time);
313 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
314 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
315 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
316 $month+=1;
317 $month = $month < 10 ? $month = "0".$month : $month;
318 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
319 $year+=1900;
320 return "$year$month$monthday$hours$minutes$seconds";
321 }
324 1;