prax_sqlite/
raw.rs

1//! Legacy JSON-first SQLite query engine.
2//!
3//! This module preserves the original JSON-returning API for backward compatibility.
4//! New code should use the typed `SqliteEngine` from `engine.rs` instead.
5
6use std::collections::HashMap;
7
8use rusqlite::types::Value;
9use serde_json::Value as JsonValue;
10use tracing::{debug, instrument, trace};
11
12use prax_query::dialect::{SqlDialect, Sqlite};
13use prax_query::filter::FilterValue;
14use prax_query::types::SortOrder;
15
16use crate::error::SqliteError;
17use crate::pool::SqlitePool;
18use crate::types::filter_value_to_sqlite;
19
20/// Legacy SQLite query engine with JSON results.
21#[derive(Clone)]
22pub struct SqliteRawEngine {
23    pool: SqlitePool,
24}
25
26/// Result of a query operation (JSON-based).
27#[derive(Debug, Clone)]
28pub struct SqliteJsonRow {
29    /// The result data as JSON.
30    pub data: JsonValue,
31}
32
33impl SqliteJsonRow {
34    /// Create a new query result.
35    pub fn new(data: JsonValue) -> Self {
36        Self { data }
37    }
38
39    /// Get the result as JSON.
40    pub fn json(&self) -> &JsonValue {
41        &self.data
42    }
43
44    /// Convert to the inner JSON value.
45    pub fn into_json(self) -> JsonValue {
46        self.data
47    }
48}
49
50impl SqliteRawEngine {
51    /// Create a new SQLite engine with the given pool.
52    pub fn new(pool: SqlitePool) -> Self {
53        Self { pool }
54    }
55
56    /// Get a reference to the connection pool.
57    pub fn pool(&self) -> &SqlitePool {
58        &self.pool
59    }
60
61    /// Build a SELECT query.
62    fn build_select(
63        &self,
64        table: &str,
65        columns: &[String],
66        filters: &HashMap<String, FilterValue>,
67        sort: &[(String, SortOrder)],
68        limit: Option<u64>,
69        offset: Option<u64>,
70    ) -> (String, Vec<Value>) {
71        let mut sql = String::new();
72        let mut params: Vec<Value> = Vec::new();
73
74        // SELECT clause
75        let cols = if columns.is_empty() {
76            "*".to_string()
77        } else {
78            columns
79                .iter()
80                .map(|c| Sqlite.quote_ident(c))
81                .collect::<Vec<_>>()
82                .join(", ")
83        };
84        sql.push_str(&format!(
85            "SELECT {} FROM {}",
86            cols,
87            Sqlite.quote_ident(table)
88        ));
89
90        // WHERE clause
91        if !filters.is_empty() {
92            let mut conditions = Vec::new();
93            for (field, value) in filters {
94                match value {
95                    FilterValue::Null => {
96                        conditions.push(format!("{} IS NULL", Sqlite.quote_ident(field)));
97                    }
98                    _ => {
99                        conditions.push(format!("{} = ?", Sqlite.quote_ident(field)));
100                        params.push(filter_value_to_sqlite(value));
101                    }
102                }
103            }
104            sql.push_str(" WHERE ");
105            sql.push_str(&conditions.join(" AND "));
106        }
107
108        // ORDER BY clause
109        if !sort.is_empty() {
110            let order_parts: Vec<String> = sort
111                .iter()
112                .map(|(col, dir)| {
113                    let direction = match dir {
114                        SortOrder::Asc => "ASC",
115                        SortOrder::Desc => "DESC",
116                    };
117                    format!("{} {}", Sqlite.quote_ident(col), direction)
118                })
119                .collect();
120            sql.push_str(" ORDER BY ");
121            sql.push_str(&order_parts.join(", "));
122        }
123
124        // LIMIT and OFFSET
125        if let Some(lim) = limit {
126            sql.push_str(&format!(" LIMIT {}", lim));
127        }
128        if let Some(off) = offset {
129            sql.push_str(&format!(" OFFSET {}", off));
130        }
131
132        (sql, params)
133    }
134
135    /// Build an INSERT query.
136    fn build_insert(
137        &self,
138        table: &str,
139        data: &HashMap<String, FilterValue>,
140    ) -> (String, Vec<Value>) {
141        let mut columns = Vec::new();
142        let mut placeholders = Vec::new();
143        let mut params: Vec<Value> = Vec::new();
144
145        for (col, val) in data {
146            columns.push(Sqlite.quote_ident(col));
147            placeholders.push("?".to_string());
148            params.push(filter_value_to_sqlite(val));
149        }
150
151        let sql = format!(
152            "INSERT INTO {} ({}) VALUES ({})",
153            Sqlite.quote_ident(table),
154            columns.join(", "),
155            placeholders.join(", ")
156        );
157
158        (sql, params)
159    }
160
161    /// Build an UPDATE query.
162    fn build_update(
163        &self,
164        table: &str,
165        data: &HashMap<String, FilterValue>,
166        filters: &HashMap<String, FilterValue>,
167    ) -> (String, Vec<Value>) {
168        let mut params: Vec<Value> = Vec::new();
169
170        // SET clause
171        let set_parts: Vec<String> = data
172            .iter()
173            .map(|(col, val)| {
174                params.push(filter_value_to_sqlite(val));
175                format!("{} = ?", Sqlite.quote_ident(col))
176            })
177            .collect();
178
179        let mut sql = format!(
180            "UPDATE {} SET {}",
181            Sqlite.quote_ident(table),
182            set_parts.join(", ")
183        );
184
185        // WHERE clause
186        if !filters.is_empty() {
187            let mut conditions = Vec::new();
188            for (field, value) in filters {
189                match value {
190                    FilterValue::Null => {
191                        conditions.push(format!("{} IS NULL", Sqlite.quote_ident(field)));
192                    }
193                    _ => {
194                        conditions.push(format!("{} = ?", Sqlite.quote_ident(field)));
195                        params.push(filter_value_to_sqlite(value));
196                    }
197                }
198            }
199            sql.push_str(" WHERE ");
200            sql.push_str(&conditions.join(" AND "));
201        }
202
203        (sql, params)
204    }
205
206    /// Build a DELETE query.
207    fn build_delete(
208        &self,
209        table: &str,
210        filters: &HashMap<String, FilterValue>,
211    ) -> (String, Vec<Value>) {
212        let mut sql = format!("DELETE FROM {}", Sqlite.quote_ident(table));
213        let mut params: Vec<Value> = Vec::new();
214
215        if !filters.is_empty() {
216            let mut conditions = Vec::new();
217            for (field, value) in filters {
218                match value {
219                    FilterValue::Null => {
220                        conditions.push(format!("{} IS NULL", Sqlite.quote_ident(field)));
221                    }
222                    _ => {
223                        conditions.push(format!("{} = ?", Sqlite.quote_ident(field)));
224                        params.push(filter_value_to_sqlite(value));
225                    }
226                }
227            }
228            sql.push_str(" WHERE ");
229            sql.push_str(&conditions.join(" AND "));
230        }
231
232        (sql, params)
233    }
234
235    /// Execute a query and return multiple results.
236    #[instrument(skip(self, columns, filters, sort), fields(table = %table))]
237    pub async fn query_many(
238        &self,
239        table: &str,
240        columns: &[String],
241        filters: &HashMap<String, FilterValue>,
242        sort: &[(String, SortOrder)],
243        limit: Option<u64>,
244        offset: Option<u64>,
245    ) -> Result<Vec<SqliteJsonRow>, SqliteError> {
246        let (sql, params) = self.build_select(table, columns, filters, sort, limit, offset);
247        trace!(sql = %sql, "Executing query_many");
248
249        let conn = self.pool.get().await?;
250
251        let results = conn.query_params(&sql, params).await?;
252
253        Ok(results.into_iter().map(SqliteJsonRow::new).collect())
254    }
255
256    /// Execute a query and return a single result.
257    #[instrument(skip(self, columns, filters), fields(table = %table))]
258    pub async fn query_one(
259        &self,
260        table: &str,
261        columns: &[String],
262        filters: &HashMap<String, FilterValue>,
263    ) -> Result<SqliteJsonRow, SqliteError> {
264        let (sql, params) = self.build_select(table, columns, filters, &[], Some(1), None);
265        trace!(sql = %sql, "Executing query_one");
266
267        let conn = self.pool.get().await?;
268
269        let results = conn.query_params(&sql, params).await?;
270
271        results
272            .into_iter()
273            .next()
274            .map(SqliteJsonRow::new)
275            .ok_or_else(|| {
276                SqliteError::query(format!(
277                    "No row found in table '{}' with the given filters",
278                    table
279                ))
280            })
281    }
282
283    /// Execute a query and return an optional result.
284    #[instrument(skip(self, columns, filters), fields(table = %table))]
285    pub async fn query_optional(
286        &self,
287        table: &str,
288        columns: &[String],
289        filters: &HashMap<String, FilterValue>,
290    ) -> Result<Option<SqliteJsonRow>, SqliteError> {
291        let (sql, params) = self.build_select(table, columns, filters, &[], Some(1), None);
292        trace!(sql = %sql, "Executing query_optional");
293
294        let conn = self.pool.get().await?;
295
296        let results = conn.query_params(&sql, params).await?;
297
298        Ok(results.into_iter().next().map(SqliteJsonRow::new))
299    }
300
301    /// Execute an INSERT and return the result.
302    ///
303    /// The returned row echoes the submitted values plus the rowid of the new
304    /// row under the `id` key (skipped if an `id` value was submitted); it does
305    /// not re-read the persisted row, so database-generated defaults and
306    /// trigger effects are not reflected.
307    #[instrument(skip(self, data), fields(table = %table))]
308    pub async fn execute_insert(
309        &self,
310        table: &str,
311        data: &HashMap<String, FilterValue>,
312    ) -> Result<SqliteJsonRow, SqliteError> {
313        let (sql, params) = self.build_insert(table, data);
314        trace!(sql = %sql, "Executing insert");
315
316        let conn = self.pool.get().await?;
317
318        let last_rowid = conn.execute_insert_params(&sql, params).await?;
319
320        // Return the inserted row
321        let mut result = data.clone();
322        if !result.contains_key("id") {
323            result.insert("id".to_string(), FilterValue::Int(last_rowid));
324        }
325
326        let json = result
327            .into_iter()
328            .map(|(k, v)| (k, filter_value_to_json(&v)))
329            .collect::<serde_json::Map<_, _>>();
330
331        Ok(SqliteJsonRow::new(JsonValue::Object(json)))
332    }
333
334    /// Execute an UPDATE and return the number of affected rows.
335    #[instrument(skip(self, data, filters), fields(table = %table))]
336    pub async fn execute_update(
337        &self,
338        table: &str,
339        data: &HashMap<String, FilterValue>,
340        filters: &HashMap<String, FilterValue>,
341    ) -> Result<u64, SqliteError> {
342        let (sql, params) = self.build_update(table, data, filters);
343        trace!(sql = %sql, "Executing update");
344
345        let conn = self.pool.get().await?;
346
347        let affected = conn.execute_params(&sql, params).await?;
348
349        Ok(affected as u64)
350    }
351
352    /// Execute a DELETE and return the number of affected rows.
353    #[instrument(skip(self, filters), fields(table = %table))]
354    pub async fn execute_delete(
355        &self,
356        table: &str,
357        filters: &HashMap<String, FilterValue>,
358    ) -> Result<u64, SqliteError> {
359        let (sql, params) = self.build_delete(table, filters);
360        trace!(sql = %sql, "Executing delete");
361
362        let conn = self.pool.get().await?;
363
364        let affected = conn.execute_params(&sql, params).await?;
365
366        Ok(affected as u64)
367    }
368
369    /// Execute raw SQL and return results.
370    #[instrument(skip(self, params), fields(sql = %sql))]
371    pub async fn execute_raw(
372        &self,
373        sql: &str,
374        params: &[FilterValue],
375    ) -> Result<Vec<SqliteJsonRow>, SqliteError> {
376        debug!("Executing raw SQL");
377
378        let sqlite_params: Vec<Value> = params.iter().map(filter_value_to_sqlite).collect();
379
380        let conn = self.pool.get().await?;
381
382        let results = conn.query_params(sql, sqlite_params).await?;
383
384        Ok(results.into_iter().map(SqliteJsonRow::new).collect())
385    }
386
387    // =========================================================================
388    // Raw SQL Functions
389    // =========================================================================
390
391    /// Execute a raw SQL query using the `Sql` builder from prax-query.
392    ///
393    /// # Example
394    ///
395    /// ```rust,ignore
396    /// use prax_query::raw::Sql;
397    ///
398    /// let sql = Sql::new("SELECT * FROM users WHERE age > ")
399    ///     .bind(18)
400    ///     .push(" AND active = ")
401    ///     .bind(true);
402    ///
403    /// let results = engine.raw_sql(sql).await?;
404    /// ```
405    #[instrument(skip(self, sql))]
406    pub async fn raw_sql(
407        &self,
408        sql: prax_query::raw::Sql,
409    ) -> Result<Vec<SqliteJsonRow>, SqliteError> {
410        let (query_string, params) = sql.build();
411        debug!(sql = %query_string, "Executing raw SQL from builder");
412        self.raw_sql_query(&query_string, &params).await
413    }
414
415    /// Execute a raw SQL query string with parameters and return results.
416    ///
417    /// # Example
418    ///
419    /// ```rust,ignore
420    /// let results = engine.raw_sql_query(
421    ///     "SELECT * FROM users WHERE age > ? AND active = ?",
422    ///     &[FilterValue::Int(18), FilterValue::Bool(true)]
423    /// ).await?;
424    /// ```
425    #[instrument(skip(self, params), fields(sql = %sql))]
426    pub async fn raw_sql_query(
427        &self,
428        sql: &str,
429        params: &[FilterValue],
430    ) -> Result<Vec<SqliteJsonRow>, SqliteError> {
431        debug!("Executing raw SQL query");
432
433        let sqlite_params: Vec<Value> = params.iter().map(filter_value_to_sqlite).collect();
434
435        let conn = self.pool.get().await?;
436
437        let results = conn.query_params(sql, sqlite_params).await?;
438
439        Ok(results.into_iter().map(SqliteJsonRow::new).collect())
440    }
441
442    /// Execute a raw SQL statement and return the number of affected rows.
443    ///
444    /// Use this for INSERT, UPDATE, DELETE, or other statements that don't return rows.
445    ///
446    /// # Example
447    ///
448    /// ```rust,ignore
449    /// let affected = engine.raw_sql_execute(
450    ///     "UPDATE users SET last_login = datetime('now') WHERE id = ?",
451    ///     &[FilterValue::Int(user_id)]
452    /// ).await?;
453    /// println!("Updated {} rows", affected);
454    /// ```
455    #[instrument(skip(self, params), fields(sql = %sql))]
456    pub async fn raw_sql_execute(
457        &self,
458        sql: &str,
459        params: &[FilterValue],
460    ) -> Result<u64, SqliteError> {
461        debug!("Executing raw SQL statement");
462
463        let sqlite_params: Vec<Value> = params.iter().map(filter_value_to_sqlite).collect();
464
465        let conn = self.pool.get().await?;
466
467        let affected = conn.execute_params(sql, sqlite_params).await?;
468
469        Ok(affected as u64)
470    }
471
472    /// Execute a raw SQL query and return the first result.
473    ///
474    /// Returns an error if no rows are returned.
475    ///
476    /// # Example
477    ///
478    /// ```rust,ignore
479    /// let user = engine.raw_sql_first(
480    ///     "SELECT * FROM users WHERE id = ?",
481    ///     &[FilterValue::Int(user_id)]
482    /// ).await?;
483    /// ```
484    #[instrument(skip(self, params), fields(sql = %sql))]
485    pub async fn raw_sql_first(
486        &self,
487        sql: &str,
488        params: &[FilterValue],
489    ) -> Result<SqliteJsonRow, SqliteError> {
490        debug!("Executing raw SQL first");
491
492        let sqlite_params: Vec<Value> = params.iter().map(filter_value_to_sqlite).collect();
493
494        let conn = self.pool.get().await?;
495
496        let results = conn.query_params(sql, sqlite_params).await?;
497
498        results
499            .into_iter()
500            .next()
501            .map(SqliteJsonRow::new)
502            .ok_or_else(|| SqliteError::query("raw_sql_first returned no rows"))
503    }
504
505    /// Execute a raw SQL query and return the first result, or None if no rows.
506    ///
507    /// # Example
508    ///
509    /// ```rust,ignore
510    /// let user = engine.raw_sql_optional(
511    ///     "SELECT * FROM users WHERE email = ?",
512    ///     &[FilterValue::String("test@example.com".into())]
513    /// ).await?;
514    /// ```
515    #[instrument(skip(self, params), fields(sql = %sql))]
516    pub async fn raw_sql_optional(
517        &self,
518        sql: &str,
519        params: &[FilterValue],
520    ) -> Result<Option<SqliteJsonRow>, SqliteError> {
521        debug!("Executing raw SQL optional");
522
523        let sqlite_params: Vec<Value> = params.iter().map(filter_value_to_sqlite).collect();
524
525        let conn = self.pool.get().await?;
526
527        let results = conn.query_params(sql, sqlite_params).await?;
528
529        Ok(results.into_iter().next().map(SqliteJsonRow::new))
530    }
531
532    /// Execute a raw SQL query and return a single scalar value.
533    ///
534    /// # Example
535    ///
536    /// ```rust,ignore
537    /// let count: i64 = engine.raw_sql_scalar(
538    ///     "SELECT COUNT(*) FROM users WHERE active = ?",
539    ///     &[FilterValue::Bool(true)]
540    /// ).await?;
541    /// ```
542    #[instrument(skip(self, params), fields(sql = %sql))]
543    pub async fn raw_sql_scalar<T>(
544        &self,
545        sql: &str,
546        params: &[FilterValue],
547    ) -> Result<T, SqliteError>
548    where
549        T: for<'a> serde::Deserialize<'a>,
550    {
551        debug!("Executing raw SQL scalar");
552
553        let sqlite_params: Vec<Value> = params.iter().map(filter_value_to_sqlite).collect();
554
555        let conn = self.pool.get().await?;
556
557        let results = conn.query_params(sql, sqlite_params).await?;
558
559        let row = results
560            .into_iter()
561            .next()
562            .ok_or_else(|| SqliteError::query("raw_sql_scalar returned no rows"))?;
563
564        // Get the first column value
565        let value = row
566            .as_object()
567            .and_then(|obj| obj.values().next())
568            .ok_or_else(|| SqliteError::query("raw_sql_scalar returned empty row"))?;
569
570        serde_json::from_value(value.clone()).map_err(|e| {
571            SqliteError::deserialization(format!("failed to deserialize scalar: {}", e))
572        })
573    }
574
575    /// Execute multiple raw SQL statements in a batch.
576    ///
577    /// This is useful for running schema migrations or multiple DDL statements.
578    ///
579    /// # Example
580    ///
581    /// ```rust,ignore
582    /// engine.raw_sql_batch(r#"
583    ///     CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY);
584    ///     CREATE TABLE IF NOT EXISTS posts (id INTEGER PRIMARY KEY);
585    /// "#).await?;
586    /// ```
587    #[instrument(skip(self), fields(sql_len = %sql.len()))]
588    pub async fn raw_sql_batch(&self, sql: &str) -> Result<(), SqliteError> {
589        debug!("Executing raw SQL batch");
590
591        let conn = self.pool.get().await?;
592
593        conn.execute_batch(sql).await
594    }
595
596    /// Count rows matching the filter.
597    #[instrument(skip(self, filters), fields(table = %table))]
598    pub async fn count(
599        &self,
600        table: &str,
601        filters: &HashMap<String, FilterValue>,
602    ) -> Result<u64, SqliteError> {
603        let mut sql = format!(
604            "SELECT COUNT(*) as count FROM {}",
605            Sqlite.quote_ident(table)
606        );
607        let mut params: Vec<Value> = Vec::new();
608
609        if !filters.is_empty() {
610            let mut conditions = Vec::new();
611            for (field, value) in filters {
612                match value {
613                    FilterValue::Null => {
614                        conditions.push(format!("{} IS NULL", Sqlite.quote_ident(field)));
615                    }
616                    _ => {
617                        conditions.push(format!("{} = ?", Sqlite.quote_ident(field)));
618                        params.push(filter_value_to_sqlite(value));
619                    }
620                }
621            }
622            sql.push_str(" WHERE ");
623            sql.push_str(&conditions.join(" AND "));
624        }
625
626        trace!(sql = %sql, "Executing count");
627
628        let conn = self.pool.get().await?;
629
630        let results = conn.query_params(&sql, params).await?;
631
632        // Extract count from first row
633        let count = results
634            .first()
635            .and_then(|row| row.get("count"))
636            .and_then(|v| v.as_i64())
637            .unwrap_or(0);
638
639        Ok(count as u64)
640    }
641}
642
643/// Convert a FilterValue to JSON.
644fn filter_value_to_json(value: &FilterValue) -> JsonValue {
645    match value {
646        FilterValue::Null => JsonValue::Null,
647        FilterValue::Bool(b) => JsonValue::Bool(*b),
648        FilterValue::Int(i) => JsonValue::Number((*i).into()),
649        FilterValue::Float(f) => serde_json::Number::from_f64(*f)
650            .map(JsonValue::Number)
651            .unwrap_or(JsonValue::Null),
652        FilterValue::String(s) => JsonValue::String(s.clone()),
653        FilterValue::Json(j) => j.clone(),
654        FilterValue::List(list) => {
655            JsonValue::Array(list.iter().map(filter_value_to_json).collect())
656        }
657    }
658}
659
660#[cfg(test)]
661mod tests {
662    use super::*;
663
664    #[test]
665    fn test_quote_ident_simple() {
666        assert_eq!(Sqlite.quote_ident("users"), "\"users\"");
667        assert_eq!(Sqlite.quote_ident("user_name"), "\"user_name\"");
668        assert_eq!(Sqlite.quote_ident(""), "\"\"");
669    }
670
671    #[test]
672    fn test_quote_ident_doubles_embedded_quotes() {
673        assert_eq!(Sqlite.quote_ident("we\"ird"), "\"we\"\"ird\"");
674        assert_eq!(Sqlite.quote_ident("\"quoted\""), "\"\"\"quoted\"\"\"");
675        assert_eq!(Sqlite.quote_ident("a\"b\"c"), "\"a\"\"b\"\"c\"");
676    }
677
678    #[test]
679    fn test_filter_value_to_json() {
680        assert_eq!(filter_value_to_json(&FilterValue::Null), JsonValue::Null);
681        assert_eq!(
682            filter_value_to_json(&FilterValue::Bool(true)),
683            JsonValue::Bool(true)
684        );
685        assert_eq!(
686            filter_value_to_json(&FilterValue::Int(42)),
687            JsonValue::Number(42.into())
688        );
689        assert_eq!(
690            filter_value_to_json(&FilterValue::String("test".to_string())),
691            JsonValue::String("test".to_string())
692        );
693    }
694
695    #[test]
696    fn test_build_select_simple() {
697        let sql = "SELECT * FROM \"users\"";
698        assert!(sql.contains("SELECT"));
699        assert!(sql.contains("users"));
700    }
701
702    #[test]
703    fn test_query_result() {
704        let result = SqliteJsonRow::new(JsonValue::Object(serde_json::Map::new()));
705        assert!(result.json().is_object());
706    }
707
708    #[test]
709    fn test_query_result_into_json() {
710        let json = JsonValue::Object(serde_json::Map::new());
711        let result = SqliteJsonRow::new(json.clone());
712        assert_eq!(result.into_json(), json);
713    }
714
715    #[test]
716    fn test_sql_builder_integration() {
717        use prax_query::raw::Sql;
718
719        let sql = Sql::new("SELECT * FROM users WHERE age > ")
720            .bind(18)
721            .push(" AND active = ")
722            .bind(true);
723
724        let (query, params) = sql.build();
725        assert!(query.contains("SELECT"));
726        assert!(query.contains("users"));
727        assert_eq!(params.len(), 2);
728    }
729}
prax_sqlite/raw.rs

prax_sqlite/
raw.rs
