[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

Gerhard Weydt  changed:

   What|Removed |Added

 Status|RESOLVED|VERIFIED

--- Comment #23 from Gerhard Weydt  ---
Tested in 6..1 and 6.2. Works as expected.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

Tamas Bunth  changed:

   What|Removed |Added

 Status|NEW |RESOLVED
 Resolution|--- |FIXED

--- Comment #22 from Tamas Bunth  ---
The above commit intends to solve the initial problem described by Gerhard. If
there are further problems (slightly related or not related to this one),
please open a new bug.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #21 from Commit Notification 
 ---
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4f043c0a269ff0bb13d4714dbcda471e47f7c1c1

tdf#117092 dbahsql: handle multiple primary keys

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.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

Commit Notification  changed:

   What|Removed |Added

 Whiteboard||target:6.1.0

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #20 from Drew Jensen  ---
Created attachment 141551
  --> https://bugs.documentfoundation.org/attachment.cgi?id=141551=edit
screen shot comparing keys after drag drop of table hsql->fb

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #19 from Drew Jensen  ---
It may be worth noting, using the migrate function, it failed but I save the
file with a new name, then open the original file in hsql mode a standard drag
and drop of the table from hsql_src->fb_trgt works as expected and you get the
table with data and the same key structure as what is in the original file. So
this nut has been cracked somewhere in the current code once already; right?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #18 from Gerhard Weydt  ---
Inspired by Julien's example in comment 9 I undertook some (or much) more
testing and arrived at two sets of interesting results:
FIRST:
I used (on Windows 10) Tools -> SQL... to get similar results of executed SQL
as Julien.
Creating a primary key for two fields (and as well for a single field, although
I have not covered all test cases for that constellation, too) fails in most
cases due to some issue of case sensitivity. For example: For a table whose
name is already uppercase, but the field names are not, you get:
Column not found: ID in statement [ALTER TABLE TABELLE2  ADD PRIMARY KEY (id,
id_1)]
Mark that the column not found is uppercase whereas it's lowercase in the
statement.
This is, by the way, an example using HSQLDB, showing that it's not a database
issue, but one of the UI of Base.
Using Tools -> SQL... works, though, quite well, if Table and field names (at
least for the key fields) are uppercase.
Now using an all uppercase scenario we finally get a message which helps us on:

1: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE TABLE3 failed
*Column: ID not defined as NOT NULL - cannot be used in PRIMARY KEY constraint
definition
caused by
'ALTER TABLE TABLE3 ADD PRIMARY KEY (ID, ID_1);'

SECOND:
It follows that the crucial problem is the fact that the fields used for a
primary key should be not nullable!
I haven't yet researched if that is a categorical request for a primary key,
generally, but  it seems that LibO requires that.
So if you set "Entry required" to Yes for the fields used for a primary key,
you can define the primary key in the UI of a table, selecting the relevant
field definitions and using the Ctrl-Key and Context menu -> Primary Key.
If you haven't set the "Entry required"-property for the key fields, you will
be prompted by the message: "The column "..." could not be changed. Should the
column instead be deleted and the new format appended?" for each of those
fields, resulting in a deletion of the original field and the addition of a new
one, quite similar, but carrying the attribute "non-nullable. (This it the same
for HSQLDB and Firebird).
If "Entry required" was originally set or you answered Yes to all questions for
deleting and appending, the primary key is created.
If you do this for a HSQLDB-database, the type seems to be implicitly changed
to not nullable.

CONCLUSION:
The usage of Uppercase seems at least not understandable. But his seems to be
restricted to the usage of the SQL dialog.

