1# NAME 2 3POE::Component::EasyDBI - Perl extension for asynchronous non-blocking DBI calls in POE 4 5# SYNOPSIS 6 7 use POE qw(Component::EasyDBI); 8 9 # Set up the DBI 10 POE::Component::EasyDBI->spawn( # or new(), witch returns an obj 11 alias => 'EasyDBI', 12 dsn => 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306', 13 username => 'user', 14 password => 'pass', 15 options => { 16 AutoCommit => 0, 17 }, 18 ); 19 20 # Create our own session to communicate with EasyDBI 21 POE::Session->create( 22 inline_states => { 23 _start => sub { 24 $_[KERNEL]->post('EasyDBI', 25 do => { 26 sql => 'CREATE TABLE users (id INT, username VARCHAR(100))', 27 event => 'table_created', 28 } 29 ); 30 }, 31 table_created => sub { 32 $_[KERNEL]->post('EasyDBI', 33 insert => { 34 # multiple inserts 35 insert => [ 36 { id => 1, username => 'foo' }, 37 { id => 2, username => 'bar' }, 38 { id => 3, username => 'baz' }, 39 ], 40 table => 'users', 41 event => 'done', 42 }, 43 ); 44 $_[KERNEL]->post('EasyDBI', 45 commit => { 46 event => 'done' 47 } 48 ); 49 $_[KERNEL]->post('EasyDBI' => 'shutdown'); 50 }, 51 done => sub { 52 my $result = $_[ARG0]; 53 } 54 }, 55 ); 56 57 POE::Kernel->run(); 58 59# ABSTRACT 60 61 This module simplifies DBI usage in POE's multitasking world. 62 63 This module is easy to use, you'll have DBI calls in your POE program 64 up and running in no time. 65 66 It also works in Windows environments! 67 68# DESCRIPTION 69 70This module works by creating a new session, then spawning a child process 71to do the DBI queries. That way, your main POE process can continue servicing 72other clients. 73 74The standard way to use this module is to do this: 75 76 use POE; 77 use POE::Component::EasyDBI; 78 79 POE::Component::EasyDBI->spawn(...); 80 81 POE::Session->create(...); 82 83 POE::Kernel->run(); 84 85## Starting EasyDBI 86 87To start EasyDBI, just call it's spawn method. (or new for an obj) 88 89This one is for Postgresql: 90 91 POE::Component::EasyDBI->spawn( 92 alias => 'EasyDBI', 93 dsn => 'DBI:Pg:dbname=test;host=10.0.1.20', 94 username => 'user', 95 password => 'pass', 96 ); 97 98This one is for mysql: 99 100 POE::Component::EasyDBI->spawn( 101 alias => 'EasyDBI', 102 dsn => 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306', 103 username => 'user', 104 password => 'pass', 105 ); 106 107This method will die on error or return success. 108 109Note the difference between dbname and database, that is dependant on the 110driver used, NOT EasyDBI 111 112NOTE: If the SubProcess could not connect to the DB, it will return an error, 113causing EasyDBI to croak/die. 114 115NOTE: Starting with version .10, I've changed new() to return a EasyDBI object 116and spawn() returns a session reference. Also, create() is the same as spawn(). 117See ["OBJECT METHODS"](#object-methods). 118 119This constructor accepts 6 different options. 120 121- `alias` 122 123 This will set the alias EasyDBI uses in the POE Kernel. 124 This will default TO "EasyDBI" if undef 125 126 If you do not want to use aliases, specify '' as the ailas. This helps when 127 spawning many EasyDBI objects. See ["OBJECT METHODS"](#object-methods). 128 129- `dsn` 130 131 This is the DSN (Database connection string) 132 133 EasyDBI expects this to contain everything you need to connect to a database 134 via DBI, without the username and password. 135 136 For valid DSN strings, contact your DBI driver's manual. 137 138- `username` 139 140 This is the DB username EasyDBI will use when making the call to connect 141 142- `password` 143 144 This is the DB password EasyDBI will use when making the call to connect 145 146- `options` 147 148 Pass a hash ref that normally would be after the $password param on a 149 DBI->connect call. 150 151- `max_retries` 152 153 This is the max number of times the database wheel will be restarted, default 154 is 5. Set this to -1 to retry forever. 155 156- `ping_timeout` 157 158 Optional. This is the timeout to ping the database handle. If set to 0 the 159 database will be pinged before every query. The default is 0. 160 161- `no_connect_failures` 162 163 Optional. If set to a true value, the connect\_error event will be valid, but not 164 necessary. If set to a false value, then connection errors will be fatal. 165 166- `connect_error` 167 168 Optional. Supply a array ref of session\_id or alias and an event. Any connect 169 errors will be posted to this session and event with the query that failed as 170 ARG0 or an empty hash ref if no query was in the queue. The query will be 171 retried, so DON'T resend the query. If this parameter is not supplied, the 172 normal behavour will be to drop the subprocess and restart [max\_retries](https://metacpan.org/pod/max_retries) times. 173 174- `reconnect_wait` 175 176 Optional. Defaults to 2 seconds. After a connection failure this is the time 177 to wait until another connection is attempted. Setting this to 0 would not 178 be good for your cpu load. 179 180- `connected` 181 182 Optional. Supply a array ref of session\_id or alias and an event. When 183 the component makes a successful connection this event will be called 184 with the next query as ARG0 or an empty hash ref if no queries are in the queue. 185 DON'T resend the query, it will be processed. 186 187- `no_cache` 188 189 Optional. If true, prepare\_cached won't be called on queries. Use this when 190 using [DBD::AnyData](https://metacpan.org/pod/DBD::AnyData). This can be overridden with each query. 191 192- `alt_fork` 193 194 Optional. If 1, an alternate type of fork will be used for the database 195 process. This usually results in lower memory use of the child. 196 You can also specify alt\_fork => '/path/to/perl' if you are using POE inside of 197 another app like irssi. 198 \*Experimental, and WILL NOT work on Windows Platforms\* 199 200- `stopwatch` 201 202 Optional. If true, [Time::Stopwatch](https://metacpan.org/pod/Time::Stopwatch) will be loaded and tied to the 'stopwatch' 203 key on every query. Check the stopwatch key in the return event to measure how 204 long a query took. 205 206## Events 207 208There is only a few events you can trigger in EasyDBI. 209They all share a common argument format, except for the shutdown event. 210 211Note: you can change the session that the query posts back to, it uses $\_\[SENDER\] 212as the default. 213 214You can use a postback, or callback (See POE::Session) 215 216For example: 217 218 $kernel->post('EasyDBI', 219 quote => { 220 sql => 'foo$*@%%sdkf"""', 221 event => 'quoted_handler', 222 session => 'dbi_helper', # or session id 223 } 224 ); 225 226or 227 228 $kernel->post('EasyDBI', 229 quote => { 230 sql => 'foo$*@%%sdkf"""', 231 event => $_[SESSION]->postback("quoted_handler"), 232 session => 'dbi_helper', # or session id 233 } 234 ); 235 236- `quote` 237 238 This sends off a string to be quoted, and gets it back. 239 240 Internally, it does this: 241 242 return $dbh->quote($SQL); 243 244 Here's an example on how to trigger this event: 245 246 $kernel->post('EasyDBI', 247 quote => { 248 sql => 'foo$*@%%sdkf"""', 249 event => 'quoted_handler', 250 } 251 ); 252 253 The Success Event handler will get a hash ref in ARG0: 254 { 255 sql => Unquoted SQL sent 256 result => Quoted SQL 257 } 258 259- `do` 260 261 This query is for those queries where you UPDATE/DELETE/etc. 262 263 Internally, it does this: 264 265 $sth = $dbh->prepare_cached($sql); 266 $rows_affected = $sth->execute($placeholders); 267 return $rows_affected; 268 269 Here's an example on how to trigger this event: 270 271 $kernel->post('EasyDBI', 272 do => { 273 sql => 'DELETE FROM FooTable WHERE ID = ?', 274 placeholders => [qw(38)], 275 event => 'deleted_handler', 276 } 277 ); 278 279 The Success Event handler will get a hash in ARG0: 280 { 281 sql => SQL sent 282 result => Scalar value of rows affected 283 rows => Same as result 284 placeholders => Original placeholders 285 } 286 287- `single` 288 289 This query is for those queries where you will get exactly one row and 290 column back. 291 292 Internally, it does this: 293 294 $sth = $dbh->prepare_cached($sql); 295 $sth->bind_columns(%result); 296 $sth->execute($placeholders); 297 $sth->fetch(); 298 return %result; 299 300 Here's an example on how to trigger this event: 301 302 $kernel->post('EasyDBI', 303 single => { 304 sql => 'Select test_id from FooTable', 305 event => 'result_handler', 306 } 307 ); 308 309 The Success Event handler will get a hash in ARG0: 310 { 311 sql => SQL sent 312 result => scalar 313 placeholders => Original placeholders 314 } 315 316- `arrayhash` 317 318 This query is for those queries where you will get more than one row and 319 column back. Also see arrayarray 320 321 Internally, it does this: 322 323 $sth = $dbh->prepare_cached($SQL); 324 $sth->execute($PLACEHOLDERS); 325 while ($row = $sth->fetchrow_hashref()) { 326 push( @results,{ %{ $row } } ); 327 } 328 return @results; 329 330 Here's an example on how to trigger this event: 331 332 $kernel->post('EasyDBI', 333 arrayhash => { 334 sql => 'SELECT this, that FROM my_table WHERE my_id = ?', 335 event => 'result_handler', 336 placeholders => [qw(2021)], 337 } 338 ); 339 340 The Success Event handler will get a hash in ARG0: 341 { 342 sql => SQL sent 343 result => Array of hashes of the rows (array of fetchrow_hashref's) 344 rows => Scalar value of rows 345 placeholders => Original placeholders 346 cols => An array of the cols in query order 347 } 348 349- `hashhash` 350 351 This query is for those queries where you will get more than one row and 352 column back. 353 354 The primary_key should be UNIQUE! If it is not, then use hasharray instead. 355 356 Internally, it does something like this: 357 358 if ($primary_key =~ m/^\d+$/) { 359 if ($primary_key} > $sth->{NUM_OF_FIELDS}) { 360 die "primary_key is out of bounds"; 361 } 362 $primary_key = $sth->{NAME}->[($primary_key-1)]; 363 } 364 365 for $i (0..$sth->{NUM_OF_FIELDS}-1) { 366 $col{$sth->{NAME}->[$i]} = $i; 367 push(@cols, $sth->{NAME}->[$i]); 368 } 369 370 $sth = $dbh->prepare_cached($SQL); 371 $sth->execute($PLACEHOLDERS); 372 while (@row = $sth->fetch_array()) { 373 foreach $c (@cols) { 374 $results{$row[$col{$primary_key}]}{$c} = $row[$col{$c}]; 375 } 376 } 377 return %results; 378 379 Here's an example on how to trigger this event: 380 381 $kernel->post('EasyDBI', 382 hashhash => { 383 sql => 'SELECT this, that FROM my_table WHERE my_id = ?', 384 event => 'result_handler', 385 placeholders => [qw(2021)], 386 primary_key => "2", # making 'that' the primary key 387 } 388 ); 389 390 The Success Event handler will get a hash in ARG0: 391 { 392 sql => SQL sent 393 result => Hashes of hashes of the rows 394 rows => Scalar value of rows 395 placeholders => Original placeholders 396 cols => An array of the cols in query order 397 } 398 399- `hasharray` 400 401 This query is for those queries where you will get more than one row 402 and column back. 403 404 Internally, it does something like this: 405 406 # find the primary key 407 if ($primary_key =~ m/^\d+$/) { 408 if ($primary_key} > $sth->{NUM_OF_FIELDS}) { 409 die "primary_key is out of bounds"; 410 } 411 $primary_key = $sth->{NAME}->[($primary_key-1)]; 412 } 413 414 for $i (0..$sth->{NUM_OF_FIELDS}-1) { 415 $col{$sth->{NAME}->[$i]} = $i; 416 push(@cols, $sth->{NAME}->[$i]); 417 } 418 419 $sth = $dbh->prepare_cached($SQL); 420 $sth->execute($PLACEHOLDERS); 421 while (@row = $sth->fetch_array()) { 422 push(@{ $results{$row[$col{$primary_key}}]} }, @row); 423 } 424 return %results; 425 426 Here's an example on how to trigger this event: 427 428 $kernel->post('EasyDBI', 429 hasharray => { 430 sql => 'SELECT this, that FROM my_table WHERE my_id = ?', 431 event => 'result_handler', 432 placeholders => [qw(2021)], 433 primary_key => "1", # making 'this' the primary key 434 } 435 ); 436 437 The Success Event handler will get a hash in ARG0: 438 { 439 sql => SQL sent 440 result => Hashes of hashes of the rows 441 rows => Scalar value of rows 442 placeholders => Original placeholders 443 primary_key => 'this' # the column name for the number passed in 444 cols => An array of the cols in query order 445 } 446 447- `array` 448 449 This query is for those queries where you will get more than one row with 450 one column back. (or joined columns) 451 452 Internally, it does this: 453 454 $sth = $dbh->prepare_cached($SQL); 455 $sth->execute($PLACEHOLDERS); 456 while (@row = $sth->fetchrow_array()) { 457 if ($separator) { 458 push(@results, join($separator,@row)); 459 } else { 460 push(@results, join(',',@row)); 461 } 462 } 463 return @results; 464 465 Here's an example on how to trigger this event: 466 467 $kernel->post('EasyDBI', 468 array => { 469 sql => 'SELECT this FROM my_table WHERE my_id = ?', 470 event => 'result_handler', 471 placeholders => [qw(2021)], 472 separator => ',', # default separator 473 } 474 ); 475 476 The Success Event handler will get a hash in ARG0: 477 { 478 sql => SQL sent 479 result => Array of scalars (joined with separator if more 480 than one column is returned) 481 rows => Scalar value of rows 482 placeholders => Original placeholders 483 } 484 485- `arrayarray` 486 487 This query is for those queries where you will get more than one row and 488 column back. Also see arrayhash 489 490 Internally, it does this: 491 492 $sth = $dbh->prepare_cached($SQL); 493 $sth->execute($PLACEHOLDERS); 494 while (@row = $sth->fetchrow_array()) { 495 push( @results,\@row ); 496 } 497 return @results; 498 499 Here's an example on how to trigger this event: 500 501 $kernel->post('EasyDBI', 502 arrayarray => { 503 sql => 'SELECT this,that FROM my_table WHERE my_id > ?', 504 event => 'result_handler', 505 placeholders => [qw(2021)], 506 } 507 ); 508 509 The Success Event handler will get a hash in ARG0: 510 { 511 sql => SQL sent 512 result => Array of array refs 513 rows => Scalar value of rows 514 placeholders => Original placeholders 515 } 516 517- `hash` 518 519 This query is for those queries where you will get one row with more than 520 one column back. 521 522 Internally, it does this: 523 524 $sth = $dbh->prepare_cached($SQL); 525 $sth->execute($PLACEHOLDERS); 526 @row = $sth->fetchrow_array(); 527 if (@row) { 528 for $i (0..$sth->{NUM_OF_FIELDS}-1) { 529 $results{$sth->{NAME}->[$i]} = $row[$i]; 530 } 531 } 532 return %results; 533 534 Here's an example on how to trigger this event: 535 536 $kernel->post('EasyDBI', 537 hash => { 538 sql => 'SELECT * FROM my_table WHERE my_id = ?', 539 event => 'result_handler', 540 placeholders => [qw(2021)], 541 } 542 ); 543 544 The Success Event handler will get a hash in ARG0: 545 { 546 sql => SQL sent 547 result => Hash 548 rows => Scalar value of rows 549 placeholders => Original placeholders 550 } 551 552- `keyvalhash` 553 554 This query is for those queries where you will get one row with more than 555 one column back. 556 557 Internally, it does this: 558 559 $sth = $dbh->prepare_cached($SQL); 560 $sth->execute($PLACEHOLDERS); 561 while (@row = $sth->fetchrow_array()) { 562 $results{$row[0]} = $row[1]; 563 } 564 return %results; 565 566 Here's an example on how to trigger this event: 567 568 $kernel->post('EasyDBI', 569 keyvalhash => { 570 sql => 'SELECT this, that FROM my_table WHERE my_id = ?', 571 event => 'result_handler', 572 placeholders => [qw(2021)], 573 primary_key => 1, # uses 'this' as the key 574 } 575 ); 576 577 The Success Event handler will get a hash in ARG0: 578 { 579 sql => SQL sent 580 result => Hash 581 rows => Scalar value of rows 582 placeholders => Original placeholders 583 } 584 585- `insert` 586 587 This is for inserting rows. 588 589 Here's an example on how to trigger this event: 590 591 $_[KERNEL]->post('EasyDBI', 592 insert => { 593 sql => 'INSERT INTO zipcodes (zip,city,state) VALUES (?,?,?)', 594 placeholders => ['98004', 'Bellevue', 'WA'], 595 event => 'insert_handler', 596 } 597 ); 598 599 a multiple insert: 600 601 $_[KERNEL]->post('EasyDBI', 602 insert => { 603 insert => [ 604 { id => 1, username => 'foo' }, 605 { id => 2, username => 'bar' }, 606 { id => 3, username => 'baz' }, 607 ], 608 table => 'users', 609 event => 'insert_handler', 610 }, 611 ); 612 613 also an example to retrieve a last insert id 614 615 $_[KERNEL]->post('EasyDBI', 616 insert => { 617 hash => { username => 'test', pass => 'sUpErSeCrEt', name => 'John' }, 618 table => 'users', 619 last_insert_id => { 620 field => 'user_id', # mysql uses SELECT LAST_INSERT_ID instead 621 table => 'users', # of these values, just specify {} for mysql 622 }, 623 # or last_insert_id can be => 'SELECT LAST_INSERT_ID()' or some other 624 # query that will return a value 625 }, 626 ); 627 628 The Success Event handler will get a hash in ARG0: 629 { 630 action => insert 631 event => result_handler 632 id => queue id 633 insert => original multiple insert hash reference 634 insert_id => insert id if last_insert_id is used 635 last_insert_id => the original hash or scalar sent 636 placeholders => original placeholders 637 rows => number of rows affected 638 result => same as rows 639 sql => SQL sent 640 table => table from insert 641 } 642 643- `combo` 644 645 This is for combining multiple SQL statements in one call. 646 647 Here's an example of how to trigger this event: 648 649 $_[KERNEL]->post('EasyDBI', 650 combo => { 651 queries => [ 652 { 653 do => { 654 sql => 'CREATE TABLE test (id INT, foo TEXT, bar TEXT)', 655 } 656 }, 657 { 658 insert => { 659 table => 'test', 660 insert => [ 661 { id => 1, foo => 123456, bar => 'a quick brown fox' }, 662 { id => 2, foo => 7891011, bar => time() }, 663 ], 664 }, 665 }, 666 { 667 insert => { 668 table => 'test', 669 hash => { id => 2, foo => 7891011, bar => time() }, 670 }, 671 }, 672 ], 673 event => 'combo_handler', 674 } 675 ); 676 677 The Success Event handler will get a hash for each of the queries in 678 ARG0..$#. See the respective hash structure for each of the single events. 679 680- `func` 681 682 This is for calling $dbh->func(), when using a driver that supports it. 683 684 Internally, it does this: 685 686 return $dbh->func(@{$args}); 687 688 Here's an example on how to trigger this event (Using DBD::AnyData): 689 690 $kernel->post('EasyDBI', 691 func => { 692 args => ['test2','CSV',["id,phrase\n1,foo\n2,bar"],'ad_import'], 693 event => 'result_handler', 694 } 695 ); 696 697 The Success Event handler will get a hash in ARG0: 698 { 699 sql => SQL sent 700 result => return value 701 } 702 703- `method` 704 705 This is for calling any method on the $dbh, 706 707 Internally, it does this: 708 709 return $dbh->{method}(@{$args}); 710 711 Here's an example on how to trigger this event (Using DBD::SQLite): 712 713 $kernel->post('EasyDBI', 714 method => { 715 method => 'sqlite_table_column_metadata' 716 args => [undef, 'users', 'username'], 717 event => 'result_handler', 718 } 719 ); 720 721 The Success Event handler will get a hash in ARG0: 722 { 723 action => method 724 args => original array reference containing the arguments 725 event => result_handler 726 id => queue id 727 method => sqlite_table_column_metadata 728 result => return value 729 session => session id 730 } 731 732- `commit` 733 734 This is for calling $dbh->commit(), if the driver supports it. 735 736 Internally, it does this: 737 738 return $dbh->commit(); 739 740 Here's an example on how to trigger this event: 741 742 $kernel->post('EasyDBI', 743 commit => { 744 event => 'result_handler', 745 } 746 ); 747 748 The Success Event handler will get a hash in ARG0: 749 { 750 action => commit 751 event => result_handler 752 id => queue id 753 result => return value 754 session => session id 755 } 756 757- `rollback` 758 759 This is for calling $dbh->rollback(), if the driver supports it. 760 761 Internally, it does this: 762 763 return $dbh->rollback(); 764 765 Here's an example on how to trigger this event: 766 767 $kernel->post('EasyDBI', 768 rollback => { 769 event => 'result_handler', 770 } 771 ); 772 773 The Success Event handler will get a hash in ARG0: 774 { 775 action => rollback 776 event => result_handler 777 id => queue id 778 result => return value 779 session => session id 780 } 781 782- `begin_work` 783 784 This is for calling $dbh->begin_work(), if the driver supports it. 785 786 Internally, it does this: 787 788 return $dbh->begin_work(); 789 790 Here's an example on how to trigger this event: 791 792 $kernel->post('EasyDBI', 793 begin_work => { 794 event => 'result_handler', 795 } 796 ); 797 798 The Success Event handler will get a hash in ARG0: 799 { 800 action => begin_work 801 event => result_handler 802 id => queue id 803 result => return value 804 session => session id 805 } 806 807- `shutdown` 808 809 $kernel->post('EasyDBI', 'shutdown'); 810 811 This will signal EasyDBI to start the shutdown procedure. 812 813 NOTE: This will let all outstanding queries run! 814 EasyDBI will kill it's session when all the queries have been processed. 815 816 you can also specify an argument: 817 818 $kernel->post('EasyDBI', 'shutdown' => 'NOW'); 819 820 This will signal EasyDBI to shutdown. 821 822 NOTE: This will NOT let the outstanding queries finish! 823 Any queries running will be lost! 824 825 Due to the way POE's queue works, this shutdown event will take some time 826 to propagate POE's queue. If you REALLY want to shut down immediately, do 827 this: 828 829 $kernel->call('EasyDBI', 'shutdown' => 'NOW'); 830 831 ALL shutdown NOW's send kill 9 to thier children, beware of any 832 transactions that you may be in. Your queries will revert if you are in 833 transaction mode 834 835### Arguments 836 837They are passed in via the $kernel->post(...); 838 839Note: all query types can be in ALL-CAPS or lowercase but not MiXeD! 840 841ie ARRAYHASH or arrayhash but not ArrayHash 842 843- `sql` 844 845 This is the actual SQL line you want EasyDBI to execute. 846 You can put in placeholders, this module supports them. 847 848- `placeholders` 849 850 This is an array of placeholders. 851 852 You can skip this if your query does not use placeholders in it. 853 854- `event` 855 856 This is the success/failure event, triggered whenever a query finished 857 successfully or not. 858 859 It will get a hash in ARG0, consult the specific queries on what you will get. 860 861 \*\*\*\*\* NOTE \*\*\*\*\* 862 863 In the case of an error, the key 'error' will have the specific error that 864 occurred. Always, always, \_always\_ check for this in this event. 865 866 \*\*\*\*\* NOTE \*\*\*\*\* 867 868- `separator` 869 870 Query types single, and array accept this parameter. 871 The default is a comma (,) and is optional 872 873 If a query has more than one column returned, the columns are joined with 874 the 'separator'. 875 876- `primary_key` 877 878 Query types hashhash, and hasharray accept this parameter. 879 It is used to key the hash on a certain field 880 881- `chunked` 882 883 All multi-row queries can be chunked. 884 885 You can pass the parameter 'chunked' with a number of rows to fire the 'event' 886 event for every 'chunked' rows, it will fire the 'event' event. (a 'chunked' 887 key will exist) A 'last\_chunk' key will exist when you have received the last 888 chunk of data from the query 889 890- `last_insert_id` 891 892 See the insert event for a example of its use. 893 894- `begin_work` 895 896 Optional. Works with all queries. You should have AutoCommit => 0 set on 897 connect. 898 899- `commit` 900 901 Optional. After a successful 'do' or 'insert', a commit is performed. 902 ONLY used when using `do` or `insert` 903 904- (arbitrary data) 905 906 You can pass custom keys and data not mentioned above, BUT I suggest using a 907 prefix like \_ in front of your custom keys. For example: 908 909 $_[KERNEL->post('EasyDBI', 910 do => { 911 sql => 'DELETE FROM sessions WHERE ip = ?', 912 placeholders => [$ip], 913 _ip => $ip, 914 _port => $port, 915 _filehandle => $fh, 916 } 917 ); 918 919 If I were to add an option 'filehandle' (for importing data from a file for 920 instance) you don't want an upgrade to produce BAD results. 921 922## OBJECT METHODS 923 924When using new() to spawn/create the EasyDBI object, you can use the methods 925listed below 926 927NOTE: The object interface will be improved in later versions, please send 928suggestions to the author. 929 930- `ID` 931 932 This retrieves the session ID. When managing a pool of EasyDBI objects, you 933 can set the alias to '' (nothing) and retrieve the session ID in this manner. 934 935 $self->ID() 936 937- `commit, rollback, begin_work, func, method, insert, do, single, quote, arrayhash, hashhash, hasharray, array, arrayarray, hash, keyvalhash, combo, shutdown` 938 939 All query types are now supported as object methods. For example: 940 941 $self->arrayhash( 942 sql => 'SELECT user_id,user_login from users where logins = ?', 943 event => 'arrayash_handler', 944 placeholders => [ qw( 53 ) ], 945 ); 946 947- `DESTROY` 948 949 This will shutdown EasyDBI. 950 951 $self->DESTROY() 952 953## LONG EXAMPLE 954 955 use POE qw(Component::EasyDBI); 956 957 # Set up the DBI 958 POE::Component::EasyDBI->spawn( 959 alias => 'EasyDBI', 960 dsn => 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306', 961 username => 'user', 962 password => 'pass', 963 ); 964 965 # Create our own session to communicate with EasyDBI 966 POE::Session->create( 967 inline_states => { 968 _start => sub { 969 $_[KERNEL]->post('EasyDBI', 970 do => { 971 sql => 'DELETE FROM users WHERE user_id = ?', 972 placeholders => [qw(144)], 973 event => 'deleted_handler', 974 } 975 ); 976 977 # 'single' is very different from the single query in SimpleDBI 978 # look at 'hash' to get those results 979 980 # If you select more than one field, you will only get the last one 981 # unless you pass in a separator with what you want the fields seperated by 982 # to get null sperated values, pass in separator => "\0" 983 $_[KERNEL]->post('EasyDBI', 984 single => { 985 sql => 'Select user_id,user_login from users where user_id = ?', 986 event => 'single_handler', 987 placeholders => [qw(144)], 988 separator => ',', #optional! 989 } 990 ); 991 992 $_[KERNEL]->post('EasyDBI', 993 quote => { 994 sql => 'foo$*@%%sdkf"""', 995 event => 'quote_handler', 996 } 997 ); 998 999 $_[KERNEL]->post('EasyDBI', 1000 arrayhash => { 1001 sql => 'SELECT user_id,user_login from users where logins = ?', 1002 event => 'arrayash_handler', 1003 placeholders => [qw(53)], 1004 } 1005 ); 1006 1007 my $postback = $_[SESSION]->postback("arrayhash_handler",3,2,1); 1008 1009 $_[KERNEL]->post('EasyDBI', 1010 arrayhash => { 1011 sql => 'SELECT user_id,user_login from users', 1012 event => $postback, 1013 } 1014 ); 1015 1016 $_[KERNEL]->post('EasyDBI', 1017 arrayarray => { 1018 sql => 'SELECT * from locations', 1019 event => 'arrayarray_handler', 1020 primary_key => '1', # you can specify a primary key, or a number based on what column to use 1021 } 1022 ); 1023 1024 $_[KERNEL]->post('EasyDBI', 1025 hashhash => { 1026 sql => 'SELECT * from locations', 1027 event => 'hashhash_handler', 1028 primary_key => '1', # you can specify a primary key, or a number based on what column to use 1029 } 1030 ); 1031 1032 $_[KERNEL]->post('EasyDBI', 1033 hasharray => { 1034 sql => 'SELECT * from locations', 1035 event => 'hasharray_handler', 1036 primary_key => "1", 1037 } 1038 ); 1039 1040 # you should use limit 1, it is NOT automaticly added 1041 $_[KERNEL]->post('EasyDBI', 1042 hash => { 1043 sql => 'SELECT * from locations LIMIT 1', 1044 event => 'hash_handler', 1045 } 1046 ); 1047 1048 $_[KERNEL]->post('EasyDBI', 1049 array => { 1050 sql => 'SELECT location_id from locations', 1051 event => 'array_handler', 1052 } 1053 ); 1054 1055 $_[KERNEL]->post('EasyDBI', 1056 keyvalhash => { 1057 sql => 'SELECT location_id,location_name from locations', 1058 event => 'keyvalhash_handler', 1059 # if primary_key isn't used, the first one is assumed 1060 } 1061 ); 1062 1063 $_[KERNEL]->post('EasyDBI', 1064 insert => { 1065 sql => 'INSERT INTO zipcodes (zip,city,state) VALUES (?,?,?)', 1066 placeholders => ['98004', 'Bellevue', 'WA'], 1067 event => 'insert_handler', 1068 } 1069 ); 1070 1071 $_[KERNEL]->post('EasyDBI', 1072 insert => { 1073 # this can also be a array of hashes similar to this 1074 hash => { username => 'test' , pass => 'sUpErSeCrEt', name => 'John' }, 1075 table => 'users', 1076 last_insert_id => { 1077 field => 'user_id', # mysql uses SELECT LAST_INSERT_ID instead 1078 table => 'users', # of these values, just specify {} for mysql 1079 }, 1080 event => 'insert_handler', 1081 # or last_insert_id can be => 'SELECT LAST_INSERT_ID()' or some other 1082 # query that will return a value 1083 }, 1084 ); 1085 1086 # 3 ways to shutdown 1087 1088 # This will let the existing queries finish, then shutdown 1089 $_[KERNEL]->post('EasyDBI', 'shutdown'); 1090 1091 # This will terminate when the event traverses 1092 # POE's queue and arrives at EasyDBI 1093 #$_[KERNEL]->post('EasyDBI', shutdown => 'NOW'); 1094 1095 # Even QUICKER shutdown :) 1096 #$_[KERNEL]->call('EasyDBI', shutdown => 'NOW'); 1097 }, 1098 1099 deleted_handler => \&deleted_handler, 1100 quote_handler => \"e_handler, 1101 arrayhash_handler => \&arrayhash_handler, 1102 }, 1103 ); 1104 1105 sub quote_handler { 1106 # For QUOTE calls, we receive the scalar string of SQL quoted 1107 # $_[ARG0] = { 1108 # sql => The SQL you sent 1109 # result => scalar quoted SQL 1110 # placeholders => The placeholders 1111 # action => 'QUOTE' 1112 # error => Error occurred, check this first 1113 # } 1114 } 1115 1116 sub deleted_handler { 1117 # For DO calls, we receive the scalar value of rows affected 1118 # $_[ARG0] = { 1119 # sql => The SQL you sent 1120 # result => scalar value of rows affected 1121 # placeholders => The placeholders 1122 # action => 'do' 1123 # error => Error occurred, check this first 1124 # } 1125 } 1126 1127 sub single_handler { 1128 # For SINGLE calls, we receive a scalar 1129 # $_[ARG0] = { 1130 # sql => The SQL you sent 1131 # result => scalar 1132 # placeholders => The placeholders 1133 # action => 'single' 1134 # separator => Seperator you may have sent 1135 # error => Error occurred, check this first 1136 # } 1137 } 1138 1139 sub arrayhash_handler { 1140 # For arrayhash calls, we receive an array of hashes 1141 # $_[ARG0] = { 1142 # sql => The SQL you sent 1143 # result => array of hash refs 1144 # placeholders => The placeholders 1145 # action => 'arrayhash' 1146 # error => Error occurred, check this first 1147 # } 1148 } 1149 1150 sub hashhash_handler { 1151 # For hashhash calls, we receive a hash of hashes 1152 # $_[ARG0] = { 1153 # sql => The SQL you sent 1154 # result => hash ref of hash refs keyed on primary key 1155 # placeholders => The placeholders 1156 # action => 'hashhash' 1157 # cols => array of columns in order (to help recreate the sql order) 1158 # primary_key => column you specified as primary key, if you specifed 1159 # a number, the real column name will be here 1160 # error => Error occurred, check this first 1161 # } 1162 } 1163 1164 sub hasharray_handler { 1165 # For hasharray calls, we receive an hash of arrays 1166 # $_[ARG0] = { 1167 # sql => The SQL you sent 1168 # result => hash ref of array refs keyed on primary key 1169 # placeholders => The placeholders 1170 # action => 'hashhash' 1171 # cols => array of columns in order (to help recreate the sql order) 1172 # primary_key => column you specified as primary key, if you specifed 1173 # a number, the real column name will be here 1174 # error => Error occurred, check this first 1175 # } 1176 } 1177 1178 sub array_handler { 1179 # For array calls, we receive an array 1180 # $_[ARG0] = { 1181 # sql => The SQL you sent 1182 # result => an array, if multiple fields are used, they are comma 1183 # seperated (specify separator in event call to change this) 1184 # placeholders => The placeholders 1185 # action => 'array' 1186 # separator => you sent # optional! 1187 # error => Error occurred, check this first 1188 # } 1189 } 1190 1191 sub arrayarray_handler { 1192 # For array calls, we receive an array ref of array refs 1193 # $_[ARG0] = { 1194 # sql => The SQL you sent 1195 # result => an array ref of array refs 1196 # placeholders => The placeholders 1197 # action => 'arrayarray' 1198 # error => Error occurred, check this first 1199 # } 1200 } 1201 1202 sub hash_handler { 1203 # For hash calls, we receive a hash 1204 # $_[ARG0] = { 1205 # sql => The SQL you sent 1206 # result => a hash 1207 # placeholders => The placeholders 1208 # action => 'hash' 1209 # error => Error occurred, check this first 1210 # } 1211 } 1212 1213 sub keyvalhash_handler { 1214 # For keyvalhash calls, we receive a hash 1215 # $_[ARG0] = { 1216 # sql => The SQL you sent 1217 # result => a hash # first field is the key, second is the value 1218 # placeholders => The placeholders 1219 # action => 'keyvalhash' 1220 # error => Error occurred, check this first 1221 # primary_key => primary key used 1222 # } 1223 } 1224 1225 sub insert_handle { 1226 # $_[ARG0] = { 1227 # sql => The SQL you sent 1228 # placeholders => The placeholders 1229 # action => 'insert' 1230 # table => 'users', 1231 # # for postgresql, or others? 1232 # last_insert_id => { # used to retrieve the insert id of the inserted row 1233 # field => The field of id requested 1234 # table => The table the holds the field 1235 # }, 1236 # -OR- 1237 # last_insert_id => 'SELECT LAST_INSERT_ID()', # mysql style 1238 # result => the id from the last_insert_id post query 1239 # error => Error occurred, check this first 1240 # } 1241 } 1242 1243## EasyDBI Notes 1244 1245This module is very picky about capitalization! 1246 1247All of the options are in lowercase. Query types can be in ALL-CAPS or lowercase. 1248 1249This module will try to keep the SubProcess alive. 1250if it dies, it will open it again for a max of 5 retries by 1251default, but you can override this behavior by using [max\_retries](https://metacpan.org/pod/max_retries) 1252 1253Please rate this module. [http://cpanratings.perl.org/rate/?distribution=POE-Component-EasyDBI](http://cpanratings.perl.org/rate/?distribution=POE-Component-EasyDBI) 1254 1255## EXPORT 1256 1257Nothing. 1258 1259# SEE ALSO 1260 1261[DBI](https://metacpan.org/pod/DBI), [POE](https://metacpan.org/pod/POE), [POE::Wheel::Run](https://metacpan.org/pod/POE::Wheel::Run), [POE::Component::DBIAgent](https://metacpan.org/pod/POE::Component::DBIAgent), [POE::Component::LaDBI](https://metacpan.org/pod/POE::Component::LaDBI), 1262[POE::Component::SimpleDBI](https://metacpan.org/pod/POE::Component::SimpleDBI) 1263 1264[DBD::AnyData](https://metacpan.org/pod/DBD::AnyData), [DBD::SQLite](https://metacpan.org/pod/DBD::SQLite) 1265 1266[AnyEvent::DBI](https://metacpan.org/pod/AnyEvent::DBI) 1267 1268# AUTHOR 1269 1270David Davis <xantus@cpan.org> 1271 1272# CREDITS 1273 1274- Apocalypse <apocal@cpan.org> 1275- Chris Williams <chris@bingosnet.co.uk> 1276- Andy Grundman <andy@hybridized.org> 1277- Gelu Lupaș <gvl@cpan.org> 1278- Olivier Mengué <dolmen@cpan.org> 1279- Stephan Jauernick <stephan@stejau.de> 1280 1281# COPYRIGHT AND LICENSE 1282 1283Copyright 2003-2005 by David Davis and Teknikill Software 1284 1285This library is free software; you can redistribute it and/or modify 1286it under the same terms as Perl itself. 1287