1 package GOSA::DBsqlite;
4 use strict;
5 use warnings;
6 use DBI;
7 use Data::Dumper;
8 use GOSA::GosaSupportDaemon;
9 use threads;
10 use Time::HiRes qw(usleep);
14 my $col_names = {};
16 sub new {
17 my $class = shift;
18 my $db_name = shift;
20 my $lock='/tmp/gosa_si_lock';
21 my $_lock = $db_name;
22 $_lock =~ tr/\//_/;
23 $lock.=$_lock;
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");
26 $self->{dbh} = $dbh;
27 $self->{db_name} = $db_name;
28 $self->{db_lock} = $lock;
29 bless($self,$class);
31 return($self);
32 }
34 sub lock_exists : locked {
35 my $self=shift;
36 my $funcname=shift;
37 my $lock = $self->{db_lock};
38 my $result=(-f $lock);
39 if($result) {
40 &main::daemon_log("(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden", 6);
41 usleep 100;
42 }
43 return $result;
44 }
46 sub create_lock : locked {
47 my $self=shift;
48 my $funcname=shift;
49 &main::daemon_log("(".((defined $funcname)?$funcname:"").") Erzeuge Lock (PID ".$$.") ".($self->{db_lock}),6);
51 my $lock = $self->{db_lock};
52 while( -f $lock ) {
53 print STDERR "(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock gefunden\n";
54 sleep 1;
55 }
57 open($self->{db_lock_handle},'>',$self->{db_lock});
58 }
60 sub remove_lock : locked {
61 my $self=shift;
62 my $funcname=shift;
63 &main::daemon_log("(".((defined $funcname)?$funcname:"").") Entferne Lock (PID ".$$.") ".$self->{db_lock}, 6);
64 close($self->{db_lock_handle});
65 unlink($self->{db_lock});
66 }
69 sub create_table {
70 my $self = shift;
71 my $table_name = shift;
72 my $col_names_ref = shift;
73 $col_names->{ $table_name } = $col_names_ref;
74 my $col_names_string = join(', ', @{$col_names_ref});
75 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
76 &create_lock($self,'create_table');
77 $self->{dbh}->do($sql_statement);
78 &remove_lock($self,'create_table');
79 return 0;
80 }
83 sub add_dbentry {
84 my $self = shift;
85 my $arg = shift;
87 # if dbh not specified, return errorflag 1
88 my $table = $arg->{table};
89 if( not defined $table ) {
90 return 1 ;
91 }
93 # specify primary key in table
94 if (not exists $arg->{primkey}) {
95 return 2;
96 }
97 my $primkey = $arg->{primkey};
99 # if primkey is id, fetch max id from table and give new job id= max(id)+1
100 if ($primkey eq 'id') {
101 my $id;
102 my $sql_statement = "SELECT MAX(CAST(id AS INTEGER)) FROM $table";
103 &create_lock($self,'add_dbentry');
104 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
105 &remove_lock($self,'add_dbentry');
106 if( defined $max_id) {
107 $id = $max_id + 1;
108 } else {
109 $id = 1;
110 }
111 $arg->{id} = $id;
112 }
114 # if timestamp is not provided, add timestamp
115 if( not exists $arg->{timestamp} ) {
116 $arg->{timestamp} = &get_time;
117 }
119 # check wether primkey is unique in table, otherwise return errorflag
120 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
121 &create_lock($self,'add_dbentry');
122 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
123 &remove_lock($self,'add_dbentry');
124 if ($res == 0) {
125 # primekey is unique
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)."')";
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 }
148 } else {
149 # entry already exists, so update it
150 my $where_str= " WHERE $primkey='".$arg->{$primkey}."'";
152 my @update_l;
153 while( my ($pram, $val) = each %{$arg} ) {
154 if( $pram eq 'table' ) { next; }
155 if( $pram eq 'primkey' ) { next; }
156 push(@update_l, "$pram='$val'");
157 }
158 my $update_str= join(", ", @update_l);
159 $update_str= " SET $update_str";
161 my $sql_statement= "UPDATE $table $update_str $where_str";
162 my $db_res = &update_dbentry($self, $sql_statement );
164 }
166 return 0;
167 }
169 sub update_dbentry {
170 my ($self, $sql)= @_;
171 my $db_answer= &exec_statement($self, $sql);
172 return $db_answer;
173 }
176 sub del_dbentry {
177 my ($self, $sql)= @_;;
178 my $db_res= &exec_statement($self, $sql);
179 return $db_res;
180 }
183 sub get_table_columns {
184 my $self = shift;
185 my $table = shift;
186 my @column_names;
188 if(exists $col_names->{$table}) {
189 @column_names = @{$col_names->{$table}};
190 } else {
191 &create_lock($self,'get_table_columns');
192 my @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
193 &remove_lock($self,'get_table_columns');
194 foreach my $column (@res) {
195 push(@column_names, @$column[1]);
196 }
197 }
198 return \@column_names;
200 }
203 sub select_dbentry {
204 my ($self, $sql)= @_;
205 my $error= 0;
206 my $answer= {};
208 my $db_answer= &exec_statement($self, $sql);
210 # fetch column list of db and create a hash with column_name->column_value of the select query
211 $sql =~ /FROM ([\S]*?)( |$)/g;
212 my $table = $1;
213 my $column_list = &get_table_columns($self, $table);
214 my $list_len = @{ $column_list } ;
215 my $hit_counter = 0;
216 foreach my $hit ( @{ $db_answer }) {
217 $hit_counter++;
218 for ( my $i = 0; $i < $list_len; $i++) {
219 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
220 }
221 }
223 return $answer;
224 }
227 sub show_table {
228 my $self = shift;
229 my $table_name = shift;
230 #&create_lock($self,'show_table');
231 #my @res = @{$self->{dbh}->selectall_arrayref( "SELECT * FROM $table_name ORDER BY timestamp")};
232 #&remove_lock($self,'show_table');
234 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
235 &create_lock($self,'show_table');
236 my $res= &exec_statement($self, $sql_statement);
237 &remove_lock($self,'show_table');
239 my @answer;
240 foreach my $hit (@{$res}) {
241 push(@answer, "hit: ".join(', ', @{$hit}));
242 }
243 return join("\n", @answer);
244 }
247 sub exec_statement {
248 my $self = shift;
249 my $sql_statement = shift;
251 &create_lock($self,'exec_statement');
252 my @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
253 &remove_lock($self, 'exec_statement');
255 return \@db_answer;
256 }
259 sub count_dbentries {
260 my ($self, $table)= @_;
261 my $error= 0;
262 my $answer= -1;
264 my $sql_statement= "SELECT * FROM $table";
265 &create_lock($self,'count_dbentries');
266 my $db_answer= &select_dbentry($self, $sql_statement);
267 &remove_lock($self, 'count_dbentries');
269 my $count = keys(%{$db_answer});
270 return $count;
271 }
273 1;