NAME

SQL::Entity - Entity sql abstraction layer.

CLASS HIERARCHY

SQL::Entity::Table
   |
   +----SQL::Entity

SYNOPSIS

use SQL::Entity;

my $entity = SQL::Entity->new(
    id                    => 'emp',
    name                  => 'emp',
    unique_expression     => 'rowid',
    columns               => {
        emp_name => sql_column(name => 'ename'),
        emp_no   => sql_column(name => 'empno'),
    },
);

my($sql_text, $bind_variables) = $entity->query(
  sql_cond('emp_no', '>', '20')
  ->and(sql_cond('emp_name', 'NOT LIKE', 'HO%'))
)

# select from database
.... do some stuff

my ($sql_text, $bind_variables) = $entity->insert(
    emp_no   => '0',
    emp_name => 'Smith',
);

# insert row/s
... do some stuff

my ($sql_text, $bind_variables) = $entity->update(
    { ename => 'Smith'},
    { empno => '20'} #pk values
);

# update row
... do some stuff

my ($sql_text, $bind_variables) = $entity->delete(
    empno => '20'
);
# delete row/s
... do some stuff


my $dept = SQL::Entity->new(
    name    => 'dept',
    columns => [
        sql_column(name => 'deptno'),
        sql_column(name => 'dname')
    ],
);

my $emp = SQL::Entity->new(
    name                  => 'emp',
    primary_key	      => ['empno'],
    unique_expression     => 'rowid',
    columns               => [
        sql_column(name => 'ename'),
        sql_column(name => 'empno'),
        sql_column(name => 'deptno')
    ],
);


$emp->add_to_one_relationships(sql_relationship(
    target_entity => $dept,
    condition     => sql_cond($dept->column('deptno'), '=', $entity->column('deptno'))
    # or join_columns => ['deptno'],
));


$emp->add_subquery_columns($dept->column('dname'));

DESCRIPTION

This class uses entity meta definition to generate different kinds of sql statmements.

EXPORT

sql_column
sql_index
sql_cond
sql_and
sql_or by 'all' tag

ATTRIBUTES

id

query_from

SQL fragment.

columns

unique_expression

Expression that's value will be used to identifying the unique row in Entity. It may be any column or pseudo column like ROWID for Oracle, or expression like PK_COLUMN1||PK_COLUMN2

unique_row_column

Association to the column object that based on unique_expression.

to_one_relations

Association many_to_one, or one_to_one tables.

to_many_relations

Association many_to_many, or one_to_many tables. To many relation implicitly creates to one relation on the reflective entity.

sql_template_parameters

Allows use mini language variable,

SELECT t.* FROM (SELECT t.* FROM tab t WHERE t.col1 = [% var1 %]) t

METHODS

initialise

initialise_unique_row_column

set_join_methods

query

Returns sql statement and bind variables, Takes optionally array ref of the requeted columns (undef returns all entity columns), condition object, bind_variables reference

my ($sql, $bind_variables) = $entity->query(undef, 
  sql_cond('empno', '>', '20')->and(sql_cond('dname', 'NOT LIKE', 'HO%'))
);

lock

Returns sql that locks all rows that meets passed in condition It uses SELECT ... FOR UPDATE pattern. Takes optionally array ref of the requeted columns, condition object, bind_variables reference

my ($sql, $bind_variables) = $entity->lock(undef, 
  sql_cond('empno', '>', '20')->and(sql_cond('dname', 'NOT LIKE', 'HO%'))
);

insert

Returns insert sql statement and bind variables

my ($sql, $bind_variables) = $entity->insert(
    dname  => 'hr',
    deptno => '10',
    ename  => 'adi',
    empno => '1',
);

update

Returns update sql statement and bind variables

my ($sql, $bind_variables) = $entity->update(
    {dname  => 'hr',
    deptno => '10',
    ename  => 'adi',
    empno => '1',},
    {the_rowid => 'AAAMgzAAEAAAAAgAAB'},
);

delete

Returns deletes sql statement and bind variables

my ($sql, $bind_variables) = $entity->delete(empno => '1');

unique_condition_values

Returns condition that uniquely identify the entity. Takes the entity fields values, and validation flag. If validation flag is true, then exception will be raise if there are not condition values.

selectable_columns

Retuns list of columns that can be selected. Takes requested columns as parameter.

from_sql_clause

Returns FROM .. sql fragment without join.

from_clause_params

Returns FROM sql frgments with join clause.

join_clause

Returns "JOIN ... " sql fragment for all to one relationship

relationship_query

Returns sql query + bind_variables to many relationship

normalise_field_names

Replaces all keys that are passed in as alias to column name

relationship

Return relationship object, takes relationship name.

query_columns

All columns that belongs to this object.

_condition_converter

Converts passed in argumets to condition object

parse_template_parameters

Parses template variables.

clone

Clones this entity

SEE ALSO

SQL::Entity::Table SQL::Entity::Index SQL::Entity::Column SQL::Entity::Condition

COPYRIGHT AND LICENSE

The SQL::Entity module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

AUTHOR

Adrian Witas, adrian@webapp.strefa.pl

See also DBIx::Connection DBIx::QueryCursor DBIx::PLSQLHandler.