Introduction

The following is a list of examples of diffxml2ddl's output. The XML on the left is the database schema before hand, and the XML on the right is what we want to get to. Underneath them is the list of SQL statements required to get from the Before to the After for each type of DBMS supported.
The output comes directly from the test files which are used to unit test xml2ddl.

Index

DescriptionPostgresPostgres7OracleMysqlMysql4Firebird
Create table of various types yesyesyesyesyesyes
Test names that need to be escaped yesyesyesyesyesyes
Drop Table yesyesyesyesyesyes
Change a column to a larger size yesyesyesyesyesyes
Change a column to a smaller size yesyesyesyesyesyes
Add a default and change another default yesyesyesyesyesno
Rename a column yesyesyesyesyesyes
Rename and change column type at the same time yesyesyesyesyesno
Change a column type and a default at the same time yesyesyesyesyesno
Drop a column yesyesyesyesyesyes
Drop an end column yesyesyesyesyesyes
Rename a table yesyesyesyesyesno
Change a table description yesyesyesnonoyes
Change a column description yesyesyesyesyesyes
Create two indexes yesyesyesyesyesyes
Drop two indexes yesyesyesyesyesyes
Rename two indexes, with index names yesyesyesyesyesyes
Rename two indexes, no index name given originally yesyesyesyesyesyes
Change two indexes with index names given yesyesyesyesyesyes
Change two indexes, no index name given originally yesyesyesyesyesyes
Create a relation yesyesyesyesyesno
Drop a relation yesyesyesyesyesno
Create a relation with 'on delete' yesyesyesyesyesno
Change a relation yesyesyesnonono
Add an auto increment yesyesnoyesyesyes
Drop an autoincrement yesyesnoyesnono
Drop a complex table yesyesnoyesyesyes
Create a view yesyesyesyesnoyes
Drop a table with a related view yesyesyesyesnoyes
Drop a view yesyesyesyesnoyes
Rename a view yesyesyesyesnoyes
Create a function yesyesyesyesyesyes
Drop a function yesyesyesyesyesyes
Change a function yesyesyesyesyesyes
Rename a function yesyesyesyesyesyes
Test a naming convention with abbreviations yesyesyesyesyesyes
1 Create table of various types
..\tests\testfiles\test-01.xml
<schema>
</schema>
BeforeAfter
<schema>
    <table name="table1">
        <columns>
            <column name="col1" type="integer"/>
            <column name="col2" size="32" type="varchar"/>
            <column name="col3" size="15" type="decimal"/>
            <column name="col4" precision="3" size="15" type="decimal"/>
            <column name="col5" type="date"/>
            <column default="2" name="col6" type="int"/>
            <column default="1.234" name="col7" type="float"/>
        </columns>
    </table>
</schema>
DBMS: postgres, postgres7, oracle, firebird
CREATE TABLE table1 (
    col1 integer,
    col2 varchar(32),
    col3 decimal(15),
    col4 decimal(15, 3),
    col5 date,
    col6 int DEFAULT 2,
    col7 float DEFAULT 1.234
);
DBMS: mysql,mysql4
CREATE TABLE table1 (
    col1 integer,
    col2 varchar(32),
    col3 decimal(15),
    col4 decimal(15, 3),
    col5 date,
    col6 int DEFAULT 2,
    col7 float DEFAULT 1.234
) TYPE=InnoDB;
2 Test names that need to be escaped
..\tests\testfiles\test-01a.xml
<schema>
</schema>
BeforeAfter
<schema>
    <table name="Table with spaces">
        <columns>
            <column name="column with spaces" type="int"/>
            <column name="select" type="int"/>
        </columns>
        <indexes>
            <index columns="column with spaces,select" name="from"/>
        </indexes>
    </table>
</schema>
DBMS: postgres, postgres7, oracle, firebird
CREATE TABLE "Table with spaces" (
        "column with spaces" int,
        "select" int
    );
CREATE INDEX "from" ON "Table with spaces" ("column with spaces", "select");
DBMS: mysql,mysql4
CREATE TABLE `Table with spaces` (
        `column with spaces` int,
        `select` int
    ) TYPE=InnoDB;
