Bug 69949 - EDITING - Firebird : does not respect AUTOVALUE NOT NULL setting via UI - no autoincrement/serial function
Summary: EDITING - Firebird : does not respect AUTOVALUE NOT NULL setting via UI - no ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Tamas Bunth
URL:
Whiteboard: target:5.3.0
Keywords:
: 72467 (view as bug list)
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2013-09-30 08:02 UTC by Alex Thurgood
Modified: 2016-09-07 18:00 UTC (History)
13 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 Alex Thurgood 2013-09-30 08:02:35 UTC
While testing the new embedded firebird db, I tried to create a table via the Table Designer having :

id INT
ENTRY REQUIRED = YES aka NOT NULL
AUTOVALUE = YES aka AUTOINCREMENT or SERIAL

the id field being also a primary key (default behaviour of the UI).

Although the settings can be made by the user with the UI, when the table is saved and re-opened, they are not respected, i.e. they switch back to the defaults of :

ENTRY REQUIRED = NO
AUTOVALUE = NO


Tested on OSX 64bit master build and Linux 32bit master build.

Alex
Comment 1 Terrence Enger 2013-09-30 16:40:22 UTC
Alas, I can see why we might have chosen not to support autoincrement
in a firebird database.  "The Firebird FAQ, How to create an
autoincrement column?"  <http://www.firebirdfaq.org/faq29/> says that
it takes a BEFORE INSERT trigger to accomplish the autoincrement.
Moreover, that page reassuringly tells us "Most GUI administration
tools have options to generate this code for you automatically, so it
is not a problem."

So, I think that it would be an enhancement to provide this
functionality.  Alternatively, it could be seen as a bug that the
Table Design View offers Autovalue in a Firebird database.  The
decision about what to do is way above my pay grade.

Oh yeah, I do see th problematic behavior on master from 2013-09-19,
but I am leaving status UNCONFIRMED pending a decision on which
direction we want to go.

Terry.
Comment 2 Lionel Elie Mamane 2013-09-30 17:08:42 UTC
Seeing Firebird as our new default/embedded database, I think it should rather support autoincrement-by-GUI. Expecting users to create the trigger themselves is just too newbie-unfriendly.

We can just derive the name of the trigger and sequence (generator) from the name of the table and column, the way PostgreSQL does it (PostgreSQL also uses a sequence for autoincrement columns, but has some syntactic sugar that will automatically create the sequence).
Comment 3 Andrzej Hunt 2013-09-30 17:19:23 UTC
(In reply to comment #2)
> Seeing Firebird as our new default/embedded database, I think it should
> rather support autoincrement-by-GUI. Expecting users to create the trigger
> themselves is just too newbie-unfriendly.
> 
> We can just derive the name of the trigger and sequence (generator) from the
> name of the table and column, the way PostgreSQL does it (PostgreSQL also
> uses a sequence for autoincrement columns, but has some syntactic sugar that
> will automatically create the sequence).

Fully agreed. The current lack of this is mostly an oversight on my part (and thanks for pointing out an example, I wasn't too sure what would be correct otherwise) ;). And either way it's still a bug offering something that isn't implemented.
Comment 4 ribotb 2013-11-30 18:58:57 UTC Comment hidden (me-too)
Comment 5 pierre-yves samyn 2013-12-08 17:13:08 UTC
*** Bug 72467 has been marked as a duplicate of this bug. ***
Comment 6 Alex Thurgood 2013-12-15 07:41:19 UTC
Note that, in addition, I can not even create a trigger manually in Tools > SQL if I follow the Firebird FAQ :

http://www.firebirdfaq.org/faq29/

1) The DDL have to be executed singly, i.e. it doesn't seem to possible to execute several DDL statements in succession, separated by semi-colons ;

2) This means that statements like the following :

set term !! ;
CREATE TRIGGER T1_BI FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
END!!
set term ; !!


