NuoDB Migrator Schema Command

The schema command generates a DDL SQL statement script to create database objects that have been defined in a schema of a source NuoDB or third-party database. The types of command line options that are available to the schema command include:

The output from running the schema command includes a data types mapping summary. For each source type, the summary shows the NuoDB type it was mapped to.

Syntax

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_and_filename_for_ddl_sql_file [option]...

The following is a list of schema command line options (option). Some of these NuoDB Migrator schema command line options are also available to be used with the NuoDB Migrator dump and/or load commands.

Options related to the source database connection
Option Required? schema option valid for dump and/or load command? Description
--source.driver=driver Yes - JDBC driver class name
--source.url=url Yes - Source database connection URL in the standard syntax: jdbc:subprotocol:subname

Optionally, include the source database username and password in the url specification. As shown in the following table, the syntax can vary according to the source database. The url examples show the specification of rootuser as the username and rootpw as the password.

MSSQL Server using JDBC driver--source.url=jdbc:sqlserver://192.168.0.6;user=rootuser;password=rootpw;database=tempdb
MSSQL Server using JTDS driver--source.url=jdbc:jtds:sqlserver://192.168.0.6:1433;user=rootuser;password=rootpw;databaseName=tempdb
MySQL driver--source.url=jdbc:mysql://localhost:3306/test?user=rootuser&password=rootpw
NuoDB driver--source.url=jdbc:com.nuodb://127.0.0.1/test?schema=TEST&user=rootuser&password=rootpw
Oracle driver--source.url=jdbc:oracle:thin:rootuser/rootpw@192.168.0.11:1521/XE
PostgreSQL--source.url=jdbc:postgresql://192.168.0.99:5432/test?user=rootuser&password=rootpw

It is allowed to specify the database username and password in both the source.url option and in the source.username and source.password options. If you do this and the specifications are not the same then there is a warning message but the schema command continues execution by using one of the specifications. Which specification the command uses depends on which source driver you are using:

Specification in source.url UsedSpecification in source.username and source.password Used

MSSQL Server using JTDS driver
MySQL driver
Oracle driver
PostgreSQL driver

MSSQL Server using JDBC driver
NuoDB driver
--source.username=username Yes - Source database username. Alternatively you can specify the user name in the source.url option.
--source.password=password No - Source database password. Alternatively you can specify the password in the source.url option.
--source.properties=properties No - Additional connection properties encoded as URL query string, e.g. "property1=value1&property2=value2..."
--source.catalog=catalog No - Default database catalog name to use
--source.schema=schema No - Default database schema name to use
--source.auto.commit={ true | false } No - If set to true, each individual statement is treated as a transaction and is automatically committed after it is executed. The default is false.
--source.transaction.isolation=value No -

Sets the transaction isolation level for the migration execution. A valid value can be either a symbolic name or an integer constant representing transaction isolation levels as defined in the JDBC java.sql.Connection class. Valid values include:

  • none or 0
  • read.uncommitted or 1
  • read.committed or 2
  • repeatable.read or 4
  • serializable or 8
  • vendor_dependent_level

NuoDB does not support all of the levels, only read.committed or 2, serializable or 8 and two additional levels that are not in the JDBC standard: write.commited or 5 and consistent read or 7. See Supported Transaction Isolation Levels for more information.


Options related to the target database connection. If provided, the DDL commands will be executed in the target database. The target parameters also define specifics regarding some data mapping definitions.
Option Required? schema option valid for dump and/or load command? Description
--target.url=url No - Target database connection URL in the format, jdbc:com.nuodb://host:port/database
--target.username=username No - Target database user name
--target.password=password No - Target database password
--target.properties=properties No - Additional connection properties encoded as URL query string, e.g. "property1=value1&property2=value2..."
--target.schema=schema No - Database schema name to use. If not provided, objects will be generated in the same schema as the source database schema.

