NAME

DB::Object::Mysql::Tables - MySQL Table Object

SYNOPSIS

use DB::Object::Mysql::Tables;
my $this = DB::Object::Mysql::Tables->new || die( DB::Object::Mysql::Tables->error, "\n" );

VERSION

v0.300.1

DESCRIPTION

This is a MySQL table object class.

METHODS

check

This will prepare the statement to check the table.

Checking table is a query specific to MySQL

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

It returns the newly created statement handler.

create

This creates a table.

It takes some array reference data containing the columns definitions, some optional parameters and a statement handler.

If a statement handler is provided, then no need to provide an array reference of columns definition. The columns definition will be taken from the statement handler. However, at least either one of them needs to be provided to set the columns definition.

Possible parameters are:

comment

inherits

Takes the name of another table to inherit from

on commit

tablespace

temporary

If provided, this will create a temporary table.

with oids

If true, this will enable table oid

without oids

If true, this will disable table oid

This will return an error if the table already exists, so best to check beforehand with "exists".

Upon success, it will return the new statement to create the table. However, if "create" is called in void context, then the statement is executed right away and returned.

create_info

This returns the create info for the current table object as a string representing the sql script necessary to recreate the table.

drop

This will prepare a drop statement to drop the current table.

If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.

It takes no option.

See MySQL documentation for more information

exists

Returns true if the current table exists, or false otherwise.

lock

If no parameter is provided, this will issue the following query LOCK t LOW_PRIORITY WRITE where t is the table name.

If one parameter is provided and is an array reference containing the table alias and some lock option, otherwise if the one parameter provided is the lock option. If no lock option is provided this will default to LOW_PRIORITY WRITE.

For example:

$t->lock([ 'n', 'low_priority write' ]);

This will issue the following query:

LOCK TABLE t AS n LOW_PRIORITY WRITE

$t->lock( 'low_priority write' );

This will issue the following query:

LOCK TABLE t LOW_PRIORITY WRITE

If the parameters provided is an hash of table name-option pairs, such as:

$t->lock(
    t1  => ['n' => 'low_priority write'], # alias to n with option
    t2  => 'low_priority write', # only option
);

This will issue the following query:

LOCK TABLES t1 AS n LOW_PRIORITY WRITE, t2 LOW_PRIORITY WRITE

The option can only be:

read

read local

write

low priority write

This will prepare the query to lock the table or tables and return the statement handler. If it is called in void context, the statement handler returned is executed immediately.

See MyQL documentation for more information

optimize

my $sth = $t->optimize; # OPTIMIZE TABLE t

This will prepare a query to optimize the table. If it is called in void context, the statement handler returned is executed immediately.

See MyQL documentation for more information

qualified_name

This return a fully qualified name to be used as a prefix to columns in queries.

Note that in MySQL there is no meaning of schema like in other modern drivers like PostgreSQL. In MySQL a schema is equivalent to a database. See this StackOverflow discussion

If "prefixed" in DB::Object::Tables is greater than 2, the database name will be added.

At minimum, the table name is added.

$tbl->prefixed(2);
$tbl->qualified_name;
# Would return something like: mydb.my_table

$tbl->prefixed(1);
$tbl->qualified_name;
# Would return only: my_table

See MyQL documentation for more information

rename

Provided with a new table name, and this will prepare the necessary query to rename the table and return the statement handler.

If it is called in void context, the statement handler is executed immediately.

# Get the prefs table object
my $tbl = $dbh->pref;
$tbl->rename( 'prefs' );
# Would issue a statement handler for the query: ALTER TABLE pref RENAME TO prefs

It returns the statement handler created.

See PostgreSQL documentation for more information

repair

Provided with an optional hash or hash reference of parameter, and this will prepare a query to repair the MySQL table.

my $tbl = $dbh->my_table || die( $dbh->error );
my $sth = $tbl->repair || die( $tbl->error );
$sth->exec || die( $sth->error );

If it is called in void context, the statement handler is executed immediately.

It returns the statement handler created.

See PostgreSQL documentation for more information

stat

Provided with a table name and this will prepare a SHOW TABLE STATUS MySQL query. If no table explicitly specified, then this will prepare a stat query for all tables in the database.

$tbl->stat( 'my_table' );
# SHOW TABLE STATUS FROM my_database LIKE 'my_table'
$tbl->stat;
# SHOW TABLE STATUS FROM my_database

The stat statement will be executed and an hash reference of property-value pairs in lower case will be retrieved for each table. Each table hash is stored in another hash reference of table name-properties hash reference pairs.

If only one table was the subject of the stat, in list context, this returns an hash of those table stat properties, and in scalar context its hash reference.

If the stat was done for the entire database, in list context, this returns an hash of all those tables to properties pairs, or an hash reference in scalar context.

structure

This returns in list context an hash and in scalar context an hash reference of the table structure.

The hash, or hash reference returned contains the column name and its definition.

This method will also set the following object properties:

"type" in DB::Object::Tables

The table type.

"schema" in DB::Object::Tables

No such thing in MySQL, so this is unavailable.

default

A column name to default value hash reference

fields

A column name to field position (integer) hash reference

null

A column name to a boolean representing whether the column is nullable or not.

"primary" in DB::Object::Tables

An array reference of column names that are used as primary key for the table.

structure

A column name to its sql definition

types

A column name to column data type hash reference

unlock

This will unlock a previously locked table.

If an argument is provided, this calls instead CORE::unlock passing it whatever parameters provided.

Otherwise, it will prepare a query UNLOCK TABLES and returns the statement handler.

If it is called in void context, this will execute the statement handler immediately.

See MyQL documentation for more information

SEE ALSO

perl

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.

cpanm DB::Object

perl -MCPAN -e shell
install DB::Object