Details

Description

Islay Symonette, in an email thread called "Storing Java Objects in a table" on October 26, 2005 requests the ability to store java objects in the database.

Old releases of Cloudscape allow users to declare a column's type to be a Serializable class. This feature was removed from Derby because the syntax was non-standard. However, most of the machinery to support objects serialized to columns is still in Derby and is even used in system tables. We need to agree on some standard syntax here and re-expose this useful feature. Some subset of the ANSI adt syntax, cumbersome as it is, would do.

Satheesh Bandaram
added a comment - 27/Oct/05 09:44 I think this "bug" needs to be marked as Enhancement... Derby never had capability to store Java objects, so this should be seen as an enhancement request.
SQL specification Part 13:SQL Routines and Types Using the Java Programming Language (SQL/JRT) is the standard way to add java objects.

Attaching a first rev of a functional spec for User Defined Types. This describes a minimal amount of work needed to declare Java objects which can be stored in the database. In this first rev, the query language is only able to see these objects as structs. The spec lists future enhancements which can make these types even more useful.

I don't expect that I will have time to implement this feature in the 10.6 timeframe. However, I would be happy to coach someone else through the process.

Rick Hillegas
added a comment - 02/Oct/09 20:34 Attaching a first rev of a functional spec for User Defined Types. This describes a minimal amount of work needed to declare Java objects which can be stored in the database. In this first rev, the query language is only able to see these objects as structs. The spec lists future enhancements which can make these types even more useful.
I don't expect that I will have time to implement this feature in the 10.6 timeframe. However, I would be happy to coach someone else through the process.
Thanks is advance for reading this spec and posting your feedback.

yHi Rick, thanks for starting this work. It would be nice to re-enable
Derby's capability to store Java objects directly in columns of
structured user defined types.

Meta-comment: Would it be possible (legal) to allow a SQL User defined
type that maps to a Java class that has no attributes (the fields are
opaque to SQL)? If so, wouldn't that provide an even smaller build
increment to re-enable Derby's ability to store Java object values in
columns? Then, SQL visible attributes (fields) could be added later if
desired.

Please see comments in-lined.

Declare that you are talking about SQL structured types, not distinct
types (which can be UDTs, too).

> * Access - Type-creators control who can use their types.

"Can control". Derby does not force you to control access.

> * Structs - These user-defined objects appear to the query
> language as structs. That is, queries can filter results based
> on the values of public fields in these objects. However, in
> this first increment, queries cannot invoke methods on these

"methods": To be clear, this means UDT methods, presumably (in contrast to other
SQL functions, procedures). They can't be declared (in SQL) either?

> * Selection - Nevertheless, these user-defined objects can be
> SELECTed and returned to application code.

And/or their fields?

A note on terminology; are they really objects after serialization to
a column? Melton says no, because they have no unique id, they are
just (compound) values. He says in his book that only Typed tables can
have objects (with id). Does "instances" imply it's an object?

Should the overview say that the corresponding Java class can have
non-public fields, too (and of course, methods)?

> Follow-on Work

> Future efforts can enable other capabilities. Each of these
> features should be a small increment of work on top of this
> spec. However, these capabilities are not addressed by this spec:

If this is not available, how will getObject construct the object?
Using metadata? Ok, explained later. Answer is serializable.

> * Attribute/field mismatch - For each mentioned attribute,
> the Java class must contain a public instance field with
> the corresponding singleQuotedFieldName. Furthermore,
> the SQL dataType of the attribute must correspond to the
> Java datatype of the field. See Appendix A for the
> correspondences of SQL to Java types.

Is it an error if the Java class contains more public fields than
are declared in the SQL? Do we allows fields to be Java object
references?

> Further errors can occur at query-execution time:
> * Null mismatch - A null cannot be stored in an attribute
> which maps to a primitive Java field.

Confused.. This concerns SQL NULL, right? Or a JDBC null? In what
section of the standard is this described?

Is type checked as part of setObject? Or only at execute time? So,
the fact that BigDecimal is an object reference (not a Java primitive
type) is not a problem here? It will be separately type checked
against decimal( 31, 5 )? I'm muddled here...

> SELECT

> No changes are made to the SELECT syntax. You can use JDBC to
> retrieve selected user defined types. For example:

Cf. above comment. Presumably, we would need to allow SELECTing of attributes for these
condition to be relevant? Or?

> GRANT/REVOKE USAGE

> The work on sequence generators. added a new USAGE privilege to
> Derby. This is needed to support user defined types too. The
> relevant material in the SQL Standard is in part 2 section 4.34.2
> (Privileges) and part 2 section 12.3 (<privileges>). You need
> USAGE privilege in order to create or access a column of user
> defined type. Here is the first increment of Derby support for

Only if SQL authorization mode is enabled. Currently, we don't grant
creation privileges, e.g. CREATE ROLE can only be done by the data
base owner. A priori, the DBO will have USAGE privilege, I assume?

> * Casts - A user defined type cannot be cast to any other
> type. The converse is also true: no other type can be cast
> to a user defined type.

To be clear, this is only true for structured user defined types, not
distinct ones (e.g. CAST(shoesize to INTEGER) is OK, according to
"Understanding SQL and Java together".

> * Comparisons - A user defined type has no ordering. This
> means that you cannot compare and sort user defined
> types. You cannot use them in expressions involving the <,
> =, >, IN, BETWEEN, and LIKE operators. You cannot use user
> defined types in aggregates, DISTINCT expressions, and
> GROUP/ORDER BY clauses. You cannot build indexes on them.

Ditto.

> * Subtypes - Derby is not aware of sub/super-type
> relationships among user defined types. For this reason, you
> cannot put a subtype object in a supertyped column..

> Type Evolution
:

> * Recompilation - If the second rev of your application is
> compiled on a different platform than the first rev, then
> your serialized objects may fail to de-serialize. This
> problem and a possible workaround are discussed in the
> "Version Control" section near the end of this Serialization
> Primer and in the last paragraph of the header comment for
> java.io.Serializable.

Client/server: Client's view of type may differ from server's view of
same type?

> Fortunately, it's easy to write a version-aware user defined type
> which implements java.io.Externalizable and can evolve itself over
> time. For example, here is the first rev of such a class:

: <example>

Thanks for this explanation; really useful for User Guide later!

> DatabaseMetaData

> DatabaseMetaData.getColumns()

> The DatabaseMetaData.getColumns() method must account for user
> defined types. This method returns a ResultSet which contains
> a row for every column in a table or view. That row, in turn,
> has two columns which are aware of user defined types:

OTHER - The constant in the Java programming language that indicates
that the SQL type is database-specific and gets mapped to a Java
object that can be accessed via the methods getObject and setObject.

I am not sure when JAVA_OBJECT or more appropriate than OTHER. Can you
enlighten me?

> In addition, the DatabaseMetaData.getTypeInfo() method must
> report that Derby supports user defined types. This method is
> described in the JDBC 2.0 spec. It returns a ResultSet which
> contains a row for every JDBC type supported by Derby. We add
> a new row to this ResultSet:

Dag H. Wanvik
added a comment - 08/Oct/09 00:04 yHi Rick, thanks for starting this work. It would be nice to re-enable
Derby's capability to store Java objects directly in columns of
structured user defined types.
Meta-comment: Would it be possible (legal) to allow a SQL User defined
type that maps to a Java class that has no attributes (the fields are
opaque to SQL)? If so, wouldn't that provide an even smaller build
increment to re-enable Derby's ability to store Java object values in
columns? Then, SQL visible attributes (fields) could be added later if
desired.
Please see comments in-lined.
Declare that you are talking about SQL structured types, not distinct
types (which can be UDTs, too).
> * Access - Type-creators control who can use their types.
"Can control". Derby does not force you to control access.
> * Structs - These user-defined objects appear to the query
> language as structs. That is, queries can filter results based
> on the values of public fields in these objects. However, in
> this first increment, queries cannot invoke methods on these
"methods": To be clear, this means UDT methods, presumably (in contrast to other
SQL functions, procedures). They can't be declared (in SQL) either?
> * Selection - Nevertheless, these user-defined objects can be
> SELECTed and returned to application code.
And/or their fields?
A note on terminology; are they really objects after serialization to
a column? Melton says no, because they have no unique id, they are
just (compound) values. He says in his book that only Typed tables can
have objects (with id). Does "instances" imply it's an object?
Should the overview say that the corresponding Java class can have
non-public fields, too (and of course, methods)?
> Follow-on Work
> Future efforts can enable other capabilities. Each of these
> features should be a small increment of work on top of this
> spec. However, these capabilities are not addressed by this spec:
> * Ordering - The Standard supports ordering of Java types which
> implement java.lang.Comparable.
>
> * Methods/Constructors - Methods and constructors can be
> declared by CREATE/ALTER TYPE and then invoked in queries.
>
> * Streaming - Object transport and persistence can occur via
> the streaming java.sql.SQLData interface.
If this is not available, how will getObject construct the object?
Using metadata? Ok, explained later. Answer is serializable.
> * Attribute/field mismatch - For each mentioned attribute,
> the Java class must contain a public instance field with
> the corresponding singleQuotedFieldName. Furthermore,
> the SQL dataType of the attribute must correspond to the
> Java datatype of the field. See Appendix A for the
> correspondences of SQL to Java types.
Is it an error if the Java class contains more public fields than
are declared in the SQL? Do we allows fields to be Java object
references?
> Further errors can occur at query-execution time:
> * Null mismatch - A null cannot be stored in an attribute
> which maps to a primitive Java field.
Confused.. This concerns SQL NULL, right? Or a JDBC null? In what
section of the standard is this described?
> Example:
> CREATE TABLE order
> (
> orderID INT GENERATED ALWAYS AS IDENTITY,
> customerID INT REFERENCES customer( customerID ),
> totalPrice price
schema.type would be allowed here, too, presumably (if USAGE is granted)?
E.g. "totalPrice dags.price"
> PreparedStatement ps = conn.prepareStatement( "INSERT INTO
> order( customerID, totalPrice ) values ( ?, ? )" );
> ps.setInt( 1, customerID );
> ps.setObject( 2, new Price( "USD", new BigDecimal( "9.99" ) ) );
Is type checked as part of setObject? Or only at execute time? So,
the fact that BigDecimal is an object reference (not a Java primitive
type) is not a problem here? It will be separately type checked
against decimal( 31, 5 )? I'm muddled here...
> SELECT
> No changes are made to the SELECT syntax. You can use JDBC to
> retrieve selected user defined types. For example:
> PreparedStatement ps = conn.prepareStatement( "SELECT * from order" );
> ResultSet rs = ps.executeQuery();
> while( rs.next() )
>
{
> int orderID = rs.getInt( 1 );
> int customerID = rs.getInt( 2 );
> Price totalPrice = (Price) getObject( 3 );
> ...
> }
So can one select directly the attribute?
E.g. SELECT totalPrice.amount from order
> ALTER TYPE
:
> ALTER TYPE addAttribute | dropAttribute
> addAttribute :: ADD ATTRIBUTE attribute
> dropAttribute :: DROP ATTRIBUTE identifier RESTRICT
> You cannot drop an attribute if it is mentioned in any of the
> following SQL objects:
> * View definition
> * Constraint definition
> * Triggered action of a trigger definition
> * Generation clause of a generated column
Cf. above comment. Presumably, we would need to allow SELECTing of attributes for these
condition to be relevant? Or?
> GRANT/REVOKE USAGE
> The work on sequence generators. added a new USAGE privilege to
> Derby. This is needed to support user defined types too. The
> relevant material in the SQL Standard is in part 2 section 4.34.2
> (Privileges) and part 2 section 12.3 (<privileges>). You need
> USAGE privilege in order to create or access a column of user
> defined type. Here is the first increment of Derby support for
Only if SQL authorization mode is enabled. Currently, we don't grant
creation privileges, e.g. CREATE ROLE can only be done by the data
base owner. A priori, the DBO will have USAGE privilege, I assume?
> managing privileges on user defined types:
> GRANT USAGE ON TYPE [ schemaName. ] typeName TO grantees
> REVOKE USAGE ON TYPE [ schemaName. ] typeName FROM grantees RESTRICT
> You cannot REVOKE USAGE on a user defined type if this would
> make some schema object unusable by its owner. Attribute
> References
So, it might make the schema object unaccessible for other users that
otherwise have, say, SELECT privilege on a table which has a column of
that type? I.e. the RESTRICT only applies for the owner?
> SELECT *
> FROM t, s
> WHERE t.att IS NOT NULL;
> Derby raises an error when it encounters this kind of
You mean "will raise" ? Or currently?
> ambiguity. The solution, as Melton points out, is to add a
> disambiguating correlation variable. Derby knows how to
> interpret the following queries:
> SELECT *
> FROM t cor, s
> WHERE cor.att IS NOT NULL;
> SELECT *
> FROM t, s cor
> WHERE cor.t.att IS NOT NULL;
In this case, wouldn't s.t.att be unambiguous also? As long as you
have only looked at the "s.t" is is still ambiguous, so maybe its not
allowed?
> Here are supported example references to attributes inside
> user defined types:
> SELECT *
> FROM order
> WHERE totalPrice.currencyCode = 'USD';
So in this case, a correlation name is not required, so it would seem
a correlation name is only required if there is an ambiguity.
> UPDATE order
> set totalPrice.amount = totalPrice.amount * (cast (1.05 as decimal( 31, 5 )));
> Behavior
> Note the following behaviors of user defined types:
> * Casts - A user defined type cannot be cast to any other
> type. The converse is also true: no other type can be cast
> to a user defined type.
To be clear, this is only true for structured user defined types, not
distinct ones (e.g. CAST(shoesize to INTEGER) is OK, according to
"Understanding SQL and Java together".
> * Comparisons - A user defined type has no ordering. This
> means that you cannot compare and sort user defined
> types. You cannot use them in expressions involving the <,
> =, >, IN, BETWEEN, and LIKE operators. You cannot use user
> defined types in aggregates, DISTINCT expressions, and
> GROUP/ORDER BY clauses. You cannot build indexes on them.
Ditto.
> * Subtypes - Derby is not aware of sub/super-type
> relationships among user defined types. For this reason, you
> cannot put a subtype object in a supertyped column..
> Type Evolution
:
> * Recompilation - If the second rev of your application is
> compiled on a different platform than the first rev, then
> your serialized objects may fail to de-serialize. This
> problem and a possible workaround are discussed in the
> "Version Control" section near the end of this Serialization
> Primer and in the last paragraph of the header comment for
> java.io.Serializable.
Client/server: Client's view of type may differ from server's view of
same type?
> * Evolution - Your tools for evolving a class which simply
> implements java.io.Serializable are very limited.
> Fortunately, it's easy to write a version-aware user defined type
> which implements java.io.Externalizable and can evolve itself over
> time. For example, here is the first rev of such a class:
: <example>
Thanks for this explanation; really useful for User Guide later!
> DatabaseMetaData
> DatabaseMetaData.getColumns()
> The DatabaseMetaData.getColumns() method must account for user
> defined types. This method returns a ResultSet which contains
> a row for every column in a table or view. That row, in turn,
> has two columns which are aware of user defined types:
> DatabaseMetaData type information Column name Description Value returned for a user defined type
> DATA_TYPE The JDBC type id of the column's data type java.sql.Types.JAVA_OBJECT
> java.sql.Types.JAVA_OBJECT links to BOOLEAN, not JAVA_OBJECT.
What about this candidate:
OTHER - The constant in the Java programming language that indicates
that the SQL type is database-specific and gets mapped to a Java
object that can be accessed via the methods getObject and setObject.
I am not sure when JAVA_OBJECT or more appropriate than OTHER. Can you
enlighten me?
> TYPE_NAME The schema-qualified name of the user defined type
> CREATE TYPE statement Corresponding TYPE_NAME
> create type mySchema.myType ... "MYSCHEMA"."MYTYPE"
> create type "mySchema"."myType" ... "mySchema"."myType"
> DatabaseMetaData.getTypeInfo()
> In addition, the DatabaseMetaData.getTypeInfo() method must
> report that Derby supports user defined types. This method is
> described in the JDBC 2.0 spec. It returns a ResultSet which
> contains a row for every JDBC type supported by Derby. We add
> a new row to this ResultSet:
> Supported JDBC Types Column name Description Value returned for a user defined type
> TYPE_NAME Database-specific name for the JDBC type OBJECT
> DATA_TYPE JDBC type java.sql.Types.JAVA_OBJECT
java.sql.Types.JAVA_OBJECT links to BOOLEAN, not JAVA_OBJECT.
> PRECISION For numeric and string types null
> LITERAL_PREFIX Prefix for quoting literals null
> LITERAL_SUFFIX Suffix for quoting literals null
> CREATE_PARAMS Parameters used to create the type null
> NULLABLE Whether nulls are allows DatabaseMetaData.typeNullable
So we do allow a NULL for such columns. Maybe nice to mention
somewhere above?
> System Tables
> SYSALIASES
> This feature affects a couple columns of this table.
> * ALIASTYPE - This column takes a new value, A for user defined types.
A character value 'A', right.
> * NAMESPACE - This column takes a new value, A for user defined types.
> * ALIASINFO - This column can hold a new kind of AliasInfo object which describes a user defined type.
So the "ALIAS" column holds the SQL type name, I presume.
> SYSPERMS
> This table was added as part of the work on sequence
> generators. A couple columns of this table are affected by
> user defined types:
> * OBJECTTYPE - This column can hold an additional value:
> "USER-DEFINED TYPE".
> * OBJECTID - This column can now join to
> SYS.SYSALIASES.ALIASID. In this case, the SYSALIASES row
> describes a user defined type.
This is a UUID for the UDT I presume.
> * IS_GRANTABLE - If this is a permission on a user defined
> type, this column is 'Y' only if the GRANTOR is the
> owner of the type. Otherwise, this column is 'N'.
> Upgrade
> Soft Upgrade
> After a soft-upgrade to target release, this feature remains NOT enabled.
> Hard Upgrade
> After a hard-upgrade to target release, this feature is enabled.
Will it work with a down-rev client?

Thanks for the great feedback, Dag. I am attaching a second rev of the spec, which addresses many of your comments:

Clarify that these are structured, not distinct types.

Clarify that instance methods are not covered by this first increment.

Clarify that declared public fields can be selected.

Clarify that UDT names are schema-qualified names.

Clarify authorization language.

Clarify the importance of defensive code to handle client/server version mismatches.

Fix links to java.sql.Types.JAVA_OBJECT javadoc.

Cite the JDBC spec to explain why java.sql.Types.JAVA_OBJECT is the value of DatabaseMetaData.getColumns().DATA_TYPE for UDT columns.

Clarify that by definition, UDTs are nullable.

Clarify that SYSALIASES.ALIAS holds the UDT's name.

Clarify that SYSCOLUMNS.REFERENCEID and SYSPERMS.OBJECTID continue to be UUIDs.

Clarify that down-rev clients can access UDTs after hard upgrade.

In the meantime, I have been toying with the idea of simplifying the CREATE TYPE syntax. I would like to get feedback on this proposal before addressing your other comments, many of which hinge on the syntax of the CREATE TYPE statement.

I believe that the following extension would be compatible with the SQL Standard and would greatly simplify the task of the type designer in mapping Java to SQL types. We could extend the standard syntax with a new <user defined type option> (see part 2 of the standard, section 11.42 <user-defined type definition>):

STYLE DERBY

Here's the simplified Derby syntax for binding a SQL type to a Java type:

With this syntax, you would not need to declare public attributes, methods, and constructors. Derby would automatically recognize them. Here's how simple the declaration of the example Price type would look using this syntax:

This would eliminate the need for an ALTER TYPE statement and so dodge the odd limitation in part 13, section 9.6 <alter type statement>. For the first increment, Derby would just recognize public fields of the bound class. In later increments, we would add support for public methods and constructors.

Rick Hillegas
added a comment - 09/Oct/09 17:20 Thanks for the great feedback, Dag. I am attaching a second rev of the spec, which addresses many of your comments:
Clarify that these are structured, not distinct types.
Clarify that instance methods are not covered by this first increment.
Clarify that declared public fields can be selected.
Clarify that UDT names are schema-qualified names.
Clarify authorization language.
Clarify the importance of defensive code to handle client/server version mismatches.
Fix links to java.sql.Types.JAVA_OBJECT javadoc.
Cite the JDBC spec to explain why java.sql.Types.JAVA_OBJECT is the value of DatabaseMetaData.getColumns().DATA_TYPE for UDT columns.
Clarify that by definition, UDTs are nullable.
Clarify that SYSALIASES.ALIAS holds the UDT's name.
Clarify that SYSCOLUMNS.REFERENCEID and SYSPERMS.OBJECTID continue to be UUIDs.
Clarify that down-rev clients can access UDTs after hard upgrade.
In the meantime, I have been toying with the idea of simplifying the CREATE TYPE syntax. I would like to get feedback on this proposal before addressing your other comments, many of which hinge on the syntax of the CREATE TYPE statement.
I believe that the following extension would be compatible with the SQL Standard and would greatly simplify the task of the type designer in mapping Java to SQL types. We could extend the standard syntax with a new <user defined type option> (see part 2 of the standard, section 11.42 <user-defined type definition>):
STYLE DERBY
Here's the simplified Derby syntax for binding a SQL type to a Java type:
CREATE TYPE [ schemaName . ] typeName
EXTERNAL NAME singleQuotedJavaClassName
LANGUAGE JAVA
STYLE DERBY
With this syntax, you would not need to declare public attributes, methods, and constructors. Derby would automatically recognize them. Here's how simple the declaration of the example Price type would look using this syntax:
CREATE TYPE price
EXTERNAL NAME 'com.acme.types.Price'
LANGUAGE JAVA
STYLE DERBY
This would eliminate the need for an ALTER TYPE statement and so dodge the odd limitation in part 13, section 9.6 <alter type statement>. For the first increment, Derby would just recognize public fields of the bound class. In later increments, we would add support for public methods and constructors.
What do you think?

Rick Hillegas
added a comment - 16/Oct/09 17:46 Attaching a third rev of a functional spec for this feature. In this rev:
o The CREATE TYPE statement is even simpler.
o UDTs are opaque objects with no visible fields/methods/constructors.
o However, UDTs can be inspected and manipulated via functions and procedures.
Here is an example of the CREATE TYPE statement supported by this rev of the spec:
CREATE TYPE price
EXTERNAL NAME 'com.acme.types.Price'
LANGUAGE JAVA

Just a heads up, I don't think the storage support for user defined types which don't implement the
special read/write interfaces works anymore. It may partially work, but I seem to remember some
dead code being not updated or being removed, there may be ASSERTS that pop. So extra testing necessary in this area. Best to test types that are both sometimes less than a page and some that are more than a page. Another area that
may be problem would be network testing in addition to embedded testing, again in the area of a new
type that just defaults to the basic java externalization routines.

Mike Matrigali
added a comment - 16/Oct/09 20:02 Just a heads up, I don't think the storage support for user defined types which don't implement the
special read/write interfaces works anymore. It may partially work, but I seem to remember some
dead code being not updated or being removed, there may be ASSERTS that pop. So extra testing necessary in this area. Best to test types that are both sometimes less than a page and some that are more than a page. Another area that
may be problem would be network testing in addition to embedded testing, again in the area of a new
type that just defaults to the basic java externalization routines.

Attaching derby-651-01-aa-basicCreateDropType.diff. This adds machinery to create and drop UDTs. You can't do anything with these UDTs yet. That is, you can't declare columns or parameters to be UDT types. But this is a foundation to build on. The regression tests ran cleanly for me.

Rick Hillegas
added a comment - 18/Nov/09 19:29 Attaching derby-651-01-aa-basicCreateDropType.diff. This adds machinery to create and drop UDTs. You can't do anything with these UDTs yet. That is, you can't declare columns or parameters to be UDT types. But this is a foundation to build on. The regression tests ran cleanly for me.
Touches the following files:
M java/engine/org/apache/derby/impl/sql/catalog/SYSALIASESRowFactory.java
M java/engine/org/apache/derby/iapi/sql/dictionary/AliasDescriptor.java
M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
M java/engine/org/apache/derby/catalog/AliasInfo.java
A java/engine/org/apache/derby/catalog/types/UDTAliasInfo.java
DataDictionary support for a new kind of alias, representing a Java class that can be used as a UDT.
M java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
M java/engine/org/apache/derby/impl/sql/compile/DropAliasNode.java
M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
M java/engine/org/apache/derby/impl/sql/execute/CreateAliasConstantAction.java
Support for CREATE/DROP TYPE.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_6.java
Basic tests for CREATE/DROP TYPE plus upgrade test to verify that you must hard-upgrade to 10.6 before you can create a UDT.

Attaching derby-651-02-af-udtColumnsRetvalsParams.diff. This patch makes it possible to create UDT columns and store UDTs in them. This patch also makes it possible to pass UDTs as parameters into functions and procedures and to return UDTs from functions. The following is possible with this patch:

At least for small UDTs, it turns out that Derby still has the logic needed to store the UDT values in columns. It appears (although I have not tested this yet), that the Store can probably persist UDT values for UDTs which implement SQLData in addition to UDTs which implement Serializable. What was needed was the ability to identify UDTs by schema qualified names. Touches the following files:

Adds logic to bind a UDT declaration. This means looking up the UDT in the DataDictionary given its schema qualified name and then poking the corresponding Java class name into the UDT type descriptor.

Thanks for this patch, Rick. Nice to see this addition! It was pretty
straight-forward to grok, although as always the changes in the type
classes challenge me. The general approach seems fine. I have only
nits which may have escaped your attention to contribute for now.
You may want to assign yourself now that you have made two nice patches

Clarify somewhere difference between Derby User defined types and
(actual user created) UDTs; usage is a bit confusing now, you do use
the term UserDefinedTypeIdImpl for UDTs...

Rick Hillegas
added a comment - 28/Nov/09 17:48 Thanks for the comments, Dag. Attaching derby-651-04-aa-javadoc.diff:
o I added a class javadoc header to UserDefinedTypeIdImpl, describing the 2 kinds of user defined types
o I added some javadoc tags. I didn't add all of the tags you suggested. Instead, I erred on the side of keeping the javadoc style consistent throughout those files.
The javadoc builds cleanly for me.
Thanks.

Dag H. Wanvik
added a comment - 29/Nov/09 21:23 - edited Thanks, Rick, for the comments on the two kinds of udts!
Changes look good, except for this small typo in Javadoc for
BaseTypeIdImpl(String schemaName, String unqualifiedName ):
@param unqualifiedName The qualified name of the UDT in that schema
That should be "The unqualified name of the UDT in that schema", presumably.

Attaching derby-651-05-ac-dependencyTable.diff. This adds persistent dependency tracking between tables and the UDT types of their columns. This patch prevents you from dropping a UDT if there are tables whose columns rely on that type. Regression tests passed cleanly for me.

Ideally, we would like to track these dependencies at column granularity. However, columns don't have UUIDs. UUIDs identify the tuple descriptors which are endpoints of arcs in the dependency graph. The following design choice had to be made:

1) Create UUIDs for every column.

2) Track the dependency at a higher level, drawing the arcs between tables and UDTs rather than between columns and UDTs.

Option (1) looked like a lot of work with a high probability of destabilizing the codeline. I opted for (2) instead. This ended up pushing some complexity into one localized method: DDLConstantAction.adjustUDTDependencies(). That method is responsible for making sure that there is only one dependency arc between a table and a UDT, regardless of how many columns in the table may share that UDT type.

Rick Hillegas
added a comment - 06/Dec/09 21:58 - edited Attaching derby-651-05-ac-dependencyTable.diff. This adds persistent dependency tracking between tables and the UDT types of their columns. This patch prevents you from dropping a UDT if there are tables whose columns rely on that type. Regression tests passed cleanly for me.
Ideally, we would like to track these dependencies at column granularity. However, columns don't have UUIDs. UUIDs identify the tuple descriptors which are endpoints of arcs in the dependency graph. The following design choice had to be made:
1) Create UUIDs for every column.
2) Track the dependency at a higher level, drawing the arcs between tables and UDTs rather than between columns and UDTs.
Option (1) looked like a lot of work with a high probability of destabilizing the codeline. I opted for (2) instead. This ended up pushing some complexity into one localized method: DDLConstantAction.adjustUDTDependencies(). That method is responsible for making sure that there is only one dependency arc between a table and a UDT, regardless of how many columns in the table may share that UDT type.
Touches the following files:
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java
Adds a dictionary method for looking up the AliasDescriptor associated with a UDT. This AliasDescriptor is the persistent object that is one of the endpoints of a dependency arc.
M java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
M java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
M java/engine/org/apache/derby/iapi/sql/dictionary/AliasDescriptor.java
Boilerplate to support DROP TYPE.
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
New error message raised when a dependent table prevents a UDT from being dropped.
M java/engine/org/apache/derby/impl/sql/compile/TableElementNode.java
Made DROP COLUMN nodes report that they are DROP COLUMN nodes rather than MODIFY COLUMN nodes. Some of the DROP COLUMN logic was being skipped.
M java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
Made it possible for a table to be the starting point of a dependency arc. Previously, a table could only be the ending point of a dependency arc.
M java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
M java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
This is the heart of the logic which adds and drops the dependency arc from a table to a UDT.
M java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
M java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
M java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
More debug machinery for printing out the contents of DDL nodes in the AST. Debug support for DDL nodes seems weak.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
Basic tests to verify that if a table has a UDT column then that UDT cannot be dropped.

