[mythtv] More ideas on PostgreSQL support, comments welcome

[Isaac Richards]

On Wednesday 01 September 2004 03:18 am, Mike Benoit wrote:
> I personally agree with all your points except your opinion on
> XMLSchema. The last thing I want is to cause you a bunch more work,
> because I'm sure your time is better spent improving MythTV in other
> directions.
>
> I've used XMLSchema with great success for the last year or so on my own
> open source project, with a fairly complex schema at that. I'm not sure
> what you dislike about the XML file, it seems about as clean as XML
> gets. Beyond that, the upgrade procedure is dead simple.

The 'type' and 'size' attributes could have been made much, much cleaner.  
Don't they teach people to use descriptive identifiers these days?  As they 
are now, it's completely non-obvious what types things are.  The mixing of 
lowercase and all caps names is just all wrong, too. =)

> With my very limited knowledge of MythTV's schema upgrade procedure, the
> way I see making use of this tool would be:
>
> 1. Developer modifies the XML schema file.
> 2. He then runs a script (ie: generate_sql.php) similar to the above,
> which outputs the raw SQL to mysql/<schema_version>.sql or
> pgsql/<schema_version>.sql.
> 3. dbcheck(?) is modified to read these .sql files in version order and
> execute the raw SQL specific to the database currently in use on a users
> machine.

I certainly don't want multiple secondary files for each schema change.

> Unless a similar tool for C exists, I personally don't see any other
> efficient way of doing this. You mentioned using simple text flags in an
> earlier email (ie, %AUTOINCREMENT%), which would work mostly, but when
> it comes to any schema modifications, simple text flags just will not
> work unfortunately. PGSQL's alter table functionality is not quite as
> flexible as MySQL's just yet, and some things may take several queries
> in PGSQL, where MySQL just takes one.

Then a few helper functions (create a table, drop a table, add a field, modify 
a field) instead of simple text substitution should suffice.  That would even 
be nicer/cleaner than the current raw SQL in dbcheck.cpp, if done right.

> However XMLSchema was basically designed for this exact case, with the
> added benefit of working on more then just MySQL and PGSQL if the need
> ever arises.

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.

Isaac