rss_actions/db/
transaction.rs

1use std::path::PathBuf;
2
3use anyhow::{anyhow, Context, Result};
4use chrono::{DateTime, Utc};
5use rusqlite::named_params;
6use url::Url;
7
8use crate::db::{RSSActionsTx};
9use crate::models::Feed;
10use crate::models::Filter;
11
12struct FilterId(pub usize);
13
14/// Sort the filters list and then join with two "unit separator" (code 1F) ascii characters into a
15/// single string to serialize in the database.
16fn encode_filter_keywords(keywords: &[String]) -> String {
17    let mut sorted_keywords: Vec<String> = keywords.to_vec();
18    sorted_keywords.sort();
19
20    sorted_keywords.into_iter()
21        .filter(|s| !s.is_empty())
22        .collect::<Vec<_>>()
23        .join("\x1F")
24}
25
26/// Deserialize from `encode_filter_keywords`.
27fn decode_filter_keywords(keywords_packed: &str) -> Vec<String> {
28    keywords_packed
29        .split('\x1F')
30        .filter(|s| !s.is_empty())
31        .map(|s| s.into()).collect()
32}
33
34
35impl<'conn> RSSActionsTx<'conn> {
36    pub fn store_feed(&self, alias: &str, url: &Url) -> Result<()> {
37        self.tx.execute(
38            "INSERT INTO feeds
39              (url, alias) VALUES (:url, :alias)",
40            named_params!{":url": url, ":alias": alias})
41            .with_context(|| format!("Failed to insert feed {} {} into db", alias, url))
42            .map(|_| ()) // ignore returned number of rows modified
43    }
44
45    pub fn fetch_feeds(&self) -> Result<Vec<Feed>> {
46        let mut stmt = self.tx.prepare("SELECT url, alias FROM feeds")?;
47        
48        return stmt.query_map([], |row| Ok((row.get(0)?, row.get(1)?)))
49            .context("Failed to fetch feeds from db")?
50            .map(|res| {
51                let (url, alias): (String, String) = res.context("Failed to read feed from db")?;
52                let url = Url::parse(&url)
53                    .with_context(|| format!("Failed to parse feed {} url from database", alias))?;
54                Ok(Feed {
55                    url,
56                    alias
57                })
58            }).collect();
59
60    }
61
62    pub fn store_filter(&self, filter: &Filter) -> Result<()> {
63        let keywords = encode_filter_keywords(&filter.keywords);
64
65        let res = self.tx.execute(
66            "INSERT INTO filters
67             (feed_id, keywords, script_path, last_updated) VALUES
68             ((SELECT id FROM feeds WHERE feeds.alias = :alias),
69              :keywords, :script_path, :last_updated)",
70            named_params!{":alias": &filter.alias, ":keywords": keywords,
71                    ":script_path": &filter.script_path.to_string_lossy(), ":last_updated": &filter.last_updated})
72            .with_context(|| format!("Failed to insert filter {:?} {:?} {:?} into db", &filter.alias, &keywords, &filter.script_path))
73            .map(|_| ()); // ignore returned number of rows modified
74
75        // TODO this is a hack but the alternative is to do another select and check explicitly
76        // for each error message or perhaps check the rusqlite error type.
77        // Add custom error messages for certain errors.
78        if let Err(err) = res {
79            // Check whether the constraint failed via the feed id select
80            if err.chain().any(|e| e.to_string() == "NOT NULL constraint failed: filters.feed_id") {
81                return Err(err).with_context(|| format!("Couldn't find a feed with alias {}.", filter.alias));
82            }
83            else if err.chain().any(|e| e.to_string() == "UNIQUE constraint failed: filters.feed_id, filters.keywords, filters.script_path") {
84                return Err(err).context("You can't add another filter with the same feed alias, keyword, and script path.");
85            }
86            else {
87                return Err(err);
88            }
89        }
90        // otherwise return result as normal
91        return res;
92    }
93
94    pub fn fetch_filters(&self) -> Result<Vec<Filter>> {
95        Ok(self.fetch_filters_with_ids()?
96            .into_iter().map(|(_db_id, filter)| filter)
97            .collect())
98    }
99
100    fn fetch_filters_with_ids(&self) -> Result<Vec<(FilterId, Filter)>> {
101        let mut stmt = self.tx.prepare(
102            "SELECT filters.id, feeds.alias, filters.keywords, filters.script_path, filters.last_updated
103             FROM filters
104             LEFT JOIN feeds
105             ON filters.feed_id = feeds.id
106             ORDER BY filters.last_updated DESC")?;
107
108        return stmt.query_map([], |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?, row.get(4)?)))
109            .context("Failed to fetch filters from db")?
110            .map(|res| {
111                let (filter_id, alias, keywords, script_path, last_updated): (usize, String, String, String, Option<DateTime<Utc>>) =
112                     res.context("Failed to read feed from db")?;
113
114                let keywords = decode_filter_keywords(&keywords);
115                let script_path = PathBuf::from(script_path);
116                Ok((FilterId(filter_id), Filter {
117                    alias,
118                    keywords,
119                    script_path,
120                    last_updated
121                }))
122            }).collect();
123
124    }
125
126    /// Update filter last_updated keyed on alias, keywords, and script path
127    pub fn update_filter(&mut self, filter: &Filter) -> Result<()> {
128        let keywords = encode_filter_keywords(&filter.keywords);
129
130        let sp = self.tx.savepoint()?;
131        let res = sp.execute(
132            "UPDATE filters
133            SET last_updated = :last_updated
134            WHERE
135                feed_id = (SELECT id FROM feeds WHERE feeds.alias = :alias) AND
136                keywords = :keywords AND
137                script_path = :script_path",
138            named_params!{":alias": &filter.alias, ":keywords": keywords,
139                    ":script_path": &filter.script_path.to_string_lossy(), ":last_updated": &filter.last_updated})
140            .with_context(|| format!("Failed to update filter {:?} {:?} {:?} in db with new time {:?}",
141                    &filter.alias, &keywords, &filter.script_path, &filter.last_updated));
142
143        // Add custom error messages for certain errors.
144        match res {
145            Ok(count) => {
146                if count == 0 {
147                    return Err(anyhow!("No filter was found to update that matches {:?}", filter));
148                }
149                else if count > 1 {
150                    return Err(anyhow!("More than one filter was updated when updating {:?}", filter));
151                }
152            }
153            Err(err) => {
154                return Err(err)
155                    .with_context(|| format!("A database error occurred updating a filter {:?}", filter));
156            }
157        }
158
159        // If no error, commit subtransaction and return result as normal, ignoring count
160        sp.commit()
161            .with_context(|| format!("Failed to commit subtransaction savepoint updating filter {:?}", filter))?;
162        return res.map(|_| ());
163    }
164
165    // TODO return the filter deleted as read from the database.
166    // TODO check whether the feed exists and return a different error in that case
167    pub fn delete_filter(&mut self, alias: &str, keywords: &[String]) -> Result<()> {
168        let filters = self.fetch_filters_with_ids()?;
169
170        // NOTE: originally I wanted to do this in the db with a `LIKE %XkeyX%XwordX%` kind of
171        // query but that doesn't work because you can't put a % in a parameter and have it act as
172        // a wildcard (because then users could insert them). So the options are either insert the
173        // parameter into the query string with normal string formatting and enable sql injections
174        // or do it like this.
175        //
176        // Another option is generating the query string with a bunch of
177        // ```
178        // keywords LIKE %Xkeyword1X%
179        // OR keywords LIKE %Xkeyword2X%
180        // OR ...
181        // ```
182        //
183        // but that's more complicated and error-prone and it's a wash whether it's actually faster
184        // to do that (it probably is but you'd want to measure at that point).
185
186        let mut matching_filters = Vec::new();
187        for (id, filter) in filters {
188            // all user-given keywords are in the filter we're checking
189            let all_keywords_match = keywords.iter().all(|k| filter.keywords.contains(k));
190
191            if filter.alias == alias && all_keywords_match {
192                matching_filters.push((id, filter));
193            }
194        }
195
196        if matching_filters.is_empty() {
197            return Err(anyhow!("No filters matching `{}` on the feed `{}` were found in the database.",
198                    &keywords.join(","), &alias));
199        }
200        else if matching_filters.len() > 1 {
201            return Err(anyhow!("Multiple filters matching `{}` on the feed `{}` were found in the database.",
202                    &keywords.join(","), &alias));
203        }
204
205        let single_matching_filter = matching_filters.pop().expect("checked for 0 and >1");
206
207        self.tx.execute(
208            "DELETE FROM filters
209            WHERE
210                id = :filter_id",
211            named_params!{":filter_id": &single_matching_filter.0.0})
212            // .with_context(|| format!("Failed to filter alias {} keywords {:?}",
213            //         &alias, &keywords));
214            .with_context(|| format!("A database error occurred deleting filter with keywords {:?} on feed {}",
215                    &keywords, &alias))
216            .map(|_| ())
217    }
218
219    pub fn delete_feed(&mut self, alias: &str) -> Result<()> {
220        self.tx.pragma_update(None, "foreign_keys", true)
221            .context("failed to enable foreign keys pragma")?;
222        let res = self.tx.execute(
223            "DELETE FROM feeds
224            WHERE
225                alias = :alias",
226            named_params!{":alias": &alias,})
227            .with_context(|| format!("Failed to delete feed {} in db",
228                    &alias));
229
230        // Add custom error messages for certain errors.
231        match res {
232            Ok(count) => {
233                if count == 0 {
234                    return Err(anyhow!("No feed was found to delete that matches name `{}`", alias));
235                }
236                else if count > 1 {
237                    return Err(anyhow!("More than one feed was found when trying to delete `{}`", alias));
238                }
239            }
240            Err(err) => {
241                return Err(err)
242                    .with_context(|| format!("A database error occurred deleting feed `{}`", alias));
243            }
244        }
245
246        Ok(())
247    }
248}
rss_actions/db/transaction.rs

rss_actions/db/
transaction.rs
