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.
On Mon, Feb 25, 2013 at 1:29 PM, Greg Sabino Mullane greg@endpoint.com wrote:
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.
This was exactly what we tried with the abstract-schema branch, before it was abandoned. The place we got stuck on was handling updates.
-Chad
Where is the abstract-schema branch now? The only thing I remember about that was a brief mailing list discussion (I think started by Daniel Friesnen) about making such a format. What exactly were the hold-ups with updates?
*--* *Tyler Romeo* Stevens Institute of Technology, Class of 2015 Major in Computer Science www.whizkidztech.com | tylerromeo@gmail.com
On Mon, Feb 25, 2013 at 4:35 PM, Chad innocentkiller@gmail.com wrote:
On Mon, Feb 25, 2013 at 1:29 PM, Greg Sabino Mullane greg@endpoint.com wrote:
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.
This was exactly what we tried with the abstract-schema branch, before it was abandoned. The place we got stuck on was handling updates.
-Chad
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
On Mon, Feb 25, 2013 at 2:00 PM, Tyler Romeo tylerromeo@gmail.com wrote:
Where is the abstract-schema branch now? The only thing I remember about that was a brief mailing list discussion (I think started by Daniel Friesnen) about making such a format. What exactly were the hold-ups with updates?
As I said earlier in the thread, it's still in SVN. I didn't bother migrating it because nobody was caring at the time. If anyone's wanting to dust it off and make a new branch for core, we can do that.
-Chad
Oh. Sorry must have missed that. I'll take a look and see if there's anything interesting. On Feb 25, 2013 5:05 PM, "Chad" innocentkiller@gmail.com wrote:
On Mon, Feb 25, 2013 at 2:00 PM, Tyler Romeo tylerromeo@gmail.com wrote:
Where is the abstract-schema branch now? The only thing I remember about that was a brief mailing list discussion (I think started by Daniel Friesnen) about making such a format. What exactly were the hold-ups with updates?
As I said earlier in the thread, it's still in SVN. I didn't bother migrating it because nobody was caring at the time. If anyone's wanting to dust it off and make a new branch for core, we can do that.
-Chad
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
For reference this is the RFC that was discussed in my thread on the subject.
https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitio...
Should probably dig up some gmane/archive links for both this and that discussion and add them to the RFC page.
wikitech-l@lists.wikimedia.org