1 package 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 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name (".join(', ', @{$col_names_ref}).")";
29 $object->{dbh}->do($sql_statement);
30 return 0;
31 }
35 sub add_dbentry {
37 my $obj = shift;
38 my $arg = shift;
40 # if dbh not specified, return errorflag 1
41 my $table = $arg->{table};
42 if (not defined $table) {
43 return 1;
44 }
46 # incrementing running id
47 if (not exists $arg->{id}) {
48 my $max_id = @{@{$obj->{dbh}->selectall_arrayref("SELECT MAX(id) FROM $table")}[0]}[0];
49 if (not defined $max_id) {
50 $max_id = 0;
51 }
52 $arg->{id} = $max_id + 1;
53 }
56 # fetch column names of table
57 my $col_names = $obj->get_table_columns($table);
59 # assign values to column name variables
60 my @add_list;
61 foreach my $col_name (@{$col_names}) {
62 if (exists $arg->{$col_name}) {
63 push(@add_list, $arg->{$col_name});
64 } else {
65 my $default_val = "none";
66 if ($col_name eq "timestamp") {
67 $default_val = "19700101000000";
68 }
69 push(@add_list, $default_val);
70 }
72 }
74 # check wether id does not exists in table, otherwise return errorflag 2
75 my $res = @{$obj->{dbh}->selectall_arrayref( "SELECT * FROM $table WHERE id='$arg->{id}'")};
76 if ($res != 0) {
77 return 2;
78 }
80 my $sql_statement = " INSERT INTO $table VALUES ('".join("', '", @add_list)."') ";
81 print " INSERT INTO $table VALUES ('".join("', '", @add_list)."')\n";
82 $obj->{dbh}->do($sql_statement);
84 return 0;
86 }
88 sub change_dbentry {
89 my $obj = shift;
90 my $arg = shift;
92 # check completeness of function parameter
93 # extract table statement from arg hash
94 my $table = $arg->{table};
95 if (not defined $table) {
96 return 1;
97 } else {
98 delete $arg->{table};
99 }
100 # extract where parameter from arg hash
101 my $restric_pram = $arg->{where};
102 if (not defined $restric_pram) {
103 return 2;
104 } else {
105 delete $arg->{'where'};
106 }
107 # extrac where value from arg hash
108 my $restric_val = $arg->{$restric_pram};
109 if (not defined $restric_val) {
110 return 3;
111 } else {
112 delete $arg->{$restric_pram};
113 }
115 # check wether table has all specified columns
116 my $columns = {};
117 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
118 foreach my $column (@res) {
119 $columns->{@$column[1]} = "";
120 }
121 my @pram_list = keys %$arg;
122 foreach my $pram (@pram_list) {
123 if (not exists $columns->{$pram}) {
124 return 4;
125 }
126 }
129 # select all changes
130 my @change_list;
131 my $sql_part;
133 while (my($pram, $val) = each(%{$arg})) {
134 push(@change_list, "$pram='$val'");
135 }
137 if (not@change_list) {
138 return 5;
139 }
141 $obj->{dbh}->do("UPDATE $table SET ".join(', ',@change_list)." WHERE $restric_pram='$restric_val'");
142 return 0;
143 }
146 sub del_dbentry {
147 my $obj = shift;
148 my $arg = shift;
150 # check completeness of function parameter
151 # extract table statement from arg hash
152 my $table = $arg->{table};
153 if (not defined $table) {
154 return 1;
155 } else {
156 delete $arg->{table};
157 }
158 # extract where parameter from arg hash
159 my $restric_pram = $arg->{where};
160 if (not defined $restric_pram) {
161 return 2;
162 } else {
163 delete $arg->{'where'};
164 }
165 # extrac where value from arg hash
166 my $restric_val = $arg->{$restric_pram};
167 if (not defined $restric_val) {
168 return 3;
169 } else {
170 delete $arg->{$restric_pram};
171 }
173 # check wether entry exists
174 my $res = @{$obj->{dbh}->selectall_arrayref( "SELECT * FROM $table WHERE $restric_pram='$restric_val'")};
175 if ($res == 0) {
176 return 4;
177 }
179 $obj->{dbh}->do("DELETE FROM $table WHERE $restric_pram='$restric_val'");
181 return 0;
182 }
185 sub get_table_columns {
186 my $obj = shift;
187 my $table = shift;
189 my @columns;
190 my @res = @{$obj->{dbh}->selectall_arrayref("pragma table_info('$table')")};
191 foreach my $column (@res) {
192 push(@columns, @$column[1]);
193 }
195 return \@columns;
196 }
198 sub select_dbentry {
199 my $obj = shift;
200 my $arg = shift;
202 # check completeness of function parameter
203 # extract table statement from arg hash
204 my $table = $arg->{table};
205 if (not defined $table) {
206 return 1;
207 } else {
208 delete $arg->{table};
209 }
211 # collect select statements
212 my @select_list;
213 my $sql_part;
214 while (my ($pram, $val) = each %{$arg}) {
215 push(@select_list, "$pram = '$val'");
216 }
218 my $sql_statement = "SELECT * FROM 'jobs' WHERE ".join(' AND ', @select_list);
219 my $answer = $obj->{dbh}->selectall_arrayref($sql_statement);
220 return $answer;
221 }
224 sub show_table {
225 my $obj = shift;
226 my $table_name = shift;
227 my @res = @{$obj->{dbh}->selectall_arrayref( "SELECT * FROM $table_name")};
228 my @answer;
229 foreach my $hit (@res) {
230 push(@answer, "hit: ".join(', ', @{$hit}));
231 }
232 return join("\n", @answer);
233 }
236 sub exec_statement {
237 my $obj = shift;
238 my $sql_statement = shift;
239 my @res = @{$obj->{dbh}->selectall_arrayref($sql_statement)};
240 return \@res;
241 }
243 1;