Mark A. Hershberger wrote:
How can we improve the support for databases like PostgreSQL, Oracle, DB2 and MS SQL?
Getting Jenkins involved in testing isn't the (only) answer, though it would certainly help.
If developers who were interested in those databases could watch includes/db, that would help, as well.
I've tackled this problem, and will share my experience. The problem being how to keep all the schemas in sync. I looked at the existing solution someone else started, but found it pretty rough. My idea (I called it "abstract schema") was a central SQL file, which used a fairly straightforward SQL-ish syntax, and then a parser that could read that file and create versions for MySQL, Postgres, etc. Or just load that information into memory for the install and upgrade process. I actually had a working prototype for that, which worked quite well. It made the upgrades in particular very smooth, as there were no more patch files needed, the installer simply read the current canonical schema state and made the necessary changes.
The big, showstopping problem was trying to map the existing tables.sql file (the main MySQL one) into the new system. There is no straightforward mapping possible, as every single table in the system I had to try and figure out why column such-and-such was using this type, and why sometimes there was a default and other times not for similar cases, etc. It was quite the nightmare, so that's why I eventually abandoned the work. The tables.sql file is quite obviously organically grown, and has little rhyme or reason. I did come up with some basic naming rules (esp. for indexes), which means that the first release with the new system will rename a lot of objects, but as they are not referenced directly, that should not be a problem.
I'm happy to dig out my notes if anyone wants some examples of the type mapping issues. I think an abstract tables.sql is a good general approach, but getting from here to there is going to require a lot of work slogging through those data types.