Bug 117090 - EDITING: Firebird: migration: View definitions are lost from embedded hsql files using functions unknown to Firebird
Summary: EDITING: Firebird: migration: View definitions are lost from embedded hsql fi...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: x86-64 (AMD64) Linux (All)
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected
: 125161 (view as bug list)
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-18 18:05 UTC by Robert Großkopf
Modified: 2019-05-07 18:35 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Error message for Concat (6.48 KB, image/png)
2018-04-18 20:25 UTC, Gerhard Weydt
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2018-04-18 18:05:18 UTC
Download https://bugs.documentfoundation.org/attachment.cgi?id=141456
Open the attached database with 6.1.0.0.alpha0
Try to migrate the database to Firebird.
Migration fails, because Firebird doesn't know anything about the function CONCAT.

If you save the data after migrating the code for the view is lost.

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.
...
Comment 1 Gerhard Weydt 2018-04-18 20:25:33 UTC
Created attachment 141471 [details]
Error message for Concat
Comment 2 Gerhard Weydt 2018-04-18 20:29:42 UTC
I created a comment, but it was lost when I added the attachment (I still have to learn how bugzilla works). So here is again my comment which the attachment created in Comment 1 belongs to:
I can confirm the problem with Concat independently for one of my own databases in
Version: 6.1.0.0.alpha0+
Build ID: 9c4eaa7b81a40d97fe49b85272b40bfeaaf44f86
CPU threads: 4; OS: Windows 10.0; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-04-16_03:31:36
Locale: de-DE (de_DE); Calc: group
Comment 3 Xisco Faulí 2018-04-19 10:22:13 UTC
The error message started to be shown after

author	Tamas Bunth <tamas.bunth@collabora.co.uk>	2018-04-14 20:56:26 +0200
committer	Tamás Bunth <btomi96@gmail.com>	2018-04-15 21:29:02 +0200
commit	098b84ff26212d5bee30f16848fac90d061e8b14 (patch)
tree	4726d2fe470207e48222621129bbbe886601b073
parent	22cfac8edeb3371e458b9f9533478c6e5a81287e (diff)
tdf#116954 dbahsql: migrate views too
Comment 4 Tamas Bunth 2018-05-24 10:45:10 UTC Comment hidden (no-value)
Comment 5 Tamas Bunth 2018-05-24 10:45:51 UTC Comment hidden (no-value)
Comment 6 Julien Nabet 2018-05-30 14:36:47 UTC
Just for curiosity, would it be ok to detect the presence of CONCAT (as a function, not as a variable called CONCAT1 for example) and display a warning message inviting the user to change CONCAT syntax to || syntax before migrating?

Indeed, replacing CONCAT syntax with || syntax is not easy since:
- you can have imbricated CONCAT => recursive algo
- you can have some spaces before/after concat, parenthesis, ...
- you can have values named <CONCAT something>
- you must find matching end parenthesis of the beginning parenthesis of the CONCAT
I found some algo which are not straightforward and that they don't even take into account parenthesis as values + deal with values declared with quote or double quotes: eg :
CONCAT(a, ')', CONCAT(b, ")", c))

