Bug 64377

Summary: DATABASE: Please automatically add a hidden ROWID field while creating tables.
Product: LibreOffice Reporter: Biju <biju.maillist>
Component: DatabaseAssignee: Not Assigned <libreoffice-bugs>
Status: NEW --- QA Contact:
Severity: enhancement    
Priority: medium CC: iplaw67, robert
Version: 4.0.0.2 rc   
Hardware: Other   
OS: All   
Whiteboard:
i915 platform: i915 features:
Bug Depends on:    
Bug Blocks: 61547    

Description Biju 2013-05-09 04:59:51 UTC
Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3)
TinderBox: MacOSX TDF Release, Branch:libreoffice-4-0, Time: 2013-03-26_15:52:16

DATABASE: Please automatically add a hidden ROWID field while creating tables.

When ever user create a new table in BASE, LibreOffice should automatically add a hidden ROWID field. It should be a AutoValue = true field and should act like a primary key field for the purpose of editing.

At present 
1. if you create a table with out primary key or if you uncheck "primary key" property of a field on existing table with primary key. The table become non-insertable/non-update-able.

2. Additionally from GUI, you will not be able to add a primary key field to the table or turn an existing field to a primary key.

I believe fixing this should solve issues like Bug 61547 

Also please see bug 56242, bug 37333
Comment 1 robert 2013-05-09 07:32:47 UTC
I wouldn't prefer an automatically added and hidden primary-key-field. 
When you create a table without a primary-key a dialog in Base appears:
"No primary key ... you can only enter data with a primary key ... should the primary key created now?"
At this moment the user has only to press a button and will see the primary key in his table. He will be able to create relations between tables, because the primary-key isn't hidden and he could see the field-type of this key.
For many people the primary-key with autovalue and field-type "Integer" is the default. But there are also tables, which would work better without this automatic.
Example: 
table - "Gender"
fields - "ID", "Gender", "Title", "Salutation"
"ID" would be varchar with 1 char. Content of this field would be "m" and "f", while the content of "Gender" is "male" and "female".
I also use tables with only one row for settings of the database, where I change the value. Primary key in this tables has the field-type "Bool".

The only bug in this moment is: It isn't possible to add a primary key in an existing table without primary key while using the GUI. It is possible to create such a value using the direct SQL-mode. You have to decide the creating of a primary-key when creating the table. There is a warning, when you will save a table without creating such a key. If somebody ignores this warning it should be possible to change this with the GUI - and not hidden. But this is https://bugs.freedesktop.org/show_bug.cgi?id=61547
Comment 2 Biju 2013-05-12 00:39:51 UTC
Please Note

(In reply to comment #1)
> I wouldn't prefer an automatically added and hidden primary-key-field.

The request is not asking to add hidden primary-key-field.
It is asking "add a hidden ROWID field",
which "act like a primary key field" at certain times. 
may be some thing similar to what Oracle have.

Only problem in Oracle is there is no guaranty that ROWID of a record stay constant for the life time of the record. It changes when we/system reorg a table.
 
> When you create a table without a primary-key a dialog in Base appears:
> "No primary key ... you can only enter data with a primary key ... should
> the primary key created now?"

Not much help for a new bee.
Basically most first time users dont understand the concept of primary key, foreign key, and relational integrity.  

> The only bug in this moment is: It isn't possible to add a primary key in an
> existing table without primary key while using the GUI. 

No, I dont think you need to force user to create a primary key all the time.
Oracle, MS-SQL, MS-Access dont do that.
Still corresponding GUI tools like SQL-Developer, TOAD, SQL-Server Management Studio, and also I think MS-Access allow user to edit the table content.
Comment 3 Alex Thurgood 2015-01-03 17:39:30 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.