[mythtv] More ideas on PostgreSQL support, comments welcome

[Isaac Richards]

On Wednesday 01 September 2004 02:55 pm, Mike Benoit wrote:
> > > 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 can't see how they'd be anything _but_ trivial.

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

Why's that non-trivial?  Sounds fairly straightforward to me.

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

As far as I can tell, there isn't support in axmls for updating PGSQL tables - 
it emits an ALTER TABLE (by calling ChangeTableSQL in adodb).

Remember, there's no need for it to be able to handle _everything_.  Just 
what's used in Myth.

Isaac