1631 lines
61 KiB
Swift
1631 lines
61 KiB
Swift
import XCTest
|
|
import GRDB
|
|
|
|
private final class TestStream: TextOutputStream {
|
|
var output: String
|
|
|
|
init() {
|
|
output = ""
|
|
}
|
|
|
|
func write(_ string: String) {
|
|
output.append(string)
|
|
}
|
|
}
|
|
|
|
private struct Player: Codable, MutablePersistableRecord {
|
|
static let team = belongsTo(Team.self)
|
|
var id: Int64?
|
|
var name: String
|
|
var teamId: String?
|
|
|
|
mutating func didInsert(_ inserted: InsertionSuccess) {
|
|
id = inserted.rowID
|
|
}
|
|
}
|
|
|
|
private struct Team: Codable, PersistableRecord {
|
|
static let players = hasMany(Player.self)
|
|
var id: String
|
|
var name: String
|
|
var color: String
|
|
}
|
|
|
|
final class DatabaseDumpTests: GRDBTestCase {
|
|
// MARK: - Debug
|
|
|
|
func test_debug_value_formatting() throws {
|
|
try makeValuesDatabase().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM value ORDER BY name", format: .debug(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
blob: ascii apostrophe|[']
|
|
blob: ascii double quote|["]
|
|
blob: ascii line feed|[
|
|
]
|
|
blob: ascii long|Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.
|
|
blob: ascii short|Hello
|
|
blob: ascii tab|[\t]
|
|
blob: binary short|X'80'
|
|
blob: empty|
|
|
blob: utf8 short|您好🙂
|
|
blob: uuid|69BF8A9C-D9F0-4777-BD11-93451D84CBCF
|
|
double: -1.0|-1.0
|
|
double: 0.0|0.0
|
|
double: 123.45|123.45
|
|
double: nan|
|
|
integer: -1|-1
|
|
integer: 0|0
|
|
integer: 123|123
|
|
integer: max|9223372036854775807
|
|
integer: min|-9223372036854775808
|
|
null|
|
|
text: ascii apostrophe|[']
|
|
text: ascii backslash|[\\]
|
|
text: ascii double quote|["]
|
|
text: ascii line feed|[
|
|
]
|
|
text: ascii long|Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.
|
|
text: ascii short|Hello
|
|
text: ascii slash|[/]
|
|
text: ascii tab|[\t]
|
|
text: ascii url|https://github.com/groue/GRDB.swift
|
|
text: empty|
|
|
text: utf8 short|您好🙂
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_debug_empty_results() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
do {
|
|
// Columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT NULL WHERE NULL", format: .debug(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
do {
|
|
// No columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("CREATE TABLE t(a)", format: .debug(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_debug_headers() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Headers on
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .debug(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id|teamId|name
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Headers on, no result
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player WHERE 0", format: .debug(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
do {
|
|
// Headers off
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .debug(header: false), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Headers off, no result
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player WHERE 0", format: .debug(header: false), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_debug_duplicate_columns() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT 1 AS name, 'foo' AS name", format: .debug(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
name|name
|
|
1|foo
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_debug_multiple_statements() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL(
|
|
"""
|
|
CREATE TABLE t(a, b);
|
|
INSERT INTO t VALUES (1, 'foo');
|
|
INSERT INTO t VALUES (2, 'bar');
|
|
SELECT * FROM t ORDER BY a;
|
|
SELECT b FROM t ORDER BY b;
|
|
SELECT NULL WHERE NULL;
|
|
""",
|
|
format: .debug(),
|
|
to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|foo
|
|
2|bar
|
|
bar
|
|
foo
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_debug_separator() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default separator
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .debug(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id|teamId|name
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom separator
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .debug(header: true, separator: "---"), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id---teamId---name
|
|
1---FRA---Antoine Dupond
|
|
2---ENG---Owen Farrell
|
|
3------Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_debug_nullValue() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default null
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .debug(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom null
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .debug(nullValue: "NULL"), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3|NULL|Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - JSON
|
|
|
|
func test_json_value_formatting() throws {
|
|
guard #available(iOS 13.0, macOS 10.15, tvOS 13.0, watchOS 6.0, *) else {
|
|
throw XCTSkip("Skip because this test relies on JSONEncoder.OutputFormatting.withoutEscapingSlashes")
|
|
}
|
|
|
|
try makeValuesDatabase().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM value ORDER BY name", format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"name":"blob: ascii apostrophe","value":"Wydd"},
|
|
{"name":"blob: ascii double quote","value":"WyJd"},
|
|
{"name":"blob: ascii line feed","value":"Wwpd"},
|
|
{"name":"blob: ascii long","value":"TG9yZW0gaXBzdW0gZG9sb3Igc2l0IGFtZXQsIGNvbnNlY3RldHVyIGFkaXBpc2NpbmcgZWxpdC4gTW9yYmkgdHJpc3RpcXVlIHRlbXBvciBjb25kaW1lbnR1bS4gUGVsbGVudGVzcXVlIHBoYXJldHJhIGxhY3VzIG5vbiBhbnRlIHNvbGxpY2l0dWRpbiBhdWN0b3IuIFZlc3RpYnVsdW0gc2l0IGFtZXQgbWF1cmlzIHZpdGFlIHVybmEgbm9uIGx1Y3R1cy4="},
|
|
{"name":"blob: ascii short","value":"SGVsbG8="},
|
|
{"name":"blob: ascii tab","value":"Wwld"},
|
|
{"name":"blob: binary short","value":"gA=="},
|
|
{"name":"blob: empty","value":""},
|
|
{"name":"blob: utf8 short","value":"5oKo5aW98J+Zgg=="},
|
|
{"name":"blob: uuid","value":"ab+KnNnwR3e9EZNFHYTLzw=="},
|
|
{"name":"double: -1.0","value":-1},
|
|
{"name":"double: 0.0","value":0},
|
|
{"name":"double: 123.45","value":123.45},
|
|
{"name":"double: nan","value":null},
|
|
{"name":"integer: -1","value":-1},
|
|
{"name":"integer: 0","value":0},
|
|
{"name":"integer: 123","value":123},
|
|
{"name":"integer: max","value":9223372036854775807},
|
|
{"name":"integer: min","value":-9223372036854775808},
|
|
{"name":"null","value":null},
|
|
{"name":"text: ascii apostrophe","value":"[']"},
|
|
{"name":"text: ascii backslash","value":"[\\\\]"},
|
|
{"name":"text: ascii double quote","value":"[\\"]"},
|
|
{"name":"text: ascii line feed","value":"[\\n]"},
|
|
{"name":"text: ascii long","value":"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus."},
|
|
{"name":"text: ascii short","value":"Hello"},
|
|
{"name":"text: ascii slash","value":"[/]"},
|
|
{"name":"text: ascii tab","value":"[\\t]"},
|
|
{"name":"text: ascii url","value":"https://github.com/groue/GRDB.swift"},
|
|
{"name":"text: empty","value":""},
|
|
{"name":"text: utf8 short","value":"您好🙂"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_json_empty_results() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
do {
|
|
// Columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT NULL WHERE NULL", format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[]
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// No columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("CREATE TABLE t(a)", format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_json_duplicate_columns() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT 1 AS name, 'foo' AS name", format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"name":1,"name":"foo"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_json_multiple_statements() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL(
|
|
"""
|
|
CREATE TABLE t(a, b);
|
|
INSERT INTO t VALUES (1, 'foo');
|
|
INSERT INTO t VALUES (2, 'bar');
|
|
SELECT * FROM t ORDER BY a;
|
|
SELECT b FROM t ORDER BY b;
|
|
SELECT NULL WHERE NULL;
|
|
""",
|
|
format: .json(),
|
|
to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"a":1,"b":"foo"},
|
|
{"a":2,"b":"bar"}]
|
|
[{"b":"bar"},
|
|
{"b":"foo"}]
|
|
[]
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_json_custom_encoder() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
try db.execute(literal: """
|
|
CREATE TABLE t(id INTEGER PRIMARY KEY, name TEXT, value DOUBLE);
|
|
INSERT INTO t VALUES (1, 'a', 0.0);
|
|
INSERT INTO t VALUES (2, 'b', \(1.0 / 0));
|
|
INSERT INTO t VALUES (3, 'c', \(-1.0 / 0));
|
|
""")
|
|
let encoder = JSONDumpFormat.defaultEncoder
|
|
encoder.nonConformingFloatEncodingStrategy = .convertToString(
|
|
positiveInfinity: "much too much",
|
|
negativeInfinity: "whoops",
|
|
nan: "")
|
|
encoder.outputFormatting = [.prettyPrinted, .sortedKeys /* ignored */]
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM t ORDER BY id", format: .json(encoder: encoder), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[
|
|
{
|
|
"id":1,
|
|
"name":"a",
|
|
"value":0
|
|
},
|
|
{
|
|
"id":2,
|
|
"name":"b",
|
|
"value":"much too much"
|
|
},
|
|
{
|
|
"id":3,
|
|
"name":"c",
|
|
"value":"whoops"
|
|
}
|
|
]
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
// MARK: - Line
|
|
|
|
func test_line_value_formatting() throws {
|
|
try makeValuesDatabase().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM value ORDER BY name", format: .line(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
name = blob: ascii apostrophe
|
|
value = [']
|
|
|
|
name = blob: ascii double quote
|
|
value = ["]
|
|
|
|
name = blob: ascii line feed
|
|
value = [
|
|
]
|
|
|
|
name = blob: ascii long
|
|
value = Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.
|
|
|
|
name = blob: ascii short
|
|
value = Hello
|
|
|
|
name = blob: ascii tab
|
|
value = [\t]
|
|
|
|
name = blob: binary short
|
|
value = �
|
|
|
|
name = blob: empty
|
|
value = \n\
|
|
|
|
name = blob: utf8 short
|
|
value = 您好🙂
|
|
|
|
name = blob: uuid
|
|
value = \("i\u{fffd}\u{fffd}\u{fffd}\u{fffd}\u{fffd}Gw\u{fffd}\u{11}\u{fffd}E\u{1d}\u{fffd}\u{fffd}\u{fffd}")
|
|
|
|
name = double: -1.0
|
|
value = -1.0
|
|
|
|
name = double: 0.0
|
|
value = 0.0
|
|
|
|
name = double: 123.45
|
|
value = 123.45
|
|
|
|
name = double: nan
|
|
value = \n\
|
|
|
|
name = integer: -1
|
|
value = -1
|
|
|
|
name = integer: 0
|
|
value = 0
|
|
|
|
name = integer: 123
|
|
value = 123
|
|
|
|
name = integer: max
|
|
value = 9223372036854775807
|
|
|
|
name = integer: min
|
|
value = -9223372036854775808
|
|
|
|
name = null
|
|
value = \n\
|
|
|
|
name = text: ascii apostrophe
|
|
value = [']
|
|
|
|
name = text: ascii backslash
|
|
value = [\\]
|
|
|
|
name = text: ascii double quote
|
|
value = ["]
|
|
|
|
name = text: ascii line feed
|
|
value = [
|
|
]
|
|
|
|
name = text: ascii long
|
|
value = Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.
|
|
|
|
name = text: ascii short
|
|
value = Hello
|
|
|
|
name = text: ascii slash
|
|
value = [/]
|
|
|
|
name = text: ascii tab
|
|
value = [\t]
|
|
|
|
name = text: ascii url
|
|
value = https://github.com/groue/GRDB.swift
|
|
|
|
name = text: empty
|
|
value = \n\
|
|
|
|
name = text: utf8 short
|
|
value = 您好🙂
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_line_empty_results() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
do {
|
|
// Columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT NULL WHERE NULL", format: .line(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
do {
|
|
// No columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("CREATE TABLE t(a)", format: .line(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_line_duplicate_columns() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT 1 AS name, 'foo' AS name", format: .line(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
name = 1
|
|
name = foo
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_line_multiple_statements() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL(
|
|
"""
|
|
CREATE TABLE t(a, b);
|
|
INSERT INTO t VALUES (1, 'foo');
|
|
INSERT INTO t VALUES (2, 'bar');
|
|
SELECT * FROM t ORDER BY a;
|
|
SELECT b FROM t ORDER BY b;
|
|
SELECT NULL WHERE NULL;
|
|
""",
|
|
format: .line(),
|
|
to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
a = 1
|
|
b = foo
|
|
|
|
a = 2
|
|
b = bar
|
|
|
|
b = bar
|
|
|
|
b = foo
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_line_nullValue() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default null
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .line(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id = 1
|
|
teamId = FRA
|
|
name = Antoine Dupond
|
|
|
|
id = 2
|
|
teamId = ENG
|
|
name = Owen Farrell
|
|
|
|
id = 3
|
|
teamId = \n\
|
|
name = Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom null
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .line(nullValue: "NULL"), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id = 1
|
|
teamId = FRA
|
|
name = Antoine Dupond
|
|
|
|
id = 2
|
|
teamId = ENG
|
|
name = Owen Farrell
|
|
|
|
id = 3
|
|
teamId = NULL
|
|
name = Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - List
|
|
|
|
func test_list_value_formatting() throws {
|
|
try makeValuesDatabase().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM value ORDER BY name", format: .list(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
blob: ascii apostrophe|[']
|
|
blob: ascii double quote|["]
|
|
blob: ascii line feed|[
|
|
]
|
|
blob: ascii long|Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.
|
|
blob: ascii short|Hello
|
|
blob: ascii tab|[\t]
|
|
blob: binary short|�
|
|
blob: empty|
|
|
blob: utf8 short|您好🙂
|
|
blob: uuid|\("i\u{fffd}\u{fffd}\u{fffd}\u{fffd}\u{fffd}Gw\u{fffd}\u{11}\u{fffd}E\u{1d}\u{fffd}\u{fffd}\u{fffd}")
|
|
double: -1.0|-1.0
|
|
double: 0.0|0.0
|
|
double: 123.45|123.45
|
|
double: nan|
|
|
integer: -1|-1
|
|
integer: 0|0
|
|
integer: 123|123
|
|
integer: max|9223372036854775807
|
|
integer: min|-9223372036854775808
|
|
null|
|
|
text: ascii apostrophe|[']
|
|
text: ascii backslash|[\\]
|
|
text: ascii double quote|["]
|
|
text: ascii line feed|[
|
|
]
|
|
text: ascii long|Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.
|
|
text: ascii short|Hello
|
|
text: ascii slash|[/]
|
|
text: ascii tab|[\t]
|
|
text: ascii url|https://github.com/groue/GRDB.swift
|
|
text: empty|
|
|
text: utf8 short|您好🙂
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_list_empty_results() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
do {
|
|
// Columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT NULL WHERE NULL", format: .list(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
do {
|
|
// No columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("CREATE TABLE t(a)", format: .list(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_list_headers() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Headers on
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .list(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id|teamId|name
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Headers on, no result
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player WHERE 0", format: .list(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
do {
|
|
// Headers off
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .list(header: false), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Headers off, no result
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player WHERE 0", format: .list(header: false), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_list_duplicate_columns() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT 1 AS name, 'foo' AS name", format: .list(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
name|name
|
|
1|foo
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_list_multiple_statements() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL(
|
|
"""
|
|
CREATE TABLE t(a, b);
|
|
INSERT INTO t VALUES (1, 'foo');
|
|
INSERT INTO t VALUES (2, 'bar');
|
|
SELECT * FROM t ORDER BY a;
|
|
SELECT b FROM t ORDER BY b;
|
|
SELECT NULL WHERE NULL;
|
|
""",
|
|
format: .list(),
|
|
to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|foo
|
|
2|bar
|
|
bar
|
|
foo
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_list_separator() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default separator
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .list(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id|teamId|name
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom separator
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .list(header: true, separator: "---"), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
id---teamId---name
|
|
1---FRA---Antoine Dupond
|
|
2---ENG---Owen Farrell
|
|
3------Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_list_nullValue() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default null
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .list(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom null
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .list(nullValue: "NULL"), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3|NULL|Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - Quote
|
|
|
|
func test_quote_value_formatting() throws {
|
|
try makeValuesDatabase().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM value ORDER BY name", format: .quote(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
'blob: ascii apostrophe',X'5B275D'
|
|
'blob: ascii double quote',X'5B225D'
|
|
'blob: ascii line feed',X'5B0A5D'
|
|
'blob: ascii long',X'4C6F72656D20697073756D20646F6C6F722073697420616D65742C20636F6E73656374657475722061646970697363696E6720656C69742E204D6F726269207472697374697175652074656D706F7220636F6E64696D656E74756D2E2050656C6C656E746573717565207068617265747261206C61637573206E6F6E20616E746520736F6C6C696369747564696E20617563746F722E20566573746962756C756D2073697420616D6574206D61757269732076697461652075726E61206E6F6E206C75637475732E'
|
|
'blob: ascii short',X'48656C6C6F'
|
|
'blob: ascii tab',X'5B095D'
|
|
'blob: binary short',X'80'
|
|
'blob: empty',X''
|
|
'blob: utf8 short',X'E682A8E5A5BDF09F9982'
|
|
'blob: uuid',X'69BF8A9CD9F04777BD1193451D84CBCF'
|
|
'double: -1.0',-1.0
|
|
'double: 0.0',0.0
|
|
'double: 123.45',123.45
|
|
'double: nan',NULL
|
|
'integer: -1',-1
|
|
'integer: 0',0
|
|
'integer: 123',123
|
|
'integer: max',9223372036854775807
|
|
'integer: min',-9223372036854775808
|
|
'null',NULL
|
|
'text: ascii apostrophe','['']'
|
|
'text: ascii backslash','[\\]'
|
|
'text: ascii double quote','["]'
|
|
'text: ascii line feed','[
|
|
]'
|
|
'text: ascii long','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.'
|
|
'text: ascii short','Hello'
|
|
'text: ascii slash','[/]'
|
|
'text: ascii tab','[\t]'
|
|
'text: ascii url','https://github.com/groue/GRDB.swift'
|
|
'text: empty',''
|
|
'text: utf8 short','您好🙂'
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_quote_empty_results() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
do {
|
|
// Columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT NULL WHERE NULL", format: .quote(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
do {
|
|
// No columns
|
|
let stream = TestStream()
|
|
try db.dumpSQL("CREATE TABLE t(a)", format: .quote(), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_quote_headers() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Headers on
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .quote(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
'id','teamId','name'
|
|
1,'FRA','Antoine Dupond'
|
|
2,'ENG','Owen Farrell'
|
|
3,NULL,'Gwendal Roué'
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Headers on, no result
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player WHERE 0", format: .quote(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
do {
|
|
// Headers off
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .quote(header: false), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1,'FRA','Antoine Dupond'
|
|
2,'ENG','Owen Farrell'
|
|
3,NULL,'Gwendal Roué'
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Headers off, no result
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player WHERE 0", format: .quote(header: false), to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_quote_duplicate_columns() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT 1 AS name, 'foo' AS name", format: .quote(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
'name','name'
|
|
1,'foo'
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_quote_multiple_statements() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
let stream = TestStream()
|
|
try db.dumpSQL(
|
|
"""
|
|
CREATE TABLE t(a, b);
|
|
INSERT INTO t VALUES (1, 'foo');
|
|
INSERT INTO t VALUES (2, 'bar');
|
|
SELECT * FROM t ORDER BY a;
|
|
SELECT b FROM t ORDER BY b;
|
|
SELECT NULL WHERE NULL;
|
|
""",
|
|
format: .quote(),
|
|
to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1,'foo'
|
|
2,'bar'
|
|
'bar'
|
|
'foo'
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_quote_separator() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default separator
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .quote(header: true), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
'id','teamId','name'
|
|
1,'FRA','Antoine Dupond'
|
|
2,'ENG','Owen Farrell'
|
|
3,NULL,'Gwendal Roué'
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom separator
|
|
let stream = TestStream()
|
|
try db.dumpSQL("SELECT * FROM player ORDER BY id", format: .quote(header: true, separator: "---"), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
'id'---'teamId'---'name'
|
|
1---'FRA'---'Antoine Dupond'
|
|
2---'ENG'---'Owen Farrell'
|
|
3---NULL---'Gwendal Roué'
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - Dump error
|
|
|
|
func test_dumpError() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
do {
|
|
try db.dumpSQL(
|
|
"""
|
|
SELECT 'Hello';
|
|
Not sql;
|
|
""",
|
|
to: stream)
|
|
XCTFail("Expected error")
|
|
} catch {
|
|
XCTAssertEqual(stream.output, """
|
|
Hello
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - Request dump
|
|
|
|
func test_dumpRequest() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpRequest(Player.orderByPrimaryKey(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpRequest(Player.orderByPrimaryKey(), format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"id":1,"teamId":"FRA","name":"Antoine Dupond"},
|
|
{"id":2,"teamId":"ENG","name":"Owen Farrell"},
|
|
{"id":3,"teamId":null,"name":"Gwendal Roué"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_dumpRequest_association_to_one() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
let request = Player.orderByPrimaryKey().including(required: Player.team)
|
|
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpRequest(request, to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond|FRA|XV de France|blue
|
|
2|ENG|Owen Farrell|ENG|England Rugby|white
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpRequest(request, format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"id":1,"teamId":"FRA","name":"Antoine Dupond","id":"FRA","name":"XV de France","color":"blue"},
|
|
{"id":2,"teamId":"ENG","name":"Owen Farrell","id":"ENG","name":"England Rugby","color":"white"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_dumpRequest_association_to_many() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
let request = Team.orderByPrimaryKey().including(all: Team.players.orderByPrimaryKey())
|
|
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpRequest(request, to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
ENG|England Rugby|white
|
|
FRA|XV de France|blue
|
|
|
|
players
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpRequest(request, format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"id":"ENG","name":"England Rugby","color":"white"},
|
|
{"id":"FRA","name":"XV de France","color":"blue"}]
|
|
|
|
players
|
|
[{"id":1,"teamId":"FRA","name":"Antoine Dupond"},
|
|
{"id":2,"teamId":"ENG","name":"Owen Farrell"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - Table dump
|
|
|
|
func test_dumpTables_zero() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpTables([], to: stream)
|
|
XCTAssertEqual(stream.output, "")
|
|
}
|
|
}
|
|
|
|
func test_dumpTables_single_table() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["player"], to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["player"], format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"id":1,"teamId":"FRA","name":"Antoine Dupond"},
|
|
{"id":2,"teamId":"ENG","name":"Owen Farrell"},
|
|
{"id":3,"teamId":null,"name":"Gwendal Roué"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_dumpTables_single_view() throws {
|
|
try makeRugbyDatabase().write { db in
|
|
try db.create(view: "playerName", as: Player
|
|
.orderByPrimaryKey()
|
|
.select(Column("name")))
|
|
|
|
do {
|
|
// Default order: use the view ordering
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["playerName"], to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
Antoine Dupond
|
|
Owen Farrell
|
|
Gwendal Roué
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["playerName"], format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"name":"Antoine Dupond"},
|
|
{"name":"Owen Farrell"},
|
|
{"name":"Gwendal Roué"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
do {
|
|
// Stable order
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["playerName"], stableOrder: true, to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
Antoine Dupond
|
|
Gwendal Roué
|
|
Owen Farrell
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["playerName"], format: .json(), stableOrder: true, to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
[{"name":"Antoine Dupond"},
|
|
{"name":"Gwendal Roué"},
|
|
{"name":"Owen Farrell"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_dumpTables_multiple() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["player", "team"], to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
player
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
team
|
|
ENG|England Rugby|white
|
|
FRA|XV de France|blue
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpTables(["team", "player"], format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
team
|
|
[{"id":"ENG","name":"England Rugby","color":"white"},
|
|
{"id":"FRA","name":"XV de France","color":"blue"}]
|
|
|
|
player
|
|
[{"id":1,"teamId":"FRA","name":"Antoine Dupond"},
|
|
{"id":2,"teamId":"ENG","name":"Owen Farrell"},
|
|
{"id":3,"teamId":null,"name":"Gwendal Roué"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
// MARK: - Database schema dump
|
|
|
|
func test_dumpSchema() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSchema(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE "player" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "teamId" TEXT REFERENCES "team"("id"), "name" TEXT NOT NULL);
|
|
CREATE INDEX "player_on_teamId" ON "player"("teamId");
|
|
CREATE TABLE "team" ("id" TEXT PRIMARY KEY NOT NULL, "name" TEXT NOT NULL, "color" TEXT NOT NULL);
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpSchema_empty_database() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSchema(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpSchema_empty_tables() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
try db.execute(literal: """
|
|
CREATE TABLE blue(name);
|
|
CREATE TABLE red(name);
|
|
CREATE TABLE yellow(name);
|
|
INSERT INTO red VALUES ('vermillon')
|
|
""")
|
|
let stream = TestStream()
|
|
try db.dumpSchema(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE blue(name);
|
|
CREATE TABLE red(name);
|
|
CREATE TABLE yellow(name);
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpSchema_sqlite_master_ordering() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
try db.execute(literal: """
|
|
CREATE TABLE blue(name);
|
|
CREATE TABLE RED(name);
|
|
CREATE TABLE yellow(name);
|
|
CREATE INDEX index_blue1 ON blue(name);
|
|
CREATE INDEX INDEX_blue2 ON blue(name);
|
|
CREATE INDEX indexRed1 ON RED(name);
|
|
CREATE INDEX INDEXRed2 ON RED(name);
|
|
CREATE VIEW colors1 AS SELECT name FROM blue;
|
|
CREATE VIEW COLORS2 AS SELECT name FROM blue UNION SELECT name FROM yellow;
|
|
CREATE TRIGGER update_blue UPDATE OF name ON blue
|
|
BEGIN
|
|
DELETE FROM RED;
|
|
END;
|
|
CREATE TRIGGER update_RED UPDATE OF name ON RED
|
|
BEGIN
|
|
DELETE FROM yellow;
|
|
END;
|
|
""")
|
|
let stream = TestStream()
|
|
try db.dumpSchema(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE blue(name);
|
|
CREATE INDEX index_blue1 ON blue(name);
|
|
CREATE INDEX INDEX_blue2 ON blue(name);
|
|
CREATE TRIGGER update_blue UPDATE OF name ON blue
|
|
BEGIN
|
|
DELETE FROM RED;
|
|
END;
|
|
CREATE VIEW colors1 AS SELECT name FROM blue;
|
|
CREATE VIEW COLORS2 AS SELECT name FROM blue UNION SELECT name FROM yellow;
|
|
CREATE TABLE RED(name);
|
|
CREATE INDEX indexRed1 ON RED(name);
|
|
CREATE INDEX INDEXRed2 ON RED(name);
|
|
CREATE TRIGGER update_RED UPDATE OF name ON RED
|
|
BEGIN
|
|
DELETE FROM yellow;
|
|
END;
|
|
CREATE TABLE yellow(name);
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpSchema_ignores_shadow_tables() throws {
|
|
guard sqlite3_libversion_number() >= 3037000 else {
|
|
throw XCTSkip("Can't detect shadow tables")
|
|
}
|
|
|
|
try makeDatabaseQueue().write { db in
|
|
try db.create(table: "document") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.column("body")
|
|
}
|
|
|
|
try db.execute(sql: "INSERT INTO document VALUES (1, 'Hello world!')")
|
|
|
|
try db.create(virtualTable: "document_ft", using: FTS4()) { t in
|
|
t.synchronize(withTable: "document")
|
|
t.column("body")
|
|
}
|
|
|
|
let stream = TestStream()
|
|
try db.dumpSchema(to: stream)
|
|
print(stream.output)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE "document" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "body");
|
|
CREATE TRIGGER "__document_ft_ai" AFTER INSERT ON "document" BEGIN
|
|
INSERT INTO "document_ft"("docid", "body") VALUES(new."id", new."body");
|
|
END;
|
|
CREATE TRIGGER "__document_ft_au" AFTER UPDATE ON "document" BEGIN
|
|
INSERT INTO "document_ft"("docid", "body") VALUES(new."id", new."body");
|
|
END;
|
|
CREATE TRIGGER "__document_ft_bd" BEFORE DELETE ON "document" BEGIN
|
|
DELETE FROM "document_ft" WHERE docid=old."id";
|
|
END;
|
|
CREATE TRIGGER "__document_ft_bu" BEFORE UPDATE ON "document" BEGIN
|
|
DELETE FROM "document_ft" WHERE docid=old."id";
|
|
END;
|
|
CREATE VIRTUAL TABLE "document_ft" USING fts4(body, content="document");
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpSchema_ignores_GRDB_internal_tables() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
var migrator = DatabaseMigrator()
|
|
migrator.registerMigration("v1") { db in
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
}
|
|
try migrator.migrate(dbQueue)
|
|
|
|
try dbQueue.read { db in
|
|
let stream = TestStream()
|
|
try db.dumpSchema(to: stream)
|
|
print(stream.output)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE "player" ("id" INTEGER PRIMARY KEY AUTOINCREMENT);
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
// MARK: - Database content dump
|
|
|
|
func test_dumpContent() throws {
|
|
try makeRugbyDatabase().read { db in
|
|
do {
|
|
// Default format
|
|
let stream = TestStream()
|
|
try db.dumpContent(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE "player" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "teamId" TEXT REFERENCES "team"("id"), "name" TEXT NOT NULL);
|
|
CREATE INDEX "player_on_teamId" ON "player"("teamId");
|
|
CREATE TABLE "team" ("id" TEXT PRIMARY KEY NOT NULL, "name" TEXT NOT NULL, "color" TEXT NOT NULL);
|
|
|
|
player
|
|
1|FRA|Antoine Dupond
|
|
2|ENG|Owen Farrell
|
|
3||Gwendal Roué
|
|
|
|
team
|
|
ENG|England Rugby|white
|
|
FRA|XV de France|blue
|
|
|
|
""")
|
|
}
|
|
do {
|
|
// Custom format
|
|
let stream = TestStream()
|
|
try db.dumpContent(format: .json(), to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE "player" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "teamId" TEXT REFERENCES "team"("id"), "name" TEXT NOT NULL);
|
|
CREATE INDEX "player_on_teamId" ON "player"("teamId");
|
|
CREATE TABLE "team" ("id" TEXT PRIMARY KEY NOT NULL, "name" TEXT NOT NULL, "color" TEXT NOT NULL);
|
|
|
|
player
|
|
[{"id":1,"teamId":"FRA","name":"Antoine Dupond"},
|
|
{"id":2,"teamId":"ENG","name":"Owen Farrell"},
|
|
{"id":3,"teamId":null,"name":"Gwendal Roué"}]
|
|
|
|
team
|
|
[{"id":"ENG","name":"England Rugby","color":"white"},
|
|
{"id":"FRA","name":"XV de France","color":"blue"}]
|
|
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func test_dumpContent_empty_database() throws {
|
|
try makeDatabaseQueue().read { db in
|
|
let stream = TestStream()
|
|
try db.dumpContent(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpContent_empty_tables() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
try db.execute(literal: """
|
|
CREATE TABLE blue(name);
|
|
CREATE TABLE red(name);
|
|
CREATE TABLE yellow(name);
|
|
INSERT INTO red VALUES ('vermillon')
|
|
""")
|
|
let stream = TestStream()
|
|
try db.dumpContent(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE blue(name);
|
|
CREATE TABLE red(name);
|
|
CREATE TABLE yellow(name);
|
|
|
|
blue
|
|
|
|
red
|
|
vermillon
|
|
|
|
yellow
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpContent_sqlite_master_ordering() throws {
|
|
try makeDatabaseQueue().write { db in
|
|
try db.execute(literal: """
|
|
CREATE TABLE blue(name);
|
|
CREATE TABLE RED(name);
|
|
CREATE TABLE yellow(name);
|
|
CREATE INDEX index_blue1 ON blue(name);
|
|
CREATE INDEX INDEX_blue2 ON blue(name);
|
|
CREATE INDEX indexRed1 ON RED(name);
|
|
CREATE INDEX INDEXRed2 ON RED(name);
|
|
CREATE VIEW colors1 AS SELECT name FROM blue;
|
|
CREATE VIEW COLORS2 AS SELECT name FROM blue UNION SELECT name FROM yellow;
|
|
CREATE TRIGGER update_blue UPDATE OF name ON blue
|
|
BEGIN
|
|
DELETE FROM RED;
|
|
END;
|
|
CREATE TRIGGER update_RED UPDATE OF name ON RED
|
|
BEGIN
|
|
DELETE FROM yellow;
|
|
END;
|
|
""")
|
|
let stream = TestStream()
|
|
try db.dumpContent(to: stream)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE blue(name);
|
|
CREATE INDEX index_blue1 ON blue(name);
|
|
CREATE INDEX INDEX_blue2 ON blue(name);
|
|
CREATE TRIGGER update_blue UPDATE OF name ON blue
|
|
BEGIN
|
|
DELETE FROM RED;
|
|
END;
|
|
CREATE VIEW colors1 AS SELECT name FROM blue;
|
|
CREATE VIEW COLORS2 AS SELECT name FROM blue UNION SELECT name FROM yellow;
|
|
CREATE TABLE RED(name);
|
|
CREATE INDEX indexRed1 ON RED(name);
|
|
CREATE INDEX INDEXRed2 ON RED(name);
|
|
CREATE TRIGGER update_RED UPDATE OF name ON RED
|
|
BEGIN
|
|
DELETE FROM yellow;
|
|
END;
|
|
CREATE TABLE yellow(name);
|
|
|
|
blue
|
|
|
|
RED
|
|
|
|
yellow
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpContent_ignores_shadow_tables() throws {
|
|
guard sqlite3_libversion_number() >= 3037000 else {
|
|
throw XCTSkip("Can't detect shadow tables")
|
|
}
|
|
|
|
try makeDatabaseQueue().write { db in
|
|
try db.create(table: "document") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.column("body")
|
|
}
|
|
|
|
try db.execute(sql: "INSERT INTO document VALUES (1, 'Hello world!')")
|
|
|
|
try db.create(virtualTable: "document_ft", using: FTS4()) { t in
|
|
t.synchronize(withTable: "document")
|
|
t.column("body")
|
|
}
|
|
|
|
let stream = TestStream()
|
|
try db.dumpContent(to: stream)
|
|
print(stream.output)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE "document" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "body");
|
|
CREATE TRIGGER "__document_ft_ai" AFTER INSERT ON "document" BEGIN
|
|
INSERT INTO "document_ft"("docid", "body") VALUES(new."id", new."body");
|
|
END;
|
|
CREATE TRIGGER "__document_ft_au" AFTER UPDATE ON "document" BEGIN
|
|
INSERT INTO "document_ft"("docid", "body") VALUES(new."id", new."body");
|
|
END;
|
|
CREATE TRIGGER "__document_ft_bd" BEFORE DELETE ON "document" BEGIN
|
|
DELETE FROM "document_ft" WHERE docid=old."id";
|
|
END;
|
|
CREATE TRIGGER "__document_ft_bu" BEFORE UPDATE ON "document" BEGIN
|
|
DELETE FROM "document_ft" WHERE docid=old."id";
|
|
END;
|
|
CREATE VIRTUAL TABLE "document_ft" USING fts4(body, content="document");
|
|
|
|
document
|
|
1|Hello world!
|
|
|
|
document_ft
|
|
Hello world!
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
func test_dumpContent_ignores_GRDB_internal_tables() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
var migrator = DatabaseMigrator()
|
|
migrator.registerMigration("v1") { db in
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
}
|
|
try migrator.migrate(dbQueue)
|
|
|
|
try dbQueue.read { db in
|
|
let stream = TestStream()
|
|
try db.dumpContent(to: stream)
|
|
print(stream.output)
|
|
XCTAssertEqual(stream.output, """
|
|
sqlite_master
|
|
CREATE TABLE "player" ("id" INTEGER PRIMARY KEY AUTOINCREMENT);
|
|
|
|
player
|
|
|
|
""")
|
|
}
|
|
}
|
|
|
|
// MARK: - Support Databases
|
|
|
|
private func makeValuesDatabase() throws -> DatabaseQueue {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.execute(literal: """
|
|
CREATE TABLE value(name, value);
|
|
|
|
INSERT INTO value VALUES ('blob: ascii long', CAST('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.' AS BLOB));
|
|
INSERT INTO value VALUES ('blob: ascii short', CAST('Hello' AS BLOB));
|
|
INSERT INTO value VALUES ('blob: ascii tab', CAST('['||char(9)||']' AS BLOB));
|
|
INSERT INTO value VALUES ('blob: ascii line feed', CAST('['||char(10)||']' AS BLOB));
|
|
INSERT INTO value VALUES ('blob: ascii apostrophe', CAST('['']' AS BLOB));
|
|
INSERT INTO value VALUES ('blob: ascii double quote', CAST('["]' AS BLOB));
|
|
INSERT INTO value VALUES ('blob: binary short', X'80');
|
|
INSERT INTO value VALUES ('blob: empty', x'');
|
|
INSERT INTO value VALUES ('blob: utf8 short', CAST('您好🙂' AS BLOB));
|
|
INSERT INTO value VALUES ('blob: uuid', x'69BF8A9CD9F04777BD1193451D84CBCF');
|
|
|
|
INSERT INTO value VALUES ('double: -1.0', -1.0);
|
|
INSERT INTO value VALUES ('double: 0.0', 0.0);
|
|
INSERT INTO value VALUES ('double: 123.45', 123.45);
|
|
INSERT INTO value VALUES ('double: nan', \(0.0 / 0));
|
|
|
|
INSERT INTO value VALUES ('integer: 0', 0);
|
|
INSERT INTO value VALUES ('integer: -1', -1);
|
|
INSERT INTO value VALUES ('integer: 123', 123);
|
|
INSERT INTO value VALUES ('integer: max', 9223372036854775807);
|
|
INSERT INTO value VALUES ('integer: min', -9223372036854775808);
|
|
|
|
INSERT INTO value VALUES ('null', NULL);
|
|
|
|
INSERT INTO value VALUES ('text: empty', '');
|
|
INSERT INTO value VALUES ('text: ascii apostrophe', '['']');
|
|
INSERT INTO value VALUES ('text: ascii backslash', '[\\]');
|
|
INSERT INTO value VALUES ('text: ascii double quote', '["]');
|
|
INSERT INTO value VALUES ('text: ascii long', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi tristique tempor condimentum. Pellentesque pharetra lacus non ante sollicitudin auctor. Vestibulum sit amet mauris vitae urna non luctus.');
|
|
INSERT INTO value VALUES ('text: ascii line feed', '['||char(10)||']');
|
|
INSERT INTO value VALUES ('text: ascii short', 'Hello');
|
|
INSERT INTO value VALUES ('text: ascii slash', '[/]');
|
|
INSERT INTO value VALUES ('text: ascii tab', '['||char(9)||']');
|
|
INSERT INTO value VALUES ('text: ascii url', 'https://github.com/groue/GRDB.swift');
|
|
INSERT INTO value VALUES ('text: utf8 short', '您好🙂');
|
|
""")
|
|
}
|
|
return dbQueue
|
|
}
|
|
|
|
private func makeRugbyDatabase() throws -> DatabaseQueue {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.write { db in
|
|
try db.create(table: "team") { t in
|
|
t.primaryKey("id", .text)
|
|
t.column("name", .text).notNull()
|
|
t.column("color", .text).notNull()
|
|
}
|
|
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("team")
|
|
t.column("name", .text).notNull()
|
|
}
|
|
|
|
let england = Team(id: "ENG", name: "England Rugby", color: "white")
|
|
let france = Team(id: "FRA", name: "XV de France", color: "blue")
|
|
|
|
try england.insert(db)
|
|
try france.insert(db)
|
|
|
|
_ = try Player(name: "Antoine Dupond", teamId: france.id).inserted(db)
|
|
_ = try Player(name: "Owen Farrell", teamId: england.id).inserted(db)
|
|
_ = try Player(name: "Gwendal Roué", teamId: nil).inserted(db)
|
|
}
|
|
return dbQueue
|
|
}
|
|
}
|