Bug 116954 - EDITING: firebird: migration: View definitions are lost on conversion from embedded hsql files
Summary: EDITING: firebird: migration: View definitions are lost on conversion from em...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: All All
: high major
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-12 00:26 UTC by Drew Jensen
Modified: 2018-04-18 18:05 UTC (History)
4 users (show)

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


Attachments
test file with hsql embedded data with view definition (3.45 KB, application/vnd.oasis.opendocument.database)
2018-04-12 00:26 UTC, Drew Jensen
Details
test file with two views (5.33 KB, application/vnd.oasis.opendocument.database)
2018-04-18 12:15 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:26:01 UTC
Created attachment 141294 [details]
test file with hsql embedded data with view definition

Tested with Libo6.1alpha (daily)

Open attached file, created with Libo6.0.4 embedded hsql data and a single View.

Check the tables, the View was not migrated.

I would of expected for the view definition do migrate just as the single query in that test file did.
Comment 1 Robert Großkopf 2018-04-12 06:19:00 UTC
Could confirm the buggy behavior.
View hasn't been created by the automatic migration from HSQLDB to Firebird with 
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

Tested with OpenSUSE 42.3 64bit rpm Linux
Comment 2 Commit Notification 2018-04-15 19:30:01 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=098b84ff26212d5bee30f16848fac90d061e8b14

tdf#116954 dbahsql: migrate views too

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 3 Drew Jensen 2018-04-18 12:14:05 UTC
Verified with simple views.

Views that use hsqldb specific features fail, which fails the entire migration.

Not sure what could be done about that, other than having the users change the views before the migration. 

This bug should be closed verified though. 

I've attached another test file. This one has two views:

vEventReservations (simple view, migrates no problem)

vEventCustomers (this view uses the CONCAT function and fails to migrate)
Comment 4 Drew Jensen 2018-04-18 12:15:37 UTC
Created attachment 141456 [details]
test file with two views

the attached file has the two views.
With vEventCustomers the file will fail migration.
Remove vEventCustomers and the file will migrate with vEventReservations included.
Comment 5 Robert Großkopf 2018-04-18 17:28:25 UTC
(In reply to Drew Jensen from comment #3)
> 
> Views that use hsqldb specific features fail, which fails the entire
> migration.
> 
> Not sure what could be done about that, other than having the users change
> the views before the migration. 
> 
> vEventCustomers (this view uses the CONCAT function and fails to migrate)

So the view definitions aren't completely migrated. We have to open another bug for views, which will fail.

There are different possibilities to change this behavior:
→ Views, which fail in Firebird, should be saved as queries. So the code isn't lost.
This is the simplest version.
→ Views with functions, that aren't defined with that specific name in Firebird, but will work with functions of Firebird the same way, should be "translated" to the new functions:
CONCAT doesn't work. || will work.
LCASE doesn't work. LOWER will work.
...