aranet_store/
queries.rs

1//! Query builders for readings and history.
2//!
3//! This module provides fluent query builders for filtering and paginating
4//! stored sensor data. Both [`ReadingQuery`] and [`HistoryQuery`] follow
5//! the builder pattern for ergonomic query construction.
6//!
7//! # Example
8//!
9//! ```
10//! use aranet_store::{Store, ReadingQuery, HistoryQuery};
11//! use time::{OffsetDateTime, Duration};
12//!
13//! let store = Store::open_in_memory()?;
14//! let yesterday = OffsetDateTime::now_utc() - Duration::hours(24);
15//!
16//! // Query recent readings with pagination
17//! let query = ReadingQuery::new()
18//!     .device("Aranet4 17C3C")
19//!     .since(yesterday)
20//!     .limit(50)
21//!     .offset(0);
22//!
23//! let readings = store.query_readings(&query)?;
24//!
25//! // Query all history for export
26//! let history_query = HistoryQuery::new()
27//!     .device("Aranet4 17C3C")
28//!     .oldest_first();
29//!
30//! let history = store.query_history(&history_query)?;
31//! # Ok::<(), aranet_store::Error>(())
32//! ```
33
34use time::OffsetDateTime;
35
36/// Maximum allowed limit for queries to prevent DoS via large result sets.
37/// This caps LIMIT values to prevent memory exhaustion attacks.
38pub const MAX_QUERY_LIMIT: u32 = 1_000_000;
39
40/// Fluent query builder for current readings.
41///
42/// Use this to construct queries for [`Store::query_readings`](crate::Store::query_readings).
43/// All filter methods are optional and can be chained in any order.
44///
45/// By default, queries return results ordered by `captured_at` descending
46/// (newest first).
47///
48/// # Example
49///
50/// ```
51/// use aranet_store::ReadingQuery;
52/// use time::{OffsetDateTime, Duration};
53///
54/// let now = OffsetDateTime::now_utc();
55///
56/// // Query last hour's readings for a device
57/// let query = ReadingQuery::new()
58///     .device("Aranet4 17C3C")
59///     .since(now - Duration::hours(1))
60///     .limit(100);
61///
62/// // Query with pagination
63/// let page_2 = ReadingQuery::new()
64///     .device("Aranet4 17C3C")
65///     .limit(50)
66///     .offset(50);
67///
68/// // Query oldest first (chronological order)
69/// let chronological = ReadingQuery::new()
70///     .device("Aranet4 17C3C")
71///     .oldest_first();
72/// ```
73#[derive(Debug, Default, Clone)]
74pub struct ReadingQuery {
75    /// Filter by device ID.
76    pub device_id: Option<String>,
77    /// Filter readings after this time.
78    pub since: Option<OffsetDateTime>,
79    /// Filter readings before this time.
80    pub until: Option<OffsetDateTime>,
81    /// Maximum number of results.
82    pub limit: Option<u32>,
83    /// Offset for pagination.
84    pub offset: Option<u32>,
85    /// Order by captured_at descending (newest first).
86    pub newest_first: bool,
87}
88
89impl ReadingQuery {
90    /// Create a new query with default settings.
91    ///
92    /// Default behavior:
93    /// - No device filter (all devices)
94    /// - No time range filter
95    /// - No limit (all matching records)
96    /// - Ordered by newest first
97    pub fn new() -> Self {
98        Self {
99            newest_first: true,
100            ..Default::default()
101        }
102    }
103
104    /// Filter by device ID.
105    ///
106    /// Only include readings from the specified device.
107    pub fn device(mut self, device_id: &str) -> Self {
108        self.device_id = Some(device_id.to_string());
109        self
110    }
111
112    /// Filter to readings captured at or after this time.
113    ///
114    /// Useful for querying "last N hours" or "since last sync".
115    pub fn since(mut self, time: OffsetDateTime) -> Self {
116        self.since = Some(time);
117        self
118    }
119
120    /// Filter to readings captured at or before this time.
121    ///
122    /// Use with `since()` to query a specific time range.
123    pub fn until(mut self, time: OffsetDateTime) -> Self {
124        self.until = Some(time);
125        self
126    }
127
128    /// Limit the maximum number of results returned.
129    ///
130    /// Use with `offset()` for pagination. Values are capped at `MAX_QUERY_LIMIT`
131    /// to prevent resource exhaustion.
132    pub fn limit(mut self, limit: u32) -> Self {
133        self.limit = Some(limit.min(MAX_QUERY_LIMIT));
134        self
135    }
136
137    /// Skip the first N results.
138    ///
139    /// Use with `limit()` for pagination. For example, to get page 2
140    /// with 50 items per page: `.limit(50).offset(50)`.
141    ///
142    /// Values are capped at `MAX_QUERY_LIMIT` to prevent degenerate queries.
143    pub fn offset(mut self, offset: u32) -> Self {
144        self.offset = Some(offset.min(MAX_QUERY_LIMIT));
145        self
146    }
147
148    /// Order results by oldest first (ascending by `captured_at`).
149    ///
150    /// By default, queries return newest first. Use this for chronological
151    /// ordering, useful when exporting or processing data sequentially.
152    pub fn oldest_first(mut self) -> Self {
153        self.newest_first = false;
154        self
155    }
156
157    /// Build the SQL WHERE clause and parameters.
158    pub(crate) fn build_where(&self) -> (String, Vec<Box<dyn rusqlite::ToSql>>) {
159        let mut conditions = Vec::new();
160        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
161
162        if let Some(ref device_id) = self.device_id {
163            conditions.push("device_id = ?");
164            params.push(Box::new(device_id.clone()));
165        }
166
167        if let Some(since) = self.since {
168            conditions.push("captured_at >= ?");
169            params.push(Box::new(since.unix_timestamp()));
170        }
171
172        if let Some(until) = self.until {
173            conditions.push("captured_at <= ?");
174            params.push(Box::new(until.unix_timestamp()));
175        }
176
177        let where_clause = if conditions.is_empty() {
178            String::new()
179        } else {
180            format!("WHERE {}", conditions.join(" AND "))
181        };
182
183        (where_clause, params)
184    }
185
186    /// Build the full SQL query.
187    pub(crate) fn build_sql(&self) -> String {
188        let (where_clause, _) = self.build_where();
189        let order = if self.newest_first { "DESC" } else { "ASC" };
190
191        let mut sql = format!(
192            "SELECT id, device_id, captured_at, co2, temperature, pressure, humidity, \
193             battery, status, radon, radiation_rate, radiation_total, \
194             radon_avg_24h, radon_avg_7d, radon_avg_30d \
195             FROM readings {} ORDER BY captured_at {}",
196            where_clause, order
197        );
198
199        if let Some(limit) = self.limit {
200            sql.push_str(&format!(" LIMIT {}", limit));
201        }
202
203        if let Some(offset) = self.offset {
204            sql.push_str(&format!(" OFFSET {}", offset));
205        }
206
207        sql
208    }
209}
210
211/// Fluent query builder for history records.
212///
213/// Use this to construct queries for [`Store::query_history`](crate::Store::query_history),
214/// [`Store::history_stats`](crate::Store::history_stats), and export methods.
215/// All filter methods are optional and can be chained in any order.
216///
217/// By default, queries return results ordered by `timestamp` descending
218/// (newest first).
219///
220/// # Example
221///
222/// ```
223/// use aranet_store::HistoryQuery;
224/// use time::{OffsetDateTime, Duration};
225///
226/// let now = OffsetDateTime::now_utc();
227///
228/// // Query last week's history
229/// let query = HistoryQuery::new()
230///     .device("Aranet4 17C3C")
231///     .since(now - Duration::days(7));
232///
233/// // Query specific date range for export
234/// let export_query = HistoryQuery::new()
235///     .device("Aranet4 17C3C")
236///     .since(now - Duration::days(30))
237///     .until(now - Duration::days(7))
238///     .oldest_first();
239/// ```
240#[derive(Debug, Default, Clone)]
241pub struct HistoryQuery {
242    /// Filter by device ID (optional).
243    pub device_id: Option<String>,
244    /// Include only records at or after this time (optional).
245    pub since: Option<OffsetDateTime>,
246    /// Include only records at or before this time (optional).
247    pub until: Option<OffsetDateTime>,
248    /// Maximum number of results to return (optional).
249    pub limit: Option<u32>,
250    /// Number of results to skip for pagination (optional).
251    pub offset: Option<u32>,
252    /// If true, order by timestamp descending (newest first). Default: true.
253    pub newest_first: bool,
254}
255
256impl HistoryQuery {
257    /// Create a new query with default settings.
258    ///
259    /// Default behavior:
260    /// - No device filter (all devices)
261    /// - No time range filter
262    /// - No limit (all matching records)
263    /// - Ordered by newest first
264    pub fn new() -> Self {
265        Self {
266            newest_first: true,
267            ..Default::default()
268        }
269    }
270
271    /// Filter by device ID.
272    ///
273    /// Only include history records from the specified device.
274    pub fn device(mut self, device_id: &str) -> Self {
275        self.device_id = Some(device_id.to_string());
276        self
277    }
278
279    /// Filter to records at or after this time.
280    ///
281    /// Useful for querying "last N days" or data after a specific point.
282    pub fn since(mut self, time: OffsetDateTime) -> Self {
283        self.since = Some(time);
284        self
285    }
286
287    /// Filter to records at or before this time.
288    ///
289    /// Use with `since()` to query a specific time range.
290    pub fn until(mut self, time: OffsetDateTime) -> Self {
291        self.until = Some(time);
292        self
293    }
294
295    /// Limit the maximum number of results returned.
296    ///
297    /// Use with `offset()` for pagination. Values are capped at `MAX_QUERY_LIMIT`
298    /// to prevent resource exhaustion.
299    pub fn limit(mut self, limit: u32) -> Self {
300        self.limit = Some(limit.min(MAX_QUERY_LIMIT));
301        self
302    }
303
304    /// Skip the first N results.
305    ///
306    /// Use with `limit()` for pagination. For example, to get page 3
307    /// with 100 items per page: `.limit(100).offset(200)`.
308    ///
309    /// Values are capped at `MAX_QUERY_LIMIT` to prevent degenerate queries.
310    pub fn offset(mut self, offset: u32) -> Self {
311        self.offset = Some(offset.min(MAX_QUERY_LIMIT));
312        self
313    }
314
315    /// Order results by oldest first (ascending by `timestamp`).
316    ///
317    /// By default, queries return newest first. Use this for chronological
318    /// ordering, which is useful for CSV export or time-series analysis.
319    pub fn oldest_first(mut self) -> Self {
320        self.newest_first = false;
321        self
322    }
323
324    /// Build the SQL WHERE clause and parameters.
325    pub(crate) fn build_where(&self) -> (String, Vec<Box<dyn rusqlite::ToSql>>) {
326        let mut conditions = Vec::new();
327        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
328
329        if let Some(ref device_id) = self.device_id {
330            conditions.push("device_id = ?");
331            params.push(Box::new(device_id.clone()));
332        }
333
334        if let Some(since) = self.since {
335            conditions.push("timestamp >= ?");
336            params.push(Box::new(since.unix_timestamp()));
337        }
338
339        if let Some(until) = self.until {
340            conditions.push("timestamp <= ?");
341            params.push(Box::new(until.unix_timestamp()));
342        }
343
344        let where_clause = if conditions.is_empty() {
345            String::new()
346        } else {
347            format!("WHERE {}", conditions.join(" AND "))
348        };
349
350        (where_clause, params)
351    }
352
353    /// Build a SELECT query using the configured filters, ordering, and pagination.
354    pub(crate) fn build_sql_with_select(&self, select: &str) -> String {
355        let (where_clause, _) = self.build_where();
356        let order = if self.newest_first { "DESC" } else { "ASC" };
357
358        let mut sql = format!("{select} {where_clause} ORDER BY timestamp {order}");
359
360        if let Some(limit) = self.limit {
361            sql.push_str(&format!(" LIMIT {}", limit));
362        }
363
364        if let Some(offset) = self.offset {
365            sql.push_str(&format!(" OFFSET {}", offset));
366        }
367
368        sql
369    }
370
371    /// Build the full SQL query for history rows.
372    pub(crate) fn build_sql(&self) -> String {
373        self.build_sql_with_select(
374            "SELECT id, device_id, timestamp, synced_at, co2, temperature, pressure, \
375             humidity, radon, radiation_rate, radiation_total FROM history",
376        )
377    }
378}
379
380#[cfg(test)]
381mod tests {
382    use super::*;
383    use time::macros::datetime;
384
385    // ==================== ReadingQuery Tests ====================
386
387    #[test]
388    fn test_reading_query_new_defaults() {
389        let query = ReadingQuery::new();
390        assert!(query.device_id.is_none());
391        assert!(query.since.is_none());
392        assert!(query.until.is_none());
393        assert!(query.limit.is_none());
394        assert!(query.offset.is_none());
395        assert!(query.newest_first);
396    }
397
398    #[test]
399    fn test_reading_query_default_is_different_from_new() {
400        let default_query = ReadingQuery::default();
401        let new_query = ReadingQuery::new();
402
403        // Default doesn't set newest_first, but new() does
404        assert!(!default_query.newest_first);
405        assert!(new_query.newest_first);
406    }
407
408    #[test]
409    fn test_reading_query_device_filter() {
410        let query = ReadingQuery::new().device("test-device-123");
411        assert_eq!(query.device_id, Some("test-device-123".to_string()));
412    }
413
414    #[test]
415    fn test_reading_query_since_filter() {
416        let time = datetime!(2024-01-15 10:30:00 UTC);
417        let query = ReadingQuery::new().since(time);
418        assert_eq!(query.since, Some(time));
419    }
420
421    #[test]
422    fn test_reading_query_until_filter() {
423        let time = datetime!(2024-01-15 18:30:00 UTC);
424        let query = ReadingQuery::new().until(time);
425        assert_eq!(query.until, Some(time));
426    }
427
428    #[test]
429    fn test_reading_query_limit() {
430        let query = ReadingQuery::new().limit(100);
431        assert_eq!(query.limit, Some(100));
432    }
433
434    #[test]
435    fn test_reading_query_offset() {
436        let query = ReadingQuery::new().offset(50);
437        assert_eq!(query.offset, Some(50));
438    }
439
440    #[test]
441    fn test_reading_query_oldest_first() {
442        let query = ReadingQuery::new().oldest_first();
443        assert!(!query.newest_first);
444    }
445
446    #[test]
447    fn test_reading_query_chaining() {
448        let since = datetime!(2024-01-01 00:00:00 UTC);
449        let until = datetime!(2024-12-31 23:59:59 UTC);
450
451        let query = ReadingQuery::new()
452            .device("my-device")
453            .since(since)
454            .until(until)
455            .limit(10)
456            .offset(5)
457            .oldest_first();
458
459        assert_eq!(query.device_id, Some("my-device".to_string()));
460        assert_eq!(query.since, Some(since));
461        assert_eq!(query.until, Some(until));
462        assert_eq!(query.limit, Some(10));
463        assert_eq!(query.offset, Some(5));
464        assert!(!query.newest_first);
465    }
466
467    #[test]
468    fn test_reading_query_build_where_empty() {
469        let query = ReadingQuery::new();
470        let (where_clause, params) = query.build_where();
471        assert_eq!(where_clause, "");
472        assert!(params.is_empty());
473    }
474
475    #[test]
476    fn test_reading_query_build_where_device_only() {
477        let query = ReadingQuery::new().device("test-device");
478        let (where_clause, params) = query.build_where();
479        assert_eq!(where_clause, "WHERE device_id = ?");
480        assert_eq!(params.len(), 1);
481    }
482
483    #[test]
484    fn test_reading_query_build_where_time_range() {
485        let since = datetime!(2024-01-01 00:00:00 UTC);
486        let until = datetime!(2024-12-31 23:59:59 UTC);
487
488        let query = ReadingQuery::new().since(since).until(until);
489        let (where_clause, params) = query.build_where();
490
491        assert_eq!(where_clause, "WHERE captured_at >= ? AND captured_at <= ?");
492        assert_eq!(params.len(), 2);
493    }
494
495    #[test]
496    fn test_reading_query_build_where_all_filters() {
497        let since = datetime!(2024-01-01 00:00:00 UTC);
498        let until = datetime!(2024-12-31 23:59:59 UTC);
499
500        let query = ReadingQuery::new()
501            .device("device-1")
502            .since(since)
503            .until(until);
504        let (where_clause, params) = query.build_where();
505
506        assert!(where_clause.contains("device_id = ?"));
507        assert!(where_clause.contains("captured_at >= ?"));
508        assert!(where_clause.contains("captured_at <= ?"));
509        assert_eq!(params.len(), 3);
510    }
511
512    #[test]
513    fn test_reading_query_build_sql_basic() {
514        let query = ReadingQuery::new();
515        let sql = query.build_sql();
516
517        assert!(sql.contains("SELECT"));
518        assert!(sql.contains("FROM readings"));
519        assert!(sql.contains("ORDER BY captured_at DESC"));
520        assert!(!sql.contains("WHERE"));
521        assert!(!sql.contains("LIMIT"));
522        assert!(!sql.contains("OFFSET"));
523    }
524
525    #[test]
526    fn test_reading_query_build_sql_with_limit() {
527        let query = ReadingQuery::new().limit(50);
528        let sql = query.build_sql();
529
530        assert!(sql.contains("LIMIT 50"));
531    }
532
533    #[test]
534    fn test_reading_query_build_sql_with_offset() {
535        let query = ReadingQuery::new().offset(25);
536        let sql = query.build_sql();
537
538        assert!(sql.contains("OFFSET 25"));
539    }
540
541    #[test]
542    fn test_reading_query_build_sql_oldest_first() {
543        let query = ReadingQuery::new().oldest_first();
544        let sql = query.build_sql();
545
546        assert!(sql.contains("ORDER BY captured_at ASC"));
547    }
548
549    #[test]
550    fn test_reading_query_build_sql_complete() {
551        let since = datetime!(2024-06-01 00:00:00 UTC);
552        let query = ReadingQuery::new()
553            .device("my-sensor")
554            .since(since)
555            .limit(100)
556            .offset(10)
557            .oldest_first();
558
559        let sql = query.build_sql();
560
561        assert!(sql.contains("WHERE"));
562        assert!(sql.contains("device_id = ?"));
563        assert!(sql.contains("captured_at >= ?"));
564        assert!(sql.contains("ORDER BY captured_at ASC"));
565        assert!(sql.contains("LIMIT 100"));
566        assert!(sql.contains("OFFSET 10"));
567    }
568
569    #[test]
570    fn test_reading_query_build_sql_selects_all_columns() {
571        let query = ReadingQuery::new();
572        let sql = query.build_sql();
573
574        assert!(sql.contains("id"));
575        assert!(sql.contains("device_id"));
576        assert!(sql.contains("captured_at"));
577        assert!(sql.contains("co2"));
578        assert!(sql.contains("temperature"));
579        assert!(sql.contains("pressure"));
580        assert!(sql.contains("humidity"));
581        assert!(sql.contains("battery"));
582        assert!(sql.contains("status"));
583        assert!(sql.contains("radon"));
584        assert!(sql.contains("radiation_rate"));
585        assert!(sql.contains("radiation_total"));
586        assert!(sql.contains("radon_avg_24h"));
587        assert!(sql.contains("radon_avg_7d"));
588        assert!(sql.contains("radon_avg_30d"));
589    }
590
591    // ==================== HistoryQuery Tests ====================
592
593    #[test]
594    fn test_history_query_new_defaults() {
595        let query = HistoryQuery::new();
596        assert!(query.device_id.is_none());
597        assert!(query.since.is_none());
598        assert!(query.until.is_none());
599        assert!(query.limit.is_none());
600        assert!(query.offset.is_none());
601        assert!(query.newest_first);
602    }
603
604    #[test]
605    fn test_history_query_default_is_different_from_new() {
606        let default_query = HistoryQuery::default();
607        let new_query = HistoryQuery::new();
608
609        // Default doesn't set newest_first, but new() does
610        assert!(!default_query.newest_first);
611        assert!(new_query.newest_first);
612    }
613
614    #[test]
615    fn test_history_query_device_filter() {
616        let query = HistoryQuery::new().device("aranet4-abc123");
617        assert_eq!(query.device_id, Some("aranet4-abc123".to_string()));
618    }
619
620    #[test]
621    fn test_history_query_since_filter() {
622        let time = datetime!(2024-03-15 08:00:00 UTC);
623        let query = HistoryQuery::new().since(time);
624        assert_eq!(query.since, Some(time));
625    }
626
627    #[test]
628    fn test_history_query_until_filter() {
629        let time = datetime!(2024-03-15 20:00:00 UTC);
630        let query = HistoryQuery::new().until(time);
631        assert_eq!(query.until, Some(time));
632    }
633
634    #[test]
635    fn test_history_query_limit() {
636        let query = HistoryQuery::new().limit(500);
637        assert_eq!(query.limit, Some(500));
638    }
639
640    #[test]
641    fn test_history_query_offset() {
642        let query = HistoryQuery::new().offset(200);
643        assert_eq!(query.offset, Some(200));
644    }
645
646    #[test]
647    fn test_history_query_oldest_first() {
648        let query = HistoryQuery::new().oldest_first();
649        assert!(!query.newest_first);
650    }
651
652    #[test]
653    fn test_history_query_chaining() {
654        let since = datetime!(2024-01-01 00:00:00 UTC);
655        let until = datetime!(2024-06-30 23:59:59 UTC);
656
657        let query = HistoryQuery::new()
658            .device("sensor-xyz")
659            .since(since)
660            .until(until)
661            .limit(1000)
662            .offset(100)
663            .oldest_first();
664
665        assert_eq!(query.device_id, Some("sensor-xyz".to_string()));
666        assert_eq!(query.since, Some(since));
667        assert_eq!(query.until, Some(until));
668        assert_eq!(query.limit, Some(1000));
669        assert_eq!(query.offset, Some(100));
670        assert!(!query.newest_first);
671    }
672
673    #[test]
674    fn test_history_query_clone() {
675        let query = HistoryQuery::new().device("device-1").limit(50);
676        let cloned = query.clone();
677
678        assert_eq!(cloned.device_id, query.device_id);
679        assert_eq!(cloned.limit, query.limit);
680    }
681
682    #[test]
683    fn test_reading_query_clone() {
684        let query = ReadingQuery::new().device("device-1").limit(50);
685        let cloned = query.clone();
686
687        assert_eq!(cloned.device_id, query.device_id);
688        assert_eq!(cloned.limit, query.limit);
689    }
690
691    #[test]
692    fn test_reading_query_debug() {
693        let query = ReadingQuery::new().device("test");
694        let debug_str = format!("{:?}", query);
695        assert!(debug_str.contains("ReadingQuery"));
696        assert!(debug_str.contains("test"));
697    }
698
699    #[test]
700    fn test_history_query_debug() {
701        let query = HistoryQuery::new().device("test");
702        let debug_str = format!("{:?}", query);
703        assert!(debug_str.contains("HistoryQuery"));
704        assert!(debug_str.contains("test"));
705    }
706
707    #[test]
708    fn test_reading_query_limit_zero() {
709        let query = ReadingQuery::new().limit(0);
710        let sql = query.build_sql();
711        assert!(sql.contains("LIMIT 0"));
712    }
713
714    #[test]
715    fn test_reading_query_large_pagination() {
716        // Both limit and offset are clamped to MAX_QUERY_LIMIT
717        let query = ReadingQuery::new().limit(u32::MAX).offset(u32::MAX);
718        let sql = query.build_sql();
719        assert!(sql.contains(&format!("LIMIT {}", MAX_QUERY_LIMIT)));
720        assert!(sql.contains(&format!("OFFSET {}", MAX_QUERY_LIMIT)));
721    }
722}
aranet_store/queries.rs

aranet_store/
queries.rs
