BUG24947078: Create or alter view raises an error using non updatable views

nmariz · nmariz · commit eeae989d2c75 · 2016-10-31T13:56:45.000Z
This patch fixes the error raised when attempting to create or alter non
updatable views.

By default the statement for creating or altering views included WITH CHECK
OPTION clause, this was removed to allow the usage of non updatable views.
The user can still use the with_check_option method to set this clause.

Tests were added for regression.
diff --git a/lib/mysqlx/statement.py b/lib/mysqlx/statement.py
@@ -29,7 +29,7 @@
 from .errors import ProgrammingError
 from .expr import ExprParser
 from .compat import STRING_TYPES
-from .constants import Algorithms, Securities, CheckOptions
+from .constants import Algorithms, Securities
 from .dbdoc import DbDoc
 from .protobuf import mysqlx_crud_pb2 as MySQLxCrud
 from .result import SqlResult, Result, ColumnType
@@ -784,7 +784,7 @@ def __init__(self, view, replace=False):
         self._security = Securities.DEFINER
         self._definer = None
         self._defined_as = None
-        self._check_option = CheckOptions.CASCADED
+        self._check_option = None
 
     def columns(self, columns):
         """Sets the column names.
@@ -870,14 +870,16 @@ def execute(self):
         columns = " ({0})".format(", ".join(self._columns)) \
                   if self._columns else ""
         view_name = quote_multipart_identifier((self._schema.name, self._name))
+        check_option = " WITH {0} CHECK OPTION".format(self._check_option) \
+                       if self._check_option else ""
         sql = ("CREATE{replace} ALGORITHM = {algorithm}{definer} "
                "SQL SECURITY {security} VIEW {view_name}{columns} "
-               "AS {defined_as} WITH {check_option} CHECK OPTION"
+               "AS {defined_as}{check_option}"
                "".format(replace=replace, algorithm=self._algorithm,
                          definer=definer, security=self._security,
                          view_name=view_name, columns=columns,
                          defined_as=self._defined_as,
-                         check_option=self._check_option))
+                         check_option=check_option))
 
         self._connection.execute_nonquery("sql", sql)
         return self._view
@@ -903,13 +905,15 @@ def execute(self):
         columns = " ({0})".format(", ".join(self._columns)) \
                   if self._columns else ""
         view_name = quote_multipart_identifier((self._schema.name, self._name))
+        check_option = " WITH {0} CHECK OPTION".format(self._check_option) \
+                       if self._check_option else ""
         sql = ("ALTER ALGORITHM = {algorithm}{definer} "
                "SQL SECURITY {security} VIEW {view_name}{columns} "
-               "AS {defined_as} WITH {check_option} CHECK OPTION"
+               "AS {defined_as}{check_option}"
                "".format(algorithm=self._algorithm, definer=definer,
                          security=self._security, view_name=view_name,
                          columns=columns, defined_as=self._defined_as,
-                         check_option=self._check_option))
+                         check_option=check_option))
 
         self._connection.execute_nonquery("sql", sql)
         return self._view
diff --git a/tests/test_mysqlx_crud.py b/tests/test_mysqlx_crud.py
@@ -116,6 +116,16 @@ def test_create_view(self):
                           .defined_as(defined_as) \
                           .execute()
 
+        self.schema.drop_table(view_name)
+
+        # using a non-updatable view
+        defined_as = ("SELECT COLUMN_TYPE, COLUMN_COMMENT FROM "
+                      "INFORMATION_SCHEMA.columns WHERE TABLE_NAME='{0}' "
+                      "AND COLUMN_NAME='id'".format(table_name))
+        view = self.schema.create_view(view_name, True) \
+                          .defined_as(defined_as) \
+                          .execute()
+
         self.schema.drop_table(table_name)
         self.schema.drop_table(view_name)
 
@@ -144,6 +154,14 @@ def test_alter_view(self):
                           .execute()
         self.assertEqual(5, view.count())
 
+        # using a non-updatable view
+        defined_as = ("SELECT COLUMN_TYPE, COLUMN_COMMENT FROM "
+                      "INFORMATION_SCHEMA.columns WHERE TABLE_NAME='{0}' "
+                      "AND COLUMN_NAME='id'".format(table_name))
+        view = self.schema.alter_view(view_name) \
+                          .defined_as(defined_as) \
+                          .execute()
+
         self.schema.drop_table(table_name)
         self.schema.drop_table(view_name)
 
