[ViewVC] Diff of: cvs/PApp-SQL/SQL.pm

Comparing PApp-SQL/SQL.pm (file contents):
Revision 1.23 by root, Sun Apr 7 16:23:56 2002 UTC vs.
Revision 1.41 by root, Thu Apr 11 02:20:42 2013 UTC

  my $st = sql_exec $DBH, "select ... where a = ?", $a;
  local $DBH = <database handle>;
  my $st = sql_exec \my($bind_a, $bind_b), "select a,b ...";
- my $st = sql_insertid
+ my $id = sql_insertid
              sql_exec "insert into ... values (?, ?)", $v1, $v2;
  my $a = sql_fetch "select a from ...";
  sql_fetch \my($a, $b), "select a,b ...";
  sql_exists "table where name like 'a%'"
 =cut
 package PApp::SQL;
+use Carp ();
 use DBI ();
 BEGIN {
    use base qw(Exporter DynaLoader);
-   $VERSION = 0.13;
+   $VERSION = '2.0';
    @EXPORT = qw(
          sql_exec  sql_fetch  sql_fetchall  sql_exists sql_insertid $sql_exec
          sql_uexec sql_ufetch sql_ufetchall sql_uexists
    );
    @EXPORT_OK = qw(
    );
    bootstrap PApp::SQL $VERSION;
 }
+boot2 DBI::SQL_VARCHAR, DBI::SQL_INTEGER, DBI::SQL_DOUBLE;
 our $sql_exec;  # last result of sql_exec's execute call
 our $DBH;       # the default database handle
 our $Database;	# the current SQL::Database object, if applicable
 our %dbcache;
-=head2 GLOBAL VARIABLES
+=head2 Global Variables
 =over 4
 =item $sql_exec
 done by this module.
 =item $PApp::SQL::DBH
 The default database handle used by this module if no C<$DBH> was
-specified as argument and no C<$DBH> is found in the current package. See
+specified as argument. See C<sql_exec> for a discussion.
-C<sql_exec> for a discussion.
 =item $PApp::SQL::Database
 The current default C<PApp::SQL::Database>-object. Future versions might
 automatically fall back on this database and create database handles from
 be nice as a placeholder for the database object that corresponds to
 $PApp::SQL::DBH.
 =back
-=head2 FUNCTIONS
+=head2 Functions
 =over 4
 =item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect
 (not exported by by default)
 Connect to the database given by C<($dsn,$user,$pass)>, while using the
 flags from C<$flags>. These are just the same arguments as given to
-C<DBI->connect>.
+C<< DBI->connect >>.
 The database handle will be cached under the unique id
 C<$id|$dsn|$user|$pass>. If the same id is requested later, the
 cached handle will be checked (using ping), and the connection will
 be re-established if necessary (be sure to prefix your application or
       # then connect anew
       $dbcache{$id} =
          eval { DBI->connect($dsn, $user, $pass, $flags) }
          || eval { DBI->connect($dsn, $user, $pass, $flags) }
-         || die "unable to connect to database $dsn: $DBI::errstr\n";
+         || Carp::croak "unable to connect to database $dsn: $DBI::errstr\n";
       $connect->($dbcache{$id}) if $connect;
    }
    $dbcache{$id};
 }
 with the same dbh and sql-statement twice (e.g. in a subroutine you
 called), the statement handle for the first call mustn't not be in use
 anymore, as the subsequent call will re-use the handle.
 The database handle (the first argument) is optional. If it is missing,
-C<sql_exec> first tries to use the variable C<$DBH> in the current (=
+it tries to use database handle in C<$PApp::SQL::DBH>, which you can set
-calling) package and, if that fails, it tries to use database handle in
+before calling these functions. NOTICE: future and former versions of
-C<$PApp::SQL::DBH>, which you can set before calling these functions.
+PApp::SQL might also look up the global variable C<$DBH> in the callers
+package.
+=begin comment
+If it is missing, C<sql_exec> first tries to use the variable C<$DBH>
+in the current (= calling) package and, if that fails, it tries to use
+database handle in C<$PApp::SQL::DBH>, which you can set before calling
+these functions.
+=end comment
-The actual return value from the C<$sth->execute> call is stored in the
+The actual return value from the C<< $sth->execute >> call is stored in
-package-global (and exported) variable C<$sql_exec>.
+the package-global (and exported) variable C<$sql_exec>.
 If any error occurs C<sql_exec> will throw an exception.
 C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to
-utf8 before calling the C<execute> method.
+UTF-8 before calling the C<execute> method.
 Examples:
  # easy one
  my $st = sql_exec "select name, id from table where id = ?", $id;
  my($name, $amount) = sql_fetch "select ...", args...
 ... and it's still quite fast unless you fetch large amounts of data.
 C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to
-utf8 and forces all result values to utf8 (this does I<not> include result
+UTF-8 and forces all result values to UTF-8 (this does I<not> include result
-parameters, only return values. Using bind variables in cinjunction with
+parameters, only return values. Using bind variables in conjunction with
-sql_u* functions results in undefined behaviour).
+sql_u* functions might result in undefined behaviour - we use UTF-8 on
+bind-variables at execution time and it seems to work on DBD::mysql as it
+ignores the UTF-8 bit completely. Which just means that that DBD-driver is
+broken).
 =item sql_fetchall <see sql_exec>
 =item sql_ufetchall <see sql_uexec>
  for (sql_fetchall "select name, age, place from user") {
     my ($name, $age, $place) = @$_;
  }
 C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input
