NAME

DBIx::SQLEngine::Schema::Table - A table in a data source

SYNOPSIS

  $sqldb = DBIx::SQLEngine->new( ... );
  
  $table = $sqldb->table( $table_name );
  
  $hash_ary = $table->fetch_select( where => { status => 2 } );

  $table->do_insert( values => { somefield => 'A Value', status => 3 } );
  $table->do_update( values => { status => 3 }, where => { status => 2 } );
  $table->do_delete( where => { somefield => 'A Value' } );

  $hash = $table->fetch_row( $primary_key );

  $table->insert_row( { somefield => 'Some Value' } );
  $table->update_row( { id => $primary_key, somefield => 'Some Value' } );
  $table->delete_row( { id => $primary_key } );

DESCRIPTION

The DBIx::SQLEngine::Schema::Table class represents database tables accessible via a particular DBIx::SQLEngine.

By storing a reference to a SQLEngine and the name of a table to operate on, a Schema::Table object can facilitate generation of SQL queries that operate on the named table.

Each table can retrieve and cache a ColumnSet containing information about the name and type of the columns in the table. Column information is loaded from the storage as needed, but if you are creating a new table you must provide the definition.

The *_row() methods use this information about the table columns to facilitate common operations on table rows using their primary keys and simple hash-refs.

INSTANTIATION AND ACCESSORS

Table Object Creation

SQLEngine->table()

  $sqldb->table( $tablename ) : $table

Convenience function to create a table with the given table name and sqlengine.

new()

  DBIx::SQLEngine::Schema::Table->new( sqlengine=>$sqldb, name=>$name ) : $table

Standard hash constructor. You are expected to provde the name and sqlengine arguments.

Name Accessor

name()

  $table->name() : $string
  $table->name($string)

Get and set the table name. Required value. Identifies this table in the data source.

get_name()

  $table->get_name() : $string or exception

Returns the table name, or throws an exception if it is not set.

SQLEngine Accessor

sqlengine()

  $table->sqlengine() : $sqldb
  $table->sqlengine($sqldb)

Get and set our current DBIx::SQLEngine. Required value. The SQLEngine provides the DBI connection and SQL execution capabilities required to talk to the remote data storage.

get_sqlengine()

  $table->get_sqlengine() : $sqldb or exception

Returns the SQLEngine, or throws an exception if it is not set.

SQLEngine Method Invocation

sqlengine_do()

  $table->sqlengine_do( $method, %sql_clauses ) : $results or exception

Calls the provided method name on the associated SQLEngine, passing along the table name and the other provided arguments. Intended for methods with hash-based argument parsing like "fetch_select( table => $table_name )".

sqlengine_table_method()

  $table->sqlengine_table_method( $method, @args ) : $results or exception

Calls the provided method name on the associated SQLEngine, passing along the table name and the other provided arguments. Intended for methods with list-based argument parsing like "detect_table( $table_name )".

Detect Availability

detect_sqlengine()

  $table->detect_sqlengine : $flag

Detects whether the SQL database is avaialable by attempting to connect.

detect_table()

  $table->detect_table : @columns

Checks to see if the table exists in the SQL database by attempting to retrieve its columns.

Row Class

record_class()

  $table->record_class() : $record_class

Returns the Record::Class which corresponds to the table.

FETCHING DATA (SQL DQL)

Select to Retrieve Rows

fetch_select()

  $table->fetch_select ( %select_clauses ) : $row_hash_array

Calls the corresponding SQLEngine method with the table name and the provided arguments. Return rows from the table that match the provided criteria, and in the requested order, by executing a SQL select statement.

visit_select()

  $table->visit_select ( $sub_ref, %select_clauses ) : @results
  $table->visit_select ( %select_clauses, $sub_ref ) : @results

Calls the provided subroutine on each matching row as it is retrieved. Returns the accumulated results of each subroutine call (in list context).

select_row()

  $table->select_row ( $primary_key_value ) : $row_hash
  $table->select_row ( \@compound_primary_key ) : $row_hash
  $table->select_row ( \%hash_with_primary_key_value ) : $row_hash

Fetches a single row by primary key.

select_rows()

  $table->select_rows ( @primary_key_values_or_hashrefs ) : $row_hash_array

