[PROPOSAL] DB_Simple

======== Overview -------- DB_Simple attempts to fill the space between DB, MDB, and DB_DataObject by supporting automated SQL queries, providing and easy to use configuration system, and abstracting datatypes. See the two attached files for the DB_Simple class and an example extension class. ============ Introduction ------------ DB_Simple is a combination of DB (which abstracts API calls to databases), MDB (which abstracts API calls and RDBMS native data types), and DB_DataObject (which automates the building of SQL queries and "object"-ifies SQL results). DB is a strong foundation but does not provide datatype abstraction. While MDB is powerful, it is very complex and difficult for a new user to get started with. DB_DataObject is only moderately complex, but does not appear to support automated table creation, definition of calculated columns, or abstracted data types. To fill in the spaces between these three, DB_Simple provides: - API abstraction through DB, and data type abstraction similar to MDB - a way to avoid and extend native SQL data types for date and time - column definition for declared fields and calculated columns - index definition for declared fields and calculated columns - automated table creation based on a column mapping array - automated generation of SQL queries based on user-configured

     mappings similar to DB_DataObject

- automated validation of insert/update data on a by-column basis - easy-to-understand configuration options using class property arrays - clean-running (no notices under E_ALL) and well-commented code

     compatible with both PHP4 and PHP5

DB_Simple is _not_: - a data object per se; instead, it acts as an interface to a single

     table for insert/update/delete (although the automated SQL maps may
     select from joined tables as desired)

- supported for all PHP databases; only fbsql, mssql, mysql, oci8,

     pgsql, and oci8 are available at this time (similar to MDB)

- intended to be used on its own; like DB_DataObject, you have to

     extend it with your table-specific column maps and application-
     specific SQL maps.

- a replacement for MDB or DB_DataObject, although it walks a

     parallel path.

========== Data Types ---------- Similar to MDB, DB_Simple abstracts a number of datatypes, defined as constants within the class: - DB_SIMPLE_STRING variable-length string, typically VARCHAR - DB_SIMPLE_INTEGER

     signed long integer, typically BIGINT or LONGINT

- DB_SIMPLE_INTNOSIGN

     unsigned long integer, typcially BIGINT UNSIGNED

- DB_SIMPLE_DECIMAL

     fixed-point decimal value, typically DECIMAL or NUMBER

- DB_SIMPLE_FLOAT

     double-precision floating-point decimal value, typically DOUBLE

- DB_SIMPLE_CLOB

     a character large-object, typically LONGTEXT or CLOB

- DB_SIMPLE_TIME *

     ISO standard time, "hh:ii:ss"

- DB_SIMPLE_TIMEZONE *

     ISO standard time with zone letter, "hh:ii:ssz"

- DB_SIMPLE_TIMEOFFSET *

     ISO standard time with zone offset, "hh:ii:ss+hh:ii"

- DB_SIMPLE_DATE *

     ISO standard date, "yyyy-mm-dd"

- DB_SIMPLE_DATETIME *

     ISO standard date and time, "yyyy-mm-dd hh:ii:ss"

- DB_SIMPLE_DTZONE *

     ISO standard date and time with zone letter, "yyyy-mm-dd hh:ii:ssz"

- DB_SIMPLE_DTOFFSET *

     ISO standard date and time with zone offset,
     "yyyy-mm-dd hh:ii:ss+hh:ii"

* Stored in the table as a specific-length string. Note that instead of attempting to maintain and convert database-native dates and times, DB_Simple uses string datatypes 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 datatype and the database native type. The benefit of the "forced storage" in DB_Simple is that when composing a WHERE clause, you do not need to know the native format of the SQL datatype; 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_SIMPLE_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)). =========== Index Types ----------- Also similar to MDB, DB_Simple abstracts index creation, defined as constants within the class: - DB_SIMPLE_INDEX

     a normal index

- DB_SIMPLE_UNIQUE

     a unique index

================= Column Definition ----------------- To support automated table creation, automated SQL generation, and automated validation of insert/update, DB_Simple requires that you define your table columns in advance, using the DB_Simple $column property. Here is an example column definition: $this->column['id'] = array(

	'type'    => DB_SIMPLE_INTNOSIGN,

'notnull' => DB_SIMPLE_NOTNULL, 'index' => DB_SIMPLE_UNIQUE ); That will define a table field for the DB_Simple class called 'id'; DB_Simple recognizes that the column contains unsigned integers, that values for this column are not allowed to be null, and that it has a unique index. Here is another example: $this->column['cost'] = array( 'type' => DB_SIMPLE_DECIMAL, 'size' => 10, 'scope' => 2, 'default' => "'0.00'" ); That will define a table field for the DB_Simple object called 'cost'; DB_Simple recognizes that the column is a fixed-point decimal 10 digits long with 2 places for the decmial portion, defaults to a value of '0.00', is allowed to be null, and has no index. Finally, an example of a calculated column: $this->column['area'] = array( 'type' => DB_SIMPLE_FLOAT, 'calc' => "length * width", 'index' => DB_SIMPLE_INDEX ); This does not define a table field; DB_Simple sees that the column is calculated at SELECT-time. The column is calculated as the value of the 'length' field times the value of the 'width' field, and is expected to be returned as a double-precision floating-point decimal. At table creation time, DB_Simple will create an index called 'area', based on the calculation results for each row in the table. The keys for column definitions are as follows: - 'type' Required. A DB_Simple datatype constant. - 'size' Required for DB_SIMPLE_STRING, DB_SIMPLE_DECIMAL, otherwise ignored. An integer maximum length for the column. - 'scope'

     Required for DB_SIMPLE_DECIMAL, otherwise ignored.
     An integer fixed number of decimal places.

