Repository: genjidb/genji
Pull request #387 (2021-04-13 06:53:08)

Add support for UNIQUE constraint
This adds support for the `UNIQUE` field constraint and table constraint

```sql
CREATE TABLE foo (a INT UNIQUE);
CREATE TABLE foo (a INT, UNIQUE (b));
```


Patch:
diff --git a/database/constraint.go b/database/constraint.go
index 2ef05884..4f651d13 100644
--- a/database/constraint.go
+++ b/database/constraint.go
@@ -13,6 +13,7 @@ type FieldConstraint struct {
 	Type         document.ValueType
 	IsPrimaryKey bool
 	IsNotNull    bool
+	IsUnique     bool // not stored, only set during table creation
 	DefaultValue document.Value
 	IsInferred   bool
 	InferredBy   []document.Path
diff --git a/query/create.go b/query/create.go
index 445e5ed2..8d674dc4 100644
--- a/query/create.go
+++ b/query/create.go
@@ -28,6 +28,20 @@ func (stmt CreateTableStmt) Run(tx *database.Transaction, args []expr.Param) (Re
 		err = nil
 	}
 
+	for _, fc := range stmt.Info.FieldConstraints {
+		if fc.IsUnique {
+			err = tx.CreateIndex(&database.IndexInfo{
+				TableName: stmt.TableName,
+				Path:      fc.Path,
+				Unique:    true,
+				Type:      fc.Type,
+			})
+			if err != nil {
+				return res, err
+			}
+		}
+	}
+
 	return res, err
 }
 
diff --git a/sql/parser/create.go b/sql/parser/create.go
index 1d89f7e1..a22de8b5 100644
--- a/sql/parser/create.go
+++ b/sql/parser/create.go
@@ -2,7 +2,6 @@ package parser
 
 import (
 	"github.com/genjidb/genji/database"
-	"github.com/genjidb/genji/document"
 	"github.com/genjidb/genji/expr"
 	"github.com/genjidb/genji/query"
 	"github.com/genjidb/genji/sql/scanner"
@@ -72,9 +71,9 @@ func (p *Parser) parseFieldDefinition(fc *database.FieldConstraint) (err error)
 		return err
 	}
 
