database/gdb: postgresql Composite primary key for non-ID primary key LastInsertId() support

[thanatoskira]

Description

场景：

• 数据库：Postgresql
• 表结构：使用不包含 ID(int64, auto_increment) 的其他字段作为复合主键，eg: (Name, ProjectId)
• 关联：ID 将作为外键关联使用

Example:

_, err := dao.Company.
		OmitEmpty().
		OnConflict(
			dao.Company.Columns().Name,
			dao.Company.Columns().ProjectId,
		).
		OnDuplicateEx(
			dao.Company.Columns().Id,
			dao.Company.Columns().Name,
			dao.Company.Columns().ProjectId,
			dao.Company.Columns().CreateTime,
		).Save(companies)

需求：

• 需要使用 Save() 进行 Upsert 操作，并获取 LastInsertId
• 不希望采用写 SQL RAW 语句的方式

问题：

• Save() 在 postgresql 中实际执行为 INSERT INTO 语句，Insert() 操作会添加 RETURNING 语句，但 Save() 不会
• Insert() 操作对于不包含 ID int64的复合主键场景下，无法获取 LastInsertId() 返回值，如上述场景将执行如下语句：INSERT INTO ... RETURNING "name" ，在执行 LastInsertId() 时将返回 LastInsertId is not supported by primary key type 错误

临时解决方案：

从如下代码中可以看到，可以通过在 ctx 中配置 internalPrimaryKeyInCtx 值来控制 RETURNING 语句返回的字段

• gogf/gf/contrib/drivers/pgsql/v2@v2.7.2/pgsql_do_exec.go

const (
	internalPrimaryKeyInCtx gctx.StrKey = "primary_key"
)
// Check if it is an insert operation with primary key.
if value := ctx.Value(internalPrimaryKeyInCtx); value != nil {
  var ok bool
  pkField, ok = value.(gdb.TableField)
  if !ok {
    isUseCoreDoExec = true
  }
} else {
  isUseCoreDoExec = true
}

// check if it is an insert operation.
if !isUseCoreDoExec && pkField.Name != "" && strings.Contains(sql, "INSERT INTO") {
  primaryKey = pkField.Name
  sql += fmt.Sprintf(` RETURNING "%s"`, primaryKey)
} else {
  // use default DoExec
  return d.Core.DoExec(ctx, link, sql, args...)
}

如下代码展示了 LastInsertId() 操作需要 RETURNING xxx 中的字段需要为 int 类型

• gogf/gf/contrib/drivers/pgsql/v2@v2.7.2/pgsql_do_exec.go

affected := len(out.Records)
  if affected > 0 {
    if !strings.Contains(pkField.Type, "int") {
      return Result{
        affected:     int64(affected),
        lastInsertId: 0,
        lastInsertIdError: gerror.NewCodef(
          gcode.CodeNotSupported,
          "LastInsertId is not supported by primary key type: %s", pkField.Type),
      }, nil
    }
  ...

因此修改为如下代码后可正常调用 LastInsertId()：

result, err := dao.Company.
		Ctx(context.WithValue(ctx, consts.InternalPrimaryKeyInCtx, gdb.TableField{Name: "id", Type: "int64"})). // new add
		OmitEmpty().
		OnConflict(
			dao.Company.Columns().Name,
			dao.Company.Columns().ProjectId,
		).
		OnDuplicateEx(
			dao.Company.Columns().Id,
			dao.Company.Columns().Name,
			dao.Company.Columns().ProjectId,
			dao.Company.Columns().CreateTime,
		).Save(companies)
	if err != nil {
		return
	}
	id, err = result.LastInsertId()

总结：

• 是否可以提供相关参数对 RETURNING 字段进行控制

Additional

No response

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

Description

Scenes:

• Database: Postgresql
• Table structure: Use other fields that do not contain ID(int64, auto_increment) as composite primary keys, eg: (Name, ProjectId)
• Association: ID will be used as a foreign key association

Example:

_, err := dao.Company.
OmitEmpty().
OnConflict(
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
).
OnDuplicateEx(
dao.Company.Columns().Id,
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
dao.Company.Columns().CreateTime,
).Save(companies)

need:

• Need to use Save() to perform Upsert operation and obtain LastInsertId
• Don’t want to write SQL RAW statements

question:

• Save() is actually executed as an INSERT INTO statement in postgresql. The Insert() operation will add a RETURNING statement, but Save() will not
• Insert() operation cannot obtain the return value of LastInsertId() in the scenario of composite primary key that does not contain ID int64. In the above scenario, the following statement will be executed: INSERT INTO ... RETURNING "name", when executing LastInsertId() Will return LastInsertId is not supported by primary key type error

Temporary solution:

As you can see from the following code, you can control the fields returned by the RETURNING statement by configuring the internalPrimaryKeyInCtx value in ctx

• gogf/gf/contrib/drivers/pgsql/v2@v2.7.2/pgsql_do_exec.go

const (
internalPrimaryKeyInCtx gctx.StrKey = "primary_key"
)
// Check if it is an insert operation with primary key.
if value := ctx.Value(internalPrimaryKeyInCtx); value != nil {
  var OK bool
  pkField, ok = value.(gdb.TableField)
  if !ok {
    isUseCoreDoExec = true
  }
} else {
  isUseCoreDoExec = true
}

// check if it is an insert operation.
if !isUseCoreDoExec && pkField.Name != "" && strings.Contains(sql, "INSERT INTO") {
  primaryKey = pkField.Name
  sql += fmt.Sprintf(` RETURNING "%s"`, primaryKey)
} else {
  // use default DoExec
  return d.Core.DoExec(ctx, link, sql, args...)
}

The following code shows that the LastInsertId() operation requires RETURNING that the fields in xxx need to be of type int

• gogf/gf/contrib/drivers/pgsql/v2@v2.7.2/pgsql_do_exec.go

affected := len(out.Records)
  if affected > 0 {
    if !strings.Contains(pkField.Type, "int") {
      return Result{
        affected: int64(affected),
        lastInsertId: 0,
        lastInsertIdError: gerror.NewCodef(
          gcode.CodeNotSupported,
          "LastInsertId is not supported by primary key type: %s", pkField.Type),
      }, nil
    }
  ...

Therefore, after changing the code to the following, LastInsertId() can be called normally:

result, err := dao.Company.
Ctx(context.WithValue(ctx, consts.InternalPrimaryKeyInCtx, gdb.TableField{Name: "id", Type: "int64"})). // new add
OmitEmpty().
OnConflict(
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
).
OnDuplicateEx(
dao.Company.Columns().Id,
dao.Company.Columns().Name,
dao.Company.Columns().ProjectId,
dao.Company.Columns().CreateTime,
).Save(companies)
if err != nil {
return
}
id, err = result.LastInsertId()

Summarize:

• Whether relevant parameters can be provided to control the RETURNING field

Additional

No response