/ 
DB-Object-v1.4.6
River stage zero No dependents
/ DB::Object::Postgres

NAME

DB::Object::Postgres - SQL API

SYNOPSIS

use DB::Object::Postgres;

my $dbh = DB::Object::Postgres->connect({
driver => 'Pg',
conf_file => 'db-settings.json',
database => 'webstore',
host => 'localhost',
login => 'store-admin',
schema => 'auth',
debug => 3,
}) || bailout( "Unable to connect to sql 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;

# and more elaborate:
# Ref: https://www.postgresql.org/docs/10/sql-insert.html#SQL-ON-CONFLICT
$login->on_conflict({
    # mandatory, can be a constraint name or a field name or array of fields
    target => 'on constraint idx_prefs_unique',
    action => 'update',
    # where => '',
    # which fields to update. It can also be more specific by providing a hash ref like fields => { val => 'plop' }
    fields => [qw( val )],
});
# would become:
insert into login (..) values(...) on conflict on constraint idx_prefs_unique do update set val = EXCLUDED.val;

# 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.2.2

DESCRIPTION

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

DB::Object::Postgres is a SQL API much alike DBD::Pg. So why use a private module instead of using that great DBD::Pg 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::Postgres 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::Postgres. Nothing much to say.

connect

Provided with a database, login, password, server, driver, and this will attempt a database connection.

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

You can specify the following parameters:

  • cache_connections

    See "connect" in DB::Object for more information

  • 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

attribute

Sets or gets one more pg attributes.

Valid attributes are:

  • ActiveKids

    Is read-only.

  • AutoCommit

    Can be changed.

  • AutoInactiveDestroy

    Can be changed.

  • CachedKids

    Is read-only.

  • ChildHandles

    Is read-only.

  • ChopBlanks

    Can be changed.

  • CursorName

    Is read-only.

  • Driver

    Is read-only.

  • ErrCount

    Can be changed.

  • Executed

    Is read-only.

  • FetchHashKeyName

    Can be changed.

  • HandleError

    Can be changed.

  • HandleSetErr

    Can be changed.

  • InactiveDestroy

    Can be changed.

  • Kids

    Is read-only.

  • NAME

    Is read-only.

  • NULLABLE

    Is read-only.

  • NUM_OF_FIELDS

    Is read-only.

  • NUM_OF_PARAMS

    Is read-only.

  • Name

    Is read-only.

  • PRECISION

    Is read-only.

  • PrintError

    Can be changed.

  • PrintWarn

    Can be changed.

  • Profile

    Can be changed.

  • RaiseError

    Can be changed.

  • ReadOnly

    Can be changed.

    Specifies if the current database connection should be in read-only mode or not.

  • RowCacheSize

    Is read-only.

  • RowsInCache

    Is read-only.

  • SCALE

    Is read-only.

  • ShowErrorStatement

    Can be changed.

  • Statement

    Is read-only.

  • TYPE

    Is read-only.

  • Taint

    Can be changed.

  • TaintIn

    Can be changed.

  • TaintOut

    Can be changed.

  • TraceLevel

    Can be changed.

  • Type

    Can be changed.

  • Username

    Is read-only.

  • Warn

    Can be changed.

  • pg_INV_READ

    Is read-only.

  • pg_INV_WRITE

    Is read-only.

  • pg_async_status

    Is read-only.

  • pg_bool_tf

    Can be changed.

    If true, boolean values will be returned as the characters 't' and 'f' instead of '1' and '0'.

  • pg_db

    Is read-only.

  • pg_default_port

    Is read-only.

  • pg_enable_utf8

    Can be changed.

  • pg_errorlevel

    Can be changed.

    Valid entries are 0, 1 and 2

  • pg_expand_array

    Can be changed.

  • pg_host

    Is read-only.

  • pg_lib_version

    Is read-only.

  • pg_options

    Is read-only.

  • pg_pass

    Is read-only.

  • pg_pid

    Is read-only.

  • pg_placeholder_dollaronly

    Can be changed.

    When true, question marks inside of statements are not treated as placeholders, e.g. geometric operators

  • pg_placeholder_nocolons

    Can be changed.

    When true, colons inside of statements are not treated as placeholders

  • pg_port

    Is read-only.

  • pg_prepare_now

    Can be changed.

  • pg_protocol

    Is read-only.

  • pg_server_prepare

    Can be changed.

    Indicates if DBD::Pg should attempt to use server-side prepared statements. On by default

  • pg_server_version

    Is read-only.

  • pg_socket

    Is read-only.

  • pg_standard_conforming_strings

    Is read-only.

  • pg_switch_prepared

    Can be changed.

  • pg_user

    Is read-only.

begin_work

Mark the beginning of a transaction.

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

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::Pg

connect

Same as "connect" in DB::Object, only specific to PostgreSQL.

See "_connection_params2hash"

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:

  • allowcon

    Sets the ALLOW_CONNECTIONS attribute

    "If false then no one can connect to this database. The default is true, allowing connections."

  • connlimit

    Sets the CONNECTION LIMIT attribute

    "How many concurrent connections can be made to this database. -1 (the default) means no limit."

  • encoding

    Sets the ENCODING attribute

    "Character set encoding to use in the new database."

  • lc_collate

    Sets the LC_COLLATE attribute

    "Collation order (LC_COLLATE) to use in the new database."

  • lc_ctype

    Sets the LC_CTYPE attribute

    "Character classification (LC_CTYPE) to use in the new database."

  • istemplate

    Sets the IS_TEMPLATE attribute

    "If true, then this database can be cloned by any user with CREATEDB privileges; if false (the default), then only superusers or the owner of the database can clone it."

  • owner

    Sets the OWNER attribute

    "The role name of the user who will own the new database"

  • tablespace

    Sets the TABLESPACE attribute

    "The name of the tablespace that will be associated with the new database"

  • template

    Sets the TEMPLATE attribute

    "The name of the template from which to create the new database"

See PostgreSQL documentation for more information

create_table

Provided with a table name and some options as hash reference and this will create a new table.

A mandatory parameter is sql which must contain the sql script to be used.

The sql script is executed using "do" in DB::Object and the returned value is returned.

databases

Returns a list of all available databases.

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

datatypes

my $types = $dbh->datatypes;

Returns an hash reference of data types to their respective values.

It will return the PostgreSQL's constants.

func

Provided with a table name and a function name and this will call "func" in DBD::Pg and returns the result.

get_sql_type

my $const $dbh->get_sql_type( 'bytea' );
# returns 17
my $const $dbh->get_sql_type( 'json' );
# returns 114
my $const $dbh->get_sql_type( 'jsonb' );
# returns 3802

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

having

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

large_object

Instantiate a new DB::Object::Postgres::Lo and returns it.

last_insert_id

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

lock

Takes a table name, a lock mode and an optional nowait and this will lock the given table by issuing the relevant query.

Supported lock types are: SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE

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

make_schema

Provided with a database name and this will create its schema.

In list context, it returns an array of schema lines, and in scalar context, it returns the schema as a string.

on_conflict

See "on_conflict" in DB::Object::Postgres::Tables

for Pod::Coverage pg_notifies

pg_ping

Calls "pg_ping" in DBD::Pg

query_object

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

quote

Provided with a data and some data type, and this will possibly put surrounding single quotes and return the result.

release

$dbh->release( 'mysavepoint' );

Calls DBD::pg_release passing it through whatever arguments were provided.

See also savepoint and rollback_to

replace

Replace queries are not supported in PostgreSQL

returning

A convenient wrapper to "returning" in DB::Object::Postgres::Query

rollback

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

rollback_to

$dbh->rollback_to( 'mysavepoint' );

To be used inside a transaction. This will rollback any change up to the specified savepoint.

Will call "pg_rollback_to" in DBD::Pg and passing it through whatever arguments were provided.

See also release and savepoint

savepoint

$dbh->savepoint( 'mysavepoint' );

To be used inside a transaction. This creates a savepoint, which you can rollback to or "release" in release

Will call "pg_savepoint" in DBD::Pg and passing it through whatever arguments were provided.

See also rollback_to and release

schema

Sets or gets the database schema.

It returns the value as a Module:Generic::Scalar object

search_path

If a search path is provided, this will issue the query to set it using SET search_path = $search_path whatever $search_path is. It returns the returned value from "execute" in DBD::Pg

If no arguments is provided, this will issue the query SHOW search_path to retrieve the current search path.

It returns an array object (Module::Generic::Array) containing the search paths found.

set

This is inherited from DB::Object

Please see PostgreSQL documentation for the variables that can be set.

socket

This returns the database handler property pg_socket

table_exists

Provided with a table name and this will check if the table exists.

It accepts the following options:

  • anywhere

    If true, this will search anywhere.

  • schema

    A database schema.

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.

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 PostgreSQL system tables for every table found in the given database are:

  • name

    The object name

  • owner

    The object owner (role)

  • schema

    Database schema, if any.

  • type

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

trace

Calls "trace" in DBD::Pg passing through whatever arguments were provided.

trace_msg

Calls "trace_msg" in DBD::Pg and pass it whatever arguments were provided.

unlock

Unlock does not work with PostgreSQL

table_info

It returns an array reference of hash reference containing information about each table column.

variables

Variables are currently unsupported in Postgres

version

Returns the PostgreSQL database server version.

This information is cached per object for efficiency.

_check_connect_param

Given some parameters hash and this will return a proper hash reference of parameters suitable for connection parameters.

This will call "_connection_parameters" to get the valid parameters and "_connection_options" to get valid connection options based on the arguments provided.

It returns the hash reference of connection parameters.

_check_default_option

Based on optional arguments and this will enable default options for the parameters provided.

Currently this only check client_encoding and set the default to utf8

It returns an hash reference of those parameters.

_connection_options

Based on an hash reference of parameters and this will call "_connection_options" in DB::Object and return a new hash reference of keys starting with pg_

_connection_parameters

Based on an hash reference of parameters, this will return an array reference of core properties plus additional PostgreSQL specific properties that start with pg_

The core properties are: db, login, passwd, host, port, driver, database, schema, server, opt, uri, debug

_convert_datetime2object

Based on an hash reference of parameters and this will transcode any datetime column into a DateTime object.

It returns the data hash reference

Possible parameters are:

  • data

    An hash reference of data typically returned from a "fetchrow_hashref" in DBD::Pg

  • statement

    This is the statement from which to check for columns

_convert_json2hash

Based on an hash reference of parameters, and this will check for the data for any json column and if found, it will transcode the json to hash reference.

It returns the data hash reference

Possible parameters are:

  • data

    An hash reference of data typically returned from a "fetchrow_hashref" in DBD::Pg

  • statement

    This is the statement from which to check for columns

_dsn

This returns a properly formatted dsn as a string.

SEE ALSO

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.