commit 9cfbe20e86656898cc9fb124c662e29f3d1d32dc
parent 4114fb969a0f8d09911e323b4fa76e8a0e170ab2
Author: triesap <tyson@radroots.org>
Date: Sun, 12 Apr 2026 05:24:30 +0000
replica_db: add shaped query helpers
Diffstat:
3 files changed, 223 insertions(+), 2 deletions(-)
diff --git a/crates/replica_db/src/lib.rs b/crates/replica_db/src/lib.rs
@@ -119,13 +119,17 @@ pub mod migrations;
#[cfg(not(feature = "coverage-minimal"))]
pub mod models;
#[cfg(not(feature = "coverage-minimal"))]
+pub mod query;
+#[cfg(not(feature = "coverage-minimal"))]
pub use backup::{DatabaseBackup, MigrationBackup, SchemaEntry};
#[cfg(not(feature = "coverage-minimal"))]
pub use export::{
- REPLICA_DB_EXPORT_VERSION, ReplicaDbExportManifestRs, TableCount, export_manifest,
+ export_manifest, ReplicaDbExportManifestRs, TableCount, REPLICA_DB_EXPORT_VERSION,
};
#[cfg(not(feature = "coverage-minimal"))]
pub use models::*;
+#[cfg(not(feature = "coverage-minimal"))]
+pub use query::ReplicaTradeProductSummaryRow;
pub struct ReplicaSql<E: SqlExecutor> {
executor: E,
@@ -133,7 +137,11 @@ pub struct ReplicaSql<E: SqlExecutor> {
impl<E: SqlExecutor> ReplicaSql<E> {
pub fn coverage_branch_probe(enabled: bool) -> &'static str {
- if enabled { "enabled" } else { "disabled" }
+ if enabled {
+ "enabled"
+ } else {
+ "disabled"
+ }
}
}
diff --git a/crates/replica_db/src/query.rs b/crates/replica_db/src/query.rs
@@ -0,0 +1,125 @@
+use radroots_sql_core::utils;
+use serde::Deserialize;
+use serde_json::Value;
+
+use crate::{ReplicaSql, SqlError, SqlExecutor};
+
+#[derive(Debug, Clone, Deserialize, PartialEq)]
+pub struct ReplicaTradeProductSummaryRow {
+ pub id: String,
+ pub key: String,
+ pub category: String,
+ pub title: String,
+ pub summary: String,
+ pub qty_amt: i64,
+ pub qty_unit: String,
+ pub qty_label: Option<String>,
+ pub qty_avail: Option<i64>,
+ pub price_amt: f64,
+ pub price_currency: String,
+ pub price_qty_amt: u32,
+ pub price_qty_unit: String,
+ pub location_primary: Option<String>,
+}
+
+#[derive(Debug, Clone, Deserialize)]
+struct ReplicaFarmDTagRow {
+ d_tag: String,
+}
+
+#[derive(Debug, Clone, Deserialize)]
+struct ReplicaEventFreshnessRow {
+ last_created_at: Option<i64>,
+}
+
+impl<E: SqlExecutor> ReplicaSql<E> {
+ pub fn trade_product_lookup(
+ &self,
+ lookup: &str,
+ ) -> Result<Vec<ReplicaTradeProductSummaryRow>, SqlError> {
+ let sql = "SELECT tp.id, tp.key, tp.category, tp.title, tp.summary, tp.qty_amt, tp.qty_unit, tp.qty_label, tp.qty_avail, tp.price_amt, tp.price_currency, tp.price_qty_amt, tp.price_qty_unit, loc.location_primary \
+ FROM trade_product tp \
+ LEFT JOIN (\
+ SELECT tpl.tb_tp AS trade_product_id, MIN(COALESCE(gl.label, gl.gc_name, gl.gc_admin1_name, gl.gc_country_name, gl.d_tag)) AS location_primary \
+ FROM trade_product_location tpl \
+ JOIN gcs_location gl ON gl.id = tpl.tb_gl \
+ GROUP BY tpl.tb_tp\
+ ) loc ON loc.trade_product_id = tp.id \
+ WHERE tp.id = ? OR tp.key = ? \
+ ORDER BY lower(tp.title) ASC, tp.id ASC;";
+ let params_json = utils::to_params_json(vec![
+ Value::from(lookup.to_owned()),
+ Value::from(lookup.to_owned()),
+ ])?;
+ let json = self.executor().query_raw(sql, ¶ms_json)?;
+ serde_json::from_str(&json).map_err(SqlError::from)
+ }
+
+ pub fn trade_product_search(
+ &self,
+ query_terms: &[String],
+ ) -> Result<Vec<ReplicaTradeProductSummaryRow>, SqlError> {
+ if query_terms.is_empty() {
+ return Ok(Vec::new());
+ }
+
+ let mut where_clauses = Vec::with_capacity(query_terms.len());
+ let mut bind_values = Vec::<Value>::with_capacity(query_terms.len() * 5);
+ for term in query_terms {
+ let pattern = format!("%{}%", term.to_lowercase());
+ where_clauses.push(
+ "(lower(tp.title) LIKE ? OR lower(tp.summary) LIKE ? OR lower(tp.category) LIKE ? OR lower(tp.key) LIKE ? OR lower(COALESCE(tp.notes, '')) LIKE ?)"
+ .to_owned(),
+ );
+ for _ in 0..5 {
+ bind_values.push(Value::from(pattern.clone()));
+ }
+ }
+
+ let sql = format!(
+ "SELECT tp.id, tp.key, tp.category, tp.title, tp.summary, tp.qty_amt, tp.qty_unit, tp.qty_label, tp.qty_avail, tp.price_amt, tp.price_currency, tp.price_qty_amt, tp.price_qty_unit, loc.location_primary \
+ FROM trade_product tp \
+ LEFT JOIN (\
+ SELECT tpl.tb_tp AS trade_product_id, MIN(COALESCE(gl.label, gl.gc_name, gl.gc_admin1_name, gl.gc_country_name, gl.d_tag)) AS location_primary \
+ FROM trade_product_location tpl \
+ JOIN gcs_location gl ON gl.id = tpl.tb_gl \
+ GROUP BY tpl.tb_tp\
+ ) loc ON loc.trade_product_id = tp.id \
+ WHERE {} \
+ ORDER BY lower(tp.title) ASC, tp.id ASC;",
+ where_clauses.join(" AND ")
+ );
+ let params_json = utils::to_params_json(bind_values)?;
+ let json = self.executor().query_raw(&sql, ¶ms_json)?;
+ serde_json::from_str(&json).map_err(SqlError::from)
+ }
+
+ pub fn farm_unique_d_tag_by_pubkey(
+ &self,
+ seller_pubkey: &str,
+ ) -> Result<Option<String>, SqlError> {
+ let sql = "SELECT d_tag FROM farm WHERE pubkey = ? ORDER BY d_tag ASC;";
+ let params_json = utils::to_params_json(vec![Value::from(seller_pubkey.to_owned())])?;
+ let json = self.executor().query_raw(sql, ¶ms_json)?;
+ let rows: Vec<ReplicaFarmDTagRow> = serde_json::from_str(&json).map_err(SqlError::from)?;
+ if rows.len() == 1 {
+ Ok(Some(rows[0].d_tag.clone()))
+ } else {
+ Ok(None)
+ }
+ }
+
+ pub fn nostr_event_last_created_at(&self) -> Result<Option<u64>, SqlError> {
+ let json = self.executor().query_raw(
+ "SELECT MAX(last_created_at) AS last_created_at FROM nostr_event_state WHERE last_created_at IS NOT NULL",
+ "[]",
+ )?;
+ let rows: Vec<ReplicaEventFreshnessRow> =
+ serde_json::from_str(&json).map_err(SqlError::from)?;
+ Ok(rows
+ .into_iter()
+ .next()
+ .and_then(|row| row.last_created_at)
+ .and_then(|value| u64::try_from(value).ok()))
+ }
+}
diff --git a/crates/replica_db/tests/full_mode.rs b/crates/replica_db/tests/full_mode.rs
@@ -95,6 +95,94 @@ fn open_db() -> ReplicaSql<SqliteExecutor> {
}
#[test]
+fn full_mode_shaped_query_helpers_cover_cli_reads() {
+ let db = open_db();
+
+ let farm: IFarmCreate = parse_json(json!({
+ "d_tag": "farm-a",
+ "pubkey": hex64('a'),
+ "name": "farm a"
+ }));
+ db.farm_create(&farm).expect("farm create");
+
+ let gcs_location: IGcsLocationCreate = parse_json(json!({
+ "d_tag": "gcs-a",
+ "lat": 59.33,
+ "lng": 18.06,
+ "geohash": "u6sce4f",
+ "point": "POINT(18.06 59.33)",
+ "polygon": "POLYGON((18.06 59.33,18.07 59.33,18.07 59.34,18.06 59.34,18.06 59.33))",
+ "label": "stockholm"
+ }));
+ let gcs_created = db.gcs_location_create(&gcs_location).expect("gcs create").result;
+
+ let trade_product: ITradeProductCreate = parse_json(json!({
+ "key": "product-a",
+ "category": "coffee",
+ "title": "coffee a",
+ "summary": "washed lot",
+ "process": "washed",
+ "lot": "lot-a",
+ "profile": "floral",
+ "year": 2024,
+ "qty_amt": 100,
+ "qty_unit": "kg",
+ "qty_label": "bags",
+ "qty_avail": 2,
+ "price_amt": 7.5,
+ "price_currency": "USD",
+ "price_qty_amt": 1,
+ "price_qty_unit": "kg",
+ "notes": "fresh coffee"
+ }));
+ let trade_product_created = db
+ .trade_product_create(&trade_product)
+ .expect("trade product create")
+ .result;
+
+ let product_location_rel: ITradeProductLocationRelation = parse_json(json!({
+ "trade_product": { "id": trade_product_created.id },
+ "gcs_location": { "id": gcs_created.id }
+ }));
+ db.trade_product_location_set(&product_location_rel)
+ .expect("product location set");
+
+ let nostr_event_state: INostrEventStateCreate = parse_json(json!({
+ "key": "state-a",
+ "kind": 30023,
+ "pubkey": hex64('d'),
+ "d_tag": "listing-a",
+ "last_event_id": hex64('e'),
+ "last_created_at": 42,
+ "content_hash": "hash-a"
+ }));
+ db.nostr_event_state_create(&nostr_event_state)
+ .expect("nostr event state create");
+
+ let rows = db
+ .trade_product_search(&["coffee".to_owned()])
+ .expect("trade product search");
+ assert_eq!(rows.len(), 1);
+ assert_eq!(rows[0].key, "product-a");
+ assert_eq!(rows[0].location_primary.as_deref(), Some("stockholm"));
+
+ let lookup_rows = db.trade_product_lookup("product-a").expect("trade product lookup");
+ assert_eq!(lookup_rows.len(), 1);
+ assert_eq!(lookup_rows[0].id, trade_product_created.id);
+
+ assert_eq!(db.trade_product_search(&[]).expect("empty search"), Vec::new());
+ assert_eq!(
+ db.farm_unique_d_tag_by_pubkey(hex64('a').as_str())
+ .expect("farm unique d tag"),
+ Some("farm-a".to_owned())
+ );
+ assert_eq!(
+ db.nostr_event_last_created_at().expect("nostr event freshness"),
+ Some(42)
+ );
+}
+
+#[test]
fn full_mode_crud_and_relation_paths() {
let db = open_db();
