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
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
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)
@Andrzej : are you still working on a fix for this ?
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.
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
> 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.
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.
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.
Fixed in 0a9123152387f7a742481e9f35401270e29ed695