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 }