Custom types / "long" datatypes in custom functions

[David Blasby <db...@gmail.com>]

I released a spatial datablade for derby a few weeks ago.  There were
some fundamental problems that I mentioned when I first messaged this
list.

1.  Custom Types
     It would be a great to be able to store arbitrary java Objects in
the database - and Derby would handle the
serialization/deserialization.  I was thinking something like:

CREATE TYPE Geometry AS "com.vividsolutions.jts.geom.Geometry";
then being able to do things like:
CREATE TABLE mytable (g Geometry, description VARCHAR(100));

Alternately, just being able to "tag" a BLOB with a type name would
enough. Something like:
CREATE TYPE Geometry [ALIAS OF BLOB]; 

2.   Functions
   Currently, functions cannot send/return non-fixed-length data
types.  It would be great to be able to either (1) send custom types
to custom functions OR (2) send BLOB-like things to custom functions.
(oh, are BLOBs inefficient to use?)



Currently, I use a "VARCHAR(1000000)" as my Geometry type, and stick a
base-64 encoding of the binary version of the Geometry inside.  This
is pretty messy and inefficient.  Not to mention the fact that you can
run spatial functions on text like 'how now brown cow'.

Is there anything planned to address issues of these types?


dave
ps. more information on the Spatial DB in a Box here (see
"Derby/Cloudbase bindings"):
http://docs.codehaus.org/display/GEOS/SpatialDBBox

Re: Custom types / "long" datatypes in custom functions

[Daniel John Debrunner <dj...@debrunners.com>]

David Blasby wrote:

> Unfortunately, the maximum size for varchar is only 32k.  Spatial data
> is commonly much much larger than this.  "LONG VARCHAR FOR BIT DATA"
> would be acceptable for storage, but you are unable to pass this to a
> user-defined function (or return one).

And unfortunately LONG VARCHAR FOR BIT DATA isn't much better, still
limited at around 32k. Though both those limits could be removed fairly
easily.

The issue with passing LONG VARCHAR FOR BIT DATA and BLOB into functions
is that types that need to be passed and returned are
java.io.InputStream and java.sql.Blob respectively. Derby can return
them for java.sql.ResultSet getBinaryStream and gteBlob but the logic is
 contained in the ResultSet implementation. I think that the logic from
EmbedResultSet would need to move into the type system, e.g. SQLBlob.

> Are "VARCHAR FOR BIT DATA" represented by a String type or a byte[]?

byte[]

Dan.

Re: Custom types / "long" datatypes in custom functions

[David Blasby <db...@gmail.com>]

> Just to be clear functions can send and return non-fixed length data
> types, also known as variable length types. An example is VARCHAR(1000)
> which is a character type whose value can be any length from 0
> characters to 1000 characters. And VARCHAR(1000) FOR BIT DATA is binary
> type whose length can be from 0 bytes to 1000 bytes.

Unfortunately, the maximum size for varchar is only 32k.  Spatial data
is commonly much much larger than this.  "LONG VARCHAR FOR BIT DATA"
would be acceptable for storage, but you are unable to pass this to a
user-defined function (or return one).

Are "VARCHAR FOR BIT DATA" represented by a String type or a byte[]?

dave

Re: Custom types / "long" datatypes in custom functions

[Daniel John Debrunner <dj...@debrunners.com>]

David Blasby wrote:

> 2.   Functions
>    Currently, functions cannot send/return non-fixed-length data
> types.  It would be great to be able to either (1) send custom types
> to custom functions OR (2) send BLOB-like things to custom functions.
> (oh, are BLOBs inefficient to use?)

Just to be clear functions can send and return non-fixed length data
types, also known as variable length types. An example is VARCHAR(1000)
which is a character type whose value can be any length from 0
characters to 1000 characters. And VARCHAR(1000) FOR BIT DATA is binary
type whose length can be from 0 bytes to 1000 bytes.

> Currently, I use a "VARCHAR(1000000)" as my Geometry type, and stick a
> base-64 encoding of the binary version of the Geometry inside.  This
> is pretty messy and inefficient.  Not to mention the fact that you can
> run spatial functions on text like 'how now brown cow'.

So does that tell you what path the cow jumped over the moon? :-)

Dan.