From 7c39bb021b55d7219c60ceaec34b396dfc02ce18 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Sun, 4 May 2025 16:28:54 -0400 Subject: [PATCH 01/15] add caching performance test and caching strategy enum --- Makefile | 14 ++++++++--- src/core.rs | 21 +++++++++++++---- src/sql.rs | 67 +++++++++++++++++++++++++++++++++++++++++++++++++++-- src/test.rs | 64 +++++++++++++++++++++++++++++++++++++++++++++++++- 4 files changed, 156 insertions(+), 10 deletions(-) diff --git a/Makefile b/Makefile index 833946d..1ebb6d5 100644 --- a/Makefile +++ b/Makefile @@ -166,6 +166,14 @@ perf_test_size = 100000 SQLITE_DB = ".relatable/relatable.db" PG_DB = "postgresql:///rltbl_db" +.PHONY: test_caching_sqlite +test_caching_sqlite: debug + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 20 --force + +.PHONY: test_caching_postgres +test_caching_postgres: sqlx_debug + target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 30 --force + .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug target/debug/rltbl --database $(SQLITE_DB) init --force @@ -192,13 +200,13 @@ test_perf_sqlx_postgres: test/perf/tsv/penguin.tsv sqlx_debug # Combined tests .PHONY: test_rusqlite -test_rusqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc test_tesh_common_as_sqlite test_tesh_sqlite_only test_random_sqlite test_perf_sqlite +test_rusqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc test_tesh_common_as_sqlite test_tesh_sqlite_only test_random_sqlite test_perf_sqlite test_caching_sqlite .PHONY: test_sqlx_sqlite -test_sqlx_sqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc_sqlx test_tesh_sqlx_common_as_sqlite test_tesh_sqlx_sqlite_only test_random_sqlx_sqlite test_perf_sqlx_sqlite +test_sqlx_sqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc_sqlx test_tesh_sqlx_common_as_sqlite test_tesh_sqlx_sqlite_only test_random_sqlx_sqlite test_perf_sqlx_sqlite test_caching_sqlite .PHONY: test_sqlx_postgres -test_sqlx_postgres: src/resources/main.js src/resources/main.css test_fmt_and_unittest_postgres test_tesh_doc_sqlx test_tesh_sqlx_common_as_postgres test_tesh_sqlx_postgres_only test_random_sqlx_postgres test_perf_sqlx_postgres +test_sqlx_postgres: src/resources/main.js src/resources/main.css test_fmt_and_unittest_postgres test_tesh_doc_sqlx test_tesh_sqlx_common_as_postgres test_tesh_sqlx_postgres_only test_random_sqlx_postgres test_perf_sqlx_postgres test_caching_postgres .PHONY: test test: test_rusqlite diff --git a/src/core.rs b/src/core.rs index d0ba65f..1ce6b88 100644 --- a/src/core.rs +++ b/src/core.rs @@ -8,8 +8,8 @@ use rltbl::{ git, select::{Select, SelectField}, sql::{ - self, DbActiveConnection, DbConnection, DbKind, DbTransaction, JsonRow, SqlParam, - VecInto as _, + self, CachingStrategy, DbActiveConnection, DbConnection, DbKind, DbTransaction, JsonRow, + SqlParam, VecInto as _, }, table::{Column, Message, Row, Table}, }; @@ -512,10 +512,23 @@ impl Relatable { /// Get the number of rows returned by this [Select] pub async fn count(&self, select: &Select) -> Result { tracing::trace!("Relatable::count({select:?})"); + self.count_with_strategy(select, CachingStrategy::NoCache) + .await + } + + /// Get the number of rows returned by this [Select] using the given caching strategy. + pub async fn count_with_strategy( + &self, + select: &Select, + strategy: CachingStrategy, + ) -> Result { + tracing::trace!("Relatable::count_with_strategy({select:?}, {strategy:?})"); let (statement, params) = select.to_sql_count(&self.connection.kind())?; let params = json!(params); - // TODO: Implement caching. - let json_rows = self.connection.query(&statement, Some(¶ms)).await?; + let json_rows = self + .connection + .cache(&statement, Some(¶ms), strategy) + .await?; match json_rows.get(0) { Some(json_row) => json_row.get_unsigned("count"), None => Ok(0), diff --git a/src/sql.rs b/src/sql.rs index b5f3699..52663e3 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -18,8 +18,7 @@ use regex::Regex; use serde::{Deserialize, Serialize}; use serde_json::{json, Map as JsonMap, Value as JsonValue}; -#[cfg(feature = "sqlx")] -use std::str::FromStr as _; +use std::str::FromStr; #[cfg(feature = "rusqlite")] use rusqlite; @@ -52,6 +51,40 @@ pub static MAX_PARAMS_SQLITE: usize = 32766; /// that can be bound to a Postgres query pub static MAX_PARAMS_POSTGRES: usize = 65535; +// TODO: Read the below comment and consider whether it is time to get rid of this +// It's possible that will only useful up until when we decide upon our final caching strategy. +// In the meantime, it is useful to define the following struct, which lets us compare the +// performance of various caching strategies. +#[derive(Clone, Copy, Debug, PartialEq, Eq)] +pub enum CachingStrategy { + NoCache, + Truncate, + MaxChange, + Metadata, + Trigger, +} + +impl FromStr for CachingStrategy { + type Err = anyhow::Error; + + fn from_str(strategy: &str) -> Result { + tracing::trace!("CachingStrategy::from_str({strategy:?})"); + match strategy.to_lowercase().as_str() { + "none" => Ok(Self::NoCache), + "truncate" => Ok(Self::Truncate), + "max_change" => Ok(Self::MaxChange), + "metadata" => Ok(Self::Metadata), + "trigger" => Ok(Self::Trigger), + _ => { + return Err(RelatableError::InputError(format!( + "Unrecognized strategy: {strategy}" + )) + .into()); + } + } + } +} + /// Represents the kind of database being managed #[derive(Clone, Copy, Debug, PartialEq, Eq)] pub enum DbKind { @@ -297,6 +330,21 @@ impl DbConnection { let rows = self.query(statement, params).await?; Ok(extract_value(&rows)) } + + pub async fn cache( + &self, + statement: &str, + params: Option<&JsonValue>, + strategy: CachingStrategy, + ) -> Result> { + match strategy { + CachingStrategy::NoCache => self.query(statement, params).await, + CachingStrategy::Truncate => todo!(), + CachingStrategy::MaxChange => todo!(), + CachingStrategy::Metadata => todo!(), + CachingStrategy::Trigger => todo!(), + } + } } #[derive(Debug)] @@ -391,6 +439,21 @@ impl DbTransaction<'_> { let rows = self.query(statement, params)?; Ok(extract_value(&rows)) } + + pub fn cache( + &mut self, + statement: &str, + params: Option<&JsonValue>, + strategy: CachingStrategy, + ) -> Result> { + match strategy { + CachingStrategy::NoCache => self.query(statement, params), + CachingStrategy::Truncate => todo!(), + CachingStrategy::MaxChange => todo!(), + CachingStrategy::Metadata => todo!(), + CachingStrategy::Trigger => todo!(), + } + } } /////////////////////////////////////////////////////////////////////////////// diff --git a/src/test.rs b/src/test.rs index 9395d2a..6a9f32e 100644 --- a/src/test.rs +++ b/src/test.rs @@ -1,6 +1,10 @@ //! API tests -use rltbl::core::{Relatable, RLTBL_DEFAULT_DB}; +use rltbl::{ + core::{Relatable, RLTBL_DEFAULT_DB}, + select::Select, + sql::CachingStrategy, +}; use clap::{ArgAction, Parser, Subcommand}; use clap_verbosity_flag::Verbosity; @@ -9,6 +13,7 @@ use rand::{ rngs::StdRng, SeedableRng as _, }; +use std::{str::FromStr, time::Instant}; #[derive(Parser, Debug)] #[command(version, about = "Relatable (rltbl): Connect your data!", long_about = None)] @@ -51,6 +56,29 @@ pub enum Command { #[arg(long, default_value = "15", action = ArgAction::Set)] max_length: usize, }, + /// Test database read performance by repeatedly counting the number of rows in a given + /// table. + TestReadPerf { + #[arg(action = ArgAction::Set)] + table: String, + + #[arg(action = ArgAction::Set)] + size: usize, + + #[arg(action = ArgAction::Set)] + fetches: usize, + + #[arg(action = ArgAction::Set)] + fail_after_secs: u64, + + /// Overwrite an existing database + #[arg(long, action = ArgAction::SetTrue)] + force: bool, + + /// One of: none, truncate, max_change, metadata, trigger + #[arg(long, default_value = "none", action = ArgAction::Set)] + caching_strategy: String, + }, } #[derive(Clone, Debug, PartialEq, Eq)] @@ -255,5 +283,39 @@ async fn main() { .expect("Could not connect to relatable database"); generate_operation_sequence(&cli, &rltbl, table, *min_length, *max_length).await; } + Command::TestReadPerf { + table, + size, + fetches, + fail_after_secs, + caching_strategy, + force, + } => { + tracing::info!("Building demonstration database with {size} rows ..."); + let rltbl = Relatable::build_demo(Some(&cli.database), force, *size) + .await + .unwrap(); + tracing::info!("Demonstration database built and loaded."); + + tracing::info!("Counting the number of rows in table {table} ..."); + let now = Instant::now(); + let select = Select::from(table); + let strategy = CachingStrategy::from_str(&caching_strategy.to_lowercase()).unwrap(); + let mut i = 0; + let mut count = 0; + let mut elapsed; + while i < *fetches { + count = rltbl.count_with_strategy(&select, strategy).await.unwrap(); + elapsed = now.elapsed().as_secs(); + if elapsed > *fail_after_secs { + panic!("Taking longer than {fail_after_secs}s. Timing out."); + } + i += 1; + } + elapsed = now.elapsed().as_secs(); + tracing::info!( + "Counted {count} rows from table '{table}' {fetches} times in {elapsed}s" + ); + } } } From d7a979c67a49cc5f4f12e1ab04e644abd55e57c7 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Sun, 4 May 2025 18:08:35 -0400 Subject: [PATCH 02/15] add naive caching strategy --- Makefile | 6 +++-- src/sql.rs | 78 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 82 insertions(+), 2 deletions(-) diff --git a/Makefile b/Makefile index 1ebb6d5..6b9cfc6 100644 --- a/Makefile +++ b/Makefile @@ -168,11 +168,13 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 20 --force + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 10000 5 --force --caching-strategy naive + # target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 20 --force .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 30 --force + target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 10000 5 --force --caching-strategy naive + # target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 30 --force .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug diff --git a/src/sql.rs b/src/sql.rs index 52663e3..39cdacb 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -58,6 +58,7 @@ pub static MAX_PARAMS_POSTGRES: usize = 65535; #[derive(Clone, Copy, Debug, PartialEq, Eq)] pub enum CachingStrategy { NoCache, + Naive, Truncate, MaxChange, Metadata, @@ -71,6 +72,7 @@ impl FromStr for CachingStrategy { tracing::trace!("CachingStrategy::from_str({strategy:?})"); match strategy.to_lowercase().as_str() { "none" => Ok(Self::NoCache), + "naive" => Ok(Self::Naive), "truncate" => Ok(Self::Truncate), "max_change" => Ok(Self::MaxChange), "metadata" => Ok(Self::Metadata), @@ -339,6 +341,30 @@ impl DbConnection { ) -> Result> { match strategy { CachingStrategy::NoCache => self.query(statement, params).await, + CachingStrategy::Naive => { + let sql2 = format!( + r#"SELECT value FROM "cache" WHERE key = {param} LIMIT 1"#, + param = SqlParam::new(&self.kind()).next() + ); + let params2 = json!([statement]); + match self.query_one(&sql2, Some(¶ms2)).await? { + Some(json_row) => { + let value = json_row.get_string("value")?; + // tracing::warn!("CACHED VALUE: {value:?}"); + let json_rows: Vec = serde_json::from_str(&value)?; + tracing::debug!("USED CACHE: {json_rows:?}"); + Ok(json_rows) + } + None => { + let json_rows = self.query(statement, params).await?; + let sql3 = r#"INSERT INTO "cache" VALUES ($1, $2)"#; + let params3 = json!([statement, json_rows]); + self.query(&sql3, Some(¶ms3)).await?; + tracing::debug!("UPDATED CACHE: {json_rows:?}"); + Ok(json_rows) + } + } + } CachingStrategy::Truncate => todo!(), CachingStrategy::MaxChange => todo!(), CachingStrategy::Metadata => todo!(), @@ -448,6 +474,30 @@ impl DbTransaction<'_> { ) -> Result> { match strategy { CachingStrategy::NoCache => self.query(statement, params), + CachingStrategy::Naive => { + let sql2 = format!( + r#"SELECT value FROM "cache" WHERE key = {param} LIMIT 1"#, + param = SqlParam::new(&self.kind()).next() + ); + let params2 = json!([statement]); + match self.query_one(&sql2, Some(¶ms2))? { + Some(json_row) => { + let value = json_row.get_string("value")?; + // tracing::warn!("CACHED VALUE: {value:?}"); + let json_rows: Vec = serde_json::from_str(&value)?; + tracing::debug!("USED CACHE: {json_rows:?}"); + Ok(json_rows) + } + None => { + let json_rows = self.query(statement, params)?; + let sql3 = r#"INSERT INTO "cache" VALUES ($1, $2)"#; + let params3 = json!([statement, json_rows]); + self.query(&sql3, Some(¶ms3))?; + tracing::debug!("UPDATED CACHE: {json_rows:?}"); + Ok(json_rows) + } + } + } CachingStrategy::Truncate => todo!(), CachingStrategy::MaxChange => todo!(), CachingStrategy::Metadata => todo!(), @@ -918,6 +968,33 @@ pub fn generate_table_table_ddl(force: bool, db_kind: &DbKind) -> Vec { generate_table_ddl(&table, force, db_kind).unwrap() } +pub fn generate_cache_table_ddl(force: bool, db_kind: &DbKind) -> Vec { + tracing::trace!("generate_cache_table_ddl({force}, {db_kind:?})"); + let mut table = Table { + name: "cache".to_string(), + has_meta: false, + ..Default::default() + }; + table.columns.insert( + "key".into(), + Column { + table: "cache".into(), + name: "key".into(), + primary_key: true, + ..Default::default() + }, + ); + table.columns.insert( + "value".into(), + Column { + table: "cache".into(), + name: "value".into(), + ..Default::default() + }, + ); + generate_table_ddl(&table, force, db_kind).unwrap() +} + // TODO: When the Table struct is rich enough to support different datatypes, foreign keys, // and defaults, create these other meta tables in a similar way to the table table above. @@ -1068,6 +1145,7 @@ pub fn generate_message_table_ddl(force: bool, db_kind: &DbKind) -> Vec pub fn generate_meta_tables_ddl(force: bool, db_kind: &DbKind) -> Vec { tracing::trace!("generate_meta_tables_ddl({force}, {db_kind:?})"); let mut ddl = generate_table_table_ddl(force, db_kind); + ddl.append(&mut generate_cache_table_ddl(force, db_kind)); ddl.append(&mut generate_user_table_ddl(force, db_kind)); ddl.append(&mut generate_change_table_ddl(force, db_kind)); ddl.append(&mut generate_history_table_ddl(force, db_kind)); From 5a0158d5104affb5bcebe1c8d0d9088551e5af7e Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Sun, 4 May 2025 19:02:44 -0400 Subject: [PATCH 03/15] implement truncate caching strategy --- Makefile | 8 +++---- src/core.rs | 9 +++++-- src/sql.rs | 67 +++++++++++++---------------------------------------- src/test.rs | 53 +++++++++++++++++++++++++++++++----------- 4 files changed, 66 insertions(+), 71 deletions(-) diff --git a/Makefile b/Makefile index 6b9cfc6..3cca36c 100644 --- a/Makefile +++ b/Makefile @@ -168,13 +168,13 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 10000 5 --force --caching-strategy naive - # target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 20 --force + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 1000 10 --force --caching-strategy truncate + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 0 15 --force --caching-strategy none .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 10000 5 --force --caching-strategy naive - # target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 30 --force + target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 1000 10 --force --caching-strategy truncate + target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 0 20 --force --caching-strategy none .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug diff --git a/src/core.rs b/src/core.rs index 1ce6b88..c78265c 100644 --- a/src/core.rs +++ b/src/core.rs @@ -804,7 +804,7 @@ impl Relatable { let author = match std::env::var("RLTBL_GIT_AUTHOR") { Err(err) => match err { std::env::VarError::NotPresent => { - tracing::info!("Not committing to git because RLTBL_GIT_AUTHOR not defined"); + tracing::debug!("Not committing to git because RLTBL_GIT_AUTHOR not defined"); return Ok(()); } _ => { @@ -1058,6 +1058,11 @@ impl Relatable { } }; } + + tracing::debug!("Truncating cache"); + let sql = r#"DELETE FROM "cache""#; + tx.query_value(&sql, None)?; + Ok(()) } @@ -2200,7 +2205,7 @@ impl Relatable { // Add the row to the table: let (sql, params) = new_row.as_insert(&table.name, &tx.kind()); - tracing::info!("_add_row {sql} {params:?}"); + tracing::debug!("_add_row {sql} {params:?}"); tx.query(&sql, Some(¶ms))?; let after_id = match after_id { diff --git a/src/sql.rs b/src/sql.rs index 39cdacb..a408035 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -335,37 +335,41 @@ impl DbConnection { pub async fn cache( &self, - statement: &str, + sql: &str, params: Option<&JsonValue>, strategy: CachingStrategy, ) -> Result> { + tracing::trace!("cache({sql}, {params:?}, {strategy:?})"); match strategy { - CachingStrategy::NoCache => self.query(statement, params).await, - CachingStrategy::Naive => { + CachingStrategy::NoCache => self.query(sql, params).await, + CachingStrategy::Naive | CachingStrategy::Truncate => { let sql2 = format!( - r#"SELECT value FROM "cache" WHERE key = {param} LIMIT 1"#, + r#"SELECT "value" FROM "cache" WHERE "key" = {param} LIMIT 1"#, param = SqlParam::new(&self.kind()).next() ); - let params2 = json!([statement]); + let params2 = json!([sql]); match self.query_one(&sql2, Some(¶ms2)).await? { Some(json_row) => { + tracing::debug!("Cache hit"); let value = json_row.get_string("value")?; - // tracing::warn!("CACHED VALUE: {value:?}"); let json_rows: Vec = serde_json::from_str(&value)?; - tracing::debug!("USED CACHE: {json_rows:?}"); Ok(json_rows) } None => { - let json_rows = self.query(statement, params).await?; - let sql3 = r#"INSERT INTO "cache" VALUES ($1, $2)"#; - let params3 = json!([statement, json_rows]); + tracing::debug!("Cache miss"); + let json_rows = self.query(sql, params).await?; + let mut param = SqlParam::new(&self.kind()); + let sql3 = format!( + r#"INSERT INTO "cache" VALUES ({param1}, {param2})"#, + param1 = param.next(), + param2 = param.next() + ); + let params3 = json!([sql, json_rows]); self.query(&sql3, Some(¶ms3)).await?; - tracing::debug!("UPDATED CACHE: {json_rows:?}"); Ok(json_rows) } } } - CachingStrategy::Truncate => todo!(), CachingStrategy::MaxChange => todo!(), CachingStrategy::Metadata => todo!(), CachingStrategy::Trigger => todo!(), @@ -465,45 +469,6 @@ impl DbTransaction<'_> { let rows = self.query(statement, params)?; Ok(extract_value(&rows)) } - - pub fn cache( - &mut self, - statement: &str, - params: Option<&JsonValue>, - strategy: CachingStrategy, - ) -> Result> { - match strategy { - CachingStrategy::NoCache => self.query(statement, params), - CachingStrategy::Naive => { - let sql2 = format!( - r#"SELECT value FROM "cache" WHERE key = {param} LIMIT 1"#, - param = SqlParam::new(&self.kind()).next() - ); - let params2 = json!([statement]); - match self.query_one(&sql2, Some(¶ms2))? { - Some(json_row) => { - let value = json_row.get_string("value")?; - // tracing::warn!("CACHED VALUE: {value:?}"); - let json_rows: Vec = serde_json::from_str(&value)?; - tracing::debug!("USED CACHE: {json_rows:?}"); - Ok(json_rows) - } - None => { - let json_rows = self.query(statement, params)?; - let sql3 = r#"INSERT INTO "cache" VALUES ($1, $2)"#; - let params3 = json!([statement, json_rows]); - self.query(&sql3, Some(¶ms3))?; - tracing::debug!("UPDATED CACHE: {json_rows:?}"); - Ok(json_rows) - } - } - } - CachingStrategy::Truncate => todo!(), - CachingStrategy::MaxChange => todo!(), - CachingStrategy::Metadata => todo!(), - CachingStrategy::Trigger => todo!(), - } - } } /////////////////////////////////////////////////////////////////////////////// diff --git a/src/test.rs b/src/test.rs index 6a9f32e..66abbaf 100644 --- a/src/test.rs +++ b/src/test.rs @@ -3,7 +3,7 @@ use rltbl::{ core::{Relatable, RLTBL_DEFAULT_DB}, select::Select, - sql::CachingStrategy, + sql::{CachingStrategy, JsonRow}, }; use clap::{ArgAction, Parser, Subcommand}; @@ -13,7 +13,11 @@ use rand::{ rngs::StdRng, SeedableRng as _, }; -use std::{str::FromStr, time::Instant}; +use std::{ + str::FromStr, + thread, + time::{Duration, Instant}, +}; #[derive(Parser, Debug)] #[command(version, about = "Relatable (rltbl): Connect your data!", long_about = None)] @@ -26,7 +30,7 @@ pub struct Cli { database: String, #[arg(long, default_value="", action = ArgAction::Set, env = "RLTBL_USER")] - user: String, + user: Option, #[command(flatten)] verbose: Verbosity, @@ -68,6 +72,9 @@ pub enum Command { #[arg(action = ArgAction::Set)] fetches: usize, + #[arg(action = ArgAction::Set)] + edit_rate: usize, + #[arg(action = ArgAction::Set)] fail_after_secs: u64, @@ -104,6 +111,17 @@ impl std::fmt::Display for DbOperation { } } +fn random_between(min: usize, max: usize, seed: &mut i64) -> usize { + let between = Uniform::from(min..max); + let mut rng = if *seed < 0 { + StdRng::from_entropy() + } else { + *seed += 10; + StdRng::seed_from_u64(*seed as u64) + }; + between.sample(&mut rng) +} + async fn generate_operation_sequence( cli: &Cli, rltbl: &Relatable, @@ -129,17 +147,6 @@ async fn generate_operation_sequence( After this function returns, the database should be in the same logical state as it was before. */ - fn random_between(min: usize, max: usize, seed: &mut i64) -> usize { - let between = Uniform::from(min..max); - let mut rng = if *seed < 0 { - StdRng::from_entropy() - } else { - *seed += 10; - StdRng::seed_from_u64(*seed as u64) - }; - between.sample(&mut rng) - } - let mut seed: i64 = match cli.seed { None => -1, Some(seed) => seed as i64, @@ -287,6 +294,7 @@ async fn main() { table, size, fetches, + edit_rate, fail_after_secs, caching_strategy, force, @@ -310,6 +318,23 @@ async fn main() { if elapsed > *fail_after_secs { panic!("Taking longer than {fail_after_secs}s. Timing out."); } + if *edit_rate != 0 && random_between(0, *edit_rate, &mut -1) == 1 { + let user = match &cli.user { + Some(user) => user.clone(), + None => whoami::username(), + }; + let after_id = random_between(1, *size, &mut -1); + let row = rltbl + .add_row(table, &user, Some(after_id), &JsonRow::new()) + .await + .unwrap(); + tracing::debug!("Added row {} (order {})", row.id, row.order); + } else { + tracing::debug!("Not making any edits"); + } + + // A small sleep to prevent over-taxing the CPU + thread::sleep(Duration::from_millis(2)); i += 1; } elapsed = now.elapsed().as_secs(); From 9c0a5946513fb65852df2b0c803c8936a746f52a Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Mon, 5 May 2025 09:14:46 -0400 Subject: [PATCH 04/15] implement max_change caching strategy --- Makefile | 10 +++--- src/core.rs | 10 ++++-- src/sql.rs | 88 +++++++++++++++++++++++++++++++++++++---------------- src/test.rs | 2 ++ 4 files changed, 77 insertions(+), 33 deletions(-) diff --git a/Makefile b/Makefile index 3cca36c..0dbc28b 100644 --- a/Makefile +++ b/Makefile @@ -168,13 +168,15 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 1000 10 --force --caching-strategy truncate - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 2000 0 15 --force --caching-strategy none + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy max_change + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 0 35 --force --caching-strategy none .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 1000 10 --force --caching-strategy truncate - target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 2000 0 20 --force --caching-strategy none + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy max_change + target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate + target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 0 60 --force --caching-strategy none .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug diff --git a/src/core.rs b/src/core.rs index c78265c..2f9b094 100644 --- a/src/core.rs +++ b/src/core.rs @@ -1059,9 +1059,13 @@ impl Relatable { }; } - tracing::debug!("Truncating cache"); - let sql = r#"DELETE FROM "cache""#; - tx.query_value(&sql, None)?; + tracing::info!("Deleting entries earlier than change_id {change_id} from cache"); + let sql = format!( + r#"DELETE FROM "cache" WHERE "change_id" < {}"#, + SqlParam::new(&tx.kind()).next() + ); + let params = json!([change_id]); + tx.query(&sql, Some(¶ms))?; Ok(()) } diff --git a/src/sql.rs b/src/sql.rs index a408035..f1ac891 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -356,11 +356,11 @@ impl DbConnection { Ok(json_rows) } None => { - tracing::debug!("Cache miss"); + tracing::info!("Cache miss"); let json_rows = self.query(sql, params).await?; let mut param = SqlParam::new(&self.kind()); let sql3 = format!( - r#"INSERT INTO "cache" VALUES ({param1}, {param2})"#, + r#"INSERT INTO "cache" VALUES ({param1}, {param2}, NULL)"#, param1 = param.next(), param2 = param.next() ); @@ -370,7 +370,51 @@ impl DbConnection { } } } - CachingStrategy::MaxChange => todo!(), + CachingStrategy::MaxChange => { + let sql2 = format!( + r#"SELECT "value" FROM "cache" WHERE "key" = {param} LIMIT 1"#, + param = SqlParam::new(&self.kind()).next() + ); + let params2 = json!([sql]); + match self.query_one(&sql2, Some(¶ms2)).await? { + Some(json_row) => { + tracing::debug!("Cache hit"); + let value = json_row.get_string("value")?; + let json_rows: Vec = serde_json::from_str(&value)?; + Ok(json_rows) + } + None => { + // Get the last change_id + let change_id = match self + .query_value( + r#"SELECT MAX("change_id") AS "change_id" FROM "change""#, + None, + ) + .await? + { + None => json!(0), + Some(change_id) if change_id.to_string().to_lowercase() == "null" => { + json!(0) + } + Some(change_id) => change_id, + }; + tracing::info!("Cache miss. Adding to cache with change_id: {change_id}"); + + let json_rows = self.query(sql, params).await?; + let mut param = SqlParam::new(&self.kind()); + let sql3 = format!( + r#"INSERT INTO "cache" ("key", "value", "change_id") + VALUES ({param1}, {param2}, {param3})"#, + param1 = param.next(), + param2 = param.next(), + param3 = param.next(), + ); + let params3 = json!([sql, json_rows, change_id]); + self.query(&sql3, Some(¶ms3)).await?; + Ok(json_rows) + } + } + } CachingStrategy::Metadata => todo!(), CachingStrategy::Trigger => todo!(), } @@ -935,29 +979,21 @@ pub fn generate_table_table_ddl(force: bool, db_kind: &DbKind) -> Vec { pub fn generate_cache_table_ddl(force: bool, db_kind: &DbKind) -> Vec { tracing::trace!("generate_cache_table_ddl({force}, {db_kind:?})"); - let mut table = Table { - name: "cache".to_string(), - has_meta: false, - ..Default::default() - }; - table.columns.insert( - "key".into(), - Column { - table: "cache".into(), - name: "key".into(), - primary_key: true, - ..Default::default() - }, - ); - table.columns.insert( - "value".into(), - Column { - table: "cache".into(), - name: "value".into(), - ..Default::default() - }, - ); - generate_table_ddl(&table, force, db_kind).unwrap() + let mut ddl = vec![]; + if force { + if let DbKind::Postgres = db_kind { + ddl.push(format!(r#"DROP TABLE IF EXISTS "cache" CASCADE"#)); + } + } + + ddl.push(format!( + r#"CREATE TABLE "cache" ( + "key" TEXT PRIMARY KEY, + "value" TEXT, + "change_id" INTEGER + )"# + )); + ddl } // TODO: When the Table struct is rich enough to support different datatypes, foreign keys, diff --git a/src/test.rs b/src/test.rs index 66abbaf..6c39c05 100644 --- a/src/test.rs +++ b/src/test.rs @@ -319,6 +319,8 @@ async fn main() { panic!("Taking longer than {fail_after_secs}s. Timing out."); } if *edit_rate != 0 && random_between(0, *edit_rate, &mut -1) == 1 { + // TODO: Don't always do the same operation. Mix it up a little (updates, + // adds, deletes, etc.) let user = match &cli.user { Some(user) => user.clone(), None => whoami::username(), From e200d93afa95b09e3165f18ac265217ca1f15463 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Mon, 5 May 2025 09:56:07 -0400 Subject: [PATCH 05/15] don't always edit in the same way --- src/test.rs | 68 ++++++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 59 insertions(+), 9 deletions(-) diff --git a/src/test.rs b/src/test.rs index 6c39c05..89e8c9b 100644 --- a/src/test.rs +++ b/src/test.rs @@ -1,7 +1,7 @@ //! API tests use rltbl::{ - core::{Relatable, RLTBL_DEFAULT_DB}, + core::{Change, ChangeAction, ChangeSet, Relatable, RLTBL_DEFAULT_DB}, select::Select, sql::{CachingStrategy, JsonRow}, }; @@ -13,6 +13,7 @@ use rand::{ rngs::StdRng, SeedableRng as _, }; +use serde_json::json; use std::{ str::FromStr, thread, @@ -305,6 +306,15 @@ async fn main() { .unwrap(); tracing::info!("Demonstration database built and loaded."); + fn random_op<'a>() -> &'a str { + match random_between(0, 3, &mut -1) { + 0 => "add", + 1 => "update", + 2 => "move", + _ => unreachable!(), + } + } + tracing::info!("Counting the number of rows in table {table} ..."); let now = Instant::now(); let select = Select::from(table); @@ -319,18 +329,58 @@ async fn main() { panic!("Taking longer than {fail_after_secs}s. Timing out."); } if *edit_rate != 0 && random_between(0, *edit_rate, &mut -1) == 1 { - // TODO: Don't always do the same operation. Mix it up a little (updates, - // adds, deletes, etc.) let user = match &cli.user { Some(user) => user.clone(), None => whoami::username(), }; - let after_id = random_between(1, *size, &mut -1); - let row = rltbl - .add_row(table, &user, Some(after_id), &JsonRow::new()) - .await - .unwrap(); - tracing::debug!("Added row {} (order {})", row.id, row.order); + match random_op() { + "add" => { + let after_id = random_between(1, *size, &mut -1); + let row = rltbl + .add_row(table, &user, Some(after_id), &JsonRow::new()) + .await + .unwrap(); + tracing::debug!("Added row {} (order {})", row.id, row.order); + } + "update" => { + let row_to_update = random_between(1, *size, &mut -1); + let num_changes = rltbl + .set_values(&ChangeSet { + user, + action: ChangeAction::Do, + table: table.to_string(), + description: "Set one value".to_string(), + changes: vec![Change::Update { + row: row_to_update, + column: "study_name".to_string(), + before: json!("FAKE123"), + after: json!("PHONY123"), + }], + }) + .await + .unwrap() + .changes + .len(); + if num_changes < 1 { + panic!("No changes made"); + } + tracing::debug!("Updated row {row_to_update}"); + } + "move" => { + let after_id = random_between(1, *size, &mut -1); + let row = random_between(1, *size, &mut -1); + let new_order = rltbl + .move_row(table, &user, row, after_id) + .await + .expect("Failed to move row"); + if new_order > 0 { + tracing::debug!("Moved row {row} after row {after_id}"); + } else { + panic!("No changes made"); + } + } + operation => panic!("Unrecognized operation: {operation}"), + } } else { tracing::debug!("Not making any edits"); } From 43bef44e952810ebae9acef6f58323ea1934a6da Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Tue, 6 May 2025 12:21:13 -0400 Subject: [PATCH 06/15] add TruncateForTable caching strategy --- Makefile | 4 +- src/core.rs | 48 +++++++++++++---------- src/sql.rs | 109 +++++++++++++++++++--------------------------------- src/test.rs | 11 +++--- 4 files changed, 76 insertions(+), 96 deletions(-) diff --git a/Makefile b/Makefile index 0dbc28b..4e13b0c 100644 --- a/Makefile +++ b/Makefile @@ -168,13 +168,13 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy max_change + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate_all target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 0 35 --force --caching-strategy none .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy max_change + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate_all target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 0 60 --force --caching-strategy none diff --git a/src/core.rs b/src/core.rs index 2f9b094..1a4b6e1 100644 --- a/src/core.rs +++ b/src/core.rs @@ -91,6 +91,7 @@ pub struct Relatable { // pub minijinja: Environment<'static>, pub default_limit: usize, pub max_limit: usize, + pub strategy: CachingStrategy, } impl Relatable { @@ -131,6 +132,7 @@ impl Relatable { // minijinja: env, default_limit: DEFAULT_LIMIT, max_limit: MAX_LIMIT, + strategy: CachingStrategy::None, }) } @@ -509,25 +511,19 @@ impl Relatable { self.connection.query(&statement, Some(¶ms)).await } - /// Get the number of rows returned by this [Select] + /// Get the number of rows returned by this [Select] using the given caching strategy. pub async fn count(&self, select: &Select) -> Result { tracing::trace!("Relatable::count({select:?})"); - self.count_with_strategy(select, CachingStrategy::NoCache) - .await - } - - /// Get the number of rows returned by this [Select] using the given caching strategy. - pub async fn count_with_strategy( - &self, - select: &Select, - strategy: CachingStrategy, - ) -> Result { - tracing::trace!("Relatable::count_with_strategy({select:?}, {strategy:?})"); let (statement, params) = select.to_sql_count(&self.connection.kind())?; let params = json!(params); let json_rows = self .connection - .cache(&statement, Some(¶ms), strategy) + .cache( + &statement, + Some(¶ms), + &select.table_name, + &self.strategy, + ) .await?; match json_rows.get(0) { Some(json_row) => json_row.get_unsigned("count"), @@ -1059,13 +1055,25 @@ impl Relatable { }; } - tracing::info!("Deleting entries earlier than change_id {change_id} from cache"); - let sql = format!( - r#"DELETE FROM "cache" WHERE "change_id" < {}"#, - SqlParam::new(&tx.kind()).next() - ); - let params = json!([change_id]); - tx.query(&sql, Some(¶ms))?; + match self.strategy { + CachingStrategy::None | CachingStrategy::Naive => (), + CachingStrategy::TruncateAll => { + tracing::info!("Truncating cache"); + let sql = r#"DELETE FROM "cache""#; + tx.query(&sql, None)?; + } + CachingStrategy::TruncateForTable => { + tracing::info!("Deleting entries for table '{table}' from cache"); + let sql = format!( + r#"DELETE FROM "cache" WHERE "table" = {}"#, + SqlParam::new(&tx.kind()).next(), + ); + let params = json!([table]); + tx.query(&sql, Some(¶ms))?; + } + CachingStrategy::Metadata => todo!(), + CachingStrategy::Trigger => todo!(), + }; Ok(()) } diff --git a/src/sql.rs b/src/sql.rs index f1ac891..2683c25 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -57,10 +57,10 @@ pub static MAX_PARAMS_POSTGRES: usize = 65535; // performance of various caching strategies. #[derive(Clone, Copy, Debug, PartialEq, Eq)] pub enum CachingStrategy { - NoCache, + None, Naive, - Truncate, - MaxChange, + TruncateAll, + TruncateForTable, Metadata, Trigger, } @@ -71,10 +71,10 @@ impl FromStr for CachingStrategy { fn from_str(strategy: &str) -> Result { tracing::trace!("CachingStrategy::from_str({strategy:?})"); match strategy.to_lowercase().as_str() { - "none" => Ok(Self::NoCache), + "none" => Ok(Self::None), "naive" => Ok(Self::Naive), - "truncate" => Ok(Self::Truncate), - "max_change" => Ok(Self::MaxChange), + "truncate_all" => Ok(Self::TruncateAll), + "truncate" => Ok(Self::TruncateForTable), "metadata" => Ok(Self::Metadata), "trigger" => Ok(Self::Trigger), _ => { @@ -337,18 +337,28 @@ impl DbConnection { &self, sql: &str, params: Option<&JsonValue>, - strategy: CachingStrategy, + table: &str, + strategy: &CachingStrategy, ) -> Result> { tracing::trace!("cache({sql}, {params:?}, {strategy:?})"); match strategy { - CachingStrategy::NoCache => self.query(sql, params).await, - CachingStrategy::Naive | CachingStrategy::Truncate => { - let sql2 = format!( - r#"SELECT "value" FROM "cache" WHERE "key" = {param} LIMIT 1"#, - param = SqlParam::new(&self.kind()).next() - ); - let params2 = json!([sql]); - match self.query_one(&sql2, Some(¶ms2)).await? { + CachingStrategy::None => self.query(sql, params).await, + CachingStrategy::Naive + | CachingStrategy::TruncateAll + | CachingStrategy::TruncateForTable => { + let query_cache_sql = { + let mut sql_param = SqlParam::new(&self.kind()); + format!( + r#"SELECT "value" FROM "cache" WHERE "table" = {} AND "key" = {} LIMIT 1"#, + sql_param.next(), + sql_param.next() + ) + }; + let query_cache_params = json!([table, sql]); + match self + .query_one(&query_cache_sql, Some(&query_cache_params)) + .await? + { Some(json_row) => { tracing::debug!("Cache hit"); let value = json_row.get_string("value")?; @@ -358,59 +368,17 @@ impl DbConnection { None => { tracing::info!("Cache miss"); let json_rows = self.query(sql, params).await?; - let mut param = SqlParam::new(&self.kind()); - let sql3 = format!( - r#"INSERT INTO "cache" VALUES ({param1}, {param2}, NULL)"#, - param1 = param.next(), - param2 = param.next() - ); - let params3 = json!([sql, json_rows]); - self.query(&sql3, Some(¶ms3)).await?; - Ok(json_rows) - } - } - } - CachingStrategy::MaxChange => { - let sql2 = format!( - r#"SELECT "value" FROM "cache" WHERE "key" = {param} LIMIT 1"#, - param = SqlParam::new(&self.kind()).next() - ); - let params2 = json!([sql]); - match self.query_one(&sql2, Some(¶ms2)).await? { - Some(json_row) => { - tracing::debug!("Cache hit"); - let value = json_row.get_string("value")?; - let json_rows: Vec = serde_json::from_str(&value)?; - Ok(json_rows) - } - None => { - // Get the last change_id - let change_id = match self - .query_value( - r#"SELECT MAX("change_id") AS "change_id" FROM "change""#, - None, - ) - .await? - { - None => json!(0), - Some(change_id) if change_id.to_string().to_lowercase() == "null" => { - json!(0) - } - Some(change_id) => change_id, - }; - tracing::info!("Cache miss. Adding to cache with change_id: {change_id}"); - - let json_rows = self.query(sql, params).await?; - let mut param = SqlParam::new(&self.kind()); - let sql3 = format!( - r#"INSERT INTO "cache" ("key", "value", "change_id") - VALUES ({param1}, {param2}, {param3})"#, - param1 = param.next(), - param2 = param.next(), - param3 = param.next(), + let mut sql_param = SqlParam::new(&self.kind()); + let update_cache_sql = format!( + r#"INSERT INTO "cache" ("table", "key", "value") + VALUES ({}, {}, {})"#, + sql_param.next(), + sql_param.next(), + sql_param.next(), ); - let params3 = json!([sql, json_rows, change_id]); - self.query(&sql3, Some(¶ms3)).await?; + let update_cache_params = json!([table, sql, json_rows]); + self.query(&update_cache_sql, Some(&update_cache_params)) + .await?; Ok(json_rows) } } @@ -987,10 +955,13 @@ pub fn generate_cache_table_ddl(force: bool, db_kind: &DbKind) -> Vec { } ddl.push(format!( + // TODO: Generalize the "table" field to support an array of table names (for join + // purposes) r#"CREATE TABLE "cache" ( - "key" TEXT PRIMARY KEY, + "table" TEXT, + "key" TEXT, "value" TEXT, - "change_id" INTEGER + PRIMARY KEY ("table", "key") )"# )); ddl diff --git a/src/test.rs b/src/test.rs index 89e8c9b..156cf09 100644 --- a/src/test.rs +++ b/src/test.rs @@ -265,8 +265,6 @@ async fn generate_operation_sequence( ); } -/// TODO: Add a docstring and then move this to web.rs - #[async_std::main] async fn main() { let cli = Cli::parse(); @@ -301,10 +299,11 @@ async fn main() { force, } => { tracing::info!("Building demonstration database with {size} rows ..."); - let rltbl = Relatable::build_demo(Some(&cli.database), force, *size) + let mut rltbl = Relatable::build_demo(Some(&cli.database), force, *size) .await .unwrap(); tracing::info!("Demonstration database built and loaded."); + rltbl.strategy = CachingStrategy::from_str(&caching_strategy.to_lowercase()).unwrap(); fn random_op<'a>() -> &'a str { match random_between(0, 3, &mut -1) { @@ -315,15 +314,17 @@ async fn main() { } } + // TODO: Need to query more than one table to test the performance of + // CachingStrategy::TruncateForTable + tracing::info!("Counting the number of rows in table {table} ..."); let now = Instant::now(); let select = Select::from(table); - let strategy = CachingStrategy::from_str(&caching_strategy.to_lowercase()).unwrap(); let mut i = 0; let mut count = 0; let mut elapsed; while i < *fetches { - count = rltbl.count_with_strategy(&select, strategy).await.unwrap(); + count = rltbl.count(&select).await.unwrap(); elapsed = now.elapsed().as_secs(); if elapsed > *fail_after_secs { panic!("Taking longer than {fail_after_secs}s. Timing out."); From f6c0e226ac44c7f76e1233eddba841c87fc54764 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Wed, 7 May 2025 11:34:03 -0400 Subject: [PATCH 07/15] implement Metatable caching strategy --- src/core.rs | 31 +++------ src/sql.rs | 196 +++++++++++++++++++++++++++++++++++++++++----------- src/test.rs | 2 +- 3 files changed, 169 insertions(+), 60 deletions(-) diff --git a/src/core.rs b/src/core.rs index 1a4b6e1..db96039 100644 --- a/src/core.rs +++ b/src/core.rs @@ -8,8 +8,8 @@ use rltbl::{ git, select::{Select, SelectField}, sql::{ - self, CachingStrategy, DbActiveConnection, DbConnection, DbKind, DbTransaction, JsonRow, - SqlParam, VecInto as _, + self, delete_from_cache, CachingStrategy, DbActiveConnection, DbConnection, DbKind, + DbTransaction, JsonRow, SqlParam, VecInto as _, }, table::{Column, Message, Row, Table}, }; @@ -74,7 +74,7 @@ pub enum RelatableError { UserError(String), } -impl std::fmt::Display for RelatableError { +impl Display for RelatableError { fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { write!(f, "{:?}", self) } @@ -132,7 +132,7 @@ impl Relatable { // minijinja: env, default_limit: DEFAULT_LIMIT, max_limit: MAX_LIMIT, - strategy: CachingStrategy::None, + strategy: CachingStrategy::Metatable, }) } @@ -1055,24 +1055,15 @@ impl Relatable { }; } + // Possibly delete dirty entries from the cache in accordance with our caching strategy: match self.strategy { - CachingStrategy::None | CachingStrategy::Naive => (), - CachingStrategy::TruncateAll => { - tracing::info!("Truncating cache"); - let sql = r#"DELETE FROM "cache""#; - tx.query(&sql, None)?; + // Trigger has the same behaviour as None here, since the database will be triggering + // this step automatically everytime the table is edited in that case. + CachingStrategy::None | CachingStrategy::Naive | CachingStrategy::Trigger => (), + CachingStrategy::TruncateAll => delete_from_cache(tx, None)?, + CachingStrategy::TruncateForTable | CachingStrategy::Metatable => { + delete_from_cache(tx, Some(&table))? } - CachingStrategy::TruncateForTable => { - tracing::info!("Deleting entries for table '{table}' from cache"); - let sql = format!( - r#"DELETE FROM "cache" WHERE "table" = {}"#, - SqlParam::new(&tx.kind()).next(), - ); - let params = json!([table]); - tx.query(&sql, Some(¶ms))?; - } - CachingStrategy::Metadata => todo!(), - CachingStrategy::Trigger => todo!(), }; Ok(()) diff --git a/src/sql.rs b/src/sql.rs index 2683c25..622128d 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -18,7 +18,7 @@ use regex::Regex; use serde::{Deserialize, Serialize}; use serde_json::{json, Map as JsonMap, Value as JsonValue}; -use std::str::FromStr; +use std::{fmt::Display, str::FromStr}; #[cfg(feature = "rusqlite")] use rusqlite; @@ -61,7 +61,7 @@ pub enum CachingStrategy { Naive, TruncateAll, TruncateForTable, - Metadata, + Metatable, Trigger, } @@ -75,7 +75,7 @@ impl FromStr for CachingStrategy { "naive" => Ok(Self::Naive), "truncate_all" => Ok(Self::TruncateAll), "truncate" => Ok(Self::TruncateForTable), - "metadata" => Ok(Self::Metadata), + "metadata" => Ok(Self::Metatable), "trigger" => Ok(Self::Trigger), _ => { return Err(RelatableError::InputError(format!( @@ -87,6 +87,19 @@ impl FromStr for CachingStrategy { } } +impl Display for CachingStrategy { + fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { + match self { + CachingStrategy::None => write!(f, "none"), + CachingStrategy::Naive => write!(f, "naive"), + CachingStrategy::TruncateAll => write!(f, "truncate_all"), + CachingStrategy::TruncateForTable => write!(f, "truncate"), + CachingStrategy::Metatable => write!(f, "metadata"), + CachingStrategy::Trigger => write!(f, "trigger"), + } + } +} + /// Represents the kind of database being managed #[derive(Clone, Copy, Debug, PartialEq, Eq)] pub enum DbKind { @@ -341,50 +354,114 @@ impl DbConnection { strategy: &CachingStrategy, ) -> Result> { tracing::trace!("cache({sql}, {params:?}, {strategy:?})"); + + async fn query_cache( + conn: &DbConnection, + table: &str, + sql: &str, + params: Option<&JsonValue>, + ) -> Result> { + let query_cache_sql = { + let mut sql_param = SqlParam::new(&conn.kind()); + format!( + r#"SELECT "value" FROM "cache" WHERE "table" = {} AND "key" = {} LIMIT 1"#, + sql_param.next(), + sql_param.next() + ) + }; + let query_cache_params = json!([table, sql]); + match conn + .query_one(&query_cache_sql, Some(&query_cache_params)) + .await? + { + Some(json_row) => { + tracing::debug!("Cache hit"); + let value = json_row.get_string("value")?; + let json_rows: Vec = serde_json::from_str(&value)?; + Ok(json_rows) + } + None => { + tracing::info!("Cache miss"); + let json_rows = conn.query(sql, params).await?; + let mut sql_param = SqlParam::new(&conn.kind()); + let update_cache_sql = format!( + r#"INSERT INTO "cache" ("table", "key", "value") + VALUES ({}, {}, {})"#, + sql_param.next(), + sql_param.next(), + sql_param.next(), + ); + let update_cache_params = json!([table, sql, json_rows]); + conn.query(&update_cache_sql, Some(&update_cache_params)) + .await?; + Ok(json_rows) + } + } + } + match strategy { CachingStrategy::None => self.query(sql, params).await, CachingStrategy::Naive | CachingStrategy::TruncateAll - | CachingStrategy::TruncateForTable => { - let query_cache_sql = { - let mut sql_param = SqlParam::new(&self.kind()); - format!( - r#"SELECT "value" FROM "cache" WHERE "table" = {} AND "key" = {} LIMIT 1"#, - sql_param.next(), - sql_param.next() - ) - }; - let query_cache_params = json!([table, sql]); - match self - .query_one(&query_cache_sql, Some(&query_cache_params)) - .await? - { - Some(json_row) => { - tracing::debug!("Cache hit"); - let value = json_row.get_string("value")?; - let json_rows: Vec = serde_json::from_str(&value)?; - Ok(json_rows) + | CachingStrategy::TruncateForTable + | CachingStrategy::Trigger => query_cache(self, table, sql, params).await, + CachingStrategy::Metatable => { + // Unfortunately, the meta table strategy has a big loophole, since commit times + // are only recorded per row and not per table. This is fine unless the last + // operation was a delete. In that case, since the row is gone, you won't get the + // correct last modified time. So we need to get rid of this strategy. But just + // leave it for now. + match self.kind() { + DbKind::Postgres => { + let sql = format!( + r#"SELECT + last_known_modified::TEXT AS "last_modified_by_rltbl", + last_actual_modified::TEXT AS "last_modified", + last_actual_modified > last_known_modified AS "dirty" + FROM ( + SELECT date_trunc( + 'seconds', + pg_xact_commit_timestamp(t.xmin) AT TIME ZONE 'UTC' + ) AS modified_ts + FROM "{table}" t + ORDER BY modified_ts + DESC NULLS LAST limit 1 + ) as last_actual_modified, + ( + SELECT date_trunc('seconds', "last_modified"::TIMESTAMP) + FROM "table" + WHERE "table" = {} + ) as "last_known_modified""#, + SqlParam::new(&self.kind()).next() + ); + let params = json!([table]); + let row = self.query_one(&sql, Some(¶ms)).await?.unwrap(); + tracing::info!("ROW: {row:?}"); + let last_rltbl_mod = row.get_string("last_modified_by_rltbl")?; + match row.get_value("dirty")? { + JsonValue::Bool(is_dirty) => { + if last_rltbl_mod == "()" || is_dirty { + tracing::info!("Cache dirty. Cleaning it ..."); + let mut conn = self.reconnect()?; + let mut tx = self.begin(&mut conn).await?; + delete_from_cache(&mut tx, Some(table))?; + tx.commit()?; + } else { + tracing::info!("Cache is clean"); + } + } + _ => panic!("Unexpected Value type"), + }; } - None => { - tracing::info!("Cache miss"); - let json_rows = self.query(sql, params).await?; - let mut sql_param = SqlParam::new(&self.kind()); - let update_cache_sql = format!( - r#"INSERT INTO "cache" ("table", "key", "value") - VALUES ({}, {}, {})"#, - sql_param.next(), - sql_param.next(), - sql_param.next(), + DbKind::Sqlite => { + tracing::warn!( + "Metatable caching strategy not supported for SQLite. \ + Skipping this step." ); - let update_cache_params = json!([table, sql, json_rows]); - self.query(&update_cache_sql, Some(&update_cache_params)) - .await?; - Ok(json_rows) } - } + }; + query_cache(self, table, sql, params).await } - CachingStrategy::Metadata => todo!(), - CachingStrategy::Trigger => todo!(), } } } @@ -942,6 +1019,14 @@ pub fn generate_table_table_ddl(force: bool, db_kind: &DbKind) -> Vec { ..Default::default() }, ); + table.columns.insert( + "last_modified".into(), + Column { + table: "table".into(), + name: "last_modified".into(), + ..Default::default() + }, + ); generate_table_ddl(&table, force, db_kind).unwrap() } @@ -1125,6 +1210,39 @@ pub fn generate_meta_tables_ddl(force: bool, db_kind: &DbKind) -> Vec { ddl } +// TODO: Make sure this is the right spot to place this function. +pub fn delete_from_cache(tx: &mut DbTransaction<'_>, table: Option<&str>) -> Result<()> { + let mut sql = r#"DELETE FROM "cache""#.to_string(); + if let Some(table) = table { + tracing::info!("Deleting entries for table '{table}' from cache"); + sql.push_str(&format!( + r#" WHERE "table" = {}"#, + SqlParam::new(&tx.kind()).next() + )); + let params = json!([table]); + tx.query(&sql, Some(¶ms))?; + } else { + tracing::info!("Truncating cache"); + tx.query(&sql, None)?; + } + + let mut sql = r#"UPDATE "table" SET "last_modified" = CURRENT_TIMESTAMP::TEXT"#.to_string(); + if let Some(table) = table { + tracing::info!("Updating last_modified time for table '{table}' in table table"); + sql.push_str(&format!( + r#" WHERE "table" = {}"#, + SqlParam::new(&tx.kind()).next() + )); + let params = json!([table]); + tx.query(&sql, Some(¶ms))?; + } else { + tracing::info!("Updating last_modified times in table table"); + tx.query(&sql, None)?; + } + + Ok(()) +} + /////////////////////////////////////////////////////////////////////////////// // Utilities for dealing with JSON representations of rows. The reason these // are located here instead of in core.rs is because the implementation of diff --git a/src/test.rs b/src/test.rs index 156cf09..19711eb 100644 --- a/src/test.rs +++ b/src/test.rs @@ -386,7 +386,7 @@ async fn main() { tracing::debug!("Not making any edits"); } - // A small sleep to prevent over-taxing the CPU + // A small sleep to prevent over-taxing the CPU: thread::sleep(Duration::from_millis(2)); i += 1; } From 7ce6f06cfa892cbc34128e17341bf88c924b1ee2 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Thu, 8 May 2025 11:35:49 -0400 Subject: [PATCH 08/15] make tests pass again --- Makefile | 12 ++++++------ src/sql.rs | 14 +++++++++----- 2 files changed, 15 insertions(+), 11 deletions(-) diff --git a/Makefile b/Makefile index 4e13b0c..c95305f 100644 --- a/Makefile +++ b/Makefile @@ -168,15 +168,15 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate_all - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 0 35 --force --caching-strategy none + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate_all + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate + # target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 0 35 --force --caching-strategy none .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate_all - target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 100 25 --force --caching-strategy truncate - target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 0 60 --force --caching-strategy none + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate_all + target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate + # target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 0 60 --force --caching-strategy none .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug diff --git a/src/sql.rs b/src/sql.rs index 622128d..94009c8 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -454,10 +454,10 @@ impl DbConnection { }; } DbKind::Sqlite => { - tracing::warn!( - "Metatable caching strategy not supported for SQLite. \ - Skipping this step." - ); + //tracing::warn!( + // "Metatable caching strategy not supported for SQLite. \ + // Skipping this step." + //); } }; query_cache(self, table, sql, params).await @@ -1226,7 +1226,11 @@ pub fn delete_from_cache(tx: &mut DbTransaction<'_>, table: Option<&str>) -> Res tx.query(&sql, None)?; } - let mut sql = r#"UPDATE "table" SET "last_modified" = CURRENT_TIMESTAMP::TEXT"#.to_string(); + let cast = match tx.kind() { + DbKind::Sqlite => "", + DbKind::Postgres => "::TEXT", + }; + let mut sql = format!(r#"UPDATE "table" SET "last_modified" = CURRENT_TIMESTAMP{cast}"#); if let Some(table) = table { tracing::info!("Updating last_modified time for table '{table}' in table table"); sql.push_str(&format!( From 713cf2ee121e91de33c19984e65c64136ad1a298 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Fri, 9 May 2025 11:17:26 -0400 Subject: [PATCH 09/15] add caching triggers for sqlite --- Makefile | 8 +- src/core.rs | 107 +++++++++++------------ src/sql.rs | 241 +++++++++++++++++++++++++++------------------------- 3 files changed, 180 insertions(+), 176 deletions(-) diff --git a/Makefile b/Makefile index c95305f..61cd050 100644 --- a/Makefile +++ b/Makefile @@ -168,14 +168,14 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate_all - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy trigger +# target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate # target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 0 35 --force --caching-strategy none .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate_all - target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate + target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy trigger +# target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate # target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 0 60 --force --caching-strategy none .PHONY: test_perf_sqlite diff --git a/src/core.rs b/src/core.rs index db96039..7e31c9a 100644 --- a/src/core.rs +++ b/src/core.rs @@ -8,8 +8,8 @@ use rltbl::{ git, select::{Select, SelectField}, sql::{ - self, delete_from_cache, CachingStrategy, DbActiveConnection, DbConnection, DbKind, - DbTransaction, JsonRow, SqlParam, VecInto as _, + self, CachingStrategy, DbActiveConnection, DbConnection, DbKind, DbTransaction, JsonRow, + SqlParam, VecInto as _, }, table::{Column, Message, Row, Table}, }; @@ -132,7 +132,7 @@ impl Relatable { // minijinja: env, default_limit: DEFAULT_LIMIT, max_limit: MAX_LIMIT, - strategy: CachingStrategy::Metatable, + strategy: CachingStrategy::Trigger, }) } @@ -249,58 +249,12 @@ impl Relatable { ); rltbl.connection.query(&sql, None).await?; - // TODO (maybe) Don't explicitly execute any create table/trigger etc. statements here. - // Save the generated values to a TSV file first in a demo/ directory instead and then load - // it. - if rltbl.connection.kind() == DbKind::Postgres { - // This is required, because in PostgreSQL, assigning SERIAL PRIMARY KEY to a column is - // equivalent to: - // CREATE SEQUENCE table_name_id_seq; - // CREATE TABLE table_name ( - // id integer NOT NULL DEFAULT nextval('table_name_id_seq') - // ); - // ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id; - // This means that such a column is only ever auto-incremented when it is explicitly - // left out of an INSERT statement. To replicate SQLite's more sane behaviour, we define - // the following trigger to *always* update the last value of the sequence to the - // currently inserted row number. A similar trigger is also defined generically for - // postgresql tables in [rltbl::core]. - let sql = format!( - r#"CREATE OR REPLACE FUNCTION "update_order_and_nextval_penguin"() - RETURNS TRIGGER - LANGUAGE PLPGSQL - AS - $$ - BEGIN - IF NEW._id > (SELECT MAX(last_value) FROM "penguin__id_seq") THEN - PERFORM setval('penguin__id_seq', NEW._id); - END IF; - RETURN NEW; - END; - $$"#, - ); - rltbl.connection.query(&sql, None).await?; - - let sql = format!( - r#"CREATE TRIGGER "penguin_order" - AFTER INSERT ON "penguin" - FOR EACH ROW - EXECUTE FUNCTION "update_order_and_nextval_penguin"()"#, - ); - rltbl.connection.query(&sql, None).await?; - } else { - let sql = format!( - r#"CREATE TRIGGER "update_order_penguin" - AFTER INSERT ON "penguin" - WHEN NEW._order IS NULL - BEGIN - UPDATE "penguin" SET _order = ({NEW_ORDER_MULTIPLIER} * NEW._id) - WHERE _id = NEW._id; - END"#, - ); + let mut ddl = vec![]; + sql::add_metacolumn_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); + sql::add_caching_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); + for sql in ddl { rltbl.connection.query(&sql, None).await?; } - // Populate the penguin table with random data. let islands = vec!["Biscoe", "Dream", "Torgersen"]; let mut rng = StdRng::seed_from_u64(0); @@ -531,6 +485,43 @@ impl Relatable { } } + /// TODO: Add docstring + pub fn clean_cache(tx: &mut DbTransaction<'_>, table: Option<&str>) -> Result<()> { + let mut sql = r#"DELETE FROM "cache""#.to_string(); + if let Some(table) = table { + tracing::info!("Deleting entries for table '{table}' from cache"); + sql.push_str(&format!( + r#" WHERE "table" = {}"#, + SqlParam::new(&tx.kind()).next() + )); + let params = json!([table]); + tx.query(&sql, Some(¶ms))?; + } else { + tracing::info!("Truncating cache"); + tx.query(&sql, None)?; + } + + let cast = match tx.kind() { + DbKind::Sqlite => "", + DbKind::Postgres => "::TEXT", + }; + let mut sql = format!(r#"UPDATE "table" SET "last_modified" = CURRENT_TIMESTAMP{cast}"#); + if let Some(table) = table { + tracing::info!("Updating last_modified time for table '{table}' in table table"); + sql.push_str(&format!( + r#" WHERE "table" = {}"#, + SqlParam::new(&tx.kind()).next() + )); + let params = json!([table]); + tx.query(&sql, Some(¶ms))?; + } else { + tracing::info!("Updating last_modified times in table table"); + tx.query(&sql, None)?; + } + + Ok(()) + } + /// Loads the given table from the given path. When `force` is set to true, deletes any /// existing table of the same name in the database first. Note that this function may panic. pub async fn load_table(&self, table_name: &str, path: &str, force: bool) { @@ -606,7 +597,9 @@ impl Relatable { }; // Generate the SQL statements needed to create the table and execute them: - for sql in sql::generate_table_ddl(&table, force, &db_kind).expect("Error getting DDL") { + for sql in sql::generate_table_ddl(&table, force, &db_kind, &self.strategy) + .expect("Error getting DDL") + { self.connection .query(&sql, None) .await @@ -1058,11 +1051,11 @@ impl Relatable { // Possibly delete dirty entries from the cache in accordance with our caching strategy: match self.strategy { // Trigger has the same behaviour as None here, since the database will be triggering - // this step automatically everytime the table is edited in that case. + // this step automatically every time the table is edited in that case. CachingStrategy::None | CachingStrategy::Naive | CachingStrategy::Trigger => (), - CachingStrategy::TruncateAll => delete_from_cache(tx, None)?, + CachingStrategy::TruncateAll => Self::clean_cache(tx, None)?, CachingStrategy::TruncateForTable | CachingStrategy::Metatable => { - delete_from_cache(tx, Some(&table))? + Self::clean_cache(tx, Some(&table))? } }; diff --git a/src/sql.rs b/src/sql.rs index 94009c8..82d8418 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -7,7 +7,7 @@ use crate as rltbl; use rltbl::{ - core::{RelatableError, NEW_ORDER_MULTIPLIER}, + core::{Relatable, RelatableError, NEW_ORDER_MULTIPLIER}, table::{Column, Table}, }; @@ -444,7 +444,7 @@ impl DbConnection { tracing::info!("Cache dirty. Cleaning it ..."); let mut conn = self.reconnect()?; let mut tx = self.begin(&mut conn).await?; - delete_from_cache(&mut tx, Some(table))?; + Relatable::clean_cache(&mut tx, Some(table))?; tx.commit()?; } else { tracing::info!("Cache is clean"); @@ -781,7 +781,12 @@ pub fn extract_value(rows: &Vec) -> Option { // Functions for generating DDL //////////////// -pub fn generate_table_ddl(table: &Table, force: bool, db_kind: &DbKind) -> Result> { +pub fn generate_table_ddl( + table: &Table, + force: bool, + db_kind: &DbKind, + caching_strategy: &CachingStrategy, +) -> Result> { tracing::trace!("generate_table_ddl({table:?}, {force}, {db_kind:?})"); if table.has_meta { for (cname, col) in table.columns.iter() { @@ -848,57 +853,105 @@ pub fn generate_table_ddl(table: &Table, force: bool, db_kind: &DbKind) -> Resul sql.push_str(&format!(" {})", column_clauses.join(", "))); ddl.push(sql); + // Add triggers for metacolumns if they are present: if table.has_meta { - let update_stmt = format!( - r#"UPDATE "{table}" SET _order = ({NEW_ORDER_MULTIPLIER} * NEW._id) - WHERE _id = NEW._id;"#, - table = table.name, - ); - match db_kind { - DbKind::Sqlite => { - ddl.push(format!( - r#"CREATE TRIGGER "{table}_order" - AFTER INSERT ON "{table}" - WHEN NEW._order IS NULL - BEGIN - {update_stmt} - END"#, - table = table.name, - )); - } - DbKind::Postgres => { - ddl.push(format!( - r#"CREATE OR REPLACE FUNCTION "update_order_and_nextval_{table}"() - RETURNS TRIGGER - LANGUAGE PLPGSQL - AS - $$ - BEGIN - IF NEW._order IS NOT DISTINCT FROM NULL THEN - {update_stmt} - END IF; - IF NEW._id > (SELECT MAX(last_value) FROM "{table}__id_seq") THEN - PERFORM setval('{table}__id_seq', NEW._id); - END IF; - RETURN NEW; - END; - $$"#, - table = table.name, - )); - ddl.push(format!( - r#"CREATE TRIGGER "{table}_order" - AFTER INSERT ON "{table}" - FOR EACH ROW - EXECUTE FUNCTION "update_order_and_nextval_{table}"()"#, - table = table.name, - )); - } - }; + add_metacolumn_trigger_ddl(&mut ddl, &table.name, db_kind); + } + + // Add triggers for updating the "cache" and "table" tables whenever this table is + // changed, if the Trigger caching strategy has been specified: + if let CachingStrategy::Trigger = caching_strategy { + add_caching_trigger_ddl(&mut ddl, &table.name, db_kind); } Ok(ddl) } +pub fn add_metacolumn_trigger_ddl(ddl: &mut Vec, table: &str, db_kind: &DbKind) { + let update_stmt = format!( + r#"UPDATE "{table}" SET _order = ({NEW_ORDER_MULTIPLIER} * NEW._id) + WHERE _id = NEW._id;"# + ); + match db_kind { + DbKind::Sqlite => { + ddl.push(format!( + r#"CREATE TRIGGER "{table}_order" + AFTER INSERT ON "{table}" + WHEN NEW._order IS NULL + BEGIN + {update_stmt} + END"# + )); + } + DbKind::Postgres => { + // This is required, because in PostgreSQL, assigning SERIAL PRIMARY KEY to a column is + // equivalent to: + // CREATE SEQUENCE table_name_id_seq; + // CREATE TABLE table_name ( + // id integer NOT NULL DEFAULT nextval('table_name_id_seq') + // ); + // ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id; + // This means that such a column is only ever auto-incremented when it is explicitly + // left out of an INSERT statement. To replicate SQLite's more sane behaviour, we define + // the following trigger to *always* update the last value of the sequence to the + // currently inserted row number. A similar trigger is also defined generically for + // postgresql tables in [rltbl::core]. + ddl.push(format!( + r#"CREATE OR REPLACE FUNCTION "update_order_and_nextval_{table}"() + RETURNS TRIGGER + LANGUAGE PLPGSQL + AS + $$ + BEGIN + IF NEW._order IS NOT DISTINCT FROM NULL THEN + {update_stmt} + END IF; + IF NEW._id > (SELECT MAX(last_value) FROM "{table}__id_seq") THEN + PERFORM setval('{table}__id_seq', NEW._id); + END IF; + RETURN NEW; + END; + $$"# + )); + ddl.push(format!( + r#"CREATE TRIGGER "{table}_order" + AFTER INSERT ON "{table}" + FOR EACH ROW + EXECUTE FUNCTION "update_order_and_nextval_{table}"()"# + )); + } + }; +} + +pub fn add_caching_trigger_ddl(ddl: &mut Vec, table: &str, db_kind: &DbKind) { + match db_kind { + DbKind::Sqlite => { + ddl.push(format!( + r#"CREATE TRIGGER "{table}_cache_after_insert" + AFTER INSERT ON "{table}" + BEGIN + DELETE FROM "cache" WHERE "table" = '{table}'; + END"# + )); + ddl.push(format!( + r#"CREATE TRIGGER "{table}_cache_after_update" + AFTER UPDATE ON "{table}" + BEGIN + DELETE FROM "cache" WHERE "table" = '{table}'; + END"# + )); + ddl.push(format!( + r#"CREATE TRIGGER "{table}_cache_after_delete" + AFTER DELETE ON "{table}" + BEGIN + DELETE FROM "cache" WHERE "table" = '{table}'; + END"# + )); + } + DbKind::Postgres => todo!(), + }; +} + pub fn generate_view_ddl( table_name: &str, view_name: &str, @@ -997,37 +1050,32 @@ pub fn generate_view_ddl( pub fn generate_table_table_ddl(force: bool, db_kind: &DbKind) -> Vec { tracing::trace!("generate_table_table_ddl({force}, {db_kind:?})"); - let mut table = Table { - name: "table".to_string(), - ..Default::default() + let mut ddl = vec![]; + if force { + if let DbKind::Postgres = db_kind { + ddl.push(format!(r#"DROP TABLE IF EXISTS "table" CASCADE"#)); + } + } + let pkey_clause = match db_kind { + DbKind::Sqlite => "INTEGER PRIMARY KEY AUTOINCREMENT", + DbKind::Postgres => "SERIAL PRIMARY KEY", }; - table.columns.insert( - "table".into(), - Column { - table: "table".into(), - name: "table".into(), - unique: true, - ..Default::default() - }, - ); - table.columns.insert( - "path".into(), - Column { - table: "table".into(), - name: "path".into(), - unique: true, - ..Default::default() - }, - ); - table.columns.insert( - "last_modified".into(), - Column { - table: "table".into(), - name: "last_modified".into(), - ..Default::default() - }, - ); - generate_table_ddl(&table, force, db_kind).unwrap() + + // TODO: Since we will not be using the Metatable caching strategy, we can remove the + // last_modified field. + ddl.push(format!( + r#"CREATE TABLE "table" ( + "_id" {pkey_clause}, + "_order" INTEGER UNIQUE, + "table" TEXT UNIQUE, + "path" TEXT UNIQUE, + "last_modified" TEXT + )"# + )); + + // Add metacolumn triggers before returning the DDL: + add_metacolumn_trigger_ddl(&mut ddl, "table", db_kind); + ddl } pub fn generate_cache_table_ddl(force: bool, db_kind: &DbKind) -> Vec { @@ -1210,43 +1258,6 @@ pub fn generate_meta_tables_ddl(force: bool, db_kind: &DbKind) -> Vec { ddl } -// TODO: Make sure this is the right spot to place this function. -pub fn delete_from_cache(tx: &mut DbTransaction<'_>, table: Option<&str>) -> Result<()> { - let mut sql = r#"DELETE FROM "cache""#.to_string(); - if let Some(table) = table { - tracing::info!("Deleting entries for table '{table}' from cache"); - sql.push_str(&format!( - r#" WHERE "table" = {}"#, - SqlParam::new(&tx.kind()).next() - )); - let params = json!([table]); - tx.query(&sql, Some(¶ms))?; - } else { - tracing::info!("Truncating cache"); - tx.query(&sql, None)?; - } - - let cast = match tx.kind() { - DbKind::Sqlite => "", - DbKind::Postgres => "::TEXT", - }; - let mut sql = format!(r#"UPDATE "table" SET "last_modified" = CURRENT_TIMESTAMP{cast}"#); - if let Some(table) = table { - tracing::info!("Updating last_modified time for table '{table}' in table table"); - sql.push_str(&format!( - r#" WHERE "table" = {}"#, - SqlParam::new(&tx.kind()).next() - )); - let params = json!([table]); - tx.query(&sql, Some(¶ms))?; - } else { - tracing::info!("Updating last_modified times in table table"); - tx.query(&sql, None)?; - } - - Ok(()) -} - /////////////////////////////////////////////////////////////////////////////// // Utilities for dealing with JSON representations of rows. The reason these // are located here instead of in core.rs is because the implementation of From 869bd3842a1b706b73886f2a7deccd6ed3743d33 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Fri, 9 May 2025 11:37:32 -0400 Subject: [PATCH 10/15] add caching triggers for postgresql --- src/sql.rs | 30 +++++++++++++++++++++++++++++- 1 file changed, 29 insertions(+), 1 deletion(-) diff --git a/src/sql.rs b/src/sql.rs index 82d8418..0794988 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -948,7 +948,35 @@ pub fn add_caching_trigger_ddl(ddl: &mut Vec, table: &str, db_kind: &DbK END"# )); } - DbKind::Postgres => todo!(), + DbKind::Postgres => { + ddl.push(format!( + r#"CREATE OR REPLACE FUNCTION "clean_cache_for_{table}"() + RETURNS TRIGGER + LANGUAGE PLPGSQL + AS + $$ + BEGIN + DELETE FROM "cache" WHERE "table" = '{table}'; + RETURN NEW; + END; + $$"# + )); + ddl.push(format!( + r#"CREATE TRIGGER "{table}_cache_after_insert" + AFTER INSERT ON "{table}" + EXECUTE FUNCTION "clean_cache_for_{table}"()"# + )); + ddl.push(format!( + r#"CREATE TRIGGER "{table}_cache_after_update" + AFTER UPDATE ON "{table}" + EXECUTE FUNCTION "clean_cache_for_{table}"()"# + )); + ddl.push(format!( + r#"CREATE TRIGGER "{table}_cache_after_delete" + AFTER DELETE ON "{table}" + EXECUTE FUNCTION "clean_cache_for_{table}"()"# + )); + } }; } From d856a654d3355c6743a16289d96d4950f9a8d6cb Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Fri, 9 May 2025 11:52:58 -0400 Subject: [PATCH 11/15] remove unneeded strategies --- src/cli.rs | 13 +++++++--- src/core.rs | 39 ++++++++++------------------ src/sql.rs | 73 +++-------------------------------------------------- src/test.rs | 4 +-- 4 files changed, 28 insertions(+), 101 deletions(-) diff --git a/src/cli.rs b/src/cli.rs index 9371108..948c0bc 100644 --- a/src/cli.rs +++ b/src/cli.rs @@ -7,7 +7,7 @@ use rltbl::{ core::{Change, ChangeAction, ChangeSet, Relatable}, select::{Format, Select}, sql, - sql::{JsonRow, SqlParam, VecInto}, + sql::{CachingStrategy, JsonRow, SqlParam, VecInto}, web::{serve, serve_cgi}, }; @@ -800,9 +800,14 @@ pub async fn save_all(cli: &Cli, save_dir: Option<&str>) { pub async fn build_demo(cli: &Cli, force: &bool, size: usize) { tracing::trace!("build_demo({cli:?}, {force}, {size})"); - Relatable::build_demo(cli.database.as_deref(), force, size) - .await - .expect("Error building demonstration database"); + Relatable::build_demo( + cli.database.as_deref(), + force, + size, + &CachingStrategy::TruncateForTable, + ) + .await + .expect("Error building demonstration database"); println!( "Created a demonstration database in '{}'", match &cli.database { diff --git a/src/core.rs b/src/core.rs index 7e31c9a..4621cea 100644 --- a/src/core.rs +++ b/src/core.rs @@ -187,8 +187,15 @@ impl Relatable { } /// Build a demonstration database - pub async fn build_demo(database: Option<&str>, force: &bool, size: usize) -> Result { - let rltbl = Relatable::init(force, database.as_deref()).await?; + pub async fn build_demo( + database: Option<&str>, + force: &bool, + size: usize, + caching_strategy: &CachingStrategy, + ) -> Result { + let mut rltbl = Relatable::init(force, database.as_deref()).await?; + // TODO: Add this as an argument to init() + rltbl.strategy = *caching_strategy; if *force { if let DbKind::Postgres = rltbl.connection.kind() { @@ -251,7 +258,9 @@ impl Relatable { let mut ddl = vec![]; sql::add_metacolumn_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); - sql::add_caching_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); + if let CachingStrategy::Trigger = caching_strategy { + sql::add_caching_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); + } for sql in ddl { rltbl.connection.query(&sql, None).await?; } @@ -501,24 +510,6 @@ impl Relatable { tx.query(&sql, None)?; } - let cast = match tx.kind() { - DbKind::Sqlite => "", - DbKind::Postgres => "::TEXT", - }; - let mut sql = format!(r#"UPDATE "table" SET "last_modified" = CURRENT_TIMESTAMP{cast}"#); - if let Some(table) = table { - tracing::info!("Updating last_modified time for table '{table}' in table table"); - sql.push_str(&format!( - r#" WHERE "table" = {}"#, - SqlParam::new(&tx.kind()).next() - )); - let params = json!([table]); - tx.query(&sql, Some(¶ms))?; - } else { - tracing::info!("Updating last_modified times in table table"); - tx.query(&sql, None)?; - } - Ok(()) } @@ -1052,11 +1043,9 @@ impl Relatable { match self.strategy { // Trigger has the same behaviour as None here, since the database will be triggering // this step automatically every time the table is edited in that case. - CachingStrategy::None | CachingStrategy::Naive | CachingStrategy::Trigger => (), + CachingStrategy::None | CachingStrategy::Trigger => (), CachingStrategy::TruncateAll => Self::clean_cache(tx, None)?, - CachingStrategy::TruncateForTable | CachingStrategy::Metatable => { - Self::clean_cache(tx, Some(&table))? - } + CachingStrategy::TruncateForTable => Self::clean_cache(tx, Some(&table))?, }; Ok(()) diff --git a/src/sql.rs b/src/sql.rs index 0794988..7d58b3b 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -7,7 +7,7 @@ use crate as rltbl; use rltbl::{ - core::{Relatable, RelatableError, NEW_ORDER_MULTIPLIER}, + core::{RelatableError, NEW_ORDER_MULTIPLIER}, table::{Column, Table}, }; @@ -58,10 +58,8 @@ pub static MAX_PARAMS_POSTGRES: usize = 65535; #[derive(Clone, Copy, Debug, PartialEq, Eq)] pub enum CachingStrategy { None, - Naive, TruncateAll, TruncateForTable, - Metatable, Trigger, } @@ -72,10 +70,8 @@ impl FromStr for CachingStrategy { tracing::trace!("CachingStrategy::from_str({strategy:?})"); match strategy.to_lowercase().as_str() { "none" => Ok(Self::None), - "naive" => Ok(Self::Naive), "truncate_all" => Ok(Self::TruncateAll), "truncate" => Ok(Self::TruncateForTable), - "metadata" => Ok(Self::Metatable), "trigger" => Ok(Self::Trigger), _ => { return Err(RelatableError::InputError(format!( @@ -91,10 +87,8 @@ impl Display for CachingStrategy { fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { match self { CachingStrategy::None => write!(f, "none"), - CachingStrategy::Naive => write!(f, "naive"), CachingStrategy::TruncateAll => write!(f, "truncate_all"), CachingStrategy::TruncateForTable => write!(f, "truncate"), - CachingStrategy::Metatable => write!(f, "metadata"), CachingStrategy::Trigger => write!(f, "trigger"), } } @@ -401,67 +395,9 @@ impl DbConnection { match strategy { CachingStrategy::None => self.query(sql, params).await, - CachingStrategy::Naive - | CachingStrategy::TruncateAll + CachingStrategy::TruncateAll | CachingStrategy::TruncateForTable | CachingStrategy::Trigger => query_cache(self, table, sql, params).await, - CachingStrategy::Metatable => { - // Unfortunately, the meta table strategy has a big loophole, since commit times - // are only recorded per row and not per table. This is fine unless the last - // operation was a delete. In that case, since the row is gone, you won't get the - // correct last modified time. So we need to get rid of this strategy. But just - // leave it for now. - match self.kind() { - DbKind::Postgres => { - let sql = format!( - r#"SELECT - last_known_modified::TEXT AS "last_modified_by_rltbl", - last_actual_modified::TEXT AS "last_modified", - last_actual_modified > last_known_modified AS "dirty" - FROM ( - SELECT date_trunc( - 'seconds', - pg_xact_commit_timestamp(t.xmin) AT TIME ZONE 'UTC' - ) AS modified_ts - FROM "{table}" t - ORDER BY modified_ts - DESC NULLS LAST limit 1 - ) as last_actual_modified, - ( - SELECT date_trunc('seconds', "last_modified"::TIMESTAMP) - FROM "table" - WHERE "table" = {} - ) as "last_known_modified""#, - SqlParam::new(&self.kind()).next() - ); - let params = json!([table]); - let row = self.query_one(&sql, Some(¶ms)).await?.unwrap(); - tracing::info!("ROW: {row:?}"); - let last_rltbl_mod = row.get_string("last_modified_by_rltbl")?; - match row.get_value("dirty")? { - JsonValue::Bool(is_dirty) => { - if last_rltbl_mod == "()" || is_dirty { - tracing::info!("Cache dirty. Cleaning it ..."); - let mut conn = self.reconnect()?; - let mut tx = self.begin(&mut conn).await?; - Relatable::clean_cache(&mut tx, Some(table))?; - tx.commit()?; - } else { - tracing::info!("Cache is clean"); - } - } - _ => panic!("Unexpected Value type"), - }; - } - DbKind::Sqlite => { - //tracing::warn!( - // "Metatable caching strategy not supported for SQLite. \ - // Skipping this step." - //); - } - }; - query_cache(self, table, sql, params).await - } } } } @@ -1089,15 +1025,12 @@ pub fn generate_table_table_ddl(force: bool, db_kind: &DbKind) -> Vec { DbKind::Postgres => "SERIAL PRIMARY KEY", }; - // TODO: Since we will not be using the Metatable caching strategy, we can remove the - // last_modified field. ddl.push(format!( r#"CREATE TABLE "table" ( "_id" {pkey_clause}, "_order" INTEGER UNIQUE, "table" TEXT UNIQUE, - "path" TEXT UNIQUE, - "last_modified" TEXT + "path" TEXT UNIQUE )"# )); diff --git a/src/test.rs b/src/test.rs index 19711eb..d76bdeb 100644 --- a/src/test.rs +++ b/src/test.rs @@ -299,11 +299,11 @@ async fn main() { force, } => { tracing::info!("Building demonstration database with {size} rows ..."); - let mut rltbl = Relatable::build_demo(Some(&cli.database), force, *size) + let strategy = CachingStrategy::from_str(&caching_strategy.to_lowercase()).unwrap(); + let rltbl = Relatable::build_demo(Some(&cli.database), force, *size, &strategy) .await .unwrap(); tracing::info!("Demonstration database built and loaded."); - rltbl.strategy = CachingStrategy::from_str(&caching_strategy.to_lowercase()).unwrap(); fn random_op<'a>() -> &'a str { match random_between(0, 3, &mut -1) { From a5f34cf36a38df2827a26c7a737de39b6d1e9ab7 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Fri, 9 May 2025 13:08:58 -0400 Subject: [PATCH 12/15] integrate CachingStrategy and improve logging --- Makefile | 14 ++++++---- src/cli.rs | 77 ++++++++++++++++++++++++++++++++++----------------- src/core.rs | 33 ++++++++++++---------- src/select.rs | 16 +++++++++-- src/sql.rs | 3 +- src/test.rs | 28 +++++++++---------- src/web.rs | 10 ++++--- 7 files changed, 114 insertions(+), 67 deletions(-) diff --git a/Makefile b/Makefile index 61cd050..f51edc6 100644 --- a/Makefile +++ b/Makefile @@ -168,15 +168,17 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy trigger -# target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate - # target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 0 35 --force --caching-strategy none + target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy trigger -vv test-read-perf penguin 100000 5000 100 60 --force + target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy truncate -vv test-read-perf penguin 100000 5000 100 60 --force + target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy truncate_all -vv test-read-perf penguin 100000 5000 100 60 --force + # target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy none -vv test-read-perf penguin 100000 5000 0 60 --force .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(SQLITE_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy trigger -# target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 100 60 --force --caching-strategy truncate - # target/debug/rltbl_test --database $(PG_DB) -vv test-read-perf penguin 100000 5000 0 60 --force --caching-strategy none + target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy trigger -vv test-read-perf penguin 100000 5000 100 60 --force + target/debug/rltbl_test --database $(PG_DB) --caching-strategy truncate -vv test-read-perf penguin 100000 5000 100 60 --force + target/debug/rltbl_test --database $(PG_DB) --caching-strategy truncate_all -vv test-read-perf penguin 100000 5000 100 60 --force + # target/debug/rltbl_test --database $(PG_DB) --caching-strategy none -vv test-read-perf penguin 100000 5000 0 90 --force .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug diff --git a/src/cli.rs b/src/cli.rs index 948c0bc..8b678a3 100644 --- a/src/cli.rs +++ b/src/cli.rs @@ -47,6 +47,10 @@ pub struct Cli { #[command(flatten)] verbose: Verbosity, + /// One of: none, truncate, truncate_all, trigger + #[arg(long, default_value = "trigger", action = ArgAction::Set)] + pub caching_strategy: CachingStrategy, + // Subcommand: #[command(subcommand)] pub command: Command, @@ -308,9 +312,9 @@ pub enum LoadSubcommand { }, } -pub async fn init(_cli: &Cli, force: &bool, path: Option<&str>) { - tracing::trace!("init({_cli:?}, {force}, {path:?})"); - match Relatable::init(force, path).await { +pub async fn init(cli: &Cli, force: &bool, path: Option<&str>) { + tracing::trace!("init({cli:?}, {force}, {path:?})"); + match Relatable::init(force, path, &cli.caching_strategy).await { Ok(_) => println!( "Initialized a relatable database in '{}'", match path { @@ -356,7 +360,9 @@ pub async fn print_table( // TODO: We need to ouput round numbers consistently between PostgreSQL and SQLite. // Currently, for instance, 37 is displayed as 37.0 in SQLite and 37 in PostgreSQL. tracing::debug!("print_table {table_name}"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let select = Select::from(table_name) .filters(filters) .unwrap() @@ -392,7 +398,9 @@ pub async fn print_table( // Print rows of a table, without column header. pub async fn print_rows(cli: &Cli, table_name: &str, limit: &usize, offset: &usize) { tracing::trace!("print_rows({cli:?}, {table_name}, {limit}, {offset})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let select = Select::from(table_name).limit(limit).offset(offset); let rows = rltbl.fetch_rows(&select).await.unwrap().vec_into(); print_text(&rows); @@ -400,7 +408,9 @@ pub async fn print_rows(cli: &Cli, table_name: &str, limit: &usize, offset: &usi pub async fn print_value(cli: &Cli, table: &str, row: usize, column: &str) { tracing::trace!("print_value({cli:?}, {table}, {row}, {column})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let statement = format!( r#"SELECT "{column}" FROM "{table}" WHERE _id = {sql_param}"#, sql_param = sql::SqlParam::new(&rltbl.connection.kind()).next(), @@ -433,7 +443,9 @@ pub async fn print_history(cli: &Cli, context: usize) { } let user = get_username(&cli); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let history = rltbl .get_user_history( &user, @@ -510,7 +522,9 @@ pub fn get_username(cli: &Cli) -> String { pub async fn set_value(cli: &Cli, table: &str, row: usize, column: &str, value: &str) { tracing::trace!("set_value({cli:?}, {table}, {row}, {column}, {value})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); // Fetch the current value from the db: let sql = format!( @@ -631,7 +645,9 @@ pub async fn prompt_for_json_row(rltbl: &Relatable, table_name: &str) -> Result< /// from STDIN, either interactively or in JSON format. pub async fn add_message(cli: &Cli, table: &str, row: usize, column: &str) { tracing::trace!("add_message({cli:?}, {table:?}, {row:?}, {column:?})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let json_message = match &cli.input { Some(s) if s == "JSON" => input_json_row(), Some(s) => panic!("Unsupported input type '{s}'"), @@ -670,7 +686,9 @@ pub async fn add_message(cli: &Cli, table: &str, row: usize, column: &str) { pub async fn add_row(cli: &Cli, table: &str, after_id: Option) { tracing::trace!("add_row({cli:?}, {table}, {after_id:?})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let json_row = match &cli.input { Some(s) if s == "JSON" => input_json_row(), Some(s) => panic!("Unsupported input type '{s}'"), @@ -693,7 +711,9 @@ pub async fn add_row(cli: &Cli, table: &str, after_id: Option) { pub async fn move_row(cli: &Cli, table: &str, row: usize, after_id: usize) { tracing::trace!("move_row({cli:?}, {table}, {row}, {after_id})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let user = get_username(&cli); let new_order = rltbl .move_row(table, &user, row, after_id) @@ -708,7 +728,9 @@ pub async fn move_row(cli: &Cli, table: &str, row: usize, after_id: usize) { pub async fn delete_row(cli: &Cli, table: &str, row: usize) { tracing::trace!("delete_row({cli:?}, {table}, {row})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let user = get_username(&cli); let num_deleted = rltbl .delete_row(table, &user, row) @@ -732,7 +754,9 @@ pub async fn delete_message( tracing::trace!( "delete_message({cli:?}, {target_rule:?}, {target_user:?}, {table}, {row:?}, {column:?})" ); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let num_deleted = rltbl .delete_message(table, row, column, target_rule, target_user) .await @@ -746,7 +770,9 @@ pub async fn delete_message( pub async fn undo(cli: &Cli) { tracing::trace!("undo({cli:?})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let user = get_username(&cli); let changeset = rltbl.undo(&user).await.expect("Failed to undo"); if let None = changeset { @@ -757,7 +783,9 @@ pub async fn undo(cli: &Cli) { pub async fn redo(cli: &Cli) { tracing::trace!("redo({cli:?})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); let user = get_username(&cli); let changeset = rltbl.redo(&user).await.expect("Failed to redo"); if let None = changeset { @@ -775,7 +803,9 @@ pub async fn load_tables(cli: &Cli, paths: &Vec, force: bool) { pub async fn load_table(cli: &Cli, path: &str, force: bool) { tracing::trace!("load_table({cli:?}, {path})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); // We will use this pattern to normalize the table name: let pattern = Regex::new(r#"[^0-9a-zA-Z_]+"#).expect("Invalid regex pattern"); @@ -794,20 +824,17 @@ pub async fn load_table(cli: &Cli, path: &str, force: bool) { pub async fn save_all(cli: &Cli, save_dir: Option<&str>) { tracing::trace!("save_all({cli:?})"); - let rltbl = Relatable::connect(cli.database.as_deref()).await.unwrap(); + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + .await + .unwrap(); rltbl.save_all(save_dir).await.expect("Error saving all"); } pub async fn build_demo(cli: &Cli, force: &bool, size: usize) { tracing::trace!("build_demo({cli:?}, {force}, {size})"); - Relatable::build_demo( - cli.database.as_deref(), - force, - size, - &CachingStrategy::TruncateForTable, - ) - .await - .expect("Error building demonstration database"); + Relatable::build_demo(cli.database.as_deref(), force, size, &cli.caching_strategy) + .await + .expect("Error building demonstration database"); println!( "Created a demonstration database in '{}'", match &cli.database { diff --git a/src/core.rs b/src/core.rs index 4621cea..4446b5b 100644 --- a/src/core.rs +++ b/src/core.rs @@ -91,15 +91,15 @@ pub struct Relatable { // pub minijinja: Environment<'static>, pub default_limit: usize, pub max_limit: usize, - pub strategy: CachingStrategy, + pub caching_strategy: CachingStrategy, } impl Relatable { /// Connect to a relatable database at the given path, or, if not given, at the location /// indicated by the environment variable RLTBL_CONNECTION, or, if that is not given, /// at [RLTBL_DEFAULT_DB] - pub async fn connect(path: Option<&str>) -> Result { - tracing::trace!("Relatable::connect({path:?})"); + pub async fn connect(path: Option<&str>, caching_strategy: &CachingStrategy) -> Result { + tracing::trace!("Relatable::connect({path:?}, {caching_strategy:?})"); let root = std::env::var("RLTBL_ROOT").unwrap_or_default(); // Set up database connection. let readonly = match std::env::var("RLTBL_READONLY") { @@ -132,15 +132,19 @@ impl Relatable { // minijinja: env, default_limit: DEFAULT_LIMIT, max_limit: MAX_LIMIT, - strategy: CachingStrategy::Trigger, + caching_strategy: *caching_strategy, }) } /// Initialize a [relatable](crate) database at the given path, or, if not given, at /// the location indicated by the environment variable RLTBL_CONNECTION, or, if that is not /// given, at [RLTBL_DEFAULT_DB]. Overwrites an existing database if `force` is set to true. - pub async fn init(force: &bool, path: Option<&str>) -> Result { - tracing::trace!("Relatable::init({force:?}, {path:?})"); + pub async fn init( + force: &bool, + path: Option<&str>, + caching_strategy: &CachingStrategy, + ) -> Result { + tracing::trace!("Relatable::init({force:?}, {path:?}, {caching_strategy:?})"); let path = match path { Some(path) => path.to_string(), None => { @@ -177,7 +181,7 @@ impl Relatable { } // Create the meta tables: - let rltbl = Relatable::connect(Some(&path)).await?; + let rltbl = Relatable::connect(Some(&path), caching_strategy).await?; let ddl = sql::generate_meta_tables_ddl(*force, &rltbl.connection.kind()); for sql in ddl { rltbl.connection.query(&sql, None).await?; @@ -193,9 +197,10 @@ impl Relatable { size: usize, caching_strategy: &CachingStrategy, ) -> Result { - let mut rltbl = Relatable::init(force, database.as_deref()).await?; - // TODO: Add this as an argument to init() - rltbl.strategy = *caching_strategy; + tracing::trace!( + "Relatable::build_demo({database:?}, {force}, {size}, {caching_strategy:?})" + ); + let rltbl = Relatable::init(force, database.as_deref(), caching_strategy).await?; if *force { if let DbKind::Postgres = rltbl.connection.kind() { @@ -258,7 +263,7 @@ impl Relatable { let mut ddl = vec![]; sql::add_metacolumn_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); - if let CachingStrategy::Trigger = caching_strategy { + if let CachingStrategy::Trigger = rltbl.caching_strategy { sql::add_caching_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); } for sql in ddl { @@ -485,7 +490,7 @@ impl Relatable { &statement, Some(¶ms), &select.table_name, - &self.strategy, + &self.caching_strategy, ) .await?; match json_rows.get(0) { @@ -588,7 +593,7 @@ impl Relatable { }; // Generate the SQL statements needed to create the table and execute them: - for sql in sql::generate_table_ddl(&table, force, &db_kind, &self.strategy) + for sql in sql::generate_table_ddl(&table, force, &db_kind, &self.caching_strategy) .expect("Error getting DDL") { self.connection @@ -1040,7 +1045,7 @@ impl Relatable { } // Possibly delete dirty entries from the cache in accordance with our caching strategy: - match self.strategy { + match self.caching_strategy { // Trigger has the same behaviour as None here, since the database will be triggering // this step automatically every time the table is edited in that case. CachingStrategy::None | CachingStrategy::Trigger => (), diff --git a/src/select.rs b/src/select.rs index 8d917c4..955c79d 100644 --- a/src/select.rs +++ b/src/select.rs @@ -1672,6 +1672,7 @@ pub fn render_values( #[cfg(test)] mod tests { + use crate::sql::CachingStrategy; use async_std::task::block_on; use super::*; @@ -1681,6 +1682,7 @@ mod tests { let rltbl = block_on(Relatable::init( &true, Some("build/test_select_from_path_and_query.db"), + &CachingStrategy::Trigger, )) .unwrap(); let sql_param = SqlParam::new(&rltbl.connection.kind()).next(); @@ -1786,7 +1788,12 @@ WHERE "foo"."bar" = {sql_param}"# #[test] fn test_select_methods() { - let rltbl = block_on(Relatable::init(&true, Some("build/test_select_methods.db"))).unwrap(); + let rltbl = block_on(Relatable::init( + &true, + Some("build/test_select_methods.db"), + &CachingStrategy::Trigger, + )) + .unwrap(); let drop_sql = r#"DROP TABLE IF EXISTS "penguin_test""#; let create_sql = r#"CREATE TABLE "penguin_test" ( _id INTEGER, @@ -1911,7 +1918,12 @@ FROM "penguin_test""# #[test] fn test_subquery() { - let rltbl = block_on(Relatable::init(&true, Some("build/test_subquery.db"))).unwrap(); + let rltbl = block_on(Relatable::init( + &true, + Some("build/test_subquery.db"), + &CachingStrategy::Trigger, + )) + .unwrap(); let sql_param = SqlParam::new(&rltbl.connection.kind()).next(); let mut inner_select = Select::from("penguin").limit(&0); diff --git a/src/sql.rs b/src/sql.rs index 7d58b3b..dacb461 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -723,7 +723,7 @@ pub fn generate_table_ddl( db_kind: &DbKind, caching_strategy: &CachingStrategy, ) -> Result> { - tracing::trace!("generate_table_ddl({table:?}, {force}, {db_kind:?})"); + tracing::trace!("generate_table_ddl({table:?}, {force}, {db_kind:?}, {caching_strategy:?})"); if table.has_meta { for (cname, col) in table.columns.iter() { if cname == "_id" || cname == "_order" { @@ -859,6 +859,7 @@ pub fn add_metacolumn_trigger_ddl(ddl: &mut Vec, table: &str, db_kind: & }; } +/// TODO: Add docstring pub fn add_caching_trigger_ddl(ddl: &mut Vec, table: &str, db_kind: &DbKind) { match db_kind { DbKind::Sqlite => { diff --git a/src/test.rs b/src/test.rs index d76bdeb..e0629ab 100644 --- a/src/test.rs +++ b/src/test.rs @@ -15,7 +15,6 @@ use rand::{ }; use serde_json::json; use std::{ - str::FromStr, thread, time::{Duration, Instant}, }; @@ -42,6 +41,10 @@ pub struct Cli { #[arg(long, action = ArgAction::Set)] seed: Option, + /// One of: none, truncate, truncate_all, trigger + #[arg(long, default_value = "trigger", action = ArgAction::Set)] + caching_strategy: CachingStrategy, + // Subcommand: #[command(subcommand)] pub command: Command, @@ -82,10 +85,6 @@ pub enum Command { /// Overwrite an existing database #[arg(long, action = ArgAction::SetTrue)] force: bool, - - /// One of: none, truncate, max_change, metadata, trigger - #[arg(long, default_value = "none", action = ArgAction::Set)] - caching_strategy: String, }, } @@ -284,7 +283,7 @@ async fn main() { min_length, max_length, } => { - let rltbl = Relatable::connect(Some(&cli.database)) + let rltbl = Relatable::connect(Some(&cli.database), &cli.caching_strategy) .await .expect("Could not connect to relatable database"); generate_operation_sequence(&cli, &rltbl, table, *min_length, *max_length).await; @@ -295,14 +294,13 @@ async fn main() { fetches, edit_rate, fail_after_secs, - caching_strategy, force, } => { tracing::info!("Building demonstration database with {size} rows ..."); - let strategy = CachingStrategy::from_str(&caching_strategy.to_lowercase()).unwrap(); - let rltbl = Relatable::build_demo(Some(&cli.database), force, *size, &strategy) - .await - .unwrap(); + let rltbl = + Relatable::build_demo(Some(&cli.database), force, *size, &cli.caching_strategy) + .await + .unwrap(); tracing::info!("Demonstration database built and loaded."); fn random_op<'a>() -> &'a str { @@ -315,7 +313,7 @@ async fn main() { } // TODO: Need to query more than one table to test the performance of - // CachingStrategy::TruncateForTable + // CachingStrategy::TruncateForTable as opposed to CachingStrategy::Truncate tracing::info!("Counting the number of rows in table {table} ..."); let now = Instant::now(); @@ -341,7 +339,7 @@ async fn main() { .add_row(table, &user, Some(after_id), &JsonRow::new()) .await .unwrap(); - tracing::debug!("Added row {} (order {})", row.id, row.order); + tracing::info!("Added row {} (order {})", row.id, row.order); } "update" => { let row_to_update = random_between(1, *size, &mut -1); @@ -365,7 +363,7 @@ async fn main() { if num_changes < 1 { panic!("No changes made"); } - tracing::debug!("Updated row {row_to_update}"); + tracing::info!("Updated row {row_to_update}"); } "move" => { let after_id = random_between(1, *size, &mut -1); @@ -375,7 +373,7 @@ async fn main() { .await .expect("Failed to move row"); if new_order > 0 { - tracing::debug!("Moved row {row} after row {after_id}"); + tracing::info!("Moved row {row} after row {after_id}"); } else { panic!("No changes made"); } diff --git a/src/web.rs b/src/web.rs index 3acca17..d3826c3 100644 --- a/src/web.rs +++ b/src/web.rs @@ -8,7 +8,7 @@ use rltbl::{ core::{ChangeSet, Cursor, Relatable, RelatableError, ResultSet}, select::{Format, QueryParams, Select}, sql, - sql::JsonRow, + sql::{CachingStrategy, JsonRow}, table::Row, }; use std::io::Write; @@ -644,9 +644,9 @@ pub async fn app(rltbl: Relatable, host: &str, port: &u16, timeout: &usize) -> R Ok("Stopping Relatable server...".into()) } -pub async fn serve(_cli: &Cli, host: &str, port: &u16, timeout: &usize) -> Result<()> { +pub async fn serve(cli: &Cli, host: &str, port: &u16, timeout: &usize) -> Result<()> { tracing::debug!("serve({host}, {port})"); - let rltbl = Relatable::connect(None).await?; + let rltbl = Relatable::connect(None, &cli.caching_strategy).await?; app(rltbl, host, port, timeout)?; Ok(()) } @@ -733,7 +733,9 @@ pub async fn serve_cgi() { .unwrap(); tracing::debug!("REQUEST {request:?}"); - let rltbl = Relatable::connect(None).await.expect("Database connection"); + let rltbl = Relatable::connect(None, &CachingStrategy::Trigger) + .await + .expect("Database connection"); let shared_state = Arc::new(rltbl); let mut router = build_app(shared_state).await; let response = router.call(request).await; From 0e5816c3a24de933e810c42a777d7592fd076c13 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Sat, 10 May 2025 11:25:25 -0400 Subject: [PATCH 13/15] improve caching tests, refactor penguin demo --- Makefile | 24 +++++++++++------ src/cli.rs | 34 +++++++++++------------ src/core.rs | 77 ++++++++++++++++++++++++++++++++--------------------- src/sql.rs | 4 +-- src/test.rs | 70 +++++++++++++++++++++++++++++------------------- src/web.rs | 2 +- 6 files changed, 124 insertions(+), 87 deletions(-) diff --git a/Makefile b/Makefile index f51edc6..c6af17b 100644 --- a/Makefile +++ b/Makefile @@ -168,17 +168,25 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy trigger -vv test-read-perf penguin 100000 5000 100 60 --force - target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy truncate -vv test-read-perf penguin 100000 5000 100 60 --force - target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy truncate_all -vv test-read-perf penguin 100000 5000 100 60 --force - # target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy none -vv test-read-perf penguin 100000 5000 0 60 --force + target/debug/rltbl_test --database $(SQLITE_DB) --caching trigger -vv test-read-perf 100000 5000 50 30 --force + @echo "--------------------" + target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate -vv test-read-perf 100000 5000 50 30 --force + @echo "--------------------" + target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 30 --force + @echo "--------------------" + target/debug/rltbl_test --database $(SQLITE_DB) --caching none -vv test-read-perf 100000 5000 50 60 --force + @echo "--------------------" .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(SQLITE_DB) --caching-strategy trigger -vv test-read-perf penguin 100000 5000 100 60 --force - target/debug/rltbl_test --database $(PG_DB) --caching-strategy truncate -vv test-read-perf penguin 100000 5000 100 60 --force - target/debug/rltbl_test --database $(PG_DB) --caching-strategy truncate_all -vv test-read-perf penguin 100000 5000 100 60 --force - # target/debug/rltbl_test --database $(PG_DB) --caching-strategy none -vv test-read-perf penguin 100000 5000 0 90 --force + target/debug/rltbl_test --database $(PG_DB) --caching trigger -vv test-read-perf 100000 5000 50 30 --force + @echo "--------------------" + target/debug/rltbl_test --database $(PG_DB) --caching truncate -vv test-read-perf 100000 5000 50 30 --force + @echo "--------------------" + target/debug/rltbl_test --database $(PG_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 30 --force + @echo "--------------------" + target/debug/rltbl_test --database $(PG_DB) --caching none -vv test-read-perf 100000 5000 50 60 --force + @echo "--------------------" .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug diff --git a/src/cli.rs b/src/cli.rs index 8b678a3..bd13af2 100644 --- a/src/cli.rs +++ b/src/cli.rs @@ -49,7 +49,7 @@ pub struct Cli { /// One of: none, truncate, truncate_all, trigger #[arg(long, default_value = "trigger", action = ArgAction::Set)] - pub caching_strategy: CachingStrategy, + pub caching: CachingStrategy, // Subcommand: #[command(subcommand)] @@ -314,7 +314,7 @@ pub enum LoadSubcommand { pub async fn init(cli: &Cli, force: &bool, path: Option<&str>) { tracing::trace!("init({cli:?}, {force}, {path:?})"); - match Relatable::init(force, path, &cli.caching_strategy).await { + match Relatable::init(force, path, &cli.caching).await { Ok(_) => println!( "Initialized a relatable database in '{}'", match path { @@ -360,7 +360,7 @@ pub async fn print_table( // TODO: We need to ouput round numbers consistently between PostgreSQL and SQLite. // Currently, for instance, 37 is displayed as 37.0 in SQLite and 37 in PostgreSQL. tracing::debug!("print_table {table_name}"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let select = Select::from(table_name) @@ -398,7 +398,7 @@ pub async fn print_table( // Print rows of a table, without column header. pub async fn print_rows(cli: &Cli, table_name: &str, limit: &usize, offset: &usize) { tracing::trace!("print_rows({cli:?}, {table_name}, {limit}, {offset})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let select = Select::from(table_name).limit(limit).offset(offset); @@ -408,7 +408,7 @@ pub async fn print_rows(cli: &Cli, table_name: &str, limit: &usize, offset: &usi pub async fn print_value(cli: &Cli, table: &str, row: usize, column: &str) { tracing::trace!("print_value({cli:?}, {table}, {row}, {column})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let statement = format!( @@ -443,7 +443,7 @@ pub async fn print_history(cli: &Cli, context: usize) { } let user = get_username(&cli); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let history = rltbl @@ -522,7 +522,7 @@ pub fn get_username(cli: &Cli) -> String { pub async fn set_value(cli: &Cli, table: &str, row: usize, column: &str, value: &str) { tracing::trace!("set_value({cli:?}, {table}, {row}, {column}, {value})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); @@ -645,7 +645,7 @@ pub async fn prompt_for_json_row(rltbl: &Relatable, table_name: &str) -> Result< /// from STDIN, either interactively or in JSON format. pub async fn add_message(cli: &Cli, table: &str, row: usize, column: &str) { tracing::trace!("add_message({cli:?}, {table:?}, {row:?}, {column:?})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let json_message = match &cli.input { @@ -686,7 +686,7 @@ pub async fn add_message(cli: &Cli, table: &str, row: usize, column: &str) { pub async fn add_row(cli: &Cli, table: &str, after_id: Option) { tracing::trace!("add_row({cli:?}, {table}, {after_id:?})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let json_row = match &cli.input { @@ -711,7 +711,7 @@ pub async fn add_row(cli: &Cli, table: &str, after_id: Option) { pub async fn move_row(cli: &Cli, table: &str, row: usize, after_id: usize) { tracing::trace!("move_row({cli:?}, {table}, {row}, {after_id})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let user = get_username(&cli); @@ -728,7 +728,7 @@ pub async fn move_row(cli: &Cli, table: &str, row: usize, after_id: usize) { pub async fn delete_row(cli: &Cli, table: &str, row: usize) { tracing::trace!("delete_row({cli:?}, {table}, {row})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let user = get_username(&cli); @@ -754,7 +754,7 @@ pub async fn delete_message( tracing::trace!( "delete_message({cli:?}, {target_rule:?}, {target_user:?}, {table}, {row:?}, {column:?})" ); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let num_deleted = rltbl @@ -770,7 +770,7 @@ pub async fn delete_message( pub async fn undo(cli: &Cli) { tracing::trace!("undo({cli:?})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let user = get_username(&cli); @@ -783,7 +783,7 @@ pub async fn undo(cli: &Cli) { pub async fn redo(cli: &Cli) { tracing::trace!("redo({cli:?})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); let user = get_username(&cli); @@ -803,7 +803,7 @@ pub async fn load_tables(cli: &Cli, paths: &Vec, force: bool) { pub async fn load_table(cli: &Cli, path: &str, force: bool) { tracing::trace!("load_table({cli:?}, {path})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); @@ -824,7 +824,7 @@ pub async fn load_table(cli: &Cli, path: &str, force: bool) { pub async fn save_all(cli: &Cli, save_dir: Option<&str>) { tracing::trace!("save_all({cli:?})"); - let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching_strategy) + let rltbl = Relatable::connect(cli.database.as_deref(), &cli.caching) .await .unwrap(); rltbl.save_all(save_dir).await.expect("Error saving all"); @@ -832,7 +832,7 @@ pub async fn save_all(cli: &Cli, save_dir: Option<&str>) { pub async fn build_demo(cli: &Cli, force: &bool, size: usize) { tracing::trace!("build_demo({cli:?}, {force}, {size})"); - Relatable::build_demo(cli.database.as_deref(), force, size, &cli.caching_strategy) + Relatable::build_demo(cli.database.as_deref(), force, size, &cli.caching) .await .expect("Error building demonstration database"); println!( diff --git a/src/core.rs b/src/core.rs index 4446b5b..f3e3aa8 100644 --- a/src/core.rs +++ b/src/core.rs @@ -201,31 +201,40 @@ impl Relatable { "Relatable::build_demo({database:?}, {force}, {size}, {caching_strategy:?})" ); let rltbl = Relatable::init(force, database.as_deref(), caching_strategy).await?; - if *force { if let DbKind::Postgres = rltbl.connection.kind() { rltbl .connection .query(r#"DROP TABLE IF EXISTS "column" CASCADE"#, None) .await?; - rltbl - .connection - .query(r#"DROP TABLE IF EXISTS "penguin" CASCADE"#, None) + } + } + rltbl.create_demo_table("penguin", force, size).await?; + Ok(rltbl) + } + + /// TODO: Add docstring + pub async fn create_demo_table(&self, table: &str, force: &bool, size: usize) -> Result<()> { + if *force { + if let DbKind::Postgres = self.connection.kind() { + self.connection + .query(&format!(r#"DROP TABLE IF EXISTS "{table}" CASCADE"#), None) .await?; } } - let sql = r#"INSERT INTO "table" ("table", "path") VALUES ('penguin', 'penguin.tsv')"#; - rltbl.connection.query(sql, None).await?; + let sql = + format!(r#"INSERT INTO "table" ("table", "path") VALUES ('{table}', '{table}.tsv')"#); + self.connection.query(&sql, None).await?; - let pkey_clause = match rltbl.connection.kind() { + let pkey_clause = match self.connection.kind() { DbKind::Sqlite => "INTEGER PRIMARY KEY AUTOINCREMENT", DbKind::Postgres => "SERIAL PRIMARY KEY", }; // Create and populate a column table: let sql = format!( - r#"CREATE TABLE "column" ( + r#"CREATE TABLE IF NOT EXISTS "column" ( _id {pkey_clause}, _order INTEGER UNIQUE, "table" TEXT, @@ -235,19 +244,21 @@ impl Relatable { "nulltype" TEXT )"#, ); - rltbl.connection.query(&sql, None).await?; + self.connection.query(&sql, None).await?; - let sql = r#"INSERT INTO "column" - ("table", "column", "label", "description", "nulltype") - VALUES ('penguin', 'study_name', 'muddy_name', NULL, NULL), - ('penguin', 'sample_number', NULL, 'a sample number', NULL), - ('penguin', 'maple_syrup', 'maple syrup', NULL, NULL), - ('penguin', 'species', NULL, NULL, 'empty')"#; - rltbl.connection.query(sql, None).await?; + let sql = format!( + r#"INSERT INTO "column" + ("table", "column", "label", "description", "nulltype") + VALUES ('{table}', 'study_name', 'muddy_name', NULL, NULL), + ('{table}', 'sample_number', NULL, 'a sample number', NULL), + ('{table}', 'maple_syrup', 'maple syrup', NULL, NULL), + ('{table}', 'species', NULL, NULL, 'empty')"# + ); + self.connection.query(&sql, None).await?; - // Create a data table called penguin: + // Create the demo table: let sql = format!( - r#"CREATE TABLE penguin ( + r#"CREATE TABLE "{table}" ( _id {pkey_clause}, _order INTEGER UNIQUE, study_name TEXT, @@ -259,24 +270,24 @@ impl Relatable { body_mass TEXT )"#, ); - rltbl.connection.query(&sql, None).await?; + self.connection.query(&sql, None).await?; let mut ddl = vec![]; - sql::add_metacolumn_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); - if let CachingStrategy::Trigger = rltbl.caching_strategy { - sql::add_caching_trigger_ddl(&mut ddl, "penguin", &rltbl.connection.kind()); + sql::add_metacolumn_trigger_ddl(&mut ddl, table, &self.connection.kind()); + if let CachingStrategy::Trigger = self.caching_strategy { + sql::add_caching_trigger_ddl(&mut ddl, table, &self.connection.kind()); } for sql in ddl { - rltbl.connection.query(&sql, None).await?; + self.connection.query(&sql, None).await?; } - // Populate the penguin table with random data. + // Populate the demo table with random data. let islands = vec!["Biscoe", "Dream", "Torgersen"]; let mut rng = StdRng::seed_from_u64(0); - let sql_first_part = r#"INSERT INTO "penguin" VALUES "#; + let sql_first_part = format!(r#"INSERT INTO "{table}" VALUES "#); let mut sql_value_parts = vec![]; - let mut sql_param = SqlParam::new(&rltbl.connection.kind()); + let mut sql_param = SqlParam::new(&self.connection.kind()); let mut param_values = vec![]; - let max_params = match rltbl.connection.kind() { + let max_params = match self.connection.kind() { DbKind::Sqlite => sql::MAX_PARAMS_SQLITE, DbKind::Postgres => sql::MAX_PARAMS_POSTGRES, }; @@ -287,8 +298,11 @@ impl Relatable { sql_value_part = sql_value_parts.join(", ") ); let values_so_far = json!(param_values); - rltbl.connection.query(&sql, Some(&values_so_far)).await?; - tracing::info!("{num_rows} rows loaded to table penguin", num_rows = i - 1); + self.connection.query(&sql, Some(&values_so_far)).await?; + tracing::info!( + "{num_rows} rows loaded to table '{table}'", + num_rows = i - 1 + ); param_values.clear(); sql_value_parts.clear(); sql_param.reset(); @@ -320,9 +334,10 @@ impl Relatable { sql_value_part = sql_value_parts.join(", ") ); let param_values = json!(param_values); - rltbl.connection.query(&sql, Some(¶m_values)).await?; + self.connection.query(&sql, Some(¶m_values)).await?; } - Ok(rltbl) + + Ok(()) } pub fn render(&self, template: &str, context: T) -> Result { diff --git a/src/sql.rs b/src/sql.rs index dacb461..19e267d 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -369,13 +369,13 @@ impl DbConnection { .await? { Some(json_row) => { - tracing::debug!("Cache hit"); + tracing::debug!("Cache hit for table '{table}'"); let value = json_row.get_string("value")?; let json_rows: Vec = serde_json::from_str(&value)?; Ok(json_rows) } None => { - tracing::info!("Cache miss"); + tracing::info!("Cache miss for table '{table}'"); let json_rows = conn.query(sql, params).await?; let mut sql_param = SqlParam::new(&conn.kind()); let update_cache_sql = format!( diff --git a/src/test.rs b/src/test.rs index e0629ab..ca38394 100644 --- a/src/test.rs +++ b/src/test.rs @@ -43,7 +43,7 @@ pub struct Cli { /// One of: none, truncate, truncate_all, trigger #[arg(long, default_value = "trigger", action = ArgAction::Set)] - caching_strategy: CachingStrategy, + caching: CachingStrategy, // Subcommand: #[command(subcommand)] @@ -68,10 +68,7 @@ pub enum Command { /// table. TestReadPerf { #[arg(action = ArgAction::Set)] - table: String, - - #[arg(action = ArgAction::Set)] - size: usize, + table_size: usize, #[arg(action = ArgAction::Set)] fetches: usize, @@ -283,24 +280,32 @@ async fn main() { min_length, max_length, } => { - let rltbl = Relatable::connect(Some(&cli.database), &cli.caching_strategy) + let rltbl = Relatable::connect(Some(&cli.database), &cli.caching) .await .expect("Could not connect to relatable database"); generate_operation_sequence(&cli, &rltbl, table, *min_length, *max_length).await; } Command::TestReadPerf { - table, - size, + table_size, fetches, edit_rate, fail_after_secs, force, } => { - tracing::info!("Building demonstration database with {size} rows ..."); + tracing::info!("Building demonstration database with {table_size} rows per table ..."); let rltbl = - Relatable::build_demo(Some(&cli.database), force, *size, &cli.caching_strategy) + Relatable::build_demo(Some(&cli.database), force, *table_size, &cli.caching) .await .unwrap(); + let tables_to_choose_from = vec!["penguin", "qenguin", "renguin", "senguin"]; + for table in tables_to_choose_from.iter() { + if *table != "penguin" { + rltbl + .create_demo_table(table, force, *table_size) + .await + .unwrap(); + } + } tracing::info!("Demonstration database built and loaded."); fn random_op<'a>() -> &'a str { @@ -312,17 +317,25 @@ async fn main() { } } - // TODO: Need to query more than one table to test the performance of - // CachingStrategy::TruncateForTable as opposed to CachingStrategy::Truncate + fn random_table<'a>(tables_to_choose_from: &'a Vec<&str>) -> &'a str { + match random_between(0, 4, &mut -1) { + 0 => tables_to_choose_from[0], + 1 => tables_to_choose_from[1], + 2 => tables_to_choose_from[2], + 3 => tables_to_choose_from[3], + _ => unreachable!(), + } + } - tracing::info!("Counting the number of rows in table {table} ..."); + tracing::info!("Counting rows from tables {tables_to_choose_from:?} ..."); let now = Instant::now(); - let select = Select::from(table); let mut i = 0; - let mut count = 0; let mut elapsed; while i < *fetches { - count = rltbl.count(&select).await.unwrap(); + let table = random_table(&tables_to_choose_from); + let select = Select::from(table); + let count = rltbl.count(&select).await.unwrap(); + tracing::debug!("Counted {count} rows from table '{table}'"); elapsed = now.elapsed().as_secs(); if elapsed > *fail_after_secs { panic!("Taking longer than {fail_after_secs}s. Timing out."); @@ -334,15 +347,15 @@ async fn main() { }; match random_op() { "add" => { - let after_id = random_between(1, *size, &mut -1); + let after_id = random_between(1, *table_size, &mut -1); let row = rltbl .add_row(table, &user, Some(after_id), &JsonRow::new()) .await .unwrap(); - tracing::info!("Added row {} (order {})", row.id, row.order); + tracing::info!("Added row {} (order {}) to {table}", row.id, row.order); } "update" => { - let row_to_update = random_between(1, *size, &mut -1); + let row_to_update = random_between(1, *table_size, &mut -1); let num_changes = rltbl .set_values(&ChangeSet { user, @@ -361,27 +374,27 @@ async fn main() { .changes .len(); if num_changes < 1 { - panic!("No changes made"); + panic!("No changes made to {table}"); } - tracing::info!("Updated row {row_to_update}"); + tracing::info!("Updated row {row_to_update} in {table}"); } "move" => { - let after_id = random_between(1, *size, &mut -1); - let row = random_between(1, *size, &mut -1); + let after_id = random_between(1, *table_size, &mut -1); + let row = random_between(1, *table_size, &mut -1); let new_order = rltbl .move_row(table, &user, row, after_id) .await - .expect("Failed to move row"); + .expect("Failed to move row within {table}"); if new_order > 0 { - tracing::info!("Moved row {row} after row {after_id}"); + tracing::info!("Moved row {row} after row {after_id} in {table}"); } else { - panic!("No changes made"); + panic!("No changes made to {table}"); } } operation => panic!("Unrecognized operation: {operation}"), } } else { - tracing::debug!("Not making any edits"); + tracing::debug!("Not making any edits to {table}"); } // A small sleep to prevent over-taxing the CPU: @@ -390,7 +403,8 @@ async fn main() { } elapsed = now.elapsed().as_secs(); tracing::info!( - "Counted {count} rows from table '{table}' {fetches} times in {elapsed}s" + "Performed {fetches} counts using strategy {} on tables {tables_to_choose_from:?} in {elapsed}s", + cli.caching ); } } diff --git a/src/web.rs b/src/web.rs index d3826c3..111ed2a 100644 --- a/src/web.rs +++ b/src/web.rs @@ -646,7 +646,7 @@ pub async fn app(rltbl: Relatable, host: &str, port: &u16, timeout: &usize) -> R pub async fn serve(cli: &Cli, host: &str, port: &u16, timeout: &usize) -> Result<()> { tracing::debug!("serve({host}, {port})"); - let rltbl = Relatable::connect(None, &cli.caching_strategy).await?; + let rltbl = Relatable::connect(None, &cli.caching).await?; app(rltbl, host, port, timeout)?; Ok(()) } From eae14f6e19127ec1839b173d9a461ba2504339f0 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Sun, 11 May 2025 09:51:22 -0400 Subject: [PATCH 14/15] only edit penguin in caching tests --- Makefile | 14 +++++++------- src/test.rs | 5 ++++- 2 files changed, 11 insertions(+), 8 deletions(-) diff --git a/Makefile b/Makefile index c6af17b..8335e4c 100644 --- a/Makefile +++ b/Makefile @@ -168,22 +168,22 @@ PG_DB = "postgresql:///rltbl_db" .PHONY: test_caching_sqlite test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) --caching trigger -vv test-read-perf 100000 5000 50 30 --force + target/debug/rltbl_test --database $(SQLITE_DB) --caching trigger -vv test-read-perf 100000 5000 50 35 --force @echo "--------------------" - target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate -vv test-read-perf 100000 5000 50 30 --force + target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate -vv test-read-perf 100000 5000 50 35 --force @echo "--------------------" - target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 30 --force + target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 45 --force @echo "--------------------" - target/debug/rltbl_test --database $(SQLITE_DB) --caching none -vv test-read-perf 100000 5000 50 60 --force + target/debug/rltbl_test --database $(SQLITE_DB) --caching none -vv test-read-perf 100000 5000 50 45 --force @echo "--------------------" .PHONY: test_caching_postgres test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(PG_DB) --caching trigger -vv test-read-perf 100000 5000 50 30 --force + target/debug/rltbl_test --database $(PG_DB) --caching trigger -vv test-read-perf 100000 5000 50 35 --force @echo "--------------------" - target/debug/rltbl_test --database $(PG_DB) --caching truncate -vv test-read-perf 100000 5000 50 30 --force + target/debug/rltbl_test --database $(PG_DB) --caching truncate -vv test-read-perf 100000 5000 50 35 --force @echo "--------------------" - target/debug/rltbl_test --database $(PG_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 30 --force + target/debug/rltbl_test --database $(PG_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 40 --force @echo "--------------------" target/debug/rltbl_test --database $(PG_DB) --caching none -vv test-read-perf 100000 5000 50 60 --force @echo "--------------------" diff --git a/src/test.rs b/src/test.rs index ca38394..cda5cf3 100644 --- a/src/test.rs +++ b/src/test.rs @@ -331,6 +331,7 @@ async fn main() { let now = Instant::now(); let mut i = 0; let mut elapsed; + let table_to_edit = tables_to_choose_from[0]; while i < *fetches { let table = random_table(&tables_to_choose_from); let select = Select::from(table); @@ -345,6 +346,7 @@ async fn main() { Some(user) => user.clone(), None => whoami::username(), }; + let table = table_to_edit; match random_op() { "add" => { let after_id = random_between(1, *table_size, &mut -1); @@ -403,7 +405,8 @@ async fn main() { } elapsed = now.elapsed().as_secs(); tracing::info!( - "Performed {fetches} counts using strategy {} on tables {tables_to_choose_from:?} in {elapsed}s", + "Performed {fetches} counts using strategy {} on tables {tables_to_choose_from:?} \ + in {elapsed}s", cli.caching ); } From 433fdcd177e939faeedd173c99d0487681e7e7c4 Mon Sep 17 00:00:00 2001 From: Michael Cuffaro Date: Mon, 12 May 2025 07:13:08 -0400 Subject: [PATCH 15/15] cleanup --- Makefile | 28 +++------------------------- src/sql.rs | 7 +------ 2 files changed, 4 insertions(+), 31 deletions(-) diff --git a/Makefile b/Makefile index 8335e4c..833946d 100644 --- a/Makefile +++ b/Makefile @@ -166,28 +166,6 @@ perf_test_size = 100000 SQLITE_DB = ".relatable/relatable.db" PG_DB = "postgresql:///rltbl_db" -.PHONY: test_caching_sqlite -test_caching_sqlite: debug - target/debug/rltbl_test --database $(SQLITE_DB) --caching trigger -vv test-read-perf 100000 5000 50 35 --force - @echo "--------------------" - target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate -vv test-read-perf 100000 5000 50 35 --force - @echo "--------------------" - target/debug/rltbl_test --database $(SQLITE_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 45 --force - @echo "--------------------" - target/debug/rltbl_test --database $(SQLITE_DB) --caching none -vv test-read-perf 100000 5000 50 45 --force - @echo "--------------------" - -.PHONY: test_caching_postgres -test_caching_postgres: sqlx_debug - target/debug/rltbl_test --database $(PG_DB) --caching trigger -vv test-read-perf 100000 5000 50 35 --force - @echo "--------------------" - target/debug/rltbl_test --database $(PG_DB) --caching truncate -vv test-read-perf 100000 5000 50 35 --force - @echo "--------------------" - target/debug/rltbl_test --database $(PG_DB) --caching truncate_all -vv test-read-perf 100000 5000 50 40 --force - @echo "--------------------" - target/debug/rltbl_test --database $(PG_DB) --caching none -vv test-read-perf 100000 5000 50 60 --force - @echo "--------------------" - .PHONY: test_perf_sqlite test_perf_sqlite: test/perf/tsv/penguin.tsv debug target/debug/rltbl --database $(SQLITE_DB) init --force @@ -214,13 +192,13 @@ test_perf_sqlx_postgres: test/perf/tsv/penguin.tsv sqlx_debug # Combined tests .PHONY: test_rusqlite -test_rusqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc test_tesh_common_as_sqlite test_tesh_sqlite_only test_random_sqlite test_perf_sqlite test_caching_sqlite +test_rusqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc test_tesh_common_as_sqlite test_tesh_sqlite_only test_random_sqlite test_perf_sqlite .PHONY: test_sqlx_sqlite -test_sqlx_sqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc_sqlx test_tesh_sqlx_common_as_sqlite test_tesh_sqlx_sqlite_only test_random_sqlx_sqlite test_perf_sqlx_sqlite test_caching_sqlite +test_sqlx_sqlite: src/resources/main.js src/resources/main.css test_fmt_and_unittest test_tesh_doc_sqlx test_tesh_sqlx_common_as_sqlite test_tesh_sqlx_sqlite_only test_random_sqlx_sqlite test_perf_sqlx_sqlite .PHONY: test_sqlx_postgres -test_sqlx_postgres: src/resources/main.js src/resources/main.css test_fmt_and_unittest_postgres test_tesh_doc_sqlx test_tesh_sqlx_common_as_postgres test_tesh_sqlx_postgres_only test_random_sqlx_postgres test_perf_sqlx_postgres test_caching_postgres +test_sqlx_postgres: src/resources/main.js src/resources/main.css test_fmt_and_unittest_postgres test_tesh_doc_sqlx test_tesh_sqlx_common_as_postgres test_tesh_sqlx_postgres_only test_random_sqlx_postgres test_perf_sqlx_postgres .PHONY: test test: test_rusqlite diff --git a/src/sql.rs b/src/sql.rs index 19e267d..7661567 100644 --- a/src/sql.rs +++ b/src/sql.rs @@ -51,10 +51,7 @@ pub static MAX_PARAMS_SQLITE: usize = 32766; /// that can be bound to a Postgres query pub static MAX_PARAMS_POSTGRES: usize = 65535; -// TODO: Read the below comment and consider whether it is time to get rid of this -// It's possible that will only useful up until when we decide upon our final caching strategy. -// In the meantime, it is useful to define the following struct, which lets us compare the -// performance of various caching strategies. +/// Strategy to use for caching #[derive(Clone, Copy, Debug, PartialEq, Eq)] pub enum CachingStrategy { None, @@ -1050,8 +1047,6 @@ pub fn generate_cache_table_ddl(force: bool, db_kind: &DbKind) -> Vec { } ddl.push(format!( - // TODO: Generalize the "table" field to support an array of table names (for join - // purposes) r#"CREATE TABLE "cache" ( "table" TEXT, "key" TEXT,