[−][src]Crate rsqlite
This library is a zero-overhead, ergonamic wrapper over sqlite C api.
// creates a database file 'dbfile.db' if it does not exists. let database = Database::open("dbfile.db")?; // executes the query and creates a 'user' table database.execute(r#" create table if not exists user ( id integer primary key autoincrement not null, age int, name text, weight real );"#, ())?; // inserts a new user record. // binds the fields to '?' . // note that only these types are allowed for bindings: // int32, i64, f64, &str, &[u8] // use `&[u8]` to store blob data. database.execute( "insert into user(age, name, weight) values(?, ?, ?)", (29, "amin", 69.5) )?; let name = String::from("negar"); database.execute( "insert into user(age, name, weight) values(?, ?, ?)", (26, name.as_str(), 61.0) )?; // slects from user table on a condition ( age > 27 ), // and executes the closure for each row returned. database.for_each( "select name, age, weight from user where age > ?", (27), |name: String, age: i32, weight: f64| { dbg!(name, age, weight); } )?; // selects the count(*) from user table // you can extract a single culumn single row result to: // i32, i64, f64, String, Box<[u8]> let count: i32 = database.collect("select count(*) from user", ())?; // you can also extract single row with multiple columns let amin: (i32, String, f64) = database.collect( "select age, name, weight from user where name = ?", ("amin") )?; // this also works, the returned value will be automatically converted to String let str_count: String = database.collect("select count(*) from user", ())?;
Additional flags
You can pass additional open flags to SQLite:
[dependencies]
sqlite3-sys = "*"
use rsqlite::{ffi, Database}; let flags = ffi::SQLITE_READONLY; let database = Database::open_with_flags("dbfile.db", flags)?; // now you can only read from the database let n: i32 = database.collect( "select a from table where something >= ?", (1))?;
Prepared Statements
It is possible to retain and reuse statments, this will keep the query plan and might increase the performance significantly if the statement is reused.
let mut statement = database.prepare("select age from user where age > ?")?; // Database methods are simply implemented in terms of statements. statement.for_each((27), |age: i32| { dbg!(age); })?; let age: i32 = database.prepare("select count(*) from user where age > ? limit 1")? .collect((200))?;
NULL values
If you have NULLABLE columes, you can use Option<T>
to pass and collect the values.
// use `None` to insert NULL values database.execute("insert into user(name, age) values (?,?)", (None::<&str>, 20))?; // use Option<T> to collect them back let (name, age) : (Option<String>, i32) = database.collect("select name, age from user limit 1", ())?; assert!((name, age) == (None, 20)); // an empty result set, would also be treated as None let name : Option<String> = database.collect("select name from user where age = ?", (200))?; assert!(name == None);
Type conversions
implsit type convertions in sqlite follow this table:
for example, if you collect a NULL
column as i32
, you'll get 0
.
Internal Type | Requested Type | Conversion |
---|---|---|
NULL | i32/i64 | Result is 0 |
NULL | f64 | Result is 0.0 |
NULL | String | Result is empty String::new() |
NULL | Box<u8> | Result is empty Box::new([]) |
INTEGER | f64 | Convert from integer to f64 |
INTEGER | String | ASCII rendering of the integer |
INTEGER | Box<u8> | Same as INTEGER->String |
FLOAT | i32/i64 | CAST to INTEGER |
FLOAT | String | ASCII rendering of the float |
FLOAT | Box<u8> | CAST to u8 |
TEXT | i32/i64 | CAST to i32/i64 |
TEXT | f64 | CAST to f64 |
TEXT | Box<u8> | No change |
BLOB | i32/i64 | CAST to i32/i64 |
BLOB | f64 | CAST to f64 |
BLOB | String | No change |
Transactions
You can use transactions with begin
, commit
and rollback
commands.
database.execute("begin", ())?; // begin a transaction ... let mut statement = database.prepare("insert into user(name, age) values (?, ?)")?; // insert 10 users using a prepared statement for age in 0..10 { let name = format!("user-{}", age); statement.execute((name.as_str(), age))?; } database.execute("commit", ())?; // commit all the changes database.execute("begin", ())?; // begin another transaction ... database.execute("delete from user where age > ?", (3))?; database.execute("rollback", ())?; // cancel this transaction let sum_age : i32 = database.collect("select sum(age) from user", ())?; assert!(sum_age == 45);
Blob
Use &[u8]
to store and Box<[u8]>
to retrive blob data.
database.execute("create table user (name TEXT, numbers BLOB)", ())?; let numbers = vec![1, 1, 2, 3, 5]; database.execute("insert into user values (?, ?)", ("amin", numbers.as_slice()))?; let stored_numbers : Box<[u8]> = database.collect("select numbers from user where name = ?", ("amin"))?; assert!(numbers.as_slice() == stored_numbers.as_ref());
License
MIT license - http://www.opensource.org/licenses/mit-license.php
Re-exports
pub use bindable::Bindable; |
pub use collectable::Collectable; |
pub use error::RsqliteError; |
pub use iterable::Iterable; |
pub use sqlite3_sys as ffi; |
Modules
bindable | |
collectable | |
error | |
iterable | Iterable types are expected to iterate over the sqlite rows |
Structs
Database | |
Statement |
Type Definitions
Result |