From a26e3f61ad687342d37499c0e5ef3d126d3b5dd3 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Wed, 9 Feb 2022 11:40:24 +0800 Subject: [PATCH 01/13] feature(SQLite): InsertOrUpdate --- .../ktorm/support/sqlite/InsertOrUpdate.kt | 447 ++++++++++++++++++ .../org/ktorm/support/sqlite/SQLiteDialect.kt | 34 ++ 2 files changed, 481 insertions(+) create mode 100644 ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt new file mode 100644 index 00000000..6b9b0356 --- /dev/null +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt @@ -0,0 +1,447 @@ +/* + * Copyright 2018-2021 the original author or authors. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.ktorm.support.sqlite + +import org.ktorm.database.CachedRowSet +import org.ktorm.database.Database +import org.ktorm.dsl.AssignmentsBuilder +import org.ktorm.dsl.KtormDsl +import org.ktorm.expression.ColumnAssignmentExpression +import org.ktorm.expression.ColumnExpression +import org.ktorm.expression.SqlExpression +import org.ktorm.expression.TableExpression +import org.ktorm.schema.BaseTable +import org.ktorm.schema.Column + +/** + * Insert or update expression, represents an insert statement with an + * `on conflict (key) do update set` clause in PostgreSQL. + * + * @property table the table to be inserted. + * @property assignments the inserted column assignments. + * @property conflictColumns the index columns on which the conflict may happen. + * @property updateAssignments the updated column assignments while any key conflict exists. + * @property returningColumns the returning columns. + */ +public data class InsertOrUpdateExpression( + val table: TableExpression, + val assignments: List>, + val conflictColumns: List> = emptyList(), + val updateAssignments: List> = emptyList(), + val returningColumns: List> = emptyList(), + override val isLeafNode: Boolean = false, + override val extraProperties: Map = emptyMap() +) : SqlExpression() + +/** + * Insert a record to the table, determining if there is a key conflict while it's being inserted, and automatically + * performs an update if any conflict exists. + * + * Usage: + * + * ```kotlin + * database.insertOrUpdate(Employees) { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = t_employee.salary + ? + * ``` + * + * @since 2.7 + * @param table the table to be inserted. + * @param block the DSL block used to construct the expression. + * @return the effected row count. + */ +public fun > Database.insertOrUpdate( + table: T, block: InsertOrUpdateStatementBuilder.(T) -> Unit +): Int { + val expression = buildInsertOrUpdateExpression(table, returning = emptyList(), block = block) + return executeUpdate(expression) +} + +/** + * Insert a record to the table, determining if there is a key conflict while it's being inserted, automatically + * performs an update if any conflict exists, and finally returns the specific columns. + * + * Usage: + * + * ```kotlin + * val (id, job) = database.insertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = t_employee.salary + ? + * returning id, job + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any> Database.insertOrUpdateReturning( + table: T, returning: Pair, Column>, block: InsertOrUpdateStatementBuilder.(T) -> Unit +): Pair { + val (c1, c2) = returning + val row = insertOrUpdateReturningRow(table, listOf(c1, c2), block) + if (row == null) { + return Pair(null, null) + } else { + return Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) + } +} + +/** + * Insert a record to the table, determining if there is a key conflict while it's being inserted, automatically + * performs an update if any conflict exists, and finally returns the specific columns. + * + * Usage: + * + * ```kotlin + * val (id, job, salary) = + * database.insertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = t_employee.salary + ? + * returning id, job, salary + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any, C3 : Any> Database.insertOrUpdateReturning( + table: T, returning: Triple, Column, Column>, block: InsertOrUpdateStatementBuilder.(T) -> Unit +): Triple { + val (c1, c2, c3) = returning + val row = insertOrUpdateReturningRow(table, listOf(c1, c2, c3), block) + if (row == null) { + return Triple(null, null, null) + } else { + return Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) + } +} + +/** + * Insert or update, returning one row. + */ +private fun > Database.insertOrUpdateReturningRow( + table: T, returning: List>, block: InsertOrUpdateStatementBuilder.(T) -> Unit +): CachedRowSet? { + val expression = buildInsertOrUpdateExpression(table, returning, block) + val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) + + if (rowSet.size() == 0) { + // Possible when using onConflict { doNothing() } + return null + } + + if (rowSet.size() == 1) { + check(rowSet.next()) + return rowSet + } else { + val (sql, _) = formatExpression(expression, beautifySql = true) + throw IllegalStateException("Expected 1 row but ${rowSet.size()} returned from sql: \n\n$sql") + } +} + +/** + * Build an insert or update expression. + */ +private fun > buildInsertOrUpdateExpression( + table: T, returning: List>, block: InsertOrUpdateStatementBuilder.(T) -> Unit +): InsertOrUpdateExpression { + val builder = InsertOrUpdateStatementBuilder().apply { block(table) } + + val conflictColumns = builder.conflictColumns.ifEmpty { table.primaryKeys } + if (conflictColumns.isEmpty()) { + val msg = "" + + "Table '$table' doesn't have a primary key, " + + "you must specify the conflict columns when calling onConflict(col) { .. }" + throw IllegalStateException(msg) + } + + if (!builder.doNothing && builder.updateAssignments.isEmpty()) { + val msg = "" + + "Cannot leave the onConflict clause empty! " + + "If you desire no update action at all please explicitly call `doNothing()`" + throw IllegalStateException(msg) + } + + return InsertOrUpdateExpression( + table = table.asExpression(), + assignments = builder.assignments, + conflictColumns = conflictColumns.map { it.asExpression() }, + updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments, + returningColumns = returning.map { it.asExpression() } + ) +} + +/** + * Insert a record to the table and return the specific column. + * + * Usage: + * + * ```kotlin + * val id = database.insertReturning(Employees, Employees.id) { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?) + * returning id + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the column to return + * @param block the DSL block used to construct the expression. + * @return the returning column's value. + */ +public fun , C : Any> Database.insertReturning( + table: T, returning: Column, block: AssignmentsBuilder.(T) -> Unit +): C? { + val row = insertReturningRow(table, listOf(returning), block) + return returning.sqlType.getResult(row, 1) +} + +/** + * Insert a record to the table and return the specific columns. + * + * Usage: + * + * ```kotlin + * val (id, job) = database.insertReturning(Employees, Pair(Employees.id, Employees.job)) { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?) + * returning id, job + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any> Database.insertReturning( + table: T, returning: Pair, Column>, block: AssignmentsBuilder.(T) -> Unit +): Pair { + val (c1, c2) = returning + val row = insertReturningRow(table, listOf(c1, c2), block) + return Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) +} + +/** + * Insert a record to the table and return the specific columns. + * + * Usage: + * + * ```kotlin + * val (id, job, salary) = + * database.insertReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?) + * returning id, job, salary + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any, C3 : Any> Database.insertReturning( + table: T, returning: Triple, Column, Column>, block: AssignmentsBuilder.(T) -> Unit +): Triple { + val (c1, c2, c3) = returning + val row = insertReturningRow(table, listOf(c1, c2, c3), block) + return Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) +} + +/** + * Insert and returning one row. + */ +private fun > Database.insertReturningRow( + table: T, returning: List>, block: AssignmentsBuilder.(T) -> Unit +): CachedRowSet { + val builder = PostgreSqlAssignmentsBuilder().apply { block(table) } + + val expression = InsertOrUpdateExpression( + table = table.asExpression(), + assignments = builder.assignments, + returningColumns = returning.map { it.asExpression() } + ) + + val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) + + if (rowSet.size() == 1) { + check(rowSet.next()) + return rowSet + } else { + val (sql, _) = formatExpression(expression, beautifySql = true) + throw IllegalStateException("Expected 1 row but ${rowSet.size()} returned from sql: \n\n$sql") + } +} + +/** + * Base class of PostgreSQL DSL builders, provide basic functions used to build assignments for insert or update DSL. + */ +@KtormDsl +public open class PostgreSqlAssignmentsBuilder : AssignmentsBuilder() { + + /** + * A getter that returns the readonly view of the built assignments list. + */ + internal val assignments: List> get() = _assignments +} + +/** + * DSL builder for insert or update statements. + */ +@KtormDsl +public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { + internal val conflictColumns = ArrayList>() + internal val updateAssignments = ArrayList>() + internal var doNothing = false + + /** + * Specify the update assignments while any key conflict exists. + */ + @Deprecated( + message = "This function will be removed in the future, please use onConflict { } instead", + replaceWith = ReplaceWith("onConflict(columns, block)") + ) + public fun onDuplicateKey(vararg columns: Column<*>, block: AssignmentsBuilder.() -> Unit) { + onConflict(*columns, block = block) + } + + /** + * Specify the update assignments while any key conflict exists. + */ + public fun onConflict(vararg columns: Column<*>, block: InsertOrUpdateOnConflictClauseBuilder.() -> Unit) { + val builder = InsertOrUpdateOnConflictClauseBuilder().apply(block) + this.conflictColumns += columns + this.updateAssignments += builder.assignments + this.doNothing = builder.doNothing + } +} + +/** + * DSL builder for insert or update on conflict clause. + */ +@KtormDsl +public class InsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBuilder() { + internal var doNothing = false + + /** + * Explicitly tells ktorm to ignore any on-conflict errors and continue insertion. + */ + public fun doNothing() { + this.doNothing = true + } + + /** + * Reference the 'EXCLUDED' table in a ON CONFLICT clause. + */ + public fun excluded(column: Column): ColumnExpression { + // excluded.name + return ColumnExpression( + table = TableExpression(name = "excluded"), + name = column.name, + sqlType = column.sqlType + ) + } +} diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index d6d90cd9..62a65029 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -19,6 +19,7 @@ package org.ktorm.support.sqlite import org.ktorm.database.* import org.ktorm.expression.ArgumentExpression import org.ktorm.expression.QueryExpression +import org.ktorm.expression.SqlExpression import org.ktorm.expression.SqlFormatter import org.ktorm.schema.IntSqlType @@ -63,10 +64,43 @@ public open class SQLiteFormatter( database: Database, beautifySql: Boolean, indentSize: Int ) : SqlFormatter(database, beautifySql, indentSize) { + override fun visit(expr: SqlExpression): SqlExpression { + val result = when (expr) { + is InsertOrUpdateExpression -> visitInsertOrUpdate(expr) + else -> super.visit(expr) + } + + check(result === expr) { "SqlFormatter cannot modify the expression trees." } + return result + } + override fun writePagination(expr: QueryExpression) { newLine(Indentation.SAME) writeKeyword("limit ?, ? ") _parameters += ArgumentExpression(expr.offset ?: 0, IntSqlType) _parameters += ArgumentExpression(expr.limit ?: Int.MAX_VALUE, IntSqlType) } + + protected open fun visitInsertOrUpdate(expr: InsertOrUpdateExpression): InsertOrUpdateExpression { + writeKeyword("insert into ") + visitTable(expr.table) + writeInsertColumnNames(expr.assignments.map { it.column }) + writeKeyword("values ") + writeInsertValues(expr.assignments) + + if (expr.conflictColumns.isNotEmpty()) { + writeKeyword("on conflict ") + writeInsertColumnNames(expr.conflictColumns) + + if (expr.updateAssignments.isNotEmpty()) { + writeKeyword("do update set ") + visitColumnAssignments(expr.updateAssignments) + } else { + writeKeyword("do nothing ") + } + } + + return expr + } + } From 889d40f9ceabce889b4f67adf8a6ae391f89dba5 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Wed, 9 Feb 2022 14:24:01 +0800 Subject: [PATCH 02/13] =?UTF-8?q?feature(SQLite):=20InsertOrUpdate=20?= =?UTF-8?q?=E4=BF=AE=E6=94=B9=E4=B8=BA=20sqlite=20=E6=96=B9=E8=A8=80?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 参照 postgresql 添加 InsertOrUpdate 相关代码 参照 mysql 添加 AliasRemover 代码 --- .../ktorm/support/sqlite/InsertOrUpdate.kt | 308 +++--------------- .../org/ktorm/support/sqlite/SQLiteDialect.kt | 44 ++- .../org/ktorm/support/sqlite/SQLiteTest.kt | 29 ++ 3 files changed, 107 insertions(+), 274 deletions(-) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt index 6b9b0356..fe9ea996 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt @@ -16,7 +16,6 @@ package org.ktorm.support.sqlite -import org.ktorm.database.CachedRowSet import org.ktorm.database.Database import org.ktorm.dsl.AssignmentsBuilder import org.ktorm.dsl.KtormDsl @@ -29,20 +28,18 @@ import org.ktorm.schema.Column /** * Insert or update expression, represents an insert statement with an - * `on conflict (key) do update set` clause in PostgreSQL. + * `on conflict (key) do update set` clause in SQLite. * * @property table the table to be inserted. * @property assignments the inserted column assignments. * @property conflictColumns the index columns on which the conflict may happen. * @property updateAssignments the updated column assignments while any key conflict exists. - * @property returningColumns the returning columns. */ public data class InsertOrUpdateExpression( val table: TableExpression, val assignments: List>, val conflictColumns: List> = emptyList(), val updateAssignments: List> = emptyList(), - val returningColumns: List> = emptyList(), override val isLeafNode: Boolean = false, override val extraProperties: Map = emptyMap() ) : SqlExpression() @@ -61,7 +58,7 @@ public data class InsertOrUpdateExpression( * set(it.salary, 1000) * set(it.hireDate, LocalDate.now()) * set(it.departmentId, 1) - * onConflict { + * onConflict(it.id) { * set(it.salary, it.salary + 900) * } * } @@ -83,133 +80,15 @@ public data class InsertOrUpdateExpression( public fun > Database.insertOrUpdate( table: T, block: InsertOrUpdateStatementBuilder.(T) -> Unit ): Int { - val expression = buildInsertOrUpdateExpression(table, returning = emptyList(), block = block) + val expression = AliasRemover.visit(buildInsertOrUpdateExpression(table, block = block)) return executeUpdate(expression) } -/** - * Insert a record to the table, determining if there is a key conflict while it's being inserted, automatically - * performs an update if any conflict exists, and finally returns the specific columns. - * - * Usage: - * - * ```kotlin - * val (id, job) = database.insertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * onConflict { - * set(it.salary, it.salary + 900) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?) - * on conflict (id) do update set salary = t_employee.salary + ? - * returning id, job - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any> Database.insertOrUpdateReturning( - table: T, returning: Pair, Column>, block: InsertOrUpdateStatementBuilder.(T) -> Unit -): Pair { - val (c1, c2) = returning - val row = insertOrUpdateReturningRow(table, listOf(c1, c2), block) - if (row == null) { - return Pair(null, null) - } else { - return Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) - } -} - -/** - * Insert a record to the table, determining if there is a key conflict while it's being inserted, automatically - * performs an update if any conflict exists, and finally returns the specific columns. - * - * Usage: - * - * ```kotlin - * val (id, job, salary) = - * database.insertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * onConflict { - * set(it.salary, it.salary + 900) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?) - * on conflict (id) do update set salary = t_employee.salary + ? - * returning id, job, salary - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any, C3 : Any> Database.insertOrUpdateReturning( - table: T, returning: Triple, Column, Column>, block: InsertOrUpdateStatementBuilder.(T) -> Unit -): Triple { - val (c1, c2, c3) = returning - val row = insertOrUpdateReturningRow(table, listOf(c1, c2, c3), block) - if (row == null) { - return Triple(null, null, null) - } else { - return Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) - } -} - -/** - * Insert or update, returning one row. - */ -private fun > Database.insertOrUpdateReturningRow( - table: T, returning: List>, block: InsertOrUpdateStatementBuilder.(T) -> Unit -): CachedRowSet? { - val expression = buildInsertOrUpdateExpression(table, returning, block) - val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) - - if (rowSet.size() == 0) { - // Possible when using onConflict { doNothing() } - return null - } - - if (rowSet.size() == 1) { - check(rowSet.next()) - return rowSet - } else { - val (sql, _) = formatExpression(expression, beautifySql = true) - throw IllegalStateException("Expected 1 row but ${rowSet.size()} returned from sql: \n\n$sql") - } -} - /** * Build an insert or update expression. */ private fun > buildInsertOrUpdateExpression( - table: T, returning: List>, block: InsertOrUpdateStatementBuilder.(T) -> Unit + table: T, block: InsertOrUpdateStatementBuilder.(T) -> Unit ): InsertOrUpdateExpression { val builder = InsertOrUpdateStatementBuilder().apply { block(table) } @@ -232,155 +111,15 @@ private fun > buildInsertOrUpdateExpression( table = table.asExpression(), assignments = builder.assignments, conflictColumns = conflictColumns.map { it.asExpression() }, - updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments, - returningColumns = returning.map { it.asExpression() } + updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments ) } /** - * Insert a record to the table and return the specific column. - * - * Usage: - * - * ```kotlin - * val id = database.insertReturning(Employees, Employees.id) { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?) - * returning id - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the column to return - * @param block the DSL block used to construct the expression. - * @return the returning column's value. - */ -public fun , C : Any> Database.insertReturning( - table: T, returning: Column, block: AssignmentsBuilder.(T) -> Unit -): C? { - val row = insertReturningRow(table, listOf(returning), block) - return returning.sqlType.getResult(row, 1) -} - -/** - * Insert a record to the table and return the specific columns. - * - * Usage: - * - * ```kotlin - * val (id, job) = database.insertReturning(Employees, Pair(Employees.id, Employees.job)) { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?) - * returning id, job - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any> Database.insertReturning( - table: T, returning: Pair, Column>, block: AssignmentsBuilder.(T) -> Unit -): Pair { - val (c1, c2) = returning - val row = insertReturningRow(table, listOf(c1, c2), block) - return Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) -} - -/** - * Insert a record to the table and return the specific columns. - * - * Usage: - * - * ```kotlin - * val (id, job, salary) = - * database.insertReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?) - * returning id, job, salary - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any, C3 : Any> Database.insertReturning( - table: T, returning: Triple, Column, Column>, block: AssignmentsBuilder.(T) -> Unit -): Triple { - val (c1, c2, c3) = returning - val row = insertReturningRow(table, listOf(c1, c2, c3), block) - return Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) -} - -/** - * Insert and returning one row. - */ -private fun > Database.insertReturningRow( - table: T, returning: List>, block: AssignmentsBuilder.(T) -> Unit -): CachedRowSet { - val builder = PostgreSqlAssignmentsBuilder().apply { block(table) } - - val expression = InsertOrUpdateExpression( - table = table.asExpression(), - assignments = builder.assignments, - returningColumns = returning.map { it.asExpression() } - ) - - val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) - - if (rowSet.size() == 1) { - check(rowSet.next()) - return rowSet - } else { - val (sql, _) = formatExpression(expression, beautifySql = true) - throw IllegalStateException("Expected 1 row but ${rowSet.size()} returned from sql: \n\n$sql") - } -} - -/** - * Base class of PostgreSQL DSL builders, provide basic functions used to build assignments for insert or update DSL. + * Base class of SQLite DSL builders, provide basic functions used to build assignments for insert or update DSL. */ @KtormDsl -public open class PostgreSqlAssignmentsBuilder : AssignmentsBuilder() { +public open class SQLiteAssignmentsBuilder : AssignmentsBuilder() { /** * A getter that returns the readonly view of the built assignments list. @@ -392,7 +131,7 @@ public open class PostgreSqlAssignmentsBuilder : AssignmentsBuilder() { * DSL builder for insert or update statements. */ @KtormDsl -public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { +public class InsertOrUpdateStatementBuilder : SQLiteAssignmentsBuilder() { internal val conflictColumns = ArrayList>() internal val updateAssignments = ArrayList>() internal var doNothing = false @@ -423,7 +162,7 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { * DSL builder for insert or update on conflict clause. */ @KtormDsl -public class InsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBuilder() { +public class InsertOrUpdateOnConflictClauseBuilder : SQLiteAssignmentsBuilder() { internal var doNothing = false /** @@ -444,4 +183,33 @@ public class InsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBuilde sqlType = column.sqlType ) } + + /** + * be equal to 'set(column, excluded(column))'. + */ + public fun setExcluded(column: Column) { + set(column, excluded(column)) + } +} + +/** + * [SQLiteExpressionVisitor] implementation used to removed table aliases, used by Ktorm internal. + */ +internal object AliasRemover : SQLiteExpressionVisitor() { + + override fun visitTable(expr: TableExpression): TableExpression { + if (expr.tableAlias == null) { + return expr + } else { + return expr.copy(tableAlias = null) + } + } + + override fun visitColumn(expr: ColumnExpression): ColumnExpression { + if (expr.table == null) { + return expr + } else { + return expr.copy(table = null) + } + } } diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index 62a65029..85f998b5 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -17,10 +17,7 @@ package org.ktorm.support.sqlite import org.ktorm.database.* -import org.ktorm.expression.ArgumentExpression -import org.ktorm.expression.QueryExpression -import org.ktorm.expression.SqlExpression -import org.ktorm.expression.SqlFormatter +import org.ktorm.expression.* import org.ktorm.schema.IntSqlType /** @@ -104,3 +101,42 @@ public open class SQLiteFormatter( } } + +/** + * Base class designed to visit or modify SQLite's expression trees using visitor pattern. + * + * For detailed documents, see [SqlExpressionVisitor]. + */ +public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { + + override fun visit(expr: SqlExpression): SqlExpression { + return when (expr) { + is InsertOrUpdateExpression -> visitInsertOrUpdate(expr) + else -> super.visit(expr) + } + } + + protected open fun visitInsertOrUpdate(expr: InsertOrUpdateExpression): InsertOrUpdateExpression { + val table = visitTable(expr.table) + val assignments = visitColumnAssignments(expr.assignments) + val conflictColumns = visitExpressionList(expr.conflictColumns) + val updateAssignments = visitColumnAssignments(expr.updateAssignments) + + @Suppress("ComplexCondition") + if (table === expr.table + && assignments === expr.assignments + && conflictColumns === expr.conflictColumns + && updateAssignments === expr.updateAssignments + ) { + return expr + } else { + return expr.copy( + table = table, + assignments = assignments, + conflictColumns = conflictColumns, + updateAssignments = updateAssignments + ) + } + } + +} diff --git a/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt b/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt index e4ccd062..75f14904 100644 --- a/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt +++ b/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt @@ -78,6 +78,35 @@ class SQLiteTest : BaseTest() { database.delete(configs) { it.key eq "test" } } + @Test + fun testInsertOrUpdate() { + database.insertOrUpdate(Employees) { + set(it.id, 1) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + onConflict { + set(it.salary, it.salary + 1000) + } + } + database.insertOrUpdate(Employees.aliased("t")) { + set(it.id, 5) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + onConflict(it.id) { + set(it.salary, it.salary + 1000) + } + } + + assert(database.employees.find { it.id eq 1 }!!.salary == 1100L) + assert(database.employees.find { it.id eq 5 }!!.salary == 1000L) + } + @Test fun testLimit() { val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(0, 2) From 8b0fdeddc1e3b73424e40409ecd57f652d30fff0 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Wed, 9 Feb 2022 15:58:47 +0800 Subject: [PATCH 03/13] fix: Codacy Static Code Analysis --- .../kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt | 8 ++++---- .../main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt | 1 - 2 files changed, 4 insertions(+), 5 deletions(-) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt index fe9ea996..e2828ca3 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt @@ -95,15 +95,15 @@ private fun > buildInsertOrUpdateExpression( val conflictColumns = builder.conflictColumns.ifEmpty { table.primaryKeys } if (conflictColumns.isEmpty()) { val msg = "" + - "Table '$table' doesn't have a primary key, " + - "you must specify the conflict columns when calling onConflict(col) { .. }" + "Table '$table' doesn't have a primary key, " + + "you must specify the conflict columns when calling onConflict(col) { .. }" throw IllegalStateException(msg) } if (!builder.doNothing && builder.updateAssignments.isEmpty()) { val msg = "" + - "Cannot leave the onConflict clause empty! " + - "If you desire no update action at all please explicitly call `doNothing()`" + "Cannot leave the onConflict clause empty! " + + "If you desire no update action at all please explicitly call `doNothing()`" throw IllegalStateException(msg) } diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index 85f998b5..44959d22 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -99,7 +99,6 @@ public open class SQLiteFormatter( return expr } - } /** From 261b5ee53ec479516648159f123dc39e94de9223 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Wed, 9 Feb 2022 16:03:24 +0800 Subject: [PATCH 04/13] fix: Codacy Static Code Analysis --- .../src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt | 1 - 1 file changed, 1 deletion(-) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index 44959d22..102f0221 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -137,5 +137,4 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { ) } } - } From 9c6ddf7c68c0ac3257a52cdb5ed35b2e1e69bcf7 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 08:36:06 +0800 Subject: [PATCH 05/13] feature(SQLite): Adding Functions --- .../org/ktorm/support/sqlite/Functions.kt | 238 ++++++++++++++++++ 1 file changed, 238 insertions(+) create mode 100644 ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Functions.kt diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Functions.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Functions.kt new file mode 100644 index 00000000..90ac8d26 --- /dev/null +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Functions.kt @@ -0,0 +1,238 @@ +/* + * Copyright 2018-2021 the original author or authors. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.ktorm.support.sqlite + +import org.ktorm.expression.ArgumentExpression +import org.ktorm.expression.FunctionExpression +import org.ktorm.schema.* +import java.math.BigDecimal +import java.sql.Date +import java.sql.Time +import java.sql.Timestamp +import java.time.* +import java.util.* + +@PublishedApi +internal inline fun sqlTypeOf(): SqlType? { + val sqlType = when (T::class) { + Boolean::class -> BooleanSqlType + Int::class -> IntSqlType + Short::class -> ShortSqlType + Long::class -> LongSqlType + Float::class -> FloatSqlType + Double::class -> DoubleSqlType + BigDecimal::class -> DecimalSqlType + String::class -> VarcharSqlType + ByteArray::class -> BytesSqlType + Timestamp::class -> TimestampSqlType + Date::class -> DateSqlType + Time::class -> TimeSqlType + Instant::class -> InstantSqlType + LocalDateTime::class -> LocalDateTimeSqlType + LocalDate::class -> LocalDateSqlType + LocalTime::class -> LocalTimeSqlType + MonthDay::class -> MonthDaySqlType + YearMonth::class -> YearMonthSqlType + Year::class -> YearSqlType + UUID::class -> UuidSqlType + else -> null + } + + @Suppress("UNCHECKED_CAST") + return sqlType as SqlType? +} + +// region SQLite: The JSON1 Extension + +/** + * SQLite json_extract function, translated to `json_extract(column, path)`. + */ +public inline fun ColumnDeclaring<*>.jsonExtract( + path: String, + sqlType: SqlType = sqlTypeOf() ?: error("Cannot detect the result's SqlType, please specify manually.") +): FunctionExpression { + // json_extract(column, path) + return FunctionExpression( + functionName = "json_extract", + arguments = listOf(asExpression(), ArgumentExpression(path, VarcharSqlType)), + sqlType = sqlType + ) +} + +/** + * SQLite json_patch function, translated to `json_patch(left, right)`. + */ +public fun ColumnDeclaring<*>.jsonPatch(right: ColumnDeclaring<*>): FunctionExpression { + // json_patch(left, right) + return FunctionExpression( + functionName = "json_patch", + arguments = listOf(this, right).map { it.asExpression() }, + sqlType = VarcharSqlType + ) +} + +/** + * SQLite json_remove function, translated to `json_remove(column, path)`. + */ +public fun ColumnDeclaring<*>.jsonRemove(path: String): FunctionExpression { + // json_remove(column, path) + return FunctionExpression( + functionName = "json_remove", + arguments = listOf(asExpression(), ArgumentExpression(path, VarcharSqlType)), + sqlType = VarcharSqlType + ) +} + +/** + * SQLite json_valid function, translated to `json_valid(column)`. + */ +public fun ColumnDeclaring<*>.jsonValid(): FunctionExpression { + // json_valid(column) + return FunctionExpression( + functionName = "json_valid", + arguments = listOf(asExpression()), + sqlType = BooleanSqlType + ) +} + +// endregion + +// region SQLite: Built-In Scalar SQL Functions + +/** + * SQLite random function, translated to `random()`. + */ +public fun random(): FunctionExpression { + return FunctionExpression(functionName = "random", arguments = emptyList(), sqlType = LongSqlType) +} + +/** + * SQLite ifnull function, translated to `ifnull(left, right)`. + */ +public fun ColumnDeclaring.ifNull(right: ColumnDeclaring): FunctionExpression { + // ifnull(left, right) + return FunctionExpression( + functionName = "ifnull", + arguments = listOf(this, right).map { it.asExpression() }, + sqlType = sqlType + ) +} + +/** + * SQLite ifnull function, translated to `ifnull(left, right)`. + */ +public fun ColumnDeclaring.ifNull(right: T?): FunctionExpression { + return this.ifNull(wrapArgument(right)) +} + +/** + * SQLite iif function, translated to `iif(condition, then, otherwise)`. + */ +public fun iif( + condition: ColumnDeclaring, + then: ColumnDeclaring, + otherwise: ColumnDeclaring +): FunctionExpression { + // iif(condition, then, otherwise) + return FunctionExpression( + functionName = "iif", + arguments = listOf(condition, then, otherwise).map { it.asExpression() }, + sqlType = then.sqlType + ) +} + +/** + * SQLite iif function, translated to `iif(condition, then, otherwise)`. + */ +public inline fun iif( + condition: ColumnDeclaring, + then: T, + otherwise: T, + sqlType: SqlType = sqlTypeOf() ?: error("Cannot detect the param's SqlType, please specify manually.") +): FunctionExpression { + // iif(condition, then, otherwise) + return FunctionExpression( + functionName = "iif", + arguments = listOf( + condition.asExpression(), + ArgumentExpression(then, sqlType), + ArgumentExpression(otherwise, sqlType) + ), + sqlType = sqlType + ) +} + +/** + * SQLite instr function, translated to `instr(left, right)`. + */ +public fun ColumnDeclaring.instr(right: ColumnDeclaring): FunctionExpression { + // instr(left, right) + return FunctionExpression( + functionName = "instr", + arguments = listOf(this, right).map { it.asExpression() }, + sqlType = IntSqlType + ) +} + +/** + * SQLite instr function, translated to `instr(left, right)`. + */ +public fun ColumnDeclaring.instr(right: String): FunctionExpression { + return instr(wrapArgument(right)) +} + +/** + * SQLite replace function, translated to `replace(str, oldValue, newValue)`. + */ +public fun ColumnDeclaring.replace(oldValue: String, newValue: String): FunctionExpression { + // replace(str, oldValue, newValue) + return FunctionExpression( + functionName = "replace", + arguments = listOf( + this.asExpression(), + ArgumentExpression(oldValue, VarcharSqlType), + ArgumentExpression(newValue, VarcharSqlType) + ), + sqlType = VarcharSqlType + ) +} + +/** + * SQLite lower function, translated to `lower(str). ` + */ +public fun ColumnDeclaring.toLowerCase(): FunctionExpression { + // lower(str) + return FunctionExpression( + functionName = "lower", + arguments = listOf(this.asExpression()), + sqlType = VarcharSqlType + ) +} + +/** + * SQLite upper function, translated to `upper(str). ` + */ +public fun ColumnDeclaring.toUpperCase(): FunctionExpression { + // upper(str) + return FunctionExpression( + functionName = "upper", + arguments = listOf(this.asExpression()), + sqlType = VarcharSqlType + ) +} + +// endregion From 87c498bded995a3f855f3d200df472024e459c86 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 08:51:16 +0800 Subject: [PATCH 06/13] feature(SQLite): BulkInsert.kt copy from postgresql --- .../org/ktorm/support/sqlite/BulkInsert.kt | 556 ++++++++++++++++++ .../org/ktorm/support/sqlite/SQLiteDialect.kt | 75 +++ 2 files changed, 631 insertions(+) create mode 100644 ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt new file mode 100644 index 00000000..c6e72e12 --- /dev/null +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt @@ -0,0 +1,556 @@ +/* + * Copyright 2018-2021 the original author or authors. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.ktorm.support.sqlite + +import org.ktorm.database.CachedRowSet +import org.ktorm.database.Database +import org.ktorm.database.asIterable +import org.ktorm.dsl.AssignmentsBuilder +import org.ktorm.dsl.KtormDsl +import org.ktorm.dsl.batchInsert +import org.ktorm.expression.ColumnAssignmentExpression +import org.ktorm.expression.ColumnExpression +import org.ktorm.expression.SqlExpression +import org.ktorm.expression.TableExpression +import org.ktorm.schema.BaseTable +import org.ktorm.schema.Column + +/** + * Bulk insert expression, represents a bulk insert statement in SQLite. + * + * For example: + * + * ```sql + * insert into table (column1, column2) + * values (?, ?), (?, ?), (?, ?)... + * on conflict (...) do update set ...` + * ``` + * + * @property table the table to be inserted. + * @property assignments column assignments of the bulk insert statement. + * @property conflictColumns the index columns on which the conflict may happen. + * @property updateAssignments the updated column assignments while key conflict exists. + * @property returningColumns the returning columns. + */ +public data class BulkInsertExpression( + val table: TableExpression, + val assignments: List>>, + val conflictColumns: List> = emptyList(), + val updateAssignments: List> = emptyList(), + val returningColumns: List> = emptyList(), + override val isLeafNode: Boolean = false, + override val extraProperties: Map = emptyMap() +) : SqlExpression() + +/** + * Bulk insert records to the table and return the effected row count. + * + * The usage is almost the same as [batchInsert], but this function is implemented by generating a special SQL + * using SQLite's bulk insert syntax, instead of based on JDBC batch operations. For this reason, its performance + * is much better than [batchInsert]. + * + * The generated SQL is like: `insert into table (column1, column2) values (?, ?), (?, ?), (?, ?)...`. + * + * Usage: + * + * ```kotlin + * database.bulkInsert(Employees) { + * item { + * set(it.name, "jerry") + * set(it.job, "trainee") + * set(it.managerId, 1) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 50) + * set(it.departmentId, 1) + * } + * item { + * set(it.name, "linda") + * set(it.job, "assistant") + * set(it.managerId, 3) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 100) + * set(it.departmentId, 2) + * } + * } + * ``` + * + * @since 3.3.0 + * @param table the table to be inserted. + * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. + * @return the effected row count. + * @see batchInsert + */ +public fun > Database.bulkInsert( + table: T, block: BulkInsertStatementBuilder.(T) -> Unit +): Int { + val builder = BulkInsertStatementBuilder(table).apply { block(table) } + val expression = BulkInsertExpression(table.asExpression(), builder.assignments) + return executeUpdate(expression) +} + +/** + * Bulk insert records to the table and return the specific column's values. + * + * Usage: + * + * ```kotlin + * database.bulkInsertReturning(Employees, Employees.id) { + * item { + * set(it.name, "jerry") + * set(it.job, "trainee") + * set(it.managerId, 1) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 50) + * set(it.departmentId, 1) + * } + * item { + * set(it.name, "linda") + * set(it.job, "assistant") + * set(it.managerId, 3) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 100) + * set(it.departmentId, 2) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into table (name, job, manager_id, hire_date, salary, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)... + * returning id + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the column to return + * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. + * @return the returning column's values. + */ +public fun , C : Any> Database.bulkInsertReturning( + table: T, returning: Column, block: BulkInsertStatementBuilder.(T) -> Unit +): List { + val rowSet = bulkInsertReturningRowSet(table, listOf(returning), block) + return rowSet.asIterable().map { row -> returning.sqlType.getResult(row, 1) } +} + +/** + * Bulk insert records to the table and return the specific columns' values. + * + * Usage: + * + * ```kotlin + * database.bulkInsertReturning(Employees, Pair(Employees.id, Employees.job)) { + * item { + * set(it.name, "jerry") + * set(it.job, "trainee") + * set(it.managerId, 1) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 50) + * set(it.departmentId, 1) + * } + * item { + * set(it.name, "linda") + * set(it.job, "assistant") + * set(it.managerId, 3) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 100) + * set(it.departmentId, 2) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into table (name, job, manager_id, hire_date, salary, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)... + * returning id, job + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any> Database.bulkInsertReturning( + table: T, returning: Pair, Column>, block: BulkInsertStatementBuilder.(T) -> Unit +): List> { + val (c1, c2) = returning + val rowSet = bulkInsertReturningRowSet(table, listOf(c1, c2), block) + return rowSet.asIterable().map { row -> Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) } +} + +/** + * Bulk insert records to the table and return the specific columns' values. + * + * Usage: + * + * ```kotlin + * database.bulkInsertReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { + * item { + * set(it.name, "jerry") + * set(it.job, "trainee") + * set(it.managerId, 1) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 50) + * set(it.departmentId, 1) + * } + * item { + * set(it.name, "linda") + * set(it.job, "assistant") + * set(it.managerId, 3) + * set(it.hireDate, LocalDate.now()) + * set(it.salary, 100) + * set(it.departmentId, 2) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into table (name, job, manager_id, hire_date, salary, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)... + * returning id, job, salary + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertReturning( + table: T, returning: Triple, Column, Column>, block: BulkInsertStatementBuilder.(T) -> Unit +): List> { + val (c1, c2, c3) = returning + val rowSet = bulkInsertReturningRowSet(table, listOf(c1, c2, c3), block) + return rowSet.asIterable().map { row -> + Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) + } +} + +/** + * Bulk insert records to the table, returning row set. + */ +private fun > Database.bulkInsertReturningRowSet( + table: T, returning: List>, block: BulkInsertStatementBuilder.(T) -> Unit +): CachedRowSet { + val builder = BulkInsertStatementBuilder(table).apply { block(table) } + + val expression = BulkInsertExpression( + table = table.asExpression(), + assignments = builder.assignments, + returningColumns = returning.map { it.asExpression() } + ) + + val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) + return rowSet +} + +/** + * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, + * and automatically performs updates if any conflict exists. + * + * Usage: + * + * ```kotlin + * database.bulkInsertOrUpdate(Employees) { + * item { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * item { + * set(it.id, 5) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = t_employee.salary + ? + * ``` + * + * @since 3.3.0 + * @param table the table to be inserted. + * @param block the DSL block used to construct the expression. + * @return the effected row count. + */ +public fun > Database.bulkInsertOrUpdate( + table: T, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): Int { + val expression = buildBulkInsertOrUpdateExpression(table, returning = emptyList(), block = block) + return executeUpdate(expression) +} + +/** + * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, + * automatically performs updates if any conflict exists, and finally returns the specific column. + * + * Usage: + * + * ```kotlin + * database.bulkInsertOrUpdateReturning(Employees, Employees.id) { + * item { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * item { + * set(it.id, 5) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = t_employee.salary + ? + * returning id + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the column to return + * @param block the DSL block used to construct the expression. + * @return the returning column's values. + */ +public fun , C : Any> Database.bulkInsertOrUpdateReturning( + table: T, returning: Column, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): List { + val expression = buildBulkInsertOrUpdateExpression(table, listOf(returning), block) + val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) + return rowSet.asIterable().map { row -> returning.sqlType.getResult(row, 1) } +} + +/** + * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, + * automatically performs updates if any conflict exists, and finally returns the specific columns. + * + * Usage: + * + * ```kotlin + * database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) { + * item { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * item { + * set(it.id, 5) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = t_employee.salary + ? + * returning id, job + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any> Database.bulkInsertOrUpdateReturning( + table: T, returning: Pair, Column>, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): List> { + val (c1, c2) = returning + val expression = buildBulkInsertOrUpdateExpression(table, listOf(c1, c2), block) + val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) + return rowSet.asIterable().map { row -> Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) } +} + +/** + * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, + * automatically performs updates if any conflict exists, and finally returns the specific columns. + * + * Usage: + * + * ```kotlin + * database.bulkInsertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { + * item { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * item { + * set(it.id, 5) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = t_employee.salary + ? + * returning id, job, salary + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param returning the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ +public fun , C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertOrUpdateReturning( + table: T, + returning: Triple, Column, Column>, + block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): List> { + val (c1, c2, c3) = returning + val expression = buildBulkInsertOrUpdateExpression(table, listOf(c1, c2, c3), block) + val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) + return rowSet.asIterable().map { row -> + Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) + } +} + +/** + * Build a bulk insert or update expression. + */ +private fun > buildBulkInsertOrUpdateExpression( + table: T, returning: List>, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): BulkInsertExpression { + val builder = BulkInsertOrUpdateStatementBuilder(table).apply { block(table) } + + val conflictColumns = builder.conflictColumns.ifEmpty { table.primaryKeys } + if (conflictColumns.isEmpty()) { + val msg = "" + + "Table '$table' doesn't have a primary key, " + + "you must specify the conflict columns when calling onConflict(col) { .. }" + throw IllegalStateException(msg) + } + + if (!builder.doNothing && builder.updateAssignments.isEmpty()) { + val msg = "" + + "Cannot leave the onConflict clause empty! " + + "If you desire no update action at all please explicitly call `doNothing()`" + throw IllegalStateException(msg) + } + + return BulkInsertExpression( + table = table.asExpression(), + assignments = builder.assignments, + conflictColumns = conflictColumns.map { it.asExpression() }, + updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments, + returningColumns = returning.map { it.asExpression() } + ) +} + +/** + * DSL builder for bulk insert statements. + */ +@KtormDsl +public open class BulkInsertStatementBuilder>(internal val table: T) { + internal val assignments = ArrayList>>() + + /** + * Add the assignments of a new row to the bulk insert. + */ + public fun item(block: AssignmentsBuilder.() -> Unit) { + val builder = SQLiteAssignmentsBuilder().apply(block) + + if (assignments.isEmpty() + || assignments[0].map { it.column.name } == builder.assignments.map { it.column.name } + ) { + assignments += builder.assignments + } else { + throw IllegalArgumentException("Every item in a batch operation must be the same.") + } + } +} + +/** + * DSL builder for bulk insert or update statements. + */ +@KtormDsl +public class BulkInsertOrUpdateStatementBuilder>(table: T) : BulkInsertStatementBuilder(table) { + internal val conflictColumns = ArrayList>() + internal val updateAssignments = ArrayList>() + internal var doNothing: Boolean = false + + /** + * Specify the update assignments while any key conflict exists. + */ + public fun onConflict(vararg columns: Column<*>, block: InsertOrUpdateOnConflictClauseBuilder.() -> Unit) { + val builder = InsertOrUpdateOnConflictClauseBuilder().apply(block) + this.conflictColumns += columns + this.updateAssignments += builder.assignments + this.doNothing = builder.doNothing + } +} diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index 102f0221..36e3584a 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -64,6 +64,7 @@ public open class SQLiteFormatter( override fun visit(expr: SqlExpression): SqlExpression { val result = when (expr) { is InsertOrUpdateExpression -> visitInsertOrUpdate(expr) + is BulkInsertExpression -> visitBulkInsert(expr) else -> super.visit(expr) } @@ -99,6 +100,35 @@ public open class SQLiteFormatter( return expr } + + protected open fun visitBulkInsert(expr: BulkInsertExpression): BulkInsertExpression { + writeKeyword("insert into ") + visitTable(expr.table) + writeInsertColumnNames(expr.assignments[0].map { it.column }) + writeKeyword("values ") + + for ((i, assignments) in expr.assignments.withIndex()) { + if (i > 0) { + removeLastBlank() + write(", ") + } + writeInsertValues(assignments) + } + + if (expr.conflictColumns.isNotEmpty()) { + writeKeyword("on conflict ") + writeInsertColumnNames(expr.conflictColumns) + + if (expr.updateAssignments.isNotEmpty()) { + writeKeyword("do update set ") + visitColumnAssignments(expr.updateAssignments) + } else { + writeKeyword("do nothing ") + } + } + + return expr + } } /** @@ -111,6 +141,7 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { override fun visit(expr: SqlExpression): SqlExpression { return when (expr) { is InsertOrUpdateExpression -> visitInsertOrUpdate(expr) + is BulkInsertExpression -> visitBulkInsert(expr) else -> super.visit(expr) } } @@ -137,4 +168,48 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { ) } } + + protected open fun visitBulkInsert(expr: BulkInsertExpression): BulkInsertExpression { + val table = expr.table + val assignments = visitBulkInsertAssignments(expr.assignments) + val conflictColumns = visitExpressionList(expr.conflictColumns) + val updateAssignments = visitColumnAssignments(expr.updateAssignments) + val returningColumns = visitExpressionList(expr.returningColumns) + + @Suppress("ComplexCondition") + if (table === expr.table + && assignments === expr.assignments + && conflictColumns === expr.conflictColumns + && updateAssignments === expr.updateAssignments + && returningColumns === expr.returningColumns + ) { + return expr + } else { + return expr.copy( + table = table, + assignments = assignments, + conflictColumns = conflictColumns, + updateAssignments = updateAssignments, + returningColumns = returningColumns + ) + } + } + + protected open fun visitBulkInsertAssignments( + assignments: List>> + ): List>> { + val result = ArrayList>>() + var changed = false + + for (row in assignments) { + val visited = visitColumnAssignments(row) + result += visited + + if (visited !== row) { + changed = true + } + } + + return if (changed) result else assignments + } } From edafba5ffb0fc99e0116b8eb0aefdd52e5911553 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 09:35:02 +0800 Subject: [PATCH 07/13] fix(SQLite): no need to remove 'table-name' when visitColumn --- .../kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt | 8 -------- 1 file changed, 8 deletions(-) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt index e2828ca3..0e720e08 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt @@ -204,12 +204,4 @@ internal object AliasRemover : SQLiteExpressionVisitor() { return expr.copy(tableAlias = null) } } - - override fun visitColumn(expr: ColumnExpression): ColumnExpression { - if (expr.table == null) { - return expr - } else { - return expr.copy(table = null) - } - } } From 6488c8ca5580e1e9c859edd6ce57ed4e62e5ef96 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 09:48:45 +0800 Subject: [PATCH 08/13] feature(SQLite): BulkInsert.kt change to SQLite dialect --- .../org/ktorm/support/sqlite/BulkInsert.kt | 344 +----------------- .../org/ktorm/support/sqlite/SQLiteDialect.kt | 7 +- .../org/ktorm/support/sqlite/SQLiteTest.kt | 129 +++++++ 3 files changed, 140 insertions(+), 340 deletions(-) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt index c6e72e12..67d0f609 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt @@ -16,9 +16,7 @@ package org.ktorm.support.sqlite -import org.ktorm.database.CachedRowSet import org.ktorm.database.Database -import org.ktorm.database.asIterable import org.ktorm.dsl.AssignmentsBuilder import org.ktorm.dsl.KtormDsl import org.ktorm.dsl.batchInsert @@ -44,14 +42,12 @@ import org.ktorm.schema.Column * @property assignments column assignments of the bulk insert statement. * @property conflictColumns the index columns on which the conflict may happen. * @property updateAssignments the updated column assignments while key conflict exists. - * @property returningColumns the returning columns. */ public data class BulkInsertExpression( val table: TableExpression, val assignments: List>>, val conflictColumns: List> = emptyList(), val updateAssignments: List> = emptyList(), - val returningColumns: List> = emptyList(), override val isLeafNode: Boolean = false, override val extraProperties: Map = emptyMap() ) : SqlExpression() @@ -98,171 +94,12 @@ public fun > Database.bulkInsert( table: T, block: BulkInsertStatementBuilder.(T) -> Unit ): Int { val builder = BulkInsertStatementBuilder(table).apply { block(table) } - val expression = BulkInsertExpression(table.asExpression(), builder.assignments) - return executeUpdate(expression) -} - -/** - * Bulk insert records to the table and return the specific column's values. - * - * Usage: - * - * ```kotlin - * database.bulkInsertReturning(Employees, Employees.id) { - * item { - * set(it.name, "jerry") - * set(it.job, "trainee") - * set(it.managerId, 1) - * set(it.hireDate, LocalDate.now()) - * set(it.salary, 50) - * set(it.departmentId, 1) - * } - * item { - * set(it.name, "linda") - * set(it.job, "assistant") - * set(it.managerId, 3) - * set(it.hireDate, LocalDate.now()) - * set(it.salary, 100) - * set(it.departmentId, 2) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into table (name, job, manager_id, hire_date, salary, department_id) - * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)... - * returning id - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the column to return - * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. - * @return the returning column's values. - */ -public fun , C : Any> Database.bulkInsertReturning( - table: T, returning: Column, block: BulkInsertStatementBuilder.(T) -> Unit -): List { - val rowSet = bulkInsertReturningRowSet(table, listOf(returning), block) - return rowSet.asIterable().map { row -> returning.sqlType.getResult(row, 1) } -} - -/** - * Bulk insert records to the table and return the specific columns' values. - * - * Usage: - * - * ```kotlin - * database.bulkInsertReturning(Employees, Pair(Employees.id, Employees.job)) { - * item { - * set(it.name, "jerry") - * set(it.job, "trainee") - * set(it.managerId, 1) - * set(it.hireDate, LocalDate.now()) - * set(it.salary, 50) - * set(it.departmentId, 1) - * } - * item { - * set(it.name, "linda") - * set(it.job, "assistant") - * set(it.managerId, 3) - * set(it.hireDate, LocalDate.now()) - * set(it.salary, 100) - * set(it.departmentId, 2) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into table (name, job, manager_id, hire_date, salary, department_id) - * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)... - * returning id, job - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any> Database.bulkInsertReturning( - table: T, returning: Pair, Column>, block: BulkInsertStatementBuilder.(T) -> Unit -): List> { - val (c1, c2) = returning - val rowSet = bulkInsertReturningRowSet(table, listOf(c1, c2), block) - return rowSet.asIterable().map { row -> Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) } -} - -/** - * Bulk insert records to the table and return the specific columns' values. - * - * Usage: - * - * ```kotlin - * database.bulkInsertReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { - * item { - * set(it.name, "jerry") - * set(it.job, "trainee") - * set(it.managerId, 1) - * set(it.hireDate, LocalDate.now()) - * set(it.salary, 50) - * set(it.departmentId, 1) - * } - * item { - * set(it.name, "linda") - * set(it.job, "assistant") - * set(it.managerId, 3) - * set(it.hireDate, LocalDate.now()) - * set(it.salary, 100) - * set(it.departmentId, 2) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into table (name, job, manager_id, hire_date, salary, department_id) - * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)... - * returning id, job, salary - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertReturning( - table: T, returning: Triple, Column, Column>, block: BulkInsertStatementBuilder.(T) -> Unit -): List> { - val (c1, c2, c3) = returning - val rowSet = bulkInsertReturningRowSet(table, listOf(c1, c2, c3), block) - return rowSet.asIterable().map { row -> - Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) - } -} -/** - * Bulk insert records to the table, returning row set. - */ -private fun > Database.bulkInsertReturningRowSet( - table: T, returning: List>, block: BulkInsertStatementBuilder.(T) -> Unit -): CachedRowSet { - val builder = BulkInsertStatementBuilder(table).apply { block(table) } - - val expression = BulkInsertExpression( - table = table.asExpression(), - assignments = builder.assignments, - returningColumns = returning.map { it.asExpression() } + val expression = AliasRemover.visit( + BulkInsertExpression(table.asExpression(), builder.assignments) ) - val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) - return rowSet + return executeUpdate(expression) } /** @@ -311,180 +148,18 @@ private fun > Database.bulkInsertReturningRowSet( public fun > Database.bulkInsertOrUpdate( table: T, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit ): Int { - val expression = buildBulkInsertOrUpdateExpression(table, returning = emptyList(), block = block) - return executeUpdate(expression) -} - -/** - * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, - * automatically performs updates if any conflict exists, and finally returns the specific column. - * - * Usage: - * - * ```kotlin - * database.bulkInsertOrUpdateReturning(Employees, Employees.id) { - * item { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * item { - * set(it.id, 5) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * onConflict { - * set(it.salary, it.salary + 900) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) - * on conflict (id) do update set salary = t_employee.salary + ? - * returning id - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the column to return - * @param block the DSL block used to construct the expression. - * @return the returning column's values. - */ -public fun , C : Any> Database.bulkInsertOrUpdateReturning( - table: T, returning: Column, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit -): List { - val expression = buildBulkInsertOrUpdateExpression(table, listOf(returning), block) - val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) - return rowSet.asIterable().map { row -> returning.sqlType.getResult(row, 1) } -} - -/** - * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, - * automatically performs updates if any conflict exists, and finally returns the specific columns. - * - * Usage: - * - * ```kotlin - * database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) { - * item { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * item { - * set(it.id, 5) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * onConflict { - * set(it.salary, it.salary + 900) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) - * on conflict (id) do update set salary = t_employee.salary + ? - * returning id, job - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any> Database.bulkInsertOrUpdateReturning( - table: T, returning: Pair, Column>, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit -): List> { - val (c1, c2) = returning - val expression = buildBulkInsertOrUpdateExpression(table, listOf(c1, c2), block) - val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) - return rowSet.asIterable().map { row -> Pair(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2)) } -} + val expression = AliasRemover.visit( + buildBulkInsertOrUpdateExpression(table, block = block) + ) -/** - * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, - * automatically performs updates if any conflict exists, and finally returns the specific columns. - * - * Usage: - * - * ```kotlin - * database.bulkInsertOrUpdateReturning(Employees, Triple(Employees.id, Employees.job, Employees.salary)) { - * item { - * set(it.id, 1) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * item { - * set(it.id, 5) - * set(it.name, "vince") - * set(it.job, "engineer") - * set(it.salary, 1000) - * set(it.hireDate, LocalDate.now()) - * set(it.departmentId, 1) - * } - * onConflict { - * set(it.salary, it.salary + 900) - * } - * } - * ``` - * - * Generated SQL: - * - * ```sql - * insert into t_employee (id, name, job, salary, hire_date, department_id) - * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) - * on conflict (id) do update set salary = t_employee.salary + ? - * returning id, job, salary - * ``` - * - * @since 3.4.0 - * @param table the table to be inserted. - * @param returning the columns to return - * @param block the DSL block used to construct the expression. - * @return the returning columns' values. - */ -public fun , C1 : Any, C2 : Any, C3 : Any> Database.bulkInsertOrUpdateReturning( - table: T, - returning: Triple, Column, Column>, - block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit -): List> { - val (c1, c2, c3) = returning - val expression = buildBulkInsertOrUpdateExpression(table, listOf(c1, c2, c3), block) - val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) - return rowSet.asIterable().map { row -> - Triple(c1.sqlType.getResult(row, 1), c2.sqlType.getResult(row, 2), c3.sqlType.getResult(row, 3)) - } + return executeUpdate(expression) } /** * Build a bulk insert or update expression. */ private fun > buildBulkInsertOrUpdateExpression( - table: T, returning: List>, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit + table: T, block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit ): BulkInsertExpression { val builder = BulkInsertOrUpdateStatementBuilder(table).apply { block(table) } @@ -507,8 +182,7 @@ private fun > buildBulkInsertOrUpdateExpression( table = table.asExpression(), assignments = builder.assignments, conflictColumns = conflictColumns.map { it.asExpression() }, - updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments, - returningColumns = returning.map { it.asExpression() } + updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments ) } diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index 36e3584a..87b03b85 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -170,18 +170,16 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { } protected open fun visitBulkInsert(expr: BulkInsertExpression): BulkInsertExpression { - val table = expr.table + val table = visitTable(expr.table) val assignments = visitBulkInsertAssignments(expr.assignments) val conflictColumns = visitExpressionList(expr.conflictColumns) val updateAssignments = visitColumnAssignments(expr.updateAssignments) - val returningColumns = visitExpressionList(expr.returningColumns) @Suppress("ComplexCondition") if (table === expr.table && assignments === expr.assignments && conflictColumns === expr.conflictColumns && updateAssignments === expr.updateAssignments - && returningColumns === expr.returningColumns ) { return expr } else { @@ -189,8 +187,7 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { table = table, assignments = assignments, conflictColumns = conflictColumns, - updateAssignments = updateAssignments, - returningColumns = returningColumns + updateAssignments = updateAssignments ) } } diff --git a/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt b/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt index 75f14904..b4d83ee4 100644 --- a/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt +++ b/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt @@ -107,6 +107,135 @@ class SQLiteTest : BaseTest() { assert(database.employees.find { it.id eq 5 }!!.salary == 1000L) } + @Test + fun testInsertOrUpdate1() { + database.insertOrUpdate(Employees) { + set(it.id, 1) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + onConflict { + setExcluded(it.salary) + } + } + database.insertOrUpdate(Employees.aliased("t")) { + set(it.id, 5) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + onConflict(it.id) { + set(it.salary, it.salary + 1000) + } + } + + assert(database.employees.find { it.id eq 1 }!!.salary == 1000L) + assert(database.employees.find { it.id eq 5 }!!.salary == 1000L) + } + + @Test + fun testBulkInsert() { + database.bulkInsert(Employees.aliased("t")) { + item { + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + } + item { + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + } + } + + assert(database.employees.count() == 6) + } + + @Test + fun testBulkInsertOrUpdate() { + database.bulkInsertOrUpdate(Employees.aliased("t")) { + item { + set(it.id, 1) + set(it.name, "vince") + set(it.job, "trainee") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 2) + } + item { + set(it.id, 5) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 2) + } + onConflict(it.id) { + set(it.job, it.job) + set(it.departmentId, excluded(it.departmentId)) + set(it.salary, it.salary + 1000) + } + } + + database.employees.find { it.id eq 1 }!!.let { + assert(it.job == "engineer") + assert(it.department.id == 2) + assert(it.salary == 1100L) + } + + database.employees.find { it.id eq 5 }!!.let { + assert(it.job == "engineer") + assert(it.department.id == 2) + assert(it.salary == 1000L) + } + } + + @Test + fun testBulkInsertOrUpdate1() { + val bulkInsertWithUpdate = { ignoreErrors: Boolean -> + database.bulkInsertOrUpdate(Employees.aliased("t")) { + item { + set(it.id, 5) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + } + item { + set(it.id, 6) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + } + onConflict { + if (ignoreErrors) doNothing() else set(it.salary, it.salary + 900) + } + } + } + + bulkInsertWithUpdate(false) + assert(database.employees.find { it.id eq 5 }!!.salary == 1000L) + assert(database.employees.find { it.id eq 6 }!!.salary == 1000L) + + bulkInsertWithUpdate(false) + assert(database.employees.find { it.id eq 5 }!!.salary == 1900L) + assert(database.employees.find { it.id eq 6 }!!.salary == 1900L) + + bulkInsertWithUpdate(true) + assert(database.employees.find { it.id eq 5 }!!.salary == 1900L) + assert(database.employees.find { it.id eq 6 }!!.salary == 1900L) + } + @Test fun testLimit() { val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(0, 2) From edf0f296f428eafcf2d10d190702841d1c9f3e9b Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 10:18:50 +0800 Subject: [PATCH 09/13] feature(SQLite): WHERE clause at the end of the DO UPDATE --- .../org/ktorm/support/sqlite/BulkInsert.kt | 12 ++-- .../ktorm/support/sqlite/InsertOrUpdate.kt | 15 +++-- .../org/ktorm/support/sqlite/SQLiteDialect.kt | 20 +++++- .../org/ktorm/support/sqlite/SQLiteTest.kt | 66 +++++++++++++++++++ 4 files changed, 102 insertions(+), 11 deletions(-) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt index 67d0f609..bf4c6ceb 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt @@ -20,12 +20,10 @@ import org.ktorm.database.Database import org.ktorm.dsl.AssignmentsBuilder import org.ktorm.dsl.KtormDsl import org.ktorm.dsl.batchInsert -import org.ktorm.expression.ColumnAssignmentExpression -import org.ktorm.expression.ColumnExpression -import org.ktorm.expression.SqlExpression -import org.ktorm.expression.TableExpression +import org.ktorm.expression.* import org.ktorm.schema.BaseTable import org.ktorm.schema.Column +import org.ktorm.schema.ColumnDeclaring /** * Bulk insert expression, represents a bulk insert statement in SQLite. @@ -48,6 +46,7 @@ public data class BulkInsertExpression( val assignments: List>>, val conflictColumns: List> = emptyList(), val updateAssignments: List> = emptyList(), + val where: ScalarExpression? = null, override val isLeafNode: Boolean = false, override val extraProperties: Map = emptyMap() ) : SqlExpression() @@ -182,7 +181,8 @@ private fun > buildBulkInsertOrUpdateExpression( table = table.asExpression(), assignments = builder.assignments, conflictColumns = conflictColumns.map { it.asExpression() }, - updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments + updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments, + where = builder.where?.asExpression() ) } @@ -216,6 +216,7 @@ public open class BulkInsertStatementBuilder>(internal val tabl public class BulkInsertOrUpdateStatementBuilder>(table: T) : BulkInsertStatementBuilder(table) { internal val conflictColumns = ArrayList>() internal val updateAssignments = ArrayList>() + internal var where: ColumnDeclaring? = null internal var doNothing: Boolean = false /** @@ -225,6 +226,7 @@ public class BulkInsertOrUpdateStatementBuilder>(table: T) : Bu val builder = InsertOrUpdateOnConflictClauseBuilder().apply(block) this.conflictColumns += columns this.updateAssignments += builder.assignments + this.where = builder.where this.doNothing = builder.doNothing } } diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt index 0e720e08..68859d86 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt @@ -19,12 +19,10 @@ package org.ktorm.support.sqlite import org.ktorm.database.Database import org.ktorm.dsl.AssignmentsBuilder import org.ktorm.dsl.KtormDsl -import org.ktorm.expression.ColumnAssignmentExpression -import org.ktorm.expression.ColumnExpression -import org.ktorm.expression.SqlExpression -import org.ktorm.expression.TableExpression +import org.ktorm.expression.* import org.ktorm.schema.BaseTable import org.ktorm.schema.Column +import org.ktorm.schema.ColumnDeclaring /** * Insert or update expression, represents an insert statement with an @@ -40,6 +38,7 @@ public data class InsertOrUpdateExpression( val assignments: List>, val conflictColumns: List> = emptyList(), val updateAssignments: List> = emptyList(), + val where: ScalarExpression? = null, override val isLeafNode: Boolean = false, override val extraProperties: Map = emptyMap() ) : SqlExpression() @@ -111,6 +110,7 @@ private fun > buildInsertOrUpdateExpression( table = table.asExpression(), assignments = builder.assignments, conflictColumns = conflictColumns.map { it.asExpression() }, + where = builder.where?.asExpression(), updateAssignments = if (builder.doNothing) emptyList() else builder.updateAssignments ) } @@ -134,6 +134,7 @@ public open class SQLiteAssignmentsBuilder : AssignmentsBuilder() { public class InsertOrUpdateStatementBuilder : SQLiteAssignmentsBuilder() { internal val conflictColumns = ArrayList>() internal val updateAssignments = ArrayList>() + internal var where: ColumnDeclaring? = null internal var doNothing = false /** @@ -154,6 +155,7 @@ public class InsertOrUpdateStatementBuilder : SQLiteAssignmentsBuilder() { val builder = InsertOrUpdateOnConflictClauseBuilder().apply(block) this.conflictColumns += columns this.updateAssignments += builder.assignments + this.where = builder.where this.doNothing = builder.doNothing } } @@ -163,8 +165,13 @@ public class InsertOrUpdateStatementBuilder : SQLiteAssignmentsBuilder() { */ @KtormDsl public class InsertOrUpdateOnConflictClauseBuilder : SQLiteAssignmentsBuilder() { + internal var where: ColumnDeclaring? = null internal var doNothing = false + public fun where(block: () -> ColumnDeclaring) { + this.where = block() + } + /** * Explicitly tells ktorm to ignore any on-conflict errors and continue insertion. */ diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index 87b03b85..1dd41999 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -93,6 +93,11 @@ public open class SQLiteFormatter( if (expr.updateAssignments.isNotEmpty()) { writeKeyword("do update set ") visitColumnAssignments(expr.updateAssignments) + + if (expr.where != null) { + writeKeyword("where ") + visit(expr.where) + } } else { writeKeyword("do nothing ") } @@ -122,6 +127,11 @@ public open class SQLiteFormatter( if (expr.updateAssignments.isNotEmpty()) { writeKeyword("do update set ") visitColumnAssignments(expr.updateAssignments) + + if (expr.where != null) { + writeKeyword("where ") + visit(expr.where) + } } else { writeKeyword("do nothing ") } @@ -151,12 +161,14 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { val assignments = visitColumnAssignments(expr.assignments) val conflictColumns = visitExpressionList(expr.conflictColumns) val updateAssignments = visitColumnAssignments(expr.updateAssignments) + val where = expr.where?.let { visitScalar(it) } @Suppress("ComplexCondition") if (table === expr.table && assignments === expr.assignments && conflictColumns === expr.conflictColumns && updateAssignments === expr.updateAssignments + && where === expr.where ) { return expr } else { @@ -164,7 +176,8 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { table = table, assignments = assignments, conflictColumns = conflictColumns, - updateAssignments = updateAssignments + updateAssignments = updateAssignments, + where = where ) } } @@ -174,12 +187,14 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { val assignments = visitBulkInsertAssignments(expr.assignments) val conflictColumns = visitExpressionList(expr.conflictColumns) val updateAssignments = visitColumnAssignments(expr.updateAssignments) + val where = expr.where?.let { visitScalar(it) } @Suppress("ComplexCondition") if (table === expr.table && assignments === expr.assignments && conflictColumns === expr.conflictColumns && updateAssignments === expr.updateAssignments + && where === expr.where ) { return expr } else { @@ -187,7 +202,8 @@ public open class SQLiteExpressionVisitor : SqlExpressionVisitor() { table = table, assignments = assignments, conflictColumns = conflictColumns, - updateAssignments = updateAssignments + updateAssignments = updateAssignments, + where = where ) } } diff --git a/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt b/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt index b4d83ee4..d15f10fe 100644 --- a/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt +++ b/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt @@ -136,6 +136,41 @@ class SQLiteTest : BaseTest() { assert(database.employees.find { it.id eq 5 }!!.salary == 1000L) } + @Test + fun testInsertOrUpdateOnConflictWhere() { + database.insertOrUpdate(Employees.aliased("t")) { + set(it.id, 1) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + onConflict { + set(it.salary, it.salary + excluded(it.salary)) + where { + it.salary less 1000 + } + } + } + assert(database.employees.find { it.id eq 1 }!!.salary == 1100L) + database.insertOrUpdate(Employees.aliased("t")) { + set(it.id, 1) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + onConflict(it.id) { + set(it.salary, it.salary + excluded(it.salary)) + where { + it.salary less 1000 + } + } + } + + assert(database.employees.find { it.id eq 1 }!!.salary == 1100L) + } + @Test fun testBulkInsert() { database.bulkInsert(Employees.aliased("t")) { @@ -197,6 +232,37 @@ class SQLiteTest : BaseTest() { } } + @Test + fun testBulkInsertOrUpdateOnConflictWhere() { + database.bulkInsertOrUpdate(Employees.aliased("t")) { + item { + set(it.id, 1) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + } + item { + set(it.id, 2) + set(it.name, "marry") + set(it.job, "trainee") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + } + onConflict(it.id) { + set(it.salary, it.salary + 1000) + where { + it.job eq "engineer" + } + } + } + + assert(database.employees.find { it.id eq 1 }!!.salary == 1100L) + assert(database.employees.find { it.id eq 2 }!!.salary == 50L) + } + @Test fun testBulkInsertOrUpdate1() { val bulkInsertWithUpdate = { ignoreErrors: Boolean -> From 169bb6f6506c9d7e2457611cd766895b6c021e99 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 10:35:54 +0800 Subject: [PATCH 10/13] feature(SQLite): Adding Global.kt Adding Global.kt remove '@since' notes --- .../org/ktorm/support/sqlite/BulkInsert.kt | 2 - .../kotlin/org/ktorm/support/sqlite/Global.kt | 159 ++++++++++++++++++ .../ktorm/support/sqlite/InsertOrUpdate.kt | 1 - 3 files changed, 159 insertions(+), 3 deletions(-) create mode 100644 ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Global.kt diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt index bf4c6ceb..16b88da4 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt @@ -83,7 +83,6 @@ public data class BulkInsertExpression( * } * ``` * - * @since 3.3.0 * @param table the table to be inserted. * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. * @return the effected row count. @@ -139,7 +138,6 @@ public fun > Database.bulkInsert( * on conflict (id) do update set salary = t_employee.salary + ? * ``` * - * @since 3.3.0 * @param table the table to be inserted. * @param block the DSL block used to construct the expression. * @return the effected row count. diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Global.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Global.kt new file mode 100644 index 00000000..a1f601d7 --- /dev/null +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/Global.kt @@ -0,0 +1,159 @@ +/* + * Copyright 2018-2021 the original author or authors. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.ktorm.support.sqlite + +import org.ktorm.database.Database +import org.ktorm.dsl.batchInsert +import org.ktorm.schema.BaseTable +import java.lang.reflect.InvocationTargetException + +/** + * Obtain the global database instance via reflection, throwing an exception if ktorm-global is not + * available in the classpath. + */ +@Suppress("SwallowedException") +internal val Database.Companion.global: Database get() { + try { + val cls = Class.forName("org.ktorm.global.GlobalKt") + val method = cls.getMethod("getGlobal", Database.Companion::class.java) + return method.invoke(null, Database.Companion) as Database + } catch (e: ClassNotFoundException) { + throw IllegalStateException("Cannot detect the global database object, please add ktorm-global to classpath", e) + } catch (e: InvocationTargetException) { + throw e.targetException + } +} + +/** + * Insert a record to the table, determining if there is a key conflict while it's being inserted, and automatically + * performs an update if any conflict exists. + * + * Usage: + * + * ```kotlin + * Employees.insertOrUpdate { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) values (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = salary + ? + * ``` + * + * @param block the DSL block used to construct the expression. + * @return the effected row count. + */ +public fun > T.insertOrUpdate(block: InsertOrUpdateStatementBuilder.(T) -> Unit): Int { + return Database.global.insertOrUpdate(this, block) +} + +/** + * Construct a bulk insert expression in the given closure, then execute it and return the effected row count. + * + * The usage is almost the same as [batchInsert], but this function is implemented by generating a special SQL + * using SQLite's bulk insert syntax, instead of based on JDBC batch operations. For this reason, its performance + * is much better than [batchInsert]. + * + * The generated SQL is like: `insert into table (column1, column2) values (?, ?), (?, ?), (?, ?)...`. + * + * Usage: + * + * ```kotlin + * Employees.bulkInsert { + * item { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * item { + * set(it.id, 5) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * } + * ``` + * + * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. + * @return the effected row count. + * @see batchInsert + */ +public fun > T.bulkInsert(block: BulkInsertStatementBuilder.(T) -> Unit): Int { + return Database.global.bulkInsert(this, block) +} + +/** + * Bulk insert records to the table, determining if there is a key conflict while inserting each of them, + * and automatically performs updates if any conflict exists. + * + * Usage: + * + * ```kotlin + * Employees.bulkInsertOrUpdate { + * item { + * set(it.id, 1) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * item { + * set(it.id, 5) + * set(it.name, "vince") + * set(it.job, "engineer") + * set(it.salary, 1000) + * set(it.hireDate, LocalDate.now()) + * set(it.departmentId, 1) + * } + * onConflict { + * set(it.salary, it.salary + 900) + * } + * } + * ``` + * + * Generated SQL: + * + * ```sql + * insert into t_employee (id, name, job, salary, hire_date, department_id) + * values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?) + * on conflict (id) do update set salary = salary + ? + * ``` + * + * @param block the DSL block used to construct the expression. + * @return the effected row count. + * @see bulkInsert + */ +public fun > T.bulkInsertOrUpdate(block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit): Int { + return Database.global.bulkInsertOrUpdate(this, block) +} diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt index 68859d86..d981c344 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt @@ -71,7 +71,6 @@ public data class InsertOrUpdateExpression( * on conflict (id) do update set salary = t_employee.salary + ? * ``` * - * @since 2.7 * @param table the table to be inserted. * @param block the DSL block used to construct the expression. * @return the effected row count. From b5e849554de01a8bbaa5374554d6d67f23f1a6d0 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 10:41:06 +0800 Subject: [PATCH 11/13] docs(SQLite): add docs on function 'where' --- .../src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt | 3 +++ 1 file changed, 3 insertions(+) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt index d981c344..98660b4d 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt @@ -167,6 +167,9 @@ public class InsertOrUpdateOnConflictClauseBuilder : SQLiteAssignmentsBuilder() internal var where: ColumnDeclaring? = null internal var doNothing = false + /** + * Specify the where clause for this update statement. + */ public fun where(block: () -> ColumnDeclaring) { this.where = block() } From 6d1423893d67c9e35e541166567c6967a0f656ee Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Thu, 10 Feb 2022 17:32:07 +0800 Subject: [PATCH 12/13] build(build.gradle): Add name to list of developers --- build.gradle | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/build.gradle b/build.gradle index 67f86634..40f32d31 100644 --- a/build.gradle +++ b/build.gradle @@ -168,6 +168,11 @@ subprojects { project -> name = "Eric Fenderbosch" email = "eric@fender.net" } + developer { + id = "2938137849" + name = "ccr" + email = "2938137849@qq.com" + } } } } From 7190535a07607dc01f013d0dc5863133b6ddd136 Mon Sep 17 00:00:00 2001 From: 2938137849 <2938137849@qq.com> Date: Tue, 15 Feb 2022 16:20:13 +0800 Subject: [PATCH 13/13] fix(SQLite): UNION clause can only use `visitQuery` --- .../org/ktorm/support/sqlite/SQLiteDialect.kt | 28 +++++++++++++++++++ 1 file changed, 28 insertions(+) diff --git a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt index 1dd41999..5f6174cd 100644 --- a/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt +++ b/ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/SQLiteDialect.kt @@ -79,6 +79,34 @@ public open class SQLiteFormatter( _parameters += ArgumentExpression(expr.limit ?: Int.MAX_VALUE, IntSqlType) } + override fun visitUnion(expr: UnionExpression): UnionExpression { + when (expr.left) { + is SelectExpression -> visitQuery(expr.left) + is UnionExpression -> visitUnion(expr.left as UnionExpression) + } + + if (expr.isUnionAll) { + writeKeyword("union all ") + } else { + writeKeyword("union ") + } + + when (expr.right) { + is SelectExpression -> visitQuery(expr.right) + is UnionExpression -> visitUnion(expr.right as UnionExpression) + } + + if (expr.orderBy.isNotEmpty()) { + newLine(Indentation.SAME) + writeKeyword("order by ") + visitOrderByList(expr.orderBy) + } + if (expr.offset != null || expr.limit != null) { + writePagination(expr) + } + return expr + } + protected open fun visitInsertOrUpdate(expr: InsertOrUpdateExpression): InsertOrUpdateExpression { writeKeyword("insert into ") visitTable(expr.table)