nf_rated/data/
db.rs

1use rusqlite::{params, Connection, Error, Result, NO_PARAMS};
2
3use super::{rated_row_from_row, CsvRow};
4use crate::core::RatedRow;
5use std::{error::Error as StdError, fs};
6
7use super::DatabaseInfo;
8
9const CREATE_TABLE_QUERY: &str = "CREATE TABLE IF NOT EXISTS nf_imdb (
10    id               INTEGER PRIMARY KEY,
11    title            TEXT NOT NULL,
12    year             INTEGER NOT NULL,
13    cast             TEXT NOT NULL,
14    country          TEXT NOT NULL,
15    director         TEXT NOT NULL,
16    type             TEXT NOT NULL,
17    duration         TEXT NOT NULL,
18    plot             TEXT NOT NULL,
19
20    genre            TEXT,
21    writer           TEXT,
22    language         TEXT,
23
24    imdb_rating      INTEGER,
25    imdb_votes       INTEGER,
26    imdb_id          TEXT,
27
28    last_sync        INTEGER
29)";
30
31const UPSERT_QUERY: &str = "INSERT INTO nf_imdb (
32    id          ,
33    title       ,
34    year        ,
35    cast        ,
36    country     ,
37    director    ,
38    type        ,
39    duration    ,
40    plot        ,
41
42    genre       ,
43    writer      ,
44    language    ,
45
46    imdb_rating ,
47    imdb_votes  ,
48    imdb_id     ,
49
50    last_sync)
51VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16)
52ON CONFLICT (id) DO NOTHING;
53";
54
55const SYNC_QUERY: &str = "UPDATE nf_imdb
56    SET
57        id         = ?1,
58        title      = ?2,
59        year       = ?3,
60        cast       = ?4,
61        country    = ?5,
62        director   = ?6,
63        type       = ?7,
64        duration   = ?8,
65        plot       = ?9,
66
67        genre      = ?10,
68        writer     = ?11,
69        language   = ?12,
70
71        imdb_rating= ?13,
72        imdb_votes = ?14,
73        imdb_id    = ?15,
74
75        last_sync  = ?16
76    WHERE
77        id = ?1;
78";
79
80const SELECT_UNSYNCED_QUERY: &str = "SELECT * FROM nf_imdb WHERE last_sync IS NULL;";
81const SELECT_SYNCED_QUERY: &str = "SELECT * FROM nf_imdb WHERE last_sync IS NOT NULL;";
82const DELETE_ROW_QUERY: &str = "DELETE FROM nf_imdb WHERE id = ?1;";
83const SELECT_ALL_QUERY: &str = "SELECT * FROM nf_imdb;";
84const SELECT_SYNCED_SORTED_BY_RATING_QUERY: &str =
85    "SELECT * FROM nf_imdb WHERE last_sync IS NOT NULL ORDER BY imdb_rating DESC;";
86
87pub struct Db {
88    con: Connection,
89}
90
91impl Db {
92    pub fn new(info: &DatabaseInfo) -> Result<Db, Box<dyn StdError>> {
93        if !info.folder_exists {
94            fs::create_dir_all(&info.folder)?;
95        }
96
97        let con = Connection::open(&info.db_path)?;
98        let db = Self { con };
99        if !info.db_exists {
100            db.init_data()?;
101        }
102        Ok(db)
103    }
104
105    fn init_data(&self) -> Result<(), Box<dyn StdError>> {
106        self.create_table()?;
107
108        let csv = include_str!("../../resources/data/netflix_titles.csv");
109        let mut rdr = csv::Reader::from_reader(csv.as_bytes());
110        for result in rdr.records() {
111            let row: CsvRow = result?.into();
112            self.upsert_row(&row.into())?;
113        }
114
115        Ok(())
116    }
117
118    pub fn create_table(&self) -> Result<usize> {
119        // CSV data
120        // - id             show_id
121        // - title          title
122        // - year           release_year
123        // - cast           cast
124        // - country        country
125        // - director       director
126
127        // Combination (JSON if available otherwise CSV)
128        // - type           (Type, type)
129        // - duration       (Runtime, duration)
130        // - plot           (Plot, description)
131
132        // JSON
133        // - genre          Genre
134        // - language       Language
135        // - writer         Writer
136        // - imdb_rating    imdbRating (multiplied by 10 -> 0..100)
137        // - imdb_votes     imdbVotes
138        // - imdb_id        imdbID -> URL https://www.imdb.com/title/<imdb_id>
139        self.con.execute(CREATE_TABLE_QUERY, NO_PARAMS)
140    }
141
142    pub fn upsert_row(&self, row: &RatedRow) -> Result<usize> {
143        self.con.execute(
144            UPSERT_QUERY,
145            params![
146                row.id,
147                row.title,
148                row.year,
149                row.cast,
150                row.country,
151                row.director,
152                row.typ,
153                row.duration,
154                row.plot,
155                row.genre,
156                row.writer,
157                row.language,
158                row.imdb_rating,
159                row.imdb_votes,
160                row.imdb_id,
161                row.last_sync
162            ],
163        )
164    }
165
166    pub fn get_unsynced_rows(&self) -> Result<Vec<RatedRow>, Error> {
167        let mut stmt = self.con.prepare(SELECT_UNSYNCED_QUERY)?;
168        let iter = stmt.query_map(NO_PARAMS, |row| Ok(rated_row_from_row(&row)))?;
169        iter.collect()
170    }
171
172    pub fn get_synced_rows(&self) -> Result<Vec<RatedRow>, Error> {
173        let mut stmt = self.con.prepare(SELECT_SYNCED_QUERY)?;
174        let iter = stmt.query_map(NO_PARAMS, |row| Ok(rated_row_from_row(&row)))?;
175        iter.collect()
176    }
177
178    pub fn get_synced_rows_sorted(&self) -> Result<Vec<RatedRow>, Error> {
179        let mut stmt = self.con.prepare(SELECT_SYNCED_SORTED_BY_RATING_QUERY)?;
180        let iter = stmt.query_map(NO_PARAMS, |row| Ok(rated_row_from_row(&row)))?;
181        iter.collect()
182    }
183
184    pub fn get_no_params_query_result(&self, query: &str) -> Result<Vec<RatedRow>, Error> {
185        let mut stmt = self.con.prepare(query)?;
186        let iter = stmt.query_map(NO_PARAMS, |row| Ok(rated_row_from_row(&row)))?;
187        iter.collect()
188    }
189
190    pub fn sync_row(&self, row: &RatedRow) -> Result<usize> {
191        self.con.execute(
192            SYNC_QUERY,
193            params![
194                row.id,
195                row.title,
196                row.year,
197                row.cast,
198                row.country,
199                row.director,
200                row.typ,
201                row.duration,
202                row.plot,
203                row.genre,
204                row.writer,
205                row.language,
206                row.imdb_rating,
207                row.imdb_votes,
208                row.imdb_id,
209                row.last_sync
210            ],
211        )
212    }
213
214    pub fn delete_row(&self, id: u32) -> Result<usize> {
215        self.con.execute(DELETE_ROW_QUERY, params![id])
216    }
217
218    pub fn get_all_rows(&self) -> Result<Vec<RatedRow>, Error> {
219        let mut stmt = self.con.prepare(SELECT_ALL_QUERY)?;
220        let iter = stmt.query_map(NO_PARAMS, |row| Ok(rated_row_from_row(&row)))?;
221        iter.collect()
222    }
223}
nf_rated/data/db.rs

nf_rated/data/
db.rs
