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 
        -url=jdbc:mysql://localhost:3306/sqlfdb?user=username&password=password
  7. Use the sqlf write-data-to-db command and specify both the data XML file and the schema XML file to import the data to SQLFire:
    sqlf write-data-to-db -files=mysql-data.xml -schema-files=mysql-schema.xml 
        -client-bind-address=localhost -client-port=1527
  8. If necessary, manually add triggers, views, and Java stored procedures in the SQLFire database.