search

gobuffalo / pop

A Tasty Treat For All Your Database Needs
MIT License
1.43k stars 241 forks source link

Feature: add dialect dependent functions #596

Open zepatrik opened 3 years ago

zepatrik commented 3 years ago

Description

There are some basic build-in SQL functions that are not supported by every database in the same way. One example is the version() function. Sqlite does not support it but has sqlite_version(). Another example is now() wich is also not supported by sqlite, instead one has to use CURRENT_TIMESTAMP there.

Expected Behavior

I would like pop to handle these cases for me. One idea I had was to export some constants like pop.Now or pop.Version that one can insert into the query and that would be filled in by the driver accordingly. E.g.:

c.RawQuery("SELECT ? as version", pop.Version).First(&v))

Actual Behavior

Currently I have to do it similar to:

versionFunc := "version()"
if c.Dialect.Name() == "sqlite3" {
    versionFunc = "sqlite_version()"
}
/* #nosec G201 - versionFunc is an enum */
require.NoError(t, c.RawQuery(fmt.Sprintf("SELECT %s as version", versionFunc)).First(&v))

I can work on this if it is desired to have.

aeneasr commented 3 years ago

? is unfortunately reserved for escaped SQL arguments (on a driver level) which makes the proposal difficult to implement. Alternatively, we could expose an interface of "generic" SQL functions such as NOW() or version in a common interface which you could use with fmt.Sprintf. Question is if there is enough overlap in these generic functions to warrant such an interface or if there are really just a few (e.g. NOW()) where this could be used.