Proposal for "DB_Table"

» Metadata » Status
  • Category: Database
  • Proposer: Paul Jones 
  • License: LGPL
» Description
Proposal: DB_Table

Recent Changes

DB_Table now supports HTML_QuickForm rules for elements derived from table columns, and supports setting of default values for QuickForm elements. Documentation for these is also complete.

Except for (1) automated discovery of existing table definitions, and (2) automated alteration of tables to match the class table definitions, DB_Table is now feature-complete. I expect to move it to beta, unless serious bug reports come in.


Overview

DB_Table is a wrapper for DB that acts as an interface to an SQL table, providing automated table creation, predefined and automated select views, automated insert and validation of inserted data, automated update and validation of updated data, automated form creation based ont eh table columns definitions through HTML_QuickForm, and a form of data type abstraction that forces the database to store date and time data as strings in ISO format.

The main benefit of DB_Table is that a developer can, with one file, distribute a table interface class that creates the table automatically and validates all data going into the table, regardless of the database backend, and creates input forms based on the table columns.

See the complete user-documentation, which includes extensive examples and explanations (much more than is covered in this proposal) here.

See the source code for DB_Table here.


Apology In Advance

Not to beat a dead horse, but this proposal is a new cover for a revised version of DB_Simple. Recall that there was no vote on DB_Simple, so although the three commentors were in the negative, it was not rejected.

The recent talk of the DB_OO class and the Propel/Creole project leads me to believe that others like myself are unsatisfied with DB_DataObject, MDB, et. al. While they are fine classes, they are not well-suited to my needs, and the fact that others are proposing code similar to DB_Table makes me think that it could be a useful addition to PEAR.

I have gone over the old DB_Simple thread and I think I have addressed some of the concerns there. Lukas, Alexey, and Alan were the main detractors of DB_Simple; perhaps the intervening time and the similar proposals will cause them to think differently about allowing DB_Table into PEAR.

I would prefer for DB_Table to be in the DB package, rather than a package of its own. In line with that preference, I have contacted Daniel Convissor (lead for DB) numerous times over the past few weeks, but he has been unable or unwilling to assess DB_Table as yet. As such, I cannot say one way or another if he will accept it into DB, so I will continue to maintain this proposal.


Introduction

DB_Table is a wrapper for DB (which abstracts API calls to databases) that provides MDB-like capabilities, especially as regards data type abstraction. It is not a data object class, it is a table interface.

Among other things, DB_Table provides:


  1. Complete end-user documentation as well as inline comments.
  2. A constructor that takes an existing DB object, so you can re-use the same connection object for multiple tables
  3. API abstraction through DB
  4. data type abstraction by avoiding and extending native SQL data types for date and time
  5. an in-class column definition system (no external config file required, which significantly eases extension and distribution of classes based on DB_Table)
  6. an in-class index definition system (no external config file required)
  7. an in-class HTML_QuickForm definition system for form elements based on column definitions (no external config file required)
  8. automated table creation (based on the defined columns and indexes) at object instantiation time; this means no need for a separate table-creation script
  9. an in-class "view" (i.e., predefined SELECT) system (no external config file)
  10. automated insert and update based on associative arrays (as per DB::autoExecute)
  11. automated validation of insert/update data on a by-column basis at insert/update time
  12. automated creation of HTML_QuickForm objects based on the table column definitions
  13. easy-to-understand configuration options using class property arrays
  14. clean-running (no notices under E_ALL) and well-commented code compatible with both PHP4 and PHP5


DB_Table is not:


  1. a competitor for DB_DataObject; it only acts as a table manipulation (create/insert/update/delete) and reading (select/view) device, not as a data encapsulator
  2. a code generator; you extend DB_Table yourself and define columns, indexes, and views inside the extended object
  3. supported for all PHP databases; only fbsql, mssql, mysql, oci8, pgsql, oci8, and sqlite are available at this time
  4. a replacement for MDB or DB_DataObject, although it walks a similar path.


Why Not Use (or Contribute To) Package X Instead?

DB: DB_Table extends (technically, it wraps) DB. I'd be happy to have DB_Table become a part of the DB package if only Daniel Convissor can take time to assess it for inclusion.

(M)DB_DataObject: Until recently only supported INT and STR types (update: Alan notes that it now supports DATE, DATETIME, TIME, MYSQLTIMESTAMP, but still no FLOATs), not as easy to get started with, it auto-generates classes (update: Alan notes that this is now optional). Goes at things exactly backwards from the way I (and apparently others) like to proceed. It uses the class to generate a .ini file, not the other way around. Similarly, the createTables script only creates from a .ini file, not from the table() and keys() setup methods. In short, its philosophy is quite different from DB_Table. This is not bad, just not my preferred way of doing things.

