NAME

db.pl - table-level repository interface to relational database

SYNOPSIS

  use Msql;
  require "db.pl";

  # Most common methods shown here.  See METHODS for others.

  if (!db_table_exists("Patient")) {
    db_new_table("Patient", "pnum int, name char(20), sex char(1)");
    db_create_index_unique("Patient", "pnum");
    db_create_index("Patient", "name");
  }

  db_insert_row("Patient", ["Jones", 555, "M"]);

  $pats = db_select("Patient", "sex = 'M' AND pnum > 100");
  while (@p = $pats->fetchrow()) {
    print "Name: $p[0], PNUM: $p[1], Sex: $p[2] \n";
  }

  $pat = db_select_row("Patient", "name = 'Jones'");
  print "Jones has PNUM $pat[1] \n";

  db_copy_into("MalePatients", "Patients", "sex = 'M'");

  db_update("Patient", "sex = 'F'", "name = 'Jones'");  # change Jones sex

  db_delete("Patient", "sex = 'M'"); # removes males from table

  db_drop("Patient");  # destroys table

  $date = db_date_now(); # gets current date in msql date format

  $bool = db_valid_type($typename);

REQUIRES

Msql, wrm.pl

DESCRIPTION

The db.pl API connects to the relational database and exports a table-level interface to repository data. Supports SQL-like queries, which return a ``statement handle'' from which results can be retrieved one at a time using the ``fetchrow'' method on the statement handle (see SYNOPSIS). The repository object interface (repo.pl) builds an additional level of abstraction on top of db.pl (preserving the SQL functionality), so the cgi programmer doesn't need direct access to this package. There are no dependencies on the repo data model in this package.

db.pl uses MsqlPerl-1.03 as the interface to the database server. Future version of db.pl will use the more generic DBI instead. Note that the db.pl buffers repo.pl from changes to the underlying database adaptor, so migrating to DBI should only minimally affect the exported interface.

Some of the methods below accept an optional ``$filter'' parameter. For these methods, the query is constrained by the filter string, which should have the following form:

 column OPERATOR value 
   [ AND | OR column OPERATOR value ]*

where OPERATOR can be <, >, =, <=, >=, <>, LIKE, RLIKE or CLIKE.

METHODS

db_create_index($tablename, $columname): Creates an index on the specified column which speeds up queries using that column.
db_select($table, $filter): Returns statement handle reference to 2D array of selected rows from named table. If SQL-filter string present, applies it to where clause, otherwise defaults to all rows. Returns statement handle reference, from which each row can be accessed as an array of fields using $sth->fetchrow(). prints msql error string if select fails.
db_select_row($table, $filter): Returns array of field values. Selects single row from named table. Note distinction from ``db_select''. If SQL-filter string present, applies it to where clause, otherwise defaults to all rows. If more than row row satisfies query, only the first is returned. prints msql error string if select fails.
db_new_table($table_name, $column_string): Creates new table named $table_name. $column_string specifies each column name followed by it's type, which can be int, real, or char(N). Spaces should delimit name and type, commas delimit name-type pairs. Prints msql error string if creation fails (see Msql).
db_table_exists($table_name): Returns 1 if table exists, 0 otherwise.
db_fields($table): Returns reference to array of field names for specified table, ordered by column-order in table. Use db_types to get a corresponding array of the types of those fields. Returns 0 if table not found.
db_types($table): Reference to array of type names for the columns of specified table. ordered by column-order in table. Each type will be one of: UNDEFINED INT CHAR REAL IDENT NULL TEXT DATE UINT MONEY TIME. Use db_fields to get a corresponding array of the names of those fields. Note: references to repos and files will appear as type INT at this level. Use the global hash %WRM_SCHEMA_DEFS to see the actual types at the repo level. Returns 0 if table not found.
db_fields_multi(@tables): Returns reference to array of field names for list of tables, in the form ``table_name.field_name''.
db_join_all($tables, $filter): Create a join of all the fields of the specified tables. Return rows which satisfy the $filter clause.
db_join($tables, $cols, $filter): Create a join of the specified columns from the specified tables. columns should be specified as ``table_name.column_name''. Return rows which satisfy the $filter clause.
db_insert_row($tablename, $values): Inserts a row into named table. $values should be a ref to a list of values whose types correspond to the columns of that table. The function adds the necessary quotes around string values, and translates empty numbers to be zero.
db_copy_into($dest, $source, $filter): Queries $source table using $filter, then copies resulting rows into $dest table.
db_update($tablename, $setstr, $filter): Updates record which matches $filter, using $setstr, which should be of the form: column=value [, column=value]*
db_delete($tablename, $filter): Deletes rows matching $filter from table.
db_drop($tablename): Drops named table. All data will be destroyed.
db_connect($db, $host): Connects to named database located on $host machine. Prints error message if connection fails.
db_date_now(): Gets current date in msql date format, which is of the form DD-MON-YYYY, where MON is the common 3-letter abbreviation (e.g. ``23-Jan-1999'').
db_valid_type($typename): Returns 1 if $typename is a valid database type (e.g. int, char, etc.), returns 0 otherwise.

AUTHOR