1. Create a build.xml as follows. This example is taken from $SQLFIRE_HOME/lib/ddlutils/example directory. It's been edited to point to a MYSQL database for the source schema and a target SQLFire database as the target schema.
<?xml version="1.0"?>
<project name="test" default="writeDDLToXML" basedir=".">
<path id="runtime-classpath">
<fileset dir="./lib">
<include name="**/*.jar"/>
<include name="**/*.zip"/>
</fileset>
</path>
<!-- This will output all the object in the database 'test' and output the schema definition
to a file 'db-schema1.xml'. This will also extract the data and output this to 'data.xml'
-->
<target name="writeDDLToXML" description="Dumps the database structure">
<taskdef name="databaseToDdl"
classname="org.apache.ddlutils.task.DatabaseToDdlTask">
<classpath refid="runtime-classpath"/>
</taskdef>
<databaseToDdl verbosity="debug">
<!--
<database url="jdbc:sqlfire://localhost:1527"
driverClassName="com.vmware.sqlfire.jdbc.ClientDriver"
username="app"
password="app"/>
-->
<database url="jdbc:mysql://localhost/scottdb"
driverClassName="com.mysql.jdbc.Driver"
username="scott"
password="tiger"/>
<writeSchemaToFile outputFile="db-schema1.xml" failonerror="false" />
<!-- Comment line below if the source DB is too big -->
<writeDataToFile outputFile="data.xml"/>
</databaseToDdl>
</target>
<!-- This will create the tables, etc in SQLFire.
If your table names or column names contain embedded spaces, set
usedelimitedsqlidentifiers to true.
-->
<target name="createDBFromXML" description="Create the DB tables ..">
<taskdef classname="org.apache.ddlutils.task.DdlToDatabaseTask"
name="ddlToDatabase"
classpathref="runtime-classpath"/>
<ddlToDatabase usedelimitedsqlidentifiers="false">
<database driverclassname="com.vmware.sqlfire.jdbc.ClientDriver"
url="jdbc:sqlfire://localhost:1527"
username="app"
password="app"/>
<fileset dir=".">
<include name="db-schema1.xml"/>
</fileset>
<!-- <createdatabase failonerror="false"/> -->
<writeschematodatabase alterdatabase="true"
failonerror="false"/>
</ddlToDatabase>
</target>
<!-- Extract DDL for a schema and write this out as a 'SQL' script file (db-schema1.sql).
EDIT THIS FILE TO CHANGE THE TABLES TO BE CUSTOM PARTITIONED, REPLICATED, PERSISTENT, ETC
Then, execute the SQL script using 'IJ' or your favorite tool (like SQuirrel)
-->
<target name="writeDDLToSQL" description="Dumps the database structure">
<taskdef name="databaseToDdl"
classname="org.apache.ddlutils.task.DatabaseToDdlTask">
<classpath refid="runtime-classpath"/>
</taskdef>
<databaseToDdl verbosity="debug">
<database url="jdbc:sqlfire://localhost:1527"
driverClassName="com.vmware.sqlfire.jdbc.ClientDriver"
username="app"
password="app"/>
<writeSchemaSqlToFile outputFile="db-schema1.sql" dodrops="true" failonerror="false" alterdatabase="false"/>
</databaseToDdl>
</target>
<!-- Imports data rows into a database.
If your table names or column names contain embedded spaces, set
usedelimitedsqlidentifiers to true.
-->
<target name="ImportDataToDB" description="Import the data ..">
<taskdef classname="org.apache.ddlutils.task.DdlToDatabaseTask"
name="ddlToDatabase"
classpathref="runtime-classpath"/>
<ddlToDatabase usedelimitedsqlidentifiers="false">
<database url="jdbc:sqlfire://localhost:1527"
driverClassName="com.vmware.sqlfire.jdbc.ClientDriver"
username="app"
password="app"/>
<fileset dir=".">
<include name="db-schema1.xml"/>
</fileset>
<writedatatodatabase datafile="data.xml"
usebatchmode="true"
batchsize="1000"/>
</ddlToDatabase>
</target>
</project>
2. Ensure you have Apache ant in your path
3. Ensure you have placed the JDBC drivers for SQLFire and MYSQL in the path which will be picked up by ant. In this example ANT is using a "lib" sub directory to include all JARS in that directory at runtime. This "lib" directory would also include the Apache DDLUtils jar files as well.
mysql-connector-java-5.1.17-bin.jar
sqlfireclient.jar
Here is a full directory listing on the "lib" directory JAR files I was using.
[Thu Jun 21 14:29:01 papicella@:~/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/lib ] $ d
total 38848
-rw-r--r-- 1 papicella staff 787920 4 Jul 2011 mysql-connector-java-5.1.17-bin.jar
-rw-r--r-- 1 papicella staff 474464 20 Jun 14:32 wstx-asl-3.0.2.jar
-rw-r--r-- 1 papicella staff 26514 20 Jun 14:32 stax-api-1.0.1.jar
-rw-r--r-- 1 papicella staff 352668 20 Jun 14:32 log4j-1.2.8.jar
-rw-r--r-- 1 papicella staff 486522 20 Jun 14:32 dom4j-1.4.jar
-rw-r--r-- 1 papicella staff 42492 20 Jun 14:32 commons-pool-1.2.jar
-rw-r--r-- 1 papicella staff 38015 20 Jun 14:32 commons-logging-1.0.4.jar
-rw-r--r-- 1 papicella staff 207723 20 Jun 14:32 commons-lang-2.1.jar
-rw-r--r-- 1 papicella staff 139966 20 Jun 14:32 commons-digester-1.7.jar
-rw-r--r-- 1 papicella staff 107631 20 Jun 14:32 commons-dbcp-1.2.1.jar
-rw-r--r-- 1 papicella staff 559366 20 Jun 14:32 commons-collections-3.1.jar
-rw-r--r-- 1 papicella staff 46725 20 Jun 14:32 commons-codec-1.3.jar
-rw-r--r-- 1 papicella staff 188671 20 Jun 14:32 commons-beanutils-1.7.0.jar
-rw-r--r-- 1 papicella staff 447398 20 Jun 14:34 DdlUtils-1.0.jar
-rw-r--r-- 1 papicella staff 822779 20 Jun 14:37 sqlfireclient.jar
-rw-r--r-- 1 papicella staff 15125561 20 Jun 14:37 sqlfire.jar
drwxr-xr-x 18 papicella staff 612 21 Jun 14:08 ./
drwxr-xr-x 7 papicella staff 238 21 Jun 14:08 ../
** At this point you can begin the process as shown below **
4. Create schema / data file in XML using the ant task as follows
[Thu Jun 21 08:36:10 papicella@:~/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils ] $ ant writeDDLToXML
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/build.xml
writeDDLToXML:
[databaseToDdl] Borrowed connection org.apache.commons.dbcp.PoolableConnection@bdee400 from data source
[databaseToDdl] Returning connection org.apache.commons.dbcp.PoolableConnection@bdee400 to data source.
[databaseToDdl] Remaining connections: None
[databaseToDdl] Written schema to /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/db-schema1.xml
[databaseToDdl] Borrowed connection org.apache.commons.dbcp.PoolableConnection@bdee400 from data source
[databaseToDdl] Returning connection org.apache.commons.dbcp.PoolableConnection@bdee400 to data source.
[databaseToDdl] Remaining connections: None
[databaseToDdl] Borrowed connection org.apache.commons.dbcp.PoolableConnection@bdee400 from data source
[databaseToDdl] Returning connection org.apache.commons.dbcp.PoolableConnection@bdee400 to data source.
[databaseToDdl] Remaining connections: None
[databaseToDdl] Written data XML to file/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/data.xml
BUILD SUCCESSFUL
Total time: 1 second
5. If your happy to use only replicated tables then you can import the schema definition into
SQLFire as follows. Every table created by default is "replicate" in this scenario.
[Thu Jun 21 08:36:37 papicella@:~/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils ] $ ant createDBFromXML
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/build.xml
createDBFromXML:
[ddlToDatabase] Read schema file /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/db-schema1.xml
[ddlToDatabase] Table DEPT needs to be added
[ddlToDatabase] Table EMP needs to be added
[ddlToDatabase] Foreign key Foreign key [name=EMP_FK; foreign table=DEPT; 1 references] needs to be added to table EMP
[ddlToDatabase] Executed 3 SQL command(s) with 0 error(s)
[ddlToDatabase] Written schema to database
BUILD SUCCESSFUL
Total time: 0 seconds
6. This next step will take the XML created in step #1 and create a schema file in SQL. This is great because you can now add the SQLFire SQL to use partioned, disk stores etc. Without persisteant disk stores the data will disappear once the system goes down.
[Thu Jun 21 08:54:01 papicella@:~/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils ] $ ant writeDDLToSQL
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/build.xml
writeDDLToSQL:
[databaseToDdl] Borrowed connection org.apache.commons.dbcp.PoolableConnection@2d1e233 from data source
[databaseToDdl] Returning connection org.apache.commons.dbcp.PoolableConnection@2d1e233 to data source.
[databaseToDdl] Remaining connections: None
[databaseToDdl] Written schema SQL to /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/db-schema1.sql
BUILD SUCCESSFUL
Total time: 1 second
7. Finally with a schema created we can simply import the data created from #4 above as follows.
[Thu Jun 21 08:54:43 papicella@:~/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils ] $ ant ImportDataToDB
Buildfile: /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/build.xml
ImportDataToDB:
[ddlToDatabase] Read schema file /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/db-schema1.xml
[ddlToDatabase] Written data from file /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo/ddlutils/data.xml to database
BUILD SUCCESSFUL
Total time: 0 seconds
8. The last thing left to do is verify we have our schema data within SQLFire as shown below.
[Thu Jun 21 08:55:32 papicella@:~/sqlfire/vFabric_SQLFire_1021/pasdemos/ddlutils-demo ] $ sqlf sqlf version 10.4 sqlf> connect client 'localhost:1527'; sqlf> show tables in app; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |DEPT | APP |EMP | 2 rows selected sqlf> select * from dept; DEPTNO |DNAME |LOC ---------------------------------------- 20 |RESEARCH |DALLAS 10 |ACCOUNTING |NEW YORK 30 |SALES |CHICAGO 40 |OPERATIONS |BOSTON 4 rows selected
For more information on DDLUtils with SQLFire see the link below.
http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.0/data_management/ddlutils-procedure.html
No comments:
Post a Comment