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

NAME

DB::Object::Mysql - Mysql Database Object

SYNOPSIS

use DB::Object;

my $dbh = DB::Object->connect({
driver => 'mysql',
conf_file => 'db-settings.json',
database => 'webstore',
host => 'localhost',
login => 'store-admin',
debug => 3,
}) || bailout( "Unable to connect to Mysql server on host localhost: ", DB::Object->error );

# Legacy regular query
my $sth = $dbh->prepare( "SELECT login,name FROM login WHERE login='jack'" ) ||
die( $dbh->errstr() );
$sth->execute() || die( $sth->errstr() );
my $ref = $sth->fetchrow_hashref();
$sth->finish();

# Get a list of databases;
my @databases = $dbh->databases;
# Doesn't exist? Create it:
my $dbh2 = $dbh->create_db( 'webstore' );
# Load some sql into it
my $rv = $dbh2->do( $sql ) || die( $dbh->error );

# Check a table exists
$dbh->table_exists( 'customers' ) || die( "Cannot find the customers table!\n" );

# Get list of tables, as array reference:
my $tables = $dbh->tables;

my $cust = $dbh->customers || die( "Cannot get customers object." );
$cust->where( email => 'john@example.org' );
my $str = $cust->delete->as_string;
# Becomes: DELETE FROM customers WHERE email='john\@example.org'

# Do some insert with transaction
$dbh->begin_work;
# Making some other inserts and updates here...
my $cust_sth_ins = $cust->insert(
    first_name => 'Paul',
    last_name => 'Goldman',
    email => 'paul@example.org',
    active => 0,
) || do
{
    # Rollback everything since the begin_work
    $dbh->rollback;
    die( "Error while create query to add data to table customers: " . $cust->error );
};
$result = $cust_sth_ins->as_string;
# INSERT INTO customers (first_name, last_name, email, active) VALUES('Paul', 'Goldman', 'paul\@example.org', '0')
$dbh->commit;
# Get the last used insert id
my $id = $dbh->last_insert_id();

$cust->where( email => 'john@example.org' );
$cust->order( 'last_name' );
$cust->having( email => qr/\@example/ );
$cust->limit( 10 );
my $cust_sth_sel = $cust->select || die( "An error occurred while creating a query to select data frm table customers: " . $cust->error );
# Becomes:
# SELECT id, first_name, last_name, email, created, modified, active, created::ABSTIME::INTEGER AS created_unixtime, modified::ABSTIME::INTEGER AS modified_unixtime, CONCAT(first_name, ' ', last_name) AS name FROM customers WHERE email='john\@example.org' HAVING email ~ '\@example' ORDER BY last_name LIMIT 10

$cust->reset;
$cust->where( email => 'john@example.org' );
my $cust_sth_upd = $cust->update( active => 0 )
# Would become:
# UPDATE ONLY customers SET active='0' WHERE email='john\@example.org'

# Lets' dump the result of our query
# First to STDERR
$login->where( "login='jack'" );
$login->select->dump();
# Now dump the result to a file
$login->select->dump( "my_file.txt" );

VERSION

v1.1.2

DESCRIPTION

This package inherits from DB::Object, so any method not mentioned here, but listed there, you can use.

DB::Object is a SQL API much alike DBI. So why use a private module instead of using that great DBI package?

At first, I started to inherit from DBI to conform to perlmod perl manual page and to general perl coding guidlines. It became very quickly a real hassle. Barely impossible to inherit, difficulty to handle error, too much dependent from an API that change its behaviour with new versions. In short, I wanted a better, more accurate control over the SQL connection.

So, DB::Object acts as a convenient, modifiable wrapper that provide the programmer with an intuitive, user-friendly and hassle free interface.

CONSTRUCTOR

new

Create a new instance of DB::Object. Nothing much to say.

connect

Create a new instance of DB::Object, but also attempts a connection to SQL server.

You can specify the following arguments:

  • database

    The database name you wish to connect to

  • login

    The login used to access that database

  • password

    The password that goes along

  • server

    The server, that is hostname of the machine serving a SQL server.

  • driver

    The driver you want to use. It needs to be of the same type than the server you want to connect to. If you are connecting to a MySQL server, you would use mysql, if you would connecto to an Oracle server, you would use oracle.

    You need to make sure that those driver are properly installed in the system before attempting to connect.

    To install the required driver, you could start with the command line:

    perl -MCPAN -e shell

    which will provide you a special shell to install modules in a convenient way.

