DB::Object::Postgres - SQL API
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" );
v0.5.0
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.
DBI
perlmod
So, DB::Object::Postgres acts as a convenient, modifiable wrapper that provide the programmer with an intuitive, user-friendly and hassle free interface.
Create a new instance of DB::Object::Postgres. Nothing much to say.
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:
See "connect" in DB::Object for more information
The database name you wish to connect to
The login used to access that database
The password that goes along
The server, that is hostname of the machine serving a SQL server.
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.
mysql
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.
Sets or gets one more pg attributes.
Valid attributes are:
Is read-only.
Can be changed.
Specifies if the current database connection should be in read-only mode or not.
If true, boolean values will be returned as the characters 't' and 'f' instead of '1' and '0'.
Valid entries are 0, 1 and 2
When true, question marks inside of statements are not treated as placeholders, e.g. geometric operators
When true, colons inside of statements are not treated as placeholders
Indicates if DBD::Pg should attempt to use server-side prepared statements. On by default
Mark the beginning of a transaction.
Any arguments provided are passed along to "begin_work" in DBD::Pg
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
Same as "connect" in DB::Object, only specific to PostgreSQL.
See "_connection_params2hash"
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:
Sets the ALLOW_CONNECTIONS attribute
ALLOW_CONNECTIONS
"If false then no one can connect to this database. The default is true, allowing connections."
Sets the CONNECTION LIMIT attribute
CONNECTION LIMIT
"How many concurrent connections can be made to this database. -1 (the default) means no limit."
Sets the ENCODING attribute
ENCODING
"Character set encoding to use in the new database."
Sets the LC_COLLATE attribute
LC_COLLATE
"Collation order (LC_COLLATE) to use in the new database."
Sets the LC_CTYPE attribute
LC_CTYPE
"Character classification (LC_CTYPE) to use in the new database."
Sets the IS_TEMPLATE attribute
IS_TEMPLATE
"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."
Sets the OWNER attribute
OWNER
"The role name of the user who will own the new database"
Sets the TABLESPACE attribute
TABLESPACE
"The name of the tablespace that will be associated with the new database"
Sets the TEMPLATE attribute
TEMPLATE
"The name of the template from which to create the new database"
See PostgreSQL documentation for more information
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.
Returns a list of all available databases.
Provided with a table name and a function name and this will call "func" in DBD::Pg and returns the result.
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.
A convenient wrapper to "having" in DB::Object::Postgres::Query
Instantiate a new DB::Object::Postgres::Lo and returns it.
Get the id of the primary key from the last insert.
Takes a table name, a lock mode and an optional nowait and this will lock the given table by issuing the relevant query.
nowait
Supported lock types are: SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE
If the lock failed (NULL), it returns undef(), otherwise, it returns the return value.
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.
See "on_conflict" in DB::Object::Postgres::Tables
Calls "pg_ping" in DBD::Pg
Set or gets the PostgreSQL query object (DB::Object::Postgres::Query) used to process and format queries.
Provided with a data and some data type, and this will possibly put surrounding single quotes and return the result.
Calls DBD::pg_release passing it through whatever arguments were provided.
Replace queries are not supported in PostgreSQL
A convenient wrapper to "returning" in DB::Object::Postgres::Query
Will roll back any changes made to the database since the last transaction point marked with "begin_work"
Will call "pg_rollback_to" in DBD::Pg and passing it through whatever arguments were provided.
Will call "pg_savepoint" in DBD::Pg and passing it through whatever arguments were provided.
Sets or gets the database schema.
It returns the value as a Module:Generic::Scalar object
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
SET search_path = $search_path
$search_path
If no arguments is provided, this will issue the query SHOW search_path to retrieve the current search path.
SHOW search_path
It returns an array object (Module::Generic::Array) containing the search paths found.
This is inherited from DB::Object
Please see PostgreSQL documentation for the variables that can be set.
This returns the database handler property pg_socket
pg_socket
Provided with a table name and this will check if the table exists.
It accepts the following options:
If true, this will search anywhere.
A database schema.
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:
If true, it will search anywhere.
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:
The object name
The object owner (role)
Database schema, if any.
The object type, which may be one of: table, view, materialized view, special, foreign table
table
view
materialized view
special
foreign table
Calls "trace" in DBD::Pg passing through whatever arguments were provided.
Calls "trace_msg" in DBD::Pg and pass it whatever arguments were provided.
Unlock does not work with PostgreSQL
Variables are currently unsupported in Postgres
Returns the PostgreSQL database server version.
This information is cached per object for efficiency.
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.
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
client_encoding
utf8
It returns an hash reference of those parameters.
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_
pg_
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
db
login
passwd
host
port
driver
database
schema
server
opt
uri
debug
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:
An hash reference of data typically returned from a "fetchrow_hashref" in DBD::Pg
This is the statement from which to check for columns
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.
This returns a properly formatted dsn as a string.
dsn
DBI, Apache::DBI
Jacques Deguest <jack@deguest.jp>
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.
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.