• NAME
• SYNOPSIS
• ABSTRACT
• DESCRIPTION
• LANGUAGE
• METHODS
• AUTHOR
• COPYRIGHT

NAME

DBIx::DAL -- Database Abstraction Language

   use DBIx::DAL;

   my $f = 3;
   dal->connect("mysql:dbname:localhost", "user", "pass");
   my $id = query "foo[blah = 1 | 2, baz = ?](id)", $f;
   dal->disconnect;
[download]

ABSTRACT

Let's face it: SQL is ugly, especially when a query uses data from multiple tables. DBIx::DAL is an attempt to solve this problem. This module implements a mini language that is converted to SQL. All SQL is tailored to MySQL, so it may or may not work with other databases. There is no support for database creation, only for accessing, storing, and changing data.

DESCRIPTION

LANGUAGE

To demonstrate how DAL works, we will use a table of colors that have an id and a rating (1-10). All queries will be done from this table.

 +----Table: colors-----+
 | id | name   | rating |
 +----+--------+--------+
 |  1 | blue   |      9 |
 |  2 | green  |     10 |
 |  3 | red    |      7 |
 |  4 | orange |     10 |
 |  5 | brown  |      3 |
 +----+--------+--------+
[download]

First off you want to want to select the rating of the color green. That would be done using this command:

  my $color = "green";
  my ($ans) = query "colors[name = ?](rating)", $color;
  print "Rating: ", $ans->[0], "\n"; # prints "Rating: 10\n"
[download]

As you can see, the table name comes first, followed by the where clause and, finally, what it is that you want to select. We used a binding for the value of the color (the ?) that allows us to pass the variable we want to use in order to make sure it's quoted right. To select all the columns from the table, don't specify any: table[]().

All results from the query are returned, as an array of anonymous arrays, which correspond to the different rows returned.

If you want to alter the data in the table, the command is very similar. Someone has decided that they don't like green so much, so we'll change its rating to 8.

  my ($color, $new_rating) = ("green", 8);
  query "colors[name = ?](rating) = ?", $color, $new_rating;
[download]

That has the same order: table name, where clause, and column selection. In fact it is identical to the above command, except that here we do an assignment, using =. We even use a binding for the value, as we did before.

Statements in the where clause are seperated by commas, which mean 'and'. If you want an 'or', use the keyword or. Junctions, like those coming in perl 6, are also supported -- 'or' as | and 'and' as &.

It's important to note here that we can nest calls. In other words, say we have a table of items which each have a color.

 +-----Table: items------+
 | id | name  | color_id |
 +----+-------+----------+
 |  1 | sky   |        1 |
 |  2 | brick |        3 | 
 |  3 | tree  |        5 |
 |  4 | water |        1 |
 +----+-------+----------+
[download]

Now we decide we want to know the rating of the color of the sky. Instead of making the query much more complex by using identifiers for each table name, we do this:

  my ($item) = ("sky");
  my ($ans) = query "colors[id = items[name = ?](color_id)](rating)", 
+$item;
  print "Rating: ", $ans->[0], "\n"; # prints "Rating: 9\n"
[download]

Now someone wants to add a leaf to the items table.

  my ($item, $color) = ("leaf", "green");
  query "items(name, color_id) = ?, colors[name = ?](id)";
[download]

To insert items, leave out a where clause and do an assignment, similar to an alter. DAL will automatically seperate the values into the different rows (if you specify a column, you must pass a value for it, even if that value is ''). Optionally, you can surround the different rows with square brackets.

METHODS

connect('type:database:host', username, password)

Opens a connection to the specified database with the given username and password. Call using dal.

dal

This method returns 'DBIx::DAL'. This is done so that methods like connect and disconnect don't need to be imported. Instead, you can call them using dal. dal->connect

disconnect

Closes the connection to the current database. Call using dal.

query('query string' [, vars])

This is the query interface. Pass variables along with it for any bindings in the query string.

The return value is a list of all rows that match the query. The row is represented as an anonymous array that contains the values for the columns that were requested.

AUTHOR

Matt Diephouse <matt@diephouse.com>