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
On Fri, Apr 12, 2013 at 11:23 AM, Noel Grandin <[email protected]>wrote:
> 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, [email protected] 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
