Programming user-defined types

+ +

The key to designing a good user-defined type is to remember that data +evolves over time, just like code. A good user-defined type has version +information built into it. This allows the user-defined data to upgrade itself +as the application changes. For this reason, it is a good idea for a +user-defined type to implement java.io.Externalizable and not just +java.io.Serializable. Although the SQL standard allows a Java class to +implement only java.io.Serializable, this is bad practice for the +following reasons:

Recompilation - If the second version of your application is +compiled on a different platform from the first version, then your serialized +objects may fail to deserialize. 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.
Evolution - Your tools for evolving a class which simply implements +java.io.Serializable are very limited.

Fortunately, it is easy to write a version-aware UDT which implements +java.io.Serializable and can evolve itself over time. For example, here +is the first version of such a class:

+ +package com.acme.types; + +import java.io.*; +import java.math.*; + +public class Price implements Externalizable +{ + // initial version id + private static final int FIRST_VERSION = 0; + + public String currencyCode; + public BigDecimal amount; + + // zero-arg constructor needed by Externalizable machinery + public Price() {} + + public Price( String currencyCode, BigDecimal amount ) + { + this.currencyCode = currencyCode; + this.amount = amount; + } + + // Externalizable implementation + public void writeExternal(ObjectOutput out) throws IOException + { + // first write the version id + out.writeInt( FIRST_VERSION ); + + // now write the state + out.writeObject( currencyCode ); + out.writeObject( amount ); + } + + public void readExternal(ObjectInput in) + throws IOException, ClassNotFoundException + { + // read the version id + int oldVersion = in.readInt(); + if ( oldVersion < FIRST_VERSION ) { + throw new IOException( "Corrupt data stream." ); + } + if ( oldVersion > FIRST_VERSION ) { + throw new IOException( "Can't deserialize from the future." ); + } + + currencyCode = (String) in.readObject(); + amount = (BigDecimal) in.readObject(); + } +} +

After this, it is easy to write a second version of the user-defined type +which adds a new field. When old versions of Price values are +read from the database, they upgrade themselves on the fly. Changes are shown +in bold:

+ +package com.acme.types; + +import java.io.*; +import java.math.*; +import java.sql.*; + +public class Price implements Externalizable +{ + // initial version id + private static final int FIRST_VERSION = 0; + private static final int TIMESTAMPED_VERSION = FIRST_VERSION + 1; + + private static final Timestamp DEFAULT_TIMESTAMP = new Timestamp( 0L ); + + public String currencyCode; + public BigDecimal amount; + public Timestamp timeInstant; + + // 0-arg constructor needed by Externalizable machinery + public Price() {} + + public Price( String currencyCode, BigDecimal amount, + Timestamp timeInstant ) + { + this.currencyCode = currencyCode; + this.amount = amount; + this.timeInstant = timeInstant; + } + + // Externalizable implementation + public void writeExternal(ObjectOutput out) throws IOException + { + // first write the version id + out.writeInt( TIMESTAMPED_VERSION ); + + // now write the state + out.writeObject( currencyCode ); + out.writeObject( amount ); + out.writeObject( timeInstant ); + } + + public void readExternal(ObjectInput in) + throws IOException, ClassNotFoundException + { + // read the version id + int oldVersion = in.readInt(); + if ( oldVersion < FIRST_VERSION ) { + throw new IOException( "Corrupt data stream." ); + } + if ( oldVersion > TIMESTAMPED_VERSION ) { + throw new IOException( "Can't deserialize from the future." ); + } + + currencyCode = (String) in.readObject(); + amount = (BigDecimal) in.readObject(); + + if ( oldVersion >= TIMESTAMPED_VERSION ) { + timeInstant = (Timestamp) in.readObject(); + } + else { + timeInstant = DEFAULT_TIMESTAMP; + } + } +} +

An application needs to keep its code in sync across all tiers. This is true +for all Java code which runs both in the client and in the server. This is true +for functions and procedures which run in multiple tiers. It is also true for +user-defined types which run in multiple tiers. The programmer should code +defensively for the case when the client and server are running different +versions of the application code. In particular, the programmer should write +defensive serialization logic for user-defined types so that the application +gracefully handles client/server version mismatches.

Describes the columns within all tables in the current database: +

Describes the columns within all tables in the current database:

CHAR 128 false -column or parameter name +Column or parameter name COLUMNNUMBER INT 4 false -the position of the column within the table +The position of the column within the table COLUMNDATATYPE -org.apache.derby.catalog. TypeDescriptor

This class +org.apache.derby.catalog. +TypeDescriptor

This class is not part of the public API.

- + false -system type that describes precision, length, scale, nullability, -type name, and storage type of data +System type that describes precision, length, scale, nullability, +type name, and storage type of data. For a user-defined type, this column can +hold a TypeDescriptor that refers to the appropriate type alias in +SYS.SYSALIASES. COLUMNDEFAULT java.io.Serializable - + true -for tables, describes default value of the column. The toString() method +For tables, describes default value of the column. The toString() method on the object stored in the table returns the text of the default value as specified in the CREATE TABLE or ALTER TABLE statement. @@ -80,34 +83,34 @@ specified in the CREATE TABLE or ALTER T CHAR 36 true -unique identifier for the default value +Unique identifier for the default value AUTOINCREMENT COLUMNVALUE BIGINT - + true -what the next value for column will be, if the column is +What the next value for column will be, if the column is an identity column AUTOINCREMENT COLUMNSTART BIGINT - + true -initial value of column (if specified), if it is an identity +Initial value of column (if specified), if it is an identity column AUTOINCREMENT COLUMNINC BIGINT - + true -amount column value is automatically incremented (if specified), +Amount column value is automatically incremented (if specified), if the column is an identity column -

Describes the procedures and functions in the database. +
Describes the procedures, functions, and user-defined types in the +database.
+
CHAR 36 false -unique identifier for the alias +Unique identifier for the alias ALIAS VARCHAR 128 false -alias +Alias (in the case of a user-defined type, the name of the +user-defined type) SCHEMAID CHAR 36 true -reserved for future use +Reserved for future use JAVACLASSNAME LONGVARCHAR 255 false -the Java class name +The Java class name ALIASTYPE CHAR 1 false -'F' (function)'P' (procedure) +'F' (function), 'P' (procedure), +'A' (user-defined type) NAMESPACE CHAR 1 false -'F' (function)'P' (procedure) +'F' (function), 'P' (procedure), +'A' (user-defined type) SYSTEMALIAS BOOLEAN - + false true (system supplied or built-in alias)
false (alias created by a user)
ALIASINFO - org.apache.derby. -catalog.AliasInfo:
This class is not part of the public API
- +org.apache.derby. +catalog.AliasInfo
This class is not part of the public API.
+ true A Java interface that encapsulates the additional information that is specific to an alias @@ -101,10 +105,10 @@ that is specific to an alias VARCHAR 128 false -system-generated identifier +System-generated identifier -

A value of a user-defined type can be assigned to a value of any supertype of +that user-defined type. However, no explicit casts of user-defined types are +allowed.

Mapping SQL data types to Java data types

-The following table shows how Derby maps specific SQL data types to Java -data types: +The following table shows how + maps specific SQL data +types to Java data types:

sequenceName -[ CREATE SEQUENCE [ schemaName. ] SQL92Identifier -
If schemaName is not provided, the current schema is the default -schema. If a qualified sequence name is specified, the schema name cannot -begin with SYS.

sequenceElement { AS dataType Added: db/derby/docs/trunk/src/ref/rrefsqljcreatetype.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljcreatetype.dita?rev=935706&view=auto ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljcreatetype.dita (added) +++ db/derby/docs/trunk/src/ref/rrefsqljcreatetype.dita Mon Apr 19 18:58:48 2010 @@ -0,0 +1,108 @@ + + + + + +CREATE TYPE statement + +CREATE TYPE statement +SQL statementsCREATE TYPE +user-defined typescreating +data typesuser-defined + + + +
+The CREATE TYPE statement creates a user-defined type (UDT). A UDT is a +serializable Java class whose instances are stored in columns. The class must +implement the java.io.Serializable interface.
+Syntax +CREATE TYPE [ schemaName. ] SQL92Identifier +EXTERNAL NAME singleQuotedJavaClassName +LANGUAGE JAVA +
The type name is composed of an optional schemaName and a +SQL92Identifier. If a schemaName is not provided, the current +schema is the default schema. If a qualified type name is specified, the schema +name cannot begin with SYS.
+
If the Java class does not implement java.io.Serializable, or if it is +not public and visible on the classpath, + raises an exception +when preparing statements which refer to the UDT.
+
A UDT cannot be cast explicitly to any other type, and no other type can be +cast to a UDT.
+
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.
+
You can use subtypes in UDTs. That is, if you use the CREATE TYPE statement +to bind a class named C to a UDT, you can populate that UDT value with an +instance of any subclass of C.
+ +Example +CREATE TYPE price +EXTERNAL NAME 'com.acme.types.Price' +LANGUAGE JAVA + +
Using user-defined types +
You can create tables and views with columns that have UDTs. For example:
+CREATE TABLE order +( + orderID INT GENERATED ALWAYS AS IDENTITY, + customerID INT REFERENCES customer( customerID ), + totalPrice typeSchema.price +); +
Although UDTs have no natural order, you can use generated columns to provide +useful sort orders:
+ALTER TABLE order + ADD COLUMN normalizedValue DECIMAL( 31, 5 ) GENERATED ALWAYS AS + ( convert( 'EUR', TIMESTAMP('2005-01-01 09:00:00'), totalPrice ) ); +CREATE INDEX normalizedOrderPrice ON order( normalizedValue ); +
You can use factory functions to construct UDTs. For example: +
+INSERT INTO order( customerID, totalPrice ) + VALUES ( 12345, + makePrice( 'USD', + CAST( 9.99 AS DECIMAL( 31, 5 ) ), + TIMESTAMP('2009-10-16 14:24:43') ) ); +
Once a UDT column has been populated, you can use it in other INSERT and +UPDATE statements. For example:
+INSERT INTO backOrder SELECT * from order; + +UPDATE order SET totalPrice = ( SELECT todaysDiscount FROM discount ); +UPDATE order SET totalPrice = adjustForInflation( totalPrice ); +
Using functions, you can access fields inside UDTs in a SELECT statement:
+SELECT getCurrencyCode( totalPrice ) from order; +
You can use JDBC API setObject() and getObject() methods to +store and retrieve values of UDTs. 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) rs.getObject( 3 ); + ... +} +
+ + Propchange: db/derby/docs/trunk/src/ref/rrefsqljcreatetype.dita ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/docs/trunk/src/ref/rrefsqljdropsequence.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljdropsequence.dita?rev=935706&r1=935705&r2=935706&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljdropsequence.dita (original) +++ db/derby/docs/trunk/src/ref/rrefsqljdropsequence.dita Mon Apr 19 18:58:48 2010 @@ -30,21 +30,20 @@ limitations under the License.
The DROP SEQUENCE statement removes a sequence generator that was created using a .
Syntax -DROP SEQUENCE sequenceName RESTRICT +DROP SEQUENCE [ schemaName. ] SQL92Identifier RESTRICT +
The sequence name is composed of an optional schemaName and a +SQL92Identifier. If a schemaName is not provided, the current +schema is the default schema. If a qualified sequence name is specified, the +schema name cannot begin with SYS.

The RESTRICT keyword is required. If a trigger or view references the sequence generator, throws an exception.

Dropping a sequence generator implicitly drops all USAGE privileges that reference it.
-
sequenceName -[ schemaName. ] SQL92Identifier -
If schemaName is not provided, the current schema is the default -schema. If a qualified sequence name is specified, the schema name cannot -begin with SYS.
Example DROP SEQUENCE order_id RESTRICT; Added: db/derby/docs/trunk/src/ref/rrefsqljdroptype.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljdroptype.dita?rev=935706&view=auto ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljdroptype.dita (added) +++ db/derby/docs/trunk/src/ref/rrefsqljdroptype.dita Mon Apr 19 18:58:48 2010 @@ -0,0 +1,65 @@ + + + + + +DROP TYPE statement + +DROP TYPE statement +SQL statementsDROP TYPE +user-defined typesdropping +data typesuser-defined + + + +
The DROP TYPE statement removes a user-defined type (UDT) that was +created using a +.
+
+Syntax +DROP TYPE [ schemaName. ] SQL92Identifier RESTRICT +
The type name is composed of an optional schemaName and a +SQL92Identifier. If a schemaName is not provided, the current +schema is the default schema. If a qualified type name is specified, the schema +name cannot begin with SYS.
+
The RESTRICT keyword is required. CASCADE semantics are not supported. That +is, will not track down +and drop orphaned objects.
+
Dropping a UDT implicitly drops all USAGE privileges that reference it.
+
You cannot drop a type if it would make another SQL object unusable. This +includes the following restrictions:
+
+
Table columns: No table columns have this UDT.
+
Views: No view definition involves expressions which have this UDT.
+
Constraints: No constraints reference expressions of this UDT.
+
Generated columns: No generated columns reference expressions of this +UDT.
+
Routines: No functions or procedures have arguments or return values of this +UDT.
+
Table Functions: No table functions return tables with columns of this +UDT.
+
+ +Example +DROP TYPE price RESTRICT; + + + Propchange: db/derby/docs/trunk/src/ref/rrefsqljdroptype.dita ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/docs/trunk/src/ref/rrefsqljgrant.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljgrant.dita?rev=935706&r1=935705&r2=935706&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljgrant.dita (original) +++ db/derby/docs/trunk/src/ref/rrefsqljgrant.dita Mon Apr 19 18:58:48 2010 @@ -40,7 +40,7 @@ from a table.
Create a trigger on a table.

Update data in a table or in a subset of columns in a table.

Run a specified function or procedure.
-
Use a sequence generator.
+
Use a sequence generator or a user-defined type.

Before you issue a GRANT statement, check that the derby.database.sqlAuthorization property is set to true. The derby.database.sqlAuthorization property @@ -50,7 +50,7 @@ object if you are the owner of the objec CREATE statement for the database object that you want to grant privileges on for more information.
The syntax that you use for the GRANT statement depends on whether you are granting -privileges to a table, a routine, or a sequence generator, or granting a role.
+privileges to a schema object or granting a role.

For more information on using the GRANT statement, see "Using SQL standard authorization" in the .

Syntax for sequence generators -GRANT USAGE ON SEQUENCE sequenceName TO grantees +GRANT USAGE ON SEQUENCE [ schemaName. ] SQL92Identifier TO grantees

In order to use a sequence generator, you must have the USAGE privilege on it. This privilege can be granted to users and to roles. See for more -information.

Syntax for user-defined types +GRANT USAGE ON TYPE [ schemaName. ] SQL92Identifier TO grantees +

In order to use a user-defined type, you must have the USAGE privilege on +it. This privilege can be granted to users and to roles. See + for more +information.

The type name is composed of an optional schemaName and a +SQL92Identifier. If a schemaName is not provided, the current +schema is the default schema. If a qualified type name is specified, the schema +name cannot begin with SYS.

Syntax for roles GRANT roleName [ {, roleName }* ] TO

routine-designator{ function-name | procedure-name }

sequenceName -[ schemaName. ] SQL92Identifier -

If schemaName is not provided, the current schema is the default -schema. If a qualified sequence name is specified, the schema name cannot -begin with SYS.

Syntax for sequence generators -REVOKE USAGE ON SEQUENCE sequenceName FROM grantees RESTRICT +REVOKE USAGE ON SEQUENCE [ schemaName. ] SQL92Identifier FROM grantees RESTRICT

In order to use a sequence generator, you must have the USAGE privilege on it. This privilege can be revoked from users and roles. Only RESTRICTed revokes are allowed. This means that the REVOKE statement cannot make a view, trigger, or constraint unusable by its owner. The USAGE privilege cannot be revoked from the schema owner. See for more -information.

Syntax for user-defined types +REVOKE USAGE ON TYPE [ schemaName. ] SQL92Identifier FROM grantees RESTRICT +

In order to use a user-defined type, you must have the USAGE privilege on +it. This privilege can be revoked from users and roles. Only RESTRICTed revokes +are allowed. This means that the REVOKE statement cannot make a view, trigger, +or constraint unusable by its owner. The USAGE privilege cannot be revoked from +the schema owner. See + for more +information.

Syntax for roles REVOKE roleName [ {, roleName }* ] FROM order_id from the role sales_role, use the following syntax:
REVOKE USAGE ON SEQUENCE order_id FROM sales_role; +
To revoke the USAGE privilege on the user-defined type +price from the role finance_role, use the +following syntax:
+REVOKE USAGE ON TYPE price FROM finance_role; Added: db/derby/docs/trunk/src/ref/rrefsqljudt.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljudt.dita?rev=935706&view=auto ============================================================================== --- db/derby/docs/trunk/src/ref/rrefsqljudt.dita (added) +++ db/derby/docs/trunk/src/ref/rrefsqljudt.dita Mon Apr 19 18:58:48 2010 @@ -0,0 +1,43 @@ + + + + + +User-defined types + +user-defined types +data typesuser-defined + + +
allows +you to create user-defined types. A user-defined type is a serializable Java +class whose instances are stored in columns. The class must implement the +java.io.Serializable interface.
+
For information on creating and removing types, see + and +. See + and + for information on usage +privileges for types.
+
For information on writing the Java classes that implement user-defined +types, see "Programming user-defined types" in the +.
+
+ + Propchange: db/derby/docs/trunk/src/ref/rrefsqljudt.dita ------------------------------------------------------------------------------ svn:eol-style = native