DBIish - a simple database interface for Rakudo Perl 6

use v6;
use DBIish;

my $dbh = DBIish.connect("SQLite", :database<example-db.sqlite3>);

my $sth = $dbh.do(q:to/STATEMENT/);
    DROP TABLE IF EXISTS nom
    STATEMENT

$sth = $dbh.do(q:to/STATEMENT/);
    CREATE TABLE nom (
        name        varchar(4),
        description varchar(30),
        quantity    int,
        price       numeric(5,2)
    )
    STATEMENT

$sth = $dbh.do(q:to/STATEMENT/);
    INSERT INTO nom (name, description, quantity, price)
    VALUES ( 'BUBH', 'Hot beef burrito', 1, 4.95 )
    STATEMENT

$sth = $dbh.prepare(q:to/STATEMENT/);
    INSERT INTO nom (name, description, quantity, price)
    VALUES ( ?, ?, ?, ? )
    STATEMENT

$sth.execute('TAFM', 'Mild fish taco', 1, 4.85);
$sth.execute('BEOM', 'Medium size orange juice', 2, 1.20);

$sth = $dbh.prepare(q:to/STATEMENT/);
    SELECT name, description, quantity, price, quantity*price AS amount
    FROM nom
    STATEMENT

$sth.execute();

my @rows = $sth.allrows();
say @rows.elems; # 3

$sth.finish;

$dbh.dispose;

The DBIish project provides a simple database interface for Perl 6.

It's not a port of the Perl 5 DBI and does not intend to become one. It is, however, a simple and useful database interface for Perl 6 that works now. It looks like a DBI, and it talks like a DBI (although it only offers a subset of the functionality).

It is based on Martin Berends' MiniDBI project, but unlike MiniDBI, DBDish aims to provide an interface that takes advantage of Perl 6 idioms.

You obtain a DataBaseHandler by calling the static DBIish.connect method, passing as the only positional argument the driver name followed by any required named arguments.

Those named arguments are driver specific, but commonly required ones are: database, user and password.

For the different syntactic forms of named arguments see the language documentation.

For example, for connect to a database 'hierarchy' on PostgreSQL, with the user in $user and using the function get-secret to obtain you password, you can:

my $dbh = DBIish.connect('Pg', :database<hierarchy>, :$user, password => get-secret());

See ahead more examples.

To disconnect from a database and free the allocated resources you should call the dispose method:

$dbh.dispose;

DBIish provides nearly all the perl5 DBI fetch* method to fetch values from the StatementHandle object. However it's recommended to use the row and allrows methods. They provide you typed values

row take the hash adverb if you want to have the values in a Hash form instead of a plain Array

Example:

my @values = $sth.row();
my %values = $sth.row(:hash);

allrows lazily returns all the row as a list of arrays. If you want to fetch the values in a hash form, use one of the two adverbs array-of-hash and hash-of-array

Example:

my @data = $sth.allrows(); # [[1, 2, 3], [4, 5, 6]]
my @data = $sth.allrows(:array-of-hash); # [ ( a => 1, b => 2), ( a => 3, b => 4) ]
my %data = $sth.allrows(:hash-of-array); # a => [1, 3], b => [2, 4]

$ zef install DBIish

Some DBDish drivers install together with DBIish.pm6 and are maintained as a single project.

Search the Perl 6 ecosystem for additional DBDish drivers.

Currently the following backends are included:

Supports basic CRUD operations and prepared statements with placeholders

my $dbh = DBIish.connect('Pg', :host<db01.yourdomain.com>, :port(5432),
        :database<blerg>, :user<myuser>, :$password);

Pg supports the following named arguments: host, hostaddr, port, database (or its alias dbname), user, password, connect-timeout, client-encoding, options, application-name, keepalives, keepalives-idle, keepalives-interval, sslmode, requiressl, sslcert, sslkey, sslrootcert, sslcrl, requirepeer, krbsrvname, gsslib, and service.

See your PostgreSQL documentation for details.

