Derby
  1. Derby
  2. DERBY-651

Re-enable the storing of java objects in the database

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Patch Available

      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.

      1. UserDefinedTypes.html
        43 kB
        Rick Hillegas
      2. UserDefinedTypes.html
        47 kB
        Rick Hillegas
      3. UserDefinedTypes.html
        53 kB
        Rick Hillegas
      4. UserDefinedTypes.html
        52 kB
        Rick Hillegas
      5. UserDefinedTypes.html
        53 kB
        Rick Hillegas
      6. derby-651-15-aa-testSQLData.diff
        6 kB
        Rick Hillegas
      7. derby-651-14-aa-testBigUDTs.diff
        14 kB
        Rick Hillegas
      8. derby-651-13-aa-tableFunctionColumns.diff
        10 kB
        Rick Hillegas
      9. derby-651-12-ab-metadata.diff
        15 kB
        Rick Hillegas
      10. derby-651-11-aa-dropSchema.diff
        6 kB
        Rick Hillegas
      11. derby-651-10-aa-usageTriggers.diff
        15 kB
        Rick Hillegas
      12. derby-651-09-ac-usagePrivilege.diff
        88 kB
        Rick Hillegas
      13. derby-651-08-aa-dependencyRoutines.diff
        20 kB
        Rick Hillegas
      14. derby-651-07-aa-dependencyView.diff
        6 kB
        Rick Hillegas
      15. derby-651-06-aa-dropTable.diff
        7 kB
        Rick Hillegas
      16. derby-651-05-ac-dependencyTable.diff
        31 kB
        Rick Hillegas
      17. derby-651-04-aa-javadoc.diff
        4 kB
        Rick Hillegas
      18. derby-651-03-aa-udttestInstability.diff
        2 kB
        Rick Hillegas
      19. derby-651-02-af-udtColumnsRetvalsParams.diff
        43 kB
        Rick Hillegas
      20. derby-651-01-aa-basicCreateDropType.diff
        25 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Satheesh Bandaram added a comment -

          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.

          Show
          Satheesh Bandaram added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          Thanks, Satheesh. You're right, this is an enhancement.

          Show
          Rick Hillegas added a comment - Thanks, Satheesh. You're right, this is an enhancement.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Dag H. Wanvik added a comment -

          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?

          Show
          Dag H. Wanvik added a comment - 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?
          Hide
          Rick Hillegas added a comment -

          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?

          Show
          Rick Hillegas added a comment - 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?
          Hide
          Rick Hillegas added a comment -

          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

          Show
          Rick Hillegas added a comment - 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
          Hide
          Mike Matrigali added a comment -

          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.

          Show
          Mike Matrigali added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          Attaching version 4 of the spec, incorporating feedback from Kim Haase.

          Show
          Rick Hillegas added a comment - Attaching version 4 of the spec, incorporating feedback from Kim Haase.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          Committed derby-651-01-aa-basicCreateDropType.diff at subversion revision 882569.

          Show
          Rick Hillegas added a comment - Committed derby-651-01-aa-basicCreateDropType.diff at subversion revision 882569.
          Hide
          Rick Hillegas added a comment -

          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:

          create type Price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java;

          create table orders( orderID int generated always as identity, customerID int, totalPrice price );

          create function makePrice( currencyCode char( 3 ), amount decimal( 31, 5 ), timeInstant Timestamp )
          returns Price language java parameter style java no sql
          external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice';
          create function getCurrencyCode( price Price ) returns char( 3 ) language java parameter style java no sql
          external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getCurrencyCode';
          create function getAmount( price Price ) returns decimal( 31, 5 ) language java parameter style java no sql
          external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getAmount';
          create function getTimeInstant( price Price ) returns timestamp language java parameter style java no sql
          external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getTimeInstant';

          insert into orders( customerID, totalPrice ) values
          ( 12345, makePrice( 'USD', cast( 9.99 as decimal( 31, 5 ) ), timestamp('2009-10-16 14:24:43') ) );

          select getCurrencyCode( totalPrice ), getAmount( totalPrice ), getTimeInstant( totalPrice ) from orders;

          select totalPrice from orders;

          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:

          M java/engine/org/apache/derby/iapi/types/TypeId.java
          M java/engine/org/apache/derby/iapi/types/UserType.java
          M java/engine/org/apache/derby/catalog/TypeDescriptor.java
          M java/engine/org/apache/derby/catalog/types/DecimalTypeIdImpl.java
          M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
          M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java
          M java/engine/org/apache/derby/catalog/types/UserDefinedTypeIdImpl.java

          This adds a persistable schema qualified name to the existing UserDefinedType object.

          M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
          M java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
          M java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
          M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java

          Changes to allow schema qualified UDT names in CREATE TABLE/FUNCTION/PROCEDURE statements.

          M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java

          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.

          M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java

          Small changes to support the use of UDT values at execution time.

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

          Some basic tests to verify that you can create columns, parameters, and return values of UDT type.

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ErrorCodeTest.java
          M java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out

          Changes some tests to account for the fact that the parser now raises a different error when it encounters illegal type declarations.

          The regression tests pass for me.

          Show
          Rick Hillegas added a comment - 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: create type Price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java; create table orders( orderID int generated always as identity, customerID int, totalPrice price ); create function makePrice( currencyCode char( 3 ), amount decimal( 31, 5 ), timeInstant Timestamp ) returns Price language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'; create function getCurrencyCode( price Price ) returns char( 3 ) language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getCurrencyCode'; create function getAmount( price Price ) returns decimal( 31, 5 ) language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getAmount'; create function getTimeInstant( price Price ) returns timestamp language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getTimeInstant'; insert into orders( customerID, totalPrice ) values ( 12345, makePrice( 'USD', cast( 9.99 as decimal( 31, 5 ) ), timestamp('2009-10-16 14:24:43') ) ); select getCurrencyCode( totalPrice ), getAmount( totalPrice ), getTimeInstant( totalPrice ) from orders; select totalPrice from orders; 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: M java/engine/org/apache/derby/iapi/types/TypeId.java M java/engine/org/apache/derby/iapi/types/UserType.java M java/engine/org/apache/derby/catalog/TypeDescriptor.java M java/engine/org/apache/derby/catalog/types/DecimalTypeIdImpl.java M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java M java/engine/org/apache/derby/catalog/types/UserDefinedTypeIdImpl.java This adds a persistable schema qualified name to the existing UserDefinedType object. M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj M java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java M java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java Changes to allow schema qualified UDT names in CREATE TABLE/FUNCTION/PROCEDURE statements. M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java 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. M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java Small changes to support the use of UDT values at execution time. A java/testing/org/apache/derbyTesting/functionTests/tests/lang/Price.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java Some basic tests to verify that you can create columns, parameters, and return values of UDT type. M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ErrorCodeTest.java M java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out Changes some tests to account for the fact that the parser now raises a different error when it encounters illegal type declarations. The regression tests pass for me.
          Hide
          Rick Hillegas added a comment -

          Committed derby-651-02-af-udtColumnsRetvalsParams.diff at subversion revision 884970.

          Show
          Rick Hillegas added a comment - Committed derby-651-02-af-udtColumnsRetvalsParams.diff at subversion revision 884970.
          Hide
          Dag H. Wanvik added a comment -

          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...
          • Many lines > 80
          • UserDefinedTypeIdImpl#isBound:
          • lacks proper javadoc @return tag (more methods do too)
          • simplify return !(className == null) ->
            return className != null
          • TypeDescriptor#isUserDefinedType:
          • lacks proper javadoc @return tag
          • BaseTypeIdImpl#getSchemaName, getUnqualifiedName: lack proper javadoc
            @return tag
          • TypeDescriptorImpl#isUserDefinedType: Javadoc: suggest {@inheritDoc}

            instead of @see
            isBound: lacks proper javadoc @return tag

          • TypeId#getUserDefinedTypeId lacks all javadoc tags
          • ColumnDefinitionNode.java: spurious blank lines introduced
          • UserType.java: would be nice to see docs describing difference between
            setValue, setObject. Both have an Object parameter...
          • QueryTreeNode#bindUserType: lacks all javadoc tags
          Show
          Dag H. Wanvik added a comment - 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... Many lines > 80 UserDefinedTypeIdImpl#isBound: lacks proper javadoc @return tag (more methods do too) simplify return !(className == null) -> return className != null TypeDescriptor#isUserDefinedType: lacks proper javadoc @return tag BaseTypeIdImpl#getSchemaName, getUnqualifiedName: lack proper javadoc @return tag TypeDescriptorImpl#isUserDefinedType: Javadoc: suggest {@inheritDoc} instead of @see isBound: lacks proper javadoc @return tag TypeId#getUserDefinedTypeId lacks all javadoc tags ColumnDefinitionNode.java: spurious blank lines introduced UserType.java: would be nice to see docs describing difference between setValue, setObject. Both have an Object parameter... QueryTreeNode#bindUserType: lacks all javadoc tags
          Hide
          Rick Hillegas added a comment -

          Attaching derby-651-03-aa-udttestInstability.diff, which hopefully commits an instability in UDTTest which seems to be VM-specific. Committed at subversion revision 885114.

          Show
          Rick Hillegas added a comment - Attaching derby-651-03-aa-udttestInstability.diff, which hopefully commits an instability in UDTTest which seems to be VM-specific. Committed at subversion revision 885114.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Dag H. Wanvik added a comment - - 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.

          Show
          Dag H. Wanvik added a comment - - 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.
          Hide
          Rick Hillegas added a comment -

          Thanks, Dag. Fixed the typo at subversion revision 885489.

          Show
          Rick Hillegas added a comment - Thanks, Dag. Fixed the typo at subversion revision 885489.
          Hide
          Rick Hillegas added a comment - - 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.

          Show
          Rick Hillegas added a comment - - 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.
          Hide
          Rick Hillegas added a comment -

          Committed derby-651-05-ac-dependencyTable.diff at subversion revision 888811.

          Show
          Rick Hillegas added a comment - Committed derby-651-05-ac-dependencyTable.diff at subversion revision 888811.
          Hide
          Kathey Marsden added a comment -

          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

          Show
          Kathey Marsden added a comment - 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
          Hide
          Rick Hillegas added a comment -

          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

          Show
          Rick Hillegas added a comment - 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
          Hide
          Knut Anders Hatlen added a comment -

          Hi Rick,

          I noticed this error while playing with the feature:

          ij> create type java_string external name 'java.lang.String' language java;
          0 rows inserted/updated/deleted
          ij> create table t(x java_string);
          0 rows inserted/updated/deleted
          ij> drop table t;
          0 rows inserted/updated/deleted
          ij> drop type java_string restrict;
          ERROR 42X94: Table '341cc09e-0125-7948-f4fb-00003fb80d23' does not exist.

          Show
          Knut Anders Hatlen added a comment - Hi Rick, I noticed this error while playing with the feature: ij> create type java_string external name 'java.lang.String' language java; 0 rows inserted/updated/deleted ij> create table t(x java_string); 0 rows inserted/updated/deleted ij> drop table t; 0 rows inserted/updated/deleted ij> drop type java_string restrict; ERROR 42X94: Table '341cc09e-0125-7948-f4fb-00003fb80d23' does not exist.
          Hide
          Rick Hillegas added a comment -

          Thanks for kicking the tires, Knut! I'll take a look at that one. Cheers-Rick

          Show
          Rick Hillegas added a comment - Thanks for kicking the tires, Knut! I'll take a look at that one. Cheers-Rick
          Hide
          Kathey Marsden added a comment -

          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.

          Show
          Kathey Marsden added a comment - 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.
          Hide
          Knut Anders Hatlen added a comment -

          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

          Show
          Knut Anders Hatlen added a comment - 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
          Hide
          Rick Hillegas added a comment -

          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!

          Show
          Rick Hillegas added a comment - 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!
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-651-07-aa-dependencyView.diff. This adds dependencies of views on UDTs. Regression tests pass for me. Committed at subversion revision 889876.

          Touches the following files:

          M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java

          Adds a method for adding dependencies on UDTs.

          M java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
          M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java

          Wires that method into nodes which views rely on.

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/Price.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java

          Basic tests for dependencies of views on UDTs.

          Show
          Rick Hillegas added a comment - Attaching derby-651-07-aa-dependencyView.diff. This adds dependencies of views on UDTs. Regression tests pass for me. Committed at subversion revision 889876. Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Adds a method for adding dependencies on UDTs. M java/engine/org/apache/derby/impl/sql/compile/ValueNode.java M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java Wires that method into nodes which views rely on. M java/testing/org/apache/derbyTesting/functionTests/tests/lang/Price.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java Basic tests for dependencies of views on UDTs.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Knut Anders Hatlen added a comment -

          Hi Rick,

          There was one little piece of the 08 patch I didn't quite understand. Why does AliasDescriptor.isValid() have to be synchronized?

          Show
          Knut Anders Hatlen added a comment - Hi Rick, There was one little piece of the 08 patch I didn't quite understand. Why does AliasDescriptor.isValid() have to be synchronized?
          Hide
          Rick Hillegas added a comment -

          Hi Knut,

          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:

          Synchronized:

          Alias
          Constraint
          Default
          Sequence
          SPS
          Table
          Trigger

          Unsynchronized:

          BaseActivation
          GenericActivationHolder
          GenericPreparedStatement
          View

          Show
          Rick Hillegas added a comment - Hi Knut, 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: Synchronized: Alias Constraint Default Sequence SPS Table Trigger Unsynchronized: BaseActivation GenericActivationHolder GenericPreparedStatement View
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for investigating, Rick. Looks like it's an established pattern, then, and not something that needs to be addressed in this issue.

          Show
          Knut Anders Hatlen added a comment - Thanks for investigating, Rick. Looks like it's an established pattern, then, and not something that needs to be addressed in this issue.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          Committed derby-651-09-ac-usagePrivilege.diff at subversion revision 892272.

          Show
          Rick Hillegas added a comment - Committed derby-651-09-ac-usagePrivilege.diff at subversion revision 892272.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

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

          Show
          Rick Hillegas added a comment - 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().
          Hide
          Myrna van Lunteren added a comment -

          Since Dec 18, I see the following javadoc warnings:

          [javadoc] <mypathtotrunk>\java\engine\org\apache\derby\iapi\sql\dictionary\DataDictionary.java:2035: warning - @param argument "granteeAuthid" is not a parameter name.
          [javadoc] <mypathtotrunk>\java\engine\org\apache\derby\impl\sql\catalog\DataDictionaryImpl.java:12843: warning - @param argument "granteeAuthid" is not a parameter name.

          Show
          Myrna van Lunteren added a comment - Since Dec 18, I see the following javadoc warnings: [javadoc] <mypathtotrunk>\java\engine\org\apache\derby\iapi\sql\dictionary\DataDictionary.java:2035: warning - @param argument "granteeAuthid" is not a parameter name. [javadoc] <mypathtotrunk>\java\engine\org\apache\derby\impl\sql\catalog\DataDictionaryImpl.java:12843: warning - @param argument "granteeAuthid" is not a parameter name.
          Hide
          Rick Hillegas added a comment -

          Thanks for spotting this, Myrna. Javadoc corrected at subversion revision 893795.

          Show
          Rick Hillegas added a comment - Thanks for spotting this, Myrna. Javadoc corrected at subversion revision 893795.
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          Tests ran cleanly for me on derby-651-13-aa-tableFunctionColumns.diff. Committed at subversion revision 907097.

          Show
          Rick Hillegas added a comment - Tests ran cleanly for me on derby-651-13-aa-tableFunctionColumns.diff. Committed at subversion revision 907097.
          Hide
          Rick Hillegas added a comment -

          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

          Show
          Rick Hillegas added a comment - 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
          Hide
          Rick Hillegas added a comment -

          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

          Show
          Rick Hillegas added a comment - 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
          Hide
          Rick Hillegas added a comment -

          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.

          Show
          Rick Hillegas added a comment - 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.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development