5bee915c414a74b42152acc92d4beaf4bd8dd92b
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 my $primkey = $arg->{primkey};
51 # check wether value to primary key is specified
52 if ( ( defined $primkey ) && ( not $arg->{ $primkey } ) ) {
53 return 2;
54 }
56 # if timestamp is not provided, add timestamp
57 if( not exists $arg->{timestamp} ) {
58 $arg->{timestamp} = &get_time;
59 }
61 # check wether primkey is unique in table, otherwise return errorflag 3
62 my $res = @{ $obj->{dbh}->selectall_arrayref( "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'") };
63 if ($res == 0) {
64 # fetch column names of table
65 my $col_names = $obj->get_table_columns($table);
67 # assign values to column name variables
68 my @add_list;
69 foreach my $col_name (@{$col_names}) {
70 # use function parameter for column values
71 if (exists $arg->{$col_name}) {
72 push(@add_list, $arg->{$col_name});
73 }
74 }
76 my $sql_statement = " INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
77 my $db_res = $obj->{dbh}->do($sql_statement);
78 if( $db_res != 1 ) {
79 return 1;
80 } else {
81 return 0;
82 }
84 } else {
85 my $update_hash = { table=>$table };
86 $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ];
87 $update_hash->{update} = [ {} ];
88 while( my ($pram, $val) = each %{$arg} ) {
89 if( $pram eq 'table' ) { next; }
90 if( $pram eq 'primkey' ) { next; }
91 $update_hash->{update}[0]->{$pram} = [$val];
92 }
93 my $db_res = &update_dbentry( $obj, $update_hash );
94 if( $db_res != 1 ) {
95 return 1;
96 } else {
97 return 0;
98 }
100 }
101 }
104 # error-flags
105 # 1 no table ($table) defined
106 # 2 no restriction parameter ($restric_pram) defined
107 # 3 no restriction value ($restric_val) defined
108 # 4 column name not known in table
109 # 5 no column names to change specified
110 sub update_dbentry {
111 my $obj = shift;
112 my $arg = shift;
114 # check completeness of function parameter
115 # extract table statement from arg hash
116 my $table = $arg->{table};
117 if (not defined $table) {
118 return 1;
119 } else {
120 delete $arg->{table};
121 }
123 # extract where parameter from arg hash
124 my $where_statement = "";
125 if( exists $arg->{where} ) {
126 my $where_hash = @{ $arg->{where} }[0];
127 if( 0 < keys %{ $where_hash } ) {
128 my @where_list;
129 while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
130 my $statement;
131 if( $rest_pram eq 'timestamp' ) {
132 $statement = "$rest_pram<'@{ $rest_val }[0]'";
133 } else {
134 $statement = "$rest_pram='@{ $rest_val }[0]'";
135 }
136 push( @where_list, $statement );
137 }
138 $where_statement .= "WHERE ".join('AND ', @where_list);
139 }
140 }
142 # extract update parameter from arg hash
143 my $update_hash = @{ $arg->{update} }[0];
144 my $update_statement = "";
145 if( 0 < keys %{ $update_hash } ) {
146 my @update_list;
147 while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
148 my $statement = "$rest_pram='@{ $rest_val }[0]'";
149 push( @update_list, $statement );
150 }
151 $update_statement .= join(', ', @update_list);
152 }
154 my $sql_statement = "UPDATE $table SET $update_statement $where_statement";
155 my $db_answer = $obj->{dbh}->do($sql_statement);
156 return $db_answer;
157 }
160 sub del_dbentry {
161 my $obj = shift;
162 my $arg = shift;
164 # check completeness of function parameter
165 # extract table statement from arg hash
166 my $table = $arg->{table};
167 if (not defined $table) {
168 return 1;
169 } else {
170 delete $arg->{table};
171 }
173 # collect select statements
174 my @del_list;
175 while (my ($pram, $val) = each %{$arg}) {
176 if ( $pram eq 'timestamp' ) {
177 push(@del_list, "$pram < '$val'");
178 } else {
179 push(@del_list, "$pram = '$val'");
180 }
181 }
183 my $where_statement;
184 if( not @del_list ) {
185 $where_statement = "";
186 } else {
187 $where_statement = "WHERE ".join(' AND ', @del_list);
188 }
190 my $sql_statement = "DELETE FROM $table $where_statement";
191 my $db_res = $obj->{dbh}->do($sql_statement);
193 return $db_res;
194 }
197 sub get_table_columns {
198 my $obj = shift;
199 my $table = shift;
201 my @columns;
202 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
203 foreach my $column (@res) {
204 push(@columns, @$column[1]);
205 }
207 return \@columns;
208 }
210 sub select_dbentry {
211 my $obj = shift;
212 my $arg = shift;
214 # check completeness of function parameter
215 # extract table statement from arg hash
216 my $table = $arg->{table};
217 if (not defined $table) {
218 return 1;
219 } else {
220 delete $arg->{table};
221 }
223 # collect select statements
224 my @select_list;
225 my $sql_statement;
226 while (my ($pram, $val) = each %{$arg}) {
227 if ( $pram eq 'timestamp' ) {
228 push(@select_list, "$pram < '$val'");
229 } else {
230 push(@select_list, "$pram = '$val'");
231 }
232 }
234 if (@select_list == 0) {
235 $sql_statement = "SELECT ROWID, * FROM '$table'";
236 } else {
237 $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list);
238 }
240 # query db
241 my $query_answer = $obj->{dbh}->selectall_arrayref($sql_statement);
243 # fetch column list of db and create a hash with column_name->column_value of the select query
244 my $column_list = &get_table_columns($obj, $table);
245 my $list_len = @{ $column_list } ;
246 my $answer = {};
247 my $hit_counter = 0;
250 foreach my $hit ( @{ $query_answer }) {
251 $hit_counter++;
252 $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit };
253 for ( my $i = 0; $i < $list_len; $i++) {
254 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
255 }
256 }
257 return $answer;
258 }
261 sub show_table {
262 my $obj = shift;
263 my $table_name = shift;
264 my @res = @{$obj->{dbh}->selectall_arrayref( "SELECT ROWID, * FROM $table_name")};
265 my @answer;
266 foreach my $hit (@res) {
267 push(@answer, "hit: ".join(', ', @{$hit}));
268 }
269 return join("\n", @answer);
270 }
273 sub exec_statement {
274 my $obj = shift;
275 my $sql_statement = shift;
276 my @res = @{$obj->{dbh}->selectall_arrayref($sql_statement)};
277 return \@res;
278 }
280 sub get_time {
281 my ($seconds, $minutes, $hours, $monthday, $month,
282 $year, $weekday, $yearday, $sommertime) = localtime(time);
283 $hours = $hours < 10 ? $hours = "0".$hours : $hours;
284 $minutes = $minutes < 10 ? $minutes = "0".$minutes : $minutes;
285 $seconds = $seconds < 10 ? $seconds = "0".$seconds : $seconds;
286 $month+=1;
287 $month = $month < 10 ? $month = "0".$month : $month;
288 $monthday = $monthday < 10 ? $monthday = "0".$monthday : $monthday;
289 $year+=1900;
290 return "$year$month$monthday$hours$minutes$seconds";
291 }
294 1;