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.

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).

(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.

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

*** Bug 72467 has been marked as a duplicate of this bug. ***

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

(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

Hi Alex

You must first create the generator GEN_T1_ID

Bernard

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

(In reply to comment #7)
> 
> Should I open a separate bug report for this ?
> 
> Alex

I think.

Bernard

(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

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.

Yes ! And my opinion is that there are currently too many unresolved issues for firebird is the default engine.

Bernard

Adding as blocker for making Firebird the default

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).

(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.

(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)

(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"

Adding self to CC if not already on