SPB Git

Repository Public

LibraryStore+Collections.swift 16.7 KB 373 lines swift

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
Files main
Menu

Theme