NAME

DB::Object::SQLite - DB Object SQLite Driver

SYNOPSIS

use DB::Object;

my $dbh = DB::Object->connect({
    driver => 'SQLite',
    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" );

VERSION

v1.1.3

DESCRIPTION

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

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

connect

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

See "_connection_params2hash"

METHODS

alias

This is inherited from "alias" in DB::Object

as_string

This is inherited from "as_string" in DB::Object

avoid

This is inherited from "avoid" in DB::Object

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:

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

Can be changed.
sqlite_see_if_its_a_number

Can be changed.
sqlite_unicode

Can be changed.
sqlite_unprepared_statements

Is read-only.
sqlite_use_immediate_transaction

Can be changed.
sqlite_version

Is read-only.

available_drivers

Return the list of available drivers.

This is an inherited method from "available_drivers" in DB::Object

begin_work

Mark the beginning of a transaction.

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

bind

This is an inherited method from "bind" in DB::Object

cache

This is an inherited method from "cache" in DB::Object

can_update_delete_limit

Returns the boolean value for the SQLite compiled option ENABLE_UPDATE_DELETE_LIMIT by calling "has_compile_option"

check_driver

This is an inherited method from "check_driver" 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::SQLite

compile_options

Returns the cached list of SQLite compiled options. The cached file is in the file sql_sqlite_compile_options.cfg in the sytem directory.

connect

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

It sets sqlite_unicode to a true value in the connection parameters returned by "_connection_params2hash"

See "_connection_params2hash"

copy

This is an inherited method from "copy" in DB::Object

create_table

This is an inherited method from "create_table" in DB::Object

data_sources

This is an inherited method from "data_sources" in DB::Object

data_type

This is an inherited method from "data_type" in DB::Object

database

This is an inherited method from "database" in DB::Object

database_file

Returns the file path to the database file.

databases

Returns a list of databases, which in SQLite, means a list of opened sqlite database files.

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

delete

This is an inherited method from "database" in DB::Object

disconnect

This is an inherited method from "disconnect" in DB::Object

do

This is an inherited method from "do" in DB::Object

enhance

This is an inherited method from "enhance" in DB::Object

func

Provided with a table name and a function name and this will call DB::SQLite passing it the table name and the function name.

It returns the value received from the function call.

get_sql_type

Provided with a data type as a string and this returns a SQLite constant suitable to be passed to "bind_param" in DBI

having

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

has_compile_option

Provided with a compile option (the character case is irrelevant) and this will check if it exists or not.

last_insert_id

lock

This is an unsupported feature in SQLIte

on_conflict

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

pragma

This is still a work in progress.

replace

Just like for the INSERT query, "replace" takes one optional argument representing a DB::Object::SQLite::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.

register_function

This takes an hash reference of parameters and will register a new function by calling "sql_function_register" in DBD::SQLite

Possible options are:

code

Anonymous code to be executed when the function is called.
func

This is an hash reference representing registry of functions. The value for each key is the option hash reference.
flags

An array reference of flags
name

The function name

remove_function

Provided with a function name and this will remove it.

It returns false if there is no function, or returns the options hash reference originally set for the function removed.

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"

sql_function_register

Provided with an hash reference of options and this will register a sql function by calling "sqlite_create_function" in DBD::SQLite

Possible options are:

argc

The function arguments
code

Anonymous perl code to be executed when the function is called
flags

An array reference of flags. Those flags are joined with | and "eval" in perlfunc'ed
name

The function name

stat

Provided with an hash or hash reference of parameters and this will call "sqlite_status" in DBD::SQLite and get the hash reference of values returned.

If the option reset is set, then this will call "sqlite_status" in DBD::SQLite passing it 0 to reset it instead.

If the option type is specified, this will return the equivalent property from the stat hash reference returned by "sqlite_status" in DBD::SQLite, otherwise, it will return the hash in list context and the hash reference of stat properties in scalar context.

table_info

Provided with a table name and this will retrieve the table information as an hash reference.

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

It takes no optional parameters.

Information retrieved are:

name

The table name
type

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

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.

tables_info

Provided with a database or using by default the current database and this will issue a query to get an array reference of all tables.

It returns the array reference.

trace

Trace is unsupported on SQLite.

unlock

Unlock is unsupported on SQLite.

variables

Variables are unsupported on SQLite.

version

This returns the, possibly cached, SQLite server version as a version object.

_check_connect_param

This returns an hash reference of connection parameters.

If there is no "database_file" currently set, it will use the property uri.

The database is taken from the property database, or derived from the last path segment of the uri.

The database file is made absolute and is et as the database_file property

The database is name, if not set, is derived from the base path of the database_file

The host property is set to localhost and port property to 0

It returns the hash reference of parameters thus processed.

_check_default_option

Provided with an hash or hash reference of options and this will check it and set some default value.

The only default property this sets is sqlite_unicode to true.

It returns the hash reference of options.

_connection_options

Provided with an hash reference of parameters and this will check them and returns an hash reference of options who name start with sqlite_

_connection_parameters

Provided with an hash or hash reference of connection parameters and this will extra all the properties that start with sqlite_/ and add them to an array of core properties: db login passwd host port driver database server opt uri debug

It returns those properties as an array reference.

_dbi_connect

This calls "_dbi_connect" in DB::Connect and do more driver specific processing.

It will register all the functions set in the global hash reference $PRIVATE_FUNCTIONS which is a function name to code reference pairs. For each of those function, they will be added by calling "sql_function_register"

It returns the database handler object (DBD::Object::SQLite)

_dsn

Using the "database_file" set and this will issue a connection to the SQLite database file.

If the file does not exist or is not writable, this will return an error, otherwise this will return the string representing the dsn, which are connection parameters separated by ;

_parse_timestamp

Provided a string and this will parse it to return a DateTime object.

SQLITE FUNCTIONS AVAILABLE

ceiling

This is a sql function to be registered automatically upon connection to the SQLite database file.

It leverages "ceil" in POSIX

concat

This returns the arguments provided concatenated as a string.

curdate

Returns a string representing the year, month and date separated by a -

This is computed using DateTime

curtime

Returns a string representing the hours, minutes and seconds separated by a :

This is computed using DateTime

dayname

Based on a datetime that is parsed using "_parse_timestamp", this returns the day name of the week, such as Monday

dayofmonth

Based on a datetime that is parsed using "_parse_timestamp", this returns the day of the month, such as 17.

dayofweek

Based on a datetime that is parsed using "_parse_timestamp", this returns the day of the week as a number from 1 to 7 with 1 being Monday

dayofyear

Based on a datetime that is parsed using "_parse_timestamp", this returns the day of the year, such as a number from 1 to 365, or possibly 366 depending on the year.

distance_miles

Provided with an original latitude, longitude and a target latitude and longitude and this will calculate the distance between the 2.

See the source StackOverflow post on which this function is based.

from_days

Calculate the number of days since January 1st of year 0 and returns a DateTime object.

from_unixtime

Provided with a unix timestamp, and this will return a datetime string such as YYYY-mm-dd HH:MM:SS

hour

Provided with a date time, and this will parse it and return the hour.

lcase

Provided with a string and this returns its lower case value.

left

Provided with a string and an integer n and this will return a substring capturing the nth first characters.

locate

This essentially does the same as "index" in perlfunc

log10

Provided with a number and this returns its logarithm base 10.

minute

Provided with a date time, and this will parse it and return the minutes.

month

Provided with a date time, and this will parse it and return the month.

monthname

Provided with a date time, and this will parse it and return the month name.

number_format

Provided with a number, a thousand separator, a decimal separator and a decimal precision and this will format the number accordingly and return it as a string.

power

Provided with a number and a power, and this will return the number powered

quarter

Provided with a date time, and this will parse it and return the quarter.

query_object

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

rand

This takes no argument and simply returns a random number using "rand" in perlfunc

regexp

Provided with a regular expression and the string to test, and this will test the regular expression and return true if it matches or false otherwise.

replace

Provided with a string, some term to replace and a replacement string and this will do a perl substitution and return the resulting string.

right

Provided with a string and an integer n and this will return the nth right most characters.

second

Provided with a date time, and this will parse it and return the seconds.

space

Provided with an integer and this will return as much spaces.

sprintf

This behaves like "sprintf" in perlfunc and provided with a template and some arguments, it will return a formatted string.

to_days

Provided with a date time, and this will return the number of days since January 1st of year 0.

ucase

This returns the string provided with all its characters in upper case.

unix_timestamp

Provided with a date time and this will returns its unix timestamp representation.

week

Provided with a date time, and this will parse it and return the week.

weekday

Provided with a date time, and this will parse it and return the day of the week.

year

Provided with a date time, and this will parse it and return the day of the year.

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)