Pg array are supported when fetching array fields with row/allrows. You will get the properly typed array according to the field type.

Passing array to execute/do is now implemented. But you can also use the pg-array-str method on your Pg StatementHandle to convert an Array to a string Pg can understand:

#prepare an insertion of an array field;
$sth.execute($sth.pg-array-str(@data));

Consume available input from the server, buffering the read data if there is any. This is only necessary if you are planning on calling pg-notifies without having requested input by other means (such as an execute.)

$ret = $dbh.pg-notifies;

Looks for any asynchronous notifications received and returns a pg-notify object that looks like this

class pg-notify {
    has Str                           $.relname; # Channel Name
    has int32                         $.be_pid; # Backend pid
    has Str                           $.extra; # Payload
}

or nothing if there is no pending notifications.

In order to receive the notifications you should execute the PostgreSQL command "LISTEN" prior to calling pg-notifies the first time, if you have not executed any other commands in the meantime you will also need to execute pg-consume-input first.

For example:

$db.do("LISTEN foo");

loop {
    $db.pg-consume-input
    if $db.pg-notifies -> $not {
        say $not;
    }
}

The payload is optional and will always be an empty string for PostgreSQL servers less than version 9.0.

my Int $socket = $db.pg-socket;

Returns the file description number of the connection socket to the server.

Supports basic CRUD operations and prepared statements with placeholders

my $dbh = DBIish.connect('SQLite', :database<thefile>);

The :database parameter can be an absolute file path as well (or even an IO::Path object):

my $dbh = DBIish.connect('SQLite', database => '/path/to/sqlite.db' );

If the SQLite library was compiled to be threadsafe (which is usually the case), then it is possible to use SQLite from multiple threads. This can be introspected:

say DBIish.install-driver('SQLite').threadsafe;

SQLite does support using one connection object concurrently, however other databases may not; if portability is a concern, then only use a particular connection object from one thread at a time (and so have multiple connection objects).

When using a SQLite database concurrently (from multiple threads, or even multiple processes), operations may not be able to happen immediately due to the database being locked. DBIish sets a default timeout of 10000 miliseconds; this can be changed by passing the busy-timeout option to connect.

my $dbh = DBIish.connect('SQLite', :database<thefile>, :60000busy-timeout);

Passing a value less than or equal to zero will disable the timeout, resulting in any operation that cannot take place immediately producing a database locked error.

Supports basic CRUD operations and prepared statements with placeholders

my $dbh = DBIish.connect('mysql', :host<db02.yourdomain.com>, :port(3306),
        :database<blerg>, :user<myuser>, :$password);
# Or via socket:
my $dbh = DBIish.connect('mysql', :socket<mysql.sock>,
        :database<blerg>, :user<myuser>, :$password);

Since MariaDB uses the same wire protocol as MySQL, the `mysql` backend also works for MariaDB.

Supports basic CRUD operations and prepared statements with placeholders

my $dbh = DBIish.connect('Oracle', database => 'XE', :user<sysadm>, :password('secret'));

The DBIish::CommonTesting module, now with over 100 tests, provides a common unit testing that allows a driver developer to test its driver capabilities and the minimum expected compatibility.

Add some more drivers. Improve robustness of all drivers. Improve the test suite. Attract more contributors.

Integrate with the DBDI project (http://github.com/timbunce/DBDI) once it has sufficient functionality.

The Perl 6 Pod in the doc:DBIish module. The Perl 5 doc:DBI and doc:DBI::DBD.

This README and the documention of the DBIish and the DBDish modules are in the Pod6 format. It can be extracted by running

perl6 --doc <filename>

Or, if Pod::To::HTML is installed,

perl6 --doc=html <filename>

Written by Moritz Lenz, based on the MiniDBI code by Martin Berends.

See the CREDITS file for a list of all contributors.

Copyright © 2009-2016, the DBIish contributors All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice,
  this list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright
  notice, this list of conditions and the following disclaimer in the
  documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.