[ic] Managing Database Structure Changes

Stefan Hornburg (Racke) racke at linuxia.de
Thu May 28 08:22:06 UTC 2009


Hello, Interchange enthusiasts!

I'm currently in Ljubljana with Jure and we are thinking about new ways to
create ecommerce sites with Interchange.

We want introduce some new major Interchange features, like:

* plugins for features like content management, reviews, coupons, etc.
* building forms from the database and processing them through hooks
* convenient way to deal with templates and components (no [control])
* one user database for customers and shop owners, with simple permissions
* get rid of [process] tag
* and much more

This is currently implemented in the wellwell catalog, which is quite
experimental right now.

You find the GIT web for WellWell here:

http://git.icdevgroup.org/?p=wellwell.git

These new features require more structural information (content, 
permissions, forms, ...) to be kept in the SQL database.

This leads me into thinking about how to create and update tables
and columns in the database.

I looked into a few other Open Source systems and how they deal with
that:

* Request Tracker
* Sympa
* Drupal

They all basically executing SQL scripts to create or update the database.

This has IMHO the following disadvantages:

* you need to write and maintain the scripts for every SQL server
* updates rely on the exact structure
* no good place for additional information
* hard to create columns depending on some pre-conditions

The same applies to creating the actual data needed for standard
(login, checkout) and plugin (content editor, reviews) forms.

A better approach would be to keep the database definitions in XML, and
run a script to create the actual SQL statements:

<xml>
   <table name="products">
     <primary_key name="sku" />
     <column
         default=""
         length="32"
         name="sku"
         null=""
         type="varchar"
     />
     <column
         default=""
         length="255"
         name="name"
         null=""
         type="varchar"
     />
     <column
         default=""
         length="255"
         name="manufacturer"
         null="NULL"
         type="varchar"
     />
     <column
         default=""
         length=""
         name="description"
         null="NULL"
         type="text"
     />
   </table>
</xml>

I thought this can be whipped up in "no time" with XML::Twig and 
DBIx::DBSchema, but unfortunately the latter has serious issues
in this case (doesn't support multiple primary keys for instance).

I would like to see feedback on my idea, alternatives to DBIx::DBSchema
or whether it would be worthwile to hack on DBIx::DBSchema to
support the missing things.

Regards
             Racke

-- 
LinuXia Systems => http://www.linuxia.de/
Expert Interchange Consulting and System Administration
ICDEVGROUP => http://www.icdevgroup.org/
Interchange Development Team




More information about the interchange-users mailing list