Thank you for tackling this feature. I am sure it will be most useful for users. I have not been following your progress closely, but was wondering if you could confirm that all functionality being added is part of the SQL Standard or do you plan some Derby specific syntax?

Kathey Marsden
added a comment - 09/Dec/09 18:36 Hi Rick,
Thank you for tackling this feature. I am sure it will be most useful for users. I have not been following your progress closely, but was wondering if you could confirm that all functionality being added is part of the SQL Standard or do you plan some Derby specific syntax?
Thanks
Kathey

Rick Hillegas
added a comment - 09/Dec/09 18:53 Hi Kathey,
So far the syntax is all a subset of what is defined in the SQL Standard and I don't anticipate needing any extensions for this first increment.
Thanks,
-Rick

Kathey Marsden
added a comment - 10/Dec/09 16:41 Thanks Rick for the clarification on the standard compliance. Please raise a flag on this issue if you want to start working on extensions so we can discuss at that time.

I think it makes sense that one cannot in general cast to a UDT, but in some special cases it may make sense. For instance, it may be useful to allow casting of parameters or NULLs to a UDT. That is, queries like these:

VALUES CAST(? AS MY_TYPE)

VALUES CAST(NULL AS MY_TYPE)

Further, the Behavior section says:
> Comparisons - A UDT has no ordering. This means that you cannot compare and sort UDTs. You cannot use them in expressions involving the <, =, >, IN, BETWEEN, and LIKE operators. You cannot use UDTs in aggregates, DISTINCT expressions, and GROUP/ORDER BY clauses. You cannot build indexes on them.

This is not how the current code behaves. Possibly because of implicit casts to string types?

ij> select distinct * from t where s like 'ab%' group by s order by s desc;
S
---------------
abcdef
abc

Knut Anders Hatlen
added a comment - 11/Dec/09 12:20 Hi Rick,
The Behavior section in the func spec says:
> Casts - A UDT cannot be cast explicitly to any other type. The converse is also true: no other type can be cast to a UDT.
With the current code, casting from a UDT to CHAR/VARCHAR appears to work:
ij> create type java_string external name 'java.lang.String' language java;
0 rows inserted/updated/deleted
ij> create table t(s java_string);
0 rows inserted/updated/deleted
ij> insert into t values ('a');
1 row inserted/updated/deleted
ij> insert into t values ('abc');
1 row inserted/updated/deleted
ij> insert into t values ('abcdef');
1 row inserted/updated/deleted
ij> select cast(s as varchar(7)), length(s) from t;
1 |2
-------------------
a |1
abc |3
abcdef |6
3 rows selected
Casting to a UDT, on the other hand, gives a syntax error:
ij> values cast('abc' as java_string);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 22.
I think it makes sense that one cannot in general cast to a UDT, but in some special cases it may make sense. For instance, it may be useful to allow casting of parameters or NULLs to a UDT. That is, queries like these:
VALUES CAST(? AS MY_TYPE)
VALUES CAST(NULL AS MY_TYPE)
Further, the Behavior section says:
> Comparisons - A UDT has no ordering. This means that you cannot compare and sort UDTs. You cannot use them in expressions involving the <, =, >, IN, BETWEEN, and LIKE operators. You cannot use UDTs in aggregates, DISTINCT expressions, and GROUP/ORDER BY clauses. You cannot build indexes on them.
This is not how the current code behaves. Possibly because of implicit casts to string types?
ij> select distinct * from t where s like 'ab%' group by s order by s desc;
S
---------------
abcdef
abc
2 rows selected

