Developers > Kernel Documentation > Database Management > SQL.pm

SQL.pm


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. Escape the '%' symbol with a backslash if it should be taken literally.

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.

If the query contains '?' parameter placeholders, then the internal parameters attribute is used to provide data to fill these placeholders. Some statement generation methods will automatically generate statements with placeholders and parameter lists.

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

Examples:

    $db->query("SELECT * FROM SALESDATA");
    $db->query("SELECT * FROM INVOICE WHERE DATE > ?");  # uses parameters

get_sth($sql_statement) : get a statement handle

Returns a statement handle for the given SQL statement. It tries to reuse an existing handle, if the query has already been executed. (This is not likely if you are not parameterizing your statements, since that would mean you are re-executing the same statement.)

reset() : clear internal state

The state of the previous query is retained in the object for status handling purposes. The reset function clears the state for a new query.

param(@values) : parameter handling

If one or more values is passed, they are added to the parameters list for the current query. No sanity check is performed to ensure that the length of the parameter list equals the number of placeholders in the statement.

If no values are passed, the current parameter list is return in list mode, or the number of parameters in the list is returned in scalar mode.

where_match($match_hash) : constructs a WHERE clause

Returns a WHERE clause built from the passed match hash, which is a set of column->value pairs. Each pair is related using a SQL operator (one of '=', 'IS', or 'LIKE', depending on the type of data in the value), and all are concatenated using '&&'.

If the value is a scalar reference, it is explicitly inserted into the clause, and if 'NULL' then the operator is also changed to IS.

If the value contains unescaped wildcards ('%'), then the operator is changed to 'LIKE'.

All user data is parameterized, which means the where clause takes a '?' parameter placeholder, and the actual value is added to the statement's parameter list. Substitutions of values into the statement occurs when the statement is executed, which provides for safer handling of user data.

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 table attributes

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

custom_query($sql_statement,@parameters) : 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.

The complete statement can be provided, or a parameterized statement can be given, along with a list of parameter values. The latter is safer when handling untrusted data values.

This method is not portable to other ExSite database drivers.

dbh() : return a handle to the (DBI) DB driver

This is an internal method that delays connecting to the database until a query is actually executed. Requests that rely on cached data will thus be able to avoid the overhead of a SQL connection entirely.

Topics