blib/lib/DBD/mysql/SimpleMySQL.pm untested

filter: Statementstmt 0.0% 0 / 276 Branchbran 0.0% 0 / 78 Conditioncond 0.0% 0 / 14 MC/DCmcdc 0.0% 0 / 9 Subroutinesub 0.0% 0 / 23 Podpod 0.0% 0 / 19 totaltotal 0.0% 0 / 419 CC 40 SCAR 37.9
CC
40
Cov
0%
debug
110.0
dbconnect
110.0
build_select
42.0
CRAP 44.2
1 use strict;
2 use DBI;
3  
4 package DBD::mysql::SimpleMySQL;
5 use Exporter;
6 use vars qw/$VERSION $MOD_DATE $NAME $DEBUG $DEBUG_FILE @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS/;
7 @ISA = qw|Exporter|;
8 @EXPORT_OK = qw|
9 dbinsert
10 dbconnect
11 dbselect
12 dbselect_arrayref
13 dbinsert
14 dbupdate
15 dbdo
16 where_struct
17 join_struct
18 build_joins
19 build_wheres
20 build_select
21 build_insert
22 build_delete
23 find_dup
24 |;
25  
26 %EXPORT_TAGS = (
27 all => [
28 'dbinsert',
29 'dbconnect',
30 'dbselect',
31 'dbselect_arrayref',
32 'dbinsert',
33 'dbupdate',
34 'dbdo',
35 'where_struct',
36 'join_struct',
37 'build_joins',
38 'build_wheres',
39 'build_select',
40 'build_insert',
41 'build_delete',
42 'find_dup'
43 ]
44 );
45  
46  
47 $VERSION = q|$Revision: 1.8 $|;
48 $MOD_DATE = q|$Date: 2004/04/28 20:07:27 $|;
49 $NAME = "SimpleMySQL.pm";
50 $DEBUG = 0;
51 $DEBUG_FILE = 0;
52  
53  
54  
55 #####
56 # Methods
57 ###
58 #
59 #
60  
61  
62 sub pnl () {
63 print "\n";
64 }
65  
66 sub nl () {
67 return "\n";
68 }
69  
70 sub debug {
71 my $message = shift;
72 my $level = shift;
73
74 $DEBUG = 0 unless $DEBUG;
75 $level = 1 unless $level;
76 if ($DEBUG >= $level) {
77 my $tabs = '';
78 my $i = 0;
79 my @lines = ();
80 while (my @info = caller($i)) {
81 push @lines, "$info[0], $info[2], $info[3]";
82 $lines[$i] .= " $message" if ($i == 1);
83 $tabs .= "\t" if ($i > 0);
84 $i++;
85 }
86 $lines[0] .= " $message" if ($i < 2);
87 push @lines, "END DEBUG";
88
89 my $fh = 0;
90 if (-f $DEBUG_FILE and -w $DEBUG_FILE) {
91 open FILE, ">>$DEBUG_FILE" or warn "Could not open $DEBUG_FILE:$!";
92 $fh = \*FILE;
93 } else {
94 $fh = \*STDERR;
95 }
96 for my $line (@lines) {
97 print $fh $tabs, $line, "\n" if($line);
98 }
99 close $fh if (-f $DEBUG_FILE and -w $DEBUG_FILE);
100 }
101 }
102
103  
104 sub dbconnect ($) {
105 my $dbinfo = shift;
106  
107 ${$dbinfo}{host} = 'localhost' unless(defined(${$dbinfo}{host}));
108 ${$dbinfo}{user} = scalar(getpwuid($<)) unless(defined(${$dbinfo}{user}));
109 ${$dbinfo}{pass} = '' unless(defined(${$dbinfo}{pass}));
110 ${$dbinfo}{port} = '3306' unless(defined(${$dbinfo}{port}));
111 ${$dbinfo}{RaiseError} = 0 unless(defined(${$dbinfo}{RaiseError}));
112 ${$dbinfo}{AutoCommit} = 1 unless(defined(${$dbinfo}{AutoCommit}));
113
114 unless(defined(${$dbinfo}{dsh})) {
115 ${$dbinfo}{dsh} .= "DBI";
116 ${$dbinfo}{dsh} .= ":mysql";
117 ${$dbinfo}{dsh} .= ":host=${$dbinfo}{host}";
118 ${$dbinfo}{dsh} .= ":datasbase=${$dbinfo}{database}" unless(defined(${$dbinfo}{database}));;
119 ${$dbinfo}{dsh} .= ":port=${$dbinfo}{port}";
120 }
121  
122 my $dbh = DBI->connect(
123 $$dbinfo{dsh},
124 $$dbinfo{user},
125 $$dbinfo{pass},
126 {
127 RaiseError => ${$dbinfo}{RaiseError},
128 AutoCommit => ${$dbinfo}{AutoCommit}
129 }
130 );
131 unless(ref($dbh)){
132 debug("dbconnect error: $DBI::errstr", 0);
133 return 0;
134 }
135 return $dbh;
136 }
137  
138 sub dbselect ($$) {
139 my $dbh = shift;
140 my $query = shift;
141  
142 debug("executing query \n$query", 3);
143 my $sth = $dbh->prepare($query);
144 $sth->execute;
145 if ($DBI::errstr) {
146 debug("dbselect error: $DBI::errstr\n\tQuery is $query", 0);
147 $sth = 0;
148 }
149  
150 return $sth;
151 }
152  
153 sub dbselect_arrayref ($$) {
154 my $dbh = shift;
155 my $sql = shift;
156  
157 my $sth = dbselect($dbh, $sql);
158  
159 return 0 unless ($sth);
160 my @return = ();
161 while (my $r = $sth->fetchrow_hashref) {
162 push @return, $r;
163 }
164  
165 return \@return;
166 }
167  
168 sub dbdo ($$) {
169 my $dbh = shift;
170 my $sql = shift;
171  
172 debug("Running sql command: $sql", 3);
173 my $rv = $dbh->do($sql);
174 if ($DBI::errstr) {
175 debug("$DBI::errstr",0);
176 return 0;
177 }
178
179 return $rv;
180 }
181
182 sub dbupdate ($$$$) {
183 my $dbh = shift;
184 my $table = shift;
185 my $pairs = shift;
186 my $id = shift;
187
188 unless ($id and ref($pairs)) {
189 return 0;
190 }
191 my @tmp;
192 for my $key (keys(%{$pairs})) {
193 next if ($key eq $id);
194 push @tmp, qq|$key = "${$pairs}{$key}"|;
195 }
196 my $set = join ",", @tmp;
197 my $where = qq|$id = "${$pairs}{$id}"|;
198
199 my $sql = qq|UPDATE $table SET $set WHERE $where|;
200
201 my $return = dbdo($dbh, $sql);
202
203 return $return;
204 }
205  
206 sub where_struct ($$$) {
207 my $key = shift;
208 my $type = shift;
209 my $value = shift;
210  
211 my $return = {
212 key => $key,
213 value => $value,
214 type => $type,
215 };
216  
217 return $return;
218 }
219  
220  
221 sub join_struct ($$$) {
222 my $table = shift;
223 my $tableid = shift;
224 my $joinid = shift;
225  
226 my $return = {
227 table => $table,
228 tableid => $tableid,
229 joinid => $joinid,
230 };
231  
232 return $return;
233 }
234  
235 sub build_joins ($) {
236 my $joins = shift;
237  
238 my $return = '';
239 if (ref($joins)) {
240 for my $join (@{$joins}) {
241 if (ref($join)) {
242 $return .= " LEFT JOIN ";
243 $return .= ${$join}{table};
244 $return .= " ON ";
245 $return .= ${$join}{tableid};
246 $return .= " = ";
247 $return .= ${$join}{joinid};
248 } else {
249 $return .= $join;
250 }
251 }
252 } else {
253 $return .= $joins;
254 }
255  
256 return $return;
257 }
258  
259 sub build_wheres ($) {
260 my $wheres = shift;
261  
262 my $return;
263 if (ref($wheres)) {
264 $return = ' WHERE ';
265 my @and;
266 for my $where (@{$wheres}) {
267 my $tmp = qq/${$where}{key} ${$where}{type} "${$where}{value}"/;
268 $tmp = qq/${$where}{key} ${$where}{type} (${$where}{value})/ if (${$where}{type} =~ /^in$/i);
269 push @and, $tmp;
270 }
271 $return .= join " AND ", @and;
272 } else {
273 unless ($wheres =~ m/WHERE/i) {
274 $return = ' WHERE ';
275 }
276 $return .= $wheres;
277 }
278  
279 return $return;
280 }
281  
282 sub build_delete ($$$) {
283 my $from = shift;
284 my $joins = shift;
285 my $wheres = shift;
286  
287 my $return = "DELETE FROM ";
288
289 $return .= join ",", @{$from};
290 $return .= build_joins($joins) if ($joins);
291 $return .= build_wheres($wheres) if ($wheres);
292  
293 return $return;
294 }
295  
296 sub overload ($$) {
297 my $at = shift;
298 my $argc = shift;
299 }
300  
301 sub build_select {
302 my $select;
303 my $from;
304 my $joins;
305 my $wheres;
306 my $order;
307 my $ref;
308 my $limit;
309
310 if (scalar(@_) == 5) {
311 $select = shift;
312 $from = shift;
313 $joins = shift;
314 $wheres = shift;
315 $order = shift;
316 } elsif (scalar(@_) == 1) {
317 $ref = shift;
318
319 $select = ${$ref}{'select'};
320 $from = ${$ref}{from};
321 $joins = ${$ref}{joins};
322 $wheres = ${$ref}{wheres};
323 $order = ${$ref}{order};
324 $limit = ${$ref}{limit};
325 } else {
326 die "build_select must be passed 1 or 5 args";
327 }
328  
329 my $return = "SELECT ";
330  
331 $return .= join ",", @{$select};
332 $return .= " FROM ";
333 $return .= join ",", @{$from};
334 $return .= build_joins($joins) if ($joins);
335 $return .= build_wheres($wheres) if ($wheres);
336 $return .= " $order" if ($order);
337  
338 return $return;
339 }
340  
341 sub build_insert ($$) {
342 my $table = shift;
343 my $pairs = shift;
344  
345 return 0 unless (ref($pairs));
346 my $return = "INSERT INTO $table SET ";
347 my @tmp;
348 for my $key (keys(%{$pairs})) {
349 push @tmp, qq/$key = '${$pairs}{$key}'/;
350 }
351  
352 $return .= join ",", @tmp;
353  
354 return $return;
355 }
356  
357 sub find_dup ($$$$) {
358 my $dbh = shift;
359 my $table = shift;
360 my $pairs = shift;
361 my $matchon = shift;
362  
363 my $select = ['*'];
364 my $from = [$table];
365 my $wheres = [];
366 for my $match (@{$matchon}) {
367 push @{$wheres}, wheres_struct($match, 'like', ${$pairs}{$match});
368 }
369  
370 my $rows = dbselect_arrayref($dbh, build_select($select, $from, 0, $wheres, 0));
371  
372 if (scalar(@{$rows}) > 0) {
373 return $rows;
374 } else {
375 return 0;
376 }
377 }
378  
379 sub add_hashs ($$) {
380 my $hash1 = shift;
381 my $hash2 = shift;
382  
383 my $return = {};
384 return $return unless (ref($hash1) and ref($hash2));
385  
386 for my $key (keys(%{$hash1})) {
387 ${$return}{$key} = ${$hash1}{$key};
388 }
389
390 for my $key (keys(%{$hash2})) {
391 ${$return}{$key} = ${$hash2}{$key};
392 }
393  
394 return $return;
395 }
396
397 sub dbinsert ($$$) {
398 my $dbh = shift;
399 my $table = shift;
400 my $pairs = shift;
401  
402 my $sql = build_insert($table, $pairs);
403  
404 my @r;
405 debug("$sql", 3);
406 $dbh->do($sql);
407 if ($DBI::errstr) {
408 debug("$DBI::errstr",0);
409 return 0;
410 }
411 @r = $dbh->selectrow_array("select last_insert_id()");
412  
413 return $r[0];
414 }
415  
416 #####
417 # End Functions
418 ###
419 #
420 #
421  
422 1;
423  
424 ## Documentation
425 #
426 =head1 NAME
427  
428 DBD::mysql::SimpleMySQL - A simple interface to DBD::mysql
429  
430 =head1 SYNOPSIS
431  
432 my %dbinfo = (
433 user => 'user',
434 pass => 'password',
435 dsh => "DBI:mysql:database=DB:host=127.0.0.1:port=3307"
436 );
437  
438 OR
439
440 my %dbinfo = (
441 user => 'user',
442 pass => 'password',
443 database => 'DB',
444 host => '127.0.0.1',
445 port => '3307'
446 );
447  
448 $my $dbh = dbconnect(\%dbinfo);
449  
450 my $select = ['Passwd.*', 'UsrGrp.UsrGrpName'];
451 my $from = ['Passwd'];
452 my $joins = [];
453 push @{$joins}, join_struct("PasswdHostGrp", "Passwd.PasswdID", "PasswdHostGrp.PasswdID");
454 push @{$joins}, join_struct("UsrGrp", "Passwd.PrimaryGroupID", "UsrGrp.UsrGrpID");
455 my $wheres = "PasswdHostGrp.HostGrpID IN ('group1', 'group2')";
456
457 my $arrayref = dbselect_array($dbh, build_select($select, $from, $joins, $wheres, 0));
458  
459 =head1 EXAMPLE
460  
461 #!/usr/bin/perl -w
462 #
463 use strict;
464 use DBD::mysql::SimpleMySQL qw/:all/;
465  
466 my %dbinfo = (
467 user => 'user',
468 pass => 'password',
469 database => 'DB',
470 host => '127.0.0.1',
471 port => '3307'
472 );
473  
474 $my $dbh = dbconnect(\%dbinfo);
475  
476 my $select = ['Passwd.*', 'UsrGrp.UsrGrpName'];
477 my $from = ['Passwd'];
478 my $joins = [];
479 push @{$joins}, join_struct("PasswdHostGrp", "Passwd.PasswdID", "PasswdHostGrp.PasswdID");
480 push @{$joins}, join_struct("UsrGrp", "Passwd.PrimaryGroupID", "UsrGrp.UsrGrpID");
481 my $wheres = "PasswdHostGrp.HostGrpID IN ('group1', 'group2')";
482
483 my $arrayref = dbselect_array($dbh, build_select($select, $from, $joins, $wheres, 0));
484  
485  
486 my $i = 1;
487 for my $row (@{$arrayref}) {
488 print "Row $i\n";
489 for my $key (keys(%{$row})) {
490 print "\t$key = ${$row}{$key}\n";
491 }
492 $i++;
493 }
494
495 =head1 DESCRIPTION
496  
497 DBD::mysql::SimpleMySQL is an extention of the DBI mysql driver. It simplifies getting the data you want out of your DB. I wrote it because I found that everytime I put together a DBI based app I ended up writing these functions anyway.
498  
499 =head1 Public Methods
500 dbinsert
501 dbconnect($)
502 Takes a hash ref, return a DBI database handle.
503  
504 dbselect($$)
505 Takes a DBI db handle and a MySQL query string.
506  
507 dbselect_arrayref($$)
508 Takes a DBI db handle and a MySQL query string.
509  
510 dbinsert
511 dbupdate($$$$)
512 Takes a DBI db handle, table name string, a hash ref of Columns to update including an "ID" column, and the "ID" column string
513  
514 dbdo($$)
515 Takes a DBI db handle and a SQL command string. This string is pretty much passed directly to $dbh->do
516  
517 where_struct($$$)
518 Takes a key, type and value argument.
519 Key is the column name or function in a where clause.
520 Type is the comparison, "LIKE", "IN", "=" etc.
521 Value is the value. "WHERE Key = Value."
522 Returns a reference to be used in "build_wheres"
523  
524 join_struct($$$)
525 Takes a table, tableid and joinid.
526 Table is the table to join to query.
527 Tableid is the key to join the table on. "JOIN table ON tableid = joinid."
528 Joinid is the key to join the table on. "JOIN table ON tableid = joinid."
529 Returns a reference to be used in "build_joins"
530  
531 build_joins($)
532 Takes a "join_struct" reference. Builds a LEFT JOIN query string.
533 OR
534 Takes a JOIN string directly including the "JOIN".
535 Returns a LEFT JOIN string to be passed to "build_*"
536  
537 build_wheres($)
538 Takes a reference to an array of "where_struct" references. Builds a WHERE query string.
539 OR
540 Takes a WHERE string directly not including the "WHERE".
541 Returns a WHERE string to be passed to "build_*"
542  
543 build_select($$$$$)
544  
545 build_insert($$)
546  
547 build_delete($$$)
548  
549 find_dup($$$$)
550  
551 =head1 AUTHOR
552  
553 Jacob Boswell
554  
555 =head1 COPYRIGHT
556  
557 DBD::mysql::SimpleMySQL is Copyright(c) 2004 Jacob Boswell. All rights reserved
558 You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
559  
560 =back
561  
562 =cut
563