From da0707cadfbc55ff786632a704b0a58c97c9994c Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= Date: Fri, 16 Apr 2021 23:17:36 +0300 Subject: MDEV-10825 Feature Request: Persist exact view definitions DDL MDEV-13805 SHOW CREATE VIEW don't provide formating in .frm file This patch extends I_S.views with an extra column called SOURCE. "SOURCE" is a verbatim copy of the view's frm field source=... TODO A few feature details need to be ironed out: * Should we make sure that SOURCE is a verbatim copy of create view? Currently we only present the select statement. https://database.guide/4-ways-to-get-a-view-definition-using-transact-sql/ SQL Server for the "definition" column has the following: SELECT definition FROM sys.sql_modules WHERE object_id = object_id('Website.Customers'); +--------------+ | definition | |--------------| | CREATE VIEW Website.Customers AS SELECT s.CustomerID, s.CustomerName, sc.CustomerCategoryName, pp.FullName AS PrimaryContact, ap.FullName AS AlternateContact, s.PhoneNumber, s.FaxNumber, bg.BuyingGroupName, s.WebsiteURL, dm.DeliveryMethodName AS DeliveryMethod, c.CityName AS CityName, s.DeliveryLocation AS DeliveryLocation, s.DeliveryRun, s.RunPosition FROM Sales.Customers AS s LEFT OUTER JOIN Sales.CustomerCategories AS sc ON s.CustomerCategoryID = sc.CustomerCategoryID LEFT OUTER JOIN [Application].People AS pp ON s.PrimaryContactPersonID = pp.PersonID LEFT OUTER JOIN [Application].People AS ap ON s.AlternateContactPersonID = ap.PersonID LEFT OUTER JOIN Sales.BuyingGroups AS bg ON s.BuyingGroupID = bg.BuyingGroupID LEFT OUTER JOIN [Application].DeliveryMethods AS dm ON s.DeliveryMethodID = dm.DeliveryMethodID LEFT OUTER JOIN [Application].Cities AS c ON s.DeliveryCityID = c.CityID | +--------------+ (1 row affected) --- mysql-test/main/view_source.result | 48 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/view_source.test | 24 +++++++++++++++++++ sql/sql_show.cc | 5 ++++ 3 files changed, 77 insertions(+) create mode 100644 mysql-test/main/view_source.result create mode 100644 mysql-test/main/view_source.test diff --git a/mysql-test/main/view_source.result b/mysql-test/main/view_source.result new file mode 100644 index 00000000000..bb37b2617ad --- /dev/null +++ b/mysql-test/main/view_source.result @@ -0,0 +1,48 @@ +SET NAMES utf8; +create table t1 (a int); +insert into t1 values (1), (2), (11), (12); +create view v1 as +-- Some comment +select a as 'ăș' from t1 where a > 10 +-- Another comment with ășîț +order by a +; +SET NAMES latin1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `??` from `t1` where `t1`.`a` > 10 order by `t1`.`a` utf8 utf8_general_ci +select * from information_schema.views where table_schema = 'test'; +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME v1 +VIEW_DEFINITION select `test`.`t1`.`a` AS `??` from `test`.`t1` where `test`.`t1`.`a` > 10 order by `test`.`t1`.`a` +CHECK_OPTION NONE +IS_UPDATABLE YES +DEFINER root@localhost +SECURITY_TYPE DEFINER +CHARACTER_SET_CLIENT utf8 +COLLATION_CONNECTION utf8_general_ci +ALGORITHM UNDEFINED +SOURCE -- Some comment +select a as '??' from t1 where a > 10 +-- Another comment with ??î? +order by a +SET NAMES utf8; +select * from information_schema.views where table_schema = 'test'; +TABLE_CATALOG def +TABLE_SCHEMA test +TABLE_NAME v1 +VIEW_DEFINITION select `test`.`t1`.`a` AS `ăș` from `test`.`t1` where `test`.`t1`.`a` > 10 order by `test`.`t1`.`a` +CHECK_OPTION NONE +IS_UPDATABLE YES +DEFINER root@localhost +SECURITY_TYPE DEFINER +CHARACTER_SET_CLIENT utf8 +COLLATION_CONNECTION utf8_general_ci +ALGORITHM UNDEFINED +SOURCE -- Some comment +select a as 'ăș' from t1 where a > 10 +-- Another comment with ășîț +order by a +drop table t1; +drop view v1; diff --git a/mysql-test/main/view_source.test b/mysql-test/main/view_source.test new file mode 100644 index 00000000000..a4389ff918a --- /dev/null +++ b/mysql-test/main/view_source.test @@ -0,0 +1,24 @@ +SET NAMES utf8; +create table t1 (a int); +insert into t1 values (1), (2), (11), (12); + +create view v1 as + -- Some comment + select a as 'ăș' from t1 where a > 10 + -- Another comment with ășîț + order by a +; + +SET NAMES latin1; + +show create view v1; + +--vertical_results +select * from information_schema.views where table_schema = 'test'; + +SET NAMES utf8; +--vertical_results +select * from information_schema.views where table_schema = 'test'; + +drop table t1; +drop view v1; diff --git a/sql/sql_show.cc b/sql/sql_show.cc index b1cc696e9fe..b4d91b050bc 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -6867,6 +6867,10 @@ static int get_schema_views_record(THD *thd, TABLE_LIST *tables, table->field[10]->store(view_algorithm(tables), cs); + if (tables->allowed_show) + table->field[11]->store(tables->source.str, tables->source.length, + tables->view_creation_ctx->get_client_cs()); + if (schema_table_store_record(thd, table)) DBUG_RETURN(1); if (unlikely(res && thd->is_error())) @@ -9125,6 +9129,7 @@ ST_FIELD_INFO view_fields_info[]= Column("CHARACTER_SET_CLIENT", CSName(), NOT_NULL, OPEN_FRM_ONLY), Column("COLLATION_CONNECTION", CSName(), NOT_NULL, OPEN_FRM_ONLY), Column("ALGORITHM", Varchar(10),NOT_NULL, OPEN_FRM_ONLY), + Column("SOURCE", Longtext(65535), NOT_NULL, OPEN_FRM_ONLY), CEnd() }; -- cgit v1.2.1