Repository Public
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