+
Skip to content

support: Sqlite returning clause on Insert & insertOrUpdate #427

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 3 commits into from
Sep 23, 2022
Merged

support: Sqlite returning clause on Insert & insertOrUpdate #427

merged 3 commits into from
Sep 23, 2022

Conversation

bin-tenkuu
Copy link
Contributor

主要针对 50459d9

对于 executeUpdateAndRetrieveKeys 改为调用 executeQuery 可能需要讨论

对于 buildBulkInsertOrUpdateExpression 方法 给返回值加上 AliasRemover 可能需要讨论

@bin-tenkuu
Copy link
Contributor Author

mysql test 报错？

@bin-tenkuu bin-tenkuu changed the title Sqlite returning clause support: Sqlite returning clause Aug 26, 2022
@bin-tenkuu bin-tenkuu changed the title support: Sqlite returning clause support: Sqlite returning clause on Insert & insertOrUpdate Aug 26, 2022
@bin-tenkuu
Copy link
Contributor Author

bin-tenkuu commented Aug 26, 2022 •
edited
Loading

这个功能之前就能做了，但是用 executeUpdate 的方法一直提示不能有返回值。大概意思就是有了 returning 之后，不管是 delete还是 insert 还是 update 语法，统统都变成了查询语句，都得用 query 方法

@bin-tenkuu
Copy link
Contributor Author

现在只有新增操作写了 returning，修改和删除要不要也支持一下

@vincentlauvlwj
Copy link
Member

谢谢，我先找时间看看你的代码

@bin-tenkuu
Copy link
Contributor Author

bin-tenkuu commented Aug 30, 2022 •
edited
Loading

我想了一下，这个 returning 的操作感觉可以做成直接返回全部列。
就好像调用 map.remove(key) 一样会返回 key 原先对应的 value。
在数据库中进行 新增/修改/删除 操作之后返回受到影响的行。

那么新增操作可以使用 returning 返回自增主键，删除操作返回被删除的行然后映射成对象。

vincentlauvlwj
vincentlauvlwj approved these changes Sep 23, 2022
View reviewed changes
ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt
* returning id
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

since 3.6.0

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt
* returning id, job
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

同上

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt
* returning id, job, salary
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

同上

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt
table = table.asExpression(),
assignments = builder.assignments,
returningColumns = returning.map { it.asExpression() }
))
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

code style:

val expression = AliasRemover.visit(
    BulkInsertExpression(
        table = table.asExpression(),
        assignments = builder.assignments,
        returningColumns = returning.map { it.asExpression() }
    )
)

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/BulkInsert.kt
* returning id
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

since 3.6.0

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt
* returning id, job
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

同上

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt
* returning id, job, salary
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

同上

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt
* returning id
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

同上

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt
* returning id, job
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

同上

ktorm-support-sqlite/src/main/kotlin/org/ktorm/support/sqlite/InsertOrUpdate.kt
* returning id, job, salary
* ```
*
* @since 3.4.0
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

同上

@vincentlauvlwj vincentlauvlwj merged commit c7e8ca4 into kotlin-orm:v3.6.x Sep 23, 2022
@vincentlauvlwj
Copy link
Member

代码合并了，整体没有什么问题

对于 executeUpdateAndRetrieveKeys 改为调用 executeQuery 可能需要讨论

这个是 OK 的，不同数据库驱动的实现方式不一样，我们的写法不同也是正常的

对于 buildBulkInsertOrUpdateExpression 方法 给返回值加上 AliasRemover 可能需要讨论

AliasRemover 确实存在一些问题，不止 SQLite，其他方言模块也是一样，这个我会在 3.6.0 版本统一优化

现在只有新增操作写了 returning，修改和删除要不要也支持一下

returning 的主要用途是插入后返回数据库自动生成的值，比如主键，修改和删除对这个的需求很低，暂时不需要支持

returning 的操作感觉可以做成直接返回全部列

理由同上，returning 的主要用途是返回主键，既然是 insert 操作，其他字段的值用户已经有了，返回还有什么意义呢

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@vincentlauvlwj vincentlauvlwj vincentlauvlwj approved these changes

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
2 participants
@bin-tenkuu @vincentlauvlwj
点击 这是indexloc提供的php浏览器服务，不要输入任何密码和下载