Bug 46841 - EDITING: SQL of queries looses returns when saving and reopening
Summary: EDITING: SQL of queries looses returns when saving and reopening
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 122212 (view as bug list)
Depends on:
Blocks: Database-Queries
  Show dependency treegraph
 
Reported: 2012-03-01 10:33 UTC by Robert Großkopf
Modified: 2023-05-14 06:50 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2012-03-01 10:33:03 UTC
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.

Robert
Comment 1 sasha.libreoffice 2012-05-29 05:07:12 UTC
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".
Comment 2 sasha.libreoffice 2012-05-29 05:12:35 UTC
@ Lionel
Greetings
What do You thing about this idea?
Comment 3 Lionel Elie Mamane 2012-05-30 03:33:33 UTC
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.

OR/AND

 - 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
   WHERE condition1
   GROUP BY "t"."foo"
   HAVING condition2

   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) 
               AND long3

  but avoid

   WHERE (long1 OR
               long2) AND long3

   Also OK:

   WHERE (long1 OR
                long2)
              AND long3
Comment 4 Juetho 2012-11-27 10:27:16 UTC
Reproduced using LibO Version 3.6.3.2 (Build ID: 58f22d5) on Windows 7 Home Premium 64-bit

@Lionel
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
Comment 5 Alex Thurgood 2015-01-03 17:40:54 UTC Comment hidden (no-value)
Comment 6 michael 2015-05-04 16:39:39 UTC
Still the same in 4.1.6.2. 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.
Comment 7 Alex Thurgood 2018-12-20 09:48:30 UTC
*** Bug 122212 has been marked as a duplicate of this bug. ***
Comment 8 Jeff D. Hanson 2023-05-13 20:55:05 UTC
Still occurs in 7.5.3.2 on Win10 with loss of all newlines.

Also the conversion of identifier delimiting square brackets to grave accents (backticks) is annoying when working with Access databases (*.mdb, *.accdb) through JDBC/UCanAccess.
Comment 9 Robert Großkopf 2023-05-14 06:50:34 UTC
(In reply to Jeff D. Hanson from comment #8)
> 
> Also the conversion of identifier delimiting square brackets to grave
> accents (backticks) is annoying when working with Access databases (*.mdb,
> *.accdb) through JDBC/UCanAccess.

Pleas don't mix different problems in one bug description. This is asking for an enhancement for returns in SQL-code, not for different delimiter.