new(%options) : SQL driver constructorget_tables() : return a list of tables in the databasedbget_columns($table) : return a list of columns in a tabledbget_key($table) : return the primary key of a tabledbselect($rtable,$rcolumn,$cond,$sort,$limit) : fetches records from the databasedbcount($rtable,$condition) : returns number of matching recordsdbinsert($table,$data) : inserts a new record into a table.dbupdate($table,$data,$cond) : modifies records in a table.dbdelete($table,$cond) : deletes records from a table.query($sql_statement) : generic query methodget_sth($sql_statement) : get a statement handlereset() : clear internal stateparam(@values) : parameter handlingwhere_match($match_hash) : constructs a WHERE clauseget_table_info($table) : fetches table attributescustom_query($sql_statement,@parameters) : dbh() : return a handle to the (DBI) DB driverThe 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()dbget_tables()dbget_columns()dbget_key()dbselect()dbinsert()dbupdate()dbdelete()There are two useful concepts for understanding how ExSite represents data records:
$data[2]{price}.
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 constructorUsage: 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:
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 databaseExample: my @tables = $db->get_tables();
dbget_columns($table) : return a list of columns in a tableExample: my @columns = $db->get_columns("sales_data");
dbget_key($table) : return the primary key of a tableExample: my $key = $db->dbget_key("sales_data");
dbselect($rtable,$rcolumn,$cond,$sort,$limit) : fetches records from the databaseReturns an array of datahashes matching the input criteria:
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''.
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 methodThis 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 handleReturns 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 stateThe 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 handlingIf 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 clauseReturns 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 attributesThis 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 driverThis 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.