[mythtv] More ideas on PostgreSQL support, comments welcome

[David Härdeman]

On Wed, Sep 01, 2004 at 12:13:06PM -0400, Isaac Richards wrote:
>The current dbcheck/schema upgrade code is:
>- Very clean.
>- Very simple.
>
>And I rather like it.  I don't want to munge it up terribly much for something 
>that isn't completely necessary.

I beg to differ, I challenge anyone to tell me what the current DB 
schema of MythTV is without: 1) running MythTV 2) letting MythTV 
auto-create/alter/drop the tables, 3) checking the resulting tables in the 
database.

Right now, when initializing the DB for the first time, the code starts 
out with an old version (1003, added Tue Sep 30 18:19:00 2003) and then 
goes trough all intermediate versions up until the recent version (1054) 
via 51 minor or major alterations.

IMHO, it would have been better to include the incremental stuff but 
keep the InitializeDatabase up-to-date with the very latest schema 
(that can still be done of course, but merging the 51 intermediate 
changes is not a fun job). That way Initialization is done directly to 
current version (faster, less error-prone) and the current schema is 
easy to overview.

Now over to summing the different alternatives up and some of the 
opinions on them which I've seen:

1) Provide duplicate dbcheck.cpp for PostgreSQL

Pros:
o Familiar
o Provides programmatical changes between DB versions
o Simplest solution proposed so far

Cons:
o Code duplication
o Embeds even more SQL in the binary itself
o Two files which must be updated

2) Change dbcheck to run SQL queries from an external file which is 
appropriate (depending on the DB and MythTV module). Seek within that 
file to find appropriate SQL queries.

Pros:
o Moves several hundred lines of "code" out of mythtv binaries
o Consolidates db check methods
o Allow great deal of freedom in db-specific code

Cons:
o Generates more external files which the myth programs depend on
  (So? Try deleting /usr/share/mythtv/theme and see what happends)
o Two files to update rather than one
o Things which can't be expressed in SQL can't be done
  (This is actually the biggest drawback IMHO, see the update to version 
  1021 in dbcheck.cpp for examples)

3) Dynamic rewrites

Pros:
o Minimal changes to MySQL code
o One version of SQL statements
o Only one schema to update

Cons:
o Lots of initial effort
o Not sure if this can work in a sufficient amount of cases
o Can create subtle bugs if rewriting is 95% accurate
o Does not cover the case of programmatic changes

4) External db-agnostic methods (such as Hibernate)

Pros:
o Already written
o (Should have) solved most of the issues already

Cons:
o Add another dependency for something which is only done "once" (db 
init/upgrade) per myth upgrade
o Might also imply lots of code changes
o No programmatical changes
o Not sure how much flexibility has to be sacrificed for compability

I am currently voting for these in the order presented. If we go the 
route of a separate dbcheck for PostgreSQL, we can evaluate how well 
it's working in a confined space, and should (2) be regarded as a good 
alternative, we can implement it later as a decision which is not 
related to whether or not Myth supports one or several DB's.

Frankly, how large are most DB schema changes? Most of them in 
dbcheck.cpp are along the lines of adding a table, adding a column or 
changing a default. On the other hand, changing column names as some of 
the DB schema updates have done over the life of dbcheck.cpp, means that 
all references to that column must be changed throughout the code...

Well, enough talk, I don't want to come across as a zealot, this is your 
baby Isaac and I respect your choices and opinions, but it would be a 
shame to waste time on this for nothing.

Pending any more messages which address technical issues I'll shut up 
and go back to coding. Expect a patch in a week or two which is more 
full-fledged than what I have to offer right now and then we have some 
real code to base discussion on.

//David

PS,
On Anonymous developers impeding PostgreSQL integration:
On Wed, Sep 01, 2004 at 02:25:27AM -0400, Isaac Richards wrote:
>On Wednesday 01 September 2004 12:30 am, Mike Benoit wrote:
>> - You won't let anyone else take responsibility for looking after
>> alternate database code.
>
>If someone who had a history of contributing to this project wanted to take it 
>on, then that would be fine.  If it's someone who's never contributed before, 
>I can't know that they _will_ take responsibility for looking after it.

You never can. But if the code was integrated, the necessary changes 
would be a few lines of SQL code between versions. Any of the PostgreSQL 
interested people on the list could do it. And if noone does...big deal, 
drop the PostgreSQL code from Myth (which would 95% be in dbcheck).

You could even add warnings to that effect to the 
docs/output when Myth is run/whatever...after people complain that 
PostgreSQL is broken you can then reply "told ya so".

Besides, even "old time" developers loose interest, get caught up in 
real life, get tired, break a leg....etc. There are no guarantees.