GRDB.swift
6.29.3
애플리케이션 개발에 중점을 둔 SQLITE 데이터베이스 용 툴킷
2015 년부터 자랑스럽게 커뮤니티에 봉사합니다
최신 릴리스 : 2024 년 10 월 13 일 • 버전 7.0.0-Beta.6 • Changelog • GRDB 6에서 GRDB로 마이그레이션
요구 사항 : iOS 13.0+ / MacOS 10.15+ / TVOS 13.0+ / WATKOS 7.0+ • SQLITE 3.20.0+ • SWIFT 6+ / XCODE 16+
연락하다 :
이 라이브러리를 사용하여 응용 프로그램의 영구 데이터를 SQLITE 데이터베이스에 저장하십시오. 일반적인 요구를 해결하는 내장 도구가 제공됩니다.
SQL 생성
지속성 및 페치 방법으로 애플리케이션 모델을 향상시켜 원하지 않을 때 SQL 및 원시 데이터베이스 행을 처리 할 필요가 없습니다.
데이터베이스 관찰
데이터베이스 값이 수정되면 알림을 가져옵니다.
강력한 동시성
다중 스레드 애플리케이션은 동시 읽기 및 쓰기를 지원하는 WAL 데이터베이스를 포함하여 데이터베이스를 효율적으로 사용할 수 있습니다.
마이그레이션
새 버전의 애플리케이션을 배송 할 때 데이터베이스의 스키마를 발전시킵니다.
sqlite 기술을 활용하십시오
모든 개발자가 고급 SQLITE 기능이 필요한 것은 아닙니다. 그러나 할 때 GRDB는 원하는만큼 날카 롭습니다. SQL 및 SQLITE 기술과 함께 오거나 갈 때 새로운 기술을 배우십시오!
사용법 • 문서 • 설치 • FAQ
import GRDB
// 1. Open a database connection
let dbQueue = try DatabaseQueue ( path : " /path/to/database.sqlite " )
// 2. Define the database schema
try dbQueue . write { db in
try db . create ( table : " player " ) { t in
t . primaryKey ( " id " , . text )
t . column ( " name " , . text ) . notNull ( )
t . column ( " score " , . integer ) . notNull ( )
}
}
// 3. Define a record type
struct Player : Codable , FetchableRecord , PersistableRecord {
var id : String
var name : String
var score : Int
}
// 4. Write and read in the database
try dbQueue . write { db in
try Player ( id : " 1 " , name : " Arthur " , score : 100 ) . insert ( db )
try Player ( id : " 2 " , name : " Barbara " , score : 1000 ) . insert ( db )
}
let players : [ Player ] = try dbQueue . read { db in
try Player . fetchAll ( db )
} try dbQueue . write { db in
try db . execute ( sql : """
CREATE TABLE place (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
favorite BOOLEAN NOT NULL DEFAULT 0,
latitude DOUBLE NOT NULL,
longitude DOUBLE NOT NULL)
""" )
try db . execute ( sql : """
INSERT INTO place (title, favorite, latitude, longitude)
VALUES (?, ?, ?, ?)
""" , arguments : [ " Paris " , true , 48.85341 , 2.3488 ] )
let parisId = db . lastInsertedRowID
// Avoid SQL injection with SQL interpolation
try db . execute ( literal : """
INSERT INTO place (title, favorite, latitude, longitude)
VALUES ( ( " King's Cross " ) , ( true ) , ( 51.52151 ) , ( - 0.12763 ) )
""" )
}업데이트 실행을 참조하십시오
try dbQueue . read { db in
// Fetch database rows
let rows = try Row . fetchCursor ( db , sql : " SELECT * FROM place " )
while let row = try rows . next ( ) {
let title : String = row [ " title " ]
let isFavorite : Bool = row [ " favorite " ]
let coordinate = CLLocationCoordinate2D (
latitude : row [ " latitude " ] ,
longitude : row [ " longitude " ] )
}
// Fetch values
let placeCount = try Int . fetchOne ( db , sql : " SELECT COUNT(*) FROM place " ) ! // Int
let placeTitles = try String . fetchAll ( db , sql : " SELECT title FROM place " ) // [String]
}
let placeCount = try dbQueue . read { db in
try Int . fetchOne ( db , sql : " SELECT COUNT(*) FROM place " ) !
}페치 쿼리를 참조하십시오
struct Place {
var id : Int64 ?
var title : String
var isFavorite : Bool
var coordinate : CLLocationCoordinate2D
}
// snip: turn Place into a "record" by adopting the protocols that
// provide fetching and persistence methods.
try dbQueue . write { db in
// Create database table
try db . create ( table : " place " ) { t in
t . autoIncrementedPrimaryKey ( " id " )
t . column ( " title " , . text ) . notNull ( )
t . column ( " favorite " , . boolean ) . notNull ( ) . defaults ( to : false )
t . column ( " longitude " , . double ) . notNull ( )
t . column ( " latitude " , . double ) . notNull ( )
}
var berlin = Place (
id : nil ,
title : " Berlin " ,
isFavorite : false ,
coordinate : CLLocationCoordinate2D ( latitude : 52.52437 , longitude : 13.41053 ) )
try berlin . insert ( db )
berlin . id // some value
berlin . isFavorite = true
try berlin . update ( db )
}기록을 참조하십시오
try dbQueue . read { db in
// Place
let paris = try Place . find ( db , id : 1 )
// Place?
let berlin = try Place . filter ( Column ( " title " ) == " Berlin " ) . fetchOne ( db )
// [Place]
let favoritePlaces = try Place
. filter ( Column ( " favorite " ) == true )
. order ( Column ( " title " ) )
. fetchAll ( db )
// Int
let favoriteCount = try Place . filter ( Column ( " favorite " ) ) . fetchCount ( db )
// SQL is always welcome
let places = try Place . fetchAll ( db , sql : " SELECT * FROM place " )
}쿼리 인터페이스를 참조하십시오
// Define the observed value
let observation = ValueObservation . tracking { db in
try Place . fetchAll ( db )
}
// Start observation
let cancellable = observation . start (
in : dbQueue ,
onError : { error in ... } ,
onChange : { ( places : [ Place ] ) in print ( " Fresh places: ( places ) " ) } )Combine 및 RXSwift에 대한 기성품 지원 :
// Combine
let cancellable = observation . publisher ( in : dbQueue ) . sink (
receiveCompletion : { completion in ... } ,
receiveValue : { ( places : [ Place ] ) in print ( " Fresh places: ( places ) " ) } )
// RxSwift
let disposable = observation . rx . observe ( in : dbQueue ) . subscribe (
onNext : { ( places : [ Place ] ) in print ( " Fresh places: ( places ) " ) } ,
onError : { error in ... } )데이터베이스 관찰, 지원, RXGRDB를 참조하십시오.
GRDB는 SQLITE 위에서 실행합니다 . SQLITE FAQ에 익숙해 져야합니다. 일반적이고 자세한 정보는 SQLite 문서로 이동하십시오.
FAQ
샘플 코드
아래 설치 절차는 GRDB가 대상 운영 체제와 함께 제공되는 SQLITE 버전을 사용하도록합니다.
sqlcipher를 사용한 GRDB의 설치 절차는 암호화를 참조하십시오.
SQLITE의 사용자 정의 된 빌드를 사용하여 GRDB의 설치 절차에 대한 사용자 정의 SQLITE 빌드를 참조하십시오.
Swift Package Manager는 Swift 코드의 배포를 자동화합니다. SPM과 함께 grdb를 사용하려면 https://github.com/groue/GRDB.swift.git 에 종속성을 추가하십시오.
GRDB는 GRDB 와 GRDB-dynamic 두 라이브러리를 제공합니다. 하나만 선택하십시오. 의심스러운 경우 GRDB 선호하십시오. GRDB-dynamic 라이브러리는 앱 내의 여러 대상과 연결하려는 경우 유용하고 공유 된 역동적 인 프레임 워크에만 연결하려는 경우 유용합니다. 자세한 내용은 신속한 패키지를 동적으로 연결하는 방법을 참조하십시오.
참고 : Linux는 현재 지원되지 않습니다.
Cocoapods는 Xcode 프로젝트의 종속성 관리자입니다. Cocoapods (버전 1.2 이상)와 함께 grdb를 사용하려면 Podfile 에 지정하십시오.
pod 'GRDB.swift'GRDB는 프레임 워크 또는 정적 라이브러리로 설치할 수 있습니다.
Cocoapods 설치에 대한 중요한 참고 사항
Cocoapods의 문제로 인해 현재 GRDB의 새로운 버전을 Cocoapod에 배포 할 수 없습니다. Cocoapods에서 사용 가능한 마지막 버전은 6.24.1입니다. Cocoapods를 사용하여 이후 버전의 GRDB를 설치하려면 다음 해결 방법 중 하나를 사용하십시오.
GRDB6 지점에 의존합니다. Cocoapods가 새로운 GRDB 버전을 공개 할 경우 pod 'GRDB.swift', '~> 6.0' 이 일반적으로 수행 할 것과 다소 동일합니다.
# Can't use semantic versioning due to https://github.com/CocoaPods/CocoaPods/issues/11839
pod 'GRDB.swift' , git : 'https://github.com/groue/GRDB.swift.git' , branch : 'GRDB6'특정 버전에 명시 적으로 의존합니다 (사용하려는 버전으로 태그를 바꾸십시오).
# Can't use semantic versioning due to https://github.com/CocoaPods/CocoaPods/issues/11839
# Replace the tag with the tag that you want to use.
pod 'GRDB.swift' , git : 'https://github.com/groue/GRDB.swift.git' , tag : 'v6.29.0' 카르타고는 지원되지 않습니다 . 이 결정에 대한 일부 맥락은 #433을 참조하십시오.
GRDB 사본을 다운로드하거나 저장소를 복제하고 최신 태그 버전을 체크 아웃하십시오.
자신의 프로젝트에 GRDB.xcodeproj 프로젝트를 포함시킵니다.
응용 프로그램 대상의 빌드 단계 탭 (WatchOS의 확장 대상)의 대상 종속성 섹션에서 GRDB 대상을 추가하십시오.
응용 프로그램 대상의 일반 탭의 임베디드 바이너리 섹션 (WatchOS의 확장 대상)에 GRDB.framework 를 추가하십시오.
GRDB는 SQLITE 데이터베이스에 액세스하기위한 두 가지 클래스를 제공합니다 : DatabaseQueue 및 DatabasePool :
import GRDB
// Pick one:
let dbQueue = try DatabaseQueue ( path : " /path/to/database.sqlite " )
let dbPool = try DatabasePool ( path : " /path/to/database.sqlite " )차이점은 다음과 같습니다.
확실하지 않은 경우 DatabaseQueue 선택하십시오. 나중에 항상 DatabasePool 로 전환 할 수 있습니다.
연결을 열 때 자세한 정보 및 팁은 데이터베이스 연결을 참조하십시오.
문서 의이 섹션에서는 SQL에 대해 이야기합니다. SQL이 차 한잔이 아닌 경우 쿼리 인터페이스로 이동하십시오.
DatabaseValueConvertible : 사용자 정의 값 유형에 대한 프로토콜고급 주제 :
데이터베이스 연결이 부여되면 execute(sql:arguments:) 메서드는 CREATE TABLE , INSERT , DELETE , ALTER 등과 같은 데이터베이스 행을 반환하지 않는 SQL 문을 실행합니다.
예를 들어:
try dbQueue . write { db in
try db . execute ( sql : """
CREATE TABLE player (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
score INT)
""" )
try db . execute (
sql : " INSERT INTO player (name, score) VALUES (?, ?) " ,
arguments : [ " Barbara " , 1000 ] )
try db . execute (
sql : " UPDATE player SET score = :score WHERE id = :id " ,
arguments : [ " score " : 1000 , " id " : 1 ] )
}
} ? SQL 쿼리의 :score 진술 인수 입니다. 위의 예에서와 같이 배열 또는 사전으로 인수를 전달합니다. 지원되는 인수 유형 (bool, int, string, date, swift enums 등) 및 sqlite 인수에 대한 자세한 문서에 대한 자세한 내용 StatementArguments 값을 참조하십시오.
아래 예제에서와 같이 execute(literal:) 사용하여 쿼리 인수를 SQL 쿼리에 바로 포함시킬 수도 있습니다. 자세한 내용은 SQL 보간을 참조하십시오.
try dbQueue . write { db in
let name = " O'Brien "
let score = 550
try db . execute ( literal : """
INSERT INTO player (name, score) VALUES ( ( name ) , ( score ) )
""" )
}원시 SQL 문자열에 값을 직접 포함시키지 마십시오 . 자세한 내용은 SQL 주입 방지를 참조하십시오.
// WRONG: don't embed values in raw SQL strings
let id = 123
let name = textField . text
try db . execute (
sql : " UPDATE player SET name = ' ( name ) ' WHERE id = ( id ) " )
// CORRECT: use arguments dictionary
try db . execute (
sql : " UPDATE player SET name = :name WHERE id = :id " ,
arguments : [ " name " : name , " id " : id ] )
// CORRECT: use arguments array
try db . execute (
sql : " UPDATE player SET name = ? WHERE id = ? " ,
arguments : [ name , id ] )
// CORRECT: use SQL Interpolation
try db . execute (
literal : " UPDATE player SET name = ( name ) WHERE id = ( id ) " )세미콜론과 여러 진술에 참여하십시오 .
try db . execute ( sql : """
INSERT INTO player (name, score) VALUES (?, ?);
INSERT INTO player (name, score) VALUES (?, ?);
""" , arguments : [ " Arthur " , 750 , " Barbara " , 1000 ] )
try db . execute ( literal : """
INSERT INTO player (name, score) VALUES ( ( " Arthur " ) , ( 750 ) );
INSERT INTO player (name, score) VALUES ( ( " Barbara " ) , ( 1000 ) );
""" ) 단일 명령문이 실행되도록하려면 준비된 Statement 사용하십시오.
삽입 문 후에는 삽입 된 행의 행 ID를 lastInsertedRowID 묶은 RowID로 얻을 수 있습니다.
try db . execute (
sql : " INSERT INTO player (name, score) VALUES (?, ?) " ,
arguments : [ " Arthur " , 1000 ] )
let playerId = db . lastInsertedRowID전형적인 지속성 방법을 제공하는 레코드를 놓치지 마십시오.
var player = Player ( name : " Arthur " , score : 1000 )
try player . insert ( db )
let playerId = player . id데이터베이스 연결을 사용하면 데이터베이스 행, 일반 값 및 사용자 정의 모델 일명 "레코드"를 가져올 수 있습니다.
행은 SQL 쿼리의 원시 결과입니다.
try dbQueue . read { db in
if let row = try Row . fetchOne ( db , sql : " SELECT * FROM wine WHERE id = ? " , arguments : [ 1 ] ) {
let name : String = row [ " name " ]
let color : Color = row [ " color " ]
print ( name , color )
}
}값 은 bool, int, string, date, swift enum 등입니다. 행 열에 저장됩니다.
try dbQueue . read { db in
let urls = try URL . fetchCursor ( db , sql : " SELECT url FROM wine " )
while let url = try urls . next ( ) {
print ( url )
}
}레코드는 행에서 자신을 초기화 할 수있는 응용 프로그램 객체입니다.
let wines = try dbQueue . read { db in
try Wine . fetchAll ( db , sql : " SELECT * FROM wine " )
}GRDB 전체에 걸쳐 모든 페치 가능한 유형의 커서 , 어레이 , 세트 또는 단일 값 (데이터베이스 행, 간단한 값 또는 사용자 정의 레코드)을 항상 가져올 수 있습니다.
try Row . fetchCursor ( ... ) // A Cursor of Row
try Row . fetchAll ( ... ) // [Row]
try Row . fetchSet ( ... ) // Set<Row>
try Row . fetchOne ( ... ) // Row? fetchCursor 페치 된 값을 통해 커서를 반환합니다.
let rows = try Row . fetchCursor ( db , sql : " SELECT ... " ) // A Cursor of Row fetchAll 배열을 반환합니다.
let players = try Player . fetchAll ( db , sql : " SELECT ... " ) // [Player] fetchSet 세트를 반환합니다.
let names = try String . fetchSet ( db , sql : " SELECT ... " ) // Set<String> fetchOne 단일 선택적 값을 반환하고 단일 데이터베이스 행 (있는 경우)을 소비합니다.
let count = try Int . fetchOne ( db , sql : " SELECT COUNT(*) ... " ) // Int?이러한 모든 페치 방법에는 단일 SQL 문이 포함 된 SQL 문자열이 필요합니다. 세미콜론과 합류 한 여러 문장에서 가져 오려면 SQL 문자열에서 발견 된 여러 준비된 진술을 반복하십시오.
Cursor
데이터베이스에서 여러 행을 소비 할 때마다 배열, 세트 또는 커서를 가져올 수 있습니다 .
fetchAll() 및 fetchSet() 메소드는 일반 스위프트 배열 및 세트를 반환하여 다른 모든 배열 및 세트와 마찬가지로 반복합니다.
try dbQueue . read { db in
// [Player]
let players = try Player . fetchAll ( db , sql : " SELECT ... " )
for player in players {
// use player
}
} 배열 및 세트와 달리 fetchCursor() 에 의해 반환 된 커서는 결과 단계 후 단계를로드합니다.
try dbQueue . read { db in
// Cursor of Player
let players = try Player . fetchCursor ( db , sql : " SELECT ... " )
while let player = try players . next ( ) {
// use player
}
}커서는 스레드에서 사용할 수 없습니다 . 생성 된 디스플레이 큐에서 커서를 소비해야합니다. 특히 데이터베이스 액세스 방법에서 커서를 추출하지 마십시오.
// Wrong
let cursor = try dbQueue . read { db in
try Player . fetchCursor ( db , ... )
}
while let player = try cursor . next ( ) { ... }반대로, 배열 및 세트는 모든 스레드에서 소비 될 수 있습니다.
// OK
let array = try dbQueue . read { db in
try Player . fetchAll ( db , ... )
}
for player in array { ... }커서는 한 번만 반복 할 수 있습니다. 배열 및 세트는 여러 번 반복 할 수 있습니다.
커서는 데이터베이스를 반복하여 게으른 방식으로 결과를 얻지 못하고 많은 메모리를 소비하지 않습니다. 배열 및 세트에는 데이터베이스 값 사본이 포함되어 있으며 많은 결과가있을 때 많은 메모리를 취할 수 있습니다.
커서는 데이터베이스 값을 복사하기 위해 시간을 내어야하는 배열 및 세트와 달리 SQLITE에 직접 액세스하여 부여됩니다 . 추가 성능을 돌보는 경우 커서를 선호 할 수 있습니다.
커서는 신속한 컬렉션을 공급할 수 있습니다.
배열이나 세트를 원할 때 대부분의 시간은 fetchAll 또는 fetchSet 사용합니다. 보다 구체적인 요구에 대해서는 아래의 초기화기 중 하나를 선호 할 수 있습니다. 그들 모두는 커서의 요소 수에 대한 아이디어가있을 때 앱을 최적화하는 데 도움이되는 추가 선택적 minimumCapacity 인수를 수락합니다 (내장 fetchAll 및 fetchSet 그러한 최적화를 수행하지 않습니다).
RangeReplaceableCollection 에 부합하는 배열 및 모든 유형 :
// [String]
let cursor = try String . fetchCursor ( db , ... )
let array = try Array ( cursor )세트 :
// Set<Int>
let cursor = try Int . fetchCursor ( db , ... )
let set = try Set ( cursor )사전 :
// [Int64: [Player]]
let cursor = try Player . fetchCursor ( db )
let dictionary = try Dictionary ( grouping : cursor , by : { $0 . teamID } )
// [Int64: Player]
let cursor = try Player . fetchCursor ( db ) . map { ( $0 . id , $0 ) }
let dictionary = try Dictionary ( uniqueKeysWithValues : cursor ) 커서는 표준 게으른 시퀀스의 신속한 시퀀스와 비슷한 커서 프로토콜을 채택합니다. 따라서 커서는 compactMap , contains , dropFirst , dropLast , drop(while:) , enumerated , filter , first , flatMap , forEach , joined joined(separator:) , max , max(by:) , 다음과 같은 많은 편의 방법이 있습니다. min , min(by:) , map , prefix , prefix(while:) , reduce , reduce(into:) , suffix :
// Prints all Github links
try URL
. fetchCursor ( db , sql : " SELECT url FROM link " )
. filter { url in url . host == " github.com " }
. forEach { url in print ( url ) }
// An efficient cursor of coordinates:
let locations = try Row .
. fetchCursor ( db , sql : " SELECT latitude, longitude FROM place " )
. map { row in
CLLocationCoordinate2D ( latitude : row [ 0 ] , longitude : row [ 1 ] )
}커서는 신속한 시퀀스가 아닙니다. SQLITE 결과를 읽는 경우 언제든지 신속한 시퀀스가 반복 오류를 처리 할 수 없기 때문입니다.
커서에는 약간의 관리가 필요합니다 .
커서 반복 중에 결과를 수정하지 마십시오.
// Undefined behavior
while let player = try players . next ( ) {
try db . execute ( sql : " DELETE ... " )
} Row 의 커서를 배열이나 세트로 바꾸지 마십시오. 당신은 당신이 기대하는 독특한 행을 얻지 못할 것입니다. 행의 배열을 얻으려면 Row.fetchAll(...) 사용하십시오. 행 세트를 얻으려면 Row.fetchSet(...) 을 사용하십시오. 일반적으로 말하면, 나중에 사용하기 위해 커서에서 추출 할 때마다 행을 복사해야합니다. row.copy() .
차이를 보지 못하거나 차이를 신경 쓰지 않으면 배열을 사용하십시오. 메모리와 성능에 관심이있는 경우 적절한 경우 커서를 사용하십시오.
Row행, 어레이 , 세트 또는 단일 행의 커서 페치 (페치 방법 참조) :
try dbQueue . read { db in
try Row . fetchCursor ( db , sql : " SELECT ... " , arguments : ... ) // A Cursor of Row
try Row . fetchAll ( db , sql : " SELECT ... " , arguments : ... ) // [Row]
try Row . fetchSet ( db , sql : " SELECT ... " , arguments : ... ) // Set<Row>
try Row . fetchOne ( db , sql : " SELECT ... " , arguments : ... ) // Row?
let rows = try Row . fetchCursor ( db , sql : " SELECT * FROM wine " )
while let row = try rows . next ( ) {
let name : String = row [ " name " ]
let color : Color = row [ " color " ]
print ( name , color )
}
}
let rows = try dbQueue . read { db in
try Row . fetchAll ( db , sql : " SELECT * FROM player " )
} 인수는 위치를 채우는 선택적 배열 또는 사전입니까 ? 그리고 콜론 준비된 키와 같은 :name :
let rows = try Row . fetchAll ( db ,
sql : " SELECT * FROM player WHERE name = ? " ,
arguments : [ " Arthur " ] )
let rows = try Row . fetchAll ( db ,
sql : " SELECT * FROM player WHERE name = :name " ,
arguments : [ " name " : " Arthur " ] ) 지원되는 인수 유형 (bool, int, string, date, swift enums 등) 및 sqlite 인수에 대한 자세한 문서에 대한 자세한 내용 StatementArguments 값을 참조하십시오.
데이터베이스 행의 사본이 포함 된 로우 어레이와 달리 행 커서는 SQLite 금속에 가깝고 약간의 관리가 필요합니다.
참고 :
Row커서를 배열이나 세트로 바꾸지 마십시오 . 당신은 당신이 기대하는 독특한 행을 얻지 못할 것입니다. 행의 배열을 얻으려면Row.fetchAll(...)사용하십시오. 행 세트를 얻으려면Row.fetchSet(...)을 사용하십시오. 일반적으로 말하면, 나중에 사용하기 위해 커서에서 추출 할 때마다 행을 복사해야합니다.row.copy().
색인 또는 열 이름으로 열 값을 읽으십시오 .
let name : String = row [ 0 ] // 0 is the leftmost column
let name : String = row [ " name " ] // Leftmost matching column - lookup is case-insensitive
let name : String = row [ Column ( " name " ) ] // Using query interface's Column값이 무일하게있을 때 선택 사항을 요청하십시오.
let name : String ? = row [ " name " ] row[] 첨자는 요청 유형을 반환합니다. 지원되는 값 유형에 대한 자세한 내용은 값을 참조하십시오.
let bookCount : Int = row [ " bookCount " ]
let bookCount64 : Int64 = row [ " bookCount " ]
let hasBooks : Bool = row [ " bookCount " ] // false when 0
let string : String = row [ " date " ] // "2015-09-11 18:14:15.123"
let date : Date = row [ " date " ] // Date
self . date = row [ " date " ] // Depends on the type of the property. as 유형 주조 연산자를 사용할 수도 있습니다.
row [ ... ] as Int
row [ ... ] as Int ?경고 :
as!그리고as?운영자 :if let int = row [ ... ] as? Int { ... } // BAD - doesn't work if let int = row [ ... ] as Int ? { ... } // GOOD
경고 : nil 코일 스코어링 행 값을 피하고 대신
coalesce방법을 선호합니다.let name : String ? = row [ " nickname " ] ?? row [ " name " ] // BAD - doesn't work let name : String ? = row . coalesce ( [ " nickname " , " name " ] ) // GOOD
일반적으로 기본 SQLITE 값에서 변환 할 수있는 경우 필요한 유형을 추출 할 수 있습니다.
성공적인 전환에는 다음이 포함됩니다.
지원되는 유형에 대한 자세한 내용은 값을 참조하십시오 (Bool, Int, String, Date, Swift Enums 등).
null은 nil을 반환합니다.
let row = try Row . fetchOne ( db , sql : " SELECT NULL " ) !
row [ 0 ] as Int ? // nil
row [ 0 ] as Int // fatal error: could not convert NULL to Int.그러나 한 가지 예외가 있습니다. DatabaseValue 유형 :
row [ 0 ] as DatabaseValue // DatabaseValue.null누락 된 열은 nil을 반환합니다.
let row = try Row . fetchOne ( db , sql : " SELECT 'foo' AS foo " ) !
row [ " missing " ] as String ? // nil
row [ " missing " ] as String // fatal error: no such column: missing hasColumn 메소드가있는 열 존재를 명시 적으로 확인할 수 있습니다.
잘못된 전환은 치명적인 오류를 던집니다.
let row = try Row . fetchOne ( db , sql : " SELECT 'Mom’s birthday' " ) !
row [ 0 ] as String // "Mom’s birthday"
row [ 0 ] as Date ? // fatal error: could not convert "Mom’s birthday" to Date.
row [ 0 ] as Date // fatal error: could not convert "Mom’s birthday" to Date.
let row = try Row . fetchOne ( db , sql : " SELECT 256 " ) !
row [ 0 ] as Int // 256
row [ 0 ] as UInt8 ? // fatal error: could not convert 256 to UInt8.
row [ 0 ] as UInt8 // fatal error: could not convert 256 to UInt8.이러한 변환 치명적 오류는 DatabaseValue 유형으로 피할 수 있습니다.
let row = try Row . fetchOne ( db , sql : " SELECT 'Mom’s birthday' " ) !
let dbValue : DatabaseValue = row [ 0 ]
if dbValue . isNull {
// Handle NULL
} else if let date = Date . fromDatabaseValue ( dbValue ) {
// Handle valid date
} else {
// Handle invalid date
}이 추가적인 진실성은 신뢰할 수없는 데이터베이스를 다루어야하는 결과입니다. 대신 데이터베이스의 내용을 수정하는 것을 고려할 수 있습니다. 자세한 내용은 치명적인 오류를 참조하십시오.
SQLITE는 약한 유형 시스템을 가지고 있으며 문자열을 int로, 두 배로 블로브 등을 돌릴 수있는 편의 변환을 제공합니다.
GRDB는 때때로 이러한 전환을 통과하게합니다.
let rows = try Row . fetchCursor ( db , sql : " SELECT '20 small cigars' " )
while let row = try rows . next ( ) {
row [ 0 ] as Int // 20
}놀라지 마십시오 : 이러한 전환으로 인해 SQLITE가 사용하려는 엄청나게 성공적인 데이터베이스 엔진이되는 것을 막지 못했습니다. GRDB는 위에 설명 된 안전 점검을 추가합니다. 또한 DatabaseValue 유형을 사용하여 이러한 편의 변환을 방지 할 수도 있습니다.
DatabaseValue
DatabaseValue 는 SQLITE와 귀하의 값 사이의 중간 유형으로 데이터베이스에 저장된 원시 값에 대한 정보를 제공합니다.
다른 값 유형과 마찬가지로 DatabaseValue 얻습니다.
let dbValue : DatabaseValue = row [ 0 ]
let dbValue : DatabaseValue ? = row [ " name " ] // nil if and only if column does not exist
// Check for NULL:
dbValue . isNull // Bool
// The stored value:
dbValue . storage . value // Int64, Double, String, Data, or nil
// All the five storage classes supported by SQLite:
switch dbValue . storage {
case . null : print ( " NULL " )
case . int64 ( let int64 ) : print ( " Int64: ( int64 ) " )
case . double ( let double ) : print ( " Double: ( double ) " )
case . string ( let string ) : print ( " String: ( string ) " )
case . blob ( let data ) : print ( " Data: ( data ) " )
} DatabaseValue 에서 FromDatabaseValue () 메소드를 사용하여 일반 값 (BOOL, Int, String, Date, Swift Enums 등)을 추출 할 수 있습니다.
let dbValue : DatabaseValue = row [ " bookCount " ]
let bookCount = Int . fromDatabaseValue ( dbValue ) // Int?
let bookCount64 = Int64 . fromDatabaseValue ( dbValue ) // Int64?
let hasBooks = Bool . fromDatabaseValue ( dbValue ) // Bool?, false when 0
let dbValue : DatabaseValue = row [ " date " ]
let string = String . fromDatabaseValue ( dbValue ) // "2015-09-11 18:14:15.123"
let date = Date . fromDatabaseValue ( dbValue ) // Date? 무효 변환에 대해서는 fromDatabaseValue 반환합니다.
let row = try Row . fetchOne ( db , sql : " SELECT 'Mom’s birthday' " ) !
let dbValue : DatabaseValue = row [ 0 ]
let string = String . fromDatabaseValue ( dbValue ) // "Mom’s birthday"
let int = Int . fromDatabaseValue ( dbValue ) // nil
let date = Date . fromDatabaseValue ( dbValue ) // nil 행은 표준 randomAccessCollection 프로토콜을 채택하며 DatabaseValue 사전으로 볼 수 있습니다.
// All the (columnName, dbValue) tuples, from left to right:
for (columnName , dbValue ) in row {
...
}사전 (표준 신속한 사전 및 nsdictionary)에서 행을 구축 할 수 있습니다 . 지원되는 유형에 대한 자세한 내용은 값을 참조하십시오.
let row : Row = [ " name " : " foo " , " date " : nil ]
let row = Row ( [ " name " : " foo " , " date " : nil ] )
let row = Row ( /* [AnyHashable: Any] */ ) // nil if invalid dictionary그러나 행은 실제 사전이 아닙니다. 중복 열이 포함될 수 있습니다.
let row = try Row . fetchOne ( db , sql : " SELECT 1 AS foo, 2 AS foo " ) !
row . columnNames // ["foo", "foo"]
row . databaseValues // [1, 2]
row [ " foo " ] // 1 (leftmost matching column)
for (columnName , dbValue ) in row { ... } // ("foo", 1), ("foo", 2)행에서 사전을 만들 때 동일한 열을 명확하게하고 데이터베이스 값을 제시하는 방법을 선택해야합니다. 예를 들어:
[String: DatabaseValue] 사전 중복 열 이름의 경우 가장 왼쪽 값을 유지하는 사전 :
let dict = Dictionary ( row , uniquingKeysWith : { ( left , _ ) in left } ) [String: AnyObject] 사전은 중복 열 이름의 경우 가장 값을 유지합니다. 이 사전은 FMDB의 FMRESULTSET의 결과와 동일합니다. NULL 열에 대한 NSNULL 값이 포함되어 있으며 Objective-C와 공유 할 수 있습니다.
let dict = Dictionary (
row . map { ( column , dbValue ) in
( column , dbValue . storage . value as AnyObject )
} ,
uniquingKeysWith : { ( _ , right ) in right } ) 예를 들어 Jsonserialization과 같은 [String: Any] 사전을 먹일 수 있습니다.
let dict = Dictionary (
row . map { ( column , dbValue ) in
( column , dbValue . storage . value )
} ,
uniquingKeysWith : { ( left , _ ) in left } ) 자세한 내용은 Dictionary.init(_:uniquingKeysWith:) 의 문서를 참조하십시오.
DatabaseValueConvertible
행 대신 값을 직접 가져올 수 있습니다. 지원되는 값 유형 (bool, int, string, date, swift enums 등)이 많이 있습니다.
행과 마찬가지로 값을 커서 , 배열 , 세트 또는 단일 값으로 가져옵니다 (페치 메소드 참조). 값은 SQL 쿼리의 가장 왼쪽 열에서 추출됩니다.
try dbQueue . read { db in
try Int . fetchCursor ( db , sql : " SELECT ... " , arguments : ... ) // A Cursor of Int
try Int . fetchAll ( db , sql : " SELECT ... " , arguments : ... ) // [Int]
try Int . fetchSet ( db , sql : " SELECT ... " , arguments : ... ) // Set<Int>
try Int . fetchOne ( db , sql : " SELECT ... " , arguments : ... ) // Int?
let maxScore = try Int . fetchOne ( db , sql : " SELECT MAX(score) FROM player " ) // Int?
let names = try String . fetchAll ( db , sql : " SELECT name FROM player " ) // [String]
} Int.fetchOne 두 가지 경우에 nil을 반환합니다. select 문은 행이 없거나 널 값이 1 행을 산출했습니다.
// No row:
try Int . fetchOne ( db , sql : " SELECT 42 WHERE FALSE " ) // nil
// One row with a NULL value:
try Int . fetchOne ( db , sql : " SELECT NULL " ) // nil
// One row with a non-NULL value:
try Int . fetchOne ( db , sql : " SELECT 42 " ) // 42NULL이 포함될 수있는 요청의 경우 선택 사항을 가져옵니다.
try dbQueue . read { db in
try Optional < Int > . fetchCursor ( db , sql : " SELECT ... " , arguments : ... ) // A Cursor of Int?
try Optional < Int > . fetchAll ( db , sql : " SELECT ... " , arguments : ... ) // [Int?]
try Optional < Int > . fetchSet ( db , sql : " SELECT ... " , arguments : ... ) // Set<Int?>
}팁 : 하나의 값을 가져 오면 한 번의 고급 유스 케이스는 행이 없거나 널 값이 1 행을 산출하는 진술의 사례를 구별하는 것입니다. 이렇게하려면
Optional<Int>.fetchOne사용하여 이중 옵션Int??:// No row: try Optional < Int > . fetchOne ( db , sql : " SELECT 42 WHERE FALSE " ) // .none // One row with a NULL value: try Optional < Int > . fetchOne ( db , sql : " SELECT NULL " ) // .some(.none) // One row with a non-NULL value: try Optional < Int > . fetchOne ( db , sql : " SELECT 42 " ) // .some(.some(42))
지원되는 값 유형 (bool, int, string, date, swift enums 등)이 많이 있습니다. 자세한 내용은 값을 참조하십시오.
GRDB는 다음 값 유형에 대한 내장 지원을 제공합니다.
Swift Standard Library : Bool, Double, Float, 모든 서명 및 서명되지 않은 정수 유형, String, Swift Enums.
재단 : 데이터, 날짜, 데이터 호텔, 소수점, NSNULL, NSNUMBER, NSSTRING, URL, UUID.
CoreGraphics : CGFLOAT.
DatabaseValue , 데이터베이스에 저장된 원시 값에 대한 정보를 제공하는 유형입니다.
전체 텍스트 패턴 : fts3pattern 및 fts5pattern.
일반적으로 DatabaseValueConvertible 프로토콜을 채택하는 모든 유형.
값은 진술 인수로 사용할 수 있습니다.
let url : URL = ...
let verified : Bool = ...
try db . execute (
sql : " INSERT INTO link (url, verified) VALUES (?, ?) " ,
arguments : [ url , verified ] )행에서 값을 추출 할 수 있습니다.
let rows = try Row . fetchCursor ( db , sql : " SELECT * FROM link " )
while let row = try rows . next ( ) {
let url : URL = row [ " url " ]
let verified : Bool = row [ " verified " ]
}값은 직접 가져올 수 있습니다.
let urls = try URL . fetchAll ( db , sql : " SELECT url FROM link " ) // [URL]레코드에서 값 사용 :
struct Link : FetchableRecord {
var url : URL
var isVerified : Bool
init ( row : Row ) {
url = row [ " url " ]
isVerified = row [ " verified " ]
}
}쿼리 인터페이스에서 값을 사용하십시오.
let url : URL = ...
let link = try Link . filter ( Column ( " url " ) == url ) . fetchOne ( db )데이터는 Blob Sqlite 열에 적합합니다. 다른 값과 마찬가지로 데이터베이스에서 저장 및 가져올 수 있습니다.
let rows = try Row . fetchCursor ( db , sql : " SELECT data, ... " )
while let row = try rows . next ( ) {
let data : Data = row [ " data " ]
} 요청 반복의 각 단계에서 row[] 첨자는 데이터베이스 바이트의 두 개의 사본을 생성합니다.
sqlite가 가져온 데이터를 복사하지 않음으로써 메모리를 저장할 수있는 기회가 있습니다 .
while let row = try rows . next ( ) {
try row . withUnsafeData ( name : " data " ) { ( data : Data ? ) in
...
}
}비공개 데이터는 반복 단계보다 더 오래 살지 않습니다.이 시점을 지나서 사용하지 않도록하십시오.
날짜 및 데이터 컴포넌트는 데이터베이스에서 저장 및 가져올 수 있습니다.
GRDB가 SQLITE에서 지원하는 다양한 날짜 형식을 지원하는 방법은 다음과 같습니다.
| sqlite 형식 | 날짜 | Datecomponents |
|---|---|---|
| yyyy-mm-dd | 읽기 ¹ | 읽기 / 쓰기 |
| Yyyy-MM-DD HH : MM | ¹ ²를 읽으십시오 | 읽으십시오 |
| yyyy-mm-dd hh : mm : ss | ¹ ²를 읽으십시오 | 읽으십시오 |
| yyyy-mm-dd hh : mm : ss.sss | 읽기 ¹ ² / 쓰기 ¹ | 읽으십시오 |
| yyyy-mm-dd t hh : mm | ¹ ²를 읽으십시오 | 읽기 ² |
| yyyy-mm-dd t hh : mm : ss | ¹ ²를 읽으십시오 | 읽기 ² |
| yyyy-mm-dd t hh : mm : ss.sss | ¹ ²를 읽으십시오 | 읽기 ² |
| HH : MM | 읽으십시오 | |
| HH : MM : SS | 읽으십시오 | |
| HH : MM : SS.SSS | 읽으십시오 | |
| Unix Epoch 이후 타임 스탬프 | ³ | |
now |
¹ 누락 된 구성 요소는 0이라고 가정합니다. 형식에 이어 시간대 표시기 인 ⁽²⁾가 뒤 따르는 경우가 아니라면 날짜는 UTC 시간대에 저장 및 읽습니다.
²이 형식은 선택적으로 [+-]HH:MM 또는 Z 형식의 시간대 표시기가 이어질 수 있습니다.
³ GRDB 2+는 숫자 값을 연료 Date(timeIntervalSince1970:) . 이전 GRDB 버전은 숫자를 Julian Days로 해석하는 데 사용되었습니다. Julian Days는 여전히 Date(julianDay:) 이니셜 라이저와 함께 지원됩니다.
경고 : SQLITE 날짜 형식의 유효한 연도는 0000-9999입니다. 응용 프로그램 이이 범위를 벗어난 몇 년을 처리해야 할 때 다른 날짜 형식을 선택해야합니다. 다음 장을 참조하십시오.
날짜는 다른 값과 마찬가지로 데이터베이스에서 저장 및 가져올 수 있습니다.
try db . execute (
sql : " INSERT INTO player (creationDate, ...) VALUES (?, ...) " ,
arguments : [ Date ( ) , ... ] )
let row = try Row . fetchOne ( db , ... ) !
let creationDate : Date = row [ " creationDate " ]날짜는 UTC 시간대에서 "YYYY-MM-DD HH : MM : SS.SSS"형식을 사용하여 저장됩니다. 밀리 초에 정확합니다.
참고 :이 형식은 다음과 같은 유일한 형식이기 때문에 선택되었습니다.
- 비슷한 (
ORDER BY date)- sqlite 키워드 current_timestamp
WHERE date > CURRENT_TIMESTAMP비교할 수 있습니다.- SQLITE 날짜 및 시간 기능을 공급할 수 있습니다
- 충분히 정확합니다
경고 : SQLITE 날짜 형식의 유효한 연도는 0000-9999입니다. 디코딩 오류 또는 SQLITE 날짜 및 시간 기능을 사용한 잘못된 날짜 계산과 같은이 범위 이외의 수년간의 문제를 경험하게됩니다.
일부 응용 프로그램은 다른 날짜 형식을 선호 할 수 있습니다.
T 분리기를 가진 ISO-8601을 선호 할 수 있습니다.Date 왕복이 필요할 수 있습니다.다른 날짜 형식을 선택하기 전에 두 번 생각해야합니다.
Date 라운드 트립은 첫눈에 보이는 것처럼 명백한 요구가 아닙니다. 다른 시스템이 자신의 날짜 표현 (앱의 안드로이드 버전, 응용 프로그램에 대해 이야기하는 서버 등)을 사용하기 때문에 일반적으로 날짜는 정확하게 응용 프로그램을 떠나 자마자 반올림하지 않습니다. 그 중 Date 비교는 적어도 부동 소수점 비교만큼 단단하고 불쾌합니다.날짜 형식의 사용자 정의는 명시 적입니다. 예를 들어:
let date = Date ( )
let timeInterval = date . timeIntervalSinceReferenceDate
try db . execute (
sql : " INSERT INTO player (creationDate, ...) VALUES (?, ...) " ,
arguments : [ timeInterval , ... ] )
if let row = try Row . fetchOne ( db , ... ) {
let timeInterval : TimeInterval = row [ " creationDate " ]
let creationDate = Date ( timeIntervalSinceReferenceDate : timeInterval )
} 더 많은 날짜 사용자 정의 옵션에 대한 코딩 가능한 레코드 및 사용자 정의 된 데이터베이스 표현으로 날짜 랩핑 유형을 정의하려면 DatabaseValueConvertible 참조하십시오.
DateComponents는 DatabasedAteComponents 도우미 유형을 통해 간접적으로 지원됩니다.
DatabaseDateComponents SQLITE에서 지원하는 모든 날짜 형식의 날짜 구성 요소를 읽고 HH : MM에서 YYYY-MM-DD HH : MM : SS.SSS까지 선택한 형식으로 저장합니다.
경고 : 유효한 연도의 범위는 0000-9999입니다. 디코딩 오류 또는 SQLITE 날짜 및 시간 기능을 사용한 잘못된 날짜 계산과 같은이 범위 이외의 수년간의 문제를 경험하게됩니다. 자세한 내용은 날짜를 참조하십시오.
DatabasedAteComponents는 다른 값과 마찬가지로 데이터베이스에서 저장 및 가져올 수 있습니다.
let components = DateComponents ( )
components . year = 1973
components . month = 9
components . day = 18
// Store "1973-09-18"
let dbComponents = DatabaseDateComponents ( components , format : . YMD )
try db . execute (
sql : " INSERT INTO player (birthDate, ...) VALUES (?, ...) " ,
arguments : [ dbComponents , ... ] )
// Read "1973-09-18"
let row = try Row . fetchOne ( db , sql : " SELECT birthDate ... " ) !
let dbComponents : DatabaseDateComponents = row [ " birthDate " ]
dbComponents . format // .YMD (the actual format found in the database)
dbComponents . dateComponents // DateComponentsNSNUMBER 및 DECIMAL은 다른 값과 마찬가지로 데이터베이스에서 저장 및 가져올 수 있습니다.
GRDB가 SQLITE에서 지원하는 다양한 데이터 유형을 지원하는 방법은 다음과 같습니다.
| 정수 | 더블 | 끈 | |
|---|---|---|---|
| nsnumber | 읽기 / 쓰기 | 읽기 / 쓰기 | 읽다 |
| NSDECIMALNUMBER | 읽기 / 쓰기 | 읽기 / 쓰기 | 읽다 |
| 소수 | 읽다 | 읽다 | 읽기 / 쓰기 |
세 가지 유형 모두 데이터베이스 정수 및 복식을 해독 할 수 있습니다.
let number = try NSNumber . fetchOne ( db , sql : " SELECT 10 " ) // NSNumber
let number = try NSDecimalNumber . fetchOne ( db , sql : " SELECT 1.23 " ) // NSDecimalNumber
let number = try Decimal . fetchOne ( db , sql : " SELECT -100 " ) // Decimal세 가지 유형 모두 데이터베이스 문자열을 소수점 숫자로 디코딩합니다.
let number = try NSNumber . fetchOne ( db , sql : " SELECT '10' " ) // NSDecimalNumber (sic)
let number = try NSDecimalNumber . fetchOne ( db , sql : " SELECT '1.23' " ) // NSDecimalNumber
let number = try Decimal . fetchOne ( db , sql : " SELECT '-100' " ) // Decimal NSNumber 및 NSDecimalNumber 데이터베이스에서 64 비트 서명 정수 및 복식을 보냅니다.
// INSERT INTO transfer VALUES (10)
try db . execute ( sql : " INSERT INTO transfer VALUES (?) " , arguments : [ NSNumber ( value : 10 ) ] )
// INSERT INTO transfer VALUES (10.0)
try db . execute ( sql : " INSERT INTO transfer VALUES (?) " , arguments : [ NSNumber ( value : 10.0 ) ] )
// INSERT INTO transfer VALUES (10)
try db . execute ( sql : " INSERT INTO transfer VALUES (?) " , arguments : [ NSDecimalNumber ( string : " 10.0 " ) ] )
// INSERT INTO transfer VALUES (10.5)
try db . execute ( sql : " INSERT INTO transfer VALUES (?) " , arguments : [ NSDecimalNumber ( string : " 10.5 " ) ] )경고 : SQLITE는 소수점 숫자를 지원하지 않기 때문에 비 integer
NSDecimalNumber보내면 전환 중에 정밀도가 손실 될 수 있습니다.비인간
NSDecimalNumber데이터베이스로 보내는 대신 다음을 선호 할 수 있습니다.
- 대신
Decimal보내십시오 (데이터베이스에 소수점을 저장하십시오).- 대신 정수를 보냅니다 (예 : 유로의 양 대신 센트를 저장하십시오).
Decimal 데이터베이스에서 10 진수 문자열을 보냅니다.
// INSERT INTO transfer VALUES ('10')
try db . execute ( sql : " INSERT INTO transfer VALUES (?) " , arguments : [ Decimal ( 10 ) ] )
// INSERT INTO transfer VALUES ('10.5')
try db . execute ( sql : " INSERT INTO transfer VALUES (?) " , arguments : [ Decimal ( string : " 10.5 " ) ! ] )UUID는 다른 값과 마찬가지로 데이터베이스에서 저장 및 가져올 수 있습니다.
GRDB는 UUID를 16 바이트 데이터 블로브로 저장하고 16 바이트 데이터 블로브 및 "E621E1F8-C36C-495A-93FC-0C247A3E6E5F"와 같은 문자열에서 디코딩합니다.
Swift 열거 및 일반적으로 RawPresentable 프로토콜을 채택하는 모든 유형은 원시 값과 마찬가지로 데이터베이스에서 저장 및 가져올 수 있습니다.
enum Color : Int {
case red , white , rose
}
enum Grape : String {
case chardonnay , merlot , riesling
}
// Declare empty DatabaseValueConvertible adoption
extension Color : DatabaseValueConvertible { }
extension Grape : DatabaseValueConvertible { }
// Store
try db . execute (
sql : " INSERT INTO wine (grape, color) VALUES (?, ?) " ,
arguments : [ Grape . merlot , Color . red ] )
// Read
let rows = try Row . fetchCursor ( db , sql : " SELECT * FROM wine " )
while let row = try rows . next ( ) {
let grape : Grape = row [ " grape " ]
let color : Color = row [ " color " ]
}데이터베이스 값이 열거적인 경우와 일치하지 않으면 치명적인 오류가 발생합니다. 이 치명적인 오류는 DatabaseValue 유형으로 피할 수 있습니다.
let row = try Row . fetchOne ( db , sql : " SELECT 'syrah' " ) !
row [ 0 ] as String // "syrah"
row [ 0 ] as Grape ? // fatal error: could not convert "syrah" to Grape.
row [ 0 ] as Grape // fatal error: could not convert "syrah" to Grape.
let dbValue : DatabaseValue = row [ 0 ]
if dbValue . isNull {
// Handle NULL
} else if let grape = Grape . fromDatabaseValue ( dbValue ) {
// Handle valid grape
} else {
// Handle unknown grape
} SQLITE를 사용하면 SQL 기능 및 집계를 정의 할 수 있습니다.
사용자 정의 SQL 함수 또는 집계는 SQLITE를 확장합니다.
SELECT reverse(name) FROM player; -- custom function
SELECT maxLength(name) FROM player; -- custom aggregate DatabaseFunction
함수 인수는 데이터베이스 값의 배열을 가져 와서 유효한 값 (bool, int, string, date, swift enums 등)을 반환합니다. 데이터베이스 값의 수는 ArgumentCount 로 보장됩니다.
SQLITE는 함수가 "순수"일 때 추가 최적화를 수행 할 수있는 기회가 있습니다. 이는 결과가 인수에만 의존한다는 것을 의미합니다. 따라서 가능하면 순수한 인수를 참으로 설정하십시오.
let reverse = DatabaseFunction ( " reverse " , argumentCount : 1 , pure : true ) { ( values : [ DatabaseValue ] ) in
// Extract string value, if any...
guard let string = String . fromDatabaseValue ( values [ 0 ] ) else {
return nil
}
// ... and return reversed string:
return String ( string . reversed ( ) )
}구성을 통해 데이터베이스 연결에 기능을 사용할 수 있습니다.
var config = Configuration ( )
config . prepareDatabase { db in
db . add ( function : reverse )
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )
try dbQueue . read { db in
// "oof"
try String . fetchOne ( db , sql : " SELECT reverse('foo') " ) !
}함수는 가변적인 수의 인수를 취할 수 있습니다.
명백한 인수를 제공하지 않으면 기능은 여러 가지 인수를 취할 수 있습니다.
let averageOf = DatabaseFunction ( " averageOf " , pure : true ) { ( values : [ DatabaseValue ] ) in
let doubles = values . compactMap { Double . fromDatabaseValue ( $0 ) }
return doubles . reduce ( 0 , + ) / Double ( doubles . count )
}
db . add ( function : averageOf )
// 2.0
try Double . fetchOne ( db , sql : " SELECT averageOf(1, 2, 3) " ) !기능은 던질 수 있습니다.
let sqrt = DatabaseFunction ( " sqrt " , argumentCount : 1 , pure : true ) { ( values : [ DatabaseValue ] ) in
guard let double = Double . fromDatabaseValue ( values [ 0 ] ) else {
return nil
}
guard double >= 0 else {
throw DatabaseError ( message : " invalid negative number " )
}
return sqrt ( double )
}
db . add ( function : sqrt )
// SQLite error 1 with statement `SELECT sqrt(-1)`: invalid negative number
try Double . fetchOne ( db , sql : " SELECT sqrt(-1) " ) !쿼리 인터페이스에서 사용자 정의 기능을 사용하십시오.
// SELECT reverseString("name") FROM player
Player . select ( reverseString ( nameColumn ) )GRDB는 유니 코드 인식 문자열 변환을 수행하는 내장 SQL 기능을 제공합니다. 유니 코드를 참조하십시오.
DatabaseFunction , DatabaseAggregate
사용자 정의 집계를 등록하기 전에 DatabaseAggregate 프로토콜을 채택하는 유형을 정의해야합니다.
protocol DatabaseAggregate {
// Initializes an aggregate
init ( )
// Called at each step of the aggregation
mutating func step ( _ dbValues : [ DatabaseValue ] ) throws
// Returns the final result
func finalize ( ) throws -> DatabaseValueConvertible ?
}예를 들어:
struct MaxLength : DatabaseAggregate {
var maxLength : Int = 0
mutating func step ( _ dbValues : [ DatabaseValue ] ) {
// At each step, extract string value, if any...
guard let string = String . fromDatabaseValue ( dbValues [ 0 ] ) else {
return
}
// ... and update the result
let length = string . count
if length > maxLength {
maxLength = length
}
}
func finalize ( ) -> DatabaseValueConvertible ? {
maxLength
}
}
let maxLength = DatabaseFunction (
" maxLength " ,
argumentCount : 1 ,
pure : true ,
aggregate : MaxLength . self )사용자 정의 SQL 기능과 마찬가지로 구성을 통해 데이터베이스 연결에 집계 기능을 사용할 수 있습니다.
var config = Configuration ( )
config . prepareDatabase { db in
db . add ( function : maxLength )
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )
try dbQueue . read { db in
// Some Int
try Int . fetchOne ( db , sql : " SELECT maxLength(name) FROM player " ) !
} 집계의 step 메소드는 데이터베이스 값 배열을 취합니다. 이 배열에는 ArgumentCount 매개 변수 (또는 ArgumentCount 가 생략 될 때 값의 모든 값)만큼 많은 값이 포함됩니다.
골재의 finalize 방법은 최종 집계 값 (bool, int, string, date, swift enums 등)을 반환합니다.
SQLITE는 골재가 "순수"일 때 추가 최적화를 수행 할 수있는 기회가 있습니다. 즉, 결과는 입력에만 의존한다는 것을 의미합니다. 따라서 가능하면 순수한 인수를 참으로 설정하십시오.
쿼리 인터페이스에서 사용자 정의 집계를 사용하십시오.
// SELECT maxLength("name") FROM player
let request = Player . select ( maxLength . apply ( nameColumn ) )
try Int . fetchOne ( db , request ) // Int? 모든 SQLITE API가 GRDB에 노출되지 않더라도 SQLITE C 인터페이스를 사용하고 SQLITE C 함수를 호출 할 수 있습니다.
SQLCIPHER 또는 SYSTEM SQLITE의 C SQLITE 기능에 액세스하려면 추가 가져 오기를 수행해야합니다.
import SQLite3 // System SQLite
import SQLCipher // SQLCipher
let sqliteVersion = String ( cString : sqlite3_libversion ( ) ) 데이터베이스 연결 및 명령문에 대한 원시 포인터는 Database.sqliteConnection 및 Statement.sqliteStatement 속성을 통해 제공됩니다.
try dbQueue . read { db in
// The raw pointer to a database connection:
let sqliteConnection = db . sqliteConnection
// The raw pointer to a statement:
let statement = try db . makeStatement ( sql : " SELECT ... " )
let sqliteStatement = statement . sqliteStatement
}메모
- 이 포인터는 GRDB가 소유하고 있습니다. 연결을 닫지 말고 GRDB가 만든 진술을 마무리합니다.
- GRDB는 "멀티 스레드 모드"에서 SQLITE 연결을 엽니 다. 이는 (홀수) 스레드 안전이 아님을 의미합니다. 전용 파견 대기열 내에서 원시 데이터베이스 및 명령문을 터치하십시오.
- 자신의 위험에 따라 RAW SQLITE C 인터페이스를 사용하십시오. GRDB는 발에 자신을 쏘는 것을 막지 않습니다.
SQLite API 외에 GRDB는 데이터베이스 행을 "레코드"라는 일반 객체로 조작하는 데 도움이되는 프로토콜을 제공합니다 .
try dbQueue . write { db in
if var place = try Place . fetchOne ( db , id : 1 ) {
place . isFavorite = true
try place . update ( db )
}
}물론 데이터베이스 연결을 열고 먼저 데이터베이스 테이블을 만들어야합니다.
레코드 유형을 정의하려면 유형을 정의하고 집중된 기능 세트와 함께 제공되는 프로토콜로 확장하십시오.
예를 들어:
struct Player: {
var id: Int64
var name: String
var score: Int
}
// Players can be fetched from the database.
extension Player: FetchableRecord { ... }
// Players can be saved into the database.
extension Player: PersistableRecord { ... }
레코드 정의의 몇 가지 예를 참조하십시오.
참고 : Core Data의 nsmanagedObject 또는 Realm의 객체에 익숙하다면 문화적 충격을 경험할 수 있습니다. GRDB 레코드는 고유하지 않고 자동 업데이트하지 않으며 게으른로드하지 않습니다. 이것은 프로토콜 지향 프로그래밍의 목적이자 결과입니다.
팁 : 레코드 유형 설계를위한 권장 사례는 일반적인 지침을 제공합니다 ..
팁 : 레코드를 사용하는 샘플 앱의 데모 애플리케이션을 참조하십시오.
개요
프로토콜 및 레코드 클래스
RETURNING 조항 데이터베이스에 레코드를 삽입하려면 insert 방법을 호출하십시오.
let player = Player ( name : " Arthur " , email : " [email protected] " )
try player . insert ( db ) insert PersistableRecord 프로토콜을 채택하는 유형에 사용할 수 있습니다.
데이터베이스에서 레코드를 가져 오려면 페치 방법을 호출하십시오.
let arthur = try Player . fetchOne ( db , // Player?
sql : " SELECT * FROM players WHERE name = ? " ,
arguments : [ " Arthur " ] )
let bestPlayers = try Player // [Player]
. order ( Column ( " score " ) . desc )
. limit ( 10 )
. fetchAll ( db )
let spain = try Country . fetchOne ( db , id : " ES " ) // Country?
let italy = try Country . find ( db , id : " IT " ) // CountryRAW SQL의 Fetching은 Fetchablerecord 프로토콜을 채택하는 유형에 사용할 수 있습니다.
쿼리 인터페이스를 사용하는 SQL이없는 페치는 Fetchablerecord 및 Tableerecord 프로토콜을 모두 채택하는 유형에 사용할 수 있습니다.
데이터베이스에서 레코드를 업데이트하려면 update 방법을 호출하십시오.
var player : Player = ...
player . score = 1000
try player . update ( db )쓸모없는 업데이트를 피할 수 있습니다.
// does not hit the database if score has not changed
try player . updateChanges ( db ) {
$0 . score = 1000
}배치 업데이트는 쿼리 인터페이스를 참조하십시오.
try Player
. filter ( Column ( " team " ) == " red " )
. updateAll ( db , Column ( " score " ) += 1 )PersistableRecord 프로토콜을 채택하는 유형에 대한 업데이트 방법을 사용할 수 있습니다. 배치 업데이트는 TableRecord 프로토콜에서 사용할 수 있습니다.
데이터베이스에서 레코드를 삭제하려면 delete 방법을 호출하십시오.
let player : Player = ...
try player . delete ( db )기본 키, 고유 키로 삭제하거나 배치 삭제를 수행 할 수도 있습니다 (요청 삭제 참조).
try Player . deleteOne ( db , id : 1 )
try Player . deleteOne ( db , key : [ " email " : " [email protected] " ] )
try Country . deleteAll ( db , ids : [ " FR " , " US " ] )
try Player
. filter ( Column ( " email " ) == nil )
. deleteAll ( db )삭제 방법은 PersistableRecord 프로토콜을 채택하는 유형에 사용할 수 있습니다. 배치 삭제는 타블러 코드 프로토콜에서 사용할 수 있습니다.
레코드를 계산하려면 fetchCount 메소드로 전화하십시오.
let playerCount : Int = try Player . fetchCount ( db )
let playerWithEmailCount : Int = try Player
. filter ( Column ( " email " ) == nil )
. fetchCount ( db ) fetchCount Tablerecord 프로토콜을 채택하는 유형에 사용할 수 있습니다.
세부 사항 다음 :
GRDB는 3 개의 레코드 프로토콜을 제공합니다 . 자신의 유형은 유형을 연장하려는 능력에 따라 하나 이상의 유형을 채택합니다.
Fetchablerecord는 데이터베이스 행을 해독 할 수 있습니다.
struct Place : FetchableRecord { ... }
let places = try dbQueue . read { db in
try Place . fetchAll ( db , sql : " SELECT * FROM place " )
}팁 :
FetchableRecord표준Decodable프로토콜에서 구현을 도출 할 수 있습니다. 자세한 내용은 코딩 가능한 레코드를 참조하십시오.
FetchableRecord 데이터베이스 행을 해독 할 수 있지만 SQL 요청을 구축 할 수는 없습니다. 이를 위해서는 TableRecord 도 필요합니다.
TableCord는 SQL 쿼리를 생성 할 수 있습니다.
struct Place : TableRecord { ... }
let placeCount = try dbQueue . read { db in
// Generates and runs `SELECT COUNT(*) FROM place`
try Place . fetchCount ( db )
} 유형이 TableRecord 와 FetchableRecord 모두 채택하면 해당 요청에서로드 할 수 있습니다.
struct Place : TableRecord , FetchableRecord { ... }
try dbQueue . read { db in
let places = try Place . order ( Column ( " title " ) ) . fetchAll ( db )
let paris = try Place . fetchOne ( id : 1 )
}PersistableRecord는 다음 을 작성할 수 있습니다. 데이터베이스에서 행을 생성, 업데이트 및 삭제할 수 있습니다.
struct Place : PersistableRecord { ... }
try dbQueue . write { db in
try Place . delete ( db , id : 1 )
try Place ( ... ) . insert ( db )
}지속 가능한 레코드는 다른 레코드와 비교할 수 있으며 쓸모없는 데이터베이스 업데이트를 피할 수 있습니다.
팁 :
PersistableRecord표준Encodable프로토콜에서 구현을 도출 할 수 있습니다. 자세한 내용은 코딩 가능한 레코드를 참조하십시오.
FetchableRecord
Fetchablerecord 프로토콜은 데이터베이스 행에서 구축 할 수있는 모든 유형에 메소드를 페치하는 방법을 부여합니다 .
protocol FetchableRecord {
/// Row initializer
init ( row : Row ) throws
}예를 들어:
struct Place {
var id : Int64 ?
var title : String
var coordinate : CLLocationCoordinate2D
}
extension Place : FetchableRecord {
init ( row : Row ) {
id = row [ " id " ]
title = row [ " title " ]
coordinate = CLLocationCoordinate2D (
latitude : row [ " latitude " ] ,
longitude : row [ " longitude " ] )
}
}행은 또한 열 열거를 허용합니다.
extension Place : FetchableRecord {
enum Columns : String , ColumnExpression {
case id , title , latitude , longitude
}
init ( row : Row ) {
id = row [ Columns . id ]
title = row [ Columns . title ]
coordinate = CLLocationCoordinate2D (
latitude : row [ Columns . latitude ] ,
longitude : row [ Columns . longitude ] )
}
} row[] 첨자에 대한 자세한 내용은 열 값을 참조하십시오.
레코드 유형이 표준 디코딩 가능한 프로토콜을 채택하면 init(row:) 에 대한 구현을 제공 할 필요가 없습니다. 자세한 내용은 코딩 가능한 레코드를 참조하십시오.
// That's all
struct Player : Decodable , FetchableRecord {
var id : Int64
var name : String
var score : Int
}FetchablereCord는 SQL 쿼리에서 유형을 채택 할 수 있습니다.
try Place . fetchCursor ( db , sql : " SELECT ... " , arguments : ... ) // A Cursor of Place
try Place . fetchAll ( db , sql : " SELECT ... " , arguments : ... ) // [Place]
try Place . fetchSet ( db , sql : " SELECT ... " , arguments : ... ) // Set<Place>
try Place . fetchOne ( db , sql : " SELECT ... " , arguments : ... ) // Place? fetchCursor , fetchAll , fetchSet 및 fetchOne 방법에 대한 정보에 대한 정보는 Fetching 방법을 참조하십시오. 쿼리 인수에 대한 자세한 내용은 StatementArguments 참조하십시오.
참고 : 성능의 이유로,
init(row:)반복되는 동안 재사용됩니다. 나중에 사용하기 위해 행을 유지하려면self.row = row.copy()를 저장하십시오.
참고 :
FetchableRecord.init(row:)이니셜 라이저는 대부분의 응용 프로그램의 요구에 맞습니다. 그러나 일부 응용 프로그램은 다른 응용 프로그램보다 더 까다 롭습니다. Fetchablerecord가 필요한 지원을 정확히 제공하지 않으면 Beyond Fetchablerecord 장을 살펴보십시오.
TableRecord
Tableerecord 프로토콜은 귀하를 위해 SQL을 생성합니다.
protocol TableRecord {
static var databaseTableName : String { get }
static var databaseSelection : [ any SQLSelectable ] { get }
} databaseSelection 유형 속성은 선택 사항이며 요청 장에서 선택한 열에 문서화됩니다.
databaseTableName 유형 속성은 데이터베이스 테이블의 이름입니다. 기본적으로 유형 이름에서 파생됩니다.
struct Place : TableRecord { }
print ( Place . databaseTableName ) // prints "place"예를 들어:
placecountrypostalAddresshttpRequesttoefl여전히 사용자 정의 테이블 이름을 제공 할 수 있습니다.
struct Place : TableRecord {
static let databaseTableName = " location "
}
print ( Place . databaseTableName ) // prints "location"유형이 TableEcord와 FetchBarberecord를 모두 채택하면 쿼리 인터페이스를 사용하여 가져올 수 있습니다.
// SELECT * FROM place WHERE name = 'Paris'
let paris = try Place . filter ( nameColumn == " Paris " ) . fetchOne ( db )TableRecord는 또한 기본 및 고유 키를 가져올 수 있습니다. Key 및 레코드 존재 테스트로 가져 오기를 참조하십시오.
EncodableRecord , MutablePersistableRecord , PersistableRecord
GRDB 레코드 유형은 데이터베이스에서 행을 생성, 업데이트 및 삭제할 수 있습니다.
이러한 능력은 세 가지 프로토콜에 의해 부여됩니다.
// Defines how a record encodes itself into the database
protocol EncodableRecord {
/// Defines the values persisted in the database
func encode ( to container : inout PersistenceContainer ) throws
}
// Adds persistence methods
protocol MutablePersistableRecord : TableRecord , EncodableRecord {
/// Optional method that lets your adopting type store its rowID upon
/// successful insertion. Don't call it directly: it is called for you.
mutating func didInsert ( _ inserted : InsertionSuccess )
}
// Adds immutability
protocol PersistableRecord : MutablePersistableRecord {
/// Non-mutating version of the optional didInsert(_:)
func didInsert ( _ inserted : InsertionSuccess )
}예, 하나 대신 3 개의 프로토콜이 있습니다. 다음은 하나를 선택하는 방법입니다.
유형이 클래스 인 경우 PersistableRecord 선택하십시오. 또한 데이터베이스 테이블에 자동 증가 된 기본 키가있는 경우 didInsert(_:) 구현하십시오.
If your type is a struct, and the database table has an auto-incremented primary key , choose MutablePersistableRecord , and implement didInsert(_:) .
Otherwise , choose PersistableRecord , and ignore didInsert(_:) .
The encode(to:) method defines which values (Bool, Int, String, Date, Swift enums, etc.) are assigned to database columns.
The optional didInsert method lets the adopting type store its rowID after successful insertion, and is only useful for tables that have an auto-incremented primary key. It is called from a protected dispatch queue, and serialized with all database updates.
예를 들어:
extension Place : MutablePersistableRecord {
/// The values persisted in the database
func encode ( to container : inout PersistenceContainer ) {
container [ " id " ] = id
container [ " title " ] = title
container [ " latitude " ] = coordinate . latitude
container [ " longitude " ] = coordinate . longitude
}
// Update auto-incremented id upon successful insertion
mutating func didInsert ( _ inserted : InsertionSuccess ) {
id = inserted . rowID
}
}
var paris = Place (
id : nil ,
title : " Paris " ,
coordinate : CLLocationCoordinate2D ( latitude : 48.8534100 , longitude : 2.3488000 ) )
try paris . insert ( db )
paris . id // some valuePersistence containers also accept column enums:
extension Place : MutablePersistableRecord {
enum Columns : String , ColumnExpression {
case id , title , latitude , longitude
}
func encode ( to container : inout PersistenceContainer ) {
container [ Columns . id ] = id
container [ Columns . title ] = title
container [ Columns . latitude ] = coordinate . latitude
container [ Columns . longitude ] = coordinate . longitude
}
} When your record type adopts the standard Encodable protocol, you don't have to provide the implementation for encode(to:) . See Codable Records for more information:
// That's all
struct Player : Encodable , MutablePersistableRecord {
var id : Int64 ?
var name : String
var score : Int
// Update auto-incremented id upon successful insertion
mutating func didInsert ( _ inserted : InsertionSuccess ) {
id = inserted . rowID
}
}Types that adopt the PersistableRecord protocol are given methods that insert, update, and delete:
// INSERT
try place . insert ( db )
let insertedPlace = try place . inserted ( db ) // non-mutating
// UPDATE
try place . update ( db )
try place . update ( db , columns : [ " title " ] )
// Maybe UPDATE
try place . updateChanges ( db , from : otherPlace )
try place . updateChanges ( db ) { $0 . isFavorite = true }
// INSERT or UPDATE
try place . save ( db )
let savedPlace = place . saved ( db ) // non-mutating
// UPSERT
try place . upsert ( db )
let insertedPlace = place . upsertAndFetch ( db )
// DELETE
try place . delete ( db )
// EXISTENCE CHECK
let exists = try place . exists ( db )See Upsert below for more information about upserts.
The TableRecord protocol comes with batch operations :
// UPDATE
try Place . updateAll ( db , ... )
// DELETE
try Place . deleteAll ( db )
try Place . deleteAll ( db , ids : ... )
try Place . deleteAll ( db , keys : ... )
try Place . deleteOne ( db , id : ... )
try Place . deleteOne ( db , key : ... )For more information about batch updates, see Update Requests.
All persistence methods can throw a DatabaseError.
update and updateChanges throw RecordError if the database does not contain any row for the primary key of the record.
save makes sure your values are stored in the database. It performs an UPDATE if the record has a non-null primary key, and then, if no row was modified, an INSERT. It directly performs an INSERT if the record has no primary key, or a null primary key.
delete and deleteOne returns whether a database row was deleted or not. deleteAll returns the number of deleted rows. updateAll returns the number of updated rows. updateChanges returns whether a database row was updated or not.
All primary keys are supported , including composite primary keys that span several columns, and the hidden rowid column.
To customize persistence methods , you provide Persistence Callbacks, described below. Do not attempt at overriding the ready-made persistence methods.
UPSERT is an SQLite feature that causes an INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint (primary key or unique index).
Note : Upsert apis are available from SQLite 3.35.0+: iOS 15.0+, macOS 12.0+, tvOS 15.0+, watchOS 8.0+, or with a custom SQLite build or SQLCipher.
Note : With regard to persistence callbacks, an upsert behaves exactly like an insert. In particular: the
aroundInsert(_:)anddidInsert(_:)callbacks reports the rowid of the inserted or updated row;willUpdate,aroundUdate,didUdateare not called.
PersistableRecord provides three upsert methods:
upsert(_:)
Inserts or updates a record.
The upsert behavior is triggered by a violation of any uniqueness constraint on the table (primary key or unique index). In case of conflict, all columns but the primary key are overwritten with the inserted values:
struct Player : Encodable , PersistableRecord {
var id : Int64
var name : String
var score : Int
}
// INSERT INTO player (id, name, score)
// VALUES (1, 'Arthur', 1000)
// ON CONFLICT DO UPDATE SET
// name = excluded.name,
// score = excluded.score
let player = Player ( id : 1 , name : " Arthur " , score : 1000 )
try player . upsert ( db ) upsertAndFetch(_:onConflict:doUpdate:) (requires FetchableRecord conformance)
Inserts or updates a record, and returns the upserted record.
The onConflict and doUpdate arguments let you further control the upsert behavior. Make sure you check the SQLite UPSERT documentation for detailed information.
onConflict : the "conflict target" is the array of columns in the uniqueness constraint (primary key or unique index) that triggers the upsert.
If empty (the default), all uniqueness constraint are considered.
doUpdate : a closure that returns columns assignments to perform in case of conflict. Other columns are overwritten with the inserted values.
By default, all inserted columns but the primary key and the conflict target are overwritten.
In the example below, we upsert the new vocabulary word "jovial". It is inserted if that word is not already in the dictionary. Otherwise, count is incremented, isTainted is not overwritten, and kind is overwritten:
// CREATE TABLE vocabulary(
// word TEXT NOT NULL PRIMARY KEY,
// kind TEXT NOT NULL,
// isTainted BOOLEAN DEFAULT 0,
// count INT DEFAULT 1))
struct Vocabulary : Encodable , PersistableRecord {
var word : String
var kind : String
var isTainted : Bool
}
// INSERT INTO vocabulary(word, kind, isTainted)
// VALUES('jovial', 'adjective', 0)
// ON CONFLICT(word) DO UPDATE SET
// count = count + 1, -- on conflict, count is incremented
// kind = excluded.kind -- on conflict, kind is overwritten
// RETURNING *
let vocabulary = Vocabulary ( word : " jovial " , kind : " adjective " , isTainted : false )
let upserted = try vocabulary . upsertAndFetch (
db , onConflict : [ " word " ] ,
doUpdate : { _ in
[ Column ( " count " ) += 1 , // on conflict, count is incremented
Column ( " isTainted " ) . noOverwrite ] // on conflict, isTainted is NOT overwritten
} ) The doUpdate closure accepts an excluded TableAlias argument that refers to the inserted values that trigger the conflict. You can use it to specify an explicit overwrite, or to perform a computation. In the next example, the upsert keeps the maximum date in case of conflict:
// INSERT INTO message(id, text, date)
// VALUES(...)
// ON CONFLICT DO UPDATE SET
// text = excluded.text,
// date = MAX(date, excluded.date)
// RETURNING *
let upserted = try message . upsertAndFetch ( doUpdate : { excluded in
// keep the maximum date in case of conflict
[ Column ( " date " ) . set ( to : max ( Column ( " date " ) , excluded [ " date " ] ) ) ]
} ) upsertAndFetch(_:as:onConflict:doUpdate:) (does not require FetchableRecord conformance)
This method is identical to upsertAndFetch(_:onConflict:doUpdate:) described above, but you can provide a distinct FetchableRecord record type as a result, in order to specify the returned columns.
RETURNING clause SQLite is able to return values from a inserted, updated, or deleted row, with the RETURNING clause.
Note : Support for the
RETURNINGclause is available from SQLite 3.35.0+: iOS 15.0+, macOS 12.0+, tvOS 15.0+, watchOS 8.0+, or with a custom SQLite build or SQLCipher.
The RETURNING clause helps dealing with database features such as auto-incremented ids, default values, and generated columns. You can, for example, insert a few columns and fetch the default or generated ones in one step.
GRDB uses the RETURNING clause in all persistence methods that contain AndFetch in their name.
For example, given a database table with an auto-incremented primary key and a default score:
try dbQueue . write { db in
try db . execute ( sql : """
CREATE TABLE player(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
score INTEGER NOT NULL DEFAULT 1000)
""" )
}You can define a record type with full database information, and another partial record type that deals with a subset of columns:
// A player with full database information
struct Player : Codable , PersistableRecord , FetchableRecord {
var id : Int64
var name : String
var score : Int
}
// A partial player
struct PartialPlayer : Encodable , PersistableRecord {
static let databaseTableName = " player "
var name : String
}And now you can get a full player by inserting a partial one:
try dbQueue . write { db in
let partialPlayer = PartialPlayer ( name : " Alice " )
// INSERT INTO player (name) VALUES ('Alice') RETURNING *
let player = try partialPlayer . insertAndFetch ( db , as : Player . self )
print ( player . id ) // The inserted id
print ( player . name ) // The inserted name
print ( player . score ) // The default score
} For extra precision, you can select only the columns you need, and fetch the desired value from the provided prepared Statement :
try dbQueue . write { db in
let partialPlayer = PartialPlayer ( name : " Alice " )
// INSERT INTO player (name) VALUES ('Alice') RETURNING score
let score = try partialPlayer . insertAndFetch ( db , selection : [ Column ( " score " ) ] ) { statement in
try Int . fetchOne ( statement )
}
print ( score ) // Prints 1000, the default score
} There are other similar persistence methods, such as upsertAndFetch , saveAndFetch , updateAndFetch , updateChangesAndFetch , etc. They all behave like upsert , save , update , updateChanges , except that they return saved values. 예를 들어:
// Save and return the saved player
let savedPlayer = try player . saveAndFetch ( db ) See Persistence Methods, Upsert, and updateChanges methods for more information.
Batch operations can return updated or deleted values:
Warning : Make sure you check the documentation of the
RETURNINGclause, which describes important limitations and caveats for batch operations.
let request = Player . filter ( ... ) ...
// Fetch all deleted players
// DELETE FROM player RETURNING *
let deletedPlayers = try request . deleteAndFetchAll ( db ) // [Player]
// Fetch a selection of columns from the deleted rows
// DELETE FROM player RETURNING name
let statement = try request . deleteAndFetchStatement ( db , selection : [ Column ( " name " ) ] )
let deletedNames = try String . fetchSet ( statement )
// Fetch all updated players
// UPDATE player SET score = score + 10 RETURNING *
let updatedPlayers = try request . updateAndFetchAll ( db , [ Column ( " score " ) += 10 ] ) // [Player]
// Fetch a selection of columns from the updated rows
// UPDATE player SET score = score + 10 RETURNING score
let statement = try request . updateAndFetchStatement (
db , [ Column ( " score " ) += 10 ] ,
select : [ Column ( " score " ) ] )
let updatedScores = try Int . fetchAll ( statement )Your custom type may want to perform extra work when the persistence methods are invoked.
To this end, your record type can implement persistence callbacks . Callbacks are methods that get called at certain moments of a record's life cycle. With callbacks it is possible to write code that will run whenever an record is inserted, updated, or deleted.
In order to use a callback method, you need to provide its implementation. For example, a frequently used callback is didInsert , in the case of auto-incremented database ids:
struct Player : MutablePersistableRecord {
var id : Int64 ?
// Update auto-incremented id upon successful insertion
mutating func didInsert ( _ inserted : InsertionSuccess ) {
id = inserted . rowID
}
}
try dbQueue . write { db in
var player = Player ( id : nil , ... )
try player . insert ( db )
print ( player . id ) // didInsert was called: prints some non-nil id
}Callbacks can also help implementing record validation:
struct Link : PersistableRecord {
var url : URL
func willSave ( _ db : Database ) throws {
if url . host == nil {
throw ValidationError ( " url must be absolute. " )
}
}
}
try link . insert ( db ) // Calls the willSave callback
try link . update ( db ) // Calls the willSave callback
try link . save ( db ) // Calls the willSave callback
try link . upsert ( db ) // Calls the willSave callback Here is a list with all the available persistence callbacks, listed in the same order in which they will get called during the respective operations:
Inserting a record (all record.insert and record.upsert methods)
willSavearoundSavewillInsertaroundInsertdidInsertdidSave Updating a record (all record.update methods)
willSavearoundSavewillUpdatearoundUpdatedidUpdatedidSave Deleting a record (only the record.delete(_:) method)
willDeletearoundDeletedidDeleteFor detailed information about each callback, check the reference.
In the MutablePersistableRecord protocol, willInsert and didInsert are mutating methods. In PersistableRecord , they are not mutating.
Note : The
record.save(_:)method performs an UPDATE if the record has a non-null primary key, and then, if no row was modified, an INSERT. It directly performs an INSERT if the record has no primary key, or a null primary key. It triggers update and/or insert callbacks accordingly.Warning : Callbacks are only invoked from persistence methods called on record instances. Callbacks are not invoked when you call a type method, perform a batch operations, or execute raw SQL.
Warning : When a
did***callback is invoked, do not assume that the change is actually persisted on disk, because the database may still be inside an uncommitted transaction. When you need to handle transaction completions, use the afterNextTransaction(onCommit:onRollback:). 예를 들어:struct PictureFile : PersistableRecord { var path : String func willDelete ( _ db : Database ) { db . afterNextTransaction { _ in try ? deleteFileOnDisk ( ) } } }
When a record type maps a table with a single-column primary key, it is recommended to have it adopt the standard Identifiable protocol.
struct Player : Identifiable , FetchableRecord , PersistableRecord {
var id : Int64 // fulfills the Identifiable requirement
var name : String
var score : Int
} When id has a database-compatible type (Int64, Int, String, UUID, ...), the Identifiable conformance unlocks type-safe record and request methods:
let player = try Player . find ( db , id : 1 ) // Player
let player = try Player . fetchOne ( db , id : 1 ) // Player?
let players = try Player . fetchAll ( db , ids : [ 1 , 2 , 3 ] ) // [Player]
let players = try Player . fetchSet ( db , ids : [ 1 , 2 , 3 ] ) // Set<Player>
let request = Player . filter ( id : 1 )
let request = Player . filter ( ids : [ 1 , 2 , 3 ] )
try Player . deleteOne ( db , id : 1 )
try Player . deleteAll ( db , ids : [ 1 , 2 , 3 ] )Note : Not all record types can be made
Identifiable, and not all tables have a single-column primary key. GRDB provides other methods that deal with primary and unique keys, but they won't check the type of their arguments:// Available on non-Identifiable types try Player . fetchOne ( db , key : 1 ) try Player . fetchOne ( db , key : [ " email " : " [email protected] " ] ) try Country . fetchAll ( db , keys : [ " FR " , " US " ] ) try Citizenship . fetchOne ( db , key : [ " citizenId " : 1 , " countryCode " : " FR " ] ) let request = Player . filter ( key : 1 ) let request = Player . filter ( keys : [ 1 , 2 , 3 ] ) try Player . deleteOne ( db , key : 1 ) try Player . deleteAll ( db , keys : [ 1 , 2 , 3 ] )
Note : It is not recommended to use
Identifiableon record types that use an auto-incremented primary key:// AVOID declaring Identifiable conformance when key is auto-incremented struct Player { var id : Int64 ? // Not an id suitable for Identifiable var name : String var score : Int } extension Player : FetchableRecord , MutablePersistableRecord { // Update auto-incremented id upon successful insertion mutating func didInsert ( _ inserted : InsertionSuccess ) { id = inserted . rowID } }For a detailed rationale, please see issue #1435.
Some database tables have a single-column primary key which is not called "id":
try db . create ( table : " country " ) { t in
t . primaryKey ( " isoCode " , . text )
t . column ( " name " , . text ) . notNull ( )
t . column ( " population " , . integer ) . notNull ( )
} In this case, Identifiable conformance can be achieved, for example, by returning the primary key column from the id property:
struct Country : Identifiable , FetchableRecord , PersistableRecord {
var isoCode : String
var name : String
var population : Int
// Fulfill the Identifiable requirement
var id : String { isoCode }
}
let france = try dbQueue . read { db in
try Country . fetchOne ( db , id : " FR " )
} Record types that adopt an archival protocol (Codable, Encodable or Decodable) get free database support just by declaring conformance to the desired record protocols:
// Declare a record...
struct Player : Codable , FetchableRecord , PersistableRecord {
var name : String
var score : Int
}
// ...and there you go:
try dbQueue . write { db in
try Player ( name : " Arthur " , score : 100 ) . insert ( db )
let players = try Player . fetchAll ( db )
}Codable records encode and decode their properties according to their own implementation of the Encodable and Decodable protocols. Yet databases have specific requirements:
DatabaseValueConvertible protocol).For more information about Codable records, see:
Tip : see the Demo Applications for sample code that uses Codable records.
When a Codable record contains a property that is not a simple value (Bool, Int, String, Date, Swift enums, etc.), that value is encoded and decoded as a JSON string . 예를 들어:
enum AchievementColor : String , Codable {
case bronze , silver , gold
}
struct Achievement : Codable {
var name : String
var color : AchievementColor
}
struct Player : Codable , FetchableRecord , PersistableRecord {
var name : String
var score : Int
var achievements : [ Achievement ] // stored in a JSON column
}
try dbQueue . write { db in
// INSERT INTO player (name, score, achievements)
// VALUES (
// 'Arthur',
// 100,
// '[{"color":"gold","name":"Use Codable Records"}]')
let achievement = Achievement ( name : " Use Codable Records " , color : . gold )
let player = Player ( name : " Arthur " , score : 100 , achievements : [ achievement ] )
try player . insert ( db )
} GRDB uses the standard JSONDecoder and JSONEncoder from Foundation. By default, Data values are handled with the .base64 strategy, Date with the .millisecondsSince1970 strategy, and non conforming floats with the .throw strategy.
You can customize the JSON format by implementing those methods:
protocol FetchableRecord {
static func databaseJSONDecoder ( for column : String ) -> JSONDecoder
}
protocol EncodableRecord {
static func databaseJSONEncoder ( for column : String ) -> JSONEncoder
}Tip : Make sure you set the JSONEncoder
sortedKeysoption. This option makes sure that the JSON output is stable. This stability is required for Record Comparison to work as expected, and database observation tools such as ValueObservation to accurately recognize changed records.
By default, Codable Records store their values into database columns that match their coding keys: the teamID property is stored into the teamID column.
This behavior can be overridden, so that you can, for example, store the teamID property into the team_id column:
protocol FetchableRecord {
static var databaseColumnDecodingStrategy : DatabaseColumnDecodingStrategy { get }
}
protocol EncodableRecord {
static var databaseColumnEncodingStrategy : DatabaseColumnEncodingStrategy { get }
}See DatabaseColumnDecodingStrategy and DatabaseColumnEncodingStrategy to learn about all available strategies.
By default, Codable Records encode and decode their Data properties as blobs, and Date and UUID properties as described in the general Date and DateComponents and UUID chapters.
To sum up: dates encode themselves in the "YYYY-MM-DD HH:MM:SS.SSS" format, in the UTC time zone, and decode a variety of date formats and timestamps. UUIDs encode themselves as 16-bytes data blobs, and decode both 16-bytes data blobs and strings such as "E621E1F8-C36C-495A-93FC-0C247A3E6E5F".
Those behaviors can be overridden:
protocol FetchableRecord {
static func databaseDataDecodingStrategy ( for column : String ) -> DatabaseDataDecodingStrategy
static func databaseDateDecodingStrategy ( for column : String ) -> DatabaseDateDecodingStrategy
}
protocol EncodableRecord {
static func databaseDataEncodingStrategy ( for column : String ) -> DatabaseDataEncodingStrategy
static func databaseDateEncodingStrategy ( for column : String ) -> DatabaseDateEncodingStrategy
static func databaseUUIDEncodingStrategy ( for column : String ) -> DatabaseUUIDEncodingStrategy
}See DatabaseDataDecodingStrategy, DatabaseDateDecodingStrategy, DatabaseDataEncodingStrategy, DatabaseDateEncodingStrategy, and DatabaseUUIDEncodingStrategy to learn about all available strategies.
There is no customization of uuid decoding, because UUID can already decode all its encoded variants (16-bytes blobs and uuid strings, both uppercase and lowercase).
Customized coding strategies apply:
fetchOne(_:id:) , filter(id:) , deleteAll(_:keys:) , etc.They do not apply in other requests based on data, date, or uuid values.
So make sure that those are properly encoded in your requests. 예를 들어:
struct Player : Codable , FetchableRecord , PersistableRecord , Identifiable {
// UUIDs are stored as strings
static func databaseUUIDEncodingStrategy ( for column : String ) -> DatabaseUUIDEncodingStrategy {
. uppercaseString
}
var id : UUID
...
}
try dbQueue . write { db in
let uuid = UUID ( )
let player = Player ( id : uuid , ... )
// OK: inserts a player in the database, with a string uuid
try player . insert ( db )
// OK: performs a string-based query, finds the inserted player
_ = try Player . filter ( id : uuid ) . fetchOne ( db )
// NOT OK: performs a blob-based query, fails to find the inserted player
_ = try Player . filter ( Column ( " id " ) == uuid ) . fetchOne ( db )
// OK: performs a string-based query, finds the inserted player
_ = try Player . filter ( Column ( " id " ) == uuid . uuidString ) . fetchOne ( db )
} Your Codable Records can be stored in the database, but they may also have other purposes. In this case, you may need to customize their implementations of Decodable.init(from:) and Encodable.encode(to:) , depending on the context.
The standard way to provide such context is the userInfo dictionary. Implement those properties:
protocol FetchableRecord {
static var databaseDecodingUserInfo : [ CodingUserInfoKey : Any ] { get }
}
protocol EncodableRecord {
static var databaseEncodingUserInfo : [ CodingUserInfoKey : Any ] { get }
}For example, here is a Player type that customizes its decoding:
// A key that holds a decoder's name
let decoderName = CodingUserInfoKey ( rawValue : " decoderName " ) !
struct Player : FetchableRecord , Decodable {
init ( from decoder : Decoder ) throws {
// Print the decoder name
let decoderName = decoder . userInfo [ decoderName ] as? String
print ( " Decoded from ( decoderName ?? " unknown decoder " ) " )
...
}
}You can have a specific decoding from JSON...
// prints "Decoded from JSON"
let decoder = JSONDecoder ( )
decoder . userInfo = [ decoderName : " JSON " ]
let player = try decoder . decode ( Player . self , from : jsonData )... and another one from database rows:
extension Player : FetchableRecord {
static var databaseDecodingUserInfo : [ CodingUserInfoKey : Any ] {
[ decoderName : " database row " ]
}
}
// prints "Decoded from database row"
let player = try Player . fetchOne ( db , ... )Note : make sure the
databaseDecodingUserInfoanddatabaseEncodingUserInfoproperties are explicitly declared as[CodingUserInfoKey: Any]. If they are not, the Swift compiler may silently miss the protocol requirement, resulting in sticky empty userInfo.
Codable types are granted with a CodingKeys enum. You can use them to safely define database columns:
struct Player : Codable {
var id : Int64
var name : String
var score : Int
}
extension Player : FetchableRecord , PersistableRecord {
enum Columns {
static let id = Column ( CodingKeys . id )
static let name = Column ( CodingKeys . name )
static let score = Column ( CodingKeys . score )
}
}See the query interface and Recommended Practices for Designing Record Types for further information.
Records that adopt the EncodableRecord protocol can compare against other records, or against previous versions of themselves.
This helps avoiding costly UPDATE statements when a record has not been edited.
updateChanges MethodsdatabaseEquals MethoddatabaseChanges and hasDatabaseChanges MethodsupdateChanges Methods The updateChanges methods perform a database update of the changed columns only (and does nothing if record has no change).
updateChanges(_:from:)
This method lets you compare two records:
if let oldPlayer = try Player . fetchOne ( db , id : 42 ) {
var newPlayer = oldPlayer
newPlayer . score = 100
if try newPlayer . updateChanges ( db , from : oldPlayer ) {
print ( " player was modified, and updated in the database " )
} else {
print ( " player was not modified, and database was not hit " )
}
} updateChanges(_:modify:)
This method lets you update a record in place:
if var player = try Player . fetchOne ( db , id : 42 ) {
let modified = try player . updateChanges ( db ) {
$0 . score = 100
}
if modified {
print ( " player was modified, and updated in the database " )
} else {
print ( " player was not modified, and database was not hit " )
}
}databaseEquals MethodThis method returns whether two records have the same database representation:
let oldPlayer : Player = ...
var newPlayer : Player = ...
if newPlayer . databaseEquals ( oldPlayer ) == false {
try newPlayer . save ( db )
}Note : The comparison is performed on the database representation of records. As long as your record type adopts the EncodableRecord protocol, you don't need to care about Equatable.
databaseChanges and hasDatabaseChanges Methods databaseChanges(from:) returns a dictionary of differences between two records:
let oldPlayer = Player ( id : 1 , name : " Arthur " , score : 100 )
let newPlayer = Player ( id : 1 , name : " Arthur " , score : 1000 )
for (column , oldValue ) in try newPlayer . databaseChanges ( from : oldPlayer ) {
print ( " ( column ) was ( oldValue ) " )
}
// prints "score was 100"For an efficient algorithm which synchronizes the content of a database table with a JSON payload, check groue/SortedDifference.
GRDB records come with many default behaviors, that are designed to fit most situations. Many of those defaults can be customized for your specific needs:
player.insert(db)INSERT OR REPLACE queries, and generally define what happens when a persistence method violates a unique index.Player.fetchAll(db) .Codable Records have a few extra options:
Insertions and updates can create conflicts : for example, a query may attempt to insert a duplicate row that violates a unique index.
Those conflicts normally end with an error. Yet SQLite let you alter the default behavior, and handle conflicts with specific policies. For example, the INSERT OR REPLACE statement handles conflicts with the "replace" policy which replaces the conflicting row instead of throwing an error.
The five different policies are: abort (the default), replace, rollback, fail, and ignore.
SQLite let you specify conflict policies at two different places:
In the definition of the database table:
// CREATE TABLE player (
// id INTEGER PRIMARY KEY AUTOINCREMENT,
// email TEXT UNIQUE ON CONFLICT REPLACE
// )
try db . create ( table : " player " ) { t in
t . autoIncrementedPrimaryKey ( " id " )
t . column ( " email " , . text ) . unique ( onConflict : . replace ) // <--
}
// Despite the unique index on email, both inserts succeed.
// The second insert replaces the first row:
try db . execute ( sql : " INSERT INTO player (email) VALUES (?) " , arguments : [ " [email protected] " ] )
try db . execute ( sql : " INSERT INTO player (email) VALUES (?) " , arguments : [ " [email protected] " ] )In each modification query:
// CREATE TABLE player (
// id INTEGER PRIMARY KEY AUTOINCREMENT,
// email TEXT UNIQUE
// )
try db . create ( table : " player " ) { t in
t . autoIncrementedPrimaryKey ( " id " )
t . column ( " email " , . text ) . unique ( )
}
// Again, despite the unique index on email, both inserts succeed.
try db . execute ( sql : " INSERT OR REPLACE INTO player (email) VALUES (?) " , arguments : [ " [email protected] " ] )
try db . execute ( sql : " INSERT OR REPLACE INTO player (email) VALUES (?) " , arguments : [ " [email protected] " ] ) When you want to handle conflicts at the query level, specify a custom persistenceConflictPolicy in your type that adopts the PersistableRecord protocol. It will alter the INSERT and UPDATE queries run by the insert , update and save persistence methods:
protocol MutablePersistableRecord {
/// The policy that handles SQLite conflicts when records are
/// inserted or updated.
///
/// This property is optional: its default value uses the ABORT
/// policy for both insertions and updates, so that GRDB generate
/// regular INSERT and UPDATE queries.
static var persistenceConflictPolicy : PersistenceConflictPolicy { get }
}
struct Player : MutablePersistableRecord {
static let persistenceConflictPolicy = PersistenceConflictPolicy (
insert : . replace ,
update : . replace )
}
// INSERT OR REPLACE INTO player (...) VALUES (...)
try player . insert ( db )Note : If you specify the
ignorepolicy for inserts, thedidInsertcallback will be called with some random id in case of failed insert. You can detect failed insertions withinsertAndFetch:// How to detect failed `INSERT OR IGNORE`: // INSERT OR IGNORE INTO player ... RETURNING * do { let insertedPlayer = try player . insertAndFetch ( db ) { // Succesful insertion catch RecordError . recordNotFound { // Failed insertion due to IGNORE policy }Note : The
replacepolicy may have to delete rows so that inserts and updates can succeed. Those deletions are not reported to transaction observers (this might change in a future release of SQLite).
Some GRDB users eventually discover that the FetchableRecord protocol does not fit all situations. Use cases that are not well handled by FetchableRecord include:
Your application needs polymorphic row decoding: it decodes some type or another, depending on the values contained in a database row.
Your application needs to decode rows with a context: each decoded value should be initialized with some extra value that does not come from the database.
Since those use cases are not well handled by FetchableRecord, don't try to implement them on top of this protocol: you'll just fight the framework.
We will show below how to declare a record type for the following database table:
try dbQueue . write { db in
try db . create ( table : " place " ) { t in
t . autoIncrementedPrimaryKey ( " id " )
t . column ( " title " , . text ) . notNull ( )
t . column ( " isFavorite " , . boolean ) . notNull ( ) . defaults ( to : false )
t . column ( " longitude " , . double ) . notNull ( )
t . column ( " latitude " , . double ) . notNull ( )
}
}Each one of the three examples below is correct. You will pick one or the other depending on your personal preferences and the requirements of your application:
This is the shortest way to define a record type.
See the Record Protocols Overview, and Codable Records for more information.
struct Place : Codable {
var id : Int64 ?
var title : String
var isFavorite : Bool
private var latitude : CLLocationDegrees
private var longitude : CLLocationDegrees
var coordinate : CLLocationCoordinate2D {
get {
CLLocationCoordinate2D (
latitude : latitude ,
longitude : longitude )
}
set {
latitude = newValue . latitude
longitude = newValue . longitude
}
}
}
// SQL generation
extension Place : TableRecord {
/// The table columns
enum Columns {
static let id = Column ( CodingKeys . id )
static let title = Column ( CodingKeys . title )
static let isFavorite = Column ( CodingKeys . isFavorite )
static let latitude = Column ( CodingKeys . latitude )
static let longitude = Column ( CodingKeys . longitude )
}
}
// Fetching methods
extension Place : FetchableRecord { }
// Persistence methods
extension Place : MutablePersistableRecord {
// Update auto-incremented id upon successful insertion
mutating func didInsert ( _ inserted : InsertionSuccess ) {
id = inserted . rowID
}
}See the Record Protocols Overview for more information.
struct Place {
var id : Int64 ?
var title : String
var isFavorite : Bool
var coordinate : CLLocationCoordinate2D
}
// SQL generation
extension Place : TableRecord {
/// The table columns
enum Columns : String , ColumnExpression {
case id , title , isFavorite , latitude , longitude
}
}
// Fetching methods
extension Place : FetchableRecord {
/// Creates a record from a database row
init ( row : Row ) {
id = row [ Columns . id ]
title = row [ Columns . title ]
isFavorite = row [ Columns . isFavorite ]
coordinate = CLLocationCoordinate2D (
latitude : row [ Columns . latitude ] ,
longitude : row [ Columns . longitude ] )
}
}
// Persistence methods
extension Place : MutablePersistableRecord {
/// The values persisted in the database
func encode ( to container : inout PersistenceContainer ) {
container [ Columns . id ] = id
container [ Columns . title ] = title
container [ Columns . isFavorite ] = isFavorite
container [ Columns . latitude ] = coordinate . latitude
container [ Columns . longitude ] = coordinate . longitude
}
// Update auto-incremented id upon successful insertion
mutating func didInsert ( _ inserted : InsertionSuccess ) {
id = inserted . rowID
}
}This struct derives its persistence methods from the standard Encodable protocol (see Codable Records), but performs optimized row decoding by accessing database columns with numeric indexes.
See the Record Protocols Overview for more information.
struct Place : Encodable {
var id : Int64 ?
var title : String
var isFavorite : Bool
private var latitude : CLLocationDegrees
private var longitude : CLLocationDegrees
var coordinate : CLLocationCoordinate2D {
get {
CLLocationCoordinate2D (
latitude : latitude ,
longitude : longitude )
}
set {
latitude = newValue . latitude
longitude = newValue . longitude
}
}
}
// SQL generation
extension Place : TableRecord {
/// The table columns
enum Columns {
static let id = Column ( CodingKeys . id )
static let title = Column ( CodingKeys . title )
static let isFavorite = Column ( CodingKeys . isFavorite )
static let latitude = Column ( CodingKeys . latitude )
static let longitude = Column ( CodingKeys . longitude )
}
/// Arrange the selected columns and lock their order
static var databaseSelection : [ any SQLSelectable ] {
[
Columns . id ,
Columns . title ,
Columns . favorite ,
Columns . latitude ,
Columns . longitude ,
]
}
}
// Fetching methods
extension Place : FetchableRecord {
/// Creates a record from a database row
init ( row : Row ) {
// For high performance, use numeric indexes that match the
// order of Place.databaseSelection
id = row [ 0 ]
title = row [ 1 ]
isFavorite = row [ 2 ]
coordinate = CLLocationCoordinate2D (
latitude : row [ 3 ] ,
longitude : row [ 4 ] )
}
}
// Persistence methods
extension Place : MutablePersistableRecord {
// Update auto-incremented id upon successful insertion
mutating func didInsert ( _ inserted : InsertionSuccess ) {
id = inserted . rowID
}
}The query interface lets you write pure Swift instead of SQL:
try dbQueue . write { db in
// Update database schema
try db . create ( table : " wine " ) { t in ... }
// Fetch records
let wines = try Wine
. filter ( originColumn == " Burgundy " )
. order ( priceColumn )
. fetchAll ( db )
// Count
let count = try Wine
. filter ( colorColumn == Color . red )
. fetchCount ( db )
// Update
try Wine
. filter ( originColumn == " Burgundy " )
. updateAll ( db , priceColumn *= 0.75 )
// Delete
try Wine
. filter ( corkedColumn == true )
. deleteAll ( db )
}You need to open a database connection before you can query the database.
Please bear in mind that the query interface can not generate all possible SQL queries. You may also prefer writing SQL, and this is just OK. From little snippets to full queries, your SQL skills are welcome:
try dbQueue . write { db in
// Update database schema (with SQL)
try db . execute ( sql : " CREATE TABLE wine (...) " )
// Fetch records (with SQL)
let wines = try Wine . fetchAll ( db ,
sql : " SELECT * FROM wine WHERE origin = ? ORDER BY price " ,
arguments : [ " Burgundy " ] )
// Count (with an SQL snippet)
let count = try Wine
. filter ( sql : " color = ? " , arguments : [ Color . red ] )
. fetchCount ( db )
// Update (with SQL)
try db . execute ( sql : " UPDATE wine SET price = price * 0.75 WHERE origin = 'Burgundy' " )
// Delete (with SQL)
try db . execute ( sql : " DELETE FROM wine WHERE corked " )
}So don't miss the SQL API.
Note : the generated SQL may change between GRDB releases, without notice: don't have your application rely on any specific SQL output.
QueryInterfaceRequest , Table
The query interface requests let you fetch values from the database:
let request = Player . filter ( emailColumn != nil ) . order ( nameColumn )
let players = try request . fetchAll ( db ) // [Player]
let count = try request . fetchCount ( db ) // Int Query interface requests usually start from a type that adopts the TableRecord protocol:
struct Player : TableRecord { ... }
// The request for all players:
let request = Player . all ( )
let players = try request . fetchAll ( db ) // [Player] When you can not use a record type, use Table :
// The request for all rows from the player table:
let table = Table ( " player " )
let request = table . all ( )
let rows = try request . fetchAll ( db ) // [Row]
// The request for all players from the player table:
let table = Table < Player > ( " player " )
let request = table . all ( )
let players = try request . fetchAll ( db ) // [Player]Note : all examples in the documentation below use a record type, but you can always substitute a
Tableinstead.
Next, declare the table columns that you want to use for filtering, or sorting:
let idColumn = Column ( " id " )
let nameColumn = Column ( " name " )You can also declare column enums, if you prefer:
// Columns.id and Columns.name can be used just as
// idColumn and nameColumn declared above.
enum Columns : String , ColumnExpression {
case id
case name
} You can now build requests with the following methods: all , none , select , distinct , filter , matching , group , having , order , reversed , limit , joining , including , with . All those methods return another request, which you can further refine by applying another method: Player.select(...).filter(...).order(...) .
all() , none() : the requests for all rows, or no row.
// SELECT * FROM player
Player . all ( )By default, all columns are selected. See Columns Selected by a Request.
select(...) and select(..., as:) define the selected columns. See Columns Selected by a Request.
// SELECT name FROM player
Player . select ( nameColumn , as : String . self ) annotated(with: expression...) extends the selection.
// SELECT *, (score + bonus) AS total FROM player
Player . annotated ( with : ( scoreColumn + bonusColumn ) . forKey ( " total " ) ) annotated(with: aggregate) extends the selection with association aggregates.
// SELECT team.*, COUNT(DISTINCT player.id) AS playerCount
// FROM team
// LEFT JOIN player ON player.teamId = team.id
// GROUP BY team.id
Team . annotated ( with : Team . players . count ) annotated(withRequired: association) and annotated(withOptional: association) extends the selection with Associations.
// SELECT player.*, team.color
// FROM player
// JOIN team ON team.id = player.teamId
Player . annotated ( withRequired : Player . team . select ( colorColumn ) ) distinct() performs uniquing.
// SELECT DISTINCT name FROM player
Player . select ( nameColumn , as : String . self ) . distinct ( ) filter(expression) applies conditions.
// SELECT * FROM player WHERE id IN (1, 2, 3)
Player . filter ( [ 1 , 2 , 3 ] . contains ( idColumn ) )
// SELECT * FROM player WHERE (name IS NOT NULL) AND (height > 1.75)
Player . filter ( nameColumn != nil && heightColumn > 1.75 ) filter(id:) and filter(ids:) are type-safe methods available on Identifiable Records:
// SELECT * FROM player WHERE id = 1
Player . filter ( id : 1 )
// SELECT * FROM country WHERE isoCode IN ('FR', 'US')
Country . filter ( ids : [ " FR " , " US " ] ) filter(key:) and filter(keys:) apply conditions on primary and unique keys:
// SELECT * FROM player WHERE id = 1
Player . filter ( key : 1 )
// SELECT * FROM country WHERE isoCode IN ('FR', 'US')
Country . filter ( keys : [ " FR " , " US " ] )
// SELECT * FROM citizenship WHERE citizenId = 1 AND countryCode = 'FR'
Citizenship . filter ( key : [ " citizenId " : 1 , " countryCode " : " FR " ] )
// SELECT * FROM player WHERE email = '[email protected]'
Player . filter ( key : [ " email " : " [email protected] " ] ) matching(pattern) (FTS3, FTS5) performs full-text search.
// SELECT * FROM document WHERE document MATCH 'sqlite database'
let pattern = FTS3Pattern ( matchingAllTokensIn : " SQLite database " )
Document . matching ( pattern )When the pattern is nil, no row will match.
group(expression, ...) groups rows.
// SELECT name, MAX(score) FROM player GROUP BY name
Player
. select ( nameColumn , max ( scoreColumn ) )
. group ( nameColumn ) having(expression) applies conditions on grouped rows.
// SELECT team, MAX(score) FROM player GROUP BY team HAVING MIN(score) >= 1000
Player
. select ( teamColumn , max ( scoreColumn ) )
. group ( teamColumn )
. having ( min ( scoreColumn ) >= 1000 ) having(aggregate) applies conditions on grouped rows, according to an association aggregate.
// SELECT team.*
// FROM team
// LEFT JOIN player ON player.teamId = team.id
// GROUP BY team.id
// HAVING COUNT(DISTINCT player.id) >= 5
Team . having ( Team . players . count >= 5 ) order(ordering, ...) sorts.
// SELECT * FROM player ORDER BY name
Player . order ( nameColumn )
// SELECT * FROM player ORDER BY score DESC, name
Player . order ( scoreColumn . desc , nameColumn ) SQLite considers NULL values to be smaller than any other values for sorting purposes. Hence, NULLs naturally appear at the beginning of an ascending ordering and at the end of a descending ordering. With a custom SQLite build, this can be changed using .ascNullsLast and .descNullsFirst :
// SELECT * FROM player ORDER BY score ASC NULLS LAST
Player . order ( nameColumn . ascNullsLast ) Each order call clears any previous ordering:
// SELECT * FROM player ORDER BY name
Player . order ( scoreColumn ) . order ( nameColumn ) reversed() reverses the eventual orderings.
// SELECT * FROM player ORDER BY score ASC, name DESC
Player . order ( scoreColumn . desc , nameColumn ) . reversed ( )If no ordering was already specified, this method has no effect:
// SELECT * FROM player
Player . all ( ) . reversed ( ) limit(limit, offset: offset) limits and pages results.
// SELECT * FROM player LIMIT 5
Player . limit ( 5 )
// SELECT * FROM player LIMIT 5 OFFSET 10
Player . limit ( 5 , offset : 10 ) joining(required:) , joining(optional:) , including(required:) , including(optional:) , and including(all:) fetch and join records through Associations.
// SELECT player.*, team.*
// FROM player
// JOIN team ON team.id = player.teamId
Player . including ( required : Player . team ) with(cte) embeds a common table expression:
// WITH ... SELECT * FROM player
let cte = CommonTableExpression ( ... )
Player . with ( cte )Other requests that involve the primary key:
selectPrimaryKey(as:) selects the primary key.
// SELECT id FROM player
Player . selectPrimaryKey ( as : Int64 . self ) // QueryInterfaceRequest<Int64>
// SELECT code FROM country
Country . selectPrimaryKey ( as : String . self ) // QueryInterfaceRequest<String>
// SELECT citizenId, countryCode FROM citizenship
Citizenship . selectPrimaryKey ( as : Row . self ) // QueryInterfaceRequest<Row> orderByPrimaryKey() sorts by primary key.
// SELECT * FROM player ORDER BY id
Player . orderByPrimaryKey ( )
// SELECT * FROM country ORDER BY code
Country . orderByPrimaryKey ( )
// SELECT * FROM citizenship ORDER BY citizenId, countryCode
Citizenship . orderByPrimaryKey ( ) groupByPrimaryKey() groups rows by primary key.
You can refine requests by chaining those methods:
// SELECT * FROM player WHERE (email IS NOT NULL) ORDER BY name
Player . order ( nameColumn ) . filter ( emailColumn != nil ) The select , order , group , and limit methods ignore and replace previously applied selection, orderings, grouping, and limits. On the opposite, filter , matching , and having methods extend the query:
Player // SELECT * FROM player
. filter ( nameColumn != nil ) // WHERE (name IS NOT NULL)
. filter ( emailColumn != nil ) // AND (email IS NOT NULL)
. order ( nameColumn ) // - ignored -
. reversed ( ) // - ignored -
. order ( scoreColumn ) // ORDER BY score
. limit ( 20 , offset : 40 ) // - ignored -
. limit ( 10 ) // LIMIT 10Raw SQL snippets are also accepted, with eventual arguments:
// SELECT DATE(creationDate), COUNT(*) FROM player WHERE name = 'Arthur' GROUP BY date(creationDate)
Player
. select ( sql : " DATE(creationDate), COUNT(*) " )
. filter ( sql : " name = ? " , arguments : [ " Arthur " ] )
. group ( sql : " DATE(creationDate) " )By default, query interface requests select all columns:
// SELECT * FROM player
struct Player : TableRecord { ... }
let request = Player . all ( )
// SELECT * FROM player
let table = Table ( " player " )
let request = table . all ( )The selection can be changed for each individual requests, or in the case of record-based requests, for all requests built from this record type.
The select(...) and select(..., as:) methods change the selection of a single request (see Fetching from Requests for detailed information):
let request = Player . select ( max ( Column ( " score " ) ) )
let maxScore = try Int . fetchOne ( db , request ) // Int?
let request = Player . select ( max ( Column ( " score " ) ) , as : Int . self )
let maxScore = try request . fetchOne ( db ) // Int? The default selection for a record type is controlled by the databaseSelection property:
struct RestrictedPlayer : TableRecord {
static let databaseTableName = " player "
static var databaseSelection : [ any SQLSelectable ] { [ Column ( " id " ) , Column ( " name " ) ] }
}
struct ExtendedPlayer : TableRecord {
static let databaseTableName = " player "
static var databaseSelection : [ any SQLSelectable ] { [ AllColumns ( ) , Column . rowID ] }
}
// SELECT id, name FROM player
let request = RestrictedPlayer . all ( )
// SELECT *, rowid FROM player
let request = ExtendedPlayer . all ( )Note : make sure the
databaseSelectionproperty is explicitly declared as[any SQLSelectable]. If it is not, the Swift compiler may silently miss the protocol requirement, resulting in stickySELECT *requests. To verify your setup, see the How do I print a request as SQL? FAQ.
Feed requests with SQL expressions built from your Swift code:
SQLSpecificExpressible
GRDB comes with a Swift version of many SQLite built-in operators, listed below. But not all: see Embedding SQL in Query Interface Requests for a way to add support for missing SQL operators.
= , <> , < , <= , > , >= , IS , IS NOT
Comparison operators are based on the Swift operators == , != , === , !== , < , <= , > , >= :
// SELECT * FROM player WHERE (name = 'Arthur')
Player . filter ( nameColumn == " Arthur " )
// SELECT * FROM player WHERE (name IS NULL)
Player . filter ( nameColumn == nil )
// SELECT * FROM player WHERE (score IS 1000)
Player . filter ( scoreColumn === 1000 )
// SELECT * FROM rectangle WHERE width < height
Rectangle . filter ( widthColumn < heightColumn )Subqueries are supported:
// SELECT * FROM player WHERE score = (SELECT max(score) FROM player)
let maximumScore = Player . select ( max ( scoreColumn ) )
Player . filter ( scoreColumn == maximumScore )
// SELECT * FROM player WHERE score = (SELECT max(score) FROM player)
let maximumScore = SQLRequest ( " SELECT max(score) FROM player " )
Player . filter ( scoreColumn == maximumScore )Note : SQLite string comparison, by default, is case-sensitive and not Unicode-aware. See string comparison if you need more control.
* , / , + , -
SQLite arithmetic operators are derived from their Swift equivalent:
// SELECT ((temperature * 1.8) + 32) AS fahrenheit FROM planet
Planet . select ( ( temperatureColumn * 1.8 + 32 ) . forKey ( " fahrenheit " ) )Note : an expression like
nameColumn + "rrr"will be interpreted by SQLite as a numerical addition (with funny results), not as a string concatenation. See theconcatoperator below.
When you want to join a sequence of expressions with the + or * operator, use joined(operator:) :
// SELECT score + bonus + 1000 FROM player
let values = [
scoreColumn ,
bonusColumn ,
1000 . databaseValue ]
Player . select ( values . joined ( operator : . add ) ) Note in the example above how you concatenate raw values: 1000.databaseValue . A plain 1000 would not compile.
When the sequence is empty, joined(operator: .add) returns 0, and joined(operator: .multiply) returns 1.
& , | , ~ , << , >>
Bitwise operations (bitwise and, or, not, left shift, right shift) are derived from their Swift equivalent:
// SELECT mask & 2 AS isRocky FROM planet
Planet . select ( ( Column ( " mask " ) & 2 ) . forKey ( " isRocky " ) ) ||
Concatenate several strings:
// SELECT firstName || ' ' || lastName FROM player
Player . select ( [ firstNameColumn , " " . databaseValue , lastNameColumn ] . joined ( operator : . concat ) ) Note in the example above how you concatenate raw strings: " ".databaseValue . A plain " " would not compile.
When the sequence is empty, joined(operator: .concat) returns the empty string.
AND , OR , NOT
The SQL logical operators are derived from the Swift && , || 그리고 ! :
// SELECT * FROM player WHERE ((NOT verified) OR (score < 1000))
Player . filter ( !verifiedColumn || scoreColumn < 1000 ) When you want to join a sequence of expressions with the AND or OR operator, use joined(operator:) :
// SELECT * FROM player WHERE (verified AND (score >= 1000) AND (name IS NOT NULL))
let conditions = [
verifiedColumn ,
scoreColumn >= 1000 ,
nameColumn != nil ]
Player . filter ( conditions . joined ( operator : . and ) ) When the sequence is empty, joined(operator: .and) returns true, and joined(operator: .or) returns false:
// SELECT * FROM player WHERE 1
Player . filter ( [ ] . joined ( operator : . and ) )
// SELECT * FROM player WHERE 0
Player . filter ( [ ] . joined ( operator : . or ) ) BETWEEN , IN , NOT IN
To check inclusion in a Swift sequence (array, set, range…), call the contains method:
// SELECT * FROM player WHERE id IN (1, 2, 3)
Player . filter ( [ 1 , 2 , 3 ] . contains ( idColumn ) )
// SELECT * FROM player WHERE id NOT IN (1, 2, 3)
Player . filter ( ! [ 1 , 2 , 3 ] . contains ( idColumn ) )
// SELECT * FROM player WHERE score BETWEEN 0 AND 1000
Player . filter ( ( 0 ... 1000 ) . contains ( scoreColumn ) )
// SELECT * FROM player WHERE (score >= 0) AND (score < 1000)
Player . filter ( ( 0 ..< 1000 ) . contains ( scoreColumn ) )
// SELECT * FROM player WHERE initial BETWEEN 'A' AND 'N'
Player . filter ( ( " A " ... " N " ) . contains ( initialColumn ) )
// SELECT * FROM player WHERE (initial >= 'A') AND (initial < 'N')
Player . filter ( ( " A " ..< " N " ) . contains ( initialColumn ) ) To check inclusion inside a subquery, call the contains method as well:
// SELECT * FROM player WHERE id IN (SELECT playerId FROM playerSelection)
let selectedPlayerIds = PlayerSelection . select ( playerIdColumn )
Player . filter ( selectedPlayerIds . contains ( idColumn ) )
// SELECT * FROM player WHERE id IN (SELECT playerId FROM playerSelection)
let selectedPlayerIds = SQLRequest ( " SELECT playerId FROM playerSelection " )
Player . filter ( selectedPlayerIds . contains ( idColumn ) ) To check inclusion inside a common table expression, call the contains method as well:
// WITH selectedName AS (...)
// SELECT * FROM player WHERE name IN selectedName
let cte = CommonTableExpression ( named : " selectedName " , ... )
Player
. with ( cte )
. filter ( cte . contains ( nameColumn ) )Note : SQLite string comparison, by default, is case-sensitive and not Unicode-aware. See string comparison if you need more control.
EXISTS , NOT EXISTS
To check if a subquery would return rows, call the exists method:
// Teams that have at least one other player
//
// SELECT * FROM team
// WHERE EXISTS (SELECT * FROM player WHERE teamID = team.id)
let teamAlias = TableAlias ( )
let player = Player . filter ( Column ( " teamID " ) == teamAlias [ Column ( " id " ) ] )
let teams = Team . aliased ( teamAlias ) . filter ( player . exists ( ) )
// Teams that have no player
//
// SELECT * FROM team
// WHERE NOT EXISTS (SELECT * FROM player WHERE teamID = team.id)
let teams = Team . aliased ( teamAlias ) . filter ( !player . exists ( ) ) In the above example, you use a TableAlias in order to let a subquery refer to a column from another table.
In the next example, which involves the same table twice, the table alias requires an explicit disambiguation with TableAlias(name:) :
// Players who coach at least one other player
//
// SELECT coach.* FROM player coach
// WHERE EXISTS (SELECT * FROM player WHERE coachId = coach.id)
let coachAlias = TableAlias ( name : " coach " )
let coachedPlayer = Player . filter ( Column ( " coachId " ) == coachAlias [ Column ( " id " ) ] )
let coaches = Player . aliased ( coachAlias ) . filter ( coachedPlayer . exists ( ) )Finally, subqueries can also be expressed as SQL, with SQL Interpolation:
// SELECT coach.* FROM player coach
// WHERE EXISTS (SELECT * FROM player WHERE coachId = coach.id)
let coachedPlayer = SQLRequest ( " SELECT * FROM player WHERE coachId = ( coachAlias [ Column ( " id " ) ] ) " )
let coaches = Player . aliased ( coachAlias ) . filter ( coachedPlayer . exists ( ) ) LIKE
The SQLite LIKE operator is available as the like method:
// SELECT * FROM player WHERE (email LIKE '%@example.com')
Player . filter ( emailColumn . like ( " %@example.com " ) )
// SELECT * FROM book WHERE (title LIKE '%10%%' ESCAPE '')
Player . filter ( emailColumn . like ( " %10 \ %% " , escape : " \ " ) )Note : the SQLite LIKE operator is case-insensitive but not Unicode-aware. For example, the expression
'a' LIKE 'A'is true but'æ' LIKE 'Æ'is false.
MATCH
The full-text MATCH operator is available through FTS3Pattern (for FTS3 and FTS4 tables) and FTS5Pattern (for FTS5):
FTS3 and FTS4:
let pattern = FTS3Pattern ( matchingAllTokensIn : " SQLite database " )
// SELECT * FROM document WHERE document MATCH 'sqlite database'
Document . matching ( pattern )
// SELECT * FROM document WHERE content MATCH 'sqlite database'
Document . filter ( contentColumn . match ( pattern ) )FTS5:
let pattern = FTS5Pattern ( matchingAllTokensIn : " SQLite database " )
// SELECT * FROM document WHERE document MATCH 'sqlite database'
Document . matching ( pattern ) AS
To give an alias to an expression, use the forKey method:
// SELECT (score + bonus) AS total
// FROM player
Player . select ( ( Column ( " score " ) + Column ( " bonus " ) ) . forKey ( " total " ) )If you need to refer to this aliased column in another place of the request, use a detached column:
// SELECT (score + bonus) AS total
// FROM player
// ORDER BY total
Player
. select ( ( Column ( " score " ) + Column ( " bonus " ) ) . forKey ( " total " ) )
. order ( Column ( " total " ) . detached ) Unlike Column("total") , the detached column Column("total").detached is never associated to the "player" table, so it is always rendered as total in the generated SQL, even when the request involves other tables via an association or a common table expression.
SQLSpecificExpressible
GRDB comes with a Swift version of many SQLite built-in functions, listed below. But not all: see Embedding SQL in Query Interface Requests for a way to add support for missing SQL functions.
ABS , AVG , COALESCE , COUNT , DATETIME , JULIANDAY , LENGTH , MAX , MIN , SUM , TOTAL :
Those are based on the abs , average , coalesce , count , dateTime , julianDay , length , max , min , sum , and total Swift functions:
// SELECT MIN(score), MAX(score) FROM player
Player . select ( min ( scoreColumn ) , max ( scoreColumn ) )
// SELECT COUNT(name) FROM player
Player . select ( count ( nameColumn ) )
// SELECT COUNT(DISTINCT name) FROM player
Player . select ( count ( distinct : nameColumn ) )
// SELECT JULIANDAY(date, 'start of year') FROM game
Game . select ( julianDay ( dateColumn , . startOfYear ) ) For more information about the functions dateTime and julianDay , see Date And Time Functions.
CAST
Use the cast Swift function:
// SELECT (CAST(wins AS REAL) / games) AS successRate FROM player
Player . select ( ( cast ( winsColumn , as : . real ) / gamesColumn ) . forKey ( " successRate " ) )See CAST expressions for more information about SQLite conversions.
IFNULL
Use the Swift ?? 연산자:
// SELECT IFNULL(name, 'Anonymous') FROM player
Player . select ( nameColumn ?? " Anonymous " )
// SELECT IFNULL(name, email) FROM player
Player . select ( nameColumn ?? emailColumn ) LOWER , UPPER
The query interface does not give access to those SQLite functions. Nothing against them, but they are not unicode aware.
Instead, GRDB extends SQLite with SQL functions that call the Swift built-in string functions capitalized , lowercased , uppercased , localizedCapitalized , localizedLowercased and localizedUppercased :
Player . select ( nameColumn . uppercased ( ) )Note : When comparing strings, you'd rather use a collation:
let name : String = ... // Not recommended nameColumn . uppercased ( ) == name . uppercased ( ) // Better nameColumn . collating ( . caseInsensitiveCompare ) == name
Custom SQL functions and aggregates
You can apply your own custom SQL functions and aggregates:
let f = DatabaseFunction ( " f " , ... )
// SELECT f(name) FROM player
Player . select ( f . apply ( nameColumn ) ) You will sometimes want to extend your query interface requests with SQL snippets. This can happen because GRDB does not provide a Swift interface for some SQL function or operator, or because you want to use an SQLite construct that GRDB does not support.
Support for extensibility is large, but not unlimited. All the SQL queries built by the query interface request have the shape below. If you need something else, you'll have to use raw SQL requests.
WITH ... -- 1
SELECT ... -- 2
FROM ... -- 3
JOIN ... -- 4
WHERE ... -- 5
GROUP BY ... -- 6
HAVING ... -- 7
ORDER BY ... -- 8
LIMIT ... -- 9 WITH ... : see Common Table Expressions.
SELECT ...
The selection can be provided as raw SQL:
// SELECT IFNULL(name, 'O''Brien'), score FROM player
let request = Player . select ( sql : " IFNULL(name, 'O''Brien'), score " )
// SELECT IFNULL(name, 'O''Brien'), score FROM player
let defaultName = " O'Brien "
let request = Player . select ( sql : " IFNULL(name, ?), score " , arguments : [ suffix ] )The selection can be provided with SQL Interpolation:
// SELECT IFNULL(name, 'O''Brien'), score FROM player
let defaultName = " O'Brien "
let request = Player . select ( literal : " IFNULL(name, ( defaultName ) ), score " )The selection can be provided with a mix of Swift and SQL Interpolation:
// SELECT IFNULL(name, 'O''Brien') AS displayName, score FROM player
let defaultName = " O'Brien "
let displayName : SQL = " IFNULL( ( Column ( " name " ) ) , ( defaultName ) ) AS displayName "
let request = Player . select ( displayName , Column ( " score " ) ) When the custom SQL snippet should behave as a full-fledged expression, with support for the + Swift operator, the forKey aliasing method, and all other SQL Operators, build an expression literal with the SQL.sqlExpression method:
// SELECT IFNULL(name, 'O''Brien') AS displayName, score FROM player
let defaultName = " O'Brien "
let displayName = SQL ( " IFNULL( ( Column ( " name " ) ) , ( defaultName ) ) " ) . sqlExpression
let request = Player . select ( displayName . forKey ( " displayName " ) , Column ( " score " ) ) Such expression literals allow you to build a reusable support library of SQL functions or operators that are missing from the query interface. For example, you can define a Swift date function:
func date ( _ value : some SQLSpecificExpressible ) -> SQLExpression {
SQL ( " DATE( ( value ) ) " ) . sqlExpression
}
// SELECT * FROM "player" WHERE DATE("createdAt") = '2020-01-23'
let request = Player . filter ( date ( Column ( " createdAt " ) ) == " 2020-01-23 " ) See the Query Interface Organization for more information about SQLSpecificExpressible and SQLExpression .
FROM ... : only one table is supported here. You can not customize this SQL part.
JOIN ... : joins are fully controlled by Associations. You can not customize this SQL part.
WHERE ...
The WHERE clause can be provided as raw SQL:
// SELECT * FROM player WHERE score >= 1000
let request = Player . filter ( sql : " score >= 1000 " )
// SELECT * FROM player WHERE score >= 1000
let minScore = 1000
let request = Player . filter ( sql : " score >= ? " , arguments : [ minScore ] )The WHERE clause can be provided with SQL Interpolation:
// SELECT * FROM player WHERE score >= 1000
let minScore = 1000
let request = Player . filter ( literal : " score >= ( minScore ) " )The WHERE clause can be provided with a mix of Swift and SQL Interpolation:
// SELECT * FROM player WHERE (score >= 1000) AND (team = 'red')
let minScore = 1000
let scoreCondition : SQL = " ( Column ( " score " ) ) >= ( minScore ) "
let request = Player . filter ( scoreCondition && Column ( " team " ) == " red " ) See SELECT ... above for more SQL Interpolation examples.
GROUP BY ...
The GROUP BY clause can be provided as raw SQL, SQL Interpolation, or a mix of Swift and SQL Interpolation, just as the selection and the WHERE clause (see above).
HAVING ...
The HAVING clause can be provided as raw SQL, SQL Interpolation, or a mix of Swift and SQL Interpolation, just as the selection and the WHERE clause (see above).
ORDER BY ...
The ORDER BY clause can be provided as raw SQL, SQL Interpolation, or a mix of Swift and SQL Interpolation, just as the selection and the WHERE clause (see above).
In order to support the desc and asc query interface operators, and the reversed() query interface method, you must provide your orderings as expression literals with the SQL.sqlExpression method:
// SELECT * FROM "player"
// ORDER BY (score + bonus) ASC, name DESC
let total = SQL ( " (score + bonus) " ) . sqlExpression
let request = Player
. order ( total . desc , Column ( " name " ) )
. reversed ( ) LIMIT ... : use the limit(_:offset:) method. You can not customize this SQL part.
Once you have a request, you can fetch the records at the origin of the request:
// Some request based on `Player`
let request = Player . filter ( ... ) ... // QueryInterfaceRequest<Player>
// Fetch players:
try request . fetchCursor ( db ) // A Cursor of Player
try request . fetchAll ( db ) // [Player]
try request . fetchSet ( db ) // Set<Player>
try request . fetchOne ( db ) // Player?예를 들어:
let allPlayers = try Player . fetchAll ( db ) // [Player]
let arthur = try Player . filter ( nameColumn == " Arthur " ) . fetchOne ( db ) // Player? See fetching methods for information about the fetchCursor , fetchAll , fetchSet and fetchOne methods.
You sometimes want to fetch other values .
The simplest way is to use the request as an argument to a fetching method of the desired type:
// Fetch an Int
let request = Player . select ( max ( scoreColumn ) )
let maxScore = try Int . fetchOne ( db , request ) // Int?
// Fetch a Row
let request = Player . select ( min ( scoreColumn ) , max ( scoreColumn ) )
let row = try Row . fetchOne ( db , request ) ! // Row
let minScore = row [ 0 ] as Int ?
let maxScore = row [ 1 ] as Int ?You can also change the request so that it knows the type it has to fetch:
With asRequest(of:) , useful when you use Associations:
struct BookInfo : FetchableRecord , Decodable {
var book : Book
var author : Author
}
// A request of BookInfo
let request = Book
. including ( required : Book . author )
. asRequest ( of : BookInfo . self )
let bookInfos = try dbQueue . read { db in
try request . fetchAll ( db ) // [BookInfo]
} With select(..., as:) , which is handy when you change the selection:
// A request of Int
let request = Player . select ( max ( scoreColumn ) , as : Int . self )
let maxScore = try dbQueue . read { db in
try request . fetchOne ( db ) // Int?
} Fetching records according to their primary key is a common task.
Identifiable Records can use the type-safe methods find(_:id:) , fetchOne(_:id:) , fetchAll(_:ids:) and fetchSet(_:ids:) :
try Player . find ( db , id : 1 ) // Player
try Player . fetchOne ( db , id : 1 ) // Player?
try Country . fetchAll ( db , ids : [ " FR " , " US " ] ) // [Countries] All record types can use find(_:key:) , fetchOne(_:key:) , fetchAll(_:keys:) and fetchSet(_:keys:) that apply conditions on primary and unique keys:
try Player . find ( db , key : 1 ) // Player
try Player . fetchOne ( db , key : 1 ) // Player?
try Country . fetchAll ( db , keys : [ " FR " , " US " ] ) // [Country]
try Player . fetchOne ( db , key : [ " email " : " [email protected] " ] ) // Player?
try Citizenship . fetchOne ( db , key : [ " citizenId " : 1 , " countryCode " : " FR " ] ) // Citizenship? When the table has no explicit primary key, GRDB uses the hidden rowid column:
// SELECT * FROM document WHERE rowid = 1
try Document . fetchOne ( db , key : 1 ) // Document? When you want to build a request and plan to fetch from it later , use a filter method:
let request = Player . filter ( id : 1 )
let request = Country . filter ( ids : [ " FR " , " US " ] )
let request = Player . filter ( key : [ " email " : " [email protected] " ] )
let request = Citizenship . filter ( key : [ " citizenId " : 1 , " countryCode " : " FR " ] ) You can check if a request has matching rows in the database.
// Some request based on `Player`
let request = Player . filter ( ... ) ...
// Check for player existence:
let noSuchPlayer = try request . isEmpty ( db ) // BoolYou should check for emptiness instead of counting:
// Correct
let noSuchPlayer = try request . fetchCount ( db ) == 0
// Even better
let noSuchPlayer = try request . isEmpty ( db )You can also check if a given primary or unique key exists in the database.
Identifiable Records can use the type-safe method exists(_:id:) :
try Player . exists ( db , id : 1 )
try Country . exists ( db , id : " FR " ) All record types can use exists(_:key:) that can check primary and unique keys:
try Player . exists ( db , key : 1 )
try Country . exists ( db , key : " FR " )
try Player . exists ( db , key : [ " email " : " [email protected] " ] )
try Citizenship . exists ( db , key : [ " citizenId " : 1 , " countryCode " : " FR " ] )You should check for key existence instead of fetching a record and checking for nil:
// Correct
let playerExists = try Player . fetchOne ( db , id : 1 ) != nil
// Even better
let playerExists = try Player . exists ( db , id : 1 ) Requests can count. The fetchCount() method returns the number of rows that would be returned by a fetch request:
// SELECT COUNT(*) FROM player
let count = try Player . fetchCount ( db ) // Int
// SELECT COUNT(*) FROM player WHERE email IS NOT NULL
let count = try Player . filter ( emailColumn != nil ) . fetchCount ( db )
// SELECT COUNT(DISTINCT name) FROM player
let count = try Player . select ( nameColumn ) . distinct ( ) . fetchCount ( db )
// SELECT COUNT(*) FROM (SELECT DISTINCT name, score FROM player)
let count = try Player . select ( nameColumn , scoreColumn ) . distinct ( ) . fetchCount ( db )Other aggregated values can also be selected and fetched (see SQL Functions):
let request = Player . select ( max ( scoreColumn ) )
let maxScore = try Int . fetchOne ( db , request ) // Int?
let request = Player . select ( min ( scoreColumn ) , max ( scoreColumn ) )
let row = try Row . fetchOne ( db , request ) ! // Row
let minScore = row [ 0 ] as Int ?
let maxScore = row [ 1 ] as Int ? Requests can delete records , with the deleteAll() method:
// DELETE FROM player
try Player . deleteAll ( db )
// DELETE FROM player WHERE team = 'red'
try Player
. filter ( teamColumn == " red " )
. deleteAll ( db )
// DELETE FROM player ORDER BY score LIMIT 10
try Player
. order ( scoreColumn )
. limit ( 10 )
. deleteAll ( db )Note Deletion methods are available on types that adopt the TableRecord protocol, and
Table:struct Player : TableRecord { ... } try Player . deleteAll ( db ) // Fine try Table ( " player " ) . deleteAll ( db ) // Just as fine
Deleting records according to their primary key is a common task.
Identifiable Records can use the type-safe methods deleteOne(_:id:) and deleteAll(_:ids:) :
try Player . deleteOne ( db , id : 1 )
try Country . deleteAll ( db , ids : [ " FR " , " US " ] ) All record types can use deleteOne(_:key:) and deleteAll(_:keys:) that apply conditions on primary and unique keys:
try Player . deleteOne ( db , key : 1 )
try Country . deleteAll ( db , keys : [ " FR " , " US " ] )
try Player . deleteOne ( db , key : [ " email " : " [email protected] " ] )
try Citizenship . deleteOne ( db , key : [ " citizenId " : 1 , " countryCode " : " FR " ] ) When the table has no explicit primary key, GRDB uses the hidden rowid column:
// DELETE FROM document WHERE rowid = 1
try Document . deleteOne ( db , id : 1 ) // Document? Requests can batch update records . The updateAll() method accepts column assignments defined with the set(to:) method:
// UPDATE player SET score = 0, isHealthy = 1, bonus = NULL
try Player . updateAll ( db ,
Column ( " score " ) . set ( to : 0 ) ,
Column ( " isHealthy " ) . set ( to : true ) ,
Column ( " bonus " ) . set ( to : nil ) )
// UPDATE player SET score = 0 WHERE team = 'red'
try Player
. filter ( Column ( " team " ) == " red " )
. updateAll ( db , Column ( " score " ) . set ( to : 0 ) )
// UPDATE player SET top = 1 ORDER BY score DESC LIMIT 10
try Player
. order ( Column ( " score " ) . desc )
. limit ( 10 )
. updateAll ( db , Column ( " top " ) . set ( to : true ) )
// UPDATE country SET population = 67848156 WHERE id = 'FR'
try Country
. filter ( id : " FR " )
. updateAll ( db , Column ( " population " ) . set ( to : 67_848_156 ) )Column assignments accept any expression:
// UPDATE player SET score = score + (bonus * 2)
try Player . updateAll ( db , Column ( " score " ) . set ( to : Column ( " score " ) + Column ( " bonus " ) * 2 ) ) As a convenience, you can also use the += , -= , *= , or /= operators:
// UPDATE player SET score = score + (bonus * 2)
try Player . updateAll ( db , Column ( " score " ) += Column ( " bonus " ) * 2 )Default Conflict Resolution rules apply, and you may also provide a specific one:
// UPDATE OR IGNORE player SET ...
try Player . updateAll ( db , onConflict : . ignore , /* assignments... */ )Note The
updateAllmethod is available on types that adopt the TableRecord protocol, andTable:struct Player : TableRecord { ... } try Player . updateAll ( db , ... ) // Fine try Table ( " player " ) . updateAll ( db , ... ) // Just as fine
Until now, we have seen requests created from any type that adopts the TableRecord protocol:
let request = Player . all ( ) // QueryInterfaceRequest<Player> Those requests of type QueryInterfaceRequest can fetch and count:
try request . fetchCursor ( db ) // A Cursor of Player
try request . fetchAll ( db ) // [Player]
try request . fetchSet ( db ) // Set<Player>
try request . fetchOne ( db ) // Player?
try request . fetchCount ( db ) // IntWhen the query interface can not generate the SQL you need , you can still fallback to raw SQL:
// Custom SQL is always welcome
try Player . fetchAll ( db , sql : " SELECT ... " ) // [Player]But you may prefer to bring some elegance back in, and build custom requests:
// No custom SQL in sight
try Player . customRequest ( ) . fetchAll ( db ) // [Player]To build custom requests , you can use one of the built-in requests or derive requests from other requests.
SQLRequest is a fetch request built from raw SQL. 예를 들어:
extension Player {
static func filter ( color : Color ) -> SQLRequest < Player > {
SQLRequest < Player > (
sql : " SELECT * FROM player WHERE color = ? "
arguments : [ color ] )
}
}
// [Player]
try Player . filter ( color : . red ) . fetchAll ( db )SQLRequest supports SQL Interpolation:
extension Player {
static func filter ( color : Color ) -> SQLRequest < Player > {
" SELECT * FROM player WHERE color = ( color ) "
}
} The asRequest(of:) method changes the type fetched by the request. It is useful, for example, when you use Associations:
struct BookInfo : FetchableRecord , Decodable {
var book : Book
var author : Author
}
let request = Book
. including ( required : Book . author )
. asRequest ( of : BookInfo . self )
// [BookInfo]
try request . fetchAll ( db ) The adapted(_:) method eases the consumption of complex rows with row adapters. See RowAdapter and splittingRowAdapters(columnCounts:) for a sample code that uses adapted(_:) .
GRDB can encrypt your database with SQLCipher v3.4+.
Use CocoaPods, and specify in your Podfile :
# GRDB with SQLCipher 4
pod 'GRDB.swift/SQLCipher'
pod 'SQLCipher' , '~> 4.0'
# GRDB with SQLCipher 3
pod 'GRDB.swift/SQLCipher'
pod 'SQLCipher' , '~> 3.4' Make sure you remove any existing pod 'GRDB.swift' from your Podfile. GRDB.swift/SQLCipher must be the only active GRDB pod in your whole project, or you will face linker or runtime errors, due to the conflicts between SQLCipher and the system SQLite.
You create and open an encrypted database by providing a passphrase to your database connection:
var config = Configuration ( )
config . prepareDatabase { db in
try db . usePassphrase ( " secret " )
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config ) It is also in prepareDatabase that you perform other SQLCipher configuration steps that must happen early in the lifetime of a SQLCipher connection. 예를 들어:
var config = Configuration ( )
config . prepareDatabase { db in
try db . usePassphrase ( " secret " )
try db . execute ( sql : " PRAGMA cipher_page_size = ... " )
try db . execute ( sql : " PRAGMA kdf_iter = ... " )
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config ) When you want to open an existing SQLCipher 3 database with SQLCipher 4, you may want to run the cipher_compatibility pragma:
// Open an SQLCipher 3 database with SQLCipher 4
var config = Configuration ( )
config . prepareDatabase { db in
try db . usePassphrase ( " secret " )
try db . execute ( sql : " PRAGMA cipher_compatibility = 3 " )
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )See SQLCipher 4.0.0 Release and Upgrading to SQLCipher 4 for more information.
You can change the passphrase of an already encrypted database.
When you use a database queue, open the database with the old passphrase, and then apply the new passphrase:
try dbQueue . write { db in
try db . changePassphrase ( " newSecret " )
} When you use a database pool, make sure that no concurrent read can happen by changing the passphrase within the barrierWriteWithoutTransaction block. You must also ensure all future reads open a new database connection by calling the invalidateReadOnlyConnections method:
try dbPool . barrierWriteWithoutTransaction { db in
try db . changePassphrase ( " newSecret " )
dbPool . invalidateReadOnlyConnections ( )
}Note : When an application wants to keep on using a database queue or pool after the passphrase has changed, it is responsible for providing the correct passphrase to the
usePassphrasemethod called in the database preparation function. 고려하다:// WRONG: this won't work across a passphrase change let passphrase = try getPassphrase ( ) var config = Configuration ( ) config . prepareDatabase { db in try db . usePassphrase ( passphrase ) } // CORRECT: get the latest passphrase when it is needed var config = Configuration ( ) config . prepareDatabase { db in let passphrase = try getPassphrase ( ) try db . usePassphrase ( passphrase ) }
Note : The
DatabasePool.barrierWriteWithoutTransactionmethod does not prevent database snapshots from accessing the database during the passphrase change, or after the new passphrase has been applied to the database. Those database accesses may throw errors. Applications should provide their own mechanism for invalidating open snapshots before the passphrase is changed.
Note : Instead of changing the passphrase "in place" as described here, you can also export the database in a new encrypted database that uses the new passphrase. See Exporting a Database to an Encrypted Database.
Providing a passphrase won't encrypt a clear-text database that already exists, though. SQLCipher can't do that, and you will get an error instead: SQLite error 26: file is encrypted or is not a database .
Instead, create a new encrypted database, at a distinct location, and export the content of the existing database. This can both encrypt a clear-text database, or change the passphrase of an encrypted database.
The technique to do that is documented by SQLCipher.
With GRDB, it gives:
// The existing database
let existingDBQueue = try DatabaseQueue ( path : " /path/to/existing.db " )
// The new encrypted database, at some distinct location:
var config = Configuration ( )
config . prepareDatabase { db in
try db . usePassphrase ( " secret " )
}
let newDBQueue = try DatabaseQueue ( path : " /path/to/new.db " , configuration : config )
try existingDBQueue . inDatabase { db in
try db . execute (
sql : """
ATTACH DATABASE ? AS encrypted KEY ?;
SELECT sqlcipher_export('encrypted');
DETACH DATABASE encrypted;
""" ,
arguments : [ newDBQueue . path , " secret " ] )
}
// Now the export is completed, and the existing database can be deleted. It is recommended to avoid keeping the passphrase in memory longer than necessary. To do this, make sure you load the passphrase from the prepareDatabase method:
// NOT RECOMMENDED: this keeps the passphrase in memory longer than necessary
let passphrase = try getPassphrase ( )
var config = Configuration ( )
config . prepareDatabase { db in
try db . usePassphrase ( passphrase )
}
// RECOMMENDED: only load the passphrase when it is needed
var config = Configuration ( )
config . prepareDatabase { db in
let passphrase = try getPassphrase ( )
try db . usePassphrase ( passphrase )
}This technique helps manages the lifetime of the passphrase, although keep in mind that the content of a String may remain intact in memory long after the object has been released.
For even better control over the lifetime of the passphrase in memory, use a Data object which natively provides the resetBytes function.
// RECOMMENDED: only load the passphrase when it is needed and reset its content immediately after use
var config = Configuration ( )
config . prepareDatabase { db in
var passphraseData = try getPassphraseData ( ) // Data
defer {
passphraseData . resetBytes ( in : 0 ..< passphraseData . count )
}
try db . usePassphrase ( passphraseData )
}Some demanding users will want to go further, and manage the lifetime of the raw passphrase bytes. See below.
GRDB offers convenience methods for providing the database passphrases as Swift strings: usePassphrase(_:) and changePassphrase(_:) . Those methods don't keep the passphrase String in memory longer than necessary. But they are as secure as the standard String type: the lifetime of actual passphrase bytes in memory is not under control.
When you want to precisely manage the passphrase bytes, talk directly to SQLCipher, using its raw C functions.
예를 들어:
var config = Configuration ( )
config . prepareDatabase { db in
... // Carefully load passphrase bytes
let code = sqlite3_key ( db . sqliteConnection , /* passphrase bytes */ )
... // Carefully dispose passphrase bytes
guard code == SQLITE_OK else {
throw DatabaseError (
resultCode : ResultCode ( rawValue : code ) ,
message : db . lastErrorMessage )
}
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config ) When the passphrase is securely stored in the system keychain, your application can protect it using the kSecAttrAccessible attribute.
Such protection prevents GRDB from creating SQLite connections when the passphrase is not available:
var config = Configuration ( )
config . prepareDatabase { db in
let passphrase = try loadPassphraseFromSystemKeychain ( )
try db . usePassphrase ( passphrase )
}
// Success if and only if the passphrase is available
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )For the same reason, database pools, which open SQLite connections on demand, may fail at any time as soon as the passphrase becomes unavailable:
// Success if and only if the passphrase is available
let dbPool = try DatabasePool ( path : dbPath , configuration : config )
// May fail if passphrase has turned unavailable
try dbPool . read { ... }
// May trigger value observation failure if passphrase has turned unavailable
try dbPool . write { ... }Because DatabasePool maintains a pool of long-lived SQLite connections, some database accesses will use an existing connection, and succeed. And some other database accesses will fail, as soon as the pool wants to open a new connection. It is impossible to predict which accesses will succeed or fail.
For the same reason, a database queue, which also maintains a long-lived SQLite connection, will remain available even after the passphrase has turned unavailable.
Applications are thus responsible for protecting database accesses when the passphrase is unavailable. To this end, they can use Data Protection. They can also destroy their instances of database queue or pool when the passphrase becomes unavailable.
You can backup (copy) a database into another.
Backups can for example help you copying an in-memory database to and from a database file when you implement NSDocument subclasses.
let source : DatabaseQueue = ... // or DatabasePool
let destination : DatabaseQueue = ... // or DatabasePool
try source . backup ( to : destination ) The backup method blocks the current thread until the destination database contains the same contents as the source database.
When the source is a database pool, concurrent writes can happen during the backup. Those writes may, or may not, be reflected in the backup, but they won't trigger any error.
Database has an analogous backup method.
let source : DatabaseQueue = ... // or DatabasePool
let destination : DatabaseQueue = ... // or DatabasePool
try source . write { sourceDb in
try destination . barrierWriteWithoutTransaction { destDb in
try sourceDb . backup ( to : destDb )
}
} This method allows for the choice of source and destination Database handles with which to backup the database.
The backup methods take optional pagesPerStep and progress parameters. Together these parameters can be used to track a database backup in progress and abort an incomplete backup.
When pagesPerStep is provided, the database backup is performed in steps . At each step, no more than pagesPerStep database pages are copied from the source to the destination. The backup proceeds one step at a time until all pages have been copied.
When a progress callback is provided, progress is called after every backup step, including the last. Even if a non-default pagesPerStep is specified or the backup is otherwise completed in a single step, the progress callback will be called.
try source . backup (
to : destination ,
pagesPerStep : ... )
{ backupProgress in
print ( " Database backup progress: " , backupProgress )
} If a call to progress throws when backupProgress.isComplete == false , the backup will be aborted and the error rethrown. However, if a call to progress throws when backupProgress.isComplete == true , the backup is unaffected and the error is silently ignored.
Warning : Passing non-default values of
pagesPerSteporprogressto the backup methods is an advanced API intended to provide additional capabilities to expert users. GRDB's backup API provides a faithful, low-level wrapper to the underlying SQLite online backup API. GRDB's documentation is not a comprehensive substitute for the official SQLite documentation of their backup API.
The interrupt() method causes any pending database operation to abort and return at its earliest opportunity.
It can be called from any thread.
dbQueue . interrupt ( )
dbPool . interrupt ( ) A call to interrupt() that occurs when there are no running SQL statements is a no-op and has no effect on SQL statements that are started after interrupt() returns.
A database operation that is interrupted will throw a DatabaseError with code SQLITE_INTERRUPT . If the interrupted SQL operation is an INSERT, UPDATE, or DELETE that is inside an explicit transaction, then the entire transaction will be rolled back automatically. If the rolled back transaction was started by a transaction-wrapping method such as DatabaseWriter.write or Database.inTransaction , then all database accesses will throw a DatabaseError with code SQLITE_ABORT until the wrapping method returns.
예를 들어:
try dbQueue . write { db in
try Player ( ... ) . insert ( db ) // throws SQLITE_INTERRUPT
try Player ( ... ) . insert ( db ) // not executed
} // throws SQLITE_INTERRUPT
try dbQueue . write { db in
do {
try Player ( ... ) . insert ( db ) // throws SQLITE_INTERRUPT
} catch { }
} // throws SQLITE_ABORT
try dbQueue . write { db in
do {
try Player ( ... ) . insert ( db ) // throws SQLITE_INTERRUPT
} catch { }
try Player ( ... ) . insert ( db ) // throws SQLITE_ABORT
} // throws SQLITE_ABORT You can catch both SQLITE_INTERRUPT and SQLITE_ABORT errors:
do {
try dbPool . write { db in ... }
} catch DatabaseError . SQLITE_INTERRUPT , DatabaseError . SQLITE_ABORT {
// Oops, the database was interrupted.
}For more information, see Interrupt A Long-Running Query.
SQL injection is a technique that lets an attacker nuke your database.
https://xkcd.com/327/
Here is an example of code that is vulnerable to SQL injection:
// BAD BAD BAD
let id = 1
let name = textField . text
try dbQueue . write { db in
try db . execute ( sql : " UPDATE students SET name = ' ( name ) ' WHERE id = ( id ) " )
} If the user enters a funny string like Robert'; DROP TABLE students; -- , SQLite will see the following SQL, and drop your database table instead of updating a name as intended:
UPDATE students SET name = ' Robert ' ;
DROP TABLE students;
-- ' WHERE id = 1To avoid those problems, never embed raw values in your SQL queries . The only correct technique is to provide arguments to your raw SQL queries:
let name = textField . text
try dbQueue . write { db in
// Good
try db . execute (
sql : " UPDATE students SET name = ? WHERE id = ? " ,
arguments : [ name , id ] )
// Just as good
try db . execute (
sql : " UPDATE students SET name = :name WHERE id = :id " ,
arguments : [ " name " : name , " id " : id ] )
}When you use records and the query interface, GRDB always prevents SQL injection for you:
let id = 1
let name = textField . text
try dbQueue . write { db in
if var student = try Student . fetchOne ( db , id : id ) {
student . name = name
try student . update ( db )
}
} GRDB can throw DatabaseError, RecordError, or crash your program with a fatal error.
Considering that a local database is not some JSON loaded from a remote server, GRDB focuses on trusted databases . Dealing with untrusted databases requires extra care.
DatabaseError
DatabaseError are thrown on SQLite errors:
do {
try Pet ( masterId : 1 , name : " Bobby " ) . insert ( db )
} catch let error as DatabaseError {
// The SQLite error code: 19 (SQLITE_CONSTRAINT)
error . resultCode
// The extended error code: 787 (SQLITE_CONSTRAINT_FOREIGNKEY)
error . extendedResultCode
// The eventual SQLite message: FOREIGN KEY constraint failed
error . message
// The eventual erroneous SQL query
// "INSERT INTO pet (masterId, name) VALUES (?, ?)"
error . sql
// The eventual SQL arguments
// [1, "Bobby"]
error . arguments
// Full error description
// > SQLite error 19: FOREIGN KEY constraint failed -
// > while executing `INSERT INTO pet (masterId, name) VALUES (?, ?)`
error . description
}If you want to see statement arguments in the error description, make statement arguments public.
SQLite uses results codes to distinguish between various errors .
You can catch a DatabaseError and match on result codes:
do {
try ...
} catch let error as DatabaseError {
switch error {
case DatabaseError . SQLITE_CONSTRAINT_FOREIGNKEY :
// foreign key constraint error
case DatabaseError . SQLITE_CONSTRAINT :
// any other constraint error
default :
// any other database error
}
}You can also directly match errors on result codes:
do {
try ...
} catch DatabaseError . SQLITE_CONSTRAINT_FOREIGNKEY {
// foreign key constraint error
} catch DatabaseError . SQLITE_CONSTRAINT {
// any other constraint error
} catch {
// any other database error
} Each DatabaseError has two codes: an extendedResultCode (see extended result code), and a less precise resultCode (see primary result code). Extended result codes are refinements of primary result codes, as SQLITE_CONSTRAINT_FOREIGNKEY is to SQLITE_CONSTRAINT , for example.
Warning : SQLite has progressively introduced extended result codes across its versions. The SQLite release notes are unfortunately not quite clear about that: write your handling of extended result codes with care.
RecordError
RecordError is thrown by the PersistableRecord protocol when the update method could not find any row to update:
do {
try player . update ( db )
} catch let RecordError . recordNotFound ( databaseTableName : table , key : key ) {
print ( " Key ( key ) was not found in table ( table ) . " )
} RecordError is also thrown by the FetchableRecord protocol when the find method does not find any record:
do {
let player = try Player . find ( db , id : 42 )
} catch let RecordError . recordNotFound ( databaseTableName : table , key : key ) {
print ( " Key ( key ) was not found in table ( table ) . " )
}Fatal errors notify that the program, or the database, has to be changed.
They uncover programmer errors, false assumptions, and prevent misuses. 몇 가지 예는 다음과 같습니다.
The code asks for a non-optional value, when the database contains NULL:
// fatal error: could not convert NULL to String.
let name : String = row [ " name " ]Solution: fix the contents of the database, use NOT NULL constraints, or load an optional:
let name : String ? = row [ " name " ]Conversion from database value to Swift type fails:
// fatal error: could not convert "Mom’s birthday" to Date.
let date : Date = row [ " date " ]
// fatal error: could not convert "" to URL.
let url : URL = row [ " url " ]Solution: fix the contents of the database, or use DatabaseValue to handle all possible cases:
let dbValue : DatabaseValue = row [ " date " ]
if dbValue . isNull {
// Handle NULL
} else if let date = Date . fromDatabaseValue ( dbValue ) {
// Handle valid date
} else {
// Handle invalid date
}The database can't guarantee that the code does what it says:
// fatal error: table player has no unique index on column email
try Player . deleteOne ( db , key : [ " email " : " [email protected] " ] ) Solution: add a unique index to the player.email column, or use the deleteAll method to make it clear that you may delete more than one row:
try Player . filter ( Column ( " email " ) == " [email protected] " ) . deleteAll ( db )Database connections are not reentrant:
// fatal error: Database methods are not reentrant.
dbQueue . write { db in
dbQueue . write { db in
...
}
}Solution: avoid reentrancy, and instead pass a database connection along.
Let's consider the code below:
let sql = " SELECT ... "
// Some untrusted arguments for the query
let arguments : [ String : Any ] = ...
let rows = try Row . fetchCursor ( db , sql : sql , arguments : StatementArguments ( arguments ) )
while let row = try rows . next ( ) {
// Some untrusted database value:
let date : Date ? = row [ 0 ]
}It has two opportunities to throw fatal errors:
In such a situation, you can still avoid fatal errors by exposing and handling each failure point, one level down in the GRDB API:
// Untrusted arguments
if let arguments = StatementArguments ( arguments ) {
let statement = try db . makeStatement ( sql : sql )
try statement . setArguments ( arguments )
var cursor = try Row . fetchCursor ( statement )
while let row = try iterator . next ( ) {
// Untrusted database content
let dbValue : DatabaseValue = row [ 0 ]
if dbValue . isNull {
// Handle NULL
if let date = Date . fromDatabaseValue ( dbValue ) {
// Handle valid date
} else {
// Handle invalid date
}
}
} See Statement and DatabaseValue for more information.
SQLite can be configured to invoke a callback function containing an error code and a terse error message whenever anomalies occur.
This global error callback must be configured early in the lifetime of your application:
Database . logError = { ( resultCode , message ) in
NSLog ( " %@ " , " SQLite error ( resultCode ) : ( message ) " )
}Warning : Database.logError must be set before any database connection is opened. This includes the connections that your application opens with GRDB, but also connections opened by other tools, such as third-party libraries. Setting it after a connection has been opened is an SQLite misuse, and has no effect.
See The Error And Warning Log for more information.
SQLite lets you store unicode strings in the database.
However, SQLite does not provide any unicode-aware string transformations or comparisons.
The UPPER and LOWER built-in SQLite functions are not unicode-aware:
// "JéRôME"
try String . fetchOne ( db , sql : " SELECT UPPER('Jérôme') " ) GRDB extends SQLite with SQL functions that call the Swift built-in string functions capitalized , lowercased , uppercased , localizedCapitalized , localizedLowercased and localizedUppercased :
// "JÉRÔME"
let uppercased = DatabaseFunction . uppercase
try String . fetchOne ( db , sql : " SELECT ( uppercased . name ) ('Jérôme') " )Those unicode-aware string functions are also readily available in the query interface:
Player . select ( nameColumn . uppercased ) SQLite compares strings in many occasions: when you sort rows according to a string column, or when you use a comparison operator such as = and <= .
The comparison result comes from a collating function , or collation . SQLite comes with three built-in collations that do not support Unicode: binary, nocase, and rtrim.
GRDB comes with five extra collations that leverage unicode-aware comparisons based on the standard Swift String comparison functions and operators:
unicodeCompare (uses the built-in <= and == Swift operators)caseInsensitiveComparelocalizedCaseInsensitiveComparelocalizedComparelocalizedStandardCompareA collation can be applied to a table column. All comparisons involving this column will then automatically trigger the comparison function:
try db . create ( table : " player " ) { t in
// Guarantees case-insensitive email unicity
t . column ( " email " , . text ) . unique ( ) . collate ( . nocase )
// Sort names in a localized case insensitive way
t . column ( " name " , . text ) . collate ( . localizedCaseInsensitiveCompare )
}
// Players are sorted in a localized case insensitive way:
let players = try Player . order ( nameColumn ) . fetchAll ( db )Warning : SQLite requires host applications to provide the definition of any collation other than binary, nocase and rtrim. When a database file has to be shared or migrated to another SQLite library of platform (such as the Android version of your application), make sure you provide a compatible collation.
If you can't or don't want to define the comparison behavior of a column (see warning above), you can still use an explicit collation in SQL requests and in the query interface:
let collation = DatabaseCollation . localizedCaseInsensitiveCompare
let players = try Player . fetchAll ( db ,
sql : " SELECT * FROM player ORDER BY name COLLATE ( collation . name ) ) " )
let players = try Player . order ( nameColumn . collating ( collation ) ) . fetchAll ( db )You can also define your own collations :
let collation = DatabaseCollation ( " customCollation " ) { ( lhs , rhs ) -> NSComparisonResult in
// return the comparison of lhs and rhs strings.
}
// Make the collation available to a database connection
var config = Configuration ( )
config . prepareDatabase { db in
db . add ( collation : collation )
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config ) Both SQLite and GRDB use non-essential memory that help them perform better.
You can reclaim this memory with the releaseMemory method:
// Release as much memory as possible.
dbQueue . releaseMemory ( )
dbPool . releaseMemory ( )This method blocks the current thread until all current database accesses are completed, and the memory collected.
Warning : If
DatabasePool.releaseMemory()is called while a long read is performed concurrently, then no other read access will be possible until this long read has completed, and the memory has been released. If this does not suit your application needs, look for the asynchronous options below:
You can release memory in an asynchronous way as well:
// On a DatabaseQueue
dbQueue . asyncWriteWithoutTransaction { db in
db . releaseMemory ( )
}
// On a DatabasePool
dbPool . releaseMemoryEventually ( ) DatabasePool.releaseMemoryEventually() does not block the current thread, and does not prevent concurrent database accesses. In exchange for this convenience, you don't know when memory has been freed.
The iOS operating system likes applications that do not consume much memory.
Database queues and pools automatically free non-essential memory when the application receives a memory warning, and when the application enters background.
You can opt out of this automatic memory management:
var config = Configuration ( )
config . automaticMemoryManagement = false
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config ) // or DatabasePoolFAQ: Opening Connections
FAQ: SQL
FAQ: General
FAQ: Associations
FAQ: ValueObservation
FAQ: Errors
First choose a proper location for the database file. Document-based applications will let the user pick a location. Apps that use the database as a global storage will prefer the Application Support directory.
The sample code below creates or opens a database file inside its dedicated directory (a recommended practice). On the first run, a new empty database file is created. On subsequent runs, the database file already exists, so it just opens a connection:
// HOW TO create an empty database, or open an existing database file
// Create the "Application Support/MyDatabase" directory
let fileManager = FileManager . default
let appSupportURL = try fileManager . url (
for : . applicationSupportDirectory , in : . userDomainMask ,
appropriateFor : nil , create : true )
let directoryURL = appSupportURL . appendingPathComponent ( " MyDatabase " , isDirectory : true )
try fileManager . createDirectory ( at : directoryURL , withIntermediateDirectories : true )
// Open or create the database
let databaseURL = directoryURL . appendingPathComponent ( " db.sqlite " )
let dbQueue = try DatabaseQueue ( path : databaseURL . path )Open a read-only connection to your resource:
// HOW TO open a read-only connection to a database resource
// Get the path to the database resource.
if let dbPath = Bundle . main . path ( forResource : " db " , ofType : " sqlite " ) {
// If the resource exists, open a read-only connection.
// Writes are disallowed because resources can not be modified.
var config = Configuration ( )
config . readonly = true
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )
} else {
// The database resource can not be found.
// Fix your setup, or report the problem to the user.
} Database connections are automatically closed when DatabaseQueue or DatabasePool instances are deinitialized.
If the correct execution of your program depends on precise database closing, perform an explicit call to close() . This method may fail and create zombie connections, so please check its detailed documentation.
When you want to debug a request that does not deliver the expected results, you may want to print the SQL that is actually executed.
You can compile the request into a prepared Statement :
try dbQueue . read { db in
let request = Player . filter ( Column ( " email " ) == " [email protected] " )
let statement = try request . makePreparedRequest ( db ) . statement
print ( statement ) // SELECT * FROM player WHERE email = ?
print ( statement . arguments ) // ["[email protected]"]
}Another option is to setup a tracing function that prints out the executed SQL requests. For example, provide a tracing function when you connect to the database:
// Prints all SQL statements
var config = Configuration ( )
config . prepareDatabase { db in
db . trace { print ( $0 ) }
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )
try dbQueue . read { db in
// Prints "SELECT * FROM player WHERE email = ?"
let players = try Player . filter ( Column ( " email " ) == " [email protected] " ) . fetchAll ( db )
} If you want to see statement arguments such as '[email protected]' in the logged statements, make statement arguments public.
Note : the generated SQL may change between GRDB releases, without notice: don't have your application rely on any specific SQL output.
Use the trace(options:_:) method, with the .profile option:
var config = Configuration ( )
config . prepareDatabase { db in
db . trace ( options : . profile ) { event in
// Prints all SQL statements with their duration
print ( event )
// Access to detailed profiling information
if case let . profile ( statement , duration ) = event , duration > 0.5 {
print ( " Slow query: ( statement . sql ) " )
}
}
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )
try dbQueue . read { db in
let players = try Player . filter ( Column ( " email " ) == " [email protected] " ) . fetchAll ( db )
// Prints "0.003s SELECT * FROM player WHERE email = ?"
} If you want to see statement arguments such as '[email protected]' in the logged statements, make statement arguments public.
Since GRDB 1.0, all backwards compatibility guarantees of semantic versioning apply: no breaking change will happen until the next major version of the library.
There is an exception, though: experimental features , marked with the " EXPERIMENTAL " badge. Those are advanced features that are too young, or lack user feedback. They are not stabilized yet.
Those experimental features are not protected by semantic versioning, and may break between two minor releases of the library. To help them becoming stable, your feedback is greatly appreciated.
No, GRDB does not support library evolution and ABI stability. The only promise is API stability according to semantic versioning, with an exception for experimental features.
Yet, GRDB can be built with the "Build Libraries for Distribution" Xcode option ( BUILD_LIBRARY_FOR_DISTRIBUTION ), so that you can build binary frameworks at your convenience.
Let's say you have two record types, Book and Author , and you want to only fetch books that have an author, and discard anonymous books.
We start by defining the association between books and authors:
struct Book : TableRecord {
...
static let author = belongsTo ( Author . self )
}
struct Author : TableRecord {
...
}And then we can write our request and only fetch books that have an author, discarding anonymous ones:
let books : [ Book ] = try dbQueue . read { db in
// SELECT book.* FROM book
// JOIN author ON author.id = book.authorID
let request = Book . joining ( required : Book . author )
return try request . fetchAll ( db )
} Note how this request does not use the filter method. Indeed, we don't have any condition to express on any column. Instead, we just need to "require that a book can be joined to its author".
See How do I filter records and only keep those that are NOT associated to another record? below for the opposite question.
Let's say you have two record types, Book and Author , and you want to only fetch anonymous books that do not have any author.
We start by defining the association between books and authors:
struct Book : TableRecord {
...
static let author = belongsTo ( Author . self )
}
struct Author : TableRecord {
...
}And then we can write our request and only fetch anonymous books that don't have any author:
let books : [ Book ] = try dbQueue . read { db in
// SELECT book.* FROM book
// LEFT JOIN author ON author.id = book.authorID
// WHERE author.id IS NULL
let authorAlias = TableAlias ( )
let request = Book
. joining ( optional : Book . author . aliased ( authorAlias ) )
. filter ( !authorAlias . exists )
return try request . fetchAll ( db )
} This request uses a TableAlias in order to be able to filter on the eventual associated author. We make sure that the Author.primaryKey is nil, which is another way to say it does not exist: the book has no author.
See How do I filter records and only keep those that are associated to another record? above for the opposite question.
Let's say you have two record types, Book and Author , and you want to fetch all books with their author name, but not the full associated author records.
We start by defining the association between books and authors:
struct Book : Decodable , TableRecord {
...
static let author = belongsTo ( Author . self )
}
struct Author : Decodable , TableRecord {
...
enum Columns {
static let name = Column ( CodingKeys . name )
}
}And then we can write our request and the ad-hoc record that decodes it:
struct BookInfo : Decodable , FetchableRecord {
var book : Book
var authorName : String ? // nil when the book is anonymous
static func all ( ) -> QueryInterfaceRequest < BookInfo > {
// SELECT book.*, author.name AS authorName
// FROM book
// LEFT JOIN author ON author.id = book.authorID
let authorName = Author . Columns . name . forKey ( CodingKeys . authorName )
return Book
. annotated ( withOptional : Book . author . select ( authorName ) )
. asRequest ( of : BookInfo . self )
}
}
let bookInfos : [ BookInfo ] = try dbQueue . read { db in
BookInfo . all ( ) . fetchAll ( db )
} By defining the request as a static method of BookInfo, you have access to the private CodingKeys.authorName , and a compiler-checked SQL column name.
By using the annotated(withOptional:) method, you append the author name to the top-level selection that can be decoded by the ad-hoc record.
By using asRequest(of:) , you enhance the type-safety of your request.
Sometimes it looks that a ValueObservation does not notify the changes you expect.
There may be four possible reasons for this:
To answer the first two questions, look at SQL statements executed by the database. This is done when you open the database connection:
// Prints all SQL statements
var config = Configuration ( )
config . prepareDatabase { db in
db . trace { print ( " SQL: ( $0 ) " ) }
}
let dbQueue = try DatabaseQueue ( path : dbPath , configuration : config )If, after that, you are convinced that the expected changes were committed into the database, and not overwritten soon after, trace observation events:
let observation = ValueObservation
. tracking { db in ... }
. print ( ) // <- trace observation events
let cancellable = observation . start ( ... ) Look at the observation logs which start with cancel or failure : maybe the observation was cancelled by your app, or did fail with an error.
Look at the observation logs which start with value : make sure, again, that the expected value was not actually notified, then overwritten.
Finally, look at the observation logs which start with tracked region . Does the printed database region cover the expected changes?
예를 들어:
empty : The empty region, which tracks nothing and never triggers the observation.player(*) : The full player tableplayer(id,name) : The id and name columns of the player tableplayer(id,name)[1] : The id and name columns of the row with id 1 in the player tableplayer(*),team(*) : Both the full player and team tables If you happen to use the ValueObservation.trackingConstantRegion(_:) method and see a mismatch between the tracked region and your expectation, then change the definition of your observation by using tracking(_:) . You should witness that the logs which start with tracked region now evolve in order to include the expected changes, and that you get the expected notifications.
If after all those steps (thanks you!), your observation is still failing you, please open an issue and provide a minimal reproducible example!
You may get this error when using the read and write methods of database queues and pools:
// Generic parameter 'T' could not be inferred
let string = try dbQueue . read { db in
let result = try String . fetchOne ( db , ... )
return result
}This is a limitation of the Swift compiler.
The general workaround is to explicitly declare the type of the closure result:
// General Workaround
let string = try dbQueue . read { db -> String ? in
let result = try String . fetchOne ( db , ... )
return result
}You can also, when possible, write a single-line closure:
// Single-line closure workaround:
let string = try dbQueue . read { db in
try String . fetchOne ( db , ... )
} The insert and save persistence methods can trigger a compiler error in async contexts:
var player = Player ( id : nil , name : " Arthur " )
try await dbWriter . write { db in
// Error: Mutation of captured var 'player' in concurrently-executing code
try player . insert ( db )
}
print ( player . id ) // A non-nil id When this happens, prefer the inserted and saved methods instead:
// OK
var player = Player ( id : nil , name : " Arthur " )
player = try await dbWriter . write { [ player ] db in
return try player . inserted ( db )
}
print ( player . id ) // A non-nil idThis error message is self-explanatory: do check for misspelled or non-existing column names.
However, sometimes this error only happens when an app runs on a recent operating system (iOS 14+, Big Sur+, etc.) The error does not happen with previous ones.
When this is the case, there are two possible explanations:
Maybe a column name is really misspelled or missing from the database schema.
To find it, check the SQL statement that comes with the DatabaseError.
Maybe the application is using the character " instead of the single quote ' as the delimiter for string literals in raw SQL queries. Recent versions of SQLite have learned to tell about this deviation from the SQL standard, and this is why you are seeing this error .
For example: this is not standard SQL: UPDATE player SET name = "Arthur" .
The standard version is: UPDATE player SET name = 'Arthur' .
It just happens that old versions of SQLite used to accept the former, non-standard version. Newer versions are able to reject it with an error.
The fix is to change the SQL statements run by the application: replace " with ' in your string literals.
It may also be time to learn about statement arguments and SQL injection:
let name : String = ...
// NOT STANDARD (double quote)
try db . execute ( sql : """
UPDATE player SET name = " ( name ) "
""" )
// STANDARD, BUT STILL NOT RECOMMENDED (single quote)
try db . execute ( sql : " UPDATE player SET name = ' ( name ) ' " )
// STANDARD, AND RECOMMENDED (statement arguments)
try db . execute ( sql : " UPDATE player SET name = ? " , arguments : [ name ] )For more information, see Double-quoted String Literals Are Accepted, and Configuration.acceptsDoubleQuotedStringLiterals.
Those errors may be the sign that SQLite can't access the database due to data protection.
When your application should be able to run in the background on a locked device, it has to catch this error, and, for example, wait for UIApplicationDelegate.applicationProtectedDataDidBecomeAvailable(_:) or UIApplicationProtectedDataDidBecomeAvailable notification and retry the failed database operation.
do {
try ...
} catch DatabaseError . SQLITE_IOERR , DatabaseError . SQLITE_AUTH {
// Handle possible data protection error
}This error can also be prevented altogether by using a more relaxed file protection.
You may get the error "wrong number of statement arguments" when executing a LIKE query similar to:
let name = textField . text
let players = try dbQueue . read { db in
try Player . fetchAll ( db , sql : " SELECT * FROM player WHERE name LIKE '%?%' " , arguments : [ name ] )
} The problem lies in the '%?%' pattern.
SQLite only interprets ? as a parameter when it is a placeholder for a whole value (int, double, string, blob, null). In this incorrect query, ? is just a character in the '%?%' string: it is not a query parameter, and is not processed in any way. See https://www.sqlite.org/lang_expr.html#varparam for more information about SQLite parameters.
To fix the error, you can feed the request with the pattern itself, instead of the name:
let name = textField . text
let players : [ Player ] = try dbQueue . read { db in
let pattern = " % ( name ) % "
return try Player . fetchAll ( db , sql : " SELECT * FROM player WHERE name LIKE ? " , arguments : [ pattern ] )
}GRDB.xcworkspace : it contains GRDB-enabled playgrounds to play with.감사해요
URIs don't change: people change them.
This chapter was renamed to Embedding SQL in Query Interface Requests.
This chapter has moved.
This chapter has moved.
This chapter has moved.
This chapter has been renamed Record Comparison.
This chapter has moved.
Custom Value Types conform to the DatabaseValueConvertible protocol.
This chapter has been renamed Beyond FetchableRecord.
This chapter was replaced with Persistence Callbacks.
This chapter has moved.
This chapter has moved.
This chapter has moved.
This chapter has moved.
This chapter has moved.
This chapter was removed. See the references of DatabaseReader and DatabaseWriter.
This chapter has been renamed Data, Date, and UUID Coding Strategies.
This chapter has been superseded by the Sharing a Database guide.
This chapter has moved.
FTS5 is enabled by default since GRDB 6.7.0.
FetchedRecordsController has been removed in GRDB 5.
The Database Observation chapter describes the other ways to observe the database.
This chapter has moved.
This chapter has moved.
This chapter was replaced with the documentation of splittingRowAdapters(columnCounts:).
See Records and the Query Interface.
This chapter has moved.
This chapter has moved.
This protocol has been renamed PersistableRecord in GRDB 3.0.
This error was renamed to RecordError.
This chapter has moved.
The Record class is a legacy GRDB type. Since GRDB 7, it is not recommended to define record types by subclassing the Record class.
This chapter has moved.
This protocol has been renamed FetchableRecord in GRDB 3.0.
This protocol has been renamed TableRecord in GRDB 3.0.
This chapter has moved.
This chapter has moved.
This chapter has moved.
This chapter has moved.
This chapter has moved.
This chapter has been superseded by ValueObservation and DatabaseRegionObservation.
