/ 
DB-Object-v1.4.3
River stage zero No dependents
/ DB::Object::Tables

NAME

DB::Object::Tables - Database Table Object

SYNOPSIS

VERSION

v1.0.1

DESCRIPTION

This is the table object package used to represent and manipulate table objects.

CONSTRUCTOR

new

my $tbl = DB::Object::Tables->new( 'my_table' ) || die( DB::Object::Tables->error );

Creates a new DB::Object::Tables object.

A table name may be provided as first argument.

It may also take an hash of arguments, that also are method of the same name.

It will call "structure" to get the table structure from database and returns an error if it fails.

Possible arguments are:

  • debug

    Toggles debug mode on/off

METHODS

alias

This is a convenient wrapper around "alias" in DB::Object::Query

It takes a column name to alias hash and sets those aliases for the following query.

Get/set alias for table fields in SELECT queries. The hash provided thus contain a list of field => alias pairs.

alter

Provided with an array or array reference of specification for the alter and this will prepare the proper query.

The specification array or array reference will be joined with a comma

If called in void context, the resulting statement handler will be executed immediately.

This returns the resulting statement handler.

as

Provided with a table alias and this will call "table_alias" in DB::Object::Query passing it whatever arguments were provided.

avoid

Takes a list of array reference of column to avoid in the next query.

This is a convenient wrapper around "avoid" in DB::Object::Query

check

Sets or gets the hash object of check constraint objects for this table.

Each key in the hash represents the foreign key constraint name and its value is an check constraint object that contains the following methods:

  • expr

    The check constraint expression

  • fields

    The array object of table columns associated with this check constraint.

  • name

    The check constraint name.

columns

Returns an array object of the table columns.

This information is provided by "fields", which is in turn provided by "structure"

constant

Sets the query object constant for statement caching and return our current object.

create

This must be implemented by the driver package, so check "create" in DB::Object::Mysql::Tables, "create" in DB::Object::Postgres::Tables or "create" in DB::Object::SQLite::Tables

create_info

This must be implemented by the driver package, so check "create_info" in DB::Object::Mysql::Tables, "create_info" in DB::Object::Postgres::Tables or "create_info" in DB::Object::SQLite::Tables

database

Returns the name of the current database by calling "database" in DB::Object

database_object

Returns the database object (DB::Object)

dbh

Returns the database handler (DBI)

dbo

Sets or get the database object, which can be one of DB::Object::Mysql, DB::Object::Postgres or DB::Object::SQLite

default

This calls "structure" which may return cached data.

Returns an hash in list context and an hash reference in scalar representing column to its default values pairs.

If nothing is found, it returns an empty list in list context and "undef" in perlfunc in scalar context.

delete

"delete" will format a delete query based on previously set parameters, such as "where".

"delete" will refuse to execute a query without a where condition. To achieve this, one must prepare the delete query on his/her own by using the "do" method and passing the sql query directly.

$tbl->where( login => 'jack' );
$tbl->limit(1);
my $rows_affected = $tbl->delete();
# or passing the where condition directly to delete
my $sth = $tbl->delete( login => 'jack' );

drop

This will prepare the query to drop the current table.

In void context, this will execute the resulting statement handler.

It returns the resulting statement handler

enhance

Sets or gets the boolean value. When true, this will instruct the query object to make certain enhancements to the SQL query.

exists

This must be implemented by the driver package, so check "exists" in DB::Object::Mysql::Tables, "exists" in DB::Object::Postgres::Tables or "exists" in DB::Object::SQLite::Tables

field_exists

Provided with a field name, and this returns a boolean value as to whether that field exists in the table or not.

fields_as_array

Returns the table fields name as an array object

fields

This calls "structure" which may return cached data.

Returns an hash of fields to their corresponding object. Those objects are instantiated once by the structure method. If you plan on making change, make sure to clone them first.

