db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: oracle database model dangerously broken
Date Mon, 06 Feb 2006 21:12:48 GMT
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).

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.

Just a couple of pennies thought,

Craig

> 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

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


Mime
View raw message