Am_DbSync

Am_DbSync is another in-house developed solution for keeping MySQL database structure in sync.

Database structure is defined in multiple XML files - one per module, named db.xml (these files are merged before usage).

This is a piece of a db.xml file:

<?xml version="1.0"?>
<schema version="4.0.0" id="$Id$" >

  <table name="access">
    <field name="access_id" type="int" notnull="1" extra="auto_increment"/>
    <field name="invoice_id" type="int" notnull="1"/>
    <field name="invoice_payment_id" type="int"/>
    <field name="user_id" type="int" notnull="1"/>
    <field name="product_id" type="int" notnull="1"/>
    <field name="transaction_id" type="varchar" len="64"/>
    <field name="begin_date" type="date" notnull="1"/>
    <field name="expire_date" type="date" notnull="1"/>
    <index name="PRIMARY" unique="1">
      <field name="access_id"/>
    </index>
    <index name="user_id">
      <field name="user_id"/>
    </index>
    <index name="invoice_id">
      <field name="invoice_id"/>
    </index>
  </table>
  .... other tables ...
</schema>

Structure of the file is self-explanatory for anyone who dealt with MySQL tables. When you run http://example.com/am/admin-upgrade-db , DbSync takes db.xml files from all enabled modules, and checks if tables in database needs any changes. If changes are necessary, it is applied.

However there are a few important notes:

• If fields or indexes, or tables, are not specified in db.xml files it will not be deleted from database. There is a special syntax to remove fields if that is necessary.
• Although adding fields to existing table is possible from another module, this is not recommended. It is better to create another table related 1:1 with existing, or use data() records.