ViewVC Help
View File | Revision Log | Show Annotations | Download File
/cvs/PApp-SQL/SQL.pm
(Generate patch)

Comparing PApp-SQL/SQL.pm (file contents):
Revision 1.30 by stefan, Wed Jan 28 19:50:38 2004 UTC vs.
Revision 1.40 by root, Mon Feb 13 05:59:55 2012 UTC

… …
8 8
9 my $st = sql_exec $DBH, "select ... where a = ?", $a; 9 my $st = sql_exec $DBH, "select ... where a = ?", $a;
10 10
11 local $DBH = <database handle>; 11 local $DBH = <database handle>;
12 my $st = sql_exec \my($bind_a, $bind_b), "select a,b ..."; 12 my $st = sql_exec \my($bind_a, $bind_b), "select a,b ...";
13 my $st = sql_insertid 13 my $id = sql_insertid
14 sql_exec "insert into ... values (?, ?)", $v1, $v2; 14 sql_exec "insert into ... values (?, ?)", $v1, $v2;
15 my $a = sql_fetch "select a from ..."; 15 my $a = sql_fetch "select a from ...";
16 sql_fetch \my($a, $b), "select a,b ..."; 16 sql_fetch \my($a, $b), "select a,b ...";
17 17
18 sql_exists "table where name like 'a%'" 18 sql_exists "table where name like 'a%'"
… …
39 39
40=cut 40=cut
41 41
42package PApp::SQL; 42package PApp::SQL;
43 43
44use Carp ();
44use DBI (); 45use DBI ();
45 46
46BEGIN { 47BEGIN {
47 use base qw(Exporter DynaLoader); 48 use base qw(Exporter DynaLoader);
48 49
49 $VERSION = 0.143; 50 $VERSION = '2.0';
50 @EXPORT = qw( 51 @EXPORT = qw(
51 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec 52 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec
52 sql_uexec sql_ufetch sql_ufetchall sql_uexists 53 sql_uexec sql_ufetch sql_ufetchall sql_uexists
53 ); 54 );
54 @EXPORT_OK = qw( 55 @EXPORT_OK = qw(
… …
56 ); 57 );
57 58
58 bootstrap PApp::SQL $VERSION; 59 bootstrap PApp::SQL $VERSION;
59} 60}
60 61
62boot2 DBI::SQL_VARCHAR, DBI::SQL_INTEGER, DBI::SQL_DOUBLE;
63
61our $sql_exec; # last result of sql_exec's execute call 64our $sql_exec; # last result of sql_exec's execute call
62our $DBH; # the default database handle 65our $DBH; # the default database handle
63our $Database; # the current SQL::Database object, if applicable 66our $Database; # the current SQL::Database object, if applicable
64 67
65our %dbcache; 68our %dbcache;
66 69
67=head2 GLOBAL VARIABLES 70=head2 Global Variables
68 71
69=over 4 72=over 4
70 73
71=item $sql_exec 74=item $sql_exec
72 75
… …
88be nice as a placeholder for the database object that corresponds to 91be nice as a placeholder for the database object that corresponds to
89$PApp::SQL::DBH. 92$PApp::SQL::DBH.
90 93
91=back 94=back
92 95
93=head2 FUNCTIONS 96=head2 Functions
94 97
95=over 4 98=over 4
96 99
97=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect 100=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect
98 101
… …
144 147
145 # then connect anew 148 # then connect anew
146 $dbcache{$id} = 149 $dbcache{$id} =
147 eval { DBI->connect($dsn, $user, $pass, $flags) } 150 eval { DBI->connect($dsn, $user, $pass, $flags) }
148 || eval { DBI->connect($dsn, $user, $pass, $flags) } 151 || eval { DBI->connect($dsn, $user, $pass, $flags) }
149 || die "unable to connect to database $dsn: $DBI::errstr\n"; 152 || Carp::croak "unable to connect to database $dsn: $DBI::errstr\n";
150 $connect->($dbcache{$id}) if $connect; 153 $connect->($dbcache{$id}) if $connect;
151 } 154 }
152 $dbcache{$id}; 155 $dbcache{$id};
153} 156}
154 157
… …
186package-global (and exported) variable C<$sql_exec>. 189package-global (and exported) variable C<$sql_exec>.
187 190
188If any error occurs C<sql_exec> will throw an exception. 191If any error occurs C<sql_exec> will throw an exception.
189 192
190C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to 193C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to
191utf8 before calling the C<execute> method. 194UTF-8 before calling the C<execute> method.
192 195
193Examples: 196Examples:
194 197
195 # easy one 198 # easy one
196 my $st = sql_exec "select name, id from table where id = ?", $id; 199 my $st = sql_exec "select name, id from table where id = ?", $id;
… …
230 my($name, $amount) = sql_fetch "select ...", args... 233 my($name, $amount) = sql_fetch "select ...", args...
231 234
232... and it's still quite fast unless you fetch large amounts of data. 235... and it's still quite fast unless you fetch large amounts of data.
233 236
234C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to 237C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to
235utf8 and forces all result values to utf8 (this does I<not> include result 238UTF-8 and forces all result values to UTF-8 (this does I<not> include result
236parameters, only return values. Using bind variables in cinjunction with 239parameters, only return values. Using bind variables in conjunction with
237sql_u* functions results in undefined behaviour - we use utf8_on on 240sql_u* functions might result in undefined behaviour - we use UTF-8 on
238bind-variables and it seems to work on DBD::mysql which just means 241bind-variables at execution time and it seems to work on DBD::mysql as it
239that that DBD-driver is broken). 242ignores the UTF-8 bit completely. Which just means that that DBD-driver is
243broken).
240 244
241=item sql_fetchall <see sql_exec> 245=item sql_fetchall <see sql_exec>
242 246
243=item sql_ufetchall <see sql_uexec> 247=item sql_ufetchall <see sql_uexec>
244 248
… …
261 for (sql_fetchall "select name, age, place from user") { 265 for (sql_fetchall "select name, age, place from user") {
262 my ($name, $age, $place) = @$_; 266 my ($name, $age, $place) = @$_;
263 } 267 }
264 268
265C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input 269C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input
266values to utf8 and forces all result values to utf8 (see the caveats in 270values to UTF-8 and forces all result values to UTF-8 (see the caveats in
267the description of C<sql_ufetch>, though). 271the description of C<sql_ufetch>, though).
268 272
269=item sql_exists "<table_references> where <where_condition>...", args... 273=item sql_exists "<table_references> where <where_condition>...", args...
270 274
271=item sql_uexists <see sql_exists> 275=item sql_uexists <see sql_exists>
… …
275"select * from" were prepended to your statement (it isn't)). Should work 279"select * from" were prepended to your statement (it isn't)). Should work
276with every database but can be quite slow, except on mysql, where this 280with every database but can be quite slow, except on mysql, where this
277should be quite fast. 281should be quite fast.
278 282
279C<sql_uexists> is similar to C<sql_exists> but upgrades all parameters to 283C<sql_uexists> is similar to C<sql_exists> but upgrades all parameters to
280utf8. 284UTF-8.
281 285
282Examples: 286Examples:
283 287
284 print "user 7 exists!\n" 288 print "user 7 exists!\n"
285 if sql_exists "user where id = ?", 7; 289 if sql_exists "user where id = ?", 7;
… …
298 302
299 mysql: first C<AUTO_INCREMENT> column set to NULL 303 mysql: first C<AUTO_INCREMENT> column set to NULL
300 postgres: C<oid> column (is there a way to get the last SERIAL?) 304 postgres: C<oid> column (is there a way to get the last SERIAL?)
301 sybase: C<IDENTITY> column of the last insert (slow) 305 sybase: C<IDENTITY> column of the last insert (slow)
302 informix: C<SERIAL> or C<SERIAL8> column of the last insert 306 informix: C<SERIAL> or C<SERIAL8> column of the last insert
307 sqlite: C<last_insert_rowid()>
303 308
304Except for sybase, this does not require a server access. 309Except for sybase, this does not require a server access.
305 310
306=cut 311=cut
307 312
308sub sql_insertid($) { 313sub sql_insertid($) {
309 my $sth = shift or die "sql_insertid requires a statement handle"; 314 my $sth = shift or Carp::croak "sql_insertid requires a statement handle";
310 my $dbh = $sth->{Database}; 315 my $dbh = $sth->{Database};
311 my $driver = $dbh->{Driver}{Name}; 316 my $driver = $dbh->{Driver}{Name};
312 317
313 $driver eq "mysql" and return $sth->{mysql_insertid}; 318 $driver eq "mysql" and return $sth->{mysql_insertid};
314 $driver eq "Pg" and return $sth->{pg_oid_status}; 319 $driver eq "Pg" and return $sth->{pg_oid_status};
315 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY'); 320 $driver eq "Sybase" and return sql_fetch ($dbh, 'SELECT @@IDENTITY');
316 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1]; 321 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1];
322 $driver eq "SQLite" and return sql_fetch ($dbh, 'SELECT last_insert_rowid ()');
317 323
318 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid"; 324 Carp::croak "sql_insertid does not support the dbd driver '$driver', at";
319} 325}
320 326
321=item [old-size] = cachesize [new-size] 327=item [old-size] = cachesize [new-size]
322 328
323Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The 329Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The
… …
355 361
356=cut 362=cut
357 363
358reinitialize; 364reinitialize;
359 365
366=head2 Type Deduction
367
368Since every database driver seems to deduce parameter types differently,
369usually wrongly, and at leats in the case of DBD::mysql, different in
370every other release or so, and this can and does lead to data corruption,
371this module does type deduction itself.
372
373What does it mean? Simple - sql parameters for placeholders will be
374explicitly marked as SQL_VARCHAR, SQL_INTEGER or SQL_DOUBLE the first time
375a statement is prepared.
376
377To force a specific type, you can either continue to use e.g. sql casts,
378or you can make sure to consistently use strings or numbers. To make a
379perl scalar look enough like a string or a number, use this when passing
380it to sql_exec or a similar functions:
381
382 "$string" # to pass a string
383 $num+0 # to pass a number
384
385=cut
386
360package PApp::SQL::Database; 387package PApp::SQL::Database;
361 388
362=head2 THE DATABASE CLASS 389=head2 The Database Class
363 390
364Again (sigh) the problem of persistency. What do you do when you have 391Again (sigh) the problem of persistency. What do you do when you have
365to serialize on object that contains (or should contain) a database 392to serialize on object that contains (or should contain) a database
366handle? Short answer: you don't. Long answer: you can embed the necessary 393handle? Short answer: you don't. Long answer: you can embed the necessary
367information to recreate the dbh when needed. 394information to recreate the dbh when needed.
… …
405 432
406sub checked_dbh($) { 433sub checked_dbh($) {
407 my $dbh = $dbcache{$_[0][0]}; 434 my $dbh = $dbcache{$_[0][0]};
408 $dbh && $dbh->ping 435 $dbh && $dbh->ping
409 ? $dbh 436 ? $dbh
410 : PApp::SQL::connect_cached((split /\x00/, $_[0][0]), $_[0][1], $_[0][2]); 437 : PApp::SQL::connect_cached((split /\x00/, $_[0][0], 4), $_[0][1], $_[0][2]);
411} 438}
412 439
413=item $db->dsn 440=item $db->dsn
414 441
415Return the DSN (L<DBI>) fo the database object (e.g. for error messages). 442Return the DSN (L<DBI>) fo the database object (e.g. for error messages).
… …
449 476
450L<PApp>. 477L<PApp>.
451 478
452=head1 AUTHOR 479=head1 AUTHOR
453 480
454 Marc Lehmann <pcg@goof.com> 481 Marc Lehmann <schmorp@schmorp.de>
455 http://www.goof.com/pcg/marc/ 482 http://home.schmorp.de/
456 483
457=cut 484=cut
458 485

Diff Legend

– Removed lines
+ Added lines
< Changed lines
> Changed lines