[mythtv] More ideas on PostgreSQL support, comments welcome

[David Härdeman]

Hi,

after the initial support patch which I've sent to the list I've now 
been looking at the code which creates/updates the tables in the 
mythtv module (libs/libsmythtv/dbcheck.*).

Of course, I could add PostgreSQL equivalents of everything in there, 
but after looking at the code I've got another suggestion:

Right now there are duplicate implementations of dbcheck in several of 
the modules and the SQL code is included inline in the libraries. My 
suggestion would be to create one dbcheck implementation in libmyth and 
let all the modules call that implementation.

The generic dbcheck would be passed the name of the module which to 
update for, check the current version of the relevant tables and then 
consult an external file with sql statements to see which (if any) 
updates were necessary.

An example file could look something like this:
# INIT
CREATE TABLE dbtest (
	id INT,
	name VARCHAR(32),
	description VARCHAR(32)
);
# BEGIN 1001
ALTER TABLE dbtest ADD COLUMN accessed TIMESTAMP;
# BEGIN 1002
ALTER TABLE dbtest ADD COLUMN created TIMESTAMP;
# BEGIN 1003

etc...

So if the tables weren't there at all the file would just need to be 
played back from the beginning while an update would be performed by 
first seeking to the appropriate version entry and play the file from 
there.

It would then be easy to create one sql file per db-type per myth module 
and to install them somewhere like /usr/share/mythtv/dbschemas/ which 
would create a structure like:

/usr/share/mythtv/dbschemas$ ls -1
mythtv-mysql.sql
mythtv-pgsql.sql
mythvideo-mysql.sql
mythvideo-pgsql.sql
etc...

Does this sound like a good idea? I wouldn't want to start coding on an 
implementation just to have it rejected which is why I'm presenting this 
idea to the mailing list for review first.

Regards,
David Härdeman
david at 2gen.com