DESCRIPTION
DBIx::Simple provides a simplified interface to DBI, Perl's powerful database module.EXAMPLES
General
#!/usr/bin/perl -w
use strict;
use DBIx::Simple;
# Instant database with DBD::SQLite
my $db = DBIx::Simple->connect('dbi:SQLite:dbname=file.dat')
or die DBIx::Simple->error;
# Connecting to a MySQL database
my $db = DBIx::Simple->connect(
'DBI:mysql:database=test', # DBI source specification
'test', 'test', # Username and password
{ RaiseError => 1 } # Additional options
);
# Using an existing database handle
my $db = DBIx::Simple->connect($dbh);
# Abstracted example: $db->query($query, @variables)->what_you_want;
$db->commit or die $db->error;
Simple Queries
$db->query('DELETE FROM foo WHERE id = ?', $id) or die $db->error;
for (1..100) {
$db->query(
'INSERT INTO randomvalues VALUES (?, ?)',
int rand(10),
int rand(10)
) or die $db->error;
}
$db->query(
'INSERT INTO sometable VALUES (??)',
$first, $second, $third, $fourth, $fifth, $sixth
);
# (??) is expanded to (?, ?, ?, ?, ?, ?) automatically
Single row queries
my ($two) = $db->query('SELECT 1 + 1')->list;
my ($three, $four) = $db->query('SELECT 3, 2 + 2')->list;
my ($name, $email) = $db->query(
'SELECT name, email FROM people WHERE email = ? LIMIT 1',
$mail
)->list;
Or, more efficiently:
$db->query('SELECT 1 + 1')->into(my $two);
$db->query('SELECT 3, 2 + 2')->into(my ($three, $four));
$db->query(
'SELECT name, email FROM people WHERE email = ? LIMIT 1',
$mail
)->into(my ($name, $email));
Fetching all rows in one go
One big flattened list (primarily for single column queries)
my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;
Rows as array references
for my $row ($db->query('SELECT name, email FROM people')->arrays) {
print "Name: $row->[0], Email: $row->[1]\n";
}
Rows as hash references
for my $row ($db->query('SELECT name, email FROM people')->hashes) {
print "Name: $row->{name}, Email: $row->{email}\n";
}
Fetching one row at a time
Rows into separate variables
{
my $result = $db->query('SELECT name, email FROM people');
$result->bind(my ($name, $email));
while ($result->fetch) {
print "Name: $name, Email: $email\n";
}
}
or:
{
my $result = $db->query('SELECT name, email FROM people');
while ($result->into(my ($name, $email))) {
print "Name: $name, Email: $email\n";
}
}
Rows as lists
{
my $result = $db->query('SELECT name, email FROM people');
while (my @row = $result->list) {
print "Name: $row[0], Email: $row[1]\n";
}
}
Rows as array references
{
my $result = $db->query('SELECT name, email FROM people');
while (my $row = $result->array) {
print "Name: $row->[0], Email: $row->[1]\n";
}
}
Rows as hash references
{
my $result = $db->query('SELECT name, email FROM people');
while (my $row = $result->hash) {
print "Name: $row->{name}, Email: $row->{email}\n";
}
}
Building maps (also fetching all rows in one go)
A hash of hashes
my $customers =
$db
-> query('SELECT id, name, location FROM people')
-> map_hashes('id');
# $customers = { $id => { name => $name, location => $location } }
A hash of arrays
my $customers =
$db
-> query('SELECT id, name, location FROM people')
-> map_arrays(0);
# $customers = { $id => [ $name, $location ] }
A hash of values (two-column queries)
my $names =
$db
-> query('SELECT id, name FROM people')
-> map;
# $names = { $id => $name }
EXAMPLES WITH SQL::Interp
If you have SQL::Interp installed, you can use the semi-abstracting method "iquery". This works just like "query", but with parts of the query interleaved with the bind arguments, passed as references.You should read SQL::Interp. These examples are not enough to fully understand all the possibilities.
The following examples are based on the documentation of SQL::Interp.
my $result = $db->iquery('INSERT INTO table', \%item);
my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);
# These two select syntax produce the same result
my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});
for ($result->hashes) { ... }
Use a syntax highlighting editor for good visual distinction.
If you need the helper functions "sql" and "sql_type", you can import them with "use SQL::Interp;"
EXAMPLES WITH SQL::Abstract
If you have SQL::Abstract installed, you can use the abstracting methods "select", "insert", "update", "delete". These work like "query", but instead of a query and bind arguments, use abstracted arguments.You should read SQL::Abstract. These examples are not enough to fully understand all the possibilities.
The SQL::Abstract object is available (writable) through the "abstract" property.
The following examples are based on the documentation of SQL::Abstract.
Overview
If you don't like the defaults, just assign a new object:
$db->abstract = SQL::Abstract->new(
case => 'lower',
cmp => 'like',
logic => 'and',
convert => 'upper'
);
If you don't assign any object, one will be created automatically using the default options. The SQL::Abstract module is loaded on demand.
my $result = $db->select($table, \@fields, \%where, \@order);
my $result = $db->insert($table, \%fieldvals || \@values);
my $result = $db->update($table, \%fieldvals, \%where);
my $result = $db->delete($table, \%where);
for ($result->hashes) { ... }
Complete examples
select
my @tickets = $db->select(
'tickets', '*', {
requestor => 'inna',
worker => ['nwiger', 'rcwe', 'sfz'],
status => { '!=', 'completed' }
}
)->hashes;
insert
If you already have your data as a hash, inserting becomes much easier:
$db->insert('people', \%data);
Instead of:
$db->query(
q[
INSERT
INTO people (name, phone, address, ...)
VALUES (??)
],
@data{'name', 'phone', 'address', ... }
);
update, delete
$db->update(
'tickets', {
worker => 'juerd',
status => 'completed'
},
{ id => $id }
)
$db->delete('tickets', { id => $id });
where
The "where" method is not wrapped directly, because it doesn't generate a query and thus doesn't really have anything to do with the database module.
But using the "abstract" property, you can still easily access it:
my $where = $db->abstract->where({ foo => $foo });
EXAMPLES WITH DBIx::XHTML_Table
If you have DBIx::XHTML_Table installed, you can use the result methods "xto" and "html".You should read DBIx::XHTML_Table. These examples are not enough to fully understand what is going on. When reading that documentation, note that you don't have to pass hash references to DBIx::Simple's methods. It is supported, though.
DBIx::XHTML_Table is loaded on demand.
Overview
To print a simple table, all you have to do is:
print $db->query('SELECT * FROM foo')->html;
Of course, anything that produces a result object can be used. The same thing using the abstraction method "select" would be:
print $db->select('foo', '*')->html;
A DBIx::XHTML_Table object can be generated with the "xto" (XHTML_Table Object) method:
my $table = $db->query($query)->xto;
Passing attributes
DBIx::Simple sends the attributes you pass to "html" both to the constructor and the output method. This allows you to specify both HTML attributes (like "bgcolor") and options for XHTML_Table (like "no_ucfirst" and "no_indent") all at once:
print $result->html(
tr => { bgcolor => [ qw/silver white/ ] },
no_ucfirst => 1
);
Using an XHTML_Table object
Not everything can be controlled by passing attributes. For full flexibility, the XHTML_Table object can be used directly:
my $table = $db->query($query)->xto(
tr => { bgcolor => [ qw/silver white/ ] }
);
$table->set_group('client', 1);
$table->calc_totals('credit', '%.2f');
print $table->output({ no_ucfirst => 1 }); # note the {}!
EXAMPLES WITH Text::Table
- "$result->text("neat")"
-
Neither neat nor pretty, but useful for debugging. Uses DBI's "neat_list"
method. Doesn't display column names.
'1', 'Camel', 'mammal' '2', 'Llama', 'mammal' '3', 'Owl', 'bird' '4', 'Juerd', undef - "$result->text("table")"
-
Displays a simple table using ASCII lines.
id | animal | type ---+--------+------- 1 | Camel | mammal 2 | Llama | mammal 3 | Owl | bird 4 | Juerd | - "$result->text("box")"
-
Displays a simple table using ASCII lines, with an outside border.
+----+--------+--------+ | id | animal | type | +----+--------+--------+ | 1 | Camel | mammal | | 2 | Llama | mammal | | 3 | Owl | bird | | 4 | Juerd | | +----+--------+--------+
For "table" and "box", you need Anno Siegel's Text::Table module installed.