DB::Object - SQL API
use DB::Object; my $dbh = DB::Object->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; # 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" );
Using fields objects
$cust->where( $dbh->OR( $cust->fo->email == 'john@example.org', $cust->fo->id == 2 ) ); my $ref = $cust->select->fetchrow_hashref;
Doing some left join
my $geo_tbl = $dbh->geoip || return( $self->error( "Unable to get the database object \"geoip\"." ) ); my $name_tbl = $dbh->geoname || return( $self->error( "Unable to get the database object \"geoname\"." ) ); $geo_tbl->as( 'i' ); $name_tbl->as( 'l' ); $geo_tbl->where( "INET '?'" << $geo_tbl->fo->network ); $geo_tbl->alias( id => 'ip_id' ); $name_tbl->alias( country_iso_code => 'code' ); my $sth = $geo_tbl->select->join( $name_tbl, $geo_tbl->fo->geoname_id == $name_tbl->fo->geoname_id ); # SELECT # -- tables fields # FROM # geoip AS i # LEFT JOIN geoname AS l ON i.geoname_id = l.geoname_id # WHERE # INET '?' << i.network
Using a promise (Promise::Me) to execute an asynchronous query:
my $sth = $dbh->prepare( "SELECT some_slow_function(?)" ) || die( $dbh->error ); my $p = $sth->promise(10)->then(sub { my $st = shift( @_ ); my $ref = $st->fetchrow_hashref; my $obj = My::Module->new( %$ref ); })->catch(sub { $log->warn( "Failed to execute query: ", @_ ); }); # Do other regular processing here # Get the My::Module object my( $obj ) = await( $p );
Sometimes, having placeholders in expression makes it difficult to work, so you can use placeholder objects to make it work:
my $P = $dbh->placeholder( type => 'inet' ); $orders_tbl->where( $dbh->OR( $orders_tbl->fo->ip_addr == "inet $P", "inet $P" << $orders_tbl->fo->ip_addr ) ); my $order_ip_sth = $orders_tbl->select( 'id' ) || fail( "An error has occurred while trying to create a select by ip query for table orders: " . $orders_tbl->error ); # SELECT id FROM orders WHERE ip_addr = inet ? OR inet ? << ip_addr
Be careful though, when using fields objects, not to do this:
my $tbl = $dbh->some_table; $tbl->where( $tbl->fo->some_field => '?', $tbl->fo->other_field => '?' ); my $sth = $tbl->select || die( $tbl->error );
Because the fields objects are overloaded, instead do this:
my $tbl = $dbh->some_table; $tbl->where( $tbl->fo->some_field == '?', $tbl->fo->other_field == '?' ); my $sth = $tbl->select || die( $tbl->error );
v1.4.0
DB::Object is a SQL API much alike DBI, but with the added benefits that it formats queries in a simple object oriented, chaining way.
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 changes its behaviour with new versions. In short, I wanted a better, more accurate control over the SQL connection and an easy way to format sql statement using an object oriented approach.
perlmod
So, DB::Object acts as a convenient, modifiable wrapper that provides the programmer with an intuitive, user-friendly, object oriented and hassle free interface.
However, if you use the power of this interface to prepare queries conveniently, you should cache the resulting statement handler object, because there is an obvious real cost penalty in preparing queries and they absolutely do not need to be prepared each time. So you can do something like:
my $sth; unless( $sth = $dbh->cache_query_get( 'some_arbitrary_identifier' ) ) { # prepare the query my $tbl = $dbh->some_table || die( $dbh->error ); $tbl->where( id => '?' ); $sth = $tbl->select || die( $tbl->error ); $dbh->cache_query_set( some_arbitrary_identifier => $sth ); } $sth->exec(12) || die( $sth->error ); my $ref = $sth->fetchrow_hashref;
This will provide you with the convenience and power of DB::Object while keeping execution fast.
Create a new instance of DB::Object. Nothing much to say.
Provided with a database, login, password, server:[port], driver, schema, and optional hash or hash reference of parameters and this will issue a, possibly cached, database connection and return the resulting database handler.
database
login
password
server
port
driver
schema
Create a new instance of DB::Object, but also attempts a connection to SQL server.
It can take either an array of value in the order database name, login, password, host, driver and optionally schema, or it can take a has or hash reference. The hash or hash reference attributes are as follow.
Note that if you provide connection options that are not among the followings, this will return an error.
cache_connections
Defaults to true.
If true, this will instruct DBI to use "connect_cached" in DBI instead of just "connect" in DBI
Beware that using cached connections can have some drawbacks, such as if you open a cached connection, enters into a transaction using "begin_work" in DB::Object, then somewhere else in your code a call to a cached connection using the same parameters, which DBI will provide, but will reset the database handler parameters, including the AutoCommit that will have been temporarily set to false when you called "begin_work", and then you close your transaction by calling "rollback" or "commit", but it will trigger an error, because AutoCommit will have been reset on this cached connection to a true value. "rollback" and "commit" require that AutoCommit be disabled, which "begin_work" normally do.
AutoCommit
Thus, if you want to avoid using a cached connection, set this to false.
More on this issue at DBI documentation
database or DB_NAME
The database name you wish to connect to
login or DB_LOGIN
The login used to access that database
passwd or DB_PASSWD
passwd
The password that goes along
host or DB_HOST
host
The server, that is hostname of the machine serving a SQL server.
port or DB_PORT
The port to connect to
driver or DB_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.
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.
schema or DB_SCHEMA
The schema to use to access the tables. Currently only used by PostgreSQL
opt
This takes a hash reference and contains the standard DBI options such as PrintError, RaiseError, AutoCommit, etc
conf_file or DB_CON_FILE
conf_file
DB_CON_FILE
This is used to specify a json connection configuration file. It can also provided via the environment variable DB_CON_FILE. It has the following structure:
{ "database": "some_database", "host": "db.example.com", "login": "sql_joe", "passwd": "some password", "driver": "Pg", "schema": "warehouse", "opt": { "RaiseError": false, "PrintError": true, "AutoCommit": true } }
Alternatively, it can contain connections parameters for multiple databases and drivers, such as:
{ "databases": [ { "database": "some_database", "host": "db.example.com", "port": 5432, "login": "sql_joe", "passwd": "some password", "driver": "Pg", "schema": "warehouse", "opt": { "RaiseError": false, "PrintError": true, "AutoCommit": true } }, { "database": "other_database", "host": "db.example2.com", "login": "sql_bob", "passwd": "other password", "driver": "mysql", }, { "database": "/path/to/my/database.sqlite", "driver": "SQLite", } ] }
uri or DB_CON_URI
uri
This is used to specify an uri to contain all the connection parameters for one database connection. It can also provided via the environment variable DB_CON_URI. For example:
http://db.example.com:5432?database=some_database&login=sql_joe&passwd=some%020password&driver=Pg&schema=warehouse&&opt=%7B%22RaiseError%22%3A+false%2C+%22PrintError%22%3Atrue%2C+%22AutoCommit%22%3Atrue%7D
Here the opt parameter is passed as a json string, for example:
{"RaiseError": false, "PrintError":true, "AutoCommit":true}
See "alias" in DB::Object::Tables
Sets/gets the boolean value for whether to allow unsafe bulk delete. This means query without any where clause.
where
Sets/gets the boolean value for whether to allow unsafe bulk update. This means query without any where clause.
Takes any arguments and wrap them into a AND clause.
AND
$tbl->where( $dbh->AND( $tbl->fo->id == ?, $tbl->fo->frequency >= .30 ) );
See "as_string" in DB::Object::Statement
Sets or gets the boolean value. If true, then this api will automatically transcode datetime value into their equivalent DateTime object.
Sets or gets the boolean value. If true, then this api will automatically transcode json data into perl hash reference.
See "avoid" in DB::Object::Tables
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:
Is read-only.
Can be changed.
Return the list of available drivers.
Returns the base class.
If no values to bind to the underlying query is provided, "bind" 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()
Activate caching.
$tbl->cache->select->fetchrow_hashref();
Sets/get the cached database connection.
Sets or gets the directory on the file system used for caching data.
Provided with a unique name, and this will return a cached statement object if it exists already, otherwise it will return undef
Provided with a unique name and a statement object (DB::Object::Statement), and this will cache it.
What this does simply is store the statement object in a global $QUERIES_CACHE hash reference of identifier-statement object pairs.
$QUERIES_CACHE
It returns the statement object cached.
Sets or gets a boolean value whether to cache the table fields object.
When this is enabled, the second time a database table is accessed, it will retrieve its field objects from the cache rather than recreating them after reading the structure from the database. This is much faster.
By default, this is set to false.
This can be specified in the configuration file passed when instantiating a new DB::Object object with the property cache_table
DB::Object
cache_table
my $all_dbs = $dbh->cache_table_fields; my $all_tables = $dbh->cache_table_fields( database => $some_database ); my $all_fields = $dbh->cache_table_fields( database => $some_database, table => $some_table, ); $dbh->cache_table_fields( database => $some_database, table => $some_table, fields => $some_hash_reference, );
Sets or gets the hash reference of database table field name to their corresponding object.
If no parameter is provided, it will return the entire cache for all databases for a given driver.
If only a database name is provided, it will return the cache hash reference for all the tables in the given database.
If a database and a table name is provided, this will return an hash reference of field name to their corresponding object.
If a database and a table name and an hash reference of field names to their corresponding objects is provided, it will set this hash as the cache for the given database and table.
Sets or gets the DB::Object::Cache::Tables object.
Check that the driver set in $SQL_DRIVER in ~/etc/common.cfg is indeed available.
It does this by calling "available_drivers".
This will attempt a database server connection.
It called "_connection_params2hash" to get the necessary connection parameters, which is superseded in each driver package.
Then, it will call "_check_connect_param" to get the right parameters for connection.
It will also call "_check_default_option" to get some driver specific default options unless the previous call to _check_connect_param returned an has with a property opt.
It will then set the following current object properties: "database", "host", "port", "login", "passwd", "driver", "cache", "bind", "opt"
Unless specified in the connection options retrieved with "_check_default_option", it sets some basic default value:
Finally it tries to connect by calling the, possibly superseded, method "_dbi_connect"
It instantiate a DB::Object::Cache::Tables object to cache database tables and return the current object.
Returns the global value for $CONSTANT_QUERIES_CACHE
$CONSTANT_QUERIES_CACHE
This is used by "constant_queries_cache_get" and "constant_queries_cache_set"
Provided with some hash reference with properties pack, file and line that are together used as a key in the cache and this will use an existing entry in the cache if available.
pack
file
line
Provided with some hash reference with properties pack, file and line that are together used as a key in the cache and query_object and this will set an entry in the cache. it returns the hash reference initially provided.
query_object
Provided with a data type constant value and this returns its equivalent data type as a string in upper case.
This constant is set by the driver, or by default by DBI. For example SQL_VARCHAR is 12 and its data type is VARCHAR
SQL_VARCHAR
12
VARCHAR
See also "datatype_to_constant"
Provided with either a reference to an hash or an hash of key => value pairs, "copy" 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.
This is a method that must be implemented by the driver package.
Given an optional list of options as hash, this return the data source of the database handler.
Given a reference to an array or an array of data type, "data_type" 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.
Return the name of the current database.
This returns the list of available databases.
Returns an hash reference of each data type with their equivalent constant, regular expression (re), constant name and type name.
constant
re
name
type
Each data type is an hash with the following properties for each type: constant, name, re, type
The data returned is dependent on each driver.
my $type = $dbh->datatype_to_constant( 'varchar' ); # 12 # Below achieves the same result use DBI ':sql_types'; say SQL_VARCHAR; # 12
Provided with a data type as a string and this returns its equivalent driver value if any, or by default the one of set by DBI.
The data type provided is case insensitive.
If no matching data type exists, it returns undef in scalar context, or an empty list in list context.
undef
As pointed out by DBI documentation: "just because the DBI defines a named constant for a given data type doesn't mean that drivers will support that data type."
See also "constant_to_datatype"
my $types = $dbh->datatypes;
Returns an hash reference of data types to their respective values.
If the driver has its own, it will return the driver's constants, otherwise, this will return an hash reference of DBI data type constants.
See "delete" in DB::Object::Tables
Disconnect from database. Returns the return code.
my $rc = $dbh->disconnect;
Provided with a string representing a sql query, some hash reference of attributes and some optional values to bind and this will execute the query and return the statement handler.
The attributes list will be used to prepare the query and the bind values will be used when executing the query.
$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 );
Return the name of the driver for the current object.
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.
Get the currently set error.
Is just an alias for "err".
Is just an alias for "errstr".
Get the currently set error string.
This return the keyword FALSE to be used in queries.
FALSE
Provided a boolean value and this toggles fatal mode on/off.
See "format_statement" in DB::Object::Tables
See "format_update" in DB::Object::Tables
See "from_unixtime" in DB::Object::Tables
Provided with a sql type, irrespective of the character case, and this will return the driver equivalent constant value.
See "group" in DB::Object::Tables
Sets or gets the host property for this database object.
See "insert" in DB::Object::Tables
Get the id of the primary key from the last insert.
See "limit" in DB::Object::Tables
See "local" in DB::Object::Tables
This method must be implemented by the driver package.
Sets or gets the login property for this database object.
When invoked, "no_bind" 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.
Disable caching of queries.
Returns a new DB::Object::NOT object, passing it whatever arguments were provided.
Returns a NULL string to be used in queries.
NULL
See "on_conflict" in DB::Object::Tables
Returns a new DB::Object::OR object, passing it whatever arguments were provided.
See "order" in DB::Object::Tables
Returns a DB::Object::Placeholder object, passing it whatever arguments was provided.
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.
SET
Supported parameters are:
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 perlfunc in case of error.
Otherwise, it returns the current object used to call the method.
Sets or gets the passwd property for this database object.
Evals a SELECT 1 statement and returns 0 if errors occurred or the return value.
Will prepare and execute a simple SELECT 1 and return 0 upon failure or return the value returned from calling "execute" in DBI.
SELECT 1
Same as "P". Returns a DB::Object::Placeholder object, passing it whatever arguments was provided.
Sets or gets the port property for this database object.
Provided with a sql query and some hash reference of options and this will prepare 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" or "error".
Same as "prepare" except the query is cached.
It prepares and executes the given SQL query with the options provided and return "undef" in perlfunc upon error or the statement handler upon success.
Sets or gets the query object.
This is used to properly format data by surrounding them with quotes or not.
Calls "quote" in DBI and pass it whatever argument was provided.
See "replace" in DB::Object::Tables
See "reset" in DB::Object::Tables
See "returning" in DB::Object::Tables
See "reverse" in DB::Object::Tables
See "select" in DB::Object::Tables
Provided with variable and this will issue a query to SET the given SQL variable.
If any error occurred, undef will be returned and an error set, otherwise it returns true.
See "sort" in DB::Object::Tables
Issue a SHOW STATUS query and if a particular $type is provided, it will return its value if it exists, otherwise it will return "undef" in perlfunc.
SHOW STATUS
$type
In absence of particular $type provided, it returns the hash list of values returns or a reference to the hash list in scalar context.
Queries the DBI state and return its value.
Returns the list of driver packages such as DB::Object::Postgres
Returns the list of driver name such as Pg
Given a table name, "table" will return a DB::Object::Tables object. The object is cached for re-use.
When a cached table object is found, it is cloned and reset (using "reset"), before it is returned to avoid undesirable effets in following query that would have some table properties set such as table alias.
Provided with a table name and this returns true if the table exist or false otherwise.
It returns an array reference of hash reference containing information about each table column.
Add the given table name to the stack of cached table names.
Connects to the database and finds out the list of all available tables. If cache is available, it will use it instead of querying the database server.
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.
Returns the table cache object
Rebuild the list of available database table.
Returns the list of table in list context or a reference of it in scalar context.
See "tie" in DB::Object::Tables
True when a transaction has been started with "begin_work", false otherwise.
Returns TRUE to be used in queries.
TRUE
See "unix_timestamp" in DB::Object::Tables
This is a convenient wrapper around "unlock" in DB::Object::Query
See "update" in DB::Object::Tables
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.
Provided with a boolean value and this sets or get the use_cache parameter.
Query the SQL variable $type
It returns a blank string if nothing was found, or the value found.
See "where" in DB::Object::Tables
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.
Provided with an hash reference of connection parameters, this will get the valid parameters by calling "_connection_parameters" and the connection default options by calling "_connection_options"
It returns the connection parameters hash reference.
Provided with an hash reference of options, and it actually returns it, so this does not do much, because this method is supposed to be supereded by the driver package.
Provided with an hash reference of connection parameters and this will returns an hash reference of options whose keys match the regular expression /^[A-Z][a-zA-Z]+/
/^[A-Z][a-zA-Z]+/
So this does not do much, because this method is supposed to be superseded by the driver package.
Returns an array reference containing the following keys: db login passwd host port driver database server opt uri debug
Provided with an hash reference of connection parameters and this will check if the following environment variables exists and if so use them: DB_NAME, DB_LOGIN, DB_PASSWD, DB_HOST, DB_PORT, DB_DRIVER, DB_SCHEMA
DB_NAME
DB_LOGIN
DB_PASSWD
DB_HOST
DB_PORT
DB_DRIVER
DB_SCHEMA
If the parameter property uri was provided of if the environment variable DB_CON_URI is set, it will use this connection uri to get the necessary connection parameters values.
DB_CON_URI
An URI could be http://localhost:5432?database=somedb or file:/foo/bar?opt={"RaiseError":true}
http://localhost:5432?database=somedb
file:/foo/bar?opt={"RaiseError":true}
Alternatively, if the connection parameter conf_file is provided then its json content will be read and decoded into an hash reference.
The following keys can be used in the json data in the conf_file: database, login, passwd, host, port, driver, schema, opt
The port can be specified in the host parameter by separating it with a semicolon such as localhost:5432
localhost:5432
The opt parameter can Alternatively be provided through the environment variable DB_OPT
DB_OPT
It returns the hash reference of connection parameters.
Given a query string or a reference to it, it cleans the statement by removing leading and trailing space before and after line breaks.
It returns the cleaned up query as a string if the original query was provided as a scalar reference.
Provided with an hash or hash reference of options and this will simply return the data property.
This does not do anything meaningful, because it is supposed to be superseded by the diver package.
This will call "_dsn" which must exist in the driver package, and based on the dsn received, this will initiate a "connect_cache" in DBI if the object property "cache_connections" has a true value, or simply a "connect" in DBI otherwise.
dsn
Provided with some json data and this will decode it using JSON and return the associated hash reference or "undef" in perlfunc if an error occurred.
This will die complaining the driver has not implemented this method, unless the driver did implement it.
Provided with an hash reference and this will encode it into a json string and return it.
Given a package name and a hash reference, this builds 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.
Provided with some hash reference parameters and this will simply return it, so it does not do anything meaningful.
This is supposed to be superseded by the driver package.
A convenient wrapper around the "_process_limit" in DB::Object::Query
Provided with a DB::Object::Query and this will add it to the current object property query_object and return it.
This is supposed to be called from a DB::Object::Tables
Create a new DB::Object::Query object, sets the debug and verbose values and sets its property "table_object" in DB::Object::Query to the value of the current object.
Returns the current query_object
Check to see if the "query_object" is already set and then return its value, otherwise create a new object by calling "_query_object_create" and return it.
Provided with a DB::Object::Query and this will remove it from the current object property query_object.
It returns the object removed.
If this has not already been reset, this will mark the current query object as reset and calls "_query_object_remove" and return the value for "_query_object_get_or_create"
If it has been already reset, this will return the value for "_query_object_current"
This operator is used to query an array where all elements must match.
my $tbl = $dbh->hosts || die( "Uable to get table object 'hosts'." ); $tbl->where( $dbh->OR( $tbl->fo->name == 'example.com', 'example.com' == $dbh->ALL( $tbl->fo->alias ) )); my $sth = $tbl->select || die( "Failed to prepare query to get host information: ", $tbl->error ); my $ref = $sth->fetchrow_hashref;
See PostgreSQL documentation
Given a value, this returns a DB::Object::AND object. You can retrieve the value with "value" in DB::Object::AND
This is used by "where"
my $op = $dbh->AND( login => 'joe', status => 'active' ); # will produce: WHERE login = 'joe' AND status = 'active'
my $tbl = $dbh->hosts || die( "Uable to get table object 'hosts'." ); $tbl->where( $dbh->OR( $tbl->fo->name == 'example.com', 'example.com' == $dbh->ANY( $tbl->fo->alias ) )); my $sth = $tbl->select || die( "Failed to prepare query to get host information: ", $tbl->error ); my $ref = $sth->fetchrow_hashref;
For example:
SELECT c.code, c.name, c.name_l10n, c.locale FROM country_locale AS c WHERE c.locale = 'fr_FR' OR ('fr_FR' NOT IN (SELECT DISTINCT l.locale FROM country_locale AS l ORDER BY l.locale) AND c.locale = 'en_GB') ORDER BY c.code my $tbl = $dbh->country_locale || die( $dbh->error ); my $tbl2 = $dbh->country_locale || die( $dbh->error ); $tbl2->as( 'l' ); $tbl2->order( 'locale' ); my $sth2 = $tbl2->select( 'DISTINCT locale' ) || die( $tbl2->error ); $tbl->as( 'c' ); $tbl->where( $dbh->OR( $tbl->fo->locale == 'fr_FR', $dbh->AND( 'fr_FR' != $dbh->IN( $sth2 ), $tbl->fo->locale == 'en_GB' ) ) ); $tbl->order( $tbl->fo->code ); my $sth = $tbl->select( qw( code name name_l10n locale ) ) || die( $tbl->error ); say $sth->as_string;
Given a value, this returns a DB::Object::NOT object. You can retrieve the value with "value" in DB::Object::NOT
my $op = $dbh->AND( login => 'joe', status => $dbh->NOT( 'active' ) ); # will produce: WHERE login = 'joe' AND status != 'active'
Given a value, this returns a DB::Object::OR object. You can retrieve the value with "value" in DB::Object::OR
my $op = $dbh->OR( login => 'joe', login => 'john' ); # will produce: WHERE login = 'joe' OR login = 'john'
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.