Hello :wave:

Description

This is a proposal to add a function to the API of Connection to make SELECT queries more easily. This API has been inspired from sqlx.

Why?

There is already the function iterate() which allows doing a SELECT query easily. Unfortunately iterate() has two difficult constraints:

1. It works only for string values.
2. It does not return an iterator which forces the user to mutate variables outside the closure.

This new select() function is different as it does return an object that implements Iterator. Secondly, this iterator yields "row" objects that can be used to retrieve any type of value and not just strings.

Features

• The new Select iterator can be created directly from Connection but it can also be created from a statement instead (using the From/Into traits).
• The new Row object can be used to retrieve a column by its index or by its name.
• The method get() and try_get() that can be found on the Row object allows the user to retrieve the value in the type of their choice. For example: they can directly get and i64 instead of having to go through Value
• The type can be optional: if the value in database is NULL it will return None, otherwise it will try to get the type you requested

Motivations

I was using sqlx on a private project but I didn't need any performance or concurrency at all. Because of that, the async dependencies and the use of async code felt heavy for no good reason at all. I looked for a blocking crate and I found sqlite and rusqlite.

rusqlite has a nicer API and more features but I don't need those features. Also the API forces me to handle many more results than with sqlx (prepare returns a result, query_map returns a result, the closure you need to pass to query_map must also return a result).

sqlite is very tight which is more what I'm looking for. Unfortunately I can't adapt the code of my project to sqlite because its API is very different and not necessarily aimed for convenience. It actually looks more aimed for speed. This is fine but I would like to introduce some optional convenience to sqlite.

Considerations

Even though this Select iterator is very convenient, it's best used with itertools for its functions map_ok() and fold_ok(). Using it without itertools would require the user to either collect the data early using .collect::<Result<Vec<_>, _>() or deal with the result in the items. Since a row can fail to fetch at any time, there must be a result during the iterations.

Checklist

• [x] actual code
• [x] tests
• [ ] doc

https://www.sqlite.org/c3ref/close.html

Also, is there any way to verify an error code. For example when calling open is there a way to verify the returned error is sqlite::Error::CANTOPEN for testing purposes?

I'd expect rows that contain data in the specified column to return that and the others simply fail but all the rows fail during serialization for the specified column

                 CREATE TABLE IF NOT EXISTS current 
                 (user VARCHAR(50) ,
                 admin VARCHAR(50),
                 price FLOAT,
                 new_credit FLOAT,
                 card_id INT,
                 OP VARCHAR(10),
                 table_id VARCHAR(50),
                 game_id INT,
                 game_name VARCHAR(20),
                 winning_type varchar(20) not null default '',  #if theres no default here it fails the resulting rows are always empty during the serialization even if theres data
                 winning_amount float not null default 0.0,
                 op_time INT default (CURRENT_TIMESTAMP) )

See #16.

Two design choices have been considered:

// 1. a set of connection flags
let flags = ConnectionFlags::new().set_bla();
let connection = Connection::open_with_flags(path, flags);

// 2. a connection builder
let connection = ConnectionBuilder::new().set_bla().open(path);

I have decided for the former in order to avoid creating tension with the existing set_ methods.

Any feedback is very much welcome.

Helpful to developers with no experience with sqlite to know that row columns are 0 indexed, other database row columns are usually 1 indexed and they can give the 0'th column access to db internal row id's.

Tried to use the style of Java's Resultset's documentation e.g. https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getBytes(int)

There is a case where sqlite3_column_text will return a NULL pointer that is entirely valid — when the table has a NULL value (see table on this page), so without this patch, we get a panic despite everything actually being OK. This patch simply adds a check if there was an actual error when getting a NULL pointer back from sqlite.

(BLOB handling already deals with this case, so there is no need to change things there, either.)

To reproduce the original bug, simply try to SELECT a NULL value and read::<String>() the cursor.

Hello, would you be willing to include transaction and savepoints? The structures here will automatically rollback when dropped.

