[mythtv] More ideas on PostgreSQL support, comments welcome

[Mike Benoit]

On Wed, 2004-09-01 at 12:13 -0400, Isaac Richards wrote:
> 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. =)
> 

Because XML Schema sits on top of ADODB it is forced to use ADODB's
metatypes (which have been around for years). Which when you look at the
documentation, they seem quite clean and intuitive to me:

*  C: Character fields that should be shown in a <input type="text">
tag.  
  * X: Clob (character large objects), or large text fields that should
    be shown in a <textarea> 
  * D: Date field 
  * T: Timestamp field 
  * L: Logical field (boolean or bit-field) 
  * N: Numeric field. Includes decimal, numeric, floating point, and
    real.  
  * I:  Integer field.  
  * R: Counter or Autoincrement field. Must be numeric. 
  * B: Blob, or binary large objects.

Keep in mind, the metatypes are designed to work with _any_ database.
Everything from Oracle to SQLLite.

As far as the lower/upper case is concerned, I don't believe its a
requirement at all, just a preference thing.

> > 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.

I suppose you could create your own file format that specifies both
MySQL and PGSQL specific queries in it for each version and have dbcheck
parse it. Would you prefer something like that, over multiple files?

My only intention in the above proposal was to separate the raw SQL from
any code, so it can easily be generated with simple scripts. 

I suppose you could just build on the current dbcheck with an extra if
statement inside the already existing if (dbver == X), like: 

if (dbtype == "pgsql") 

and specify the queries there. 

Seems like more work to me, but I'm just trying to figure out what hoops
(and how many) have to be jumped through for you to accept supporting
PGSQL.

> 
> > 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.
> 

I fail to see how writing these helper functions minimizes the amount of
work needed to make this happen. I don't see these helper functions
being trivial by any means.

I'm not sure how familiar you are with PostgreSQL, but for starters,
PGSQL can't change column types like MySQL can (mostly for good reason
too, because PGSQL values the data in those columns, where MySQL could
care less), so to work around this usually requires you rename the
current table, create a new table with the columns you want, then move
the data across with a SELECT INTO ... query, then delete the old table
of course. Luckily this can all be done in a single transaction. 

Modifying columns has similar problems, if your changing default values
for instance, the helper function is going to have to be smart enough to
know exactly which cases PGSQL actually allows you to do that in, and
handle them accordingly. 

These are probably the more rare cases, but if your gonna do it, you
gotta do it right, no? I'm sure I forgot a few other "gotchas" that
would have to be handled in these helper functions as well.

Sounds like you would basically be re-writing the PGSQL specific parts
of XML Schema functionality just for MythTV.

-- 
Mike Benoit <ipso at snappymail.ca>