[mythtv] More ideas on PostgreSQL support, comments welcome

[Mike Benoit]

Disclaimer: I'm not familiar with MythTV code at all, but I have
experience with projects that are mostly database independent. 

Dynamic query re-writing is just a bad idea, it shouldn't be difficult
to make the SQL queries portable. It may take some work initially if
MythTV has used a lot of non-standard MySQL'isms, but after that is
done, writing portable SQL queries isn't hard at all.

The hardest part about supporting multiple databases is almost always
schema creation/updating. Updating usually being the most difficult. A
possible solution is to use the XMLSchema (http://adodb-
xmlschema.sourceforge.net/docs/ ) library that comes with ADODB. 

Simply write your schema in XML once, and filter it through XMLSchema
and it will automatically write the SQL statements to create/upgrade the
currently in-use schema to match the XML schema file, regardless of the
database type. The drawback is ADODB is PHP, so PHP would be required to
do this. However it could be a part of the "build system" so you don't
have to make PHP a requirement for all installations. Each time the
schema changes, simply run a small script, and have it automatically
save the resulting SQL to a mysql or pgsql specific file. I'm sure it
could all be automated quite easily, with only the "build system"
requiring PHP.

It would be very nice to get my MythTV box off MySQL, table corruption
starts to get very annoying after a while.


On Tue, 2004-08-31 at 11:50 +0200, David Härdeman wrote:
> On Mon, Aug 30, 2004 at 05:49:23PM -0400, Isaac Richards wrote:
> >On Monday 30 August 2004 04:59 pm, David Härdeman wrote:
> >> >Sounds like a horrible idea to me.  The 'duplicate' implementation is a
> >> > few simple lines of code that doesn't need to be consolidated, and I will
> >> > _not_ update multiple files every schema change.
> >>
> >> Fine, so don't update the psql file, just update the mysql one and I'm
> >> sure that someone that cares for postgresql can update the corresponding
> >> pgsql soon thereafter. Worst case scenario is that PostgreSQL support is
> >> broken for a period of time. Would this be an acceptable compromise?
> >
> >No, I wouldn't really consider that's acceptable at all.  What happens when I 
> >want to make a release and the postgresql support isn't updated?
> >
> 
> The situation isn't very different from what the Linux kernel 
> maintainers experience. People add new drivers for hardware which the 
> maintainers don't have access to all the time. If changes are later made 
> to other parts which break that driver, people complains and the driver 
> is either updated by someone who does care about that piece of hardware 
> or the driver is ultimately removed.
> 
> >> >I will not add support for other databases if it makes the current
> >> > internal database usage any harder to use.  I see absolutely no benefit
> >> > in doing that.
> >>
> >> I fail to see how my suggestion would make internal database usage
> >> harder? Is updating a separate file with SQL queries harder than updating
> >> a .cpp file with SQL queries?
> >
> >It's not harder, but it's a large step backwards, IMO.  It's one more thing to 
> >keep track of, and one more thing that can possibly break.
> >
> >If multiple databases can't be supported without extra work for me down the 
> >road, then there won't be support for multiple databases.  I don't see the 
> >benefit in it.
> 
> Ok, so here's another suggestion more along the lines of what Roland 
> proposed in this thread (albeit one that is MySQL centric rather than 
> completely generic):
> 
> Create a lightweight DB wrapper that will accept MySQL queries and 
> either pass them directly to the database (in case it's a MySQL backend) 
> or do some dynamic rewriting and then pass it to the backend (if it's 
> anything but a MySQL backend).
> 
> Looking at the dbcheck code there aren't that many things which would 
> need to be rewritten, and considering that amount of SQL in there, the 
> wrapper should be in a pretty good shape once it manages to rewrite it 
> all into PostgreSQL acceptable code.
> 
> Examples of rewriting would be:
> remove any "TYPE=MyISAM" parameters
> change "SMALLINT AUTO_INCREMENT" to "SERIAL"
> remove "IF EXISTS / IF NOT EXISTS" or rewrite to something equivalent
> etc...
> 
> also some minor rewrites to MySQL SQL code (statements such as "INSERT 
> INTO recordingprofiles SET name" would need to be "INSERT INTO 
> recordingprofiles (name) VALUES") which shouldn't affect functionality 
> at all.
> 
> Does this sound like a better solution?
> 
> As another alternative, I could also write duplicate dbcheck 
> functionality but PostgreSQL centric (so mydbcheck and pgdbcheck would 
> both be provided) but I doubt that it would be percieved as more tolerable 
> than my first suggestion?
> 
> All suggestions would of course lead to some extra work (that's 
> unavoidable), but the goal would be to minimize the work and 
> interruption for the majority of users/developers (MySQL using ones that 
> is).
> 
> Regards,
> David
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
-- 
Mike Benoit <ipso at snappymail.ca>