CREATE INDEX `from` ON `Table with spaces` (`column with spaces`, `select`);
3 Drop Table
..\tests\testfiles\test-02.xml
<schema>
    <dictionary name="column">
        <dict class="mytype" type="integer"/>
    </dictionary>
    <table name="table1">
        <columns>
            <column class="mytype" name="col1"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
BeforeAfter
<schema>
</schema>
DBMS: postgres, postgres7, oracle, mysql, mysql4, firebird
DROP TABLE table1;
4 Change a column to a larger size
..\tests\testfiles\test-05.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" size="10" type="decimal"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, firebird
ALTER TABLE table1 ALTER col1 TYPE decimal(10);
DBMS: postgres7
ALTER TABLE table1 ADD tmp_col1 decimal(10);
UPDATE table1 SET tmp_col1 = col1;
ALTER TABLE table1 DROP col1;
ALTER TABLE table1 RENAME tmp_col1 TO col1;
DBMS: mysql,mysql4,oracle
ALTER TABLE table1 MODIFY col1 decimal(10);
5 Change a column to a smaller size
..\tests\testfiles\test-06.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" size="15" type="decimal"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" size="10" type="decimal"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, firebird
ALTER TABLE table1 ALTER col1 TYPE decimal(10);
DBMS: postgres7
ALTER TABLE table1 ADD tmp_col1 decimal(10);
UPDATE table1 SET tmp_col1 = col1;
ALTER TABLE table1 DROP col1;
ALTER TABLE table1 RENAME tmp_col1 TO col1;
DBMS: mysql,mysql4,oracle
ALTER TABLE table1 MODIFY col1 decimal(10);
6 Add a default and change another default
..\tests\testfiles\test-06a.xml
<schema>
<table name="table1">
    <columns>
        <column default="0" name="col1" size="15" type="decimal"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column default="2" name="col1" size="15" type="decimal"/>
        <column default="3" name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7, mysql, mysql4
ALTER TABLE table1 ALTER col1 SET DEFAULT 2;
ALTER TABLE table1 ALTER col2 SET DEFAULT 3;
DBMS: oracle
ALTER TABLE table1 MODIFY col1 decimal(15) DEFAULT 2;
ALTER TABLE table1 MODIFY col2 integer DEFAULT 3;
DBMS: firebird Warning: Fails
ALTER TABLE table1 ALTER col1 TYPE decimal(15) SET DEFAULT 2;
ALTER TABLE table1 ALTER col2 TYPE integer SET DEFAULT 3;
7 Rename a column
..\tests\testfiles\test-07.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col_newname" oldname="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7
ALTER TABLE table1 RENAME col2 TO col_newname;
DBMS: oracle
ALTER TABLE table1 RENAME COLUMN col2 TO col_newname;
DBMS: mysql,mysql4
ALTER TABLE table1 CHANGE col2 col_newname integer;
DBMS: firebird
ALTER TABLE table1 ALTER col2 TO col_newname;
8 Rename and change column type at the same time
..\tests\testfiles\test-07a.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col-newname" oldname="col2" size="80" type="varchar"/>
    </columns>
</table>
</schema>
DBMS: postgres
ALTER TABLE table1 ALTER col2 TYPE varchar(80);
ALTER TABLE table1 RENAME col2 TO "col-newname";
DBMS: postgres7
ALTER TABLE table1 ADD tmp_col2 varchar(80);
UPDATE table1 SET tmp_col2 = col2;
ALTER TABLE table1 DROP col2;
ALTER TABLE table1 RENAME tmp_col2 TO col2;
ALTER TABLE table1 RENAME col2 TO "col-newname";
DBMS: mysql,mysql4
ALTER TABLE table1 MODIFY col2 varchar(80);
ALTER TABLE table1 CHANGE col2 `col-newname` varchar(80);
DBMS: oracle
ALTER TABLE table1 MODIFY col2 varchar(80);
ALTER TABLE table1 RENAME COLUMN col2 TO "col-newname";
DBMS: firebird Warning: Fails
ALTER TABLE table1 ALTER col2 TO col-newname;
ALTER TABLE table1 ALTER col-newname TYPE varchar(80);
9 Change a column type and a default at the same time
..\tests\testfiles\test-07b.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column default="1" name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column default="2" name="col1" size="10" type="decimal"/>
        <column default="3" name="col2" size="15" type="decimal"/>
    </columns>
