Repository Public
1// ORIX DJ2// File: Packages/ORIXDJCore/Sources/ORIXLibrary/LibraryStore+Collections.swift3// Description: Crates, ordered playlists, smart crates, play history and sessions.4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai78import Foundation9import SQLite31011extension LibraryStore {12 private func cleanName(_ name: String, _ what: String) throws -> String {13 let trimmed = name.trimmingCharacters(in: .whitespacesAndNewlines)14 guard !trimmed.isEmpty else { throw LibraryError.invalidArgument("a \(what) needs a name") }15 return trimmed16 }1718 private func nextSortIndex(_ table: String, _ db: SQLiteDatabase) throws -> Int {19 Int(try db.scalarInt("SELECT IFNULL(MAX(sort_index), -1) + 1 FROM \(table)"))20 }2122 private func require(_ table: String, _ id: Int64, _ what: String, _ db: SQLiteDatabase) throws {23 guard try db.scalarInt("SELECT COUNT(*) FROM \(table) WHERE id = ?", [.integer(id)]) == 1 else {24 throw LibraryError.notFound("\(what) \(id)")25 }26 }2728 private func setOrder(_ table: String, _ ids: [Int64], _ db: SQLiteDatabase) throws {29 for (index, id) in ids.enumerated() {30 try db.run("UPDATE \(table) SET sort_index = ? WHERE id = ?", [.int(index), .integer(id)])31 }32 }3334 /// Foreign-key failures (unknown track id) become `.notFound`.35 private func mappingMissingRows<T>(_ what: String, _ body: () throws -> T) throws -> T {36 do {37 return try body()38 } catch let LibraryError.sqlite(code, _) where code == (SQLITE_CONSTRAINT | (3 << 8)) /* SQLITE_CONSTRAINT_FOREIGNKEY */ {39 throw LibraryError.notFound(what)40 }41 }4243 // MARK: Crates4445 @discardableResult46 public func createCrate(name: String) throws -> Crate {47 let name = try cleanName(name, "crate")48 return try write { db in49 let index = try nextSortIndex("crates", db)50 try db.run("INSERT INTO crates (name, sort_index) VALUES (?, ?)", [.text(name), .int(index)])51 return Crate(id: db.lastInsertRowID, name: name, sortIndex: index)52 }53 }5455 /// Crates by `sortIndex`, with their track counts.56 public func crates() throws -> [Crate] {57 try read {58 try $0.query("""59 SELECT c.id, c.name, c.sort_index, (SELECT COUNT(*) FROM crate_tracks ct WHERE ct.crate_id = c.id)60 FROM crates c ORDER BY c.sort_index, c.id61 """) { Crate(id: $0.int64(0), name: $0.string(1), sortIndex: $0.int(2), trackCount: $0.int(3)) }62 }63 }6465 public func renameCrate(id: Int64, to name: String) throws {66 let name = try cleanName(name, "crate")67 try write { db in68 guard try db.run("UPDATE crates SET name = ? WHERE id = ?", [.text(name), .integer(id)]) == 1 else {69 throw LibraryError.notFound("crate \(id)")70 }71 }72 }7374 public func deleteCrate(id: Int64) throws {75 try write { _ = try $0.run("DELETE FROM crates WHERE id = ?", [.integer(id)]) }76 }7778 /// Sets `sortIndex` from the position of each id in `ids`.79 public func setCrateOrder(_ ids: [Int64]) throws {80 try write { try setOrder("crates", ids, $0) }81 }8283 /// Adds tracks to a crate; tracks already in it are ignored.84 public func addTracks(_ trackIDs: [Int64], toCrate crateID: Int64) throws {85 try write { db in86 try require("crates", crateID, "crate", db)87 try mappingMissingRows("track") {88 for trackID in trackIDs {89 try db.run("INSERT OR IGNORE INTO crate_tracks (crate_id, track_id) VALUES (?, ?)", [.integer(crateID), .integer(trackID)])90 }91 }92 }93 }9495 public func removeTracks(_ trackIDs: [Int64], fromCrate crateID: Int64) throws {96 try write { db in97 for trackID in trackIDs {98 try db.run("DELETE FROM crate_tracks WHERE crate_id = ? AND track_id = ?", [.integer(crateID), .integer(trackID)])99 }100 }101 }102103 /// Ids of the crates that contain a track.104 public func crateIDs(containing trackID: Int64) throws -> [Int64] {105 try read { try $0.query("SELECT crate_id FROM crate_tracks WHERE track_id = ? ORDER BY crate_id", [.integer(trackID)]) { $0.int64(0) } }106 }107108 // MARK: Playlists109110 @discardableResult111 public func createPlaylist(name: String) throws -> Playlist {112 let name = try cleanName(name, "playlist")113 return try write { db in114 let index = try nextSortIndex("playlists", db)115 try db.run("INSERT INTO playlists (name, sort_index) VALUES (?, ?)", [.text(name), .int(index)])116 return Playlist(id: db.lastInsertRowID, name: name, sortIndex: index)117 }118 }119120 /// Playlists by `sortIndex`, with their track counts.121 public func playlists() throws -> [Playlist] {122 try read {123 try $0.query("""124 SELECT p.id, p.name, p.sort_index, (SELECT COUNT(*) FROM playlist_tracks pt WHERE pt.playlist_id = p.id)125 FROM playlists p ORDER BY p.sort_index, p.id126 """) { Playlist(id: $0.int64(0), name: $0.string(1), sortIndex: $0.int(2), trackCount: $0.int(3)) }127 }128 }129130 public func renamePlaylist(id: Int64, to name: String) throws {131 let name = try cleanName(name, "playlist")132 try write { db in133 guard try db.run("UPDATE playlists SET name = ? WHERE id = ?", [.text(name), .integer(id)]) == 1 else {134 throw LibraryError.notFound("playlist \(id)")135 }136 }137 }138139 public func deletePlaylist(id: Int64) throws {140 try write { _ = try $0.run("DELETE FROM playlists WHERE id = ?", [.integer(id)]) }141 }142143 public func setPlaylistOrder(_ ids: [Int64]) throws {144 try write { try setOrder("playlists", ids, $0) }145 }146147 private func entryIDs(_ playlistID: Int64, _ db: SQLiteDatabase) throws -> [Int64] {148 try db.query("SELECT id FROM playlist_tracks WHERE playlist_id = ? ORDER BY position, id", [.integer(playlistID)]) { $0.int64(0) }149 }150151 private func writePositions(_ entryIDs: [Int64], _ db: SQLiteDatabase) throws {152 for (position, id) in entryIDs.enumerated() {153 try db.run("UPDATE playlist_tracks SET position = ? WHERE id = ?", [.int(position), .integer(id)])154 }155 }156157 /// The slots of a playlist in order (positions are dense, 0-based).158 public func playlistEntries(playlistID: Int64) throws -> [PlaylistEntry] {159 try read { db in160 let rows = try db.query("SELECT id, track_id FROM playlist_tracks WHERE playlist_id = ? ORDER BY position, id",161 [.integer(playlistID)]) { ($0.int64(0), $0.int64(1)) }162 return rows.enumerated().map { PlaylistEntry(id: $1.0, playlistID: playlistID, trackID: $1.1, position: $0) }163 }164 }165166 /// Inserts tracks at `index` (appends when `nil` or past the end). The same track may appear several times.167 public func addTracks(_ trackIDs: [Int64], toPlaylist playlistID: Int64, at index: Int? = nil) throws {168 try write { db in169 try require("playlists", playlistID, "playlist", db)170 var order = try entryIDs(playlistID, db)171 let insertion = min(max(0, index ?? order.count), order.count)172 var inserted: [Int64] = []173 try mappingMissingRows("track") {174 for trackID in trackIDs {175 try db.run("INSERT INTO playlist_tracks (playlist_id, track_id, position) VALUES (?, ?, 0)",176 [.integer(playlistID), .integer(trackID)])177 inserted.append(db.lastInsertRowID)178 }179 }180 order.insert(contentsOf: inserted, at: insertion)181 try writePositions(order, db)182 }183 }184185 /// Replaces the whole content of a playlist.186 public func setPlaylistTracks(playlistID: Int64, trackIDs: [Int64]) throws {187 try write { db in188 try require("playlists", playlistID, "playlist", db)189 try db.run("DELETE FROM playlist_tracks WHERE playlist_id = ?", [.integer(playlistID)])190 try mappingMissingRows("track") {191 for (position, trackID) in trackIDs.enumerated() {192 try db.run("INSERT INTO playlist_tracks (playlist_id, track_id, position) VALUES (?, ?, ?)",193 [.integer(playlistID), .integer(trackID), .int(position)])194 }195 }196 }197 }198199 /// Removes the slots at the given positions.200 public func removeFromPlaylist(_ playlistID: Int64, atPositions positions: IndexSet) throws {201 try write { db in202 var order = try entryIDs(playlistID, db)203 for position in positions.reversed() where position >= 0 && position < order.count {204 try db.run("DELETE FROM playlist_tracks WHERE id = ?", [.integer(order[position])])205 order.remove(at: position)206 }207 try writePositions(order, db)208 }209 }210211 /// Removes every slot of the given tracks from a playlist.212 public func removeTracks(_ trackIDs: [Int64], fromPlaylist playlistID: Int64) throws {213 try write { db in214 for trackID in trackIDs {215 try db.run("DELETE FROM playlist_tracks WHERE playlist_id = ? AND track_id = ?", [.integer(playlistID), .integer(trackID)])216 }217 try writePositions(try entryIDs(playlistID, db), db)218 }219 }220221 /// Moves the slots at `offsets` so that they start at `destination`, with the semantics of SwiftUI's222 /// `onMove` / `Array.move(fromOffsets:toOffset:)`: `destination` is an index in the list *before* the move.223 public func movePlaylistEntries(playlistID: Int64, fromOffsets offsets: IndexSet, toOffset destination: Int) throws {224 try write { db in225 let order = try entryIDs(playlistID, db)226 let valid = offsets.filter { $0 >= 0 && $0 < order.count }227 guard !valid.isEmpty else { return }228 let target = min(max(0, destination), order.count)229 let moved = valid.map { order[$0] }230 let movedSet = Set(valid)231 var remaining: [Int64] = []232 var insertion = 0233 for (index, id) in order.enumerated() where !movedSet.contains(index) {234 if index < target { insertion += 1 }235 remaining.append(id)236 }237 remaining.insert(contentsOf: moved, at: insertion)238 try writePositions(remaining, db)239 }240 }241242 /// Moves one slot from a position to another final position.243 public func movePlaylistEntry(playlistID: Int64, from source: Int, to destination: Int) throws {244 try write { db in245 var order = try entryIDs(playlistID, db)246 guard source >= 0, source < order.count else { throw LibraryError.invalidArgument("no playlist position \(source)") }247 let id = order.remove(at: source)248 order.insert(id, at: min(max(0, destination), order.count))249 try writePositions(order, db)250 }251 }252253 // MARK: Smart crates254255 func loadSmartCrate(_ id: Int64, _ db: SQLiteDatabase) throws -> SmartCrate? {256 try db.first("SELECT id, name, sort_index, match, rules FROM smart_crates WHERE id = ?", [.integer(id)], map: LibraryStore.decodeSmartCrate)257 }258259 private static func decodeSmartCrate(_ r: SQLiteRow) -> SmartCrate {260 SmartCrate(id: r.int64(0), name: r.string(1), sortIndex: r.int(2),261 match: SmartCrate.Match(rawValue: r.string(3)) ?? .all,262 rules: decodeJSON(r.string(4)))263 }264265 /// Creates a smart crate. The rules are validated (compiled) first.266 @discardableResult267 public func createSmartCrate(name: String, match: SmartCrate.Match = .all, rules: [SmartRule]) throws -> SmartCrate {268 var crate = SmartCrate(name: try cleanName(name, "smart crate"), match: match, rules: rules)269 _ = try crate.predicate()270 return try write { db in271 crate.sortIndex = try nextSortIndex("smart_crates", db)272 try db.run("INSERT INTO smart_crates (name, sort_index, match, rules) VALUES (?, ?, ?, ?)",273 [.text(crate.name), .int(crate.sortIndex), .text(match.rawValue), .text(LibraryStore.encodeJSON(rules))])274 crate.id = db.lastInsertRowID275 return crate276 }277 }278279 public func smartCrates() throws -> [SmartCrate] {280 try read {281 try $0.query("SELECT id, name, sort_index, match, rules FROM smart_crates ORDER BY sort_index, id", map: LibraryStore.decodeSmartCrate)282 }283 }284285 public func smartCrate(id: Int64) throws -> SmartCrate? {286 try read { try loadSmartCrate(id, $0) }287 }288289 /// Replaces name, match mode, rules and sort index of an existing smart crate.290 public func updateSmartCrate(_ crate: SmartCrate) throws {291 let name = try cleanName(crate.name, "smart crate")292 _ = try crate.predicate()293 try write { db in294 let changed = try db.run("UPDATE smart_crates SET name = ?, sort_index = ?, match = ?, rules = ? WHERE id = ?",295 [.text(name), .int(crate.sortIndex), .text(crate.match.rawValue),296 .text(LibraryStore.encodeJSON(crate.rules)), .integer(crate.id)])297 guard changed == 1 else { throw LibraryError.notFound("smart crate \(crate.id)") }298 }299 }300301 public func deleteSmartCrate(id: Int64) throws {302 try write { _ = try $0.run("DELETE FROM smart_crates WHERE id = ?", [.integer(id)]) }303 }304305 /// Tracks matching ad-hoc rules, without storing a smart crate (live preview in the rule editor).306 public func tracks(matching rules: [SmartRule], match: SmartCrate.Match = .all, limit: Int? = nil) throws -> [Track] {307 let predicate = try SmartCrate(name: "preview", match: match, rules: rules).predicate()308 return try read {309 try $0.query("SELECT \(LibraryStore.trackSelect) FROM tracks t WHERE \(predicate.sql) ORDER BY t.title_f, t.artist_f, t.id LIMIT ?",310 predicate.parameters + [.int(limit ?? -1)], map: LibraryStore.decodeTrack)311 }312 }313314 // MARK: History315316 /// Starts a history session (one DJ set).317 @discardableResult318 public func beginSession(name: String = "", at date: Date = Date()) throws -> HistorySession {319 try write { db in320 try db.run("INSERT INTO sessions (name, started_at) VALUES (?, ?)", [.text(name), .date(date)])321 return HistorySession(id: db.lastInsertRowID, name: name, startedAt: date, endedAt: nil, entryCount: 0)322 }323 }324325 public func endSession(id: Int64, at date: Date = Date()) throws {326 try write { db in327 guard try db.run("UPDATE sessions SET ended_at = ? WHERE id = ?", [.date(date), .integer(id)]) == 1 else {328 throw LibraryError.notFound("session \(id)")329 }330 }331 }332333 /// Sessions, newest first.334 public func sessions() throws -> [HistorySession] {335 try read {336 try $0.query("""337 SELECT s.id, s.name, s.started_at, s.ended_at, (SELECT COUNT(*) FROM history h WHERE h.session_id = s.id)338 FROM sessions s ORDER BY s.started_at DESC, s.id DESC339 """) {340 HistorySession(id: $0.int64(0), name: $0.string(1), startedAt: $0.date(2), endedAt: $0.optionalDate(3), entryCount: $0.int(4))341 }342 }343 }344345 /// Deletes a session and its history entries (play counts are kept).346 public func deleteSession(id: Int64) throws {347 try write { _ = try $0.run("DELETE FROM sessions WHERE id = ?", [.integer(id)]) }348 }349350 /// History entries, newest first. `sessionID == nil` returns every session.351 public func history(sessionID: Int64? = nil, limit: Int? = nil) throws -> [HistoryEntry] {352 try read { db in353 let map: (SQLiteRow) -> HistoryEntry = {354 HistoryEntry(id: $0.int64(0), trackID: $0.int64(1), playedAt: $0.date(2), deck: $0.string(3), sessionID: $0.optionalInt64(4))355 }356 if let sessionID {357 return try db.query("SELECT id, track_id, played_at, deck, session_id FROM history WHERE session_id = ? ORDER BY played_at DESC, id DESC LIMIT ?",358 [.integer(sessionID), .int(limit ?? -1)], map: map)359 }360 return try db.query("SELECT id, track_id, played_at, deck, session_id FROM history ORDER BY played_at DESC, id DESC LIMIT ?",361 [.int(limit ?? -1)], map: map)362 }363 }364365 /// Erases the whole play history (sessions included). Play counts and last-played dates are kept.366 public func clearHistory() throws {367 try write { db in368 try db.run("DELETE FROM history")369 try db.run("DELETE FROM sessions")370 }371 }372}373