use std::path::Path;
use crate::migrations;
use anyhow::{Context, Result};
pub fn open_from_file(db_path: &Path) -> Result<(rusqlite::Connection, Vec<crate::Table>)> {
anyhow::ensure!(
db_path.extension().is_some_and(|ext| ext == "db"),
"suspicious db path extension: {db_path:?}"
);
// no SQLITE_OPEN_CREATE and SQLITE_OPEN_URI
let db = rusqlite::Connection::open_with_flags(
db_path,
rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE | rusqlite::OpenFlags::SQLITE_OPEN_NO_MUTEX,
)
.context("failed to open db")?;
validate_song_db(&db).context("likely an invalid song.db file")?;
maybe_apply_migrations(&db)?;
let tables = load_tables(&db).context("Failed to read tables from DB")?;
Ok((db, tables))
}
/// * `tables` - all tables to be present in DB. Those not in the list will be removed from db.
/// Playlist IDs will be updated in-place if necessary.
pub fn save_db(conn: &rusqlite::Connection, tables: &mut [crate::Table]) -> Result<()> {
load_table_urls(conn)
.context("failed to fetch the list of table URLs from db")?
.into_iter()
.filter(|existing| !tables.iter().any(|new| new.0.web_url.0 == *existing))
.try_for_each(|page_url| {
log::debug!("Deleting table from DB, page_url={page_url}");
conn.execute(
"DELETE FROM lr2oxytabler_playlist WHERE page_url = ?",
rusqlite::params![page_url],
)
.with_context(|| format!("failed to delete playlist WHERE page_url={page_url}"))
.map(|_| ())
})
.context("failed to delete old tables from the DB")?;
let table_ids = tables.iter().try_fold(
Vec::with_capacity(tables.len()),
|mut acc, table| -> Result<_> {
acc.push(
upsert_table(conn, table)
.with_context(|| format!("failed to upsert playlist {}", table.0.web_url.0))?,
);
Ok(acc)
},
)?;
for (table, id) in tables.iter_mut().zip(table_ids) {
table.1.playlist_id = Some(id);
}
Ok(())
}
pub fn update_tags_inplace(conn: &rusqlite::Connection) -> Result<()> {
let write_tags = r#"
UPDATE song SET tag = (
SELECT GROUP_CONCAT(TRIM(COALESCE(user_symbol, symbol) || folder), ", ")
FROM lr2oxytabler_playlist_entry
INNER JOIN
lr2oxytabler_playlist
ON
lr2oxytabler_playlist_entry.playlist_id
= lr2oxytabler_playlist.playlist_id
WHERE song.hash = lr2oxytabler_playlist_entry.md5
)
"#;
let rows = conn
.execute(write_tags, [])
.context("failed to set song tags to playlist entry levels")?;
log::debug!("Wrote tags to {rows} songs");
Ok(())
}
/// Check that a valid song.db was supplied and not some other random database file.
fn validate_song_db(conn: &rusqlite::Connection) -> Result<()> {
match conn.query_row(
"SELECT 1 FROM sqlite_master WHERE type = 'table' and name = 'song'",
[],
|row| row.get::<_, usize>(0),
) {
Ok(_) => Ok(()),
Err(rusqlite::Error::QueryReturnedNoRows) => {
anyhow::bail!("supplied database seems not to be an LR2 song.db")
}
Err(e) => Err(e).context("failed to check song.db validity"),
}
}
fn load_entries(
conn: &rusqlite::Connection,
playlist_id: crate::PlaylistId,
) -> Result<Vec<crate::TableEntry>> {
let mut stmt = conn
.prepare("SELECT md5, folder FROM lr2oxytabler_playlist_entry WHERE playlist_id = ?")
.context("failed to prepare read existing playlist entries")?;
let mut rows = stmt.query([playlist_id.0])?;
let mut out = Vec::<crate::TableEntry>::new();
while let Some(row) = rows.next()? {
out.push(crate::TableEntry {
md5: row.get(0)?,
level: row.get(1)?,
});
}
Ok(out)
}
fn load_tables(conn: &rusqlite::Connection) -> Result<Vec<crate::Table>> {
let mut stmt = conn
.prepare(
"SELECT
playlist_id,
name,
symbol,
folder_order,
page_url,
header_url,
data_url,
last_update,
user_symbol
FROM
lr2oxytabler_playlist
ORDER BY
name",
)
.context("failed to prepare read existing playlists")?;
let mut rows = stmt.query([])?;
let mut out = Vec::<crate::Table>::new();
while let Some(row) = rows.next()? {
let playlist_id: usize = row.get(0)?;
let folder_order: String = row.get(3)?;
out.push(crate::Table(
crate::TableData {
web_url: crate::ResolvedUrl::try_from_str(row.get(4)?)?,
name: row.get(1)?,
symbol: row.get(2)?,
data_url: crate::ResolvedUrl::try_from_str(row.get(6)?)?,
entries: load_entries(conn, crate::PlaylistId(playlist_id))
.context("failed to load playlist entries")?,
folder_order: serde_json::from_str(&folder_order)
.context("failed to parse folder_order")?,
header_url: crate::ResolvedUrl::try_from_str(row.get(5)?)?,
},
crate::TableAddData {
last_update: row.get(7)?,
playlist_id: Some(crate::PlaylistId(playlist_id)),
user_symbol: row.get(8)?,
edited_symbol: None,
edited_url: None,
pending_removal: false,
},
));
}
Ok(out)
}
fn load_table_urls(conn: &rusqlite::Connection) -> Result<Vec<String>> {
let mut stmt = conn
.prepare("SELECT page_url FROM lr2oxytabler_playlist")
.context("failed to prepare SELECT page_url")?;
let mut rows = stmt.query([])?;
let mut out = Vec::<String>::new();
while let Some(row) = rows.next()? {
out.push(row.get(0)?);
}
Ok(out)
}
fn upsert_table(conn: &rusqlite::Connection, table: &crate::Table) -> Result<crate::PlaylistId> {
let add_data = &table.1;
let table = &table.0;
let folder_order: String = serde_json::to_string(&table.folder_order)
.context("failed to format table folder order")?;
let id = conn
.query_row(
"INSERT INTO
lr2oxytabler_playlist (
name,
symbol,
folder_order,
page_url,
header_url,
data_url,
last_update,
user_symbol
)
VALUES
(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
ON CONFLICT (page_url) DO UPDATE
SET
name = ?1,
symbol = ?2,
folder_order = ?3,
header_url = ?5,
data_url = ?6,
last_update = ?7,
user_symbol = ?8 RETURNING playlist_id",
rusqlite::params![
&table.name,
&table.symbol,
&folder_order,
&table.web_url.0,
&table.header_url.0,
&table.data_url.0,
&add_data.last_update,
&add_data.user_symbol,
],
|row| row.get(0),
)
.with_context(|| format!("failed to insert playlist into db; {:?}", &table))?;
anyhow::ensure!(
add_data.playlist_id.is_none_or(|x| x.0 == id),
"playlist suddenly changed it's ID"
);
conn.execute(
"DELETE FROM lr2oxytabler_playlist_entry WHERE playlist_id = ?",
rusqlite::params![id],
)
.with_context(|| format!("failed to delete old entries for playlist_id={id}"))?;
let mut insert_entry = conn
.prepare(
"INSERT INTO lr2oxytabler_playlist_entry(playlist_id, md5, folder) VALUES (?, ?, ?)",
)
.context("failed to prepare statement")?;
for entry in &table.entries {
insert_entry
.execute(rusqlite::params![id, &entry.md5, &entry.level])
.with_context(|| format!("failed to insert playlist entry into db; {:?}", &entry))?;
}
Ok(crate::PlaylistId(id))
}
fn maybe_apply_migrations(conn: &rusqlite::Connection) -> Result<()> {
migrations::maybe_apply_migration(
conn,
&migrations::Migration {
#[allow(clippy::unreadable_literal)]
id: 20250101,
description: "init",
sql: "
CREATE TABLE IF NOT EXISTS lr2oxytable_playlist (
playlist_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR NOT NULL,
symbol VARCHAR NOT NULL,
folder_order JSONB NOT NULL,
page_url VARCHAR NOT NULL,
header_url VARCHAR NOT NULL,
data_url VARCHAR NOT NULL,
last_update BIGINT,
UNIQUE (page_url)
);
CREATE TABLE IF NOT EXISTS lr2oxytable_playlist_entry (
playlist_id INTEGER NOT NULL REFERENCES lr2oxytable_playlist (playlist_id) ON DELETE CASCADE,
md5 VARCHAR NOT NULL,
folder VARCHAR NOT NULL,
UNIQUE (md5, playlist_id, folder)
);
CREATE INDEX IF NOT EXISTS lr2oxytable_playlist_entry_folder ON lr2oxytable_playlist_entry (
playlist_id, folder
);
CREATE INDEX IF NOT EXISTS lr2oxytable_playlist_entry_md5 ON lr2oxytable_playlist_entry (
playlist_id, md5
);
",
},
)
.context("db migration 'init' failed")?;
migrations::maybe_apply_migration(
conn,
&migrations::Migration {
#[allow(clippy::unreadable_literal)]
id: 20250517,
description: "user symbol",
// nullable
sql: "ALTER TABLE lr2oxytable_playlist ADD COLUMN user_symbol VARCHAR;",
},
)
.context("db migration 'user symbol' failed")?;
migrations::maybe_apply_migration(
conn,
&migrations::Migration {
#[allow(clippy::unreadable_literal)]
id: 20250602,
description: "rename to tabler",
// NOTE: can't rename indexes in SQLite conveniently. Just ignore them.
sql: "
ALTER TABLE lr2oxytable_playlist RENAME TO lr2oxytabler_playlist;
ALTER TABLE lr2oxytable_playlist_entry RENAME TO lr2oxytabler_playlist_entry;
",
},
)
.context("db migration 'rename to tabler' failed")?;
Ok(())
}
#[cfg(test)]
mod tests {
fn create_song_db() -> rusqlite::Connection {
use super::maybe_apply_migrations;
let db = rusqlite::Connection::open_in_memory().unwrap();
maybe_apply_migrations(&db).unwrap();
// LR2 copy-paste
db.execute_batch("
CREATE TABLE song(hash TEXT ,title TEXT ,subtitle TEXT ,genre TEXT,artist TEXT,subartist TEXT,tag TEXT ,path TEXT primary key ,type INTEGER,folder TEXT,stagefile TEXT,banner TEXT,backbmp TEXT,parent TEXT,level INTEGER,difficulty INTEGER,maxbpm INTEGER,minbpm INTEGER,mode INTEGER,judge INTEGER,longnote INTEGER,bga INTEGER,random INTEGER,date INTEGER,favorite INTEGER,txt INTEGER,karinotes INTEGER,adddate INTEGER,exlevel INTEGER)
").unwrap();
db
}
fn insert_song(conn: &rusqlite::Connection, md5: &str) {
// loh
conn.execute(r#"
INSERT INTO "main"."song" ("hash", "title", "subtitle", "genre", "artist", "subartist", "tag", "path", "type", "folder", "stagefile", "banner", "backbmp", "parent", "level", "difficulty", "maxbpm", "minbpm", "mode", "judge", "longnote", "bga", "random", "date", "favorite", "txt", "karinotes", "adddate", "exlevel") VALUES (?, '3y3s', '', 'DANCE SPEED', '青龍', '', NULL, 'C:\Microbot\Bimbows', 0, 'deadbeef', '', '', '', 'deadbeef', 12, 4, 191, 191, 14, 2, 0, 1, 0, 1111111111, 0, 0, 3132, 1111111111, 0);
"#, rusqlite::params![md5]).unwrap();
}
#[test]
fn cascade_deletes_entries() {
use super::{load_tables, maybe_apply_migrations, save_db};
let db = rusqlite::Connection::open_in_memory().unwrap();
maybe_apply_migrations(&db).unwrap();
save_db(&db, &mut [crate::Table::empty().with_url("http://1").with_entry()]).unwrap();
assert_eq!(load_tables(&db).unwrap().len(), 1);
save_db(&db, &mut []).unwrap();
assert_eq!(load_tables(&db).unwrap().len(), 0);
}
#[test]
fn disallows_non_song_db() {
use super::validate_song_db;
assert!(validate_song_db(&create_song_db()).is_ok());
assert_eq!(
validate_song_db(&rusqlite::Connection::open_in_memory().unwrap())
.unwrap_err()
.to_string(),
"supplied database seems not to be an LR2 song.db"
);
}
// Necessary to work-around buggy folder updating in LR2.
#[test]
fn playlist_id_preserved_between_updates() {
use super::{load_tables, maybe_apply_migrations, save_db};
use crate::Table;
let db = rusqlite::Connection::open_in_memory().unwrap();
maybe_apply_migrations(&db).unwrap();
let tables = &mut [
Table::empty().with_url("http://1"),
Table::empty().with_url("http://2"),
];
save_db(&db, tables).unwrap();
let tables = load_tables(&db).unwrap();
let id1 = tables
.iter()
.find(|t| t.0.web_url.0 == "http://1")
.map(|t| t.1.playlist_id.unwrap())
.unwrap();
let id2 = tables
.iter()
.find(|t| t.0.web_url.0 == "http://2")
.map(|t| t.1.playlist_id.unwrap())
.unwrap();
let tables = &mut [
Table::empty().with_url("http://2"),
Table::empty().with_url("http://3"),
];
save_db(&db, tables).unwrap();
let tables = load_tables(&db).unwrap();
assert!(!tables.iter().any(|t| t.1.playlist_id.unwrap() == id1));
assert!(tables.iter().any(|t| t.1.playlist_id.unwrap() == id2));
}
#[test]
fn updates_tags() {
use super::{save_db, update_tags_inplace};
use crate::Table;
let md5 = "feedfeedfeedfeedfeedfeedfeedfeed";
let entry = crate::TableEntry {
md5: md5.to_string(),
level: " DELAYMASTER".to_string(),
};
let run = |table| {
let db = create_song_db();
save_db(&db, &mut [table]).unwrap();
insert_song(&db, md5);
update_tags_inplace(&db).unwrap();
db.query_row(
"SELECT tag FROM song WHERE hash = ?",
rusqlite::params![md5],
|row| Ok(row.get::<_, String>(0).unwrap()),
)
.unwrap()
};
{
let mut table = Table::empty();
table.0.entries = vec![entry.clone()];
assert_eq!(run(table), "DELAYMASTER");
}
{
let mut table = Table::empty().with_symbol("omg ");
table.0.entries = vec![entry.clone()];
assert_eq!(run(table), "omg DELAYMASTER");
}
{
let mut table = Table::empty().with_symbol("nope").with_user_symbol(" omg");
table.0.entries = vec![entry];
assert_eq!(run(table), "omg DELAYMASTER");
}
}
}