=head1 NAME

SQL::DB::Tutorial - How to use SQL::DB

=head1 INTRODUCTION

***** Incomplete and Inaccurate - just random notes at this stage *****

This tutorial assumes you already have some familiarity with the
Perl L<DBI> module and the Structured Query Language (SQL), as SQL::DB
is a rather thin interface between the two.

The typical object mapper system (such as DBIx::Class, Rose::DB,
Class::DBI, Tangram, etc) goes through great pains to hide the details
of the database. The B<SQL::DB> philosophy is very much the opposite.
As a very thin interface to SQL the goal is to allow the Perl programmer
access to whatever functionality the database allows.

The ORMs are naturally object-based, so they by necessity hide the
details. SQL::DB is a low-level system, which exposes the lower details.


B<SQL::DB> is not an SQL abstraction, because I don't believe it makes
sense to try. There range of different syntax and functionality offered
by the various SQL implementations that an abstraction wishing to
cover all of them has to limit itself to the subset of common
functionality. Or it creates an entirely new API and does lots of
programming in the background to implement the missing features, and often
much less efficient in the native case.


=head1 DEFINING A SCHEMA


=head1 CONNECTING TO A DATABASE


  use SQL::DB;
  my $db = SQL::DB->new(<table definitions>);

  $db->connect($dbi, $user, $pass, $attrs);

  if (@ARGV and $ARGV[0] eq '--install') {
      $db->deploy;
  }

  my $artist  = Artist->arow;
  my $cd      = CD->arow;

  my @objects = $db->simple_objects(
      select   => [$cd->title, $artist->name],
      from     => [$cd, $artist],
      where    => !($track->length < 248) & ($cd->year > 1997)
      order_by => [$track->title->asc],
      limit    => 5,
  );

  foreach my $obj (@objects) {
      print $obj->title, ',', $obj->name, "\n";
  }

=head1 DESCRIPTION

B<SQL::DB> provides a low-level interface to SQL databases. It allows
you to generate and run queries using Perl constructs such as objects
and logic operators. It is not quite a full Object Mapping Layer
(such as L<Class::DBI>) but is also more than a pure SQL abstraction (such
as L<SQL::Abstract>). It falls somewhere inbetween.

=head1 TUTORIAL

=head2 Schema Definition

B<SQL::DB> needs to know the structure of the database tables and
columns, and their inter-relationships (eg primary & foreign keys).
The schema is built (as defined by L<SQL::DB::Schema>) as follows.
We will use the age-old Music Album example consisting of Artists,
their CDs, and the Tracks on the CDs.
 
  my $schema = SQL::DB->schema(
    [   
        table   => 'artists',
        columns => [
            [
                name    => 'id',
                type    => 'INTEGER',      # mandatory, any SQL type
                primary => 1,              # optional
            ],
            [
                name    => 'name',
                type    => 'VARCHAR(255)',
                unique  => 1,              # optional
            ],
        ],
    ],
    [
        table   => 'cds',
        columns => [
            [
                name    => 'id',
                type    => 'INTEGER',
                primary => 1,
            ],
            [
                name    => 'artist',
                type    => 'INTEGER',
                references => 'artists(id)',
            ],
            [
                name    => 'title',
                type    => 'VARCHAR(255)',
            ],
        ],
        unique => [
            ['artist,title'],
        ],
        index => [
            columns => ['artist'],
        ],
    ],
    [
        table   => 'tracks',
        columns => [
            [
                name    => 'id',
                type    => 'INTEGER',
                primary => 1,
            ],
            [
                name    => 'cd',
                type    => 'INTEGER',
                references => 'artists(id)',
            ],
            [
                name    => 'title',
                type    => 'VARCHAR(255)',
            ],
            [
                name    => 'length',
                type    => 'INTEGER',
            ],
        ],
        unique => [
            ['cd,title'],
        ],
        index => [
            columns => ['cd'],
        ],
    ],
  );

Column definitions may also include 'null', 'unique' and 'default'
values, which which will be used at table creation time. If you want
to see the SQL generated for creating the tables you can simply
"print $schema->tables;".

The order in which the tables are defined is important, just as
if you were creating the tables in SQL. Tables with foreign
key definitions should come _after_ the table definitions they refer
to.

