DBIx::...amples(3pm)

✨ DBIx::...amples(3pm) ✨

okay so bestie, this is DBIx::...amples(3pm), straight out of the User Contributed Perl Documentation, and it is literally a whole guided tour of DBIx::Simple by example 🫶 if you've ever looked at a database and thought "why is this so much", this page is for you. grab a snack, we're doing this together 💖

---

🌸 NAME 🌸

DBIx::Simple::Examples — examples of how to use DBIx::Simple 💅

🦋 DESCRIPTION 🦋

DBIx::Simple gives you a simplified interface to DBI, which is Perl's powerful database module. that's the whole description, and honestly? iconic. periodt.

---

💅 EXAMPLES 💅

✨ General ✨

here's the whole starter-pack vibe: connecting, handles, committing — the way that this all fits together is genuinely so satisfying:

#!/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 🌈

deletes, inserts, and the lil (??) trick that expands itself — obsessed, honestly:

$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 🎀

grabbing exactly one row, two ways. first the list way:

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 — bind first, then fetch:

{
    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) 💜

🍓 map 🍓

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, ... }

🧚 group 🧚

A hash of arrays of hashes:

my $customers =
    $db
    -> query('SELECT id, name, location FROM people')
    -> group_hashes('location');

# $customers = { $location => [ { id => $id, name => $name }, ... ], ... }

A hash of arrays of arrays:

my $customers =
    $db
    -> query('SELECT id, name, location FROM people')
    -> group_arrays(2);

# $customers = { $location => [ [ $id, $name ], ... ], ... }

A hash of arrays of values (two-column queries):

my $names =
    $db
    -> query('SELECT location, name FROM people')
    -> group;

# $names = { $location => [ $name, $name, ... ], ... }

---

🔥 EXAMPLES WITH SQL::Interp 🔥

okay so if you have SQL::Interp installed, you can use the semi-abstracting method "iquery". it works just like "query", except parts of the query are interleaved with the bind arguments, passed as references. it's giving "query but make it art" 💅

you really should read SQL::Interp. these examples are not enough to fully understand all the possibilities. fr fr.

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. lowkey the best advice on this whole page.

and 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, you use abstracted arguments. main character energy, honestly.

you should read SQL::Abstract. these examples are not enough to fully understand all the possibilities. don't skip it bestie 🙈

the SQL::Abstract object is available (writable) through the "abstract" property. yes, writable. you can just... change it. slay.

the following examples are based on the documentation of SQL::Abstract.

🌟 Overview 🌟

if you don't like the defaults, just assign a whole new object:

$db->abstract = SQL::Abstract->new(
    case    => 'lower',
    cmp     => 'like',
    logic   => 'and',
    convert => 'upper'
);

and if you don't assign any object, one gets created automatically using the default options. the SQL::Abstract module is loaded on demand. so chill.

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 so 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 🌸

okay so 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. it's just out here doing its own thing.

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". tables but make it fashion 💅

you should read DBIx::XHTML_Table. these examples are not enough to fully understand what is going on. and when you're reading that documentation, note that you don't have to pass hash references to DBIx::Simple's methods. it is supported, though. okay bestie? 🫶

DBIx::XHTML_Table is loaded on demand.

✨ Overview ✨

to print a simple table, literally all you have to do is:

print $db->query('SELECT * FROM foo')->html;

and 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" to both the constructor and the output method. which means you can specify HTML attributes (like "bgcolor") and options for XHTML_Table (like "no_ucfirst" and "no_indent") all at once. two birds, one stone, iconic:

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 {}!

note the {}! seriously bestie, that curly-brace thing is doing so much work there. don't forget it 💀

✨ EXAMPLES WITH Text::Table ✨

Okay bestie, this is the part where your data gets glowed up for the terminal 💅 Three flavours of text(), one for every mood.

---

• $result->text("neat") — lowkey neither neat nor pretty, but honestly iconic for debugging 🐛 It uses DBI's neat_list method under the hood. Heads up: it does not display column names. Periodt.

'1', 'Camel', 'mammal'
'2', 'Llama', 'mammal'
'3', 'Owl', 'bird'
'4', 'Juerd', undef

• $result->text("table") — now this is giving organized. A simple table drawn with ASCII lines, no cap 🎀

id | animal |  type
---+--------+-------
 1 |  Camel | mammal
 2 |  Llama | mammal
 3 |  Owl   | bird
 4 |  Juerd |

• $result->text("box") — same simple ASCII-line table, but it said "let me serve looks" and added an outside border around the whole thing 🫶 main character energy fr fr

+----+--------+--------+
| id | animal |  type  |
+----+--------+--------+
|  1 |  Camel | mammal |
|  2 |  Llama | mammal |
|  3 |  Owl   | bird   |
|  4 |  Juerd |        |
+----+--------+--------+

💡 Bestie, for "table" and "box" you need Anno Siegel's Text::Table module installed. No module, no box. That's the vibe check. 🙈

---

👑 AUTHOR 👑

Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/> 💖

---

🌈 SEE ALSO 🌈

• DBIx::Simple
• SQL::Abstract

---

And that's a wrap on the whole page, bestie!! 🎉 This was perl v5.36.0, dated 2023-09-01, and it's the DBIx::...amples(3pm) page — go forth and query slayfully 💅✨🌸