Generating DDL SQL Statement Scripts for Migrated Data

The nuodb-migrator command line tool also supports a nuodb-migrator 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 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

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, sequence objects, and data, nuodb-migrator schema 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 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.