Re: oracle database model dangerously broken

[Craig L Russell]

Hi Thomas,

On Feb 4, 2006, at 9:39 AM, Thomas Dudziak wrote:

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).

Just a couple of pennies thought,

Craig

I think that it would be more useful if DdlUtils distinguished between the type actually stored in the database versus the mapping from the abstract type to the actual type. Specifically, I'd like to see it be able to know the difference between a column defined as LONG RAW and BLOB, since Oracle treats them as different. If the user wants to define a real column type they should be able to use either LONG RAW or BLOB. If the user just wants an abstract column type LONGVARBINARY, then I have no problem with DdlUtils creating a BLOB by default (if the user doesn't override the generated column type with a specific type). I haven't looked closely enough into the implications of this, but I have worked with column types on many projects and it is generally useful to separate the actual column type from the generated column type based on an abstract type. Another example is the abstract type String with a length. Databases have different names for various lengths, e.g. VARCHAR, VARCHAR2, CLOB. So the type for a String-6000 will be different for different databases. But the actual column type should always be available to the user of the API.

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



* 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.setDataSource(ods);
// the table was created without delimiters, so we should use DdlUtils
in the same way
platform.getPlatformInfo().setUseDelimitedIdentifiers(false);



dumpDb(db);

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


Tom

The crucial thing is that this may conflict with the workflow that you've tried: Platform platform = PlatformFactory.createNewPlatformInstance ("Oracle10"); Database db = platform.readModelFromDatabase("test", null, schema, null);

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature