/ 
SQL-DB-0.19_4
/ SQL::DB::Schema

NAME

SQL::DB::Schema - Model tables and rows for SQL::DB queries

VERSION

0.18. Development release.

SYNOPSIS

package My::Schema;
use SQL::DB::Schema;

table 'cars' => qw/ id make model /;

table 'people' => qw/ id name carmake carmodel /,
    { col      => 'photo',
      btype    => 'SQL_BLOB',
      btype_Pg => '{ pg_type => DBD::Pg::BYTEA }',
    },
);

end_schema();

# in your application somewhere
use SQL::DB;
use My::Schema; # imports cars() and people()

my $db = SQL::DB->new(...);

my $cars = srow('cars');
$db->do(
  insert_into => people(qw/id name carmake carmodel/),
  select  => [ 1, 'Harry', $cars->make, $cars->model ],
  from    => $cars,
  where   => $cars->make == 'Ford',
);

my $people = urow('people');
$db->do(
  update => $people,
  set    => [ $people->name('Linda') ],
  where  => $people->id == 1,
);

DESCRIPTION

SQL::DB provides a low-level interface to SQL databases, using Perl objects and logic operators. SQL::DB::Schema is used to specify database tables in a way that can be used in SQL::DB queries.

NOTE: You probably don't want to be writing your schema classes manually. Build them using sqldb-builder(1) instead.

See SQL::DB for how to use your schema classes.

FUNCTIONS

table( $table, @columns )

Define a table in the schema.

row( $name, @args ) -> SQL::DB::Row::$name

Returns a new object representing a single row of the table $name, with values as specified by @args. Such an object can be inserted (or potentially updated or deleted) by SQL::DB.

arow( $name ) -> SQL::DB::ARow::$name

Returns an object representing any row of table $name. This abstraction object is used with the SQL::DB 'fetch' and 'do' methods.

arows( @names ) -> @{ SQL::DB::ARow::$name1, ... }

Returns objects representing any row of a table. These abstraction objects are used with the SQL::DB 'fetch' and 'do' methods.

end_schema

Called when you have finished defining your schema. This imports a bunch of functions necessary to use the schema.

expr( @statements )

Create a new expression based on @statements. This is a very dumb function. All plain string statements are uppercased with all occurences of '_' converted to spaces.

_expr_join( $separator, @expressions )

Does the same as Perl's 'join' built-in but for SQL::DB::Expr objects. See BUGS below for why this is needed.

_bexpr( $item )

Return $item if it is already an expression, or a new SQL::DB::Expr object otherwise.

_expr_binary( $op, $e1, $e2, $swap )

An internal method for building binary operator expressions.

AND, OR

These subroutines let you write SQL logical expressions in Perl using string concatenation:

( $e1 .AND. $e2 ) .OR. ( $e3 .AND. $e4 )

Note that due to operator precedence, expressions either side of .AND. or .OR. should be bracketed if they are not already single expression objects.

Things are implemented this way due to Perl not allowing the overloading of the 'and' and 'or' built-ins.

sql_concat( $a, $b )

Maps to "$a || $b".

sql_exists( stuff )

Maps to "EXISTS( stuff )".

sql_sum( stuff )

Maps to "SUM( stuff )".

sql_min( stuff )

Maps to "MIN( stuff )".

sql_max( stuff )

Maps to "MAX( stuff )".

sql_count( stuff )

Maps to "COUNT( stuff )".

sql_case( stuff ) =item sql_cast( stuff ) =item sql_coalesce( stuff ) =item sql_length( stuff ) =item sql_lower( stuff ) =item sql_upper( stuff ) =item sql_values( stuff )
sql_func( $name, @args )

This is a generic way to write an SQL function that isn't already handled by SQL::DB::Expr. For example:

sub my_weird_func {
    sql_func('MY_WEIRD_FUNC', @_);
}

my_weird_func( 1, 3 );

results in an expression with:

SQL:          MY_WEIRD_FUNC(?, ?)
Bind values:  [ 1, 3 ]

Plus the appropriate bind_type values according to the expression arguments.

SEE ALSO

SQL::DB, sqldb-builder

AUTHOR

Mark Lawrence <nomad@null.net>

COPYRIGHT AND LICENSE

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