641 lines
53 KiB
Swift
641 lines
53 KiB
Swift
import Foundation
|
|
import SQLite3
|
|
|
|
struct Song: Identifiable, Hashable, Sendable {
|
|
var id: Int64
|
|
var title: String
|
|
var audio: String
|
|
var cdg: String?
|
|
var rating: Int
|
|
var plays: Int
|
|
var artist: String = ""
|
|
var album: String = ""
|
|
var label: String = ""
|
|
var duration: Double = 0
|
|
var fingerprint: String = ""
|
|
var trackNumber: String = ""
|
|
var rendition: String = ""
|
|
var originalName: String = ""
|
|
var originalPath: String = ""
|
|
var trackOrder: Int { Int(trackNumber) ?? 0 }
|
|
}
|
|
struct MediaLocation: Identifiable, Hashable, Sendable {
|
|
var id: Int64
|
|
var name: String
|
|
var path: String
|
|
var bookmark: Data?
|
|
var external: Bool
|
|
var lastScan: String
|
|
var lastError: String
|
|
var songCount: Int
|
|
var online: Bool { var directory: ObjCBool = false;return FileManager.default.fileExists(atPath:path,isDirectory:&directory) && directory.boolValue }
|
|
}
|
|
struct IndexedSourceFile {
|
|
var size: Int64
|
|
var modified: Double
|
|
var songs: [Int64]
|
|
}
|
|
struct MusicStore: Identifiable, Hashable {
|
|
var id: Int64
|
|
var name: String
|
|
var url: String
|
|
var searchTemplate: String
|
|
func destination(query: String = "") throws -> URL {
|
|
let query = query.trimmingCharacters(in:.whitespacesAndNewlines)
|
|
let encoded = query.addingPercentEncoding(withAllowedCharacters:CharacterSet(charactersIn:"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-._~")) ?? ""
|
|
let value = query.isEmpty || searchTemplate.isEmpty ? url : searchTemplate.replacingOccurrences(of:"{query}",with:encoded)
|
|
guard let result = URL(string:value), ["http","https"].contains(result.scheme?.lowercased() ?? ""), result.host?.isEmpty == false, result.user == nil, result.password == nil else { throw LibraryError.message("Use a valid http:// or https:// store URL without credentials") }
|
|
return result
|
|
}
|
|
}
|
|
struct PersonPlace: Identifiable, Hashable { var id: Int64; var name: String }
|
|
struct SongPreference: Equatable, Codable {
|
|
var pitch: Double = 0
|
|
var tempo: Double = 1
|
|
var rating: Int = 0
|
|
}
|
|
struct Performance: Identifiable {
|
|
var id: Int64
|
|
var song: String
|
|
var singer: String
|
|
var venue: String
|
|
var date: String
|
|
var singerID: Int64?
|
|
var songID: Int64?
|
|
}
|
|
struct QueueEntry: Identifiable {
|
|
var id: Int64
|
|
var songID: Int64
|
|
var singerID: Int64?
|
|
var title: String
|
|
var singer: String
|
|
}
|
|
enum LibraryError: LocalizedError {
|
|
case message(String)
|
|
var errorDescription: String? { if case .message(let s) = self { return s }; return nil }
|
|
}
|
|
final class Library {
|
|
private var db: OpaquePointer?
|
|
init(url: URL) throws {
|
|
guard sqlite3_open(url.path, &db) == SQLITE_OK else { throw LibraryError.message("Cannot open library") }
|
|
sqlite3_busy_timeout(db,5000)
|
|
guard sqlite3_exec(db,"PRAGMA journal_mode=WAL",nil,nil,nil) == SQLITE_OK else { throw error() }
|
|
try execute("PRAGMA foreign_keys=ON")
|
|
try execute("CREATE TABLE IF NOT EXISTS songs(id INTEGER PRIMARY KEY,title TEXT NOT NULL,audio TEXT UNIQUE NOT NULL,cdg TEXT,rating INTEGER NOT NULL DEFAULT 0 CHECK(rating BETWEEN 0 AND 5),plays INTEGER NOT NULL DEFAULT 0)")
|
|
try execute("CREATE TABLE IF NOT EXISTS singers(id INTEGER PRIMARY KEY,name TEXT UNIQUE NOT NULL)")
|
|
try execute("CREATE TABLE IF NOT EXISTS venues(id INTEGER PRIMARY KEY,name TEXT UNIQUE NOT NULL)")
|
|
try execute("CREATE TABLE IF NOT EXISTS history(id INTEGER PRIMARY KEY,song INTEGER REFERENCES songs(id),singer INTEGER REFERENCES singers(id),venue INTEGER REFERENCES venues(id),played TEXT DEFAULT CURRENT_TIMESTAMP)")
|
|
for column in ["track_number","rendition","original_name","original_path"] {
|
|
if try !columns("songs").contains(column) { try execute("ALTER TABLE songs ADD COLUMN \(column) TEXT NOT NULL DEFAULT ''") }
|
|
}
|
|
// Additive migration: preserve older libraries and infer existing memberships from history.
|
|
for column in ["artist", "album", "label"] {
|
|
if try !columns("songs").contains(column) { try execute("ALTER TABLE songs ADD COLUMN \(column) TEXT NOT NULL DEFAULT ''") }
|
|
}
|
|
try execute("CREATE TABLE IF NOT EXISTS venue_singers(venue INTEGER NOT NULL REFERENCES venues(id) ON DELETE CASCADE,singer INTEGER NOT NULL REFERENCES singers(id) ON DELETE CASCADE,PRIMARY KEY(venue,singer))")
|
|
if try schemaVersion() < 3 { try execute("INSERT OR IGNORE INTO venue_singers SELECT DISTINCT venue,singer FROM history WHERE venue IS NOT NULL AND singer IS NOT NULL") }
|
|
try execute("CREATE TABLE IF NOT EXISTS settings(key TEXT PRIMARY KEY,value TEXT)")
|
|
try execute("CREATE TABLE IF NOT EXISTS preferences(song INTEGER NOT NULL REFERENCES songs(id) ON DELETE CASCADE,singer INTEGER NOT NULL REFERENCES singers(id) ON DELETE CASCADE,pitch REAL NOT NULL DEFAULT 0 CHECK(pitch BETWEEN -12 AND 12),tempo REAL NOT NULL DEFAULT 1 CHECK(tempo BETWEEN 0.5 AND 1.5),rating INTEGER NOT NULL DEFAULT 0 CHECK(rating BETWEEN 0 AND 5),PRIMARY KEY(song,singer))")
|
|
try execute("CREATE TABLE IF NOT EXISTS queue(id INTEGER PRIMARY KEY,venue INTEGER REFERENCES venues(id) ON DELETE CASCADE,song INTEGER NOT NULL REFERENCES songs(id) ON DELETE CASCADE,singer INTEGER REFERENCES singers(id) ON DELETE CASCADE,position INTEGER NOT NULL)")
|
|
try execute("CREATE TABLE IF NOT EXISTS playlists(id INTEGER PRIMARY KEY,name TEXT UNIQUE NOT NULL)")
|
|
try execute("CREATE TABLE IF NOT EXISTS playlist_songs(playlist INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,song INTEGER NOT NULL REFERENCES songs(id) ON DELETE CASCADE,position INTEGER NOT NULL,PRIMARY KEY(playlist,song))")
|
|
try execute("CREATE TABLE IF NOT EXISTS singer_songs(singer INTEGER NOT NULL REFERENCES singers(id) ON DELETE CASCADE,song INTEGER NOT NULL REFERENCES songs(id) ON DELETE CASCADE,position INTEGER NOT NULL,PRIMARY KEY(singer,song))")
|
|
if try schemaVersion() < 4 {
|
|
try execute("INSERT OR IGNORE INTO singer_songs(singer,song,position) SELECT singer,song,MIN(id) FROM history WHERE singer IS NOT NULL AND song IS NOT NULL GROUP BY singer,song")
|
|
try execute("INSERT OR IGNORE INTO singer_songs(singer,song,position) SELECT singer,song,0 FROM preferences")
|
|
}
|
|
if try !columns("songs").contains("duration") { try execute("ALTER TABLE songs ADD COLUMN duration REAL NOT NULL DEFAULT 0") }
|
|
if try !columns("singer_songs").contains("favourite") { try execute("ALTER TABLE singer_songs ADD COLUMN favourite INTEGER NOT NULL DEFAULT 0 CHECK(favourite IN (0,1))") }
|
|
if try schemaVersion() < 5 { try execute("UPDATE singer_songs SET favourite=1 WHERE EXISTS(SELECT 1 FROM preferences WHERE preferences.singer=singer_songs.singer AND preferences.song=singer_songs.song AND preferences.rating>=4)") }
|
|
try execute("CREATE TABLE IF NOT EXISTS song_settings(song INTEGER PRIMARY KEY REFERENCES songs(id) ON DELETE CASCADE,pitch REAL NOT NULL DEFAULT 0 CHECK(pitch BETWEEN -12 AND 12),tempo REAL NOT NULL DEFAULT 1 CHECK(tempo BETWEEN 0.5 AND 1.5))")
|
|
try execute("CREATE TABLE IF NOT EXISTS music_stores(id INTEGER PRIMARY KEY,name TEXT UNIQUE NOT NULL,url TEXT NOT NULL,search_template TEXT NOT NULL DEFAULT '')")
|
|
if try !columns("songs").contains("metadata_edited") { try execute("ALTER TABLE songs ADD COLUMN metadata_edited INTEGER NOT NULL DEFAULT 0") }
|
|
try execute("CREATE TABLE IF NOT EXISTS singer_tonight(venue INTEGER NOT NULL,singer INTEGER NOT NULL,song INTEGER NOT NULL REFERENCES songs(id) ON DELETE CASCADE,position INTEGER NOT NULL,PRIMARY KEY(venue,singer,song),FOREIGN KEY(venue,singer) REFERENCES venue_singers(venue,singer) ON DELETE CASCADE)")
|
|
if try schemaVersion() < 7 {
|
|
try execute("INSERT OR IGNORE INTO singer_tonight(venue,singer,song,position) SELECT queue.venue,queue.singer,queue.song,MIN(queue.position) FROM queue JOIN venue_singers ON venue_singers.venue=queue.venue AND venue_singers.singer=queue.singer WHERE queue.singer IS NOT NULL GROUP BY queue.venue,queue.singer,queue.song")
|
|
}
|
|
if try !columns("songs").contains("fingerprint") { try execute("ALTER TABLE songs ADD COLUMN fingerprint TEXT NOT NULL DEFAULT ''") }
|
|
try execute("CREATE INDEX IF NOT EXISTS songs_fingerprint ON songs(fingerprint)")
|
|
try execute("CREATE TABLE IF NOT EXISTS media_sources(id INTEGER PRIMARY KEY,name TEXT NOT NULL,path TEXT NOT NULL UNIQUE,bookmark TEXT,external INTEGER NOT NULL DEFAULT 0,last_scan TEXT NOT NULL DEFAULT '',last_error TEXT NOT NULL DEFAULT '')")
|
|
try execute("CREATE TABLE IF NOT EXISTS source_files(source INTEGER NOT NULL REFERENCES media_sources(id) ON DELETE CASCADE,path TEXT NOT NULL,size INTEGER NOT NULL,modified REAL NOT NULL,PRIMARY KEY(source,path))")
|
|
try execute("CREATE TABLE IF NOT EXISTS source_songs(source INTEGER NOT NULL,path TEXT NOT NULL,song INTEGER NOT NULL REFERENCES songs(id) ON DELETE CASCADE,PRIMARY KEY(source,path,song),FOREIGN KEY(source,path) REFERENCES source_files(source,path) ON DELETE CASCADE)")
|
|
if try schemaVersion() < 8 {
|
|
for song in try songs() where song.fingerprint.isEmpty {
|
|
let url = URL(fileURLWithPath:song.audio),hash = URL(fileURLWithPath:song.audio).deletingPathExtension().lastPathComponent
|
|
if url.deletingLastPathComponent().lastPathComponent == "Media",hash.count == 64,hash.allSatisfy({ $0.isHexDigit }) { try execute("UPDATE songs SET fingerprint=? WHERE id=?",[hash,String(song.id)]) }
|
|
}
|
|
}
|
|
try execute("CREATE TABLE IF NOT EXISTS singer_rotation(venue INTEGER NOT NULL,singer INTEGER NOT NULL,position INTEGER NOT NULL,here INTEGER NOT NULL DEFAULT 1,PRIMARY KEY(venue,singer),FOREIGN KEY(venue,singer) REFERENCES venue_singers(venue,singer) ON DELETE CASCADE)")
|
|
try execute("CREATE TABLE IF NOT EXISTS show_screens(song INTEGER PRIMARY KEY REFERENCES songs(id) ON DELETE CASCADE,body TEXT NOT NULL,footer TEXT NOT NULL,tint TEXT NOT NULL)")
|
|
try execute("CREATE TABLE IF NOT EXISTS singer_access(venue INTEGER NOT NULL,singer INTEGER NOT NULL,token TEXT NOT NULL UNIQUE,PRIMARY KEY(venue,singer),FOREIGN KEY(venue,singer) REFERENCES venue_singers(venue,singer) ON DELETE CASCADE)")
|
|
try execute("CREATE TABLE IF NOT EXISTS import_history(path TEXT PRIMARY KEY,signature TEXT NOT NULL,songs TEXT NOT NULL,imported_at TEXT NOT NULL)")
|
|
try execute("CREATE TABLE IF NOT EXISTS reinterpret_undo(batch TEXT NOT NULL,song INTEGER NOT NULL REFERENCES songs(id) ON DELETE CASCADE,before_json TEXT NOT NULL,after_json TEXT NOT NULL,edited INTEGER NOT NULL,created TEXT NOT NULL,PRIMARY KEY(batch,song))")
|
|
try execute("PRAGMA user_version=11")
|
|
}
|
|
deinit { sqlite3_close(db) }
|
|
private func statement(_ sql: String, _ values: [String?]) throws -> OpaquePointer {
|
|
var s: OpaquePointer?
|
|
guard sqlite3_prepare_v2(db, sql, -1, &s, nil) == SQLITE_OK, let s else { throw error() }
|
|
for (i,v) in values.enumerated() {
|
|
let code = v.map { sqlite3_bind_text(s, Int32(i + 1), $0, -1, unsafeBitCast(-1, to: sqlite3_destructor_type.self)) } ?? sqlite3_bind_null(s, Int32(i + 1))
|
|
if code != SQLITE_OK { sqlite3_finalize(s); throw error() }
|
|
}
|
|
return s
|
|
}
|
|
private func error() -> LibraryError { .message(String(cString: sqlite3_errmsg(db))) }
|
|
func execute(_ sql: String, _ values: [String?] = []) throws {
|
|
let s = try statement(sql, values); defer { sqlite3_finalize(s) }
|
|
guard sqlite3_step(s) == SQLITE_DONE else { throw error() }
|
|
}
|
|
func songs() throws -> [Song] {
|
|
let s = try statement("SELECT id,title,audio,cdg,rating,plays,artist,album,label,duration,fingerprint,track_number,rendition,original_name,original_path FROM songs ORDER BY title COLLATE NOCASE", []); defer { sqlite3_finalize(s) }
|
|
var result = [Song]()
|
|
while true {
|
|
let code = sqlite3_step(s); if code == SQLITE_DONE { break }; guard code == SQLITE_ROW else { throw error() }
|
|
result.append(Song(id: sqlite3_column_int64(s,0), title: String(cString: sqlite3_column_text(s,1)), audio: String(cString: sqlite3_column_text(s,2)), cdg: sqlite3_column_text(s,3).map { String(cString:$0) }, rating: Int(sqlite3_column_int(s,4)), plays: Int(sqlite3_column_int(s,5)), artist: text(s,6), album: text(s,7), label: text(s,8),duration:sqlite3_column_double(s,9),fingerprint:text(s,10),trackNumber:text(s,11),rendition:text(s,12),originalName:text(s,13),originalPath:text(s,14)))
|
|
}
|
|
return result
|
|
}
|
|
@discardableResult func add(title: String,audio: String,cdg: String?,artist: String = "",album: String = "",label: String = "",duration: Double = 0,fingerprint: String = "",preferIncomingFile: Bool = false) throws -> Int64 {
|
|
var path = audio,graphics = cdg
|
|
if !fingerprint.isEmpty {
|
|
let statement = try self.statement("SELECT id,audio,cdg FROM songs WHERE audio=? OR fingerprint=? ORDER BY CASE WHEN audio=? THEN 0 ELSE 1 END,id LIMIT 1",[audio,fingerprint,audio])
|
|
if sqlite3_step(statement) == SQLITE_ROW {
|
|
let id = sqlite3_column_int64(statement,0),existing = text(statement,1)
|
|
let oldGraphics = sqlite3_column_type(statement,2) == SQLITE_NULL ? nil : text(statement,2)
|
|
if existing != audio,URL(fileURLWithPath:existing).pathExtension.lowercased() == URL(fileURLWithPath:audio).pathExtension.lowercased() {
|
|
if preferIncomingFile || !FileManager.default.fileExists(atPath:existing) {
|
|
sqlite3_finalize(statement)
|
|
try execute("UPDATE songs SET audio=?,cdg=? WHERE id=?",[audio,cdg,String(id)])
|
|
} else {
|
|
path = existing
|
|
if let oldGraphics,FileManager.default.fileExists(atPath:oldGraphics) { graphics = oldGraphics }
|
|
sqlite3_finalize(statement)
|
|
}
|
|
} else { sqlite3_finalize(statement) }
|
|
} else { sqlite3_finalize(statement) }
|
|
}
|
|
try execute("INSERT INTO songs(title,audio,cdg,artist,album,label,duration,fingerprint) VALUES(?,?,?,?,?,?,?,?) ON CONFLICT(audio) DO UPDATE SET cdg=COALESCE(excluded.cdg,songs.cdg),artist=CASE WHEN songs.metadata_edited=0 AND excluded.artist!='' THEN excluded.artist ELSE songs.artist END,album=CASE WHEN songs.metadata_edited=0 AND excluded.album!='' THEN excluded.album ELSE songs.album END,label=CASE WHEN songs.metadata_edited=0 AND excluded.label!='' THEN excluded.label ELSE songs.label END,duration=CASE WHEN excluded.duration>0 THEN excluded.duration ELSE songs.duration END,fingerprint=CASE WHEN excluded.fingerprint!='' THEN excluded.fingerprint ELSE songs.fingerprint END",[title,path,graphics,artist,album,label,String(duration),fingerprint])
|
|
let result = try statement("SELECT id FROM songs WHERE audio=?",[path]);defer { sqlite3_finalize(result) }
|
|
guard sqlite3_step(result) == SQLITE_ROW else { throw error() };return sqlite3_column_int64(result,0)
|
|
}
|
|
func rate(_ id: Int64, _ rating: Int) throws { try execute("UPDATE songs SET rating=? WHERE id=?", [String(rating),String(id)]) }
|
|
func entities(_ table: String) throws -> [PersonPlace] {
|
|
precondition(["singers","venues","playlists"].contains(table))
|
|
let s = try statement("SELECT id,name FROM \(table) ORDER BY name", []); defer { sqlite3_finalize(s) }
|
|
var rows = [PersonPlace]()
|
|
while sqlite3_step(s) == SQLITE_ROW { rows.append(.init(id: sqlite3_column_int64(s,0), name: String(cString: sqlite3_column_text(s,1)))) }
|
|
return rows
|
|
}
|
|
func addEntity(_ table: String, name: String) throws {
|
|
precondition(["singers","venues","playlists"].contains(table))
|
|
let name = name.trimmingCharacters(in: .whitespacesAndNewlines)
|
|
guard !name.isEmpty else { throw LibraryError.message("Enter a name") }
|
|
try execute("INSERT OR IGNORE INTO \(table)(name) VALUES(?)", [name])
|
|
}
|
|
func record(_ song: Int64, singer: Int64?, venue: Int64?) throws {
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
if let singer { try assignSong(song,singer:singer) }
|
|
try execute("INSERT INTO history(song,singer,venue) VALUES(?,?,?)", [String(song),singer.map(String.init),venue.map(String.init)])
|
|
try execute("UPDATE songs SET plays=plays+1 WHERE id=?", [String(song)])
|
|
try execute("COMMIT")
|
|
} catch { try? execute("ROLLBACK"); throw error }
|
|
}
|
|
|
|
private func text(_ s: OpaquePointer, _ index: Int32) -> String { sqlite3_column_text(s,index).map { String(cString:$0) } ?? "" }
|
|
private func schemaVersion() throws -> Int {
|
|
let s = try statement("PRAGMA user_version",[]); defer { sqlite3_finalize(s) }
|
|
return sqlite3_step(s) == SQLITE_ROW ? Int(sqlite3_column_int(s,0)) : 0
|
|
}
|
|
private func columns(_ table: String) throws -> Set<String> {
|
|
let s = try statement("PRAGMA table_info(\(table))", []); defer { sqlite3_finalize(s) }
|
|
var result = Set<String>(); while sqlite3_step(s) == SQLITE_ROW { result.insert(text(s,1)) }; return result
|
|
}
|
|
func members(of venue: Int64) throws -> [PersonPlace] {
|
|
let s = try statement("SELECT singers.id,singers.name FROM singers JOIN venue_singers ON singers.id=venue_singers.singer WHERE venue=? ORDER BY name", [String(venue)]); defer { sqlite3_finalize(s) }
|
|
var result = [PersonPlace](); while sqlite3_step(s) == SQLITE_ROW { result.append(.init(id:sqlite3_column_int64(s,0),name:text(s,1))) }; return result
|
|
}
|
|
func setMembership(singer: Int64, venue: Int64, included: Bool) throws {
|
|
try execute(included ? "INSERT OR IGNORE INTO venue_singers(venue,singer) VALUES(?,?)" : "DELETE FROM venue_singers WHERE venue=? AND singer=?", [String(venue),String(singer)])
|
|
if !included { try execute("DELETE FROM queue WHERE venue=? AND singer=?",[String(venue),String(singer)]) }
|
|
}
|
|
func currentVenue() throws -> Int64? {
|
|
let s = try statement("SELECT venues.id FROM venues JOIN settings ON CAST(settings.value AS INTEGER)=venues.id WHERE settings.key='currentVenue'", []); defer { sqlite3_finalize(s) }
|
|
return sqlite3_step(s) == SQLITE_ROW ? sqlite3_column_int64(s,0) : nil
|
|
}
|
|
func selectVenue(_ id: Int64?) throws {
|
|
if let id {
|
|
guard try entities("venues").contains(where: { $0.id == id }) else { throw LibraryError.message("Venue no longer exists") }
|
|
}
|
|
try execute("INSERT INTO settings(key,value) VALUES('currentVenue',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value",[id.map(String.init)])
|
|
}
|
|
func rename(_ table: String, id: Int64, name: String) throws {
|
|
precondition(["singers","venues","playlists"].contains(table))
|
|
let name = name.trimmingCharacters(in:.whitespacesAndNewlines)
|
|
guard !name.isEmpty else { throw LibraryError.message("Enter a name") }
|
|
try execute("UPDATE \(table) SET name=? WHERE id=?",[name,String(id)])
|
|
}
|
|
func deleteEntity(_ table: String, id: Int64) throws {
|
|
precondition(["singers","venues","playlists"].contains(table))
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
if table == "singers" { try execute("UPDATE history SET singer=NULL WHERE singer=?",[String(id)]) }
|
|
if table == "venues" {
|
|
try execute("UPDATE history SET venue=NULL WHERE venue=?",[String(id)])
|
|
try execute("DELETE FROM settings WHERE key='currentVenue' AND value=?",[String(id)])
|
|
}
|
|
try execute("DELETE FROM \(table) WHERE id=?",[String(id)])
|
|
try execute("COMMIT")
|
|
} catch { try? execute("ROLLBACK"); throw error }
|
|
}
|
|
func preference(song: Int64, singer: Int64) throws -> SongPreference {
|
|
let s = try statement("SELECT pitch,tempo,rating FROM preferences WHERE song=? AND singer=?",[String(song),String(singer)]); defer { sqlite3_finalize(s) }
|
|
guard sqlite3_step(s) == SQLITE_ROW else { return try defaultPreference(song:song) }
|
|
return .init(pitch:sqlite3_column_double(s,0),tempo:sqlite3_column_double(s,1),rating:Int(sqlite3_column_int(s,2)))
|
|
}
|
|
func savePreference(song: Int64, singer: Int64, value: SongPreference) throws {
|
|
guard value.pitch.isFinite,value.tempo.isFinite,(-12...12).contains(value.pitch),(0.5...1.5).contains(value.tempo),(0...5).contains(value.rating) else { throw LibraryError.message("Invalid singer settings") }
|
|
try execute("INSERT INTO preferences(song,singer,pitch,tempo,rating) VALUES(?,?,?,?,?) ON CONFLICT(song,singer) DO UPDATE SET pitch=excluded.pitch,tempo=excluded.tempo,rating=excluded.rating",[String(song),String(singer),String(value.pitch),String(value.tempo),String(value.rating)])
|
|
}
|
|
func history(venue: Int64?, singer: Int64? = nil) throws -> [Performance] {
|
|
let s = try statement("SELECT history.id,COALESCE(songs.title,'Deleted song'),COALESCE(singers.name,'—'),COALESCE(venues.name,'—'),played,history.singer,history.song FROM history LEFT JOIN songs ON songs.id=history.song LEFT JOIN singers ON singers.id=history.singer LEFT JOIN venues ON venues.id=history.venue WHERE (? IS NULL OR history.venue=?) AND (? IS NULL OR history.singer=?) ORDER BY history.id DESC",[venue.map(String.init),venue.map(String.init),singer.map(String.init),singer.map(String.init)]); defer { sqlite3_finalize(s) }
|
|
var result = [Performance](); while sqlite3_step(s) == SQLITE_ROW { result.append(.init(id:sqlite3_column_int64(s,0),song:text(s,1),singer:text(s,2),venue:text(s,3),date:text(s,4),singerID:sqlite3_column_type(s,5) == SQLITE_NULL ? nil : sqlite3_column_int64(s,5),songID:sqlite3_column_type(s,6) == SQLITE_NULL ? nil : sqlite3_column_int64(s,6))) }; return result
|
|
}
|
|
func enqueue(song: Int64, singer: Int64?, venue: Int64?) throws {
|
|
guard let venue else { throw LibraryError.message("Select a current venue before adding to its queue") }
|
|
if let singer, try !members(of:venue).contains(where: { $0.id == singer }) { throw LibraryError.message("Add this singer to the current venue first") }
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
if let singer {
|
|
try assignSong(song,singer:singer)
|
|
try execute("INSERT OR IGNORE INTO singer_tonight(venue,singer,song,position) VALUES(?,?,?,(SELECT COALESCE(MAX(position),0)+1 FROM singer_tonight WHERE venue=? AND singer=?))",[String(venue),String(singer),String(song),String(venue),String(singer)])
|
|
}
|
|
try execute("INSERT INTO queue(venue,song,singer,position) VALUES(?,?,?,(SELECT COALESCE(MAX(position),0)+1 FROM queue WHERE venue=?))",[String(venue),String(song),singer.map(String.init),String(venue)])
|
|
try execute("COMMIT")
|
|
} catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func queue(venue: Int64?) throws -> [QueueEntry] {
|
|
guard let venue else { return [] }
|
|
let s = try statement("SELECT queue.id,queue.song,queue.singer,songs.title,COALESCE(singers.name,'No singer') FROM queue JOIN songs ON songs.id=queue.song LEFT JOIN singers ON singers.id=queue.singer WHERE queue.venue=? ORDER BY queue.position,queue.id",[String(venue)]); defer { sqlite3_finalize(s) }
|
|
var result = [QueueEntry](); while sqlite3_step(s) == SQLITE_ROW { result.append(.init(id:sqlite3_column_int64(s,0),songID:sqlite3_column_int64(s,1),singerID:sqlite3_column_type(s,2) == SQLITE_NULL ? nil : sqlite3_column_int64(s,2),title:text(s,3),singer:text(s,4))) }; return result
|
|
}
|
|
func removeQueue(_ id: Int64) throws { try execute("DELETE FROM queue WHERE id=?",[String(id)]) }
|
|
func moveQueue(_ id: Int64, venue: Int64, by offset: Int) throws {
|
|
var rows = try queue(venue:venue)
|
|
guard let index = rows.firstIndex(where: { $0.id == id }), rows.indices.contains(index+offset) else { return }
|
|
rows.swapAt(index,index+offset)
|
|
try execute("BEGIN IMMEDIATE")
|
|
do { for (i,row) in rows.enumerated() { try execute("UPDATE queue SET position=? WHERE id=?",[String(i),String(row.id)]) }; try execute("COMMIT") }
|
|
catch { try? execute("ROLLBACK"); throw error }
|
|
}
|
|
func addToPlaylist(_ playlist: Int64, song: Int64) throws {
|
|
try execute("INSERT OR IGNORE INTO playlist_songs(playlist,song,position) VALUES(?,?,(SELECT COALESCE(MAX(position),0)+1 FROM playlist_songs WHERE playlist=?))",[String(playlist),String(song),String(playlist)])
|
|
}
|
|
func playlistSongIDs(_ playlist: Int64) throws -> [Int64] {
|
|
let s = try statement("SELECT song FROM playlist_songs WHERE playlist=? ORDER BY position",[String(playlist)]); defer { sqlite3_finalize(s) }
|
|
var ids = [Int64](); while sqlite3_step(s) == SQLITE_ROW { ids.append(sqlite3_column_int64(s,0)) }; return ids
|
|
}
|
|
func removeFromPlaylist(_ playlist: Int64, song: Int64) throws { try execute("DELETE FROM playlist_songs WHERE playlist=? AND song=?",[String(playlist),String(song)]) }
|
|
func assignSong(_ song: Int64, singer: Int64) throws {
|
|
try execute("INSERT OR IGNORE INTO singer_songs(singer,song,position) VALUES(?,?,(SELECT COALESCE(MAX(position),0)+1 FROM singer_songs WHERE singer=?))",[String(singer),String(song),String(singer)])
|
|
}
|
|
func removeSingerSong(_ song: Int64, singer: Int64) throws {
|
|
try execute("DELETE FROM singer_songs WHERE singer=? AND song=?",[String(singer),String(song)])
|
|
}
|
|
func singerSongIDs() throws -> [Int64:[Int64]] {
|
|
let s = try statement("SELECT singer,song FROM singer_songs ORDER BY singer,position,song",[]); defer { sqlite3_finalize(s) }
|
|
var result = [Int64:[Int64]]()
|
|
while sqlite3_step(s) == SQLITE_ROW { result[sqlite3_column_int64(s,0),default:[]].append(sqlite3_column_int64(s,1)) }
|
|
return result
|
|
}
|
|
func defaultPreference(song: Int64) throws -> SongPreference {
|
|
let s = try statement("SELECT pitch,tempo FROM song_settings WHERE song=?",[String(song)]);defer { sqlite3_finalize(s) }
|
|
guard sqlite3_step(s) == SQLITE_ROW else { return .init() }
|
|
return .init(pitch:sqlite3_column_double(s,0),tempo:sqlite3_column_double(s,1),rating:0)
|
|
}
|
|
func saveDefaultPreference(song: Int64, value: SongPreference) throws {
|
|
try execute("INSERT INTO song_settings(song,pitch,tempo) VALUES(?,?,?) ON CONFLICT(song) DO UPDATE SET pitch=excluded.pitch,tempo=excluded.tempo",[String(song),String(value.pitch),String(value.tempo)])
|
|
}
|
|
func updateDuration(_ song: Int64, seconds: Double) throws {
|
|
guard seconds.isFinite && seconds >= 0 else { return }
|
|
try execute("UPDATE songs SET duration=? WHERE id=?",[String(seconds),String(song)])
|
|
}
|
|
func setFavourite(song: Int64, singer: Int64, included: Bool) throws {
|
|
if included { try assignSong(song,singer:singer) }
|
|
try execute("UPDATE singer_songs SET favourite=? WHERE singer=? AND song=?",[included ? "1" : "0",String(singer),String(song)])
|
|
}
|
|
func favouriteSongIDs() throws -> [Int64:Set<Int64>] {
|
|
let s = try statement("SELECT singer,song FROM singer_songs WHERE favourite=1",[]);defer { sqlite3_finalize(s) }
|
|
var result = [Int64:Set<Int64>]()
|
|
while sqlite3_step(s) == SQLITE_ROW { result[sqlite3_column_int64(s,0),default:[]].insert(sqlite3_column_int64(s,1)) };return result
|
|
}
|
|
func stores() throws -> [MusicStore] {
|
|
let s = try statement("SELECT id,name,url,search_template FROM music_stores ORDER BY name",[]);defer { sqlite3_finalize(s) }
|
|
var result = [MusicStore]()
|
|
while sqlite3_step(s) == SQLITE_ROW { result.append(.init(id:sqlite3_column_int64(s,0),name:text(s,1),url:text(s,2),searchTemplate:text(s,3))) };return result
|
|
}
|
|
func saveStore(_ store: MusicStore) throws {
|
|
let name = store.name.trimmingCharacters(in:.whitespacesAndNewlines),url = store.url.trimmingCharacters(in:.whitespacesAndNewlines),search = store.searchTemplate.trimmingCharacters(in:.whitespacesAndNewlines)
|
|
guard !name.isEmpty else { throw LibraryError.message("Enter a store name") }
|
|
let cleaned = MusicStore(id:store.id,name:name,url:url,searchTemplate:search)
|
|
_ = try cleaned.destination()
|
|
if !search.isEmpty {
|
|
guard search.contains("{query}") else { throw LibraryError.message("Search URL must contain {query}") }
|
|
_ = try cleaned.destination(query:"test")
|
|
}
|
|
if store.id == 0 { try execute("INSERT INTO music_stores(name,url,search_template) VALUES(?,?,?)",[name,url,search]) }
|
|
else { try execute("UPDATE music_stores SET name=?,url=?,search_template=? WHERE id=?",[name,url,search,String(store.id)]) }
|
|
}
|
|
func deleteStore(_ id: Int64) throws { try execute("DELETE FROM music_stores WHERE id=?",[String(id)]) }
|
|
func updateMetadata(song: Int64,title: String,artist: String,album: String,label: String,rating: Int,trackNumber: String? = nil,rendition: String? = nil) throws {
|
|
let title = title.trimmingCharacters(in:.whitespacesAndNewlines)
|
|
guard !title.isEmpty else { throw LibraryError.message("Song title cannot be empty") }
|
|
if let trackNumber,!trackNumber.isEmpty,trackNumber.count > 3 || !trackNumber.allSatisfy({$0.isASCII && $0.isNumber}) { throw LibraryError.message("Use a track number from 0 to 999, or leave it empty") }
|
|
try execute("UPDATE songs SET title=?,artist=?,album=?,label=?,rating=?,metadata_edited=1 WHERE id=?",[title,artist.trimmingCharacters(in:.whitespacesAndNewlines),album.trimmingCharacters(in:.whitespacesAndNewlines),label.trimmingCharacters(in:.whitespacesAndNewlines),String(rating),String(song)])
|
|
guard sqlite3_changes(db) == 1 else { throw LibraryError.message("Song no longer exists") }
|
|
if let trackNumber { try execute("UPDATE songs SET track_number=? WHERE id=?",[trackNumber,String(song)]) }
|
|
if let rendition { try execute("UPDATE songs SET rendition=? WHERE id=?",[rendition,String(song)]) }
|
|
}
|
|
func tonightSongIDs(venue: Int64?) throws -> [Int64:[Int64]] {
|
|
guard let venue else { return [:] }
|
|
let s = try statement("SELECT singer,song FROM singer_tonight WHERE venue=? ORDER BY position,song",[String(venue)]);defer { sqlite3_finalize(s) }
|
|
var result = [Int64:[Int64]]()
|
|
while sqlite3_step(s) == SQLITE_ROW { result[sqlite3_column_int64(s,0),default:[]].append(sqlite3_column_int64(s,1)) };return result
|
|
}
|
|
func addTonight(song: Int64,singer: Int64,venue: Int64,transaction: Bool = true) throws {
|
|
guard try members(of:venue).contains(where:{ $0.id == singer }) else { throw LibraryError.message("This singer is not a member of the current venue") }
|
|
if transaction { try execute("BEGIN IMMEDIATE") }
|
|
do {
|
|
try assignSong(song,singer:singer)
|
|
try execute("INSERT OR IGNORE INTO singer_tonight(venue,singer,song,position) VALUES(?,?,?,(SELECT COALESCE(MAX(position),0)+1 FROM singer_tonight WHERE venue=? AND singer=?))",[String(venue),String(singer),String(song),String(venue),String(singer)])
|
|
try execute("INSERT INTO queue(venue,song,singer,position) SELECT ?,?,?,(SELECT COALESCE(MAX(position),0)+1 FROM queue WHERE venue=?) WHERE NOT EXISTS(SELECT 1 FROM queue WHERE venue=? AND singer=? AND song=?)",[String(venue),String(song),String(singer),String(venue),String(venue),String(singer),String(song)])
|
|
if transaction { try execute("COMMIT") }
|
|
} catch { if transaction { try? execute("ROLLBACK") };throw error }
|
|
}
|
|
func removeTonight(song: Int64,singer: Int64,venue: Int64) throws {
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
try execute("DELETE FROM singer_tonight WHERE venue=? AND singer=? AND song=?",[String(venue),String(singer),String(song)])
|
|
try execute("DELETE FROM queue WHERE venue=? AND singer=? AND song=?",[String(venue),String(singer),String(song)])
|
|
try execute("COMMIT")
|
|
} catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
/// Atomically adds/copies or reorders a request, retaining other singers' queue slots.
|
|
func placeTonight(song: Int64,singer: Int64,venue: Int64,before: Int64?,addIfNeeded: Bool) throws {
|
|
guard try members(of:venue).contains(where:{ $0.id == singer }) else { throw LibraryError.message("This singer is not in the target venue") }
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
var ids = try tonightSongIDs(venue:venue)[singer] ?? []
|
|
if let before, !ids.contains(before) { throw LibraryError.message("The drop target changed. Please drop again.") }
|
|
if !ids.contains(song) && !addIfNeeded { throw LibraryError.message("This Tonight request no longer exists") }
|
|
if addIfNeeded {
|
|
try assignSong(song,singer:singer)
|
|
try execute("INSERT OR IGNORE INTO singer_tonight(venue,singer,song,position) VALUES(?,?,?,0)",[String(venue),String(singer),String(song)])
|
|
try execute("INSERT INTO queue(venue,song,singer,position) SELECT ?,?,?,(SELECT COALESCE(MAX(position),0)+1 FROM queue WHERE venue=?) WHERE NOT EXISTS(SELECT 1 FROM queue WHERE venue=? AND singer=? AND song=?)",[String(venue),String(song),String(singer),String(venue),String(venue),String(singer),String(song)])
|
|
}
|
|
if before != song {
|
|
ids.removeAll { $0 == song }
|
|
if let before,let index = ids.firstIndex(of:before) { ids.insert(song,at:index) } else { ids.append(song) }
|
|
}
|
|
for (index,id) in ids.enumerated() { try execute("UPDATE singer_tonight SET position=? WHERE venue=? AND singer=? AND song=?",[String(index),String(venue),String(singer),String(id)]) }
|
|
let all = try queue(venue:venue)
|
|
let slots = all.indices.filter { all[$0].singerID == singer }
|
|
let rank = Dictionary(uniqueKeysWithValues:ids.enumerated().map { ($0.element,$0.offset) })
|
|
let requests = slots.enumerated().map { (original:$0.offset,row:all[$0.element]) }.sorted {
|
|
let a = rank[$0.row.songID] ?? Int.max,b = rank[$1.row.songID] ?? Int.max
|
|
return a == b ? $0.original < $1.original : a < b
|
|
}
|
|
var ordered = all
|
|
for (slot,request) in zip(slots,requests) { ordered[slot] = request.row }
|
|
for (index,row) in ordered.enumerated() { try execute("UPDATE queue SET position=? WHERE id=?",[String(index),String(row.id)]) }
|
|
try execute("COMMIT")
|
|
} catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func mediaLocations() throws -> [MediaLocation] {
|
|
let s = try statement("SELECT media_sources.id,name,media_sources.path,bookmark,external,last_scan,last_error,(SELECT COUNT(DISTINCT song) FROM source_songs WHERE source=media_sources.id) FROM media_sources ORDER BY name",[]);defer { sqlite3_finalize(s) }
|
|
var result = [MediaLocation]()
|
|
while sqlite3_step(s) == SQLITE_ROW { result.append(.init(id:sqlite3_column_int64(s,0),name:text(s,1),path:text(s,2),bookmark:Data(base64Encoded:text(s,3)),external:sqlite3_column_int(s,4) != 0,lastScan:text(s,5),lastError:text(s,6),songCount:Int(sqlite3_column_int(s,7)))) };return result
|
|
}
|
|
func saveMediaLocation(name: String,url: URL,bookmark: Data?,external: Bool) throws -> Int64 {
|
|
try execute("INSERT INTO media_sources(name,path,bookmark,external) VALUES(?,?,?,?) ON CONFLICT(path) DO UPDATE SET name=excluded.name,bookmark=COALESCE(excluded.bookmark,media_sources.bookmark),external=excluded.external",[name,url.standardizedFileURL.path,bookmark?.base64EncodedString(),external ? "1" : "0"])
|
|
let s = try statement("SELECT id FROM media_sources WHERE path=?",[url.standardizedFileURL.path]);defer { sqlite3_finalize(s) };guard sqlite3_step(s) == SQLITE_ROW else { throw error() };return sqlite3_column_int64(s,0)
|
|
}
|
|
func removeMediaLocation(_ id: Int64) throws { try execute("DELETE FROM media_sources WHERE id=?",[String(id)]) }
|
|
func sourceSnapshot(_ source: Int64) throws -> [String:IndexedSourceFile] {
|
|
let s = try statement("SELECT source_files.path,size,modified,song FROM source_files LEFT JOIN source_songs ON source_songs.source=source_files.source AND source_songs.path=source_files.path WHERE source_files.source=?",[String(source)]);defer { sqlite3_finalize(s) }
|
|
var result = [String:IndexedSourceFile]()
|
|
while sqlite3_step(s) == SQLITE_ROW {
|
|
let path = text(s,0)
|
|
if result[path] == nil { result[path] = .init(size:sqlite3_column_int64(s,1),modified:sqlite3_column_double(s,2),songs:[]) }
|
|
if sqlite3_column_type(s,3) != SQLITE_NULL { result[path]?.songs.append(sqlite3_column_int64(s,3)) }
|
|
};return result
|
|
}
|
|
func recordSourceFile(source: Int64,path: String,size: Int64,modified: Double,songs: [Int64]) throws {
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
try execute("INSERT INTO source_files(source,path,size,modified) VALUES(?,?,?,?) ON CONFLICT(source,path) DO UPDATE SET size=excluded.size,modified=excluded.modified",[String(source),path,String(size),String(modified)])
|
|
try execute("DELETE FROM source_songs WHERE source=? AND path=?",[String(source),path])
|
|
for song in Set(songs) { try execute("INSERT INTO source_songs(source,path,song) VALUES(?,?,?)",[String(source),path,String(song)]) }
|
|
try execute("COMMIT")
|
|
} catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func finishSourceScan(_ source: Int64,path: String,message: String) throws {
|
|
try execute("UPDATE media_sources SET path=?,last_scan=?,last_error=? WHERE id=?",[path,ISO8601DateFormatter().string(from:Date()),message,String(source)])
|
|
}
|
|
func song(_ id: Int64) throws -> Song? {
|
|
let s = try statement("SELECT id,title,audio,cdg,rating,plays,artist,album,label,duration,fingerprint,track_number,rendition,original_name,original_path FROM songs WHERE id=?",[String(id)]);defer { sqlite3_finalize(s) }
|
|
guard sqlite3_step(s) == SQLITE_ROW else { return nil }
|
|
return .init(id:sqlite3_column_int64(s,0),title:text(s,1),audio:text(s,2),cdg:sqlite3_column_type(s,3) == SQLITE_NULL ? nil : text(s,3),rating:Int(sqlite3_column_int(s,4)),plays:Int(sqlite3_column_int(s,5)),artist:text(s,6),album:text(s,7),label:text(s,8),duration:sqlite3_column_double(s,9),fingerprint:text(s,10),trackNumber:text(s,11),rendition:text(s,12),originalName:text(s,13),originalPath:text(s,14))
|
|
}
|
|
}
|
|
|
|
extension Library {
|
|
func setting(_ key: String) throws -> String? {
|
|
let s = try statement("SELECT value FROM settings WHERE key=?",[key]);defer { sqlite3_finalize(s) }
|
|
return sqlite3_step(s) == SQLITE_ROW ? text(s,0) : nil
|
|
}
|
|
func setSetting(_ key: String,_ value: String?) throws {
|
|
try execute("INSERT INTO settings(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value",[key,value])
|
|
}
|
|
func rotation(_ venue: Int64) throws -> [RotationSinger] {
|
|
let s = try statement("SELECT singer_rotation.singer,singers.name,singer_rotation.here FROM singer_rotation JOIN singers ON singers.id=singer_rotation.singer WHERE venue=? ORDER BY position,singer_rotation.singer",[String(venue)]);defer { sqlite3_finalize(s) }
|
|
var rows = [RotationSinger]()
|
|
while sqlite3_step(s) == SQLITE_ROW { rows.append(.init(id:sqlite3_column_int64(s,0),name:text(s,1),here:sqlite3_column_int(s,2) != 0)) };return rows
|
|
}
|
|
func markHere(singer: Int64,venue: Int64,here: Bool) throws {
|
|
guard try members(of:venue).contains(where:{$0.id == singer}) else { throw LibraryError.message("Singer is not at this venue") }
|
|
try execute("INSERT INTO singer_rotation(venue,singer,position,here) VALUES(?,?,(SELECT COALESCE(MAX(position),0)+1 FROM singer_rotation WHERE venue=?),?) ON CONFLICT(venue,singer) DO UPDATE SET here=excluded.here,position=CASE WHEN singer_rotation.here=0 AND excluded.here=1 THEN excluded.position ELSE singer_rotation.position END",[String(venue),String(singer),String(venue),here ? "1" : "0"])
|
|
}
|
|
func moveRotation(singer: Int64,venue: Int64,by offset: Int) throws {
|
|
var rows = try rotation(venue);guard let i = rows.firstIndex(where:{$0.id == singer}),rows.indices.contains(i+offset) else { return };rows.swapAt(i,i+offset)
|
|
try execute("BEGIN IMMEDIATE")
|
|
do { for (i,row) in rows.enumerated() { try execute("UPDATE singer_rotation SET position=? WHERE venue=? AND singer=?",[String(i),String(venue),String(row.id)]) };try execute("COMMIT") } catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func assignQueue(_ entry: Int64,singer: Int64?,venue: Int64) throws {
|
|
guard let row = try queue(venue:venue).first(where:{$0.id == entry}) else { throw LibraryError.message("Queue entry changed") }
|
|
if let singer { guard try members(of:venue).contains(where:{$0.id == singer}) else { throw LibraryError.message("Singer is not at this venue") };try assignSong(row.songID,singer:singer) }
|
|
try execute("UPDATE queue SET singer=? WHERE id=? AND venue=?",[singer.map(String.init),String(entry),String(venue)])
|
|
}
|
|
func reorderPlaylist(_ playlist: Int64,song: Int64,by offset: Int) throws {
|
|
var ids = try playlistSongIDs(playlist);guard let i = ids.firstIndex(of:song),ids.indices.contains(i+offset) else { return };ids.swapAt(i,i+offset)
|
|
try execute("BEGIN IMMEDIATE")
|
|
do { for (i,id) in ids.enumerated() { try execute("UPDATE playlist_songs SET position=? WHERE playlist=? AND song=?",[String(i),String(playlist),String(id)]) };try execute("COMMIT") } catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func batchMetadata(_ ids: Set<Int64>,patch: MetadataPatch) throws {
|
|
let updates = try ids.map { id -> Song in guard let song = try song(id) else { throw LibraryError.message("A selected song no longer exists") };return try patch.applying(to:song) }
|
|
try execute("BEGIN IMMEDIATE")
|
|
do { for song in updates { try updateMetadata(song:song.id,title:song.title,artist:song.artist,album:song.album,label:song.label,rating:song.rating) };try execute("COMMIT") } catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func screens() throws -> [ShowScreen] {
|
|
let s = try statement("SELECT songs.id,songs.title,show_screens.body,show_screens.footer,show_screens.tint,songs.duration FROM show_screens JOIN songs ON songs.id=show_screens.song ORDER BY songs.title COLLATE NOCASE",[]);defer { sqlite3_finalize(s) }
|
|
var rows = [ShowScreen]();while sqlite3_step(s) == SQLITE_ROW { rows.append(.init(id:sqlite3_column_int64(s,0),title:text(s,1),body:text(s,2),footer:text(s,3),tint:text(s,4),seconds:sqlite3_column_double(s,5))) };return rows
|
|
}
|
|
@discardableResult func saveScreen(_ screen: ShowScreen) throws -> Int64 {
|
|
guard !screen.title.trimmingCharacters(in:.whitespacesAndNewlines).isEmpty,(3...300).contains(screen.seconds),screen.body.count <= 4000,screen.footer.count <= 500 else { throw LibraryError.message("Give the slide a title and a duration from 3 to 300 seconds") }
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
let id: Int64
|
|
if screen.id == 0 { id = try add(title:screen.title,audio:"showscreen://"+UUID().uuidString,cdg:nil,duration:screen.seconds) }
|
|
else { guard try screens().contains(where:{$0.id == screen.id}) else { throw LibraryError.message("Slide no longer exists") };id = screen.id;try execute("UPDATE songs SET title=?,duration=? WHERE id=?",[screen.title,String(screen.seconds),String(id)]) }
|
|
try execute("INSERT INTO show_screens(song,body,footer,tint) VALUES(?,?,?,?) ON CONFLICT(song) DO UPDATE SET body=excluded.body,footer=excluded.footer,tint=excluded.tint",[String(id),screen.body,screen.footer,screen.tint]);try execute("COMMIT");return id
|
|
} catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func singerToken(singer: Int64,venue: Int64,renew: Bool = false) throws -> String {
|
|
guard try members(of:venue).contains(where:{$0.id == singer}) else { throw LibraryError.message("Singer is not at this venue") }
|
|
if !renew {
|
|
let s = try statement("SELECT token FROM singer_access WHERE venue=? AND singer=?",[String(venue),String(singer)]);defer { sqlite3_finalize(s) }
|
|
if sqlite3_step(s) == SQLITE_ROW { return text(s,0) }
|
|
}
|
|
let token = UUID().uuidString.replacingOccurrences(of:"-",with:"")+UUID().uuidString.replacingOccurrences(of:"-",with:"")
|
|
try execute("INSERT INTO singer_access(venue,singer,token) VALUES(?,?,?) ON CONFLICT(venue,singer) DO UPDATE SET token=excluded.token",[String(venue),String(singer),token]);return token
|
|
}
|
|
func singerForToken(_ token: String,venue: Int64) throws -> Int64? {
|
|
guard token.count == 64 else { return nil }
|
|
let s = try statement("SELECT singer FROM singer_access WHERE venue=? AND token=?",[String(venue),token]);defer { sqlite3_finalize(s) }
|
|
return sqlite3_step(s) == SQLITE_ROW ? sqlite3_column_int64(s,0) : nil
|
|
}
|
|
func revokeSinger(singer: Int64,venue: Int64) throws { try execute("DELETE FROM singer_access WHERE singer=? AND venue=?",[String(singer),String(venue)]) }
|
|
}
|
|
|
|
extension Library {
|
|
/// One bulk read replaces per-row database queries during navigation and drawing.
|
|
func tuningSnapshot(singer: Int64?) throws -> [Int64:SongPreference] {
|
|
let defaults = try statement("SELECT song,pitch,tempo FROM song_settings",[]);defer { sqlite3_finalize(defaults) }
|
|
var result = [Int64:SongPreference]()
|
|
while sqlite3_step(defaults) == SQLITE_ROW { result[sqlite3_column_int64(defaults,0)] = .init(pitch:sqlite3_column_double(defaults,1),tempo:sqlite3_column_double(defaults,2)) }
|
|
if let singer {
|
|
let values = try statement("SELECT song,pitch,tempo,rating FROM preferences WHERE singer=?",[String(singer)]);defer { sqlite3_finalize(values) }
|
|
while sqlite3_step(values) == SQLITE_ROW { result[sqlite3_column_int64(values,0)] = .init(pitch:sqlite3_column_double(values,1),tempo:sqlite3_column_double(values,2),rating:Int(sqlite3_column_int(values,3))) }
|
|
};return result
|
|
}
|
|
func orderQueueByRoster(venue: Int64) throws {
|
|
let roster = try rotation(venue).map(\.id),rows = try queue(venue:venue)
|
|
let rank = Dictionary(uniqueKeysWithValues:roster.enumerated().map{($0.element,$0.offset)})
|
|
let ordered = rows.enumerated().sorted {
|
|
let a = $0.element.singerID.flatMap{rank[$0]} ?? Int.max
|
|
let b = $1.element.singerID.flatMap{rank[$0]} ?? Int.max
|
|
return a == b ? $0.offset < $1.offset : a < b
|
|
}.map(\.element)
|
|
try execute("BEGIN IMMEDIATE")
|
|
do { for (index,row) in ordered.enumerated() { try execute("UPDATE queue SET position=? WHERE id=? AND venue=?",[String(index),String(row.id),String(venue)]) };try execute("COMMIT") }
|
|
catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
func placeRotation(singer: Int64,venue: Int64,before: Int64?) throws {
|
|
var ids = try rotation(venue).map(\.id)
|
|
guard ids.contains(singer),before == nil || ids.contains(before!) else { throw LibraryError.message("Rotation changed; choose the singer again") }
|
|
if before == singer { return };ids.removeAll{$0 == singer};if let before,let i = ids.firstIndex(of:before) { ids.insert(singer,at:i) } else { ids.append(singer) }
|
|
try execute("BEGIN IMMEDIATE")
|
|
do { for (i,id) in ids.enumerated() { try execute("UPDATE singer_rotation SET position=? WHERE venue=? AND singer=?",[String(i),String(venue),String(id)]) };try execute("COMMIT") } catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
}
|
|
|
|
extension Library {
|
|
func importedSongs(path: String,signature: String) throws -> [Int64]? {
|
|
let query = try statement("SELECT songs FROM import_history WHERE path=? AND signature=?",[path,signature]);defer { sqlite3_finalize(query) }
|
|
guard sqlite3_step(query) == SQLITE_ROW,let data = text(query,0).data(using:.utf8),let ids = try? JSONDecoder().decode([Int64].self,from:data),!ids.isEmpty else { return nil }
|
|
for id in ids {
|
|
guard let track = try song(id),MediaByteCopy.nonempty(URL(fileURLWithPath:track.audio)) else { return nil }
|
|
if let cdg = track.cdg,!MediaByteCopy.nonempty(URL(fileURLWithPath:cdg)) { return nil }
|
|
}
|
|
return ids
|
|
}
|
|
func rememberImport(path: String,signature: String,songs: [Int64]) throws {
|
|
guard !songs.isEmpty else { return }
|
|
let json = String(decoding:try JSONEncoder().encode(Array(Set(songs)).sorted()),as:UTF8.self)
|
|
try execute("INSERT INTO import_history(path,signature,songs,imported_at) VALUES(?,?,?,?) ON CONFLICT(path) DO UPDATE SET signature=excluded.signature,songs=excluded.songs,imported_at=excluded.imported_at",[path,signature,json,ISO8601DateFormatter().string(from:Date())])
|
|
}
|
|
func songWithFingerprint(_ fingerprint: String) throws -> Song? {
|
|
let query = try statement("SELECT id FROM songs WHERE fingerprint=? ORDER BY id LIMIT 1",[fingerprint]);defer { sqlite3_finalize(query) }
|
|
return sqlite3_step(query) == SQLITE_ROW ? try song(sqlite3_column_int64(query,0)) : nil
|
|
}
|
|
}
|
|
|
|
extension Library {
|
|
func recordOriginalName(song:Int64,name:String,path:String) throws {
|
|
try execute("UPDATE songs SET original_name=?,original_path=? WHERE id=? AND original_name=''",[name,path,String(song)])
|
|
}
|
|
func originalNamesRecorded(_ ids:[Int64]) throws -> Bool {
|
|
for id in ids { if try song(id)?.originalName.isEmpty != false { return false } };return true
|
|
}
|
|
func hasNameUndo() throws -> Bool {
|
|
let query = try statement("SELECT 1 FROM reinterpret_undo LIMIT 1",[]);defer { sqlite3_finalize(query) };return sqlite3_step(query) == SQLITE_ROW
|
|
}
|
|
@discardableResult func reinterpretNames(_ changes:[NameChange]) throws -> Int {
|
|
let changes = changes.filter{$0.before != $0.after}
|
|
guard !changes.isEmpty else { return 0 }
|
|
guard Set(changes.map(\.id)).count == changes.count else { throw LibraryError.message("A song appears more than once in this batch") }
|
|
let batch = UUID().uuidString,created = ISO8601DateFormatter().string(from:Date())
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
for change in changes {
|
|
guard let current = try song(change.id),NameMetadata(current) == change.before else { throw LibraryError.message("A song changed after the preview. Refresh the preview before applying.") }
|
|
let query = try statement("SELECT metadata_edited FROM songs WHERE id=?",[String(change.id)])
|
|
let edited = sqlite3_step(query) == SQLITE_ROW ? sqlite3_column_int(query,0) : 0;sqlite3_finalize(query)
|
|
let before = String(decoding:try JSONEncoder().encode(change.before),as:UTF8.self),after = String(decoding:try JSONEncoder().encode(change.after),as:UTF8.self)
|
|
try execute("INSERT INTO reinterpret_undo(batch,song,before_json,after_json,edited,created) VALUES(?,?,?,?,?,?)",[batch,String(change.id),before,after,String(edited),created])
|
|
try setNameMetadata(change.after,id:change.id,edited:1)
|
|
}
|
|
try execute("DELETE FROM reinterpret_undo WHERE batch NOT IN (SELECT batch FROM reinterpret_undo GROUP BY batch ORDER BY MAX(rowid) DESC LIMIT 10)")
|
|
try execute("COMMIT");return changes.count
|
|
} catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
@discardableResult func undoNameReinterpretation() throws -> Int {
|
|
try execute("BEGIN IMMEDIATE")
|
|
do {
|
|
let latest = try statement("SELECT batch FROM reinterpret_undo ORDER BY rowid DESC LIMIT 1",[])
|
|
guard sqlite3_step(latest) == SQLITE_ROW else { sqlite3_finalize(latest);throw LibraryError.message("There is no name reinterpretation to undo") }
|
|
let batch = text(latest,0);sqlite3_finalize(latest)
|
|
let query = try statement("SELECT song,before_json,after_json,edited FROM reinterpret_undo WHERE batch=? ORDER BY song",[batch])
|
|
var rows = [(Int64,NameMetadata,NameMetadata,Int32)]()
|
|
do {
|
|
defer { sqlite3_finalize(query) }
|
|
while sqlite3_step(query) == SQLITE_ROW {
|
|
let before = try JSONDecoder().decode(NameMetadata.self,from:Data(text(query,1).utf8)),after = try JSONDecoder().decode(NameMetadata.self,from:Data(text(query,2).utf8))
|
|
rows.append((sqlite3_column_int64(query,0),before,after,sqlite3_column_int(query,3)))
|
|
}
|
|
}
|
|
for (id,before,after,edited) in rows {
|
|
guard let current = try song(id),NameMetadata(current) == after else { throw LibraryError.message("A song was edited after reinterpretation. Undo would overwrite that edit, so no songs were changed.") }
|
|
try setNameMetadata(before,id:id,edited:edited)
|
|
}
|
|
try execute("DELETE FROM reinterpret_undo WHERE batch=?",[batch]);try execute("COMMIT");return rows.count
|
|
} catch { try? execute("ROLLBACK");throw error }
|
|
}
|
|
private func setNameMetadata(_ value:NameMetadata,id:Int64,edited:Int32) throws {
|
|
guard !value.title.trimmingCharacters(in:.whitespacesAndNewlines).isEmpty else { throw LibraryError.message("Song title cannot be empty") }
|
|
try execute("UPDATE songs SET title=?,artist=?,album=?,track_number=?,rendition=?,metadata_edited=? WHERE id=?",[value.title,value.artist,value.album,value.track,value.rendition,String(edited),String(id)])
|
|
}
|
|
}
|
|
|
|
struct LibraryPage {
|
|
static let size = 20
|
|
let total: Int
|
|
let count: Int
|
|
let number: Int
|
|
var range: Range<Int> { let start = number*Self.size;return start..<min(total,start+Self.size) }
|
|
init(total:Int,requested:Int) {
|
|
self.total = max(0,total);count = max(1,(max(0,total)+Self.size-1)/Self.size);number = min(count-1,max(0,requested))
|
|
}
|
|
}
|