1 package GOSA::DBsqlite;
4 use strict;
5 use warnings;
6 use DBI;
7 use Data::Dumper;
11 sub new {
12 my $object = shift;
13 my $db_name = shift;
15 my $obj_ref = {};
16 bless($obj_ref,$object);
17 my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name");
18 $obj_ref->{dbh} = $dbh;
20 return($obj_ref);
21 }
24 sub create_table {
25 my $object = shift;
26 my $table_name = shift;
27 my $col_names_ref = shift;
28 # unshift(@{$col_names_ref}, "id INTEGER PRIMARY KEY AUTOINCREMENT");
29 my $col_names_string = join(', ', @{$col_names_ref});
30 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
31 $object->{dbh}->do($sql_statement);
32 return 0;
33 }
37 sub add_dbentry {
39 my $obj = shift;
40 my $arg = shift;
42 # if dbh not specified, return errorflag 1
43 my $table = $arg->{table};
44 if (not defined $table) {
45 return 1;
46 }
48 # specify primary key in table
49 if (not exists $arg->{primkey}) {
50 return 2;
51 }
52 my $primkey = $arg->{primkey};
54 # if primkey is id, fetch max id from table and give new job id= max(id)+1
55 if ($primkey eq 'id') {
56 my $id;
57 my $sql_statement = "SELECT MAX(id) FROM $table";
58 my $max_id = @{ @{ $obj->{dbh}->selectall_arrayref($sql_statement) }[0] }[0];
59 if( defined $max_id) {
60 $id = $max_id + 1;
61 } else {
62 $id = 1;
63 }
64 $arg->{id} = $id;
65 }
67 # check wether value to primary key is specified
68 if ( not exists $arg->{ $primkey } ) {
69 return 3;
70 }
72 # if timestamp is not provided, add timestamp
73 if( not exists $arg->{timestamp} ) {
74 $arg->{timestamp} = &get_time;
75 }
77 # check wether primkey is unique in table, otherwise return errorflag 3
78 my $sql_statement = "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'";
79 my $res = @{ $obj->{dbh}->selectall_arrayref($sql_statement) };
80 if ($res == 0) {
81 # fetch column names of table
82 my $col_names = $obj->get_table_columns($table);
84 # assign values to column name variables
85 my @add_list;
86 foreach my $col_name (@{$col_names}) {
87 # use function parameter for column values
88 if (exists $arg->{$col_name}) {
89 push(@add_list, $arg->{$col_name});
90 }
91 }
93 my $sql_statement = "BEGIN TRANSACTION; INSERT INTO $table VALUES ('".join("', '", @add_list)."'); COMMIT;";
94 my $db_res = $obj->{dbh}->do($sql_statement);
95 if( $db_res != 1 ) {
96 return 1;
97 } else {
98 return 0;
99 }
101 } else {
102 my $update_hash = { table=>$table };
103 $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ];
104 $update_hash->{update} = [ {} ];
105 while( my ($pram, $val) = each %{$arg} ) {
106 if( $pram eq 'table' ) { next; }
107 if( $pram eq 'primkey' ) { next; }
108 $update_hash->{update}[0]->{$pram} = [$val];
109 }
110 my $db_res = &update_dbentry( $obj, $update_hash );
111 if( $db_res != 1 ) {
112 return 1;
113 } else {
114 return 0;
115 }
117 }
118 }
121 # error-flags
122 # 1 no table ($table) defined
123 # 2 no restriction parameter ($restric_pram) defined
124 # 3 no restriction value ($restric_val) defined
125 # 4 column name not known in table
126 # 5 no column names to change specified
127 sub update_dbentry {
128 my $obj = shift;
129 my $arg = shift;
131 # check completeness of function parameter
132 # extract table statement from arg hash
133 my $table = $arg->{table};
134 if (not defined $table) {
135 return 1;
136 } else {
137 delete $arg->{table};
138 }
140 # extract where parameter from arg hash
141 my $where_statement = "";
142 if( exists $arg->{where} ) {
143 my $where_hash = @{ $arg->{where} }[0];
144 if( 0 < keys %{ $where_hash } ) {
145 my @where_list;
146 while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
147 my $statement;
148 if( $rest_pram eq 'timestamp' ) {
149 $statement = "$rest_pram<'@{ $rest_val }[0]'";
150 } else {
151 $statement = "$rest_pram='@{ $rest_val }[0]'";
152 }
153 push( @where_list, $statement );
154 }
155 $where_statement .= "WHERE ".join('AND ', @where_list);
156 }
157 }
159 # extract update parameter from arg hash
160 my $update_hash = @{ $arg->{update} }[0];
161 my $update_statement = "";
162 if( 0 < keys %{ $update_hash } ) {
163 my @update_list;
164 while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
165 my $statement = "$rest_pram='@{ $rest_val }[0]'";
166 push( @update_list, $statement );
167 }
168 $update_statement .= join(', ', @update_list);
169 }
171 my $sql_statement = "BEGIN TRANSACTION; UPDATE $table SET $update_statement $where_statement; COMMIT;";
172 my $db_answer = $obj->{dbh}->do($sql_statement);
173 return $db_answer;
174 }
177 sub del_dbentry {
178 my $obj = shift;
179 my $arg = shift;
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 }
190 # collect select statements
191 my @del_list;
192 while (my ($pram, $val) = each %{$arg}) {
193 if ( $pram eq 'timestamp' ) {
194 push(@del_list, "$pram < '$val'");
195 } else {
196 push(@del_list, "$pram = '$val'");
197 }
198 }
200 my $where_statement;
201 if( not @del_list ) {
202 $where_statement = "";
203 } else {
204 $where_statement = "WHERE ".join(' AND ', @del_list);
205 }
207 my $sql_statement = "BEGIN TRANSACTION; DELETE FROM $table $where_statement; COMMIT;";
208 my $db_res = $obj->{dbh}->do($sql_statement);
210 return $db_res;
211 }
214 sub get_table_columns {
215 my $obj = shift;
216 my $table = shift;
218 my @columns;
219 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
220 foreach my $column (@res) {
221 push(@columns, @$column[1]);
222 }
224 return \@columns;
225 }
227 sub select_dbentry {
228 my $obj = shift;
229 my $arg = shift;
231 # check completeness of function parameter
232 # extract table statement from arg hash
233 my $table = $arg->{table};
234 if (not defined $table) {
235 return 1;
236 } else {
237 delete $arg->{table};
238 }
240 # collect select statements
241 my @select_list;
242 my $sql_statement;
243 while (my ($pram, $val) = each %{$arg}) {
244 if ( $pram eq 'timestamp' ) {
245 push(@select_list, "$pram < '$val'");
246 } else {
247 push(@select_list, "$pram = '$val'");
248 }
249 }
251 if (@select_list == 0) {
252 $sql_statement = "SELECT ROWID, * FROM '$table'";
253 } else {
254 $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list);
255 }
257 # query db
258 my $query_answer = $obj->{dbh}->selectall_arrayref($sql_statement);
260 # fetch column list of db and create a hash with column_name->column_value of the select query
261 my $column_list = &get_table_columns($obj, $table);
262 my $list_len = @{ $column_list } ;
263 my $answer = {};
264 my $hit_counter = 0;
267 foreach my $hit ( @{ $query_answer }) {
268 $hit_counter++;
269 $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit };
270 for ( my $i = 0; $i < $list_len; $i++) {
271 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
272 }
273 }
274 return $answer;
275 }
278 sub show_table {
279 my $obj = shift;
280 my $table_name = shift;
281 my @res = @{$obj->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")};
282 my @answer;
283 foreach my $hit (@res) {
284 push(@answer, "hit: ".join(', ', @{$hit}));
285 }
286 return join("\n", @answer);
287 }
290 sub exec_statement {
291 my $obj = shift;
292 my $sql_statement = shift;
293 my @res = @{$obj->{dbh}->selectall_arrayref($sql_statement)};
294 return \@res;
295 }
297 sub get_time {
298 my ($seconds, $minutes, $hours, $monthday, $month,
299 $year, $weekday, $yearday, $sommertime) = localtime(time);
300 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
301 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
302 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
303 $month+=1;
304 $month = $month < 10 ? $month = "0".$month : $month;
305 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
306 $year+=1900;
307 return "$year$month$monthday$hours$minutes$seconds";
308 }
311 1;