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 my $db_res = $obj->{dbh}->do($sql_statement);
87 print " DB result: $db_res\n";
88 return 0;
90 }
93 # error-flags
94 # 1 no table ($table) defined
95 # 2 no restriction parameter ($restric_pram) defined
96 # 3 no restriction value ($restric_val) defined
97 # 4 column name not known in table
98 # 5 no column names to change specified
99 sub update_dbentry {
100 my $obj = shift;
101 my $arg = shift;
103 # check completeness of function parameter
104 # extract table statement from arg hash
105 my $table = $arg->{table};
106 if (not defined $table) {
107 return 1;
108 } else {
109 delete $arg->{table};
110 }
112 # extract where parameter from arg hash
113 my $where_statement = "";
114 if( exists $arg->{where} ) {
115 my $where_hash = @{ $arg->{where} }[0];
116 if( 0 < keys %{ $where_hash } ) {
117 my @where_list;
118 while( my ($rest_pram, $rest_val) = each %{ $where_hash } ) {
119 my $statement;
120 if( $rest_pram eq 'timestamp' ) {
121 $statement = "$rest_pram<'@{ $rest_val }[0]'";
122 } else {
123 $statement = "$rest_pram='@{ $rest_val }[0]'";
124 }
125 push( @where_list, $statement );
126 }
127 $where_statement .= "WHERE ".join('AND ', @where_list);
128 }
129 }
131 # extract update parameter from arg hash
132 my $update_hash = @{ $arg->{update} }[0];
133 my $update_statement = "";
134 if( 0 < keys %{ $update_hash } ) {
135 my @update_list;
136 while( my ($rest_pram, $rest_val) = each %{ $update_hash } ) {
137 my $statement = "$rest_pram='@{ $rest_val }[0]'";
138 push( @update_list, $statement );
139 }
140 $update_statement .= join(', ', @update_list);
141 }
143 my $sql_statement = "UPDATE $table SET $update_statement $where_statement";
144 my $db_answer = $obj->{dbh}->do($sql_statement);
145 return $db_answer;
146 }
149 sub del_dbentry {
150 my $obj = shift;
151 my $arg = shift;
153 # check completeness of function parameter
154 # extract table statement from arg hash
155 my $table = $arg->{table};
156 if (not defined $table) {
157 return 1;
158 } else {
159 delete $arg->{table};
160 }
162 # collect select statements
163 my @del_list;
164 while (my ($pram, $val) = each %{$arg}) {
165 if ( $pram eq 'timestamp' ) {
166 push(@del_list, "$pram < '$val'");
167 } else {
168 push(@del_list, "$pram = '$val'");
169 }
170 }
172 my $where_statement;
173 if( not @del_list ) {
174 $where_statement = "";
175 } else {
176 $where_statement = "WHERE ".join(' AND ', @del_list);
177 }
179 my $sql_statement = "DELETE FROM $table $where_statement";
180 my $db_res = $obj->{dbh}->do($sql_statement);
182 return $db_res;
183 }
186 sub get_table_columns {
187 my $obj = shift;
188 my $table = shift;
190 my @columns;
191 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
192 foreach my $column (@res) {
193 push(@columns, @$column[1]);
194 }
196 return \@columns;
197 }
199 sub select_dbentry {
200 my $obj = shift;
201 my $arg = shift;
203 # check completeness of function parameter
204 # extract table statement from arg hash
205 my $table = $arg->{table};
206 if (not defined $table) {
207 return 1;
208 } else {
209 delete $arg->{table};
210 }
212 # collect select statements
213 my @select_list;
214 my $sql_statement;
215 while (my ($pram, $val) = each %{$arg}) {
216 if ( $pram eq 'timestamp' ) {
217 push(@select_list, "$pram < '$val'");
218 } else {
219 push(@select_list, "$pram = '$val'");
220 }
221 }
223 if (@select_list == 0) {
224 $sql_statement = "SELECT ROWID, * FROM '$table'";
225 } else {
226 $sql_statement = "SELECT ROWID, * FROM '$table' WHERE ".join(' AND ', @select_list);
227 }
229 # query db
230 my $query_answer = $obj->{dbh}->selectall_arrayref($sql_statement);
232 # fetch column list of db and create a hash with column_name->column_value of the select query
233 my $column_list = &get_table_columns($obj, $table);
234 my $list_len = @{ $column_list } ;
235 my $answer = {};
236 my $hit_counter = 0;
239 foreach my $hit ( @{ $query_answer }) {
240 $hit_counter++;
241 $answer->{ $hit_counter }->{ 'ROWID' } = shift @{ $hit };
242 for ( my $i = 0; $i < $list_len; $i++) {
243 $answer->{ $hit_counter }->{ @{ $column_list }[$i] } = @{ $hit }[$i];
244 }
245 }
246 return $answer;
247 }
250 sub show_table {
251 my $obj = shift;
252 my $table_name = shift;
253 my @res = @{$obj->{dbh}->selectall_arrayref( "SELECT ROWID, * FROM $table_name")};
254 my @answer;
255 foreach my $hit (@res) {
256 push(@answer, "hit: ".join(', ', @{$hit}));
257 }
258 return join("\n", @answer);
259 }
262 sub exec_statement {
263 my $obj = shift;
264 my $sql_statement = shift;
265 my @res = @{$obj->{dbh}->selectall_arrayref($sql_statement)};
266 return \@res;
267 }
269 1;