840bce0d6fb7d67abd90f4ea0b28ce5a0a2ecb7a
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 # check wether primkey is unique in table, otherwise return errorflag 3
57 my $res = @{ $obj->{dbh}->selectall_arrayref( "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'") };
58 if ($res == 0) {
59 # fetch column names of table
60 my $col_names = $obj->get_table_columns($table);
62 # assign values to column name variables
63 my @add_list;
64 foreach my $col_name (@{$col_names}) {
65 # use function parameter for column values
66 if (exists $arg->{$col_name}) {
67 push(@add_list, $arg->{$col_name});
68 }
69 }
71 my $sql_statement = " INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
72 my $db_res = $obj->{dbh}->do($sql_statement);
73 if( $db_res != 1 ) {
74 return 1;
75 } else {
76 return 0;
77 }
79 } else {
80 my $update_hash = { table=>$table };
81 $update_hash->{where} = [ { $primkey=>[ $arg->{$primkey} ] } ];
82 $update_hash->{update} = [ {} ];
83 while( my ($pram, $val) = each %{$arg} ) {
84 if( $pram eq 'table' ) { next; }
85 if( $pram eq 'primkey' ) { next; }
86 $update_hash->{update}[0]->{$pram} = [$val];
87 }
88 my $db_res = &update_dbentry( $obj, $update_hash );
89 if( $db_res != 1 ) {
90 return 1;
91 } else {
92 return 0;
93 }
95 }
96 # # check wether primkey is unique in table, otherwise return errorflag 3
97 # if ( defined $primkey ) {
98 # my $res = @{ $obj->{dbh}->selectall_arrayref( "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'") };
99 # if ($res != 0) {
100 # return 3;
101 # }
102 # }
103 #
104 # # fetch column names of table
105 # my $col_names = $obj->get_table_columns($table);
106 #
107 # # assign values to column name variables
108 # my @add_list;
109 # foreach my $col_name (@{$col_names}) {
110 # # use function parameter for column values
111 # if (exists $arg->{$col_name}) {
112 # push(@add_list, $arg->{$col_name});
113 # }
114 # }
115 #
116 # my $sql_statement = " INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
117 # my $db_res = $obj->{dbh}->do($sql_statement);
118 # return 0;
120 }
123 # error-flags
124 # 1 no table ($table) defined
125 # 2 no restriction parameter ($restric_pram) defined
126 # 3 no restriction value ($restric_val) defined
127 # 4 column name not known in table
128 # 5 no column names to change specified
129 sub update_dbentry {
130 my $obj = shift;
131 my $arg = shift;
133 # check completeness of function parameter
134 # extract table statement from arg hash
135 my $table = $arg->{table};
136 if (not defined $table) {
137 return 1;
138 } else {
139 delete $arg->{table};
140 }
142 # extract where parameter from arg hash
143 my $where_statement = "";
144 if( exists $arg->{where} ) {
145 my $where_hash = @{ $arg->{where} }[0];
146 if( 0 < keys %{ $where_hash } ) {
147 my @where_list;
148 while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
149 my $statement;
150 if( $rest_pram eq 'timestamp' ) {
151 $statement = "$rest_pram<'@{ $rest_val }[0]'";
152 } else {
153 $statement = "$rest_pram='@{ $rest_val }[0]'";
154 }
155 push( @where_list, $statement );
156 }
157 $where_statement .= "WHERE ".join('AND ', @where_list);
158 }
159 }
161 # extract update parameter from arg hash
162 my $update_hash = @{ $arg->{update} }[0];
163 my $update_statement = "";
164 if( 0 < keys %{ $update_hash } ) {
165 my @update_list;
166 while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
167 my $statement = "$rest_pram='@{ $rest_val }[0]'";
168 push( @update_list, $statement );
169 }
170 $update_statement .= join(', ', @update_list);
171 }
173 my $sql_statement = "UPDATE $table SET $update_statement $where_statement";
174 my $db_answer = $obj->{dbh}->do($sql_statement);
175 return $db_answer;
176 }
179 sub del_dbentry {
180 my $obj = shift;
181 my $arg = shift;
183 # check completeness of function parameter
184 # extract table statement from arg hash
185 my $table = $arg->{table};
186 if (not defined $table) {
187 return 1;
188 } else {
189 delete $arg->{table};
190 }
192 # collect select statements
193 my @del_list;
194 while (my ($pram, $val) = each %{$arg}) {
195 if ( $pram eq 'timestamp' ) {
196 push(@del_list, "$pram < '$val'");
197 } else {
198 push(@del_list, "$pram = '$val'");
199 }
200 }
202 my $where_statement;
203 if( not @del_list ) {
204 $where_statement = "";
205 } else {
206 $where_statement = "WHERE ".join(' AND ', @del_list);
207 }
209 my $sql_statement = "DELETE FROM $table $where_statement";
210 my $db_res = $obj->{dbh}->do($sql_statement);
212 return $db_res;
213 }
216 sub get_table_columns {
217 my $obj = shift;
218 my $table = shift;
220 my @columns;
221 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
222 foreach my $column (@res) {
223 push(@columns, @$column[1]);
224 }
226 return \@columns;
227 }
229 sub select_dbentry {
230 my $obj = shift;
231 my $arg = shift;
233 # check completeness of function parameter
234 # extract table statement from arg hash
235 my $table = $arg->{table};
236 if (not defined $table) {
237 return 1;
238 } else {
239 delete $arg->{table};
240 }
242 # collect select statements
243 my @select_list;
244 my $sql_statement;
245 while (my ($pram, $val) = each %{$arg}) {
246 if ( $pram eq 'timestamp' ) {
247 push(@select_list, "$pram < '$val'");
248 } else {
249 push(@select_list, "$pram = '$val'");
250 }
251 }
253 if (@select_list == 0) {
254 $sql_statement = "SELECT ROWID, * FROM '$table'";
255 } else {
256 $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list);
257 }
259 # query db
260 my $query_answer = $obj->{dbh}->selectall_arrayref($sql_statement);
262 # fetch column list of db and create a hash with column_name->column_value of the select query
263 my $column_list = &get_table_columns($obj, $table);
264 my $list_len = @{ $column_list } ;
265 my $answer = {};
266 my $hit_counter = 0;
269 foreach my $hit ( @{ $query_answer }) {
270 $hit_counter++;
271 $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit };
272 for ( my $i = 0; $i < $list_len; $i++) {
273 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
274 }
275 }
276 return $answer;
277 }
280 sub show_table {
281 my $obj = shift;
282 my $table_name = shift;
283 my @res = @{$obj->{dbh}->selectall_arrayref( "SELECT ROWID, * FROM $table_name")};
284 my @answer;
285 foreach my $hit (@res) {
286 push(@answer, "hit: ".join(', ', @{$hit}));
287 }
288 return join("\n", @answer);
289 }
292 sub exec_statement {
293 my $obj = shift;
294 my $sql_statement = shift;
295 my @res = @{$obj->{dbh}->selectall_arrayref($sql_statement)};
296 return \@res;
297 }
299 1;