Bug 86315 - EDITING: Formatting of queries should be saved by base
Summary: EDITING: Formatting of queries should be saved by base
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 90360 116567 144701 (view as bug list)
Depends on:
Blocks: Database-Queries
  Show dependency treegraph
 
Reported: 2014-11-15 17:56 UTC by Robert Großkopf
Modified: 2021-10-05 11:52 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
test db with querydef containing comment (5.05 KB, application/vnd.oasis.opendocument.database)
2018-04-12 18:55 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2014-11-15 17:56:11 UTC
There are some asks for enhancement which couldn't be solved, because the only part of a query, which is been saved in the *.odb-file, is the name and the pure SQL-code.
This is saved in «content.xml»:
<db:queries>
<db:query
db:name="Verkauf"
db:command="SELECT * FROM &quot;Verkauf&quot"/>
</db:queries>
No more informations were saved anywhere in the *.odb-file.

So you 
- couldn't add comments to the SQL-code and save them
- couldn't format the width or something eles of a column and save it
- couldn't set a column invisible and save it

All this seems to work, but when saving the query, close it an open it again it isn't there any more.
Comment 1 Alex Thurgood 2014-12-15 15:12:14 UTC
This is an enhancement request that goes back to early OOo2 days, but was never implemented.

Confirming.
Comment 2 Alex Thurgood 2015-01-03 17:40:35 UTC Comment hidden (no-value)
Comment 3 Robert Großkopf 2015-03-31 19:00:42 UTC
*** Bug 90360 has been marked as a duplicate of this bug. ***
Comment 4 Alex Thurgood 2018-03-23 10:12:59 UTC
*** Bug 116567 has been marked as a duplicate of this bug. ***
Comment 5 Drew Jensen 2018-04-12 18:55:45 UTC
Created attachment 141314 [details]
test db with querydef containing comment
Comment 6 Drew Jensen 2018-04-12 18:59:41 UTC
So I tried this with Libo6.1 Alpha (daily builds).

It looks to me the problem is that the falg for a query definition requiring 'Run Direct SQL' is not being saved and that is the problem.

To see what I mean grab the test db I attached.

Open the query definition with 'Edit SQL view'
You will see that the comment I added was saved.
Try to run the query and it fails.
Now click on 'Run SQL Command Directly'
Run the query again and it passes.
Save the query.

The 'Run SQL Command Directly' flag is not sticking with the QueryDef.
Comment 7 Drew Jensen 2018-05-25 20:07:32 UTC
Checked this with the latest 6.1 daily build.

The sql direct flag is being stored properly and when that is set all the formatting and commenting is retained.

I would say this works.
Comment 8 Drew Jensen 2018-05-25 20:19:24 UTC
The one area that is not possible is the ability to set the column widths, or anything to do with the grid layout on screen when you run the query and have that saved. IIRC that is created at runtime so there is nothing to save there - that would be a major enhancement IMO.


However all of that functionality is already supplied in a Form using a table grid control (three clicks is all you need to generate that from a querydef) 
- in the table grid control you can rearrange the column order, adjust the column widths, hide columns or even add columns. Set any of those at design time and it is retained.
Comment 9 Robert Großkopf 2018-05-26 07:41:56 UTC
(In reply to Drew Jensen from comment #7)
> Checked this with the latest 6.1 daily build.
> 
> The sql direct flag is being stored properly and when that is set all the
> formatting and commenting is retained.
> 
> I would say this works.

Isn't a bug about code, which will be stored with direct SQL. It's about formatting, comments ...
If you save a Base-file you will save all the formatting of the tables, also the hiding of columns, for example. This does work for tables, but doesen't work for queries.
If you type code with comments its will work in GUI. You could input data in the query. If you close and reopen such a edited query the comments are lost, the breaks are lost ...
You could change to direct SQL, but there is no possibility to
- input data in query
- also to format columns
- also to hide columns.
Comment 10 Drew Jensen 2019-04-25 16:09:46 UTC
(In reply to Robert Großkopf from comment #9)
> (In reply to Drew Jensen from comment #7)
> > Checked this with the latest 6.1 daily build.
> > 
> > The sql direct flag is being stored properly and when that is set all the
> > formatting and commenting is retained.
> > 
> > I would say this works.
> 
> Isn't a bug about code, which will be stored with direct SQL. It's about
> formatting, comments ...
> If you save a Base-file you will save all the formatting of the tables, also
> the hiding of columns, for example. This does work for tables, but doesen't
> work for queries.
> If you type code with comments its will work in GUI. You could input data in
> the query. If you close and reopen such a edited query the comments are
> lost, the breaks are lost ...
> You could change to direct SQL, but there is no possibility to
> - input data in query
> - also to format columns
> - also to hide columns.

Right, I understand now.

So, this would be something that should be part of an ODF specification update, right?

Granted if LibreOffice just did it as a LO extended flavor of ODF 1.2 it's doubtful too many folks would howl but standards are standards so maybe they should (softly). 

No idea how one moves to do that, to request an ODF specification like that. I suppose it might open up some thought as what could be done with that kind of data when a query is used as the data source to other objects in the ODF spec, such as dataforms, perhaps treat the settings as a default (template) for consumer controls attached to the dataform.
Comment 11 Robert Großkopf 2021-09-25 07:37:53 UTC
*** Bug 144701 has been marked as a duplicate of this bug. ***
Comment 12 flywire 2021-09-25 08:46:16 UTC
(In reply to Drew Jensen from comment #10)

Formatted queries, external databases joins, and macros are the extent of a database for most analysts who have no need for forms or reports. Export to a wordprocessor or spreadsheet to tart up data.

So another decade before a basic usability feature in MS-Access V1 is considered? You have to be kidding!

Writer and calc are always enhanced to provide 'standardisation' with MS-Office. Base is bad enough now, at least fork a fix for 'testing', run with /enhancements option if need be.
Comment 13 flywire 2021-09-25 08:47:52 UTC
Forgot to add, it is essential for adhoc joins to views.
Comment 14 flywire 2021-09-26 13:41:14 UTC
Possible workaround: BG64-BaseGuide.pdf, Chapter 5 Queries
More rapid access to queries using table views, Tip:
...the formatting of columns in a view is retained when the database is closed, unlike columns in a query.

(I didn't realise views were implemented.)
Comment 15 flywire 2021-10-03 12:53:07 UTC
View column formatting is not retained in Base V7.2, despite the guide.

Users could run a macro could manage this functionality without adding to ODF specification:

* save column attributes to system table on close query
* apply attributes from system table on open query

Functionality models would probably be calc, writer tables or MS-Access.

==========

Version: 7.2.0.3 (x64) / LibreOffice Community
Build ID: 2a7ea282da28d665a7dc086360567b4aea27bf08
CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: threaded
Comment 16 flywire 2021-10-05 11:52:19 UTC
(In reply to Drew Jensen from comment #10)
> So, this would be something that should be part of an ODF specification
> update, right?

*Where* does ODF Spec says query formatting can't be saved?

Queries are displayed in a table view and the same display attributes can be applied - see spec: https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part3-schema/OpenDocument-v1.3-os-part3-schema.html#element-db_query 12.28 -> 12.35 -> 19.80 db:style-name


(In reply to Robert Großkopf from comment #9)
> If you type code with comments...
> If you close and reopen such an edited query the comments are lost

Why? *Where* does it say characters in a window saved to a file should filter out comments? Especially for Direct SQL - Base editor should not change it.


(In reply to flywire from comment #15)
> View column formatting is <s>not</s> retained in Base V7.2, <s>despite the guide.</s>