domain with check clause containing user defined function

[Justin Deoliveira]

Hi all,

Is it possible to create a DOMAIN with a CHECK clause containing a user defined function? 

I am trying to do the following:

CREATE ALIAS GeometryType FOR "geodb.GeoDB.GeometryType";

CREATE DOMAIN POINT AS BLOB CHECK (GeometryType(VALUE) = 'POINT');

CREATE TABLE foo (geom POINT);

While it works great i run into problems after i close the connection to the database and try to reconnect. I get the following error:

org.h2.jdbc.JdbcSQLException: Function "GEOMETRYTYPE" not found; SQL statement:

CREATE DOMAIN POINT AS BLOB CHECK (GEOMETRYTYPE(VALUE) = 'POINT') [90022-144]

    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)

    at org.h2.message.DbException.get(DbException.java:167)

    at org.h2.message.DbException.get(DbException.java:144)

    at org.h2.command.Parser.readJavaFunction(Parser.java:2004)

    at org.h2.command.Parser.readFunction(Parser.java:2048)

    at org.h2.command.Parser.readTerm(Parser.java:2348)

    at org.h2.command.Parser.readFactor(Parser.java:1930)

    at org.h2.command.Parser.readSum(Parser.java:1917)

    at org.h2.command.Parser.readConcat(Parser.java:1890)

    at org.h2.command.Parser.readCondition(Parser.java:1755)

    at org.h2.command.Parser.readAnd(Parser.java:1736)

    at org.h2.command.Parser.readExpression(Parser.java:1728)

    at org.h2.command.Parser.readTerm(Parser.java:2414)

    at org.h2.command.Parser.readFactor(Parser.java:1930)

    at org.h2.command.Parser.readSum(Parser.java:1917)

    at org.h2.command.Parser.readConcat(Parser.java:1890)

    at org.h2.command.Parser.readCondition(Parser.java:1755)

    at org.h2.command.Parser.readAnd(Parser.java:1736)

    at org.h2.command.Parser.readExpression(Parser.java:1728)

    at org.h2.command.Parser.parseCreateUserDataType(Parser.java:3854)

    at org.h2.command.Parser.parseCreate(Parser.java:3609)

    at org.h2.command.Parser.parsePrepared(Parser.java:310)

    at org.h2.command.Parser.parse(Parser.java:274)

    at org.h2.command.Parser.parse(Parser.java:246)

    at org.h2.command.Parser.prepare(Parser.java:200)

    at org.h2.engine.Session.prepare(Session.java:399)

    at org.h2.engine.Session.prepare(Session.java:386)

    at org.h2.engine.MetaRecord.execute(MetaRecord.java:56)

    at org.h2.engine.Database.open(Database.java:587)

    at org.h2.engine.Database.openDatabase(Database.java:208)

    at org.h2.engine.Database.<init>(Database.java:203)

    at org.h2.engine.Engine.openSession(Engine.java:53)

    at org.h2.engine.Engine.openSession(Engine.java:147)

    at org.h2.engine.Engine.createSessionAndValidate(Engine.java:126)

    at org.h2.engine.Engine.createSession(Engine.java:109)

    at org.h2.engine.Engine.createSession(Engine.java:27)

    at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:289)

    at org.h2.engine.SessionRemote.createSession(SessionRemote.java:265)

    at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:110)

    at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:94)

    at org.h2.Driver.connect(Driver.java:72)

    at org.h2.server.web.WebServer.getConnection(WebServer.java:629)

    at org.h2.server.web.WebApp.login(WebApp.java:889)

    at org.h2.server.web.WebApp.process(WebApp.java:205)

    at org.h2.server.web.WebApp.processRequest(WebApp.java:164)

    at org.h2.server.web.WebThread.process(WebThread.java:137)

    at org.h2.server.web.WebThread.run(WebThread.java:93)

    at java.lang.Thread.run(Thread.java:613)

Thanks in advance.

-Justin

[Sergi Vladykin]

Hi,
It seems that on database opening domains are initialize before user
defined functions, so its just a bug in h2.

[Thomas Mueller]

Hi,

Yes, this is a bug in H2, I will fix it in the next release. When
opening a database, the objects are created in the wrong order
(domains are created before aliases). The bugfix is to change this
order (MetaRecord.getCreateOrder()).

My test case:
create alias is_email as 'boolean isEmail(String x) { return x != null
&& x.indexOf(''@'') > 0; }';
create domain email as varchar check is_email(value);
create table test(e email);
<restart database>

Thanks for your help!

Regards,
Thomas