Re: Postgres Functions in H2

[Noel Grandin]

Something like:

ALTER TABLE password_tokens
ADD CONSTRAINT active_password_token_check
CHECK (
     SELECT count(*) FROM password_tokens p1,  password_tokens p2
    WHERE p1.user_id=p2.user_id
    AND p1.consumed=true
    AND p1.consumed = true
    AND p1.expiry>now()
    AND p2.expiry>now()
    ) <= 1

On 2013-04-12 01:00, tim.gr...@seekersolutions.com wrote:

Hello

I'm trying to port some things over from a postgres database we have. I'm stuck on a check constraint on one of our postgres tables. Basically I need to see if any other rows in the table would prevent the current one from being inserted.

In this case, two rows cannot

* share a user id,

* have "consumed" true, AND

* have an expiry date some time in the future.

in postgres, I'd use a function, and pass values from the row into the function, which would perform a SELECT over the table to see if any existing rows would prevent this row from being inserted.

CREATE OR REPLACE FUNCTION check_password_reset_token(int) RETURNS boolean AS '

SELECT count(*)>0

FROM password_tokens

WHERE (user_id=$1 AND consumed=true AND expiry>now());

' LANGUAGE SQL;

ALTER TABLE password_tokens ADD CONSTRAINT active_password_token_check CHECK (check_password_reset_token(user_id));

When I try the above code in H2, it complains about not understanding functions, but when i try to move the whole thing into the check constraint in the alter table statement, then I get stuck on actually passing the row's values into the parts of the SELECT up there.

I know h2 supports Java functions, so that's a last resort, but we're only using h2 for in-memory testing and I don't want to have to test the test code, as it were. Is there another way to perform the same check constraint in h2?

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

[Thomas Mueller]

Hi,

I don't have a solution that works for both H2 and PostgreSQL, but here is a solution for H2 (expiry is ignored):

drop all objects;

create table password_tokens(user_id int, consumed boolean);

create index idx_user_id on password_tokens(user_id);

create alias check_password_reset_token as $$

boolean check(Connection conn, int x, boolean consumed) throws SQLException {

PreparedStatement prep = conn.prepareStatement(

"select count(*) from password_tokens where user_id = ? and consumed = true");

prep.setInt(1, x);

ResultSet rs = prep.executeQuery();

rs.next();

return rs.getInt(1) <= (consumed ? 0 : 1);

}

$$;

ALTER TABLE password_tokens 

ADD CONSTRAINT active_password_token_check 

CHECK check_password_reset_token(user_id, consumed);

insert into password_tokens values(1, true);

insert into password_tokens values(1, false);

insert into password_tokens values(2, true);

insert into password_tokens values(2, true); -- fails

Regards,

Thomas

[tim.gr...@seekersolutions.com]

When I try that sample (and other variations) it complains about having the select in the check (specifically,

Error executing SQL ALTER TABLE password_tokens ADD CONSTRAINT active_password_token_check CHECK (

        SELECT count(*) FROM password_tokens p1, password_tokens p2

        WHERE p1.consumed=true

          AND p2.consumed=true

          AND p1.expiry > now()

          AND p2.expiry > now()

      ) <=1;: ERROR: syntax error at or near "SELECT"

It is possible to do a query inside a check, right?

[Thomas Mueller]

Hi,

> ERROR: syntax error at or near "SELECT"

This error message is from PostgreSQL, not H2. You need to ask at the PostgreSQL group.

Regards,

Thomas