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(%options)` - DB constructor

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.

Retrieving Data

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.

`fetch(), fetch_key()` - fetch a record by its primary key

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().

`fetch_match()` - fetch a record using a match hash.

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.

`fetch_all()` - fetch all records in a table

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.

`fetch_child()` - fetch related rows in a sub-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.

`fetch_join()` - fetch related rows in a sub-table

Usage: $db->fetch_join(TABLE_LIST, MATCH_LIST)

In development.

`fetch_m2m()` - fetch rows that are related via a join table.

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'')

`count()` - count records in a table

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.

`get_columns()` - return list of column names in a 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.

`get_key()` - return primary key of a table

Usage: $db->get_key(TABLE)

Returns the primary key of the given table, as reported by the DBmap.

Removing Records

Records can be removed to the trash bin (from which they can be recovered), or can be deleted irrecoverably.

Trash

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.)

`trash_key()` - trash a single record

Usage: $db->trash_key($table,$record_id)

`trash_r()` - trash a record and its descendants.

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.

`undelete()` - restore trashed records

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.

Deleting Records

Regular deletions are permanent and irreversible.

`delete_key()` - delete a single record

Usage: $db->delete_key($key)

`delete_r()` - delete a record and its descendants

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.

`delete()` - delete all matching records

Usage: $db->delete($table,$match)

This call deletes all records that match the match hash.

Modifying Data

`insert()` - add a new record

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.

`update()` - modify existing records

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.

Record Ownership

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.

`user_owns()` - determine whether the current user ``owns'' the given row

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.

`group_owns()` - determine whether the current group ``owns'' the given row

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

`find_owner()` - determine who owns the given row

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.

`fetch_ancestor()` - find ancestor row to a specific row

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().

`find_descendants()` - find all records that descend from a particular record

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} => [ ... ], ...

Query Library

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

`set_query()` - register a special re-useable query

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.

`get_query()` - request data from a special re-useable query

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.

Sorting

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.

DB.pm