How to create a trigger in sqlite?

[tcoopman]

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:

https://github.com/paurkedal/ocaml-caqti/blob/3678d9dd6e8220e28537e5080a82698df7eb710b/caqti/lib/caqti_query.ml#L256

Is there a workaround for this?

[paurkedal]

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.

[anmonteiro]

@paurkedal another workaround I'm using could be to specialize queries with ;<whitespace>END directly but it might be too specific? e.g.: replace this code: https://github.com/paurkedal/ocaml-caqti/blob/be088e63bae204cb090bef469a6f5faadfb78342/caqti/lib/caqti_query.ml#L275-L282

with:

let atom_with_end =
  atom >>= fun q ->
  char ';'
  >>= (fun _ ->
        white *> string_ci "end" *> return (Caqti_query.S [ q; L "END" ]))
  <|> return q

 let expression = 
   let stop = 
     peek_char >>= function 
      | None | Some ';' -> return () 
      | _ -> fail "unterminated" 
   in 
   fix (fun p -> (stop *> return []) <|> (List.cons <$> atom <*> p)) 
     >>= reindex >>| (function [q] -> q | qs -> S qs) 

[paurkedal]

@anmonteiro Do you still need to use a workaround for this? Support for queries containing bare semicolons was released in version 1.9.0.

[anmonteiro]

@anmonteiro Do you still need to use a workaround for this? Support for queries containing bare semicolons was released in version 1.9.0.

I think so, but maybe I'm mistaken: in my case I have a migration file that has multiple statements and I want to get each statement separately. AFAIU the parser with semicolons can't give me a list of expressions

[paurkedal]

@anmonteiro So, as I understand it, you want to use the angstrom_list_parser (or create something like it) to a schema loaded from a file. Yes, that will not work with sqlite3 triggers even in the current version. The current parser isn't meant to parse SQL and the heuristic you suggest would not work for multiple trigger statements. It might be worth reconsidering how to load and submit database schemas, but we should do that in a new issue.

[paurkedal]

@anmonteiro I created the issue, please comment when you have time, esp. on the topic of plain SQL vs Caqti SQL template.