6
\$\begingroup\$

A project that I have been working on required data to be transferred from an Oracle database into MySQL. The process I devised for that transfer involved a query (included below) on the Oracle source database to make it dump out the schema for the relevant tables to be replicated, in the form of CREATE TABLE statements to be executed in the MySQL destination database.

I'm also exporting the column names, parameter placeholders (a string of ?, ?, ?, …, ?, with as many symbols as columns), and mysqli type codes in preparation for the next step, which is to allow each row to be INSERTed using mysqli.

I'm not particularly concerned about preserving constraints and foreign key relationships. Just enforcing the primary keys should suffice.

WITH constraints AS (
    SELECT cols.*, cons.constraint_type
        FROM all_constraints cons
            INNER JOIN all_cons_columns cols
                ON cols.constraint_name = cons.constraint_name
                AND cols.owner = cons.owner
        WHERE
            cons.status = 'ENABLED'
            AND cons.constraint_type IN ('P', 'U')
        ORDER BY cons.table_name, cols.position
), relevant_schema AS (
    SELECT *
        FROM all_tab_cols
), relevant_schema_mysql_types AS (
    SELECT relevant_schema.*
         , CASE data_type
             WHEN 'NUMBER'       THEN 'NUMERIC(' || COALESCE(data_precision, 38) || ',' || COALESCE(data_scale, 0) || ')'
             WHEN 'VARCHAR2'     THEN 'VARCHAR(' || data_length || ')'
             WHEN 'CHAR'         THEN 'CHAR(' || data_length || ')'
             WHEN 'CLOB'         THEN 'BLOB'
             WHEN 'TIMESTAMP(6)' THEN 'TIMESTAMP'
             ELSE                data_type
           END
           ||
           CASE nullable
             WHEN 'N'            THEN ' NOT NULL'
           END AS mysql_type
        , CASE data_type
            WHEN 'BLOB'          THEN 'b'
            WHEN 'CLOB'          THEN 'b'
            ELSE                      's'
          END AS mysqli_placeholder_type
        FROM relevant_schema
), mysql_constraint_defs AS (
    SELECT owner
         , table_name
         , constraint_type
         , constraint_name
         , ', CONSTRAINT ' || constraint_name
           || CASE constraint_type
                 WHEN 'P' THEN ' PRIMARY KEY'
                 WHEN 'U' THEN ' UNIQUE'
              END
           || ' ('
           ||    LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY position)
           || ')'
           || CHR(10) AS constraint_def
        FROM constraints
        GROUP BY owner, table_name, constraint_type, constraint_name
), mysql_col_defs AS (
    SELECT owner
         , table_name
         -- , LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY column_id) AS col_names
         , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ', ') ORDER BY column_id),
                         '/E/text()').getCLOBVal(), ', ') AS col_names
         , LISTAGG('?', ', ') WITHIN GROUP (ORDER BY column_id) AS col_placeholders
         , LISTAGG(mysqli_placeholder_type) WITHIN GROUP (ORDER BY column_id) AS mysqli_placeholder_types
         --, LISTAGG('`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') WITHIN GROUP (ORDER BY column_id) || CHR(10) AS col_defs
         , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') ORDER BY column_id),
                         '/E/text()').getCLOBVal(), ', ') AS col_defs
        FROM relevant_schema_mysql_types
        GROUP BY owner, table_name
), mysql_table_constraints AS (
    SELECT owner
         , table_name
         , LISTAGG(constraint_def, CHR(10)) WITHIN GROUP (ORDER BY constraint_type, constraint_name) AS constraints
        FROM mysql_constraint_defs
        GROUP BY owner, table_name
), mysql_defs AS (
    SELECT mysql_col_defs.owner
         , mysql_col_defs.table_name
         , mysql_col_defs.col_names
         , mysql_col_defs.col_placeholders
         , mysql_col_defs.mysqli_placeholder_types
         , mysql_col_defs.col_defs
         , mysql_table_constraints.constraints
        FROM mysql_col_defs
            LEFT OUTER JOIN mysql_table_constraints
                ON mysql_table_constraints.owner = mysql_col_defs.owner
                AND mysql_table_constraints.table_name = mysql_col_defs.table_name
)
SELECT table_name
    ,  col_names
    ,  col_placeholders
    ,  mysqli_placeholder_types
    , 'CREATE TABLE IF NOT EXISTS `' || table_name || '`' || CHR(10)
    || '( '
    ||    col_defs
    ||    constraints
    || ') ENGINE=INNODB;' AS create_table_stmt
    FROM mysql_defs
    WHERE
        owner = …
        AND table_name IN (…)
    ORDER BY
        owner,
        table_name

Two of the LISTAGG() calls resulted in strings that exceeded Oracle's string length limit. I've used an ugly workaround involving XML serialization to generate CLOBs instead.

What would you do differently?

\$\endgroup\$
  • \$\begingroup\$ Is "not convert to MySQL" a valid answer? :p \$\endgroup\$ – cimmanon Oct 29 '14 at 18:02
  • \$\begingroup\$ @cimmanon I wish it were, but the replication is necessary for integrating existing systems. \$\endgroup\$ – 200_success Oct 29 '14 at 18:03
4
\$\begingroup\$

There are three general comments to make:

  1. don't select columns you don't need (the select * is bothersome)
  2. you should restrict the data much further before you start. The Oracle schema views are slow, and you should limit your schema selection in the first CTE, and then join that to the views for subsequent selections.
  3. order-by clauses in the CTE are often unnecessary.

