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.6 by root, Sun Jan 7 02:53:53 2001 UTC vs.
Revision 1.31 by root, Wed Jan 28 19:58:19 2004 UTC

1=head1 NAME 1=head1 NAME
2 2
3PApp::SQL - absolutely easy yet fast and powerful sql access 3PApp::SQL - absolutely easy yet fast and powerful sql access.
4 4
5=head1 SYNOPSIS 5=head1 SYNOPSIS
6 6
7 use PApp::SQL; 7 use PApp::SQL;
8 # to be written 8
9 my $st = sql_exec $DBH, "select ... where a = ?", $a;
10
11 local $DBH = <database handle>;
12 my $st = sql_exec \my($bind_a, $bind_b), "select a,b ...";
13 my $st = sql_insertid
14 sql_exec "insert into ... values (?, ?)", $v1, $v2;
15 my $a = sql_fetch "select a from ...";
16 sql_fetch \my($a, $b), "select a,b ...";
17
18 sql_exists "table where name like 'a%'"
19 or die "a* required but not existent";
20
21 my $db = new PApp::SQL::Database "", "DBI:mysql:test", "user", "pass";
22 local $PApp::SQL::DBH = $db->checked_dbh; # does 'ping'
23
24 sql_exec $db->dbh, "select ...";
9 25
10=head1 DESCRIPTION 26=head1 DESCRIPTION
11 27
12This module provides you with easy-to-use functions to execute sql 28This module provides you with easy-to-use functions to execute sql
13commands (using DBI). Despite being easy to use, they are also quite 29commands (using DBI). Despite being easy to use, they are also quite
14efficient and allow you to write faster programs in less lines of code. 30efficient and allow you to write faster programs in less lines of code. It
31should work with anything from perl-5.004_01 onwards, but I only support
325.005+. UTF8 handling (the C<sql_u*> family of functions) will only be
33effective with perl version 5.006 and beyond.
15 34
16=over 4 35If the descriptions here seem terse or if you always wanted to know
36what PApp is then have a look at the PApp module which uses this module
37extensively but also provides you with a lot more gimmicks to play around
38with to help you create cool applications ;)
17 39
18=cut 40=cut
19 41
20package PApp::SQL; 42package PApp::SQL;
21 43
22use DBI; 44use DBI ();
23
24#use PApp::Exception; # not yet used
25 45
26BEGIN { 46BEGIN {
27 use base Exporter; 47 use base qw(Exporter DynaLoader);
28 48
29 $VERSION = 0.11; 49 $VERSION = 0.143;
30 @EXPORT = qw( 50 @EXPORT = qw(
31 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec 51 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec
52 sql_uexec sql_ufetch sql_ufetchall sql_uexists
32 ); 53 );
33 @EXPORT_OK = qw( 54 @EXPORT_OK = qw(
34 connect_cached 55 connect_cached
35 ); 56 );
36 57
37 require XSLoader; 58 bootstrap PApp::SQL $VERSION;
38 XSLoader::load PApp::SQL, $VERSION;
39} 59}
40 60
41our $sql_exec; # last result of sql_exec's execute call 61our $sql_exec; # last result of sql_exec's execute call
42our $DBH; # the default database handle 62our $DBH; # the default database handle
43our $database; # the current SQL::Database object, if applicable 63our $Database; # the current SQL::Database object, if applicable
44 64
45our %dbcache; 65our %dbcache;
66
67=head2 GLOBAL VARIABLES
68
69=over 4
70
71=item $sql_exec
72
73Since the C<sql_exec> family of functions return a statement handle there
74must be another way to test the return value of the C<execute> call. This
75global variable contains the result of the most recent call to C<execute>
76done by this module.
77
78=item $PApp::SQL::DBH
79
80The default database handle used by this module if no C<$DBH> was
81specified as argument. See C<sql_exec> for a discussion.
82
83=item $PApp::SQL::Database
84
85The current default C<PApp::SQL::Database>-object. Future versions might
86automatically fall back on this database and create database handles from
87it if neccessary. At the moment this is not used by this module but might
88be nice as a placeholder for the database object that corresponds to
89$PApp::SQL::DBH.
90
91=back
92
93=head2 FUNCTIONS
94
95=over 4
46 96
47=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect 97=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect
48 98
49(not exported by by default) 99(not exported by by default)
50 100
60__LINE__ work fine as well). 110__LINE__ work fine as well).
61 111
62The reason C<$id> is necessary is that you might specify special connect 112The reason C<$id> is necessary is that you might specify special connect
63arguments or special flags, or you might want to configure your $DBH 113arguments or special flags, or you might want to configure your $DBH
64differently than maybe other applications requesting the same database 114differently than maybe other applications requesting the same database
65connection. If none of this is becessary for your application you can 115connection. If none of this is necessary for your application you can
66leave $id empty (i.e. ""). 116leave C<$id> empty (i.e. "").
67 117
68If specified, C<$connect> is a callback (e.g. a coderef) that will be 118If specified, C<$connect> is a callback (e.g. a coderef) that will be
69called each time a new connection is being established, with the new 119called each time a new connection is being established, with the new
70C<$dbh> as first argument. 120C<$dbh> as first argument.
71 121
72Examples: 122Examples:
73 123
74 # try your luck opening the papp database without access info 124 # try your luck opening the papp database without access info
75 $dbh = connect_cached __FILE__, "DBI:mysql:papp"; 125 $dbh = connect_cached __FILE__, "DBI:mysql:papp";
126
127Mysql-specific behaviour: The default setting of
128C<mysql_client_found_rows> is TRUE, you can overwrite this, though.
76 129
77=cut 130=cut
78 131
79sub connect_cached { 132sub connect_cached {
80 my ($id, $dsn, $user, $pass, $flags, $connect) = @_; 133 my ($id, $dsn, $user, $pass, $flags, $connect) = @_;
81 # the following line is duplicated in PApp::SQL::Database::new 134 # the following line is duplicated in PApp::SQL::Database::new
82 $id = "$id\0$dsn\0$user\0$pass"; 135 $id = "$id\0$dsn\0$user\0$pass";
83 unless ($dbcache{$id} && $dbcache{$id}->ping) { 136 unless ($dbcache{$id} && $dbcache{$id}->ping) {
84 #warn "connecting to ($dsn|$user|$pass|$flags)\n";#d#
85 # first, nuke our statement cache (sooory ;) 137 # first, nuke our statement cache (sooory ;)
86 cachesize cachesize 0; 138 cachesize cachesize 0;
139
140 # then make mysql behave more standardly by default
141 $dsn =~ /^[Dd][Bb][Ii]:mysql:/
142 and $dsn !~ /;mysql_client_found_rows/
143 and $dsn .= ";mysql_client_found_rows=1";
144
87 # then connect anew 145 # then connect anew
88 $dbcache{$id} = 146 $dbcache{$id} =
89 eval { DBI->connect($dsn, $user, $pass, $flags) } 147 eval { DBI->connect($dsn, $user, $pass, $flags) }
90 || eval { DBI->connect($dsn, $user, $pass, $flags) } 148 || eval { DBI->connect($dsn, $user, $pass, $flags) }
91 || die "unable to connect to database $dsn: $DBI::errstr\n"; 149 || die "unable to connect to database $dsn: $DBI::errstr\n";
94 $dbcache{$id}; 152 $dbcache{$id};
95} 153}
96 154
97=item $sth = sql_exec [dbh,] [bind-vals...,] "sql-statement", [arguments...] 155=item $sth = sql_exec [dbh,] [bind-vals...,] "sql-statement", [arguments...]
98 156
157=item $sth = sql_uexec <see sql_exec>
158
99C<sql_exec> is the most important and most-used function in this module. 159C<sql_exec> is the most important and most-used function in this module.
100 160
101Runs the given sql command with the given parameters and returns the 161Runs the given sql command with the given parameters and returns the
102statement handle. The command and the statement handle will be cached 162statement handle. The command and the statement handle will be cached
103(with the database handle and the sql string as key), so prepare will be 163(with the database handle and the sql string as key), so prepare will be
104called only once for each distinct sql call (please keep in mind that the 164called only once for each distinct sql call (please keep in mind that the
105returned statement will always be the same, so, if you call C<sql_exec> 165returned statement will always be the same, so, if you call C<sql_exec>
106with the same dbh and sql-statement twice (e.g. in a subroutine you 166with the same dbh and sql-statement twice (e.g. in a subroutine you
107called), the statement handle for the first call mustn't be used. 167called), the statement handle for the first call mustn't not be in use
168anymore, as the subsequent call will re-use the handle.
108 169
109The database handle (the first argument) is optional. If it is missing, 170The database handle (the first argument) is optional. If it is missing,
110C<sql_exec> first tries to use the variable C<$DBH> in the current (= 171it tries to use database handle in C<$PApp::SQL::DBH>, which you can set
111calling) package and, if that fails, it tries to use database handle in 172before calling these functions. NOTICE: future and former versions of
112C<$PApp::SQL::DBH>, which you can set before calling these functions. 173PApp::SQL might also look up the global variable C<$DBH> in the callers
174package.
175
176=begin comment
177
178If it is missing, C<sql_exec> first tries to use the variable C<$DBH>
179in the current (= calling) package and, if that fails, it tries to use
180database handle in C<$PApp::SQL::DBH>, which you can set before calling
181these functions.
182
183=end comment
113 184
114The actual return value from the C<$sth->execute> call is stored in the 185The actual return value from the C<$sth->execute> call is stored in the
115package-global (and exported) variable C<$sql_exec>. 186package-global (and exported) variable C<$sql_exec>.
116 187
117If any error occurs C<sql_exec> will throw an exception. 188If any error occurs C<sql_exec> will throw an exception.
189
190C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to
191UTF-8 before calling the C<execute> method.
118 192
119Examples: 193Examples:
120 194
121 # easy one 195 # easy one
122 my $st = sql_exec "select name, id from table where id = ?", $id; 196 my $st = sql_exec "select name, id from table where id = ?", $id;
132 sql_exec $dbh, "update file set name = ?", "oops.txt"; 206 sql_exec $dbh, "update file set name = ?", "oops.txt";
133 207
134 208
135=item sql_fetch <see sql_exec> 209=item sql_fetch <see sql_exec>
136 210
211=item sql_ufetch <see sql_uexec>
212
137Execute a sql-statement and fetch the first row of results. Depending on 213Execute an sql-statement and fetch the first row of results. Depending on
138the caller context the row will be returned as a list (array context), or 214the caller context the row will be returned as a list (array context), or
139just the first columns. In table form: 215just the first columns. In table form:
140 216
141 CONTEXT RESULT 217 CONTEXT RESULT
142 void () 218 void ()
153 229
154 my($name, $amount) = sql_fetch "select ...", args... 230 my($name, $amount) = sql_fetch "select ...", args...
155 231
156... and it's still quite fast unless you fetch large amounts of data. 232... and it's still quite fast unless you fetch large amounts of data.
157 233
234C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to
235UTF-8 and forces all result values to UTF-8 (this does I<not> include result
236parameters, only return values. Using bind variables in conjunction with
237sql_u* functions might result in undefined behaviour - we use UTF-8 on
238bind-variables at execution time and it seems to work on DBD::mysql as it
239ignores the UTF-8 bit completely. Which just means that that DBD-driver is
240broken).
241
158=item sql_fetchall <see sql_exec> 242=item sql_fetchall <see sql_exec>
243
244=item sql_ufetchall <see sql_uexec>
159 245
160Similarly to C<sql_fetch>, but all result rows will be fetched (this is 246Similarly to C<sql_fetch>, but all result rows will be fetched (this is
161of course inefficient for large results!). The context is ignored (only 247of course inefficient for large results!). The context is ignored (only
162list context makes sense), but the result still depends on the number of 248list context makes sense), but the result still depends on the number of
163columns in the result: 249columns in the result:
175 261
176 for (sql_fetchall "select name, age, place from user") { 262 for (sql_fetchall "select name, age, place from user") {
177 my ($name, $age, $place) = @$_; 263 my ($name, $age, $place) = @$_;
178 } 264 }
179 265
266C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input
267values to UTF-8 and forces all result values to UTF-8 (see the caveats in
268the description of C<sql_ufetch>, though).
269
180=item sql_exists "<table> where ...", args... 270=item sql_exists "<table_references> where <where_condition>...", args...
271
272=item sql_uexists <see sql_exists>
181 273
182Check wether the result of the sql-statement "select xxx from 274Check wether the result of the sql-statement "select xxx from
183$first_argument" would be empty or not (that is, imagine the string 275$first_argument" would be empty or not (that is, imagine the string
184"select from" were prepended to your statement (it isn't)). Should work 276"select * from" were prepended to your statement (it isn't)). Should work
185with every database but can be quite slow, except on mysql, where this 277with every database but can be quite slow, except on mysql, where this
186should be quite fast. 278should be quite fast.
279
280C<sql_uexists> is similar to C<sql_exists> but upgrades all parameters to
281UTF-8.
187 282
188Examples: 283Examples:
189 284
190 print "user 7 exists!\n" 285 print "user 7 exists!\n"
191 if sql_exists "user where id = ?", 7; 286 if sql_exists "user where id = ?", 7;
195 290
196=cut 291=cut
197 292
198=item $lastid = sql_insertid $sth 293=item $lastid = sql_insertid $sth
199 294
200Returns the last automatically created key value (e.g. for mysql 295Returns the last automatically created key value. It must be executed
201AUTO_INCREMENT or sybase IDENTITY fields). It must be executed directly
202after executing the insert statement that created it. 296directly after executing the insert statement that created it. This is
297what is actually returned for various databases. If your database is
298missing, please send me an e-mail on how to implement this ;)
299
300 mysql: first C<AUTO_INCREMENT> column set to NULL
301 postgres: C<oid> column (is there a way to get the last SERIAL?)
302 sybase: C<IDENTITY> column of the last insert (slow)
303 informix: C<SERIAL> or C<SERIAL8> column of the last insert
304
305Except for sybase, this does not require a server access.
203 306
204=cut 307=cut
205 308
206sub sql_insertid($) { 309sub sql_insertid($) {
207 my $sth = shift or die "sql_insertid requires a statement handle"; 310 my $sth = shift or die "sql_insertid requires a statement handle";
208 my $dbh = $sth->{Database}; 311 my $dbh = $sth->{Database};
209 my $driver = $dbh->{Driver}{Name}; 312 my $driver = $dbh->{Driver}{Name};
210 313
211 $driver eq "mysql" and return $sth->{mysql_insertid}; 314 $driver eq "mysql" and return $sth->{mysql_insertid};
315 $driver eq "Pg" and return $sth->{pg_oid_status};
212 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY'); 316 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY');
213 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1]; 317 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1];
214 318
215 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid"; 319 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid";
216} 320}
217 321
218=item [old-size] = cachesize [new-size] 322=item [old-size] = cachesize [new-size]
219 323
220Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The 324Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The
221default is somewhere around 50 (= the 50 last recently used statements 325default is somewhere around 50 (= the 50 last recently used statements
222will be cached). It shouldn't be too large, since a simple linear listed 326will be cached). It shouldn't be too large, since a simple linear list
223is used for the cache at the moment (which, for small (<100) cache sizes 327is used for the cache at the moment (which, for small (<100) cache sizes
224is actually quite fast). 328is actually quite fast).
225 329
226The function always returns the cache size in effect I<before> the call, 330The function always returns the cache size in effect I<before> the call,
227so, to nuke the cache (for example, when a database connection has died 331so, to nuke the cache (for example, when a database connection has died
232 336
233=cut 337=cut
234 338
235=item reinitialize [not exported] 339=item reinitialize [not exported]
236 340
237Clears any internal caches (statement cache, database handle cache). 341Clears any internal caches (statement cache, database handle
342cache). Should be called after C<fork> and other accidents that invalidate
343database handles.
238 344
239=cut 345=cut
240 346
241sub reinitialize { 347sub reinitialize {
242 cachesize cachesize 0; 348 cachesize cachesize 0;
243 for (values %dbcache) { 349 for (values %dbcache) {
244 eval { $_->disconnect }; 350 eval { $_->{InactiveDestroy} = 1 };
245 } 351 }
246 undef %dbcache; 352 undef %dbcache;
247} 353}
248 354
249=back 355=back
250 356
251=cut 357=cut
252 358
359reinitialize;
360
253package PApp::SQL::Database; 361package PApp::SQL::Database;
254 362
255=head2 THE DATABASE CLASS 363=head2 THE DATABASE CLASS
256 364
257Again (sigh) the problem of persistency. What do you do when you have to serialize on object 365Again (sigh) the problem of persistency. What do you do when you have
258that contains (or should contain) a database handle? Short answer: you don't. Long answer: 366to serialize on object that contains (or should contain) a database
367handle? Short answer: you don't. Long answer: you can embed the necessary
259you can embed the necessary information to recreate the dbh when needed. 368information to recreate the dbh when needed.
260 369
261The C<PApp::SQL::Database> class does that, in a relatively efficient 370The C<PApp::SQL::Database> class does that, in a relatively efficient
262fashion: the overhead is currently a single method call per access (you 371fashion: the overhead is currently a single method call per access (you
263can cache the real dbh if you want). 372can cache the real dbh if you want).
264 373
304 413
305=item $db->dsn 414=item $db->dsn
306 415
307Return the DSN (L<DBI>) fo the database object (e.g. for error messages). 416Return the DSN (L<DBI>) fo the database object (e.g. for error messages).
308 417
418=item $db->login
419
420Return the login name.
421
422=item $db->password
423
424Return the password (emphasizing the fact that the password is stored plaintext ;)
425
309=cut 426=cut
310 427
311sub dsn($) { 428sub dsn($) {
312 my $self = shift; 429 my $self = shift;
313 $self->[1][1]; 430 (split /\x00/, $self->[0])[1];
431}
432
433sub login($) {
434 my $self = shift;
435 (split /\x00/, $self->[0])[2];
436}
437
438sub password($) {
439 my $self = shift;
440 (split /\x00/, $self->[0])[3];
314} 441}
315 442
316=back 443=back
317 444
318=cut 445=cut
319 446
320reinitialize;
321
3221; 4471;
323
324=head1 BUGS
325
326As of this writing, sql_fetch and sql_fetchall are not very well tested
327(they were just re-written in C).
328
329sql_exists could be faster (it is written very ugly to not change the
330current package).
331 448
332=head1 SEE ALSO 449=head1 SEE ALSO
333 450
334L<PApp>. 451L<PApp>.
335 452

Diff Legend

Removed lines
+ Added lines
< Changed lines
> Changed lines