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 Tue, 31 Jan 2006 14:02:00 GMT
Try this (you will need to make minor changes to reflect your database 
connection):


package ddlutils;

import java.sql.Connection;
import java.sql.Statement;
import oracle.jdbc.pool.OracleDataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;


public class DemoDDLUtils {
   

    /**
     * Creates a new instance of DemoDDLUtils
     */
    public DemoDDLUtils() {
    }
   
    public static void main(String[] args) throws Exception {
        DemoDDLUtils reader = new DemoDDLUtils();
        reader.run();
    }
   
    public void run() throws Exception {
       
        OracleDataSource ods = new OracleDataSource();
       
        ods.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
        ods.setUser("my_user");
        ods.setPassword("my_password");
       
        String sql = "CREATE TABLE MY_TABLE( " +
                "prop_name VARCHAR2(200) NOT NULL PRIMARY KEY, " +
                "prop_value VARCHAR2(200), prop_value_ext LONG RAW NULL)";
       
        Connection conn = ods.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(sql);
        stmt.close();
        conn.close();
       
        String schema = "MY_SCHEMA";
       
        Platform platform = PlatformFactory.createNewPlatformInstance(ods);
        Database db = platform.readModelFromDatabase("test", null, 
schema, null);
       
        dumpDb(db);
       
        platform.alterTables(db, false, false, false);
       
    }
   
   
    private void dumpDb(Database database) {
        System.out.println("Name=" + database.getName());
        System.out.println("Version=" + database.getVersion());
        Table[] tables = database.getTables();
        System.out.println("Tables--------------------");
        for (int t=0; t<tables.length; t++) {
            System.out.println("Table: " + tables[t].getName());
            Column[] columns = tables[t].getColumns();
            for (int c=0; c<columns.length; c++) {
                System.out.println("  COLUMN: " + 
columns[c].toVerboseString());
            }
        }
    }
   
}



and this is the output I get.


