2e838333f0edb6cae2ea347605b2ec6eeb88ded4
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 }
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")};
232 &remove_lock($self,'show_table');
233 my @answer;
234 foreach my $hit (@res) {
235 push(@answer, "hit: ".join(', ', @{$hit}));
236 }
237 return join("\n", @answer);
238 }
241 sub exec_statement {
242 my $self = shift;
243 my $sql_statement = shift;
245 &create_lock($self,'exec_statement');
246 my @db_answer = @{$self->{dbh}->selectall_arrayref($sql_statement)};
247 &remove_lock($self, 'exec_statement');
249 return \@db_answer;
250 }
253 sub get_time {
254 my ($seconds, $minutes, $hours, $monthday, $month,
255 $year, $weekday, $yearday, $sommertime) = localtime(time);
256 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
257 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
258 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
259 $month+=1;
260 $month = $month < 10 ? $month = "0".$month : $month;
261 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
262 $year+=1900;
263 return "$year$month$monthday$hours$minutes$seconds";
264 }
267 1;