Bug Hunting Session
Bug 116953 - EDITING: UI: Firebird: Migration: Base fails to call QueryEditor for defined database view
Summary: EDITING: UI: Firebird: Migration: Base fails to call QueryEditor for defined ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.2.5.1 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 124262 (view as bug list)
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2018-04-12 00:13 UTC by Drew Jensen
Modified: 2019-03-22 13:00 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
test file for view editing (4.49 KB, application/vnd.oasis.opendocument.database)
2018-04-12 00:13 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2018-04-12 00:13:09 UTC
Created attachment 141293 [details]
test file for view editing

Checked with Libo6.0.4 (daily) and LibO6.1 (daily)

Open the attached file / tables.

Either right click on the single view (vTasksWithEmptyEndDates)
or
with the view selected choose the menu item 'edit'

The pop up menu fails to offer 'Edit in SQL view' and the drop down menu has it grayed out.

Select edit for the view and the dialog Table Editor dialog is opened instead of the Query Editor.
Comment 1 Robert Großkopf 2018-04-12 05:38:40 UTC
Could confirm the table-editor is opened instead of the query-editor if you want to edit a view in 
Version: 6.1.0.0.alpha0+
Build-ID: dc823f5fa4a5d2eca56297b9045e5962536c00f9
CPU-Threads: 4; BS: Linux 4.4; UI-Render: Standard; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-04-10_23:32:35

No possibility to edit the view any more. Only creating a new view will work.

Tested with OpenSUSE 42.3 64bit rpm Linux
Comment 2 Xisco Faulí 2018-04-17 13:27:48 UTC
Hello Drew,
Did it work in the past?

I've tested in

Version: 5.3.0.0.alpha1+
Build ID: 4136757b4e51c4e6f7cb4132c95538a7f831ef2c
CPU Threads: 4; OS Version: Linux 4.13; UI Render: default; VCL: gtk3; Layout Engine: new; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group

