[development] Reconciling mysql & pgsql schemas

[Barry Jaspan]

The Schema core patch (http://drupal.org/node/136171) to provide a
base level of database schema abstraction is coming along nicely.
This will include defining a table definition structured array so that
manual CREATE TABLE statements will be history as well as the suite of
db_{add,remove}_{field,key,index} so that ALTER TABLE statements will
also be history.  Existing hook_update_n() functions and the entire
existing "schema version" system will be unaffected.

One issue that has come to light is that our existing mysql and pgsql
database schemas, as represented in the CREATE TABLE statements, are
different.  The differences are pretty minor: signed vs unsigned,
default vs no default, int vs smallint.  However, since the goal is to
create a single data structure representing each module's tables, I
think we should resolve the differences.

I've listed all of the discrepancies below.  This list was derived by:

1.  Installing HEAD on mysql (with current CREATE TABLE statements)
    with all optional modules enabled.

2.  Automatically generating the database schema structure by
    inspecting the mysql database and SAVING it in each module's
    hook_schema.

3.  Installing HEAD on pgsql (with current CREATE TABLE statements)
    with all optional modules enabled.

4.  Automatically generating the database schema structure by
    inspecting the pgsql database and COMPARING it (NOT saving it)
    against each module's hook_schema.

As an example, the first mismatched table is aggregator_feed.  The
comparison report translates as: "Table aggregator_feed has a column
named 'description'.  hook_schema for this table says the column has
no default value but the actual database column has a default value of
''."  Sure enough, if you look at the mysql and pgsql CREATE TABLE
statements, you will see that mysql's (which is what was used to
create the "declared" structure) has no default while pgsql's (which
is what was used to create the "actual" structure) has one.

Fixing this is a two-step process.  First, we must decide which schema
(mysql's or pgsql's) is right in each case.  Then, either:

1.  write a system_update function to modify each mismatching table
    into conformance, or

2.  put db-specific values into the schema data structure, like this:

        $schema['aggregator_feed']['fields']['description'] = array(
          'type' => 'text', 'not null' => 1, 'pgsql_default' => '');

    Note the 'pgsql_default' attribute.

We can also use a combination of 1 and 2 if necessary.  I propose
using 1 whenever possible and 2 only if/when necessary.

Comments?  The comparison report (compliments of schema.module) follows.

Thanks,

Barry

Tables for which the schema and database are different. 

aggregator

* aggregator_feed
  o column description:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')
  o column image:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')
  o unique keys link: missing in database
  o unique keys url: unexpected (not an error)

block

* blocks
  o column pages:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')
* boxes
  o column bid:
    declared: array('type' => 'int', 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o column bid:
    declared: array('type' => 'int', 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o column format:
    declared: array('type' => 'int', 'not null' => 1, 'default' => 0)
    actual: array('type' => 'int', 'size' => 'small', 'not null' => 1, 'default' => 0)

comment

* comments
  o column format:
    declared: array('type' => 'int', 'not null' => 1, 'default' => 0)
    actual: array('type' => 'int', 'size' => 'small', 'not null' => 1, 'default' => 0)
  o indexes lid: missing in database
  o indexes nid: unexpected (not an error)
* node_comment_statistics
  o column nid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)

contact

* contact
  o column cid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o column recipients:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')
  o column reply:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')

dblog

* watchdog
  o column location:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')

drupal

* client
  o column cid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)

locale

* locales_source
  o column source:
    declared: array('type' => 'blob', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1)
  o column source:
    declared: array('type' => 'blob', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1)
  o indexes source: unexpected (not an error)
* locales_target
  o column translation:
    declared: array('type' => 'blob', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1)
  o column translation:
    declared: array('type' => 'blob', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1)
  o indexes lang: missing in database
  o indexes language: unexpected (not an error)

node

* node
  o column nid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o indexes node_title_type:
    declared: array('title', 'type')
    actual: array('title')
  o indexes node_type: missing in database
* node_revisions
  o column vid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o column vid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o column vid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o column body:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')
  o column teaser:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')
  o column log:
    declared: array('type' => 'text', 'not null' => 1)
    actual: array('type' => 'text', 'not null' => 1, 'default' => '')

poll

* poll_choices
  o column chid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)

search

* search_index
  o column score:
    declared: array('type' => 'float', 'not null' => )
    actual: array('type' => 'float', 'size' => 'big', 'not null' => )
* search_total
  o column count:
    declared: array('type' => 'float', 'not null' => )
    actual: array('type' => 'float', 'size' => 'big', 'not null' => )

system

* authmap
  o column aid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
* cache
  o column serialized:
    declared: array('type' => 'int', 'not null' => 1, 'default' => 0)
    actual: array('type' => 'int', 'size' => 'small', 'not null' => 1, 'default' => 0)
* cache_filter
  o column serialized:
    declared: array('type' => 'int', 'not null' => 1, 'default' => 0)
    actual: array('type' => 'int', 'size' => 'small', 'not null' => 1, 'default' => 0)
* cache_page
  o column serialized:
    declared: array('type' => 'int', 'not null' => 1, 'default' => 0)
    actual: array('type' => 'int', 'size' => 'small', 'not null' => 1, 'default' => 0)
* files
  o column fid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)
  o column fid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)
  o column fid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)
  o column fid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)
* menu
  o indexes pid:
    declared: array('pid')
    actual: array('parent')
* url_alias
  o column pid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
  o unique keys dst_language: missing in database

taxonomy

* term_data
  o column tid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
* term_node
  o primary key:
    declared: array('vid', 'tid', 'nid')
    actual: array('tid', 'nid', 'vid')
* term_synonym
  o indexes name: missing in database
* vocabulary
  o column vid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)

user

* role
  o column rid:
    declared: array('type' => 'serial', 'unsigned' => 1, 'not null' => 1)
    actual: array('type' => 'serial', 'not null' => 1)
* users
  o column uid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)
  o column uid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)
  o column uid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)
  o column uid:
    declared: array('type' => 'int', 'unsigned' => 1, 'not null' => 1, 'default' => 0)
    actual: array('type' => 'serial', 'not null' => 1)