Generating DDL SQL Statement Scripts for Migrated Data

The NuoDB Migrator tool also supports a schema command that can be used to generate DDL SQL statement scripts for dropping and creating one or more objects from a source database. This can also include all objects defined in the source schema. Use the following command to generate DDL SQL statements to drop and create all objects defined in a source database schema. For MySQL, replace --source.schema=my_schema with --source.catalog=my_schema.

nuodb-migrator schema                            \
        --source.driver=source_driver_class_name \
        --source.url=jdbc:source_jdbc_url        \
        --source.schema=my_schema                \
        --source.username=userid                 \
        --source.password=passwd                 \
        --output.path=path_for_ddl_sql_file

Similar to how the NuoDB Migrator load command creates objects in a target NuoDB database, this generated DDL SQL will map source database data types to NuoDB supported data types. The create object commands will be converted from source database specific SQL syntax to NuoDB SQL supported syntax.

There are command line options that can specify the types of objects that the generated DDL SQL statements will create. For example, if the NuoDB Migrator tool was used to dump and load only table and sequence objects and data, this schema command would be used to generate DDL SQL statement scripts to create the remaining objects in the target NuoDB database. The following schema command would generate the DDL for primary keys, foreign keys, and other indexes:

nuodb-migrator schema                            \
        --source.driver=source_driver_class_name \
        --source.url=jdbc:source_jdbc_url        \
        --source.schema=my_schema                \
        --source.username=userid                 \
        --source.password=passwd                 \
        --output.path=path_for_ddl_sql_file      \
        --meta.data.table=false                  \
        --meta.data.check=false                  \
        --meta.data.identity=false               \
        --meta.data.column.trigger=false         \
        --meta.data.primary.key=true             \
        --meta.data.foreign.key=true             \
        --meta.data.index=true

This DDL SQL statement script can be executed by providing the target NuoDB JDBC connection parameters as follows:

nuodb-migrator schema                                        \
        --source.driver=source_driver_class_name             \
        --source.url=jdbc:source_jdbc_url                    \
        --source.schema=my_schema                            \
        --source.username=userid                             \
        --source.password=passwd                             \
        --output.path=path_for_ddl_sql_file                  \
        --target.url=jdbc:com.nuodb://host/database_name     \
        --target.username=userid                             \
        --target.password=passwd

This DDL SQL statement script can also be executed by using the NuoDB SQL command nuosql as follows:

$ nuosql [.var]__database_name__@[.var]__host__  --user userid --password passwd --schema schema \
    --file path_for_ddl_sql_file > output.log

Standard output and error messages display to the screen but can be redirected to an output log file.