Coverage Report

Created: 2024-02-20 21:15

/builds/xfbs/cindy/src/database/handlers.rs
Line
Count
Source (jump to first uncovered line)
1
use super::*;
2
use crate::tag::TagPredicate;
3
use cindy_common::{
4
    tag::{TagNameInfo, TagValueInfo},
5
    Label, LabelKind, Point, Rectangle, Sequence,
6
};
7
use rusqlite::ToSql;
8
use std::collections::BTreeMap;
9
10
// Database interactions return Sqlite errors.
11
type Result<T, E = rusqlite::Error> = std::result::Result<T, E>;
12
13
impl<T: Handle> Database<T> {
14
    /// Add hash to database.
15
11.5k
    pub fn hash_add(&self, hash: &Hash) -> Result<()> {
16
11.5k
        let mut query = self.prepare_cached("INSERT OR IGNORE INTO files(hash) VALUES (?)")
?0
;
17
11.5k
        query.execute([hash.as_slice()])
?0
;
18
11.5k
        Ok(())
19
11.5k
    }
20
21
    /// Remove file hash from database, including all tags.
22
1
    pub fn hash_remove(&self, hash: &Hash) -> Result<()> {
23
1
        let mut query = self.prepare_cached("DELETE FROM files WHERE hash = ?")
?0
;
24
1
        query.execute([hash.as_slice()])
?0
;
25
1
        Ok(())
26
1
    }
27
28
    /// Check if a hash exists.
29
17
    pub fn hash_exists(&self, hash: &Hash) -> Result<bool> {
30
17
        let mut query = self.prepare_cached("SELECT * FROM files WHERE hash = ?")
?0
;
31
17
        let mut rows = query.query([hash.as_slice()])
?0
;
32
17
        Ok(rows.next()
?0
.is_some())
33
17
    }
34
35
6.32k
    pub fn hash_tags(
36
6.32k
        &self,
37
6.32k
        hash: &Hash,
38
6.32k
        name: Option<&str>,
39
6.32k
        value: Option<&str>,
40
6.32k
    ) -> Result<BTreeSet<Tag>> {
41
6.32k
        let mut query = self.prepare_cached(
42
6.32k
            "SELECT name, value
43
6.32k
            FROM file_tags
44
6.32k
            WHERE hash = ?
45
6.32k
            AND coalesce(name = ?, true)
46
6.32k
            AND coalesce(value = ?, true)",
47
6.32k
        )
?0
;
48
6.32k
        let rows = query.query((hash.as_slice(), name, value))
?0
;
49
76.1k
        
rows.mapped(6.32k
|row| Ok(Tag::new(row.get("name")
?0
, row.get("value")
?0
)))
50
6.32k
            .collect::<Result<BTreeSet<Tag>, _>>()
51
6.32k
            .map_err(Into::into)
52
6.32k
    }
53
54
    /// Add tag to database.
55
14.3k
    pub fn tag_value_create(&self, tag: &str, value: &str) -> Result<()> {
56
14.3k
        let mut query = self.prepare_cached(
57
14.3k
            "INSERT OR IGNORE INTO tag_values(tag_id, value)
58
14.3k
            VALUES ((SELECT id FROM tag_names WHERE name = ?), ?)",
59
14.3k
        )
?0
;
60
14.3k
        query.execute([&tag, &value])
?0
;
61
14.3k
        Ok(())
62
14.3k
    }
63
64
    /// List tags in database.
65
18
    pub fn tag_list(
66
18
        &self,
67
18
        name: Option<&str>,
68
18
        value: Option<&str>,
69
18
    ) -> Result<BTreeMap<Tag, TagValueInfo>> {
70
18
        let mut query = self.prepare_cached(
71
18
            "SELECT
72
18
                name,
73
18
                value,
74
18
                coalesce(value_display, value) as display,
75
18
                system
76
18
            FROM tags
77
18
            WHERE coalesce(name = ?, true)
78
18
            AND coalesce(value = ?, true)",
79
18
        )
?0
;
80
18
        let rows = query.query([&name, &value])
?0
;
81
31
        
rows.mapped(18
|row| {
82
31
            Ok((
83
31
                Tag::new(row.get("name")
?0
, row.get("value")
?0
),
84
                TagValueInfo {
85
                    files: 0,
86
31
                    display: row.get("display")
?0
,
87
31
                    system: row.get("system")
?0
,
88
                },
89
            ))
90
31
        })
91
18
        .collect::<Result<BTreeMap<_, _>, _>>()
92
18
        .map_err(Into::into)
93
18
    }
94
95
    /// Set a tag name's display value.
96
1
    pub fn tag_name_display(&self, name: &str, display: &str) -> Result<()> {
97
1
        let mut query = self.prepare_cached("UPDATE tag_names SET display = ? WHERE name = ?")
?0
;
98
1
        query.execute([display, name])
?0
;
99
1
        Ok(())
100
1
    }
101
102
    /// Set a tag value's display value.
103
    /// TODO: test and/or move this into tags view?
104
12
    pub fn tag_value_display(&self, name: &str, value: &str, display: &str) -> Result<()> {
105
12
        let mut query = self.prepare_cached(
106
12
            "UPDATE tag_values
107
12
            SET display = ?
108
12
            WHERE value = ?
109
12
            AND tag_id = (SELECT id FROM tag_names WHERE name = ?)",
110
12
        )
?0
;
111
12
        query.execute([display, value, name])
?0
;
112
12
        Ok(())
113
12
    }
114
115
14.1k
    pub fn tag_name_create(&self, name: &str, display: Option<&str>) -> Result<()> {
116
14.1k
        let mut query =
117
14.1k
            self.prepare_cached("INSERT OR IGNORE INTO tag_names(name, display) VALUES (?, ?)")
?0
;
118
14.1k
        query.execute((name, display))
?0
;
119
14.1k
        Ok(())
120
14.1k
    }
121
122
    /// List tag names
123
7
    pub fn tag_names(&self) -> Result<BTreeMap<String, TagNameInfo>> {
124
7
        let mut query = self.prepare_cached(
125
7
            "SELECT
126
7
                coalesce(tag_names.display, tag_names.name) as display,
127
7
                tag_names.*,
128
7
                (SELECT count(*) FROM tag_values WHERE tag_id = tag_names.id) as value
129
7
            FROM tag_names",
130
7
        )
?0
;
131
7
        let rows = query.query([])
?0
;
132
89
        
rows.mapped(7
|row| {
133
89
            Ok((
134
89
                row.get("name")
?0
,
135
                TagNameInfo {
136
89
                    values: row.get("value")
?0
,
137
89
                    system: row.get("system")
?0
,
138
89
                    display: row.get("display")
?0
,
139
                },
140
            ))
141
89
        })
142
7
        .collect()
143
7
    }
144
145
    /// Rename tag name.
146
1
    pub fn tag_name_rename(&self, old: &str, new: &str) -> Result<()> {
147
1
        let mut query = self.prepare_cached("UPDATE tag_names SET name = ? WHERE name = ?")
?0
;
148
1
        query.execute([&new, &old])
?0
;
149
1
        Ok(())
150
1
    }
151
152
    /// Rename tag value.
153
1
    pub fn tag_value_rename(&self, name: &str, old: &str, new: &str) -> Result<()> {
154
1
        let mut query = self.prepare_cached(
155
1
            "UPDATE tag_values
156
1
            SET value = ?
157
1
            WHERE value = ?
158
1
            AND tag_id = (SELECT id FROM tag_names WHERE name = ?)",
159
1
        )
?0
;
160
1
        query.execute((&new, &old, name))
?0
;
161
1
        Ok(())
162
1
    }
163
164
    /// Delete tags.
165
5
    pub fn tag_delete(&self, name: Option<&str>, value: Option<&str>) -> Result<()> {
166
5
        let mut query = self.prepare_cached(
167
5
            "DELETE FROM tags
168
5
            WHERE coalesce(name = ?, true)
169
5
            AND coalesce(value = ?, true)",
170
5
        )
?0
;
171
5
        query.execute([&name, &value])
?0
;
172
5
        Ok(())
173
5
    }
174
175
    /// Add tag to file
176
44.2k
    pub fn hash_tag_add(&self, file: &Hash, tag: &str, value: &str) -> Result<()> {
177
44.2k
        let mut query = self.prepare_cached(
178
44.2k
            "INSERT OR IGNORE INTO file_tags(hash, name, value)
179
44.2k
            VALUES (?, ?, ?)",
180
44.2k
        )
?0
;
181
44.2k
        query.execute((file.as_slice(), &tag, &value))
?0
;
182
44.2k
        Ok(())
183
44.2k
    }
184
185
    /// Remove tag from file
186
7
    pub fn hash_tag_remove(
187
7
        &self,
188
7
        file: &Hash,
189
7
        tag: Option<&str>,
190
7
        value: Option<&str>,
191
7
    ) -> Result<()> {
192
7
        let mut query = self.prepare_cached(
193
7
            "DELETE FROM file_tags
194
7
            WHERE coalesce(hash = ?, true)
195
7
            AND coalesce(name = ?, true)
196
7
            AND coalesce(value = ?, true)",
197
7
        )
?0
;
198
7
        query.execute((file.as_slice(), &tag, &value))
?0
;
199
7
        Ok(())
200
7
    }
201
202
5.82k
    pub fn query_hashes(
203
5.82k
        &self,
204
5.82k
        query: &mut dyn Iterator<Item = &TagPredicate<'_>>,
205
5.82k
    ) -> Result<BTreeSet<BoxHash>> {
206
5.82k
        let mut params: Vec<Option<&str>> = vec![];
207
5.82k
        let mut segments = vec![];
208
10.6k
        for 
predicate4.80k
in query {
209
4.80k
            let filter = predicate.filter();
210
4.80k
            let segment = "
211
4.80k
                (EXISTS (SELECT file_id FROM file_tags
212
4.80k
                    WHERE files.id = file_tags.file_id
213
4.80k
                    AND coalesce(name = ?, true)
214
4.80k
                    AND coalesce(value = ?, true)
215
4.80k
                    ))
216
4.80k
            "
217
4.80k
            .to_string();
218
4.80k
            let segment = match predicate {
219
4
                TagPredicate::Missing(_) => format!("(NOT {segment})"),
220
4.80k
                _other => segment,
221
            };
222
4.80k
            params.push(filter.name());
223
4.80k
            params.push(filter.value());
224
4.80k
            segments.push(segment);
225
        }
226
5.82k
        let query_string = match segments.len() {
227
1.02k
            0 => "SELECT hash FROM files".into(),
228
4.79k
            _ => format!("SELECT hash FROM files WHERE {}", segments.join(" AND ")),
229
        };
230
5.82k
        let mut query = self.prepare(&query_string)
?0
;
231
9.61k
        let 
params: Vec<&dyn ToSql> = params.iter().map(5.82k
|v| v as &dyn ToSql
).collect()5.82k
;
232
5.82k
        let rows = query.query(&params[..])
?0
;
233
24.2k
        
rows.mapped(5.82k
|row| Ok(Box::<[u8]>::from(row.get::<_, Vec<u8>>("hash")
?0
).into()))
234
5.82k
            .collect::<Result<BTreeSet<BoxHash>, _>>()
235
5.82k
            .map_err(Into::into)
236
5.82k
    }
237
238
    /// For a given query, compute the union of all tags of all results.
239
5.28k
    pub fn query_tag_union(
240
5.28k
        &self,
241
5.28k
        query: &mut dyn Iterator<Item = &TagPredicate<'_>>,
242
5.28k
        name: Option<&str>,
243
5.28k
        value: Option<&str>,
244
5.28k
    ) -> Result<BTreeSet<Tag>> {
245
5.28k
        let hashes = self.query_hashes(query)
?0
;
246
5.28k
        let mut union = BTreeSet::new();
247
10.8k
        for 
hash5.54k
in &hashes {
248
5.54k
            let hashes = self.hash_tags(&hash, name.as_deref(), value.as_deref())
?0
;
249
74.6k
            for 
hash69.1k
in hashes {
250
69.1k
                union.insert(hash);
251
69.1k
            }
252
        }
253
5.28k
        Ok(union)
254
5.28k
    }
255
256
    /// For a given query, compute the intersection of tags of the results.
257
512
    pub fn query_tag_intersection(
258
512
        &self,
259
512
        query: &mut dyn Iterator<Item = &TagPredicate<'_>>,
260
512
        name: Option<&str>,
261
512
        value: Option<&str>,
262
512
    ) -> Result<BTreeSet<Tag>> {
263
512
        let hashes = self.query_hashes(query)
?0
;
264
512
        let mut intersection: Option<BTreeSet<_>> = None;
265
1.27k
        for 
hash768
in &hashes {
266
768
            let hashes = self.hash_tags(&hash, name, value)
?0
;
267
768
            if let Some(
list256
) = &mut intersection {
268
256
                if list.is_empty() {
269
10
                    break;
270
246
                }
271
246
                let difference: Vec<_> = list.difference(&hashes).cloned().collect();
272
2.67k
                for 
hash2.43k
in &difference {
273
2.43k
                    list.remove(hash);
274
2.43k
                }
275
512
            } else {
276
512
                intersection = Some(hashes);
277
512
            }
278
        }
279
512
        Ok(intersection.unwrap_or_default())
280
512
    }
281
282
    /// For a given query, add a tag to all results.
283
0
    pub fn query_tag_add(
284
0
        &self,
285
0
        query: &mut dyn Iterator<Item = &TagPredicate<'_>>,
286
0
        name: &str,
287
0
        value: &str,
288
0
    ) -> Result<()> {
289
0
        let hashes = self.query_hashes(query)?;
290
0
        for hash in &hashes {
291
0
            self.hash_tag_add(hash, name, value)?;
292
        }
293
0
        Ok(())
294
0
    }
295
296
    /// For a given query, remove tags from all results.
297
0
    pub fn query_tag_remove(
298
0
        &self,
299
0
        query: &mut dyn Iterator<Item = &TagPredicate<'_>>,
300
0
        name: Option<&str>,
301
0
        value: Option<&str>,
302
0
    ) -> Result<()> {
303
0
        let hashes = self.query_hashes(query)?;
304
0
        for hash in &hashes {
305
0
            self.hash_tag_remove(hash, name, value)?;
306
        }
307
0
        Ok(())
308
0
    }
309
310
    /// Add a label to a tagged file.
311
6
    pub fn label_add(&self, file: &Hash, name: &str, value: &str, label: &Label) -> Result<()> {
312
6
        match label {
313
3
            Label::Rectangle(rect) => self.label_add_rect(file, name, value, rect),
314
3
            Label::Sequence(seq) => self.label_add_seq(file, name, value, seq),
315
        }
316
6
    }
317
318
3
    fn label_add_rect(&self, file: &Hash, name: &str, value: &str, rect: &Rectangle) -> Result<()> {
319
3
        let mut query = self.prepare_cached(
320
3
            "INSERT OR IGNORE INTO label_rectangles(file_tag_value_id, x1, y1, x2, y2)
321
3
            VALUES (
322
3
                (SELECT id FROM file_tags WHERE hash = ? AND name = ? AND value = ?),
323
3
                ?, ?, ?, ?
324
3
            )
325
3
        ",
326
3
        )
?0
;
327
3
        query.execute((
328
3
            file.as_slice(),
329
3
            name,
330
3
            value,
331
3
            rect.start.x,
332
3
            rect.start.y,
333
3
            rect.end.x,
334
3
            rect.end.y,
335
3
        ))
?0
;
336
3
        Ok(())
337
3
    }
338
339
3
    fn label_add_seq(&self, file: &Hash, name: &str, value: &str, seq: &Sequence) -> Result<()> {
340
3
        let mut query = self.prepare_cached(
341
3
            "
342
3
            INSERT OR IGNORE INTO label_sequences(file_tag_value_id, t1, t2)
343
3
            VALUES (
344
3
                (SELECT id FROM file_tags WHERE hash = ? AND name = ? AND value = ?),
345
3
                ?, ?
346
3
            )
347
3
        ",
348
3
        )
?0
;
349
3
        query.execute((file.as_slice(), name, value, seq.start, seq.end))
?0
;
350
3
        Ok(())
351
3
    }
352
353
    /// Add a label to a tagged file.
354
2
    pub fn label_remove(&self, file: &Hash, name: &str, value: &str, label: &Label) -> Result<()> {
355
2
        match label {
356
1
            Label::Rectangle(rect) => self.label_remove_rect(file, name, value, rect),
357
1
            Label::Sequence(seq) => self.label_remove_seq(file, name, value, seq),
358
        }
359
2
    }
360
361
1
    fn label_remove_rect(
362
1
        &self,
363
1
        file: &Hash,
364
1
        name: &str,
365
1
        value: &str,
366
1
        rect: &Rectangle,
367
1
    ) -> Result<()> {
368
1
        let mut query = self.prepare_cached(
369
1
            "DELETE FROM label_rectangles
370
1
            WHERE file_tag_value_id = (SELECT id FROM file_tags WHERE hash = ? AND name = ? AND value = ?)
371
1
            AND x1 = ?
372
1
            AND y1 = ?
373
1
            AND x2 = ?
374
1
            AND y2 = ?"
375
1
        )
?0
;
376
1
        query.execute((
377
1
            file.as_slice(),
378
1
            name,
379
1
            value,
380
1
            rect.start.x,
381
1
            rect.start.y,
382
1
            rect.end.x,
383
1
            rect.end.y,
384
1
        ))
?0
;
385
1
        Ok(())
386
1
    }
387
388
1
    fn label_remove_seq(&self, file: &Hash, name: &str, value: &str, seq: &Sequence) -> Result<()> {
389
1
        let mut query = self.prepare_cached(
390
1
            "DELETE FROM label_sequences
391
1
            WHERE file_tag_value_id = (SELECT id FROM file_tags WHERE hash = ? AND name = ? AND value = ?)
392
1
            AND t1 = ?
393
1
            AND t2 = ?"
394
1
        )
?0
;
395
1
        query.execute((file.as_slice(), name, value, seq.start, seq.end))
?0
;
396
1
        Ok(())
397
1
    }
398
399
    // TODO: rename this to label_query and create label_get which takes a fixed hash, name and
400
    // value but only returns Labels?
401
3
    pub fn label_get(
402
3
        &self,
403
3
        file: Option<&Hash>,
404
3
        name: Option<&str>,
405
3
        value: Option<&str>,
406
3
        kind: Option<LabelKind>,
407
3
    ) -> Result<BTreeSet<(Tag, Label)>> {
408
3
        let mut query = self.prepare_cached(
409
3
            "SELECT *
410
3
            FROM file_labels
411
3
            WHERE coalesce(hash = ?, true)
412
3
            AND coalesce(name = ?, true)
413
3
            AND coalesce(value = ?, true)
414
3
            AND coalesce(kind = ?, true)",
415
3
        )
?0
;
416
3
        let rows = query.query((
417
3
            file.map(|f| 
f.as_slice()2
),
418
3
            name,
419
3
            value,
420
3
            kind.map(|k| 
k.name()0
),
421
3
        ))
?0
;
422
3
        rows.mapped(|row| 
{2
423
2
            let tag = Tag::new(row.get("name")
?0
, row.get("value")
?0
);
424
2
            let label = match row.get::<_, String>("kind")
?0
{
425
2
                
kind1
if kind == LabelKind::Rectangle.name() => Rectangle {
426
1
                    start: Point::new(row.get("x1")
?0
, row.get("y1")
?0
),
427
1
                    end: Point::new(row.get("x2")
?0
, row.get("y2")
?0
),
428
                }
429
1
                .into(),
430
1
                kind if kind == LabelKind::Sequence.name() => Sequence {
431
1
                    start: row.get("t1")
?0
,
432
1
                    end: row.get("t2")
?0
,
433
                }
434
1
                .into(),
435
0
                _ => unreachable!("encountered unknown label kind"),
436
            };
437
2
            Ok((tag, label))
438
3
        
}2
)
439
3
        .collect::<Result<BTreeSet<(Tag, Label)>, _>>()
440
3
        .map_err(Into::into)
441
3
    }
442
443
    /// Run migrations on database.
444
    pub fn migrate(&self) -> Result<()> {
445
1.08k
        self.execute_batch(SQLITE_SCHEMA)
?0
;
446
1.08k
        Ok(())
447
1.08k
    }
448
}