The DB class is a wrapper class for one of the db driver classes.
It inherits general query methods from one, but only one of these,
determined at runtime.
DB also provides an assortment of simplified and/or convenience
wrapper methods for accessing the low-level database driver routines.
In addition, the DB class provides an assortment of caching, security,
access control, sorting, and row relationship methods.
The DB class is not normally used directly, unless the program is
doing pure data analysis. Normally the Form:: and Report:: classes
are preferred for web application work, as these classes inherit all
of DB's methods.
new() creates a database handle. The following arguments should be
specified in the options:
- type
-
the database type (``sql'' and ``text'' are most common types)
- name
-
database name
- map
-
map name
- other...
-
any other args required by driver (eg. ``user'' and ``pass'' may
be required to access SQL databases).
Note that the map is a small database that describes the main database.
The map name is the name of the map database - it will be opened up
using the DBmap class.
These methods invoke the low-level methods select, insert, update, and
delete, but otherwise have no knowledge of the underlying driver.
Unlike the low-level driver methods, they can also make use of the
dbmap, if needed.
All fetch methods return datahashes, that is one hash per record with
keys equal to the field names, and values equal to the data for those
fields. All fields in each record are returned, by default, including
fields that the user is not permitted to access (access control is
performed at a higher level, in Form:: or Report::).
The fetch methods will take an optional SORT argument, which will
attempt to get the database driver to sort the resulting records
according to the value of SORT, which can either be a field name, or a
reference to a list of field names. If no SORT argument is specified,
the data will be unsorted, but may still be sorted after the fact using the
DB::sort_rows() method.
Usage: $db->fetch(TABLE, KEY, SORT)
Returns the single record from TABLE with primary key KEY. If
more than one record has that primary key, this routine returns the
first one found. SORT is optional.
The fetch() method is synonymous with fetch_key().
Usage: $db->fetch_match(TABLE, MATCH, SORT)
MATCH is a reference to a hash containing field => value pairs.
The values are assumed to be text segments that will be used to match
data in the actual database. The SQL ``%'' wildcard may be used.
Records that match ALL field => value pairs will be returned.
SORT is optional.
Usage: $db->fetch_all(TABLE, SORT)
Returns all records in the table, optionally sorted according to the
value of SORT.
Obviously this can be a dangerous method to use on a large table.
Usage: $db->fetch_child(TABLE, PARENT, PARENT_KEY, SORT)
fetch_child() is a trivial join, fetching all rows from table B
that relate to a single row in table A, where table B references A
using its primary key. The rows in B are ``children'' of the row in A.
(There is no need to use a real JOIN mechanism, since we already know
which row in A we are matching to.) SORT is optional.
Usage: $db->fetch_join(TABLE_LIST, MATCH_LIST)
In development.
Usage: $db->fetch_m2m(TABLE_LIST, MATCH_LIST)
Say that table A relates to table B via a junction table J that contains
references to the primary keys of both A and B. fetch_m2m will
perform a join on A,B,J, selecting all members of B that
relate to a specific member of A via the junction table J.
Example: a singer can have songs on many albums, and an album can
contain many different singers. If we have tables ``singer'', ``album'',
and ``singer_album'' (which contains the primary keys of particular
singer/album pairs), then we can select all the albums with songs by
singer ``XYZ'' using:
$db->fetch_m2m("album","singer","XYZ","singer_album");
(read this as ``fetch albums for singer XYZ using singer_album'')
Usage: $db->count(TABLE, MATCH)
Returns the number of records in TABLE that match the passed match
hash. If MATCH is left off, this returns the number of records
in the table.
Usage: $db->get_columns(TABLE)
Returns an array of column names in the given table. This data is
looked up in the DBmap, not in the database itself. As such, it is
really returning a list of columns that ExSite knows about, not the
full list of columns in the database.
Usage: $db->get_key(TABLE)
Returns the primary key of the given table, as reported by the DBmap.
Records can be removed to the trash bin (from which they can be
recovered), or can be deleted irrecoverably.
The trash bin is simply a database table called ``trash'', which stores
sufficient information to reconstruct lost records if they are needed.
Each trash record corresponds to the deletion of one or more records.
Reconstruction information is written to the trash record, and then
the original records are deleted. The data field of the trash record
is a text value consisting of one line for each deleted record. The line
is a perl snippet with the following format:
"table",{column1=>"value1",column2=>"value2",...}
ie. the table the data came from, and a text representation of the
datahash representing the record.
Because a trash record can store multiple deleted records together,
the records can be removed and restored as a bunch, in the style of
transactions.
Deleting records from the trash table is equivalent to ``emptying the trash''.
(Yes, you can move trash records to the trash, but that would be pointless.)
Usage: $db->trash_key($table,$record_id)
Usage: $db->trash_r($table,$record_id,$skip_check)
The latter call finds all related records, and removes them as part of the
same trash ``transaction''. If restored, all of the records will be restored
as a group. The $skip_check flag bypasses regular record ownership
checks, if true.
Usage: $db->undelete($table,$record_id)
This reverses one of the previous trash calls. Because the record ID
is included in the reconstruction data, and we normally use serial number
primary keys, which are never recycled, the restored data should just drop
into its original positions without a problem. Your mileage may vary if
using a different type of primary key, however.
Regular deletions are permanent and irreversible.
Usage: $db->delete_key($key)
Usage: $db->delete_r($table,$key)
Note that some records (eg. a website section) can have an awful lot of
descendants, so this call can permanently remove large sections of the
database.
Usage: $db->delete($table,$match)
This call deletes all records that match the match hash.
Usage: $db->insert(TABLE,DATA);
Inserts a single record into TABLE, where DATA is a datahash reference.
If you are using normal serial-number primary key tables, the datahash
will normally contains no primary key. The insert function returns the
primary key of the new record on success.
If DATA contains the primary key, ExSite performs a REPLACE instead of an
INSERT in SQL databases.
Usage: $db->update(TABLE,DATA,CONDITION);
Updates TABLE, modifying the values to those in DATA (a datahash of
columns to change, and the values to change them to).
If CONDITION is specified as a match hash, all records matching the
data will be modified. If no CONDITION is given, update() will
modify the record whose primary key matches the primary key value
specified in DATA. This allows you to fetch a datahash using
fetch(), set some new values, and then pass the whole datahash back
to update() to save it without explicitly defining a condition.
In a simple sense, a database record is ``owned'' by its parent record(s).
A single record can therefore own its own descendant records directly,
and their descendants indirectly (and so on...). When you do a recursive
record search (such as in trash_r() and delete_r(), above), you are
recursively finding all the data that is owned by the starting record.
This search is performed by find_descendants(), below.
If the starting record is a user record (ie. a record from the
member table), then the descendant records are literally owned by
that user. That user gains special priveleges over that data that
other users do not have. (For instance, they may be allowed to edit
their own data, but only view the data of others.)
If group management is supported, then you can also proceed from an
originating group record to see what the group owns. Group managers
have permission to alter the data in their group, which could
encompass the data of multiple users. Groups can be defined in different
ways, but by default a group corresponds to a website and its users.
Usage: $db->user_owns( TABLE, RECORD );
TABLE is the table being operated on. RECORD is a record id, or a
datahash reference
Returns true if:
- user level 1
-
the record is related to the current user's member record
- user level 2
-
the record is related to the current user's group record
- user level 3 or 4
-
these users own everything
This presumes a 4-level user model, which is common, but not universal.
Usage: $db->group_owns( TABLE, RECORD );
TABLE is the table being operated on. RECORD is a record id, or a
datahash reference
Returns true if:
- user level 1 or 2
-
the record is related to the current user's group record (section, by default)
- user level 3 or 4
-
these users own everything
Usage: $db->find_owner( TABLE, RECORD, TYPE );
TABLE is the table being operated on. RECORD is a record id, or a
datahash reference. TYPE is the owner type, either ``user'' or ``group''.
The UID or GID of the record is returned.
Usage: $db->fetch_ancestor( TABLE, DESCENDANT_TABLE, DESCENDANT_RECORD );
A is an ancestral row of B, if B is among A's descendant records. In
this routine you pass the table that A is from, along with the table
and record of B. The routine returns a record from A that is an
ancestor of the record from B. In principle, there could be multiple
tables from A that are ancestral to B, but this method only returns
the first one found. It returns a datahash (or datahash reference),
in the same manner as fetch().
Usage: $db->find_descendants( TABLE, RECORD );
Each row has rows that reference it, and rows that references those,
and so on. The find_descendants method returns pointers to all
rows that descend from a single ancestral row. It is useful for
locating all data pertaining to a specific data item.
The return value is a reference to hash, formatted as follows:
$ref->{table1} => [ key1, key2, ... ], $ref->{table2} => [ ... ], ...
If you have a lot of specialized queries, you can index and store them
in a query library for reuse and caching of results. Each query is
registered with a unique name, and invoked using that name. This has
the following benefits:
- a given SQL query is only coded (and debugged) in one place
- simpler code/syntax for performing complex data fetches
- effective caching of results on customized queries
Usage: $db->set_query( "NAME", %opt );
NAME is a string that is used to reference the query. %opt can
have the following parameters:
- sql
-
This is the actual SQL statement that will be executed, with optional
substitution markers '?'.
- nparam
This is the number of query parameters, used to validate actual queries (but
is ignored if not defined). It must match the number of ``?'' placeholders
in the SQL statement.
- mode
-
``r'' (for readonly data fetches) or ``w'' (for operations that write to or
otherwise modify the database). This is used for cache management.
- keys
-
This is a table name or arrayref of table names affected by this
query, and is used for cache management. If undef, the query results
will not be cached. If defined but blank, the query results are
considered static, and will not be cleared from the cache when the
database is modified.
Usage: $db->get_query( NAME, SUBSTITUTIONS, ... );
NAME refers to a query that was previously registered using
set_query(), above. SUBSTITUTIONS is a list of parameters to
substitute into the query, and should match the parameter placeholders
in the query SQL statement.
The sort method orders lists of datahashes.
Usage: @sorted = db->sort_rows($table,$list,@columns);
where $list is a list of datahashes, and @columns is a list of columns
to sort on. Determination of whether to use alphabetic or numeric
comparisons for a given column is done automatically, using the dbmap.
NOTE: you can also sort by passing the sortable column list to
the DB fetch/select methods. Those sorts are performed by the DB
engine, if that is supported. This sort method, on the other hand,
is performed by the webserver, and works for all drivers. Which is
the more efficient approach depends on the relative loads experienced
by the two servers.
