Mailing-List: contact ddlutils-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: ddlutils-user@db.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Mon, 06 Feb 2006 13:12:48 -0800
From: Craig L Russell <Craig.Russell@Sun.COM>
Subject: Re: oracle database model dangerously broken
In-reply-to: <224f32340602040939o46d31571g99ebb21ff3e1c559@mail.gmail.com>
Sender: Craig.Russell@Sun.COM
To: ddlutils-user@db.apache.org
Message-id: <CA55DD5A-6FD6-499F-B512-04483B63837E@Sun.COM>
MIME-version: 1.0
Content-type: multipart/signed; protocol="application/pkcs7-signature";
 boundary=Apple-Mail-255--108104609; micalg=sha1
References: <43DF5461.9010600@informaticsmatters.com>
 <224f32340601310433ubc8c358rd7c95f621ac9e5ab@mail.gmail.com>
 <43DF6DD8.1000104@informaticsmatters.com>
 <224f32340601310621k3a90a94dhbf410d5a85e31c83@mail.gmail.com>
 <43DF8C69.70406@informaticsmatters.com>
 <224f32340601310912j75dc45admfd439100147ecdbb@mail.gmail.com>
 <43DF9DC5.6030909@informaticsmatters.com>
 <224f32340602040939o46d31571g99ebb21ff3e1c559@mail.gmail.com>

--Apple-Mail-255--108104609
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed

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!


--Apple-Mail-255--108104609
Content-Transfer-Encoding: base64
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Disposition: attachment;
	filename=smime.p7s

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw
ggJyoAMCAQICEB7Y7fLHmKoeHTUdbtYrvpAwDQYJKoZIhvcNAQEEBQAwYjELMAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1MTIxNTIxNDc0NFoXDTA2MTIxNTIxNDc0
NFowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny
YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC
ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMXXgEAm88nu8cFbxXSWqTq+GaYpCx/0QSom
8kBbVxpJIURuO4ErLDupeXu6y9+5e7ZKLbeSQE5xbfYPcQR/IhqmYKy5YqRcuzPXREBj2zKEkZph
pNXWpHgMdx9W8dq0Cu2i9Ux/S6c2MuEHrP6gfGGll+b/mzLoO280QHTuE4pcpKntRnwZdGxQ/5l8
IL+eLP+jpJAbYW9C+KNKofZtS6V6R0uzlqTOsEdZvwxZQ4mmPgHoz1+Gjwme/PC5sKvF09MaJDiI
pj9SvZ4CTCgcDZV78J086YwlVbMC0VQotjhu1p42lr8CS33IXLz3OWNrDETCAepah/Dgw2ZZApQ9
9L0CAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC
MAAwDQYJKoZIhvcNAQEEBQADgYEAKdIkgAWCg2Bi7ocnstfJA4iymTRI2/L4oQx9zvllM9bNJ2cR
cecJIx3HuoHbhPvemh1GExEPgHU+dXSxDmD0BEmPnhSReKCURyslnbMphPZ5kR6USzQFrRa+v0ii
J+SBO9VQYTQWT+xEjmRLM76MfkBFw3IOC9CUkRoYZ88pOoUwggM/MIICqKADAgECAgENMA0GCSqG
SIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH
EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp
Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h
aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw
NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3Rl
IENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWls
IElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUE
cJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/Ef
kTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMB
AAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3Js
LnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYD
VR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GB
AEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+
hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC
3CEZNd4ksdMdRv9dX2VPMYIDEDCCAwwCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh
d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt
YWlsIElzc3VpbmcgQ0ECEB7Y7fLHmKoeHTUdbtYrvpAwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN
AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwMjA2MjExMjQ4WjAjBgkqhkiG9w0B
CQQxFgQUzDWelDwk9JKHZDutoHm+33XaE8EwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC
WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe2O3yx5iqHh01HW7WK76QMIGHBgsqhkiG
9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ
dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe
2O3yx5iqHh01HW7WK76QMA0GCSqGSIb3DQEBAQUABIIBABmOeMHyqZEih6Psgr11H59N2cFKBsI0
ZinLQLW19KVjNrKcZNwgp8bg9ygC5qnGLSvTxJsPpycZ4DmjCkMPaTjXyResqEvx/wYJMZQRdm7L
JBxmoaUCfgtSqvg8O+heegQ64vPLMbpUeVEwENPfF7iK31ZgVd7tWKNXBD76V/pWiFmR2Igtosx8
kNabbPzvwVuZI/DPdnEdcci93xRHW1Gr+HZMT91/y0wcGRsYRTaQ85M/yqIxh3ZMRpOBxgXGpujZ
+NSg+zTFWzu24VSpWAUci5+cP5C6lIHpFxj7mO9qz/dHeNQtxMC4MKy8y6aQeHffUl3a8eCCDO5S
/WeaXxgAAAAAAAA=

--Apple-Mail-255--108104609--