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