Schema API

The old
Current system introduced in 0.9: Plugin schema changes

Problems

 * have to duplicate specifications between several ways:
 * ColumnDef arrays for creating/update schema
 * column type bitfields for DB_DataObject
 * sequence key for DB_DataObject
 * keys list for DB_DataObject
 * keys list for Memcache_DataObject
 * for core tables, also .sql files for MySQL and Postgres
 * no way to specify compound indexes other than primary keys

Drupal-style
http://api.drupal.org/api/group/schemaapi/7

Good: Need to add:
 * explicit syntax for multi-column keys
 * some canonical types to map from
 * explicit syntax for specifying foreign keys
 * gives enough info to generate tables and all the DB_DataObject metadata
 * proven support for mysql, postgres, sqlite, and more.
 * enum syntax
 * possibly fiddle with timestamp vs datetime issues...
 * auto-updating timestamps are used a lot
 * couple of freak things where funny-size / ascii stuff is specified?
 * foreign keys
 * booleans

Example
ostatus_profile:

static function schemaDef {       return array(            'fields' => array( 'uri' => array('type' => 'varchar', 'length' => 255, 'not null' => true), 'profile_id' => array('type' => 'integer'), 'group_id' => array('type' => 'integer'), 'feeduri' => array('type' => 'varchar', 'length' => 255), 'salmonuri' => array('type' => 'varchar', 'length' => 255), 'avatar' => array('type' => 'text'), 'created' => array('type' => 'datetime', 'not null' => true), 'modified' => array('type' => 'datetime', 'not null' => true), ),           'primary key' => array('uri'),            'unique keys' => array( 'ostatus_profile_profile_id_idx' => array('profile_id'), 'ostatus_profile_group_id_idx' => array('group_id'), 'ostatus_profile_feeduri_idx' => array('feeduri'), ),           'foreign keys' => array( // Ok think these examples are correct now: 'ostatus_profile_profile_id_fkey' => array('profile', array('profile_id' => 'id')), 'ostatus_profile_group_id_fkey' => array('user_group', array('group_id' => 'id')), ),       );    }

Work in progress

 * 'schema-x' branch on brion-fixes

What's implemented:
 * preliminary table defs for core
 * dumpschema.php test script to check live schemas
 * ./scripts/schemacheck.php notice # detect live structure of notice table and dump it
 * ./scripts/schemacheck.php --diff notice # detect live structure of notice table and compare it against our core definition
 * ./scripts/schemacheck.php --all # detect live structure of all core tables and dump them
 * ./scripts/schemacheck.php --all -diff # detect live structure of all core tables and compare them against our core definitions
 * schema.php mysqlschema.php pgsqlschema.php
 * Still partially in conversion from the old system
 * Basic detection of MySQL and PostgreSQL table info
 * not all type conversions are correct yet
 * primary, multi-value, and foreign key info not yet retrieved for PG (done 2010-10-11)
 * field comments are not stored or retrieved on postgresql
 * foreign key info not yet retrieved for MySQL (though these are not currently used live, our table defs do list some)
 * auto-update timestamps, some other bits still need to be worked out
 * Auto-conversion of old-style table defs for plugins
 * needs further testing to make sure this actually works
 * Table creation and update logic is incomplete, but table updating should theoretically work on PG once done (old code was unable to update individual fields on PG)
 * 'Update buddy' script to run the updater code but producing an .sql file instead of live changes; this can be helpful at large-scale situations where some sites may need manual running of some code.

Still to do:
 * finish all type conversions/definitions
 * allow for 'enum' types
 * allow consistent way to specify auto-update timestamps
 * ensure primary keys & autoupdates are consistently set
 * see if we can get prefix lengths on prefix indices (eg first 255 bytes of a blob field)
 * do a supported-feature filter/strip step before schema comparison
 * patch up installer/updater to use the schema functions for core tables

Higher-level work:
 * hash the schema layouts and keep a list of last-known-state for each table
 * then checkschema events can be much cheaper in the common case; instead of examining the live tables we can just pull a list of hashes out of a single table. If any don't match or are missing, we can run the full schema comparisons and udpate those tables.
 * make sure checkschema functions currectly in background threads, when switching sites
 * we want cheap checkschemas to be able to run on-demand in queue threads to aid in maintenance.
 * add hook points to provide for specific data-conversion steps after certain actions?
 * eg create this field: then fill it out with this data?