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.