Skip to main content

hyprstream_metrics/storage/
duckdb.rs

1// tonic::Status is the idiomatic gRPC error type - boxing would break API
2#![allow(clippy::result_large_err)]
3
4use crate::error::DuckDbErrorWrapper;
5use crate::storage::cache::{CacheEviction, CacheManager};
6use crate::storage::view::{ViewDefinition, ViewMetadata};
7use crate::storage::{Credentials, StorageBackend};
8use async_trait::async_trait;
9use duckdb::arrow::array::{
10    Array, ArrayRef, BinaryArray, Float64Array, Int64Array, RecordBatch, StringArray,
11};
12use duckdb::arrow::datatypes::{DataType, Field, Schema};
13use duckdb::{params, Config, Connection};
14use std::collections::HashMap;
15use std::sync::Arc;
16use std::time::SystemTime;
17use tokio::sync::Mutex;
18use tonic::Status;
19
20/// DuckDB-based storage backend
21#[derive(Clone)]
22pub struct DuckDbBackend {
23    conn: Arc<Mutex<Connection>>,
24    #[allow(dead_code)]
25    connection_string: String,
26    #[allow(dead_code)]
27    options: HashMap<String, String>,
28    #[allow(dead_code)]
29    cache_manager: CacheManager,
30}
31
32impl DuckDbBackend {
33    pub fn new(
34        connection_string: String,
35        options: HashMap<String, String>,
36        ttl: Option<u64>,
37    ) -> Result<Self, Status> {
38        let config = Config::default();
39        let conn = Connection::open_with_flags(&connection_string, config)
40            .map_err(|e| Status::internal(e.to_string()))?;
41
42        // Initialize tables synchronously
43        // Note: Using VARCHAR for view_definition instead of JSON to avoid
44        // requiring the json extension. We handle JSON via serde anyway.
45        conn.execute_batch(
46            r#"
47            CREATE TABLE IF NOT EXISTS view_metadata (
48                view_name VARCHAR PRIMARY KEY,
49                source_table VARCHAR NOT NULL,
50                view_definition VARCHAR NOT NULL,
51                created_at BIGINT NOT NULL
52            );
53            "#,
54        )
55        .map_err(|e| Status::internal(format!("Failed to create tables: {e}")))?;
56
57        Ok(Self {
58            conn: Arc::new(Mutex::new(conn)),
59            connection_string,
60            options,
61            cache_manager: CacheManager::new(ttl),
62        })
63    }
64
65    pub fn new_in_memory() -> Result<Self, Status> {
66        Self::new(":memory:".to_owned(), HashMap::new(), Some(0))
67    }
68
69    async fn execute_statement(&self, sql: &str) -> Result<(), Status> {
70        let conn = self.conn.lock().await;
71        conn.execute_batch(sql)
72            .map_err(|e| Status::internal(format!("Failed to execute statement: {e}")))
73    }
74}
75
76#[async_trait]
77impl StorageBackend for DuckDbBackend {
78    async fn init(&self) -> Result<(), Status> {
79        // Tables are already created in new()
80        Ok(())
81    }
82
83    async fn prepare_sql(&self, query: &str) -> Result<Vec<u8>, Status> {
84        Ok(query.as_bytes().to_vec())
85    }
86
87    async fn query_sql(&self, statement_handle: &[u8]) -> Result<RecordBatch, Status> {
88        let sql = std::str::from_utf8(statement_handle)
89            .map_err(|e| Status::internal(format!("Invalid SQL statement: {e}")))?;
90
91        let conn = self.conn.lock().await;
92        let mut stmt = conn
93            .prepare(sql)
94            .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
95
96        // Use query_arrow to get RecordBatch directly. The stream may yield
97        // several chunks (e.g. one per radix partition of a grouped
98        // aggregate), so concatenate all of them rather than taking the first.
99        let batches: Vec<RecordBatch> = stmt
100            .query_arrow(params![])
101            .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?
102            .collect();
103
104        if let Some(first) = batches.first() {
105            let schema = first.schema();
106            duckdb::arrow::compute::concat_batches(&schema, batches.iter())
107                .map_err(|e| Status::internal(format!("Failed to concatenate result chunks: {e}")))
108        } else {
109            // Create empty batch with schema from statement
110            let schema = Arc::new(Schema::new(
111                stmt.column_names()
112                    .iter()
113                    .map(|col| {
114                        Field::new(
115                            col,
116                            match col.to_uppercase().as_str() {
117                                "INTEGER" | "BIGINT" => DataType::Int64,
118                                "DOUBLE" | "REAL" | "FLOAT" => DataType::Float64,
119                                // VARCHAR, TEXT, and all other types fallback to string
120                                _ => DataType::Utf8,
121                            },
122                            true,
123                        )
124                    })
125                    .collect::<Vec<Field>>(),
126            ));
127            let empty_arrays: Vec<ArrayRef> = schema
128                .fields()
129                .iter()
130                .map(|field| match field.data_type() {
131                    DataType::Int64 => Arc::new(Int64Array::from(Vec::<i64>::new())) as ArrayRef,
132                    DataType::Float64 => {
133                        Arc::new(Float64Array::from(Vec::<f64>::new())) as ArrayRef
134                    }
135                    _ => Arc::new(StringArray::from(Vec::<String>::new())) as ArrayRef,
136                })
137                .collect();
138            RecordBatch::try_new(schema, empty_arrays)
139                .map_err(|e| Status::internal(format!("Failed to create empty batch: {e}")))
140        }
141    }
142
143    fn new_with_options(
144        connection_string: &str,
145        options: &HashMap<String, String>,
146        credentials: Option<&Credentials>,
147    ) -> Result<Self, Status> {
148        let mut all_options = options.clone();
149        if let Some(creds) = credentials {
150            if let Some(ref username) = creds.username {
151                all_options.insert("username".to_owned(), username.clone());
152            }
153            if let Some(ref password) = creds.password {
154                all_options.insert("password".to_owned(), password.clone());
155            }
156        }
157
158        let ttl = all_options
159            .get("ttl")
160            .and_then(|s| s.parse().ok())
161            .map(|ttl| if ttl == 0 { None } else { Some(ttl) })
162            .unwrap_or(None);
163
164        Self::new(connection_string.to_owned(), all_options, ttl)
165    }
166
167    async fn create_table(&self, table_name: &str, schema: &Schema) -> Result<(), Status> {
168        let sql = crate::storage::StorageUtils::generate_create_table_sql(table_name, schema)?;
169        self.execute_statement(&sql).await
170    }
171
172    async fn execute_ddl(&self, sql: &str) -> Result<(), Status> {
173        self.execute_statement(sql).await
174    }
175
176    async fn insert_into_table(&self, table_name: &str, batch: RecordBatch) -> Result<(), Status> {
177        let mut conn = self.conn.lock().await;
178        let tx = conn
179            .transaction()
180            .map_err(|e| Status::internal(format!("Failed to start transaction: {e}")))?;
181
182        let sql =
183            crate::storage::StorageUtils::generate_insert_sql(table_name, batch.num_columns());
184
185        let mut stmt = tx
186            .prepare(&sql)
187            .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
188
189        for row_idx in 0..batch.num_rows() {
190            let mut params: Vec<Box<dyn duckdb::ToSql>> = Vec::new();
191            for col_idx in 0..batch.num_columns() {
192                let col = batch.column(col_idx);
193                match col.data_type() {
194                    DataType::Int64 => {
195                        let array = col
196                            .as_any()
197                            .downcast_ref::<Int64Array>()
198                            .ok_or_else(|| Status::internal("Failed to downcast to Int64Array"))?;
199                        if array.is_null(row_idx) {
200                            params.push(Box::new(Option::<i64>::None));
201                        } else {
202                            params.push(Box::new(array.value(row_idx)));
203                        }
204                    }
205                    DataType::Float64 => {
206                        let array =
207                            col.as_any().downcast_ref::<Float64Array>().ok_or_else(|| {
208                                Status::internal("Failed to downcast to Float64Array")
209                            })?;
210                        if array.is_null(row_idx) {
211                            params.push(Box::new(Option::<f64>::None));
212                        } else {
213                            params.push(Box::new(array.value(row_idx)));
214                        }
215                    }
216                    DataType::Utf8 => {
217                        let array = col
218                            .as_any()
219                            .downcast_ref::<StringArray>()
220                            .ok_or_else(|| Status::internal("Failed to downcast to StringArray"))?;
221                        if array.is_null(row_idx) {
222                            params.push(Box::new(Option::<String>::None));
223                        } else {
224                            params.push(Box::new(array.value(row_idx).to_owned()));
225                        }
226                    }
227                    DataType::Binary => {
228                        let array = col
229                            .as_any()
230                            .downcast_ref::<BinaryArray>()
231                            .ok_or_else(|| Status::internal("Failed to downcast to BinaryArray"))?;
232                        if array.is_null(row_idx) {
233                            params.push(Box::new(Option::<Vec<u8>>::None));
234                        } else {
235                            params.push(Box::new(array.value(row_idx).to_vec()));
236                        }
237                    }
238                    _ => return Err(Status::invalid_argument("Unsupported data type")),
239                }
240            }
241
242            let param_refs: Vec<&dyn duckdb::ToSql> =
243                params.iter().map(std::convert::AsRef::as_ref).collect();
244            stmt.execute(param_refs.as_slice())
245                .map_err(|e| Status::internal(format!("Failed to insert row: {e}")))?;
246        }
247
248        tx.commit()
249            .map_err(|e| Status::internal(format!("Failed to commit transaction: {e}")))?;
250
251        Ok(())
252    }
253
254    async fn create_view(&self, name: &str, definition: ViewDefinition) -> Result<(), Status> {
255        let mut conn = self.conn.lock().await;
256        let tx = conn
257            .transaction()
258            .map_err(|e| Status::internal(format!("Failed to start transaction: {e}")))?;
259
260        // Create SQL view
261        let create_view_sql = crate::storage::StorageUtils::generate_view_sql(name, &definition);
262        tx.execute(&create_view_sql, params![])
263            .map_err(|e| Status::internal(format!("Failed to create view: {e}")))?;
264
265        // Store view metadata
266        let metadata = ViewMetadata {
267            name: name.to_owned(),
268            definition: definition.clone(),
269            created_at: SystemTime::now(),
270        };
271
272        let definition_json = serde_json::to_string(&definition)
273            .map_err(|e| Status::internal(format!("Failed to serialize view definition: {e}")))?;
274
275        let created_at_secs = i64::try_from(
276            metadata
277                .created_at
278                .duration_since(SystemTime::UNIX_EPOCH)
279                .map_err(|e| Status::internal(format!("Failed to get timestamp: {e}")))?
280                .as_secs(),
281        )
282        .unwrap_or(i64::MAX);
283
284        tx.execute(
285            "INSERT INTO view_metadata (view_name, source_table, view_definition, created_at) VALUES (?, ?, ?, ?)",
286            params![
287                name,
288                definition.source_table,
289                definition_json,
290                created_at_secs
291            ],
292        )
293        .map_err(|e| Status::internal(format!("Failed to store view metadata: {e}")))?;
294
295        tx.commit()
296            .map_err(|e| Status::internal(format!("Failed to commit transaction: {e}")))?;
297
298        Ok(())
299    }
300
301    async fn get_view(&self, name: &str) -> Result<ViewMetadata, Status> {
302        let conn = self.conn.lock().await;
303        let mut stmt = conn
304            .prepare("SELECT * FROM view_metadata WHERE view_name = ?")
305            .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
306
307        let mut rows = stmt
308            .query_map(params![name], |row| {
309                let definition_json: String = row.get(2)?;
310                let definition: ViewDefinition =
311                    serde_json::from_str(&definition_json).map_err(|e| {
312                        duckdb::Error::InvalidParameterName(format!("Invalid view definition: {e}"))
313                    })?;
314
315                let secs = row.get::<_, i64>(3)?;
316                let created_at = SystemTime::UNIX_EPOCH
317                    + std::time::Duration::from_secs(u64::try_from(secs).unwrap_or(0));
318
319                Ok(ViewMetadata {
320                    name: row.get(0)?,
321                    definition,
322                    created_at,
323                })
324            })
325            .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?;
326
327        if let Some(row) = rows.next() {
328            row.map_err(|e| Status::internal(format!("Failed to read row: {e}")))
329        } else {
330            Err(Status::not_found(format!("View {name} not found")))
331        }
332    }
333
334    async fn list_views(&self) -> Result<Vec<String>, Status> {
335        let conn = self.conn.lock().await;
336        let mut stmt = conn
337            .prepare("SELECT view_name FROM view_metadata")
338            .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
339
340        let rows = stmt
341            .query_map(params![], |row| row.get::<_, String>(0))
342            .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?;
343
344        let mut views = Vec::new();
345        for row in rows {
346            views.push(row.map_err(|e| Status::internal(format!("Failed to read row: {e}")))?);
347        }
348
349        Ok(views)
350    }
351
352    async fn drop_view(&self, name: &str) -> Result<(), Status> {
353        let mut conn = self.conn.lock().await;
354        let tx = conn
355            .transaction()
356            .map_err(|e| Status::internal(format!("Failed to start transaction: {e}")))?;
357
358        // Drop the view
359        tx.execute(&format!("DROP VIEW IF EXISTS {name}"), params![])
360            .map_err(|e| Status::internal(format!("Failed to drop view: {e}")))?;
361
362        // Remove metadata
363        tx.execute(
364            "DELETE FROM view_metadata WHERE view_name = ?",
365            params![name],
366        )
367        .map_err(|e| Status::internal(format!("Failed to remove view metadata: {e}")))?;
368
369        tx.commit()
370            .map_err(|e| Status::internal(format!("Failed to commit transaction: {e}")))?;
371
372        Ok(())
373    }
374
375    async fn drop_table(&self, table_name: &str) -> Result<(), Status> {
376        let sql = format!("DROP TABLE IF EXISTS {table_name}");
377        self.execute_statement(&sql).await
378    }
379
380    async fn list_tables(&self) -> Result<Vec<String>, Status> {
381        let conn = self.conn.lock().await;
382        let mut stmt = conn
383            .prepare("SELECT name FROM sqlite_master WHERE type='table'")
384            .map_err(|e| Status::internal(format!("Failed to prepare statement: {e}")))?;
385
386        let rows = stmt
387            .query_map(params![], |row| row.get::<_, String>(0))
388            .map_err(|e| Status::internal(format!("Failed to execute query: {e}")))?;
389
390        let mut tables = Vec::new();
391        for row in rows {
392            tables.push(row.map_err(|e| Status::internal(format!("Failed to read row: {e}")))?);
393        }
394
395        Ok(tables)
396    }
397
398    async fn get_table_schema(&self, table_name: &str) -> Result<Arc<Schema>, Status> {
399        let conn = self.conn.lock().await;
400        let mut stmt = conn
401            .prepare(&format!("PRAGMA table_info({table_name})"))
402            .map_err(|e| Into::<Status>::into(DuckDbErrorWrapper(e)))?;
403
404        let mut fields = Vec::new();
405        let rows = stmt
406            .query_map(params![], |row| {
407                let name: String = row.get(1)?;
408                let type_str: String = row.get(2)?;
409                let nullable: bool = row.get(3)?;
410
411                let data_type = match type_str.to_uppercase().as_str() {
412                    "INTEGER" | "BIGINT" => DataType::Int64,
413                    "DOUBLE" | "REAL" => DataType::Float64,
414                    _ => DataType::Utf8,
415                };
416
417                Ok((name, data_type, !nullable))
418            })
419            .map_err(|e| Into::<Status>::into(DuckDbErrorWrapper(e)))?;
420
421        for row in rows {
422            let (name, data_type, required) =
423                row.map_err(|e| Into::<Status>::into(DuckDbErrorWrapper(e)))?;
424            fields.push(Field::new(name, data_type, required));
425        }
426
427        Ok(Arc::new(Schema::new(fields)))
428    }
429
430    async fn export_to_parquet(
431        &self,
432        table_name: &str,
433        path: &std::path::Path,
434    ) -> Result<(), Status> {
435        // Ensure parent directory exists
436        if let Some(parent) = path.parent() {
437            std::fs::create_dir_all(parent)
438                .map_err(|e| Status::internal(format!("Failed to create export directory: {e}")))?;
439        }
440
441        let sql = format!(
442            "COPY {} TO '{}' (FORMAT PARQUET)",
443            table_name,
444            path.display()
445        );
446        self.execute_statement(&sql).await
447    }
448
449    async fn import_from_parquet(
450        &self,
451        table_name: &str,
452        path: &std::path::Path,
453    ) -> Result<(), Status> {
454        if !path.exists() {
455            return Err(Status::not_found(format!(
456                "Parquet file not found: {}",
457                path.display()
458            )));
459        }
460
461        let sql = format!(
462            "COPY {} FROM '{}' (FORMAT PARQUET)",
463            table_name,
464            path.display()
465        );
466        self.execute_statement(&sql).await
467    }
468}
469
470#[async_trait]
471impl CacheEviction for DuckDbBackend {
472    async fn execute_eviction(&self, query: &str) -> Result<(), Status> {
473        self.execute_statement(query).await
474    }
475}
476
477#[cfg(test)]
478mod tests {
479    use super::*;
480
481    /// Regression: query_sql must concatenate every arrow chunk — DuckDB emits
482    /// at most STANDARD_VECTOR_SIZE (2048) rows per chunk, so a first-chunk-only
483    /// read silently truncates any result set larger than that.
484    #[tokio::test]
485    async fn test_query_sql_returns_all_rows_beyond_one_chunk() -> Result<(), Status> {
486        const ROWS: i64 = 5_000;
487        let backend = DuckDbBackend::new_in_memory()?;
488        let schema = Arc::new(Schema::new(vec![
489            Field::new("id", DataType::Int64, false),
490            Field::new("name", DataType::Utf8, true),
491        ]));
492        backend.create_table("big", &schema).await?;
493        let batch = RecordBatch::try_new(
494            schema,
495            vec![
496                Arc::new(Int64Array::from_iter_values(0..ROWS)) as ArrayRef,
497                Arc::new(StringArray::from_iter(
498                    (0..ROWS).map(|i| (i % 7 == 0).then(|| format!("n{i}"))),
499                )) as ArrayRef,
500            ],
501        )
502        .map_err(|e| Status::internal(e.to_string()))?;
503        backend.insert_into_table("big", batch).await?;
504
505        let handle = backend.prepare_sql("SELECT * FROM big").await?;
506        let result = backend.query_sql(&handle).await?;
507        assert_eq!(
508            result.num_rows() as i64,
509            ROWS,
510            "plain SELECT larger than one chunk must return every row"
511        );
512
513        // Grouped results stream one chunk per radix partition.
514        let handle = backend
515            .prepare_sql("SELECT id, COUNT(*) AS n FROM big GROUP BY id")
516            .await?;
517        let result = backend.query_sql(&handle).await?;
518        assert_eq!(
519            result.num_rows() as i64,
520            ROWS,
521            "GROUP BY must return every group"
522        );
523        Ok(())
524    }
525}