Support IN operator with Vector/Tuple in values

[jsams]

Looking at the source, it seems as if there is not a bind!(stmt::Stmt, i::Int, val::Vector/Tuple) in SQLite.jl for queries of the form SELECT * FROM table WHERE x IN (?) and the parameter would be a julia vector or tuple. I also did not see a sqlite3_bind_* in api.jl for this kind of thing. Below is some example code that could be used for a test.

I have no idea how to go about calling C-code from Julia. So I can't really help here.

using SQLite
using DataFrames
using Base.Test

df = DataFrame(:x => 1:5, :y => 2:6)
db = SQLite.DB(":memory:")
SQLite.load(db, "test", df)
xs = [2:4 ...]
# select from df in multiple ways:
sdf1 = df[in.(df[:x],  [xs]),:]
sdf2 = SQLite.query(db, "SELECT * FROM test WHERE x IN (?) ORDER BY x", values=[xs])
sdf3 = SQLite.query(db, "SELECT * FROM test WHERE x IN (2, 3, 4) ORDER BY x")
@test sdf1 == sdf2
@test sdf1 == sdf3

[quinnj]

Sounds like this isn't something that's directly supported by sqlite. We could potentially support it anyway via the syntax/dispatch you've mentioned, so it would just take a bit of work to dynamically build the prepared statement w/ the correct number of ? and then bind each element of the list.

[jsams]

Indeed. Even worse, if I read the code right it's something that can't be dispatched on as you are binding arguments, but has to be done at the time the sql query string is parsed. I couldn't figure out how to do that myself.

[quinnj]

This can be done w/o parameter binding via SELECT * FROM test where x IN ($(SQLite.esc_id((2, 3, 4)))).