[Postfixadmin-devel] Database scheme and upgrade

[Christian B. <pos...@cb...>]

Hello,

short summary: The database scheme has some flaws currently.=20
Besides that, we need a method to do upgrades from 2.1 to SVN. See also
https://sourceforge.net/forum/forum.php?thread_id=3D1838084&forum_id=3D6760=
76


Long version:

Note that I'll mostly cover MySQL in this mail, because I don't know=20
much about PostgreSQL.

a) database scheme
~~~~~~~~~~~~~~~~~~

The vacation and vacation_notification tables have all fields set to=20
utf8 by default. This causes problems with index length etc. (one utf8=20
character needs 3 bytes!)
We should only make fields utf8 that may contain non-ascii data.
Especially "email" and "domain" should NOT be utf8.

OTOH, the other tables do not have any utf8 field. At least the=20
description field should be utf8.

=46or anyone not knowing the disadvantages of using utf8 unconditionally=20
(and everybody who wants to learn more on charset handling in MySQL in=20
general), here's an interesting article:
http://mysqldump.azundris.com/archives/60-Handling-character-sets.html=20


The "default '0000-00-00 00:00:00'" on datetime fields seems to cause=20
problems with MySQL strict mode. See this tracker entry:
https://sourceforge.net/tracker/index.php?func=3Ddetail&aid=3D1699218&group=
_id=3D191583&atid=3D937964

AFAIK it's possible to default to "now()" (needs testing on MySQL 4.x),=20
but we can also use real NULLs.


Using InnoDB seems to cause some technical problems, according to the=20
forum post:=20
https://sourceforge.net/forum/forum.php?thread_id=3D1838084&forum_id=3D6760=
76

The reasons why InnoDB is used in the vacation table currently are
=2D too long primary key (see utf8 issues above)
=2D DELETE CASCADE - could be replaced with an explicit DELETE command for=
=20
  vacation_notification


Another issue is that all tables have the mail address as primary key.=20
This is a) not recommended (I don't remember the details, sorry) and b)=20
makes it difficult to secure Postfixadmin because parameter checking is=20
difficult.

The better solution would be to add an "ID" column as auto_increment and=20
make the mail address an UNIQUE index.


b) database upgrade
~~~~~~~~~~~~~~~~~~~

In general: We should offer a way to upgrade a 2.1 database to SVN=20
version and any future release. Yes, this means not only 2.1 -> 2.2=20
updates, but each small change in SVN.

Possible method:
=2D revert all changes in DATABASE_MYSQL.TXT since 2.1 release
=2D instead, create ALTER TABLE statements for each change
=2D these ALTER TABLE statements should be executed by setup.php
  (it could also create the initial database, but that's another story)
=2D create an additional table that keeps the "database version" so that=20
  we always know which ALTER TABLE statements have to be executed.
=2D check the database version in login.php and print a warning if it's=20
  outdated

S9Y uses a similar method, and I never had problems with database=20
upgrades. See these files:
http://svn.berlios.de/wsvn/serendipity/trunk/include/admin/upgrader.inc.php=
?op=3Dfile&rev=3D0&sc=3D0
http://svn.berlios.de/wsvn/serendipity/trunk/include/functions_upgrader.inc=
=2Ephp?op=3Dfile&rev=3D0&sc=3D0


The Typo3 method would also be nice, but I'm afraid the initial=20
implementation needs too much work (we probably can't just use some=20
functions from the Typo3 framework, would be too easy ;-)

=46or those who don't know Typo3, I'll describe the used method:
=2D Typo3 ships with the latest sqldump
=2D the code compares the current database ("SHOW CREATE TABLE") with the=20
  sqldump and creates ALTER TABLE statements to update the database

If someone knows a good standalone tool (PHP or perl) that does this,=20
please point it out NOW.


c) general issues
~~~~~~~~~~~~~~~~~

The SQL dump should NOT
=2D GRANT anything (maybe the user uses another username etc) - it's easy=20
  to setup a new user with phpMyAdmin if someone is not familar with the=20
  mysql command line
=2D contain "USE postfix" - the database name can differ as well. Just=20
  remove this line and let the user specify the database name at the=20
  mysql commandline.
=2D contain any "DROP TABLE IF EXISTS" statements
=2D Even hardcoded table names might be a problem - at least we allow to=20
  change them in $CONF. However, I don't consider this one a real=20
  problem.


Regards,

Christian Boltz
=2D-=20
Tut mir leid. Tu hast dich daf=FCr entschieden,  Computer zu benutzen. Aus
irgendeinem Grunde glaubst du,  das sei risikofrei.  Ich versichere dir,
da=DF es das nicht ist. Computer sind b=F6se, rostige, alte Kettens=E4gen, =
die
grundlos anspringen.   [Ratti in suse-linux]