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.
--
Greg Sabino Mullane greg(a)endpoint.com
End Point Corporation
PGP Key: 0x14964AC8