search

yogthos / migratus

MIGRATE ALL THE THINGS!
643 stars 93 forks source link

Is it possible to use ; as statement separator only? #128

Closed viesti closed 6 years ago

viesti commented 6 years ago

Hi!

First off, thanks for a neat library, we've been using it in many small applications to great effect! :) I'm wondering if it would be possible to separate multiple statements with semicolon only (;), instead of the line comment marker (--;;)? Flyway for example seems to support this: https://flywaydb.org/documentation/migrations#syntax. The upside would be that people that are not so familiar with migratus convention, but who are familiar in writing snippets of SQL from time to time, would not need teaching about the --;; convention. I guess the question is, that is it realiable enough to split statements by ; only, before sending to execution over JDBC.

Don't know if this is wrong place or way to ask, but please excuse me :)

yogthos commented 6 years ago

Hi,

I think there's a bit of a semantic difference here. The --;; statements specifically delineate transactions, while ; is used to separate individual statements. You may wish to use ; within the transaction block, and overloading it would change the expected behavior.

viesti commented 6 years ago

Hmm, reading https://github.com/yogthos/migratus/blob/master/src/migratus/migration/sql.clj#L23, seems that the sql text parts split by --;; are sent to clojure.java.jdbc to be parsed as prepared statement. If I follow correctly, then it is up to the prepared statement parser/jdbc driver how statements separated by ; are handled.

yogthos commented 6 years ago

Right, and that varies between different databases. There's a bit more discussion on the problem here.

viesti commented 6 years ago

Right. Reading Flyway implementation, seems that it does splitting by ; itself https://github.com/flyway/flyway/blob/master/flyway-core/src/main/java/org/flywaydb/core/internal/sqlscript/SqlScript.java#L284-L366 to parse statements in a database specific way (seems to handle say, COPY for postgresql in a bit different way than other statements).

yogthos commented 6 years ago

My view is that it's probably better to leave ; behavior unchanged, and have a separate semantic for separating transactional statements.