</table>
</schema>
DBMS: postgres
ALTER TABLE table1 ALTER col1 TYPE decimal(10);
ALTER TABLE table1 ALTER col2 TYPE decimal(15);
ALTER TABLE table1 ALTER col1 SET DEFAULT 2;
ALTER TABLE table1 ALTER col2 SET DEFAULT 3;
DBMS: firebird Warning: Fails
ALTER TABLE table1 ALTER col1 TYPE decimal(10);
ALTER TABLE table1 ALTER col2 TYPE decimal(15);
ALTER TABLE table1 ALTER col1 SET DEFAULT 2;
ALTER TABLE table1 ALTER col2 SET DEFAULT 3;
DBMS: mysql,mysql4
ALTER TABLE table1 MODIFY col1 decimal(10);
ALTER TABLE table1 MODIFY col2 decimal(15);
ALTER TABLE table1 ALTER col1 SET DEFAULT 2;
ALTER TABLE table1 ALTER col2 SET DEFAULT 3;
DBMS: oracle
ALTER TABLE table1 MODIFY col1 decimal(10);
ALTER TABLE table1 MODIFY col2 decimal(15);
ALTER TABLE table1 MODIFY col1 decimal(10) DEFAULT 2;
ALTER TABLE table1 MODIFY col2 decimal(15) DEFAULT 3;
DBMS: postgres7
ALTER TABLE table1 ADD tmp_col1 decimal(10);
UPDATE table1 SET tmp_col1 = col1;
ALTER TABLE table1 DROP col1;
ALTER TABLE table1 RENAME tmp_col1 TO col1;
ALTER TABLE table1 ADD tmp_col2 decimal(15);
UPDATE table1 SET tmp_col2 = col2;
ALTER TABLE table1 DROP col2;
ALTER TABLE table1 RENAME tmp_col2 TO col2;
ALTER TABLE table1 ALTER col1 SET DEFAULT 2;
ALTER TABLE table1 ALTER col2 SET DEFAULT 3;
10 Drop a column
..\tests\testfiles\test-08.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7, mysql, mysql4, firebird
ALTER TABLE table1 DROP col1;
DBMS: oracle
ALTER TABLE table1 DROP COLUMN col1;
11 Drop an end column
..\tests\testfiles\test-09.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7, mysql, mysql4, firebird
ALTER TABLE table1 DROP col2;
DBMS: oracle
ALTER TABLE table1 DROP COLUMN col2;
12 Rename a table
..\tests\testfiles\test-10.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="new_table_name" oldname="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7, oracle, mysql, mysql4
ALTER TABLE table1 RENAME TO new_table_name;
DBMS: firebird Warning: Fails
ALTER TABLE table1 RENAME TO new_table_name;
13 Change a table description
..\tests\testfiles\test-11.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table desc="François's table" name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7, oracle
COMMENT ON TABLE table1 IS 'François''s table';
DBMS: mysql,mysql4 Warning: Fails
Note: Can't change a comment on a mysql table, would require recreating table from scratch. If anyone knows otherwise, please let me know.
DBMS: firebird
UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = 'François''s table'
    WHERE RDB$RELATION_NAME = upper('table1');
14 Change a column description
..\tests\testfiles\test-12.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column desc="François's column" name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7, oracle
COMMENT ON COLUMN table1.col1 IS 'François''s column';
DBMS: mysql,mysql4
ALTER TABLE table1 MODIFY col1 integer COMMENT 'François''s column';
DBMS: firebird
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'François''s column' WHERE RDB$RELATION_NAME = upper('table1') AND RDB$FIELD_NAME = upper('col1');
15 Create two indexes
..\tests\testfiles\test-13.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1"/>
        <index columns="col1,col2" name="col1col2"/>
    </indexes>
