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

[12 Mar 2010 15:44] Martin Pirringer
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"
[30 Jun 2010 12:29] Valeriy Kravchuk
Bug #54900 was marked as a duplicate of this one.
[30 Jun 2010 13:10] Alfredo Kojima
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.
[6 Jul 2010 5:51] Valeriy Kravchuk
Bug #54980 was marked as a duplicate of this one.
[6 Jul 2010 13:35] Alfredo Kojima
Marked bug #54981 as duplicate
[14 Sep 2010 5:10] Alfredo Kojima
bug #56493 marked as duplicate, although that one is about triggers.
Triggers should undergo whitespace normalization the same way
[15 Sep 2010 13:52] Valeriy Kravchuk
Bug #56790 was marked as a duplicate of this one.
[23 Sep 2010 17:01] Valeriy Kravchuk
Bug #56971 was marked as a duplicate of this one.
[18 Oct 2010 11:36] sf sf1
Please, fix this bug!
[8 Dec 2010 21:53] Johannes Taxacher
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`
[31 Dec 2010 15:44] Tony Bedford
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.