| 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 |