paurkedal/ocaml-caqti

How to create a trigger in sqlite?

tcoopman opened this issue · 6 comments

tcoopman commented

A trigger is created in sqlite like this:

CREATE TRIGGER no_update_stream_events BEFORE UPDATE ON stream_events
BEGIN
  SELECT RAISE (FAIL, 'cannot update stream_events');
END

and used in code like this:

  let trigger_no_update_stream_events =
    (unit ->. unit)
    @@ {| 
      CREATE TRIGGER no_update_stream_events BEFORE UPDATE ON stream_events
      BEGIN
        SELECT RAISE (FAIL, 'cannot update stream_events');
      END
    |}

without the ; after SELECT RAISE... it's not valid - it results in an incomplete input in the query.
But the current parser does not allow ; as input:

ocaml-caqti/caqti/lib/caqti_query.ml

Line 256 in 3678d9d

Error (`Invalid (committed, "Expression cannot contain semicolon."))

Is there a workaround for this?

paurkedal commented

A workaround for this, is to express the query directly using the constructors of the Caqti_query.t type. Since you don't have any parameters, you could simple wrap the whole query in L, i.e. (unit -->. unit) Caqti_query.(L {|CREATE ... END|}).

But for the long run, I'll need to rethink the parser. Rejecting the semicolon was motivated by the use of the parser to split up SQL schema files into individual statements, in order to send them one by one. However, your use-case shows that the semicolon may occur outside of quotes and inside a single statement. So, let's keep this open for now.

  • 👍2