MDEV-10825 Feature Request: Persist exact view definitions DDL10.6-mdev-10825

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)

3 files changed, 77 insertions, 0 deletions

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()
 };