MySQL

I have loads of ideas for implementation as LAMP applications. While I'm a pretty quick coder, it is quite disheartening (not to mention time consuming) to write basic database structures, data handling code, class wrappers for tables etc. The last three turns around I have almost decided to write a code generate to spit out the necessary. The idea is that you'd model your data structures in an XML file, and then you'd have some code which would generate the necessary MySQL code to create the database, as well as generating the necessary PHP classes to deal with the data - maybe even generate a rough-and-ready user interface.

Well, I've got fed up with wasting time writing boring code. Code generators can be a wee bit tricksy, of course, but I reckon the productivity gains will justify the time investment - especially as I have a number of cool applications which I want to implement at the moment! I've started work on it, and plan to complete it in the following stages:

1. Process model, validate. Also generate MySQL code to create the database. (DONE - it was a bank holiday weekend)
2. Generate PHP classes to handle the data model. Produce a decent front-end for the code generator. Document the model format and use of the system.
3. Generate some UI bits as well - forms, grids and so forth.
4. Iterate a model - given previous model and new model, generate ALTER TABLE statements for MySQL, or else data migration scripts or something.

Once it's further along (no earlier than after completion of step 2) and a bit more robust, I may open-source it. There seem to be a few similar ideas floating about out there, but none really suited the way I want to work, so maybe others could benefit from my work...

This weekend I setup SugarCRM on my server. Impressive stuff, but to get it going, I had to upgrade to MySQL 4.1. Unfortunately this broke Drupal - I got loads of warnings about "illegal mix of collations". My system is Debian stable, Drupal 4.7, and had MySQL 4.0 which upgraded to 4.1.

I found the answer here . The long and short of it is I had to do:

alter database drupal default CHARACTER SET utf8 COLLATE utf8_general_ci;
use drupal ;
ALTER TABLE access CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

for each Drupal database. I suppose the "ALTER TABLE" should probably be scripted for every Drupal table, but just the above seemed to do the trick.