=head2 Database Connection

Connecting to a database is basically the same as for L<DBI> with
an additional schema argument. The object returned from the connect
call is the handle to be used for all queries against the database.

  my $db = SQL::DB->connect($dbi, $user, $pass, $attrs, $schema)

=head2 Table Creation

If your tables do not already exist in the database B<SQL::DB> can
create them for you with a simple call to the deploy() method.

  $db->deploy();

It is safe to call this even if the tables do already exist. B<SQL::DB>
will just emit a warning and continue.

=head2 Abstract Rows

All queries with B<SQL::DB> depend on abstract representations
of table rows. An abstract row is obtained using the arow()
method. The object returned has methods that match the columns of
a table, plus some extra methods to compare columns in an SQL-like
way.

So we obtain an object that could represent any CD and use in 
it expressions like so:

  my $cd    = $db->arow('cds');
  my $expr1 = ($cd->id == 1);
  my $expr2 = ($cd->title->like('%Kind of Magic%'));
  my $expr3 = ($cd->id != 1) & ($cd->artist->in(1,2,5));

Very powerful expressions can be created using this combination
of abstract rows and the Perl logic operators. More details on
this in the "EXPRESSIONS" section below.

If a table column (such as the 'cds.artist' column) references a foreign
key then you can "follow through" to reach the columns of that table
as well. So to refer to the 'artists.name' column connected to the
abstract CD row we can use "$cd->artist->name" in any expression.

  my $expr4 = ($cd->artist->name == 'Queen');

On the SQL side B<SQL::DB> automatically matches up the foreign
keys for you, so there is no need to go comparing $cd->artist
with $cd->artist->id. There are more examples of this in the
"ADVANCED EXAMPLES" section below.

=head2 Row Insertion

  my $artist = $db->arow('artists');
  $db->insert(
      columns => [$artist->id, $artist->name],
      values  => [1, 'Queens'],
  );

You do not have to specify every column for an insertion provided
of course that the table definition has appropriate DEFAULTs
or allows NULLs.

=head2 Row Updates

Updating existing rows is similar to row insertion with the
additional possibility of filtering  - ie the WHERE clause.

  my $artist = $db->arow('artists');
  $db->update(
      columns => [$artist->name],
      set     => ['Queen'],
      where   => ($artist->name == 'Queens'),
  );

=head2 Row Deletion

Row deletion works the same way although you still have to specify
a column in the 'columns' field, and SQL::DB works out which row/table
it is.

  my $artist = $db->arow('artists');
  $db->delete(
      columns => [$artist->id],
      where   => $artist->name->like('Q%')
  );

=head2 Row Selection

Selection is a slightly different case because we expect data to
be returned. A successful "select" call returns a list of objects,
whose methods match the columns retrieved from the database.

  my $artist = $db->arow('artists');
  my @objs   = $db->select(
      columns => [$artist->id, $artist->name],
      where   => ($artist->id < 3)
  );
  
  foreach my $obj (@objs) {
      print $obj->id .'='. $obj->name ."\n";
  }

=head2 Disconnection

When you are finished with the database you can disconnect.
Disconnection also happens automatically if the $db object goes
out of scope and is destroyed.

  $db->disconnect;

There are lower-level methods available for creating queries
or accessing the DBI handle directly, as described in the METHODS
section below.

=head1 ADVANCED EXAMPLES

The above is all quite ordinary and not much different from writing
the SQL statements directly. However, given that B<SQL::DB> is aware
of of inter-table relationships we can make much more powerful queries.

The examples here are probably not good SQL as I'm not an SQL expert,
but the point is B<SQL::DB> is powerful enough to produce what you
want if you know what you are doing. It is also powerful enough for
you to shoot yourself in the foot.

=head2 Search using implicit join

Lets do a search to find all the track titles for our Artist 'Queen',
limited to the first 5, unique tracks ordered by reverse name.

  my $track  = $db->arow('tracks');
  my @tracks = $db->select(
      columns  => [$track->id, $track->title],
      distinct => 1,
      where    => ($track->cd->artist->name == 'Queen')
      order_by => [$track->name->desc],
      limit    => 5,
  );

What happens here is that B<SQL::DB> understands the relationships
inside $track->cd->artist and builds the appropriate statements
to link those tables together based on the primary and foreign keys.