If no one wants to tackle a complete redesign of database connection (and I
have no reason why it would be better), then
- migrating existing HSQLDB databases to Firebird you would probably have to
set all primary key columns to non nullable.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #17 from rob...@familiegrosskopf.de ---
(In reply to Gerhard Weydt from comment #15)
> I created a new bug 117048 for the problem that t is not possible to edit
> data if the table has only a unique key, contrary to the statement on save
> that exactly this is allowed.
Wrong bug-number. Right number:
https://bugs.documentfoundation.org/show_bug.cgi?id=117148

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #16 from Gerhard Weydt  ---
(In reply to Julien Nabet from comment #14)
> (In reply to Gerhard Weydt from comment #13)
> ...
> I wasn't talking about LO but Firebird version.
> Last master sources of LO use Firebird 3.0.0 whereas current Firebird
> version is 3.0.3
> The problem is to be able to build Firebird 3.0.3 in LO master sources for
> every env: Linux, Windows and MacOS.

As I said, the use of multiple columns is already described in the manual for
Firebird 2.5, so I don't think it probable that installing Firebird 3.0.3
instead of 3.0.0 can solve the problem. It is much more probable that the
reason must be sought within LibO's connection and handling of Firebird.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #15 from Gerhard Weydt  ---
referring to comment 10:
I created a new bug 117048 for the problem that t is not possible to edit data
if the table has only a unique key, contrary to the statement on save that
exactly this is allowed.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #14 from Julien Nabet  ---
(In reply to Gerhard Weydt from comment #13)
> ...
> Julien: if you do not want to create a new bug report, I will do it, but I
> will be waiting some time, in case Lionel sends a comment.

I wasn't talking about LO but Firebird version.
Last master sources of LO use Firebird 3.0.0 whereas current Firebird version
is 3.0.3
The problem is to be able to build Firebird 3.0.3 in LO master sources for
every env: Linux, Windows and MacOS.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #13 from Gerhard Weydt  ---
I used the latest version available at the time of download, probably April
17th.

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

Julien: if you do not want to create a new bug report, I will do it, but I will
be waiting some time, in case Lionel sends a comment.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

Julien Nabet  changed:

   What|Removed |Added

 CC||lio...@mamane.lu

--- Comment #12 from Julien Nabet  ---
(In reply to Gerhard Weydt from comment #11)
> In reply to comment #9 by Julien:
> ...
> I suggest you create a new bug that Firebird does not allow primary keys
> with more than one column, in contrast to the documentation and also to
> HSQLDB. This bug here could perhaps be referenced, and a comment should be
> added that this is no migration issue, but an issue of Firebird
> implementation in Base.
> ...

I know very few Firebird process and perhaps it's already fixed in the current
version.

Lionel: thought you might be interested in this one or have some thoughts here,
so I put you in cc

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #11 from Gerhard Weydt  ---
In reply to comment #9 by Julien:
According to the Firebird documentation for version 2.5 (!) setting a primary
key with more than one column is definitely possible. And the generated syntax
is also correct.
I suggest you create a new bug that Firebird does not allow primary keys with
more than one column, in contrast to the documentation and also to HSQLDB. This
bug here could perhaps be referenced, and a comment should be added that this
is no migration issue, but an issue of Firebird implementation in Base.
I think that's better than renaming this bug, because one has to read 8
comments before one gets to the point.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #10 from Gerhard Weydt  ---
I should perhaps give some information about this table.
It is a single table isolated from a database because it's the one that caused
trouble. The table describes the relation between the tables Mitglied and Jahr,
hence its name. The relation is n:m, id is the primary key of Mitglied and Jahr
is primary key of table Jahr. I needed a unique key consisting of these two
fields, and as HSQLDB always wants a primary key, I chose not to create an
additional column, but define this unique index as the primary key.
In fact a unique index would have been sufficient. I tried to create a similar
table in 6.1 with Firebird, with a unique index, which is explicitly allowed as
one of the two ways to guarantee uniqueness, but the table isn't editable in
Base. Now this could be the fault of Base and not of Firebird, because it's the
same reaction as with a HSQLDB table without primary key. I will do some more
testing and will probably enter that as another bug report, as it's not
connected with migration.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #9 from Julien Nabet  ---
I tried to create a multiple primary key with a brand new embedded Firebird
file.
I even closed the file and reopened it just to be sure the table is taken into
account but it gave this:

warn:connectivity.firebird:12594:12594:connectivity/source/drivers/firebird/Statement.cxx:121:
isc_dsql_execute failed
warn:connectivity.firebird:12594:12594:connectivity/source/drivers/firebird/Util.cxx:54:
firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE TABLE1 failed
*SQL error code = -607
*Invalid command
*Table TABLE1 does not exist
caused by
'alter table table1
add constraint PK_MY_TABLE
primary key (ID,ID_1);'

Could it be due to the fact that we're behind the current Firebird version?
We use 3.0.0 whereas last one is 3.0.3
I had tried to upgrade but I failed because of Macos pb. Meanwhile, I saw in
Firebird roadmap that Firebird devs were perhaps focused on next release
Firebird 4.0

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #8 from Julien Nabet  ---
(In reply to robert from comment #7)
> (In reply to Julien Nabet from comment #6)
> > 
> > (Attempt to define a second PRIMARY KEY for the same table)
> 
> There must be 2 primary keys, because there are 2 primary keys in the
> original table of HSQLDB:
> "Id" is '0' in every row, "Jahr" changes the values from '2015' up to '2018'.

Yes, I saw this so it means we can't deal with primary key column by column.
Perhaps with an alter table at the end after having processed all the columns?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #7 from rob...@familiegrosskopf.de ---
(In reply to Julien Nabet from comment #6)
> 
> (Attempt to define a second PRIMARY KEY for the same table)

There must be 2 primary keys, because there are 2 primary keys in the original
table of HSQLDB:
"Id" is '0' in every row, "Jahr" changes the values from '2015' up to '2018'.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #6 from Julien Nabet  ---
Created attachment 141513
  --> https://bugs.documentfoundation.org/attachment.cgi?id=141513=edit
patch

With the patch included, primary keys are parsed but the statement generated to
create the table fails:
CREATE TABLE "MitgliedJahr" ( "Id" INTEGER NOT NULL PRIMARY KEY, "Jahr" INTEGER
NOT NULL PRIMARY KEY, "Datum" DATE)

(Attempt to define a second PRIMARY KEY for the same table)

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

Julien Nabet  changed:

   What|Removed |Added

 CC||serval2...@yahoo.fr

--- Comment #5 from Julien Nabet  ---
Created attachment 141512
  --> https://bugs.documentfoundation.org/attachment.cgi?id=141512=edit
bt with debug symbols

On pc Debian x6-64 with master sources updated yesterday, I got an assertion.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

Xisco Faulí  changed:

   What|Removed |Added

 CC||xiscofa...@libreoffice.org

--- Comment #4 from Xisco Faulí  ---
Reproduced in

Version: 6.1.0.0.alpha0+
Build ID: 159dd28651788a19848eae56693ad06ed947414d
CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group

which is the first commit after the migration...

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

rob...@familiegrosskopf.de changed:

   What|Removed |Added

   See Also||https://bugs.documentfounda
   ||tion.org/show_bug.cgi?id=11
   ||7096

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

rob...@familiegrosskopf.de changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEW
 Ever confirmed|0   |1

--- Comment #3 from rob...@familiegrosskopf.de ---
Could confirm the buggy behavior: There is no BLOB in the table, but Firebird
couldn't import the table. 

... and another bug appears: I tried to have a look at the table and want edit
it: LO 6.1.0.0alpha0 crashed immediately.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

Gerhard Weydt  changed:

   What|Removed |Added

 CC||btom...@gmail.com
 Blocks||116968


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=116968
[Bug 116968] [META] Migrating existing embedded HSQLDB databases to Firebird
-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #2 from Gerhard Weydt  ---
Created attachment 141474
  --> https://bugs.documentfoundation.org/attachment.cgi?id=141474=edit
Doc after migration

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 117092] Migration to Firebird fails for a simple table

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=117092

--- Comment #1 from Gerhard Weydt  ---
Created attachment 141473
  --> https://bugs.documentfoundation.org/attachment.cgi?id=141473=edit
Doc before migration

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs