You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by "Antonio Robirosa (JIRA)" <ji...@apache.org> on 2015/11/10 16:43:11 UTC
[jira] [Updated] (DDLUTILS-289) Support for rowid column type in
Oracle
[ https://issues.apache.org/jira/browse/DDLUTILS-289?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Antonio Robirosa updated DDLUTILS-289:
--------------------------------------
Description:
h2. Description
Allow DDL Utils to read tables which contain columns with the rowid data type.
h2. Benefits
DDL Utils can be used in Oracle databases which contains materialized views with fast refresh -which required rowids-.
h2. Proposed change
To add a custom one-way mapping to the row id column type in the class Oracle8Platform to allow reading the information of thecolumns.
{code}
diff --git a/src/java/org/apache/ddlutils/model/TypeMap.java b/src/java/org/apache/ddlutils/model/TypeMap.java
index 23b4b65..d7d930b 100644
--- a/src/java/org/apache/ddlutils/model/TypeMap.java
+++ b/src/java/org/apache/ddlutils/model/TypeMap.java
@@ -132,6 +132,7 @@ public abstract class TypeMap
registerJdbcType(Types.TINYINT, TINYINT, JdbcTypeCategoryEnum.NUMERIC);
registerJdbcType(Types.VARBINARY, VARBINARY, JdbcTypeCategoryEnum.BINARY);
registerJdbcType(Types.VARCHAR, VARCHAR, JdbcTypeCategoryEnum.TEXTUAL);
+ registerJdbcType(Types.ROWID, OTHER, JdbcTypeCategoryEnum.OTHER);
// only available in JDK 1.4 and above:
if (Jdbc3Utils.supportsJava14JdbcTypes())
{code}
h2. Current Situation
I am currently working on eCommerce Project with a eCommerce Suite which uses DDL Utils and we receive the following error when tables with rowid columns in the database exist:
{code}
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | org.apache.ddlutils.model.ModelException: Unknown JDBC type code -8
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.model.Column.setTypeCode(Column.java:215)
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.platform.JdbcModelReader.readColumn(JdbcModelReader.java:781)
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.platform.oracle.Oracle8ModelReader.readColumn(Oracle8ModelReader.java:117)
{code}
h2. Example for the tests
The materialized view proposed on this article [The Trouble with Triggers|http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html] must work with DDL utils:
{code}
SQL> create table primary_currency
( country varchar2(2) primary key,
currency varchar2(3)
)
/
Table created.
SQL> create table other_currencies
( country varchar2(2),
currency varchar2(3),
constraint other_currencies_pk
primary key(country,currency)
)
/
Table created.
SQL> alter table other_currencies add
constraint
must_have_at_least_one_primary
foreign key(country)
references
primary_currency(country)
/
Table altered.
SQL> create materialized view log
on primary_currency with rowid
/
Materialized view log created.
SQL> create materialized view log
on other_currencies with rowid
/
Mat erialized view log created.
SQL> create materialized view primary_is_not_other
refresh fast
on commit
as
select a.rowid arid, b.rowid brid
from primary_currency a, other_currencies b
where a.country = b.country
and a.currency = b.currency
/
Materialized view created.
SQL> alter table primary_is_not_other add constraint primary_curr_cannot_be_other check (arid is null and brid is null)
/
Table altered.
{code}
The view is created by Oracle with the type Table.
was:
h2. Description
Allow DDL Utils to read tables which contain columns with the rowid data type.
h2. Benefits
DDL Utils can be used in Oracle databases which contains materialized views with fast refresh -which required rowids-.
h2. Proposed change
To add a custom one-way mapping to the row id column type in the class Oracle8Platform to allow reading the information of thecolumns.
{code}
diff --git a/src/java/org/apache/ddlutils/platform/oracle/Oracle8Platform.java.original b/src/java/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
index c0e2378..b1932bf 100644
--- a/src/java/org/apache/ddlutils/platform/oracle/Oracle8Platform.java.original
+++ b/src/java/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
@@ -81,6 +81,7 @@ public class Oracle8Platform extends PlatformImplBase
info.addNativeTypeMapping(Types.TINYINT, "NUMBER(3)");
info.addNativeTypeMapping(Types.VARBINARY, "RAW");
info.addNativeTypeMapping(Types.VARCHAR, "VARCHAR2");
+ info.addNativeTypeMapping(Types.ROWID, "BLOB");
info.addNativeTypeMapping("BOOLEAN", "NUMBER(1)", "BIT");
info.addNativeTypeMapping("DATALINK", "BLOB", "BLOB");
{code}
h2. Current Situation
I am currently working on eCommerce Project with a eCommerce Suite which uses DDL Utils and we receive the following error when tables with rowid columns in the database exist:
{code}
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | org.apache.ddlutils.model.ModelException: Unknown JDBC type code -8
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.model.Column.setTypeCode(Column.java:215)
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.platform.JdbcModelReader.readColumn(JdbcModelReader.java:781)
INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.platform.oracle.Oracle8ModelReader.readColumn(Oracle8ModelReader.java:117)
{code}
h2. Example for the tests
The materialized view proposed on this article [The Trouble with Triggers|http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html] must work with DDL utils:
{code}
SQL> create table primary_currency
( country varchar2(2) primary key,
currency varchar2(3)
)
/
Table created.
SQL> create table other_currencies
( country varchar2(2),
currency varchar2(3),
constraint other_currencies_pk
primary key(country,currency)
)
/
Table created.
SQL> alter table other_currencies add
constraint
must_have_at_least_one_primary
foreign key(country)
references
primary_currency(country)
/
Table altered.
SQL> create materialized view log
on primary_currency with rowid
/
Materialized view log created.
SQL> create materialized view log
on other_currencies with rowid
/
Mat erialized view log created.
SQL> create materialized view primary_is_not_other
refresh fast
on commit
as
select a.rowid arid, b.rowid brid
from primary_currency a, other_currencies b
where a.country = b.country
and a.currency = b.currency
/
Materialized view created.
SQL> alter table primary_is_not_other add constraint primary_curr_cannot_be_other check (arid is null and brid is null)
/
Table altered.
{code}
The view is created by Oracle with the type Table.
> Support for rowid column type in Oracle
> ---------------------------------------
>
> Key: DDLUTILS-289
> URL: https://issues.apache.org/jira/browse/DDLUTILS-289
> Project: DdlUtils
> Issue Type: Improvement
> Components: Core - Oracle
> Affects Versions: 1.0
> Environment: Ubuntu
> Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
> dbDriverVersion: Oracle JDBC driver : 12.1.0.2.0
> Reporter: Antonio Robirosa
> Assignee: Thomas Dudziak
> Labels: features, patch
>
> h2. Description
> Allow DDL Utils to read tables which contain columns with the rowid data type.
> h2. Benefits
> DDL Utils can be used in Oracle databases which contains materialized views with fast refresh -which required rowids-.
> h2. Proposed change
> To add a custom one-way mapping to the row id column type in the class Oracle8Platform to allow reading the information of thecolumns.
> {code}
> diff --git a/src/java/org/apache/ddlutils/model/TypeMap.java b/src/java/org/apache/ddlutils/model/TypeMap.java
> index 23b4b65..d7d930b 100644
> --- a/src/java/org/apache/ddlutils/model/TypeMap.java
> +++ b/src/java/org/apache/ddlutils/model/TypeMap.java
> @@ -132,6 +132,7 @@ public abstract class TypeMap
> registerJdbcType(Types.TINYINT, TINYINT, JdbcTypeCategoryEnum.NUMERIC);
> registerJdbcType(Types.VARBINARY, VARBINARY, JdbcTypeCategoryEnum.BINARY);
> registerJdbcType(Types.VARCHAR, VARCHAR, JdbcTypeCategoryEnum.TEXTUAL);
> + registerJdbcType(Types.ROWID, OTHER, JdbcTypeCategoryEnum.OTHER);
> // only available in JDK 1.4 and above:
> if (Jdbc3Utils.supportsJava14JdbcTypes())
> {code}
> h2. Current Situation
> I am currently working on eCommerce Project with a eCommerce Suite which uses DDL Utils and we receive the following error when tables with rowid columns in the database exist:
> {code}
> INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | org.apache.ddlutils.model.ModelException: Unknown JDBC type code -8
> INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.model.Column.setTypeCode(Column.java:215)
> INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.platform.JdbcModelReader.readColumn(JdbcModelReader.java:781)
> INFO | jvm 1 | main | 2015/11/05 17:50:04.153 | at org.apache.ddlutils.platform.oracle.Oracle8ModelReader.readColumn(Oracle8ModelReader.java:117)
> {code}
> h2. Example for the tests
> The materialized view proposed on this article [The Trouble with Triggers|http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html] must work with DDL utils:
> {code}
> SQL> create table primary_currency
> ( country varchar2(2) primary key,
> currency varchar2(3)
> )
> /
> Table created.
> SQL> create table other_currencies
> ( country varchar2(2),
> currency varchar2(3),
> constraint other_currencies_pk
> primary key(country,currency)
> )
> /
> Table created.
> SQL> alter table other_currencies add
> constraint
> must_have_at_least_one_primary
> foreign key(country)
> references
> primary_currency(country)
> /
> Table altered.
> SQL> create materialized view log
> on primary_currency with rowid
> /
> Materialized view log created.
> SQL> create materialized view log
> on other_currencies with rowid
> /
> Mat erialized view log created.
> SQL> create materialized view primary_is_not_other
> refresh fast
> on commit
> as
> select a.rowid arid, b.rowid brid
> from primary_currency a, other_currencies b
> where a.country = b.country
> and a.currency = b.currency
> /
> Materialized view created.
> SQL> alter table primary_is_not_other add constraint primary_curr_cannot_be_other check (arid is null and brid is null)
> /
> Table altered.
> {code}
> The view is created by Oracle with the type Table.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)