Database Table Definition
The database table definition file is only used when creating or updating the table definitions (install/update tables). It simply returns an array of table field names and types with the filename becoming the table's name. There must be a definition file for every table in the Exponent database. Model properties correspond to the database table's fields which are determined at runtime from the database, not the definition file.
Since most database tables are handled by a model based on expRecord, there are some standard field names which are handled automatically. Therefore a standard table structure for the generic model in the generic controller might look like this:
<?php return array( 'id'=>array( DB_FIELD_TYPE=>DB_DEF_ID, DB_PRIMARY=>true, DB_INCREMENT=>true), 'title'=>array( DB_FIELD_TYPE=>DB_DEF_STRING, DB_FIELD_LEN=>200), 'body'=>array( DB_FIELD_TYPE=>DB_DEF_STRING, DB_FIELD_LEN=>100000), 'poster'=>array( DB_FIELD_TYPE=>DB_DEF_INTEGER), 'rank'=>array( DB_FIELD_TYPE=>DB_DEF_INTEGER), 'created_at'=>array( DB_FIELD_TYPE=>DB_DEF_TIMESTAMP), 'edited_at'=>array( DB_FIELD_TYPE=>DB_DEF_TIMESTAMP), 'location_data'=>array( DB_FIELD_TYPE=>DB_DEF_STRING, DB_FIELD_LEN=>250, DB_INDEX=>10) ); ?>
This stores and manages a title and body, along with the user who created it (poster)., the time it was created, and the time is was last updated (edited_at). It also allows for manual ordering (rank) which again, will automatially be handled by expRecord.
The different types of database fields (DB_FIELD_TYPE) are:
- Numeric ID - 'DB_DEF_ID'
- Text - 'DB_DEF_STRING'
- Integer - 'DB_DEF_INTEGER'
- Boolean - 'DB_DEF_BOOLEAN'
- Timestamp - 'DB_DEF_TIMESTAMP
- Decimal - 'DB_DEF_DECIMAL'
- Datetime - 'DB_DEF_DATETIME'
You may set a default field 'value' using 'DB_DEFAULT'
You may set an auto-increment field using 'DB_INCREMENT' which should be the case for the table identifier field 'id'.
You should set the primary key field using 'DB_PRIMARY' which in most cases is also the 'id' field. You may mark multiple fields as 'DB_PRIMARY' to create a composite primary key.
You may set up a field for indexing (which aids searches on that field ) using 'DB_INDEX'. You mark multiple fields for indexing, but they will not be composite indexes.
You may set the size of a text/string field using 'DB_FIELD_LEN' which defaults to 'text'