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 if ( defined $primkey ) {
58 my $res = @{ $obj->{dbh}->selectall_arrayref( "SELECT * FROM $table WHERE $primkey='$arg->{$primkey}'") };
59 if ($res != 0) {
60 return 3;
61 }
62 }
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 # use default values for column values
75 # } else {
76 # my $default_val = "none";
77 # if ($col_name eq "timestamp") {
78 # $default_val = "19700101000000";
79 # }
80 # push(@add_list, $default_val);
81 # }
82 }
84 my $sql_statement = " INSERT INTO $table VALUES ('".join("', '", @add_list)."')";
85 print " INSERT INTO $table VALUES ('".join("', '", @add_list)."')\n";
86 $obj->{dbh}->do($sql_statement);
87 return 0;
89 }
92 # error-flags
93 # 1 no table ($table) defined
94 # 2 no restriction parameter ($restric_pram) defined
95 # 3 no restriction value ($restric_val) defined
96 # 4 column name not known in table
97 # 5 no column names to change specified
98 sub update_dbentry {
99 my $obj = shift;
100 my $arg = shift;
102 # check completeness of function parameter
103 # extract table statement from arg hash
104 my $table = $arg->{table};
105 if (not defined $table) {
106 return 1;
107 } else {
108 delete $arg->{table};
109 }
110 # extract where parameter from arg hash
111 my $restric_pram = $arg->{where};
112 if (not defined $restric_pram) {
113 return 2;
114 } else {
115 delete $arg->{'where'};
116 }
117 # extrac where value from arg hash
118 my $restric_val = $arg->{$restric_pram};
119 if (not defined $restric_val) {
120 return 3;
121 } else {
122 delete $arg->{$restric_pram};
123 }
125 # check wether table has all specified columns
126 my $columns = {};
127 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
128 foreach my $column (@res) {
129 $columns->{@$column[1]} = "";
130 }
131 my @pram_list = keys %$arg;
132 foreach my $pram (@pram_list) {
133 if (not exists $columns->{$pram}) {
134 return 4;
135 }
136 }
139 # select all changes
140 my @change_list;
141 my $sql_part;
143 while (my($pram, $val) = each(%{$arg})) {
144 push(@change_list, "$pram='$val'");
145 }
147 if (not @change_list) {
148 return 5;
149 }
151 $obj->{dbh}->do("UPDATE $table SET ".join(', ',@change_list)." WHERE $restric_pram='$restric_val'");
152 return 0;
153 }
156 sub del_dbentry {
157 my $obj = shift;
158 my $arg = shift;
160 # check completeness of function parameter
161 # extract table statement from arg hash
162 my $table = $arg->{table};
163 if (not defined $table) {
164 return 1;
165 } else {
166 delete $arg->{table};
167 }
168 # extract where parameter from arg hash
169 my $restric_pram = $arg->{where};
170 if (not defined $restric_pram) {
171 return 2;
172 } else {
173 delete $arg->{'where'};
174 }
175 # extrac where value from arg hash
176 my $restric_val = $arg->{$restric_pram};
177 if (not defined $restric_val) {
178 return 3;
179 } else {
180 delete $arg->{$restric_pram};
181 }
183 # check wether entry exists
184 my $res = @{$obj->{dbh}->selectall_arrayref( "SELECT * FROM $table WHERE $restric_pram='$restric_val'")};
185 if ($res == 0) {
186 return 4;
187 }
189 $obj->{dbh}->do("DELETE FROM $table WHERE $restric_pram='$restric_val'");
191 return 0;
192 }
195 sub get_table_columns {
196 my $obj = shift;
197 my $table = shift;
199 my @columns;
200 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
201 foreach my $column (@res) {
202 push(@columns, @$column[1]);
203 }
205 return \@columns;
206 }
208 sub select_dbentry {
209 my $obj = shift;
210 my $arg = shift;
212 # check completeness of function parameter
213 # extract table statement from arg hash
214 my $table = $arg->{table};
215 if (not defined $table) {
216 return 1;
217 } else {
218 delete $arg->{table};
219 }
221 # collect select statements
222 my @select_list;
223 my $sql_statement;
224 while (my ($pram, $val) = each %{$arg}) {
225 if ( $pram eq 'timestamp' ) {
226 push(@select_list, "$pram < '$val'");
227 } else {
228 push(@select_list, "$pram = '$val'");
229 }
231 }
232 if (@select_list == 0) {
233 $sql_statement = "SELECT ROWID, * FROM '$table'";
234 } else {
235 $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list);
236 }
238 # query db
239 my $query_answer = $obj->{dbh}->selectall_arrayref($sql_statement);
241 # fetch column list of db and create a hash with column_name->column_value of the select query
242 my $column_list = &get_table_columns($obj, $table);
243 my $list_len = @{ $column_list } ;
244 my $answer = {};
245 my $hit_counter = 0;
248 foreach my $hit ( @{ $query_answer }) {
249 $hit_counter++;
250 $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit };
251 for ( my $i = 0; $i < $list_len; $i++) {
252 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
253 }
254 }
255 return $answer;
256 }
259 sub show_table {
260 my $obj = shift;
261 my $table_name = shift;
262 my @res = @{$obj->{dbh}->selectall_arrayref( "SELECT ROWID, * FROM $table_name")};
263 my @answer;
264 foreach my $hit (@res) {
265 push(@answer, "hit: ".join(', ', @{$hit}));
266 }
267 return join("\n", @answer);
268 }
271 sub exec_statement {
272 my $obj = shift;
273 my $sql_statement = shift;
274 my @res = @{$obj->{dbh}->selectall_arrayref($sql_statement)};
275 return \@res;
276 }
278 1;