Repository: genjidb/genji
Pull request #386 (2021-04-13 05:39:24)

Auto generate index name
This allows creating indexes without specifying an index name:

Synopsis:
```
CREATE INDEX [[IF NOT EXISTS] name] ON table_name (field_name)
```

Example:

```sql
CREATE INDEX ON foo(a);
CREATE UNIQUE INDEX ON foo(a);
CREATE INDEX IF NOT EXISTS idx_foo_a ON foo(a);
```



Patch:
diff --git a/cmd/genji/shell/command.go b/cmd/genji/shell/command.go
index 15b26c28..f2073580 100644
--- a/cmd/genji/shell/command.go
+++ b/cmd/genji/shell/command.go
@@ -223,7 +223,7 @@ func runSaveCmd(ctx context.Context, db *genji.DB, engineName string, dbPath str
 			return err
 		}
 
-		err = otherTx.CreateIndex(index)
+		err = otherTx.CreateIndex(&index)
 		if err != nil {
 			return err
 		}
diff --git a/database/catalog.go b/database/catalog.go
index cc500428..1b41c14f 100644
--- a/database/catalog.go
+++ b/database/catalog.go
@@ -167,8 +167,22 @@ func (c *Catalog) DropTable(tx *Transaction, tableName string) error {
 
 // CreateIndex creates an index with the given name.
 // If it already exists, returns ErrIndexAlreadyExists.
-func (c *Catalog) CreateIndex(tx *Transaction, opts IndexInfo) error {
-	err := c.cache.AddIndex(tx, &opts)
+func (c *Catalog) CreateIndex(tx *Transaction, opts *IndexInfo) error {
+	if strings.HasPrefix(opts.IndexName, internalPrefix) {
+		return stringutil.Errorf("table name must not start with %s", internalPrefix)
+	}
+
+	// auto-generate index name
+	if opts.IndexName == "" {
+		seq, err := tx.getIndexStore().st.NextSequence()
+		if err != nil {
+			return err
+		}
+
+		opts.IndexName = stringutil.Sprintf("%sautoindex_%s_%d", internalPrefix, opts.TableName, seq)
+	}
+
+	err := c.cache.AddIndex(tx, opts)
 	if err != nil {
 		return err
 	}
diff --git a/database/config.go b/database/config.go
index e6d81370..30dc9967 100644
--- a/database/config.go
+++ b/database/config.go
@@ -303,7 +303,7 @@ type indexStore struct {
 	st engine.Store
 }
 
-func (t *indexStore) Insert(cfg IndexInfo) error {
+func (t *indexStore) Insert(cfg *IndexInfo) error {
 	key := []byte(cfg.IndexName)
 	_, err := t.st.Get(key)
 	if err == nil {
diff --git a/database/transaction.go b/database/transaction.go
index f7b78aa0..21d07d88 100644
--- a/database/transaction.go
+++ b/database/transaction.go
@@ -130,7 +130,7 @@ func (tx *Transaction) DropTable(name string) error {
 
 // CreateIndex creates an index with the given name.
 // If it already exists, returns ErrIndexAlreadyExists.
-func (tx *Transaction) CreateIndex(opts IndexInfo) error {
+func (tx *Transaction) CreateIndex(opts *IndexInfo) error {
 	err := tx.db.catalog.CreateIndex(tx, opts)
 	if err != nil {
 		return err
diff --git a/query/create.go b/query/create.go
index 9d57b64a..445e5ed2 100644
--- a/query/create.go
+++ b/query/create.go
@@ -1,8 +1,6 @@
 package query
 
 import (
-	"errors"
-
 	"github.com/genjidb/genji/database"
 	"github.com/genjidb/genji/document"
 	"github.com/genjidb/genji/expr"
@@ -25,10 +23,6 @@ func (stmt CreateTableStmt) IsReadOnly() bool {
 func (stmt CreateTableStmt) Run(tx *database.Transaction, args []expr.Param) (Result, error) {
 	var res Result
 
-	if stmt.TableName == "" {
-		return res, errors.New("missing table name")
-	}
-
 	err := tx.CreateTable(stmt.TableName, &stmt.Info)
 	if stmt.IfNotExists && err == database.ErrTableAlreadyExists {
 		err = nil
@@ -57,19 +51,7 @@ func (stmt CreateIndexStmt) IsReadOnly() bool {
 func (stmt CreateIndexStmt) Run(tx *database.Transaction, args []expr.Param) (Result, error) {
 	var res Result
 
-	if stmt.TableName == "" {
-		return res, errors.New("missing table name")
-	}
-
-	if stmt.IndexName == "" {
-		return res, errors.New("missing index name")
-	}
-
-	if len(stmt.Path) == 0 {
-		return res, errors.New("missing path")
-	}
-
-	err := tx.CreateIndex(database.IndexInfo{
+	err := tx.CreateIndex(&database.IndexInfo{
 		Unique:    stmt.Unique,
 		IndexName: stmt.IndexName,
 		TableName: stmt.TableName,
diff --git a/sql/parser/create.go b/sql/parser/create.go
index 50c5a87b..1d89f7e1 100644
--- a/sql/parser/create.go
+++ b/sql/parser/create.go
@@ -266,10 +266,15 @@ func (p *Parser) parseCreateIndexStatement(unique bool) (query.CreateIndexStmt,
 		return stmt, err
 	}
 
-	// Parse index name
+	// Parse optional index name
 	stmt.IndexName, err = p.parseIdent()
 	if err != nil {
-		return stmt, err
+		// if IF NOT EXISTS is set, index name is mandatory
+		if stmt.IfNotExists {
+			return stmt, err
+		}
+
+		p.Unscan()
 	}
 
 	// Parse "ON"


Test patch:
diff --git a/database/catalog_test.go b/database/catalog_test.go
index 6bf45379..cd1a02c0 100644
--- a/database/catalog_test.go
+++ b/database/catalog_test.go
@@ -115,9 +115,9 @@ func TestCatalogTable(t *testing.T) {
 			err := catalog.CreateTable(tx, "foo", ti)
 			require.NoError(t, err)
 
-			err = catalog.CreateIndex(tx, database.IndexInfo{Path: parsePath(t, "gender"), IndexName: "idx_gender", TableName: "foo"})
+			err = catalog.CreateIndex(tx, &database.IndexInfo{Path: parsePath(t, "gender"), IndexName: "idx_gender", TableName: "foo"})
 			require.NoError(t, err)
-			err = catalog.CreateIndex(tx, database.IndexInfo{Path: parsePath(t, "city"), IndexName: "idx_city", TableName: "foo", Unique: true})
+			err = catalog.CreateIndex(tx, &database.IndexInfo{Path: parsePath(t, "city"), IndexName: "idx_city", TableName: "foo", Unique: true})
 			require.NoError(t, err)
 
 			return nil
@@ -317,7 +317,7 @@ func TestTxCreateIndex(t *testing.T) {
 		clone := catalog.Clone()
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := catalog.CreateIndex(tx, database.IndexInfo{
+			err := catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "idx_a", TableName: "test", Path: parsePath(t, "a"),
 			})
 			require.NoError(t, err)
@@ -354,12 +354,12 @@ func TestTxCreateIndex(t *testing.T) {
 		})
 
 		update(t, db, func(tx *database.Transaction) error {
-			err := catalog.CreateIndex(tx, database.IndexInfo{
+			err := catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "idxFoo", TableName: "test", Path: parsePath(t, "foo"),
 			})
 			require.NoError(t, err)
 
-			err = catalog.CreateIndex(tx, database.IndexInfo{
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "idxFoo", TableName: "test", Path: parsePath(t, "foo"),
 			})
 			require.Equal(t, database.ErrIndexAlreadyExists, err)
@@ -372,7 +372,7 @@ func TestTxCreateIndex(t *testing.T) {
 		defer cleanup()
 		catalog := db.Catalog()
 		update(t, db, func(tx *database.Transaction) error {
-			err := catalog.CreateIndex(tx, database.IndexInfo{
+			err := catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "idxFoo", TableName: "test", Path: parsePath(t, "foo"),
 			})
 			if !errors.Is(err, database.ErrTableNotFound) {
@@ -382,6 +382,36 @@ func TestTxCreateIndex(t *testing.T) {
 			return nil
 		})
 	})
+
+	t.Run("Should generate a name if not provided", func(t *testing.T) {
+		db, cleanup := newTestDB(t)
+		defer cleanup()
+		catalog := db.Catalog()
+
+		update(t, db, func(tx *database.Transaction) error {
+			return catalog.CreateTable(tx, "test", nil)
+		})
+
+		update(t, db, func(tx *database.Transaction) error {
+			err := catalog.CreateIndex(tx, &database.IndexInfo{
+				TableName: "test", Path: parsePath(t, "foo"),
+			})
+			require.NoError(t, err)
+
+			_, err = catalog.GetIndex(tx, "__genji_autoindex_test_1")
+			require.NoError(t, err)
+
+			// create another one
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
+				TableName: "test", Path: parsePath(t, "foo"),
+			})
+			require.NoError(t, err)
+
+			_, err = catalog.GetIndex(tx, "__genji_autoindex_test_2")
+			require.NoError(t, err)
+			return nil
+		})
+	})
 }
 
 func TestTxDropIndex(t *testing.T) {
@@ -393,11 +423,11 @@ func TestTxDropIndex(t *testing.T) {
 		update(t, db, func(tx *database.Transaction) error {
 			err := catalog.CreateTable(tx, "test", nil)
 			require.NoError(t, err)
-			err = catalog.CreateIndex(tx, database.IndexInfo{
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "idxFoo", TableName: "test", Path: parsePath(t, "foo"),
 			})
 			require.NoError(t, err)
-			err = catalog.CreateIndex(tx, database.IndexInfo{
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "idxBar", TableName: "test", Path: parsePath(t, "bar"),
 			})
 			require.NoError(t, err)
@@ -456,13 +486,13 @@ func TestCatalogReIndex(t *testing.T) {
 				require.NoError(t, err)
 			}
 
-			err = catalog.CreateIndex(tx, database.IndexInfo{
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "a",
 				TableName: "test",
 				Path:      parsePath(t, "a"),
 			})
 			require.NoError(t, err)
-			err = catalog.CreateIndex(tx, database.IndexInfo{
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "b",
 				TableName: "test",
 				Path:      parsePath(t, "b"),
@@ -504,7 +534,7 @@ func TestCatalogReIndex(t *testing.T) {
 			)
 			require.NoError(t, err)
 
-			return catalog.CreateIndex(tx, database.IndexInfo{
+			return catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "b",
 				TableName: "test",
 				Path:      parsePath(t, "b"),
@@ -605,13 +635,13 @@ func TestReIndexAll(t *testing.T) {
 				require.NoError(t, err)
 			}
 
-			err = catalog.CreateIndex(tx, database.IndexInfo{
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "t1a",
 				TableName: "test1",
 				Path:      parsePath(t, "a"),
 			})
 			require.NoError(t, err)
-			err = catalog.CreateIndex(tx, database.IndexInfo{
+			err = catalog.CreateIndex(tx, &database.IndexInfo{
 				IndexName: "t2a",
 				TableName: "test2",
 				Path:      parsePath(t, "a"),
diff --git a/database/config_test.go b/database/config_test.go
index fc86cb17..c5316493 100644
--- a/database/config_test.go
+++ b/database/config_test.go
@@ -166,11 +166,11 @@ func TestIndexStore(t *testing.T) {
 			Type:      document.BoolValue,
 		}
 
-		err = idxs.Insert(cfg)
+		err = idxs.Insert(&cfg)
 		require.NoError(t, err)
 
 		// Inserting the same index should fail.
-		err = idxs.Insert(cfg)
+		err = idxs.Insert(&cfg)
 		require.EqualError(t, err, ErrIndexAlreadyExists.Error())
 
 		idxcfg, err := idxs.Get("idx_test")
@@ -200,7 +200,7 @@ func TestIndexStore(t *testing.T) {
 			{TableName: "test3", IndexName: "idx_test3", Unique: true},
 		}
 		for _, v := range idxcfgs {
-			err = idxs.Insert(*v)
+			err = idxs.Insert(v)
 			require.NoError(t, err)
 		}
 
diff --git a/database/table_test.go b/database/table_test.go
index b284cb98..ff0e967e 100644
--- a/database/table_test.go
+++ b/database/table_test.go
@@ -294,7 +294,7 @@ func TestTableInsert(t *testing.T) {
 		err := tx.CreateTable("test", nil)
 		require.NoError(t, err)
 
-		err = tx.CreateIndex(database.IndexInfo{
+		err = tx.CreateIndex(&database.IndexInfo{
 			IndexName: "idxFoo", TableName: "test", Path: parsePath(t, "foo"),
 		})
 		require.NoError(t, err)
@@ -642,7 +642,7 @@ func TestTableReplace(t *testing.T) {
 		err := tx.CreateTable("test", nil)
 		require.NoError(t, err)
 
-		err = tx.CreateIndex(database.IndexInfo{
+		err = tx.CreateIndex(&database.IndexInfo{
 			Path:      document.NewPath("a"),
 			Unique:    true,
 			TableName: "test",
@@ -735,21 +735,21 @@ func TestTableIndexes(t *testing.T) {
 		err = tx.CreateTable("test2", nil)
 		require.NoError(t, err)
 
-		err = tx.CreateIndex(database.IndexInfo{
+		err = tx.CreateIndex(&database.IndexInfo{
 			Unique:    true,
 			IndexName: "idx1a",
 			TableName: "test1",
 			Path:      parsePath(t, "a"),
 		})
 		require.NoError(t, err)
-		err = tx.CreateIndex(database.IndexInfo{
+		err = tx.CreateIndex(&database.IndexInfo{
 			Unique:    false,
 			IndexName: "idx1b",
 			TableName: "test1",
 			Path:      parsePath(t, "b"),
 		})
 		require.NoError(t, err)
-		err = tx.CreateIndex(database.IndexInfo{
+		err = tx.CreateIndex(&database.IndexInfo{
 			Unique:    false,
 			IndexName: "ifx2a",
 			TableName: "test2",
diff --git a/query/create_test.go b/query/create_test.go
index 8ab2b170..d1cd56df 100644
--- a/query/create_test.go
+++ b/query/create_test.go
@@ -266,6 +266,8 @@ func TestCreateIndex(t *testing.T) {
 		{"Basic", "CREATE INDEX idx ON test (foo)", false},
 		{"If not exists", "CREATE INDEX IF NOT EXISTS idx ON test (foo.bar)", false},
 		{"Unique", "CREATE UNIQUE INDEX IF NOT EXISTS idx ON test (foo[1])", false},
+		{"No name", "CREATE UNIQUE INDEX ON test (foo[1])", false},
+		{"No name if not exists", "CREATE UNIQUE INDEX IF NOT EXISTS ON test (foo[1])", true},
 		{"No fields", "CREATE INDEX idx ON test", true},
 		{"More than 1 field", "CREATE INDEX idx ON test (foo, bar)", true},
 	}
diff --git a/sql/parser/create_test.go b/sql/parser/create_test.go
index d2ed0cc5..bfc43285 100644
--- a/sql/parser/create_test.go
+++ b/sql/parser/create_test.go
@@ -226,6 +226,8 @@ func TestParserCreateIndex(t *testing.T) {
 		{"Basic", "CREATE INDEX idx ON test (foo)", query.CreateIndexStmt{IndexName: "idx", TableName: "test", Path: document.Path(parsePath(t, "foo"))}, false},
 		{"If not exists", "CREATE INDEX IF NOT EXISTS idx ON test (foo.bar[1])", query.CreateIndexStmt{IndexName: "idx", TableName: "test", Path: document.Path(parsePath(t, "foo.bar[1]")), IfNotExists: true}, false},
 		{"Unique", "CREATE UNIQUE INDEX IF NOT EXISTS idx ON test (foo[3].baz)", query.CreateIndexStmt{IndexName: "idx", TableName: "test", Path: document.Path(parsePath(t, "foo[3].baz")), IfNotExists: true, Unique: true}, false},
+		{"No name", "CREATE UNIQUE INDEX ON test (foo[3].baz)", query.CreateIndexStmt{TableName: "test", Path: document.Path(parsePath(t, "foo[3].baz")), Unique: true}, false},
+		{"No name with IF NOT EXISTS", "CREATE UNIQUE INDEX IF NOT EXISTS ON test (foo[3].baz)", nil, true},
 		{"No fields", "CREATE INDEX idx ON test", nil, true},
 		{"More than 1 path", "CREATE INDEX idx ON test (foo, bar)", nil, true},
 	}