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

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #18 from Gerhard Weydt <gerhard.weydt at t-online.de> ---
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180422/528ac9e1/attachment-0001.html>