Repository: genjidb/genji (go)

Issue:
Cannot create table with array type
I would like to create a table with an array type but I have an error:
```sql
genji> CREATE TABLE foo (name text primary key, animals array);
found array, expected ) at line 1, char 50
genji>
```

Is it by design or not?

Pull request:
Add support of missing types for CREATE statement
This adds the support of missing types for the `CREATE` statement.

I added tests that assert the entire set of types.

It fixes #85.

Patch:
diff --git a/document/value.go b/document/value.go
index 0d965222..c2d66ce2 100644
--- a/document/value.go
+++ b/document/value.go
@@ -10,15 +10,17 @@ import (
 )
 
 var (
+	arrayZeroValue    = NewZeroValue(ArrayValue)
 	blobZeroValue     = NewZeroValue(BlobValue)
-	textZeroValue     = NewZeroValue(TextValue)
 	boolZeroValue     = NewZeroValue(BoolValue)
+	documentZeroValue = NewZeroValue(DocumentValue)
+	durationZeroValue = NewZeroValue(DurationValue)
+	float64ZeroValue  = NewZeroValue(Float64Value)
 	int8ZeroValue     = NewZeroValue(Int8Value)
 	int16ZeroValue    = NewZeroValue(Int16Value)
 	int32ZeroValue    = NewZeroValue(Int32Value)
 	int64ZeroValue    = NewZeroValue(Int64Value)
-	float64ZeroValue  = NewZeroValue(Float64Value)
-	durationZeroValue = NewZeroValue(DurationValue)
+	textZeroValue     = NewZeroValue(TextValue)
 )
 
 // ErrUnsupportedType is used to skip struct or array fields that are not supported.
