wb_sqlite

SQLite derive macros.
Map Rust struct / field to SQLite table / column.
Generate const + fn (async sqlx / sync rusqlite).

const CREATE table, index, log

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

All derived items are saved to target/generated/wb_sqlite thanks to virtue.

Examples

Create table

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

column constraint

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

column datatype override

#[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 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())

table constraint

#[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 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())

Insert + Update

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(())
}