-	if fc.Type == 0 && fc.DefaultValue.Type.IsZero() && !fc.IsNotNull && !fc.IsPrimaryKey {
+	if fc.Type == 0 && fc.DefaultValue.Type.IsZero() && !fc.IsNotNull && !fc.IsPrimaryKey && !fc.IsUnique {
 		tok, pos, lit := p.ScanIgnoreWhitespace()
-		return newParseError(scanner.Tokstr(tok, lit), []string{"TYPE", "CONSTRAINT"}, pos)
+		return newParseError(scanner.Tokstr(tok, lit), []string{"CONSTRAINT", "TYPE"}, pos)
 	}
 
 	return nil
@@ -95,36 +94,20 @@ func (p *Parser) parseConstraints(stmt *query.CreateTableStmt) error {
 	for {
 		// we start by checking if it is a table constraint,
 		// as it's easier to determine
-		tc, err := p.parseTableConstraint()
+		ok, err := p.parseTableConstraint(stmt)
 		if err != nil {
 			return err
 		}
 
 		// no table constraint found
-		if tc == nil && parsingTableConstraints {
+		if !ok && parsingTableConstraints {
 			tok, pos, lit := p.ScanIgnoreWhitespace()
 			return newParseError(scanner.Tokstr(tok, lit), []string{"CONSTRAINT", ")"}, pos)
 		}
 
 		// only PRIMARY KEY(path) is currently supported.
-		if tc != nil {
+		if ok {
 			parsingTableConstraints = true
-
-			if pk := stmt.Info.GetPrimaryKey(); pk != nil {
-				return stringutil.Errorf("table %q has more than one primary key", stmt.TableName)
-			}
-			fc := stmt.Info.FieldConstraints.Get(tc.primaryKey)
-			if fc == nil {
-				err = stmt.Info.FieldConstraints.Add(&database.FieldConstraint{
-					Path:         tc.primaryKey,
-					IsPrimaryKey: true,
-				})
-				if err != nil {
-					return err
-				}
-			} else {
-				fc.IsPrimaryKey = true
-			}
 		}
 
 		// if set to false, we are still parsing field definitions
@@ -211,6 +194,13 @@ func (p *Parser) parseFieldConstraint(fc *database.FieldConstraint) error {
 			}
 
 			fc.DefaultValue = d
+		case scanner.UNIQUE:
+			// if it's already unique we return an error
+			if fc.IsUnique {
+				return newParseError(scanner.Tokstr(tok, lit), []string{"CONSTRAINT", ")"}, pos)
+			}
+
+			fc.IsUnique = true
 		default:
 			p.Unscan()
 			return nil
@@ -218,37 +208,81 @@ func (p *Parser) parseFieldConstraint(fc *database.FieldConstraint) error {
 	}
 }
 
-func (p *Parser) parseTableConstraint() (*tableConstraint, error) {
-	var tc tableConstraint
+func (p *Parser) parseTableConstraint(stmt *query.CreateTableStmt) (bool, error) {
 	var err error
 
 	tok, _, _ := p.ScanIgnoreWhitespace()
 	switch tok {
 	case scanner.PRIMARY:
-		// Parse "KEY"
-		if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != scanner.KEY {
-			return nil, newParseError(scanner.Tokstr(tok, lit), []string{"KEY"}, pos)
+		// Parse "KEY ("
+		err = p.parseTokens(scanner.KEY, scanner.LPAREN)
+		if err != nil {
+			return false, err
+		}
+
+		primaryKeyPath, err := p.parsePath()
+		if err != nil {
+			return false, err
+		}
+
+		// Parse ")"
+		err = p.parseTokens(scanner.RPAREN)
+		if err != nil {
+			return false, err
 		}
 
+		if pk := stmt.Info.GetPrimaryKey(); pk != nil {
+			return false, stringutil.Errorf("table %q has more than one primary key", stmt.TableName)
+		}
+		fc := stmt.Info.FieldConstraints.Get(primaryKeyPath)
+		if fc == nil {
+			err = stmt.Info.FieldConstraints.Add(&database.FieldConstraint{
+				Path:         primaryKeyPath,
+				IsPrimaryKey: true,
+			})
+			if err != nil {
+				return false, err
+			}
+		} else {
+			fc.IsPrimaryKey = true
+		}
+
+		return true, nil
+	case scanner.UNIQUE:
 		// Parse "("
-		if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != scanner.LPAREN {
-			return nil, newParseError(scanner.Tokstr(tok, lit), []string{"("}, pos)
+		err = p.parseTokens(scanner.LPAREN)
+		if err != nil {
+			return false, err
 		}
 
-		tc.primaryKey, err = p.parsePath()
+		uniquePath, err := p.parsePath()
 		if err != nil {
-			return nil, err
+			return false, err
 		}
 
 		// Parse ")"
-		if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != scanner.RPAREN {
-			return nil, newParseError(scanner.Tokstr(tok, lit), []string{")"}, pos)
+		err = p.parseTokens(scanner.RPAREN)
+		if err != nil {
+			return false, err
 		}
 
-		return &tc, nil
+		fc := stmt.Info.FieldConstraints.Get(uniquePath)
+		if fc == nil {
+			err = stmt.Info.FieldConstraints.Add(&database.FieldConstraint{
+				Path:     uniquePath,
+				IsUnique: true,
+			})
+			if err != nil {
+				return false, err
+			}
+		} else {
+			fc.IsUnique = true
+		}
+
+		return true, nil
 	default:
 		p.Unscan()
-		return nil, nil
+		return false, nil
 	}
 }
 
@@ -305,7 +339,3 @@ func (p *Parser) parseCreateIndexStatement(unique bool) (query.CreateIndexStmt,
 
 	return stmt, nil
 }
-
-type tableConstraint struct {
-	primaryKey document.Path
-}
diff --git a/sql/parser/parser.go b/sql/parser/parser.go
index 062e6938..5c555a28 100644
--- a/sql/parser/parser.go
+++ b/sql/parser/parser.go
@@ -210,9 +210,9 @@ func (p *Parser) Unscan() {
 	p.s.Unscan()
 }
 
-// parseConsecutiveTokens parses all the given tokens one after the other.
+// parseTokens parses all the given tokens one after the other.
 // It returns an error if one of the token is missing.
-func (p *Parser) parseConsecutiveTokens(tokens ...scanner.Token) error {
+func (p *Parser) parseTokens(tokens ...scanner.Token) error {
 	for _, t := range tokens {
 		if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != t {
 			return newParseError(scanner.Tokstr(tok, lit), []string{t.String()}, pos)
@@ -236,7 +236,7 @@ func (p *Parser) parseOptional(tokens ...scanner.Token) (bool, error) {
 		return true, nil
 	}
 
-	err := p.parseConsecutiveTokens(tokens[1:]...)
+	err := p.parseTokens(tokens[1:]...)
 	return err == nil, err
 }
 


Test patch:
diff --git a/database/table_test.go b/database/table_test.go
index ff0e967e..a0606e93 100644
--- a/database/table_test.go
+++ b/database/table_test.go
@@ -340,8 +340,8 @@ func TestTableInsert(t *testing.T) {
 
 		err := tx.CreateTable("test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{parsePath(t, "foo"), document.DocumentValue, false, false, document.Value{}, true, []document.Path{parsePath(t, "foo.bar")}},
-				{parsePath(t, "foo.bar"), document.IntegerValue, false, false, document.Value{}, true, []document.Path{parsePath(t, "foo")}},
+				{parsePath(t, "foo"), document.DocumentValue, false, false, false, document.Value{}, true, []document.Path{parsePath(t, "foo.bar")}},
+				{parsePath(t, "foo.bar"), document.IntegerValue, false, false, false, document.Value{}, true, []document.Path{parsePath(t, "foo")}},
 			},
 		})
 		require.NoError(t, err)
@@ -385,7 +385,7 @@ func TestTableInsert(t *testing.T) {
 
 		err := tx.CreateTable("test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{parsePath(t, "foo"), document.DoubleValue, false, false, document.Value{}, false, nil},
+				{parsePath(t, "foo"), document.DoubleValue, false, false, false, document.Value{}, false, nil},
 			},
 		})
 		require.NoError(t, err)
@@ -407,7 +407,7 @@ func TestTableInsert(t *testing.T) {
 		// no enforced type, not null
 		err := tx.CreateTable("test1", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{parsePath(t, "foo"), 0, false, true, document.Value{}, false, nil},
+				{parsePath(t, "foo"), 0, false, true, false, document.Value{}, false, nil},
 			},
 		})
 		require.NoError(t, err)
@@ -417,7 +417,7 @@ func TestTableInsert(t *testing.T) {
 		// enforced type, not null
 		err = tx.CreateTable("test2", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{parsePath(t, "foo"), document.IntegerValue, false, true, document.Value{}, false, nil},
+				{parsePath(t, "foo"), document.IntegerValue, false, true, false, document.Value{}, false, nil},
 			},
 		})
 		require.NoError(t, err)
@@ -462,7 +462,7 @@ func TestTableInsert(t *testing.T) {
 		// no enforced type, not null
 		err := tx.CreateTable("test1", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{parsePath(t, "foo"), 0, false, true, document.NewIntegerValue(42), false, nil},
+				{parsePath(t, "foo"), 0, false, true, false, document.NewIntegerValue(42), false, nil},
 			},
 		})
 		require.NoError(t, err)
@@ -472,7 +472,7 @@ func TestTableInsert(t *testing.T) {
 		// enforced type, not null
 		err = tx.CreateTable("test2", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{parsePath(t, "foo"), document.IntegerValue, false, true, document.NewIntegerValue(42), false, nil},
+				{parsePath(t, "foo"), document.IntegerValue, false, true, false, document.NewIntegerValue(42), false, nil},
 			},
 		})
 		require.NoError(t, err)
@@ -528,7 +528,7 @@ func TestTableInsert(t *testing.T) {
 
 		err := tx.CreateTable("test1", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{parsePath(t, "foo[1]"), 0, false, true, document.Value{}, false, nil},
+				{parsePath(t, "foo[1]"), 0, false, true, false, document.Value{}, false, nil},
 			},
 		})
 		require.NoError(t, err)
diff --git a/query/create_test.go b/query/create_test.go
index d1cd56df..0e49cc69 100644
--- a/query/create_test.go
+++ b/query/create_test.go
@@ -254,6 +254,56 @@ func TestCreateTable(t *testing.T) {
 				})
 			}
 		})
