func encodeSecondaryIndexes(tableID structured.ID, indexes []structured.IndexDescriptor,
colMap map[structured.ColumnID]int, values []parser.Datum, primaryIndexKeySuffix []byte) ([]indexEntry, error) {
var secondaryIndexEntries []indexEntry
for _, secondaryIndex := range indexes {
secondaryIndexKeyPrefix := structured.MakeIndexKeyPrefix(tableID, secondaryIndex.ID)
secondaryIndexKey, containsNull, err := encodeIndexKey(secondaryIndex.ColumnIDs, colMap, values, secondaryIndexKeyPrefix)
if err != nil {
return nil, err
}
if secondaryIndex.Unique && !containsNull {
secondaryIndexEntries = append(secondaryIndexEntries, indexEntry{
key: secondaryIndexKey,
value: primaryIndexKeySuffix,
})
} else {
secondaryIndexEntries = append(secondaryIndexEntries, indexEntry{
key: append(secondaryIndexKey, primaryIndexKeySuffix...),
value: nil,
})
}
}
return secondaryIndexEntries, nil
}
// Update updates columns for a selection of rows from a table.
// Privileges: WRITE and READ on table. We currently always use a select statement.
// Notes: postgres requires UPDATE. Requires SELECT with WHERE clause with table.
// mysql requires UPDATE. Also requires SELECT with WHERE clause with table.
func (p *planner) Update(n *parser.Update) (planNode, error) {
tableDesc, err := p.getAliasedTableDesc(n.Table)
if err != nil {
return nil, err
}
if !tableDesc.HasPrivilege(p.user, parser.PrivilegeWrite) {
return nil, fmt.Errorf("user %s does not have %s privilege on table %s",
p.user, parser.PrivilegeWrite, tableDesc.Name)
}
// Determine which columns we're inserting into.
var names parser.QualifiedNames
for _, expr := range n.Exprs {
names = append(names, expr.Name)
}
cols, err := p.processColumns(tableDesc, names)
if err != nil {
return nil, err
}
// Set of columns being updated
colIDSet := map[structured.ColumnID]struct{}{}
for _, c := range cols {
colIDSet[c.ID] = struct{}{}
}
// Don't allow updating any column that is part of the primary key.
for i, id := range tableDesc.PrimaryIndex.ColumnIDs {
if _, ok := colIDSet[id]; ok {
return nil, fmt.Errorf("primary key column %q cannot be updated", tableDesc.PrimaryIndex.ColumnNames[i])
}
}
// Query the rows that need updating.
// TODO(vivek): Avoid going through Select.
row, err := p.Select(&parser.Select{
Exprs: parser.SelectExprs{parser.StarSelectExpr},
From: parser.TableExprs{n.Table},
Where: n.Where,
})
if err != nil {
return nil, err
}
// Construct a map from column ID to the index the value appears at within a
// row.
colIDtoRowIndex := map[structured.ColumnID]int{}
for i, name := range row.Columns() {
c, err := tableDesc.FindColumnByName(name)
if err != nil {
return nil, err
}
colIDtoRowIndex[c.ID] = i
}
primaryIndex := tableDesc.PrimaryIndex
primaryIndexKeyPrefix := structured.MakeIndexKeyPrefix(tableDesc.ID, primaryIndex.ID)
// Evaluate all the column value expressions.
vals := make([]parser.Datum, 0, 10)
for _, expr := range n.Exprs {
val, err := parser.EvalExpr(expr.Expr)
if err != nil {
return nil, err
}
vals = append(vals, val)
}
// Secondary indexes needing updating.
var indexes []structured.IndexDescriptor
for _, index := range tableDesc.Indexes {
for _, id := range index.ColumnIDs {
if _, ok := colIDSet[id]; ok {
indexes = append(indexes, index)
break
}
}
}
// Update all the rows.
b := client.Batch{}
for row.Next() {
rowVals := row.Values()
primaryIndexKeySuffix, _, err := encodeIndexKey(primaryIndex.ColumnIDs, colIDtoRowIndex, rowVals, nil)
if err != nil {
return nil, err
}
primaryIndexKey := bytes.Join([][]byte{primaryIndexKeyPrefix, primaryIndexKeySuffix}, nil)
// Compute the current secondary index key:value pairs for this row.
secondaryIndexEntries, err := encodeSecondaryIndexes(tableDesc.ID, indexes, colIDtoRowIndex, rowVals, primaryIndexKeySuffix)
if err != nil {
return nil, err
}
// Compute the new secondary index key:value pairs for this row.
//
// Update the row values.
for i, col := range cols {
val := vals[i]
if !col.Nullable && val == parser.DNull {
return nil, fmt.Errorf("null value in column %q violates not-null constraint", col.Name)
}
rowVals[colIDtoRowIndex[col.ID]] = val
}
newSecondaryIndexEntries, err := encodeSecondaryIndexes(tableDesc.ID, indexes, colIDtoRowIndex, rowVals, primaryIndexKeySuffix)
if err != nil {
return nil, err
}
// Update secondary indexes.
for i, newSecondaryIndexEntry := range newSecondaryIndexEntries {
secondaryIndexEntry := secondaryIndexEntries[i]
if !bytes.Equal(newSecondaryIndexEntry.key, secondaryIndexEntry.key) {
if log.V(2) {
log.Infof("CPut %q -> %v", newSecondaryIndexEntry.key, newSecondaryIndexEntry.value)
}
b.CPut(newSecondaryIndexEntry.key, newSecondaryIndexEntry.value, nil)
if log.V(2) {
log.Infof("Del %q", secondaryIndexEntry.key)
}
b.Del(secondaryIndexEntry.key)
}
}
// Add the new values.
for i, val := range vals {
col := cols[i]
primitive, err := convertDatum(col, val)
if err != nil {
return nil, err
}
key := structured.MakeColumnKey(col.ID, primaryIndexKey)
if primitive != nil {
// We only output non-NULL values. Non-existent column keys are
// considered NULL during scanning and the row sentinel ensures we know
// the row exists.
if log.V(2) {
log.Infof("Put %q -> %v", key, val)
}
b.Put(key, primitive)
} else {
// The column might have already existed but is being set to NULL, so
// delete it.
if log.V(2) {
log.Infof("Del %q", key)
}
b.Del(key)
}
}
}
if err := row.Err(); err != nil {
return nil, err
}
if err := p.txn.Run(&b); err != nil {
if tErr, ok := err.(*proto.ConditionFailedError); ok {
return nil, fmt.Errorf("duplicate key value %q violates unique constraint %s", tErr.ActualValue.Bytes, "TODO(tamird)")
}
return nil, err
}
// TODO(tamird/pmattis): return the number of affected rows.
return &valuesNode{}, nil
}
func (n *scanNode) Next() bool {
if n.err != nil {
return false
}
if n.kvs == nil {
// Initialize our key/values.
if n.desc == nil {
// No table to read from, pretend there is a single empty row.
n.kvs = []client.KeyValue{}
n.primaryKey = []byte{}
} else {
// Retrieve all of the keys that start with our index key prefix.
startKey := proto.Key(structured.MakeIndexKeyPrefix(n.desc.ID, n.desc.PrimaryIndex.ID))
endKey := startKey.PrefixEnd()
// TODO(pmattis): Currently we retrieve all of the key/value pairs for
// the table. We could enhance this code so that it retrieves the
// key/value pairs in chunks.
n.kvs, n.err = n.db.Scan(startKey, endKey, 0)
if n.err != nil {
return false
}
}
}
// All of the columns for a particular row will be grouped together. We loop
// over the key/value pairs and decode the key to extract the columns encoded
// within the key and the column ID. We use the column ID to lookup the
// column and decode the value. All of these values go into a map keyed by
// column name. When the index key changes we output a row containing the
// current values.
for {
var kv client.KeyValue
if n.kvIndex < len(n.kvs) {
kv = n.kvs[n.kvIndex]
}
if n.primaryKey != nil &&
(n.kvIndex == len(n.kvs) || !bytes.HasPrefix(kv.Key, n.primaryKey)) {
// The current key belongs to a new row. Output the current row.
n.primaryKey = nil
var output bool
output, n.err = n.filterRow()
if n.err != nil {
return false
}
if output {
if n.err = n.renderRow(); n.err != nil {
return false
}
return true
}
}
if n.kvIndex == len(n.kvs) {
return false
}
if n.primaryKey == nil {
// This is the first key for the row, reset our vals map.
n.vals = valMap{}
}
var remaining []byte
remaining, n.err = decodeIndexKey(n.desc, n.desc.PrimaryIndex, n.vals, kv.Key)
if n.err != nil {
return false
}
n.primaryKey = []byte(kv.Key[:len(kv.Key)-len(remaining)])
// TODO(pmattis): We should avoid looking up the column name by column ID
// on every key. One possibility is that we could rewrite col-name
// references in expressions to refer to <table-id, col-id> tuples.
_, colID := encoding.DecodeUvarint(remaining)
var col *structured.ColumnDescriptor
col, n.err = n.desc.FindColumnByID(structured.ID(colID))
if n.err != nil {
return false
}
n.vals[col.Name] = unmarshalValue(*col, kv)
if log.V(2) {
log.Infof("Scan %q -> %v", kv.Key, n.vals[col.Name])
}
n.kvIndex++
}
}
// Delete deletes rows from a table.
// Privileges: WRITE and READ on table. We currently always use a SELECT statement.
// Notes: postgres requires DELETE. Also requires SELECT for "USING" and "WHERE" with tables.
// mysql requires DELETE. Also requires SELECT if a table is used in the "WHERE" clause.
func (p *planner) Delete(n *parser.Delete) (planNode, error) {
tableDesc, err := p.getAliasedTableDesc(n.Table)
if err != nil {
return nil, err
}
if !tableDesc.HasPrivilege(p.user, parser.PrivilegeWrite) {
return nil, fmt.Errorf("user %s does not have %s privilege on table %s",
p.user, parser.PrivilegeWrite, tableDesc.Name)
}
// TODO(tamird,pmattis): avoid going through Select to avoid encoding
// and decoding keys. Also, avoiding Select may provide more
// convenient access to index keys which we are not currently
// deleting.
node, err := p.Select(&parser.Select{
Exprs: parser.SelectExprs{
&parser.StarExpr{TableName: &parser.QualifiedName{Base: parser.Name(tableDesc.Name)}},
},
From: parser.TableExprs{n.Table},
Where: n.Where,
})
if err != nil {
return nil, err
}
// Construct a map from column ID to the index the value appears at within a
// row.
colIDtoRowIndex := map[structured.ID]int{}
for i, name := range node.Columns() {
c, err := tableDesc.FindColumnByName(name)
if err != nil {
return nil, err
}
colIDtoRowIndex[c.ID] = i
}
primaryIndex := tableDesc.PrimaryIndex
primaryIndexKeyPrefix := structured.MakeIndexKeyPrefix(tableDesc.ID, primaryIndex.ID)
b := client.Batch{}
for node.Next() {
if err := node.Err(); err != nil {
return nil, err
}
values := node.Values()
primaryIndexKeySuffix, _, err := encodeIndexKey(primaryIndex.ColumnIDs, colIDtoRowIndex, values, nil)
if err != nil {
return nil, err
}
primaryIndexKey := bytes.Join([][]byte{primaryIndexKeyPrefix, primaryIndexKeySuffix}, nil)
// Delete the secondary indexes.
secondaryIndexEntries, err := encodeSecondaryIndexes(tableDesc.ID, tableDesc.Indexes, colIDtoRowIndex, values, primaryIndexKeySuffix)
if err != nil {
return nil, err
}
for _, secondaryIndexEntry := range secondaryIndexEntries {
if log.V(2) {
log.Infof("Del %q", secondaryIndexEntry.key)
}
b.Del(secondaryIndexEntry.key)
}
// Delete the row.
rowStartKey := proto.Key(primaryIndexKey)
rowEndKey := rowStartKey.PrefixEnd()
if log.V(2) {
log.Infof("DelRange %q - %q", rowStartKey, rowEndKey)
}
b.DelRange(rowStartKey, rowEndKey)
}
if err := p.db.Run(&b); err != nil {
return nil, err
}
// TODO(tamird/pmattis): return the number of affected rows
return &valuesNode{}, nil
}
// Insert inserts rows into the database.
// Privileges: WRITE on table
// Notes: postgres requires INSERT. No "on duplicate key update" option.
// mysql requires INSERT. Also requires UPDATE on "ON DUPLICATE KEY UPDATE".
func (p *planner) Insert(n *parser.Insert) (planNode, error) {
tableDesc, err := p.getTableDesc(n.Table)
if err != nil {
return nil, err
}
if !tableDesc.HasPrivilege(p.user, parser.PrivilegeWrite) {
return nil, fmt.Errorf("user %s does not have %s privilege on table %s",
p.user, parser.PrivilegeWrite, tableDesc.Name)
}
// Determine which columns we're inserting into.
cols, err := p.processColumns(tableDesc, n.Columns)
if err != nil {
return nil, err
}
// Construct a map from column ID to the index the value appears at within a
// row.
colIDtoRowIndex := map[structured.ColumnID]int{}
for i, c := range cols {
colIDtoRowIndex[c.ID] = i
}
// Verify we have at least the columns that are part of the primary key.
primaryKeyCols := map[structured.ColumnID]struct{}{}
for i, id := range tableDesc.PrimaryIndex.ColumnIDs {
if _, ok := colIDtoRowIndex[id]; !ok {
return nil, fmt.Errorf("missing %q primary key column", tableDesc.PrimaryIndex.ColumnNames[i])
}
primaryKeyCols[id] = struct{}{}
}
// Transform the values into a rows object. This expands SELECT statements or
// generates rows from the values contained within the query.
rows, err := p.makePlan(n.Rows)
if err != nil {
return nil, err
}
primaryIndex := tableDesc.PrimaryIndex
primaryIndexKeyPrefix := structured.MakeIndexKeyPrefix(tableDesc.ID, primaryIndex.ID)
b := client.Batch{}
for rows.Next() {
values := rows.Values()
for range cols[len(values):] {
values = append(values, parser.DNull)
}
for _, col := range tableDesc.Columns {
if !col.Nullable {
if i, ok := colIDtoRowIndex[col.ID]; !ok || values[i] == parser.DNull {
return nil, fmt.Errorf("null value in column %q violates not-null constraint", col.Name)
}
}
}
primaryIndexKeySuffix, _, err := encodeIndexKey(primaryIndex.ColumnIDs, colIDtoRowIndex, values, nil)
if err != nil {
return nil, err
}
primaryIndexKey := bytes.Join([][]byte{primaryIndexKeyPrefix, primaryIndexKeySuffix}, nil)
// Write the secondary indexes.
secondaryIndexEntries, err := encodeSecondaryIndexes(tableDesc.ID, tableDesc.Indexes, colIDtoRowIndex, values, primaryIndexKeySuffix)
if err != nil {
return nil, err
}
for _, secondaryIndexEntry := range secondaryIndexEntries {
if log.V(2) {
log.Infof("CPut %q -> %v", secondaryIndexEntry.key, secondaryIndexEntry.value)
}
b.CPut(secondaryIndexEntry.key, secondaryIndexEntry.value, nil)
}
// Write the row sentinel.
if log.V(2) {
log.Infof("CPut %q -> NULL", primaryIndexKey)
}
b.CPut(primaryIndexKey, nil, nil)
// Write the row columns.
for i, val := range values {
col := cols[i]
// Make sure the value can be written to the column before proceeding.
primitive, err := convertDatum(col, val)
if err != nil {
return nil, err
}
if _, ok := primaryKeyCols[col.ID]; ok {
// Skip primary key columns as their values are encoded in the row
// sentinel key which is guaranteed to exist for as long as the row
// exists.
continue
}
if primitive != nil {
// We only output non-NULL values. Non-existent column keys are
// considered NULL during scanning and the row sentinel ensures we know
// the row exists.
key := structured.MakeColumnKey(col.ID, primaryIndexKey)
if log.V(2) {
log.Infof("CPut %q -> %v", key, primitive)
}
b.CPut(key, primitive, nil)
}
}
}
if err := rows.Err(); err != nil {
return nil, err
}
if err := p.txn.Run(&b); err != nil {
if tErr, ok := err.(*proto.ConditionFailedError); ok {
return nil, fmt.Errorf("duplicate key value %q violates unique constraint %s", tErr.ActualValue.Bytes, "TODO(tamird)")
}
return nil, err
}
// TODO(tamird/pmattis): return the number of affected rows
return &valuesNode{}, nil
}
// Insert inserts rows into the database.
// Privileges: WRITE on table
// Notes: postgres requires INSERT. No "on duplicate key update" option.
// mysql requires INSERT. Also requires UPDATE on "ON DUPLICATE KEY UPDATE".
func (p *planner) Insert(n *parser.Insert) (planNode, error) {
tableDesc, err := p.getTableDesc(n.Table)
if err != nil {
return nil, err
}
if !tableDesc.HasPrivilege(p.user, parser.PrivilegeWrite) {
return nil, fmt.Errorf("user %s does not have %s privilege on table %s",
p.user, parser.PrivilegeWrite, tableDesc.Name)
}
// Determine which columns we're inserting into.
cols, err := p.processColumns(tableDesc, n.Columns)
if err != nil {
return nil, err
}
// Construct a map from column ID to the index the value appears at within a
// row.
colIDtoRowIndex := map[structured.ID]int{}
for i, c := range cols {
colIDtoRowIndex[c.ID] = i
}
// Verify we have at least the columns that are part of the primary key.
for i, id := range tableDesc.PrimaryIndex.ColumnIDs {
if _, ok := colIDtoRowIndex[id]; !ok {
return nil, fmt.Errorf("missing %q primary key column", tableDesc.PrimaryIndex.ColumnNames[i])
}
}
// Transform the values into a rows object. This expands SELECT statements or
// generates rows from the values contained within the query.
rows, err := p.makePlan(n.Rows)
if err != nil {
return nil, err
}
primaryIndex := tableDesc.PrimaryIndex
primaryIndexKeyPrefix := structured.MakeIndexKeyPrefix(tableDesc.ID, primaryIndex.ID)
b := client.Batch{}
for rows.Next() {
values := rows.Values()
if len(values) != len(cols) {
return nil, fmt.Errorf("invalid values for columns: %d != %d", len(values), len(cols))
}
primaryIndexKeySuffix, _, err := encodeIndexKey(primaryIndex.ColumnIDs, colIDtoRowIndex, values, nil)
if err != nil {
return nil, err
}
primaryIndexKey := bytes.Join([][]byte{primaryIndexKeyPrefix, primaryIndexKeySuffix}, nil)
// Write the secondary indexes.
secondaryIndexEntries, err := encodeSecondaryIndexes(tableDesc.ID, tableDesc.Indexes, colIDtoRowIndex, values, primaryIndexKeySuffix)
if err != nil {
return nil, err
}
for _, secondaryIndexEntry := range secondaryIndexEntries {
if log.V(2) {
log.Infof("CPut %q -> %v", secondaryIndexEntry.key, secondaryIndexEntry.value)
}
b.CPut(secondaryIndexEntry.key, secondaryIndexEntry.value, nil)
}
// Write the row.
for i, val := range values {
key := structured.MakeColumnKey(cols[i].ID, primaryIndexKey)
if log.V(2) {
log.Infof("CPut %q -> %v", key, val)
}
v, err := prepareVal(cols[i], val)
if err != nil {
return nil, err
}
b.CPut(key, v, nil)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
if err := p.db.Run(&b); err != nil {
if tErr, ok := err.(*proto.ConditionFailedError); ok {
return nil, fmt.Errorf("duplicate key value %q violates unique constraint %s", tErr.ActualValue.Bytes, "TODO(tamird)")
}
return nil, err
}
// TODO(tamird/pmattis): return the number of affected rows
return &valuesNode{}, nil
}