Bug 49708 - EDITING FILESAVE Setting primary key was silently ignored
Summary: EDITING FILESAVE Setting primary key was silently ignored
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:4.2.0 target:4.1.0.2 target:4.0.5
Keywords:
: 45251 61547 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-05-09 14:50 UTC by Ulf Zibis
Modified: 2013-07-25 07:34 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Shows where to choose the primary key in the import-wizard. (25.15 KB, image/png)
2013-06-13 14:11 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ulf Zibis 2012-05-09 14:50:56 UTC
- import Data from Calc table without setting additional primary key (there is already a key column in the Calc table)
- In the table definition view set key column as primay key, see: http://www.libreoffice-forum.de/viewtopic.php?p=13914#p13914
--> primary key icon is displayed
- save and close
- open table definition view again
--> primary setting is lost

Solution:
- allow later primary key setting, even if table contains data
- if not possible, offer an error message to the user
- allow to define existing column as primary key while importing external data
Comment 1 Robert Großkopf 2012-07-08 09:45:41 UTC
When you import data from calc and follow the import-wizard you could set the primary-key for the column you wish. Also you could set the right property of the field.
When you try to import tables and ignore the wizard (choosing "Create" in the first step of the wizard), the primary-key couldn't be set later in the GUI. This is a bug.
You could set the key in SQL with

ALTER TABLE "tablename" ADD PRIMARY KEY ("columnname");

Changed the Status to "new", the Importance to "normal" (because there is a possibility to use the whole wizard and change to primary-key there and a possibility in direct SQL)
Comment 2 Ulf Zibis 2012-07-08 12:03:45 UTC
(In reply to comment #1)
> When you import data from calc and follow the import-wizard you could set the
> primary-key for the column you wish.

No, if I choose the existing column 'ID', I get the error, "There are already following fields set as primary key: ID".
I only can choose an additional primary key field, which is worthless.

> Also you could set the right property of the field.

I don't see any possibility to set field properties in the wizard, I only can set the data type/length.

> You could set the key in SQL with
> 
> ALTER TABLE "tablename" ADD PRIMARY KEY ("columnname");

Sorry, I can't see, from where to run such SQL statement, please help.

Changed the Importance again to "critical" (because none of the given workarounds seem to work).
Comment 3 Robert Großkopf 2012-07-08 12:44:24 UTC
I 'm not able to declare all in English. I know, that you have reported something in the german LO-Forum.
Try this link:
http://de.libreoffice.org/hilfe-kontakt/handbuecher/
Scroll down to the "Base Handbuch". In "Kapitel 7" you find, what you are looking for, when inserting a calc-table in base and choosing the primary-key for it. Its a little bit hidden in the wizard, but it works.
In "Kapitel 3" you find where to put SQL-code into a database in the direct way. 
Way: Tools → SQL or in German: Extras → SQL
Let us dicuss the rest in the German LO-Forum.
Switch the Importance, when you see, that the primary-key could be set in the two ways I have declared.
Comment 4 Ulf Zibis 2012-07-09 10:05:00 UTC
(In reply to comment #3)
> Scroll down to the "Base Handbuch". In "Kapitel 7" you find, ...

Thanks for your help Robert, I've found it.

> Switch the Importance, when you see, that the primary-key could be set in the
> two ways I have declared.

Done!

Please also note my 2nd "See also" link.
Comment 5 Robert Großkopf 2013-06-07 15:51:24 UTC
*** Bug 61547 has been marked as a duplicate of this bug. ***
Comment 6 Julien Nabet 2013-06-08 07:17:53 UTC
Lionel: I took a look to:
1) SetPrimaryKey + AdjustFieldDescription in dbaccess/source/ui/tabledesign/TEditControl.cxx

2) appendPrimaryKey in dbaccess/source/ui/tabledesign/TableController.cxx

I'm not sure to understand how it works but it seems something is lacking in second one.
The more I read this part the more I think there are columns and there are keys (whereas I thought first there were only columns and keys were only property setted on some columns).
If I'm right, I don't see the part which adds the primary key to the column in 2) but anyway, I don't have a clue how to add it.

Any idea?
Comment 7 Julien Nabet 2013-06-08 07:35:52 UTC
Forgot to say, I tried to follow the process and had this:
dbaccess/source/ui/tabledesign/TableController.cxx

