[mythtv] More ideas on PostgreSQL support, comments welcome
ijr at po.cwru.edu
Wed Sep 1 15:20:01 EDT 2004
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.
More information about the mythtv-dev