ExSite::SQL - generic SQL database driver for ExSite

The SQL class serves as a glue layer between ExSite::DB and the Perl DBI classes. Perl DBI (DataBase-Indepedent interface) supports many different database engines, so this class can serve as a model for connecting to a variety of back-ends. As supplied with ExSite, it is configured to operate with MySQL.

As with all ExSite Database drivers, ExSite::SQL implements the following standard calls:

new()
make a new database handle

dbget_tables()
retrieve all table names

dbget_columns()
retrieve all column names from a table

dbget_key()
    retrieve the primary key for a table
dbselect()
retrieve data from tables

dbinsert()
add new data to tables

dbupdate()
modify old data in tables

dbdelete()
remove data from tables

There are two useful concepts for understanding how ExSite represents data records:

Data hash
this is a record stored in a hash, or associative array. The hash keys are the column names, and the values are the field values for that record. Groups of records are represented as arrays of hash references, so that (for example) the value of the ``price'' field in the 3rd record is $data[2]{price}.

Match hash
this is similar to a data hash, except that the hash values represent data to match to. For instance, $match{price} = ``4.95'' will select all records whose price field contains the value 4.95. If there are multiple keys/values in the match hash, all will be matched (ie. they imply a logical AND). The ExSite::SQL driver accepts SQL wildcards (``%'') in match values.

Many ExSite::SQL methods will also accept an explicit SQL WHERE clause in place of a match hash. However, that mode of use is not portable, and is recommended only when the simple AND logic of the standard match hashes is insufficient.

new(%options) : SQL driver constructor

Usage: my $db = new ExSite::SQL(%options);

Connects to the SQL server with the appropriate authentication information, and prepares to accept database queries.

%options containts the database connection info. Up to 6 parameters are expected:

driver
the DBI driver to use (``mysql'' by default)

server
the server to connect to

port
the port number to connect to (optional)

name
the name of the database

user
the user name to connect under

pass
the password to authenticate with

Example: my $db = new ExSite::SQL(name=>"my_db",server=>"my.server.com",user=>"my_name",pass=>"my_pwd");

get_tables() : return a list of tables in the database

Example: my @tables = $db->get_tables();

dbget_columns($table) : return a list of columns in a table

Example: my @columns = $db->get_columns("sales_data");

dbget_key($table) : return the primary key of a table

Example: my $key = $db->dbget_key("sales_data");

dbselect($rtable,$rcolumn,$cond,$sort,$limit) : fetches records from the database

Returns an array of datahashes matching the input criteria:

$rtable
a table name, or reference to an array of table names

$rcolumn
a column name, or reference to an array of column names, or references to a hash of tablename => array of column names, or NULL to select all columns.

$cond
a WHERE clause string, or a reference to a match hash

$sort
a sort string, or a reference to a list of columns to sort the results by

$limit
the maximum number of records to fetch

Most of these parameters are optional.

Examples:

@data = $db->dbselect("telephone"); - fetch all telephone records

@data = $db->dbselect("telephone",["number","type"]); - fetch only the number and type fields of all telephone records

@data = $db->dbselect("telephone",undef,{type=>"fax"}); - fetch all telephone records whose type field is set to ``fax''.

@data = $db->dbselect("telephone",undef,undef,"number"}); - fetch all telephone records, sorted by the number field.

@data = $db->dbselect("telephone",undef,undef,undef,100}); - fetch the first 100 telephone records

dbcount($rtable,$condition) : returns number of matching records

$rtable = table, or reference to array of tables

$cond = explicit conditional, or a reference to a match hash, which can optionally contain wildcards

Only $rtable is required for the function to work.

dbinsert($table,$data) : inserts a new record into a table.

$data is a reference to a datahash. This is inserted as a new record into $table.

Returns the primary key of the new record.

dbupdate($table,$data,$cond) : modifies records in a table.

$table is the table that the update operation will be performed on.

$data is a reference to a datahash, containing the fields and the values they should be changed to.

$cond is an optional match hash. Only records matching this will be updated.

If $cond is undefined, then the primary key of the record to be modified is taken from $data. If the primary key is not defined in $cond, this will throw an exception.

Returns an error messge if there were problems.

Examples:

$db->dbupdate("member",{password=>"",member_id=100});> - sets an empty password for member #100 (assuming that member_id is the primary key of the member table).

$db->dbupdate("member",{password=``''},{status=>``expired''});> - clears the password field for all members whose status field is set to ``expired''.

dbdelete($table,$cond) : deletes records from a table.

Deletes records in $table matching the match hash referenced by $cond. $cond can optionally name an explicit SQL WHERE condition, but this is not portable to other drivers.

Examples:

$db->dbdelete("member",{status=>"expired"}); - removes member records whose status field is set to ``expired''.


Internal and non-standard methods

The section describes ExSite::SQL methods that do not conform to the ExSite database driver API. These methods are for internal use only, or provide non-portable extensions to the API.

query($sql_statement) : generic query method

This method prepares and executes an arbitrary SQL query, and returns the statement handle. It does not fetch the results.

Status and errors are stored in the object for later reporting.

Example: $db->query("SELECT * FROM SALESDATA");

where_match($match_hash) : constructs a WHERE clause

Returns an SQL WHERE clause built from the passed match hash. Is sensitive to wildcards in the match hash, and modifies the WHERE clause (to use ``LIKE'') appropriately. Attempts to perform some basic sanity/security checks on the column names.

This is an internal routine, not normally called from outside.

Example: my $where = $db->where_match(quantity=9, size=>``L'');>

get_table_info($table) : fetches and stores table attributes

This is an internal utility routine that is used by get_key and get_columns.

custom_query($sql_statement) : perform an explicit SQL query.

This method prepares and executes an arbitrary SQL query. Unlike the lower-level query() method, this method fetches the results and returns them in an array of datahashes. This makes it a more user-friendly way to execute SQL statements that are more complex than the standard API allows for.

WARNING: no sanity checking or taint checking is performed. The caller is responsible for validating their own SQL statements.

This method is not portable to other ExSite database drivers.