MySQL Bugs: #52004: View formatting problem

Bug #52004	View formatting problem
Submitted:	12 Mar 2010 15:44	Modified:	31 Dec 2010 15:44
Reporter:	Martin Pirringer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.16, 5.2.25	OS:	Windows (7)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	formatting, synchronizing, VIEW

Description:
If you enter a view with any kind of formatting the view will always update on synchronizing you will have to "update the model" and then the view will be difficult to read

How to repeat:
1.) Create Schema test
2.) Add table1 with idtable1 and field1 as fields
3.) Ckick "add view"
4.) type in 
CREATE  `view1` AS
SELECT * from table1
WHERE idtable1 = 6
ORDER BY idtable1;

(I know makes little sense but just to have a multi line view statement
5.) Sync it - you know have a view1 in test schema.
6.) from now on everytime you sync it will recreate this view (a problem if you got a lot of them

7.) to work around it you can sync and select to "update model" on that view

8.) go back to edit the view and you will see something like
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`view1` AS select `test`.`table1`.`idtable1` AS `idtable1`,`test`.`table1`.`field1` AS `field1` from `test`.`table1` where (`test`.`table1`.`idtable1` = 6)...... \<rest of view>

Its all in one line and difficult to read

Suggested fix:
Implement something to maintain the formatting. Problem probably is that the view is stored in the "VIEW" Table the ROUTINES table seems to maintain formatting information so either
a.) see if formatting information can be retained in the "VIEW" table
or
b.) keep 2 entries in the WB file for each view 1 in the formatted version and one translated into the single line format that corresponds with the "VIEW" table and can be used for "syncing"

Bug #54900 was marked as a duplicate of this one.

Synchronization code should be fixed to normalize both server and user SELECT statement when comparing (ignoring spaces and newlines between tokens), but should not touch the user input SELECT statement if no changes are detected.
On the other hand, if the user chooses to apply the server version of the View to the model, the formatting returned by the server will be used and I don't think there is a way around that.

Bug #54980 was marked as a duplicate of this one.

Marked bug #54981 as duplicate

bug #56493 marked as duplicate, although that one is about triggers.
Triggers should undergo whitespace normalization the same way

Bug #56790 was marked as a duplicate of this one.

Bug #56971 was marked as a duplicate of this one.

Please, fix this bug!

whitespaces and linebreaks are not taken into account on comparison of view code anymore.
but there is the remaining problem that server often does return a reformatted (or completed) statement than was used to create the initial view - in these cases, there will still be differences detected.

e.g. the view mentioned in initial description will be created and returned by server like this (without the linebreaks):

CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `test`.`view1` AS
select `test`.`table1`.`idtable1` AS `idtable1` 
from `test`.`table1` 
where (`test`.`table1`.`idtable1` = 6) 
order by `test`.`table1`.`idtable1`

An entry has been added to the 5.2.31 changelog:

Formatting of view code entered in the View Editor was not retained after synchronization of the model with the server.