Thanks for continuing to experiment with UDTs, Knut. I have logged two issues to track the behaviors you have discovered.

DERBY-4469: I agree that the spec is too harsh and we should allow certain casts to UDTs, including casts from ?, NULL, and subtypes

DERBY-4470: The ordering behavior you see is wrong and should be corrected. I think it results from the fact that java.lang.String gets special handling. I do get an error if I try to sort on the Price type. E.g.:

Rick Hillegas
added a comment - 11/Dec/09 13:42 Thanks for continuing to experiment with UDTs, Knut. I have logged two issues to track the behaviors you have discovered.
DERBY-4469 : I agree that the spec is too harsh and we should allow certain casts to UDTs, including casts from ?, NULL, and subtypes
DERBY-4470 : The ordering behavior you see is wrong and should be corrected. I think it results from the fact that java.lang.String gets special handling. I do get an error if I try to sort on the Price type. E.g.:
ij> connect 'jdbc:derby:memory:dummy;create=true';
ij> create type Price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java;
0 rows inserted/updated/deleted
ij> create table t( a price );
0 rows inserted/updated/deleted
ij> select * from t order by a;
ERROR X0X67: Columns of type '"APP"."PRICE"' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
Thanks!

Attaching derby-651-06-aa-dropTable.diff. This addresses the bug which Knut discovered: if you drop a table which depends on a UDT, the dependency arc from the table to the UDT is not dropped. We were only dropping the arcs when we dropped individual columns from the table. Tests passed cleanly for me. Committed at subversion revision 889822.