Originally I said the following, but I am going to retract that. It appears that, in Oracle, this does not help performance much at all.... in fact, it makes it worse.

Your relevant_schema and relevant_schema_mysql_types should be merged in to one CTE, the second is a pure extension of the first, and there's no other value add.

I messed about with your query, and came up with:

WITH relevant_schema AS (
    SELECT
          atc.owner
        , atc.table_name
        , atc.column_name
        , column_id

        , CASE data_type
             WHEN 'NUMBER'       THEN 'NUMERIC(' || COALESCE(data_precision, 38) || ',' || COALESCE(data_scale, 0) || ')'
             WHEN 'VARCHAR2'     THEN 'VARCHAR(' || data_length || ')'
             WHEN 'CHAR'         THEN 'CHAR(' || data_length || ')'
             WHEN 'CLOB'         THEN 'BLOB'
             WHEN 'TIMESTAMP(6)' THEN 'TIMESTAMP'
             ELSE                data_type
           END
           ||
           CASE nullable
             WHEN 'N'            THEN ' NOT NULL'
           END AS mysql_type

        , CASE data_type
            WHEN 'BLOB'          THEN 'b'
            WHEN 'CLOB'          THEN 'b'
            ELSE                      's'
          END AS mysqli_placeholder_type
    FROM all_tab_cols atc
)
, mysql_col_defs AS (
    SELECT owner
         , table_name
         -- , LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY column_id) AS col_names
         , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ', ') ORDER BY column_id),
                         '/E/text()').getCLOBVal(), ', ') AS col_names
         , LISTAGG('?', ', ') WITHIN GROUP (ORDER BY column_id) AS col_placeholders
         , LISTAGG(mysqli_placeholder_type) WITHIN GROUP (ORDER BY column_id) AS mysqli_placeholder_types
         --, LISTAGG('`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') WITHIN GROUP (ORDER BY column_id) || CHR(10) AS col_defs
         , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') ORDER BY column_id),
                         '/E/text()').getCLOBVal(), ', ') AS col_defs
        FROM relevant_schema
        GROUP BY owner, table_name
)
, constraints AS (
    SELECT cons.owner as owner,
           cons.table_name as table_name,
           cols.column_name,
           cols.position,
           cons.constraint_type,
           cons.constraint_name
    FROM all_constraints cons             
         INNER JOIN all_cons_columns cols
                ON cols.constraint_name = cons.constraint_name
               AND cols.owner = cons.owner
               AND cols.position is not null
    WHERE   cons.status = 'ENABLED'
        AND cons.constraint_type IN ('P', 'U')

)
, mysql_constraint_defs AS (
    SELECT owner
         , table_name
         , constraint_type
         , constraint_name
         , ', CONSTRAINT ' || constraint_name
           || CASE constraint_type
                 WHEN 'P' THEN ' PRIMARY KEY'
                 WHEN 'U' THEN ' UNIQUE'
              END
           || ' ('
           ||    LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY position)
           || ')'
           || CHR(10) AS constraint_def
        FROM constraints
        GROUP BY owner, table_name, constraint_type, constraint_name
)
, mysql_table_constraints AS (
    SELECT owner
         , table_name
         , LISTAGG(constraint_def, CHR(10)) WITHIN GROUP (ORDER BY constraint_type, constraint_name) AS constraints
        FROM mysql_constraint_defs
        GROUP BY owner, table_name
)
, mysql_defs AS (
    SELECT mysql_col_defs.owner
         , mysql_col_defs.table_name
         , mysql_col_defs.col_names
         , mysql_col_defs.col_placeholders
         , mysql_col_defs.mysqli_placeholder_types
         , mysql_col_defs.col_defs
         , mysql_table_constraints.constraints
        FROM mysql_col_defs
            LEFT OUTER JOIN mysql_table_constraints
                ON mysql_table_constraints.owner = mysql_col_defs.owner
                AND mysql_table_constraints.table_name = mysql_col_defs.table_name
)

SELECT table_name
    ,  col_names
    ,  col_placeholders
    ,  mysqli_placeholder_types
    , 'CREATE TABLE IF NOT EXISTS `' || table_name || '`' || CHR(10)
    || '( '
    ||    col_defs
    ||    constraints
    || ') ENGINE=INNODB;' AS create_table_stmt
    FROM mysql_defs
    where table_name in ('HEADER', 'DETAIL')
      and owner = (select user from dual)
    ORDER BY
        owner,
        table_name

Note that I put the column level items first, followed by the constraints. I removed the one redundant CTE. I put the colum level queries first because that's the order of building the create-table and I feel that makes the query flow better.

I got the query working in the following SQLFiddle.

As for the XML hack, this is a common hack to need when confronted by your circumstances. The right solution is to remove this logic entirely from the query.... and process the data in either a cursor, or in a third-party application. SQL is not designed to solve the problem you are throwing at it, and you are thus running in to its brittle components.

Pull the data in to a tool like Java, python, whatever, and process it better that way.

\$\endgroup\$
  • \$\begingroup\$ Updated with working SQLFiddle, and revised suggestion about CTE. \$\endgroup\$ – rolfl Oct 27 '14 at 20:46
3
\$\begingroup\$

One thing you'll need to be aware of is that MySQL can't guarantee uniqueness on columns over a certain size, depending on the engine type. Attempting to create a unique constraint on a column that's bigger than the limit will cause MySQL to throw an error. You can specify a length when defining the constraint like so:

UNIQUE (`column_name`(100))

Not so important on numeric type columns, but very relevant to larger types like TEXT or even VARCHAR.

See also: https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.