and the 'Edit in SQL view' option is greyed out...
Comment 3 Robert Großkopf 2018-04-17 14:07:04 UTC
(In reply to Xisco Faulí from comment #2)
> Hello Drew,
> Did it work in the past?
> 
> I've tested in
> 
> Version: 5.3.0.0.alpha1+
> Build ID: 4136757b4e51c4e6f7cb4132c95538a7f831ef2c
> CPU Threads: 4; OS Version: Linux 4.13; UI Render: default; VCL: gtk3;
> Layout Engine: new; 
> Locale: ca-ES (ca_ES.UTF-8); Calc: group
> 
> and the 'Edit in SQL view' option is greyed out...

Didn't work with Firebird since the beginning. Tested also with Firebird in LO 5.2.5.1. (with internal Firebird 2.5). Its the same buggy behavior. Views could never be edited with firebird any more after saving. If you try to create a view through the GUI from a query you get a popup "Couldn't found the tableformat". The view will be created but will only be shown after refreshing the tables.
If you try to edit the views every time the tableeditor appears instead of the queryeditor.

Remark: Editing of a view of HSQLDB works correct with this versions of LO.

All tested with OpenSUSE 42.3 and different LO-Versions (5.2.5.1, 5.3.0, 6.0.3 ...)
Comment 4 Robert Großkopf 2018-04-17 14:17:02 UTC
Seems impossible to edit a created view with Firebird:
https://bugs.documentfoundation.org/show_bug.cgi?id=76072#c6
Comment 5 Robert Großkopf 2018-04-17 18:45:41 UTC
(In reply to robert from comment #4)
> Seems impossible to edit a created view with Firebird:
> https://bugs.documentfoundation.org/show_bug.cgi?id=76072#c6

Have tried a little bit more:
Tools > SQL

CREATE OR ALTER VIEW "viewTest" AS SELECT * FROM "table" WHERE "ID" > 4

Works. A View will be created. But LO will show this view (after refreshing the tables) as table. I could see it is a view, because I change something in "table" and is has been changed also in the view.
Now

CREATE OR ALTER VIEW "viewTest" AS SELECT * FROM "Leser" WHERE "ID" < 5

Works. The view with the same name could be changed.

You could get the code for the view with

SELECT RDB$RELATION_NAME, RDB$VIEW_SOURCE FROM RDB$RELATIONS WHERE RDB$VIEW_SOURCE IS NOT NULL

So it must be possible to load this code to the GUI-query-editor, change the code and put it back to the database.
Comment 6 Julien Nabet 2018-06-10 15:45:11 UTC
With Firebird, the pb comes from here:
   1748         Reference< XAlterView > xAsAlterableView;
   1749         if ( xViews.is() && xViews->hasByName( _rTableOrViewName ) )
   1750             xAsAlterableView.set( xViews->getByName( _rTableOrViewName ), UNO_QUERY );
   1751 
   1752         bIsAlterableView = xAsAlterableView.is();
see https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/app/AppController.cxx#1748

#0  0x00007fffc9b6c96b in dbaui::OApplicationController::impl_isAlterableView_nothrow(rtl::OUString const&) const (this=0x555557c9ed80, _rTableOrViewName="View1")
    at /home/julien/lo/libreoffice/dbaccess/source/ui/app/AppController.cxx:1752
#1  0x00007fffc9b65f7f in dbaui::OApplicationController::GetState(unsigned short) const (this=0x555557c9ed80, _nId=12317)
    at /home/julien/lo/libreoffice/dbaccess/source/ui/app/AppController.cxx:773
#2  0x00007fffc9c333a4 in dbaui::OGenericUnoController::ImplBroadcastFeatureState(rtl::OUString const&, com::sun::star::uno::Reference<com::sun::star::frame::XStatusListener> const&, bool) (this=0x555557c9ed80, _rFeature=".uno:DBEditSqlView", xListener=uno::Reference to (framework::MenuBarManager *) 0x5555582bd3d0, _bIgnoreCache=true)
    at /home/julien/lo/libreoffice/dbaccess/source/ui/browser/genericcontroller.cxx:397

Except in hsqldb, searching xAlterVIew returns nothing for Firebird or other DB types https://opengrok.libreoffice.org/search?project=core&q=XAlterView&defs=&refs=&path=&hist=&type=
So it seems it's not possible to edit views in those (Mysql, Postgresql...) or perhaps I missed something?
Comment 7 Drew Jensen 2018-06-10 16:20:59 UTC
(In reply to Julien Nabet from comment #6)
> With Firebird, the pb comes from here:
>    1748         Reference< XAlterView > xAsAlterableView;
>    1749         if ( xViews.is() && xViews->hasByName( _rTableOrViewName ) )
>    1750             xAsAlterableView.set( xViews->getByName(
> _rTableOrViewName ), UNO_QUERY );
>    1751 
>    1752         bIsAlterableView = xAsAlterableView.is();
> see
> https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/app/
> AppController.cxx#1748
> 
> #0  0x00007fffc9b6c96b in
> dbaui::OApplicationController::impl_isAlterableView_nothrow(rtl::OUString
> const&) const (this=0x555557c9ed80, _rTableOrViewName="View1")
>     at
> /home/julien/lo/libreoffice/dbaccess/source/ui/app/AppController.cxx:1752
> #1  0x00007fffc9b65f7f in dbaui::OApplicationController::GetState(unsigned
> short) const (this=0x555557c9ed80, _nId=12317)
>     at
> /home/julien/lo/libreoffice/dbaccess/source/ui/app/AppController.cxx:773
> #2  0x00007fffc9c333a4 in
> dbaui::OGenericUnoController::ImplBroadcastFeatureState(rtl::OUString
> const&,
> com::sun::star::uno::Reference<com::sun::star::frame::XStatusListener>
> const&, bool) (this=0x555557c9ed80, _rFeature=".uno:DBEditSqlView",
> xListener=uno::Reference to (framework::MenuBarManager *) 0x5555582bd3d0,
> _bIgnoreCache=true)
>     at
> /home/julien/lo/libreoffice/dbaccess/source/ui/browser/genericcontroller.cxx:
> 397
> 
> Except in hsqldb, searching xAlterVIew returns nothing for Firebird or other
> DB types
> https://opengrok.libreoffice.org/
> search?project=core&q=XAlterView&defs=&refs=&path=&hist=&type=
> So it seems it's not possible to edit views in those (Mysql, Postgresql...)
> or perhaps I missed something?

You mean that LibreOffice (UNO) dbaccess is not setup to do so yet. Firebird (MySQL, Postgress also) engine certainly supports ALTER VIEW (and "CREATE OR REPLACE") command. 

(Going out on a limb here) it seems  there is a chunk of functionality simply never coded for the firebird, or MySQL, or the Postgress drivers. For the external RDMBS it isn't really that big a deal as the DBA tools for those are rather extensive and Base is rather weak with what it supports. But for the embedded engines there is no other choice, it is the Base UI or SQL commands in the SQL window.
Comment 8 Drew Jensen 2018-06-10 16:55:49 UTC
one more comment.

I'm a bit surprised to hear the MySQL and PostgresSQL connectors don't support this. 

The SQl2003 standard included a defined the INFORMATION_SCHEMA, which was expanded IIRC in 2005 and 2008. The INFORMATION_SCHEMA would give a standardized access to things such as view definitions. 

HSQLdb was one of the early adopters of the standard. PostgreSQL, MS SQLServer and MySQL all support it. 

Firebird 3.0 does not conform to the standard in this respect. 

I believe the decision was to support the 2003 standard as a baseline. Which meant that for those RDBMS(s) that didn't conform those sdbc drivers would need to recreate the code for access to those artifacts. The MySQL and PostregsSQL should of been able to use the HSQLdb code, for these types of things, almost unchanged. At least I believe that was the plan.
Comment 9 Julien Nabet 2018-06-10 18:47:35 UTC
(In reply to Drew Jensen from comment #8)
> one more comment.
> 
> I'm a bit surprised to hear the MySQL and PostgresSQL connectors don't
> support this. 
I'm not so affirmative. I just noticed xAlterVIew isn't present in Mysql or PostGreSQL part (according to Opengrok) but I didn't test editing a view on these DBs.

About your 2 last comments about missing code, the problem is Base has only 1 core dev (Lionel) + Tamas whereas it's a big part.
Indeed, Base has more dependencies than other LO components: 
- external drivers of DBs
- external package for reports (JFreereport)
- Java/JNI
Moreovover every feature for pivot DB (hsqldb and soon Firebird) should ideally work for other DBs. It's not sot easy, just consider migration part from hsqldb to Firebird. Also some DBs types can only be tested on specific envs (Macab on MacOs)
In brief, as long as Base doesn't attract more devs, I think the situation won't evolve a lot.
It may explain why some people would like to remove Base from LO. (I don't know if it's still the Tor's opinion, see https://blog.documentfoundation.org/blog/2011/07/21/developer-interview-tor-lillqvist/)

Sorry for this long digression.
Comment 10 Drew Jensen 2018-06-11 11:58:16 UTC
(In reply to Julien Nabet from comment #9)
> (In reply to Drew Jensen from comment #8)
> > one more comment.
> > 
> > I'm a bit surprised to hear the MySQL and PostgresSQL connectors don't
> > support this. 
> I'm not so affirmative. I just noticed xAlterVIew isn't present in Mysql or
> PostGreSQL part (according to Opengrok) but I didn't test editing a view on
> these DBs.
> 
> About your 2 last comments about missing code, the problem is Base has only
> 1 core dev (Lionel) + Tamas whereas it's a big part.
> Indeed, Base has more dependencies than other LO components: 
> - external drivers of DBs
> - external package for reports (JFreereport)
> - Java/JNI
> Moreovover every feature for pivot DB (hsqldb and soon Firebird) should
> ideally work for other DBs. It's not sot easy, just consider migration part
> from hsqldb to Firebird. Also some DBs types can only be tested on specific
> envs (Macab on MacOs)
> In brief, as long as Base doesn't attract more devs, I think the situation
> won't evolve a lot.
> It may explain why some people would like to remove Base from LO. (I don't
> know if it's still the Tor's opinion, see
> https://blog.documentfoundation.org/blog/2011/07/21/developer-interview-tor-
> lillqvist/)
> 
> Sorry for this long digression.

No problem and thanks for the information. I have one question; What is pivot DB? (just searched for that phrase and I can't find anything on it).
Comment 11 Julien Nabet 2018-06-11 12:12:20 UTC
(In reply to Drew Jensen from comment #10)
> ...
> No problem and thanks for the information. I have one question; What is
> pivot DB? (just searched for that phrase and I can't find anything on it).
Sorry for the confusion, I meant "by default" DB type in LO.
Comment 12 Alex Thurgood 2019-03-22 12:08:51 UTC
*** Bug 124262 has been marked as a duplicate of this bug. ***
Comment 13 Drew Jensen 2019-03-22 12:14:34 UTC
Should this be changed to a RFE asking to remove the GUI menus offering to edit views when Firebird is the rdbms? 

It is the only viable solution that I can see.
Comment 14 shar 2019-03-22 13:00:01 UTC
Sadly. It's frustrating when the functionality gets smaller (compared to previous versions)