From Kotlin to Swift: Practical Usage Examples
Features
Migrations
Secure Dynamic SQL Generator: Safeguarding Against Injection Attacks
Insert
Update
Delete
Select
Coroutines & Tasks for Asynchronous Programming in SQL
Operators
column.isNotNull()
column.isNull()
column.eq(value: T) -> column = ?
column.neq(value: T) -> column != ?
column.gt(value: T) -> column > ?
column.lt(value: T) -> column < ?
column.gte(value: T) -> column >= ?
column.lte(value: T) -> column <= ?
column.like(value: String) -> column LIKE ?
column.in(values: T) -> column IN (?, ?...)
Aggregation
column.count() -> COUNT(column)
column.sum() -> SUM(column)
column.min() -> MIN(column)
column.max() -> MAX(column)
column.avg() -> AVG(column)
Options
Columns
Joins
Inner
Left
Right
Cross
Where *
Group By
Having *
Order By
Limit
Offset
* Due to syntax limitations, use and / or for Kotlin and && / || for Swift
Kotlin
Database and Migrations
class GradeHub(context: Context) : OmniSQLite(context, dbName = "GradeHub.sqlite", dbVersion = 2) {
override fun setupQueries(): List<String> =
listOf(
"CREATE TABLE Student (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, number INTEGER);",
"CREATE TABLE Grades (id INTEGER PRIMARY KEY AUTOINCREMENT, studentId INTEGER, grade REAL, FOREIGN KEY (studentId) REFERENCES Student(id));"
)
override fun upgradeQueries(): Map<Int, List<String>> =
mapOf(
2 to listOf(
"CREATE TABLE Grades (id INTEGER PRIMARY KEY AUTOINCREMENT, studentId INTEGER, grade REAL, FOREIGN KEY (studentId) REFERENCES Student(id));"
)
)
override fun downgradeQueries(): Map<Int, List<String>> =
mapOf(
2 to listOf(
"DROP TABLE Grades;"
)
)
}
Tables
object Student : OmniSQLTable {
override val tableName: String = "Student"
val id = integerColumn(columnName = "id")
val name = textColumn(columnName = "name")
val number = integerColumn(columnName = "number")
}
object Grades : OmniSQLTable {
override val tableName: String = "Grades"
val id = integerColumn(columnName = "id")
val grade = realColumn(columnName = "grade")
val studentId = integerColumn(columnName = "studentId")
}
Insert
val gradeHubDB = GradeHub(context)
val studentId = gradeHubDB.insert(Student) { columns ->
columns[Student.name] = "Claudio"
columns[Student.number] = 1
}
Update
val gradeHubDB = GradeHub(context)
gradeHubDB.update(Student, { Student.name.like("Claudio") }) { columns ->
columns[Student.number] = 2
}
Delete
val gradeHubDB = GradeHub(context)
gradeHubDB.delete(Student, { Student.name.like("Claudio") })
Select
val gradeHubDB = GradeHub(context)
gradeHubDB.query {
Student.select(Student.name, Grades.grade)
.innerJoin(Grades, on = Student.id, equal = Grades.studentId)
.where {
Grades.grade.gte(2.0) and Grades.grade.lte(3.0)
}
.order(Student.name, type = OmniSQLOrderBy.ASC)
.limit(10)
.offset(5)
}.forEach { resultRow ->
val name = resultRow[Student.name]
val grade = resultRow[Grades.grade]
}
Aggregate
val gradeHubDB = GradeHub(context)
gradeHubDB.query {
Student.select(Student.name, Grades.grade.sum())
.innerJoin(Grades, on = Student.id, equal = Grades.studentId)
.groupBy(Student.name)
.having {
Grades.grade.sum().gte(2.0) and Grades.grade.sum().lte(3.0)
}
}.forEach { resultRow ->
val name = resultRow[Student.name]
val grade = resultRow[Grades.grade.sum()]
}
Exceptions
sealed class OmniSQLError(message: String) : Throwable(message) {
class Error(message: String) : OmniSQLError(message)
class ErrorOnQuery(message: String) : OmniSQLError(message)
class ErrorOnUpdate(message: String) : OmniSQLError(message)
class ErrorOnInsert(message: String) : OmniSQLError(message)
class ErrorOnDelete(message: String) : OmniSQLError(message)
}
Swift
Database and Migrations
class GradeHub : OmniSQLite {
init () throws {
try super.init(dbName: "GradeHub.sqlite", dbVersion: 2)
}
override func setupQueries() -> [String] {
[
"CREATE TABLE Student (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, number INTEGER);",
"CREATE TABLE Grades (id INTEGER PRIMARY KEY AUTOINCREMENT, studentId INTEGER, grade REAL, FOREIGN KEY (studentId) REFERENCES student(id));"
]
}
override func upgradeQueries() -> [Int32 : [String]] {
[
2 : [
"CREATE TABLE Grades (id INTEGER PRIMARY KEY AUTOINCREMENT, studentId INTEGER, grade REAL, FOREIGN KEY (studentId) REFERENCES student(id));"
]
]
}
override func downgradeQueries() -> [Int32 : [String]] {
[
2 : [
"DROP TABLE studentGrades;"
]
]
}
}
Tables
struct Student : OmniSQLTable {
static let tableName: String = "Student"
static let id = integerColumn(columnName: "id")
static let name = textColumn(columnName: "name")
static let number = integerColumn(columnName: "number")
}
struct Grades : OmniSQLTable {
static let tableName: String = "Grades"
static let id = integerColumn(columnName: "id")
static let grade = realColumn(columnName: "grade")
static let studentId = integerColumn(columnName: "studentId")
}
Insert
let gradeHubDB = try GradeHub()
let studentId = try await gradeHubDB.insert(Student.self) { columns in
columns[Student.name] = "Claudio"
columns[Student.number] = 1
}
Update
let gradeHubDB = try GradeHub()
try await gradeHubDB.update(Student.self, { Student.name.like("Claudio") }) { columns in
columns[Student.number] = 2
}
Delete
let gradeHubDB = try GradeHub()
try await gradeHubDB.delete(Student.self, { Student.name.like("Claudio") })
Select
let gradeHubDB = try GradeHub()
try await gradeHubDB.query {
Student.select(Student.name, Grades.grade)
.innerJoin(Grades.self, on: Student.id, equal: Grades.studentId)
.where {
Grades.grade.gte(2.0) && Grades.grade.lte(3.0)
}
.order(Student.name, type: OmniSQLOrderBy.ASC)
.limit(10)
.offset(5)
}.forEach { resultRow in
let name = resultRow[Student.name]
let grade = resultRow[Grades.grade]
}
Aggregate
let gradeHubDB = try GradeHub()
try await gradeHubDB.query {
Student.select(Student.name, Grades.grade.sum())
.innerJoin(Grades.self, on: Student.id, equal: Grades.studentId)
.groupBy(Student.name)
.having {
Grades.grade.sum().gte(2.0) && Grades.grade.sum().lte(3.0)
}
}.forEach { resultRow in
let name = resultRow[Student.name]
let grade = resultRow[Grades.grade.sum()]
}
Exceptions
public enum OmniSQLError: Error {
case error(String)
case errorOnQuery(String)
case errorOnUpdate(String)
case errorOnInsert(String)
case errorOnDelete(String)
}