XML to DDL imports, synchronizes database schemata

The Freshmeat.org project known as xml2ddl provides a set of Python programs under the GNU General Public License, or GPL. Given a working Python runtime environment, this set of tools works on many operating systems, including most Windows, Linux, and UNIX platforms. It also works with the following database engines: PostgreSQL, MySQL, Oracle, and Firebird.

Basically, xml2ddl permits users to convert an XML representation of a database into a corresponding set of SQL or DDL statements. According to its creator and custodian, Scott Kirkwood, "XML to DDL strives to be database independent so that the same XML can be used for a variety of databases. This is great for quickly testing out a variety of databases for performance, for example" [quotation comes from the index.html file included in /doc subdirectory within the project ZIP file].

To get the process started, xml2ddl permits users to point to a schema, designate a target database, and produce the requisite DDL or SQL statements necessary to instantiate that database. Given this simple sample XML schema definition in a file named schema1.xml:

  <table name="students" fullname="List of Students" 
      desc="List of students with their full names">
      <columns>
          <column name="id" fullname="Primary Key" type="integer" key="1"
              desc="Primary key for the table"/>
          <column name="student_name" fullname="Student Name" 
              type="varchar" size="80"
              desc="The full name of the student"/>
      </columns>
  </table>
</schema>

And an invocation to output the information for PostgresSQL using the following command line syntax: xml2ddl --dbms postgres schema1.xml, the following output results:

DROP TABLE students;
CREATE TABLE students (
        id integer,
        student_name varchar(80),
        CONSTRAINT pk_students PRIMARY KEY (id));
COMMENT ON TABLE students IS 'List of students with their full names';
COMMENT ON COLUMN students.id IS 'Primary key for the table';
COMMENT ON COLUMN students.student_name IS 'The full name of the student';

Tailored outputs of the same kind for the other target databases may be generated by substituting firebird, oracle, or mysql instead.

The xml2ddl programs can also examine the differences between two different versions of an XML schema and generate the DDL or SQL statements necessary to push those same changes into the target database as well. This requires two related schemas (let's call the second one schema2.xml and recycle schema1.xml) and uses the following syntax: diffxml2ddl --dbms postgres schema1.xml schema2.xml.

If schema2.xml looks as follows:

<schema>
    <table name="students" fullname="List of Students" 
        desc="List of students">
       <columns>
            <column name="id" fullname="Primary Key" type="integer" key="1"
                desc="Primary key for the table"/>
            <column name="student_name" fullname="Student Name" 
                type="varchar" size="100"
                desc="The full name of the student"/>
            <column name="email" fullname="Electronic mail address" 
                type="varchar" size="100"
                desc="The primary email for the student"/>
        </columns>
    </table>
</schema>

Then the following DDL output is produced for PostgresSQL:

ALTER TABLE students ALTER student_name TYPE varchar(80);
ALTER TABLE students DROP email;
COMMENT ON TABLE students IS 'List of students with their full names';

A complete list of examples is available at the project Web site. What's nice about these tools is that they make it easy to define and manage databases using XML and related structured editing tools, and make short work of translating your structured XML efforts into the proper metalanguage for database implementation and use.

This one's definitely worth checking out, and makes a really nifty data integration and management tool for those lucky enough to be using one of the supported target databases.

[Note: all cited examples here are lifted directly from the xml2ddl help file, as cited earlier in this tip.]

About the author

Ed Tittel is a full-time writer and trainer whose interests include XML and development topics, along with IT Certification and information security topics. Among his many XML projects are XML For Dummies, 4th edition, (Wylie, 2005) and the Shaum's Easy Outline of XML (McGraw-Hill, 2004). E-mail Ed at etittel@techtarget.com with comments, questions, or suggested topics or tools for review.