// 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
}
// 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
}
// 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
}