</table>
</schema>
DBMS: postgres, postgres7, oracle, mysql, mysql4, firebird
CREATE INDEX idx_table1col1 ON table1 (col1);
CREATE INDEX col1col2 ON table1 (col1, col2);
16 Drop two indexes
..\tests\testfiles\test-14.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1"/>
        <index columns="col1,col2" name="col1col2"/>
    </indexes>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
</table>
</schema>
DBMS: postgres, postgres7, oracle, firebird
DROP INDEX idx_table1col1;
DROP INDEX col1col2;
DBMS: mysql,mysql4
DROP INDEX idx_table1col1 ON table1;
DROP INDEX col1col2 ON table1;
17 Rename two indexes, with index names
..\tests\testfiles\test-15.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1" name="table1Col1"/>
        <index columns="col1,col2" name="col1col2"/>
    </indexes>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1" name="rename1" oldname="table1Col1"/>
        <index columns="col1,col2" name="rename2" oldname="col1col2"/>
    </indexes>
</table>
</schema>
DBMS: postgres, postgres7, oracle, firebird
DROP INDEX table1Col1;
DROP INDEX col1col2;
CREATE INDEX rename1 ON table1 (col1);
CREATE INDEX rename2 ON table1 (col1, col2);
DBMS: mysql,mysql4
DROP INDEX table1Col1 ON table1;
DROP INDEX col1col2 ON table1;
CREATE INDEX rename1 ON table1 (col1);
CREATE INDEX rename2 ON table1 (col1, col2);
18 Rename two indexes, no index name given originally
..\tests\testfiles\test-15a.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1"/>
        <index columns="col1,col2" name="col1col2"/>
    </indexes>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1" name="rename1" oldname="table1Col1"/>
        <index columns="col1,col2" name="rename2" oldname="col1col2"/>
    </indexes>
</table>
</schema>
DBMS: postgres, postgres7, oracle, firebird
DROP INDEX col1col2;
DROP INDEX idx_table1col1;
CREATE INDEX rename1 ON table1 (col1);
CREATE INDEX rename2 ON table1 (col1, col2);
DBMS: mysql,mysql4
DROP INDEX col1col2 ON table1;
DROP INDEX idx_table1col1 ON table1;
CREATE INDEX rename1 ON table1 (col1);
CREATE INDEX rename2 ON table1 (col1, col2);
19 Change two indexes with index names given
..\tests\testfiles\test-16.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1" name="table1cCol1"/>
        <index columns="col1,col2" name="col1col2"/>
    </indexes>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col2" name="table1Col1"/>
        <index columns="col2,col1" name="col1col2"/>
    </indexes>
</table>
</schema>
DBMS: postgres, postgres7, oracle, firebird
DROP INDEX col1col2;
DROP INDEX table1cCol1;
CREATE INDEX table1Col1 ON table1 (col2);
CREATE INDEX col1col2 ON table1 (col2, col1);
DBMS: mysql,mysql4
DROP INDEX col1col2 ON table1;
DROP INDEX table1cCol1 ON table1;
CREATE INDEX table1Col1 ON table1 (col2);
CREATE INDEX col1col2 ON table1 (col2, col1);
20 Change two indexes, no index name given originally
..\tests\testfiles\test-16a.xml
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col1"/>
        <index columns="col1,col2" name="col1col2"/>
    </indexes>
</table>
</schema>
BeforeAfter
<schema>
<table name="table1">
    <columns>
        <column name="col1" type="integer"/>
        <column name="col2" type="integer"/>
    </columns>
    <indexes>
        <index columns="col2" name="table1Col1"/>
        <index columns="col2,col1" name="col1col2"/>
    </indexes>
</table>
</schema>
DBMS: postgres, postgres7, oracle, mysql, mysql4, firebird Warning: Fails
DROP INDEX table1Col1;
CREATE INDEX table1Col1 ON table1 (col2);
DROP INDEX col1col2;
CREATE INDEX col1col2 ON table1 (col2, col1);
21 Create a relation
..\tests\testfiles\test-17.xml
<schema>
    <!-- Until I get dropping of tables with constraints done in the
         Right order, table2 needs to be first -->
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
    </table>
    <table name="table1">
        <columns>
            <column default="0" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
