Database interface for Perl 6
Perl6

README.pod

=encoding utf-8

=head1 NAME

DBIish - a simple database interface for Rakudo Perl 6

=for HTML <a href="https://travis-ci.org/perl6/DBIish"><img src="https://travis-ci.org/perl6/DBIish.svg?branch=master"></a>

=head1 SYNOPSIS

    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;

=head1 DESCRIPTION

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.

=head2 Connecting to, and disconnecting from, a database

You obtain a C<DataBaseHandler> by calling the static C<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:
C<database>, C<user> and C<password>.

For the different syntactic forms of
L<named arguments|https://doc.perl6.org/language/functions#Arguments> see
the language documentation.

For example, for connect to a database 'hierarchy' on Postresql, with the user in C<$user>
and using the function C<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
C<dispose> method:

  $dbh.dispose;

=head2 Fetching data

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

=head3 row

C<row> take the C<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);

=head3 allrows

C<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 C<array-of-hash>
and C<hash-of-array>

Example:

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

=head1 INSTALLATION

    $ panda install DBIish

=head1 DBDish CLASSES

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:

=head2 Pg (PostgreSQL)

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:
C<host>, C<hostaddr>, C<port>, C<database> (or its alias C<dbname>), C<user>, C<password>,
C<connect-timeout>, C<client-encoding>, C<options>, C<application-name>, C<keepalives>,
C<keepalives-idle>, C<keepalives-interval>, C<sslmode>, C<requiressl>, C<sslcert>, C<sslkey>,
C<sslrootcert>, C<sslcrl>, C<requirepeer>, C<krbsrvname>, C<gsslib>, and C<service>.

See your Postrgresql documentation for details.

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

Passing array to C<execute/do> is now implemented. But you can also use the
C<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));

=head3 B<pg-consume-input>

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

=head3 B<pg-notifies>

  $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 C<pg-notifies> the first time, if you have not executed any other
commands in the meantime you will also need to execute C<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.

=head3 B<pg-socket>

	my Int $socket = pg-socket;

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

=head2 SQLite

Supports basic CRUD operations and prepared statements with placeholders

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

=head2 mysql

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);

=head2 Oracle

Supports basic CRUD operations and prepared statements with placeholders

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

=head1 TESTING

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.

=head1 ROADMAP

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

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


=head1 SEE ALSO

The Perl 6 Pod in the L<doc:DBIish> module.
The Perl 5 L<doc:DBI> and L<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 L<Pod::To::HTML> is installed,

    perl6 --doc=html <filename>

=head1 COPYRIGHT

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

See the F<CREDITS> file for a list of all contributors.

=head1 LICENSE

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.