Thanks for the package and for considering this addition!

I've had a hard time nailing down what the problem is with this, but at very least, I've gotten something that reproduces the problem somewhat.

Doesn't seem to be anything in the code, and it does the same thing regardless whether you use a disk-backed database, or an in-memory based database.

But it only happens when you run a binary under memusage

# main.rs
use sqlite;

fn main() {
    let connection = sqlite::open(":memory:").unwrap();
    println!("Hello, world!");
}

# valgrind target/debug/sqlite-mfr

==18989== Memcheck, a memory error detector
==18989== Copyright (C) 2002-2017, and GNU GPL'd, by Julian Seward et al.
==18989== Using Valgrind-3.16.1 and LibVEX; rerun with -h for copyright info
==18989== Command: target/debug/sqlite-mfr
==18989== 
Hello, world!
==18989== 
==18989== HEAP SUMMARY:
==18989==     in use at exit: 0 bytes in 0 blocks
==18989==   total heap usage: 298 allocs, 298 frees, 76,130 bytes allocated
==18989== 
==18989== All heap blocks were freed -- no leaks are possible
==18989== 
==18989== For lists of detected and suppressed errors, rerun with: -s
==18989== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 0 from 0)

# memusage target/debug/sqlite-mfr
/usr/bin/memusage: line 253: 19481 Segmentation fault      LD_PRELOAD=/\$LIB/libmemusage.so "$@"

# memusage gdb --args target/debug/sqlite-mfr
# run
Program received signal SIGSEGV, Segmentation fault.
__memset_sse2_unaligned_erms () at ../sysdeps/x86_64/multiarch/memset-vec-unaligned-erms.S:192
192		VMOVU	%VEC(0), -VEC_SIZE(%rdi,%rdx)
# bt full
#0  __memset_sse2_unaligned_erms () at ../sysdeps/x86_64/multiarch/memset-vec-unaligned-erms.S:192
No locals.
#1  0x00007ffff7cebb66 in memset (__len=<optimized out>, __ch=0, __dest=0x5555555a7b30) at /usr/include/bits/string_fortified.h:71
No locals.
#2  rehash (new_size=4293843945, pH=0x5555555a4c98) at sqlite3.c:33016
        new_ht = 0x5555555a7b30
        elem = <optimized out>
        next_elem = <optimized out>
#3  sqlite3HashInsert (pH=pH@entry=0x5555555a4c98, pKey=0x5555555a7ac8 "fts5_expr", data=data@entry=0x5555555a7a80) at sqlite3.c:33145
        h = 0
        elem = <optimized out>
        new_elem = 0x5555555a7af0
#4  0x00007ffff7cf215f in sqlite3FindFunction (db=db@entry=0x5555555a4a60, zName=zName@entry=0x7ffff7dbb1e8 "fts5_expr", nArg=nArg@entry=-1, 
    enc=enc@entry=1 '\001', createFlag=createFlag@entry=1 '\001') at sqlite3.c:116005
        pOther = <optimized out>
        z = 0x5555555a7ad1 ""
        p = <optimized out>
        pBest = 0x5555555a7a80
        bestScore = <optimized out>
        h = <optimized out>
        nName = 9
#5  0x00007ffff7cfed97 in sqlite3CreateFunc (db=db@entry=0x5555555a4a60, zFunctionName=zFunctionName@entry=0x7ffff7dbb1e8 "fts5_expr", nArg=nArg@entry=-1, 
    enc=enc@entry=1, pUserData=pUserData@entry=0x5555555a7760, xSFunc=xSFunc@entry=0x7ffff7d46c82 <fts5ExprFunctionHr>, xStep=0x0, xFinal=0x0, xValue=0x0, 
    xInverse=0x0, pDestructor=0x0) at sqlite3.c:162920
        p = <optimized out>
        nName = <optimized out>
        extraFlags = 2097152
