New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
database/sql: ? vs $ inconsistency #3602
Comments
I think different back ends will naturally use different symbols here. I see no reason you couldn't pass a args struct that has both a value and a name, so you can have a named arg either. I would say the driver and back end should do what is natural. SQL is different enough as it is. IE, you are not going to execute pg/SQL on sqlite. |
Comment 4 by raul.san@sent.com: Related: https://groups.google.com/forum/?fromgroups=#!topic/golang-nuts/p-QPMneatDI |
Comment 6 by raul.san@sent.com: You must have in mind that there are two kind of strings which can be different in each SQL engine: the place holders and also the quotes. So, my solution has been to use a "template" with "{P}" instead of place holder string, and "{Q}" instead of the quote string. Then, they're replaced according to the SQL engine. Here it's the function that I use to replace: https://github.com/kless/modsql/blob/7c86ebaca0a8bef8bba2afa2c6ec4c0f9e5e1b82/modsql.go#L75 and like example of statements, where I'm using: https://github.com/kless/modsql/blob/7c86ebaca0a8bef8bba2afa2c6ec4c0f9e5e1b82/testdata/model.go#L50 * * * The function that I use to replace could be taken like idea to implement something similar in the standard library. |
I'm in no rush to change anything here, since I'm not sure what a safe solution is. I really don't want to get into the business of parsing SQL, or worse: a dozen different SQL dialects. I'm also reluctant to encourage driver authors to parse their SQL variant and and morph it into a Go variant (e.g. "use question marks for placeholders!"). Doing some research I found http://sqlrelay.sourceforge.net/sqlrelay/programming/binds.html which says: """ Different databases have different syntax for bind variables. Oracle bind variables are names preceeded by a colon. In MySQL, DB2 and Firebird, bind variables are represented by question marks. In Sybase and MS SQL Server, bind variables are names preceeded by an @ sign. In PostgreSQL, bind variables are numbers preceeded by a $ sign. """ What do other database libraries do? We could have a mini SQL lexer and say that any ? not in a quoted string can be replaced by the driver (optional hook) with the appropriate driver-specific positional bind variable. |
Comment 19 by raul.san@sent.com: I use the next function to replace the global character for the place holder by the used one into a specific SQL engine: const _PLACEHOLDER = "?" // SQLReplacer replaces the characters "$" with the placeholder parameter for // the given SQL engine. func SQLReplacer(eng Engine, src string) string { switch eng { case MySQL, SQLite: if _PLACEHOLDER != "?" && strings.Contains(src, _PLACEHOLDER) { return strings.Replace(src, _PLACEHOLDER, "?", -1) } case Postgres: for nParam := 1; strings.Contains(src, _PLACEHOLDER); nParam++ { src = strings.Replace(src, _PLACEHOLDER, fmt.Sprintf("$%d", nParam), 1) } default: panic("engine not supported: " + eng.String()) } return src } |
I recommend this be closed as "can't fix". In order to do such a replacement, you would need to implement a micro SQL parser that understands comments, strings, and identifiers. Comments vary from system to system (--, /* */ ...), strings are fairly normal (''), but identifiers ("", []) vary as well. I'm writing several drivers where I have reason to parse the SQL at this level, but it is at the driver level and it is to work in multiple commands or command groups, and substituting names in the SQL to ordinal position (for postgresql, this is for the rdb front-end which allows named parameters). But again, this is very unique to the driver code. If a driver wants to normalize it, then let the driver do it's thing. Also, "re-using" sql shouldn't be an issue here. Anything above the most trivial statements will not port without changes between systems. And a common sql dialect that translates into system specific sql is well beyond this package's scope. |
As @kardianos points out, even if placeholders were made consistent, there are other differences between the SQL syntax from one DB driver to the next. Personally, I think it's better to leave this to the domain of SQL builders, and not start down the slippery slope of trying to have a common dialect, even if only one small aspect of it. My 2 cents. |
I agree that this is probably out of scope for the driver. I do, however, think we could make this a bit easier to for developers to manage by exposing the driver name: type Driver interface {
Open(name string) (Conn, error)
+ Name() string
} This would allow me to use the existing switch db.Driver().Name() {
case "postgres":
// replace ? with $%d
case "mysql", "sqlite3":
} This would probably require exposing either the type Tx struct {
db *DB
}
+func (t *Tx) Driver() driver.Driver {
+ return t.db.Driver()
+} I think this sort of minor change would make this problem a bit easier to deal with. I've noticed projects writing custom functions to normalize queries (see |
The first change you propose is backward incompatible and thus cannot be done. If Name() method is added to the Driver interface then all existing drivers will stop implementing Driver interface. |
Adding new method to an existing interface is not possible under the Go 1
API guarantee.
|
We could adjust the approach without breaking the API guarantee. We could store the type DB struct {
driver driver.Driver
+ name string
dsn string Which is available at the time of opening a connection: func Open(driverName, dataSourceName string) (*DB, error) {
...
driveri, ok := drivers[driverName]
...
db := &DB{
driver: driveri,
+ name: driverName,
dsn: dataSourceName,
openerCh: make(chan struct{}, connectionRequestQueueSize),
lastPut: make(map[*driverConn]string),
}
...
} And could be exposed by a function on the +func (db *DB) Name() {
+ return db.name
+} |
Note that driver name is possible to be user-defined. https://github.com/mattn/go-sqlite3/blob/master/_example/custom_func/main.go#L65 |
I don't see how this can be realistically done across different database vendors. For simple queries, this might be possible, but not for full T-SQL or PL/SQL or pgPL/SQL queries, esp when the query definition starts going into DDL. If you want to expose the driver name, open a separate issue for that. The various use cases can be discussed there. |
I just ran across this issue as I'm stumbling my way into Go and database access. Historical footnote: The Perl DBI gained a lot by mandating over 20 years ago that drivers be required to support |
@timbunce Closed issues aren't tracked. From your comment I'm unsure what DBI gained by requiring support for ? placeholders, nor does it address the issues presented above, such as various query dialects, nor does it address systems that don't use positional placeholders at all, but only uses named placeholders. |
Hello @kardianos. I appreciate that closed issues aren't tracked. It was just a historical footnote, albeit one that wasn't very clear. To your points:
Subsequent to my original comment above I've discovered that sqlx supports |
@timbunce if you're at all interested I've been generating SQL with DBIx::Class and using it in a go project with a reasonable amount of success. I'm now generating SQLite queries as well as the Postgresql queries I started with it has been holding up. |
The text was updated successfully, but these errors were encountered: