Repository: cloudspannerecosystem/memefish
Pull request #75 (2023-10-21 07:16:21)

Add support for CREATE VIEW with SQL SECURITY DEFINER
This PR add support `DEFINER` security type for [CREATE VIEW DDL](https://cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language#create-view).

I have referenced an existing [implementation of TableSampleMethod](https://github.com/cloudspannerecosystem/memefish/blob/d6176ec284231388e9f38dd3331c0479aa401940/parser.go#L921-L930) while working on this feature.



Patch:
diff --git a/ast/ast.go b/ast/ast.go
index 39b6857..445eb89 100644
--- a/ast/ast.go
+++ b/ast/ast.go
@@ -1599,7 +1599,7 @@ type RowDeletionPolicy struct {
 // CreateView is CREATE VIEW statement node.
 //
 //	CREATE {{if .OrReplace}}OR REPLACE{{end}} VIEW {{.Name | sql}}
-//	SQL SECURITY INVOKER AS
+//	SQL SECURITY {{.SecurityType}} AS
 //	{{.Query | sql}}
 type CreateView struct {
 	// pos = Create
@@ -1607,9 +1607,10 @@ type CreateView struct {
 
 	Create token.Pos
 
-	Name      *Ident
-	OrReplace bool
-	Query     QueryExpr
+	Name         *Ident
+	OrReplace    bool
+	SecurityType SecurityType
+	Query        QueryExpr
 }
 
 // AlterTable is ALTER TABLE statement node.
diff --git a/ast/const.go b/ast/const.go
index 7031583..57840e2 100644
--- a/ast/const.go
+++ b/ast/const.go
@@ -115,3 +115,10 @@ const (
 	OnDeleteCascade  OnDeleteAction = "ON DELETE CASCADE"
 	OnDeleteNoAction OnDeleteAction = "ON DELETE NO ACTION"
 )
+
+type SecurityType string
+
+const (
+	SecurityTypeInvoker SecurityType = "INVOKER"
+	SecurityTypeDefiner SecurityType = "DEFINER"
+)
diff --git a/ast/sql.go b/ast/sql.go
index 1750522..02b7df2 100644
--- a/ast/sql.go
+++ b/ast/sql.go
@@ -771,7 +771,7 @@ func (c *CreateView) SQL() string {
 	if c.OrReplace {
 		sql += " OR REPLACE"
 	}
-	sql += " VIEW " + c.Name.SQL() + " SQL SECURITY INVOKER AS " + c.Query.SQL()
+	sql += " VIEW " + c.Name.SQL() + " SQL SECURITY " + string(c.SecurityType) + " AS " + c.Query.SQL()
 	return sql
 }
 
diff --git a/parser.go b/parser.go
index 62e19c8..42757cc 100644
--- a/parser.go
+++ b/parser.go
@@ -2164,16 +2164,28 @@ func (p *Parser) parseCreateView(pos token.Pos) *ast.CreateView {
 
 	p.expectKeywordLike("SQL")
 	p.expectKeywordLike("SECURITY")
-	p.expectKeywordLike("INVOKER")
+
+	id := p.expect(token.TokenIdent)
+	var securityType ast.SecurityType
+	switch {
+	case id.IsIdent("INVOKER"):
+		securityType = ast.SecurityTypeInvoker
+	case id.IsIdent("DEFINER"):
+		securityType = ast.SecurityTypeDefiner
+	default:
+		p.panicfAtToken(id, "expected identifier: INVOKER, DEFINER, but: %s", id.Raw)
+	}
+
 	p.expect("AS")
 
 	query := p.parseQueryExpr()
 
 	return &ast.CreateView{
-		Create:    pos,
-		Name:      name,
-		OrReplace: orReplace,
-		Query:     query,
+		Create:       pos,
+		Name:         name,
+		OrReplace:    orReplace,
+		SecurityType: securityType,
+		Query:        query,
 	}
 }
 


Test patch:
diff --git a/testdata/input/ddl/create_view_sql_security_definer.sql b/testdata/input/ddl/create_view_sql_security_definer.sql
new file mode 100644
index 0000000..b9b8923
--- /dev/null
+++ b/testdata/input/ddl/create_view_sql_security_definer.sql
@@ -0,0 +1,6 @@
+create view singernames
+sql security definer
+as select
+    singers.singerid as singerid,
+    singers.firstname || ' ' || singers.lastname as name
+from singers
diff --git a/testdata/result/ddl/create_or_replace_view.sql.txt b/testdata/result/ddl/create_or_replace_view.sql.txt
index a23a1ad..e2ffed9 100644
--- a/testdata/result/ddl/create_or_replace_view.sql.txt
+++ b/testdata/result/ddl/create_or_replace_view.sql.txt
@@ -14,8 +14,9 @@ from singers
     NameEnd: 34,
     Name:    "singernames",
   },
-  OrReplace: true,
-  Query:     &ast.Select{
+  OrReplace:    true,
+  SecurityType: "INVOKER",
+  Query:        &ast.Select{
     Select:   59,
     Distinct: false,
     AsStruct: false,
diff --git a/testdata/result/ddl/create_view.sql.txt b/testdata/result/ddl/create_view.sql.txt
index 3e93fcd..ecd9b7c 100644
--- a/testdata/result/ddl/create_view.sql.txt
+++ b/testdata/result/ddl/create_view.sql.txt
@@ -14,8 +14,9 @@ from singers
     NameEnd: 23,
     Name:    "singernames",
   },
-  OrReplace: false,
-  Query:     &ast.Select{
+  OrReplace:    false,
+  SecurityType: "INVOKER",
+  Query:        &ast.Select{
     Select:   48,
     Distinct: false,
     AsStruct: false,
diff --git a/testdata/result/ddl/create_view_sql_security_definer.sql.txt b/testdata/result/ddl/create_view_sql_security_definer.sql.txt
new file mode 100644
index 0000000..11c5a8a
--- /dev/null
+++ b/testdata/result/ddl/create_view_sql_security_definer.sql.txt
@@ -0,0 +1,120 @@
+--- create_view_sql_security_definer.sql
+create view singernames
+sql security definer
+as select
+    singers.singerid as singerid,
+    singers.firstname || ' ' || singers.lastname as name
+from singers
+
+--- AST
+&ast.CreateView{
+  Create: 0,
+  Name:   &ast.Ident{
+    NamePos: 12,
+    NameEnd: 23,
+    Name:    "singernames",
+  },
+  OrReplace:    false,
+  SecurityType: "DEFINER",
+  Query:        &ast.Select{
+    Select:   48,
+    Distinct: false,
+    AsStruct: false,
+    Results:  []ast.SelectItem{
+      &ast.Alias{
+        Expr: &ast.Path{
+          Idents: []*ast.Ident{
+            &ast.Ident{
+              NamePos: 59,
+              NameEnd: 66,
+              Name:    "singers",
+            },
+            &ast.Ident{
+              NamePos: 67,
+              NameEnd: 75,
+              Name:    "singerid",
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    -1,
+          Alias: &ast.Ident{
+            NamePos: 79,
+            NameEnd: 87,
+            Name:    "singerid",
+          },
+        },
+      },
+      &ast.Alias{
+        Expr: &ast.BinaryExpr{
+          Op:   "||",
+          Left: &ast.BinaryExpr{
+            Op:   "||",
+            Left: &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 93,
+                  NameEnd: 100,
+                  Name:    "singers",
+                },
+                &ast.Ident{
+                  NamePos: 101,
+                  NameEnd: 110,
+                  Name:    "firstname",
+                },
+              },
+            },
+            Right: &ast.StringLiteral{
+              ValuePos: 114,
+              ValueEnd: 117,
+              Value:    " ",
+            },
+          },
+          Right: &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 121,
+                NameEnd: 128,
+                Name:    "singers",
+              },
+              &ast.Ident{
+                NamePos: 129,
+                NameEnd: 137,
+                Name:    "lastname",
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    -1,
+          Alias: &ast.Ident{
+            NamePos: 141,
+            NameEnd: 145,
+            Name:    "name",
+          },
+        },
+      },
+    },
+    From: &ast.From{
+      From:   146,
+      Source: &ast.TableName{
+        Table: &ast.Ident{
+          NamePos: 151,
+          NameEnd: 158,
+          Name:    "singers",
+        },
+        Hint:   (*ast.Hint)(nil),
+        As:     (*ast.AsAlias)(nil),
+        Sample: (*ast.TableSample)(nil),
+      },
+    },
+    Where:   (*ast.Where)(nil),
+    GroupBy: (*ast.GroupBy)(nil),
+    Having:  (*ast.Having)(nil),
+    OrderBy: (*ast.OrderBy)(nil),
+    Limit:   (*ast.Limit)(nil),
+  },
+}
+
+--- SQL
+CREATE VIEW singernames SQL SECURITY DEFINER AS SELECT singers.singerid AS singerid, singers.firstname || " " || singers.lastname AS name FROM singers
diff --git a/testdata/result/statement/create_or_replace_view.sql.txt b/testdata/result/statement/create_or_replace_view.sql.txt
index a23a1ad..e2ffed9 100644
--- a/testdata/result/statement/create_or_replace_view.sql.txt
+++ b/testdata/result/statement/create_or_replace_view.sql.txt
@@ -14,8 +14,9 @@ from singers
     NameEnd: 34,
     Name:    "singernames",
   },
-  OrReplace: true,
-  Query:     &ast.Select{
+  OrReplace:    true,
+  SecurityType: "INVOKER",
+  Query:        &ast.Select{
     Select:   59,
     Distinct: false,
     AsStruct: false,
diff --git a/testdata/result/statement/create_view.sql.txt b/testdata/result/statement/create_view.sql.txt
index 3e93fcd..ecd9b7c 100644
--- a/testdata/result/statement/create_view.sql.txt
+++ b/testdata/result/statement/create_view.sql.txt
@@ -14,8 +14,9 @@ from singers
     NameEnd: 23,
     Name:    "singernames",
   },
-  OrReplace: false,
-  Query:     &ast.Select{
+  OrReplace:    false,
+  SecurityType: "INVOKER",
+  Query:        &ast.Select{
     Select:   48,
     Distinct: false,
     AsStruct: false,
diff --git a/testdata/result/statement/create_view_sql_security_definer.sql.txt b/testdata/result/statement/create_view_sql_security_definer.sql.txt
new file mode 100644
index 0000000..11c5a8a
--- /dev/null
+++ b/testdata/result/statement/create_view_sql_security_definer.sql.txt
@@ -0,0 +1,120 @@
+--- create_view_sql_security_definer.sql
+create view singernames
+sql security definer
+as select
+    singers.singerid as singerid,
+    singers.firstname || ' ' || singers.lastname as name
+from singers
+
+--- AST
+&ast.CreateView{
+  Create: 0,
+  Name:   &ast.Ident{
+    NamePos: 12,
+    NameEnd: 23,
+    Name:    "singernames",
+  },
+  OrReplace:    false,
+  SecurityType: "DEFINER",
+  Query:        &ast.Select{
+    Select:   48,
+    Distinct: false,
+    AsStruct: false,
+    Results:  []ast.SelectItem{
+      &ast.Alias{
+        Expr: &ast.Path{
+          Idents: []*ast.Ident{
+            &ast.Ident{
+              NamePos: 59,
+              NameEnd: 66,
+              Name:    "singers",
+            },
+            &ast.Ident{
+              NamePos: 67,
+              NameEnd: 75,
+              Name:    "singerid",
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    -1,
+          Alias: &ast.Ident{
+            NamePos: 79,
+            NameEnd: 87,
+            Name:    "singerid",
+          },
+        },
+      },
+      &ast.Alias{
+        Expr: &ast.BinaryExpr{
+          Op:   "||",
+          Left: &ast.BinaryExpr{
+            Op:   "||",
+            Left: &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 93,
+                  NameEnd: 100,
+                  Name:    "singers",
+                },
+                &ast.Ident{
+                  NamePos: 101,
+                  NameEnd: 110,
+                  Name:    "firstname",
+                },
+              },
+            },
+            Right: &ast.StringLiteral{
+              ValuePos: 114,
+              ValueEnd: 117,
+              Value:    " ",
+            },
+          },
+          Right: &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 121,
+                NameEnd: 128,
+                Name:    "singers",
+              },
+              &ast.Ident{
+                NamePos: 129,
+                NameEnd: 137,
+                Name:    "lastname",
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    -1,
+          Alias: &ast.Ident{
+            NamePos: 141,
+            NameEnd: 145,
+            Name:    "name",
+          },
+        },
+      },
+    },
+    From: &ast.From{
+      From:   146,
+      Source: &ast.TableName{
+        Table: &ast.Ident{
+          NamePos: 151,
+          NameEnd: 158,
+          Name:    "singers",
+        },
+        Hint:   (*ast.Hint)(nil),
+        As:     (*ast.AsAlias)(nil),
+        Sample: (*ast.TableSample)(nil),
+      },
+    },
+    Where:   (*ast.Where)(nil),
+    GroupBy: (*ast.GroupBy)(nil),
+    Having:  (*ast.Having)(nil),
+    OrderBy: (*ast.OrderBy)(nil),
+    Limit:   (*ast.Limit)(nil),
+  },
+}
+
+--- SQL
+CREATE VIEW singernames SQL SECURITY DEFINER AS SELECT singers.singerid AS singerid, singers.firstname || " " || singers.lastname AS name FROM singers