BeforeAfter
<schema>
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
        <indexes>
            <!-- MySQL addes this automatically -->
            <index columns="table1_id" name="fk_table1_id"/>
        </indexes>
        <relations>
            <relation column="table1_id" table="table1"/>
        </relations>
    </table>
    <table name="table1">
        <columns>
            <column default="0" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
DBMS: postgres, postgres7, oracle, mysql, mysql4
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id);
CREATE INDEX fk_table1_id ON table2 (table1_id);
DBMS: firebird Warning: Fails
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id);
22 Drop a relation
..\tests\testfiles\test-17a.xml
<schema>
    <!-- FIX: Broken if the tables are in the wrong order, need to find the dependancy list and try to build in the right order -->
    <table name="table1">
        <columns>
            <column key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
        <relations>
            <relation column="table1_id" table="table1"/>
        </relations>
    </table>
</schema>
BeforeAfter
<schema>
    <table name="table1">
        <columns>
            <column key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
    </table>
</schema>
DBMS: postgres, postgres7, oracle
ALTER TABLE table2 DROP CONSTRAINT fk_table1_id;
DBMS: mysql,mysql4
Note: This doesn't pass the test because mysql drops the default as well
ALTER TABLE table2 DROP CONSTRAINT fk_table1_id;
DBMS: firebird Warning: Fails
ALTER TABLE table2 DROP CONSTRAINT fk_table1_id;
23 Create a relation with 'on delete'
..\tests\testfiles\test-17b.xml
<schema>
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
    </table>
    <table name="table1">
        <columns>
            <column default="0" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
BeforeAfter
<schema>
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
        <indexes>
            <!-- MySQL silently adds this -->
            <index columns="table1_id" name="fk_table1_id"/>
        </indexes>
        <relations>
            <relation column="table1_id" fk="table1_id" name="fk_table1_id" ondelete="cascade" onupdate="cascade" table="table1"/>
        </relations>
    </table>
    <table name="table1">
        <columns>
            <column default="0" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
DBMS: postgres, postgres7, mysql, mysql4
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX fk_table1_id ON table2 (table1_id);
DBMS: oracle
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id) ON DELETE CASCADE;
CREATE INDEX fk_table1_id ON table2 (table1_id);
DBMS: firebird Warning: Fails
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id);
24 Change a relation
..\tests\testfiles\test-17c.xml
<schema>
    <!-- Fix: if the tables are in the wrong order it fails -->
    <table name="table1">
        <columns>
            <column default="0" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
        <relations>
            <relation column="table1_id" fk="table1_id" name="fk_table1_id" ondelete="cascade" onupdate="cascade" table="table1"/>
        </relations>
    </table>
</schema>
BeforeAfter
<schema>
    <table name="table2">
        <columns>
            <column name="table2_id" type="integer"/>
            <column name="table1_id" type="integer"/>
        </columns>
        <relations>
            <relation column="table1_id" fk="table1_id" name="fk_table1_id" ondelete="setnull" onupdate="setnull" table="table1"/>
        </relations>
    </table>
    <table name="table1">
        <columns>
            <column default="0" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
DBMS: postgres, postgres7
ALTER TABLE table2 DROP CONSTRAINT fk_table1_id;
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id) ON DELETE SET NULL ON UPDATE SET NULL;
DBMS: oracle
ALTER TABLE table2 DROP CONSTRAINT fk_table1_id;
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id) ON DELETE SET NULL;
DBMS: mysql,mysql4 Warning: Fails
ALTER TABLE table2 DROP CONSTRAINT fk_table1_id;
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id) ON DELETE SET NULL ON UPDATE SET NULL;
DBMS: firebird Warning: Fails
ALTER TABLE table2 DROP CONSTRAINT fk_table1_id;
ALTER TABLE table2 ADD CONSTRAINT fk_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id) ON DELETE SET NULL ON UPDATE SET NULL;
25 Add an auto increment
..\tests\testfiles\test-18.xml
<schema>
</schema>
BeforeAfter
<schema>
    <table name="table1">
        <columns>
            <column autoincrement="yes" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