Name=test
Version=null
Tables--------------------
Table: MY_TABLE
  COLUMN: Column [name=PROP_NAME; javaName=null; type=VARCHAR; 
typeCode=12; size=200; required=true; primaryKey=true; 
autoIncrement=false; defaultValue=null; precisionRadix=200; scale=0]
  COLUMN: Column [name=PROP_VALUE; javaName=null; type=VARCHAR; 
typeCode=12; size=200; required=false; primaryKey=false; 
autoIncrement=false; defaultValue=null; precisionRadix=200; scale=0]
  COLUMN: Column [name=PROP_VALUE_EXT; javaName=null; 
type=LONGVARBINARY; typeCode=-4; size=0; required=false; 
primaryKey=false; autoIncrement=false; defaultValue=null; 
precisionRadix=0; scale=0]
  PK: Column [name=PROP_NAME; javaName=null; type=VARCHAR; typeCode=12; 
size=200; required=true; primaryKey=true; autoIncrement=false; 
defaultValue=null; precisionRadix=200; scale=0]
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterTable
INFO: Column PROP_VALUE_EXT in table MY_TABLE differs from current 
specification
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table DR$POLICY_TAB can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table CS_SRS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table OGIS_SPATIAL_REFERENCE_SYSTEMS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_ANGLE_UNITS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_AREA_UNITS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_DATUMS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_DIST_UNITS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_ELLIPSOIDS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_PROJECTIONS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table USER_CS_SRS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table USER_TRANSFORM_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table AUDIT_ACTIONS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table AW$CWMTOECM can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table AW$EXPRESS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table DUAL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table ODCI_SECOBJ$ can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table ODCI_WARNINGS$ can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table OLAPTABLEVELS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table OLAPTABLEVELTUPLES can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table PSTUBTBL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table STMT_AUDIT_OPTION_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SYSTEM_PRIVILEGE_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table TABLE_PRIVILEGE_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table DEF$_TEMP$LOB can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table HELP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$CHARSET can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$CRAWLER_CONFIG_DEFAULT can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$LANG can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$MIMETYPES can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$SYS_CONFIG can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WM$WORKSPACES_TABLE can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ACL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ALL_MODEL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ANY can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ANYATTR can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ATTRGROUP_DEF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ATTRGROUP_REF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ATTRIBUTE can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$CHOICE_MODEL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$COMPLEX_TYPE can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ELEMENT can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$GROUP_DEF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$GROUP_REF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$SCHEMA can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$SEQUENCE_MODEL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$SIMPLE_TYPE can be dropped
Exception in thread "main" org.apache.ddlutils.DynaSqlException: Error 
while executing SQL -- Column PROP_VALUE_EXT in table MY_TABLE differs 
from current specification
-- Table DR$POLICY_TAB can be dropped
-- Table CS_SRS can be dropped
-- Table OGIS_SPATIAL_REFERENCE_SYSTEMS can be dropped
-- Table SDO_ANGLE_UNITS can be dropped
-- Table SDO_AREA_UNITS can be dropped
-- Table SDO_DATUMS can be dropped
-- Table SDO_DIST_UNITS can be dropped
-- Table SDO_ELLIPSOIDS can be dropped
-- Table SDO_PROJECTIONS can be dropped
-- Table USER_CS_SRS can be dropped
-- Table USER_TRANSFORM_MAP can be dropped
-- Table AUDIT_ACTIONS can be dropped
-- Table AW$CWMTOECM can be dropped
-- Table AW$EXPRESS can be dropped
-- Table DUAL can be dropped
-- Table ODCI_SECOBJ$ can be dropped
-- Table ODCI_WARNINGS$ can be dropped
-- Table OLAPTABLEVELS can be dropped
-- Table OLAPTABLEVELTUPLES can be dropped
-- Table PSTUBTBL can be dropped
-- Table STMT_AUDIT_OPTION_MAP can be dropped
-- Table SYSTEM_PRIVILEGE_MAP can be dropped
-- Table TABLE_PRIVILEGE_MAP can be dropped
-- Table DEF$_TEMP$LOB can be dropped
-- Table HELP can be dropped
-- Table WK$CHARSET can be dropped
-- Table WK$CRAWLER_CONFIG_DEFAULT can be dropped
-- Table WK$LANG can be dropped
-- Table WK$MIMETYPES can be dropped
-- Table WK$SYS_CONFIG can be dropped
-- Table WM$WORKSPACES_TABLE can be dropped
-- Table XDB$ACL can be dropped
-- Table XDB$ALL_MODEL can be dropped
-- Table XDB$ANY can be dropped
-- Table XDB$ANYATTR can be dropped
-- Table XDB$ATTRGROUP_DEF can be dropped
-- Table XDB$ATTRGROUP_REF can be dropped
-- Table XDB$ATTRIBUTE can be dropped
-- Table XDB$CHOICE_MODEL can be dropped
-- Table XDB$COMPLEX_TYPE can be dropped
-- Table XDB$ELEMENT can be dropped
-- Table XDB$GROUP_DEF can be dropped
-- Table XDB$GROUP_REF can be dropped
-- Table XDB$SCHEMA can be dropped
-- Table XDB$SEQUENCE_MODEL can be dropped
-- Table XDB$SIMPLE_TYPE can be dropped
        at 
org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:218)
        at 
org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:484)
        at 
org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:402)
        at ddlutils.DemoDDLUtils.run(DemoDDLUtils.java:76)
        at ddlutils.DemoDDLUtils.main(DemoDDLUtils.java:47)
Caused by: java.sql.SQLException: ORA-00900: invalid SQL statement

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
        at 
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
        at 
oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
        at 
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
        at 
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
        at 
oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:796)
        at 
org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:202)
        ... 4 more
Java Result: 1




Thomas Dudziak wrote:

>On 1/31/06, Tim Dudgeon <tdudgeon@informaticsmatters.com> wrote:
>  
>
>>If you read the model of an Oracle database and then try an
>>alterTables() with this unchanged model DDLUtils incorrectly thinks it
>>needs to make lots of changes to the database and can corrupt the
>>database. For instance if you do this:
>>
>> Database db = platform.readModelFromDatabase("test", null, "MYSCHEMA",
>>null);
>> platform.alterTables(db, false, false, false);
>>
>>then DDLUtils want to make lots of changes to the database, even though
>>you have made no changes at all.
>>I do NOT recommend you try this for yourself!
>>    
>>
>
>Please be more specific, e.g. post an original model and what DdlUtils
>reads back.
>
>Tom
>
>  
>

Mime
View raw message