Unless there's an existing parsing mechanism to call which would already know how to deal with all this...
Comment 7 Lionel Elie Mamane 2018-05-30 14:44:34 UTC
Yes, there is an existing parsing mechanism!
connectivity/source/parse/
Comment 8 Xisco Faulí 2018-06-06 19:24:06 UTC
I'm wondering, if we should, at least, warn the users the view definitions will be lost if they try to save the database when an error has been encountered?
Comment 9 Robert Großkopf 2018-06-06 19:43:57 UTC
(In reply to Xisco Faulí from comment #8)
> I'm wondering, if we should, at least, warn the users the view definitions
> will be lost if they try to save the database when an error has been
> encountered?

Better would be: Save all views, which couldn't be transformed to Firebird-views, as queries in direct SQL-mode. So users could change the functions, which are working in another way/with other name in Firebird. Afterwards you could be able to take this queries and define the views.
Comment 10 Gerhard Weydt 2018-06-06 20:28:54 UTC
Looking at all the bug reports regarding the migration to Firebird that have already been entered one must conclude that it will be impossible to cover all eventualities that might be happening at the conversion. But it seems that the conversion strategy still supposes that this can be done.
The impossibility of arriving at a 100 % conversion to Firebird should be recognised. The efforts to get at near to 100 % as possible should certainly be continued, because it is indispensable that the migration works generally. But we must face the fact that there will be some cases not covered by the migration routines, no matter how long we try to complete the migration code; and I think there is not even time to try to arrive at a "complete" process, because there is some deadline for the migration to Firebird.
So there should be an easy way to access all the information of features that caused problems when migrating a database document from HSQLDB to Firebird, such that it is quite easy to supply the (hopefully rare) missing features to the migrated Firebird database manually.
As I have not enough insight into the details of the migration process, I can only offer some general ideas:
1) The easiest way (regarding implementation) would be to supply a very serious warning, when a database document should be converted, to always do this, first of all, with a copy, or to save a copy beforehand, so that one always has an original document to refer to, in case of problems.
2) Unfortunately, some people might ignore even this serious warning, so a prompt to save the old state with a differnt name would perhaps be helpful.
3) I understand that the old HSQLDB is still stored within the database document aftre the migration. If so, then proposal 2) would perhaps be unnecessary, but only if there is an easy way to access this content, and the information how to access it is adequately published. I don't know where, but the migration process should supply a link or information where to find it.
Comment 11 Drew Jensen 2018-06-06 22:11:13 UTC
(In reply to Gerhard Weydt from comment #10)
> Looking at all the bug reports regarding the migration to Firebird that have
> already been entered one must conclude that it will be impossible to cover
> all eventualities that might be happening at the conversion. 
...
> 3) I understand that the old HSQLDB is still stored within the database
> document aftre the migration. If so, then proposal 2) would perhaps be
> unnecessary, but only if there is an easy way to access this content, and
> the information how to access it is adequately published. I don't know
> where, but the migration process should supply a link or information where
> to find it.

So, I would see that as  this page on the wiki https://wiki.documentfoundation.org/Documentation/FirebirdMigration 

It is a good spot to cover specifics as to what is handled by the Migratioin Assistant at the moment and as it releases. (and what is not)

With beta the current build and RC coming up this is good time to update that, with the RC there should be some specificity to that, with reference to major open issues but otherwise what is expected in release.

That page is in the structure for nl groups to have translate if they choose.
Comment 12 Gerhard Weydt 2018-06-06 22:45:50 UTC
Hi Drew,

I know you are a frequent contributor on these pages as regards database migration, and I assume that you are a native english speaker (which I am not). I appreciate your contributions on errors regarding the migration from HSQLB to Firebird. But I simply don't understand your remarks.
The link you provide doesn't add anything to the fundamental question I posed. And the remaining text is simply not intelligible for me, there seem to be too much typing errors etc.
For example: "That page is in the structure for nl groups to have translate if they choose." This sentence is in my opinion grammatically incorrect in such a high degree that I simply can't understand it.
Comment 13 Drew Jensen 2018-06-06 22:59:13 UTC
(In reply to Gerhard Weydt from comment #12)
> Hi Drew,

Howdy Gerhard,

sorry, that was poorly typed on my part.

What I intended is best stated as, I will try to assemble the type of information you asked about on that page. 

The final thought was just a mention of the wiki native language support, given how it is structured.

In fact I was looking at that page earlier today with the same thoughts, which are that it is time to put some specifics as to what is working and not.  

Looking at the release schedule on the wiki I note a possible beta 2 on the 11th of this month, am using that for my working target for updating the Migration Assistant page with where it is at, by that date.
Comment 14 Justin L 2018-12-07 06:30:15 UTC
My migration of attachment 147302 [details] has the same problem mentioned by Robert, so I thought I would include it as a sample. The wiki page mentioned in Comment 11 is very helpful.

Better error messages certainly would be helpful. Last62onMaster's MORE error details gives me a box with two errors that I can't select and the description is blank.
Comment 15 Justin L 2018-12-07 09:47:25 UTC
(In reply to Justin L from comment #14)
> My migration of attachment 147302 [details] has the same problem
The wiki documentation nicely gave me all the information I needed to convert CONCAT and SUBSTRING to mutually compatible forms, and with that the migration succeeds. A few bugs in number conversion, but overall a fairly impressive achievement at this point.
Comment 16 Julien Nabet 2019-05-07 18:35:03 UTC
*** Bug 125161 has been marked as a duplicate of this bug. ***