search

volatiletech / sqlboiler

Generate a Go ORM tailored to your database schema.
BSD 3-Clause "New" or "Revised" License
6.66k stars 539 forks source link

Mysql SELECT ... LOCK IN SHARE MODE #214

Open burkostya opened 6 years ago

burkostya commented 6 years ago

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v2.5.1

Hi

Is it possible to generate select query with LOCK IN SHARE MODE using Query Mods?

aarondl commented 6 years ago

You should be able to use: https://github.com/volatiletech/sqlboiler/blob/master/queries/qm/query_mods.go#L141

It's called FOR because I think in PSQL and MySQL most of the locking statements begin with for. It essentially just tags things on the end of the query so it should work fine. There's nothing special about it, you could technically use it to slap on anything to the end of the query. Re-open if this doesn't work for you :)

burkostya commented 6 years ago

Looks like it will generate FOR LOCK IN SHARE MODE, but mysql will return syntax error on that

https://github.com/volatiletech/sqlboiler/blob/86c580f53724a794e2f807707653f480616d455d/queries/query_builders.go#L393

aarondl commented 6 years ago

Ah, I forgot that we actually added a FOR. Ugh. So this becomes kind of icky, we could add another query mod for LOCK but it's getting a bit silly since the syntax diverges so wildly in all the SQL dialects. Maybe something generic to tag on the end of a query is preferable. What do you think?

burkostya commented 6 years ago

Something like qm.AppendSQL("LOCK IN SHARE MODE")?

aarondl commented 6 years ago

Yeah. Could do something like that. It sort of hurts in a way, but I don't know of anyway around it. Also, is there anything that's stopping you from using a raw query here? Is it too painful/inconvenient? I'm sort of shying away from the idea of adding too many things to the query mods, this might be acceptable but I'd like to hear why the raw query is undesirable here. Maybe we could improve that for advanced queries.

burkostya commented 6 years ago

There are exiting (and complex enough) queries that were written using QueryMods. For adding lock we are must rewrite those with raw query. That is annoying and more error prone. So appending sql using qm will be realy usefull.

visortim commented 5 years ago

Did a resolution ever materialize for this? I've updated to the latest code and tried With() but that also produces a syntax error. Thankfully our explicit locking is very limited so I am using raw sql where needed, but I like QMs for the safety.

aarondl commented 5 years ago

@visortim I never added this because it didn't feel "right". Basically having the equivalent of a "half raw query". But I don't care enough to fight it :) I'd accept a PR for this.

visortim commented 5 years ago

@visortim I never added this because it didn't feel "right". Basically having the equivalent of a "half raw query". But I don't care enough to fight it :) I'd accept a PR for this.

No, I tend to agree with you and trust you judgement that something like AppendSQL() is an anti-pattern and would prefer to continue with raw SQL if a more wholistic/type-safe solution doesn't exist.