special support for constants in prepared statements

[crawshaw]

(Thinking aloud.)

One of the more annoying aspects of my queries is using constants defined in Go as part of a statement:

type Role int

const (
        RoleAdmin Role = 1
        RoleGuest Role = 2
)

stmt := conn.Prep("SELECT ... WHERE Role = $role")
stmt.SetInt64("$role", int64(RoleAdmin))
...

One possibility would be pre-processing SQL literals through another package that has registered enum names, and can fill out values. That's nice, because I want to pile as little stuff into the sqlite package as possible.

Another possibility would be to leverage some uncommon component of SQLite's parameter naming, and make it possible to pre-register constant parameter names with a connection (and a pool).

It might look something like this:

conn.RegisterConst("mypkg.RoleAdmin", mypkg.RoleAdmin)
conn.RegisterConst("mypkg.RoleGuest", mypkg.RoleGuest)

then it could be used as:

stmt := conn.Prep("SELECT ... WHERE Role = $const(mypkg.RoleAdmin)")

The sqlite package would then ban the use of the parameter name $const if it's not referring to a registered constant (by inspecting the query when it is first prepared).

Hmm.

[AdamSLevy]

What feature of SQLite were you considering using for this?