I've always wanted to experiment with writing an Advogato diary but never really had the time -- besides, who is interested in my boring drivel... Still, I figure if I constantly write in this thing, perhaps some of you folks will look at the projects I'm working on -- so, if you haven't already go, go, look!
Lately I've been working on two projects, phpaga and mod_haydn. But this entry is really about the problems related to phpaga.
phpaga was written with the MySQL data container. MySQL is an excellent system when you need to insert data quickly and retrieve data quickly, but when it comes to the needs normally associated with a database, it is woefully incompetent.
For example views, read only containers, which provide a "view" of data across multiple tables in one psuedo-table don't exist, sure, I could wait till MySQL 5.0 for SQL '88 compliance, but why should I? The problem doesn't start there - you want Foreign keys, Triggers, Stored Procedures, Blobs (no, not TEXT field aliases)? Good luck! MySQL doesn't even support Sequences (no, Auto Increment is not a sequence.)
Why am I whining like a troll? Two reasons:
1) We can't drop MySQL support for a real database, so many people these days seem to have only MySQL installed, so alienating them by dropping MySQL support is shooting yourself in the foot - akin to writing GUI applications without support for 16 bit OS' like Win32.
Our current solution for handling views is simply to use temporary tables (only when MySQL is used). For sequences we have to create index tables, incremented along with the insertions (this is PEAR DB's solution to the problem).
This is ugly and its a pain in the ass to maintain. Ohh well, at least MySQL is free software, that makes me feel so much better.
2) The folks at MySQL AB would have you believe that MySQL is a real database. Calling MySQL a RDBM is like calling Oracle a flat file: its true on one-level, false on every-other level.
If I wasn't dependent on it, I might actually find it humorous - "MySQL has row level locking!", "MySQL has transactions!" - so what? - there is no database worth mentioning that doesn't have these features.
This isn't to say that MySQL isn't a great tool, its wonderful for specialized/small tasks, its also a very easy entry point for users who don't know about databases or SQL. But when it comes to being "Enterprise Ready" or "Industrial Strength," please, be reasonable, Microsoft Access is more "Enterprise Ready" (it does support FOREIGN KEY constraints.)
MySQL is fine when you don't need a database and don't want a text file.