Skip to content

Commit 223e7d9

Browse files
atdrendelatdrendel
authored
Collating sequences (#57)
* Add SQLiteDatabase.addCollation() and removeCollation() * Update CI * Update GRDB to version 7
1 parent 413a130 commit 223e7d9

5 files changed

Lines changed: 200 additions & 13 deletions

File tree

‎.github/workflows/ci.yml‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4,12 +4,12 @@ on: push
44

55
jobs:
66
test:
7-
runs-on: macos-13
7+
runs-on: macos-15
88

99
steps:
10-
- uses: actions/checkout@v3
11-
- name: Select Xcode 15
12-
run: sudo xcode-select -s /Applications/Xcode_15.0.app
10+
- uses: actions/checkout@v4
11+
- name: Select Xcode 16
12+
run: sudo xcode-select -s /Applications/Xcode_16.3.app
1313
- name: Test
1414
run: swift test
1515

‎Package.resolved‎

Lines changed: 2 additions & 2 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎Package.swift‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ let package = Package(
2020
),
2121
.package(
2222
url: "https://github.com/groue/GRDB.swift.git",
23-
from: "6.29.3"
23+
from: "7.4.1"
2424
),
2525
.package(
2626
url: "https://github.com/shareup/precise-iso-8601-date-formatter.git",

‎Sources/SQLite/SQLiteDatabase.swift‎

Lines changed: 27 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -587,6 +587,33 @@ public extension SQLiteDatabase {
587587
}
588588
}
589589

590+
// MARK: - Collating sequences
591+
592+
public extension SQLiteDatabase {
593+
func addCollation(
594+
named name: String,
595+
comparator: @escaping @Sendable (String, String) -> ComparisonResult
596+
) throws {
597+
let collation = DatabaseCollation(
598+
name,
599+
function: comparator
600+
)
601+
try database
602+
.writer
603+
.barrierWriteWithoutTransaction { $0.add(collation: collation) }
604+
}
605+
606+
func removeCollation(named name: String) throws {
607+
let collation = DatabaseCollation(
608+
name,
609+
function: { _, _ in .orderedSame }
610+
)
611+
try database
612+
.writer
613+
.barrierWriteWithoutTransaction { $0.remove(collation: collation) }
614+
}
615+
}
616+
590617
// MARK: - Pragmas
591618

592619
public extension SQLiteDatabase {
@@ -730,12 +757,6 @@ private extension SQLiteDatabase {
730757

731758
var config = Configuration()
732759
config.journalMode = isInMemory ? .default : .wal
733-
// NOTE: GRDB recommends `defaultTransactionKind` be set
734-
// to `.immediate` in order to prevent `SQLITE_BUSY`
735-
// errors.
736-
//
737-
// https://swiftpackageindex.com/groue/grdb.swift/v6.24.2/documentation/grdb/databasesharing#How-to-limit-the-SQLITEBUSY-error
738-
config.defaultTransactionKind = .immediate
739760
config.busyMode = .timeout(busyTimeout)
740761
config.observesSuspensionNotifications = true
741762
config.maximumReaderCount = max(

‎Tests/SQLiteTests/SQLiteDatabaseTests.swift‎

Lines changed: 166 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,6 +94,172 @@ final class SQLiteDatabaseTests: XCTestCase {
9494
}
9595
}
9696

97+
func testAddAndRemoveCollation() throws {
98+
struct Entity: Hashable, SQLiteTransformable {
99+
let id: String
100+
let string: String?
101+
102+
init(_ id: Int, _ string: String? = nil) {
103+
self.id = String(id)
104+
self.string = string
105+
}
106+
107+
init(row: SQLiteRow) throws {
108+
id = try row.value(for: "id")
109+
string = row.optionalValue(for: "string")
110+
}
111+
112+
var asArguments: SQLiteArguments {
113+
[
114+
"id": .text(id),
115+
"string": string.map { .text($0) } ?? .null,
116+
]
117+
}
118+
}
119+
120+
let apple = Entity(1, "Apple")
121+
let banana = Entity(2, "banana")
122+
let zebra = Entity(3, "Zebra")
123+
let null1 = Entity(4)
124+
let null2 = Entity(5)
125+
126+
try database.inTransaction { db in
127+
try db.write(_createTableWithIDAsStringAndNullableString)
128+
try [apple, banana, zebra, null1, null2]
129+
.forEach { entity in
130+
try db.write(
131+
_insertIDAndString,
132+
arguments: entity.asArguments
133+
)
134+
}
135+
}
136+
137+
let selectDefaultSorted: SQL = """
138+
SELECT * FROM test ORDER BY string;
139+
"""
140+
141+
let selectCustomCaseSensitiveSorted: SQL = """
142+
SELECT * FROM test ORDER BY string COLLATE CUSTOM;
143+
"""
144+
145+
let selectCustomCaseInsensitiveSorted: SQL = """
146+
SELECT * FROM test ORDER BY string COLLATE CUSTOM_NOCASE;
147+
"""
148+
149+
let defaultSorted: [Entity] = try database.read(selectDefaultSorted)
150+
XCTAssertEqual(
151+
defaultSorted,
152+
[null1, null2, apple, zebra, banana]
153+
)
154+
155+
XCTAssertThrowsError(
156+
try database.read(selectCustomCaseSensitiveSorted)
157+
) { error in
158+
guard case SQLiteError.SQLITE_ERROR_MISSING_COLLSEQ = error else {
159+
XCTFail("Should have thrown SQLITE_ERROR")
160+
return
161+
}
162+
}
163+
164+
try database.addCollation(named: "CUSTOM") { $0.compare($1) }
165+
let customSorted: [Entity] = try database.read(selectCustomCaseSensitiveSorted)
166+
XCTAssertEqual(
167+
customSorted,
168+
[null1, null2, apple, zebra, banana]
169+
)
170+
171+
try database.addCollation(
172+
named: "CUSTOM_NOCASE"
173+
) { $0.caseInsensitiveCompare($1) }
174+
175+
let customNoCaseSorted: [Entity] = try database
176+
.read(selectCustomCaseInsensitiveSorted)
177+
XCTAssertEqual(
178+
customNoCaseSorted,
179+
[null1, null2, apple, banana, zebra]
180+
)
181+
182+
try database.removeCollation(named: "CUSTOM_NOCASE")
183+
XCTAssertThrowsError(
184+
try database.read(selectCustomCaseInsensitiveSorted)
185+
) { error in
186+
guard case SQLiteError.SQLITE_ERROR_MISSING_COLLSEQ = error else {
187+
XCTFail("Should have thrown SQLITE_ERROR")
188+
return
189+
}
190+
}
191+
let customSortedAfterRemovingNoCase: [Entity] = try database
192+
.read(selectCustomCaseSensitiveSorted)
193+
XCTAssertEqual(
194+
customSortedAfterRemovingNoCase,
195+
[null1, null2, apple, zebra, banana]
196+
)
197+
}
198+
199+
func testCustomLocalizedCollation() throws {
200+
try database.addCollation(named: "LOCALIZED") { lhs, rhs in
201+
lhs.localizedStandardCompare(rhs)
202+
}
203+
204+
// NOTE: ([toInsert], [binary sort], [localized sort])
205+
let cases: [([String], [String], [String])] = [
206+
// Basic Latin
207+
(
208+
["a", "A", "b", "B"],
209+
["A", "B", "a", "b"],
210+
["a", "A", "b", "B"]
211+
),
212+
213+
// Accented Latin
214+
(
215+
["cafe", "café", "caffe", "caffè"],
216+
["cafe", "caffe", "caffè", "café"],
217+
["cafe", "café", "caffe", "caffè"]
218+
),
219+
220+
// Chinese
221+
(
222+
["长城", "长江", "上海", "北京"],
223+
["上海", "北京", "长城", "长江"],
224+
["上海", "北京", "长城", "长江"]
225+
),
226+
227+
// Mixed
228+
(
229+
["z", "中", "9", "ñ", "a"],
230+
["9", "a", "z", "ñ", "中"],
231+
["9", "a", "ñ", "z", "中"]
232+
),
233+
]
234+
235+
for (toInsert, binarySort, localizedSort) in cases {
236+
try database.inTransaction { db in
237+
try db.execute(raw: _createTableWithIDAsStringAndNullableString)
238+
try toInsert.enumerated().forEach { id, string in
239+
try db.write(
240+
_insertIDAndString,
241+
arguments: [
242+
"id": .text(String(id)),
243+
"string": .text(string),
244+
]
245+
)
246+
}
247+
}
248+
249+
let binarySorted: [String] = try database
250+
.read("SELECT * FROM test ORDER BY string;")
251+
.compactMap { $0["string"]?.stringValue }
252+
XCTAssertEqual(binarySorted, binarySort)
253+
254+
let localizedSorted: [String] = try database
255+
.read("SELECT * FROM test ORDER BY string COLLATE LOCALIZED;")
256+
.compactMap { $0["string"]?.stringValue }
257+
XCTAssertEqual(localizedSorted, localizedSort)
258+
259+
try database.write("DROP TABLE test;")
260+
}
261+
}
262+
97263
func testUserVersion() throws {
98264
XCTAssertEqual(0, database.userVersion)
99265

0 commit comments

Comments
 (0)