Fetches a set of one or more by primary key.

Selecting Agregate Values

fetch_one_value()

  $table->fetch_one_value( %sql_clauses ) : $scalar

Calls fetch_select, then returns the first value from the first row of results.

count_rows()

  $table->count_rows ( ) : $number
  $table->count_rows ( $criteria ) : $number

Return the number of rows in the table. If called with criteria, returns the number of matching rows.

try_count_rows()

  $table->try_count_rows ( ) : $number
  $table->try_count_rows ( $criteria ) : $number

Exception catching wrapper around count_rows. If the eval block catches an exception, undef is returned.

fetch_max()

  $table->count_rows ( $colname, CRITERIA ) : $number

Returns the largest value in the named column.

EDITING DATA (SQL DML)

Insert to Add Rows

do_insert()

  $table->do_insert ( %insert_clauses ) : $row_count

Calls the corresponding SQLEngine method with the table name and the provided arguments.

insert_row()

  $table->insert_row ( $row_hash ) : $row_count

Adds the provided row by executing a SQL insert statement. Uses column_names() and column_primary_is_sequence() to produce the proper clauses. Returns the total number of rows affected, which is typically 1.

insert_rows()

  $table->insert_rows ( @row_hashes ) : $row_count

Insert each of the rows from the provided list into the table. Returns the total number of rows affected, which is typically the same as the number of arguments passed.

Update to Change Rows

do_update()

  $table->do_update ( %update_clauses ) : $row_count

Calls the corresponding SQLEngine method with the table name and the provided arguments.

update_row()

  $table->update_row ( $row_hash ) : $row_count

Update this existing row based on its primary key. Uses column_names() and column_primary_is_sequence() to produce the proper clauses. Returns the total number of rows affected, which is typically 1.

update_rows()

  $table->update_rows ( @row_hashes ) : $row_count

Update several existing rows based on their primary keys. Uses update_row(). Returns the total number of rows affected, which is typically the same as the number of arguments passed.

Delete to Remove Rows

do_delete()

  $table->do_delete ( %delete_clauses ) : $row_count

Calls the corresponding SQLEngine method with the table name and the provided arguments.

delete_row()

  $table->delete_row ( $row_hash_or_id ) : ()

Deletes the provided row from the table. Returns the total number of rows affected, which is typically 1.

delete_rows()

  $table->delete_rows ( @row_hashes_or_ids ) : ()

Deletes all of the provided rows from the table. Returns the total number of rows affected, which is typically the same as the number of arguments passed.

DEFINING STRUCTURES (SQL DDL)

ColumnSet

columnset()

  $table->columnset () : $columnset

Returns the current columnset, if any.

get_columnset()

  $table->get_columnset () : $columnset

Returns the current columnset, or runs a trivial query to detect the columns in the sqlengine. If the table doesn't exist, the columnset will be empty.

columns()

  $table->columns () : @columns

Return the column objects from the current columnset.

column_names()

  $table->column_names () : @column_names

Return the names of the columns, in order.

column_named()

  $table->column_named ( $name ) : $column

Return the column info object for the specifically named column.

Primary Keys

column_primary_is_sequence(): Inheritable boolean which can be set for the table class or any instance. Indicates that the primary key column uses an auto-incrementing sequence.
column_primary_name(): Returns the name of the primary key column. (TODO: Currently hard-coded to the first column in the column set.)
primary_criteria(): Returns a hash of key-value pairs which could be used to select this record by its primary key.

Create and Drop Tables

table_exists()

  $table->table_exists() : $flag

Detects whether the table has been created and has not been dropped.

create_table()

  $table->create_table () 
  $table->create_table ( $column_ary )

drop_table()

  $table->drop_table ()

ensure_table_exists()

  $table->ensure_table_exists ( $column_ary )

Create the table's remote storage if it does not already exist.

recreate_table()

  $table->recreate_table ()
  $table->recreate_table ( $column_ary )

Drop and then recreate the table's remote storage.

recreate_table_with_rows

  $table->recreate_table_with_rows ()
  $table->recreate_table_with_rows ( $column_ary )

Selects all of the existing rows, then drops and recreates the table, then re-inserts all of the rows.