SQL::DB - Perl/SQL database interface
0.97_1. Development release.
use SQL::DB; my $db = SQL::DB->connect( $dsn, $dbuser, $dbpass ); ### Convenience Methods - thin wrappers around the main API $db->insert_into('purchases', values => {id => 1, count => 1}, ); $db->update('purchases', set => {count => 2}, where => {id => 1}, ); my $obj = $db->select( ['id','who','count'], from => 'customers', where => {id => 1}, ); # print $obj->id, $obj->who etc. $db->delete_from('purchases', where => {id => 1}, ); ### The Main API table 'people' => qw/ id name dob salary /; table 'purchases' => qw/ pid id /; my $people = urow('people'); # * 2 calculation takes place inside the database: $db->do( update => $people, set => $people->salary( $people->salary * 2 ), where => $people->dob == $today, ); my $purchases = srow('purchases'); my $row = $db->fetch1( select => [ $people->name, $ps->pid ], from => $people, left_join => $purchases, on => $purchases->id == $people->id, where => $people->id->in(1,2,3) .AND. $people->name->like('%paul%'), limit => 1, offset => 1, ); # then do stuff with $row->pid, $row->name etc my @rows = $db->fetch( select => [ sql_coalesce($p->pid, $p->cid)->as('pcid') ], from => $p, where => $p->cid->is_not_null, ); # coalesce column is available as $row->pcid my $cursor = $db->cursor( @query ...); while (my $row = $cursor->next) { print $row->column(), $row->some_other_column; } # Emulated sequences on SQLite $db->create_sequence('purchases'); my $id = $db->nextval('purchases'); # If you want the data your own way you can still use the query # syntax: my $sth = $db->sth( @query); map { print join(',',@$_) ."\n" } $sth->fetchall_arrayref; # Transactions provided by DBIx::Connector $db->txn( sub { # multiple statements }, catch => sub { die "WTF: $_"; });
SQL::DB is a Perl-to-SQL-database interface. By providing an interface that is very close to real SQL, SQL::DB give you unfettered access to the power and flexibility of the underlying database. It aims to be a tool for programmers who want their databases to work just as hard as their Perl scripts.
SQL::DB is capable of generating just about any kind of query, including aggregate expressions, joins, nested selects, unions, database-side operator invocations, and transactions. It has some minor cross database support, keeping the amount of database-specific code you have to write to a minimum.
Although rows can be retrieved from the database as simple objects, SQL::DB does not attempt to be an Object-Relational-Mapper al-la DBIx::Class.
there is This is nothing like a full-blown Object-Relational-Mapper (ORM) such as DBIx::Classin the The effort needed to use SQL::DB is primarily related to learning the query syntax, which is quite similar to SQL.
SQL::DB uses the Moo object system internally.
SQL::DB queries use abstract representations of table rows - objects that can be thought of as matching any row in a table. Abstract rows are obtained using the Schema->arow() method. The objects returned have methods that match the columns of a table, plus some extra SQL syntax/sugar methods.
my ( $cds, $tracks ) = $db->arow(qw/ cds tracks /);
The real power of SQL::DB lies in the way that SQL expressions can be constructed using these abstract columns. The abstract column methods do not really act on the abstract row, but intead return an expression object (SQL::DB::Expr). Using Perl's overload feature these objects can be combined and nested in Perl in a way that maps very closely to they way the SQL would be written by hand.
Perl Expression SQL Result ---------------------------------- --------------------------- $cds cds $cds->title cds.title $tracks->cd tracks.cd $tracks->id->func('count') count(tracks.cd) $tracks->set_length( SET tracks.length = $tracks->length + 10 ) tracks.length + 10 ( $cds->id == 1 ) .OR. cds.id = 1 OR $cds->title->like( '%Magic%' ) ) cds.title LIKE '%Magic%' ( $cds->id->is_not_null .AND. cds.id IS NOT NULL AND ! $tracks->id->in( 1, 2, 5 ) ) NOT tracks.id IN (1,2,5)
Here is a summary of the default available expression operators and methods. New expression subroutines can be generated in your own code - see SQL::DB::Expr for details.
Perl SQL Applies to --------- ------- ------------ .AND. AND All Expressions .OR. OR All Expressions ! NOT All Expressions .CONCAT. || or CONCAT All Expressions == = All Expressions != != All Expressions like LIKE Column only in IN Column only not_in NOT IN Column only is_null IS NULL Column only is_not_null IS NOT NULL Column only asc ASC Column only desc DESC Column only count COUNT Column only min MIN Column only max MAX Column only func('x',...) X(col,...) Column only
Here is a better example with multiple functions and multiple tables. For each CD, show me the number of tracks, the length of the longest track, and the total length of the CD in one query:
track = $db->arow('tracks'); @objs = $db->fetch( select => [ $track->cd->title, $track->id->func('count'), $track->length->func('max'), $track->length->func('sum') ], group_by => [ $track->cd->title, ], ); foreach my $obj (@objs) { print 'Title: ' . $obj->title ."\n"; print 'Number of Tracks: ' . $obj->count_id ."\n"; print 'Longest Track: ' . $obj->max_length ."\n"; print 'CD Length: ' . $obj->sum_length ."\n\n"; }
Emulated on systems that don't support native sequences. Based roughly on the PostgreSQL api.
This method is a convenience wrapper around new() for those who prefer DBI-style construction.
The new() constructor requires at least a 'dsn' option, and most likely you also want the 'dbuser' and 'dbpass' options as well. Also accepted are 'dbattrs', 'debug', 'dry_run' and 'prepare_mode'. See ATTRIBUTES below for the definitions.
Unless specified all attributes can be read and set.
General debugging state (true/false). Debug messages are 'warn'ed.
When set to false doesn't send any commands to the database. I would check the code to make sure I covered everything before you actually rely on this.
The DBI connection string.
The DBD driver name ('SQLite', 'mysql', 'Pg' etc) for the type of database we are connected to. Derived from the 'dsn' attribute.
The DBI connection username.
The DBI connection password.
The DBI connection attributes.
The handle connecting us to the database.
One of either 'prepare' or 'prepare_cached' (default). See DBI for details.
Documented here for completeness. This is used by the Moo object system at instantiation time.
Connect to a database. The parameters are passed directly to DBI->connect. This method also informs the internal table/column representations what type of database we are connected to, so they can set their database-specific features accordingly. Returns the dbh.
Creates the table $name and associated indexes and sequences in the database. Will warn and skip on any attempts to create tables that already exist.
Constructs a SQL::DB::Query object as defined by @query and runs that query against the connected database. Croaks if an error occurs. This is the method to use for any statement that doesn't retrieve values (eg INSERT, UPDATE and DELETE). Returns whatever value the underlying DBI->do call returns. This method uses "prepare_cached" to prepare the call to the database.
Constructs an SQL::DB::Query object as defined by @query and runs that query against the connected database. Croaks if an error occurs. This method should be used for SELECT-type statements that retrieve rows. This method uses "prepare_cached" to prepare the call to the database.
When called in array context returns a list of SQL::DB::Row based objects. The objects have accessors for each column in the query. Be aware that this can consume large amounts of memory if there are lots of rows retrieved.
When called in scalar context returns a query cursor (SQL::DB::Cursor) (with "next", "all" and "reset" methods) to retrieve dynamically constructed objects one at a time.
Similar to fetch() but always returns only the first object from the result set. All other rows (if any) can not be retrieved. You should only use this method if you know/expect one result. This method uses "prepare_cached" to prepare the call to the database.
Return an SQL::DB::Query object as defined by @query. This method is useful when creating nested SELECTs, UNIONs, or you can print the returned object if you just want to see what the SQL looks like.
An internal function for pretty printing SQL queries by inserting the bind values into the SQL itself. Returns a string.
The current date and time (as a string) that remains fixed within a transaction.
Runs a query and returns a SQL::DB::Cursor object. You can call next() and all() methods on this object to obtain data.
Runs a query and returns a DBI::st statement handle. You can call fetchrow_array() and other DBI method on this handle.
Insert $row into the database, where $row is an SQL::DB::Row based object returned from row() or fetch() methods.
Update $row in the database, where $row is an SQL::DB::Row based object returned from row() or fetch() methods.
Delete $row from the database, where $row is an SQL::DB::Row based object returned from row() or fetch() methods.
Runs the code in &coderef as an SQL transaction. If &coderef does not raise any exceptions then the transaction is commited, otherwise it is rolled back.
This method can be called recursively, but any sub-transaction failure will always result in the outer-most transaction also being rolled back. For savepoint support see DBIx::Connector.
Creates a sequence in the database. Takes the same arguments as Sequences are emulated on SQLite, used directly for PostgreSQL, and are unsupported for everything else.
$db->create_sequence( 'myseq' );
Advance the sequence to its next value and return that value. If $count is specified then a array of $count values are returned and the sequence incremented appropriately. Sequences are emulated on SQLite, used directly for PostgreSQL, and are unsupported for everything else.
Unimplemented.
Deploy the [ $type, $data ] pairs contained in $array_ref to the database. Only $array_ref elements greater than the previous deployment count (stored in the _sqldb table) will be deployed.
Returns the count of all deployments.
All of the following functions can be exported on demand, or all at once using the ':all' tag.
Define a table in the database by table name and columns.
Returns an object (or objects in array context) representing any row of table $name. This abstraction object is used for building SELECT statements with the 'sth', 'fetch', 'fetch1', 'cursor' etc methods. The returned object has a method for each column in the table.
Returns an object (or objects in array context) representing any row of table $name. This abstraction object is used for building UPDATE or DELETE statements with the 'do' method. The returned object has a method for each column in the table.
Create a new SQL::DB::Expr expression based on the given @statements. Scalars are uppercased with '_' converted to spaces, and ARRAYREF elements are joined together with ','.
All SQL::DB releases have so far been DEVELOPMENT!
SQL::DB jumped from version 0.18 to 0.98 due to a complete rewrite based on Moo. Lots of things were simplified, modules deleted, dependencies removed, etc. The API changed enough to almost give this distribution a new name, except I don't know of anyone using this apart from myself. 0.98 will be the last release marked as development, 0.99 will be a release candidate, and 1.00 will be the first stable release.
DBIx::Connector, SQL::DB::Expr, SQL::DB::Cursor
This distribution is still under development. Feedback, testing, bug reports and patches are all welcome.
https://rt.cpan.org/Public/Bug/Report.html?Queue=SQL-DB
git clone git://github.com/mlawren/sql-db.git
Mark Lawrence <nomad@null.net>
Copyright (C) 2007-2011 Mark Lawrence <nomad@null.net>
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version.
To install SQL::DB, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::DB
CPAN shell
perl -MCPAN -e shell install SQL::DB
For more information on module installation, please visit the detailed CPAN module installation guide.