Overriding NuoDB Migrator Type Mappings

By default the migrator maintains default mappings from source database types to NuoDB types. In most circumstances, the mappings work fine; however, if you need a different mapping, the migrator allows you to override the default mapping from the command line or by using the nuodb-types.config file (see nuodb-types.config).

To override how source column types appear in a generated schema use the --type.code and --type.name command line parameters. To further specify how a source type will be rendered in the target database use the --type.size, --type.precision and --type.scale command line parameters.

type.code

The --type.code parameter declares a source database type to be overridden. It should be specified together with the --type.name parameter, so that each parameter data type specified using type.code also has a type.nametemplate. NuoDB Migrator accepts the following expressions for the type.code:

Description Example
A fully qualified JDBC type name --type.code=java.sql.Types.FLOAT
A SQL type name --type.code=FLOAT
A Java constant representing the SQL type as defined in java.sql.Types --type.code=6
where 6 is the value for FLOAT in java.sql.Types
Vendor-specific integer constant --type.code=-155
A database vendor can introduce non-standard types that are not listed in java.sql.Types. For example MSSQL Server has the DATETIMEOFFSET type, which is reported by the MSSQL JDBC driver for corresponding columns as having a -155 code. This type is not in the java.sql.Types list, so long and short names do not exist for it and only the vendor-specific code can be used.

type.name

The type.name parameter specifies a template with optional variables for precision, scale and size. For each overridden data type specified by the --type.code parameter, the --type.name template is used to produce the resulting type:

Parameter Result
--type.name=CLOB generates CLOB type
--type.name=VARCHAR(n) generates VARCHAR type where n is the source column size value
--type.name=NUMERIC(p[,s]) produces NUMERIC where p is precision and s is scale of a source database type

type.size, precision, scale

The optional parameters, --type.size, --type.precision and --type.scale, define the maximum size, precision or scale of a data type in the source database, and allow it to be rendered as a different data type in the target database depending on the source type.

Specifying Multiple Type Mappings

Multiple --type.* mappings can be specified from the command line. The minimum requirement is that both --type.code and --type.name must be specified. Other mapping parameters are optional.

In the following example, BIT type sizes 0 and 1 are mapped to BOOLEAN:

--type.code=BIT --type.name=BOOLEAN --type.size=0  --type.code=BIT --type.name=BOOLEAN --type.size=1

Note: The example above is already the default in NuoDB.

use.nuodb.types

The --use.nuodb.types=true option loads a set of type overrides from the conf/nuodb-types.config file and instructs the migrator to remap source CHAR, VARCHAR and CLOB column types to STRING for every matching source column from a source database during schema generation.

When --use.nuodb.types=true the migrator transforms source database types to the best matching NuoDB types as defined in the nuodb-types.config file. The --use.nuodb.types option is false by default.

nuodb-types.config

Depending on how the NuoDB Migrator tool was installed, the nuodb-types.config file can be found in one of the following locations:

Migrator Installation Type Location of nuodb-types.config
NuoDB Product Installation NUODB_HOME/tools/migrator/conf/nuodb-types.config
Github Source Build NUODB_MIGRATOR_DIR/migration-tools/assembly/target/nuodb-migrator/conf/nuodb-types.config

The following block contains the default content of the nuodb-types.config file. By default, if you specify --use.nuodb.types=true, CHAR, VARCHAR, and CLOB column types are mapped to STRING.

This file can be modified and type mappings may be changed, removed, or added.

# these rules instruct the migrator to render CHAR, VARCHAR & CLOB source database types as STRING for
# every matching source column from a source database during schema generation step
# java.sql.Types.CHAR is rendered as STRING
--type.code=java.sql.Types.CHAR
--type.name=STRING
# java.sql.Types.VARCHAR is rendered as STRING
--type.code=java.sql.Types.VARCHAR
--type.name=STRING
# java.sql.Types.CLOB is rendered as STRING
--type.code=java.sql.Types.CLOB
--type.name=STRING