MySQL Bugs: #14369: Views are stored without text formatting

Bug #14369	Views are stored without text formatting
Submitted:	27 Oct 2005 4:23	Modified:	4 Oct 2008 11:45
Reporter:	Daniel Kasak (Candidate Quality Contributor)
Status:	Duplicate
Category:	Server: Views	Severity:	S4 (Feature request)
Version:	5.0.15	OS:	Linux (Linux)
Assigned to:		Target Version:
Triage:	Triaged: D5 (Feature request)

Description:
I'm testing out 5.0.15 - mainly views and stored procedures. If I create either, in the
mysql client or in mysql-administrator, all text formatting is stripped when the view /
sp is saved.

This makes it incredibly difficult to work with anything other than trivial objects -
otherwise when I return to work on a large view, I have to spend the first 5 minutes
adding new lines and tabs and things to get the query in a state where I can see what's
supposed to be happening ... and *then* I can actually make the desired changes ... and
finally all my lovely formatting of SQL is stripped again when I save things.

How to repeat:
Create a view / stored procedure in mysql-administrator with text formatting - new lines
in appropriate places, tabs to indent different things. Make it look acceptable - like
you'd like someone else to format things if they're asking you to help them out with some
SQL.

Save the view / stored procedure.

Click the 'edit view' button and look what's happened.

Suggested fix:
Allow formatting in views / stored procedures.

If this requires storing it twice - one in plain text, and one in the *original* format
that the user specified, then so be it ...

Thank you for a request. Yes, even in 5.0.16-BK your formatting is not EXACTLY preserved,
not only for, say, views, but for tables also:

mysql> create table pretty_t (
    ->   c1 integer,
    ->     c2 char(10)
    -> , c3 varchar(20)
    ->    default 'aaa'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create
    ->   view pretty_v
    -> as
    ->   select
    -> *
    -> from pretty_t;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table pretty_t;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table          |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pretty_t | CREATE TABLE `pretty_t` (
  `c1` int(11) default NULL,
  `c2` char(10) default NULL,
  `c3` varchar(20) default 'aaa'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table pretty_v;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View     | Create View                                          |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pretty_v | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `pretty_v` AS select `pretty_t`.`c1` AS `c1`,`pretty_t`.`c2` AS
`c2`,`pretty_t`.`c3` AS `c3` from `pretty_t` |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.16-log |
+------------+
1 row in set (0.00 sec)

But it is really needed to add some additional clauses for your database objects, just to
be able to recreate them correctly. I mean, those clauses you omitted, for example. 

So, you are right, a separate, second presentation fo text "as is" will be needed to be
able to get the original formatting if somebody wants it. But, in any case, you'd better
keep it in your own script files.

That is why I think it is a feature request.

For comments in stored procedures,
see related Bug#11230

Any word on this feature request? IMHO it's a very simple addition that would make MySQL
far more usable for people wanting to use views and stored procedures for more than just
playing around with 1-liners.

I realise I can maintain my own list of scripts, including formatting, outside of MySQL,
but really the question is: Is this something that is going to sit well with people
thinking about migrating to MySQL? Personally, I can live with it, but I'm by no means in
the majority here. People coming from serious server environments such as SQL Server and
Oracle will snort with indignation at the suggestion that they maintain their own view &
stored procedures in text files somewhere. Seriously.

WL#2647

This feature request should not be taken lightly. This involves SQL queries that could
affect data returned from the view that is very critical. I'm using 5.0.27 and I think
this request has been here for a long time.

Um ... pretty please?

Any word on this being fixed?  I'm starting to feel the negative effects of not having my
view retain formatting

This issue also affects 5.0.51
The suggestion of saving code externally is ok but very tedious. Few competing products
suffer this deficiency.

This is a duplicate of Bug#5159. Please use the older bug to track progress of this issue.

I don't think this issue is the same as
[url=Bug#5159[/url]">http://bugs.mysql.com/bug.php?id=5159]Bug#5159[/url]. This one relates to preserving
the format of the saved views and the other one just derived into "saving comments about
views".

By the way, I find this problem quite annoying too.

Can anybody understand this without reformatting it?:

[code]
select `Rubro`.`Rubro_ID` AS `Rubro_ID`,((select count(0) AS `Relevamientos` from
`Relevamientos_Vigentes`) - count(`Relevamiento`.`Relevamiento_ID`)) AS `Indefinidos`
from (`Relevamiento` join `Rubro`) where (`Relevamiento`.`Relevamiento_ID` in (select
`Relevamientos_Vigentes`.`Relevamiento_ID` AS `Relevamiento_ID` from
`Relevamientos_Vigentes`) and `Rubro`.`Rubro_ID` in (select
`Relevamiento_Dato`.`Rubro_ID` AS `Rubro_ID` from `Relevamiento_Dato` where
(`Relevamiento_Dato`.`Relevamiento_ID` = `Relevamiento`.`Relevamiento_ID`))) group by
`Rubro`.`Rubro_ID` union select `Rubro`.`Rubro_ID` AS `Rubro_ID`,(select count(0) AS
`Relevamientos` from `Relevamientos_Vigentes`) AS `Indefinidos` from `Rubro` where
(not(`Rubro`.`Rubro_ID` in (select `Relevamiento_Dato`.`Rubro_ID` AS `Rubro_ID` from
`Relevamiento_Dato` where `Relevamiento_Dato`.`Relevamiento_ID` in (select
`Relevamientos_Vigentes`.`Relevamiento_ID` AS `Relevamiento_ID` from
`Relevamientos_Vigentes`)))) order by `Rubro_ID`;
[/code]

Because that's the code MySQL generated form my view from the following code:

[code]
SELECT   Rubro.Rubro_ID AS Rubro_ID,
		 ((SELECT COUNT(* ) AS Relevamientos
		   FROM   Relevamientos_Vigentes)
			  - COUNT(Relevamiento.Relevamiento_ID)) AS Indefinidos
FROM     Relevamiento,
		 Rubro
WHERE    (Relevamiento.Relevamiento_ID IN (SELECT Relevamientos_Vigentes.Relevamiento_ID
AS Relevamiento_ID
										   FROM   Relevamientos_Vigentes)
		  AND Rubro.Rubro_ID IN (SELECT Relevamiento_Dato.Rubro_ID AS Rubro_ID
								 FROM   Relevamiento_Dato
								 WHERE  (Relevamiento_Dato.Relevamiento_ID = Relevamiento.Relevamiento_ID)))
GROUP BY Rubro.Rubro_ID
UNION 
SELECT Rubro.Rubro_ID AS Rubro_ID,
	   (SELECT COUNT(* ) AS Relevamientos
		FROM   Relevamientos_Vigentes) AS Indefinidos
FROM   Rubro
WHERE  (NOT (Rubro.Rubro_ID IN (SELECT Relevamiento_Dato.Rubro_ID AS Rubro_ID
								FROM   Relevamiento_Dato
								WHERE  Relevamiento_Dato.Relevamiento_ID IN (SELECT
Relevamientos_Vigentes.Relevamiento_ID AS Relevamiento_ID
																			 FROM   Relevamientos_Vigentes))))
ORDER BY Rubro_ID;
[/code]

That's the point.
As someone said, it's really tedious to have a copy of the formatted code in external
text files.
Thanks.