1.0

Export, Alter, and Import a Database Schema Using SQLF
Use sqlf commands to export the schema and data from a third-party database, and then import the schema and
data to vFabric SQLFire.
Procedure
1. To use the sqlf export commands with a third-party database, you require a JDBC driver and connection
URL for the database. Use a Java client application such as SQuirreL SQL to verify that you can connect to
the database.
This procedure uses MySQL as an example datasource. The components necessary to establish a JDBC
connection the example server are:
• Driver JAR file: mysql-connector-java-5.1.18-bin.jar
• Driver class: com.mysql.jdbc.Driver
• Connection URL: jdbc:mysql://localhost:3306/sqlfdb?user=username&password=password
To ensure that sqlf can access the JDBC driver class, add the JAR location to your CLASSPATH. For example,
open a new command prompt and enter:
export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
2.
Add the SQLFire /bin directory to your path if you have not already done so. For example:
export PATH=$PATH:~/vFabric_SQLFire_10/bin
3. Use sqlf to export the schema of the third-party data source to a schema SQL file in a format that is compatible
with SQLFire. For example:
sqlf write-schema-to-sql -file=mysql-schema.sql -to-database-type=sqlfire
-driver-class=com.mysql.jdbc.Driver
-url=jdbc:mysql://localhost:3306/sqlfdb?user=username&password=password
Note: Databases such as Oracle 11g contain system tables with data types that are incompatible with
the DdlUtils 1.1 API. To export schemas or data from these databases, you must use the
-schema-pattern option with the sqlf command to exclude schemas that contain incompatible
data types. See sqlf write-schema-to-xml.
4.
Edit the CREATE TABLE statements in the resulting schema SQL file to use SQLFire-specific clauses. For
example use syntax to specify colocation for partitioning, persist tables, associate tables with gateways, and
so forth.
5.
After editing the SQL script file, use an interactive sqlf session to execute the script in SQLFire:
sqlf
connect client 'localhost:1527';
run 'mysql-schema.sql';
exit;
6. To import the data from the third-party datasource, first use these sqlf commands to export both the data and
schema to XML files:
sqlf write-schema-to-xml -file=mysql-schema.xml
-driver-class=com.mysql.jdbc.Driver
-url=jdbc:mysql://localhost:3306/sqlfdb?user=username&password=password
sqlf write-data-to-xml -file=mysql-data.xml
-driver-class=com.mysql.jdbc.Driver
vFabric SQLFire User's Guide90
Managing Your Data in vFabric SQLFire