bring back support for LastInsertId-based INSERT

As the remaining TODO noted, this really is much more memory-efficient
than QueryRow when we can use it, since it does not allocate an
*sql.Rows instance inside the *sql.Row instance where we call Scan().

1 files changed, 23 insertions, 0 deletions

diff --git a/dialect.go b/dialect.go
index 3c49f58..d057c8d 100644
--- a/dialect.go
+++ b/dialect.go
@@ -4,11 +4,17 @@
 package oblast
 
 import (
+	"database/sql"
 	"fmt"
 	"strconv"
 	"strings"
 )
 
+var (
+	// force imports to make docstring links work
+	_ = sql.Result(nil)
+)
+
 // Dialect accounts for differences between different SQL dialects
 // that are relevant to query generation within Oblast.
 //
@@ -27,6 +33,11 @@ type Dialect interface {
 	// in order to avoid the name from being interpreted as a keyword.
 	QuoteIdentifier(name string) string
 
+	// CanUseLastInsertId returns true if this type of database system can report
+	// a single auto-generated int primary key using [sql.Result.LastInsertId].
+	// If true, the RETURNING clause will be omitted for matching INSERT queries.
+	CanUseLastInsertId() bool
+
 	// UpsertClause generates an "ON CONFLICT" or similar clause
 	// that can be appended to an INSERT query to make it fall back to
 	// behave like UPDATE if a record with the same primary key already exists.
@@ -52,6 +63,10 @@ func (mariadbDialect) QuoteIdentifier(name string) string {
 	return "`" + strings.ReplaceAll(name, "`", "``") + "`"
 }
 
+func (mariadbDialect) CanUseLastInsertId() bool {
+	return true
+}
+
 func (d mariadbDialect) UpsertClause(pkColumns, otherColumns []string) string {
 	clauses := make([]string, max(1, len(otherColumns)))
 	if len(otherColumns) == 0 {
@@ -81,6 +96,10 @@ func (postgresDialect) QuoteIdentifier(name string) string {
 	return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
 }
 
+func (postgresDialect) CanUseLastInsertId() bool {
+	return false
+}
+
 func (d postgresDialect) UpsertClause(pkColumns, otherColumns []string) string {
 	quotedPkColumns := make([]string, len(pkColumns))
 	for idx, name := range pkColumns {
@@ -116,6 +135,10 @@ func (sqliteDialect) QuoteIdentifier(name string) string {
 	return `"` + strings.ReplaceAll(name, `"`, `""`) + `"`
 }
 
+func (sqliteDialect) CanUseLastInsertId() bool {
+	return true
+}
+
 func (sqliteDialect) UpsertClause(pkColumns, otherColumns []string) string {
 	return postgresDialect{}.UpsertClause(pkColumns, otherColumns)
 }