sal_Bool OTableController::doSaveDoc(sal_Bool _bSaveAs) (line 301)
calls alterColumns line 420

OTableController::alterColumns() (line 1018)
calls appendPrimaryKey line 1344

OTableController::appendPrimaryKey(Reference<XKeysSupplier>& _rxSup,sal_Bool _bNew)
it exists of this method on line 785 ("primary key already exists after appending a column")
But we haven't done any column appending, we've just altered columns
Comment 8 Mike A. Fargo 2013-06-13 13:10:10 UTC
This bug is still there in 
Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3

I can confirm this:

I copy (^C) a table from Calc (including field names).
I paste the clipboard as new table.

There is no option to choose an existing field as primary key.

The only workaround is to allow the assistent creating an
generic ID field as primary key, then edit the database layout,
change primary key to wanted field. Save, close, reopen, then
delete the generic ID field created by the wizzard.

The suggested option to choose the primary key afterwards,
seems to work when you enter the design mode (key icon is
displayed in the right place), saving and closing gives no
error message. 

But if you try to edit or add any values, you end up with
a seemingly write protected table, and most people would
never find out, that the yet missing primary key is the
reason for this.

Suggestion:
Please, extend the import GUI to allow selecting one of
your imported fields to be the primary key.

Please, double check after defining primary key and saving,
if this change could actually be commited.

Cheers
Mike
Comment 9 Robert Großkopf 2013-06-13 14:11:34 UTC
Created attachment 80778 [details]
Shows where to choose the primary key in the import-wizard.
Comment 10 Robert Großkopf 2013-06-13 14:24:01 UTC
(In reply to comment #8)
> This bug is still there in 
> Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3
> 
> I can confirm this:
> 
> I copy (^C) a table from Calc (including field names).
> I paste the clipboard as new table.
> 
> There is no option to choose an existing field as primary key.

In my LO-Version I have the option in the wizard. I have to choose the field, when the wizard will format the field. Click the right button of the mouse over the field.

The wizard could be designed better in this position, but that isn't the reported bug. It is reported in https://bugs.freedesktop.org/show_bug.cgi?id=49554

One comment to the Version and the Platform:
It has to be the first version the bug appears. I can test it with LO 3.3.4 - the bug exists. Think the bug exists the whole time in LO. If you change the version it means: This is the first version, have a look what has been change from the version before and you will get the wrong code.
Also this bug isn't a bug of only FreeBSD or only 64-bit-systems. I will change the settings again.
Comment 11 Commit Notification 2013-06-21 13:49:04 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2741cb8685b28a4454b456b24c1545858fca52e8

fdo#49708 when there is no Primary Key, there is no Primay Key



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 12 Lionel Elie Mamane 2013-06-21 13:55:04 UTC
(In reply to comment #6)
> Lionel: I took a look to:
> 1) SetPrimaryKey + AdjustFieldDescription in
> dbaccess/source/ui/tabledesign/TEditControl.cxx

> 2) appendPrimaryKey in dbaccess/source/ui/tabledesign/TableController.cxx

Thanks, these code pointers helped me find the source of the problem. (See the commits for the actual problem.)

> The more I read this part the more I think there are columns and there are
> keys (whereas I thought first there were only columns and keys were only
> property setted on some columns).

Yes, indeed, there are columns and there are keys (and indexes). Keys (wich are more or less the same as indexes with the "UNIQUE" property set) are basically a *list* columns, it can be one column or several.

MS Access has a "Indexed" property for columns, which under the hood creates a one-column index for this column.
Comment 13 Lionel Elie Mamane 2013-06-21 13:55:32 UTC
The backport of the fix to 4.1 and 4.0 has been submitted to Gerrit.
Comment 14 Commit Notification 2013-06-24 13:21:27 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=002c79346c69c0b7f4953daec7377e71660ae306&h=libreoffice-4-1

fdo#49708 when there is no Primary Key, there is no Primay Key


It will be available in LibreOffice 4.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2013-06-24 22:00:30 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8a6df1a62ded3b6da47022a5b7eec2ba6e81112c&h=libreoffice-4-0

fdo#49708 when there is no Primary Key, there is no Primay Key


It will be available in LibreOffice 4.0.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 16 Lionel Elie Mamane 2013-07-25 07:34:25 UTC
*** Bug 45251 has been marked as a duplicate of this bug. ***