// DatabaseDetails is an endpoint that returns grants and a list of table names
// for the specified database.
func (s *adminServer) DatabaseDetails(ctx context.Context, req *DatabaseDetailsRequest) (*DatabaseDetailsResponse, error) {
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
// Placeholders don't work with SHOW statements, so we need to manually
// escape the database name.
//
// TODO(cdo): Use placeholders when they're supported by SHOW.
escDBName := parser.Name(req.Database).String()
query := fmt.Sprintf("SHOW GRANTS ON DATABASE %s; SHOW TABLES FROM %s;", escDBName, escDBName)
r := s.sqlExecutor.ExecuteStatements(ctx, session, query, nil)
if pErr := s.firstNotFoundError(r.ResultList); pErr != nil {
return nil, grpc.Errorf(codes.NotFound, "%s", pErr)
}
if err := s.checkQueryResults(r.ResultList, 2); err != nil {
return nil, s.serverError(err)
}
// Marshal grants.
var resp DatabaseDetailsResponse
{
const (
userCol = "User"
privilegesCol = "Privileges"
)
scanner := makeResultScanner(r.ResultList[0].Columns)
for _, row := range r.ResultList[0].Rows {
// Marshal grant, splitting comma-separated privileges into a proper slice.
var grant DatabaseDetailsResponse_Grant
var privileges string
if err := scanner.Scan(row, userCol, &grant.User); err != nil {
return nil, err
}
if err := scanner.Scan(row, privilegesCol, &privileges); err != nil {
return nil, err
}
grant.Privileges = strings.Split(privileges, ",")
resp.Grants = append(resp.Grants, grant)
}
}
// Marshal table names.
{
const tableCol = "Table"
scanner := makeResultScanner(r.ResultList[1].Columns)
if a, e := len(r.ResultList[1].Columns), 1; a != e {
return nil, s.serverErrorf("show tables columns mismatch: %d != expected %d", a, e)
}
for _, row := range r.ResultList[1].Rows {
var tableName string
if err := scanner.Scan(row, tableCol, &tableName); err != nil {
return nil, err
}
resp.TableNames = append(resp.TableNames, tableName)
}
}
return &resp, nil
}
// Events is an endpoint that returns the latest event log entries, with the following
// optional URL parameters:
//
// type=STRING returns events with this type (e.g. "create_table")
// targetID=INT returns events for that have this targetID
func (s *adminServer) Events(c context.Context, req *EventsRequest) (*EventsResponse, error) {
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
// Execute the query.
q := &sqlQuery{}
q.Append("SELECT timestamp, eventType, targetID, reportingID, info, uniqueID ")
q.Append("FROM system.eventlog ")
q.Append("WHERE true ") // This simplifies the WHERE clause logic below.
if len(req.Type) > 0 {
q.Append("AND eventType = $ ", parser.DString(req.Type))
}
if req.TargetId > 0 {
q.Append("AND targetID = $ ", parser.DInt(req.TargetId))
}
q.Append("ORDER BY timestamp DESC ")
q.Append("LIMIT $", parser.DInt(apiEventLimit))
if len(q.Errors()) > 0 {
return nil, s.serverErrors(q.Errors())
}
r := s.sqlExecutor.ExecuteStatements(session, q.String(), q.Params())
if err := s.checkQueryResults(r.ResultList, 1); err != nil {
return nil, s.serverError(err)
}
// Marshal response.
var resp EventsResponse
scanner := newResultScanner(r.ResultList[0].Columns)
for _, row := range r.ResultList[0].Rows {
var event EventsResponse_Event
var ts time.Time
if err := scanner.ScanIndex(row, 0, &ts); err != nil {
return nil, err
}
nanos := ts.UnixNano()
event.Timestamp = &EventsResponse_Event_Timestamp{Sec: nanos / 1e9, Nsec: uint32(nanos % 1e9)}
if err := scanner.ScanIndex(row, 1, &event.EventType); err != nil {
return nil, err
}
if err := scanner.ScanIndex(row, 2, &event.TargetID); err != nil {
return nil, err
}
if err := scanner.ScanIndex(row, 3, &event.ReportingID); err != nil {
return nil, err
}
if err := scanner.ScanIndex(row, 4, &event.Info); err != nil {
return nil, err
}
if err := scanner.ScanIndex(row, 5, &event.UniqueID); err != nil {
return nil, err
}
resp.Events = append(resp.Events, &event)
}
return &resp, nil
}
// 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.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.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.DString(val), // $1
parser.DString(key), // $2
}
r := s.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.DString(key), // $1
parser.DBytes(val), // $2
}
r := s.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
}
// SetUIData is an endpoint that sets the data associated with a key.
func (s *adminServer) SetUIData(_ context.Context, req *SetUIDataRequest) (*SetUIDataResponse, error) {
if len(req.Key) == 0 {
return nil, grpc.Errorf(codes.InvalidArgument, "key cannot be empty")
}
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
// Do an upsert of the key.
br := s.sqlExecutor.ExecuteStatements(session, "BEGIN;", nil)
if err := s.checkQueryResults(br.ResultList, 1); err != nil {
return nil, s.serverError(err)
}
// See if the key already exists.
alreadyExists := true
if _, _, err := s.getUIData(session, s.getUser(req), req.Key); err != nil {
if err != errUIKeyNotFound {
return nil, s.serverError(err)
}
alreadyExists = false
}
// INSERT or UPDATE as appropriate.
if alreadyExists {
query := "UPDATE system.ui SET value = $1, lastUpdated = NOW() WHERE key = $2; COMMIT;"
params := []parser.Datum{
parser.DString(req.Value), // $1
parser.DString(req.Key), // $2
}
r := s.sqlExecutor.ExecuteStatements(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.DString(req.Key), // $1
parser.DBytes(req.Value), // $2
}
r := s.sqlExecutor.ExecuteStatements(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
}
func makeV3Conn(
conn net.Conn, executor *sql.Executor,
metrics *serverMetrics, sessionArgs sql.SessionArgs) v3Conn {
return v3Conn{
conn: conn,
rd: bufio.NewReader(conn),
wr: bufio.NewWriter(conn),
executor: executor,
writeBuf: writeBuffer{bytecount: metrics.bytesOutCount},
metrics: metrics,
session: sql.NewSession(sessionArgs, executor, conn.RemoteAddr()),
}
}
// Users returns a list of users, stripped of any passwords.
func (s *adminServer) Users(ctx context.Context, req *UsersRequest) (*UsersResponse, error) {
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
query := "SELECT username FROM system.users"
r := s.sqlExecutor.ExecuteStatements(ctx, session, query, nil)
if err := s.checkQueryResults(r.ResultList, 1); err != nil {
return nil, s.serverError(err)
}
var resp UsersResponse
for _, row := range r.ResultList[0].Rows {
resp.Users = append(resp.Users, UsersResponse_User{string(row.Values[0].(parser.DString))})
}
return &resp, nil
}
// GetUIData returns data associated with the given keys, which was stored
// earlier through SetUIData.
//
// The stored values are meant to be opaque to the server. In the rare case that
// the server code needs to call this method, it should only read from keys that
// have the prefix `serverUIDataKeyPrefix`.
func (s *adminServer) GetUIData(_ context.Context, req *GetUIDataRequest) (*GetUIDataResponse, error) {
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
if len(req.Keys) == 0 {
return nil, grpc.Errorf(codes.InvalidArgument, "keys cannot be empty")
}
resp, err := s.getUIData(session, s.getUser(req), req.Keys)
if err != nil {
return nil, s.serverError(err)
}
return resp, nil
}
// GetUIData returns data associated with the given key, which was stored
// earlier through SetUIData.
func (s *adminServer) GetUIData(_ context.Context, req *GetUIDataRequest) (*GetUIDataResponse, error) {
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
if len(req.Key) == 0 {
return nil, grpc.Errorf(codes.InvalidArgument, "key cannot be empty")
}
val, ts, err := s.getUIData(session, s.getUser(req), req.Key)
if err != nil {
if err == errUIKeyNotFound {
return nil, grpc.Errorf(codes.NotFound, "key %s not found", req.Key)
}
return nil, s.serverError(err)
}
return &GetUIDataResponse{Value: val, LastUpdated: &ts}, nil
}
// Databases is an endpoint that returns a list of databases.
func (s *adminServer) Databases(ctx context.Context, req *DatabasesRequest) (*DatabasesResponse, error) {
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
r := s.sqlExecutor.ExecuteStatements(ctx, session, "SHOW DATABASES;", nil)
if err := s.checkQueryResults(r.ResultList, 1); err != nil {
return nil, s.serverError(err)
}
var resp DatabasesResponse
for _, row := range r.ResultList[0].Rows {
dbname, ok := row.Values[0].(parser.DString)
if !ok {
return nil, s.serverErrorf("type assertion failed on db name: %T", row.Values[0])
}
resp.Databases = append(resp.Databases, string(dbname))
}
return &resp, nil
}
func TestAdminAPIUsers(t *testing.T) {
defer leaktest.AfterTest(t)()
s, _, _ := serverutils.StartServer(t, base.TestServerArgs{})
defer s.Stopper().Stop()
ts := s.(*TestServer)
// Create sample users.
session := sql.NewSession(
context.Background(), sql.SessionArgs{User: security.RootUser}, ts.sqlExecutor, nil)
query := `
INSERT INTO system.users (username, hashedPassword)
VALUES ('admin', 'abc'), ('bob', 'xyz')`
res := ts.sqlExecutor.ExecuteStatements(session, query, nil)
if a, e := len(res.ResultList), 1; a != e {
t.Fatalf("len(results) %d != %d", a, e)
} else if res.ResultList[0].Err != nil {
t.Fatal(res.ResultList[0].Err)
}
// Query the API for users.
var resp serverpb.UsersResponse
if err := apiGet(s, "users", &resp); err != nil {
t.Fatal(err)
}
expResult := serverpb.UsersResponse{
Users: []serverpb.UsersResponse_User{
{Username: "******"},
{Username: "******"},
},
}
// Verify results.
const sortKey = "Username"
testutils.SortStructs(resp.Users, sortKey)
testutils.SortStructs(expResult.Users, sortKey)
if !reflect.DeepEqual(resp, expResult) {
t.Fatalf("result %v != expected %v", resp, expResult)
}
}
func TestAdminAPIEvents(t *testing.T) {
defer leaktest.AfterTest(t)()
s := StartTestServer(t)
defer s.Stop()
session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, s.sqlExecutor, nil)
setupQueries := []string{
"CREATE DATABASE api_test",
"CREATE TABLE api_test.tbl1 (a INT)",
"CREATE TABLE api_test.tbl2 (a INT)",
"CREATE TABLE api_test.tbl3 (a INT)",
"DROP TABLE api_test.tbl1",
"DROP TABLE api_test.tbl2",
}
for _, q := range setupQueries {
res := s.sqlExecutor.ExecuteStatements(session, q, nil)
if res.ResultList[0].PErr != nil {
t.Fatalf("error executing '%s': %s", q, res.ResultList[0].PErr)
}
}
var zeroTimestamp EventsResponse_Event_Timestamp
testcases := []struct {
eventType sql.EventLogType
expCount int
}{
{"", 7},
{sql.EventLogNodeJoin, 1},
{sql.EventLogNodeRestart, 0},
{sql.EventLogDropDatabase, 0},
{sql.EventLogCreateDatabase, 1},
{sql.EventLogDropTable, 2},
{sql.EventLogCreateTable, 3},
}
for i, tc := range testcases {
var url string
if len(tc.eventType) > 0 {
url = fmt.Sprintf("events?type=%s", tc.eventType)
} else {
url = "events"
}
var resp EventsResponse
if err := apiGet(s, url, &resp); err != nil {
t.Fatal(err)
}
if a, e := len(resp.Events), tc.expCount; a != e {
t.Errorf("%d: # of events %d != expected %d", i, a, e)
}
// Ensure we don't have blank / nonsensical fields.
for _, e := range resp.Events {
if *e.Timestamp == zeroTimestamp {
t.Errorf("%d: missing/empty timestamp", i)
}
if len(tc.eventType) > 0 {
if a, e := e.EventType, string(tc.eventType); a != e {
t.Errorf("%d: event type %s != expected %s", i, a, e)
}
} else {
if len(e.EventType) == 0 {
t.Errorf("%d: missing event type in event", i)
}
}
if e.TargetID == 0 {
t.Errorf("%d: missing/empty TargetID", i)
}
if e.ReportingID == 0 {
t.Errorf("%d: missing/empty ReportingID", i)
}
if len(e.Info) == 0 {
t.Errorf("%d: missing/empty Info", i)
}
if len(e.UniqueID) == 0 {
t.Errorf("%d: missing/empty UniqueID", i)
}
}
}
}
func TestAdminAPIDatabases(t *testing.T) {
defer leaktest.AfterTest(t)()
s, _, _ := serverutils.StartServer(t, base.TestServerArgs{})
defer s.Stopper().Stop()
ts := s.(*TestServer)
// Test databases endpoint.
const testdb = "test"
session := sql.NewSession(
context.Background(), sql.SessionArgs{User: security.RootUser}, ts.sqlExecutor, nil)
query := "CREATE DATABASE " + testdb
createRes := ts.sqlExecutor.ExecuteStatements(session, query, nil)
if createRes.ResultList[0].Err != nil {
t.Fatal(createRes.ResultList[0].Err)
}
var resp serverpb.DatabasesResponse
if err := apiGet(s, "databases", &resp); err != nil {
t.Fatal(err)
}
// We should have three databases:
// - system database
// - information_schema
// - newly created test database
if a, e := len(resp.Databases), 3; a != e {
t.Fatalf("length of result %d != expected %d", a, e)
}
sort.Strings(resp.Databases)
for i, e := range []string{"information_schema", "system", testdb} {
if a := resp.Databases[i]; a != e {
t.Fatalf("database name %s != expected %s", a, e)
}
}
// Test database details endpoint.
privileges := []string{"SELECT", "UPDATE"}
testuser := "******"
grantQuery := "GRANT " + strings.Join(privileges, ", ") + " ON DATABASE " + testdb + " TO " + testuser
grantRes := s.(*TestServer).sqlExecutor.ExecuteStatements(session, grantQuery, nil)
if grantRes.ResultList[0].Err != nil {
t.Fatal(grantRes.ResultList[0].Err)
}
var details serverpb.DatabaseDetailsResponse
if err := apiGet(s, "databases/"+testdb, &details); err != nil {
t.Fatal(err)
}
if a, e := len(details.Grants), 2; a != e {
t.Fatalf("# of grants %d != expected %d", a, e)
}
for _, grant := range details.Grants {
switch grant.User {
case security.RootUser:
if !reflect.DeepEqual(grant.Privileges, []string{"ALL"}) {
t.Fatalf("privileges %v != expected %v", details.Grants[0].Privileges, privileges)
}
case testuser:
sort.Strings(grant.Privileges)
if !reflect.DeepEqual(grant.Privileges, privileges) {
t.Fatalf("privileges %v != expected %v", grant.Privileges, privileges)
}
default:
t.Fatalf("unknown grant to user %s", grant.User)
}
}
}
// TableDetails is an endpoint that returns columns, indices, and other
// relevant details for the specified table.
func (s *adminServer) TableDetails(ctx context.Context, req *TableDetailsRequest) (
*TableDetailsResponse, error) {
session := sql.NewSession(sql.SessionArgs{User: s.getUser(req)}, s.sqlExecutor, nil)
// TODO(cdo): Use real placeholders for the table and database names when we've extended our SQL
// grammar to allow that.
escDbName := parser.Name(req.Database).String()
escTableName := parser.Name(req.Table).String()
escQualTable := fmt.Sprintf("%s.%s", escDbName, escTableName)
query := fmt.Sprintf("SHOW COLUMNS FROM %s; SHOW INDEX FROM %s; SHOW GRANTS ON TABLE %s",
escQualTable, escQualTable, escQualTable)
r := s.sqlExecutor.ExecuteStatements(ctx, session, query, nil)
if pErr := s.firstNotFoundError(r.ResultList); pErr != nil {
return nil, grpc.Errorf(codes.NotFound, "%s", pErr)
}
if err := s.checkQueryResults(r.ResultList, 3); err != nil {
return nil, err
}
var resp TableDetailsResponse
// Marshal SHOW COLUMNS result.
//
// TODO(cdo): protobuf v3's default behavior for fields with zero values (e.g. empty strings)
// is to suppress them. So, if protobuf field "foo" is an empty string, "foo" won't show
// up in the marshalled JSON. I feel that this is counterintuitive, and this should be fixed
// for our API.
{
const (
fieldCol = "Field" // column name
typeCol = "Type"
nullCol = "Null"
defaultCol = "Default"
)
scanner := makeResultScanner(r.ResultList[0].Columns)
for _, row := range r.ResultList[0].Rows {
var col TableDetailsResponse_Column
if err := scanner.Scan(row, fieldCol, &col.Name); err != nil {
return nil, err
}
if err := scanner.Scan(row, typeCol, &col.Type); err != nil {
return nil, err
}
if err := scanner.Scan(row, nullCol, &col.Nullable); err != nil {
return nil, err
}
isDefaultNull, err := scanner.IsNull(row, defaultCol)
if err != nil {
return nil, err
}
if !isDefaultNull {
if err := scanner.Scan(row, defaultCol, &col.Default); err != nil {
return nil, err
}
}
resp.Columns = append(resp.Columns, col)
}
}
// Marshal SHOW INDEX result.
{
const (
nameCol = "Name"
uniqueCol = "Unique"
seqCol = "Seq"
columnCol = "Column"
directionCol = "Direction"
storingCol = "Storing"
)
scanner := makeResultScanner(r.ResultList[1].Columns)
for _, row := range r.ResultList[1].Rows {
// Marshal grant, splitting comma-separated privileges into a proper slice.
var index TableDetailsResponse_Index
if err := scanner.Scan(row, nameCol, &index.Name); err != nil {
return nil, err
}
if err := scanner.Scan(row, uniqueCol, &index.Unique); err != nil {
return nil, err
}
if err := scanner.Scan(row, seqCol, &index.Seq); err != nil {
return nil, err
}
if err := scanner.Scan(row, columnCol, &index.Column); err != nil {
return nil, err
}
if err := scanner.Scan(row, directionCol, &index.Direction); err != nil {
return nil, err
}
if err := scanner.Scan(row, storingCol, &index.Storing); err != nil {
return nil, err
}
resp.Indexes = append(resp.Indexes, index)
}
}
// Marshal SHOW GRANTS result.
{
const (
userCol = "User"
privilegesCol = "Privileges"
)
scanner := makeResultScanner(r.ResultList[2].Columns)
for _, row := range r.ResultList[2].Rows {
// Marshal grant, splitting comma-separated privileges into a proper slice.
var grant TableDetailsResponse_Grant
var privileges string
if err := scanner.Scan(row, userCol, &grant.User); err != nil {
return nil, err
}
if err := scanner.Scan(row, privilegesCol, &privileges); err != nil {
return nil, err
}
grant.Privileges = strings.Split(privileges, ",")
resp.Grants = append(resp.Grants, grant)
}
}
// Get the number of ranges in the table. We get the key span for the table
// data. Then, we count the number of ranges that make up that key span.
{
var iexecutor sql.InternalExecutor
var tableSpan roachpb.Span
if pErr := s.db.Txn(func(txn *client.Txn) *roachpb.Error {
var pErr *roachpb.Error
tableSpan, pErr = iexecutor.GetTableSpan(s.getUser(req), txn, escDbName, escTableName)
return pErr
}); pErr != nil {
return nil, s.serverError(pErr.GoError())
}
tableRSpan := roachpb.RSpan{}
var err error
tableRSpan.Key, err = keys.Addr(tableSpan.Key)
if err != nil {
return nil, s.serverError(err)
}
tableRSpan.EndKey, err = keys.Addr(tableSpan.EndKey)
if err != nil {
return nil, s.serverError(err)
}
rangeCount, pErr := s.distSender.CountRanges(tableRSpan)
if pErr != nil {
return nil, s.serverError(pErr.GoError())
}
resp.RangeCount = rangeCount
}
return &resp, nil
}
// TableDetails is an endpoint that returns columns, indices, and other
// relevant details for the specified table.
func (s *adminServer) TableDetails(
ctx context.Context, req *serverpb.TableDetailsRequest,
) (*serverpb.TableDetailsResponse, error) {
args := sql.SessionArgs{User: s.getUser(req)}
session := sql.NewSession(ctx, args, s.server.sqlExecutor, nil)
// TODO(cdo): Use real placeholders for the table and database names when we've extended our SQL
// grammar to allow that.
escDBName := parser.Name(req.Database).String()
escTableName := parser.Name(req.Table).String()
escQualTable := fmt.Sprintf("%s.%s", escDBName, escTableName)
query := fmt.Sprintf("SHOW COLUMNS FROM %s; SHOW INDEX FROM %s; SHOW GRANTS ON TABLE %s; SHOW CREATE TABLE %s;",
escQualTable, escQualTable, escQualTable, escQualTable)
r := s.server.sqlExecutor.ExecuteStatements(session, query, nil)
if err := s.firstNotFoundError(r.ResultList); err != nil {
return nil, grpc.Errorf(codes.NotFound, "%s", err)
}
if err := s.checkQueryResults(r.ResultList, 4); err != nil {
return nil, err
}
var resp serverpb.TableDetailsResponse
// Marshal SHOW COLUMNS result.
//
// TODO(cdo): protobuf v3's default behavior for fields with zero values (e.g. empty strings)
// is to suppress them. So, if protobuf field "foo" is an empty string, "foo" won't show
// up in the marshalled JSON. I feel that this is counterintuitive, and this should be fixed
// for our API.
{
const (
fieldCol = "Field" // column name
typeCol = "Type"
nullCol = "Null"
defaultCol = "Default"
)
scanner := makeResultScanner(r.ResultList[0].Columns)
for _, row := range r.ResultList[0].Rows {
var col serverpb.TableDetailsResponse_Column
if err := scanner.Scan(row, fieldCol, &col.Name); err != nil {
return nil, err
}
if err := scanner.Scan(row, typeCol, &col.Type); err != nil {
return nil, err
}
if err := scanner.Scan(row, nullCol, &col.Nullable); err != nil {
return nil, err
}
isDefaultNull, err := scanner.IsNull(row, defaultCol)
if err != nil {
return nil, err
}
if !isDefaultNull {
if err := scanner.Scan(row, defaultCol, &col.DefaultValue); err != nil {
return nil, err
}
}
resp.Columns = append(resp.Columns, col)
}
}
// Marshal SHOW INDEX result.
{
const (
nameCol = "Name"
uniqueCol = "Unique"
seqCol = "Seq"
columnCol = "Column"
directionCol = "Direction"
storingCol = "Storing"
)
scanner := makeResultScanner(r.ResultList[1].Columns)
for _, row := range r.ResultList[1].Rows {
// Marshal grant, splitting comma-separated privileges into a proper slice.
var index serverpb.TableDetailsResponse_Index
if err := scanner.Scan(row, nameCol, &index.Name); err != nil {
return nil, err
}
if err := scanner.Scan(row, uniqueCol, &index.Unique); err != nil {
return nil, err
}
if err := scanner.Scan(row, seqCol, &index.Seq); err != nil {
return nil, err
}
if err := scanner.Scan(row, columnCol, &index.Column); err != nil {
return nil, err
}
if err := scanner.Scan(row, directionCol, &index.Direction); err != nil {
return nil, err
}
if err := scanner.Scan(row, storingCol, &index.Storing); err != nil {
return nil, err
}
resp.Indexes = append(resp.Indexes, index)
}
}
// Marshal SHOW GRANTS result.
{
const (
userCol = "User"
privilegesCol = "Privileges"
)
scanner := makeResultScanner(r.ResultList[2].Columns)
for _, row := range r.ResultList[2].Rows {
// Marshal grant, splitting comma-separated privileges into a proper slice.
var grant serverpb.TableDetailsResponse_Grant
var privileges string
if err := scanner.Scan(row, userCol, &grant.User); err != nil {
return nil, err
}
if err := scanner.Scan(row, privilegesCol, &privileges); err != nil {
return nil, err
}
grant.Privileges = strings.Split(privileges, ",")
resp.Grants = append(resp.Grants, grant)
}
}
// Marshal SHOW CREATE TABLE result.
{
const createTableCol = "CreateTable"
showResult := r.ResultList[3]
if len(showResult.Rows) != 1 {
return nil, s.serverErrorf("CreateTable response not available.")
}
scanner := makeResultScanner(showResult.Columns)
var createStmt string
if err := scanner.Scan(showResult.Rows[0], createTableCol, &createStmt); err != nil {
return nil, err
}
resp.CreateTableStatement = createStmt
}
// Get the number of ranges in the table. We get the key span for the table
// data. Then, we count the number of ranges that make up that key span.
{
var iexecutor sql.InternalExecutor
var tableSpan roachpb.Span
if err := s.server.db.Txn(func(txn *client.Txn) error {
var err error
tableSpan, err = iexecutor.GetTableSpan(s.getUser(req), txn, escDBName, escTableName)
return err
}); err != nil {
return nil, s.serverError(err)
}
tableRSpan := roachpb.RSpan{}
var err error
tableRSpan.Key, err = keys.Addr(tableSpan.Key)
if err != nil {
return nil, s.serverError(err)
}
tableRSpan.EndKey, err = keys.Addr(tableSpan.EndKey)
if err != nil {
return nil, s.serverError(err)
}
rangeCount, err := s.server.distSender.CountRanges(tableRSpan)
if err != nil {
return nil, s.serverError(err)
}
resp.RangeCount = rangeCount
}
// Query the zone configuration for this table.
{
path, err := s.queryDescriptorIDPath(session, []string{escDBName, escTableName})
if err != nil {
return nil, s.serverError(err)
}
id, zone, zoneExists, err := s.queryZonePath(session, path)
if err != nil {
return nil, s.serverError(err)
}
if !zoneExists {
zone = config.DefaultZoneConfig()
}
resp.ZoneConfig = zone
switch id {
case path[1]:
resp.ZoneConfigLevel = serverpb.ZoneConfigurationLevel_DATABASE
case path[2]:
resp.ZoneConfigLevel = serverpb.ZoneConfigurationLevel_TABLE
default:
resp.ZoneConfigLevel = serverpb.ZoneConfigurationLevel_CLUSTER
}
}
return &resp, nil
}
func TestAdminAPITableDetails(t *testing.T) {
defer leaktest.AfterTest(t)()
s := StartTestServer(t)
defer s.Stop()
session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, s.sqlExecutor, nil)
setupQueries := []string{
"CREATE DATABASE test",
`
CREATE TABLE test.tbl (
nulls_allowed INT,
nulls_not_allowed INT NOT NULL DEFAULT 1000,
default2 INT DEFAULT 2,
string_default STRING DEFAULT 'default_string'
)`,
"GRANT SELECT ON test.tbl TO readonly",
"GRANT SELECT,UPDATE,DELETE ON test.tbl TO app",
"CREATE INDEX descIdx ON test.tbl (default2 DESC)",
}
for _, q := range setupQueries {
res := s.sqlExecutor.ExecuteStatements(session, q, nil)
if res.ResultList[0].PErr != nil {
t.Fatalf("error executing '%s': %s", q, res.ResultList[0].PErr)
}
}
// Perform API call.
var resp TableDetailsResponse
if err := apiGet(s, "databases/test/tables/tbl", &resp); err != nil {
t.Fatal(err)
}
// Verify columns.
expColumns := []TableDetailsResponse_Column{
{Name: "nulls_allowed", Type: "INT", Nullable: true, Default: ""},
{Name: "nulls_not_allowed", Type: "INT", Nullable: false, Default: "1000"},
{Name: "default2", Type: "INT", Nullable: true, Default: "2"},
{Name: "string_default", Type: "STRING", Nullable: true, Default: "'default_string'"},
{Name: "rowid", Type: "INT", Nullable: false, Default: "unique_rowid()"},
}
testutils.SortStructs(expColumns, "Name")
testutils.SortStructs(resp.Columns, "Name")
if a, e := len(resp.Columns), len(expColumns); a != e {
t.Fatalf("# of result columns %d != expected %d (got: %#v)", a, e, resp.Columns)
}
for i, a := range resp.Columns {
e := expColumns[i]
if a.String() != e.String() {
t.Fatalf("mismatch at index %d: actual %#v != %#v", i, a, e)
}
}
// Verify grants.
expGrants := []TableDetailsResponse_Grant{
{User: security.RootUser, Privileges: []string{"ALL"}},
{User: "******", Privileges: []string{"DELETE", "SELECT", "UPDATE"}},
{User: "******", Privileges: []string{"SELECT"}},
}
testutils.SortStructs(expGrants, "User")
testutils.SortStructs(resp.Grants, "User")
if a, e := len(resp.Grants), len(expGrants); a != e {
t.Fatalf("# of grant columns %d != expected %d (got: %#v)", a, e, resp.Grants)
}
for i, a := range resp.Grants {
e := expGrants[i]
sort.Strings(a.Privileges)
sort.Strings(e.Privileges)
if a.String() != e.String() {
t.Fatalf("mismatch at index %d: actual %#v != %#v", i, a, e)
}
}
// Verify indexes.
expIndexes := []TableDetailsResponse_Index{
{Name: "primary", Column: "rowid", Direction: "ASC", Unique: true, Seq: 1},
{Name: "descIdx", Column: "default2", Direction: "DESC", Unique: false, Seq: 1},
}
testutils.SortStructs(expIndexes, "Column")
testutils.SortStructs(resp.Indexes, "Column")
for i, a := range resp.Indexes {
e := expIndexes[i]
if a.String() != e.String() {
t.Fatalf("mismatch at index %d: actual %#v != %#v", i, a, e)
}
}
if a, e := resp.RangeCount, int64(1); a != e {
t.Fatalf("# of ranges %d != expected %d", a, e)
}
}
func TestAdminAPIDatabases(t *testing.T) {
defer leaktest.AfterTest(t)()
s := StartTestServer(t)
defer s.Stop()
// Test databases endpoint.
const testdb = "test"
session := sql.NewSession(sql.SessionArgs{User: security.RootUser}, s.sqlExecutor, nil)
query := "CREATE DATABASE " + testdb
createRes := s.sqlExecutor.ExecuteStatements(session, query, nil)
if createRes.ResultList[0].PErr != nil {
t.Fatal(createRes.ResultList[0].PErr)
}
var resp DatabasesResponse
if err := apiGet(s, "databases", &resp); err != nil {
t.Fatal(err)
}
// We should have the system database and the newly created test database.
if a, e := len(resp.Databases), 2; a != e {
t.Fatalf("length of result %d != expected %d", a, e)
}
sort.Strings(resp.Databases)
if a, e := resp.Databases[0], "system"; a != e {
t.Fatalf("database name %s != expected %s", a, e)
}
if a, e := resp.Databases[1], testdb; a != e {
t.Fatalf("database name %s != expected %s", a, e)
}
// Test database details endpoint.
privileges := []string{"SELECT", "UPDATE"}
testuser := "******"
grantQuery := "GRANT " + strings.Join(privileges, ", ") + " ON DATABASE " + testdb + " TO " + testuser
grantRes := s.sqlExecutor.ExecuteStatements(session, grantQuery, nil)
if grantRes.ResultList[0].PErr != nil {
t.Fatal(grantRes.ResultList[0].PErr)
}
var details DatabaseDetailsResponse
if err := apiGet(s, "databases/"+testdb, &details); err != nil {
t.Fatal(err)
}
if a, e := len(details.Grants), 2; a != e {
t.Fatalf("# of grants %d != expected %d", a, e)
}
for _, grant := range details.Grants {
switch grant.User {
case security.RootUser:
if !reflect.DeepEqual(grant.Privileges, []string{"ALL"}) {
t.Fatalf("privileges %v != expected %v", details.Grants[0].Privileges, privileges)
}
case testuser:
sort.Strings(grant.Privileges)
if !reflect.DeepEqual(grant.Privileges, privileges) {
t.Fatalf("privileges %v != expected %v", grant.Privileges, privileges)
}
default:
t.Fatalf("unknown grant to user %s", grant.User)
}
}
}
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)
}