DB_QueryTool: no easy way to just "type in" the baseline query elements, it uses its own update() and insert() code (not extended from DB), lacking end-user documentation. Seems to be going for programmatic SQL generation.

MDB: Overly complex, very heavy, user documentation is lacking, is more of an alternative to DB (as a database interface) than a table interface proper.

MDB2: Less complex than MDB, but as with MDB, is more of an alternative to DB (as a database interface) than a table interface proper.

DB_Schema: It doesn't exist; or rather, it exists only as a thought in the minds of some developers.


Data Types

Similar to MDB, DB_Table abstracts a number of data types, defined as
constants within the class:


  1. DB_TABLE_COL_STRING: variable-length string, typically VARCHAR, up to 255 characters
  2. DB_TABLE_COL_SMALLINT, _INTEGER, and _BIGINT: 2-, 4-, and 8-byte integers, respectively
  3. DB_TABLE_COL_DECIMAL: fixed-point decimal value, typically DECIMAL or NUMBER
  4. DB_TABLE_COL_SINGLE: single-precision floating-point number, typically FLOAT
  5. DB_TABLE_COL_DOUBLE: double-precision floating-point number, typically REAL
  6. DB_TABLE_COL_DATE: ISO standard date, "yyyy-mm-dd" *
  7. DB_TABLE_COL_TIME: ISO standard time, "hh:ii:ss" *
  8. DB_TABLE_COL_DATETIME: ISO standard date and time, "yyyy-mm-dd hh:ii:ss" *
  9. DB_TABLE_COL_UNIXTIME: 32-bit Unix Timestamp +
  10. DB_TABLE_COL_CLOB: a character large-object, typically LONGTEXT or CLOB


* Forces the database to store as a string.

+ Forces the database to store as a 4-byte signed integer.

Note that instead of attempting to maintain and convert database-native dates and times, DB_Table uses string data types to represent those kinds of data, thus forcing every supported database to store the information in a known recognized format. This is different from MDB, which uses the database-native storage format and attempts to convert back-and-forth between the MDB data type and the database native type.

This "forcing of type storage" allows very easy portability between database servers; you do not need to convert your query terms to the database native format, you can just write them the way you know the database will store your data (because DB_Table is forcing it to be stored that way). Yes, you lose a lot of power that individual database back-ends may provide vis-a-vis their own data types; but then, if you are custom-writing your app for a specific RDBMS, you're probably not going to be able to port those database-specific abilities anyway.

The benefit of the "forced storage" in DB_Table is that when composing a WHERE clause, you do not need to know the native format of the SQL data type; you use a known standard format for dates and times. No conversion of types is necessary; the data is already in the table in a known format.

The drawback is that the database engine does not "know" that (for example) a DB_TABLE_DATE field is actually a date, because it is stored as a string. However, this should only present problems when using native SQL calculations (e.g., YEAR(date_field) or HOUR(time_field)).

This is one of the primary conceits of DB_Table: that you can force the database to store things the way you want them to be stored, thus avoiding the need to convert back-and-forth between native database types when moving from one RDBMS to another. (C.f. the comments from the SQLite guys on their implementation as well.)


Index Types

Also similar to MDB, DB_Table abstracts index creation, defined as constants within the class:


  1. DB_TABLE_IDX_NORMAL
  2. DB_TABLE_IDX_UNIQUE


Column Definition

To support automated table creation, automated SQL generation, and automated validation of insert/update, DB_Table requires that you define your table columns in advance, using the DB_Table $col property. Here is an example column definition:


$this->col['id'] = array(
DB_TABLE_COL_TYPE => DB_TABLE_COL_INTEGER,
DB_TABLE_COL_REQUIRE => true
);


That will define a table field for the DB_Table class called 'id'; DB_Table recognizes that the column contains unsigned integers, and that values for this column are not allowed to be null.

Here is another example:


$this->col['cost'] = array(
DB_TABLE_COL_TYPE => DB_TABLE_COL_DECIMAL,
DB_TABLE_COL_SIZE => 10,
DB_TABLE_COL_SCOPE => 2,
DB_TABLE_COL_DEFAULT => "'0.00'"
);


That will define a table field for the DB_Table object called 'cost'; DB_Table recognizes that the column is a fixed-point decimal 10 digits long with 2 places for the decimal portion, defaults to a value of '0.00', is allowed to be null, and has no index.


Automated CREATE

