< 返回版块

2019-06-21 14:47    责任编辑:jay

标签:Tyro,Novice,Try

db

Sqlite

// tombasche/rando_ts.rs //gist
//rando_ts.rs
extern crate sqlite;

use std::fs::File;
use std::io::{BufRead, BufReader};


fn db_connection(db_name: &String) -> sqlite::Connection {
    let connection = sqlite::open(db_name).unwrap();
    connection.execute("CREATE TABLE teas (name text, variety text, last_used date)").unwrap();

    connection
}

fn main() {
    let db_name: &String = &"tea.db".to_string();
    let connection = db_connection(&db_name);

    let f = File::open("tea.txt").expect("Unable to open file");
    let f = BufReader::new(f);

    for line in f.lines() {
        let line = line.expect("Unable to read line");
        let mut splittable_line = line.split(",");
        let vec = splittable_line.collect::<Vec<&str>>();
        let statement = format!("INSERT INTO teas (name, variety) VALUES(\"{}\",\"{}\")", vec[0], vec[1].trim());
        connection.execute(statement).unwrap();
    }

    select_tea(&db_name);

}

fn select_tea(db_name: &String) {

    let connection = sqlite::open(db_name).unwrap();
    let statement = "SELECT name, variety, last_used FROM teas WHERE (last_used != date('now') or last_used != date('now') -1 or last_used IS NULL) ORDER BY RANDOM() LIMIT 1";


    let mut cursor = connection
        .prepare(statement)
        .unwrap()
        .cursor();

    let mut tea = String::new();
    while let Some(row) = cursor.next().unwrap() {
        println!("{}", format!("{}: {}", row[0].as_string().unwrap(), row[1].as_string().unwrap()));
        tea = row[0].as_string().unwrap().to_string();
    }

    let update_statement = format!("UPDATE teas SET last_used = date('now') WHERE name = \"{}\"", tea);
    connection.execute(update_statement).unwrap();

}
extern mod sqlite;

use sqlite::types::SQLITE_OK;


fn open_db(path: ~str) -> sqlite::database::Database {
    match sqlite::open(path) {
        Ok(db) => db,
        Err(r) => fail!(r)
    }
}

fn test() {
    let mut db = open_db(~":memory:");

    // Create table
    let result = db.exec("CREATE TABLE IF NOT EXISTS test (name text)");
    println(format!("Create OK? {:b}", result.is_ok()));

    // Prepared insert
    let mut stmt = match db.prepare("INSERT INTO test (name) VALUES (?)", &None) {
        Ok(s) => s,
        Err(err) => fail!(err)
    };

    assert!(stmt.bind_param(1, &sqlite::types::Text(~"test")) == SQLITE_OK);
}

fn main() {
    test()
}
use std::cmp::Ordering;
use std::process::{Command,Stdio};
use std::error::Error;
use std::io::Write;
use std::string::String;
use diesel::types::*;
use diesel::sqlite::Sqlite;
use diesel::row::Row;
use schema::games;

#[derive(Clone,Debug,PartialEq,Eq)]
pub enum GameURL {
    Steam,
    Custom(String),
}

impl FromSqlRow<Text, Sqlite> for GameURL {
    const FIELDS_NEEDED: usize = 1;
    fn build_from_row<T: Row<Sqlite>>(row: &mut T) -> Result<Self, Box<Error+Send+Sync>> {
        FromSql::<Text, Sqlite>::from_sql(row.take())
            .map(|s:String|
                 if s=="" {
                     GameURL::Steam
                 } else {
                     GameURL::Custom(s)
                 }
            )
    }
}

impl ToSql<Text, Sqlite> for GameURL {
    fn to_sql<W: Write>(&self, out: &mut ToSqlOutput<W,Sqlite>) -> Result<IsNull, Box<Error+Send+Sync>> {
        let res: String = match *self {
            GameURL::Steam => "".to_string(),
            GameURL::Custom(ref x) => x.clone(),
        };

        <String as ToSql<Text, Sqlite>>::to_sql(&res, out)
    }
}

expression_impls!(Text -> GameURL);
queryable_impls!(Text -> GameURL);
 @rdigua
 
