Repository: genjidb/genji (go)

Issue:
Support dot notation on UPDATE ... SET
Currently, the `UPDATE` clause only accepts simple top-level fields `foo`.
We should be able to update nested fields or array indexes using [dot notation](https://genji.dev/docs/genji-sql/lexical-structure/#dot-notation).

```sql
UPDATE users SET a.3.name = [1, 2, 3];
```

Because nested values can be tricky, the SET clause must only set a value when it is possible. If we take the example above, the document must respect the following requirements in order to be settable:

- `a` is an array
- `a.3` exists and is a document
- `a.3.name` may or may not exist (same rules as for top-level fields, see below)

Currently, the rule is: _if the field doesn't exist, we create it, otherwise we replace it with the new value_.
With this change, the rule still applies but only for the element of the dot notation path that is the most to the right.

### Exception for arrays

```sql
INSERT INTO users (a) VALUES ([1, 0, 0]), ([2, 0]);
UPDATE users SET a.2 = 1;
SELECT * FROM users WHERE a.2 = 1;
{"a": [1, 0, 1]}
```

This example shows that the above rule must not apply for arrays. If the array index doesn't exist, it must not be created.

Pull request description:
Allow dot notation for update set.
This PR fixes #84 by a deep recursive course of the document based on the ValuePath.
 #Set method is now taking a ValuePath.


Patch:
diff --git a/cmd/genji/shell/command.go b/cmd/genji/shell/command.go
index cfb46bfd..60ba2d73 100644
--- a/cmd/genji/shell/command.go
+++ b/cmd/genji/shell/command.go
@@ -2,11 +2,19 @@ package shell
 
 import (
 	"fmt"
+	"sort"
 
 	"github.com/genjidb/genji"
 	"github.com/genjidb/genji/document"
 )
 
+var commands = map[string]string{
+	".tables":               "\t\tList names of tables.",
+	".exit":                 "\t\t\tExit this program.",
+	".indexes [table_name]": "\tDisplay all indexes or the indexes of the given table name.",
+	".help":                 "\t\t\tList all commands.",
+}
+
 func runTablesCmd(db *genji.DB, cmd []string) error {
 	if len(cmd) > 1 {
 		return fmt.Errorf("usage: .tables")
@@ -79,8 +87,22 @@ func runIndexesCmd(db *genji.DB, in []string) error {
 	case 2:
 		// If the input is ".indexes <tableName>"
 		return displayTableIndex(db, in[1])
-
 	}
 
 	return fmt.Errorf("usage: .indexes [tablename]")
 }
+
+// runHelpCmd display all available dot commands.
+func runHelpCmd() error {
+	var keys []string
+	for k := range commands {
+		keys = append(keys, k)
+	}
+
+	sort.Strings(keys)
+	for _, k := range keys {
+		fmt.Printf("%s %s\n", k, commands[k])
+	}
+
+	return nil
+}
diff --git a/cmd/genji/shell/shell.go b/cmd/genji/shell/shell.go
index 6283da6b..ba214f4e 100644
--- a/cmd/genji/shell/shell.go
+++ b/cmd/genji/shell/shell.go
@@ -88,6 +88,7 @@ func Run(opts *Options) error {
 		fmt.Printf("On-disk database using Badger engine at path %s.\n", opts.DBPath)
 	}
 
+	fmt.Println("Enter \".help\" for usage hints.")
 	history, err := sh.loadHistory()
 	if err != nil {
 		return err
@@ -237,9 +238,11 @@ func (sh *Shell) runCommand(in string) error {
 		}
 
 		return runIndexesCmd(db, cmd)
+	case ".help":
+		return runHelpCmd()
 	}
 
-	return fmt.Errorf("unknown command %q", cmd)
+	return fmt.Errorf("unknown command %q. Enter \".help\" for help.", cmd)
 }
 
 func (sh *Shell) runQuery(q string) error {
diff --git a/document/document.go b/document/document.go
index a85279ab..9ffdfaee 100644
--- a/document/document.go
+++ b/document/document.go
@@ -86,16 +86,101 @@ func (fb FieldBuffer) GetByField(field string) (Value, error) {
 	return Value{}, ErrFieldNotFound
 }
 
+// setFieldValue replaces a field if it already exists or creates one if not.
+func (fb *FieldBuffer) setFieldValue(field string, reqValue Value) error {
+	_, err := fb.GetByField(field)
+	switch err {
+	case ErrFieldNotFound:
+		fb.Add(field, reqValue)
+		return nil
+	case nil:
+		_ = fb.Replace(field, reqValue)
+		return nil
+	}
+
+	return err
+}
+
+// setValueAtPath deep replaces or creates a field
+// using the value path
+func (fb *FieldBuffer) setValueAtPath(v Value, p ValuePath, newValue Value) (Value, error) {
+
+	switch v.Type {
+	case DocumentValue:
+		var buf FieldBuffer
+		err := buf.ScanDocument(v.V.(Document))
+		if err != nil {
+			return v, err
+		}
+
+		if len(p) == 1 {
+			err = buf.setFieldValue(p[0], newValue)
+			return NewDocumentValue(&buf), err
+		}
+
+		va, err := buf.GetByField(p[0])
+		if err != nil {
+			return v, err
+		}
+
+		va, err = buf.setValueAtPath(va, p[1:], newValue)
+		if err != nil {
+			return v, err
+		}
+
+		err = buf.setFieldValue(p[0], va)
+		return NewDocumentValue(&buf), err
+	case ArrayValue:
+		var vb ValueBuffer
+		err := vb.ScanArray(v.V.(Array))
+		if err != nil {
+			return v, err
+		}
+
+		index, err := strconv.Atoi(p[0])
+		if err != nil {
+			return v, err
+		}
+
+		va, err := vb.GetByIndex(index)
+		if err != nil {
+			return v, err
+		}
+
+		if len(p) == 1 {
+			err = vb.Replace(index, newValue)
+			return NewArrayValue(&vb), err
+		}
+
+		va, err = fb.setValueAtPath(va, p[1:], newValue)
+		err = vb.Replace(index, va)
+		return NewArrayValue(&vb), err
+	}
+
+	return v, nil
+}
+
 // Set replaces a field if it already exists or creates one if not.
-func (fb *FieldBuffer) Set(f string, v Value) {
-	for i := range fb.fields {
-		if fb.fields[i].Field == f {
+func (fb *FieldBuffer) Set(path ValuePath, reqValue Value) error {
+	// check if the ValuePath contains only one field to set or replace
+	if len(path) == 1 {
+		// Set or replace the unique field
+		return fb.setFieldValue(path[0], reqValue)
+	}
+
+	for i, field := range fb.fields {
+		if path[0] == field.Field {
+			v, err := fb.setValueAtPath(field.Value, path[1:], reqValue)
+			if err != nil {
+				return err
+			}
 			fb.fields[i].Value = v
-			return
+			return nil
 		}
 	}
 
-	fb.Add(f, v)
+	// return an error if the first element of the path is not found
+	return ErrFieldNotFound
 }
 
 // Iterate goes through all the fields of the document and calls the given function by passing each one of them.
diff --git a/sql/parser/update.go b/sql/parser/update.go
index eb8243c7..a89a5f93 100644
--- a/sql/parser/update.go
+++ b/sql/parser/update.go
@@ -1,6 +1,8 @@
 package parser
 
 import (
+	"strings"
+
 	"github.com/genjidb/genji/sql/planner"
 	"github.com/genjidb/genji/sql/query/expr"
 	"github.com/genjidb/genji/sql/scanner"
@@ -56,24 +58,24 @@ func (p *Parser) parseSetClause() ([]updateSetPair, error) {
 			}
 		}
 
-		// Scan the identifier for the field name.
-		tok, pos, lit := p.ScanIgnoreWhitespace()
-		if tok != scanner.IDENT {
-			return nil, newParseError(scanner.Tokstr(tok, lit), []string{"identifier"}, pos)
+		ref, err := p.parseFieldRef()
+		if err != nil {
+			return nil, err
 		}
 
+		lit := strings.Join(ref, ".")
 		// Scan the eq sign
 		if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != scanner.EQ {
 			return nil, newParseError(scanner.Tokstr(tok, lit), []string{"="}, pos)
 		}
 
 		// Scan the expr for the value.
-		expr, _, err := p.ParseExpr()
+		parsedExpr, _, err := p.ParseExpr()
 		if err != nil {
 			return nil, err
 		}
-		pairs = append(pairs, updateSetPair{lit, expr})
 
+		pairs = append(pairs, updateSetPair{lit, parsedExpr})
 		firstPair = false
 	}
 
diff --git a/sql/planner/tree.go b/sql/planner/tree.go
index 57dd0ac8..5e574372 100644
--- a/sql/planner/tree.go
+++ b/sql/planner/tree.go
@@ -370,20 +370,10 @@ func (n *setNode) toStream(st document.Stream) (document.Stream, error) {
 			return nil, err
 		}
 
-		_, err = fb.GetByField(n.field)
-
-		switch err {
-		case nil:
-			// If no error, it means that the field already exists
-			// and it should be replaced.
-			_ = fb.Replace(n.field, ev)
-		case document.ErrFieldNotFound:
-			// If the field doesn't exist,
-			// it should be added to the document.
-			fb.Set(n.field, ev)
-		}
+		path := document.NewValuePath(n.field)
+		err = fb.Set(path, ev)
 
-		return &fb, nil
+		return &fb, err
 	}), nil
 }
 


Test patch:
diff --git a/document/document_test.go b/document/document_test.go
index 3b9e2712..a243909a 100644
--- a/document/document_test.go
+++ b/document/document_test.go
@@ -1,6 +1,7 @@
 package document_test
 
 import (
+	"bytes"
 	"encoding/json"
 	"errors"
 	"testing"
@@ -78,20 +79,78 @@ func TestFieldBuffer(t *testing.T) {
 	})
 
 	t.Run("Set", func(t *testing.T) {
+		d, err := document.NewFromJSON([]byte(`{"friends": [{"name": "Bar","address": {"city": "Paris","zipcode": "75001"}}]}`))
+		require.NoError(t, err)
 		var buf document.FieldBuffer
-		buf.Add("a", document.NewIntegerValue(10))
-		buf.Add("b", document.NewTextValue("hello"))
-
-		buf.Set("a", document.NewDoubleValue(11))
-		v, err := buf.GetByField("a")
+		err = buf.Copy(d)
 		require.NoError(t, err)
-		require.Equal(t, document.NewDoubleValue(11), v)
 
-		buf.Set("c", document.NewIntegerValue(12))
-		require.Equal(t, 3, buf.Len())
-		v, err = buf.GetByField("c")
+		var fb document.FieldBuffer
+		d, err = document.NewFromJSON([]byte(`{"phone":{"type": "cell", "number":"111-222-333"}}`))
+		require.NoError(t, err)
+		err = fb.Copy(d)
 		require.NoError(t, err)
-		require.Equal(t, document.NewIntegerValue(12), v)
+
+		var vb document.ValueBuffer
+		vb = vb.Append(document.NewIntegerValue(1))
+		vb = vb.Append(document.NewIntegerValue(0))
+		vb = vb.Append(document.NewIntegerValue(0))
+
+		tests := []struct {
+			name string
+			p    document.ValuePath
+			v    document.Value
+			want string
+		}{
+			{"Set replace field textValue",
+				document.NewValuePath("friends.0.name"),
+				document.NewTextValue("Baz"),
+				`{"friends": [{"name": "Baz", "address": {"city": "Paris", "zipcode": "75001"}}]}`,
+			},
+			{"Set array",
+				document.NewValuePath("friends.0.a"),
+				document.NewArrayValue(&vb),
+				`{"friends": [{"name": "Baz", "address": {"city": "Paris", "zipcode": "75001"}, "a": [1, 0, 0]}]}`,
+			},
+			{"Nested array",
+				document.NewValuePath("friends.0.a.0"),
+				document.NewArrayValue(&vb),
+				`{"friends": [{"name": "Baz", "address": {"city": "Paris", "zipcode": "75001"}, "a": [[1, 0, 0], 0, 0]}]}`,
+			},
+			{"Set at index",
+				document.NewValuePath("friends.0.a.1"),
+				document.NewIntegerValue(99),
+				`{"friends": [{"name": "Baz", "address": {"city": "Paris", "zipcode": "75001"}, "a": [[1, 0, 0], 99, 0]}]}`,
+			},
+			{
+				"Set Document",
+				document.NewValuePath("contact"),
+				document.NewDocumentValue(&fb),
+				`{"friends": [{"name": "Baz", "address": {"city": "Paris", "zipcode": "75001"}, "a": [[1, 0, 0], 99, 0]}], "contact": {"phone": {"type": "cell", "number": "111-222-333"}}}`,
+			},
+			{
+				"Set Nested Document",
+				document.NewValuePath("contact.phone.type"),
+				document.NewTextValue("fix"),
+				`{"friends": [{"name": "Baz", "address": {"city": "Paris", "zipcode": "75001"}, "a": [[1, 0, 0], 99, 0]}], "contact": {"phone": {"type": "fix", "number": "111-222-333"}}}`,
+			},
+			{"Set add field into nested document",
+				document.NewValuePath("contact.email"),
+				document.NewTextValue("foo@example.com"),
+				`{"friends": [{"name": "Baz", "address": {"city": "Paris", "zipcode": "75001"}, "a": [[1, 0, 0], 99, 0]}], "contact": {"phone": {"type": "fix", "number": "111-222-333"}, "email": "foo@example.com"}}`,
+			},
+		}
+
+		for _, test := range tests {
+			t.Run(test.name, func(t *testing.T) {
+				var bufBytes bytes.Buffer
+				require.NoError(t, err, buf.Set(test.p, test.v))
+				err := document.ToJSON(&bufBytes, buf)
+				require.NoError(t, err)
+				require.Equal(t, test.want, bufBytes.String())
+			})
+		}
+
 	})
 
 	t.Run("Delete", func(t *testing.T) {
diff --git a/sql/parser/update_test.go b/sql/parser/update_test.go
index 0e828981..b76bb4d0 100644
--- a/sql/parser/update_test.go
+++ b/sql/parser/update_test.go
@@ -25,6 +25,29 @@ func TestParserUpdate(t *testing.T) {
 					"test",
 				)),
 			false},
+		{"SET/Dot notation", "UPDATE test SET a.2.a = 1",
+			planner.NewTree(
+				planner.NewReplacementNode(
+					planner.NewSetNode(
+						planner.NewTableInputNode("test"),
+						"a.2.a", expr.IntegerValue(1),
+					),
+					"test",
+				)),
+			false},
+		{"SET/Dot notation with cond", "UPDATE test SET a.2.a = 1 WHERE age = 10",
+			planner.NewTree(
+				planner.NewReplacementNode(
+					planner.NewSetNode(
+						planner.NewSelectionNode(
+							planner.NewTableInputNode("test"),
+							expr.Eq(expr.FieldSelector([]string{"age"}), expr.IntegerValue(10)),
+						),
+						"a.2.a", expr.IntegerValue(1),
+					),
+					"test",
+				)),
+			false},
 		{"SET/With cond", "UPDATE test SET a = 1, b = 2 WHERE age = 10",
 			planner.NewTree(
 				planner.NewReplacementNode(
diff --git a/sql/query/update_test.go b/sql/query/update_test.go
index d7ff9aef..0d217218 100644
--- a/sql/query/update_test.go
+++ b/sql/query/update_test.go
@@ -77,22 +77,46 @@ func TestUpdateStmt(t *testing.T) {
 	}
 
 	t.Run("with arrays", func(t *testing.T) {
-		db, err := genji.Open(":memory:")
-		require.NoError(t, err)
-		defer db.Close()
-
-		res, err := db.Query(`
-			CREATE TABLE foo;
-			INSERT INTO foo (a) VALUES ([1, 0, 0]), ([2, 0]);
-			UPDATE foo set b = 0;
-			SELECT * FROM foo; 
-		`)
-		require.NoError(t, err)
-		defer res.Close()
-
-		var buf bytes.Buffer
-		err = document.IteratorToJSONArray(&buf, res)
-		require.NoError(t, err)
-		require.JSONEq(t, `[{"a": [1, 0, 0], "b": 0}, {"a": [2, 0], "b": 0}]`, buf.String())
+		tests := []struct {
+			name     string
+			query    string
+			fails    bool
+			expected string
+			params   []interface{}
+		}{
+			{"SET / No cond add field ", `UPDATE foo set b = 0`, false, `[{"a": [1, 0, 0], "b": 0}, {"a": [2, 0], "b": 0}]`, nil},
+			{"SET / No cond with dot notation at existing index only", `UPDATE foo SET a.2 = 10`, false, `[{"a": [1, 0, 10]}, {"a": [2, 0]}]`, nil},
+			{"SET / No cond with dot notation", `UPDATE foo SET a.1 = 10`, false, `[{"a": [1, 10, 0]}, {"a": [2, 10]}]`, nil},
+			{"SET / No cond with dot notation non existing field", `UPDATE foo SET a.foo.1 = 10`, true, "", nil},
+			{"SET / With cond with dot notation", `UPDATE foo SET a.0 = 1 WHERE a.0 = 2`, false, `[{"a": [1, 0, 0]}, {"a": [1, 0]}]`, nil},
+		}
+
+		for _, tt := range tests {
+			db, err := genji.Open(":memory:")
+			require.NoError(t, err)
+			defer db.Close()
+
+			err = db.Exec(`CREATE TABLE foo;`)
+			require.NoError(t, err)
+			err = db.Exec(`INSERT INTO foo (a) VALUES ([1, 0, 0]), ([2, 0]);`)
+			require.NoError(t, err)
+
+			err = db.Exec(tt.query, tt.params...)
+			if tt.fails {
+				require.Error(t, err)
+				continue
+			}
+
+			require.NoError(t, err)
+			st, err := db.Query("SELECT * FROM foo")
+			require.NoError(t, err)
+			defer st.Close()
+
+			var buf bytes.Buffer
+
+			err = document.IteratorToJSONArray(&buf, st)
+			require.NoError(t, err)
+			require.JSONEq(t, tt.expected, buf.String())
+		}
 	})
 }