[mythtv] More ideas on PostgreSQL support, comments welcome

[Mike Benoit]

On Wed, 2004-09-01 at 02:25 -0400, Isaac Richards wrote:
> >
> > So, to summarize this thread so far:
> >
> > - You don't want to have to write duplicate SQL queries, even if its
> > just for creating/modifying the schema
> 
> Right.  That would be quite broken.
> 
> > - You won't let anyone else take responsibility for looking after
> > alternate database code.
> 
> If someone who had a history of contributing to this project wanted to take it 
> on, then that would be fine.  If it's someone who's never contributed before, 
> I can't know that they _will_ take responsibility for looking after it.
> 
> In case you hadn't noticed, the DB is an integral part of using Myth - if 
> that's broken, then _everything_ is broken.  I simply can't have a user 
> decide to use Postgres, then get screwed over because I put out a new version 
> and no-one's bothered to update the postgres support.
> 
> > - You don't want supporting more then one database to be any extra work
> > whatsoever.
> 
> You think it's fair to make me commit to doing extra work every time someone 
> wants to change the db schema for a feature I have absolutely no use for?
> 
> > - You don't want to use any _already existing_ tools (because they do
> > exist) that might help make the process less work. Because you don't
> > want to add dependencies, even if the tool only needs to be run ONCE per
> > schema change, and have the resulting SQL output saved which could then
> > be run on users machines. Thereby NOT requiring additional dependencies
> > for users.
> 
> It's an additional dependency for anyone wanting to code, no? It's an 
> additional thing that can break.  I already said that I don't want a 
> separately distributed file for schema creation, did you not see that?
> 
> However, I may consider this, if an appropriate tool is available.  That 
> adodb-xmlschema thing looks to be utterly unuseable, though - the XML format 
> is horrible, and the upgrade procedure looks nasty.
> 
> > Short of re-inventing the wheel (writing a MythTV specific library that
> > magically converts MySQL table schema's to PostgreSQL), I get the
> > impression your not interested in even entertaining the option of
> > supporting more then just MySQL?
> 
> Sure I am, as long as it's done without making my work any more difficult.  If 
> it can't be, then I don't see the use for it.  I thought I've made this 
> clear.
> 

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. 

Here is a snippet from my projects PHP upgrade script:
-----------
$db->Connect($db_host, $db_user, $db_password, $db_name);
$schema = new adoSchema($db);
$schema->ParseSchema('schema.xml');

//Prints the raw SQL about to be executed. This is where you would save
//the SQL to a file specific to the database.
print $schema->getSQL('html'); 

// Execute the SQL on the database
$result = $schema->ExecuteSchema();
-----------

This takes care of all table creation (if they don't exist) and
detecting what table modifications need to be made, including populating
any default values that may be needed.

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.

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.

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.


Here is a snippet of my XML schema file, including index creation, just
for kicks:

       <table name="aro">
                <field name="id" type="I">
                        <DEFAULT value="0"/>
                        <NOTNULL/>
                        <KEY/>
                </field>
                <field name="section_value" type="C" size="240">
                        <DEFAULT value="0"/>
                        <NOTNULL/>
                </field>
                <field name="value" type="C" size="240">
                        <NOTNULL/>
                </field>
                <field name="order_value" type="I">
                        <DEFAULT value="0"/>
                        <NOTNULL/>
                </field>
                <field name="name" type="C" size="255">
                        <NOTNULL/>
                </field>
                <field name="hidden" type="I">
                        <DEFAULT value="0"/>
                        <NOTNULL/>
                </field>

                <index name="section_value_value_aro">
                        <col>section_value</col>
                        <col>value</col>
                        <UNIQUE/>
                </index>
                <index name="hidden_aro">
                        <col>hidden</col>
                </index>
        </table>


-- 
Mike Benoit <ipso at snappymail.ca>