genee/datafile/
sqlite_datafile.rs

1//! Handling SQLite habit databases.
2use anyhow::{Context, Result, bail};
3use chrono::{DateTime, NaiveDate, NaiveTime};
4use std::{ffi::OsString, path::Path};
5
6use super::DiaryDataConnection;
7use rusqlite::{Connection, backup, params};
8
9struct DiaryDataSqlite {
10    connection: Connection,
11}
12
13const CURRENT_DB_VERSION: usize = 1;
14
15fn insert_version_to_db(conn: &Connection) -> Result<()> {
16    conn.execute(
17        "INSERT INTO Info (info_name, info_value) VALUES (\"version\", ?1)",
18        params![CURRENT_DB_VERSION],
19    )?;
20    Ok(())
21}
22
23fn initialize_sqlite_database(conn: &Connection, headers: &[String]) -> Result<()> {
24    conn.execute_batch(
25        "BEGIN;
26        DROP TABLE IF EXISTS Info;
27        CREATE TABLE Info(
28            info_id INTEGER PRIMARY KEY AUTOINCREMENT,
29            info_name TEXT UNIQUE NOT NULL,
30            info_value TEXT NOT NULL
31        );
32        DROP TABLE IF EXISTS Category;
33        CREATE TABLE Category(
34            category_id INTEGER PRIMARY KEY AUTOINCREMENT,
35            name TEXT NOT NULL,
36            created_at INTEGER NOT NULL,
37            hidden INTEGER NOT NULL
38        );
39        DROP TABLE IF EXISTS DateEntry;
40        CREATE TABLE DateEntry(
41            date DATE PRIMARY KEY,
42            created_at INTEGER NOT NULL
43        );
44        DROP TABLE IF EXISTS EntryToCategories;
45        CREATE TABLE EntryToCategories(
46            date INTEGER NOT NULL REFERENCES DateEntry(date) ON DELETE CASCADE,
47            category_id INTEGER NOT NULL REFERENCES Category(category_id) ON DELETE CASCADE,
48            PRIMARY KEY(category_id, date)
49        );
50        COMMIT;",
51    )?;
52    insert_version_to_db(conn)?;
53    let now = chrono::Local::now().timestamp();
54    for header in headers {
55        conn.execute(
56            "INSERT INTO Category (name, created_at, hidden) VALUES (?1, ?2, 0)",
57            params![header, now],
58        )?;
59    }
60    Ok(())
61}
62
63pub fn create_new_sqlite(path: &Path, headers: &[String]) -> Result<()> {
64    let conn = Connection::open(path).context("Could not open/create SQLite database")?;
65    initialize_sqlite_database(&conn, headers)?;
66    Ok(())
67}
68
69fn open_sqlite_database(connection: Connection) -> Result<Box<dyn DiaryDataConnection>> {
70    let data = DiaryDataSqlite { connection };
71    let db_version = data.get_db_version()?;
72    if db_version < CURRENT_DB_VERSION {
73        println!(
74            "Detected an SQLite datafile of version {}. Commencing update...",
75            db_version
76        );
77        data.update_db()?;
78    }
79    Ok(Box::new(data))
80}
81
82pub fn open_sqlite_datafile(path: &Path) -> Result<Box<dyn DiaryDataConnection>> {
83    let connection = Connection::open(path).context("Could not open SQLite database")?;
84    {
85        let mut backup_ext = OsString::from(path.extension().unwrap_or_default());
86        backup_ext.push(".bak");
87        let backup_path = path.with_extension(backup_ext);
88        let mut backup_connection =
89            Connection::open(backup_path).context("Could not open SQLite database for backup")?;
90        let backup = backup::Backup::new(&connection, &mut backup_connection)
91            .context("Could not initiate database backup")?;
92        backup
93            .run_to_completion(10, std::time::Duration::default(), None)
94            .context("Could not perform backup")?;
95    }
96    open_sqlite_database(connection)
97}
98
99fn date_to_timestamp(date: &NaiveDate) -> i64 {
100    date.and_time(NaiveTime::default()).and_utc().timestamp()
101}
102
103impl DiaryDataConnection for DiaryDataSqlite {
104    fn into_any(self: Box<Self>) -> Box<dyn std::any::Any> {
105        self
106    }
107
108    fn calculate_data_counts_per_iter(
109        &self,
110        date_ranges: &[(NaiveDate, NaiveDate)],
111    ) -> Result<Vec<Vec<usize>>> {
112        let category_ids = self.get_visible_category_ids()?;
113        let mut result = vec![];
114        for (from, to) in date_ranges {
115            result.push(self.calculate_data_counts(from, to, &category_ids)?);
116        }
117        Ok(result)
118    }
119
120    fn update_data(
121        &mut self,
122        date: &NaiveDate,
123        new_row: &[usize],
124    ) -> Result<super::SuccessfulUpdate> {
125        self.update_data_internal(&[(*date, new_row.to_vec())])
126    }
127
128    fn update_data_batch(&mut self, new_items: &[(NaiveDate, Vec<usize>)]) -> Result<()> {
129        self.update_data_internal(new_items)?;
130        Ok(())
131    }
132
133    fn get_missing_dates(
134        &self,
135        from: &Option<NaiveDate>,
136        until: &NaiveDate,
137    ) -> Result<Vec<NaiveDate>> {
138        if self.is_empty()? {
139            return Ok(vec![]);
140        }
141
142        // If no from, select the earliest date in the database
143        let from = match from {
144            Some(date) => *date,
145            None => {
146                let mut statement = self.connection.prepare("SELECT MIN(date) FROM DateEntry")?;
147                let min_date = statement.query_row([], |row| row.get(0))?;
148                DateTime::from_timestamp(min_date, 0).unwrap().date_naive()
149            }
150        };
151
152        let mut statement = self
153            .connection
154            .prepare("SELECT date FROM DateEntry WHERE date>=?1 AND date<=?2")?;
155        let from_timestamp = from.and_time(NaiveTime::default()).and_utc().timestamp();
156        let until_timestamp = until.and_time(NaiveTime::default()).and_utc().timestamp();
157        let rows = statement.query_map([from_timestamp, until_timestamp], |row| row.get(0))?;
158        let mut missing_dates = vec![];
159
160        let mut current_date = from;
161        for date_val in rows {
162            let next_present_day = DateTime::from_timestamp(date_val?, 0).unwrap().date_naive();
163            while current_date <= *until {
164                let last_date = current_date;
165                current_date += chrono::Duration::try_days(1).unwrap();
166                if next_present_day == last_date {
167                    break;
168                }
169                missing_dates.push(last_date);
170            }
171        }
172        while current_date <= *until {
173            missing_dates.push(current_date);
174            current_date += chrono::Duration::try_days(1).unwrap();
175        }
176
177        Ok(missing_dates)
178    }
179
180    fn get_header(&self) -> Result<Vec<(String, usize)>> {
181        let mut statement = self.connection.prepare(
182            "SELECT name, category_id FROM Category WHERE hidden=0 ORDER BY category_id",
183        )?;
184        let rows = statement.query_map([], |row| {
185            Ok((row.get::<usize, String>(0)?, row.get::<usize, usize>(1)?))
186        })?;
187        let mut header = vec![];
188        for row in rows {
189            header.push(row?);
190        }
191        Ok(header)
192    }
193
194    fn get_row(&self, date: &NaiveDate) -> Result<Option<Vec<usize>>> {
195        Ok(self.get_rows(date, date)?.pop().unwrap())
196    }
197
198    fn get_rows(&self, from: &NaiveDate, until: &NaiveDate) -> Result<Vec<Option<Vec<usize>>>> {
199        let mut statement = self.connection.prepare(
200            "SELECT date, group_concat(coalesce(category_id, 'EMPTY'), ';') FROM DateEntry
201                LEFT JOIN EntryToCategories USING(date)
202                WHERE date>=?1 AND date<=?2
203                    AND (category_id ISNULL
204                        OR 0=(SELECT hidden FROM Category WHERE EntryToCategories.category_id=Category.category_id))
205                GROUP BY date
206                ORDER BY date DESC")?;
207
208        let mut rows =
209            statement.query(params![date_to_timestamp(from), date_to_timestamp(until)])?;
210        let mut results = vec![];
211        let mut current_date = *until;
212        while current_date >= *from {
213            if let Some(row) = rows.next()? {
214                let timestamp_s: i64 = row.get(0)?;
215                let date = DateTime::from_timestamp(timestamp_s, 0)
216                    .unwrap()
217                    .date_naive();
218
219                while date < current_date {
220                    results.push(None);
221                    current_date -= chrono::Duration::try_days(1).unwrap();
222                }
223                let row_data: String = row.get(1)?;
224                if row_data == "EMPTY" {
225                    results.push(Some(vec![]));
226                } else {
227                    let row_data_parsed = row_data
228                        .split(';')
229                        .map(|id| id.parse::<usize>().unwrap())
230                        .collect();
231                    results.push(Some(row_data_parsed));
232                }
233            } else {
234                results.push(None);
235            }
236            current_date -= chrono::Duration::try_days(1).unwrap();
237        }
238        Ok(results)
239    }
240
241    fn is_empty(&self) -> Result<bool> {
242        let mut statement = self.connection.prepare("SELECT COUNT(*) FROM DateEntry")?;
243        let count: usize = statement.query_row([], |row| row.get(0))?;
244        Ok(count == 0)
245    }
246
247    fn get_date_range(&self) -> Result<(NaiveDate, NaiveDate)> {
248        if self.is_empty()? {
249            bail!("Cannot get date range, datafile is empty")
250        }
251
252        let mut statement = self
253            .connection
254            .prepare("SELECT MIN(date), MAX(date) FROM DateEntry")?;
255        let mut rows = statement.query([])?;
256        let row = rows.next()?.unwrap();
257        let min_date = DateTime::from_timestamp(row.get(0)?, 0)
258            .unwrap()
259            .date_naive();
260        let max_date = DateTime::from_timestamp(row.get(1)?, 0)
261            .unwrap()
262            .date_naive();
263
264        Ok((min_date, max_date))
265    }
266
267    fn add_category(&self, name: &str) -> Result<super::AddCategoryResult> {
268        let mut statement = self
269            .connection
270            .prepare("SELECT category_id, hidden FROM Category WHERE name=(?1)")?;
271        let mut rows = statement.query(params![name])?;
272
273        if let Some(row) = rows.next()? {
274            let category_id: usize = row.get(0)?;
275            let hidden = 0usize != row.get::<usize, usize>(1)?;
276
277            if hidden {
278                let mut statement = self
279                    .connection
280                    .prepare("UPDATE Category SET hidden=0 WHERE category_id=(?1)")?;
281                statement.execute(params![category_id])?;
282                Ok(super::AddCategoryResult::Unhide)
283            } else {
284                Ok(super::AddCategoryResult::AlreadyPresent)
285            }
286        } else {
287            let mut statement = self
288                .connection
289                .prepare("INSERT INTO Category (name, created_at, hidden) VALUES (?1, ?2, 0)")?;
290            let now = chrono::Local::now().timestamp();
291            statement.execute(params![name, now])?;
292            Ok(super::AddCategoryResult::AddedNew)
293        }
294    }
295
296    fn hide_category(&self, name: &str) -> Result<super::HideCategoryResult> {
297        let mut statement = self
298            .connection
299            .prepare("SELECT category_id, hidden FROM Category WHERE name=(?1)")?;
300        let mut rows = statement.query(params![name])?;
301        if let Some(row) = rows.next()? {
302            let category_id: usize = row.get(0)?;
303            let hidden = 0usize != row.get::<usize, usize>(1)?;
304            if hidden {
305                Ok(super::HideCategoryResult::AlreadyHidden)
306            } else {
307                let mut statement = self
308                    .connection
309                    .prepare("UPDATE Category SET hidden=1 WHERE category_id=(?1)")?;
310                statement.execute(params![category_id])?;
311                Ok(super::HideCategoryResult::Hidden)
312            }
313        } else {
314            Ok(super::HideCategoryResult::NonExistingCategory)
315        }
316    }
317
318    fn get_most_frequent_daily_data(
319        &self,
320        from: &Option<NaiveDate>,
321        until: &NaiveDate,
322        max_count: Option<usize>,
323    ) -> Result<Vec<(Vec<usize>, usize)>> {
324        let from_timestamp = from
325            .and_then(|from_date| Some(date_to_timestamp(&from_date)))
326            .unwrap_or_default();
327        let until_timestamp = date_to_timestamp(until);
328        let max_count = max_count.unwrap_or(usize::MAX);
329
330        let mut statement = self.connection.prepare(
331        "SELECT concat_categories, COUNT(date) FROM (
332            SELECT date, group_concat(category_id, ';') AS concat_categories FROM EntryToCategories WHERE date>=(?1) AND date<=(?2)
333                AND 0=(SELECT hidden FROM Category WHERE EntryToCategories.category_id=Category.category_id)
334            GROUP BY date
335        ) GROUP BY concat_categories ORDER BY COUNT(date) DESC LIMIT (?3)
336        ")?;
337        let rows =
338            statement.query_map(params![from_timestamp, until_timestamp, max_count], |row| {
339                Ok((
340                    row.get::<usize, String>(0).unwrap(),
341                    row.get::<usize, usize>(1).unwrap(),
342                ))
343            })?;
344        Ok(rows
345            .into_iter()
346            .map(|row| {
347                let (cat_ids, count) = row.unwrap();
348                let cat_ids = cat_ids
349                    .split(';')
350                    .map(|val| val.parse::<usize>().unwrap())
351                    .collect();
352                (cat_ids, count)
353            })
354            .collect())
355    }
356}
357
358impl DiaryDataSqlite {
359    fn calculate_data_counts(
360        &self,
361        from: &NaiveDate,
362        to: &NaiveDate,
363        category_ids: &[usize],
364    ) -> Result<Vec<usize>> {
365        let mut result = vec![];
366        for &cat_id in category_ids {
367            let mut statement = self.connection.prepare(
368                "SELECT COUNT(*) FROM EntryToCategories WHERE category_id=?1 AND date<=?2 AND date>=?3",
369            )?;
370            let from_timestamp = date_to_timestamp(from);
371            let to_timestamp = date_to_timestamp(to);
372            let count = statement
373                .query_row(params![cat_id, from_timestamp, to_timestamp], |row| {
374                    row.get(0)
375                })?;
376            result.push(count);
377        }
378        Ok(result)
379    }
380
381    fn update_data_internal(
382        &mut self,
383        new_items: &[(NaiveDate, Vec<usize>)],
384    ) -> Result<super::SuccessfulUpdate> {
385        let mut statement = self.connection.prepare("BEGIN")?;
386        statement.execute([])?;
387        let mut deleted_date_entries = 0;
388
389        for (date, new_category_ids) in new_items {
390            // Remove entry in DateEntry if exists
391            let mut statement = self
392                .connection
393                .prepare("DELETE FROM DateEntry WHERE date=?1")?;
394            let date_timestamp = date_to_timestamp(date);
395            deleted_date_entries += statement.execute([date_timestamp])?;
396
397            // Add entry in DateEntry
398            let now = chrono::Local::now().timestamp();
399            let mut statement = self
400                .connection
401                .prepare("INSERT INTO DateEntry (date, created_at) VALUES (?1, ?2)")?;
402            statement.execute(params![date_timestamp, now])?;
403
404            // Add new associations
405            let mut statement = self
406                .connection
407                .prepare("INSERT INTO EntryToCategories (date, category_id) VALUES (?1, ?2)")?;
408            for id in new_category_ids {
409                statement.execute(params![date_timestamp, id])?;
410            }
411        }
412
413        let mut statement = self.connection.prepare("COMMIT")?;
414        statement.execute([])?;
415
416        if deleted_date_entries == 0 {
417            Ok(super::SuccessfulUpdate::AddedNew)
418        } else {
419            Ok(super::SuccessfulUpdate::ReplacedExisting)
420        }
421    }
422
423    fn get_db_version(&self) -> Result<usize> {
424        if let Ok(mut statement) = self
425            .connection
426            .prepare("SELECT info_value FROM Info WHERE info_name=\"version\"")
427        {
428            let version: Result<String, rusqlite::Error> =
429                statement.query_row([], |row| row.get(0));
430            version
431                .map(|str| Ok(str.parse().unwrap_or(0)))
432                .unwrap_or(Ok(0))
433        } else {
434            Ok(0)
435        }
436    }
437
438    fn update_db_to_v1(&self) -> Result<()> {
439        println!("- Updating SQLite datafile to version 1...");
440        self.connection.execute_batch(
441            "BEGIN;
442            DROP TABLE IF EXISTS Info;
443            CREATE TABLE Info(
444                info_id INTEGER PRIMARY KEY AUTOINCREMENT,
445                info_name TEXT UNIQUE NOT NULL,
446                info_value TEXT NOT NULL
447            );
448            ALTER TABLE Category ADD COLUMN hidden INTEGER NOT NULL DEFAULT 0;
449            COMMIT;",
450        )?;
451        insert_version_to_db(&self.connection)?;
452        println!("- Success");
453        Ok(())
454    }
455
456    fn update_db(&self) -> Result<()> {
457        self.update_db_to_v1()?;
458        Ok(())
459    }
460
461    fn get_visible_category_ids(&self) -> Result<Vec<usize>> {
462        let mut statement = self
463            .connection
464            .prepare("SELECT category_id FROM Category WHERE hidden=0 ORDER BY category_id")?;
465        let rows = statement.query_map([], |row| row.get(0))?;
466
467        // Ordered list of all category IDs in the database
468        let mut category_ids: Vec<usize> = vec![];
469        for id in rows {
470            category_ids.push(id?);
471        }
472        Ok(category_ids)
473    }
474}
475
476#[cfg(test)]
477mod tests {
478    use super::*;
479
480    #[test]
481    fn current_database_version() {
482        let conn = Connection::open_in_memory().unwrap();
483        initialize_sqlite_database(
484            &conn,
485            &[String::from("AA"), String::from("BBB"), String::from("CCA")],
486        )
487        .unwrap();
488        let datafile = open_sqlite_database(conn).unwrap();
489
490        assert_eq!(
491            CURRENT_DB_VERSION,
492            datafile
493                .into_any()
494                .downcast::<DiaryDataSqlite>()
495                .unwrap()
496                .get_db_version()
497                .unwrap()
498        );
499    }
500
501    #[test]
502    fn database_update() {
503        let conn = Connection::open_in_memory().unwrap();
504        conn.execute_batch(
505            "BEGIN;
506                DROP TABLE IF EXISTS Info;
507                DROP TABLE IF EXISTS Category;
508                CREATE TABLE Category(
509                    category_id INTEGER PRIMARY KEY AUTOINCREMENT,
510                    name TEXT NOT NULL,
511                    created_at INTEGER NOT NULL
512                );
513                DROP TABLE IF EXISTS DateEntry;
514                CREATE TABLE DateEntry(
515                    date DATE PRIMARY KEY,
516                    created_at INTEGER NOT NULL
517                );
518                DROP TABLE IF EXISTS EntryToCategories;
519                CREATE TABLE EntryToCategories(
520                    date INTEGER NOT NULL REFERENCES DateEntry(date) ON DELETE CASCADE,
521                    category_id INTEGER NOT NULL REFERENCES Category(category_id) ON DELETE CASCADE,
522                    PRIMARY KEY(category_id, date)
523                );
524                INSERT INTO Category (name, created_at) VALUES (\"test_category\", 101);
525                INSERT INTO DateEntry (date, created_at) VALUES (123, 101);
526                INSERT INTO EntryToCategories (date, category_id) VALUES (123, 1);
527                COMMIT;",
528        )
529        .unwrap();
530        let datafile = open_sqlite_database(conn).unwrap();
531
532        assert_eq!(
533            CURRENT_DB_VERSION,
534            datafile
535                .into_any()
536                .downcast::<DiaryDataSqlite>()
537                .unwrap()
538                .get_db_version()
539                .unwrap()
540        );
541    }
542
543    #[test]
544    fn test_sqlite() {
545        let conn = Connection::open_in_memory().unwrap();
546        initialize_sqlite_database(
547            &conn,
548            &[String::from("AA"), String::from("BBB"), String::from("CCA")],
549        )
550        .unwrap();
551        let mut datafile = open_sqlite_database(conn).unwrap();
552        datafile
553            .update_data(&NaiveDate::from_ymd_opt(2023, 2, 4).unwrap(), &[2])
554            .unwrap();
555        datafile
556            .update_data(&NaiveDate::from_ymd_opt(2023, 3, 3).unwrap(), &[2])
557            .unwrap();
558        datafile
559            .update_data(&NaiveDate::from_ymd_opt(2023, 2, 7).unwrap(), &[3])
560            .unwrap();
561        let missing_dates = datafile
562            .get_missing_dates(&None, &NaiveDate::from_ymd_opt(2023, 2, 10).unwrap())
563            .unwrap();
564        assert_eq!(
565            missing_dates,
566            vec![
567                NaiveDate::from_ymd_opt(2023, 2, 5).unwrap(),
568                NaiveDate::from_ymd_opt(2023, 2, 6).unwrap(),
569                NaiveDate::from_ymd_opt(2023, 2, 8).unwrap(),
570                NaiveDate::from_ymd_opt(2023, 2, 9).unwrap(),
571                NaiveDate::from_ymd_opt(2023, 2, 10).unwrap(),
572            ]
573        );
574        let data_counts = datafile
575            .calculate_data_counts_per_iter(&[(
576                NaiveDate::from_ymd_opt(2023, 3, 3).unwrap(),
577                NaiveDate::from_ymd_opt(2023, 2, 3).unwrap(),
578            )])
579            .unwrap();
580        assert_eq!(data_counts, vec![vec![0, 2, 1]]);
581
582        let (min_date, max_date) = datafile.get_date_range().unwrap();
583        assert_eq!(min_date, NaiveDate::from_ymd_opt(2023, 2, 4).unwrap());
584        assert_eq!(max_date, NaiveDate::from_ymd_opt(2023, 3, 3).unwrap());
585        let data_at = datafile
586            .get_row(&NaiveDate::from_ymd_opt(2023, 2, 7).unwrap())
587            .unwrap();
588        assert_eq!(Some(vec![3]), data_at);
589    }
590
591    #[test]
592    fn add_category() {
593        use crate::datafile::AddCategoryResult;
594
595        let conn = Connection::open_in_memory().unwrap();
596        initialize_sqlite_database(
597            &conn,
598            &[String::from("AA"), String::from("BBB"), String::from("CCA")],
599        )
600        .unwrap();
601        let datafile = open_sqlite_database(conn).unwrap();
602        let result = datafile.add_category("BBB").unwrap();
603        assert_eq!(AddCategoryResult::AlreadyPresent, result);
604        let result = datafile.add_category("DDD").unwrap();
605        assert_eq!(AddCategoryResult::AddedNew, result);
606
607        let header = datafile.get_header().unwrap();
608        assert_eq!(
609            vec![
610                (String::from("AA"), 1usize),
611                (String::from("BBB"), 2usize),
612                (String::from("CCA"), 3usize),
613                (String::from("DDD"), 4usize)
614            ],
615            header
616        );
617    }
618
619    #[test]
620    fn hide_category() {
621        use crate::datafile::{AddCategoryResult, HideCategoryResult};
622
623        let conn = Connection::open_in_memory().unwrap();
624        initialize_sqlite_database(
625            &conn,
626            &[String::from("AA"), String::from("BBB"), String::from("CCA")],
627        )
628        .unwrap();
629        let datafile = open_sqlite_database(conn).unwrap();
630
631        let result = datafile.hide_category("DDD").unwrap();
632        assert_eq!(HideCategoryResult::NonExistingCategory, result);
633
634        let result = datafile.hide_category("AA").unwrap();
635        assert_eq!(HideCategoryResult::Hidden, result);
636
637        let header = datafile.get_header().unwrap();
638        assert_eq!(
639            vec![(String::from("BBB"), 2), (String::from("CCA"), 3)],
640            header
641        );
642
643        let result = datafile.hide_category("AA").unwrap();
644        assert_eq!(HideCategoryResult::AlreadyHidden, result);
645
646        let header = datafile.get_header().unwrap();
647        assert_eq!(
648            vec![(String::from("BBB"), 2), (String::from("CCA"), 3)],
649            header
650        );
651
652        let result = datafile.add_category("AA").unwrap();
653        assert_eq!(AddCategoryResult::Unhide, result);
654
655        let header = datafile.get_header().unwrap();
656        assert_eq!(
657            vec![
658                (String::from("AA"), 1),
659                (String::from("BBB"), 2),
660                (String::from("CCA"), 3)
661            ],
662            header
663        );
664    }
665
666    #[test]
667    fn get_most_frequent_daily_data() {
668        let conn = Connection::open_in_memory().unwrap();
669        initialize_sqlite_database(
670            &conn,
671            &[String::from("AA"), String::from("BBB"), String::from("CCA")],
672        )
673        .unwrap();
674        let mut datafile = open_sqlite_database(conn).unwrap();
675        datafile
676            .update_data(&NaiveDate::from_ymd_opt(2023, 2, 4).unwrap(), &[2])
677            .unwrap();
678        datafile
679            .update_data(&NaiveDate::from_ymd_opt(2023, 2, 6).unwrap(), &[1, 3])
680            .unwrap();
681        datafile
682            .update_data(&NaiveDate::from_ymd_opt(2023, 2, 7).unwrap(), &[3])
683            .unwrap();
684        datafile
685            .update_data(&NaiveDate::from_ymd_opt(2023, 2, 8).unwrap(), &[3])
686            .unwrap();
687        datafile
688            .update_data(&NaiveDate::from_ymd_opt(2023, 2, 9).unwrap(), &[3])
689            .unwrap();
690
691        let most_frequent_days = datafile
692            .get_most_frequent_daily_data(
693                &NaiveDate::from_ymd_opt(2023, 2, 6),
694                &NaiveDate::from_ymd_opt(2023, 2, 8).unwrap(),
695                Some(3usize),
696            )
697            .unwrap();
698
699        assert_eq!(
700            most_frequent_days,
701            vec![(vec![3], 2usize), (vec![1, 3], 1usize)]
702        );
703    }
704
705    #[test]
706    fn test_get_rows() {
707        let conn = Connection::open_in_memory().unwrap();
708        initialize_sqlite_database(&conn, &[String::from("AA"), String::from("BBB")]).unwrap();
709        let mut datafile = open_sqlite_database(conn).unwrap();
710        datafile
711            .update_data_batch(&[
712                (NaiveDate::from_ymd_opt(2024, 2, 1).unwrap(), vec![1]),
713                (NaiveDate::from_ymd_opt(2024, 2, 2).unwrap(), vec![2]),
714                (NaiveDate::from_ymd_opt(2024, 2, 4).unwrap(), vec![1, 2]),
715                (NaiveDate::from_ymd_opt(2024, 2, 5).unwrap(), vec![]),
716            ])
717            .unwrap();
718
719        let rows = datafile
720            .get_rows(
721                &NaiveDate::from_ymd_opt(2024, 1, 30).unwrap(),
722                &NaiveDate::from_ymd_opt(2024, 2, 6).unwrap(),
723            )
724            .unwrap();
725
726        assert_eq!(
727            rows,
728            vec![
729                None,
730                Some(vec![]),
731                Some(vec![1, 2]),
732                None,
733                Some(vec![2]),
734                Some(vec![1]),
735                None,
736                None,
737            ]
738        );
739    }
740}
genee/datafile/sqlite_datafile.rs

genee/datafile/
sqlite_datafile.rs