- 'notnull'

     Optional.
     Boolean true or false, for whether null values are not allowed.  If
     not set, defaults to false (i.e., null values are allowed).  For
     clarity, use the DB_SIMPLE_NOTNULL constant to indicate true (i.e.,
     NOT NULL).

- 'default'

     Optional.
     A string; the default value for a column when a null value is
     inserted. This is an SQL calculation statement, so if you want to
     insert a string, you must properly quote and escape it.

- 'calc'

     Optional.
     A string.  If 'calc' is set, then the column is not a table field,
     but is an SQL calculation.  The value of 'calc' is an SQL calculation
     statement. Columns that have 'calc' set are not declared when DB_Simple
     creates the table, and are not allowed to be used in insert/update
     (although they are of course allowed in the automated SQL map).

- 'index'

     Optional.
     A DB_Simple index constant, either DB_SIMPLE_INDEX or
     DB_SIMPLE_UNIQUE.
     If an index is specified for a column (even if the column is
     calculated), DB_Simple declare it when DB_Simple creates the table.
     This means you can have complex, multicolumn, calculated indexes.

======================== Automated Table Creation ------------------------ When you call the DB_Simple constructor, you can ask it to set up the table (based on the column map) if the table does not already exist. DB_Simple will look for the table and create it as necessary (not including calculated fields). It will also create the related column indexes as well (whether "normal" fields or calculated columns). =============== SQL Clause Maps --------------- To support automated SELECT statements, DB_Simple requires that you define baseline SQL clauses for certain methods (typically getList() and getItem(), but you can add your own if you like). Here are some example SQL clause maps: $this->sql['getList'] = array( DB_SIMPLE_SELECT => array('id', 'username', 'email'), DB_SIMPLE_WHERE => "division = 'Information Technology'", DB_SIMPLE_ORDER => "username" ); $this->sql['getItem'] = array( DB_SIMPLE_SELECT => array('id', 'username', 'email', 'picture'), DB_SIMPLE_WHERE => "division = 'Information Technology'", ); When the getList() or getItem() method is called, DB_Simple will build an SQL select statement from these clauses and return the row results. The SQL clause map keys are: - DB_SIMPLE_SELECT

     Required.
     An array of $this->column keys.

- DB_SIMPLE_FROM

     Optional, defaults to the $table property.
     A string for the FROM clause in the SELECT statement (do not
     include the "FROM" keyword).

- DB_SIMPLE_JOIN

     Optional.
     A string for the JOIN clause in the SELECT statement.  This is the
     only key that requires you to use an SQL keyword in the string,
     because there are so many different kinds of JOINs (left, right,
     outer, etc).  All the other keys require that you do *not* use the
    SQL keyword (e.g., FROM and WHERE).

- DB_SIMPLE_WHERE

     Optional.
     A string; the baseline WHERE clause for the SELECT statement.
     Methods may or may not add to this WHERE clause to filter results.
     Do not include the "WHERE" keyword.

- DB_SIMPLE_GROUP

     Optional.
     A string for the GROUP BY clause in the SELECT statement.  Do not
     include the "GROUP" or "GROUP BY" keyword.

- DB_SIMPLE_ORDER

     Optional.
     A string for the ORDER BY clause in the SELECT statement.  Do not
     include the "ORDER" or "ORDER BY" keyword.

======================== Automated SELECT Methods ------------------------ DB_Simple has two built-in autoated SELECT methods, getList() and getItem(). The getList() method returns an array of multiple rows of results from the database, and supports ad-hoc filtering of results, ad-hoc ordering, and ad-hoc limits. For example, with respect to the 'getList' SQL map above... $rows = $this->getList(); ... gets an array of all Information Technology division members ordered by username. However, with ad-hoc filters, ordering and limits, you can do this: $rows = $this->getList("email LIKE '%@mac.com'", "email", 0, 10); That will return a list of the first ten users in the Information Technology division whose email addresses end in "@mac.com", ordered by email address. The getItem() method is similar, except it requires filter (which should result in exactly one row being returned): $row = $this->getItem("username = 'boshag'"); That will get the record for user "boshag" from the database, provided he is in Information Technology division (which is the baseline WHERE clause in the 'getItem' SQL map). =========================================== Automated INSERT and UPDATE With Validation ------------------------------------------- DB_Simple 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, becuase the DB_Simple instance has a defined column map, it knows what to expect from every field. This, it will pre-validate all INSERT and UPDATE values to make sure they match the column requirements (datatype, size, decimla places, not-null, and so on) before attempting to connect to the database. DB_Simple 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_Simple data type (e.g., DB_SIMPLE_DATETIME is always in "yyyy-mm-dd hh:ii:ss" format in the table, and attempting to insert or update a field not conforming to that format will fail validation). Likewise, if a column is defined as DB_SIMPLE_NOTNULL, you cannot insert a null value (or update to a null value). ============ Known Issues ------------ DB_Simple should throw error codes, not just error messages. DB_Simple has only been tested with MySQL. _______________________________________________________________________ Paul M. Jones pmjones@ciaweb.net http://ciaweb.net Savant: the simple alternative to Smarty. http://phpsavant.com/

Attachment: [application/x-gzip] DB_Simple.php.gz
Attachment: [application/x-gzip] DB_Simple_Content.class.php.gz