tungsten-replicator: Oracle to MySQL schema converter using ddlscan [PATCH]

The last couple of weeks we’ve been playing around with tungsten for the purpose of a full scale Oracle to MySQL replication. The newest provision feature in 2.2.1 was especially helpful – great work!

We now have a full scale replication running, and the latest “minor” obstacle we met was converting existing scheme to MySQL scheme as automatically as possible. Since this might be updated more often than first thought, we were looking for an automatic approach. Thanks to ddlscan and its features – it solved the issue for us.

The only problem with ddlscan was the inability to convert decimal precision to MySQL, hence we made som modification to the database layer of tungsten to accommodate our needs. What we essentially lacked, was the ability to convert NUMBER(14,2) to DECIMAL(14,2), and be sure that NUMBER(10, 0) could be converted to INT(11).

This is the first draft of a patch, which adds decimal precision to the Column-object, and adds the appropriate information in AbstractDatabase. I also attached a patch to the ddl-oracle-mysql Velocity-script to illustrate the use of the new feature.

See attached patch:


diff '--exclude=.svn' '--exclude=build' -ur replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm sources/replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm
--- replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm	2014-05-06 13:55:14.000000000 +0200
+++ sources/replicator/samples/extensions/velocity/includes/ddl-oracle-mysql-table.vm	2014-05-06 15:24:26.000000000 +0200
@@ -37,7 +37,7 @@
 #else
 DECIMAL($col.getLength())##
 #end
- /* $col.getTypeDescription()($col.getLength(), ?) */##
+ /* $col.getTypeDescription()($col.getLength(), $col.getDecimalPrecision()) */##
 #elseif ( $col.getTypeDescription().startsWith("FLOAT") )
 $col.getTypeDescription()($col.getLength())##
 #if( $col.getLength() > 53 )
@@ -67,4 +67,4 @@
 #if( $colReserved ) /* WARN: changed column name because of reserved word ($col.getName()) */#end##
 #if( $foreach.hasNext ),
 #end
-#end
\ No newline at end of file
+#end
diff '--exclude=.svn' '--exclude=build' -ur replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java sources/replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java
--- replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java	2014-05-06 13:55:12.000000000 +0200
+++ sources/replicator/src/java/com/continuent/tungsten/replicator/database/AbstractDatabase.java	2014-05-06 15:19:59.000000000 +0200
@@ -986,12 +986,14 @@
                 String typeDesc = rsc.getString("TYPE_NAME").toUpperCase();
                 // Issue 798. Mimicking MySQLApplier.
                 boolean isSigned = !typeDesc.contains("UNSIGNED");
+		int decimalPrec = rsc.getInt("DECIMAL_DIGITS");
 
                 Column column = new Column(colName, colType, colLength,
                         isNotNull, valueString);
                 column.setPosition(rsc.getInt("ORDINAL_POSITION"));
                 column.setTypeDescription(typeDesc);
                 column.setSigned(isSigned);
+		column.setDecimalPrecision(decimalPrec);
                 table.AddColumn(column);
                 cm.put(column.getName(), column);
             }
diff '--exclude=.svn' '--exclude=build' -ur replicator/src/java/com/continuent/tungsten/replicator/database/Column.java sources/replicator/src/java/com/continuent/tungsten/replicator/database/Column.java
--- replicator/src/java/com/continuent/tungsten/replicator/database/Column.java	2014-05-06 13:55:12.000000000 +0200
+++ sources/replicator/src/java/com/continuent/tungsten/replicator/database/Column.java	2014-05-06 14:45:24.000000000 +0200
@@ -45,6 +45,7 @@
                                                        // NULL column
     Serializable              value;
     int                       valueInputStreamLength;
+    int decimalPrecision;
     private int               position;
     private boolean           blob;
     private String            typeDescription;
@@ -109,6 +110,16 @@
         return this.type;
     }
 
+    public int getDecimalPrecision()
+    {
+	return this.decimalPrecision;
+    }
+
+    public void setDecimalPrecision(int precision)
+    {
+	this.decimalPrecision = precision;
+    }
+
     public long getLength()
     {
         return this.length;