Adding insert or update retrieving pgsql #233

PedroD · 2021-01-09T23:31:23Z

These functions will allow upserts and bulk upserts to be more efficient in PostgreSQL when the developer wants to upsert and receive the created/existing ID or any other column.

Adding RETURNING support for in all Insert, Bulk Insert, Insert or Update and Bulk Insert or Update operations.

Eg.:

@Test
   fun testBulkInsertReturning() {
       val rs = database.bulkInsertReturning(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 testBulkInsertOrUpdateReturning() {
       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 testInsertOrUpdateReturning() {
       val t1 = database.insertOrUpdateReturning(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.insertOrUpdateReturning(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.insertOrUpdateReturning(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)
   }

PedroD · 2021-01-10T13:38:16Z

@vincentlauvlwj it seems the test failed because travis was not able to launch the database container (unrelated to the code I've changed)?

Do you have an option to re-run the checks on your side @vincentlauvlwj ?

vincentlauvlwj

Sorry for my late response, I made some comment on your code, can your check them?

vincentlauvlwj · 2021-01-15T12:29:00Z

ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt

+ * @property updateAssignments the updated column assignments while key conflict exists.
+ * @property returningColumns the columns to returning.
+ */
+public data class BulkInsertReturningExpression(


Is it necessary to create a new expression type? We already have BulkInsertExpression, we can make the change on it. What's your opinion?

If I recall correctly, the reason why I did a new method is because its return type is different, and it would make a breakable change. Also, returning has a performance overhead that you might not want to have as default.

Oh, we can create a new method, we have to do that in this way because backward compatibility is important. What I mean is they can share the same expression class, we just need to add returningColumns to BulkInsertExpression, with a default value emptyList().

Ok, I'll try to make this change when I have time!

vincentlauvlwj · 2021-01-15T12:59:28Z

ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/InsertOrUpdate.kt

+ * @property updateAssignments the updated column assignments while any key conflict exists.
+ * @property returningColumns the columns to returning.
+ */
+public data class InsertOrUpdateReturningColumnsExpression(


Can make change on InsertOrUpdateExpression.

vincentlauvlwj · 2021-01-15T13:04:27Z

ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/BulkInsert.kt

+ */
+public fun <T : BaseTable<*>> Database.bulkInsertReturning(
+    table: T, block: BulkInsertReturningStatementBuilder<T>.(T) -> Unit
+): Pair<Int, CachedRowSet> {


I don't think it is a good practice to return a CachedRowSet, as users have to obtain result values by column name, which is unsafe without strong type and static compiler checking.

Is it possible to return a List<TupleN<C1, C2>>? It will be a great convenience as we can use the function like this:

val results = database.bulkInsertReturning(Employees) { // ... returning( it.id, it.job ) } for ((id, job) in results) { println(id) println(job) }

BTW, if we just return one column returning(it.id), the return type can be List<C>.

Sorry @vincentlauvlwj I am confused on this one.

Do you have another function that implements this so I can see how you do it?

I tried writing some pseudo code, the implementation might look like:

fun <T : BaseTable<*>, R1 : Any, R2 : Any> Database.bulkInsertReturning( table: T, returning: Pair<Column<R1>, Column<R2>>, block: BulkInsertStatementBuilder<T>.(T) -> Unit ): List<Pair<R1?, R2?>> { val expression = .... val (_, rowSet) = executeUpdateAndRetrieveKeys(expression) val (r1, r2) = returning return rowSet.asIterable().map { row -> Pair( r1.sqlType.getResult(row, 1), r2.sqlType.getResult(row, 2) ) } }

Then the usage should be:

val listOfIdAndJob = database.bulkInsertReturning(Employees, returning = Pair(Employees.id, Employees.job)) { item { .... } } for ((id, job) in listOfIdAndJob) { println(id) println(job) }

But in this way, we need to add some overloaded version of bulkInsertReturning function. Maybe 3 is enough, they respectively return:

List<R?>

List<Pair<R1?, R2?>>

List<Triple<R1?, R2?, R3?>>

Ok, I'll take a look into this, thank you for the examples.

PedroD · 2021-01-25T17:41:31Z

@vincentlauvlwj I just did these modifications.

Adding documentation

PedroD · 2021-01-26T14:56:56Z

Ready!

vincentlauvlwj · 2021-01-29T17:19:02Z

Merged, will release in next version.

FeatureSpitter added 6 commits January 9, 2021 22:52

Adding returning to single insert or update query

2c52178

Adding respective bulk operations

f1a887d

Fixing code format

b3a220c

Fixing end of sentence

569b5b7

Fixing accidental change in test

a224860

Making function names consistent

157f581

vincentlauvlwj requested changes Jan 15, 2021

View reviewed changes

FeatureSpitter added 2 commits January 20, 2021 17:53

Removing redundant expressions

7cfc55c

Adding static access overloads to modified methods

25852c3

FeatureSpitter added 2 commits January 25, 2021 18:09

b556513

Adding documentation

Hiding magic numbers

2d50a56

PedroD requested a review from vincentlauvlwj January 26, 2021 14:57

vincentlauvlwj changed the base branch from master to v3.4.x January 29, 2021 17:18

vincentlauvlwj merged commit b297540 into kotlin-orm:v3.4.x Jan 29, 2021

Uh oh!

Adding insert or update retrieving pgsql #233

Adding insert or update retrieving pgsql #233

Uh oh!

Conversation

PedroD commented Jan 9, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

PedroD commented Jan 10, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

vincentlauvlwj left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

vincentlauvlwj Jan 24, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

PedroD commented Jan 25, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

PedroD commented Jan 26, 2021

Uh oh!

vincentlauvlwj commented Jan 29, 2021

Uh oh!

Uh oh!

PedroD commented Jan 9, 2021 •

edited

Loading

PedroD commented Jan 10, 2021 •

edited

Loading

vincentlauvlwj Jan 24, 2021 •

edited

Loading

PedroD commented Jan 25, 2021 •

edited

Loading