Convert DB table to XML - a worked example

If you have a database table which you wish to convert into a Zymonic XML defined table, this can be a laborious task. Luckily 2 toolkit commands (import_from_db) and (export_def) can be used to automatically generate a Zymonic XML definition to replicate any DB table, below is an example.

The situation

We have a 'legacy' database (legacy_db) with a table (delivery_methods) that we wish to use in Zymonic.

There are 2 commands here, that are used together. The aim is to be able to automatically produce an xml file that defines the SQL table. You should use import_from_db to create a system definition from a table in the DB, then use export_def to create an xml file from that system defintion.

Also note that you need to have run a config build with the file ZymonicCoreDefinitions.xml in /etc/zymonic/[system name] for the following import command to work.

Importing

First we run the import_from_db command below:

 zymonic_toolkit.pl Definition import_from_db --definition export_test --dbname legacy_db --dbusername root --AutoCreateMaintenanceProcess true --AutoCreateFilter true --AutoCreateForm true --tablename delivery_methods --fieldznameprefix f_ --tableznameprefix t_

It's worth noting that here you must put the database that currently has the table, the system you wish to add the xml to, and the sql/db name of the table.

This has added the system definition to the export_test's db, in the table zz_system_definition. We can then do a config build, and the definition will be added from this table, to the system. After, when we run export_def:

 sudo zymonic_toolkit.pl System config_build --system export_def

There is now a system definition in export_test, of the table.

Editing

Once the definition has been config built into the system, the definition of the table can be viewed and edited from the system GUI with 'User System Definitions List' (although I won't here because I want to test that the tables come out identical):

Exporting

Now producing a bit of xml is as simple as redirecting the output of export_def to a file:

   sudo zymonic_toolkit.pl Definition export_def --definition export_test --zname t_delivery_methods > ~/file.xml

This can then be added to your system in /etc/zymonic/[system], and when a config build is run the table (and process and filter if you had the flags set to autocreate them), will be added to the system. (If you are adding the XML file to the system you used earlier - export_test in this example - then, before config building, you will need to delete the table and fields from zz_system_definition and remove the perist file "sudo rm /etc/zymonic/export_test/ZZPersist.conf" before config building.)

Below is a comparison of the explains of the original table, and the table produced (with the fields re-ordered for clarity):