use chrono::NaiveDateTime;
use diesel::backend::Backend;
use diesel::serialize::{self, Output, ToSql};
use diesel::deserialize::{self, FromSql};
use diesel::sqlite::Sqlite;
use diesel::sql_types::Integer;
use std::io::Write;

#[derive(FromSqlRow, SqlType, AsExpression, Clone, Copy, Default, QueryId, Debug)]
#[sqlite_type = "Integer"]
pub struct IntTimestamp;

impl ToSql<IntTimestamp, Sqlite> for NaiveDateTime {
    fn to_sql<W: Write>(&self, out: &mut Output<W, Sqlite>) -> serialize::Result {
        let x = self.timestamp() as i32;
        <i32 as ToSql<Integer, Sqlite>>::to_sql(&x, out)
    }
}

impl FromSql<IntTimestamp, Sqlite> for NaiveDateTime {
    fn from_sql(value: Option<&<Sqlite as Backend>::RawValue>) -> deserialize::Result<Self> {
        use diesel::sql_types::Date;
        let text_ptr = <*const str as FromSql<Date, Sqlite>>::from_sql(value)?;
        let text = unsafe { &*text_ptr };

        if let Ok(seconds) = text.parse::<i64>() {
            let nanos = 0 as u32;
            if let Some(timestamp) = Self::from_timestamp_opt(seconds, nanos) {
                return Ok(timestamp);
            }
        }

        Err(format!("Invalid datetime {}", text).into())
    }
}
use sqlite::Sqlite;
use types::{self, FromSql, FromSqlRow, HasSqlType, Numeric, Float};
use std::error::Error;
use sqlite::connection::SqliteValue;
use std::str::FromStr;
use query_source::Queryable;
use row::Row;

#[derive(Debug, Clone, PartialEq, PartialOrd)]
pub enum SqliteNumeric {
    Positive {
        number: f64
    },
    Negative {
        number: f64
    },
    NaN
}

impl FromSql<types::Numeric, Sqlite> for SqliteNumeric {
    fn from_sql(bytes: Option<&SqliteValue>) -> Result<Self, Box<Error>> {
        let bytes = not_none!(bytes);
        let number = f64::from_str(bytes.read_text()).unwrap();
        if number.is_sign_positive() {
            Ok(SqliteNumeric::Positive {number: number})
        } else if number.is_sign_negative() {
            Ok(SqliteNumeric::Negative {number: number})
        } else {
            Ok(SqliteNumeric::NaN)
        }
    }
}

impl HasSqlType<Numeric> for Sqlite {
    fn metadata() -> Self::TypeMetadata {
        <Sqlite as HasSqlType<Float>>::metadata()
    }
}

impl Queryable<Numeric, Sqlite> for SqliteNumeric {
    type Row = Self;

    fn build(row: Self) -> Self {
        row
    }
}

impl FromSqlRow<Numeric, Sqlite> for SqliteNumeric {
    fn build_from_row<R:  Row<Sqlite>>(row: &mut R) -> Result<Self, Box<Error>> {
        FromSql::<Numeric, Sqlite>::from_sql(row.take())
    }
}

russqlite

extern crate rusqlite;


fn main()
{
	let mut threads = vec!();
	for th in 0..300
	{
		let name = format!("{}.db", th);
		let t = std::thread::spawn(
			move ||
			{
				for _ in 0..100
				{
					let conn = rusqlite::Connection::open_with_flags(
						&name,
						rusqlite::OpenFlags::SQLITE_OPEN_NO_MUTEX
							| rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE
							| rusqlite::OpenFlags::SQLITE_OPEN_CREATE
					).expect(&format!("open sqlite db ({})", name));
					conn.execute_batch(
						"
						CREATE TABLE IF NOT EXISTS data (key primary key not null, data not null);
						"
					).unwrap();
				}
			}
		);
		threads.push(t);
	}

	for th in threads
	{
		th.join();
	}
}
extern crate rusqlite;
extern crate libsqlite3_sys as sqlite3;

use std::io::{self, Read};
use std::path::Path;
use std::mem;
use std::ptr;
use std::ffi::{CString, CStr};
use rusqlite::Connection;

#[derive(Debug)]
enum SQLite3Error {
    OpenError,
    StatementError,
    ExecuteError,
}

