Bug 130345 - FIREBIRD: Impossible to create an autovalue key when inserting a new table
Summary: FIREBIRD: Impossible to create an autovalue key when inserting a new table
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.4.0.3 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default Base-Without-Java
  Show dependency treegraph
 
Reported: 2020-02-01 10:30 UTC by Robert Großkopf
Modified: 2023-04-30 08:12 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2020-02-01 10:30:59 UTC
Open a Firebird database.
Copy a table, for example from Calc.
Insert the table to Firebird.
Set "New Field for Primary Key" > ID

There is no possibility to set this field as autoincremented field.

Save the table.
Try to set the value to autoincremented value.

An error appears.
It seems to be impossible to create an imported table with an autoincrement value for Firebird.

This bug appears with LO 6.4.0.3, but also older versions. Seems it never worked.
Comment 1 Julien Nabet 2020-02-01 12:07:22 UTC
Perhaps related or brand new bug but on pc Debian x86-64 with master sources updated today, I can't see the properties fields with gtk3 rendering (no pb with kf5 or gen rendering) for a Firebird embedded odb or an embedded hsqldb odb.
Comment 2 Xisco Faulí 2020-02-12 17:02:27 UTC
(In reply to Julien Nabet from comment #1)
> Perhaps related or brand new bug but on pc Debian x86-64 with master sources
> updated today, I can't see the properties fields with gtk3 rendering (no pb
> with kf5 or gen rendering) for a Firebird embedded odb or an embedded hsqldb
> odb.

That's bug 130623.
Setting this report back to UNCONFIRMED
Comment 3 Robert Großkopf 2020-02-12 18:20:01 UTC
Difference to HSQLDB:
The wizard is the same.
The key could be set, but no AutoValue.
But in HSQLDB you could change the table afterwards.
You could set the AutoValue in the tableeditor without any problem.
Comment 4 Julien Nabet 2020-02-13 21:48:40 UTC
According to https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/Table.cxx?r=80aec8ef#191
    188     if (bIsAutoIncrementChanged)
    189     {
    190        ::dbtools::throwSQLException(
    191             "Changing autoincrement property of existing column is not supported",
    192             ::dbtools::StandardSQLState::FUNCTION_NOT_SUPPORTED,
    193             *this);
    194 
    195     }

It's not supported yet.

More details here about autoincrement field support in the different versions of Firebird:
https://stackoverflow.com/questions/34553826/easiest-way-to-create-an-auto-increment-field-in-firebird-database

(BTW Mark Rotteveel works on "Jaybird" the JDBC connector to Firebird)

Remark: for the moment, LO includes Firebird 3.0.0 package where there's 3.0.5.
I had tried to upgrade it but there were some pb with MacOs part, I abandoned it.
If someone wants to give it a try or perhaps we prefer waiting for Firebird 4.0.
Comment 5 Robert Großkopf 2020-02-14 16:03:03 UTC
Autoincrement isn't supported by Firebird at all.
There is a special generator created for.

But when we want to change from HSQLDB to Firebird the working internal database should offer this in another way. When changing from internal HSQLDB to Firebird with the migrationwizard the autovalue will be created for existing tables. So it is implemented somewhere in this code. 

We could
- change the wizard to choose the possibility to set AutoIncrement while importing rows, which has already a value in the field. The Generator has to be set afterwords with the max value of the field.
- change the table editor. It should be possible to read the max value of a Integer field and set this value to the generator.

Since LO 4.2 we have an experimental Firebird database. It is 6 years ago. Seems it is too tricky to get it working for us. I won't use this kind of internal database.
Comment 6 Julien Nabet 2020-02-14 17:39:13 UTC
(In reply to Robert Großkopf from comment #5)
> ...
> We could
> - change the wizard to choose the possibility to set AutoIncrement while
> importing rows, which has already a value in the field. The Generator has to
> be set afterwords with the max value of the field.
> - change the table editor. It should be possible to read the max value of a
> Integer field and set this value to the generator.
Too difficult task at least for me.

> 
> Since LO 4.2 we have an experimental Firebird database. It is 6 years ago.
> Seems it is too tricky to get it working for us. I won't use this kind of
> internal database.
I understand you too well and already told it...
There have been a lot of work to integrate Firebird but it's not the case anymore since several months (years?). I think it should be removed but it's only my personal opinion. I'm gonna send an email to Lionel and Tamas and I put you in cc Alexander Thurgood and you since you're the main (only?) QA experts about Base.
Comment 7 mhonline 2020-05-12 13:11:52 UTC
dropping support of firebird within LO might be a good idea.
Non working and no developement on their site visable (last FB 4.0Beta is 15 month old, ODBC 3 Year, all new stuff around FB JAVA-based only)
m.
Comment 8 Julien Nabet 2020-05-12 13:37:25 UTC
(In reply to mhonline from comment #7)
> dropping support of firebird within LO might be a good idea.
> Non working and no developement on their site visable (last FB 4.0Beta is 15
> month old, ODBC 3 Year, all new stuff around FB JAVA-based only)
> m.

LO uses Firebird 3.0.0 whereas 3.0.5 exists but it needs some working to use it since it must work on Linux, Mac and Windows (so patches may be necessary).
For the rest, that's the reason why Firebird has been put back experimental.
So you can't create new ODB files with Firebird when experimental features are disabled. You can still open existing Firebird odb files even if experimental features are disabled.

About dropping support of Firebird, I wouldn't oppose against this. The initial goal to support Firebird was to use a non Java dependent database like HSQLDB and one day, drop HSQLDB support. There was even a migration tool for this.
But migration tool is quite buggy and Firebird implementation isn't complete.
About HSQLDB, there are more recent versions released but since there was Firebird, nobody worked on this.
In brief, we're in the middle of a bridge and are stuck for the moment.
Comment 9 Robert Großkopf 2022-02-04 10:26:28 UTC
New idea, because it isn't possible to switch a field to autoincrement in Firebird 3 (and also Firebird 4):

Couldn't we add the possibility to set autoincremented field in the wizard for importing tables? There has only to be added

GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

to the code for internal databases (HSQLDB, Firebird 3).
Comment 10 mhonline 2022-02-04 13:35:43 UTC
the concept of having no record-numbers "available for users" in firebird is weak, althought they need to have them, since they can establish table-relations. However, the proposal of robert might not work, as the value from "generated by default" can be overwriten. Pls see this copy from the FB-4.0-manual:
GENERATED BY DEFAULT
An identity column of type GENERATED BY DEFAULT will generate a value on insert if no value — other than DEFAULT — is specified on insert. When the OVERRIDING USER VALUE clause is specified in the INSERT statement, the user-provided value is ignored, and an identity value is generated (as if the column was not included in the insert, or the value DEFAULT was specified).
But the solution for the problem lies somewhere in there.
I still believe, that - as stated somewhere else - the field of type identity with autoincrement must be made "non-editable" by user, but to have additional regular fields filled by a generator might be an option too to get a usable workaround.

m.