The fix was to make the DROP TABLE logic call the same arc-dropping code as the ALTER TABLE logic calls. That logic was modified slightly to handle the DROP TABLE case.

Rick Hillegas
added a comment - 11/Dec/09 21:34 Attaching derby-651-06-aa-dropTable.diff. This addresses the bug which Knut discovered: if you drop a table which depends on a UDT, the dependency arc from the table to the UDT is not dropped. We were only dropping the arcs when we dropped individual columns from the table. Tests passed cleanly for me. Committed at subversion revision 889822.
The fix was to make the DROP TABLE logic call the same arc-dropping code as the ALTER TABLE logic calls. That logic was modified slightly to handle the DROP TABLE case.
Touches the following files:
M java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
Changes the arc-dropping code to handle DROP TABLE. Changes the signature of the arc-dropping method.
M java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
Calls that logic for DROP TABLE.
M java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Accounts for the new signature of the arc-dropping method.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
Some regression tests to verify that the bug is fixed.

Rick Hillegas
added a comment - 13/Dec/09 20:32 Attaching derby-651-08-aa-dependencyRoutines.diff. This patch adds dependencies of routines on UDTs. The tests passed cleanly for me. Committed at subversion revision 890115.
Touches the following files:
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
New error message raised when a routine blocks the dropping of a UDT.
M java/engine/org/apache/derby/iapi/sql/dictionary/TupleDescriptor.java
M java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
Moved some reporting logic out of TableDescriptor up into its superclass so that the logic can be used by AliasDescriptor.
M java/engine/org/apache/derby/iapi/sql/dictionary/AliasDescriptor.java
Made AliasDescriptor a dependent.
M java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/CreateAliasConstantAction.java
Logic to add and drop dependencies of routines on UDTs.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
Regression tests for dependencies of routines on UDTs.

