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_lob   
  sql_index
  sql_cond
  sql_and
  sql_or by 'all' tag

ATTRIBUTES

id

query_from

SQL fragment.

query_from_helper

Code referebce that may transform query_from

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

dml_generator

Represents class that will be used to generate DML statements. SQL::DMLGenerator by default.

METHODS

initialise

initialise_unique_row_column

set_relationship_join_method

Sets 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 for instance we have the folllowing SQL: SELECT ename as name, id, loc FROM emp name will be replaced to ename.

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.