wb_sqlite

SQLite宏。
将Rust结构体/字段映射到SQLite表/列。
生成const + fn (async sqlx / sync rusqlite)。

const 创建表、索引、日志

const SELECT {fields} FROM {table}

fn insert INSERT INTO {table} ...

fn update UPDATE {table} SET ... WHERE {pk} =

fn get_by_{field-name} for PRIMARY KEY + UNIQUE columns

所有派生项都保存到 target/generated/wb_sqlite，多亏了 virtue。

示例

创建表

#[derive(wb_sqlite::CreateTableSql)]
struct Dog {
	name: String,
}
assert_eq!(
	Dog::CREATE_TABLE_SQL,
	"CREATE TABLE IF NOT EXISTS dog (name TEXT NOT NULL) STRICT;"
);
assert!(rusqlite::Connection::open_in_memory().unwrap()
	.execute_batch(Dog::CREATE_TABLE_SQL).is_ok())

列约束

#[derive(wb_sqlite::CreateTableSql)]
struct Cat {
	#[sql(constraint = "UNIQUE")]
	name: String,
	weight: Option<f64>
}
assert_eq!(
	Cat::CREATE_TABLE_SQL,
	concat!(
	"CREATE TABLE IF NOT EXISTS cat ",
	"(name TEXT NOT NULL UNIQUE, weight REAL) STRICT;"
	)
);
assert!(rusqlite::Connection::open_in_memory().unwrap()
	.execute_batch(Cat::CREATE_TABLE_SQL).is_ok())

列数据类型覆盖

#[derive(wb_sqlite::CreateTableSql)]
struct Human {
	#[sql(constraint = "PRIMARY KEY")]
	id: i64,
	#[sql(constraint = "CHECK(name != '')")]
	name: String,
	image: Option<Vec<u8>>,
	#[sql(typ = "ANY")]
	data: Option<Vec<u8>>,
}
assert_eq!(
	Human::CREATE_TABLE_SQL,
	concat!(
	"CREATE TABLE IF NOT EXISTS human (",
	"id INTEGER NOT NULL PRIMARY KEY, ",
	"name TEXT NOT NULL CHECK(name != ''), ",
	"image BLOB, data ANY) STRICT;"
	)
);
assert!(rusqlite::Connection::open_in_memory().unwrap()
	.execute_batch(Human::CREATE_TABLE_SQL).is_ok())

表约束

#[derive(wb_sqlite::CreateTableSql)]
#[sql(constraint = "UNIQUE(owner,name)")]
struct Pet {
	#[sql(constraint = "PRIMARY KEY")]
	id: i64,
	#[sql(constraint = "REFERENCES human(id) ON UPDATE RESTRICT ON DELETE RESTRICT")]
	owner: i64,
	name: String,
}
assert_eq!(
	Pet::CREATE_TABLE_SQL,
	concat!(
	"CREATE TABLE IF NOT EXISTS pet (id INTEGER NOT NULL PRIMARY KEY, ",
	"owner INTEGER NOT NULL REFERENCES human(id) ON UPDATE RESTRICT ON DELETE RESTRICT, ",
	"name TEXT NOT NULL, UNIQUE(owner,name)) STRICT;"
	)
);
assert!(rusqlite::Connection::open_in_memory().unwrap()
	.execute_batch(Pet::CREATE_TABLE_SQL).is_ok())

插入 + 更新

sync rusqlite

use wb_sqlite::{CreateTableSql,InsertSync,UpdateSync};
#[derive(CreateTableSql,InsertSync,UpdateSync)]
struct Person {
	#[sql(constraint = "PRIMARY KEY")]
	id: i64,
	#[sql(constraint = "UNIQUE")]
	name: String,
}

fn main() -> Result<(), rusqlite::Error> {
	let conn = rusqlite::Connection::open_in_memory()?;
	conn.execute_batch(Person::CREATE_TABLE_SQL)?;

	let p = Person {
		id: 0,
		name: "me".to_owned(),
	};
	let id = p.insert_sync(&conn)?;
	assert!(id == 1);

	let p = Person {
		id: 1,
		name: "you".to_owned()
	};
	let ok = p.update_sync(&conn)?;
	assert!(ok);

	Ok(())
}

async sqlx

use wb_sqlite::{CreateTableSql,Get,Insert,Update};
#[derive(CreateTableSql,Get,Insert,Update,sqlx::FromRow)]
struct Person {
	#[sql(constraint = "PRIMARY KEY")]
	id: i64,
	#[sql(constraint = "UNIQUE")]
	name: String,
}

#[tokio::main(flavor = "current_thread")]
async fn main() -> Result<(), sqlx::Error> {
	use sqlx::{Connection, Executor};
	let mut conn = sqlx::SqliteConnection::connect(":memory:").await?;
	conn.execute(Person::CREATE_TABLE_SQL).await?;

	let p = Person {
		id: 0,
		name: "me".to_owned(),
	};
	let id = p.insert(&mut conn).await?;
	assert!(id == 1);

	let p = Person {
		id: 1,
		name: "you".to_owned()
	};
	let ok = p.update(&mut conn).await?;
	assert!(ok);

	let p = Person::get_by_id(1,&mut conn).await?;
	assert_eq!(p.name,"you");

	let p = Person::get_by_name("you",&mut conn).await?;
	assert_eq!(p.id,1);

	Ok(())
}