Long queries could be edited better by setting a return at some points of the query. This queries could be created in SQL-Mode. When I save them in "direct SQL-Mode" an reopen them the returns in the SQL-code are already there. When I dont save them in "direct SQL-Mode" and reopen them the returns in the SQL-code have gone.
You could say: Why don't you save them all in "direct-SQL-Mode"? Sorting and setting of filters won't work in this mode.
Thanks for new idea
reproduced in 3.5.3 on Fedora 64 bit
Carriage returns saved only in "Native SQL" mode (switch to SQL design, then appears "Native SQL" button). All Carriage returns disappears after switch to "Design view".
What do You thing about this idea?
From what I remember from the top of my head, the whole SQL code is regenerated from its abstract "design view"; not only are carriage returns removed, but the whole code is canonicalised: column names are escaped (within double quotes), order of clauses is fixed, a single fixed choice is made among syntactical variants of SQL, etc.
I see two different reasonable ways to handle this:
- If one just switches to design view and back to SQL view, not having done ANY CHANGE in the design view, do not regenerate the SQL code, just take the exact string as it is.
- when generating SQL code from design view abstraction, put newlines at reasonable places; for example between clauses, like that:
SELECT "t"."foo", "t"."bar", "t"."baz", "t"."qux"
FROM table AS t
GROUP BY "t"."foo"
In this solution, there are still details to be filled in, such as whether and where to put newlines within clauses: e.g. mutliple joins in FROM clause, long WHERE clause (put newlines at "high" points of the boolean expression, respecting the structure of the formula), ...
E.g. in "WHERE (long1 OR long2) AND long3", but a newline before/after the AND as in:
WHERE (long1 OR long2)
WHERE (long1 OR
long2) AND long3
WHERE (long1 OR
Reproduced using LibO Version 18.104.22.168 (Build ID: 58f22d5) on Windows 7 Home Premium 64-bit
IMHO it's better to insert more newlines than less to create or save clearly arranged SQL code. If you need regeneration of SQL code, you should insert newlines at the following places:
* before each FROM, WHERE, GROUP BY, HAVING, ORDER BY, UNION
* after UNION
* before each JOIN:
** [INNER] JOIN
** LEFT | RIGHT [OUTER] JOIN
** CROSS JOIN
* before each AND in WHERE clause
* after each comma in the column list
* after a closing bracket in WHERE clause
** but not between two closing brackets like (a AND (b OR c)) OR (d AND e)
* before WHEN and ELSE in CASE...END
The newline should be indented by spaces:
* 0 before SELECT, UNION
* 7 within column list
* 2 before FROM, JOINs
* 2 before WHERE, GROUP BY, HAVING, ORDER BY
* 7 within WHERE clause
There are more conceivable rules, but IMHO not necessary.
Hope this way can be realized in near future. Juergen
Adding self to CC if not already on
Still the same in 22.214.171.124. The entire SQL statement gets reformatted without any line breaks or extra spaces.
I never bother with the query design view but always enter SQL code directly,. Since my queries frequently these tend to be somewhat complex, I format them with line breaks and indentation to make them more legible at least while I create them.
Therefore I'd strongly favor storing the SQL source as plain text, with all formatting preserved, rather than in some abstraction format, when the last edit to the query was in SQL mode.
Anyone who codes is used to that behavior from their IDE or editor of choice, and I'd like to see the same in LO.
*** Bug 122212 has been marked as a duplicate of this bug. ***