MySQL engine doesn't support: CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE

dansimau commented 2 years ago

Version

1.11.0

What happened?

$ sqlc compile
# package db
migrations/20210121143728_timestamps.up.sql:11:15: syntax error near "TRIGGER `update_timestamp_collections` BEFORE UPDATE ON `collections`"
migrations/20210421162816_uuidv4.up.sql:1:15: syntax error near "FUNCTION uuidv4()"
migrations/20220117154531_migrate_collections_to_user_collections_rel.up.sql:4:17: syntax error near "PROCEDURE _migrate_collection_sort_orders()"

Looks like these are not supported by the parser: https://github.com/pingcap/parser/issues/988

Playground URL

https://play.sqlc.dev/p/8cb9a845afd03c6741aef0a13b221045c620690b6b6c5d9738eaeeaab4a7ab8e

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

dansimau commented 2 years ago

It looks like pingcap/parser is missing quite a lot.

I just had a look at what it would take to write a PR for the parser. In my case I have a stored procedure that uses a BEGIN/END, CURSOR, LOOP... this would be non-trivial to implement in pingcap/parser. And it's not at all relevant to sqlc I think.

As a workaround, I now preprocess my migration files and use a script to filter out the problematic statements. I then feed the processed .sql migration files to sqlc.

E.g. here is my Makefile:

.PHONY: sqlc
sqlc: gen-sqlc
    sqlc compile

.PHONY: gen-sqlc
gen-sqlc: $(addprefix migrations/gen-sqlc/,$(shell cd migrations && find . -name '*.sql' -maxdepth 1))

migrations/gen-sqlc/%.sql: migrations/%.sql
    mkdir -p migrations/gen-sqlc
    cat $< | \
        python3 scripts/re-filter.py "BEGIN.*?END(?=\s*;)" | \
        python3 scripts/re-filter.py "(CREATE|DROP) (TRIGGER|FUNCTION|PROCEDURE).*?;" \
            > $@

And source for re-filter.py: https://gist.github.com/dansimau/e8a88415cde5b22248399401bdc1b17c

YMMV.

merlinran commented 7 months ago

Can potentially switch to https://github.com/vitessio/vitess/tree/main/go/vt/sqlparser ? The project has https://vitess.io/docs/20.0/reference/compatibility/mysql-compatibility/

sqlc-dev / sqlc