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