DBMS: postgres,postgres7
CREATE SEQUENCE table1_table1_id_seq;
CREATE TABLE table1 (
        table1_id integer NOT NULL DEFAULT nextval('table1_table1_id_seq'),
        col2 integer,
        CONSTRAINT pk_table1 PRIMARY KEY (table1_id));
DBMS: mysql,mysql4
CREATE TABLE table1 (
        table1_id integer NOT NULL AUTO_INCREMENT,
        col2 integer,
        CONSTRAINT pk_table1 PRIMARY KEY (table1_id)) TYPE=InnoDB;
DBMS: oracle Warning: Fails
CREATE SEQUENCE table1_table1_id_seq;
CREATE TABLE table1 (
        table1_id integer NOT NULL,
        col2 integer,
        CONSTRAINT pk_table1 PRIMARY KEY (table1_id));
CREATE TRIGGER ai_table1_table1_id FOR table1
        BEFORE INSERT AS
        BEGIN
            IF (:NEW.table1_id IS NULL) THEN
             SELECT table1_table1_id_seq.NEXTVAL INTO :NEW.table1_id FROM DUAL;
            END IF;
     END;
DBMS: firebird
CREATE GENERATOR table1_table1_id_seq;
CREATE TABLE table1 (
        table1_id integer NOT NULL,
        col2 integer,
        CONSTRAINT pk_table1 PRIMARY KEY (table1_id));
CREATE TRIGGER ai_table1_table1_id FOR table1
        BEFORE INSERT AS
        BEGIN
            NEW.table1_id = GEN_ID(table1_table1_id_seq, 1);
        END;
26 Drop an autoincrement
..\tests\testfiles\test-19.xml
<schema>
    <table name="table1">
        <columns>
            <column autoincrement="yes" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
BeforeAfter
<schema>
    <table name="table1">
        <columns> <!-- Added the default="0" to work best with MySQL -->
            <column key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
DBMS: postgres,postgres7
ALTER TABLE table1 ALTER table1_id DROP DEFAULT;
DROP SEQUENCE table1_table1_id_seq;
DBMS: mysql
ALTER TABLE table1 MODIFY table1_id integer NOT NULL;
DBMS: mysql4 Warning: Fails
ALTER TABLE table1 ALTER table1_id DROP DEFAULT;
DROP SEQUENCE table1_table1_id_seq;
DBMS: oracle Warning: Fails
DROP TRIGGER ai_table1_table1_id;
DROP SEQUENCE table1_table1_id_seq;
DBMS: firebird Warning: Fails
27 Drop a complex table
..\tests\testfiles\test-19a.xml
<schema>
    <table name="table1">
        <columns>
            <column autoincrement="yes" key="1" name="table1_id" null="no" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
BeforeAfter
<schema>
</schema>
DBMS: mysql, mysql4
DROP TABLE table1;
DBMS: postgres,postgres7
ALTER TABLE table1 ALTER table1_id DROP DEFAULT;
ALTER TABLE table1 DROP CONSTRAINT pk_table1;
DROP TABLE table1;
DROP SEQUENCE table1_table1_id_seq;
DBMS: oracle Warning: Fails
DROP SEQUENCE table1_table1_id_seq;
DROP TRIGGER ai_table1_table1_id;
ALTER TABLE table1 DROP CONSTRAINT pk_table1;
DROP TABLE table1;
DBMS: firebird
ALTER TABLE table1 DROP CONSTRAINT pk_table1;
DROP TRIGGER ai_table1_table1_id;
DROP TABLE table1;
DROP GENERATOR table1_table1_id_seq;
28 Create a view
..\tests\testfiles\test-20.xml
<schema>
    <table name="table1">
        <columns>
            <column name="id" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
BeforeAfter
<schema>
    <table name="table1">
        <columns>
            <column name="id" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <view name="myview">
        SELECT table1.id, table1.col2 FROM table1
    </view>
</schema>
DBMS: postgres, postgres7, oracle, mysql, firebird
CREATE VIEW myview AS SELECT table1.id, table1.col2 FROM table1;
DBMS: mysql4 Warning: Fails
29 Drop a table with a related view
..\tests\testfiles\test-20a.xml
<schema>
    <table name="table1">
        <columns>
            <column name="id" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <view name="myview">
        SELECT table1.id, table1.col2 FROM table1
    </view>
