cde2978231a52f3a18b92169d70dc86305d4e11d
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;
104 }
105 my $primkey = $arg->{primkey};
107 # if primkey is id, fetch max id from table and give new job id= max(id)+1
108 if ($primkey eq 'id') {
109 my $id;
110 my $sql_statement = "SELECT MAX(CAST(id AS INTEGER)) FROM $table";
111 &create_lock($self,'add_dbentry');
112 my $max_id = @{ @{ $self->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
113 &remove_lock($self,'add_dbentry');
114 if( defined $max_id) {
115 $id = $max_id + 1;
116 } else {
117 $id = 1;
118 }
119 $arg->{id} = $id;
120 }
122 # if timestamp is not provided, add timestamp
123 if( not exists $arg->{timestamp} ) {
124 $arg->{timestamp} = &get_time;
125 }
127 # check wether primkey is unique in table, otherwise return errorflag
128 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
129 &create_lock($self,'add_dbentry');
130 my $res = @{ $self->{dbh}->selectall_arrayref($sql_statement) };
131 &remove_lock($self,'add_dbentry');
132 if ($res == 0) {
133 # primekey is unique
135 # fetch column names of table
136 my $col_names = &get_table_columns($self, $table);
138 # assign values to column name variables
139 my @add_list;
140 foreach my $col_name (@{$col_names}) {
141 # use function parameter for column values
143 if (exists $arg->{$col_name}) {
144 push(@add_list, $arg->{$col_name});
145 }
146 }
148 my $sql_statement = "INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
149 &create_lock($self,'add_dbentry');
150 my $db_res = $self->{dbh}->do($sql_statement);
151 &remove_lock($self,'add_dbentry');
152 if( $db_res != 1 ) {
153 return 4;
154 }
156 } else {
157 # entry already exists, so update it
158 my $where_str= " WHERE $primkey='".$arg->{$primkey}."'";
160 my @update_l;
161 while( my ($pram, $val) = each %{$arg} ) {
162 if( $pram eq 'table' ) { next; }
163 if( $pram eq 'primkey' ) { next; }
164 push(@update_l, "$pram='$val'");
165 }
166 my $update_str= join(", ", @update_l);
167 $update_str= " SET $update_str";
169 my $sql_statement= "UPDATE $table $update_str $where_str";
170 my $db_res = &update_dbentry($self, $sql_statement );
172 }
174 return 0;
175 }
177 sub update_dbentry {
178 my ($self, $sql)= @_;
179 my $db_answer= &exec_statement($self, $sql);
180 return $db_answer;
181 }
184 sub del_dbentry {
185 my ($self, $sql)= @_;;
186 my $db_res= &exec_statement($self, $sql);
187 return $db_res;
188 }
191 sub get_table_columns {
192 my $self = shift;
193 my $table = shift;
194 my @column_names;
196 if(exists $col_names->{$table}) {
197 @column_names = @{$col_names->{$table}};
198 } else {
199 &create_lock($self,'get_table_columns');
200 my @res = @{$self->{dbh}->selectall_arrayref("pragma table_info('$table')")};
201 &remove_lock($self,'get_table_columns');
203 foreach my $column (@res) {
204 push(@column_names, @$column[1]);
205 }
206 }
207 return \@column_names;
209 }
212 sub select_dbentry {
213 my ($self, $sql)= @_;
214 my $error= 0;
215 my $answer= {};
217 my $db_answer= &exec_statement($self, $sql);
219 # fetch column list of db and create a hash with column_name->column_value of the select query
220 $sql =~ /FROM ([\S]*?)( |$)/g;
221 my $table = $1;
222 my $column_list = &get_table_columns($self, $table);
223 my $list_len = @{ $column_list } ;
224 my $hit_counter = 0;
225 foreach my $hit ( @{ $db_answer }) {
226 $hit_counter++;
227 for ( my $i = 0; $i < $list_len; $i++) {
228 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
229 }
230 }
232 return $answer;
233 }
236 sub show_table {
237 my $self = shift;
238 my $table_name = shift;
240 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
241 my $res= &exec_statement($self, $sql_statement);
243 my @answer;
244 foreach my $hit (@{$res}) {
245 push(@answer, "hit: ".join(', ', @{$hit}));
246 }
247 return join("\n", @answer);
248 }
251 sub exec_statement {
252 my $self = shift;
253 my $sql_statement = shift;
255 &create_lock($self,'exec_statement');
256 my @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
257 &remove_lock($self, 'exec_statement');
259 return \@db_answer;
260 }
263 sub count_dbentries {
264 my ($self, $table)= @_;
265 my $error= 0;
266 my $answer= -1;
268 my $sql_statement= "SELECT * FROM $table";
269 my $db_answer= &select_dbentry($self, $sql_statement);
271 my $count = keys(%{$db_answer});
272 return $count;
273 }
278 1;