=head2 Only retrieve desired columns

Columns that are not in the 'columns' list are simply not retrieved
and do not exist as methods in the returned object. So for the above
query trying to call 'length' on a returned object will die.

If you want to retrieve the whole row you don't have to specify every
column. Use the abstract row's _columns() method.

      columns  => [$track->_columns],

=head2 Select from more than one table

There is nothing to stop us selecting columns from different tables
in the same query. Show me the Artist names and their Albumn titles
where the tracks are longer than 276 seconds:

  my $track  = $db->arow('tracks');
  my @objs = $db->select(
      columns  => [$track->cd->artist->name, $track->cd->title],
      distinct => 1,
      where    => ($track->length > 276)
  );
  
  foreach my $obj (@objs) {
      print $obj->name, $obj->title,"\n"; # OK
      print $obj->length, "\n";           # dies - column not retrieved
  }

The limitation with this is of course that all of the column names
retrieved must be unique. It is no good selecting the 'artists.id'
and 'cds.id' columns - there is no way to differentiate between
the two using B<SQL::DB> this way. Take a look at the execute() method
to get around this.

=head2 Nested/multiple queries, subselects

It is possible to perform subselects by defining a query (without
running it) via the schema object, and using that query as an
expression inside another one.

  my $track = $db->arow('tracks');
  my $query = $db->schema->select(
      columns => [$track->cd->artist->id],
      where   => ($track->title == 'Gimme the Prize'),
  );

  my $artist = $db->arow('artists');
  $db->select(
      columns => [$artist->name],
      where   => ($artist->id->not_in($query)),
  );

  # UNION?

  $db->select(
      columns   => [$artist->name],
      union     => $query,
      order_by  => [$artist->name],
  );

Notice that we used two abstract rows instead of following through,
because the two queries are in fact independent from each other.

=head2 Database functions

B<SQL::DB> has support for arbitary database functions. Use the
func($func) method on any abstract column and the returned object
will have a method called $func_$column.

  my $track  = $db->arow('tracks');
  my @objs = $db->select(
      columns  => [$track->id->func('count')],
      distinct => 1,
      where    => ($track->length > 276)
  );
  
  print "# tracks > 276 seconds: ",
        $objs->[0]->count_id, "\n"; # OK

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->select(
      columns   => [
                     $track->id->func('count'),
                     $track->cd->title,
                     $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";
  }

For interests sake, here is the actual SQL:

  SELECT
      COUNT(t33.id),
      t34.title,
      MAX(t33.length),
      SUM(t33.length)
  FROM
      tracks AS t33,
      cds AS t34
  WHERE
      (t33.cd = t34.id)
  GROUP BY
      t34.title

=head2 Relationships

One thing to remember is that using B<SQL::DB> you can only get
to foreign tables through the reference/foreign key, not the other
way around. Ie there is no $cd->tracks method. I'm still having a
think about if/how this should be implemented or left to higher layers.


=head1 EXPRESSIONS

The real power of B<SQL::DB> lies in the way that WHERE
$expressions are constructed.  Abstract columns and queries are derived
from an expression class. Using Perl's overload feature they can be
combined and nested any way to very closely map Perl logic to SQL logic.

  Perl          SQL             Applies to
  ---------     -------         ------------
  &             AND             Expressions
  |             OR              Expressions
  !             NOT             Expressions
  ==            ==              Column
  like          LIKE            Column
  in            IN              Column
  not_in        NOT IN          Column
  is_null       IS NULL         Column
  is_not_null   IS NOT NULL     Column
  exists        EXISTS          Expressions
  asc           ASC             Column (ORDER BY)
  desc          DESC            Column (ORDER BY)
  func('x')     X(column)       Column

See L<To::Be::Written> for more details.

=head1 OBJECT INTERFACE

If the query used a simple "select" then returns a list of simple
Class::Accessor-based objects whose method names correspond to the
columns or functions in the query.

If the query used a "selecto" then returns a list of SQL::DB::Object
-based objects.

=head1 AUTHOR

Mark Lawrence E<lt>nomad@null.netE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2007,2008 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 2 of the License, or
(at your option) any later version.

=cut

# vim: set tabstop=4 expandtab: