db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Dudziak <tom...@gmail.com>
Subject Re: oracle database model dangerously broken
Date Sat, 04 Feb 2006 17:39:50 GMT
I've investigated this, and in fact DdlUtils is behaving can be
expected. The reason is this:

DdlUtils can only fully support schemas in the database that is has
generated. The reason is simply that databases offer a whole lot more
than what DdlUtils can cover.
One aspect of this is that for most if not all databases, DdlUtils
actively supports only a subset of the native types that the database
has to offer. In the case of Oracle, (LONG) RAW is not one of the
supported types (mainly because Oracle discourages from using them in
favor of BLOB).
So while DdlUtils is able to read a table with a LONG RAW column
(which the JDBC driver reports as LONGVARBINARY), DdlUtils will treat
LONGVARBINARY as BLOB because Oracle has no dedicated LONGVARBINARY
type.
That is, if you create a database via DdlUtils and specify
LONGVARBINARY, you'll get a BLOB in the database. Now when you read
this back, the read column will be of type BLOB. And DdlUtils now
ensures that the column won't be changed when altering something other
in the database.

This may sound a bit complicated, but in the end this serves to
support the following workflow:

* create db via DdlUtils
* change something in the db model via DdlUtils => DdlUtils ensures
that as few changes as possible will be made to the db

The crucial thing is that this may conflict with the workflow that you've tried:

* create db outside of DdlUtils
* read the model via DdlUtils
* change something in the db model via DdlUtils

DdlUtils can only fully support one of these workflows, and IMO the
first one is more useful, so that's what DdlUtils focuses on. We're
trying hard to use the native types that the database vendors suggest,
so that even the second workflow works most of the time, but only if
the tables use these suggested types. E.g. if you'd use BLOB instead
of LONG RAW, DdlUtils would not try to change the column.

Btw, for Oracle it is advisable to specify the platform manually. E.g.
I've changed your code to:

OracleDataSource ods = new OracleDataSource();

ods.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
ods.setUser("my_user");
ods.setPassword("my_password");

// note the change to BLOB here
String sql = "CREATE TABLE MY_TABLE( " +
               "prop_name VARCHAR2(200) NOT NULL PRIMARY KEY, " +
               "prop_value VARCHAR2(200), prop_value_ext BLOB)";

Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();

stmt.execute(sql);
stmt.close();
conn.close();

String schema = "MY_SCHEMA";

// I've tested against an Oracle 10 database
Platform platform = PlatformFactory.createNewPlatformInstance("Oracle10");

platform.setDataSource(ods);
// the table was created without delimiters, so we should use DdlUtils
in the same way
platform.getPlatformInfo().setUseDelimitedIdentifiers(false);

Database db = platform.readModelFromDatabase("test", null, schema, null);

dumpDb(db);

// note the new arguments for catalog, schema, table types
System.out.println(platform.getAlterTablesSql(null, schema, null, db,
true, true, true));


Tom

Mime
View raw message