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);
11 use utf8;
14 my $col_names = {};
16 sub new {
17 my $class = shift;
18 my $db_name = shift;
20 my $lock = $db_name.".lock";
21 # delete existing lock - instance should be running only once
22 if(stat($lock)) {
23 unlink($lock);
24 }
25 my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
26 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name");
27 $self->{dbh} = $dbh;
28 $self->{db_name} = $db_name;
29 $self->{db_lock} = $lock;
30 bless($self,$class);
32 return($self);
33 }
35 sub lock_exists : locked {
36 my $self=shift;
37 my $funcname=shift;
38 my $lock = $self->{db_lock};
39 my $result=(-f $lock);
40 if($result) {
41 #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock found", 8);
42 usleep 100;
43 }
44 return $result;
45 }
47 sub create_lock : locked {
48 my $self=shift;
49 my $funcname=shift;
50 #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Creating Lock (PID ".$$.") ".($self->{db_lock}),8);
52 my $lock = $self->{db_lock};
53 while( -f $lock ) {
54 #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Lock (PID ".$$.") $lock found",8);
55 sleep 1;
56 }
58 open($self->{db_lock_handle},'>',$self->{db_lock});
59 }
61 sub remove_lock : locked {
62 my $self=shift;
63 my $funcname=shift;
64 #&main::daemon_log("(".((defined $funcname)?$funcname:"").") Removing Lock (PID ".$$.") ".$self->{db_lock}, 8);
65 close($self->{db_lock_handle});
66 unlink($self->{db_lock});
67 }
70 sub create_table {
71 my $self = shift;
72 my $table_name = shift;
73 my $col_names_ref = shift;
74 my @col_names;
75 foreach my $col_name (@$col_names_ref) {
76 my @t = split(" ", $col_name);
77 $col_name = $t[0];
78 push(@col_names, $col_name);
79 }
81 $col_names->{ $table_name } = $col_names_ref;
82 my $col_names_string = join(', ', @col_names);
83 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
84 &create_lock($self,'create_table');
85 $self->{dbh}->do($sql_statement);
86 &remove_lock($self,'create_table');
87 return 0;
88 }
91 sub add_dbentry {
92 my $self = shift;
93 my $arg = shift;
95 # if dbh not specified, return errorflag 1
96 my $table = $arg->{table};
97 if( not defined $table ) {
98 return 1 ;
99 }
101 # specify primary key in table
102 if (not exists $arg->{primkey}) {
103 return (2, "a hash key 'primkey' with at least an empty list as value is necessary for add_dbentry");
104 }
105 my $primkeys = $arg->{'primkey'};
106 my $prim_statement;
107 if( 0 != length(@$primkeys) ) {
108 my @prim_list;
109 foreach my $primkey (@$primkeys) {
110 if($primkey eq 'id') {
111 # if primkey is id, fetch max id from table and give new job id= max(id)+1
112 my $sql_statement = "SELECT MAX(CAST(id AS INTEGER)) FROM $table";
113 &create_lock($self,'add_dbentry');
114 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
115 &remove_lock($self,'add_dbentry');
116 my $id;
117 if( defined $max_id) {
118 $id = $max_id + 1;
119 } else {
120 $id = 1;
121 }
122 $arg->{id} = $id;
123 }
124 if( not exists $arg->{$primkey} ) {
125 return (3, "primkey '$primkey' has no value for add_dbentry");
126 }
127 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
128 }
129 $prim_statement = "WHERE ".join(" AND ", @prim_list);
130 }
132 # if timestamp is not provided, add timestamp
133 if( not exists $arg->{timestamp} ) {
134 $arg->{timestamp} = &get_time;
135 }
137 # check wether primkey is unique in table, otherwise return errorflag
138 my $sql_statement = "SELECT * FROM $table $prim_statement";
139 &create_lock($self,'add_dbentry');
140 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
141 &remove_lock($self,'add_dbentry');
143 if ($res == 0) {
144 # primekey is unique
146 # fetch column names of table
147 my $col_names = &get_table_columns($self, $table);
149 # assign values to column name variables
150 my @add_list;
151 foreach my $col_name (@{$col_names}) {
152 # use function parameter for column values
153 if (exists $arg->{$col_name}) {
154 push(@add_list, $arg->{$col_name});
155 }
156 }
158 my $sql_statement = "INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
159 &create_lock($self,'add_dbentry');
160 my $db_res = $self->{dbh}->do($sql_statement);
161 &remove_lock($self,'add_dbentry');
162 if( $db_res != 1 ) {
163 return (4, $sql_statement);
164 }
166 } else {
167 # entry already exists, so update it
168 my @update_l;
169 while( my ($pram, $val) = each %{$arg} ) {
170 if( $pram eq 'table' ) { next; }
171 if( $pram eq 'primkey' ) { next; }
172 push(@update_l, "$pram='$val'");
173 }
174 my $update_str= join(", ", @update_l);
175 $update_str= " SET $update_str";
177 my $sql_statement= "UPDATE $table $update_str $prim_statement";
178 my $db_res = &update_dbentry($self, $sql_statement );
180 }
182 return 0;
183 }
185 sub update_dbentry {
186 my ($self, $sql)= @_;
187 my $db_answer= &exec_statement($self, $sql);
188 return $db_answer;
189 }
192 sub del_dbentry {
193 my ($self, $sql)= @_;;
194 my $db_res= &exec_statement($self, $sql);
195 return $db_res;
196 }
199 sub get_table_columns {
200 my $self = shift;
201 my $table = shift;
202 my @column_names;
204 if(exists $col_names->{$table}) {
205 @column_names = @{$col_names->{$table}};
206 } else {
207 &create_lock($self,'get_table_columns');
208 my @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
209 &remove_lock($self,'get_table_columns');
211 foreach my $column (@res) {
212 push(@column_names, @$column[1]);
213 }
214 }
215 return \@column_names;
217 }
220 sub select_dbentry {
221 my ($self, $sql)= @_;
222 my $error= 0;
223 my $answer= {};
225 my $db_answer= &exec_statement($self, $sql);
227 # fetch column list of db and create a hash with column_name->column_value of the select query
228 $sql =~ /FROM ([\S]*?)( |$)/g;
229 my $table = $1;
230 my $column_list = &get_table_columns($self, $table);
231 my $list_len = @{ $column_list } ;
232 my $hit_counter = 0;
233 foreach my $hit ( @{ $db_answer }) {
234 $hit_counter++;
235 for ( my $i = 0; $i < $list_len; $i++) {
236 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
237 }
238 }
240 return $answer;
241 }
244 sub show_table {
245 my $self = shift;
246 my $table_name = shift;
248 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
249 my $res= &exec_statement($self, $sql_statement);
251 my @answer;
252 foreach my $hit (@{$res}) {
253 push(@answer, "hit: ".join(', ', @{$hit}));
254 }
255 return join("\n", @answer);
256 }
259 sub exec_statement {
260 my $self = shift;
261 my $sql_statement = shift;
263 &create_lock($self,'exec_statement');
264 my @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
265 &remove_lock($self, 'exec_statement');
267 return \@db_answer;
268 }
271 sub count_dbentries {
272 my ($self, $table)= @_;
273 my $error= 0;
274 my $answer= -1;
276 my $sql_statement= "SELECT * FROM $table";
277 my $db_answer= &select_dbentry($self, $sql_statement);
279 my $count = keys(%{$db_answer});
280 return $count;
281 }
286 1;