db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-651) Re-enable the storing of java objects in the database
Date Wed, 07 Oct 2009 23:05:31 GMT

    [ https://issues.apache.org/jira/browse/DERBY-651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12763296#action_12763296

Dag H. Wanvik commented on DERBY-651:

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

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

>       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
>           (
>               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?


>       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

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


>       * 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_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


>       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?

> Re-enable the storing of java objects in the database
> -----------------------------------------------------
>                 Key: DERBY-651
>                 URL: https://issues.apache.org/jira/browse/DERBY-651
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Rick Hillegas
>         Attachments: UserDefinedTypes.html
> 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, cumbersom as it is, would do.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message