From 2c52178cc831778c61a505e801dd9f6414606086 Mon Sep 17 00:00:00 2001 From: PedroD Date: Sat, 9 Jan 2021 22:52:15 +0000 Subject: [PATCH 01/10] Adding returning to single insert or update query --- .../support/postgresql/InsertOrUpdate.kt | 109 ++++++++++++++++++ .../support/postgresql/PostgreSqlDialect.kt | 27 +++++ .../support/postgresql/PostgreSqlTest.kt | 68 +++++++++++ 3 files changed, 204 insertions(+) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index f81cacfd..6daeeb8f 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -16,6 +16,7 @@ package org.ktorm.support.postgresql +import org.ktorm.database.CachedRowSet import org.ktorm.database.Database import org.ktorm.dsl.AssignmentsBuilder import org.ktorm.dsl.KtormDsl @@ -139,3 +140,111 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { conflictColumns += columns } } + + +/** + * Insert or update expression, represents an insert statement with an + * `on conflict (key) do update set (...) returning ...` clause in PostgreSQL, + * capable of retrieving columns. + * + * @property table the table to be inserted. + * @property assignments the inserted column assignments. + * @property conflictColumns the index columns on which the conflict may happens. + * @property updateAssignments the updated column assignments while any key conflict exists. + * @property returningColumns the columns to returning. + */ +public data class InsertOrUpdateAndReturningColumnsExpression( + val table: TableExpression, + val assignments: List>, + val conflictColumns: List> = emptyList(), + val updateAssignments: List> = emptyList(), + val returningColumns: List>, + 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) + * onDuplicateKey { + * set(it.salary, it.salary + 900) + * } + * returning { + * it.id, + * it.job + * } + * } + * ``` + * + * 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 + * @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.insertOrUpdateAndReturningColumns( + table: T, + block: InsertOrUpdateAndReturningColumnsStatementBuilder.(T) -> Unit +): Pair { + val builder = InsertOrUpdateAndReturningColumnsStatementBuilder().apply { block(table) } + + val primaryKeys = table.primaryKeys + if (primaryKeys.isEmpty() && builder.conflictColumns.isEmpty()) { + val msg = + "Table '$table' doesn't have a primary key, " + + "you must specify the conflict columns when calling onDuplicateKey(col) { .. }" + throw IllegalStateException(msg) + } + + val expression = InsertOrUpdateAndReturningColumnsExpression( + table = table.asExpression(), + assignments = builder.assignments, + conflictColumns = builder.conflictColumns.ifEmpty { primaryKeys }.map { it.asExpression() }, + updateAssignments = builder.updateAssignments, + returningColumns = builder.retrievingColumns.ifEmpty { primaryKeys }.map { it.asExpression() } + ) + + return executeUpdateAndRetrieveKeys(expression) +} + +/** + * DSL builder for insert or update statements that return columns. + */ +@KtormDsl +public class InsertOrUpdateAndReturningColumnsStatementBuilder : PostgreSqlAssignmentsBuilder() { + internal val updateAssignments = ArrayList>() + internal val conflictColumns = ArrayList>() + internal val retrievingColumns = ArrayList>() + + /** + * Specify the update assignments while any key conflict exists. + */ + public fun onDuplicateKey(vararg columns: Column<*>, block: AssignmentsBuilder.() -> Unit) { + val builder = PostgreSqlAssignmentsBuilder().apply(block) + updateAssignments += builder.assignments + conflictColumns += columns + } + + public fun returning(vararg retrievingColumns: Column<*>) { + this.retrievingColumns += retrievingColumns + } + +} diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt index 7aae5851..4a10319e 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt @@ -48,6 +48,7 @@ public open class PostgreSqlFormatter( override fun visit(expr: SqlExpression): SqlExpression { val result = when (expr) { is InsertOrUpdateExpression -> visitInsertOrUpdate(expr) + is InsertOrUpdateAndReturningColumnsExpression -> visitInsertOrUpdateAndRetrieveColumns(expr) is BulkInsertExpression -> visitBulkInsert(expr) else -> super.visit(expr) } @@ -163,6 +164,32 @@ public open class PostgreSqlFormatter( return expr } + protected open fun visitInsertOrUpdateAndRetrieveColumns(expr: InsertOrUpdateAndReturningColumnsExpression): InsertOrUpdateAndReturningColumnsExpression { + writeKeyword("insert into ") + visitTable(expr.table) + writeInsertColumnNames(expr.assignments.map { it.column }) + writeKeyword("values ") + writeInsertValues(expr.assignments) + + if (expr.updateAssignments.isNotEmpty()) { + writeKeyword("on conflict ") + writeInsertColumnNames(expr.conflictColumns) + writeKeyword("do update set ") + visitColumnAssignments(expr.updateAssignments) + } + + if (expr.returningColumns.isNotEmpty()) { + writeKeyword(" returning ") + expr.returningColumns.forEachIndexed { i, column -> + if (i > 0) write(", ") + checkColumnName(column.name) + write(column.name.quoted) + } + } + + return expr + } + protected open fun visitBulkInsert(expr: BulkInsertExpression): BulkInsertExpression { writeKeyword("insert into ") visitTable(expr.table) diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt index 2a026e7d..861a3ab2 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt @@ -161,6 +161,74 @@ class PostgreSqlTest : BaseTest() { assert(database.employees.count() == 6) } + @Test + fun testInsertOrUpdateAndRetrieveColumns() { + val t1 = database.insertOrUpdateAndReturningColumns(Employees) { + set(it.id, 1001) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + + onDuplicateKey { + set(it.salary, it.salary + 900) + } + + returning( + it.name, + it.id + ) + } + + assert(t1.first == 1) + t1.second.next() + assert(t1.second.getString("name") == "vince") + assert(t1.second.getInt("id") == 1001) + + val t2 = database.insertOrUpdateAndReturningColumns(Employees) { + set(it.id, 1001) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + + onDuplicateKey(it.id) { + set(it.salary, it.salary + 900) + } + + returning( + it.name, + it.id, + it.salary + ) + } + + assert(t2.first == 1) + t2.second.next() + assert(t2.second.getInt("salary") == 1900) + assert(t2.second.getString("name") == "vince") + assert(t2.second.getInt("id") == 1001) + + val t3 = database.insertOrUpdateAndReturningColumns(Employees) { + set(it.id, 1001) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 1) + + onDuplicateKey(it.id) { + set(it.salary, it.salary + 900) + } + } + + assert(t3.first == 1) + t3.second.next() + assert(t3.second.getInt("id") == 1001) + } + @Test fun testBulkInsertOrUpdate() { database.bulkInsertOrUpdate(Employees) { From f1a887dd502e9f84e220b836f7b91dcba8ef58a2 Mon Sep 17 00:00:00 2001 From: PedroD Date: Sat, 9 Jan 2021 23:29:21 +0000 Subject: [PATCH 02/10] Adding respective bulk operations --- .../ktorm/support/postgresql/BulkInsert.kt | 211 +++++++++++++++++- .../support/postgresql/InsertOrUpdate.kt | 26 +-- .../support/postgresql/PostgreSqlDialect.kt | 38 +++- .../support/postgresql/PostgreSqlTest.kt | 81 ++++++- 4 files changed, 335 insertions(+), 21 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index 5007829d..2fcaf1ae 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -16,6 +16,7 @@ package org.ktorm.support.postgresql +import org.ktorm.database.CachedRowSet import org.ktorm.database.Database import org.ktorm.dsl.AssignmentsBuilder import org.ktorm.dsl.KtormDsl @@ -150,7 +151,7 @@ public fun > Database.bulkInsertOrUpdate( if (conflictColumns.isEmpty()) { val msg = "Table '$table' doesn't have a primary key, " + - "you must specify the conflict columns when calling onConflict(col) { .. }" + "you must specify the conflict columns when calling onConflict(col) { .. }" throw IllegalStateException(msg) } @@ -223,3 +224,211 @@ public class BulkInsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBu ) } } + +/** + * Bulk insert expression, represents a bulk insert statement in PostgreSQL. + * + * For example: + * + * ```sql + * insert into table (column1, column2) values (?, ?), (?, ?), (?, ?)... + * `on conflict (key) do update set (...) returning ... + * ``` + * + * @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 happens. + * @property updateAssignments the updated column assignments while key conflict exists. + * @property returningColumns the columns to returning. + */ +public data class BulkInsertReturningExpression( + val table: TableExpression, + val assignments: List>>, + val conflictColumns: List> = emptyList(), + val updateAssignments: List> = emptyList(), + val returningColumns: List>, + override val isLeafNode: Boolean = false, + override val extraProperties: Map = emptyMap() +) : SqlExpression() + +/** + * 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 PostgreSQL'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 (?, ?), (?, ?), (?, ?)... + * returning column1, column2, ...`. + * + * 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) + * } + * + * returning ( + * it.id, + * it.job + * ) + * } + * ``` + * + * @since 3.4.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.bulkInsertReturning( + table: T, block: BulkInsertReturningStatementBuilder.(T) -> Unit +): Pair { + val builder = BulkInsertReturningStatementBuilder(table).apply { block(table) } + val expression = BulkInsertReturningExpression( + table.asExpression(), + builder.assignments, + returningColumns = builder.returningColumns.ifEmpty { table.primaryKeys }.map { it.asExpression() } + ) + return executeUpdateAndRetrieveKeys(expression) +} + +/** + * 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) + * } + * + * returning ( + * it.id, + * it.job + * ) + * } + * ``` + * + * 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 block the DSL block used to construct the expression. + * @return the effected row count. + * @see bulkInsert + */ +public fun > Database.bulkInsertOrUpdateReturning( + table: T, block: BulkInsertOrUpdateReturningStatementBuilder.(T) -> Unit +): Pair { + val builder = BulkInsertOrUpdateReturningStatementBuilder(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) + } + + val expression = BulkInsertReturningExpression( + table = table.asExpression(), + assignments = builder.assignments, + conflictColumns = conflictColumns.map { it.asExpression() }, + updateAssignments = builder.updateAssignments, + returningColumns = builder.returningColumns.ifEmpty { table.primaryKeys }.map { it.asExpression() } + ) + + return executeUpdateAndRetrieveKeys(expression) +} + +/** + * DSL builder for bulk insert statements. + */ +@KtormDsl +public open class BulkInsertReturningStatementBuilder>(internal val table: T) { + internal val assignments = ArrayList>>() + internal val returningColumns = ArrayList>() + + /** + * Add the assignments of a new row to the bulk insert. + */ + public fun item(block: AssignmentsBuilder.() -> Unit) { + val builder = PostgreSqlAssignmentsBuilder().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.") + } + } + + public fun returning(vararg returningColumns: Column<*>) { + this.returningColumns += returningColumns + } +} + +/** + * DSL builder for bulk insert or update statements. + */ +@KtormDsl +public class BulkInsertOrUpdateReturningStatementBuilder>(table: T) : + BulkInsertStatementBuilder(table) { + internal val updateAssignments = ArrayList>() + internal val conflictColumns = ArrayList>() + internal val returningColumns = ArrayList>() + + /** + * Specify the update assignments while any key conflict exists. + */ + public fun onConflict(vararg columns: Column<*>, block: BulkInsertOrUpdateOnConflictClauseBuilder.() -> Unit) { + val builder = BulkInsertOrUpdateOnConflictClauseBuilder().apply(block) + updateAssignments += builder.assignments + conflictColumns += columns + } + + public fun returning(vararg returningColumns: Column<*>) { + this.returningColumns += returningColumns + } +} diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index 6daeeb8f..77abd9a8 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -141,7 +141,6 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { } } - /** * Insert or update expression, represents an insert statement with an * `on conflict (key) do update set (...) returning ...` clause in PostgreSQL, @@ -153,7 +152,7 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { * @property updateAssignments the updated column assignments while any key conflict exists. * @property returningColumns the columns to returning. */ -public data class InsertOrUpdateAndReturningColumnsExpression( +public data class InsertOrUpdateReturningColumnsExpression( val table: TableExpression, val assignments: List>, val conflictColumns: List> = emptyList(), @@ -180,10 +179,10 @@ public data class InsertOrUpdateAndReturningColumnsExpression( * onDuplicateKey { * set(it.salary, it.salary + 900) * } - * returning { + * returning ( * it.id, * it.job - * } + * ) * } * ``` * @@ -200,11 +199,11 @@ public data class InsertOrUpdateAndReturningColumnsExpression( * @param block the DSL block used to construct the expression. * @return the effected row count. */ -public fun > Database.insertOrUpdateAndReturningColumns( +public fun > Database.insertOrUpdateReturningColumns( table: T, - block: InsertOrUpdateAndReturningColumnsStatementBuilder.(T) -> Unit + block: InsertOrUpdateReturningColumnsStatementBuilder.(T) -> Unit ): Pair { - val builder = InsertOrUpdateAndReturningColumnsStatementBuilder().apply { block(table) } + val builder = InsertOrUpdateReturningColumnsStatementBuilder().apply { block(table) } val primaryKeys = table.primaryKeys if (primaryKeys.isEmpty() && builder.conflictColumns.isEmpty()) { @@ -214,12 +213,12 @@ public fun > Database.insertOrUpdateAndReturningColumns( throw IllegalStateException(msg) } - val expression = InsertOrUpdateAndReturningColumnsExpression( + val expression = InsertOrUpdateReturningColumnsExpression( table = table.asExpression(), assignments = builder.assignments, conflictColumns = builder.conflictColumns.ifEmpty { primaryKeys }.map { it.asExpression() }, updateAssignments = builder.updateAssignments, - returningColumns = builder.retrievingColumns.ifEmpty { primaryKeys }.map { it.asExpression() } + returningColumns = builder.returningColumns.ifEmpty { primaryKeys }.map { it.asExpression() } ) return executeUpdateAndRetrieveKeys(expression) @@ -229,10 +228,10 @@ public fun > Database.insertOrUpdateAndReturningColumns( * DSL builder for insert or update statements that return columns. */ @KtormDsl -public class InsertOrUpdateAndReturningColumnsStatementBuilder : PostgreSqlAssignmentsBuilder() { +public class InsertOrUpdateReturningColumnsStatementBuilder : PostgreSqlAssignmentsBuilder() { internal val updateAssignments = ArrayList>() internal val conflictColumns = ArrayList>() - internal val retrievingColumns = ArrayList>() + internal val returningColumns = ArrayList>() /** * Specify the update assignments while any key conflict exists. @@ -243,8 +242,7 @@ public class InsertOrUpdateAndReturningColumnsStatementBuilder : PostgreSqlAssig conflictColumns += columns } - public fun returning(vararg retrievingColumns: Column<*>) { - this.retrievingColumns += retrievingColumns + public fun returning(vararg returningColumns: Column<*>) { + this.returningColumns += returningColumns } - } diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt index 4a10319e..8588949c 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt @@ -48,7 +48,8 @@ public open class PostgreSqlFormatter( override fun visit(expr: SqlExpression): SqlExpression { val result = when (expr) { is InsertOrUpdateExpression -> visitInsertOrUpdate(expr) - is InsertOrUpdateAndReturningColumnsExpression -> visitInsertOrUpdateAndRetrieveColumns(expr) + is InsertOrUpdateReturningColumnsExpression -> visitInsertOrUpdateReturningColumns(expr) + is BulkInsertReturningExpression -> visitBulkInsertReturningColumns(expr) is BulkInsertExpression -> visitBulkInsert(expr) else -> super.visit(expr) } @@ -164,7 +165,7 @@ public open class PostgreSqlFormatter( return expr } - protected open fun visitInsertOrUpdateAndRetrieveColumns(expr: InsertOrUpdateAndReturningColumnsExpression): InsertOrUpdateAndReturningColumnsExpression { + protected open fun visitInsertOrUpdateReturningColumns(expr: InsertOrUpdateReturningColumnsExpression): InsertOrUpdateReturningColumnsExpression { writeKeyword("insert into ") visitTable(expr.table) writeInsertColumnNames(expr.assignments.map { it.column }) @@ -190,6 +191,39 @@ public open class PostgreSqlFormatter( return expr } + protected open fun visitBulkInsertReturningColumns(expr: BulkInsertReturningExpression): BulkInsertReturningExpression { + 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.updateAssignments.isNotEmpty()) { + writeKeyword("on conflict ") + writeInsertColumnNames(expr.conflictColumns) + writeKeyword("do update set ") + visitColumnAssignments(expr.updateAssignments) + } + + if (expr.returningColumns.isNotEmpty()) { + writeKeyword(" returning ") + expr.returningColumns.forEachIndexed { i, column -> + if (i > 0) write(", ") + checkColumnName(column.name) + write(column.name.quoted) + } + } + + return expr + } + protected open fun visitBulkInsert(expr: BulkInsertExpression): BulkInsertExpression { writeKeyword("insert into ") visitTable(expr.table) diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt index 861a3ab2..7c115a94 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt @@ -2,6 +2,7 @@ package org.ktorm.support.postgresql import org.hamcrest.CoreMatchers.equalTo import org.hamcrest.CoreMatchers.nullValue +import org.junit.Assert.assertEquals import org.junit.Assert.assertThat import org.junit.ClassRule import org.junit.Test @@ -162,8 +163,8 @@ class PostgreSqlTest : BaseTest() { } @Test - fun testInsertOrUpdateAndRetrieveColumns() { - val t1 = database.insertOrUpdateAndReturningColumns(Employees) { + fun testInsertOrUpdateReturningColumns() { + val t1 = database.insertOrUpdateReturningColumns(Employees) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -186,7 +187,7 @@ class PostgreSqlTest : BaseTest() { assert(t1.second.getString("name") == "vince") assert(t1.second.getInt("id") == 1001) - val t2 = database.insertOrUpdateAndReturningColumns(Employees) { + val t2 = database.insertOrUpdateReturningColumns(Employees) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -211,7 +212,7 @@ class PostgreSqlTest : BaseTest() { assert(t2.second.getString("name") == "vince") assert(t2.second.getInt("id") == 1001) - val t3 = database.insertOrUpdateAndReturningColumns(Employees) { + val t3 = database.insertOrUpdateReturningColumns(Employees) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -268,6 +269,78 @@ class PostgreSqlTest : BaseTest() { } } + @Test + fun testBulkInsertReturningColumns() { + val rs = database.bulkInsertOrUpdateReturning(Employees) { + item { + set(it.id, 10001) + 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, 50001) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 2) + } + returning( + it.id, + it.job + ) + } + + assertEquals(rs.first, 2) + rs.second.next() + assertEquals(rs.second.getInt("id"), 10001) + assertEquals(rs.second.getString("job"), "trainee") + rs.second.next() + assertEquals(rs.second.getInt("id"), 50001) + assertEquals(rs.second.getString("job"), "engineer") + } + + @Test + fun testBulkInsertOrUpdateReturningColumns() { + val rs = database.bulkInsertOrUpdateReturning(Employees) { + item { + set(it.id, 1000) + 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, 5000) + 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.departmentId, excluded(it.departmentId)) + set(it.salary, it.salary + 1000) + } + returning( + it.id, + it.job + ) + } + + assertEquals(rs.first, 2) + rs.second.next() + assertEquals(rs.second.getInt("id"), 1000) + assertEquals(rs.second.getString("job"), "trainee") + rs.second.next() + assertEquals(rs.second.getInt("id"), 5000) + assertEquals(rs.second.getString("job"), "engineer") + } + @Test fun testInsertAndGenerateKey() { val id = database.insertAndGenerateKey(Employees) { From b3a220c361167799677e5bff243cbc638ebccc84 Mon Sep 17 00:00:00 2001 From: PedroD Date: Sat, 9 Jan 2021 23:43:51 +0000 Subject: [PATCH 03/10] Fixing code format --- .../kotlin/org/ktorm/support/postgresql/BulkInsert.kt | 6 ++++++ .../kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt | 3 +++ .../org/ktorm/support/postgresql/PostgreSqlDialect.kt | 8 ++++++-- .../kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt | 4 ++-- 4 files changed, 17 insertions(+), 4 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index 2fcaf1ae..a3a217dc 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -404,6 +404,9 @@ public open class BulkInsertReturningStatementBuilder>(internal } } + /** + * Specify the columns to return + */ public fun returning(vararg returningColumns: Column<*>) { this.returningColumns += returningColumns } @@ -428,6 +431,9 @@ public class BulkInsertOrUpdateReturningStatementBuilder>(table conflictColumns += columns } + /** + * Specify the columns to return + */ public fun returning(vararg returningColumns: Column<*>) { this.returningColumns += returningColumns } diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index 77abd9a8..5c5e5303 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -242,6 +242,9 @@ public class InsertOrUpdateReturningColumnsStatementBuilder : PostgreSqlAssignme conflictColumns += columns } + /** + * Specify the columns to return + */ public fun returning(vararg returningColumns: Column<*>) { this.returningColumns += returningColumns } diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt index 8588949c..282978df 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt @@ -165,7 +165,9 @@ public open class PostgreSqlFormatter( return expr } - protected open fun visitInsertOrUpdateReturningColumns(expr: InsertOrUpdateReturningColumnsExpression): InsertOrUpdateReturningColumnsExpression { + protected open fun visitInsertOrUpdateReturningColumns( + expr: InsertOrUpdateReturningColumnsExpression + ): InsertOrUpdateReturningColumnsExpression { writeKeyword("insert into ") visitTable(expr.table) writeInsertColumnNames(expr.assignments.map { it.column }) @@ -191,7 +193,9 @@ public open class PostgreSqlFormatter( return expr } - protected open fun visitBulkInsertReturningColumns(expr: BulkInsertReturningExpression): BulkInsertReturningExpression { + protected open fun visitBulkInsertReturningColumns( + expr: BulkInsertReturningExpression + ): BulkInsertReturningExpression { writeKeyword("insert into ") visitTable(expr.table) writeInsertColumnNames(expr.assignments[0].map { it.column }) diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt index 7c115a94..c146d68f 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt @@ -252,7 +252,7 @@ class PostgreSqlTest : BaseTest() { onConflict(it.id) { set(it.job, it.job) set(it.departmentId, excluded(it.departmentId)) - set(it.salary, it.salary + 1000) + set(it.salary, excluded(it.salary) + 1000) } } @@ -271,7 +271,7 @@ class PostgreSqlTest : BaseTest() { @Test fun testBulkInsertReturningColumns() { - val rs = database.bulkInsertOrUpdateReturning(Employees) { + val rs = database.bulkInsertReturning(Employees) { item { set(it.id, 10001) set(it.name, "vince") From 569b5b75529a5cccea35130765790e15c118b2e7 Mon Sep 17 00:00:00 2001 From: PedroD Date: Sat, 9 Jan 2021 23:53:13 +0000 Subject: [PATCH 04/10] Fixing end of sentence --- .../main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt | 4 ++-- .../kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt | 2 +- 2 files changed, 3 insertions(+), 3 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index a3a217dc..6370deb9 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -405,7 +405,7 @@ public open class BulkInsertReturningStatementBuilder>(internal } /** - * Specify the columns to return + * Specify the columns to return. */ public fun returning(vararg returningColumns: Column<*>) { this.returningColumns += returningColumns @@ -432,7 +432,7 @@ public class BulkInsertOrUpdateReturningStatementBuilder>(table } /** - * Specify the columns to return + * Specify the columns to return. */ public fun returning(vararg returningColumns: Column<*>) { this.returningColumns += returningColumns diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index 5c5e5303..b29d6242 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -243,7 +243,7 @@ public class InsertOrUpdateReturningColumnsStatementBuilder : PostgreSqlAssignme } /** - * Specify the columns to return + * Specify the columns to return. */ public fun returning(vararg returningColumns: Column<*>) { this.returningColumns += returningColumns From a224860d39373c8c676b42e32d89a62a2e305b50 Mon Sep 17 00:00:00 2001 From: PedroD Date: Sun, 10 Jan 2021 00:04:10 +0000 Subject: [PATCH 05/10] Fixing accidental change in test --- .../test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt index c146d68f..3abbf4ed 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt @@ -252,7 +252,7 @@ class PostgreSqlTest : BaseTest() { onConflict(it.id) { set(it.job, it.job) set(it.departmentId, excluded(it.departmentId)) - set(it.salary, excluded(it.salary) + 1000) + set(it.salary, it.salary + 1000) } } From 157f581fbcffba3c88ae3962dbff9a406ee39962 Mon Sep 17 00:00:00 2001 From: PedroD Date: Sun, 10 Jan 2021 13:09:21 +0000 Subject: [PATCH 06/10] Making function names consistent --- .../org/ktorm/support/postgresql/BulkInsert.kt | 4 ++-- .../org/ktorm/support/postgresql/InsertOrUpdate.kt | 4 ++-- .../org/ktorm/support/postgresql/PostgreSqlTest.kt | 12 ++++++------ 3 files changed, 10 insertions(+), 10 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index 6370deb9..30156065 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -264,7 +264,7 @@ public data class BulkInsertReturningExpression( * Usage: * * ```kotlin - * database.bulkInsert(Employees) { + * database.bulkInsertReturning(Employees) { * item { * set(it.name, "jerry") * set(it.job, "trainee") @@ -314,7 +314,7 @@ public fun > Database.bulkInsertReturning( * Usage: * * ```kotlin - * database.bulkInsertOrUpdate(Employees) { + * database.bulkInsertOrUpdateReturning(Employees) { * item { * set(it.id, 1) * set(it.name, "vince") diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index b29d6242..9c29dbae 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -169,7 +169,7 @@ public data class InsertOrUpdateReturningColumnsExpression( * Usage: * * ```kotlin - * database.insertOrUpdate(Employees) { + * database.insertOrUpdateReturning(Employees) { * set(it.id, 1) * set(it.name, "vince") * set(it.job, "engineer") @@ -199,7 +199,7 @@ public data class InsertOrUpdateReturningColumnsExpression( * @param block the DSL block used to construct the expression. * @return the effected row count. */ -public fun > Database.insertOrUpdateReturningColumns( +public fun > Database.insertOrUpdateReturning( table: T, block: InsertOrUpdateReturningColumnsStatementBuilder.(T) -> Unit ): Pair { diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt index 3abbf4ed..8ecea806 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt @@ -163,8 +163,8 @@ class PostgreSqlTest : BaseTest() { } @Test - fun testInsertOrUpdateReturningColumns() { - val t1 = database.insertOrUpdateReturningColumns(Employees) { + fun testInsertOrUpdateReturning() { + val t1 = database.insertOrUpdateReturning(Employees) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -187,7 +187,7 @@ class PostgreSqlTest : BaseTest() { assert(t1.second.getString("name") == "vince") assert(t1.second.getInt("id") == 1001) - val t2 = database.insertOrUpdateReturningColumns(Employees) { + val t2 = database.insertOrUpdateReturning(Employees) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -212,7 +212,7 @@ class PostgreSqlTest : BaseTest() { assert(t2.second.getString("name") == "vince") assert(t2.second.getInt("id") == 1001) - val t3 = database.insertOrUpdateReturningColumns(Employees) { + val t3 = database.insertOrUpdateReturning(Employees) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -270,7 +270,7 @@ class PostgreSqlTest : BaseTest() { } @Test - fun testBulkInsertReturningColumns() { + fun testBulkInsertReturning() { val rs = database.bulkInsertReturning(Employees) { item { set(it.id, 10001) @@ -304,7 +304,7 @@ class PostgreSqlTest : BaseTest() { } @Test - fun testBulkInsertOrUpdateReturningColumns() { + fun testBulkInsertOrUpdateReturning() { val rs = database.bulkInsertOrUpdateReturning(Employees) { item { set(it.id, 1000) From 7cfc55c5934d8536c5e49bafba542052e76ed31a Mon Sep 17 00:00:00 2001 From: PedroD Date: Wed, 20 Jan 2021 17:53:27 +0000 Subject: [PATCH 07/10] Removing redundant expressions --- .../ktorm/support/postgresql/BulkInsert.kt | 31 ++----------------- .../support/postgresql/InsertOrUpdate.kt | 26 ++-------------- 2 files changed, 6 insertions(+), 51 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index 30156065..590f2770 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -48,6 +48,7 @@ public data class BulkInsertExpression( 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() @@ -225,32 +226,6 @@ public class BulkInsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBu } } -/** - * Bulk insert expression, represents a bulk insert statement in PostgreSQL. - * - * For example: - * - * ```sql - * insert into table (column1, column2) values (?, ?), (?, ?), (?, ?)... - * `on conflict (key) do update set (...) returning ... - * ``` - * - * @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 happens. - * @property updateAssignments the updated column assignments while key conflict exists. - * @property returningColumns the columns to returning. - */ -public data class BulkInsertReturningExpression( - val table: TableExpression, - val assignments: List>>, - val conflictColumns: List> = emptyList(), - val updateAssignments: List> = emptyList(), - val returningColumns: List>, - override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() -) : SqlExpression() - /** * Construct a bulk insert expression in the given closure, then execute it and return the effected row count. * @@ -299,7 +274,7 @@ public fun > Database.bulkInsertReturning( table: T, block: BulkInsertReturningStatementBuilder.(T) -> Unit ): Pair { val builder = BulkInsertReturningStatementBuilder(table).apply { block(table) } - val expression = BulkInsertReturningExpression( + val expression = BulkInsertExpression( table.asExpression(), builder.assignments, returningColumns = builder.returningColumns.ifEmpty { table.primaryKeys }.map { it.asExpression() } @@ -370,7 +345,7 @@ public fun > Database.bulkInsertOrUpdateReturning( throw IllegalStateException(msg) } - val expression = BulkInsertReturningExpression( + val expression = BulkInsertExpression( table = table.asExpression(), assignments = builder.assignments, conflictColumns = conflictColumns.map { it.asExpression() }, diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index 9c29dbae..2ef18086 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -41,6 +41,7 @@ public data class InsertOrUpdateExpression( 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() @@ -141,27 +142,6 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { } } -/** - * Insert or update expression, represents an insert statement with an - * `on conflict (key) do update set (...) returning ...` clause in PostgreSQL, - * capable of retrieving columns. - * - * @property table the table to be inserted. - * @property assignments the inserted column assignments. - * @property conflictColumns the index columns on which the conflict may happens. - * @property updateAssignments the updated column assignments while any key conflict exists. - * @property returningColumns the columns to returning. - */ -public data class InsertOrUpdateReturningColumnsExpression( - val table: TableExpression, - val assignments: List>, - val conflictColumns: List> = emptyList(), - val updateAssignments: List> = emptyList(), - val returningColumns: List>, - 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. @@ -194,7 +174,7 @@ public data class InsertOrUpdateReturningColumnsExpression( * returning id, job * ``` * - * @since 3.4 + * @since 3.4.0 * @param table the table to be inserted. * @param block the DSL block used to construct the expression. * @return the effected row count. @@ -213,7 +193,7 @@ public fun > Database.insertOrUpdateReturning( throw IllegalStateException(msg) } - val expression = InsertOrUpdateReturningColumnsExpression( + val expression = InsertOrUpdateExpression( table = table.asExpression(), assignments = builder.assignments, conflictColumns = builder.conflictColumns.ifEmpty { primaryKeys }.map { it.asExpression() }, From 25852c325e1532817f43d92a62e24d98b86b140c Mon Sep 17 00:00:00 2001 From: PedroD Date: Mon, 25 Jan 2021 17:25:06 +0000 Subject: [PATCH 08/10] Adding static access overloads to modified methods --- .../ktorm/support/postgresql/BulkInsert.kt | 88 +++++++--- .../support/postgresql/InsertOrUpdate.kt | 77 +++++++-- .../support/postgresql/PostgreSqlDialect.kt | 49 +----- .../support/postgresql/PostgreSqlTest.kt | 151 +++++++++++++----- 4 files changed, 234 insertions(+), 131 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index 590f2770..acf44676 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -18,6 +18,7 @@ package org.ktorm.support.postgresql 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 @@ -239,7 +240,7 @@ public class BulkInsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBu * Usage: * * ```kotlin - * database.bulkInsertReturning(Employees) { + * database.bulkInsertReturning(Employees, Pair(Employees.id, Employees.job)) { * item { * set(it.name, "jerry") * set(it.job, "trainee") @@ -256,11 +257,6 @@ public class BulkInsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBu * set(it.salary, 100) * set(it.departmentId, 2) * } - * - * returning ( - * it.id, - * it.job - * ) * } * ``` * @@ -270,15 +266,74 @@ public class BulkInsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBu * @return the effected row count. * @see batchInsert */ -public fun > Database.bulkInsertReturning( - table: T, block: BulkInsertReturningStatementBuilder.(T) -> Unit +public fun , R : Any> Database.bulkInsertReturning( + table: T, + returningColumn: Column, + block: BulkInsertReturningStatementBuilder.(T) -> Unit +): List { + val (_, rowSet) = this.bulkInsertReturningAux( + table, + listOf(returningColumn), + block + ) + + return rowSet.asIterable().map { row -> + returningColumn.sqlType.getResult(row, 1) + } +} + +public fun , R1 : Any, R2 : Any> Database.bulkInsertReturning( + table: T, + returningColumns: Pair, Column>, + block: BulkInsertReturningStatementBuilder.(T) -> Unit +): List> { + val (_, rowSet) = this.bulkInsertReturningAux( + table, + returningColumns.toList(), + block + ) + + return rowSet.asIterable().map { row -> + Pair( + returningColumns.first.sqlType.getResult(row, 1), + returningColumns.second.sqlType.getResult(row, 2) + ) + } +} + +public fun , R1 : Any, R2 : Any, R3 : Any> Database.bulkInsertReturning( + table: T, + returningColumns: Triple, Column, Column>, + block: BulkInsertReturningStatementBuilder.(T) -> Unit +): List> { + val (_, rowSet) = this.bulkInsertReturningAux( + table, + returningColumns.toList(), + block + ) + + return rowSet.asIterable().map { row -> + Triple( + returningColumns.first.sqlType.getResult(row, 1), + returningColumns.second.sqlType.getResult(row, 2), + returningColumns.third.sqlType.getResult(row, 3) + ) + } +} + +private fun > Database.bulkInsertReturningAux( + table: T, + returningColumns: List>, + block: BulkInsertReturningStatementBuilder.(T) -> Unit ): Pair { val builder = BulkInsertReturningStatementBuilder(table).apply { block(table) } + val expression = BulkInsertExpression( table.asExpression(), builder.assignments, - returningColumns = builder.returningColumns.ifEmpty { table.primaryKeys }.map { it.asExpression() } + returningColumns = returningColumns.map { it.asExpression() } ) + return executeUpdateAndRetrieveKeys(expression) } @@ -289,7 +344,7 @@ public fun > Database.bulkInsertReturning( * Usage: * * ```kotlin - * database.bulkInsertOrUpdateReturning(Employees) { + * database.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.name)) { * item { * set(it.id, 1) * set(it.name, "vince") @@ -309,11 +364,6 @@ public fun > Database.bulkInsertReturning( * onConflict { * set(it.salary, it.salary + 900) * } - * - * returning ( - * it.id, - * it.job - * ) * } * ``` * @@ -362,7 +412,6 @@ public fun > Database.bulkInsertOrUpdateReturning( @KtormDsl public open class BulkInsertReturningStatementBuilder>(internal val table: T) { internal val assignments = ArrayList>>() - internal val returningColumns = ArrayList>() /** * Add the assignments of a new row to the bulk insert. @@ -378,13 +427,6 @@ public open class BulkInsertReturningStatementBuilder>(internal throw IllegalArgumentException("Every item in a batch operation must be the same.") } } - - /** - * Specify the columns to return. - */ - public fun returning(vararg returningColumns: Column<*>) { - this.returningColumns += returningColumns - } } /** diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index 2ef18086..8f276d4e 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -18,6 +18,7 @@ package org.ktorm.support.postgresql 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.expression.ColumnAssignmentExpression @@ -87,7 +88,7 @@ public fun > Database.insertOrUpdate( if (conflictColumns.isEmpty()) { val msg = "Table '$table' doesn't have a primary key, " + - "you must specify the conflict columns when calling onConflict(col) { .. }" + "you must specify the conflict columns when calling onConflict(col) { .. }" throw IllegalStateException(msg) } @@ -149,7 +150,7 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { * Usage: * * ```kotlin - * database.insertOrUpdateReturning(Employees) { + * database.insertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) { * set(it.id, 1) * set(it.name, "vince") * set(it.job, "engineer") @@ -159,10 +160,6 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { * onDuplicateKey { * set(it.salary, it.salary + 900) * } - * returning ( - * it.id, - * it.job - * ) * } * ``` * @@ -179,8 +176,64 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { * @param block the DSL block used to construct the expression. * @return the effected row count. */ -public fun > Database.insertOrUpdateReturning( +public fun , R : Any> Database.insertOrUpdateReturning( table: T, + returningColumn: Column, + block: InsertOrUpdateReturningColumnsStatementBuilder.(T) -> Unit +): R? { + val (_, rowSet) = this.insertOrUpdateReturningAux( + table, + listOfNotNull(returningColumn), + block + ) + + return rowSet.asIterable().map { row -> + returningColumn.sqlType.getResult(row, 1) + }.first() +} + +public fun , R1 : Any, R2 : Any> Database.insertOrUpdateReturning( + table: T, + returningColumns: Pair, Column>, + block: InsertOrUpdateReturningColumnsStatementBuilder.(T) -> Unit +): Pair { + val (_, rowSet) = this.insertOrUpdateReturningAux( + table, + returningColumns.toList(), + block + ) + + return rowSet.asIterable().map { row -> + Pair( + returningColumns.first.sqlType.getResult(row, 1), + returningColumns.second.sqlType.getResult(row, 2) + ) + }.first() +} + +public fun , R1 : Any, R2 : Any, R3 : Any> Database.insertOrUpdateReturning( + table: T, + returningColumns: Triple, Column, Column>, + block: InsertOrUpdateReturningColumnsStatementBuilder.(T) -> Unit +): Triple { + val (_, rowSet) = this.insertOrUpdateReturningAux( + table, + returningColumns.toList(), + block + ) + + return rowSet.asIterable().map { row -> + Triple( + returningColumns.first.sqlType.getResult(row, 1), + returningColumns.second.sqlType.getResult(row, 2), + returningColumns.third.sqlType.getResult(row, 3) + ) + }.first() +} + +private fun > Database.insertOrUpdateReturningAux( + table: T, + returningColumns: List>, block: InsertOrUpdateReturningColumnsStatementBuilder.(T) -> Unit ): Pair { val builder = InsertOrUpdateReturningColumnsStatementBuilder().apply { block(table) } @@ -198,7 +251,7 @@ public fun > Database.insertOrUpdateReturning( assignments = builder.assignments, conflictColumns = builder.conflictColumns.ifEmpty { primaryKeys }.map { it.asExpression() }, updateAssignments = builder.updateAssignments, - returningColumns = builder.returningColumns.ifEmpty { primaryKeys }.map { it.asExpression() } + returningColumns = returningColumns.map { it.asExpression() } ) return executeUpdateAndRetrieveKeys(expression) @@ -211,7 +264,6 @@ public fun > Database.insertOrUpdateReturning( public class InsertOrUpdateReturningColumnsStatementBuilder : PostgreSqlAssignmentsBuilder() { internal val updateAssignments = ArrayList>() internal val conflictColumns = ArrayList>() - internal val returningColumns = ArrayList>() /** * Specify the update assignments while any key conflict exists. @@ -221,11 +273,4 @@ public class InsertOrUpdateReturningColumnsStatementBuilder : PostgreSqlAssignme updateAssignments += builder.assignments conflictColumns += columns } - - /** - * Specify the columns to return. - */ - public fun returning(vararg returningColumns: Column<*>) { - this.returningColumns += returningColumns - } } diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt index 282978df..8fa6731b 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt @@ -48,8 +48,6 @@ public open class PostgreSqlFormatter( override fun visit(expr: SqlExpression): SqlExpression { val result = when (expr) { is InsertOrUpdateExpression -> visitInsertOrUpdate(expr) - is InsertOrUpdateReturningColumnsExpression -> visitInsertOrUpdateReturningColumns(expr) - is BulkInsertReturningExpression -> visitBulkInsertReturningColumns(expr) is BulkInsertExpression -> visitBulkInsert(expr) else -> super.visit(expr) } @@ -162,25 +160,6 @@ public open class PostgreSqlFormatter( visitColumnAssignments(expr.updateAssignments) } - return expr - } - - protected open fun visitInsertOrUpdateReturningColumns( - expr: InsertOrUpdateReturningColumnsExpression - ): InsertOrUpdateReturningColumnsExpression { - writeKeyword("insert into ") - visitTable(expr.table) - writeInsertColumnNames(expr.assignments.map { it.column }) - writeKeyword("values ") - writeInsertValues(expr.assignments) - - if (expr.updateAssignments.isNotEmpty()) { - writeKeyword("on conflict ") - writeInsertColumnNames(expr.conflictColumns) - writeKeyword("do update set ") - visitColumnAssignments(expr.updateAssignments) - } - if (expr.returningColumns.isNotEmpty()) { writeKeyword(" returning ") expr.returningColumns.forEachIndexed { i, column -> @@ -193,9 +172,7 @@ public open class PostgreSqlFormatter( return expr } - protected open fun visitBulkInsertReturningColumns( - expr: BulkInsertReturningExpression - ): BulkInsertReturningExpression { + protected open fun visitBulkInsert(expr: BulkInsertExpression): BulkInsertExpression { writeKeyword("insert into ") visitTable(expr.table) writeInsertColumnNames(expr.assignments[0].map { it.column }) @@ -227,30 +204,6 @@ public open class PostgreSqlFormatter( 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.updateAssignments.isNotEmpty()) { - writeKeyword("on conflict ") - writeInsertColumnNames(expr.conflictColumns) - writeKeyword("do update set ") - visitColumnAssignments(expr.updateAssignments) - } - - return expr - } } /** diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt index 8ecea806..97bf9efd 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt @@ -164,30 +164,31 @@ class PostgreSqlTest : BaseTest() { @Test fun testInsertOrUpdateReturning() { - val t1 = database.insertOrUpdateReturning(Employees) { - set(it.id, 1001) - set(it.name, "vince") + database.insertOrUpdateReturning( + Employees, + Employees.id + ) { + set(it.id, 1009) + set(it.name, "pedro") set(it.job, "engineer") - set(it.salary, 1000) + set(it.salary, 1500) set(it.hireDate, LocalDate.now()) set(it.departmentId, 1) onDuplicateKey { set(it.salary, it.salary + 900) } - - returning( - it.name, - it.id - ) + }.let { createdId -> + assert(createdId == 1009) } - assert(t1.first == 1) - t1.second.next() - assert(t1.second.getString("name") == "vince") - assert(t1.second.getInt("id") == 1001) - - val t2 = database.insertOrUpdateReturning(Employees) { + database.insertOrUpdateReturning( + Employees, + Pair( + Employees.id, + Employees.name + ) + ) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -195,24 +196,22 @@ class PostgreSqlTest : BaseTest() { set(it.hireDate, LocalDate.now()) set(it.departmentId, 1) - onDuplicateKey(it.id) { + onDuplicateKey { set(it.salary, it.salary + 900) } - - returning( - it.name, - it.id, - it.salary - ) + }.let { (createdId, createdName) -> + assert(createdId == 1001) + assert(createdName == "vince") } - assert(t2.first == 1) - t2.second.next() - assert(t2.second.getInt("salary") == 1900) - assert(t2.second.getString("name") == "vince") - assert(t2.second.getInt("id") == 1001) - - val t3 = database.insertOrUpdateReturning(Employees) { + database.insertOrUpdateReturning( + Employees, + Triple( + Employees.id, + Employees.name, + Employees.salary + ) + ) { set(it.id, 1001) set(it.name, "vince") set(it.job, "engineer") @@ -223,11 +222,11 @@ class PostgreSqlTest : BaseTest() { onDuplicateKey(it.id) { set(it.salary, it.salary + 900) } + }.let { (createdId, createdName, createdSalary) -> + assert(createdId == 1001) + assert(createdName == "vince") + assert(createdSalary == 1900L) } - - assert(t3.first == 1) - t3.second.next() - assert(t3.second.getInt("id") == 1001) } @Test @@ -271,7 +270,10 @@ class PostgreSqlTest : BaseTest() { @Test fun testBulkInsertReturning() { - val rs = database.bulkInsertReturning(Employees) { + database.bulkInsertReturning( + Employees, + Employees.id + ) { item { set(it.id, 10001) set(it.name, "vince") @@ -288,19 +290,80 @@ class PostgreSqlTest : BaseTest() { set(it.hireDate, LocalDate.now()) set(it.departmentId, 2) } - returning( - it.id, - it.job + }.let { createdIds -> + assert(createdIds.size == 2) + assert( + listOf( + 10001, + 50001 + ) == createdIds ) } - assertEquals(rs.first, 2) - rs.second.next() - assertEquals(rs.second.getInt("id"), 10001) - assertEquals(rs.second.getString("job"), "trainee") - rs.second.next() - assertEquals(rs.second.getInt("id"), 50001) - assertEquals(rs.second.getString("job"), "engineer") + database.bulkInsertReturning( + Employees, + Pair( + Employees.id, + Employees.name + ) + ) { + item { + set(it.id, 10002) + 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, 50002) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 2) + } + }.let { created -> + assert( + listOf( + (10002 to "vince"), + (50002 to "vince") + ) == created + ) + } + + database.bulkInsertReturning( + Employees, + Triple( + Employees.id, + Employees.name, + Employees.job + ) + ) { + item { + set(it.id, 10003) + 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, 50003) + set(it.name, "vince") + set(it.job, "engineer") + set(it.salary, 1000) + set(it.hireDate, LocalDate.now()) + set(it.departmentId, 2) + } + }.let { created -> + assert( + listOf( + Triple(10003,"vince","trainee"), + Triple(50003,"vince","engineer") + ) == created + ) + } } @Test From b556513d0e0942753446be05aa58bcdf8823fe8b Mon Sep 17 00:00:00 2001 From: PedroD Date: Mon, 25 Jan 2021 18:09:56 +0000 Subject: [PATCH 09/10] Adding documentation --- .../ktorm/support/postgresql/BulkInsert.kt | 307 ++++++++++++++---- .../support/postgresql/InsertOrUpdate.kt | 75 ++++- .../support/postgresql/PostgreSqlTest.kt | 26 +- 3 files changed, 329 insertions(+), 79 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index acf44676..7fc7a245 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -235,12 +235,12 @@ public class BulkInsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBu * is much better than [batchInsert]. * * The generated SQL is like: `insert into table (column1, column2) values (?, ?), (?, ?), (?, ?)... - * returning column1, column2, ...`. + * returning id`. * * Usage: * * ```kotlin - * database.bulkInsertReturning(Employees, Pair(Employees.id, Employees.job)) { + * database.bulkInsertReturning(Employees, Employees.id) { * item { * set(it.name, "jerry") * set(it.job, "trainee") @@ -263,13 +263,14 @@ public class BulkInsertOrUpdateOnConflictClauseBuilder : PostgreSqlAssignmentsBu * @since 3.4.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. + * @param returningColumn the column to return + * @return the returning column value. * @see batchInsert */ public fun , R : Any> Database.bulkInsertReturning( table: T, returningColumn: Column, - block: BulkInsertReturningStatementBuilder.(T) -> Unit + block: BulkInsertStatementBuilder.(T) -> Unit ): List { val (_, rowSet) = this.bulkInsertReturningAux( table, @@ -282,10 +283,50 @@ public fun , R : Any> Database.bulkInsertReturning( } } +/** + * 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 PostgreSQL'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 (?, ?), (?, ?), (?, ?)... + * returning id, job`. + * + * 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) + * } + * } + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. + * @param returningColumns the columns to return + * @return the returning columns' values. + * @see batchInsert + */ public fun , R1 : Any, R2 : Any> Database.bulkInsertReturning( table: T, returningColumns: Pair, Column>, - block: BulkInsertReturningStatementBuilder.(T) -> Unit + block: BulkInsertStatementBuilder.(T) -> Unit ): List> { val (_, rowSet) = this.bulkInsertReturningAux( table, @@ -301,10 +342,50 @@ public fun , R1 : Any, R2 : Any> Database.bulkInsertReturning( } } +/** + * 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 PostgreSQL'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 (?, ?), (?, ?), (?, ?)... + * returning id, job, salary`. + * + * 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) + * } + * } + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param block the DSL block, extension function of [BulkInsertStatementBuilder], used to construct the expression. + * @param returningColumns the columns to return + * @return the returning columns' values. + * @see batchInsert + */ public fun , R1 : Any, R2 : Any, R3 : Any> Database.bulkInsertReturning( table: T, returningColumns: Triple, Column, Column>, - block: BulkInsertReturningStatementBuilder.(T) -> Unit + block: BulkInsertStatementBuilder.(T) -> Unit ): List> { val (_, rowSet) = this.bulkInsertReturningAux( table, @@ -324,9 +405,9 @@ public fun , R1 : Any, R2 : Any, R3 : Any> Database.bulkInsertR private fun > Database.bulkInsertReturningAux( table: T, returningColumns: List>, - block: BulkInsertReturningStatementBuilder.(T) -> Unit + block: BulkInsertStatementBuilder.(T) -> Unit ): Pair { - val builder = BulkInsertReturningStatementBuilder(table).apply { block(table) } + val builder = BulkInsertStatementBuilder(table).apply { block(table) } val expression = BulkInsertExpression( table.asExpression(), @@ -379,13 +460,163 @@ private fun > Database.bulkInsertReturningAux( * @since 3.4.0 * @param table the table to be inserted. * @param block the DSL block used to construct the expression. - * @return the effected row count. + * @param returningColumn the column to return + * @return the returning column value. * @see bulkInsert */ -public fun > Database.bulkInsertOrUpdateReturning( - table: T, block: BulkInsertOrUpdateReturningStatementBuilder.(T) -> Unit +public fun , R : Any> Database.bulkInsertOrUpdateReturning( + table: T, + returningColumn: Column, + block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): List { + val (_, rowSet) = this.bulkInsertOrUpdateReturningAux( + table, + listOf(returningColumn), + block + ) + + return rowSet.asIterable().map { row -> + returningColumn.sqlType.getResult(row, 1) + } +} + +/** + * 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.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.name)) { + * 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 block the DSL block used to construct the expression. + * @param returningColumns the column to return + * @return the returning columns' values. + * @see bulkInsert + */ +public fun , R1 : Any, R2 : Any> Database.bulkInsertOrUpdateReturning( + table: T, + returningColumns: Pair, Column>, + block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): List> { + val (_, rowSet) = this.bulkInsertOrUpdateReturningAux( + table, + returningColumns.toList(), + block + ) + + return rowSet.asIterable().map { row -> + Pair( + returningColumns.first.sqlType.getResult(row, 1), + returningColumns.second.sqlType.getResult(row, 2) + ) + } +} + +/** + * 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.bulkInsertOrUpdateReturning(Employees, Pair(Employees.id, Employees.name, 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, ... + * ``` + * + * @since 3.4.0 + * @param table the table to be inserted. + * @param block the DSL block used to construct the expression. + * @param returningColumns the column to return + * @return the returning columns' values. + * @see bulkInsert + */ +public fun , R1 : Any, R2 : Any, R3 : Any> Database.bulkInsertOrUpdateReturning( + table: T, + returningColumns: Triple, Column, Column>, + block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit +): List> { + val (_, rowSet) = this.bulkInsertOrUpdateReturningAux( + table, + returningColumns.toList(), + block + ) + + return rowSet.asIterable().map { row -> + Triple( + returningColumns.first.sqlType.getResult(row, 1), + returningColumns.second.sqlType.getResult(row, 2), + returningColumns.third.sqlType.getResult(row, 3) + ) + } +} + +private fun > Database.bulkInsertOrUpdateReturningAux( + table: T, + returningColumns: List>, + block: BulkInsertOrUpdateStatementBuilder.(T) -> Unit ): Pair { - val builder = BulkInsertOrUpdateReturningStatementBuilder(table).apply { block(table) } + val builder = BulkInsertOrUpdateStatementBuilder(table).apply { block(table) } val conflictColumns = builder.conflictColumns.ifEmpty { table.primaryKeys } if (conflictColumns.isEmpty()) { @@ -400,58 +631,8 @@ public fun > Database.bulkInsertOrUpdateReturning( assignments = builder.assignments, conflictColumns = conflictColumns.map { it.asExpression() }, updateAssignments = builder.updateAssignments, - returningColumns = builder.returningColumns.ifEmpty { table.primaryKeys }.map { it.asExpression() } + returningColumns = returningColumns.map { it.asExpression() } ) return executeUpdateAndRetrieveKeys(expression) } - -/** - * DSL builder for bulk insert statements. - */ -@KtormDsl -public open class BulkInsertReturningStatementBuilder>(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 = PostgreSqlAssignmentsBuilder().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 BulkInsertOrUpdateReturningStatementBuilder>(table: T) : - BulkInsertStatementBuilder(table) { - internal val updateAssignments = ArrayList>() - internal val conflictColumns = ArrayList>() - internal val returningColumns = ArrayList>() - - /** - * Specify the update assignments while any key conflict exists. - */ - public fun onConflict(vararg columns: Column<*>, block: BulkInsertOrUpdateOnConflictClauseBuilder.() -> Unit) { - val builder = BulkInsertOrUpdateOnConflictClauseBuilder().apply(block) - updateAssignments += builder.assignments - conflictColumns += columns - } - - /** - * Specify the columns to return. - */ - public fun returning(vararg returningColumns: Column<*>) { - this.returningColumns += returningColumns - } -} diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index 8f276d4e..b74c6be7 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -150,7 +150,7 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { * Usage: * * ```kotlin - * database.insertOrUpdateReturning(Employees, Pair(Employees.id, Employees.job)) { + * database.insertOrUpdateReturning(Employees, Employees.id) { * set(it.id, 1) * set(it.name, "vince") * set(it.job, "engineer") @@ -168,13 +168,14 @@ public class InsertOrUpdateStatementBuilder : PostgreSqlAssignmentsBuilder() { * ```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 + * returning id * ``` * * @since 3.4.0 * @param table the table to be inserted. + * @param returningColumn the column to return * @param block the DSL block used to construct the expression. - * @return the effected row count. + * @return the returning column value. */ public fun , R : Any> Database.insertOrUpdateReturning( table: T, @@ -192,6 +193,40 @@ public fun , R : Any> Database.insertOrUpdateReturning( }.first() } +/** + * 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.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) + * onDuplicateKey { + * 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 returningColumns the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ public fun , R1 : Any, R2 : Any> Database.insertOrUpdateReturning( table: T, returningColumns: Pair, Column>, @@ -211,6 +246,40 @@ public fun , R1 : Any, R2 : Any> Database.insertOrUpdateReturni }.first() } +/** + * 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.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) + * onDuplicateKey { + * 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 returningColumns the columns to return + * @param block the DSL block used to construct the expression. + * @return the returning columns' values. + */ public fun , R1 : Any, R2 : Any, R3 : Any> Database.insertOrUpdateReturning( table: T, returningColumns: Triple, Column, Column>, diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt index 97bf9efd..ec222cf3 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/PostgreSqlTest.kt @@ -2,7 +2,6 @@ package org.ktorm.support.postgresql import org.hamcrest.CoreMatchers.equalTo import org.hamcrest.CoreMatchers.nullValue -import org.junit.Assert.assertEquals import org.junit.Assert.assertThat import org.junit.ClassRule import org.junit.Test @@ -368,7 +367,13 @@ class PostgreSqlTest : BaseTest() { @Test fun testBulkInsertOrUpdateReturning() { - val rs = database.bulkInsertOrUpdateReturning(Employees) { + database.bulkInsertOrUpdateReturning( + Employees, + Pair( + Employees.id, + Employees.job + ) + ) { item { set(it.id, 1000) set(it.name, "vince") @@ -389,19 +394,14 @@ class PostgreSqlTest : BaseTest() { set(it.departmentId, excluded(it.departmentId)) set(it.salary, it.salary + 1000) } - returning( - it.id, - it.job + }.let { created -> + assert( + listOf( + Pair(1000, "trainee"), + Pair(5000, "engineer") + ) == created ) } - - assertEquals(rs.first, 2) - rs.second.next() - assertEquals(rs.second.getInt("id"), 1000) - assertEquals(rs.second.getString("job"), "trainee") - rs.second.next() - assertEquals(rs.second.getInt("id"), 5000) - assertEquals(rs.second.getString("job"), "engineer") } @Test From 2d50a56c8859fcb3bfc4d30bb9f841df4fae2042 Mon Sep 17 00:00:00 2001 From: PedroD Date: Tue, 26 Jan 2021 10:33:53 +0000 Subject: [PATCH 10/10] Hiding magic numbers --- .../org/ktorm/support/postgresql/BulkInsert.kt | 14 ++++++++------ .../org/ktorm/support/postgresql/InsertOrUpdate.kt | 7 ++++--- 2 files changed, 12 insertions(+), 9 deletions(-) diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt index 7fc7a245..4ab996bb 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt @@ -394,10 +394,11 @@ public fun , R1 : Any, R2 : Any, R3 : Any> Database.bulkInsertR ) return rowSet.asIterable().map { row -> + var i = 0 Triple( - returningColumns.first.sqlType.getResult(row, 1), - returningColumns.second.sqlType.getResult(row, 2), - returningColumns.third.sqlType.getResult(row, 3) + returningColumns.first.sqlType.getResult(row, ++i), + returningColumns.second.sqlType.getResult(row, ++i), + returningColumns.third.sqlType.getResult(row, ++i) ) } } @@ -603,10 +604,11 @@ public fun , R1 : Any, R2 : Any, R3 : Any> Database.bulkInsertO ) return rowSet.asIterable().map { row -> + var i = 0 Triple( - returningColumns.first.sqlType.getResult(row, 1), - returningColumns.second.sqlType.getResult(row, 2), - returningColumns.third.sqlType.getResult(row, 3) + returningColumns.first.sqlType.getResult(row, ++i), + returningColumns.second.sqlType.getResult(row, ++i), + returningColumns.third.sqlType.getResult(row, ++i) ) } } diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt index b74c6be7..290776d4 100644 --- a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt @@ -292,10 +292,11 @@ public fun , R1 : Any, R2 : Any, R3 : Any> Database.insertOrUpd ) return rowSet.asIterable().map { row -> + var i = 0 Triple( - returningColumns.first.sqlType.getResult(row, 1), - returningColumns.second.sqlType.getResult(row, 2), - returningColumns.third.sqlType.getResult(row, 3) + returningColumns.first.sqlType.getResult(row, ++i), + returningColumns.second.sqlType.getResult(row, ++i), + returningColumns.third.sqlType.getResult(row, ++i) ) }.first() }