R E L A T I O N A L   D A T A B A S E S
XArch currently contains two IO driver that allow you to export data stored in a relational database for archiving. The first driver, called RDBE, exports an entire database into a flat hierarchical structure. The second driver, called DEP, allows you to specify an export script using SQL-queries for flexible export of data into a hierarchical structure. The following example explains how to create a simple relational database and archive its content in two differently structured archives.
Files
For this example you need the following file:
COMPANY.sql A file containing DDL-statements to create two simple relations for a company database, and DML-statements that insert some data.
Commands
Use the provided COMPANY.sql to create a relational database on your machine and insert a couple of tuples. In the following we assume that you use MySQL as the local database management system. We furter assume that the database is called company.
RDBE - Export the current database state
In order to archive the current database state in a flat hierarchical format you first need to create a new archive for the database:
CREATE ARCHIVE COMPANY OF XML
To export the current state of database company into your archive using the RDBE driver use the following command:
INSERT INTO COMPANY FROM TYPE RDBE
DBMSTYPE=MySQL,
HOST=localhost,
PORT=3306,
DBNAME=company,
DBSCHEMA=company,
USER=root,
PASSWORD=somepwd,
INCLUDE_SCHEMA=false
Note that you may have to adjust some of the properties (like USER and PASSWORD) in order to connect to the database. If you are using PostgreSQL instead of MySQL as the database backend change property RDBMS to POSTGRES or PSQL. The other two RDBMSs for which JDBC driver are included in the XArch package are DB2 and ORACLE.
After exporting the current database state you can view the archive contents using the following simple query:
SELECT * FROM COMPANY
DEP - Publish the database in hierarchical format
You may also want to archive the company database in a format that is similar to the following XML snipped:
<COMPANY>
 <DEPARTMENT>
  <NAME>Marketing</NAME>
  <EMPLOYEE>
   <NAME>John</NAME>
   <SALARY>30,000</SALARY>
...
For this example you need the following files:
COMPANY.dep The export script for exporting the data in the company database.
COMPANY.keys The key specification reflecting the structure of the data as output by the export script.
First, create the following archive MYCOMPANY:
CREATE ARCHIVE MYCOMPANY OF XML HAVING PROPERTIES DEFAULT_SCHEMA=COMPANY.keys
You have to modify the path of the DEFAULT_SCHEMA depending on where you stored COMPANY.keys on your local machine or whether you read the file directly from the Web.
The following statement uses the export script COMPANY.dep to export the data in the desired format:
INSERT INTO MYCOMPANY FROM TYPE DEP
DBMSTYPE=MySQL,
HOST=localhost,
PORT=3306,
DBNAME=company,
DBSCHEMA=company,
USER=root,
PASSWORD=somepwd,
PROGRAM_FILE=COMPANY.dep