flashpoint_archive/tag/
mod.rs

1use std::rc::Rc;
2
3use rusqlite::{params, types::Value, Connection, OptionalExtension, Result};
4use serde::{Deserialize, Serialize};
5
6use crate::{
7    game::search::mark_index_dirty,
8    tag_category, update::SqlVec,
9};
10
11#[cfg_attr(feature = "napi", napi(object))]
12#[derive(Debug, Clone, Deserialize, Serialize)]
13pub struct Tag {
14    pub id: i64,
15    pub name: String,
16    pub description: String,
17    pub date_modified: String,
18    pub aliases: Vec<String>,
19    pub category: Option<String>,
20}
21
22#[cfg_attr(feature = "napi", napi(object))]
23#[derive(Debug, Clone, Deserialize, Serialize)]
24pub struct PartialTag {
25    pub id: i64,
26    pub name: String,
27    pub description: Option<String>,
28    pub date_modified: Option<String>,
29    pub aliases: Option<Vec<String>>,
30    pub category: Option<String>,
31}
32
33#[cfg_attr(feature = "napi", napi(object))]
34#[derive(Debug, Clone, Deserialize, Serialize)]
35pub struct TagSuggestion {
36    pub id: i64,
37    pub name: String,
38    pub matched_from: String,
39    pub games_count: i64,
40    pub category: Option<String>,
41}
42
43#[cfg_attr(feature = "napi", napi(object))]
44#[derive(Debug, Clone, Deserialize, Serialize)]
45pub struct LooseTagAlias {
46    pub id: i64,
47    pub value: String,
48}
49
50impl Tag {
51    pub fn apply_partial(&mut self, partial: &PartialTag) {
52        self.name = partial.name.clone();
53
54        if let Some(aliases) = partial.aliases.clone() {
55            self.aliases = aliases;
56            if !self.aliases.contains(&self.name) {
57                self.aliases.push(self.name.clone());
58            }
59        }
60
61        if let Some(description) = partial.description.clone() {
62            self.description = description;
63        }
64
65        if let Some(date_modified) = partial.date_modified.clone() {
66            self.date_modified = date_modified;
67        }
68
69        if let Some(category) = partial.category.clone() {
70            self.category = Some(category);
71        }
72    }
73}
74
75impl Default for PartialTag {
76    fn default() -> Self {
77        return PartialTag {
78            id: -1,
79            name: String::new(),
80            description: None,
81            date_modified: None,
82            aliases: None,
83            category: None,
84        };
85    }
86}
87
88impl From<Tag> for PartialTag {
89    fn from(value: Tag) -> Self {
90        let mut partial = PartialTag::default();
91        partial.id = value.id;
92        partial.name = value.name;
93        partial.description = Some(value.description);
94        partial.date_modified = Some(value.date_modified);
95        partial.aliases = Some(value.aliases);
96        partial.category = value.category;
97        partial
98    }
99}
100
101pub fn find(conn: &Connection) -> Result<Vec<Tag>> {
102    let mut stmt = conn.prepare(
103        "SELECT t.id, ta.name, t.description, t.dateModified, tc.name FROM tag t
104        INNER JOIN tag_alias ta ON ta.id = t.primaryAliasId
105        INNER JOIN tag_category tc ON t.categoryId = tc.id
106        ORDER BY tc.name, ta.name",
107    )?;
108
109    let tag_iter = stmt.query_map((), |row| {
110        Ok(Tag {
111            id: row.get(0)?,
112            name: row.get(1)?,
113            description: row.get(2)?,
114            date_modified: row.get(3)?,
115            aliases: vec![],
116            category: row.get(4)?,
117        })
118    })?;
119
120    let mut tags = vec![];
121
122    for tag in tag_iter {
123        let mut tag = tag?;
124        let mut tag_alias_stmt =
125            conn.prepare("SELECT ta.name FROM tag_alias ta WHERE ta.tagId = ?")?;
126        let tag_alias_iter = tag_alias_stmt.query_map(params![&tag.id], |row| row.get(0))?;
127
128        for alias in tag_alias_iter {
129            tag.aliases.push(alias.unwrap());
130        }
131        tags.push(tag);
132    }
133
134    Ok(tags)
135}
136
137pub fn create(
138    conn: &Connection,
139    name: &str,
140    category: Option<String>,
141    id: Option<i64>,
142) -> Result<Tag> {
143    // Create the alias
144    let mut stmt = "INSERT INTO tag_alias (name, tagId) VALUES(?, ?) RETURNING id";
145    let category = tag_category::find_or_create(
146        conn,
147        category.unwrap_or_else(|| "default".to_owned()).as_str(),
148        None,
149    )?;
150
151    // Create a new tag
152    let alias_id: i64 = conn.query_row(stmt, params![name, -1], |row| row.get(0))?;
153
154    match id {
155        Some(id) => {
156            stmt =
157                "INSERT INTO tag (id, primaryAliasId, description, categoryId) VALUES (?, ?, ?, ?)";
158            conn.execute(stmt, params![id, alias_id, "", category.id])?;
159
160            // Update tag alias with the new tag id
161            stmt = "UPDATE tag_alias SET tagId = ? WHERE id = ?";
162            conn.execute(stmt, params![id, alias_id])?;
163        }
164        None => {
165            stmt = "INSERT INTO tag (primaryAliasId, description, categoryId) VALUES (?, ?, ?) RETURNING id";
166            let tag_id: i64 =
167                conn.query_row(stmt, params![alias_id, "", category.id], |row| row.get(0))?;
168
169            // Update tag alias with the new tag id
170            stmt = "UPDATE tag_alias SET tagId = ? WHERE id = ?";
171            conn.execute(stmt, params![tag_id, alias_id])?;
172        }
173    }
174
175    mark_index_dirty(conn)?;
176
177    let new_tag_result = find_by_name(conn, name)?;
178    if let Some(tag) = new_tag_result {
179        Ok(tag)
180    } else {
181        Err(rusqlite::Error::QueryReturnedNoRows)
182    }
183}
184
185pub fn find_or_create(conn: &Connection, name: &str) -> Result<Tag> {
186    let tag_result = find_by_name(conn, name)?;
187    if let Some(tag) = tag_result {
188        Ok(tag)
189    } else {
190        // Clear a lingering alias
191        conn.execute("DELETE FROM tag_alias WHERE name = ?", params![name])?;
192        create(conn, name, None, None)
193    }
194}
195
196pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Tag>> {
197    let mut stmt = conn.prepare(
198        "SELECT t.id, ta.name, t.description, t.dateModified, tc.name FROM tag t
199        INNER JOIN tag_alias ta ON t.id = ta.tagId
200        INNER JOIN tag_category tc ON t.categoryId = tc.id
201        WHERE t.id IN (SELECT alias.tagId FROM tag_alias alias WHERE alias.name = ?)
202		AND t.primaryAliasId = ta.id",
203    )?;
204
205    let tag_result = stmt
206        .query_row(params![name], |row| {
207            Ok(Tag {
208                id: row.get(0)?,
209                name: row.get(1)?,
210                description: row.get(2)?,
211                date_modified: row.get(3)?,
212                category: row.get(4)?,
213                aliases: vec![],
214            })
215        })
216        .optional()?;
217
218    if let Some(mut tag) = tag_result {
219        let mut tag_alias_stmt =
220            conn.prepare("SELECT ta.name FROM tag_alias ta WHERE ta.tagId = ?")?;
221        let tag_alias_iter = tag_alias_stmt.query_map(params![&tag.id], |row| row.get(0))?;
222
223        for alias in tag_alias_iter {
224            tag.aliases.push(alias.unwrap());
225        }
226
227        Ok(Some(tag))
228    } else {
229        Ok(None)
230    }
231}
232
233pub fn find_by_id(conn: &Connection, id: i64) -> Result<Option<Tag>> {
234    let mut stmt = conn.prepare(
235        "SELECT t.id, ta.name, t.description, t.dateModified, tc.name FROM tag t
236        INNER JOIN tag_alias ta ON t.id = ta.tagId
237        INNER JOIN tag_category tc ON t.categoryId = tc.id
238        WHERE t.id = ? AND t.primaryAliasId == ta.id",
239    )?;
240
241    let tag_result = stmt
242        .query_row(params![id], |row| {
243            Ok(Tag {
244                id: row.get(0)?,
245                name: row.get(1)?,
246                description: row.get(2)?,
247                date_modified: row.get(3)?,
248                category: row.get(4)?,
249                aliases: vec![],
250            })
251        })
252        .optional()?;
253
254    if let Some(mut tag) = tag_result {
255        let mut tag_alias_stmt =
256            conn.prepare("SELECT ta.name FROM tag_alias ta WHERE ta.tagId = ?")?;
257        let tag_alias_iter = tag_alias_stmt.query_map(params![&tag.id], |row| row.get(0))?;
258
259        for alias in tag_alias_iter {
260            tag.aliases.push(alias.unwrap());
261        }
262
263        Ok(Some(tag))
264    } else {
265        Ok(None)
266    }
267}
268
269pub fn count(conn: &Connection) -> Result<i64> {
270    conn.query_row("SELECT COUNT(*) FROM tag", (), |row| row.get::<_, i64>(0))
271}
272
273pub fn delete(conn: &Connection, name: &str) -> Result<()> {
274    let tag = find_by_name(conn, name)?;
275    match tag {
276        Some(tag) => {
277            let mut stmt = "DELETE FROM tag_alias WHERE tagId = ?";
278            conn.execute(stmt, params![tag.id])?;
279
280            stmt = "DELETE FROM tag WHERE id = ?";
281            conn.execute(stmt, params![tag.id])?;
282
283            // Update game tagsStr
284            stmt = "UPDATE game
285            SET tagsStr = (
286                SELECT IFNULL(string_agg(ta.name, '; '), '')
287                FROM game_tags_tag gtt
288                JOIN tag t ON gtt.tagId = t.id
289                JOIN tag_alias ta ON t.primaryAliasId = ta.id
290                WHERE gtt.gameId = game.id
291            ) WHERE game.id IN (
292                SELECT gameId FROM game_tags_tag WHERE tagId = ?   
293            )";
294            conn.execute(stmt, params![tag.id])?;
295
296            stmt = "DELETE FROM game_tags_tag WHERE tagId = ?";
297            conn.execute(stmt, params![tag.id])?;
298
299            mark_index_dirty(conn)?;
300
301            Ok(())
302        }
303        None => Err(rusqlite::Error::QueryReturnedNoRows),
304    }
305}
306
307pub fn delete_by_id(conn: &Connection, id: i64) -> Result<()> {
308    let mut stmt = "DELETE FROM tag_alias WHERE tagId = ?";
309    conn.execute(stmt, params![id])?;
310
311    stmt = "DELETE FROM tag WHERE id = ?";
312    conn.execute(stmt, params![id])?;
313
314    // Update game tagsStr
315    stmt = "UPDATE game
316    SET tagsStr = (
317        SELECT IFNULL(string_agg(ta.name, '; '), '')
318        FROM game_tags_tag gtt
319        JOIN tag t ON gtt.tagId = t.id
320        JOIN tag_alias ta ON t.primaryAliasId = ta.id
321        WHERE gtt.gameId = game.id
322    ) WHERE game.id IN (
323        SELECT gameId FROM game_tags_tag WHERE tagId = ?   
324    )";
325    conn.execute(stmt, params![id])?;
326
327    stmt = "DELETE FROM game_tags_tag WHERE tagId = ?";
328    conn.execute(stmt, params![id])?;
329
330    mark_index_dirty(conn)?;
331
332    Ok(())
333}
334
335pub fn merge_tag(conn: &Connection, name: &str, merged_into: &str) -> Result<Tag> {
336    let old_tag = match find_by_name(conn, name)? {
337        Some(tag) => tag,
338        None => return Err(rusqlite::Error::QueryReturnedNoRows),
339    };
340    let merged_tag = match find_by_name(conn, merged_into)? {
341        Some(tag) => tag,
342        None => return Err(rusqlite::Error::QueryReturnedNoRows),
343    };
344
345    // Remove future duplicate relations, add relations for all games with the old tag
346    let mut stmt = "DELETE FROM game_tags_tag
347    WHERE gameId IN (
348        SELECT gameId FROM game_tags_tag WHERE tagId = ?
349    )
350    AND tagId = ?";
351    conn.execute(stmt, params![old_tag.id, merged_tag.id])?;
352
353    stmt = "UPDATE game_tags_tag SET tagId = ? WHERE tagId = ?";
354    conn.execute(stmt, params![merged_tag.id, old_tag.id])?;
355
356    // Remove old tag table entries
357    stmt = "DELETE FROM tag WHERE id = ?";
358    conn.execute(stmt, params![old_tag.id])?;
359    stmt = "DELETE FROM tag_alias WHERE tagId = ?";
360    conn.execute(stmt, params![old_tag.id])?;
361
362    // Add aliases to new tag
363    for alias in old_tag.aliases {
364        stmt = "INSERT INTO tag_alias (tagId, name) VALUES (?, ?)";
365        conn.execute(stmt, params![merged_tag.id, alias])?;
366    }
367
368    // Update game tagsStr
369    stmt = "UPDATE game 
370    SET tagsStr = (
371      SELECT IFNULL(tags, '') tags FROM (
372        SELECT GROUP_CONCAT(
373          (SELECT name FROM tag_alias WHERE tagId = t.tagId), '; '
374        ) tags
375        FROM game_tags_tag t
376        WHERE t.gameId = game.id
377      )
378    )";
379    conn.execute(stmt, ())?;
380
381    mark_index_dirty(conn)?;
382
383    match find_by_name(conn, merged_into)? {
384        Some(tag) => Ok(tag),
385        None => Err(rusqlite::Error::QueryReturnedNoRows),
386    }
387}
388
389pub fn save(conn: &Connection, partial: &PartialTag) -> Result<Tag> {
390    // Allow use of rarray() in SQL queries
391    rusqlite::vtab::array::load_module(conn)?;
392
393    let mut tag = match find_by_id(conn, partial.id)? {
394        Some(t) => t,
395        None => return Err(rusqlite::Error::QueryReturnedNoRows),
396    };
397
398    let mut new_tag_aliases = vec![];
399
400    tag.apply_partial(partial);
401
402    let mut stmt = conn.prepare("SELECT tagId FROM tag_alias WHERE name = ?")?;
403
404    // Check for collisions before updating
405    for alias in tag.aliases.clone() {
406        let existing_tag_id = stmt
407            .query_row(params![alias], |row| row.get::<_, i64>(0))
408            .optional()?;
409        match existing_tag_id {
410            Some(id) => {
411                if id != tag.id {
412                    return Err(rusqlite::Error::QueryReturnedNoRows); // TODO: Make this a proper error
413                }
414            }
415            None => {
416                new_tag_aliases.push(alias);
417            }
418        }
419    }
420
421    // Apply flat edits
422    match tag.category {
423        Some(category) => {
424            let stmt = "UPDATE tag SET description = ?, dateModified = ?, categoryId = (SELECT id FROM tag_category WHERE name = ?) WHERE id = ?";
425            conn.execute(
426                stmt,
427                params![tag.description, tag.date_modified, category, tag.id],
428            )?;
429        }
430        None => {
431            let stmt = "UPDATE tag SET description = ?, dateModified = ? WHERE id = ?";
432            conn.execute(stmt, params![tag.description, tag.date_modified, tag.id])?;
433        }
434    }
435
436    // Remove old aliases
437    let mut stmt = "DELETE FROM tag_alias WHERE tagId = ? AND name NOT IN rarray(?)";
438    let alias_rc = Rc::new(
439        tag.aliases
440            .iter()
441            .map(|v| Value::from(v.clone()))
442            .collect::<Vec<Value>>(),
443    );
444    conn.execute(stmt, params![tag.id, alias_rc])?;
445
446    // Add new aliases
447    for alias in new_tag_aliases {
448        stmt = "INSERT INTO tag_alias (name, tagId) VALUES (?, ?)";
449        conn.execute(stmt, params![alias, tag.id])?;
450    }
451
452    // Update primary alias id
453    stmt = "UPDATE tag SET primaryAliasId = (SELECT id FROM tag_alias WHERE name = ?) WHERE id = ?";
454    conn.execute(stmt, params![tag.name, tag.id])?;
455
456    // Update game tagsStr fields
457    stmt = "UPDATE game
458    SET tagsStr = (
459        SELECT IFNULL(string_agg(ta.name, '; '), '')
460        FROM game_tags_tag gtt
461        JOIN tag t ON gtt.tagId = t.id
462        JOIN tag_alias ta ON t.primaryAliasId = ta.id
463        WHERE gtt.gameId = game.id
464    ) WHERE game.id IN (
465        SELECT gameId FROM game_tags_tag WHERE tagId = ?   
466    )";
467    conn.execute(stmt, params![tag.id])?;
468
469    mark_index_dirty(conn)?;
470
471    match find_by_id(&conn, tag.id)? {
472        Some(t) => Ok(t),
473        None => return Err(rusqlite::Error::QueryReturnedNoRows),
474    }
475}
476
477pub fn search_tag_suggestions(
478    conn: &Connection,
479    partial: &str,
480    blacklist: Vec<String>,
481) -> Result<Vec<TagSuggestion>> {
482    // Allow use of rarray() in SQL queries
483    rusqlite::vtab::array::load_module(conn)?;
484
485    let blacklist = SqlVec(blacklist);
486
487    let mut suggestions = vec![];
488
489    let query = "SELECT sugg.tagId, sugg.matched_alias, count(game_tag.gameId) as gameCount, sugg.primary_alias, sugg.category FROM (
490        SELECT 
491			ta1.tagId as tagId,
492			ta1.name AS matched_alias,
493			ta2.name AS primary_alias,
494            cat.name as category
495		FROM 
496			tag_alias ta1
497		JOIN 
498			tag t ON ta1.tagId = t.id
499		JOIN 
500	        tag_alias ta2 ON t.primaryAliasId = ta2.id
501        JOIN 
502            tag_category cat ON t.categoryId = cat.id
503		WHERE 
504			ta1.name LIKE ?
505    ) sugg
506    LEFT JOIN game_tags_tag game_tag ON game_tag.tagId = sugg.tagId
507    WHERE sugg.tagId NOT IN (
508        SELECT tagId FROM tag_alias WHERE name IN rarray(?)
509    )
510    GROUP BY sugg.matched_alias
511    ORDER BY COUNT(game_tag.gameId) DESC, sugg.matched_alias ASC";
512
513    let mut stmt = conn.prepare(&query)?;
514    let mut likeable = String::from(partial);
515    likeable.push_str("%");
516    let results = stmt.query_map(params![&likeable, blacklist], |row| {
517        Ok(TagSuggestion {
518            id: row.get(0)?,
519            matched_from: row.get(1)?,
520            games_count: row.get(2)?,
521            name: row.get(3)?,
522            category: row.get(4)?,
523        })
524    })?;
525
526    for sugg in results {
527        suggestions.push(sugg?);
528    }
529
530    Ok(suggestions)
531}
flashpoint_archive/tag/mod.rs

flashpoint_archive/tag/
mod.rs
