db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: oracle database model dangerously broken
Date Mon, 06 Feb 2006 18:19:09 GMT
Hi Tom,

thanks for that info. I understand your explaination, but doesn't this 
significantly limit the use of DDLUtils?
If DDLUtils can make uncalled for changes to any database that has been 
created, or modified, by anything other than DDLUtils, then that's quite 
a high risk to accept?

Tim



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