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:

  • Source database options
    Specify the details for the source NuoDB or third-party database, such as database connection, login credentials and transaction properties.

  • Target database options
    Specify the details for the target NuoDB database, such as database connection, login credentials and schema information. If target database options are provided then the DDL SQL script is executed in the target NuoDB database and it creates the database objects.

  • Output script definition and formatting
    Specify filters for which objects to generate DDL SQL object create statements. You can also specify which objects to not include in DDL SQL statement creation. For example, specify the type of DDL commands generated for each object, such as DROP and/or CREATE statements and define syntax such as qualifying schema name and using quotes.

  • Data type mapping options
    Override the default data types that will be generated.

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

dump only

JDBC driver class name

--source.url=url

Yes

dump only

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 Specification in source.username and source.password

MSSQL Server using JTDS driver MySQL driver Oracle driver PostgreSQL driver

MSSQL Server using JDBC driver NuoDB driver

--source.username= username

Yes

dump only

Source database username. Alternatively you can specify the user name in the source.url option.

--source.password= password

No

dump only

Source database password. Alternatively you can specify the password in the source.url option.

--source.properties= properties

No

dump only

Additional connection properties encoded as URL query string, e.g. “property1=value1&property2=value2…​”

--source.catalog=catalog

No

dump only

Default database catalog name to use

--source.schema=schema

No

dump only

Default database schema name to use

--source.auto.commit= { true | false }

No

dump only

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

dump only

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. 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 can also define specifics regarding data mapping definitions.

Option

Required?

schema option valid for dump and/or load command

Description

--target.url=url

No

load only

Target database connection URL in the format, jdbc:com.nuodb://host:port/database

--target.username= username

No

load only

Target database user name

--target.password= password

No

load only

Target database password

--target.properties= properties

No

load only

Additional connection properties encoded as URL query string, e.g. “property1=value1&property2=value2…​”

--target.schema=schema

No

load only

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 generated. 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 lowercase

  • uppercase - transforms characters to uppercase

  • 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 in the 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 , 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 Example 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 Example of NuoDB Migrator Schema Command.