add insert,tryInsert unify for postgres that need pk name (#14416)

* add insert,tryInsert unify for postgres that need pk name
* add ReadDbEffect to new procs
* add .since and changelog
* change since to 1.3
* Update lib/impure/db_postgres.nim

Co-authored-by: bung87 <crc32@qq.com>
Co-authored-by: Andreas Rumpf <rumpf_a@web.de>
Co-authored-by: alaviss <leorize+oss@disroot.org>

4 files changed, 67 insertions, 0 deletions

diff --git a/lib/impure/db_mysql.nim b/lib/impure/db_mysql.nim
index 510503a63..c96829830 100644
--- a/lib/impure/db_mysql.nim
+++ b/lib/impure/db_mysql.nim
@@ -88,6 +88,8 @@ import strutils, mysql
 import db_common
 export db_common
 
+import std/private/since
+
 type
   DbConn* = distinct PMySQL ## encapsulates a database connection
   Row* = seq[string]   ## a row of a dataset. NULL database values will be
@@ -359,6 +361,19 @@ proc insertId*(db: DbConn, query: SqlQuery,
   result = tryInsertID(db, query, args)
   if result < 0: dbError(db)
 
+proc tryInsert*(db: DbConn, query: SqlQuery, pkName: string,
+                args: varargs[string, `$`]): int64
+               {.tags: [WriteDbEffect], raises: [], since: (1, 3).} =
+  ## same as tryInsertID
+  tryInsertID(db, query, args)
+
+proc insert*(db: DbConn, query: SqlQuery, pkName: string,
+             args: varargs[string, `$`]): int64
+            {.tags: [WriteDbEffect], since: (1, 3).} =
+  ## same as insertId
+  result = tryInsert(db, query,pkName, args)
+  if result < 0: dbError(db)
+
 proc execAffectedRows*(db: DbConn, query: SqlQuery,
                        args: varargs[string, `$`]): int64 {.
                        tags: [ReadDbEffect, WriteDbEffect].} =
diff --git a/lib/impure/db_odbc.nim b/lib/impure/db_odbc.nim
index 9ff050bfa..863cf6c8c 100644
--- a/lib/impure/db_odbc.nim
+++ b/lib/impure/db_odbc.nim
@@ -92,6 +92,8 @@ import strutils, odbcsql
 import db_common
 export db_common
 
+import std/private/since
+
 type
   OdbcConnTyp = tuple[hDb: SqlHDBC, env: SqlHEnv, stmt: SqlHStmt]
   DbConn* = OdbcConnTyp    ## encapsulates a database connection
@@ -451,6 +453,19 @@ proc insertId*(db: var DbConn, query: SqlQuery,
   result = tryInsertID(db, query, args)
   if result < 0: dbError(db)
 
+proc tryInsert*(db: var DbConn, query: SqlQuery,pkName: string,
+                args: varargs[string, `$`]): int64
+               {.tags: [ReadDbEffect, WriteDbEffect], raises: [], since: (1, 3).} =
+  ## same as tryInsertID
+  tryInsertID(db, query, args)
+
+proc insert*(db: var DbConn, query: SqlQuery, pkName: string,
+             args: varargs[string, `$`]): int64 
+            {.tags: [ReadDbEffect, WriteDbEffect], since: (1, 3).} =
+  ## same as insertId
+  result = tryInsert(db, query,pkName, args)
+  if result < 0: dbError(db)
+
 proc execAffectedRows*(db: var DbConn, query: SqlQuery,
                        args: varargs[string, `$`]): int64 {.
              tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} =
diff --git a/lib/impure/db_postgres.nim b/lib/impure/db_postgres.nim
index 57c61fa23..9e8075450 100644
--- a/lib/impure/db_postgres.nim
+++ b/lib/impure/db_postgres.nim
@@ -68,6 +68,8 @@ import strutils, postgres
 import db_common
 export db_common
 
+import std/private/since
+
 type
   DbConn* = PPGconn    ## encapsulates a database connection
   Row* = seq[string]   ## a row of a dataset. NULL database values will be
@@ -485,6 +487,26 @@ proc insertID*(db: DbConn, query: SqlQuery,
   result = tryInsertID(db, query, args)
   if result < 0: dbError(db)
 
+proc tryInsert*(db: DbConn, query: SqlQuery,pkName: string,
+                args: varargs[string, `$`]): int64
+               {.tags: [WriteDbEffect], since: (1, 3).}=
+  ## executes the query (typically "INSERT") and returns the
+  ## generated ID for the row or -1 in case of an error. 
+  var x = pqgetvalue(setupQuery(db, SqlQuery(string(query) & " RETURNING " & pkName),
+    args), 0, 0)
+  if not isNil(x):
+    result = parseBiggestInt($x)
+  else:
+    result = -1
+
+proc insert*(db: DbConn, query: SqlQuery, pkName: string,
+             args: varargs[string, `$`]): int64
+            {.tags: [WriteDbEffect], since: (1, 3).} =
+  ## executes the query (typically "INSERT") and returns the
+  ## generated ID 
+  result = tryInsertID(db, query, args)
+  if result < 0: dbError(db)
+
 proc execAffectedRows*(db: DbConn, query: SqlQuery,
                        args: varargs[string, `$`]): int64 {.tags: [
                        ReadDbEffect, WriteDbEffect].} =
diff --git a/lib/impure/db_sqlite.nim b/lib/impure/db_sqlite.nim
index c7ace0171..ff00dd86e 100644
--- a/lib/impure/db_sqlite.nim
+++ b/lib/impure/db_sqlite.nim
@@ -118,6 +118,8 @@ import sqlite3
 import db_common
 export db_common
 
+import std/private/since
+
 type
   DbConn* = PSqlite3  ## Encapsulates a database connection.
   Row* = seq[string]  ## A row of a dataset. `NULL` database values will be
@@ -636,6 +638,19 @@ proc insertID*(db: DbConn, query: SqlQuery,
   result = tryInsertID(db, query, args)
   if result < 0: dbError(db)
 
+proc tryInsert*(db: DbConn, query: SqlQuery, pkName: string,
+                args: varargs[string, `$`]): int64
+               {.tags: [WriteDbEffect], raises: [], since: (1, 3).} =
+  ## same as tryInsertID
+  tryInsertID(db, query, args)
+
+proc insert*(db: DbConn, query: SqlQuery, pkName: string,
+             args: varargs[string, `$`]): int64
+            {.tags: [WriteDbEffect], since: (1, 3).} =
+  ## same as insertId
+  result = tryInsert(db, query,pkName, args)
+  if result < 0: dbError(db)
+
 proc execAffectedRows*(db: DbConn, query: SqlQuery,
                        args: varargs[string, `$`]): int64 {.
                        tags: [ReadDbEffect, WriteDbEffect].} =