diff --git a/sql/parser/expr.go b/sql/parser/expr.go
index f6384452..2b6c4fc5 100644
--- a/sql/parser/expr.go
+++ b/sql/parser/expr.go
@@ -276,12 +276,20 @@ func (p *Parser) parseParam() (expr.Expr, error) {
 func (p *Parser) parseType() document.ValueType {
 	tok, _, _ := p.ScanIgnoreWhitespace()
 	switch tok {
-	case scanner.TYPEBYTES:
+	case scanner.TYPEARRAY:
+		return document.ArrayValue
+	case scanner.TYPEBLOB:
 		return document.BlobValue
-	case scanner.TYPESTRING:
-		return document.TextValue
 	case scanner.TYPEBOOL:
 		return document.BoolValue
+	case scanner.TYPEBYTES:
+		return document.BlobValue
+	case scanner.TYPEDOCUMENT:
+		return document.DocumentValue
+	case scanner.TYPEDURATION:
+		return document.DurationValue
+	case scanner.TYPEFLOAT64, scanner.TYPENUMERIC:
+		return document.Float64Value
 	case scanner.TYPEINT8:
 		return document.Int8Value
 	case scanner.TYPEINT16:
@@ -290,12 +298,10 @@ func (p *Parser) parseType() document.ValueType {
 		return document.Int32Value
 	case scanner.TYPEINT64, scanner.TYPEINT, scanner.TYPEINTEGER:
 		return document.Int64Value
-	case scanner.TYPEFLOAT64, scanner.TYPENUMERIC:
-		return document.Float64Value
+	case scanner.TYPESTRING:
+		return document.TextValue
 	case scanner.TYPETEXT:
 		return document.TextValue
-	case scanner.TYPEDURATION:
-		return document.DurationValue
 	}
 
 	p.Unscan()
diff --git a/sql/scanner/token.go b/sql/scanner/token.go
index 0d79c258..6140fa3a 100644
--- a/sql/scanner/token.go
+++ b/sql/scanner/token.go
@@ -104,16 +104,20 @@ const (
 	VALUES
 	WHERE
 
-	TYPEBYTES
-	TYPESTRING
+	TYPEARRAY
+	TYPEBLOB
 	TYPEBOOL
+	TYPEBYTES
+	TYPEDOCUMENT
+	TYPEDURATION
+	TYPEFLOAT64
+	TYPEINT
 	TYPEINT8
 	TYPEINT16
 	TYPEINT32
 	TYPEINT64
-	TYPEINT
-	TYPEFLOAT64
-	TYPEDURATION
+	TYPESTRING
+
 	TYPEINTEGER // alias to TYPEINT
 	TYPENUMERIC // alias to TYPEFLOAT64
 	TYPETEXT    // alias to TYPESTRING
@@ -203,19 +207,23 @@ var tokens = [...]string{
 	VALUES:  "VALUES",
 	WHERE:   "WHERE",
 
-	TYPEBYTES:    "BYTES",
-	TYPESTRING:   "STRING",
+	TYPEARRAY:    "ARRAY",
+	TYPEBLOB:     "BLOB",
 	TYPEBOOL:     "BOOL",
+	TYPEBYTES:    "BYTES",
+	TYPEDOCUMENT: "DOCUMENT",
+	TYPEDURATION: "DURATION",
+	TYPEFLOAT64:  "FLOAT64",
+	TYPEINT:      "INT",
 	TYPEINT8:     "INT8",
 	TYPEINT16:    "INT16",
 	TYPEINT32:    "INT32",
 	TYPEINT64:    "INT64",
-	TYPEINT:      "INT",
-	TYPEDURATION: "DURATION",
-	TYPEFLOAT64:  "FLOAT64",
-	TYPEINTEGER:  "INTEGER",
-	TYPENUMERIC:  "NUMERIC",
-	TYPETEXT:     "TEXT",
+	TYPESTRING:   "STRING",
+
+	TYPEINTEGER: "INTEGER",
+	TYPENUMERIC: "NUMERIC",
+	TYPETEXT:    "TEXT",
 }
 
 var keywords map[string]Token


Test patch:
diff --git a/sql/parser/create_test.go b/sql/parser/create_test.go
index 3aa3f5a4..03a54975 100644
--- a/sql/parser/create_test.go
+++ b/sql/parser/create_test.go
@@ -89,6 +89,40 @@ func TestParserCreateTable(t *testing.T) {
 			}, false},
 		{"With multiple primary keys", "CREATE TABLE test(foo PRIMARY KEY, bar PRIMARY KEY)",
 			query.CreateTableStmt{}, true},
+		{"With all supported fixed size data types",
+			"CREATE TABLE test(i8 int8, i16 int16, i32 int32, i64 int64, f64 float64, b bool)",
+			query.CreateTableStmt{
+				TableName: "test",
+				Config: database.TableConfig{
+					FieldConstraints: []database.FieldConstraint{
+						{Path: []string{"i8"}, Type: document.Int8Value},
+						{Path: []string{"i16"}, Type: document.Int16Value},
+						{Path: []string{"i32"}, Type: document.Int32Value},
+						{Path: []string{"i64"}, Type: document.Int64Value},
+						{Path: []string{"f64"}, Type: document.Float64Value},
+						{Path: []string{"b"}, Type: document.BoolValue},
+					},
+				},
+			}, false},
+		{"With all supported variable size data types",
+			"CREATE TABLE test(i int, ig integer, n numeric, du duration, b blob, byt bytes, t text, s string, a array, d document)",
+			query.CreateTableStmt{
+				TableName: "test",
+				Config: database.TableConfig{
+					FieldConstraints: []database.FieldConstraint{
+						{Path: []string{"i"}, Type: document.Int64Value},
+						{Path: []string{"ig"}, Type: document.Int64Value},
+						{Path: []string{"n"}, Type: document.Float64Value},
+						{Path: []string{"du"}, Type: document.DurationValue},
+						{Path: []string{"b"}, Type: document.BlobValue},
+						{Path: []string{"byt"}, Type: document.BlobValue},
+						{Path: []string{"t"}, Type: document.TextValue},
+						{Path: []string{"s"}, Type: document.TextValue},
+						{Path: []string{"a"}, Type: document.ArrayValue},
+						{Path: []string{"d"}, Type: document.DocumentValue},
+					},
+				},
+			}, false},
 	}
 
 	for _, test := range tests {
diff --git a/sql/query/create_test.go b/sql/query/create_test.go
index 331da27c..79153a38 100644
--- a/sql/query/create_test.go
+++ b/sql/query/create_test.go
@@ -49,25 +49,66 @@ func TestCreateTable(t *testing.T) {
 		require.NoError(t, err)
 		defer db.Close()
 
-		err = db.Exec("CREATE TABLE test(foo.bar.1.hello bytes PRIMARY KEY, foo.a.1.2 STRING, bar.4.0.bat int8)")
-		require.NoError(t, err)
+		t.Run("with fixed size data types", func(t *testing.T) {
+			err = db.Exec(`CREATE TABLE test(i8 int8, i16 int16, i32 int32, i64 int64, f64 float64, b bool)`)
+			require.NoError(t, err)
 
-		err = db.ViewTable("test", func(_ *genji.Tx, tb *database.Table) error {
-			cfg, err := tb.Config()
-			if err != nil {
-				return err
-			}
+			err = db.ViewTable("test", func(_ *genji.Tx, tb *database.Table) error {
+				cfg, err := tb.Config()
+				if err != nil {
+					return err
+				}
+
+				require.Equal(t, &database.TableConfig{
+					FieldConstraints: []database.FieldConstraint{
+						{Path: []string{"i8"}, Type: document.Int8Value},
+						{Path: []string{"i16"}, Type: document.Int16Value},
+						{Path: []string{"i32"}, Type: document.Int32Value},
+						{Path: []string{"i64"}, Type: document.Int64Value},
+						{Path: []string{"f64"}, Type: document.Float64Value},
+						{Path: []string{"b"}, Type: document.BoolValue},
+					},
+				}, cfg)
+				return nil
+			})
+			require.NoError(t, err)
+
+		})
+
+		t.Run("with variable size data types", func(t *testing.T) {
+			err = db.Exec(`
+				CREATE TABLE test1(
+					foo.bar.1.hello bytes PRIMARY KEY, foo.a.1.2 STRING NOT NULL, bar.4.0.bat int,
+					ig integer, n numeric, du duration, b blob, t text, a array, d document
+				)
+			`)
+			require.NoError(t, err)
+
+			err = db.ViewTable("test1", func(_ *genji.Tx, tb *database.Table) error {
+				cfg, err := tb.Config()
+				if err != nil {
+					return err
+				}
+
+				require.Equal(t, &database.TableConfig{
+					FieldConstraints: []database.FieldConstraint{
+						{Path: []string{"foo", "bar", "1", "hello"}, Type: document.BlobValue, IsPrimaryKey: true},
+						{Path: []string{"foo", "a", "1", "2"}, Type: document.TextValue, IsNotNull: true},
+						{Path: []string{"bar", "4", "0", "bat"}, Type: document.Int64Value},
+						{Path: []string{"ig"}, Type: document.Int64Value},
+						{Path: []string{"n"}, Type: document.Float64Value},
+						{Path: []string{"du"}, Type: document.DurationValue},
+						{Path: []string{"b"}, Type: document.BlobValue},
+						{Path: []string{"t"}, Type: document.TextValue},
+						{Path: []string{"a"}, Type: document.ArrayValue},
+						{Path: []string{"d"}, Type: document.DocumentValue},
+					},
+				}, cfg)
+				return nil
+			})
+			require.NoError(t, err)
 
-			require.Equal(t, &database.TableConfig{
-				FieldConstraints: []database.FieldConstraint{
-					{Path: []string{"foo", "bar", "1", "hello"}, Type: document.BlobValue, IsPrimaryKey: true},
-					{Path: []string{"foo", "a", "1", "2"}, Type: document.TextValue},
-					{Path: []string{"bar", "4", "0", "bat"}, Type: document.Int8Value},
-				},
-			}, cfg)
-			return nil
 		})
-		require.NoError(t, err)
 	})
 }
 
diff --git a/sql/query/insert_test.go b/sql/query/insert_test.go
index 297febdd..4ede9ecf 100644
--- a/sql/query/insert_test.go
+++ b/sql/query/insert_test.go
@@ -3,6 +3,7 @@ package query_test
 import (
 	"bytes"
 	"database/sql"
+	"fmt"
 	"testing"
 
 	"github.com/genjidb/genji"
@@ -128,4 +129,142 @@ func TestInsertStmt(t *testing.T) {
 		require.NoError(t, err)
 		require.JSONEq(t, `{"a": "a", "b-b": "b"}`, buf.String())
 	})
+
+	t.Run("with types constraints", func(t *testing.T) {
+		// This test ensures that we can insert data into every supported types.
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		err = db.Exec(`CREATE TABLE test(
+			i8 int8, i16 int16, i32 int32, i64 int64, f64 float64, b bool,
+			i int, ig integer, n numeric, du duration, bb blob, byt bytes,
+			t text, s string, a array, d document
+		)`)
+		require.NoError(t, err)
+
+		err = db.Exec(`
+			INSERT INTO test
+			VALUES {
+				i8: 100, i16: 1000, i32: 10000,	i64: 10000000000, f64: 21.21, b: true,
+				i: 1000, ig: 1000, n: 21.21, du: 127ns, bb: "blobValue", byt: "bytesValue",
+				t: "text", s: "string", a: [1, "foo", true], d: {"foo": "bar"}
+			}`)
+		require.NoError(t, err)
+
+		res, err := db.Query("SELECT * FROM test")
+		defer res.Close()
+		require.NoError(t, err)
+
+		var buf bytes.Buffer
+		err = document.IteratorToJSON(&buf, res)
+		require.NoError(t, err)
+		require.JSONEq(t, `{
+			"i8": 100,
+			"i16": 1000,
+			"i32": 10000,
+			"i64": 10000000000,
+			"f64": 21.21,
+			"b": true,
+			"i": 1000,
+			"ig": 1000,
+			"n": 21.21,
+			"du": 127,
+			"bb": "blobValue",
+			"byt": "bytesValue",
+			"t": "text",
+			"s": "string",
+			"a": [1, "foo", true],
+			"d": {"foo": "bar"}
+		  }`, buf.String())
+	})
+
+	t.Run("with tests that require an error", func(t *testing.T) {
+		tests := []struct {
+			name            string
+			fieldConstraint string
+			value           string
+			expectedErr     string
+		}{
+			{"not null without type constraint", "NOT NULL", `{}`, `field "a" is required and must be not null`},
+
+			{"array", "ARRAY", `{a: "[1,2,3]"}`, `cannot convert "text" to "array"`},
+			{"array / not null with type constraint", "ARRAY NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"array / not null with non-respected type constraint ", "ARRAY NOT NULL", `{a: 42}`, `cannot convert "int8" to "array"`},
+
+			{"blob", "BLOB", `{a: true}`, `cannot convert "bool" to "bytes"`},
+			{"blob / not null with type constraint", "BLOB NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"blob / not null with non-respected type constraint ", "BLOB NOT NULL", `{a: 42}`, `cannot convert "int8" to "bytes"`},
+
+			{"bool / not null with type constraint", "BOOL NOT NULL", `{}`, `field "a" is required and must be not null`},
+
+			{"bytes", "BYTES", `{a: [1,2,3]}`, `cannot convert "array" to "bytes"`},
+			{"bytes / not null with type constraint", "BYTES NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"bytes / not null with non-respected type constraint ", "BYTES NOT NULL", `{a: 42}`, `cannot convert "int8" to "bytes"`},
+
+			{"document", "DOCUMENT", `{a: "foo"}`, `cannot convert "text" to "document"`},
+			{"document / not null with type constraint", "DOCUMENT NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"document / not null with non-respected type constraint ", "DOCUMENT NOT NULL", `{a: false}`, `cannot convert "bool" to "document"`},
+
+			{"duration", "DURATION", `{a: "foo"}`, `cannot convert "foo" to "duration": time: invalid duration foo`},
+			{"duration / not null with type constraint", "DURATION NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"duration / not null with non-respected type constraint ", "DURATION NOT NULL", `{a: [1,2,3]}`, `type "array" incompatible with "integer"`},
+
+			{"float64", "FLOAT64", `{a: "foo"}`, `cannot convert "text" to "float64"`},
+			{"float64 / not null with type constraint", "FLOAT64 NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"float64 / not null with non-respected type constraint ", "FLOAT64 NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "float64"`},
+
+			{"int", "INT", `{a: "foo"}`, `cannot convert "text" to "int64": type "text" incompatible with "integer"`},
+			{"int / not null with type constraint", "INT NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"int / not null with non-respected type constraint ", "INT NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "int64": type "array" incompatible with "integer"`},
+
+			{"integer", "INTEGER", `{a: "foo"}`, `cannot convert "text" to "int64": type "text" incompatible with "integer"`},
+			{"integer / not null with type constraint", "INTEGER NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"integer / not null with non-respected type constraint ", "INTEGER NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "int64": type "array" incompatible with "integer"`},
+
+			{"int8", "INT8", `{a: "foo"}`, `cannot convert "text" to "int8": type "text" incompatible with "integer"`},
+			{"int8 / not null with type constraint", "INT8 NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"int8 / not null with non-respected type constraint ", "INT8 NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "int8": type "array" incompatible with "integer"`},
+
+			{"int16", "INT16", `{a: "foo"}`, `cannot convert "text" to "int16": type "text" incompatible with "integer"`},
+			{"int16 / not null with type constraint", "INT16 NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"int16 / not null with non-respected type constraint ", "INT16 NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "int16": type "array" incompatible with "integer"`},
+
+			{"int32", "INT32", `{a: "foo"}`, `cannot convert "text" to "int32": type "text" incompatible with "integer"`},
+			{"int32 / not null with type constraint", "INT32 NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"int32 / not null with non-respected type constraint ", "INT32 NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "int32": type "array" incompatible with "integer"`},
+
+			{"int64", "INT64", `{a: "foo"}`, `cannot convert "text" to "int64": type "text" incompatible with "integer"`},
+			{"int64 / not null with type constraint", "INT64 NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"int64 / not null with non-respected type constraint ", "INT64 NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "int64": type "array" incompatible with "integer"`},
+
+			{"numeric", "NUMERIC", `{a: "foo"}`, `cannot convert "text" to "float64"`},
+			{"numeric / not null with type constraint", "NUMERIC NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"numeric / not null with non-respected type constraint ", "NUMERIC NOT NULL", `{a: [1,2,3]}`, `cannot convert "array" to "float64"`},
+
+			{"string", "STRING", `{a: [1,2,3]}`, `cannot convert "array" to "string"`},
+			{"string / not null with type constraint", "STRING NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"string / not null with non-respected type constraint ", "STRING NOT NULL", `{a: 420}`, `cannot convert "int16" to "string"`},
+
+			{"text", "TEXT", `{a: [1,2,3]}`, `cannot convert "array" to "string"`},
+			{"text / not null with type constraint", "TEXT NOT NULL", `{}`, `field "a" is required and must be not null`},
+			{"text / not null with non-respected type constraint ", "TEXT NOT NULL", `{a: 42}`, `cannot convert "int8" to "string"`},
+		}
+
+		db, err := genji.Open(":memory:")
+		require.NoError(t, err)
+		defer db.Close()
+
+		for i, test := range tests {
+			t.Run(test.name, func(t *testing.T) {
+				q := fmt.Sprintf("CREATE TABLE test%d (a %s)", i, test.fieldConstraint)
+				err := db.Exec(q)
+				require.NoError(t, err)
+
+				q = fmt.Sprintf("INSERT INTO test%d VALUES %s", i, test.value)
+				err = db.Exec(q)
+				require.EqualError(t, err, test.expectedErr)
+			})
+		}
+	})
 }