Options related to DDL SQL script file output
Option Required? schema option valid for dump and/or load command? Description
--output.path=path/filename No - Saves DDL SQL script to a file specified by path/filename. If this parameter is omitted, the DDL is displayed to standard output.
--table.type=table_type[,table_type...] No dump and load Comma separated types of table objects (TABLE, SYSTEM TABLE, GLOBAL TEMPORARY, ALIAS, SYNONYM, etc. to process.
By default, only TABLE type is processed.
Views, triggers, functions, and stored procedures are not currently supported by the Migrator tool.
--meta.data.value={true | false| No dump and load

Includes or excludes specific metadata types from the generated output.

Valid values are:

  • table
  • primary.key
  • index
  • foreign.key
  • check
  • sequence
  • column.trigger

By default, all objects are generatred.

If you have tables with a sequence auto-generated column, you cannot use the --meta.data.sequence=false option when migrating those tables.

--script.type={drop | create} No load only

Type of DDL statement to be generated for each object.

Default is both DROP and CREATE statements for each object.

--group.scripts.by={table | meta.data} No load only Group generated DDL scripts. Group by table is the default. CREATE/ALTER statements for the table and all indexes, foreign keys, etc. will be generated before it moves to the next table CREATE statement.
--identifier.quoting={
minimal | 
always |
fully_qualified_class_name}
No load only

Identifier quoting policy name, implementing com.nuodb.migrator.jdbc.dialect.IdentifierQuoting. Valid options are:

  • minimal - policy quotes only SQL keywords and not valid identifiers (valid identifier matches [a-zA-Z0-9_]* pattern)
  • always - policy quotes all identifiers. This is the default behavior.
  • a fully qualified class name of a custom identifier quoting policy (i.e. com.somepackage.CustomIdentifierQuoting).
--identifier.normalizer={
noop |
standard |
lowercase |
uppercase |
fully_qualified_class_name }
No load only

Identifier transform to use, implementing com.nuodb.migrator.jdbc.dialect.IdentifierNormalizer. Valid options are:

  • noop - strategy returns unchanged identifier. This is the default behavior.
  • standard - strategy delegates identifier normalization to the dialect, which by defaults does no normalization (and looks similar to noop)
  • lowercase - transforms characters to lower case
  • uppercase - transforms characters to upper case
  • a fully qualified class name of a custom normalizer (i. e. com.somepackage.CustomIdentifierNormalizer)
--naming.strategy={
auto |
hash |
qualify}
No load only

Naming strategy to define how some object names are generated. This relates to objects of type index, foreign key, sequence, trigger. Valid options are:

  • auto - generates a qualified name for the specific object types (see qualify option). If the object name is greater than the maximum object name limit of 128 characters, it will generate a shorter name (see hash option). This is the default value.
  • qualify - this generates qualified name for the specific object types, depending on the object type. For example, index object names are generated as table name, followed by each column name defined by the index. Each are separated by an underscore.
  • hash - generates a shorter name for objects by appending a hash value to an index type prefix. For example, index object names are generated as IDX_some hash value.
  • a custom strategy may be defined by specifying a fully qualified class name (i.e. --naming.strategy=com.package.MyCustomName). This strategy allows for full control over object name definition for all types of objects: tables, columns, sequences, indexes, primary keys, foreign keys, check constraints, and triggers.
Options related to Custom Data Type Mappings
Option Required? schema option valid for dump and/or load command? Description
--type.code=type_code No load only This parameter identifies a type int he source database for which we want to specify a customized mapping. See type.code.
--type.size=type_size No load only Defines the size of a source data type. This parameter will allow you to map source types of different sizes to different target types. See also type.size, precision, scale.
--type.precision=type_precision No load only The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. This parameter will allow you to map source types of different precision to different target types. See also type.size, precision, scale.
--type.scale=type_scale No load only The number of fractional digits (digits after the decimal point) for numeric data types. This parameter will allow you to map source types of different scale to different target types. See also type.size, precision, scale.
--type.name=type_name No load only SQL type name template, i.e., DECIMAL(p[,s]) or VARCHAR(n), where p is precision, s is scale, and n is a maximum size. See type.name.
--config=path No load only Specifies a custom type mapping configuration file, where path is a full path and filename for the file.
This file is used to transform source database data types to target database data types.
If the file conf/nuodb-types.config is to be used, it is not necessary to use this parameter. It is only necessary to use the --use.nuodb.types=true command line parameter.
--use.nuodb.types={ true | false } No load only Instructs the migrator to transform source database types to the best matching NuoDB types, where CHAR, VARCHAR and CLOB source types will be rendered as STRING columns.
The conf/nuodb-types.config file is the source file for type overrides.
If some other file is to be used, the --config=path parameter must be used to define the file.
This option is false by default.
See Examples of NuoDB Migrator Schema Command, and use.nuodb.types below.
--use.explicit.defaults={ true | false } No load only Affects only MySQL implicit default values. This option allows MySQL implicit defaults to be moved over to the NuoDB schema explicitly. The option is false by default. See Examples of NuoDB Migrator Schema Command.