METHODS

alias

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

as_string

Return the sql query as a string.

avoid

Set the provided list of table fields to avoid when returning the query result. The list of fields can be provided either as an array of a reference to an array.

attribute

Sets or get the value of database connection parameters.

If only one argument is provided, returns its value. If multiple arguments in a form of pair => value are provided, it sets the corresponding database parameters.

The authorised parameters are:

  • Warn

    Can be overridden.

  • Active

    Read-only.

  • Kids

    Read-only.

  • ActiveKids

    Read-only.

  • CachedKids

    Read-only.

  • InactiveDestroy

    Can be overridden.

  • PrintError

    Can be overridden.

  • RaiseError

    Can be overridden.

  • ChopBlanks

    Can be overridden.

  • LongReadLen

    Can be overridden.

  • LongTruncOk

    Can be overridden.

  • AutoCommit

    Can be overridden.

  • Name

    Read-only.

  • RowCacheSize

    Read-only.

  • NUM_OF_FIELDS

    Read-only.

  • NUM_OF_PARAMS

    Read-only.

  • NAME

    Read-only.

  • TYPE

    Read-only.

  • PRECISION

    Read-only.

  • SCALE

    Read-only.

  • NULLABLE

    Read-only.

  • CursorName

    Read-only.

  • Statement

    Read-only.

  • RowsInCache

    Read-only.

available_drivers

Return the list of available drivers.

begin_work

Mark the beginning of a transaction.

Any arguments provided are passed along to "begin_work" in DBD::mysql

bind

If no values to bind to the underlying query is provided, "bind" in DB::Object simply activate the bind value feature.

If values are provided, they are allocated to the statement object and will be applied when the query will be executed.

Example:

$dbh->bind()
# or
$dbh->bind->where( "something" )
# or
$dbh->bind->select->fetchrow_hashref()
# and then later
$dbh->bind( 'thingy' )->select->fetchrow_hashref()

cache

Activate caching.

$tbl->cache->select->fetchrow_hashref();

check_driver

Check that the driver set in $SQL_DRIVER in ~/etc/common.cfg is indeed available.

It does this by calling "available_drivers" in DB::Object.

commit

Make any change to the database irreversible.

This must be used only after having called "begin_work"

Any arguments provided are passed along to "commit" in DBD::mysql

copy

Provided with either a reference to an hash or an hash of key => value pairs, "copy" in DB::Object will first execute a select statement on the table object, then fetch the row of data, then replace the key-value pair in the result by the ones provided, and finally will perform an insert.

Return false if no data to copy were provided, otherwise it always returns true.

create_db

Provided with a database name and some optional parameters and this will prepare and execute the query to create the database.

Upon failure, this will return an error, and upon success, this will connect to the newly created database and return the database handler.

Possible options are:

  • charset

    The character encoding.

  • collate

    Sets the COLLATE attribute

  • if_not_exists

    Add the condition IF NOT EXISTS

See MySQL documentation for more information

create_table

The idea is to create a table with the givern parameters.

databases

Returns a list of all available databases.

data_sources

Given an optional list of options, this return the data source of the database handler.

datatype_dict

Returns an hash reference of each data type with their equivalent constant, regular expression (re), constant name and type name.

Each data type is an hash with the following properties for each type: constant, name, re, type

data_type

Given a reference to an array or an array of data type, "data_type" in DB::Object will check their availability in the database driver.

If nothing found, it return an empty list in list context, or undef in scalar context.

If something was found, it returns a hash in list context or a reference to a hash in list context.

database

Return the name of the current database.

delete

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

"delete" in DB::Object 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" in DB::Object 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" );

disconnect

Disconnect from database. Returns the return code.

my $rc = $dbh->disconnect;

do

Execute a sql query directly passed with possible attributes and values to bind.

The attributes list will be used to "prepare" in DB::Object the query and the bind values will be used when executing the query.

It returns the statement handler or the number of rows affected.

Example:

$rc  = $dbh->do( $statement ) || die( $dbh->errstr );
$rc  = $dbh->do( $statement, \%attr ) || die( $dbh->errstr );
$rv  = $dbh->do( $statement, \%attr, @bind_values ) || die( $dbh->errstr );
my( $rows_deleted ) = $dbh->do(
q{
   DELETE FROM table WHERE status = ?
}, undef(), 'DONE' ) || die( $dbh->errstr );