If nothing is found, it returns an empty list in list context and "undef" in perlfunc in scalar context.

It takes an optional parameter representing a field name, and will return its corresponding object, such as:

my $tbl = $dbh->my_database_table || die( "No table 'my_database_table' in database" );
my $field_object = $tbl->fields( 'my_table_field' ):

fields_object

my $tbl = $dbh->user || die( "No table \"user\" found in database\n" );
# get the field object for "name"
my $name = $tbl->fields_object->name
# Do something with it
my $expr = $name == 'joe';
# Resulting in an DB::Object::Fields::Overloaded object

This returns the cached object if there is one.

This will dynamically create a package based on the database and table name. For example a database Foo and a table Bar would result in the following dynamically created package: DB::Object::Tables::Foo::Bar

This new package will inherit from DB::Object::Fields, which enable the dynamic loading of column object using AUTOLOAD

This will instantiate an object from this newly created package, cache it and return it.

fo

This is a convenient shortcut for "fields_object"

my $tbl = $dbh->user || die( "No table \"user\" found in database\n" );
# get the field object for "name"
my $name = $tbl->fo->name

foreign

Sets or gets the hash object of foreign key constraint objects for this table.

Each key in the hash represents the foreign key constraint name and its value is an foreign key constraint object that contains the following methods:

  • match

    Typical value is full, partial and simple

  • on_delete

    The action the database is to take upon deletion. For example: nothing, restrict, cascade, null or default

  • on_update

    The action the database is to take upon update. For example: nothing, restrict, cascade, null or default

  • table

    The table name of the foreign key.

  • fields

    The array object of associated column names for this foreign key constraint.

  • name

    The foreign key constraint name.

format_statement

This is a convenient wrapper around "format_statement" in DB::Object::Query

Format the sql statement for queries of types select, delete and insert

In list context, it returns 2 strings: one comma-separated list of fields and one comma-separated list of values. In scalar context, it only returns a comma-separated string of fields.

format_update

This is a convenient wrapper around "format_update" in DB::Object::Query

Formats update query based on the following arguments provided:

  • data

    An array of key-value pairs to be used in the update query. This array can be provided as the prime argument as a reference to an array, an array, or as the data element of a hash or a reference to a hash provided.

    Why an array if eventually we build a list of key-value pair? Because the order of the fields may be important, and if the key-value pair list is provided, "format_update" honors the order in which the fields are provided.

"format_update" will then iterate through each field-value pair, and perform some work:

If the field being reviewed was provided to from_unixtime, then "format_update" will enclose it in the function FROM_UNIXTIME() as in:

  FROM_UNIXTIME(field_name)

If the the given value is a reference to a scalar, it will be used as-is, ie. it will not be enclosed in quotes or anything. This is useful if you want to control which function to use around that field.

If the given value is another field or looks like a function having parenthesis, or if the value is a question mark, the value will be used as-is.

If "bind" in DB::Object is off, the value will be escaped and the pair field='value' created.

If the field is a SET data type and the value is a number, the value will be used as-is without surrounding single quote.

If "bind" in DB::Object is enabled, a question mark will be used as the value and the original value will be saved as value to bind upon executing the query.

Finally, otherwise the value is escaped and surrounded by single quotes.

"format_update" returns a string representing the comma-separated list of fields that will be used.

from_unixtime

Provided with an array or array reference of table columns and this will set the list of fields that are to be treated as unix time and converted accordingly after the sql query is executed.

It returns the list of fields in list context or a reference to an array in scalar context.

get_query_object

Get the DB::Object::Query object. If none is set yet, it will instantiate one automatically.

group

This is a convenient wrapper around "group" in DB::Object::Query

indexes

my $idx = $tbl->indexes;
my $in0 = $idx->{some_index};
say "Is primary: ", $in0->is_primary ? 'yes' : 'no';
say "Is unique: ", $in0->is_unique ? 'yes' : 'no';
say "Associated fields: ", $in0->fields->join( ', ' );

