[development] Reconciling mysql & pgsql schemas

[Barry Jaspan]

> if I understand the patch right, things like the following are not
> actual mismatches, are they ?
> 
> * boxes
>   o column bid:
>     declared: array('type' => 'int', 'not null' => 1)
>     actual: array('type' => 'serial', 'not null' => 1)

Yes, they are.  The former is the result of

$schema['boxes']['fields']['bid'] = array('type' => 'int', ...);

and the latter is the result of

$schema['boxes']['fields']['bid'] = array('type' => 'serial', ...);

The latter translates to "int auto_increment" on mysql and "serial" on
pgsql.  The data type in the column is the same as if the type were
"int" but the behavior is different.

Most importantly, the code that manipulates the 'boxes' table either
treats the bid field as auto-incrementing or it does not, and the
field definition and the code should agree.  In this case, since the
system works with type 'int' on mysql, clearly the code is not
treating the field as auto-incrementing.

Actually, what is going on here is that pgsql's db_next_id() assumes
that a sequence object named <table>_<field>_seq exists.  So we aren't
using the auto-incrementing nature of the column but we are using the
fact that creating a serial column automatically creates a sequence
with a well-known name.

The easy solution to this particular class of mismatches is to declare
the columns to be type 'serial'.  'serial' then means both (a) the
column is auto-incrementing and (b) you are allowed to use
db_next_id() on it.  As per a previous message I posted here, though,
you must ALWAYS do one or the other, never both.

Barry