enhance

Toggle the enhance mode on/off.

When on, the functions from_unixtime and unix_timestamp will be used on date/time field to translate from and to unix time seamlessly.

from_unixtime

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.

format_statement

Format the sql statement.

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

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" in DB::Object 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" in DB::Object, 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" in DB::Object returns a string representing the comma-separated list of fields that will be used.

get_sql_type

Provided with a SQL type, irrespective of the character case, and this will return the equivalent constant value.

Values are taken from DBI::SQL_*

getdefault

Does some preparation work such as:

  1. the date/time field to use the FROM_UNIXTIME and UNIX_TIMESTAMP functions

  2. removing from the query the fields to avoid, ie the ones set with the "avoid" in DB::Object method.

  3. set the fields alias based on the information provided with the "alias" in DB::Object method.

  4. if a field last_name and first_name exist, it will also create an alias name based on the concatenation of the 2.

  5. it will set the default values provided. This is used for UPDATE queries.

It returns a new DB::Object::Tables object with all the data prepared within.

group

Format the group by portion of the query.

It returns an empty list in list context of undef in scalar context if no group by clause was build. Otherwise, it returns the value of the group by clause as a string in list context and the full group by clause in scalar context.

In list context, it returns: $group_by

In scalar context, it returns: GROUP BY $group_by

having

A convenient wrapper to "having" in DB::Object::Mysql::Query

insert

Prepares an INSERT query using the field-value pairs provided.

If a DB::Object::Statement object is provided as first argument, it will considered as a SELECT query to be used in the INSERT query, as in: INSERT INTO my table SELECT FROM another_table

Otherwise, "insert" in DB::Object will build the query based on the fields provided.

In scalar context, it returns the result of "execute" in DB::Object and in list context, it returns the statement object.

last_insert_id

Get the id of the primary key from the last insert.

limit

Set or get the limit for the future statement.

If only one argument is provided, it is assumed to be the end limit. If 2 are provided, they wil be the start and end.

It returns a list of the start and end limit in list context, and the string of the LIMIT in scalar context, such as: LIMIT 1, 10

local

Not sure what it does. I forgot.

lock

Set a lock using a lock identifier and a timeout. By default the timeout is 2 seconds.

If the lock failed (NULL), it returns undef(), otherwise, it returns the return value.

no_bind

When invoked, "no_bind" in DB::Object will change any preparation made so far for caching the query with bind parameters, and instead substitute the value in lieu of the question mark placeholder.

no_cache

Disable caching of queries.

order

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"

param

If only a single parameter is provided, its value is return. If a list of parameters is provided they are set accordingly using the SET sql command.

Supported parameters are:

SQL_AUTO_IS_NULL
AUTOCOMMIT
SQL_BIG_TABLES
SQL_BIG_SELECTS
SQL_BUFFER_RESULT
SQL_LOW_PRIORITY_UPDATES
SQL_MAX_JOIN_SIZE
SQL_SAFE_MODE
SQL_SELECT_LIMIT
SQL_LOG_OFF
SQL_LOG_UPDATE
TIMESTAMP
INSERT_ID
LAST_INSERT_ID

If unsupported parameters are provided, they are considered to be private and not passed to the database handler.

It then execute the query and return undef() in case of error.

Otherwise, it returns the object used to call the method.

ping

Evals a SELECT 1 statement and returns 0 if errors occurred or the return value.

prepare

Prepares the query using the options provided. The options are the same as the one in "prepare" in DBI method.

It returns a DB::Object::Statement object upon success or undef if an error occurred. The error can then be retrieved using "errstr" in DB::Object or "error" in DB::Object.

prepare_cached

Same as "prepare" in DB::Object except the query is cached.

query

It prepares and executes the given SQL query with the options provided and return undef() upon error or the statement handler upon success.

query_object

Set or gets the PostgreSQL query object (DB::Object::Mysql::Query) used to process and format queries.

replace

Just like for the INSERT query, "replace" in DB::Object::Mysql::Tables 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.

reverse

Get or set the reverse mode.

rollback

Will roll back any changes made to the database since the last transaction point marked with "begin_work"

select

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

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

"select" in DB::Object calls upon "tie" in DB::Object, "where" in DB::Object, "group" in DB::Object, "order" in DB::Object, "limit" in DB::Object and "local" in DB::Object to build the query.

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

set

Issues a query to SET the given SQL variable.

If any error occurred, undef will be returned and an error set, otherwise it returns true.