fail miserably, either because "term" is not recognized, or if we remove the set term statements, the DDL fails at line 5 (if (NEW.ID is NULL)... with the semi-colon being interpreted as the end of the command and thereby ignoring END.

So currently, unless I am doing something wrong (which is possible), there seems to be no way to even manually create a trigger for an embedded firebird db.


Alex
Comment 7 Alex Thurgood 2013-12-15 07:42:04 UTC
(In reply to comment #6)

> So currently, unless I am doing something wrong (which is possible), there
> seems to be no way to even manually create a trigger for an embedded
> firebird db.

Should I open a separate bug report for this ?


Alex
Comment 8 ribotb 2013-12-15 08:52:36 UTC
Hi Alex

You must first create the generator GEN_T1_ID

Bernard
Comment 9 ribotb 2013-12-15 09:42:09 UTC
Sorry Alex, I think that you have create first the generator ! Please, ignore my comment.

I confirm that "term" is not recognized by Firebird embedded.

I verified with my Firebird Server 2.5, it's ok with the example of the Firebird's FAQ :

set term !! ;
CREATE TRIGGER T11_BI FOR TABLE1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
END  !!
set term !! ;

Bernard
Comment 10 ribotb 2013-12-15 09:46:09 UTC
(In reply to comment #7)
> 
> Should I open a separate bug report for this ?
> 
> Alex

I think.

Bernard
Comment 11 ribotb 2013-12-15 10:50:23 UTC
(In reply to comment #9)
> 
> I confirm that "term" is not recognized by Firebird embedded.
> 
This formulation is not correct.
It should rather say that "term" is not recognized by LO Base because the issue occurs whether with embedded Firebird or with my Firebird Server through ODBC connection.

Bernard
Comment 12 Dominik 2014-04-23 11:13:55 UTC Comment hidden (me-too)
Comment 13 ribotb 2014-04-24 09:54:10 UTC Comment hidden (off-topic)
Comment 14 ribotb 2014-04-25 07:36:24 UTC
Adding as blocker for making Firebird the default
Comment 15 Julien Nabet 2014-05-31 17:03:14 UTC
I created fdo#79487 for "set term".

Alex: about comment 6, I could create a trigger by removing "set term" commands. I was quite astonished it worked but it did.

Now with these tests, I saw several other bugs (some already exist I think):
1) after having created table from SQL, I don't see the table in UI whereas Status indicates command was successful and there were no error in console logs.
If I close the DB and open it again, the table appears

2) I wanted to insert some data in the new empty table but no way via UI. So I inserted a first row via SQL

3) I thought I could add some other lines after this first one but still impossible via UI.

:-(

(All these tests have been done with master sources updated today on Debian x86-64).
Comment 16 Terrence Enger 2014-05-31 17:52:38 UTC
(In reply to comment #15)
> 1) after having created table from SQL, I don't see the table in UI whereas
> Status indicates command was successful and there were no error in console
> logs.
> If I close the DB and open it again, the table appears

I too have been annoyed by the non-appearance of a new table in the
Tables pane.  However the existence of menu option View > "Refresh
Tables" tells me that somebody thinks this is acceptable behaviour.
Comment 17 Julien Nabet 2014-05-31 18:29:08 UTC
(In reply to comment #16)
> I too have been annoyed by the non-appearance of a new table in the
> Tables pane.  However the existence of menu option View > "Refresh
> Tables" tells me that somebody thinks this is acceptable behaviour.

I haven't tested with HSQLDB but with Firebird, "Refresh view" makes all tables disappear (not "removed", just not displayed)
Comment 18 Julien Nabet 2014-05-31 20:51:43 UTC
(In reply to comment #17)
> I haven't tested with HSQLDB but with Firebird, "Refresh view" makes all
> tables disappear (not "removed", just not displayed)
With embedded hsqldb, a table doesn't appear after having runned a request to create it but the table indeed appears when using "Refresh Tables"
Comment 19 Alex Thurgood 2015-01-03 17:39:06 UTC Comment hidden (no-value)
Comment 20 Thomas Krumbein 2015-06-26 07:46:25 UTC
Can confirm bug in version 5.0.0. RC 1 on Win 8.1

Version: 5.0.0.1
Build-ID: 9a0b23dd0ab9652e0965484934309f2d49a7758e
Gebietsschema: de-DE (de_DE)
Comment 21 Alex Thurgood 2016-05-20 07:18:59 UTC
@Andrzej : are you still working on a fix for this ?
Comment 22 Lionel Elie Mamane 2016-06-18 15:19:45 UTC
Tamás, when you are finished with firebird 3.0 integration and migration to .fbk  format, or have time, I suggest this bug be your next priority.
Comment 23 Tamas Bunth 2016-06-24 17:40:57 UTC
I started to work on this issue.
There is a patch:

https://gerrit.libreoffice.org/26643

Which has some problems by now:
- Works only on an already saved table (not while creating. I used Table Design View)
- The AutoValue combobox does not remember the settings.
- There is no fancy <AutoValue> text yet while editing table, and the column is editable.

Tamás
Comment 24 Tamas Bunth 2016-06-26 17:11:40 UTC
> Which has some problems by now:
> - Works only on an already saved table (not while creating. I used Table
> Design View)
> - The AutoValue combobox does not remember the settings.
> - There is no fancy <AutoValue> text yet while editing table, and the column
> is editable.

So, the reason for the problems above is that the OResultSetMetaData::isAutoIncrement returns always with false. (See firebird/ResutSetMetaData.cxx)

There is a comment that says:
"no way of determining this here".

I'm going to examine how it is solved in PostgreSQL. As I see, the ResultSetMetaData in postgreSQL stores the name of the table, which can be used to get the properties of the column. I'm trying to find a solution like that.
Comment 25 Robert Großkopf 2016-08-23 18:12:04 UTC
Seems it is working now with the new embedded Firebird 3.0 on
Version: 5.3.0.0.alpha0+
Build ID: 5553041f4d9b5d676cb82bc1953f7334b201d9da
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-08-23_13:10:42

Have created a table, set Integer to primary-key, set autovalue and saved. 
When opening the table for input there appears <autovalue>.
Input Data: autovalue starts with 1 and counts up.

I wonder why the database seems to be a little bit slow when changing to new record together with autovalue.
Comment 26 m_a_riosv 2016-08-26 09:56:45 UTC
Also seem it works with:
Win10x64
Version: 5.3.0.0.alpha0+
Build ID: ea9a90d83d92076d41abfd31a1fd3a5d84b6ba92
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-08-26_06:19:43

I can't appreciate the slowness, changing to a new record.
Comment 27 Lionel Elie Mamane 2016-09-07 18:00:52 UTC
Fixed in  0a9123152387f7a742481e9f35401270e29ed695