sqlc-dev
MySQL engine doesn't support: CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE
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
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.
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/
