schema creation and semicolon

[phstrauss]

Hello, How do I create a schema using caqti with the semicolon restriction, here's my schema creation code, which happen to not work. I'm using the sqlite3 backend on ubuntu 20.04 and OCaml 4.13.1:

let init_db =
    [%rapper execute {sql|
        DROP TABLE IF EXISTS client
        CREATE TABLE IF NOT EXISTS client(
                    host TEXT NON NULL,
                    useragent TEXT
                )
        DROP TABLE IF EXISTS request
        CREATE TABLE IF NOT EXISTS request(
                    since_begin REAL,
                    request TEXT NON NULL,
                    retcode INTEGER,
                    size INTEGER,
                    referrer TEXT
                )
        CREATE INDEX i0 ON request(request)
        CREATE INDEX i1 ON request(referrer)
        DROP TABLE IF EXISTS visit
        CREATE TABLE IF NOT EXISTS visit(
                    begin REAL,
                    id_client INTEGER,
                    spam BOOLEAN,
                    resources INTEGER,
                    pages INTEGER
                )
        CREATE INDEX i2 ON visit(id_client)
        DROP TABLE IF EXISTS req_visit
        CREATE TABLE IF NOT EXISTS req_visit(
                    id_visit INTEGER,
                    id_request INTEGER
                )
        CREATE INDEX i3 ON req_visit(id_visit)
        CREATE INDEX i4 ON req_visit(id_request)
    |sql} syntax_off]

[phstrauss]

Oh I guess it's a ppx_rapper issue rather than a caqti one, sorry for the noise.

[paurkedal]

My assumption has been that we cannot rely on support for or a certain behaviour when multiple statements are sent to the server in a single request, though it's probably okay if at most the last statement returns any rows. I usually write the schemas and schema updates as separate SQL files. Rejecting semicolon in the parser allows splitting up statements when e.g. reading them from such a file.

It is possible to insert a semicolon by passing an env function with a variable which expands to a semicolon. Also, the next version will allow semicolon for the new API functions. The reason was to support single statements containing semicolon as seen in the sqlite3 syntax (#87), rather than multiple statements.