cs::DBI - convenience routines for working with DBI
use cs::DBI;
An assortment of routines for doing common things with DBI.
Return an ISO date string (yyyy-mm-dd) in local time for the supplied UNIX time_t gmt. If not supplied, gmt defaults to now.
Return a reference to a hash tied to a database table, which may then be manipulated directly. This is not as efficient as doing bulk changes via SQL (because every manipulation of the table does the matching SQL, incurring much latency) but it's very convenient. The optional parameter where may be used to supply a WHERE clause to the underlying SQL query. If the optional parameter preload is true the entire table is fetched at instantiation time with a single SQL call, thus bypassing the latency (a win if memory is plentiful and the table is not too large).
Return a cs::DBI::Table::RowObject representing the row whose keyfield matches keyvalue from the table specified by dbh, table and where as for hashtable above. This object is suitable for subclassing by a table specific module.
Return a reference to an array tied to a database table, which may then be manipulated directly. The optional parameter where may be used to supply a WHERE clause to the underlying SQL query.
Return a statement handle for the SQL command sql applied to database dbh. This handle is cached for later reuse.
Perform the SQL command sql on database dbh with the execute-args supplied.
Return a statement handle to query table in database dbh where all the specified fields have specific values (values to be supplied when the statement handle is executed).
Execute the statement handle sth with the supplied execute-args, returning an array of hashrefs representing matching rows.
Return an array of matching row hashrefs from table in dbh where field = value.
Return an array of matching row hashrefs from table in dbh where field = value and the columns START_DATE and END_DATE span the time when. The argument when is optional and defaults to today.
Return an array of matching row hashrefs from table in dbh ehere the columns START_DATE and END_DATE span the time when. The argument when is optional and defaults to today.
Set the field to value in the table in database dbh for records where the specified where-field = where-value pairs all match.
Lock the specified table in database dbh.
Release all locks held in database dbh.
Obtain a statement handle with a conjunctive WHERE constraint specified by ANDing together the strings wheren. Return value is an array which is empty on error and otherwise contains (sth,where-args...) where sth is the new statement handle and where-args is the where-argn values.
Construct an SQL statement with a conjunctive WHERE constraint specified by ANDing together the strings wheren. Return value is an array containing the SQL statement (a string) and the arguments with which to execute it (the where-argn values).
Obtain a statement handle with an arbitrary WHERE constraint specified by the string where and the following arguments where-args. Return value is an array which is empty on error and otherwise contains (sth,where-args...) where sth is the new statement handle.
Obtain a statement handle to DELETE records whose keyfields have values in the list keys.... Note: if keys is empty an expensive no-op is generated.
Return the ids from the field destidfield for records from table overlapping the date when and which has srcidfield equal to the supplied id. The parameter when is optional, and defaults to today.
Lookup the dated table srctable and return the records from the table desttable whose field desttableidfield matches the field destidfield from srctable for records from srctable overlapping the date when and which has srcidfield equal to the supplied id. The parameter when is optional, and defaults to today.
Retrieve active records from the specified table with the specified key value. If the optional parameter alldates is true (it defaults to false) return all the records for this key, as an array of hashrefs. Otherwise return only those records which overlap the day when. The parameter when is optional, and defaults to today.
The records are returned ordered from oldest to most recent.
cmpDatedRecords()
Compare for order the two dated records references by the global variables $a and $b, for use in sorts.
Retrieve active records from the specified table with the specified key value whose dates overlap the period denoted by start and end. The (keyfield, key) pair is optional; if omitted, all records overlapping the period will be returned.
The records are returned ordered from oldest to most recent.
Crop dated records which overlap the period start-end. At least one of start and end must be defined. If supplied, the optional parameters where and where-args specify a further constraint on the records eligible for cropping. Returns success.
Edit the specified table such that no records for a single value of keyfield overlap. Later records are presumed to be more authoritative than earlier records. Earlier records overlapping later fields have their END_DATE fields cropped. Warning: earlier records completely overlapped by later records are discarded. This may not be what you want.
If the optional list of keyfields, keys..., is supplied then only those values will have their dates cleaned. The default behaviour is to clean the entire table.
Crop the dates in the specified (dbh,table) where the field keyfield equals key and the date fields overlap the period start-end. Records completely overlapped by the period are dropped.
BUG: records which completely overlap the period are untouched because I haven't decided how to deal with holes.
last_id()
Return the id of the last item inserted with ExecuteWithRec below.
Add a row represented by the hashref record to table in database dbh. Returns the last_id value.
Create a new cs::DBI object for insertion of rows into table in database dbh. If the parameter dfltok is supplied as 0 or 1 it governs whether it is permissible for the inserted rows to lack values for the fields named; the default is for all named fields to be required. Once created, this object may be used with the ExecuteWithRec method below.
Insert the records described by the record-hashrefs into the appropriate table.
DBI(3), cs::DBI::Table::RowObject(3)
Cameron Simpson <cs@zip.com.au>