struct RawConnection {
    db: *mut sqlite3::sqlite3,
}

struct Statement {
    stmt: *mut sqlite3::sqlite3_stmt,
}

impl Drop for Statement {
    fn drop(&mut self) {
        println!("Called finalize!");
        unsafe {
            //           sqlite3::sqlite3_finalize(self.stmt);
        }
    }
}

fn create_statement(conn: &RawConnection, query: String) -> Result<Statement, SQLite3Error> {
    let len = query.len();
    let raw_query = CString::new(query).unwrap().as_ptr();
    unsafe {
        let mut stmt: *mut sqlite3::sqlite3_stmt = mem::uninitialized();
        if stmt.is_null() {
            println!("Now it is null!");
        }
        match sqlite3::sqlite3_prepare_v2(conn.db,
                                          raw_query,
                                          len as i32,
                                          &mut stmt,
                                          ptr::null_mut()) {
            sqlite3::SQLITE_OK => Ok(Statement { stmt: stmt }),
            _ => Err(SQLite3Error::StatementError),
        }
    }
}

fn open_connection(s: String) -> Result<RawConnection, SQLite3Error> {
    unsafe {
        let mut db: *mut sqlite3::sqlite3 = mem::uninitialized();
        let r = sqlite3::sqlite3_open_v2(CString::new(s).unwrap().as_ptr(),
                                         &mut db,
                                         sqlite3::SQLITE_OPEN_CREATE |
                                         sqlite3::SQLITE_OPEN_READWRITE,
                                         ptr::null());
        match r {
            sqlite3::SQLITE_OK => Ok(RawConnection { db: db }),
            _ => return Err(SQLite3Error::OpenError),
        }
    }
}


enum Cursor {
    OKCursor,
    DONECursor,
    RowsCursor {
        stmt: Statement,
        num_columns: i32,
        types: Vec<EntityType>,
        previous_status: i32,
    },
}

fn execute_statement(conn: &RawConnection, stmt: Statement) -> Result<Cursor, SQLite3Error> {

    match unsafe { sqlite3::sqlite3_step(stmt.stmt) } {
        sqlite3::SQLITE_OK => Ok(Cursor::OKCursor),
        sqlite3::SQLITE_DONE => Ok(Cursor::DONECursor),
        sqlite3::SQLITE_ROW => {
            let n_columns = unsafe { sqlite3::sqlite3_column_count(stmt.stmt) } as i32;
            let mut types: Vec<EntityType> = Vec::new();
            for i in 0..n_columns {
                types.push(match unsafe { sqlite3::sqlite3_column_type(stmt.stmt, i) } {
                    sqlite3::SQLITE_INTEGER => EntityType::Integer,
                    sqlite3::SQLITE_FLOAT => EntityType::Float,
                    sqlite3::SQLITE_TEXT => EntityType::Text,
                    sqlite3::SQLITE_BLOB => EntityType::Blob,
                    sqlite3::SQLITE_NULL => EntityType::Null,
                    _ => EntityType::Null,
                })
            }
            Ok(Cursor::RowsCursor {
                stmt: stmt,
                num_columns: n_columns,
                types: types,
                previous_status: sqlite3::SQLITE_ROW,
            })
        }
        x => {
            println!("{}", x);
            return Err(SQLite3Error::ExecuteError);
        }
    }

}

enum EntityType {
    Integer,
    Float,
    Text,
    Blob,
    Null,
}

enum Entity {
    Integer { int: i32 },
    Float { float: f64 },
    Text { text: String },
    Blob { blob: String },
    Null,
    OK,
    DONE,
}

type Row = Vec<Entity>;

impl Iterator for Cursor {
    type Item = Row;