Thanks for reading the patch. I don't know why isValid() is synchronized. I simply copied that signature from another tuple descriptor. I also introduced a synchronized isValid() method to TableDescriptor in an earlier patch: derby-651-05-ac-dependencyTable.diff and a synchronized isValid() method was introduced when SequenceDescriptor was added earlier this year. Most of our Dependents have synchronized isValid() methods, but not all. Here's the situation:

Attaching derby-651-09-ac-usagePrivilege.diff. This patch wires in the USAGE privilege, which is needed for sequences and udts. The support for sequences can't be finished until a bit more work has been done on implementing the NEXT SEQUENCE FOR operator. Regression tests pass for me.

The USAGE privilege is stored in the SYSPERMS catalog, a 10.6 catalog which was introduced by earlier work on DERBY-712. SYSPERMS is intended to be a general purpose permissions catalog. Over time, the complexity of our permissions implementation and the number of permissions catalogs can be reduced by storing other kinds of privileges in this catalog. For instance, it should be easy to store the EXECUTE privilege in SYSPERMS. Much of the implementation of the USAGE privilege parallels the implementation of the EXECUTE privilege.

This patch introduces a new abstraction: PrivilegedSQLObject. This is a data dictionary representation of a schema object which has privileges granted on it. In this patch, this abstraction covers two kinds of schema objects: sequences and udts. Over time, we may want to simplify the hierarchy descending from TupleDescriptor. More descendants of TupleDescriptor should be candidates to be PrivilegedSQLObjects represented in SYSPERMS.

Additional runtime enforcement of the new USAGE privilege. Enforcement code is shared by the EXECUTE and USAGE privileges. I have some reservations about the placement of this part of the runtime machinery in the data dictionary. To me it looks as though this code might be more comfortable in org.apache.derby.impl.sql.execute. However, that is outside the scope of this patch.

Rick Hillegas
added a comment - 17/Dec/09 19:42 Attaching derby-651-09-ac-usagePrivilege.diff. This patch wires in the USAGE privilege, which is needed for sequences and udts. The support for sequences can't be finished until a bit more work has been done on implementing the NEXT SEQUENCE FOR operator. Regression tests pass for me.
The USAGE privilege is stored in the SYSPERMS catalog, a 10.6 catalog which was introduced by earlier work on DERBY-712 . SYSPERMS is intended to be a general purpose permissions catalog. Over time, the complexity of our permissions implementation and the number of permissions catalogs can be reduced by storing other kinds of privileges in this catalog. For instance, it should be easy to store the EXECUTE privilege in SYSPERMS. Much of the implementation of the USAGE privilege parallels the implementation of the EXECUTE privilege.
This patch introduces a new abstraction: PrivilegedSQLObject. This is a data dictionary representation of a schema object which has privileges granted on it. In this patch, this abstraction covers two kinds of schema objects: sequences and udts. Over time, we may want to simplify the hierarchy descending from TupleDescriptor. More descendants of TupleDescriptor should be candidates to be PrivilegedSQLObjects represented in SYSPERMS.
Touches the following files:
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
M java/engine/org/apache/derby/impl/sql/catalog/PermissionsCacheable.java
M java/engine/org/apache/derby/iapi/sql/dictionary/PermDescriptor.java
M java/engine/org/apache/derby/impl/sql/catalog/SYSPERMSRowFactory.java
Added USAGE privileges to the permissions cache.
M java/engine/org/apache/derby/impl/sql/catalog/DDdependableFinder.java
M java/engine/org/apache/derby/impl/sql/catalog/CoreDDFinderClassInfo.java
Filled in some missing bits of the Formatable machinery for sequences and generic privileges.
M java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
M java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
M java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
Compiler support for the new USAGE privilege. The USAGE privilege can be granted on sequences and udts. However, the compiler creates runtime USAGE checks only for udts right now.
A java/engine/org/apache/derby/impl/sql/execute/GenericPrivilegeInfo.java
Runtime support for the new USAGE privilege.
A java/engine/org/apache/derby/iapi/sql/dictionary/PrivilegedSQLObject.java
M java/engine/org/apache/derby/iapi/sql/dictionary/AliasDescriptor.java
M java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java
Support for the new abstraction discussed above: a schema object which can have privileges granted on it.
M java/engine/org/apache/derby/iapi/sql/dictionary/StatementPermission.java
M java/engine/org/apache/derby/iapi/sql/dictionary/StatementRoutinePermission.java
A java/engine/org/apache/derby/iapi/sql/dictionary/StatementGenericPermission.java
Additional runtime enforcement of the new USAGE privilege. Enforcement code is shared by the EXECUTE and USAGE privileges. I have some reservations about the placement of this part of the runtime machinery in the data dictionary. To me it looks as though this code might be more comfortable in org.apache.derby.impl.sql.execute. However, that is outside the scope of this patch.
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
Some new error messages. A message related to EXECUTE permissions has been renamed and pressed into service to also serve the USAGE privilege.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
Basic tests for granting and revoking USAGE privilege on udts.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java
An assertion in this test needed to be updated to account for a new index added to SYSPERMS.