</schema>
BeforeAfter
<schema>
</schema>
DBMS: postgres, postgres7, oracle, mysql, firebird
DROP VIEW myview;
DROP TABLE table1;
DBMS: mysql4 Warning: Fails
30 Drop a view
..\tests\testfiles\test-21.xml
<schema>
    <table name="table1">
        <columns>
            <column name="id" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <view name="myview">
        SELECT table1.id, table1.col2 FROM table1
    </view>
</schema>
BeforeAfter
<schema>
    <table name="table1">
        <columns>
            <column name="id" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
</schema>
DBMS: postgres, postgres7, oracle, mysql, firebird
DROP VIEW myview;
DBMS: mysql4 Warning: Fails
31 Rename a view
..\tests\testfiles\test-22.xml
<schema>
    <table name="table1">
        <columns>
            <column name="id" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <view name="myview">
        SELECT table1.id, table1.col2 FROM table1
    </view>
</schema>
BeforeAfter
<schema>
    <table name="table1">
        <columns>
            <column name="id" type="integer"/>
            <column name="col2" type="integer"/>
        </columns>
    </table>
    <view name="newview" oldname="myview">
        SELECT table1.id, table1.col2 FROM table1
    </view>
</schema>
DBMS: postgres, postgres7, oracle, firebird
DROP VIEW myview;
CREATE VIEW newview AS SELECT table1.id, table1.col2 FROM table1;
DBMS: mysql4 Warning: Fails
DBMS: mysql
32 Create a function
..\tests\testfiles\test-23.xml
<schema>
</schema>
BeforeAfter
<schema>
    <function arguments="subtotal float" dbms="firebird" name="myfunc" returns="float"> BEGIN
ret = subtotal * 0.06; END
</function>
<function arguments="subtotal float" dbms="postgres,mysql,oracle" name="myfunc" returns="float"> BEGIN
RETURN subtotal * 0.06; END
</function>
</schema>
DBMS: postgres
CREATE FUNCTION myfunc(subtotal float) RETURNS float AS '
BEGIN
    RETURN subtotal * 0.06;
END' LANGUAGE plpgsql;
DBMS: postgres7
CREATE FUNCTION myfunc(float) RETURNS float AS '
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END' LANGUAGE plpgsql;
DBMS: mysql
CREATE FUNCTION myfunc(subtotal float) RETURNS float
BEGIN
    RETURN subtotal * 0.06;
END;
DBMS: firebird
CREATE PROCEDURE myfunc(subtotal float) RETURNS (ret float) AS
BEGIN
    ret = subtotal * 0.06;
END;;
DBMS: oracle
CREATE FUNCTION myfunc(subtotal IN float) RETURN float AS
BEGIN
    RETURN subtotal * 0.06;
END;;
33 Drop a function
..\tests\testfiles\test-24.xml
<schema>
    <function arguments="subtotal float" dbms="firebird" name="myfunc" returns="float"> BEGIN
ret = subtotal * 0.06; END
</function>
    <function arguments="subtotal float" dbms="postgres,mysql,oracle" name="myfunc" returns="float"> BEGIN
RETURN subtotal * 0.06; END;
    </function>
</schema>
BeforeAfter
<schema>
</schema>
DBMS: postgres,postgres7
DROP FUNCTION myfunc(float);
DBMS: mysql
DROP FUNCTION myfunc;
DBMS: firebird
DROP PROCEDURE myfunc;
34 Change a function
..\tests\testfiles\test-25.xml
<schema>
    <function arguments="subtotal float" dbms="postgres,mysql,oracle" name="myfunc" returns="float"> BEGIN
RETURN subtotal * 0.06; END
    </function>
    <function arguments="subtotal float" dbms="firebird" name="myfunc" returns="float"> BEGIN
ret = subtotal * 0.06; END
    </function>
</schema>
BeforeAfter
<schema>
    <function arguments="subtotal float" dbms="postgres,mysql,oracle" name="myfunc" returns="float"> BEGIN
