1 package GOSA::DBmysql;
3 use strict;
4 use warnings;
5 use DBI;
6 use Data::Dumper;
7 use GOSA::GosaSupportDaemon;
8 use Time::HiRes qw(usleep);
10 my $col_names = {};
12 sub new {
13 my $class = shift;
15 my $self = {dbh=>undef};
16 my $dbh = DBI->connect("dbi:mysql:database=$main::mysql_database;host=$main::mysql_host", $main::mysql_username, $main::mysql_password,{ RaiseError => 1, AutoCommit => 1 });
17 $dbh->{mysql_auto_reconnect} = 1;
18 $self->{dbh} = $dbh;
19 bless($self,$class);
21 return($self);
22 }
25 sub create_table {
26 my $self = shift;
27 my $table_name = shift;
28 my $col_names_ref = shift;
29 my $recreate_table = shift || 0;
30 my @col_names;
31 my $col_names_string = join(", ", @$col_names_ref);
33 if($recreate_table) {
34 $self->{dbh}->do("DROP TABLE $table_name");
35 }
36 my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )";
37 # &main::daemon_log("DEBUG: $sql_statement");
38 eval {
39 $self->{dbh}->do($sql_statement);
40 };
41 if($@) {
42 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
43 }
45 return 0;
46 }
49 sub add_dbentry {
50 my $self = shift;
51 my $arg = shift;
52 my $res = 0; # default value
54 # if dbh not specified, return errorflag 1
55 my $table = $arg->{table};
56 if( not defined $table ) {
57 return 1 ;
58 }
60 # if timestamp is not provided, add timestamp
61 if( not exists $arg->{timestamp} ) {
62 $arg->{timestamp} = &get_time;
63 }
65 # check primkey and run insert or update
66 my $primkeys = $arg->{'primkey'};
67 my $prim_statement="";
68 if( 0 != @$primkeys ) { # more than one primkey exist in list
69 my @prim_list;
70 foreach my $primkey (@$primkeys) {
71 if( not exists $arg->{$primkey} ) {
72 return (3, "primkey '$primkey' has no value for add_dbentry");
73 }
74 push(@prim_list, "$primkey='".$arg->{$primkey}."'");
75 }
76 $prim_statement = "WHERE ".join(" AND ", @prim_list);
78 # check wether primkey is unique in table, otherwise return errorflag
79 my $sql_statement = "SELECT * FROM $table $prim_statement";
80 eval {
81 # &main::daemon_log("DEBUG: $sql_statement");
82 my $sth = $self->{dbh}->prepare($sql_statement);
83 $sth->execute;
84 $res = @{ $sth->fetchall_arrayref() };
85 $sth->finish;
86 };
87 if($@) {
88 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
89 }
91 }
93 # primkey is unique or no primkey specified -> run insert
94 if ($res == 0) {
95 # fetch column names of table
96 my $col_names = &get_table_columns($self, $table);
98 #my $create_id=0;
99 #foreach my $col_name (@{$col_names}) {
100 # #if($col_name eq "id" && (! exists $arg->{$col_name})) {
101 # #&main::daemon_log("0 DEBUG: id field found without value! Creating autoincrement statement!", 7);
102 # $create_id=1;
103 # }
104 #}
106 # assign values to column name variables
107 my @col_list;
108 my @val_list;
109 foreach my $col_name (@{$col_names}) {
110 # use function parameter for column values
111 if (exists $arg->{$col_name}) {
112 push(@col_list, $col_name);
113 push(@val_list, "'".$arg->{$col_name}."'");
114 }
115 }
117 my $sql_statement;
118 #if($create_id==1) {
119 # $sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES ((select coalesce(max(id),0)+1), ".join(", ", @val_list).")";
120 #} else {
121 $sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
122 #}
123 my $db_res;
124 # &main::daemon_log("DEBUG: $sql_statement",1);
125 eval {
126 $db_res = $self->{dbh}->do($sql_statement);
127 };
128 if($@) {
129 &main::daemon_log("ERROR: $sql_statement failed with $@", 1);
130 }
132 if( $db_res != 1 ) {
133 return (4, $sql_statement);
134 }
136 # entry already exists -> run update
137 } else {
138 my @update_l;
139 while( my ($pram, $val) = each %{$arg} ) {
140 if( $pram eq 'table' ) { next; }
141 if( $pram eq 'primkey' ) { next; }
142 push(@update_l, "$pram='$val'");
143 }
144 my $update_str= join(", ", @update_l);
145 $update_str= " SET $update_str";
147 my $sql_statement= "UPDATE $table $update_str $prim_statement";
148 my $db_res = &update_dbentry($self, $sql_statement );
149 }
151 return 0;
152 }
155 sub update_dbentry {
156 my ($self, $sql)= @_;
157 my $db_answer= &exec_statement($self, $sql);
158 return $db_answer;
159 }
162 sub del_dbentry {
163 my ($self, $sql)= @_;
164 my $db_res= &exec_statement($self, $sql);
165 return $db_res;
166 }
169 sub get_table_columns {
170 my $self = shift;
171 my $table = shift;
172 my @column_names;
174 my @res;
175 eval {
176 my $sth = $self->{dbh}->prepare("describe $table") or &main::daemon_log("ERROR: Preparation of statement 'describe $table' failed!", 1);
177 $sth->execute or &main::daemon_log("ERROR: Execution of statement 'describe $table' failed!", 1);
178 @res = @{ $sth->fetchall_arrayref() };
179 $sth->finish or &main::daemon_log("ERROR: Finishing the statement handle failed!", 1);
180 };
181 if($@) {
182 &main::daemon_log("ERROR: describe ('$table') failed with $@", 1);
183 }
185 foreach my $column (@res) {
186 push(@column_names, @$column[0]);
187 }
189 return \@column_names;
190 }
193 sub select_dbentry {
194 my ($self, $sql)= @_;
195 my $error= 0;
196 my $answer= {};
197 my $db_answer= &exec_statement($self, $sql);
198 my @column_list;
200 # fetch column list of db and create a hash with column_name->column_value of the select query
201 $sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
202 my $selected_cols = $1;
203 my $table = $2;
205 # all columns are used for creating answer
206 if ($selected_cols eq '*') {
207 @column_list = @{ &get_table_columns($self, $table) };
209 # specific columns are used for creating answer
210 } else {
211 # remove all blanks and split string to list of column names
212 $selected_cols =~ s/ //g;
213 @column_list = split(/,/, $selected_cols);
214 }
216 # create answer
217 my $hit_counter = 0;
218 my $list_len = @column_list;
219 foreach my $hit ( @{$db_answer} ){
220 $hit_counter++;
221 for ( my $i = 0; $i < $list_len; $i++) {
222 $answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
223 }
224 }
226 return $answer;
227 }
230 sub show_table {
231 my $self = shift;
232 my $table_name = shift;
234 my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
235 my $res= &exec_statement($self, $sql_statement);
236 my @answer;
237 foreach my $hit (@{$res}) {
238 push(@answer, "hit: ".join(', ', @{$hit}));
239 }
241 return join("\n", @answer);
242 }
245 sub exec_statement {
246 my $self = shift;
247 my $sql_statement = shift;
248 my $sth;
249 my @db_answer;
251 # print STDERR Dumper($sql_statement);
252 eval {
253 if($sql_statement =~ /^SELECT/i) {
254 $sth = $self->{dbh}->prepare($sql_statement) or &main::daemon_log("ERROR: Preparation of statement '$sql_statement' failed!", 1);
255 $sth->execute or &main::daemon_log("ERROR: Execution of statement '$sql_statement' failed!", 1);
256 if($sth->rows > 0) {
257 @db_answer = @{ $sth->fetchall_arrayref() } or &main::daemon_log("ERROR: Fetch() failed!", 1);
258 # print STDERR Dumper(@db_answer);
259 }
260 $sth->finish or &main::daemon_log("ERROR: Finishing the statement handle failed!", 1);
261 } else {
262 $self->{dbh}->do($sql_statement);
263 }
264 };
265 if($@) {
266 &main::daemon_log("ERROR: $sql_statement failed with '$@'", 1);
267 }
268 # TODO : maybe an error handling and an erro feedback to invoking function
269 my $error = $self->{dbh}->err;
270 if ($error) {
271 &main::daemon_log("ERROR: ".@$self->{dbh}->errstr, 1);
272 }
274 return \@db_answer;
275 }
278 sub exec_statementlist {
279 my $self = shift;
280 my $sql_list = shift;
281 my @db_answer;
283 foreach my $sql (@$sql_list) {
284 if(defined($sql) && length($sql) > 0) {
285 # &main::daemon_log("DEBUG: $sql");
286 eval {
287 if($sql =~ /^SELECT/i) {
288 my $sth = $self->{dbh}->prepare($sql);
289 # &main::daemon_log("DEBUG: ".$sth->execute);
290 if($sth->rows > 0) {
291 my @answer = @{$sth->fetchall_arrayref()};
292 push @db_answer, @answer;
293 }
294 $sth->finish;
295 } else {
296 $self->{dbh}->do($sql);
297 }
298 };
299 if($@) {
300 &main::daemon_log("ERROR: $sql failed with $@", 1);
301 }
302 } else {
303 next;
304 }
305 }
307 return \@db_answer;
308 }
311 sub count_dbentries {
312 my ($self, $table)= @_;
313 my $error= 0;
314 my $answer= -1;
316 my $sql_statement= "SELECT * FROM $table";
317 my $db_answer= &select_dbentry($self, $sql_statement);
319 my $count = keys(%{$db_answer});
320 return $count;
321 }
324 sub move_table {
325 my ($self, $from, $to) = @_;
327 my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
328 my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";
330 eval {
331 $self->{dbh}->do($sql_statement_drop);
332 $self->{dbh}->do($sql_statement_alter);
333 };
335 if($@) {
336 &main::daemon_log("ERROR: $sql_statement_drop failed with $@", 1);
337 }
339 return;
340 }
343 1;