lib

Core libraries for Radroots
git clone https://radroots.dev/git/lib.git
Log | Files | Refs | README | LICENSE

query.rs (8340B)

      1 use radroots_sql_core::utils;
      2 use serde::Deserialize;
      3 use serde_json::Value;
      4 
      5 use crate::{ReplicaSql, SqlError, SqlExecutor};
      6 
      7 #[derive(Debug, Clone, Deserialize, PartialEq)]
      8 pub struct ReplicaTradeProductSummaryRow {
      9     pub id: String,
     10     pub key: String,
     11     pub category: String,
     12     pub title: String,
     13     pub summary: String,
     14     pub qty_amt: f64,
     15     pub qty_amt_exact: Option<String>,
     16     pub qty_unit: String,
     17     pub qty_label: Option<String>,
     18     pub qty_avail: Option<i64>,
     19     pub price_amt: f64,
     20     pub price_amt_exact: Option<String>,
     21     pub price_currency: String,
     22     pub price_qty_amt: f64,
     23     pub price_qty_amt_exact: Option<String>,
     24     pub price_qty_unit: String,
     25     pub listing_addr: Option<String>,
     26     pub primary_bin_id: Option<String>,
     27     pub verified_primary_bin_id: Option<String>,
     28     pub notes: Option<String>,
     29     pub location_primary: Option<String>,
     30 }
     31 
     32 #[derive(Debug, Clone, Deserialize)]
     33 struct ReplicaFarmDTagRow {
     34     d_tag: String,
     35 }
     36 
     37 #[derive(Debug, Clone, Deserialize)]
     38 struct ReplicaEventFreshnessRow {
     39     last_created_at: Option<i64>,
     40 }
     41 
     42 impl<E: SqlExecutor> ReplicaSql<E> {
     43     pub fn trade_product_lookup(
     44         &self,
     45         lookup: &str,
     46     ) -> Result<Vec<ReplicaTradeProductSummaryRow>, SqlError> {
     47         let sql = "SELECT tp.id, tp.key, tp.category, tp.title, tp.summary, tp.qty_amt, tp.qty_amt_exact, tp.qty_unit, tp.qty_label, tp.qty_avail, tp.price_amt, tp.price_amt_exact, tp.price_currency, tp.price_qty_amt, tp.price_qty_amt_exact, tp.price_qty_unit, tp.listing_addr, tp.primary_bin_id, tp.verified_primary_bin_id, tp.notes, loc.location_primary \
     48              FROM trade_product tp \
     49              LEFT JOIN (\
     50                  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 \
     51                  FROM trade_product_location tpl \
     52                  JOIN gcs_location gl ON gl.id = tpl.tb_gl \
     53                  GROUP BY tpl.tb_tp\
     54              ) loc ON loc.trade_product_id = tp.id \
     55              WHERE tp.id = ? OR tp.key = ? \
     56              ORDER BY lower(tp.title) ASC, tp.id ASC;";
     57         let params_json = utils::to_params_json(vec![
     58             Value::from(lookup.to_owned()),
     59             Value::from(lookup.to_owned()),
     60         ])?;
     61         let json = self.executor().query_raw(sql, &params_json)?;
     62         serde_json::from_str(&json).map_err(SqlError::from)
     63     }
     64 
     65     pub fn trade_product_search(
     66         &self,
     67         query_terms: &[String],
     68     ) -> Result<Vec<ReplicaTradeProductSummaryRow>, SqlError> {
     69         if query_terms.is_empty() {
     70             return Ok(Vec::new());
     71         }
     72 
     73         let mut where_clauses = Vec::with_capacity(query_terms.len());
     74         let mut bind_values = Vec::<Value>::with_capacity(query_terms.len() * 5);
     75         for term in query_terms {
     76             let pattern = format!("%{}%", term.to_lowercase());
     77             where_clauses.push(
     78                 "(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 ?)"
     79                     .to_owned(),
     80             );
     81             for _ in 0..5 {
     82                 bind_values.push(Value::from(pattern.clone()));
     83             }
     84         }
     85 
     86         let sql = format!(
     87             "SELECT tp.id, tp.key, tp.category, tp.title, tp.summary, tp.qty_amt, tp.qty_amt_exact, tp.qty_unit, tp.qty_label, tp.qty_avail, tp.price_amt, tp.price_amt_exact, tp.price_currency, tp.price_qty_amt, tp.price_qty_amt_exact, tp.price_qty_unit, tp.listing_addr, tp.primary_bin_id, tp.verified_primary_bin_id, tp.notes, loc.location_primary \
     88              FROM trade_product tp \
     89              LEFT JOIN (\
     90                  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 \
     91                  FROM trade_product_location tpl \
     92                  JOIN gcs_location gl ON gl.id = tpl.tb_gl \
     93                  GROUP BY tpl.tb_tp\
     94              ) loc ON loc.trade_product_id = tp.id \
     95              WHERE {} \
     96              ORDER BY lower(tp.title) ASC, tp.id ASC;",
     97             where_clauses.join(" AND ")
     98         );
     99         let params_json = utils::to_params_json(bind_values)?;
    100         let json = self.executor().query_raw(&sql, &params_json)?;
    101         serde_json::from_str(&json).map_err(SqlError::from)
    102     }
    103 
    104     pub fn farm_unique_d_tag_by_pubkey(
    105         &self,
    106         seller_pubkey: &str,
    107     ) -> Result<Option<String>, SqlError> {
    108         let sql = "SELECT d_tag FROM farm WHERE pubkey = ? ORDER BY d_tag ASC;";
    109         let params_json = utils::to_params_json(vec![Value::from(seller_pubkey.to_owned())])?;
    110         let json = self.executor().query_raw(sql, &params_json)?;
    111         let rows: Vec<ReplicaFarmDTagRow> = serde_json::from_str(&json).map_err(SqlError::from)?;
    112         if rows.len() == 1 {
    113             Ok(Some(rows[0].d_tag.clone()))
    114         } else {
    115             Ok(None)
    116         }
    117     }
    118 
    119     pub fn nostr_event_last_created_at(&self) -> Result<Option<u64>, SqlError> {
    120         let json = self.executor().query_raw(
    121             "SELECT MAX(last_created_at) AS last_created_at FROM nostr_event_head WHERE last_created_at IS NOT NULL",
    122             "[]",
    123         )?;
    124         let rows: Vec<ReplicaEventFreshnessRow> =
    125             serde_json::from_str(&json).map_err(SqlError::from)?;
    126         Ok(rows
    127             .into_iter()
    128             .next()
    129             .and_then(|row| row.last_created_at)
    130             .and_then(|value| u64::try_from(value).ok()))
    131     }
    132 }
    133 
    134 #[cfg(test)]
    135 mod tests {
    136     use super::*;
    137     use radroots_sql_core::ExecOutcome;
    138 
    139     struct QueryExecutor {
    140         farm_rows: &'static str,
    141         product_rows: &'static str,
    142     }
    143 
    144     impl SqlExecutor for QueryExecutor {
    145         fn exec(&self, _sql: &str, _params_json: &str) -> Result<ExecOutcome, SqlError> {
    146             Ok(ExecOutcome {
    147                 changes: 0,
    148                 last_insert_id: 0,
    149             })
    150         }
    151 
    152         fn query_raw(&self, sql: &str, _params_json: &str) -> Result<String, SqlError> {
    153             if sql.contains("FROM farm") {
    154                 Ok(self.farm_rows.to_string())
    155             } else {
    156                 Ok(self.product_rows.to_string())
    157             }
    158         }
    159 
    160         fn begin(&self) -> Result<(), SqlError> {
    161             Ok(())
    162         }
    163 
    164         fn commit(&self) -> Result<(), SqlError> {
    165             Ok(())
    166         }
    167 
    168         fn rollback(&self) -> Result<(), SqlError> {
    169             Ok(())
    170         }
    171     }
    172 
    173     fn product_rows() -> &'static str {
    174         r#"[{
    175             "id":"listing-1",
    176             "key":"coffee",
    177             "category":"produce",
    178             "title":"Coffee",
    179             "summary":"Washed coffee",
    180             "qty_amt":1.0,
    181             "qty_amt_exact":"1",
    182             "qty_unit":"kg",
    183             "qty_label":null,
    184             "qty_avail":10,
    185             "price_amt":12.0,
    186             "price_amt_exact":"12",
    187             "price_currency":"USD",
    188             "price_qty_amt":1.0,
    189             "price_qty_amt_exact":"1",
    190             "price_qty_unit":"kg",
    191             "listing_addr":"30402:pubkey:AAAAAAAAAAAAAAAAAAAAAA",
    192             "primary_bin_id":"bin-1",
    193             "verified_primary_bin_id":"bin-1",
    194             "notes":null,
    195             "location_primary":"Farm"
    196         }]"#
    197     }
    198 
    199     #[test]
    200     fn trade_product_queries_and_unique_farm_lookup_cover_empty_and_multiple_rows() {
    201         let db = ReplicaSql::new(QueryExecutor {
    202             farm_rows: r#"[{"d_tag":"farm-a"},{"d_tag":"farm-b"}]"#,
    203             product_rows: product_rows(),
    204         });
    205 
    206         assert_eq!(
    207             db.trade_product_search(&[]).expect("empty search"),
    208             Vec::new()
    209         );
    210         let lookup = db.trade_product_lookup("coffee").expect("lookup");
    211         assert_eq!(lookup[0].key, "coffee");
    212         assert_eq!(
    213             db.farm_unique_d_tag_by_pubkey("seller")
    214                 .expect("farm lookup"),
    215             None
    216         );
    217 
    218         let unique_db = ReplicaSql::new(QueryExecutor {
    219             farm_rows: r#"[{"d_tag":"farm-a"}]"#,
    220             product_rows: product_rows(),
    221         });
    222         assert_eq!(
    223             unique_db
    224                 .farm_unique_d_tag_by_pubkey("seller")
    225                 .expect("farm lookup"),
    226             Some("farm-a".to_string())
    227         );
    228     }
    229 }