You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2009/10/08 01:05:31 UTC
[jira] Commented: (DERBY-651) Re-enable the storing of java objects
in the database
[ 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
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?
> 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.