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: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Database (show other bugs)
Version: 4.2.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Andrzej Hunt
QA Contact:
URL:
Whiteboard:
Keywords:
: 72467 (view as bug list)
Depends on:
Blocks: 51780
  Show dependency treegraph
 
Reported: 2013-09-30 08:02 UTC by Alex Thurgood
Modified: 2015-01-03 17:39 UTC (History)
9 users (show)

See Also:
i915 platform:
i915 features:


Attachments

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
Hi,

I confirm this problem (LO 4.2.0 beta1 on Win 7 32bits) and I also think that this this enhancement is necessary : the table editor should support the autoincrement. Many users are not familiar with the SQL triggers :-))

Thanks,
Bernard Ribot
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
The bug still exists in Version: 4.2.4.1 on Win 7 64 pro.

I'd suggest that this is a show-stopper, at least for the "firebird as default engine" change.
Comment 13 ribotb 2014-04-24 09:54:10 UTC
Yes ! And my opinion is that there are currently too many unresolved issues for firebird is the default engine.

Bernard
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
Adding self to CC if not already on


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.