    fn next(&mut self) -> Option<Self::Item> {
        match *self {
            Cursor::OKCursor => Some(vec![Entity::OK]),
            Cursor::DONECursor => Some(vec![Entity::DONE]),

            Cursor::RowsCursor { stmt: ref stmt,
                                 num_columns: num_columns,
                                 types: ref types,
                                 previous_status: ref mut ps } => {
                match *ps {
                    sqlite3::SQLITE_ROW => {
                        let mut result = vec![];
                        for i in 0..num_columns {
                            let entity_value = match types[i as usize] {
                                EntityType::Integer => {
                                    let value =
                                        unsafe { sqlite3::sqlite3_column_int(stmt.stmt, i) };
                                    Entity::Integer { int: value }
                                }
                                EntityType::Float => {
                                    let value =
                                        unsafe { sqlite3::sqlite3_column_double(stmt.stmt, i) };
                                    Entity::Float { float: value }
                                }
                                EntityType::Text => {
                                    let value =
                                unsafe {
                                    CStr::from_ptr(sqlite3::sqlite3_column_text(stmt.stmt, i) as *const i8).to_string_lossy().into_owned()
                                };
                                    Entity::Text { text: value }
                                }
                                EntityType::Blob => {
                                    let value = 
                                unsafe { 
                                    CStr::from_ptr(sqlite3::sqlite3_column_blob(stmt.stmt, i) as *const i8).to_string_lossy().into_owned() };
                                    Entity::Blob { blob: value }
                                }
                                EntityType::Null => Entity::Null {},
                            };
                            result.push(entity_value);
                        }
                        unsafe {
                            *ps = sqlite3::sqlite3_step(stmt.stmt);
                        };
                        Some(result)
                    }
                    _ => None,
                }
            }
        }
    }
}

fn main() {
    println!("Main!");
    let mut buff = String::new();
    println!("Open a new database: ");
    io::stdin().read_line(&mut buff);
    println!("String read: {}", buff);
    let mut conn = &open_connection(buff).unwrap();

    let mut query = String::new();
    loop {
        println!("Insert your query: ");
        io::stdin().read_line(&mut query);

        let stmt = create_statement(conn, query.clone()).unwrap();
        match execute_statement(conn, stmt) {
            Ok(_) => println!("Executed!"),
            Err(_) => println!("Error"),
        }
    }
}

diesel

use diesel;
use diesel::prelude::*;

// creates a connection to a SQLite database
pub fn get_sqlite_connection(database_url: String) -> SqliteConnection {
    SqliteConnection::establish(&database_url)
        .expect(&format!("Error connecting to {}", database_url))
}
#[macro_use]
extern crate diesel;

pub mod models;
pub mod schema;

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;

use self::models::{Post, NewPost};

pub fn create_post<'a>(conn: &SqliteConnection, title: &'a str, body: &'a str) -> usize {
    use schema::posts;

    let new_post = NewPost {
        title: title,
        body: body,
    };

    diesel::insert_into(posts::table)
        .values(&new_post)
        .execute(conn)
        .expect("Cannot insert")
}

pub fn establish_connection() -> SqliteConnection {
    let database_url = "tutu.db";
    SqliteConnection::establish(&database_url).expect("Can't connect DB")
}  

no_arg_sql_function!(last_insert_rowid, diesel::sql_types::Bigint);

fn main() {
    let connection = establish_connection();
    create_post(&connection, "title", "body");
    let x: i64 = diesel::select(last_insert_rowid).first(&connection).unwrap();
}
use crate::schema::users::dsl::*;
use crate::modules::User;
use diesel::prelude::*;
use diesel::query_dsl::QueryDsl;
use diesel::ExpressionMethods;
use diesel::RunQueryDsl

trait UserAccessor <T: Connection> {
    fn get_db(&self) -> &T;
    fn filter_active(&self, limit: i64) -> QueryResult<Vec<User>> {
        use self::schema::users::dsl::*;
        use diesel::query_dsl::QueryDsl;
        use diesel::ExpressionMethods;
        use diesel::RunQueryDsl;

        users.filter(active.eq(true))
            .limit(limit)
            .load::<User>(&self.get_db())
    }
}

struct UserAccessorPg {
    conn: PgConnection
}

impl UserAccessor<PgConnection> for UserAccessorPg {

    fn get_db(&self) -> &PgConnection {
        &self.conn
    }
}

struct UserAccessorSqlite {
    conn: SqliteConnection
}

impl UserAccessor<SqliteConnection> for UserAccessorSqlite {
    
    fn get_db(&self) -> &SqliteConnection {
        &self.conn
    }
}

fn main() {

    let conn = sqlite_connection();
    let sqlite = UserAccessorSqlite{ conn };
    let res = sqlite.filter_active(10);
    println!("{:?}", res);
}