Attaching derby-651-10-aa-usageTriggers.diff. This patch adds regression tests to verify that you can't drop a UDT or revoke USAGE on it if that would orphan a trigger. Committed at subversion revision 892354.

Rick Hillegas
added a comment - 18/Dec/09 19:23 Attaching derby-651-10-aa-usageTriggers.diff. This patch adds regression tests to verify that you can't drop a UDT or revoke USAGE on it if that would orphan a trigger. Committed at subversion revision 892354.
Touches the following files:
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
Factored out some common code into method verifyDropRestrictions() and added test case for triggers.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
Added test case for revoking USAGE privilege on a type needed by a trigger.

Attaching derby-651-11-aa-dropSchema.diff. This patch prevents you from dropping a schema if it contains any types. I also noticed that it was possible to drop schemas which contained routines. I have made that illegal too. Regression tests passed for me. Committed at subversion revision 892485.

Basically, SYSALIASES is now not allowed to contain any rows that refer to the schema which is about to be dropped. Touches the following files:

Rick Hillegas
added a comment - 19/Dec/09 16:38 Attaching derby-651-11-aa-dropSchema.diff. This patch prevents you from dropping a schema if it contains any types. I also noticed that it was possible to drop schemas which contained routines. I have made that illegal too. Regression tests passed for me. Committed at subversion revision 892485.
Basically, SYSALIASES is now not allowed to contain any rows that refer to the schema which is about to be dropped. Touches the following files:
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
This is the code which enforces the rule that a schema must be empty before you drop it.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
New test to verify that a schema may not hold any routines or types if you are going to drop it.
M java/testing/org/apache/derbyTesting/junit/JDBC.java
Added a little logic so that UDTs are dropped along with routines when cleaning out a test schema.

Attaching derby-651-12-ab-metadata.diff. This patch adjusts JDBC metadata to account for the fact that UDTs can now be created (see the spec for a description of the necessary changes). Regression tests passed for me. Committed at subversion revision 893224.

Actually, the wrong results are returned for the ResultSetMetaData methods in the network client. This is a pre-existing bug and discrepancy with the embedded behavior. Apparently, when the network client was written, a deliberate decision was made to coerce object types to LONGVARBINARY. I have created DERBY-4491 to track this issue.

Rick Hillegas
added a comment - 22/Dec/09 16:36 Attaching derby-651-12-ab-metadata.diff. This patch adjusts JDBC metadata to account for the fact that UDTs can now be created (see the spec for a description of the necessary changes). Regression tests passed for me. Committed at subversion revision 893224.
Changes to metadata queries were needed for
DatabaseMetaData.getTypeInfo()
DatabaseMetaData.getUDTs()
Previous changes already resulted in the correct results for
DatabaseMetaData.getColumns()
ResultSetMetaData.getColumnType()
ResultSetMetaData.getColumnTypeName()
Actually, the wrong results are returned for the ResultSetMetaData methods in the network client. This is a pre-existing bug and discrepancy with the embedded behavior. Apparently, when the network client was written, a deliberate decision was made to coerce object types to LONGVARBINARY. I have created DERBY-4491 to track this issue.
Touches the following files:
M java/engine/org/apache/derby/impl/jdbc/metadata.properties
M java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
Changes for DatabaseMetaData.getTypeInfo() and DatabaseMetaData.getUDTs().
M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
Added a new test for DatabaseMetaData.getUDTs() and removed it from the test of vacuous methods.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
M java/testing/org/apache/derbyTesting/functionTests/master/connectionJdbc20.out
Accounted for the new type (JAVA_OBJECT) returned by DatabaseMetaData.getTypeInfo().

Rick Hillegas
added a comment - 05/Feb/10 18:43 Attaching derby-651-13-aa-tableFunctionColumns.diff. This patch makes it possible to use UDTs as columns in the ResultSets returned by table functions. Regression tests are running.
Touches the following files:
--------
M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
Added logic for binding row multi sets which contain UDT columns.
--------
M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
Always re-bind the return value of a function, even if it is not itself a UDT. This is because the return type could be a row multi set which has a UDT column.
--------
M java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
Add logic to create dependencies on UDT-typed columns in table functions.
--------
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
Added a test case to verify that UDTs can be columns in table functions.

Attaching derby-651-14-aa-testBigUDTs.diff. This patch adds tests to verify that you can store and retrieve large UDTs. The tests store and retrieve UDTs that serialize to around 90K and 1000K bytes. Committed at subversion revision revision 907509.

Touches the following files:

A java/testing/org/apache/derbyTesting/functionTests/tests/lang/FakeByteArray.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.java

Rick Hillegas
added a comment - 07/Feb/10 22:54 Attaching derby-651-14-aa-testBigUDTs.diff. This patch adds tests to verify that you can store and retrieve large UDTs. The tests store and retrieve UDTs that serialize to around 90K and 1000K bytes. Committed at subversion revision revision 907509.
Touches the following files:
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/FakeByteArray.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.java

Attaching derby-651-15-aa-testSQLData.diff. This adds a test to verify that a class does not become storeable simply because it implements SQLData. Part 13 of the SQL Standard describes user defined types which implement SQLData, but Derby does not support these types right now. Committed at subversion revision 907668.

Rick Hillegas
added a comment - 08/Feb/10 14:30 Attaching derby-651-15-aa-testSQLData.diff. This adds a test to verify that a class does not become storeable simply because it implements SQLData. Part 13 of the SQL Standard describes user defined types which implement SQLData, but Derby does not support these types right now. Committed at subversion revision 907668.
Touches the following files:
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/SampleSQLData.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java

Attaching version 5 of the spec for this first increment. This version clarifies subtyping behavior and adds a section on the dblook tool. Although more testing needs to be done, I believe that the functionality described by this spec has now been implemented.

Rick Hillegas
added a comment - 12/Feb/10 20:41 Attaching version 5 of the spec for this first increment. This version clarifies subtyping behavior and adds a section on the dblook tool. Although more testing needs to be done, I believe that the functionality described by this spec has now been implemented.