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.
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()
dbget_tables()
dbget_columns()
dbget_key()
retrieve the primary key for a table
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.
Status and errors are stored in the object for later reporting.
Example: $db->query("SELECT * FROM SALESDATA");
where_match($match_hash)
: constructs a WHERE clauseReturns 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 attributesThis 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.