game_manager_lib/services/integration/pcgamingwiki/
db.rs

1//! Persistência dos dados do PCGamingWiki no banco SQLite local.
2//!
3//! Os dados são armazenados na tabela `game_extras` sem TTL — tratados como
4//! características fixas do jogo e atualizados apenas por invalidação explícita
5//! via [`invalidate_pcgw_data`].
6
7use crate::models::{GameDataPath, GameExtras, PcgwScrapedData, SystemRequirements};
8use rusqlite::{params, Connection};
9use tracing::{info, warn};
10
11// === INICIALIZAÇÃO DO BANCO ===
12
13/// Cria a tabela `game_extras`, `system_requirements` e `game_data_paths` em `games.db` se ainda não existirem.
14///
15/// Chamada uma vez durante a inicialização do banco principal.
16pub fn initialize_pcgamingwiki_tables(conn: &Connection) -> Result<(), String> {
17    conn.execute_batch(
18        "CREATE TABLE IF NOT EXISTS game_extras (
19            steam_app_id            TEXT PRIMARY KEY,
20            pcgw_page_id            TEXT,
21            pcgw_page_name          TEXT,
22            engine                  TEXT,
23            available_on            TEXT,
24            dx_versions             TEXT,
25            vulkan_versions         TEXT,
26            opengl_versions         TEXT,
27            win64                   TEXT,
28            linux64                 TEXT,
29            macos_arm               TEXT,
30            macos_intel64           TEXT,
31            ray_tracing             TEXT,
32            upscaling               TEXT,
33            frame_gen               TEXT,
34            ultrawidescreen         TEXT,
35            four_k_support          TEXT,
36            hdr                     TEXT,
37            high_fps                TEXT,
38            fov                     TEXT,
39            borderless_windowed     TEXT,
40            color_blind             TEXT,
41            controller_support      TEXT,
42            full_controller         TEXT,
43            playstation_controllers TEXT,
44            xinput_controllers      TEXT,
45            surround_sound          TEXT,
46            subtitles               TEXT,
47            closed_captions         TEXT,
48            has_save_data           TEXT,
49            has_config_data         TEXT,
50            languages_interface     TEXT,
51            languages_audio         TEXT,
52            languages_subtitles     TEXT,
53            fetched_at              TEXT
54        );
55
56        CREATE INDEX IF NOT EXISTS idx_game_extras_fetched_at
57            ON game_extras(fetched_at);
58
59        CREATE TABLE IF NOT EXISTS system_requirements (
60            id           INTEGER PRIMARY KEY AUTOINCREMENT,
61            steam_app_id TEXT NOT NULL,
62            os_family    TEXT NOT NULL,
63            tier_title   TEXT,
64            target       TEXT,
65            min_os       TEXT,
66            min_cpu      TEXT,
67            min_cpu2     TEXT,
68            min_ram      TEXT,
69            min_gpu      TEXT,
70            min_gpu2     TEXT,
71            min_vram     TEXT,
72            min_dx       TEXT,
73            min_storage  TEXT,
74            rec_os       TEXT,
75            rec_cpu      TEXT,
76            rec_cpu2     TEXT,
77            rec_ram      TEXT,
78            rec_gpu      TEXT,
79            rec_gpu2     TEXT,
80            rec_vram     TEXT,
81            rec_dx       TEXT,
82            rec_storage  TEXT,
83            fetched_at   TEXT NOT NULL
84        );
85
86        CREATE INDEX IF NOT EXISTS idx_sysreq_app_id
87            ON system_requirements(steam_app_id);
88
89        CREATE TABLE IF NOT EXISTS game_data_paths (
90            id           INTEGER PRIMARY KEY AUTOINCREMENT,
91            steam_app_id TEXT NOT NULL,
92            kind         TEXT NOT NULL,
93            os           TEXT NOT NULL,
94            raw_path     TEXT NOT NULL,
95            fetched_at   TEXT NOT NULL
96        );
97
98        CREATE INDEX IF NOT EXISTS idx_gamedata_app_id
99            ON game_data_paths(steam_app_id);",
100    )
101    .map_err(|e| format!("Erro ao criar tabelas do PCGamingWiki: {}", e))?;
102
103    Ok(())
104}
105
106// ===  DADOS DA API (fetch.rs) ===
107
108/// Retorna os dados do PCGamingWiki armazenados para o jogo.
109///
110/// Retorna `None` se o jogo nunca foi buscado (fetched_at IS NULL ou linha inexistente).
111/// Dados expirados **nunca** são descartados automaticamente — a invalidação é explícita.
112pub fn get_pcgw_data(conn: &Connection, steam_app_id: &str) -> Option<GameExtras> {
113    let result = conn.query_row(
114        "SELECT
115            steam_app_id,
116            pcgw_page_id,
117            pcgw_page_name,
118            engine,
119            available_on,
120
121            dx_versions,
122            vulkan_versions,
123            opengl_versions,
124
125            win64,
126            linux64,
127            macos_arm,
128            macos_intel64,
129
130            ray_tracing,
131            upscaling,
132            frame_gen,
133            ultrawidescreen,
134            four_k_support,
135            hdr,
136            high_fps,
137            fov,
138            borderless_windowed,
139            color_blind,
140
141            controller_support,
142            full_controller,
143            playstation_controllers,
144            xinput_controllers,
145
146            surround_sound,
147            subtitles,
148            closed_captions,
149
150            has_save_data,
151            has_config_data,
152
153            languages_interface,
154            languages_audio,
155            languages_subtitles,
156
157            fetched_at
158         FROM game_extras
159         WHERE steam_app_id = ?1
160           AND fetched_at IS NOT NULL",
161        params![steam_app_id],
162        |row| {
163            let parse_json_vec = |s: Option<String>| -> Option<Vec<String>> {
164                s.and_then(|v| serde_json::from_str(&v).ok())
165            };
166
167            Ok(GameExtras {
168                steam_app_id: row.get(0)?,
169                pcgw_page_id: row.get(1)?,
170                pcgw_page_name: row.get(2)?,
171                engine: row.get(3)?,
172                available_on: row.get(4)?,
173
174                // API
175                dx_versions: row.get(5)?,
176                vulkan_versions: row.get(6)?,
177                opengl_versions: row.get(7)?,
178
179                win64: row.get(8)?,
180                linux64: row.get(9)?,
181                macos_arm: row.get(10)?,
182                macos_intel64: row.get(11)?,
183
184                // Video
185                ray_tracing: row.get(12)?,
186                upscaling: row.get(13)?,
187                frame_gen: row.get(14)?,
188                ultrawidescreen: row.get(15)?,
189                four_k_support: row.get(16)?,
190                hdr: row.get(17)?,
191                high_fps: row.get(18)?,
192                fov: row.get(19)?,
193                borderless_windowed: row.get(20)?,
194                color_blind: row.get(21)?,
195
196                // Input
197                controller_support: row.get(22)?,
198                full_controller: row.get(23)?,
199                playstation_controllers: row.get(24)?,
200                xinput_controllers: row.get(25)?,
201
202                // Audio
203                surround_sound: row.get(26)?,
204                subtitles: row.get(27)?,
205                closed_captions: row.get(28)?,
206
207                // Tags
208                has_save_data: row.get(29)?,
209                has_config_data: row.get(30)?,
210
211                // L10n
212                languages_interface: parse_json_vec(row.get(31)?),
213                languages_audio: parse_json_vec(row.get(32)?),
214                languages_subtitles: parse_json_vec(row.get(33)?),
215
216                fetched_at: row.get(34)?,
217            })
218        },
219    );
220
221    match result {
222        Ok(data) => Some(data),
223        Err(rusqlite::Error::QueryReturnedNoRows) => None,
224        Err(e) => {
225            warn!("Erro ao buscar game_extras para {}: {}", steam_app_id, e);
226            None
227        }
228    }
229}
230
231/// Salva ou atualiza os dados do PCGamingWiki para um jogo.
232pub fn save_pcgw_data(conn: &Connection, data: &GameExtras) -> Result<(), String> {
233    let serialize_vec = |v: &Option<Vec<String>>| -> Option<String> {
234        v.as_ref().and_then(|list| serde_json::to_string(list).ok())
235    };
236
237    conn.execute(
238        "INSERT OR REPLACE INTO game_extras (
239            steam_app_id,
240            pcgw_page_id,
241            pcgw_page_name,
242            engine,
243            available_on,
244
245            dx_versions,
246            vulkan_versions,
247            opengl_versions,
248
249            win64,
250            linux64,
251            macos_arm,
252            macos_intel64,
253
254            ray_tracing,
255            upscaling,
256            frame_gen,
257            ultrawidescreen,
258            four_k_support,
259            hdr,
260            high_fps,
261            fov,
262            borderless_windowed,
263            color_blind,
264
265            controller_support,
266            full_controller,
267            playstation_controllers,
268            xinput_controllers,
269
270            surround_sound,
271            subtitles,
272            closed_captions,
273
274            has_save_data,
275            has_config_data,
276
277            languages_interface,
278            languages_audio,
279            languages_subtitles,
280
281            fetched_at
282        ) VALUES (
283            ?1,  ?2,  ?3,  ?4,  ?5,
284            ?6,  ?7,  ?8,
285            ?9,  ?10, ?11, ?12,
286            ?13, ?14, ?15, ?16, ?17,
287            ?18, ?19, ?20, ?21, ?22,
288            ?23, ?24, ?25, ?26,
289            ?27, ?28, ?29,
290            ?30, ?31,
291            ?32, ?33, ?34,
292            ?35
293        )",
294        params![
295            data.steam_app_id,
296            data.pcgw_page_id,
297            data.pcgw_page_name,
298            data.engine,
299            data.available_on,
300            data.dx_versions,
301            data.vulkan_versions,
302            data.opengl_versions,
303            data.win64,
304            data.linux64,
305            data.macos_arm,
306            data.macos_intel64,
307            data.ray_tracing,
308            data.upscaling,
309            data.frame_gen,
310            data.ultrawidescreen,
311            data.four_k_support,
312            data.hdr,
313            data.high_fps,
314            data.fov,
315            data.borderless_windowed,
316            data.color_blind,
317            data.controller_support,
318            data.full_controller,
319            data.playstation_controllers,
320            data.xinput_controllers,
321            data.surround_sound,
322            data.subtitles,
323            data.closed_captions,
324            data.has_save_data,
325            data.has_config_data,
326            serialize_vec(&data.languages_interface),
327            serialize_vec(&data.languages_audio),
328            serialize_vec(&data.languages_subtitles),
329            data.fetched_at,
330        ],
331    )
332    .map_err(|e| format!("Erro ao salvar game_extras: {}", e))?;
333
334    Ok(())
335}
336
337/// Invalida os dados de um jogo, forçando nova busca na próxima chamada.
338///
339/// Define `fetched_at = NULL` — a linha permanece no banco mas
340/// `get_pcgw_data` ignora registros sem `fetched_at`.
341pub fn invalidate_pcgw_data(conn: &Connection, steam_app_id: &str) -> Result<(), String> {
342    conn.execute(
343        "UPDATE game_extras SET fetched_at = NULL WHERE steam_app_id = ?1",
344        params![steam_app_id],
345    )
346    .map_err(|e| format!("Erro ao invalidar game_extras: {}", e))?;
347
348    info!("game_extras invalidado para steam_app_id={}", steam_app_id);
349    Ok(())
350}
351
352// ===  DADOS OBTIDOS VIA SCRAPING DA PÁGINA (scraper.rs) ===
353
354/// Salva os dados raspados no banco, substituindo entradas anteriores do jogo.
355///
356/// Usa DELETE + INSERT em vez de REPLACE porque os dados têm múltiplas linhas
357/// por jogo (não há PK natural além de `steam_app_id + os_family + tier_title`).
358pub fn save_scraped_data(
359    conn: &Connection,
360    steam_app_id: &str,
361    data: &PcgwScrapedData,
362) -> Result<(), String> {
363    use chrono::Utc;
364    use rusqlite::params;
365
366    let now = Utc::now().to_rfc3339();
367
368    // Remove dados anteriores deste jogo antes de reinserir
369    conn.execute(
370        "DELETE FROM system_requirements WHERE steam_app_id = ?1",
371        params![steam_app_id],
372    )
373    .map_err(|e| format!("Erro ao limpar system_requirements: {}", e))?;
374
375    conn.execute(
376        "DELETE FROM game_data_paths WHERE steam_app_id = ?1",
377        params![steam_app_id],
378    )
379    .map_err(|e| format!("Erro ao limpar game_data_paths: {}", e))?;
380
381    // Insere requisitos de sistema
382    let mut sysreq_stmt = conn
383        .prepare(
384            "INSERT INTO system_requirements (
385                steam_app_id, os_family, tier_title, target,
386                min_os, min_cpu, min_cpu2, min_ram, min_gpu, min_gpu2, min_vram, min_dx, min_storage,
387                rec_os, rec_cpu, rec_cpu2, rec_ram, rec_gpu, rec_gpu2, rec_vram, rec_dx, rec_storage,
388                fetched_at
389            ) VALUES (
390                ?1,  ?2,  ?3,  ?4,
391                ?5,  ?6,  ?7,  ?8,  ?9,  ?10, ?11, ?12, ?13,
392                ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22,
393                ?23
394            )",
395        )
396        .map_err(|e| format!("Erro ao preparar insert de system_requirements: {}", e))?;
397
398    for req in &data.system_requirements {
399        sysreq_stmt
400            .execute(params![
401                steam_app_id,
402                req.os_family,
403                req.tier_title,
404                req.target,
405                req.min_os,
406                req.min_cpu,
407                req.min_cpu2,
408                req.min_ram,
409                req.min_gpu,
410                req.min_gpu2,
411                req.min_vram,
412                req.min_dx,
413                req.min_storage,
414                req.rec_os,
415                req.rec_cpu,
416                req.rec_cpu2,
417                req.rec_ram,
418                req.rec_gpu,
419                req.rec_gpu2,
420                req.rec_vram,
421                req.rec_dx,
422                req.rec_storage,
423                now,
424            ])
425            .map_err(|e| {
426                let msg = format!("Erro ao inserir system_requirement: {}", e);
427                tracing::error!("{}", msg);
428                msg
429            })?;
430    }
431
432    // Insere caminhos de game data
433    let mut path_stmt = conn
434        .prepare(
435            "INSERT INTO game_data_paths (steam_app_id, kind, os, raw_path, fetched_at)
436             VALUES (?1, ?2, ?3, ?4, ?5)",
437        )
438        .map_err(|e| format!("Erro ao preparar insert de game_data_paths: {}", e))?;
439
440    for path in &data.game_data_paths {
441        path_stmt
442            .execute(params![
443                steam_app_id,
444                path.kind,
445                path.os,
446                path.raw_path,
447                now,
448            ])
449            .map_err(|e| format!("Erro ao inserir game_data_path: {}", e))?;
450    }
451
452    Ok(())
453}
454
455/// Retorna os requisitos de sistema salvos para um jogo.
456/// Retorna vetor vazio se nunca foram buscados.
457pub fn get_system_requirements(conn: &Connection, steam_app_id: &str) -> Vec<SystemRequirements> {
458    let mut stmt = match conn.prepare(
459        "SELECT os_family, tier_title, target,
460                min_os, min_cpu, min_cpu2, min_ram, min_gpu, min_gpu2, min_vram, min_dx, min_storage,
461                rec_os, rec_cpu, rec_cpu2, rec_ram, rec_gpu, rec_gpu2, rec_vram, rec_dx, rec_storage
462         FROM system_requirements
463         WHERE steam_app_id = ?1
464         ORDER BY id ASC",
465    ) {
466        Ok(s) => s,
467        Err(_) => return Vec::new(),
468    };
469
470    let iter = stmt.query_map(rusqlite::params![steam_app_id], |row| {
471        Ok(SystemRequirements {
472            steam_app_id: steam_app_id.to_string(),
473            os_family: row.get(0)?,
474            tier_title: row.get(1)?,
475            target: row.get(2)?,
476            min_os: row.get(3)?,
477            min_cpu: row.get(4)?,
478            min_cpu2: row.get(5)?,
479            min_ram: row.get(6)?,
480            min_gpu: row.get(7)?,
481            min_gpu2: row.get(8)?,
482            min_vram: row.get(9)?,
483            min_dx: row.get(10)?,
484            min_storage: row.get(11)?,
485            rec_os: row.get(12)?,
486            rec_cpu: row.get(13)?,
487            rec_cpu2: row.get(14)?,
488            rec_ram: row.get(15)?,
489            rec_gpu: row.get(16)?,
490            rec_gpu2: row.get(17)?,
491            rec_vram: row.get(18)?,
492            rec_dx: row.get(19)?,
493            rec_storage: row.get(20)?,
494        })
495    });
496
497    match iter {
498        Ok(rows) => rows.filter_map(|r| r.ok()).collect(),
499        Err(_) => Vec::new(),
500    }
501}
502
503/// Retorna os caminhos de game data salvos para um jogo.
504/// Retorna vetor vazio se nunca foram buscados.
505pub fn get_game_data_paths(conn: &rusqlite::Connection, steam_app_id: &str) -> Vec<GameDataPath> {
506    let mut stmt = match conn.prepare(
507        "SELECT kind, os, raw_path
508         FROM game_data_paths
509         WHERE steam_app_id = ?1
510         ORDER BY id ASC",
511    ) {
512        Ok(s) => s,
513        Err(_) => return Vec::new(),
514    };
515
516    let iter = stmt.query_map(rusqlite::params![steam_app_id], |row| {
517        Ok(GameDataPath {
518            steam_app_id: steam_app_id.to_string(),
519            kind: row.get(0)?,
520            os: row.get(1)?,
521            raw_path: row.get(2)?,
522            expanded_path: None,
523        })
524    });
525
526    match iter {
527        Ok(rows) => rows.filter_map(|r| r.ok()).collect(),
528        Err(_) => Vec::new(),
529    }
530}
game_manager_lib/services/integration/pcgamingwiki/db.rs

game_manager_lib/services/integration/pcgamingwiki/
db.rs
