// golangFillQueryArguments populates the placeholder map with
// types and values from an array of Go values.
// TODO: This does not support arguments of the SQL 'Date' type, as there is not
// an equivalent type in Go's standard library. It's not currently needed by any
// of our internal tables.
func golangFillQueryArguments(pinfo *parser.PlaceholderInfo, args []interface{}) {
pinfo.Clear()
for i, arg := range args {
k := fmt.Sprint(i + 1)
if arg == nil {
pinfo.SetValue(k, parser.DNull)
continue
}
// A type switch to handle a few explicit types with special semantics:
// - Datums are passed along as is.
// - Time datatypes get special representation in the database.
var d parser.Datum
switch t := arg.(type) {
case parser.Datum:
d = t
case time.Time:
d = parser.MakeDTimestamp(t, time.Microsecond)
case time.Duration:
d = &parser.DInterval{Duration: duration.Duration{Nanos: t.Nanoseconds()}}
case *inf.Dec:
dd := &parser.DDecimal{}
dd.Set(t)
d = dd
}
if d == nil {
// Handle all types which have an underlying type that can be stored in the
// database.
// Note: if this reflection becomes a performance concern in the future,
// commonly used types could be added explicitly into the type switch above
// for a performance gain.
val := reflect.ValueOf(arg)
switch val.Kind() {
case reflect.Bool:
d = parser.MakeDBool(parser.DBool(val.Bool()))
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
d = parser.NewDInt(parser.DInt(val.Int()))
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
d = parser.NewDInt(parser.DInt(val.Uint()))
case reflect.Float32, reflect.Float64:
d = parser.NewDFloat(parser.DFloat(val.Float()))
case reflect.String:
d = parser.NewDString(val.String())
case reflect.Slice:
// Handle byte slices.
if val.Type().Elem().Kind() == reflect.Uint8 {
d = parser.NewDBytes(parser.DBytes(val.Bytes()))
}
}
if d == nil {
panic(fmt.Sprintf("unexpected type %T", arg))
}
}
pinfo.SetValue(k, d)
}
}
// Arg implements the parser.Args interface.
// TODO: This does not support arguments of the SQL 'Date' type, as there is not
// an equivalent type in Go's standard library. It's not currently needed by any
// of our internal tables.
func (gp golangParameters) Arg(name string) (parser.Datum, bool) {
i, err := processPositionalArgument(name)
if err != nil {
return nil, false
}
if i < 1 || int(i) > len(gp) {
return nil, false
}
arg := gp[i-1]
if arg == nil {
return parser.DNull, true
}
// A type switch to handle a few explicit types with special semantics.
switch t := arg.(type) {
// Datums are passed along as is.
case parser.Datum:
return t, true
// Time datatypes get special representation in the database.
case time.Time:
return parser.MakeDTimestamp(t, time.Microsecond), true
case time.Duration:
return &parser.DInterval{Duration: duration.Duration{Nanos: t.Nanoseconds()}}, true
case *inf.Dec:
dd := &parser.DDecimal{}
dd.Set(t)
return dd, true
}
// Handle all types which have an underlying type that can be stored in the
// database.
// Note: if this reflection becomes a performance concern in the future,
// commonly used types could be added explicitly into the type switch above
// for a performance gain.
val := reflect.ValueOf(arg)
switch val.Kind() {
case reflect.Bool:
return parser.MakeDBool(parser.DBool(val.Bool())), true
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
return parser.NewDInt(parser.DInt(val.Int())), true
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
return parser.NewDInt(parser.DInt(val.Uint())), true
case reflect.Float32, reflect.Float64:
return parser.NewDFloat(parser.DFloat(val.Float())), true
case reflect.String:
return parser.NewDString(val.String()), true
case reflect.Slice:
// Handle byte slices.
if val.Type().Elem().Kind() == reflect.Uint8 {
return parser.NewDBytes(parser.DBytes(val.Bytes())), true
}
}
panic(fmt.Sprintf("unexpected type %T", arg))
}
// SetUIData is an endpoint that stores the given key/value pairs in the
// system.ui table. See GetUIData for more details on semantics.
func (s *adminServer) SetUIData(ctx context.Context, req *SetUIDataRequest) (*SetUIDataResponse, error) {
if len(req.KeyValues) == 0 {
return nil, grpc.Errorf(codes.InvalidArgument, "KeyValues cannot be empty")
}
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.server.sqlExecutor, nil)
for key, val := range req.KeyValues {
// Do an upsert of the key. We update each key in a separate transaction to
// avoid long-running transactions and possible deadlocks.
br := s.server.sqlExecutor.ExecuteStatements(ctx, session, "BEGIN;", nil)
if err := s.checkQueryResults(br.ResultList, 1); err != nil {
return nil, s.serverError(err)
}
// See if the key already exists.
resp, err := s.getUIData(session, s.getUser(req), []string{key})
if err != nil {
return nil, s.serverError(err)
}
_, alreadyExists := resp.KeyValues[key]
// INSERT or UPDATE as appropriate.
if alreadyExists {
query := "UPDATE system.ui SET value = $1, lastUpdated = NOW() WHERE key = $2; COMMIT;"
params := []parser.Datum{
parser.NewDString(string(val)), // $1
parser.NewDString(key), // $2
}
r := s.server.sqlExecutor.ExecuteStatements(ctx, session, query, params)
if err := s.checkQueryResults(r.ResultList, 2); err != nil {
return nil, s.serverError(err)
}
if a, e := r.ResultList[0].RowsAffected, 1; a != e {
return nil, s.serverErrorf("rows affected %d != expected %d", a, e)
}
} else {
query := "INSERT INTO system.ui (key, value, lastUpdated) VALUES ($1, $2, NOW()); COMMIT;"
params := []parser.Datum{
parser.NewDString(key), // $1
parser.NewDBytes(parser.DBytes(val)), // $2
}
r := s.server.sqlExecutor.ExecuteStatements(ctx, session, query, params)
if err := s.checkQueryResults(r.ResultList, 2); err != nil {
return nil, s.serverError(err)
}
if a, e := r.ResultList[0].RowsAffected, 1; a != e {
return nil, s.serverErrorf("rows affected %d != expected %d", a, e)
}
}
}
return &SetUIDataResponse{}, nil
}
// RandDatum generates a random Datum of the given type.
// If null is true, the datum can be DNull.
func RandDatum(rng *rand.Rand, typ ColumnType_Kind, null bool) parser.Datum {
if null && rng.Intn(10) == 0 {
return parser.DNull
}
switch typ {
case ColumnType_BOOL:
return parser.MakeDBool(rng.Intn(2) == 1)
case ColumnType_INT:
return parser.NewDInt(parser.DInt(rng.Int63()))
case ColumnType_FLOAT:
return parser.NewDFloat(parser.DFloat(rng.NormFloat64()))
case ColumnType_DECIMAL:
d := &parser.DDecimal{}
d.Dec.SetScale(inf.Scale(rng.Intn(40) - 20))
d.Dec.SetUnscaled(rng.Int63())
return d
case ColumnType_DATE:
return parser.NewDDate(parser.DDate(rng.Intn(10000)))
case ColumnType_TIMESTAMP:
return &parser.DTimestamp{Time: time.Unix(rng.Int63n(1000000), rng.Int63n(1000000))}
case ColumnType_INTERVAL:
return &parser.DInterval{Duration: duration.Duration{Months: rng.Int63n(1000),
Days: rng.Int63n(1000),
Nanos: rng.Int63n(1000000),
}}
case ColumnType_STRING:
// Generate a random ASCII string.
p := make([]byte, rng.Intn(10))
for i := range p {
p[i] = byte(1 + rng.Intn(127))
}
return parser.NewDString(string(p))
case ColumnType_BYTES:
p := make([]byte, rng.Intn(10))
_, _ = rng.Read(p)
return parser.NewDBytes(parser.DBytes(p))
case ColumnType_TIMESTAMPTZ:
return &parser.DTimestampTZ{Time: time.Unix(rng.Int63n(1000000), rng.Int63n(1000000))}
default:
panic(fmt.Sprintf("invalid type %s", typ))
}
}
func TestValues(t *testing.T) {
defer leaktest.AfterTest(t)()
p := makePlanner()
vInt := int64(5)
vNum := 3.14159
vStr := "two furs one cub"
vBool := true
unsupp := &parser.RangeCond{}
intVal := func(v int64) *parser.NumVal {
return &parser.NumVal{Value: constant.MakeInt64(v)}
}
floatVal := func(f float64) *parser.CastExpr {
return &parser.CastExpr{
Expr: &parser.NumVal{Value: constant.MakeFloat64(f)},
Type: &parser.FloatColType{},
}
}
asRow := func(datums ...parser.Datum) []parser.DTuple {
return []parser.DTuple{datums}
}
makeValues := func(tuples ...*parser.Tuple) *parser.ValuesClause {
return &parser.ValuesClause{Tuples: tuples}
}
makeTuple := func(exprs ...parser.Expr) *parser.Tuple {
return &parser.Tuple{Exprs: exprs}
}
testCases := []struct {
stmt *parser.ValuesClause
rows []parser.DTuple
ok bool
}{
{
makeValues(makeTuple(intVal(vInt))),
asRow(parser.NewDInt(parser.DInt(vInt))),
true,
},
{
makeValues(makeTuple(intVal(vInt), intVal(vInt))),
asRow(parser.NewDInt(parser.DInt(vInt)), parser.NewDInt(parser.DInt(vInt))),
true,
},
{
makeValues(makeTuple(floatVal(vNum))),
asRow(parser.NewDFloat(parser.DFloat(vNum))),
true,
},
{
makeValues(makeTuple(parser.NewDString(vStr))),
asRow(parser.NewDString(vStr)),
true,
},
{
makeValues(makeTuple(parser.NewDBytes(parser.DBytes(vStr)))),
asRow(parser.NewDBytes(parser.DBytes(vStr))),
true,
},
{
makeValues(makeTuple(parser.MakeDBool(parser.DBool(vBool)))),
asRow(parser.MakeDBool(parser.DBool(vBool))),
true,
},
{
makeValues(makeTuple(unsupp)),
nil,
false,
},
}
for i, tc := range testCases {
plan, err := func() (_ planNode, err error) {
defer func() {
if r := recover(); r != nil {
err = errors.Errorf("%v", r)
}
}()
return p.ValuesClause(tc.stmt, nil)
}()
if err == nil != tc.ok {
t.Errorf("%d: error_expected=%t, but got error %v", i, tc.ok, err)
}
if plan != nil {
if err := plan.expandPlan(); err != nil {
t.Errorf("%d: unexpected error in expandPlan: %v", i, err)
continue
}
if err := plan.Start(); err != nil {
t.Errorf("%d: unexpected error in Start: %v", i, err)
continue
}
var rows []parser.DTuple
next, err := plan.Next()
for ; next; next, err = plan.Next() {
rows = append(rows, plan.Values())
}
if err != nil {
t.Error(err)
continue
}
if !reflect.DeepEqual(rows, tc.rows) {
t.Errorf("%d: expected rows:\n%+v\nactual rows:\n%+v", i, tc.rows, rows)
}
}
}
}
// decodeOidDatum decodes bytes with specified Oid and format code into
// a datum.
func decodeOidDatum(id oid.Oid, code formatCode, b []byte) (parser.Datum, error) {
var d parser.Datum
switch id {
case oid.T_bool:
switch code {
case formatText:
v, err := strconv.ParseBool(string(b))
if err != nil {
return d, err
}
d = parser.MakeDBool(parser.DBool(v))
default:
return d, fmt.Errorf("unsupported bool format code: %d", code)
}
case oid.T_int2:
switch code {
case formatText:
i, err := strconv.ParseInt(string(b), 10, 64)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
case formatBinary:
var i int16
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &i)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
default:
return d, fmt.Errorf("unsupported int2 format code: %d", code)
}
case oid.T_int4:
switch code {
case formatText:
i, err := strconv.ParseInt(string(b), 10, 64)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
case formatBinary:
var i int32
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &i)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
default:
return d, fmt.Errorf("unsupported int4 format code: %d", code)
}
case oid.T_int8:
switch code {
case formatText:
i, err := strconv.ParseInt(string(b), 10, 64)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
case formatBinary:
var i int64
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &i)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
default:
return d, fmt.Errorf("unsupported int8 format code: %d", code)
}
case oid.T_float4:
switch code {
case formatText:
f, err := strconv.ParseFloat(string(b), 64)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
case formatBinary:
var f float32
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &f)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
default:
return d, fmt.Errorf("unsupported float4 format code: %d", code)
}
case oid.T_float8:
switch code {
case formatText:
f, err := strconv.ParseFloat(string(b), 64)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
case formatBinary:
var f float64
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &f)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
default:
return d, fmt.Errorf("unsupported float8 format code: %d", code)
}
case oid.T_numeric:
switch code {
case formatText:
dd := &parser.DDecimal{}
if _, ok := dd.SetString(string(b)); !ok {
return nil, fmt.Errorf("could not parse string %q as decimal", b)
}
d = dd
default:
return d, fmt.Errorf("unsupported numeric format code: %d", code)
}
case oid.T_text, oid.T_varchar:
switch code {
case formatText:
d = parser.NewDString(string(b))
default:
return d, fmt.Errorf("unsupported text format code: %d", code)
}
case oid.T_bytea:
switch code {
case formatText:
// http://www.postgresql.org/docs/current/static/datatype-binary.html#AEN5667
// Code cribbed from github.com/lib/pq.
// We only support hex encoding.
if len(b) >= 2 && bytes.Equal(b[:2], []byte("\\x")) {
b = b[2:] // trim off leading "\\x"
result := make([]byte, hex.DecodedLen(len(b)))
_, err := hex.Decode(result, b)
if err != nil {
return d, err
}
d = parser.NewDBytes(parser.DBytes(result))
} else {
return d, fmt.Errorf("unsupported bytea encoding: %q", b)
}
case formatBinary:
d = parser.NewDBytes(parser.DBytes(b))
default:
return d, fmt.Errorf("unsupported bytea format code: %d", code)
}
case oid.T_timestamp, oid.T_timestamptz:
switch code {
case formatText:
ts, err := parseTs(string(b))
if err != nil {
return d, fmt.Errorf("could not parse string %q as timestamp", b)
}
d = &parser.DTimestamp{Time: ts}
case formatBinary:
return d, fmt.Errorf("unsupported timestamp format code: %d", code)
}
case oid.T_date:
switch code {
case formatText:
ts, err := parseTs(string(b))
if err != nil {
return d, fmt.Errorf("could not parse string %q as date", b)
}
daysSinceEpoch := ts.Unix() / secondsInDay
d = parser.NewDDate(parser.DDate(daysSinceEpoch))
case formatBinary:
return d, fmt.Errorf("unsupported date format code: %d", code)
}
default:
return d, fmt.Errorf("unsupported OID: %v", id)
}
return d, nil
}
// decodeOidDatum decodes bytes with specified Oid and format code into
// a datum.
func decodeOidDatum(id oid.Oid, code formatCode, b []byte) (parser.Datum, error) {
var d parser.Datum
switch id {
case oid.T_bool:
switch code {
case formatText:
v, err := strconv.ParseBool(string(b))
if err != nil {
return d, err
}
d = parser.MakeDBool(parser.DBool(v))
case formatBinary:
switch b[0] {
case 0:
d = parser.MakeDBool(false)
case 1:
d = parser.MakeDBool(true)
default:
return d, util.Errorf("unsupported binary bool: %q", b)
}
default:
return d, util.Errorf("unsupported bool format code: %s", code)
}
case oid.T_int2:
switch code {
case formatText:
i, err := strconv.ParseInt(string(b), 10, 64)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
case formatBinary:
var i int16
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &i)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
default:
return d, util.Errorf("unsupported int2 format code: %s", code)
}
case oid.T_int4:
switch code {
case formatText:
i, err := strconv.ParseInt(string(b), 10, 64)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
case formatBinary:
var i int32
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &i)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
default:
return d, util.Errorf("unsupported int4 format code: %s", code)
}
case oid.T_int8:
switch code {
case formatText:
i, err := strconv.ParseInt(string(b), 10, 64)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
case formatBinary:
var i int64
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &i)
if err != nil {
return d, err
}
d = parser.NewDInt(parser.DInt(i))
default:
return d, util.Errorf("unsupported int8 format code: %s", code)
}
case oid.T_float4:
switch code {
case formatText:
f, err := strconv.ParseFloat(string(b), 64)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
case formatBinary:
var f float32
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &f)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
default:
return d, util.Errorf("unsupported float4 format code: %s", code)
}
case oid.T_float8:
switch code {
case formatText:
f, err := strconv.ParseFloat(string(b), 64)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
case formatBinary:
var f float64
err := binary.Read(bytes.NewReader(b), binary.BigEndian, &f)
if err != nil {
return d, err
}
d = parser.NewDFloat(parser.DFloat(f))
default:
return d, util.Errorf("unsupported float8 format code: %s", code)
}
case oid.T_numeric:
switch code {
case formatText:
dd := &parser.DDecimal{}
if _, ok := dd.SetString(string(b)); !ok {
return nil, util.Errorf("could not parse string %q as decimal", b)
}
d = dd
case formatBinary:
r := bytes.NewReader(b)
alloc := struct {
pgNum pgNumeric
i16 int16
dd parser.DDecimal
}{}
for _, ptr := range []interface{}{
&alloc.pgNum.ndigits,
&alloc.pgNum.weight,
&alloc.pgNum.sign,
&alloc.pgNum.dscale,
} {
if err := binary.Read(r, binary.BigEndian, ptr); err != nil {
return d, err
}
}
decDigits := make([]byte, 0, alloc.pgNum.ndigits*pgDecDigits)
for i := int16(0); i < alloc.pgNum.ndigits-1; i++ {
if err := binary.Read(r, binary.BigEndian, &alloc.i16); err != nil {
return d, err
}
decDigits = strconv.AppendUint(decDigits, uint64(alloc.i16), 10)
}
// The last digit may contain padding, which we need to deal with.
if err := binary.Read(r, binary.BigEndian, &alloc.i16); err != nil {
return d, err
}
dscale := (alloc.pgNum.ndigits - 1 - alloc.pgNum.weight) * pgDecDigits
if overScale := dscale - alloc.pgNum.dscale; overScale > 0 {
dscale -= overScale
for i := int16(0); i < overScale; i++ {
alloc.i16 /= 10
}
}
decDigits = strconv.AppendUint(decDigits, uint64(alloc.i16), 10)
alloc.dd.UnscaledBig().SetString(string(decDigits), 10)
alloc.dd.SetScale(inf.Scale(dscale))
switch alloc.pgNum.sign {
case pgNumericPos:
case pgNumericNeg:
alloc.dd.Neg(&alloc.dd.Dec)
default:
return d, util.Errorf("unsupported numeric sign: %s", alloc.pgNum.sign)
}
d = &alloc.dd
default:
return d, util.Errorf("unsupported numeric format code: %s", code)
}
case oid.T_text, oid.T_varchar:
switch code {
case formatText, formatBinary:
d = parser.NewDString(string(b))
default:
return d, util.Errorf("unsupported text format code: %s", code)
}
case oid.T_bytea:
switch code {
case formatText:
// http://www.postgresql.org/docs/current/static/datatype-binary.html#AEN5667
// Code cribbed from github.com/lib/pq.
// We only support hex encoding.
if len(b) >= 2 && bytes.Equal(b[:2], []byte("\\x")) {
b = b[2:] // trim off leading "\\x"
result := make([]byte, hex.DecodedLen(len(b)))
_, err := hex.Decode(result, b)
if err != nil {
return d, err
}
d = parser.NewDBytes(parser.DBytes(result))
} else {
return d, util.Errorf("unsupported bytea encoding: %q", b)
}
case formatBinary:
d = parser.NewDBytes(parser.DBytes(b))
default:
return d, util.Errorf("unsupported bytea format code: %s", code)
}
case oid.T_timestamp, oid.T_timestamptz:
switch code {
case formatText:
ts, err := parseTs(string(b))
if err != nil {
return d, util.Errorf("could not parse string %q as timestamp", b)
}
d = parser.MakeDTimestamp(ts, time.Microsecond)
default:
return d, util.Errorf("unsupported timestamp format code: %s", code)
}
case oid.T_date:
switch code {
case formatText:
ts, err := parseTs(string(b))
if err != nil {
return d, util.Errorf("could not parse string %q as date", b)
}
daysSinceEpoch := ts.Unix() / secondsInDay
d = parser.NewDDate(parser.DDate(daysSinceEpoch))
default:
return d, util.Errorf("unsupported date format code: %s", code)
}
default:
return d, util.Errorf("unsupported OID: %v", id)
}
return d, nil
}
func dumpTable(w io.Writer, conn *sqlConn, origDBName, origTableName string) error {
const limit = 100
// Escape names since they can't be used in placeholders.
dbname := parser.Name(origDBName).String()
tablename := parser.Name(origTableName).String()
if err := conn.Exec(fmt.Sprintf("SET DATABASE = %s", dbname), nil); err != nil {
return err
}
// Fetch all table metadata in a transaction and its time to guarantee it
// doesn't change between the various SHOW statements.
if err := conn.Exec("BEGIN", nil); err != nil {
return err
}
vals, err := conn.QueryRow("SELECT cluster_logical_timestamp()::int", nil)
if err != nil {
return err
}
clusterTSStart := vals[0].(int64)
clusterTS := time.Unix(0, clusterTSStart).Format(time.RFC3339Nano)
// A previous version of the code did a SELECT on system.descriptor. This
// required the SELECT privilege to the descriptor table, which only root
// has. Allowing non-root to do this would let users see other users' table
// descriptors which is a problem in multi-tenancy.
// Fetch column types.
rows, err := conn.Query(fmt.Sprintf("SHOW COLUMNS FROM %s", tablename), nil)
if err != nil {
return err
}
vals = make([]driver.Value, 2)
coltypes := make(map[string]string)
for {
if err := rows.Next(vals); err == io.EOF {
break
} else if err != nil {
return err
}
name, ok := vals[0].(string)
if !ok {
return fmt.Errorf("unexpected value: %T", vals[1])
}
typ, ok := vals[1].(string)
if !ok {
return fmt.Errorf("unexpected value: %T", vals[4])
}
coltypes[name] = typ
}
if err := rows.Close(); err != nil {
return err
}
// index holds the names, in order, of the primary key columns.
var index []string
// Primary index is always the first index returned by SHOW INDEX.
rows, err = conn.Query(fmt.Sprintf("SHOW INDEX FROM %s", tablename), nil)
if err != nil {
return err
}
vals = make([]driver.Value, 5)
var primaryIndex string
// Find the primary index columns.
for {
if err := rows.Next(vals); err == io.EOF {
break
} else if err != nil {
return err
}
b, ok := vals[1].(string)
if !ok {
return fmt.Errorf("unexpected value: %T", vals[1])
}
if primaryIndex == "" {
primaryIndex = b
} else if primaryIndex != b {
break
}
b, ok = vals[4].(string)
if !ok {
return fmt.Errorf("unexpected value: %T", vals[4])
}
index = append(index, parser.Name(b).String())
}
if err := rows.Close(); err != nil {
return err
}
if len(index) == 0 {
return fmt.Errorf("no primary key index found")
}
indexes := strings.Join(index, ", ")
// Build the SELECT query.
var sbuf bytes.Buffer
fmt.Fprintf(&sbuf, "SELECT %s, * FROM %s@%s AS OF SYSTEM TIME '%s'", indexes, tablename, primaryIndex, clusterTS)
var wbuf bytes.Buffer
fmt.Fprintf(&wbuf, " WHERE ROW (%s) > ROW (", indexes)
for i := range index {
if i > 0 {
wbuf.WriteString(", ")
}
fmt.Fprintf(&wbuf, "$%d", i+1)
}
wbuf.WriteString(")")
// No WHERE clause first time, so add a place to inject it.
fmt.Fprintf(&sbuf, "%%s ORDER BY %s LIMIT %d", indexes, limit)
bs := sbuf.String()
vals, err = conn.QueryRow(fmt.Sprintf("SHOW CREATE TABLE %s", tablename), nil)
if err != nil {
return err
}
create := vals[1].(string)
if _, err := w.Write([]byte(create)); err != nil {
return err
}
if _, err := w.Write([]byte(";\n")); err != nil {
return err
}
if err := conn.Exec("COMMIT", nil); err != nil {
return err
}
// pk holds the last values of the fetched primary keys
var pk []driver.Value
q := fmt.Sprintf(bs, "")
for {
rows, err := conn.Query(q, pk)
if err != nil {
return err
}
cols := rows.Columns()
pkcols := cols[:len(index)]
cols = cols[len(index):]
inserts := make([][]string, 0, limit)
i := 0
for i < limit {
vals := make([]driver.Value, len(cols)+len(pkcols))
if err := rows.Next(vals); err == io.EOF {
break
} else if err != nil {
return err
}
if pk == nil {
q = fmt.Sprintf(bs, wbuf.String())
}
pk = vals[:len(index)]
vals = vals[len(index):]
ivals := make([]string, len(vals))
// Values need to be correctly encoded for INSERT statements in a text file.
for si, sv := range vals {
switch t := sv.(type) {
case nil:
ivals[si] = "NULL"
case bool:
ivals[si] = parser.MakeDBool(parser.DBool(t)).String()
case int64:
ivals[si] = parser.NewDInt(parser.DInt(t)).String()
case float64:
ivals[si] = parser.NewDFloat(parser.DFloat(t)).String()
case string:
ivals[si] = parser.NewDString(t).String()
case []byte:
switch ct := coltypes[cols[si]]; ct {
case "INTERVAL":
ivals[si] = fmt.Sprintf("'%s'", t)
case "BYTES":
ivals[si] = parser.NewDBytes(parser.DBytes(t)).String()
default:
// STRING and DECIMAL types can have optional length
// suffixes, so only examine the prefix of the type.
if strings.HasPrefix(coltypes[cols[si]], "STRING") {
ivals[si] = parser.NewDString(string(t)).String()
} else if strings.HasPrefix(coltypes[cols[si]], "DECIMAL") {
ivals[si] = string(t)
} else {
panic(errors.Errorf("unknown []byte type: %s, %v: %s", t, cols[si], coltypes[cols[si]]))
}
}
case time.Time:
var d parser.Datum
ct := coltypes[cols[si]]
switch ct {
case "DATE":
d = parser.NewDDateFromTime(t, time.UTC)
case "TIMESTAMP":
d = parser.MakeDTimestamp(t, time.Nanosecond)
case "TIMESTAMP WITH TIME ZONE":
d = parser.MakeDTimestampTZ(t, time.Nanosecond)
default:
panic(errors.Errorf("unknown timestamp type: %s, %v: %s", t, cols[si], coltypes[cols[si]]))
}
ivals[si] = fmt.Sprintf("'%s'", d)
default:
panic(errors.Errorf("unknown field type: %T (%s)", t, cols[si]))
}
}
inserts = append(inserts, ivals)
i++
}
for si, sv := range pk {
b, ok := sv.([]byte)
if ok && strings.HasPrefix(coltypes[pkcols[si]], "STRING") {
// Primary key strings need to be converted to a go string, but not SQL
// encoded since they aren't being written to a text file.
pk[si] = string(b)
}
}
if err := rows.Close(); err != nil {
return err
}
if i == 0 {
break
}
fmt.Fprintf(w, "\nINSERT INTO %s VALUES", tablename)
for idx, values := range inserts {
if idx > 0 {
fmt.Fprint(w, ",")
}
fmt.Fprint(w, "\n\t(")
for vi, v := range values {
if vi > 0 {
fmt.Fprint(w, ", ")
}
fmt.Fprint(w, v)
}
fmt.Fprint(w, ")")
}
fmt.Fprintln(w, ";")
if i < limit {
break
}
}
return nil
}
func TestAdminAPITableDetailsZone(t *testing.T) {
defer leaktest.AfterTest(t)()
s, _, _ := serverutils.StartServer(t, base.TestServerArgs{})
defer s.Stopper().Stop()
ts := s.(*TestServer)
// Create database and table.
session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, ts.sqlExecutor, nil)
setupQueries := []string{
"CREATE DATABASE test",
"CREATE TABLE test.tbl (val STRING)",
}
for _, q := range setupQueries {
res := ts.sqlExecutor.ExecuteStatements(context.Background(), session, q, nil)
if res.ResultList[0].Err != nil {
t.Fatalf("error executing '%s': %s", q, res.ResultList[0].Err)
}
}
// Function to verify the zone for test.tbl as returned by the Admin API.
verifyZone := func(expectedZone config.ZoneConfig, expectedLevel serverpb.ZoneConfigurationLevel) {
var resp serverpb.TableDetailsResponse
if err := apiGet(s, "databases/test/tables/tbl", &resp); err != nil {
t.Fatal(err)
}
if a, e := &resp.ZoneConfig, &expectedZone; !proto.Equal(a, e) {
t.Errorf("actual zone config %v did not match expected value %v", a, e)
}
if a, e := resp.ZoneConfigLevel, expectedLevel; a != e {
t.Errorf("actual ZoneConfigurationLevel %s did not match expected value %s", a, e)
}
if t.Failed() {
t.FailNow()
}
}
// Function to store a zone config for a given object ID.
setZone := func(zoneCfg config.ZoneConfig, id sqlbase.ID) {
zoneBytes, err := zoneCfg.Marshal()
if err != nil {
t.Fatal(err)
}
const query = `INSERT INTO system.zones VALUES($1, $2)`
params := parser.NewPlaceholderInfo()
params.SetValue(`1`, parser.NewDInt(parser.DInt(id)))
params.SetValue(`2`, parser.NewDBytes(parser.DBytes(zoneBytes)))
res := ts.sqlExecutor.ExecuteStatements(context.Background(), session, query, params)
if res.ResultList[0].Err != nil {
t.Fatalf("error executing '%s': %s", query, res.ResultList[0].Err)
}
}
// Verify zone matches cluster default.
verifyZone(config.DefaultZoneConfig(), serverpb.ZoneConfigurationLevel_CLUSTER)
// Get ID path for table. This will be an array of three IDs, containing the ID of the root namespace,
// the database, and the table (in that order).
idPath, err := ts.admin.queryDescriptorIDPath(context.Background(), session, []string{"test", "tbl"})
if err != nil {
t.Fatal(err)
}
// Apply zone configuration to database and check again.
dbZone := config.ZoneConfig{
RangeMinBytes: 456,
}
setZone(dbZone, idPath[1])
verifyZone(dbZone, serverpb.ZoneConfigurationLevel_DATABASE)
// Apply zone configuration to table and check again.
tblZone := config.ZoneConfig{
RangeMinBytes: 789,
}
setZone(tblZone, idPath[2])
verifyZone(tblZone, serverpb.ZoneConfigurationLevel_TABLE)
}
func dumpTable(w io.Writer, conn *sqlConn, origDBName, origTableName string) error {
const limit = 100
// Escape names since they can't be used in placeholders.
dbname := parser.Name(origDBName).String()
tablename := parser.Name(origTableName).String()
if err := conn.Exec(fmt.Sprintf("SET DATABASE = %s", dbname), nil); err != nil {
return err
}
// Fetch all table metadata in a transaction and its time to guarantee it
// doesn't change between the various SHOW statements.
if err := conn.Exec("BEGIN", nil); err != nil {
return err
}
vals, err := conn.QueryRow("SELECT cluster_logical_timestamp()::int", nil)
if err != nil {
return err
}
clusterTSStart := vals[0].(int64)
clusterTS := time.Unix(0, clusterTSStart).Format(time.RFC3339Nano)
// Fetch table descriptor.
vals, err = conn.QueryRow(`
SELECT descriptor
FROM system.descriptor
JOIN system.namespace tables
ON tables.id = descriptor.id
JOIN system.namespace dbs
ON dbs.id = tables.parentid
WHERE tables.name = $1 AND dbs.name = $2`,
[]driver.Value{origTableName, origDBName})
if err == io.EOF {
return errors.Errorf("unknown database or table %s.%s", origTableName, origDBName)
} else if err != nil {
return err
}
b := vals[0].([]byte)
var desc sqlbase.Descriptor
if err := proto.Unmarshal(b, &desc); err != nil {
return err
}
table := desc.GetTable()
if table == nil {
return errors.New("internal error: expected table descriptor")
}
coltypes := make(map[string]string)
for _, c := range table.Columns {
coltypes[c.Name] = c.Type.SQLString()
}
primaryIndex := table.PrimaryIndex.Name
index := table.PrimaryIndex.ColumnNames
indexes := strings.Join(index, ", ")
// Build the SELECT query.
var sbuf bytes.Buffer
fmt.Fprintf(&sbuf, "SELECT %s, * FROM %s@%s AS OF SYSTEM TIME '%s'", indexes, tablename, primaryIndex, clusterTS)
var wbuf bytes.Buffer
fmt.Fprintf(&wbuf, " WHERE ROW (%s) > ROW (", indexes)
for i := range index {
if i > 0 {
wbuf.WriteString(", ")
}
fmt.Fprintf(&wbuf, "$%d", i+1)
}
wbuf.WriteString(")")
// No WHERE clause first time, so add a place to inject it.
fmt.Fprintf(&sbuf, "%%s ORDER BY %s LIMIT %d", indexes, limit)
bs := sbuf.String()
vals, err = conn.QueryRow(fmt.Sprintf("SHOW CREATE TABLE %s", tablename), nil)
if err != nil {
return err
}
create := vals[1].([]byte)
if _, err := w.Write(create); err != nil {
return err
}
if _, err := w.Write([]byte(";\n")); err != nil {
return err
}
if err := conn.Exec("COMMIT", nil); err != nil {
return err
}
// pk holds the last values of the fetched primary keys
var pk []driver.Value
q := fmt.Sprintf(bs, "")
for {
rows, err := conn.Query(q, pk)
if err != nil {
return err
}
cols := rows.Columns()
pkcols := cols[:len(index)]
cols = cols[len(index):]
inserts := make([][]string, 0, limit)
i := 0
for i < limit {
vals := make([]driver.Value, len(cols)+len(pkcols))
if err := rows.Next(vals); err == io.EOF {
break
} else if err != nil {
return err
}
if pk == nil {
q = fmt.Sprintf(bs, wbuf.String())
}
pk = vals[:len(index)]
vals = vals[len(index):]
ivals := make([]string, len(vals))
// Values need to be correctly encoded for INSERT statements in a text file.
for si, sv := range vals {
switch t := sv.(type) {
case nil:
ivals[si] = "NULL"
case bool:
ivals[si] = parser.MakeDBool(parser.DBool(t)).String()
case int64:
ivals[si] = parser.NewDInt(parser.DInt(t)).String()
case float64:
ivals[si] = parser.NewDFloat(parser.DFloat(t)).String()
case []byte:
switch ct := coltypes[cols[si]]; ct {
case "INTERVAL":
ivals[si] = fmt.Sprintf("'%s'", t)
case "DECIMAL":
ivals[si] = fmt.Sprintf("%s", t)
default:
// STRING and BYTES types can have optional length suffixes, so only examine
// the prefix of the type.
if strings.HasPrefix(coltypes[cols[si]], "STRING") {
ivals[si] = parser.NewDString(string(t)).String()
} else if strings.HasPrefix(coltypes[cols[si]], "BYTES") {
ivals[si] = parser.NewDBytes(parser.DBytes(t)).String()
} else {
panic(errors.Errorf("unknown []byte type: %s, %v: %s", t, cols[si], coltypes[cols[si]]))
}
}
case time.Time:
var d parser.Datum
ct := coltypes[cols[si]]
switch ct {
case "DATE":
d = parser.NewDDateFromTime(t, time.UTC)
case "TIMESTAMP":
d = parser.MakeDTimestamp(t, time.Nanosecond)
case "TIMESTAMP WITH TIME ZONE":
d = parser.MakeDTimestampTZ(t, time.Nanosecond)
default:
panic(errors.Errorf("unknown timestamp type: %s, %v: %s", t, cols[si], coltypes[cols[si]]))
}
ivals[si] = fmt.Sprintf("'%s'", d)
default:
panic(errors.Errorf("unknown field type: %T (%s)", t, cols[si]))
}
}
inserts = append(inserts, ivals)
i++
}
for si, sv := range pk {
b, ok := sv.([]byte)
if ok && strings.HasPrefix(coltypes[pkcols[si]], "STRING") {
// Primary key strings need to be converted to a go string, but not SQL
// encoded since they aren't being written to a text file.
pk[si] = string(b)
}
}
if err := rows.Close(); err != nil {
return err
}
if i == 0 {
break
}
fmt.Fprintf(w, "\nINSERT INTO %s VALUES", tablename)
for idx, values := range inserts {
if idx > 0 {
fmt.Fprint(w, ",")
}
fmt.Fprint(w, "\n\t(")
for vi, v := range values {
if vi > 0 {
fmt.Fprint(w, ", ")
}
fmt.Fprint(w, v)
}
fmt.Fprint(w, ")")
}
fmt.Fprintln(w, ";")
if i < limit {
break
}
}
return nil
}