spanner/spansql: parse foreign key constraints in CREATE TABLE
This defines the AST structures and parses named and unnamed constraints
consisting of a foreign key in a CREATE TABLE statement.
Future changes will add foreign key support in ALTER TABLE.
Change-Id: I98547e1d093892a394e44ae6b9cb03a26f9ff43b
Reviewed-on: https://code-review.googlesource.com/c/gocloud/+/52990
Reviewed-by: kokoro <noreply+kokoro@google.com>
Reviewed-by: Knut Olav Løite <koloite@gmail.com>
diff --git a/spanner/spansql/keywords.go b/spanner/spansql/keywords.go
index e7a8941..4dce1ca 100644
--- a/spanner/spansql/keywords.go
+++ b/spanner/spansql/keywords.go
@@ -123,6 +123,12 @@
"WINDOW": true,
"WITH": true,
"WITHIN": true,
+
+ // For new foreign key support; these aren't officially listed yet.
+ "CONSTRAINT": true,
+ "FOREIGN": true,
+ "KEY": true,
+ "REFERENCES": true,
}
// funcs is the set of reserved keywords that are functions.
diff --git a/spanner/spansql/parser.go b/spanner/spansql/parser.go
index ff9cf1b..5e62851 100644
--- a/spanner/spansql/parser.go
+++ b/spanner/spansql/parser.go
@@ -947,7 +947,7 @@
/*
CREATE TABLE table_name(
- [column_def, ...] )
+ [column_def, ...] [ table_constraint, ...] )
primary_key [, cluster]
primary_key:
@@ -971,6 +971,15 @@
ct := &CreateTable{Name: tname, Position: pos}
err = p.parseCommaList(func(p *parser) *parseError {
+ if p.sniff("CONSTRAINT") || p.sniff("FOREIGN") {
+ tc, err := p.parseTableConstraint()
+ if err != nil {
+ return err
+ }
+ ct.Constraints = append(ct.Constraints, tc)
+ return nil
+ }
+
cd, err := p.parseColumnDef()
if err != nil {
return err
@@ -1322,6 +1331,78 @@
return kp, nil
}
+func (p *parser) parseTableConstraint() (TableConstraint, *parseError) {
+ debugf("parseTableConstraint: %v", p)
+
+ /*
+ table_constraint:
+ [ CONSTRAINT constraint_name ]
+ foreign_key
+ */
+
+ if p.eat("CONSTRAINT") {
+ pos := p.Pos()
+ // Named foreign key.
+ cname, err := p.parseTableOrIndexOrColumnName()
+ if err != nil {
+ return TableConstraint{}, err
+ }
+ fk, err := p.parseForeignKey()
+ if err != nil {
+ return TableConstraint{}, err
+ }
+ return TableConstraint{
+ Name: cname,
+ ForeignKey: fk,
+ Position: pos,
+ }, nil
+ }
+
+ // Unnamed foreign key.
+ fk, err := p.parseForeignKey()
+ if err != nil {
+ return TableConstraint{}, err
+ }
+ return TableConstraint{
+ ForeignKey: fk,
+ Position: fk.Position,
+ }, nil
+}
+
+func (p *parser) parseForeignKey() (ForeignKey, *parseError) {
+ debugf("parseForeignKey: %v", p)
+
+ /*
+ foreign_key:
+ FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] )
+ */
+
+ if err := p.expect("FOREIGN"); err != nil {
+ return ForeignKey{}, err
+ }
+ fk := ForeignKey{Position: p.Pos()}
+ if err := p.expect("KEY"); err != nil {
+ return ForeignKey{}, err
+ }
+ var err *parseError
+ fk.Columns, err = p.parseColumnNameList()
+ if err != nil {
+ return ForeignKey{}, err
+ }
+ if err := p.expect("REFERENCES"); err != nil {
+ return ForeignKey{}, err
+ }
+ fk.RefTable, err = p.parseTableOrIndexOrColumnName()
+ if err != nil {
+ return ForeignKey{}, err
+ }
+ fk.RefColumns, err = p.parseColumnNameList()
+ if err != nil {
+ return ForeignKey{}, err
+ }
+ return fk, nil
+}
+
func (p *parser) parseColumnNameList() ([]string, *parseError) {
var list []string
err := p.parseCommaList(func(p *parser) *parseError {
diff --git a/spanner/spansql/parser_test.go b/spanner/spansql/parser_test.go
index 998ca90..5c30791 100644
--- a/spanner/spansql/parser_test.go
+++ b/spanner/spansql/parser_test.go
@@ -248,7 +248,9 @@
) STORING (Count), INTERLEAVE IN SomeTable;
CREATE TABLE FooBarAux (
System STRING(MAX) NOT NULL,
+ CONSTRAINT Con1 FOREIGN KEY (System) REFERENCES FooBar (System),
RepoPath STRING(MAX) NOT NULL,
+ FOREIGN KEY (System, RepoPath) REFERENCES Stranger (Sys, RPath), -- unnamed foreign key
Author STRING(MAX) NOT NULL,
) PRIMARY KEY(System, RepoPath, Author),
INTERLEAVE IN PARENT FooBar ON DELETE CASCADE;
@@ -296,8 +298,29 @@
Name: "FooBarAux",
Columns: []ColumnDef{
{Name: "System", Type: Type{Base: String, Len: MaxLen}, NotNull: true, Position: line(12)},
- {Name: "RepoPath", Type: Type{Base: String, Len: MaxLen}, NotNull: true, Position: line(13)},
- {Name: "Author", Type: Type{Base: String, Len: MaxLen}, NotNull: true, Position: line(14)},
+ {Name: "RepoPath", Type: Type{Base: String, Len: MaxLen}, NotNull: true, Position: line(14)},
+ {Name: "Author", Type: Type{Base: String, Len: MaxLen}, NotNull: true, Position: line(16)},
+ },
+ Constraints: []TableConstraint{
+ {
+ Name: "Con1",
+ ForeignKey: ForeignKey{
+ Columns: []string{"System"},
+ RefTable: "FooBar",
+ RefColumns: []string{"System"},
+ Position: line(13),
+ },
+ Position: line(13),
+ },
+ {
+ ForeignKey: ForeignKey{
+ Columns: []string{"System", "RepoPath"},
+ RefTable: "Stranger",
+ RefColumns: []string{"Sys", "RPath"},
+ Position: line(15),
+ },
+ Position: line(15),
+ },
},
PrimaryKey: []KeyPart{
{Column: "System"},
@@ -312,18 +335,18 @@
},
&AlterTable{
Name: "FooBar",
- Alteration: AddColumn{Def: ColumnDef{Name: "TZ", Type: Type{Base: Bytes, Len: 20}, Position: line(18)}},
- Position: line(18),
+ Alteration: AddColumn{Def: ColumnDef{Name: "TZ", Type: Type{Base: Bytes, Len: 20}, Position: line(20)}},
+ Position: line(20),
},
&AlterTable{
Name: "FooBar",
Alteration: DropColumn{Name: "TZ"},
- Position: line(19),
+ Position: line(21),
},
&AlterTable{
Name: "FooBar",
Alteration: SetOnDelete{Action: NoActionOnDelete},
- Position: line(20),
+ Position: line(22),
},
&AlterTable{
Name: "FooBar",
@@ -331,21 +354,21 @@
Name: "Author",
Type: Type{Base: String, Len: MaxLen},
NotNull: true,
- Position: line(21),
+ Position: line(23),
}},
- Position: line(21),
+ Position: line(23),
},
- &DropIndex{Name: "MyFirstIndex", Position: line(23)},
- &DropTable{Name: "FooBar", Position: line(24)},
+ &DropIndex{Name: "MyFirstIndex", Position: line(25)},
+ &DropTable{Name: "FooBar", Position: line(26)},
&CreateTable{
Name: "NonScalars",
Columns: []ColumnDef{
- {Name: "Dummy", Type: Type{Base: Int64}, NotNull: true, Position: line(29)},
- {Name: "Ids", Type: Type{Array: true, Base: Int64}, Position: line(30)},
- {Name: "Names", Type: Type{Array: true, Base: String, Len: MaxLen}, Position: line(31)},
+ {Name: "Dummy", Type: Type{Base: Int64}, NotNull: true, Position: line(31)},
+ {Name: "Ids", Type: Type{Array: true, Base: Int64}, Position: line(32)},
+ {Name: "Names", Type: Type{Array: true, Base: String, Len: MaxLen}, Position: line(33)},
},
PrimaryKey: []KeyPart{{Column: "Dummy"}},
- Position: line(28),
+ Position: line(30),
},
}, Comments: []*Comment{
{Marker: "#", Start: line(2), End: line(2),
@@ -354,11 +377,13 @@
Text: []string{"This is another comment."}},
{Marker: "/*", Start: line(4), End: line(5),
Text: []string{" This is a", "\t\t\t\t\t\t * multiline comment."}},
- {Marker: "--", Isolated: true, Start: line(26), End: line(27),
+ {Marker: "--", Start: line(15), End: line(15),
+ Text: []string{"unnamed foreign key"}},
+ {Marker: "--", Isolated: true, Start: line(28), End: line(29),
Text: []string{"This table has some commentary", "that spans multiple lines."}},
// These comments shouldn't get combined:
- {Marker: "--", Start: line(29), End: line(29), Text: []string{"dummy comment"}},
- {Marker: "--", Start: line(30), End: line(30), Text: []string{"comment on ids"}},
+ {Marker: "--", Start: line(31), End: line(31), Text: []string{"dummy comment"}},
+ {Marker: "--", Start: line(32), End: line(32), Text: []string{"comment on ids"}},
}}},
// No trailing comma:
{`ALTER TABLE T ADD COLUMN C2 INT64`, &DDL{Filename: "filename", List: []DDLStmt{
diff --git a/spanner/spansql/sql.go b/spanner/spansql/sql.go
index f5ffc45..5cbfcfb 100644
--- a/spanner/spansql/sql.go
+++ b/spanner/spansql/sql.go
@@ -29,6 +29,9 @@
for _, c := range ct.Columns {
str += " " + c.SQL() + ",\n"
}
+ for _, tc := range ct.Constraints {
+ str += " " + tc.SQL() + ",\n"
+ }
str += ") PRIMARY KEY("
for i, c := range ct.PrimaryKey {
if i > 0 {
@@ -127,6 +130,24 @@
return str
}
+func (tc TableConstraint) SQL() string {
+ var str string
+ if tc.Name != "" {
+ str += "CONSTRAINT " + tc.Name
+ }
+ str += tc.ForeignKey.SQL()
+ return str
+}
+
+func (fk ForeignKey) SQL() string {
+ str := "FOREIGN KEY ("
+ str += strings.Join(fk.Columns, ", ")
+ str += ") REFERENCES " + fk.RefTable + " ("
+ str += strings.Join(fk.RefColumns, ", ")
+ str += ")"
+ return str
+}
+
func (t Type) SQL() string {
str := t.Base.SQL()
if t.Base == String || t.Base == Bytes {
diff --git a/spanner/spansql/types.go b/spanner/spansql/types.go
index 8af40e3..5c915b4 100644
--- a/spanner/spansql/types.go
+++ b/spanner/spansql/types.go
@@ -29,10 +29,11 @@
// CreateTable represents a CREATE TABLE statement.
// https://cloud.google.com/spanner/docs/data-definition-language#create_table
type CreateTable struct {
- Name string
- Columns []ColumnDef
- PrimaryKey []KeyPart
- Interleave *Interleave
+ Name string
+ Columns []ColumnDef
+ Constraints []TableConstraint
+ PrimaryKey []KeyPart
+ Interleave *Interleave
Position Position // position of the "CREATE" token
}
@@ -45,9 +46,27 @@
// Mutate in place.
ct.Columns[i].clearOffset()
}
+ for i := range ct.Constraints {
+ // Mutate in place.
+ ct.Constraints[i].clearOffset()
+ }
ct.Position.Offset = 0
}
+// TableConstraint represents a constraint on a table.
+type TableConstraint struct {
+ Name string // may be empty
+ ForeignKey ForeignKey
+
+ Position Position // position of the "CONSTRAINT" or "FOREIGN" token
+}
+
+func (tc TableConstraint) Pos() Position { return tc.Position }
+func (tc *TableConstraint) clearOffset() {
+ tc.Position.Offset = 0
+ tc.ForeignKey.clearOffset()
+}
+
// Interleave represents an interleave clause of a CREATE TABLE statement.
type Interleave struct {
Parent string
@@ -181,6 +200,19 @@
func (cd ColumnDef) Pos() Position { return cd.Position }
func (cd *ColumnDef) clearOffset() { cd.Position.Offset = 0 }
+// ForeignKey represents a foreign key definition as part of a CREATE TABLE
+// or ALTER TABLE statement.
+type ForeignKey struct {
+ Columns []string
+ RefTable string
+ RefColumns []string
+
+ Position Position // position of the "FOREIGN" token
+}
+
+func (fk ForeignKey) Pos() Position { return fk.Position }
+func (fk *ForeignKey) clearOffset() { fk.Position.Offset = 0 }
+
// Type represents a column type.
type Type struct {
Array bool
