// NOTE: for rusqlite, don't use plain BEGIN and COMMIT as rollback will never be called if an
// error occurs there.
use std::path::Path;
use crate::migrations;
use anyhow::{Context as _, Result};
/// ID of the playlist in the database.
/// NOTE: ID must be preserved between database updates to avoid triggering folder reloading
/// related bugs in LR2.
//
// FIXME: lr2folder files must be preserved in the same way. It just happens that these don't
// appear/disappear in folder often. This case is not handled yet.
#[derive(Copy, Clone, Debug, PartialEq, Eq)]
pub struct TableId(pub usize);
pub fn open_from_file(db_path: &Path) -> Result<(rusqlite::Connection, Vec<crate::table::Table>)> {
anyhow::ensure!(
db_path.extension().is_some_and(|ext| ext == "db"),
"suspicious db path extension: {}",
db_path.display()
);
// 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.
pub fn save_db_raw(
conn: &rusqlite::Connection,
tables: &[crate::table::Table],
) -> Result<Vec<TableId>> {
load_table_urls_ids(conn)
.context("failed to fetch the list of table URLs from db")?
.into_iter()
.filter(|(existing_id, existing_url)| {
!tables.iter().any(|new| {
new.1.playlist_id.map_or_else(
|| new.0.web_url.as_str() == existing_url,
|id| id == *existing_id,
)
})
})
.try_for_each(|(id, page_url)| {
log::debug!("Deleting table from DB, id={} page_url={}", id.0, page_url);
conn.execute(
"DELETE FROM lr2oxytabler_playlist WHERE playlist_id = ?",
rusqlite::params![id.0],
)
.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()
.map(|table| {
upsert_table(conn, table)
.with_context(|| format!("failed to upsert playlist {}", table.0.web_url.as_str()))
})
.collect::<Result<Vec<_>>>()?;
Ok(table_ids)
}
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(())
}
pub fn load_song(conn: &rusqlite::Connection, md5: &str) -> Result<Option<String>> {
let mut stmt = conn
.prepare("SELECT title, subtitle FROM song WHERE hash = ?1")
.context("failed to prepare SELECT in load_song")?;
let mut rows = stmt.query([md5])?;
if let Some(row) = rows.next()? {
let title = row.get::<_, String>(0)?;
let subtitle = row.get::<_, String>(1)?;
if subtitle.is_empty() {
return Ok(Some(title));
}
return Ok(Some(format!("{title} {subtitle}")));
}
Ok(None)
}
/// 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: TableId,
) -> Result<Vec<crate::table::Entry>> {
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::table::Entry>::new();
while let Some(row) = rows.next()? {
out.push(crate::table::Entry {
md5: row.get(0)?,
level: row.get(1)?,
});
}
Ok(out)
}
fn load_tables(conn: &rusqlite::Connection) -> Result<Vec<crate::table::Table>> {
let mut stmt = conn
.prepare(
"SELECT
playlist_id,
name,
symbol,
folder_order,
page_url,
last_update,
user_name,
user_symbol,
output
FROM
lr2oxytabler_playlist
ORDER BY
name",
)
.context("failed to prepare read existing playlists")?;
let mut rows = stmt.query([])?;
let mut out = Vec::<crate::table::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::Table(
crate::table::Data {
entries: load_entries(conn, TableId(playlist_id))
.context("failed to load playlist entries")?,
folder_order: serde_json::from_str(&folder_order)
.context("failed to parse folder_order")?,
name: row.get(1)?,
symbol: row.get(2)?,
web_url: row.get::<_, String>(4)?.try_into()?,
},
crate::table::Context {
summary_changelog: String::new(),
full_changelog: String::new(),
entry_diff_to_save_to_db: vec![],
edited_name: None,
edited_symbol: None,
edited_url: None,
last_update: row.get::<_, Option<u64>>(5)?.map(crate::UnixEpochTs),
pending_removal: false,
playlist_id: Some(TableId(playlist_id)),
user_name: row.get(6)?,
user_symbol: row.get(7)?,
output: crate::OutputFolderKey(row.get(8)?),
status: crate::table::Status::Ready,
},
));
}
Ok(out)
}
fn load_table_urls_ids(conn: &rusqlite::Connection) -> Result<Vec<(TableId, String)>> {
let mut stmt = conn
.prepare("SELECT playlist_id, page_url FROM lr2oxytabler_playlist")
.context("failed to prepare SELECT in load_table_urls_ids")?;
let mut rows = stmt.query([])?;
let mut out: Vec<(TableId, String)> = vec![];
while let Some(row) = rows.next()? {
out.push((TableId(row.get(0)?), row.get(1)?));
}
Ok(out)
}
#[expect(clippy::too_many_lines)]
fn upsert_table(conn: &rusqlite::Connection, table: &crate::table::Table) -> Result<TableId> {
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 = match add_data.playlist_id {
Some(id) => {
let updated = conn
.execute(
"UPDATE
lr2oxytabler_playlist SET
name = ?1,
symbol = ?2,
folder_order = ?3,
page_url = ?4,
last_update = ?5,
user_name = ?6,
user_symbol = ?7,
output = ?8
WHERE playlist_id = ?9",
rusqlite::params![
&table.name,
&table.symbol,
&folder_order,
table.web_url.as_str(),
&add_data.last_update.map(|t| t.0),
&add_data.user_name,
&add_data.user_symbol,
&add_data.output.0,
&id.0,
],
)
.with_context(|| format!("failed to insert playlist into db; {:?}", &table))?;
anyhow::ensure!(
updated == 1,
"should've updated 1 playlists, but updated {updated}",
);
id.0
}
None => conn
.query_row(
"INSERT INTO
lr2oxytabler_playlist (
name,
symbol,
folder_order,
page_url,
last_update,
user_name,
user_symbol,
output
)
VALUES
(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
ON CONFLICT (page_url) DO UPDATE
SET
name = ?1,
symbol = ?2,
folder_order = ?3,
last_update = ?5,
user_name = ?6,
user_symbol = ?7,
output = ?8 RETURNING playlist_id",
rusqlite::params![
&table.name,
&table.symbol,
&folder_order,
table.web_url.as_str(),
&add_data.last_update.map(|t| t.0),
&add_data.user_name,
&add_data.user_symbol,
&add_data.output.0
],
|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"
);
for diff in &add_data.entry_diff_to_save_to_db {
let mut delete_entry = conn
.prepare(
"DELETE FROM lr2oxytabler_playlist_entry WHERE playlist_id = ? AND md5 = ? AND folder = ?",
)
.context("failed to prepare statement")?;
for (md5, level) in &diff.deleted {
delete_entry
.execute(rusqlite::params![id, &md5, &level])
.with_context(|| {
format!(
"failed to delete db playlist entry, on {} {}, changelog: {}",
md5, level, add_data.full_changelog
)
})?;
}
for (md5, from, to) in &diff.changed {
delete_entry
.execute(rusqlite::params![id, &md5, &from])
.with_context(|| {
format!(
"failed to delete db playlist entry, on {} {} -> {}, changelog: {}",
md5, from, to, add_data.full_changelog
)
})?;
}
let mut insert_entry = conn
.prepare(
"INSERT INTO lr2oxytabler_playlist_entry(playlist_id, md5, folder) VALUES (?, ?, ?)",
)
.context("failed to prepare statement")?;
for (md5, from, to) in &diff.changed {
insert_entry
.execute(rusqlite::params![id, &md5, &to])
.with_context(|| {
format!(
"failed to insert db playlist entry, on {} {} -> {}, changelog: {}",
md5, from, to, add_data.full_changelog
)
})?;
}
for (md5, level) in &diff.new {
insert_entry
.execute(rusqlite::params![id, &md5, &level])
.with_context(|| {
format!(
"failed to insert db playlist entry, on {} {}, changelog: {}",
md5, level, add_data.full_changelog
)
})?;
}
}
Ok(TableId(id))
}
fn maybe_apply_migrations(conn: &rusqlite::Connection) -> Result<()> {
migrations::maybe_apply_migration(
conn,
&migrations::Migration {
#[expect(clippy::unreadable_literal, reason = "date")]
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, reason = "date")]
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, reason = "date")]
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")?;
migrations::maybe_apply_migration(
conn,
&migrations::Migration {
#[allow(clippy::unreadable_literal, reason = "date")]
id: 20250922,
description: "table output",
sql: "ALTER TABLE lr2oxytabler_playlist ADD COLUMN output VARCHAR NOT NULL DEFAULT 'migrated';",
},
)
.context("db migration 'table output' failed")?;
migrations::maybe_apply_migration(
conn,
&migrations::Migration {
#[allow(clippy::unreadable_literal, reason = "date")]
id: 20250926,
description: "remove data and header URLs",
sql: "
ALTER TABLE lr2oxytabler_playlist DROP COLUMN data_url;
ALTER TABLE lr2oxytabler_playlist DROP COLUMN header_url;
",
},
)
.context("db migration 'remove data and header URLs' failed")?;
migrations::maybe_apply_migration(
conn,
&migrations::Migration {
#[allow(clippy::unreadable_literal, reason = "date")]
id: 20260327,
description: "table user-defined name",
// nullable
sql: "ALTER TABLE lr2oxytabler_playlist ADD COLUMN user_name VARCHAR;",
},
)
.context("db migration 'table user-defined name' failed")?;
Ok(())
}
#[cfg(test)]
pub(crate) mod tests {
use test_log::test;
/// Playlist IDs will be updated in-place if necessary.
fn save_db(
conn: &rusqlite::Connection,
tables: &mut [crate::table::Table],
) -> anyhow::Result<()> {
let table_ids = super::save_db_raw(conn, tables)?;
for (table, id) in tables.iter_mut().zip(table_ids) {
table.1.playlist_id = Some(id);
}
for table in tables.iter_mut() {
table.1.entry_diff_to_save_to_db.clear();
}
Ok(())
}
pub fn create_lr2_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
}
pub fn insert_lr2_song(conn: &rusqlite::Connection, md5: &str) {
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};
let db = rusqlite::Connection::open_in_memory().unwrap();
maybe_apply_migrations(&db).unwrap();
save_db(
&db,
&mut [crate::table::Table::empty()
.with_url("http://1")
.with_entry(0, "")],
)
.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;
validate_song_db(&create_lr2_song_db()).unwrap();
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_no_ids() {
use super::{load_tables, maybe_apply_migrations};
use crate::table::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.as_str() == "http://1")
.and_then(|t| t.1.playlist_id)
.unwrap();
let id2 = tables
.iter()
.find(|t| t.0.web_url.as_str() == "http://2")
.and_then(|t| t.1.playlist_id)
.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 saves_and_loads_tables() {
use super::{load_tables, maybe_apply_migrations};
use crate::table::Table;
let db = rusqlite::Connection::open_in_memory().unwrap();
maybe_apply_migrations(&db).unwrap();
let mut tables_saved = [Table::empty()
.with_entry(0, "")
.with_updated_changelog(&[])
.with_name("name")
.with_user_name("user-name")
.with_url("http://url")
.with_symbol("symbol")
.with_user_symbol("user-symbol")
.with_output("output")];
assert!(tables_saved.iter().all(|t| t.1.playlist_id.is_none()));
save_db(&db, &mut tables_saved).unwrap();
assert!(tables_saved.iter().all(|t| t.1.playlist_id.is_some()));
save_db(&db, &mut tables_saved).unwrap(); // and again with no problems
assert_eq!(load_tables(&db).unwrap(), tables_saved);
tables_saved = [Table::empty()
.with_updated_changelog(&tables_saved[0].0.entries)
.with_id(tables_saved[0].1.playlist_id.unwrap())
.with_name("new-name")
.with_user_symbol("new-user-name")
.with_url("http://new-url")
.with_symbol("new-symbol")
.with_user_symbol("new-user-symbol")
.with_output("new-output")];
save_db(&db, &mut tables_saved).unwrap();
assert_eq!(load_tables(&db).unwrap(), tables_saved);
}
#[test]
fn updates_tags() {
use super::update_tags_inplace;
use crate::table::Table;
let run = |table: Table| {
let db = create_lr2_song_db();
let md5_of_the_new_entry = "foodfoodfoodfoodfoodfoodfood0001";
save_db(&db, &mut [table]).unwrap();
insert_lr2_song(&db, md5_of_the_new_entry);
update_tags_inplace(&db).unwrap();
db.query_row(
"SELECT tag FROM song WHERE hash = ?",
rusqlite::params![md5_of_the_new_entry],
|row| Ok(row.get::<_, String>(0).unwrap()),
)
.unwrap()
};
assert_eq!(
run(Table::empty()
.with_entry(1, " DELAYMASTER")
.with_updated_changelog(&[])),
"DELAYMASTER"
);
assert_eq!(
run(Table::empty()
.with_entry(1, " DELAYMASTER")
.with_updated_changelog(&[])
.with_symbol("omg ")),
"omg DELAYMASTER"
);
assert_eq!(
run(Table::empty()
.with_entry(1, " DELAYMASTER")
.with_updated_changelog(&[])
.with_symbol("nope")
.with_user_symbol(" omg")),
"omg DELAYMASTER"
);
}
}