kobo_db_tools/
parser.rs

1use crate::{
2    get_bookmarks, AppEvent, AppEventKind, Book, Bookmark, Brightness, BrightnessEvent,
3    BrightnessHistory, ChargeCycle, ChargeCycleMetrics, CorrelatedAnalysis, CorrelatedSession,
4    DictionaryWord, NaturalLightHistory, OrphanEvents, ReadingSession, ReadingSessions,
5};
6use chrono::{DateTime, Duration, Utc};
7use rusqlite::{params_from_iter, Connection, OpenFlags};
8use std::collections::{HashMap, HashSet};
9use std::path::Path;
10use std::str::FromStr;
11use thiserror::Error;
12use uuid::Uuid;
13
14#[derive(Debug, Error)]
15pub enum ParseError {
16    #[error("Event is not valid")]
17    InvalidEventType,
18    #[error("Error during session completation")]
19    SessionCompletionFailed,
20    #[error("Error during deserialize")]
21    DeserializationError,
22}
23
24#[derive(serde::Deserialize, Clone)]
25struct ReadingSessionAttributes {
26    progress: String,
27    volumeid: Option<String>,
28    title: Option<String>,
29    #[serde(rename = "attribution")]
30    author: Option<String>,
31}
32
33#[derive(serde::Deserialize)]
34struct LeaveContentMetrics {
35    #[serde(rename = "ButtonPressCount")]
36    button_press_count: usize,
37    #[serde(rename = "SecondsRead")]
38    seconds_read: usize,
39    #[serde(rename = "PagesTurned")]
40    pages_turned: usize,
41}
42
43#[derive(serde::Deserialize)]
44struct LightAttributes {
45    #[serde(rename = "Method")]
46    method: String,
47}
48
49#[derive(serde::Deserialize)]
50struct LightMetrics {
51    #[serde(alias = "NewNaturalLight")]
52    #[serde(alias = "NewBrightness")]
53    new_light: u8,
54}
55
56#[derive(serde::Deserialize)]
57struct DictionaryAttributes {
58    #[serde(rename = "Dictionary")]
59    lang: String,
60    #[serde(rename = "Word")]
61    word: String,
62}
63
64struct TimedDictionaryWord {
65    timestamp: DateTime<Utc>,
66    word: DictionaryWord,
67}
68
69#[derive(Debug, Clone, Copy, PartialEq, Eq)]
70pub enum ParseOption {
71    All,
72    ReadingSessions,
73    DictionaryLookups,
74    BrightnessHistory,
75    NaturalLightHistory,
76    Bookmarks,
77    AppStart,
78    PluggedIn,
79}
80
81#[derive(Debug, Default)]
82pub struct EventAnalysis {
83    pub sessions: Option<ReadingSessions>,
84    pub terms: Option<HashMap<DictionaryWord, usize>>,
85    pub brightness_history: Option<BrightnessHistory>,
86    pub natural_light_history: Option<NaturalLightHistory>,
87    pub bookmarks: Option<Vec<Bookmark>>,
88    pub books: Option<Vec<Book>>,
89    pub app_events: Option<Vec<AppEvent>>,
90}
91
92pub struct Parser;
93
94impl Parser {
95    pub fn parse_events(db: &Connection, option: ParseOption) -> rusqlite::Result<EventAnalysis> {
96        let mut analysis = EventAnalysis::default();
97        let event_types = event_types_for_option(option);
98        let include_bookmarks = matches!(option, ParseOption::All | ParseOption::Bookmarks);
99        let include_sessions = matches!(option, ParseOption::All | ParseOption::ReadingSessions);
100        let include_dictionary =
101            matches!(option, ParseOption::All | ParseOption::DictionaryLookups);
102        let include_brightness =
103            matches!(option, ParseOption::All | ParseOption::BrightnessHistory);
104        let include_natural_light =
105            matches!(option, ParseOption::All | ParseOption::NaturalLightHistory);
106        let include_app_events = matches!(
107            option,
108            ParseOption::All | ParseOption::AppStart | ParseOption::PluggedIn
109        );
110
111        if include_bookmarks {
112            analysis.bookmarks = Some(get_bookmarks(db)?);
113        }
114
115        if !event_types.is_empty() {
116            let q = build_event_query(event_types.len());
117
118            let mut stmt = db.prepare(&q)?;
119            let mut rows = stmt.query(params_from_iter(event_types.iter().copied()))?;
120
121            let mut current_session: Option<ReadingSession> = None;
122            let mut sessions_vec = ReadingSessions::new();
123            let mut terms_map = HashMap::new();
124            let mut brightness_hist = BrightnessHistory::new();
125            let mut natural_light_hist = NaturalLightHistory::new();
126            let mut volume_ids_to_query = HashSet::new();
127            let mut books_from_events = HashMap::new();
128            let mut app_events = Vec::new();
129
130            while let Some(row) = rows.next()? {
131                let event_id: String = row.get("Id")?;
132                let event_type: String = row.get("Type")?;
133                let ts_str: String = row.get("Timestamp")?;
134                let ts = parse_timestamp(&ts_str, 2)?;
135
136                match event_type.as_str() {
137                    "OpenContent" | "LeaveContent" if include_sessions => {
138                        let attr_json: String = row.get("Attributes")?;
139                        let attr: ReadingSessionAttributes = from_json(&attr_json, 1)?;
140                        let progress = attr.progress.parse::<u8>().unwrap_or(0);
141
142                        match (&attr.volumeid, &attr.title, &attr.author) {
143                            (None, Some(title), Some(author)) => {
144                                books_from_events.entry(title.clone()).or_insert_with(|| {
145                                    Book::new(author.clone(), title.clone(), None, String::new())
146                                });
147                            }
148                            (Some(volume_id), None, _) => {
149                                volume_ids_to_query.insert(volume_id.clone());
150                            }
151                            _ => {}
152                        }
153
154                        let metrics = if event_type == "LeaveContent" {
155                            let metr_json: String = row.get("Metrics")?;
156                            Some(from_json::<LeaveContentMetrics>(&metr_json, 2)?)
157                        } else {
158                            None
159                        };
160
161                        match handle_reading_session_event(
162                            &event_type,
163                            &event_id,
164                            &mut current_session,
165                            ts,
166                            progress,
167                            &attr,
168                            metrics,
169                        ) {
170                            Ok(Some(session)) => sessions_vec.add_session(session),
171                            Ok(None) => {}
172                            Err(e) => eprintln!("Errore evento {}: {:?}", &event_id, e),
173                        }
174                    }
175                    "DictionaryLookup" if include_dictionary => {
176                        let session_id = current_session.as_ref().map(|s| s.id);
177                        let attr_json: String = row.get("Attributes")?;
178                        *terms_map
179                            .entry(on_dictionary_lookup(&attr_json, session_id)?)
180                            .or_insert(0) += 1;
181                    }
182                    "BrightnessAdjusted" if include_brightness => {
183                        let attr_json: String = row.get("Attributes")?;
184                        let metr_json: String = row.get("Metrics")?;
185                        let event = on_light_adjusted(&attr_json, &metr_json, ts)?;
186                        brightness_hist.insert(event);
187                    }
188                    "NaturalLightAdjusted" if include_natural_light => {
189                        let attr_json: String = row.get("Attributes")?;
190                        let metr_json: String = row.get("Metrics")?;
191                        let event = on_light_adjusted(&attr_json, &metr_json, ts)?;
192                        natural_light_hist.insert(event);
193                    }
194                    "AppStart" | "PluggedIn" if include_app_events => {
195                        let attr_json: Option<String> = row.get("Attributes")?;
196                        let attributes = parse_optional_json_value(attr_json, 1)?;
197                        let kind = match event_type.as_str() {
198                            "AppStart" => AppEventKind::AppStart,
199                            "PluggedIn" => AppEventKind::PluggedIn,
200                            _ => continue,
201                        };
202                        app_events.push(AppEvent::new(kind, ts, attributes));
203                    }
204                    _ => {
205                        eprintln!("Unknown event: {}", event_type);
206                    }
207                }
208            }
209
210            let mut books_from_db = get_books_by_volume_id(db, &volume_ids_to_query)?;
211            books_from_db.extend(books_from_events);
212            analysis.books = Some(books_from_db.values().cloned().collect());
213
214            for session in sessions_vec.get_mut_sessions() {
215                if let Some(volume_id) = &session.volume_id {
216                    if let Some(book) = books_from_db.get(volume_id) {
217                        session.book_title = Some(book.title.clone());
218                    }
219                }
220            }
221
222            if include_sessions {
223                analysis.sessions = Some(sessions_vec);
224            }
225            if include_dictionary {
226                analysis.terms = Some(terms_map);
227            }
228            if include_brightness {
229                analysis.brightness_history = Some(brightness_hist);
230            }
231            if include_natural_light {
232                analysis.natural_light_history = Some(natural_light_hist);
233            }
234            if include_app_events {
235                analysis.app_events = Some(app_events);
236            }
237        }
238        Ok(analysis)
239    }
240
241    pub fn parse_correlated(db: &Connection) -> rusqlite::Result<CorrelatedAnalysis> {
242        const TOLERANCE_SECONDS: i64 = 30;
243        let tolerance = Duration::seconds(TOLERANCE_SECONDS);
244        let event_types = event_types_for_option(ParseOption::All);
245        let q = build_event_query(event_types.len());
246
247        let mut stmt = db.prepare(&q)?;
248        let mut rows = stmt.query(params_from_iter(event_types.iter().copied()))?;
249
250        let mut current_session: Option<ReadingSession> = None;
251        let mut sessions_vec = ReadingSessions::new();
252        let mut dictionary_events = Vec::new();
253        let mut brightness_events = Vec::new();
254        let mut natural_light_events = Vec::new();
255        let mut app_events = Vec::new();
256        let mut volume_ids_to_query = HashSet::new();
257        let mut books_from_events = HashMap::new();
258
259        while let Some(row) = rows.next()? {
260            let event_id: String = row.get("Id")?;
261            let event_type: String = row.get("Type")?;
262            let ts_str: String = row.get("Timestamp")?;
263            let ts = parse_timestamp(&ts_str, 2)?;
264
265            match event_type.as_str() {
266                "OpenContent" | "LeaveContent" => {
267                    let attr_json: String = row.get("Attributes")?;
268                    let attr: ReadingSessionAttributes = from_json(&attr_json, 1)?;
269                    let progress = attr.progress.parse::<u8>().unwrap_or(0);
270
271                    match (&attr.volumeid, &attr.title, &attr.author) {
272                        (None, Some(title), Some(author)) => {
273                            books_from_events.entry(title.clone()).or_insert_with(|| {
274                                Book::new(author.clone(), title.clone(), None, String::new())
275                            });
276                        }
277                        (Some(volume_id), None, _) => {
278                            volume_ids_to_query.insert(volume_id.clone());
279                        }
280                        _ => {}
281                    }
282
283                    let metrics = if event_type == "LeaveContent" {
284                        let metr_json: String = row.get("Metrics")?;
285                        Some(from_json::<LeaveContentMetrics>(&metr_json, 2)?)
286                    } else {
287                        None
288                    };
289
290                    match handle_reading_session_event(
291                        &event_type,
292                        &event_id,
293                        &mut current_session,
294                        ts,
295                        progress,
296                        &attr,
297                        metrics,
298                    ) {
299                        Ok(Some(session)) => sessions_vec.add_session(session),
300                        Ok(None) => {}
301                        Err(e) => eprintln!("Errore evento {}: {:?}", &event_id, e),
302                    }
303                }
304                "DictionaryLookup" => {
305                    let attr_json: String = row.get("Attributes")?;
306                    let word = on_dictionary_lookup(&attr_json, None)?;
307                    dictionary_events.push(TimedDictionaryWord {
308                        timestamp: ts,
309                        word,
310                    });
311                }
312                "BrightnessAdjusted" => {
313                    let attr_json: String = row.get("Attributes")?;
314                    let metr_json: String = row.get("Metrics")?;
315                    let event = on_light_adjusted(&attr_json, &metr_json, ts)?;
316                    brightness_events.push(event);
317                }
318                "NaturalLightAdjusted" => {
319                    let attr_json: String = row.get("Attributes")?;
320                    let metr_json: String = row.get("Metrics")?;
321                    let event = on_light_adjusted(&attr_json, &metr_json, ts)?;
322                    natural_light_events.push(event);
323                }
324                "AppStart" | "PluggedIn" => {
325                    let attr_json: Option<String> = row.get("Attributes")?;
326                    let attributes = parse_optional_json_value(attr_json, 1)?;
327                    let kind = match event_type.as_str() {
328                        "AppStart" => AppEventKind::AppStart,
329                        "PluggedIn" => AppEventKind::PluggedIn,
330                        _ => continue,
331                    };
332                    app_events.push(AppEvent::new(kind, ts, attributes));
333                }
334                _ => {}
335            }
336        }
337
338        let mut books_from_db = get_books_by_volume_id(db, &volume_ids_to_query)?;
339        books_from_db.extend(books_from_events);
340
341        for session in sessions_vec.get_mut_sessions() {
342            if let Some(volume_id) = &session.volume_id {
343                if let Some(book) = books_from_db.get(volume_id) {
344                    session.book_title = Some(book.title.clone());
345                }
346            }
347        }
348
349        let sessions = std::mem::take(sessions_vec.get_mut_sessions());
350        let mut correlated_sessions: Vec<CorrelatedSession> =
351            sessions.into_iter().map(CorrelatedSession::new).collect();
352
353        let mut orphan_dictionary = Vec::new();
354        for timed in dictionary_events {
355            if let Some(index) =
356                find_session_index(&correlated_sessions, timed.timestamp, tolerance)
357            {
358                let session_id = correlated_sessions[index].session.id;
359                let word = DictionaryWord::new(
360                    timed.word.term().to_string(),
361                    timed.word.lang().to_string(),
362                    Some(session_id),
363                );
364                correlated_sessions[index].dictionary.push(word);
365            } else {
366                orphan_dictionary.push(DictionaryWord::new(
367                    timed.word.term().to_string(),
368                    timed.word.lang().to_string(),
369                    None,
370                ));
371            }
372        }
373
374        let mut orphan_brightness = Vec::new();
375        for event in brightness_events {
376            if let Some(index) =
377                find_session_index(&correlated_sessions, event.timestamp, tolerance)
378            {
379                correlated_sessions[index].brightness.push(event);
380            } else {
381                orphan_brightness.push(event);
382            }
383        }
384
385        let mut orphan_natural_light = Vec::new();
386        for event in natural_light_events {
387            if let Some(index) =
388                find_session_index(&correlated_sessions, event.timestamp, tolerance)
389            {
390                correlated_sessions[index].natural_light.push(event);
391            } else {
392                orphan_natural_light.push(event);
393            }
394        }
395
396        let mut orphan_app_events = Vec::new();
397        for event in app_events {
398            if let Some(index) =
399                find_session_index(&correlated_sessions, event.timestamp, tolerance)
400            {
401                correlated_sessions[index].app_events.push(event);
402            } else {
403                orphan_app_events.push(event);
404            }
405        }
406
407        let orphans = OrphanEvents {
408            dictionary: orphan_dictionary,
409            brightness: orphan_brightness,
410            natural_light: orphan_natural_light,
411            app_events: orphan_app_events,
412        };
413
414        let mut all_app_events: Vec<AppEvent> = correlated_sessions
415            .iter()
416            .flat_map(|session| session.app_events.iter().cloned())
417            .collect();
418        all_app_events.extend(orphans.app_events.iter().cloned());
419
420        let cycles = build_charge_cycles(&correlated_sessions, &all_app_events);
421        let mut app_start_counts_by_day = HashMap::new();
422        for event in all_app_events
423            .iter()
424            .filter(|event| event.kind == AppEventKind::AppStart)
425        {
426            let day = event.timestamp.date_naive();
427            *app_start_counts_by_day.entry(day).or_insert(0) += 1;
428        }
429
430        Ok(CorrelatedAnalysis {
431            sessions: correlated_sessions,
432            orphans,
433            cycles,
434            app_start_counts_by_day,
435        })
436    }
437    pub fn parse_from_str<P: AsRef<Path>>(
438        path: P,
439        option: ParseOption,
440    ) -> rusqlite::Result<EventAnalysis> {
441        let path_ref = path.as_ref();
442        let conn = Connection::open(path_ref).or_else(|err| {
443            Connection::open_with_flags(path_ref, OpenFlags::SQLITE_OPEN_READ_ONLY).map_err(|_| err)
444        })?;
445        Self::parse_events(&conn, option)
446    }
447}
448
449fn handle_reading_session_event(
450    event_type: &str,
451    event_id: &str,
452    current_session: &mut Option<ReadingSession>,
453    ts: DateTime<Utc>,
454    progress: u8,
455    attr: &ReadingSessionAttributes,
456    metrics: Option<LeaveContentMetrics>,
457) -> Result<Option<ReadingSession>, ParseError> {
458    match event_type {
459        "OpenContent" => {
460            *current_session = Some(ReadingSession::new(
461                ts,
462                progress,
463                attr.title.clone(),
464                attr.volumeid.clone(),
465                event_id.to_string(),
466            ));
467            Ok(None)
468        }
469        "LeaveContent" => {
470            let mut session = current_session
471                .take()
472                .ok_or(ParseError::SessionCompletionFailed)?;
473            let m = metrics.ok_or(ParseError::SessionCompletionFailed)?;
474            if session
475                .complete_session(
476                    ts,
477                    progress,
478                    m.button_press_count as u64,
479                    m.seconds_read as u64,
480                    m.pages_turned as u64,
481                    event_id.to_string(),
482                )
483                .is_err()
484            {
485                *current_session = Some(session);
486                return Err(ParseError::SessionCompletionFailed);
487            }
488            Ok(Some(session))
489        }
490        _ => Err(ParseError::InvalidEventType),
491    }
492}
493
494fn on_dictionary_lookup(
495    attr_json: &str,
496    session_id: Option<Uuid>,
497) -> rusqlite::Result<DictionaryWord> {
498    let attr: DictionaryAttributes = from_json(attr_json, 1)?;
499    Ok(DictionaryWord::new(attr.word, attr.lang, session_id))
500}
501
502fn on_light_adjusted(
503    attr_json: &str,
504    metr_json: &str,
505    ts: DateTime<Utc>,
506) -> rusqlite::Result<BrightnessEvent> {
507    let attributes: LightAttributes = from_json(attr_json, 1)?;
508    let metrics: LightMetrics = from_json(metr_json, 1)?;
509    let brightness = Brightness::new(attributes.method, metrics.new_light);
510    Ok(BrightnessEvent::new(brightness, ts))
511}
512
513fn from_json<T: serde::de::DeserializeOwned>(
514    json: &str,
515    column_index: usize,
516) -> rusqlite::Result<T> {
517    serde_json::from_str(json).map_err(|e| {
518        rusqlite::Error::FromSqlConversionFailure(
519            column_index,
520            rusqlite::types::Type::Text,
521            Box::new(e),
522        )
523    })
524}
525
526fn parse_timestamp(ts: &str, column_index: usize) -> rusqlite::Result<DateTime<Utc>> {
527    DateTime::<Utc>::from_str(ts).map_err(|e| {
528        rusqlite::Error::FromSqlConversionFailure(
529            column_index,
530            rusqlite::types::Type::Text,
531            Box::new(e),
532        )
533    })
534}
535
536fn parse_optional_json_value(
537    json: Option<String>,
538    column_index: usize,
539) -> rusqlite::Result<Option<serde_json::Value>> {
540    match json {
541        None => Ok(None),
542        Some(raw) => {
543            let trimmed = raw.trim();
544            if trimmed.is_empty() {
545                return Ok(None);
546            }
547            serde_json::from_str(trimmed).map(Some).map_err(|e| {
548                rusqlite::Error::FromSqlConversionFailure(
549                    column_index,
550                    rusqlite::types::Type::Text,
551                    Box::new(e),
552                )
553            })
554        }
555    }
556}
557
558fn session_contains(session: &ReadingSession, ts: DateTime<Utc>, tolerance: Duration) -> bool {
559    match session.time_end {
560        Some(end) => ts >= session.time_start - tolerance && ts <= end + tolerance,
561        None => false,
562    }
563}
564
565fn find_session_index(
566    sessions: &[CorrelatedSession],
567    ts: DateTime<Utc>,
568    tolerance: Duration,
569) -> Option<usize> {
570    sessions
571        .iter()
572        .position(|session| session_contains(&session.session, ts, tolerance))
573}
574
575fn session_in_cycle(
576    session: &ReadingSession,
577    start: DateTime<Utc>,
578    end: Option<DateTime<Utc>>,
579) -> bool {
580    match end {
581        Some(end) => session.time_start >= start && session.time_start < end,
582        None => session.time_start >= start,
583    }
584}
585
586fn app_event_in_cycle(event: &AppEvent, start: DateTime<Utc>, end: Option<DateTime<Utc>>) -> bool {
587    match end {
588        Some(end) => event.timestamp >= start && event.timestamp < end,
589        None => event.timestamp >= start,
590    }
591}
592
593fn compute_cycle_metrics(
594    sessions: &[CorrelatedSession],
595    app_events: &[AppEvent],
596) -> ChargeCycleMetrics {
597    let total_seconds_read = sessions
598        .iter()
599        .map(|session| session.session.seconds_read.unwrap_or(0))
600        .sum();
601    let total_pages = sessions
602        .iter()
603        .map(|session| session.session.pages_turned.unwrap_or(0))
604        .sum();
605    let total_button_presses = sessions
606        .iter()
607        .map(|session| session.session.button_press_count.unwrap_or(0))
608        .sum();
609    let dictionary_lookups = sessions
610        .iter()
611        .map(|session| session.dictionary.len())
612        .sum();
613    let brightness_events = sessions
614        .iter()
615        .map(|session| session.brightness.len() + session.natural_light.len())
616        .sum();
617    let app_starts = app_events
618        .iter()
619        .filter(|event| event.kind == AppEventKind::AppStart)
620        .count();
621    ChargeCycleMetrics {
622        total_seconds_read,
623        total_pages,
624        total_button_presses,
625        dictionary_lookups,
626        brightness_events,
627        app_starts,
628    }
629}
630
631fn build_charge_cycles(
632    sessions: &[CorrelatedSession],
633    app_events: &[AppEvent],
634) -> Vec<ChargeCycle> {
635    let mut plug_events: Vec<AppEvent> = app_events
636        .iter()
637        .filter(|event| event.kind == AppEventKind::PluggedIn)
638        .cloned()
639        .collect();
640    plug_events.sort_by_key(|event| event.timestamp);
641
642    let mut cycles = Vec::new();
643    for (index, plug_event) in plug_events.iter().enumerate() {
644        let start = plug_event.timestamp;
645        let end = plug_events.get(index + 1).map(|event| event.timestamp);
646        let cycle_sessions: Vec<CorrelatedSession> = sessions
647            .iter()
648            .filter(|session| session_in_cycle(&session.session, start, end))
649            .cloned()
650            .collect();
651        let cycle_app_events: Vec<AppEvent> = app_events
652            .iter()
653            .filter(|event| app_event_in_cycle(event, start, end))
654            .cloned()
655            .collect();
656        let metrics = compute_cycle_metrics(&cycle_sessions, &cycle_app_events);
657        cycles.push(ChargeCycle {
658            start,
659            end,
660            sessions: cycle_sessions,
661            app_events: cycle_app_events,
662            metrics,
663        });
664    }
665    cycles
666}
667
668fn event_types_for_option(option: ParseOption) -> Vec<&'static str> {
669    match option {
670        ParseOption::All => vec![
671            "OpenContent",
672            "LeaveContent",
673            "DictionaryLookup",
674            "BrightnessAdjusted",
675            "NaturalLightAdjusted",
676            "AppStart",
677            "PluggedIn",
678        ],
679        ParseOption::ReadingSessions => vec!["OpenContent", "LeaveContent"],
680        ParseOption::DictionaryLookups => vec!["DictionaryLookup"],
681        ParseOption::BrightnessHistory => vec!["BrightnessAdjusted"],
682        ParseOption::NaturalLightHistory => vec!["NaturalLightAdjusted"],
683        ParseOption::AppStart => vec!["AppStart"],
684        ParseOption::PluggedIn => vec!["PluggedIn"],
685        ParseOption::Bookmarks => Vec::new(),
686    }
687}
688
689fn build_event_query(event_types_len: usize) -> String {
690    let placeholders = std::iter::repeat_n("?", event_types_len)
691        .collect::<Vec<_>>()
692        .join(", ");
693    format!(
694        "SELECT Id, Type, Timestamp, Attributes, Metrics FROM AnalyticsEvents WHERE Type IN ({}) ORDER BY Timestamp ASC;",
695        placeholders
696    )
697}
698
699fn get_books_by_volume_id(
700    db: &Connection,
701    volume_ids: &HashSet<String>,
702) -> rusqlite::Result<HashMap<String, Book>> {
703    let mut books = HashMap::new();
704    if volume_ids.is_empty() {
705        return Ok(books);
706    }
707    let mut stmt = db.prepare(
708        "SELECT BookID, Title, Attribution as Authors FROM content WHERE ContentType=6 AND (ContentID = ?1 OR BookID = ?1)",
709    )?;
710
711    for volume_id in volume_ids {
712        let mut rows = stmt.query([volume_id])?;
713        if let Some(row) = rows.next()? {
714            let title: String = row.get("Title")?;
715            let authors: String = row.get("Authors")?;
716            let book_id: Option<String> = row.get("BookID")?;
717            let book_id = book_id.unwrap_or_else(|| volume_id.clone());
718            books.insert(volume_id.clone(), Book::new(authors, title, None, book_id));
719        }
720    }
721
722    Ok(books)
723}
724
725#[cfg(test)]
726mod tests {
727    use super::{ParseOption, Parser};
728    use crate::AppEventKind;
729    use chrono::NaiveDate;
730    use rusqlite::Connection;
731
732    fn setup_test_db() -> Connection {
733        let conn = Connection::open_in_memory().unwrap();
734        conn.execute_batch(
735            "CREATE TABLE AnalyticsEvents (\n                Id TEXT PRIMARY KEY,\n                Type TEXT NOT NULL,\n                Timestamp TEXT NOT NULL,\n                Attributes TEXT,\n                Metrics TEXT\n            );\n            CREATE TABLE content (\n                ContentID TEXT PRIMARY KEY,\n                ContentType INTEGER,\n                Title TEXT,\n                Attribution TEXT,\n                BookID TEXT\n            );\n            CREATE TABLE Bookmark (\n                BookmarkID TEXT PRIMARY KEY,\n                Text TEXT,\n                VolumeID TEXT,\n                Color INTEGER,\n                ChapterProgress REAL,\n                DateCreated TEXT,\n                DateModified TEXT\n            );",
736        )
737        .unwrap();
738        conn
739    }
740
741    #[test]
742    fn test_parse_events_all() {
743        let db = setup_test_db();
744
745        // Insert sample data
746        db.execute(
747            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
748            [
749                "session1_open",
750                "OpenContent",
751                "2023-01-01T10:00:00Z",
752                "{\"progress\":\"0\",\"volumeid\":\"book1\"}", ""
753            ],
754        ).unwrap();
755        db.execute(
756            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
757            [
758                "session1_leave",
759                "LeaveContent",
760                "2023-01-01T10:05:00Z",
761                "{\"progress\":\"10\",\"volumeid\":\"book1\"}", "{\"ButtonPressCount\":10,\"SecondsRead\":300,\"PagesTurned\":5}"
762            ],
763        ).unwrap();
764        db.execute(
765            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
766            [
767                "dict_lookup1",
768                "DictionaryLookup",
769                "2023-01-01T10:01:00Z",
770                "{\"Dictionary\":\"en\",\"Word\":\"test\"}", ""
771            ],
772        ).unwrap();
773        db.execute(
774            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
775            [
776                "brightness_adj1",
777                "BrightnessAdjusted",
778                "2023-01-01T10:02:00Z",
779                "{\"Method\":\"manual\"}", "{\"NewBrightness\":50}"
780            ],
781        ).unwrap();
782        db.execute(
783            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
784            [
785                "natural_light_adj1",
786                "NaturalLightAdjusted",
787                "2023-01-01T10:03:00Z",
788                "{\"Method\":\"auto\"}", "{\"NewNaturalLight\":70}"
789            ],
790        ).unwrap();
791        db.execute(
792            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
793            [
794                "app_start1",
795                "AppStart",
796                "2023-01-01T09:59:00Z",
797                "{\"app\":\"nickel\"}",
798                ""
799            ],
800        ).unwrap();
801        db.execute(
802            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
803            [
804                "plugged_in1",
805                "PluggedIn",
806                "2023-01-01T10:04:00Z",
807                "",
808                ""
809            ],
810        ).unwrap();
811
812        db.execute(
813            "INSERT INTO content (ContentID, Title, ContentType, Attribution, BookID) VALUES (?, ?, ?, ?, ?)",
814            ["book1", "Book One", "6", "Author One", "book1"],
815        )
816        .unwrap();
817        db.execute(
818            "INSERT INTO Bookmark (BookmarkID, Text, VolumeID, Color, ChapterProgress, DateCreated, DateModified) VALUES (?, ?, ?, ?, ?, ?, ?)",
819            ["bookmark1", "Some text", "book1", "1", "0.5", "2023-01-01T10:06:00Z", "2023-01-01T10:06:00Z"],
820        ).unwrap();
821
822        let analysis = Parser::parse_events(&db, ParseOption::All).unwrap();
823
824        assert!(analysis.sessions.is_some());
825        assert_eq!(analysis.sessions.unwrap().sessions_count(), 1);
826
827        assert!(analysis.terms.is_some());
828        assert_eq!(analysis.terms.unwrap().len(), 1);
829
830        assert!(analysis.brightness_history.is_some());
831        assert_eq!(analysis.brightness_history.unwrap().events.len(), 1);
832
833        assert!(analysis.natural_light_history.is_some());
834        assert_eq!(analysis.natural_light_history.unwrap().events.len(), 1);
835
836        assert!(analysis.bookmarks.is_some());
837        assert_eq!(analysis.bookmarks.unwrap().len(), 1);
838
839        assert!(analysis.books.is_some());
840        assert_eq!(analysis.books.unwrap().len(), 1);
841
842        assert!(analysis.app_events.is_some());
843        let app_events = analysis.app_events.unwrap();
844        assert_eq!(app_events.len(), 2);
845        assert!(app_events
846            .iter()
847            .any(|event| event.kind == AppEventKind::AppStart));
848        assert!(app_events
849            .iter()
850            .any(|event| event.kind == AppEventKind::PluggedIn));
851    }
852
853    #[test]
854    fn test_parse_events_reading_sessions() {
855        let db = setup_test_db();
856        db.execute(
857            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
858            [
859                "session1_open",
860                "OpenContent",
861                "2023-01-01T10:00:00Z",
862                "{\"progress\":\"0\",\"volumeid\":\"book1\"}", ""
863            ],
864        ).unwrap();
865        db.execute(
866            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
867            [
868                "session1_leave",
869                "LeaveContent",
870                "2023-01-01T10:05:00Z",
871                "{\"progress\":\"10\",\"volumeid\":\"book1\"}", "{\"ButtonPressCount\":10,\"SecondsRead\":300,\"PagesTurned\":5}"
872            ],
873        ).unwrap();
874
875        db.execute(
876            "INSERT INTO content (ContentID, Title, ContentType, Attribution, BookID) VALUES (?, ?, ?, ?, ?)",
877            ["book1", "The Real Book Title", "6", "Author One", "book1"],
878        )
879        .unwrap();
880
881        let analysis = Parser::parse_events(&db, ParseOption::ReadingSessions).unwrap();
882
883        assert!(analysis.sessions.is_some());
884        let sessions = analysis.sessions.unwrap();
885        assert_eq!(sessions.sessions_count(), 1);
886        let session = sessions.get_sessions().first().unwrap();
887        assert_eq!(session.book_title.as_deref(), Some("The Real Book Title"));
888        assert!(analysis.terms.is_none());
889        assert!(analysis.brightness_history.is_none());
890        assert!(analysis.natural_light_history.is_none());
891        assert!(analysis.bookmarks.is_none());
892        assert!(analysis.books.is_some());
893        assert_eq!(analysis.books.unwrap().len(), 1);
894        assert!(analysis.app_events.is_none());
895    }
896
897    #[test]
898    fn test_parse_events_bookmarks() {
899        let db = setup_test_db();
900        db.execute(
901            "INSERT INTO content (ContentID, Title, ContentType, Attribution, BookID) VALUES (?, ?, ?, ?, ?)",
902            ["book1", "Book One", "6", "Author One", "book1"],
903        )
904        .unwrap();
905        db.execute(
906            "INSERT INTO Bookmark (BookmarkID, Text, VolumeID, Color, ChapterProgress, DateCreated, DateModified) VALUES (?, ?, ?, ?, ?, ?, ?)",
907            ["bookmark1", "Some text", "book1", "1", "0.5", "2023-01-01T10:06:00Z", "2023-01-01T10:06:00Z"],
908        ).unwrap();
909
910        let analysis = Parser::parse_events(&db, ParseOption::Bookmarks).unwrap();
911
912        assert!(analysis.sessions.is_none());
913        assert!(analysis.terms.is_none());
914        assert!(analysis.brightness_history.is_none());
915        assert!(analysis.natural_light_history.is_none());
916        assert!(analysis.bookmarks.is_some());
917        assert_eq!(analysis.bookmarks.unwrap().len(), 1);
918        assert!(analysis.books.is_none());
919        assert!(analysis.app_events.is_none());
920    }
921
922    #[test]
923    fn test_parse_events_app_start() {
924        let db = setup_test_db();
925        db.execute(
926            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
927            [
928                "app_start1",
929                "AppStart",
930                "2023-01-01T09:59:00Z",
931                "{\"app\":\"nickel\"}",
932                ""
933            ],
934        ).unwrap();
935        db.execute(
936            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
937            [
938                "plugged_in1",
939                "PluggedIn",
940                "2023-01-01T10:04:00Z",
941                "",
942                ""
943            ],
944        ).unwrap();
945
946        let analysis = Parser::parse_events(&db, ParseOption::AppStart).unwrap();
947        assert!(analysis.app_events.is_some());
948        let app_events = analysis.app_events.unwrap();
949        assert_eq!(app_events.len(), 1);
950        assert_eq!(app_events[0].kind, AppEventKind::AppStart);
951    }
952
953    #[test]
954    fn test_parse_events_plugged_in() {
955        let db = setup_test_db();
956        db.execute(
957            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
958            [
959                "app_start1",
960                "AppStart",
961                "2023-01-01T09:59:00Z",
962                "{\"app\":\"nickel\"}",
963                ""
964            ],
965        ).unwrap();
966        db.execute(
967            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
968            [
969                "plugged_in1",
970                "PluggedIn",
971                "2023-01-01T10:04:00Z",
972                "",
973                ""
974            ],
975        ).unwrap();
976
977        let analysis = Parser::parse_events(&db, ParseOption::PluggedIn).unwrap();
978        assert!(analysis.app_events.is_some());
979        let app_events = analysis.app_events.unwrap();
980        assert_eq!(app_events.len(), 1);
981        assert_eq!(app_events[0].kind, AppEventKind::PluggedIn);
982    }
983
984    #[test]
985    fn test_parse_correlated_basic() {
986        let db = setup_test_db();
987        db.execute(
988            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
989            [
990                "session1_open",
991                "OpenContent",
992                "2023-01-01T10:00:00Z",
993                "{\"progress\":\"0\",\"volumeid\":\"book1\"}",
994                "",
995            ],
996        )
997        .unwrap();
998        db.execute(
999            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
1000            [
1001                "session1_leave",
1002                "LeaveContent",
1003                "2023-01-01T10:10:00Z",
1004                "{\"progress\":\"10\",\"volumeid\":\"book1\"}",
1005                "{\"ButtonPressCount\":10,\"SecondsRead\":600,\"PagesTurned\":5}",
1006            ],
1007        )
1008        .unwrap();
1009        db.execute(
1010            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
1011            [
1012                "dict_lookup1",
1013                "DictionaryLookup",
1014                "2023-01-01T10:02:00Z",
1015                "{\"Dictionary\":\"en\",\"Word\":\"test\"}",
1016                "",
1017            ],
1018        )
1019        .unwrap();
1020        db.execute(
1021            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
1022            [
1023                "dict_lookup2",
1024                "DictionaryLookup",
1025                "2023-01-01T11:00:00Z",
1026                "{\"Dictionary\":\"en\",\"Word\":\"orphan\"}",
1027                "",
1028            ],
1029        )
1030        .unwrap();
1031        db.execute(
1032            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
1033            [
1034                "brightness_adj1",
1035                "BrightnessAdjusted",
1036                "2023-01-01T10:03:00Z",
1037                "{\"Method\":\"manual\"}",
1038                "{\"NewBrightness\":50}",
1039            ],
1040        )
1041        .unwrap();
1042        db.execute(
1043            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
1044            [
1045                "natural_light_adj1",
1046                "NaturalLightAdjusted",
1047                "2023-01-01T10:04:00Z",
1048                "{\"Method\":\"auto\"}",
1049                "{\"NewNaturalLight\":70}",
1050            ],
1051        )
1052        .unwrap();
1053        db.execute(
1054            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
1055            [
1056                "plugged_in1",
1057                "PluggedIn",
1058                "2023-01-01T09:50:00Z",
1059                "",
1060                "",
1061            ],
1062        )
1063        .unwrap();
1064        db.execute(
1065            "INSERT INTO AnalyticsEvents (Id, Type, Timestamp, Attributes, Metrics) VALUES (?, ?, ?, ?, ?)",
1066            [
1067                "app_start1",
1068                "AppStart",
1069                "2023-01-01T10:00:10Z",
1070                "{\"app\":\"nickel\"}",
1071                "",
1072            ],
1073        )
1074        .unwrap();
1075
1076        let analysis = Parser::parse_correlated(&db).unwrap();
1077
1078        assert_eq!(analysis.sessions.len(), 1);
1079        let session = &analysis.sessions[0];
1080        assert_eq!(session.dictionary.len(), 1);
1081        assert_eq!(session.brightness.len(), 1);
1082        assert_eq!(session.natural_light.len(), 1);
1083        assert_eq!(session.app_events.len(), 1);
1084
1085        assert_eq!(analysis.orphans.dictionary.len(), 1);
1086        assert_eq!(analysis.orphans.app_events.len(), 1);
1087
1088        assert_eq!(analysis.cycles.len(), 1);
1089        let cycle = &analysis.cycles[0];
1090        assert_eq!(cycle.metrics.total_seconds_read, 600);
1091        assert_eq!(cycle.metrics.total_pages, 5);
1092        assert_eq!(cycle.metrics.total_button_presses, 10);
1093        assert_eq!(cycle.metrics.dictionary_lookups, 1);
1094        assert_eq!(cycle.metrics.brightness_events, 2);
1095        assert_eq!(cycle.metrics.app_starts, 1);
1096        assert_eq!(
1097            analysis
1098                .app_start_counts_by_day
1099                .get(&NaiveDate::from_ymd_opt(2023, 1, 1).unwrap())
1100                .copied(),
1101            Some(1)
1102        );
1103    }
1104}
kobo_db_tools/parser.rs

kobo_db_tools/
parser.rs
