User Defined Datatype

[Akash]

Hi,

How do i create a new datatype in H2DB. The feature says that user-
defined datatype is allowed.
Ex. I want to create a new datatype "Point" which takes two floating
point arguments say (float x, float y).

So when i issue a command
create table coordinate(cord point(float x, float y));
I get a point attribute with x and y coordinates.


Thanks,
Akash

[Thomas Mueller]

Hi,

H2 does support storing serialized objects, so basically H2 can do the
same as with Apache Derby:
http://db.apache.org/derby/docs/10.7/ref/rrefsqljcreatetype.html - so
you can use

INSERT INTO order( customerID, totalPrice )
VALUES ( 12345,
makePrice( 'USD',
CAST( 9.99 AS DECIMAL( 31, 5 ) ),
TIMESTAMP('2009-10-16 14:24:43') ) );

Price totalPrice = (Price) rs.getObject( 3 );

and so on.

However, H2 doesn't require or support CREATE TYPE as Apache Derby
does. Instead, use the data types OTHER, OBJECT, or JAVA_OBJECT. If
you need to restrict the object to be of a certain type, you could
emulate that using a domain and check constraint. However, I don't
know what is your use case, so I don't know if that's required.
Anyway, the code looks a bit different than with Apache Derby:

DROP ALL OBJECTS;
CREATE ALIAS IS_TYPE AS $$
boolean isType(byte[] data, String className) throws Exception {
if (data == null) return true;
Object x = org.h2.util.Utils.deserialize(data);
return Class.forName(className).isAssignableFrom(x.getClass());
} $$;
CREATE ALIAS MAKE_POINT AS $$
java.awt.Point newPoint(int x, int y) {
return new java.awt.Point(x, y);
} $$;
CREATE ALIAS POINT_X AS $$
int pointX(byte[] data) {
java.awt.Point p = (java.awt.Point)
org.h2.util.Utils.deserialize(data);
return p.x;
} $$;
CREATE ALIAS POINT_Y AS $$
int pointY(byte[] data) {
java.awt.Point p = (java.awt.Point)
org.h2.util.Utils.deserialize(data);
return p.y;
} $$;
CREATE DOMAIN POINT AS OTHER
CHECK IS_TYPE(VALUE, 'java.awt.Point');
CREATE TABLE TEST(ID INT, P POINT);
INSERT INTO TEST VALUES(1, MAKE_POINT(20, 30));
SELECT POINT_X(P), POINT_Y(P) FROM TEST;

It's a bit unfortunate that H2 doesn't support using the class itself
in parameters, so currently you have to de-serialize the object. I
will check why this is is required.

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

[Akash Agrawal]

Thanks Thomas,

The example helped a lot.

Thanks and regards,

Akash