Sets or gets the hash object of index objects for this table.

Each key in the hash represents the index name and its value is an index object that contains the following methods:

  • fields

    An array object of table field names.

  • is_primary

    Boolean value whether this index is the table primary index.

  • is_unique

    Boolean value whether this is a unique index.

insert

This is a convenient wrapper around "insert" in DB::Object::Query

limit

This is a convenient wrapper around "limit" in DB::Object::Query

local

This is a convenient wrapper around "local" in DB::Object::Query

lock

This must be implemented by the driver package, so check "lock" in DB::Object::Mysql::Tables, "lock" in DB::Object::Postgres::Tables or "lock" in DB::Object::SQLite::Tables

name

Returns the table name. This is read-only.

new_check

This takes an hash or an hash reference of parameters and instantiate a new DB::Object::Constraint::Check object.

If no debug parameter is provided, the one of the current table object will be used.

It returns the new object upon success, or upon error, it sets an exception object and return undef in scalar context, or an empty list in list context.

new_foreign

This takes an hash or an hash reference of parameters and instantiate a new DB::Object::Constraint::Foreign object.

If no debug parameter is provided, the one of the current table object will be used.

It returns the new object upon success, or upon error, it sets an exception object and return undef in scalar context, or an empty list in list context.

new_index

This takes an hash or an hash reference of parameters and instantiate a new DB::Object::Constraint::Index object.

If no debug parameter is provided, the one of the current table object will be used.

It returns the new object upon success, or upon error, it sets an exception object and return undef in scalar context, or an empty list in list context.

no_bind

Boolean. Sets the no bind flags to true or false.

null

This calls "structure" which may return cached data.

Returns an hash in list context and an hash reference in scalar representing column to its default null values pairs.

If nothing is found, it returns an empty list in list context and "undef" in perlfunc in scalar context.

on_conflict

The SQL ON CONFLICT clause needs to be implemented by the driver and is currently supported only by DB::Object::Postgres and DB::Object::SQLite.

optimize

This must be implemented by the driver package, so check "optimize" in DB::Object::Mysql::Tables, "optimize" in DB::Object::Postgres::Tables or "optimize" in DB::Object::SQLite::Tables

order

This is a convenient wrapper around "order" in DB::Object::Query

Prepares the ORDER BY clause and returns the value of the clause in list context or the ORDER BY clause in full in scalar context, ie. "ORDER BY $clause"

parent

For the drivers who support it, this will represent the parent table if the current table inherits from another table.

prefix

Based on the prefix level, this will return a string with the database name if prefix is higher than 2, with the schema if the prefix level is higher than 1 and with the table name if the prefix level is higher than 0.

The resulting string is used as prefix to table columns when preparing queries.

prefix_database

Returns true if "prefixed" is higher than 2.

prefix_schema

Returns true if "prefixed" is higher than 1.

prefix_table

Returns true if "prefixed" is higher than 0.

prefixed

Sets or gets the prefix level. 0 being no prefix and 2 implying the use of the database name in prefix.

primary

This calls "structure" which may return cached data.

Returns an hash in list context and an hash reference in scalar representing column to primary keys pairs. If a column has no primary keys, its value would be empty.

If nothing is found, it returns an empty list in list context and "undef" in perlfunc in scalar context.

qualified_name

Returns the table name. This is read-only.

query_object

Returns the query object (DB::Object::Query)

query_reset

Reset the query object (DB::Object::Query)

rename

This must be implemented by the driver package, so check "rename" in DB::Object::Mysql::Tables, "rename" in DB::Object::Postgres::Tables or "rename" in DB::Object::SQLite::Tables

repair

This must be implemented by the driver package, so check "repair" in DB::Object::Mysql::Tables, "repair" in DB::Object::Postgres::Tables or "repair" in DB::Object::SQLite::Tables

replace

