[mythtv] More ideas on PostgreSQL support, comments welcome

[David Härdeman]

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