#6  0x00007ffff7d130b3 in createFunctionApi (db=db@entry=0x5555555a4a60, zFunc=0x7ffff7dbb1e8 "fts5_expr", nArg=nArg@entry=-1, enc=enc@entry=1, 
    p=p@entry=0x5555555a7760, xSFunc=0x7ffff7d46c82 <fts5ExprFunctionHr>, xStep=0x0, xFinal=0x0, xValue=0x0, xInverse=0x0, xDestroy=0x0) at sqlite3.c:162986
        rc = 1
        pArg = 0x0
#7  0x00007ffff7d130d6 in sqlite3_create_function (db=db@entry=0x5555555a4a60, zFunc=<optimized out>, nArg=nArg@entry=-1, enc=enc@entry=1, 
    p=p@entry=0x5555555a7760, xSFunc=<optimized out>, xStep=0x0, xFinal=0x0) at sqlite3.c:163014
No locals.
#8  0x00007ffff7d1709f in sqlite3Fts5ExprInit (db=0x5555555a4a60, pGlobal=0x5555555a7760) at sqlite3.c:215601
        p = 0x7fffffffca50
        aFunc = {{z = 0x7ffff7dbb1e8 "fts5_expr", x = 0x7ffff7d46c82 <fts5ExprFunctionHr>}, {z = 0x7ffff7dbb1f2 "fts5_expr_tcl", 
            x = 0x7ffff7d46c6f <fts5ExprFunctionTcl>}, {z = 0x7ffff7dbb200 "fts5_isalnum", x = 0x7ffff7cf3e19 <fts5ExprIsAlnum>}, {
            z = 0x7ffff7dbb20d "fts5_fold", x = 0x7ffff7cf3dad <fts5ExprFold>}}
        i = 0
        rc = 0
        pCtx = 0x5555555a7760
#9  fts5Init (db=0x5555555a4a60) at sqlite3.c:29244
        p = 0x5555555a7760
        rc = 0
        pGlobal = 0x5555555a7760
        fts5Mod = {iVersion = 3, xCreate = 0x7ffff7d97d8b <fts5CreateMethod>, xConnect = 0x7ffff7d97d68 <fts5ConnectMethod>, 
          xBestIndex = 0x7ffff7d34710 <fts5BestIndexMethod>, xDisconnect = 0x7ffff7d4828f <fts5DisconnectMethod>, 
          xDestroy = 0x7ffff7d8256e <fts5DestroyMethod>, xOpen = 0x7ffff7d8891d <fts5OpenMethod>, xClose = 0x7ffff7d498b7 <fts5CloseMethod>, 
          xFilter = 0x7ffff7d99f69 <fts5FilterMethod>, xNext = 0x7ffff7d999b5 <fts5NextMethod>, xEof = 0x7ffff7cda9a5 <fts5EofMethod>, 
          xColumn = 0x7ffff7d8a4ca <fts5ColumnMethod>, xRowid = 0x7ffff7ce8a3b <fts5RowidMethod>, xUpdate = 0x7ffff7d9c496 <fts5UpdateMethod>, 
          xBegin = 0x7ffff7d8890a <fts5BeginMethod>, xSync = 0x7ffff7d9bd23 <fts5SyncMethod>, xCommit = 0x7ffff7cda9c7 <fts5CommitMethod>, 
          xRollback = 0x7ffff7d46ce1 <fts5RollbackMethod>, xFindFunction = 0x7ffff7cdaa98 <fts5FindFunctionMethod>, 
          xRename = 0x7ffff7d9bc56 <fts5RenameMethod>, xSavepoint = 0x7ffff7d9bd55 <fts5SavepointMethod>, xRelease = 0x7ffff7d9bd69 <fts5ReleaseMethod>, 
          xRollbackTo = 0x7ffff7d46cc2 <fts5RollbackToMethod>, xShadowName = 0x7ffff7cdab11 <fts5ShadowName>}
        rc = <optimized out>
        pGlobal = <optimized out>
        p = <optimized out>
