flashpoint_archive/platform/
mod.rs

1use std::rc::Rc;
2
3use rusqlite::{params, types::Value, Connection, OptionalExtension, Result};
4use serde::{Deserialize, Serialize};
5
6use crate::tag::{PartialTag, Tag, TagSuggestion};
7
8#[cfg_attr(feature = "napi", napi(object))]
9#[derive(Debug, Clone, Deserialize, Serialize)]
10pub struct PlatformAppPath {
11    pub app_path: String,
12    pub count: i64,
13}
14
15pub fn count(conn: &Connection) -> Result<i64> {
16    conn.query_row("SELECT COUNT(*) FROM platform", (), |row| {
17        row.get::<_, i64>(0)
18    })
19}
20
21pub fn find(conn: &Connection) -> Result<Vec<Tag>> {
22    let mut stmt = conn.prepare(
23        "SELECT p.id, pa.name, p.description, p.dateModified FROM platform_alias pa
24        INNER JOIN platform p ON p.id = pa.platformId
25        WHERE pa.id == p.primaryAliasId")?;
26
27    let platform_iter = stmt.query_map((), |row| {
28        Ok(Tag {
29            id: row.get(0)?,
30            name: row.get(1)?,
31            description: row.get(2)?,
32            date_modified: row.get(3)?,
33            aliases: vec![],
34            category: None,
35        })
36    })?;
37
38    let mut platforms = vec![];
39
40    for platform in platform_iter {
41        let mut platform = platform?;
42        let mut platform_alias_stmt = conn.prepare(
43            "SELECT ta.name FROM platform_alias ta WHERE ta.platformId = ?")?;
44        let platform_alias_iter = platform_alias_stmt.query_map(params![&platform.id], |row| row.get(0))?;
45        
46        for alias in platform_alias_iter {
47            platform.aliases.push(alias.unwrap());
48        }
49        platforms.push(platform);
50    }
51
52    Ok(platforms)
53}
54
55pub fn create(conn: &Connection, name: &str, id: Option<i64>) -> Result<Tag> {
56    // Create the alias
57    let mut stmt = "INSERT INTO platform_alias (name, platformId) VALUES(?, ?) RETURNING id";    
58
59    // Create a new tag
60    let alias_id: i64 = conn.query_row(stmt, params![name, -1], |row| row.get(0))?;
61
62    match id {
63        Some(id) => {
64            stmt = "INSERT INTO platform (id, primaryAliasId, description) VALUES (?, ?, ?)";
65            conn.execute(stmt, params![id, alias_id, ""])?;
66        
67            // Update tag alias with the new tag id
68            stmt = "UPDATE platform_alias SET platformId = ? WHERE id = ?";
69            conn.execute(stmt, params![id, alias_id])?;
70        }
71        None => {
72            stmt = "INSERT INTO platform (primaryAliasId, description) VALUES (?, ?) RETURNING id";
73            let tag_id: i64 = conn.query_row(stmt, params![alias_id, ""], |row| row.get(0))?;
74        
75            // Update tag alias with the new tag id
76            stmt = "UPDATE platform_alias SET platformId = ? WHERE id = ?";
77            conn.execute(stmt, params![tag_id, alias_id])?;
78        }
79    }
80
81
82    let new_tag_result = find_by_name(conn, name)?;
83    if let Some(tag) = new_tag_result {
84        Ok(tag)
85    } else {
86        Err(rusqlite::Error::QueryReturnedNoRows)
87    }
88}
89
90pub fn find_or_create(conn: &Connection, name: &str, id: Option<i64>) -> Result<Tag> {
91    let platform_result = find_by_name(conn, name)?;
92    if let Some(platform) = platform_result {
93        Ok(platform)
94    } else {
95        // Clear a lingering alias
96        conn.execute("DELETE FROM platform_alias WHERE name = ?", params![name])?;
97        create(conn, name, id)
98    }
99}
100
101pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Tag>> {
102    let mut stmt = conn.prepare(
103        "SELECT p.id, pa.name, p.description, p.dateModified FROM platform p
104        INNER JOIN platform_alias pa ON p.id = pa.platformId
105        WHERE p.id IN (SELECT alias.platformId FROM platform_alias alias WHERE alias.name = ?)
106		AND p.primaryAliasId = pa.id")?;
107
108    let platform_result = stmt.query_row(params![name], |row| {
109        Ok(Tag {
110            id: row.get(0)?,
111            name: row.get(1)?,
112            description: row.get(2)?,
113            date_modified: row.get(3)?,
114            category: None,
115            aliases: vec![],
116        })
117    });
118
119    match platform_result {
120        Ok(mut platform) => {
121            let mut platform_alias_stmt = conn.prepare(
122                "SELECT pa.name FROM platform_alias pa WHERE pa.platformId = ?")?;
123            let platform_alias_iter = platform_alias_stmt.query_map(params![&platform.id], |row| row.get(0))?;
124            
125            for alias in platform_alias_iter {
126                platform.aliases.push(alias.unwrap());
127            }
128
129            Ok(Some(platform))
130        },
131        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
132        Err(e) => Err(e),
133    }
134}
135
136pub fn  find_by_id(conn: &Connection, id: i64) -> Result<Option<Tag>> {
137    let mut stmt = conn.prepare(
138        "SELECT p.id, pa.name, p.description, p.dateModified FROM platform_alias pa
139        INNER JOIN platform p ON p.id = pa.platformId
140        WHERE p.id = ? AND p.primaryAliasId == pa.id")?;
141
142    let platform_result = stmt.query_row(params![id], |row| {
143        Ok(Tag {
144            id: row.get(0)?,
145            name: row.get(1)?,
146            description: row.get(2)?,
147            date_modified: row.get(3)?,
148            category: None,
149            aliases: vec![],
150        })
151    });
152
153    match platform_result {
154        Ok(mut platform) => {
155            let mut platform_alias_stmt = conn.prepare(
156                "SELECT pa.name FROM platform_alias pa WHERE pa.platformId = ?")?;
157            let platform_alias_iter = platform_alias_stmt.query_map(params![&platform.id], |row| row.get(0))?;
158            
159            for alias in platform_alias_iter {
160                platform.aliases.push(alias.unwrap());
161            }
162
163            Ok(Some(platform))
164        },
165        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
166        Err(e) => Err(e),
167    }
168}
169
170pub fn save(conn: &Connection, partial: &PartialTag) -> Result<Tag> {
171    // Allow use of rarray() in SQL queries
172    rusqlite::vtab::array::load_module(conn)?;
173
174    let mut tag = match find_by_id(conn, partial.id)? {
175        Some(t) => t,
176        None => return Err(rusqlite::Error::QueryReturnedNoRows)
177    };
178
179    let mut new_tag_aliases = vec![];
180
181    if tag.name != partial.name {
182        // Update game primary fields
183        let stmt = "UPDATE game
184        SET platformName = ?
185        WHERE game.id IN (
186            SELECT gameId FROM game_platforms_platform WHERE platformId = ?   
187        )";
188        conn.execute(stmt, params![partial.name, tag.id])?;
189    }
190
191    tag.apply_partial(partial);
192
193    let mut stmt = conn.prepare("SELECT platformId FROM platform_alias WHERE name = ?")?;
194
195    // Check for collisions before updating
196    for alias in tag.aliases.clone() {
197        let existing_platform_id = stmt.query_row(params![alias], |row| row.get::<_, i64>(0)).optional()?;
198        match existing_platform_id {
199            Some(id) => {
200                if id != tag.id {
201                    return Err(rusqlite::Error::QueryReturnedNoRows) // TODO: Make this a proper error
202                }
203            },
204            None => {
205                new_tag_aliases.push(alias);
206            }
207        }
208    }
209
210    // Apply flat edits
211    stmt = conn.prepare("UPDATE platform SET description = ?, dateModified = ? WHERE id = ?")?;
212    stmt.execute(params![tag.description, tag.date_modified, tag.id])?;
213
214    // Remove old aliases
215    let mut stmt = "DELETE FROM platform_alias WHERE platformId = ? AND name NOT IN rarray(?)";
216    let alias_rc = Rc::new(tag.aliases.iter().map(|v| Value::from(v.clone())).collect::<Vec<Value>>());
217    conn.execute(stmt, params![tag.id, alias_rc])?;
218
219    // Add new aliases
220    for alias in new_tag_aliases {
221        stmt = "INSERT INTO platform_alias (name, platformId) VALUES (?, ?)";
222        conn.execute(stmt, params![alias, tag.id])?;
223    }
224
225    // Update primary alias id
226    stmt = "UPDATE platform SET primaryAliasId = (SELECT id FROM platform_alias WHERE name = ?) WHERE id = ?";
227    conn.execute(stmt, params![tag.name, tag.id])?;
228
229    // Update game platformsStr fields
230    stmt = "UPDATE game
231    SET platformsStr = (
232        SELECT IFNULL(string_agg(pa.name, '; '), '')
233        FROM game_platforms_platform gpp
234        JOIN platform p ON gpp.platformId = p.id
235        JOIN platform_alias pa ON p.primaryAliasId = pa.id
236        WHERE gpp.gameId = game.id
237    ) WHERE game.id IN (
238        SELECT gameId FROM game_platforms_platform WHERE platformId = ?   
239    )";
240    conn.execute(stmt, params![tag.id])?;
241
242    match find_by_id(&conn, tag.id)? {
243        Some(t) => Ok(t),
244        None => return Err(rusqlite::Error::QueryReturnedNoRows)
245    }
246}
247
248pub fn delete(conn: &Connection, name: &str) -> Result<()> {
249    let tag = find_by_name(conn, name)?;
250    match tag {
251        Some(tag) => {
252            let mut stmt = "DELETE FROM platform_alias WHERE platformId = ?";
253            conn.execute(stmt, params![tag.id])?;
254
255            stmt = "DELETE FROM platform WHERE id = ?";
256            conn.execute(stmt, params![tag.id])?;
257
258            stmt = "UPDATE game
259            SET platformName = ?
260            WHERE game.id IN (
261                SELECT gameId FROM game_platforms_platform WHERE platformId = ?   
262            )";
263            conn.execute(stmt, params!["", tag.id])?;
264
265            // Update game platformsStr fields
266            stmt = "UPDATE game
267            SET platformsStr = (
268                SELECT IFNULL(string_agg(pa.name, '; '), '')
269                FROM game_platforms_platform gpp
270                JOIN platform p ON gpp.platformId = p.id
271                JOIN platform_alias pa ON p.primaryAliasId = pa.id
272                WHERE gpp.gameId = game.id
273            ) WHERE game.id IN (
274                SELECT gameId FROM game_platforms_platform WHERE platformId = ?   
275            )";
276            conn.execute(stmt, params![tag.id])?;
277
278            stmt = "DELETE FROM game_platforms_platform WHERE platformId = ?";
279            conn.execute(stmt, params![tag.id])?;
280
281            Ok(())
282        },
283        None => Err(rusqlite::Error::QueryReturnedNoRows),
284    }
285}
286
287pub fn search_platform_suggestions(
288    conn: &Connection,
289    partial: &str,
290) -> Result<Vec<TagSuggestion>> {
291    let mut suggestions = vec![];
292
293    let query = "SELECT sugg.tagId, sugg.matched_alias, count(game_tag.gameId) as gameCount, sugg.primary_alias FROM (
294        SELECT 
295			ta1.platformId as tagId,
296			ta1.name AS matched_alias,
297			ta2.name AS primary_alias
298		FROM 
299			platform_alias ta1
300		JOIN 
301        platform t ON ta1.platformId = t.id
302		JOIN 
303        platform_alias ta2 ON t.primaryAliasId = ta2.id
304		WHERE 
305			ta1.name LIKE ?
306    ) sugg
307    LEFT JOIN game_platforms_platform game_tag ON game_tag.platformId = sugg.tagId
308    GROUP BY sugg.matched_alias
309    ORDER BY COUNT(game_tag.gameId) DESC, sugg.matched_alias ASC";
310
311    let mut stmt = conn.prepare(&query)?;
312    let mut likeable = String::from(partial);
313    likeable.push_str("%");
314    let results = stmt.query_map(params![&likeable], |row| {
315        Ok(TagSuggestion {
316            id: row.get(0)?,
317            matched_from: row.get(1)?,
318            games_count: row.get(2)?,
319            name: row.get(3)?,
320            category: None,
321        })
322    })?;
323
324    for sugg in results {
325        suggestions.push(sugg?);
326    }
327
328    Ok(suggestions)
329}
flashpoint_archive/platform/mod.rs

flashpoint_archive/platform/
mod.rs