Just like for the INSERT query, "replace" takes one optional argument representing a DB::Object::Statement SELECT object or a list of field-value pairs.

If a SELECT statement is provided, it will be used to construct a query of the type of REPLACE INTO mytable SELECT FROM other_table

Otherwise the query will be REPLACE INTO mytable (fields) VALUES(values)

In scalar context, it execute the query and in list context it simply returns the statement handler.

reset

This is used to reset a prepared query to its default values. If a field is a date/time type, its default value will be set to NOW()

It execute an update with the reseted value and return the number of affected rows.

reset_structure

Resets the cache for the "structure" method

returning

The SQL RETURNING clause needs to be implemented by the driver and is currently supported only by and DB::Object::Postgres (see "returning" in DB::Object::Postgres::Query) and DB::Object::SQLite (see "returning" in DB::Object::SQLite::Query).

reverse

Get or set the reverse mode.

schema

Returns the schema name, if any. For example, with PostgreSQL, the default schema name would be public.

select

Given an optional list of fields to fetch, "select" prepares a SELECT query.

If no field was provided, "select" will use default value where appropriate like the NOW() for date/time fields.

"select" in DB::Object::Query calls upon "tie" in DB::Object::Query, "where" in DB::Object::Query, "group" in DB::Object::Query, "order" in DB::Object::Query, "limit" in DB::Object::Query, "local" in DB::Object::Query, and possibly more depending on the driver implementation, to build the query.

In scalar context, it execute the query and return it. In list context, it just returns the statement handler.

sort

It toggles sort mode on and consequently disable reverse mode.

stat

This must be implemented by the driver package, so check "stat" in DB::Object::Mysql::Tables, "stat" in DB::Object::Postgres::Tables or "stat" in DB::Object::SQLite::Tables

structure

The implementation is driver specific.

This must be implemented by the driver package, so check "structure" in DB::Object::Mysql::Tables, "structure" in DB::Object::Postgres::Tables or "structure" in DB::Object::SQLite::Tables

This returns a cached data for speed. See "reset_structure" to reset that cache.

table

Returns the table name. This is read-only.

tie

This is a convenient wrapper around "tie" in DB::Object::Query

type

The table type

types

This calls "structure" which may return cached data.

Returns an hash in list context and an hash reference in scalar representing column to data type.

If nothing is found, it returns an empty list in list context and "undef" in perlfunc in scalar context.

types_const

This calls "structure" which may return cached data.

Returns an hash in list context and an hash reference in scalar representing column to hash that defines the driver constant for this data type:

some_column => { constant => 17, name => 'PG_JSONB', type => 'jsonb' }

This is used to help manage binded value with the right type, or helps when converting an hash into json.

If nothing is found, it returns an empty list in list context and "undef" in perlfunc in scalar context.

unix_timestamp

This is a convenient wrapper around "unix_timestamp" in DB::Object::Query

unlock

This must be implemented by the driver package, so check "unlock" in DB::Object::Mysql::Tables, "unlock" in DB::Object::Postgres::Tables or "unlock" in DB::Object::SQLite::Tables

update

Given a list of field-value pairs, "update" prepares a sql update query.

It calls upon "where" in DB::Object::Query and "limit" in DB::Object::Query as previously set.

It returns undef and sets an error if it failed to prepare the update statement. In scalar context, it execute the query. In list context, it simply return the statement handler.

where

This is a convenient wrapper around "where" in DB::Object::Query

AUTHOR

Jacques Deguest <jack@deguest.jp>

SEE ALSO

DB::Object::Mysql::Tables, DB::Object::Postgres::Tables or DB::Object::SQLite::Tables

AUTHOR

Jacques Deguest <jack@deguest.jp>

COPYRIGHT & LICENSE

Copyright (c) 2019-2021 DEGUEST Pte. Ltd.

You can use, copy, modify and redistribute this package and associated files under the same terms as Perl itself.