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.0

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

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

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

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

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

savepoint

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

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.

AUTHOR

Jacques Deguest <jack@deguest.jp>

COPYRIGHT & LICENSE

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

To install DB::Object, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DB::Object

CPAN shell

perl -MCPAN -e shell
install DB::Object

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

VERSION

DESCRIPTION

CONSTRUCTOR

new

connect

METHODS

attribute

begin_work

commit

connect

create_db

create_table

databases

datatype_dict

datatypes

func

get_sql_type

having

large_object

last_insert_id

lock

make_schema

on_conflict

pg_ping

query_object

quote

release

replace

returning

rollback

rollback_to

savepoint

schema

search_path

set

socket

table_exists

table_info

tables_info

trace

trace_msg

unlock

table_info

variables

version

_check_connect_param

_check_default_option

_connection_options

_connection_parameters

_convert_datetime2object

_convert_json2hash

_dsn

SEE ALSO

AUTHOR

COPYRIGHT & LICENSE

Module Install Instructions