ms-sql-server.conf

/**
 * This is a template with default settings for migrating a Microsoft SQL Server database to Postgres.
 *
 * It defines the information-schema query for retrieving the names of the tables that should be migrated, the type
 * mappings, e.g. `datetime` => `timestamptz`, `image` => `bytea`, etc., and other settings.
 */
{
    /**
     * Default migration project name
     */
    "name" : "ms-sql-server",

    /**
     * The information schema query determines which tables and/or views will be migrated.
     *
     * In MS SQL Server, the user must have the 'VIEW DEFINITION' permission in order to
     * view column defaults and computed values.  You can add it with the following command
     * in the source database:
     *
     *    GRANT VIEW DEFINITION TO <user>;
     */
    "information_schema" : {
        "query" : "
            SELECT   C.TABLE_SCHEMA
                    ,C.TABLE_NAME
                    ,C.COLUMN_NAME
                    ,C.ORDINAL_POSITION
                    ,C.IS_NULLABLE
                    ,C.DATA_TYPE
                    ,C.CHARACTER_MAXIMUM_LENGTH
                    ,C.NUMERIC_PRECISION
                    ,C.COLUMN_DEFAULT
                    ,COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY
                    ,COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsComputed') AS IS_COMPUTED
--                    ,COALESCE(OBJECTPROPERTY(OBJECT_ID(CCU.CONSTRAINT_SCHEMA + '.' + CCU.CONSTRAINT_NAME), 'IsPrimaryKey'), 0) AS IS_PRIMARY_KEY
                    ,SCC.DEFINITION AS COLUMN_DEFINITION
            FROM    INFORMATION_SCHEMA.COLUMNS C
                JOIN INFORMATION_SCHEMA.TABLES T ON
                        T.TABLE_SCHEMA      = C.TABLE_SCHEMA
                    AND T.TABLE_NAME        = C.TABLE_NAME
                    AND T.TABLE_CATALOG     = C.TABLE_CATALOG
                LEFT JOIN sys.columns SC ON
                        SC.object_id        = OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME)
                    AND SC.name             = C.COLUMN_NAME
                LEFT JOIN sys.computed_columns SCC ON
                        SCC.object_id       = OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME)
                    AND SCC.name            = C.COLUMN_NAME
            /*
                LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU on
                        CCU.TABLE_SCHEMA    = C.TABLE_SCHEMA
                    AND CCU.TABLE_NAME      = C.TABLE_NAME
                    AND CCU.TABLE_CATALOG   = C.TABLE_CATALOG
                    AND CCU.COLUMN_NAME     = C.COLUMN_NAME     --*/
            WHERE    T.TABLE_CATALOG        = '%information_schema.database_name%'
                AND T.TABLE_TYPE           = 'BASE TABLE'
            --  AND T.TABLE_SCHEMA NOT LIKE 'exclude-pattern'
            --  AND T.TABLE_NAME   NOT LIKE 'exclude-pattern'
            --  AND C.COLUMN_NAME  NOT LIKE 'exclude-pattern'
                AND T.TABLE_NAME NOT IN ('dtproperties')
            ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, C.ORDINAL_POSITION;
            "
    },

    /**
     * DDL (create objects) command settings
     */
    "ddl" : {
        /**
         * SQL type mapping for DDL, e.g. map ntext -> text
         */
        "sql_type_mapping" : {
        //  "bigint"                : "",
            "bigint_auto_increment" : "bigserial",
        //  "bigint_identity"       : "bigint generated always as identity",
            "bigint_identity"       : "bigint generated by default as identity",
            "bit"                   : "boolean",
            "char"                  : "varchar",
        //  "date"                  : "",
            "datetime"              : "timestamptz",
            "float"                 : "real",
            "geography"             : "text",
            "hierarchyid"           : "text",         // TODO: ltree ?
            "image"                 : "bytea",
        //  "int"                   : "",
            "int_auto_increment"    : "serial",
        //  "int_identity"          : "int generated always as identity",
            "int_identity"          : "int generated by default as identity",
            "money"                 : "numeric",
            "ntext"                 : "text",
            "nvarchar"              : "varchar",
            "nvarchar_invalid"      : "text",
        //  "real"                  : ""
            "smalldatetime"         : "timestamptz",
            "smallint"              : "int",
            "smallint_identity"     : "int generated by default as identity",
            "smallmoney"            : "numeric",
        //  "text"                  : "",
            "tinyint"               : "int",
            "tinyint_identity"      : "int generated by default as identity",
            "uniqueidentifier"      : "char(36)",
            "varbinary"             : "bytea",
        //  "varchar"               : "text",
            "varchar_invalid"       : "text",
            "xml"                   : "text"          // TODO: convert to xml?
        },

        /**
        * Regular Expressions that will be used to convert defaults, e.g. from getdate() to current_timestamp
        * Only matches will be used in DDL without being commented out. Slash (\) characters must be escaped.
        */
        "column_default_replace" : {
            "^\\(+(0|1)\\)+$"              : "$1",                  // (0), ((0)), (1), ((1))
            "^\\(+(\\d+\\.?(\\d+)?)\\)+$"  : "$1",                  // ('123'), ('123.45')
            "^\\(+('.*?')\\)+$"            : "$1",                  // ('abc'), ('Abc Def')
            "^\\(+(getdate\\(\\)\\))+$"    : "current_timestamp",   // (getdate())
            "^\\(+(newid\\(\\)\\))+$"      : "gen_random_uuid()"    // (newid())
        }
    },

    /**
     * DML command settings (copy data)
     */
    "dml" : {

        /**
         * rollback - roll back the current transaction, default
         * // TODO: continue - ignore the error
         * // TODO: abort    - aborts the whole operation
         */
        "on_error" : "rollback",

        /**
            JDBC type mapping used for DML, e.g. longnvarchar -> longvarchar
            see https://docs.oracle.com/javase/8/docs/api/java/sql/JDBCType.html
            and https://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.ARRAY

            TODO: Microsoft changed SpatialLocation type from VARBINARY (-3 in driver version 6.4) to Geography (-158 driver version 7.x)
        */
        "jdbc_type_mapping" : {
            "longnvarchar"  : "longvarchar",      // MSSQL ntext
            "nchar"         : "char",
            "nclob"         : "clob",
            "nvarchar"      : "varchar"
        },

        /**
         * Wrap columns with prefix and suffix in case a column name is a keyword, e.g. [key] or has space
         */
        "source_column_quote_prefix" : "[",
        "source_column_quote_suffix" : "]"
    }
}