RETURN subtotal * 0.07; END
    </function>
    <function arguments="subtotal float" dbms="firebird" name="myfunc" returns="float"> BEGIN
ret = subtotal * 0.07; END
    </function>
</schema>
DBMS: postgres
DROP FUNCTION myfunc(float);
CREATE FUNCTION myfunc(subtotal float) RETURNS float AS '
BEGIN
    RETURN subtotal * 0.07;
END' LANGUAGE plpgsql;
DBMS: postgres7
DROP FUNCTION myfunc(float);
CREATE FUNCTION myfunc(subtotal float) RETURNS float AS '
BEGIN
    RETURN subtotal * 0.07;
END' LANGUAGE plpgsql;
DBMS: mysql
DROP FUNCTION myfunc;
CREATE FUNCTION myfunc(subtotal float) RETURNS float
BEGIN
    RETURN subtotal * 0.07;
END;
DBMS: firebird
DROP PROCEDURE myfunc;
CREATE PROCEDURE myfunc(subtotal float) RETURNS (ret float) AS
BEGIN
    ret = subtotal * 0.07;
END;;
35 Rename a function
..\tests\testfiles\test-26.xml
<schema>
    <function arguments="subtotal float" dbms="postgres,mysql,oracle" name="newfunc" oldname="myfunc" returns="float"> BEGIN
RETURN subtotal * 0.06; END
    </function>
    <function arguments="subtotal float" dbms="firebird" name="newfunc" oldname="myfunc" returns="float"> BEGIN
ret = subtotal * 0.06; END
    </function>
</schema>
BeforeAfter
<schema>
    <function arguments="subtotal float" dbms="postgres,mysql,oracle" name="newfunc" oldname="myfunc" returns="float"> BEGIN
RETURN subtotal * 0.06; END
    </function>
    <function arguments="subtotal float" dbms="firebird" name="newfunc" oldname="myfunc" returns="float"> BEGIN
ret = subtotal * 0.06; END
    </function>
</schema>
DBMS: postgres,postgres7
DROP FUNCTION myfunc(float);
CREATE FUNCTION newfunc(float) RETURNS float AS '
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END' LANGUAGE plpgsql;
DBMS: mysql
DROP FUNCTION myfunc;
CREATE FUNCTION newfunc(subtotal float) RETURNS float
BEGIN
    RETURN subtotal * 0.06;
END;
DBMS: firebird
DROP PROCEDURE myfunc;
CREATE PROCEDURE newfunc(subtotal float) RETURNS (ret float) AS
BEGIN
    RETURN subtotal * 0.06;
END;;
36 Test a naming convention with abbreviations
..\tests\testfiles\test-27.xml
<schema>
</schema>
BeforeAfter
<schema>
    <dictionary name="column">
        <dict class="mytype" type="integer"/>
    </dictionary>
    <table abbr="t" name="table1">
        <columns>
            <column class="mytype" name="col1"/>
            <column name="col2" type="integer"/>
            <column name="col3" size="32" type="varchar"/>
            <column name="col4" size="15" type="decimal"/>
            <column name="col5" precision="3" size="15" type="decimal"/>
            <column name="col6" type="date"/>
            <!-- <column name="col7" type="timestamp"/> This one causing problems with mysql -->
            <column default="2" name="col8" type="int"/>
            <column default="1.234" name="col9" type="float"/>
        </columns>
    </table>
</schema>
DBMS: oracle,postgres,postgres7,firebird
CREATE TABLE table1 (
    t_col1 integer,
    t_col2 integer,
    t_col3 varchar(32),
    t_col4 decimal(15),
    t_col5 decimal(15, 3),
    t_col6 date,
    t_col8 int DEFAULT 2,
    t_col9 float DEFAULT 1.234
);
DBMS: mysql,mysql4
CREATE TABLE table1 (
    t_col1 integer,
    t_col2 integer,
    t_col3 varchar(32),
    t_col4 decimal(15),
    t_col5 decimal(15, 3),
    t_col6 date,
    t_col8 int DEFAULT 2,
    t_col9 float DEFAULT 1.234
) TYPE=InnoDB;