SPB Git

Repository Public

SmartCrateSQL.swift 8.2 KB 163 lines swift

1// ORIX DJ2// File: Packages/ORIXDJCore/Sources/ORIXLibrary/SmartCrateSQL.swift3// Description: Compiles smart-crate rules to a parameterised SQL predicate; user text is only ever bound, never interpolated.4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai78import Foundation910/// A SQL boolean expression over the `tracks` table (aliased `t`) and its bound parameters.11public struct SQLPredicate: Sendable, Equatable {12    public let sql: String13    public let parameters: [SQLiteValue]14}1516extension SmartCrate {17    /// Compiles the rules. The returned SQL is built from fixed fragments only; every rule value is a parameter.18    /// Throws `LibraryError.invalidArgument` for a rule that makes no sense (text where a number is needed,19    /// `contains` on a numeric field, missing upper bound…). No rules = every track.20    public func predicate(now: Date = Date()) throws -> SQLPredicate {21        guard !rules.isEmpty else { return SQLPredicate(sql: "1", parameters: []) }22        var parts: [String] = []23        var parameters: [SQLiteValue] = []24        for rule in rules {25            let compiled = try rule.predicate(now: now)26            parts.append("(" + compiled.sql + ")")27            parameters.append(contentsOf: compiled.parameters)28        }29        return SQLPredicate(sql: parts.joined(separator: match == .all ? " AND " : " OR "), parameters: parameters)30    }31}3233extension SmartRule {34    private enum Kind {35        case number(column: String)36        case foldedText(column: String)37        case tag38        case flag(expression: String)39        case ageInDays40    }4142    private var kind: Kind {43        switch field {44        case .bpm: return .number(column: "t.bpm")45        case .rating: return .number(column: "t.rating")46        case .year: return .number(column: "t.year")47        case .duration: return .number(column: "t.duration")48        case .playCount: return .number(column: "t.play_count")49        case .title: return .foldedText(column: "t.title_f")50        case .artist: return .foldedText(column: "t.artist_f")51        case .album: return .foldedText(column: "t.album_f")52        case .genre: return .foldedText(column: "t.genre_f")53        case .key: return .foldedText(column: "LOWER(IFNULL(t.key, ''))")54        case .camelot: return .foldedText(column: "LOWER(IFNULL(t.camelot, ''))")55        case .format: return .foldedText(column: "LOWER(t.format)")56        case .tag: return .tag57        case .favorite: return .flag(expression: "t.favorite = 1")58        case .analysed: return .flag(expression: "t.analysis_version > 0")59        case .dateAdded: return .ageInDays60        }61    }6263    private func invalid(_ reason: String) -> LibraryError {64        .invalidArgument("Smart rule \(field.rawValue) \(comparison.rawValue): \(reason)")65    }6667    private func number(_ text: String?) throws -> Double {68        guard let text else { throw invalid("an upper value is required") }69        let trimmed = text.trimmingCharacters(in: .whitespaces).replacingOccurrences(of: ",", with: ".")70        guard let value = Double(trimmed), value.isFinite else { throw invalid("\"\(text)\" is not a number") }71        return value72    }7374    private func flag(_ text: String) throws -> Bool {75        switch text.trimmingCharacters(in: .whitespaces).lowercased() {76        case "true", "yes", "1", "on": return true77        case "false", "no", "0", "off": return false78        default: throw invalid("\"\(text)\" is not a boolean")79        }80    }8182    func predicate(now: Date) throws -> SQLPredicate {83        switch kind {84        case let .number(column):85            switch comparison {86            case .equals:87                return SQLPredicate(sql: "\(column) IS NOT NULL AND ABS(\(column) - ?) < 0.005", parameters: [.real(try number(value))])88            case .notEquals:89                return SQLPredicate(sql: "\(column) IS NULL OR ABS(\(column) - ?) >= 0.005", parameters: [.real(try number(value))])90            case .greaterThan:91                return SQLPredicate(sql: "\(column) > ?", parameters: [.real(try number(value))])92            case .lessThan:93                return SQLPredicate(sql: "\(column) < ?", parameters: [.real(try number(value))])94            case .between:95                let low = try number(value), high = try number(upperValue)96                return SQLPredicate(sql: "\(column) BETWEEN ? AND ?", parameters: [.real(min(low, high)), .real(max(low, high))])97            case .contains:98                throw invalid("\"contains\" needs a text field")99            }100101        case let .foldedText(column):102            let folded = LibraryText.fold(value.trimmingCharacters(in: .whitespaces))103            switch comparison {104            case .equals: return SQLPredicate(sql: "\(column) = ?", parameters: [.text(folded)])105            case .notEquals: return SQLPredicate(sql: "\(column) <> ?", parameters: [.text(folded)])106            case .contains:107                return SQLPredicate(sql: "\(column) LIKE ? ESCAPE '\\'",108                                    parameters: [.text("%" + LibraryText.likeEscaped(folded) + "%")])109            case .greaterThan: return SQLPredicate(sql: "\(column) > ?", parameters: [.text(folded)])110            case .lessThan: return SQLPredicate(sql: "\(column) < ?", parameters: [.text(folded)])111            case .between:112                guard let upperValue else { throw invalid("an upper value is required") }113                let upper = LibraryText.fold(upperValue.trimmingCharacters(in: .whitespaces))114                return SQLPredicate(sql: "\(column) BETWEEN ? AND ?", parameters: [.text(min(folded, upper)), .text(max(folded, upper))])115            }116117        case .tag:118            let folded = LibraryText.likeEscaped(LibraryText.fold(value.trimmingCharacters(in: .whitespaces)))119            let separator = LibraryStore.tagSeparator120            switch comparison {121            case .equals:122                return SQLPredicate(sql: "t.tags_index LIKE ? ESCAPE '\\'", parameters: [.text("%\(separator)\(folded)\(separator)%")])123            case .notEquals:124                return SQLPredicate(sql: "t.tags_index NOT LIKE ? ESCAPE '\\'", parameters: [.text("%\(separator)\(folded)\(separator)%")])125            case .contains:126                return SQLPredicate(sql: "t.tags_index LIKE ? ESCAPE '\\'", parameters: [.text("%\(folded)%")])127            case .greaterThan, .lessThan, .between:128                throw invalid("tags support equals, notEquals and contains only")129            }130131        case let .flag(expression):132            let wanted = try flag(value)133            switch comparison {134            case .equals: return SQLPredicate(sql: wanted ? expression : "NOT (\(expression))", parameters: [])135            case .notEquals: return SQLPredicate(sql: wanted ? "NOT (\(expression))" : expression, parameters: [])136            case .contains, .greaterThan, .lessThan, .between:137                throw invalid("boolean fields support equals and notEquals only")138            }139140        case .ageInDays:141            // The value is an age in days; a larger age is an older (smaller) date_added.142            let reference = now.timeIntervalSinceReferenceDate143            func cutoff(_ days: Double) -> SQLiteValue { .real(reference - days * 86_400) }144            switch comparison {145            case .greaterThan: return SQLPredicate(sql: "t.date_added < ?", parameters: [cutoff(try number(value))])146            case .lessThan: return SQLPredicate(sql: "t.date_added > ?", parameters: [cutoff(try number(value))])147            case .between:148                let low = try number(value), high = try number(upperValue)149                return SQLPredicate(sql: "t.date_added BETWEEN ? AND ?", parameters: [cutoff(max(low, high)), cutoff(min(low, high))])150            case .equals:151                // "N days ago" = between N and N+1 days old.152                let days = try number(value).rounded(.down)153                return SQLPredicate(sql: "t.date_added > ? AND t.date_added <= ?", parameters: [cutoff(days + 1), cutoff(days)])154            case .notEquals:155                let days = try number(value).rounded(.down)156                return SQLPredicate(sql: "NOT (t.date_added > ? AND t.date_added <= ?)", parameters: [cutoff(days + 1), cutoff(days)])157            case .contains:158                throw invalid("\"contains\" needs a text field")159            }160        }161    }162}163
Files main
Menu

Theme