use super::model::*;
use rayon::prelude::*;
use sqlx::migrate::Migrator;
use sqlx::prelude::*;
use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode};
use sqlx::SqliteConnection;
use std::convert::TryFrom;
use std::str::FromStr;
static MIGRATOR: Migrator = sqlx::migrate!();
pub async fn establish_connection(url: &str) -> SqliteConnection {
let mut connection: SqliteConnection = SqliteConnectOptions::from_str(url)
.unwrap()
.foreign_keys(true)
.journal_mode(SqliteJournalMode::Wal)
.connect()
.await
.expect(&format!("Error connecting to {}", url));
connection
.execute(
"
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA wal_checkpoint(TRUNCATE);
",
)
.await
.expect("Failed to setup the database");
MIGRATOR
.run(&mut connection)
.await
.expect("Failed to run database migrations");
connection
}
pub async fn close_connection(connection: &mut SqliteConnection) {
connection
.execute("PRAGMA optimize")
.await
.expect("Failed to optimize the database");
}
pub async fn create_system(connection: &mut SqliteConnection, system_xml: &SystemXml) -> i64 {
sqlx::query!(
"
INSERT INTO systems (name, description, version)
VALUES (?, ?, ?)
",
system_xml.name,
system_xml.description,
system_xml.version,
)
.execute(connection)
.await
.expect("Error while creating system")
.last_insert_rowid()
}
pub async fn update_system(connection: &mut SqliteConnection, id: i64, system_xml: &SystemXml) {
sqlx::query!(
"
UPDATE systems
SET name = ?, description = ?, version = ?
WHERE id = ?
",
system_xml.name,
system_xml.description,
system_xml.version,
id,
)
.execute(connection)
.await
.expect(&format!("Error while updating system with id {}", id));
}
pub async fn find_systems(connection: &mut SqliteConnection) -> Vec<System> {
sqlx::query_as!(
System,
"
SELECT *
FROM systems
ORDER BY name
",
)
.fetch_all(connection)
.await
.expect("Error while finding systems")
}
pub async fn find_system_by_id(connection: &mut SqliteConnection, id: i64) -> System {
sqlx::query_as!(
System,
"
SELECT *
FROM systems
WHERE id = ?
",
id,
)
.fetch_one(connection)
.await
.expect(&format!("Error while finding system with id {}", id))
}
pub async fn find_system_by_name(connection: &mut SqliteConnection, name: &str) -> Option<System> {
sqlx::query_as!(
System,
"
SELECT *
FROM systems
WHERE name = ?
",
name,
)
.fetch_optional(connection)
.await
.expect(&format!("Error while finding system with name {}", name))
}
pub async fn create_game<'a>(
connection: &mut SqliteConnection,
game_xml: &GameXml,
regions: &str,
system_id: i64,
parent_id: Option<i64>,
) -> i64 {
sqlx::query!(
"
INSERT INTO games (name, description, regions, system_id, parent_id)
VALUES (?, ?, ?, ?, ?)
",
game_xml.name,
game_xml.description,
regions,
system_id,
parent_id,
)
.execute(connection)
.await
.expect("Error while creating game")
.last_insert_rowid()
}
pub async fn update_game(
connection: &mut SqliteConnection,
id: i64,
game_xml: &GameXml,
regions: &str,
system_id: i64,
parent_id: Option<i64>,
) {
sqlx::query!(
"
UPDATE games
SET name = ?, description = ?, regions = ?, system_id = ?, parent_id = ?
WHERE id = ?
",
game_xml.name,
game_xml.description,
regions,
system_id,
parent_id,
id,
)
.execute(connection)
.await
.expect(&format!("Error while updating game with id {}", id));
}
pub async fn find_games(connection: &mut SqliteConnection) -> Vec<Game> {
sqlx::query_as!(
Game,
"
SELECT *
FROM games
ORDER BY name
",
)
.fetch_all(connection)
.await
.expect("Error while finding games")
}
pub async fn find_games_by_system_id(
connection: &mut SqliteConnection,
system_id: i64,
) -> Vec<Game> {
sqlx::query_as!(
Game,
"
SELECT *
FROM games
WHERE system_id = ?
",
system_id,
)
.fetch_all(connection)
.await
.expect(&format!(
"Error while finding games with system id {}",
system_id
))
}
pub async fn find_games_by_ids<'a>(connection: &mut SqliteConnection, ids: &[i64]) -> Vec<Game> {
let sql = format!(
"
SELECT *
FROM games
WHERE id IN ({})
",
ids.par_iter()
.map(ToString::to_string)
.collect::<Vec<String>>()
.join(", ")
);
sqlx::query_as::<_, Game>(&sql)
.fetch_all(connection)
.await
.expect("Error while finding games")
}
pub async fn find_parent_games_by_system_id(
connection: &mut SqliteConnection,
system_id: i64,
) -> Vec<Game> {
sqlx::query_as!(
Game,
"
SELECT *
FROM games
WHERE system_id = ?
AND parent_id IS NULL
",
system_id,
)
.fetch_all(connection)
.await
.expect(&format!(
"Error while finding parent games with system id {}",
system_id
))
}
pub async fn find_clone_games_by_system_id(
connection: &mut SqliteConnection,
system_id: i64,
) -> Vec<Game> {
sqlx::query_as!(
Game,
"
SELECT *
FROM games
WHERE system_id = ?
AND parent_id IS NOT NULL
",
system_id,
)
.fetch_all(connection)
.await
.expect(&format!(
"Error while finding parent games with system id {}",
system_id
))
}
pub async fn find_game_by_name_and_system_id(
connection: &mut SqliteConnection,
name: &str,
system_id: i64,
) -> Option<Game> {
sqlx::query_as!(
Game,
"
SELECT *
FROM games
WHERE name = ?
AND system_id = ?
",
name,
system_id,
)
.fetch_optional(connection)
.await
.expect(&format!(
"Error while finding games with name {} and system id {}",
name, system_id
))
}
pub async fn delete_game_by_name_and_system_id(
connection: &mut SqliteConnection,
name: &str,
system_id: i64,
) {
sqlx::query!(
"
DELETE FROM games
WHERE name = ?
AND system_id = ?
",
name,
system_id,
)
.execute(connection)
.await
.expect(&format!(
"Error while deleting game with name {} and system_id {}",
name, system_id
));
}
pub async fn create_rom<'a>(
connection: &mut SqliteConnection,
rom_xml: &RomXml,
game_id: i64,
) -> i64 {
let crc = rom_xml.crc.to_lowercase();
let md5 = rom_xml.md5.to_lowercase();
let sha1 = rom_xml.sha1.to_lowercase();
sqlx::query!(
"
INSERT INTO roms (name, size, crc, md5, sha1, rom_status, game_id)
VALUES (?, ?, ?, ?, ?, ?, ?)
",
rom_xml.name,
rom_xml.size,
crc,
md5,
sha1,
rom_xml.status,
game_id,
)
.execute(connection)
.await
.expect("Error while creating rom")
.last_insert_rowid()
}
pub async fn update_rom(
connection: &mut SqliteConnection,
id: i64,
rom_xml: &RomXml,
game_id: i64,
) {
let crc = rom_xml.crc.to_lowercase();
let md5 = rom_xml.md5.to_lowercase();
let sha1 = rom_xml.sha1.to_lowercase();
sqlx::query!(
"
UPDATE roms
SET name = ?, size = ?, crc = ?, md5 = ?, sha1 = ?, rom_status = ?, game_id = ?
WHERE id = ?
",
rom_xml.name,
rom_xml.size,
crc,
md5,
sha1,
rom_xml.status,
game_id,
id,
)
.execute(connection)
.await
.expect(&format!("Error while updating rom with id {}", id));
}
pub async fn update_rom_romfile(connection: &mut SqliteConnection, id: i64, romfile_id: Option<i64>) {
sqlx::query!(
"
UPDATE roms
SET romfile_id = ?
WHERE id = ?
",
romfile_id,
id,
)
.execute(connection)
.await
.expect(&format!("Error while updating rom with id {}", id));
}
pub async fn find_roms(connection: &mut SqliteConnection) -> Vec<Rom> {
sqlx::query_as!(
Rom,
"
SELECT *
FROM roms
ORDER BY name
",
)
.fetch_all(connection)
.await
.expect("Error while finding roms")
}
pub async fn find_roms_by_game_id<'a>(connection: &mut SqliteConnection, game_id: i64) -> Vec<Rom> {
sqlx::query_as!(
Rom,
"
SELECT *
FROM roms
WHERE game_id = ?
ORDER BY name
",
game_id,
)
.fetch_all(connection)
.await
.expect(&format!("Error while finding rom with game id {}", game_id))
}
pub async fn find_roms_without_romfile_by_system_id(
connection: &mut SqliteConnection,
system_id: i64,
) -> Vec<Rom> {
sqlx::query_as!(
Rom,
"
SELECT *
FROM roms
WHERE romfile_id IS NULL
AND game_id IN (
SELECT id
FROM games
WHERE system_id = ?
)
ORDER BY name
",
system_id,
)
.fetch_all(connection)
.await
.expect("Error while finding roms without romfile")
}
pub async fn find_roms_without_romfile_by_game_ids<'a>(
connection: &mut SqliteConnection,
game_ids: &[i64],
) -> Vec<Rom> {
let sql = format!(
"
SELECT *
FROM roms
WHERE romfile_id IS NULL
AND game_id IN ({})
ORDER BY name
",
game_ids
.par_iter()
.map(ToString::to_string)
.collect::<Vec<String>>()
.join(", ")
);
sqlx::query_as::<_, Rom>(&sql)
.fetch_all(connection)
.await
.expect("Error while finding roms with romfile")
}
pub async fn find_roms_with_romfile_by_system_id<'a>(
connection: &mut SqliteConnection,
system_id: i64,
) -> Vec<Rom> {
sqlx::query_as!(
Rom,
"
SELECT *
FROM roms
WHERE romfile_id IS NOT NULL
AND game_id IN (
SELECT id
FROM games
WHERE system_id = ?
)
ORDER BY name
",
system_id,
)
.fetch_all(connection)
.await
.expect("Error while finding roms with romfile")
}
pub async fn find_roms_with_romfile_by_system_id_and_name<'a>(
connection: &mut SqliteConnection,
system_id: i64,
name: &str,
) -> Vec<Rom> {
let sql = format!(
"
SELECT *
FROM roms
WHERE romfile_id IS NOT NULL
AND game_id IN (
SELECT id
FROM games
WHERE system_id = {}
AND name LIKE '%{}%'
)
ORDER BY name
",
system_id, name
);
sqlx::query_as::<_, Rom>(&sql)
.fetch_all(connection)
.await
.expect("Error while finding roms with romfile")
}
pub async fn find_roms_with_romfile_by_game_ids<'a>(
connection: &mut SqliteConnection,
game_ids: &[i64],
) -> Vec<Rom> {
let sql = format!(
"
SELECT *
FROM roms
WHERE romfile_id IS NOT NULL
AND game_id IN ({})
ORDER BY name
",
game_ids
.par_iter()
.map(ToString::to_string)
.collect::<Vec<String>>()
.join(", ")
);
sqlx::query_as::<_, Rom>(&sql)
.fetch_all(connection)
.await
.expect("Error while finding roms with romfile")
}
pub async fn find_rom_by_name_and_game_id<'a>(
connection: &mut SqliteConnection,
name: &str,
game_id: i64,
) -> Option<Rom> {
sqlx::query_as!(
Rom,
"
SELECT *
FROM roms
WHERE name = ?
AND game_id = ?
",
name,
game_id,
)
.fetch_optional(connection)
.await
.expect(&format!(
"Error while finding rom with name {} and game id {}",
name, game_id
))
}
pub async fn find_roms_by_size_and_crc_and_system_id(
connection: &mut SqliteConnection,
size: u64,
crc: &str,
system_id: i64,
) -> Vec<Rom> {
let size = i64::try_from(size).unwrap();
let crc = crc.to_lowercase();
sqlx::query_as!(
Rom,
"
SELECT r.id, r.name, r.size, r.crc, r.md5, r.sha1, r.rom_status, r.game_id, r.romfile_id
FROM roms AS r
JOIN games AS g ON r.game_id = g.id
WHERE r.size = ?
AND r.crc = ?
AND g.system_id = ?
ORDER BY r.name
",
size,
crc,
system_id,
)
.fetch_all(connection)
.await
.expect(&format!(
"Error while finding rom with size {} and CRC {} and system id {}",
size, crc, system_id
))
}
pub async fn delete_rom_by_name_and_game_id(
connection: &mut SqliteConnection,
name: &str,
game_id: i64,
) {
sqlx::query!(
"
DELETE FROM roms
WHERE name = ?
AND game_id = ?
",
name,
game_id,
)
.execute(connection)
.await
.expect(&format!(
"Error while deleting rom with name {} and game_id {}",
name, game_id
));
}
pub async fn create_romfile(connection: &mut SqliteConnection, path: &str) -> i64 {
sqlx::query!(
"
INSERT INTO romfiles (path)
VALUES (?)
",
path,
)
.execute(connection)
.await
.expect(&format!("Error while creating romfile"))
.last_insert_rowid()
}
pub async fn update_romfile(connection: &mut SqliteConnection, id: i64, path: &str) {
sqlx::query!(
"
UPDATE romfiles
SET path = ?
WHERE id = ?
",
path,
id,
)
.execute(connection)
.await
.expect(&format!("Error while updating romfile with id {}", id));
}
pub async fn find_romfiles(connection: &mut SqliteConnection) -> Vec<Romfile> {
sqlx::query_as!(
Romfile,
"
SELECT *
FROM romfiles
ORDER BY path
",
)
.fetch_all(connection)
.await
.expect(&format!("Error while finding romfiles"))
}
pub async fn find_romfiles_by_ids<'a>(
connection: &mut SqliteConnection,
ids: &[i64],
) -> Vec<Romfile> {
let sql = format!(
"
SELECT *
FROM romfiles
WHERE id IN ({})
",
ids.par_iter()
.map(ToString::to_string)
.collect::<Vec<String>>()
.join(", ")
);
sqlx::query_as::<_, Romfile>(&sql)
.fetch_all(connection)
.await
.expect(&format!("Error while finding romfiles"))
}
pub async fn find_romfiles_by_system_id<'a>(
connection: &mut SqliteConnection,
system_id: i64,
) -> Vec<Romfile> {
sqlx::query_as!(
Romfile,
"
SELECT *
FROM romfiles
WHERE id IN (
SELECT romfile_id
FROM roms
WHERE game_id IN (
SELECT id
FROM games
WHERE system_id = ?
)
)
",
system_id,
)
.fetch_all(connection)
.await
.expect(&format!("Error while finding romfiles in trash"))
}
pub async fn find_romfiles_in_trash<'a>(connection: &mut SqliteConnection) -> Vec<Romfile> {
sqlx::query_as!(
Romfile,
"
SELECT *
FROM romfiles
WHERE path LIKE \"%/Trash/%\"
ORDER BY path
",
)
.fetch_all(connection)
.await
.expect(&format!("Error while finding romfiles in trash"))
}
pub async fn find_romfile_by_path<'a>(
connection: &mut SqliteConnection,
path: &str,
) -> Option<Romfile> {
sqlx::query_as!(
Romfile,
"
SELECT *
FROM romfiles
WHERE path = ?
",
path,
)
.fetch_optional(connection)
.await
.expect(&format!("Error while finding romfile with path {}", path))
}
pub async fn find_romfile_by_id(connection: &mut SqliteConnection, id: i64) -> Romfile {
sqlx::query_as!(
Romfile,
"
SELECT *
FROM romfiles
WHERE id = ?
",
id,
)
.fetch_one(connection)
.await
.expect(&format!("Error while finding romfile with id {}", id))
}
pub async fn delete_romfile_by_id(connection: &mut SqliteConnection, id: i64) {
sqlx::query!(
"
DELETE FROM romfiles
WHERE id = ?
",
id,
)
.execute(connection)
.await
.expect(&format!("Error while deleting setting with id {}", id));
}
pub async fn delete_romfiles_without_rom<'a>(connection: &mut SqliteConnection) {
sqlx::query!(
"
DELETE
FROM romfiles
WHERE id NOT IN (
SELECT DISTINCT(romfile_id)
FROM roms
WHERE romfile_id IS NOT NULL
)
"
)
.execute(connection)
.await
.expect(&format!("Error while finding romfiles without rom"));
}
pub async fn create_header(
connection: &mut SqliteConnection,
detector_xml: &DetectorXml,
system_id: i64,
) -> i64 {
let start_byte = i64::from_str_radix(&detector_xml.rule.data.offset, 16).unwrap();
let size = i64::from_str_radix(&detector_xml.rule.start_offset, 16).unwrap();
sqlx::query!(
"
INSERT INTO headers (name, version, start_byte, size, hex_value, system_id)
VALUES (?, ?, ?, ?, ?, ?)
",
detector_xml.name,
detector_xml.version,
start_byte,
size,
detector_xml.rule.data.value,
system_id,
)
.execute(connection)
.await
.expect(&format!("Error while creating header"))
.last_insert_rowid()
}
pub async fn update_header(
connection: &mut SqliteConnection,
id: i64,
detector_xml: &DetectorXml,
system_id: i64,
) {
let start_byte = i64::from_str_radix(&detector_xml.rule.data.offset, 16).unwrap();
let size = i64::from_str_radix(&detector_xml.rule.start_offset, 16).unwrap();
sqlx::query!(
"
UPDATE headers
SET name = ?, version = ?, start_byte = ?, size = ?, hex_value = ?, system_id = ?
WHERE id = ?
",
detector_xml.name,
detector_xml.version,
start_byte,
size,
detector_xml.rule.data.value,
system_id,
id,
)
.execute(connection)
.await
.expect(&format!("Error while updating header with id {}", id));
}
pub async fn find_header_by_system_id(
connection: &mut SqliteConnection,
system_id: i64,
) -> Option<Header> {
sqlx::query_as!(
Header,
"
SELECT *
FROM headers
WHERE system_id = ?
",
system_id,
)
.fetch_optional(connection)
.await
.expect(&format!(
"Error while finding header with system id {}",
system_id
))
}
pub async fn create_setting(connection: &mut SqliteConnection, key: &str, value: Option<String>) {
sqlx::query!(
"
INSERT INTO settings (key, value)
VALUES (?, ?)
",
key,
value,
)
.execute(connection)
.await
.expect(&format!("Error while creating setting"));
}
pub async fn update_setting(connection: &mut SqliteConnection, id: i64, value: Option<String>) {
sqlx::query!(
"
UPDATE settings
SET value = ?
WHERE id = ?
",
value,
id,
)
.execute(connection)
.await
.expect(&format!("Error while updating setting with id {}", id));
}
pub async fn find_settings(connection: &mut SqliteConnection) -> Vec<Setting> {
sqlx::query_as!(
Setting,
"
SELECT *
FROM settings
ORDER BY key
",
)
.fetch_all(connection)
.await
.expect("Error while finding settings")
}
pub async fn find_setting_by_key(connection: &mut SqliteConnection, key: &str) -> Option<Setting> {
sqlx::query_as!(
Setting,
"
SELECT *
FROM settings
WHERE key = ?
",
key,
)
.fetch_optional(connection)
.await
.expect(&format!("Error while finding setting with key {}", key))
}
pub async fn delete_setting_by_key(connection: &mut SqliteConnection, key: &str) {
sqlx::query!(
"
DELETE FROM settings
WHERE key = ?
",
key,
)
.execute(connection)
.await
.expect(&format!("Error while deleting setting with key {}", key));
}