1#!/usr/bin/perl 2 3package Goo::Database; 4 5############################################################################### 6# Nigel Hamilton 7# 8# Copyright Nigel Hamilton 2004 9# All Rights Reserved 10# 11# Author: Nigel Hamilton 12# Filename: Goo::Database.pm 13# Description: This script connects to an MySQL database and executes queries 14# 15# Date Change 16# ---------------------------------------------------------------------------- 17# 18/12/2004 Version 1 18# 19/08/2005 Added method: createDatabase 19# 18/10/2005 Added method: getPrimaryKey 20# 18/10/2005 Created test file: GooDatabaseTest.tpm 21# 18/10/2005 Added method: getTableColumns 22# 25/10/2005 Added method: getDate 23# 24############################################################################## 25 26use strict; 27 28use DBI; 29 30use Data::Dumper; 31 32our $dbh; 33 34# = get_connection(); 35 36#$dbh->{PrintError} = 0; # enable error checking via warn 37#$dbh->{RaiseError} = 0; # enable error checking via die 38 39 40############################################################################### 41# 42# get_connection - open a connection to the default database 43# 44############################################################################### 45 46sub get_connection { 47 48 # the location of the database is based on the user 49 # for example: nigel.db and sven.db 50 51 # return DBI->connect_cached("DBI:mysql:database=goo;host=64.246.0.61", 'goo', 'goo') 52 53 return DBI->connect_cached("DBI:mysql:database=goo;host=localhost", 'goo', 'goo') 54 or handle_error('Connect to local database failed.'); 55 56} 57 58 59############################################################################### 60# 61# do_sql - execute some sql 62# 63############################################################################### 64 65sub do_sql { 66 67 my ($querystring, $testing) = @_; 68 69 execute_sql($querystring, $testing); 70 71} 72 73 74############################################################################### 75# 76# do_query - execute sql and return the result all in one 77# 78############################################################################### 79 80sub do_query { 81 82 my ($querystring) = @_; 83 84 my $query = execute_sql($querystring); 85 86 return get_result_hash($query); 87 88} 89 90 91############################################################################### 92# 93# generate_numeric_sqlin_clause - return an sql 'in' clause with numeric values 94# 95############################################################################### 96 97sub generate_numeric_sqlin_clause { 98 99 my (@values) = @_; 100 101 my $inclause = join(",", @values); 102 103 return "(" . $inclause . ")"; 104 105} 106 107 108############################################################################### 109# 110# generate_string_sqlin_clause - return an sql 'in' clause with string values 111# 112############################################################################### 113 114sub generate_string_sqlin_clause { 115 116 my (@values) = @_; 117 118 my $inclause = join("','", @values); 119 120 return "('" . $inclause . "')"; 121 122} 123 124 125############################################################################### 126# 127# get_number_of_rows - return the number of rows for this statement handle 128# 129############################################################################### 130 131sub get_number_of_rows { 132 133 my ($sth) = @_; 134 return $sth->rows(); 135 136} 137 138 139############################################################################### 140# 141# get_next_row - alias for get_result_hash 142# 143############################################################################### 144 145sub get_next_row { 146 147 my ($sth) = @_; 148 149 return $sth->fetchrow_hashref(); 150} 151 152 153############################################################################### 154# 155# get_result_hash - return a hash for this result 156# 157############################################################################### 158 159sub get_result_hash { 160 161 my ($sth) = @_; 162 163 return $sth->fetchrow_hashref(); 164} 165 166 167############################################################################### 168# 169# bind_param - bind a parameter to a value 170# 171############################################################################### 172 173sub bind_param { 174 175 my ($sth, $param, $value) = @_; 176 177 $sth->bind_param($param, $value) || 178 handle_error("failed to bind parameter: $param = $value in $sth->{statement}", caller()); 179 180} 181 182 183############################################################################### 184# 185# prepare_sql - take a string and prepare the SQL 186# 187############################################################################### 188 189sub prepare_sql { 190 191 my ($querytext, $testmode) = @_; 192 193 if ($testmode) { print $querytext; } 194 195 unless ($dbh) { 196 $dbh = get_connection(); 197 } 198 199 my $sth = $dbh->prepare($querytext) || 200 handle_error("failed to prepare $querytext", caller()); 201 202 return $sth; 203 204} 205 206 207############################################################################### 208# 209# show_sql - display sql statement useful for debugging 210# 211############################################################################### 212 213sub show_sql { 214 215 my ($querytext) = @_; 216 217 print $querytext. "\n"; 218 219} 220 221 222############################################################################### 223# 224# execute_sql - take a string and execute the sql return a hash of column headings and values 225# 226############################################################################### 227 228sub execute_sql { 229 230 my ($querytext, $testmode) = @_; 231 232 if ($testmode) { print $querytext. "\n"; } 233 234 my $sth = prepare_sql($querytext) || 235 handle_error("error preparing $querytext", caller()); 236 237 # execute the query - if it fails pass to the error handler 238 $sth->execute() || handle_error("error executing $querytext", caller()); 239 240 return $sth; 241 242} 243 244 245############################################################################### 246# 247# execute - execute prepared statement 248# 249############################################################################### 250 251sub execute { 252 253 my ($sth) = @_; 254 255 # execute the query - if it fails pass to the errorHandler 256 $sth->execute() || 257 handle_error("error executing $sth->{statement}", caller()); 258 259 return $sth; 260 261} 262 263 264############################################################################### 265# 266# get_max - return the maximum value of a database column 267# 268############################################################################### 269 270sub get_max { 271 272 my ($column, $table) = @_; 273 274 my $row = do_query( <<EOSQL); 275 276 select max($column) as $column 277 from $table 278 279EOSQL 280 281 return $row->{$column}; 282 283} 284 285 286############################################################################### 287# 288# get_last_max - return the latest increment for this database handle 289# 290############################################################################### 291 292sub get_last_max { 293 294 my $row = do_query( <<EOSQL); 295 296 select last_insert_id() as lastmaxid 297 298EOSQL 299 300 return $row->{lastmaxid}; 301 302} 303 304 305############################################################################### 306# 307# count_rows_in_table - check if a value exists in a given column and table 308# 309############################################################################### 310 311sub count_rows_in_table { 312 313 my ($table, $column, $value) = @_; 314 315 my $query = prepare_sql( <<EOSQL); 316 317 select count(*) as rowcount 318 from $table 319 where $column = ? 320 321EOSQL 322 323 bind_param($query, 1, $value); 324 execute($query); 325 326 my $row = get_result_hash($query); 327 return $row->{rowcount}; 328 329} 330 331 332############################################################################### 333# 334# exists_in_table - check if a value exists in a given column and table 335# 336############################################################################### 337 338sub exists_in_table { 339 340 my ($table, $column, $value) = @_; 341 342 return count_rows_in_table($table, $column, $value) > 0; 343 344} 345 346 347############################################################################### 348# 349# get_row - return a row based on a key 350# 351############################################################################### 352 353sub get_row { 354 355 my ($table, $column, $value) = @_; 356 357 my $query = prepare_sql( <<EOSQL); 358 359 select * 360 from $table 361 where $column = ? 362 363EOSQL 364 365 bind_param($query, 1, $value); 366 execute($query); 367 return get_result_hash($query); 368 369} 370 371 372############################################################################### 373# 374# get_count - return a simple row in the table 375# 376############################################################################### 377 378sub get_count { 379 380 my ($table) = @_; 381 382 my $row = do_query("select count(*) as 'count' from $table"); 383 384 return $row->{count}; 385 386} 387 388 389############################################################################### 390# 391# delete_row - delete a row based on a key 392# 393############################################################################### 394 395sub delete_row { 396 397 my ($table, $column, $value) = @_; 398 399 my $query = prepare_sql( <<EOSQL); 400 401 delete 402 from $table 403 where $column = ? 404 405EOSQL 406 407 bind_param($query, 1, $value); 408 execute($query); 409 410} 411 412 413############################################################################### 414# 415# quote - quote a value for insertion into the database 416# 417############################################################################### 418 419sub quote { 420 421 my ($value) = @_; 422 423 unless ($dbh) { $dbh = get_connection(); } 424 425 return $dbh->quote($value); 426 427} 428 429 430############################################################################### 431# 432# handle_error - handle any error thrown by the dbi subsystem 433# 434############################################################################### 435 436sub handle_error { 437 438 my ($message, $calledby) = @_; 439 440 die("[$calledby] $message \n[DB says: $DBI::err $DBI::errstr $DBI::state]"); 441 442} 443 444 445############################################################################### 446# 447# get_primary_key - return the primary key for a table 448# 449############################################################################### 450 451sub get_primary_key { 452 453 my ($table) = @_; 454 455 my $query; 456 457 eval { $query = Goo::Database::execute_sql("describe $table"); }; 458 459 if ($@) { return ""; } 460 461 while (my $row = Goo::Database::get_result_hash($query)) { 462 463 if ($row->{Key} eq "PRI") { 464 return $row->{Field}; 465 } 466 467 } 468 469} 470 471 472############################################################################### 473# 474# get_table_columns - return a list of column names for the table 475# 476############################################################################### 477 478sub get_table_columns { 479 480 my ($table) = @_; 481 482 # mysql-centric command :-( 483 my $query = execute_sql("describe $table"); 484 485 my @columns; 486 487 while (my $row = get_result_hash($query)) { 488 push(@columns, $row->{Field}); 489 } 490 491 return @columns; 492 493} 494 495 496############################################################################### 497# 498# get_date - return the date and time according to the database 499# 500############################################################################### 501 502sub get_date { 503 504 my $query = execute_sql("select now() as 'now'"); 505 506 my $row = get_result_hash($query); 507 508 return $row->{now}; 509 510} 511 5121; 513 514 515__END__ 516 517=head1 NAME 518 519Goo::Database - Interface to a MySQL database via DBI 520 521=head1 SYNOPSIS 522 523use Goo::Database; 524 525=head1 DESCRIPTION 526 527Simple interface to DBI. 528 529=head1 METHODS 530 531=over 532 533=item get_connection 534 535open a connection to the default database 536 537=item do_sql 538 539execute some SQL 540 541=item do_query 542 543execute sql and return the result all in one 544 545=item generate_numeric_sqlin_clause 546 547return an SQL 'in' clause with numeric values 548 549=item generate_string_sqlin_clause 550 551return an SQL 'in' clause with string values 552 553=item get_number_of_rows 554 555return the number of rows for this statement handle 556 557=item get_next_row 558 559alias for get_result_hash 560 561=item get_result_hash 562 563return a hash for this query handle 564 565=item bind_param 566 567bind a parameter to a value 568 569=item prepare_sql 570 571take a string and prepare the SQL 572 573=item show_sql 574 575display the SQL statement useful for debugging 576 577=item execute_sql 578 579take a string, execute the SQL return a hash of column headings and values 580 581=item execute 582 583execute prepared statement 584 585=item get_max 586 587return the maximum value of a database column 588 589=item get_last_max 590 591return the latest increment for this database handle 592 593=item count_rows_in_table 594 595check if a value exists in a given column and table 596 597=item exists_in_table 598 599check if a value exists in a given column and table 600 601=item get_row 602 603return a row based on a key 604 605=item get_count 606 607return a simple row in the table 608 609=item delete_row 610 611delete a row based on a key 612 613=item quote 614 615quote a value for insertion into the database 616 617=item handle_error 618 619handle any error thrown by the DBI subsystem 620 621=item get_primary_key 622 623return the primary key for a table 624 625=item get_table_columns 626 627return a list of column names for the table 628 629=item get_date 630 631return the date and time according to the database 632 633=back 634 635=head1 AUTHOR 636 637Nigel Hamilton <nigel@trexy.com> 638 639=head1 SEE ALSO 640 641