sort

It toggles sort mode on and consequently disable reverse mode.

stat

Issue a SHOW STATUS query and if a particular $type is provided, it will returns its value if it exists, otherwise it will return undef.

In absence of particular $type provided, it returns the hash list of values returns or a reference to the hash list in scalar context.

state

Queries the DBI state and return its value.

table

Given a table name, "table" in DB::Object will return a DB::Object::Tables object. The object is cached for re-use.

table_info

Provided with a table name and some optional parameters and this will retrieve the table information.

It returns an array reference of tables information found if no schema was provided or if anywhere is true.

If a schema was provided, and the table found it returns an hash reference for that table.

Otherwise, if nothing can be found, it returns an empty array reference.

Optional parameters are:

  • anywhere

    If true, it will search anywhere.

  • schema

    A database schema.

table_push

Add the given table name to the stack of cached table names.

tables

Connects to the database and finds out the list of all available tables.

Returns undef or empty list in scalar or list context respectively if no table found.

Otherwise, it returns the list of table in list context or a reference of it in scalar context.

table_info

Provided with a table name and this returns all the matching table information from the MySQL system.

It returns an array reference of tables information found if no schema was provided or if anywhere is true.

If a schema was provided, and the table found it returns an hash reference for that table.

Otherwise, if nothing can be found, it returns an empty array reference.

It takes the following options:

  • anywhere

    If true, this will have this method an array reference of hash reference for each match.

  • database

    An optional database name to restrict the search.

Information retrieved are:

  • name

    The table name

  • schema

    Database schema, if any.

  • type

    The object type, which may be one of: table, view, materialized view, special, foreign table

tables_info

Provided with a database name and this returns all the tables information.

Information retrieved from the MySQL system tables for every table found in the given database are:

  • name

    The object name

  • database

    Database name.

  • type

    The object type, which may be one of: base table, view

tables_refresh

Rebuild the list of available database table.

Returns the list of table in list context or a reference of it in scalar context.

tie

If provided a hash or a hash ref, it sets the list of fields and their corresponding perl variable to bind their values to.

In list context, it returns the list of those field-variable pair, or a reference to it in scalar context.

unix_timestamp

Provided a list of fields or a reference to it, this sets the fields to be treated for seamless conversion from and to unix time.

unlock

Given a lock identifier, "unlock" in DB::Object releases the lock previously set with "lock" in DB::Object. It executes the underlying sql command and returns undef() if the result is NULL or the value returned otherwise.

update

Given a list of field-value pairs, "update" in DB::Object prepares a sql update query.

It calls upon "where" in DB::Object and "limit" in DB::Object 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.

use

Given a database, it switch to it, but before it checks that the database exists. If the database is different than the current one, it sets the multi_db parameter, which will have the fields in the queries be prefixed by their respective database name.

It returns the database handler.

use_cache

Sets or get the use_cache parameter.

use_bind

Sets or get the use_cache parameter.

variables

Query the SQL variable $type

It returns a blank string if nothing was found, or the value found.

version

Returns the MySQL database server version.

This information is cached per object for efficiency.

where

Build the where clause based on the field-value hash provided.

It returns the where clause in list context or the full where clause in scalar context, ie "WHERE $clause"

_cache_this

Provided with a query, this will cache it for future re-use.

It does some check and maintenance job to ensure the cache does not get too big whenever it exceed the value of $CACHE_SIZE set in the main config file.

It returns the cached statement as an DB::Object::Statement object.

_clean_statement

Given a query string or a reference to it, it cleans the statement by removing leading and trailing space before and after line breaks.

_cleanup

Removes object attributes, namely where, selected_fields, group_by, order_by, limit, alias, avoid, local, and as_string

_make_sth

Given a package name and a hashref, this build a statement object with all the necessary parameters.

It also sets the query time to the current time with the parameter query_time

It returns an object of the given $package.

_reset_query

Being called using a statement handler, this reset the object by removing all the parameters set by various subroutine calls, such as "where" in DB::Object, "group" in DB::Object, "order" in DB::Object, "avoid" in DB::Object, "limit" in DB::Object, etc.

_save_bind

This saves/cache the bin query and return the object used to call it.

_value2bind

Given a sql query and a array reference, "_value2bind" in DB::Object parse the query and interpolate values for placeholder (?).

It returns true.

SEE ALSO

DB::Object

DBI, Apache::DBI

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.