+
+		t.Run("unique", func(t *testing.T) {
+			db, err := genji.Open(":memory:")
+			require.NoError(t, err)
+			defer db.Close()
+
+			err = db.Exec("CREATE TABLE test (a INT UNIQUE, b DOUBLE UNIQUE, c UNIQUE)")
+			require.NoError(t, err)
+
+			err = db.View(func(tx *genji.Tx) error {
+				tb, err := tx.GetTable("test")
+				require.NoError(t, err)
+				info := tb.Info()
+				require.Len(t, info.FieldConstraints, 3)
+
+				require.Equal(t, &database.FieldConstraint{
+					Path:     parsePath(t, "a"),
+					Type:     document.IntegerValue,
+					IsUnique: true,
+				}, info.FieldConstraints[0])
+
+				require.Equal(t, &database.FieldConstraint{
+					Path:     parsePath(t, "b"),
+					Type:     document.DoubleValue,
+					IsUnique: true,
+				}, info.FieldConstraints[1])
+
+				require.Equal(t, &database.FieldConstraint{
+					Path:     parsePath(t, "c"),
+					IsUnique: true,
+				}, info.FieldConstraints[2])
+
+				idx, err := tx.GetIndex("__genji_autoindex_test_1")
+				require.NoError(t, err)
+				require.Equal(t, document.IntegerValue, idx.Info.Type)
+				require.True(t, idx.Info.Unique)
+
+				idx, err = tx.GetIndex("__genji_autoindex_test_2")
+				require.NoError(t, err)
+				require.Equal(t, document.DoubleValue, idx.Info.Type)
+				require.True(t, idx.Info.Unique)
+
+				idx, err = tx.GetIndex("__genji_autoindex_test_3")
+				require.NoError(t, err)
+				require.Zero(t, idx.Info.Type)
+				require.True(t, idx.Info.Unique)
+				return nil
+			})
+			require.NoError(t, err)
+		})
 	})
 }
 
