Skip to content

feat: auto-fix query and args with List of values in database/sql driver for YDB #2021

@asmyasnikov

Description

@asmyasnikov

Require behaviour

  1. If query contains IN clause
// var db *sql.DB
rows, err := db.QueryContext(ctx, "SELECT * FROM t WHERE id IN ($p1, $p2, $p3, $p4)", 
  sql.Named("p1", 1),
  sql.Named("p2", 2),
  sql.Named("p3", 3),
  sql.Named("p4", 4),
)

then final query which send to YDB must be

// var s query.Session
r, err := s.Query(ctx, "SELECT * FROM t WHERE id IN $argsList", query.WithParameters(
  ydb.ParamsBuilder().Param("$argsList").BeginList().AddItems(
	types.Uint64Value(1),
	types.Uint64Value(2),
	types.Uint64Value(3),
	types.Uint64Value(4),
  ).EndList().Build(),
))

Few IN clauses in source query must be replaced with few argsList params

    1. If query contains INSERT INTO VALUES (), (), ()
// var db *sql.DB
rows, err := db.QueryContext(ctx, "INSERT INTO t (id, value) VALUES ($p1, $p2), ($p3, $p4), ($p5, $p6)", 
  sql.Named("p1", 1),
  sql.Named("p2", "1"),
  sql.Named("p3", 2),
  sql.Named("p4", "2"),
  sql.Named("p5", 3),
  sql.Named("p6", "3"),
)

then final query which send to YDB must be

// var s query.Session
r, err := s.Query(ctx, "INSERT INTO t (id, value) SELECT id, value FROM AS_TABLE($valuesList)", query.WithParameters(
	ydb.ParamsBuilder().Param("$valuesList").BeginList().AddItems(
		types.StructValue(
			types.StructFieldValue("id", types.Uint64Value(1)),
			types.StructFieldValue("value", types.TextValue("1")),
		),
		types.StructValue(
			types.StructFieldValue("id", types.Uint64Value(2)),
			types.StructFieldValue("value", types.TextValue("2")),
		),
		types.StructValue(
			types.StructFieldValue("id", types.Uint64Value(3)),
			types.StructFieldValue("value", types.TextValue("3")),
		),
	).EndList().Build(),
))

INSERT, UPSERT, REPLACE must works equal when VALUES rows great then 1


Behaviour for replace query and args must enabled only with specific ydb.Connector option for database/sql driver

Potential place for replace queries - internal/xsql (Bind api)

Some ways for implement this feature:

  1. regexp
  2. using yql-parser from https://github.com/ydb-platform/yql-parsers/tree/main/go, detect IN and other clauses, replace
  3. is there something else (need to find out how the feature was implemented with query substitution and arguments in other databases)?

Need to discover all ways before implement - pros and cons, plus and minuses, risks and performance

Metadata

Metadata

Labels

enhancementNew feature or request

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions