TUWF::DB Documentation
NAME
TUWF::DB - Database handling and convenience functions for TUWF
DESCRIPTION
This module provides convenient methods for interacting with SQL
databases and takes care to properly start, commit or rollback
transactions for each request. This module is automatically loaded by
TUWF when the db_login configuration
parameter has been set before calling TUWF::run()
. The
methods this module provides are then automatically available from the
main TUWF object.
While most of the functionality of this module can only be used within the TUWF framework, this module does export a single utility function that can be used outside TUWF. See FUNCTIONS below.
When running in FastCGI, the connection to the database is persistent: it will stay open as long as the FastCGI process is running. This can add some significant performance improvements.
Note that this module does have several limitations not imposed by DBI:
- It can only be used for a single database connection within a TUWF website. If you want to connect to multiple databases you will have to use the usual DBI functions. In which case it is also the responsibility of your code to properly handle transactions.
- The database you are connecting to must implement transactions. MySQL with MyISAM is probably not going to work.
- This module has only been properly tested for PostgreSQL. Other databases will most likely work as well, but you might run into some issues.
Every request is one transaction
TUWF automatically makes sure that before a request will be processed
(that is, before the before hooks are called), the database
connection will be in a clean state and has just started a new
transaction. After the request has been successfully handled (that is,
none of your functions die()
'd), the transaction will be
committed automatically. If something went wrong while processing the
request, the transaction will be rolled back before the
error_500_handler is called. So from the perspective of the
database, each request is atomic: the request either never happened or
everything is committed.
Of course, you are free to commit or rollback changes or use savepoints (if your database has those) thourough your code as much as you wish. As long as you keep in mind that TUWF also does some transaction handling.
METHODS
The following methods are added to the main TUWF object:
dbh()
Returns the DBI connection handle.
dbCommit()
Commits the current transaction. You normally don't have to call this method since TUWF does this automatically after each request.
dbRollBack()
Rolls back the current transaction. You normally don't have to call this method since TUWF does this automatically if something goes wrong.
dbExec(query, @params)
Execute an SQL query and return the number of rows affected. This is
similar to $dbh->do()
, except dbExec()
simply returns 0 (zero and false) instead of "0E0" (zero but true) when
no rows are affected, and this method passes the query through the
sqlprint()
function (see below), allowing you to use the
extended formatting codes in the query.
Example of an "UPDATE or INSERT" query:
tuwf->dbExec(
'UPDATE users !H !W',
{'username = ?' => $user, 'email = ?' => $email},
{'id = ?' => $uid}
) || tuwf->dbExec(
'INSERT INTO users (id, username, email) VALUES(!l)',
[ $uid, $user, $email ]
);
Of course, the use of the extended formatting in the above example is not very practical. Their advantage is more apparent when the queries get more dynamic.
dbVal(query, @params)
Accepts the same arguments as dbExec()
, and returns the
first value of the first row.
Examples:
my $count = tuwf->dbVal('SELECT COUNT(*) FROM users');
my $exists = tuwf->dbVal('SELECT 1 FROM users WHERE id = ?', $id);
dbRow(query, @params)
Accepts the same arguments as dbExec()
, and returns the
first row of the result as a hashref. The return value is equivalent to
$sth->fetchrow_hashref()
, except dbRow()
returns an empty hash if the query did not return any rows.
Example:
my %latest_announcement = %{ tuwf->dbRow(q{
SELECT title, message, post_date
FROM announcements
ORDER BY post_date DESC
LIMIT 1
}) };
die "No announcements!" if !keys %latest_announcement;
dbAll(query, @params)
Accepts the same arguments as dbExec()
, and returns an
arrayref of hashrefs. The return value is equivalent to
$sth->fetchall_arrayref({})
.
dbPage(opts, query, @params)
This method is similar to dbAll()
, but provides some
extra functionality to ease the paging of the results. opts
should be a hashref with the keys results, a number indicating
the maximum number of rows per page, and page, a number
indicating the currently requested page, counting from 1. This method
automatically adds a LIMIT
and OFFSET
to the
end of the query, so these should be omitted from the query
argument.
In scalar context, this method returns an arrayref of hashrefs, the
same as dbAll()
except with the proper LIMIT
and OFFSET
clauses applied. In array context, this method
returns two values: the first value is the arrayref, the second value is
either 0 or 1, indicating whether there are more rows. That is, if the
second value is true, calling the this method with the same arguments
but with the page option increased with one will return at
least one more row.
my($rows, $nextpage) = tuwf->dbPage(
{page => 2, results => 10},
'SELECT title FROM threads ORDER BY title'
);
# $rows is now equivalent to:
# tuwf->dbAll('SELECT title FROM threads ORDER BY title LIMIT 10 OFFSET 10')
The value of $nextpage
is determined by fetching one row
more than requested and later removing it from the results.
FUNCTIONS
TUWF::DB
exports one function, which can also be used
outside of the TUWF framework by 'use'ing the module as follows:
use TUWF::DB 'sqlprint';
# or, if you don't want to import the function but prefer to call it as
# TUWF::DB::sqlprint(): (the parentheses are important)
use TUWF::DB ();
sqlprint(query, @params)
This function expands the extended formatting codes in query
and returns a new query and parameters, suitable for use as arguments to
DBI
functions.
The following codes are supported:
- ?
-
The standard placeholder for bind values. These will be left untouched by this function.
- !l
-
List of placeholders. The corresponsing value in @params should be an array reference. Will be expanded to a comma-separated list of standard placeholders.
- !s
-
Will be replaced with the corresponding value in @params, without any quoting or escaping. Similar to the
%s
format of printf. There is a difference with simply adding a string in the query manually and using this formatting code, though:sqlprint()
will not attempt to interpret the string, so any formatting codes in the string are ignored.IMPORTANT: Do NOT use this format to insert user input in the query, or you will make your application vulnerable to SQL injection attacks.
- !W
-
Generates a
WHERE
clause. The corresponding value in @params is expected to be a hashref or arrayref, in both cases containing key/value pairs that will beAND
'ed together. If the hash or array is empty, noWHERE
clause will be generated at all.The keys should consist of strings that contain formatting codes, and the values can be either scalars (if the key only has one formatting code) or arrayrefs (if it has multiple). Note that you should still use an extra arrayref as value if you want to pass an arrayref as parameter. For example, if you have a key such as
'id IN(!l)'
, then the value should be[ [ id1, id2, .. ] ]
. - !H
-
Similar to
!W
, except it generates aSET
clause and the key/value pairs are joined together with a comma instead ofAND
.
The above explanation warrants some examples, here you go:
my($q, @p) = sqlprint('SELECT !s FROM threads !W LIMIT ?',
# parameter for !s
'id, title',
# parameter for !W
{ 'title like ?' => $title,
'id IN(!l)' => [ [ 2, 5 ] ] },
# parameter for ?
10
);
# $q = 'SELECT id, title FROM threads WHERE title like ? AND id IN(?,?) LIMIT ?'
# @p = ($title, 2, 5, 10);
my($q, @p) = sqlprint('UPDATE posts !H !W',
# parameter for !H:
{ 'uid = ?' => $user },
# parameter for !W, this time using an array
[ 'msg like ?' => $msg1,
'msg like ?' => $msg2 ]
);
# $q = 'UPDATE posts SET uid = ? WHERE msg like ? AND msg like ?'
# @p = ($user, $msg1, $msg2)
SEE ALSO
COPYRIGHT
Copyright (c) 2008-2019 Yoran Heling.
This module is part of the TUWF framework and is free software available under the liberal MIT license. See the COPYING file in the TUWF distribution for the details.
AUTHOR
Yoran Heling <projects@yorhel.nl>