#10 sqlite3Fts5Init (db=0x5555555a4a60) at sqlite3.c:29310
No locals.
#11 0x00007ffff7da7ac6 in openDatabase (zFilename=0x5555555a4740 ":memory:", ppDb=0x7fffffffcbe8, flags=<optimized out>, zVfs=<optimized out>) at sqlite3.c:164379
        db = 0x5555555a4a60
        rc = 0
        isThreadsafe = <optimized out>
        zOpen = 0x5555555a47c4 ":memory:"
        zErrMsg = 0x0
        i = 1
#12 0x00007ffff7da7c49 in sqlite3_open_v2 (filename=<optimized out>, ppDb=<optimized out>, flags=<optimized out>, zVfs=<optimized out>) at sqlite3.c:164461
No locals.
#13 0x000055555555ae19 in sqlite::connection::Connection::open_with_flags (path=..., flags=...)
    at /home/kent/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlite-0.25.3/src/connection.rs:31
        raw = 0x0
#14 0x000055555555b199 in sqlite::connection::Connection::open (path=...) at /home/kent/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlite-0.25.3/src/connection.rs:24
No locals.
#15 0x000055555555a99a in sqlite::open (path=...) at /home/kent/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlite-0.25.3/src/lib.rs:304
No locals.
#16 0x000055555555b5e0 in sqlite_mfr::main () at src/main.rs:4
No locals.

It could be something in glibc, it could be something in sqlite, but seems a more likely bet is something is "off" in FFI.

I had to rebuild both with debug symbols to make it clearer what's happening here :sweat_smile:

If a cursor is for a statement which has a literal wildcard in it (no binding '?') like the following, it returns all the matching rows as expected:

let mut no_bind = connection.prepare( "SELECT value from english where value like '%type'").unwrap();
let cur = no_bind.cursor();

However if the wild card is bound to the statement it returns less rows (often 0) but I've seen any number of rows in between.

let mut with_bind = connection.prepare( "SELECT value from english where value like ?").unwrap();
ok!(with_bind.bind(1, "%type"));
let cur = with_bind.cursor();

I've submitted pull request #2 for a failing test that reproduces it.

I'm having trouble getting column type information from Cursors:

let db = sqlite::open("db.sqlite").unwrap();
let statement = db.prepare("select * from users;").unwrap();
let mut cursor = statement.into_iter();

while let Some(Ok(row)) = cursor.next() {
    for column_index in 0..cursor.column_count() {
        let column_type = cursor.column_type(column_index);
        println!("col type: {:?}", column_type);
    }
}

The users table contains two columns. name is a string, and age is an integer. Instead this code prints None for every column.

Any help is much appreciated!

I noticed I couldn't bind String's directly without having to ".as_str()" them first. I've added support to bind Strings and &Strings along with an updated test.

hello, dev author, I use gcc-toolchain ( gcc-linaro-7.5.0-2019.12-rc1-x86_64_arm-linux-gnueabihf, linaro website ) build my demo, bui it fail.

The error message as:

my Cargo.toml example:

my guess

arm-linux-gnueabihf is armv7l gcc-toolchain, instructions are 32-bit, but this cargo library compiled sqllite is 64-bit, so the error tell me:

undefined reference to `fcntl64'
collect2: error: ld returned 1 exit status

similar problems：rusqlite#1180

last

Thank you very much for taking time out of your busy schedule to check this problem

Hi,

It seems out of the box the extenstion is not supported. I did try to use json1 and got error like function json_extract does not exists.

How can I build the library with json1 ext support for example?

Thanks

Greetings, i'm trying to use a select in statement but don't seem to able to.

let mut cursor = conn
		.prepare("SELECT * FROM users WHERE age in (?)").     //  <-- carray
		.unwrap()
		.into_cursor();

cursor.bind(&[[Value::Integer(42),Value::Integer(50)]]).unwrap();   // <-- fails since expectation is Value not array/tuple

is this currently possible ?