The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
SQL-DB-0.18
/ SQL::DB::Tutorial

NAME

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

INTRODUCTION

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

This tutorial assumes you already have some familiarity with the Perl 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 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.

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.

DEFINING A SCHEMA

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";
  }

DESCRIPTION

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 Class::DBI) but is also more than a pure SQL abstraction (such as SQL::Abstract). It falls somewhere inbetween.

TUTORIAL

Schema Definition

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

Database Connection

Connecting to a database is basically the same as for 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)

Table Creation

If your tables do not already exist in the database 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. SQL::DB will just emit a warning and continue.

Abstract Rows

All queries with 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 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.

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.

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

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

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";
  }

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.

ADVANCED EXAMPLES

The above is all quite ordinary and not much different from writing the SQL statements directly. However, given that 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 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.

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

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],

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 SQL::DB this way. Take a look at the execute() method to get around this.

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.

Database functions

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

Relationships

One thing to remember is that using 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.

EXPRESSIONS

The real power of 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 To::Be::Written for more details.

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.

AUTHOR

Mark Lawrence <nomad@null.net>

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.