When you call the DB_Table constructor, you can ask it to set up the table (based on the column and index maps). Just pass the proper flag to the constructor:


  1. DB_TABLE_CREATE_SAFE: Creates a table only if it does not exist in the database
  2. DB_TABLE_CREATE_DROP: Drops an existing version of the table and re-creates it


Automated SELECT

To support automated SELECT statements via select() and selectResult(), DB_Table lets you define baseline SQL clause sets in the $sql property array. Here are some example view definitions:


$this->sql['list'] = array(
DB_TABLE_SQL_SELECT => 'id, username, email',
DB_TABLE_SQL_FROM => $this->table,
DB_TABLE_SQL_WHERE => "division = 'Information Technology'",
DB_TABLE_SQL_ORDER => "username",
DB_TABLE_GET => DB_TABLE_GET_ALL
);

$this->sql['item'] = array(
DB_TABLE_SQL_SELECT => '*',
DB_TABLE_SQL_FROM => $this->table,
DB_TABLE_SQL_WHERE => "division = 'Information Technology'",
DB_TABLE_GET => DB_TABLE_GET_ROW
);


When the select('list') or select('item') method is called, DB_Table will build an SQL select statement from these clauses and return the row results as an array.

When selectResult() is called instead of select(), DB_Table will return the results as DB_Result object.

DB_Table supports GROUP BY, HAVING, and JOIN clauses as well.

The select() and selectResult() methods allow for ad-hoc filters, ordering, and limits.


Automated INSERT and UPDATE With Validation

DB_Table uses DB::autoExecute() in its insert() and update() methods, which means all you need to do to insert or update table rows is pass an associative array where the key is a field name and the value is the field value.

In addition, because the DB_Table instance has a defined column map, it knows what to expect from every field. Thus, it can pre-validate all INSERT and UPDATE values to make sure they match the column requirements (data type, size, decimal places, not-null, and so on) before attempting to connect to the database. This also allows developers to add customized validations for insert and update calls.

DB_Table does not need to convert back and forth from database-native data types, because all data is stored in a format consistent with the DB_Table data type (e.g., DB_TABLE_DATETIME is always in "yyyy-mm-dd hh:ii:ss" format in the table, and attempting to insert or update with a value not conforming to that format will fail validation). Likewise, if a column is defined as DB_TABLE_NOTNULL, you cannot insert a null value (or update to a null value).

Automated Form Generation

DB_Table can generate HTML_QuickForm elements based on the column defintions with DB_Table::getForm(). This makes getting initial application developent very fast, as you don't need to code your form pages by hand.

The default form elements are very generic, so DB_Table allows you to specify HTML_QuickForm options inside the column defintions, thus ensuring that form fields are presented to the user in a consistent manner. For example, to define a column and add HTML_QuickForm element options:


$this->col['gender'] = array(

// column definition for the table
DB_TABLE_COL_TYPE => DB_TABLE_COL_STRING,
DB_TABLE_COL_SIZE => 1,

// form definition for HTML_QuickForm element
DB_TABLE_QF_LABEL => 'Your gender:',
DB_TABLE_QF_TYPE => 'select',
DB_TABLE_QF_VALS => array(
'm' => "Male",
'f' => "Female",
'h' => "Hermaphrodite",
'e' => "Eunuch"
),
DB_TABLE_QF_ATTRS => array('size' => '4')
);


DB_Table also supports setting default values for form elements, and supports HTML_QuickForm rules.

Known Issues

DB_Table has only been tested with MySQL.

DB_Table needs unit tests.
» Dependencies » Links
  • DB
  • HTML_QuickForm (optional)
» Timeline » Changelog
  • First Draft: 2004-01-01
  • Proposal: 2004-01-05
  • Call for Votes: 2004-04-12
  • Paul Jones
    [2004-01-21 21:32 UTC]

    BBCode now working properly, updated with new constants.
  • Paul Jones
    [2004-03-08 17:10 UTC]

    Now has integrated support for HTML_QuickForm; elements are automatically defined based on the table schema.
  • Paul Jones
    [2004-03-23 14:47 UTC]

    Updated links to documentation.
  • Paul Jones
    [2004-03-23 15:23 UTC]

    Updated with comments from Alan on the state of DB_Dataobject.
  • Paul Jones
    [2004-04-05 21:17 UTC]

    DB_Table now supports HTML_QuickForm rules for elements derived from table columns, and supports setting of default values for QuickForm elements. Documentation for these is also complete.

    Except for (1) automated discovery of existing table definitions, and (2) automated alteration of tables to match the class table definitions, DB_Table is now feature-complete. I expect to move it to beta, unless serious bug reports come in.