-values to utf8 and forces all result values to utf8 (see the caveats in
+values to UTF-8 and forces all result values to UTF-8 (see the caveats in
 the description of C<sql_ufetch>, though).
 =item sql_exists "<table_references> where <where_condition>...", args...
 =item sql_uexists <see sql_exists>
 "select * from" were prepended to your statement (it isn't)). Should work
 with every database but can be quite slow, except on mysql, where this
 should be quite fast.
 C<sql_uexists> is similar to C<sql_exists> but upgrades all parameters to
-utf8.
+UTF-8.
 Examples:
  print "user 7 exists!\n"
     if sql_exists "user where id = ?", 7;
  mysql:    first C<AUTO_INCREMENT> column set to NULL
  postgres: C<oid> column (is there a way to get the last SERIAL?)
  sybase:   C<IDENTITY> column of the last insert (slow)
  informix: C<SERIAL> or C<SERIAL8> column of the last insert
+ sqlite:   C<last_insert_rowid()>
 Except for sybase, this does not require a server access.
 =cut
 sub sql_insertid($) {
-   my $sth = shift or die "sql_insertid requires a statement handle";
+   my $sth = shift or Carp::croak "sql_insertid requires a statement handle";
    my $dbh = $sth->{Database};
    my $driver = $dbh->{Driver}{Name};
    $driver eq "mysql"    and return $sth->{mysql_insertid};
    $driver eq "Pg"       and return $sth->{pg_oid_status};
-   $driver eq "Sybase"   and return sql_fetch($dbh, 'SELECT @@IDENTITY');
+   $driver eq "Sybase"   and return sql_fetch ($dbh, 'SELECT @@IDENTITY');
    $driver eq "Informix" and return $sth->{ix_sqlerrd}[1];
+   $driver eq "SQLite"   and return sql_fetch ($dbh, 'SELECT last_insert_rowid ()');
-   die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid";
+   Carp::croak "sql_insertid does not support the dbd driver '$driver', at";
 }
 =item [old-size] = cachesize [new-size]
 Returns (and possibly changes) the LRU cache size used by C<sql_exec>. The
 default is somewhere around 50 (= the 50 last recently used statements
-will be cached). It shouldn't be too large, since a simple linear listed
+will be cached). It shouldn't be too large, since a simple linear list
 is used for the cache at the moment (which, for small (<100) cache sizes
 is actually quite fast).
 The function always returns the cache size in effect I<before> the call,
 so, to nuke the cache (for example, when a database connection has died
 =cut
 reinitialize;
+=head2 Type Deduction
+Since every database driver seems to deduce parameter types differently,
+usually wrongly, and at leats in the case of DBD::mysql, different in
+every other release or so, and this can and does lead to data corruption,
+this module does type deduction itself.
+What does it mean? Simple - sql parameters for placeholders will be
+explicitly marked as SQL_VARCHAR, SQL_INTEGER or SQL_DOUBLE the first time
+a statement is prepared.
+To force a specific type, you can either continue to use e.g. sql casts,
+or you can make sure to consistently use strings or numbers. To make a
+perl scalar look enough like a string or a number, use this when passing
+it to sql_exec or a similar functions:
+   "$string"   # to pass a string
+   $num+0      # to pass a number
+=cut
 package PApp::SQL::Database;
-=head2 THE DATABASE CLASS
+=head2 The Database Class
 Again (sigh) the problem of persistency. What do you do when you have
 to serialize on object that contains (or should contain) a database
 handle? Short answer: you don't. Long answer: you can embed the necessary
 information to recreate the dbh when needed.
 sub checked_dbh($) {
    my $dbh = $dbcache{$_[0][0]};
    $dbh && $dbh->ping
       ? $dbh
-      : PApp::SQL::connect_cached((split /\x00/, $_[0][0]), $_[0][1], $_[0][2]);
+      : PApp::SQL::connect_cached((split /\x00/, $_[0][0], 4), $_[0][1], $_[0][2]);
 }
 =item $db->dsn
 Return the DSN (L<DBI>) fo the database object (e.g. for error messages).
 Return the login name.
 =item $db->password
-Return the password (emphasizing the fact that the apssword is stored plaintext ;)
+Return the password (emphasizing the fact that the password is stored plaintext ;)
 =cut
 sub dsn($) {
    my $self = shift;
 L<PApp>.
 =head1 AUTHOR
- Marc Lehmann <pcg@goof.com>
+ Marc Lehmann <schmorp@schmorp.de>
- http://www.goof.com/pcg/marc/
+ http://home.schmorp.de/
 =cut

Diff Legend

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

Comparing PApp-SQL/SQL.pm (file contents): Revision 1.23 by root, Sun Apr 7 16:23:56 2002 UTC vs. Revision 1.41 by root, Thu Apr 11 02:20:42 2013 UTC

Diff Legend

Comparing PApp-SQL/SQL.pm (file contents):
Revision 1.23 by root, Sun Apr 7 16:23:56 2002 UTC vs.
Revision 1.41 by root, Thu Apr 11 02:20:42 2013 UTC