Expand description
§Overview
This library is designed to provide simple interfaces to complete basic CRUD operations in a SQLite database, by leveraging rusqlite.
This should satisfy 90% of the creator’s local app needs
§Highlighted Features
§Actions
They are Serializeable (see serde) and Deserializeable enums that can be used to easily translate JSON requests into SQL operation. Then they can be used to build generic CRUD services, such as web services using Axum
Currently, the following actions are supported:
§Schema
sqlite::schema::fetch_schema_family can be used to automatically extract the schema of the database and use it to validate the input data, reducing the risk of malicious attacks
- It should be used together with the actions’ run (or additionally, for Create/Update ops, run_map) method to validate the input data
§Example of using a Read action
See
- this example of using this library together with Axum to create a simple web service
- related frontend code that uses the web service above to memorize Anime songs locally
Also, see the example below
§Quick code example of how to use a Read action to get data from a SQLite database
use jankenstore::action::{payload::ParsableOp, ReadOp};
use jankenstore::sqlite::{
schema::fetch_schema_family,
shift::val::v_txt,
basics::FetchConfig
};
use rusqlite::Connection;
use serde_json::{json, from_value};
let conn = Connection::open_in_memory().unwrap();
conn.execute_batch(
r#"
CREATE TABLE myexample (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
memo TEXT DEFAULT ''
);
INSERT INTO myexample (id, name, memo) VALUES (1, 'Alice', 'big');
INSERT INTO myexample (id,name, memo) VALUES (2, 'Alice', 'little');
INSERT INTO myexample (id, name, memo) VALUES (3, 'Bob', 'big');
"#
).unwrap();
/*
Schema family is a collection of table definitions as well as their relationships
following certain conventions, the function below will automatically extract them
and use them as basic violation checks to reduce malicious attacks
*/
let schema_family = fetch_schema_family(&conn, &[], "", "").unwrap();
// get all records that have the primary key 2
let op: ReadOp = from_value(json!(
{
"ByPk": {
"src": "myexample",
"keys": [2]
}
})).unwrap();
let (results, total) = op.run(&conn, &schema_family, None).unwrap();
assert_eq!(results.len(), 1);
assert_eq!(results[0]["name"], "Alice");
assert_eq!(results[0]["memo"], "little");
assert_eq!(total, 1);
// get all records by search keyword in the name column
// the action can also be created from a string
// a practical use case might be if on a API endpoint handler,
// the JSON request is received as a string, then
let query_param = r#"{ "Search": {
"table": "myexample",
"col": "name",
"keyword": "Alice"
}
}"#;
let op = ReadOp::from_str(query_param).unwrap();
let (results, total) = op.run(&conn, &schema_family, None).unwrap();
assert_eq!(results.len(), 2);
assert_eq!(results[0]["name"], "Alice");
assert_eq!(results[1]["name"], "Alice");
assert_eq!(total, 2);
// Add further condition to the search by using a FetchConfig
let (results, total) = op.run(&conn, &schema_family, Some(FetchConfig{
display_cols: Some(&["name", "memo"]),
is_distinct: true,
where_config: Some(("memo like '%'||?||'%'", &[v_txt("big")])),
group_by: None,
order_by: None,
limit: None,
offset: None
})).unwrap();
assert_eq!(results.len(), 1);
assert_eq!(results[0]["name"], "Alice");
assert_eq!(results[0]["memo"], "big");
assert_eq!(total, 1);