diff --git a/sql/parser/create_test.go b/sql/parser/create_test.go
index bfc43285..1114f893 100644
--- a/sql/parser/create_test.go
+++ b/sql/parser/create_test.go
@@ -57,10 +57,21 @@ func TestParserCreateTable(t *testing.T) {
 					},
 				},
 			}, false},
+		{"With unique", "CREATE TABLE test(foo UNIQUE)",
+			query.CreateTableStmt{
+				TableName: "test",
+				Info: database.TableInfo{
+					FieldConstraints: []*database.FieldConstraint{
+						{Path: document.Path(parsePath(t, "foo")), IsUnique: true},
+					},
+				},
+			}, false},
 		{"With default twice", "CREATE TABLE test(foo DEFAULT 10 DEFAULT 10)",
 			query.CreateTableStmt{}, true},
 		{"With not null twice", "CREATE TABLE test(foo NOT NULL NOT NULL)",
 			query.CreateTableStmt{}, true},
+		{"With unique twice", "CREATE TABLE test(foo UNIQUE UNIQUE)",
+			query.CreateTableStmt{}, true},
 		{"With type and not null", "CREATE TABLE test(foo INTEGER NOT NULL)",
 			query.CreateTableStmt{
 				TableName: "test",
@@ -122,6 +133,36 @@ func TestParserCreateTable(t *testing.T) {
 		{"With table constraints / field constraint after table constraint", "CREATE TABLE test(PRIMARY KEY (bar), foo INTEGER)", nil, true},
 		{"With table constraints / duplicate pk", "CREATE TABLE test(foo INTEGER PRIMARY KEY, PRIMARY KEY (bar))", nil, true},
 		{"With table constraints / duplicate pk on same path", "CREATE TABLE test(foo INTEGER PRIMARY KEY, PRIMARY KEY (foo))", nil, true},
+		{"With table constraints / UNIQUE on defined field", "CREATE TABLE test(foo INTEGER, bar NOT NULL, UNIQUE (foo))",
+			query.CreateTableStmt{
+				TableName: "test",
+				Info: database.TableInfo{
+					FieldConstraints: []*database.FieldConstraint{
+						{Path: document.Path(parsePath(t, "foo")), Type: document.IntegerValue, IsUnique: true},
+						{Path: document.Path(parsePath(t, "bar")), IsNotNull: true},
+					},
+				},
+			}, false},
+		{"With table constraints / UNIQUE on undefined field", "CREATE TABLE test(foo INTEGER, UNIQUE (bar))",
+			query.CreateTableStmt{
+				TableName: "test",
+				Info: database.TableInfo{
+					FieldConstraints: []*database.FieldConstraint{
+						{Path: document.Path(parsePath(t, "foo")), Type: document.IntegerValue},
+						{Path: document.Path(parsePath(t, "bar")), IsUnique: true},
+					},
+				},
+			}, false},
+		{"With table constraints / UNIQUE twice", "CREATE TABLE test(foo INTEGER UNIQUE, UNIQUE (foo))",
+			query.CreateTableStmt{
+				TableName: "test",
+				Info: database.TableInfo{
+					FieldConstraints: []*database.FieldConstraint{
+						{Path: document.Path(parsePath(t, "foo")), Type: document.IntegerValue, IsUnique: true},
+					},
+				},
+			}, false},
+		{"With table constraints / duplicate pk on same path", "CREATE TABLE test(foo INTEGER PRIMARY KEY, PRIMARY KEY (foo))", nil, true},
 		{"With multiple primary keys", "CREATE TABLE test(foo PRIMARY KEY, bar PRIMARY KEY)",
 			query.CreateTableStmt{}, true},
 		{"With all supported fixed size data types",