Pluf migration changes

[Thomas Keller]

Hi!

Due to (the neverending) problems with Postgres in indefero (issue 800)
I made a couple of bigger changes to Pluf's migration code [0]. Here is
the commit message:

---

commit a45dc195a7f1cc6943734434bc13cdd466c96800
Author: Thomas Keller <m...@thomaskeller.biz>
Date: Tue May 15 20:23:39 2012 +0200

Pluf used to create foreign key relations as part of the table
definitions. This however made problems when two tables, A and B,
cross-referenced themselves via a foreign key relation, because strict
DBMS like PostgreSQL or MySQL couldn't create such a relation without
that both target tables existed prior. This patch changes this behaviour
by separating table creation and foreign key setup into two phases, that
have to be called after another, namely Pluf_DB_Schema::createTables()
and Pluf_DB_Schema::createConstraints(). For "reverse" migrations the
same logic is used, at first Pluf_DB_Schema::dropConstraints() has to be
called, then Pluf_DB_Schema::dropTables() can be executed.

While being at it, I fixed the following things:

- Association tables (*_assoc) where missing foreign key relations to
the base tables for PostgreSQL; this has been fixed
- PostgreSQL's (and MySQL's as well) limit for name identifiers are 64
characters; the schema code now ensures that we do not hit this
boundary for foreign keys
- MySQL uses InnoDB tables now by default and also sets up foreign key
relations
- no changes to SQLite, beside that constraints are still completly
ignored, due to the unability of SQLite to change table definitions
after a table has been created

All these changes are likely to break backwards compatibility, so if you
are already using Pluf for example to manage your database, you might be
surprised that some of your (old) tables are relation-less, while others
(new ones) come with relations. In cases like this it might be wise to
backup your database (migrate.php -b) and restore it from the dump,
because this re-creates the table structure including all the foreign
key relations. Note however that the data dump might have become
inconsistent, so the restore might not go through without that your DBMS
yells at you...

---

Please test this and give me feedback.

Thanks,
Thomas.

[0]
http://projects.ceondo.com/p/pluf/source/commit/a45dc195a7f1cc6943734434bc13cdd466c96800/

--
GPG-Key 0x160D1092 | tommy...@jabber.ccc.de | http://thomaskeller.biz
Please note that according to the EU law on data retention, information
on every electronic information exchange might be retained for a period
of six months or longer: http://www.vorratsdatenspeicherung.de/?lang=en