Bug 121210 - IMPORT: No possibility to set Auto Value to a Integer Primary Key when importing Calc range
Summary: IMPORT: No possibility to set Auto Value to a Integer Primary Key when import...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.2.1 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Import
  Show dependency treegraph
 
Reported: 2018-11-06 15:07 UTC by Robert Großkopf
Modified: 2022-04-04 07:25 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Screenshot: No autovalue available for Integer-PrimaryKey-field (44.15 KB, image/png)
2018-11-06 15:07 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2018-11-06 15:07:48 UTC
Created attachment 146362 [details]
Screenshot: No autovalue available for Integer-PrimaryKey-field

For the first steps see bug 121208. 
Set the field for an Integer as "Primary Key" (right mousebutton on fieldname, choose primarykey there).
You couldn't set this field to Auto Value.
You have to edit the table afterwords and change the field to Auto Value.

Tested with LO 6.1.3.2, 64bit rpm Linux.
Comment 1 Drew Jensen 2018-11-06 18:13:25 UTC
Setting the field to Integer(Autovalue) which becomes an IDENTITY would also automatically create that PK. 

In the case of importing from a Calc this could lead rows with duplicate values in the Calc column not importing.

Is this a better solution than importing all the rows?

With all the rows in the database then if the user adds a PK for that column of data the step of doing so will give an error should a duplicate arise, offering the user the chance to fixup the value as needed.
Comment 2 Robert Großkopf 2018-11-06 18:32:19 UTC
(In reply to Drew Jensen from comment #1)
> Setting the field to Integer(Autovalue) which becomes an IDENTITY would also
> automatically create that PK. 
> 
> In the case of importing from a Calc this could lead rows with duplicate
> values in the Calc column not importing.

If you could create a primarykey for a field you couldn't import a Calc-table with duplicate values. This is independent of AutoValue "yes" or "no". The import would fail. So the bug is: primarykey could be created and AutoValue could not.
Comment 3 Alex Thurgood 2018-11-06 18:35:36 UTC
I can confirm this with

Version: 6.1.2.1
Build ID: 65905a128db06ba48db947242809d14d3f9a93fe
Threads CPU : 8; OS : Mac OS X 10.14; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group threaded
Comment 4 Alex Thurgood 2018-11-06 18:39:34 UTC
I tested on a Firebird ODB database, so perhaps this is not representative given the already huge problems that this currently has, however, I was unable to either produce a PK using the GUI autovalue, or alternatively, ignoring that first wizard screen option, and waiting until the end of the table creation when the user is asked whether they would like to add a PK field.

In both cases, i.e manual definition in first wizard screen, or wizard executed suggestion, the PK field could not be created.

I seem to have seen this issue already reported though, so this current report might be a DUP.
Comment 5 Alex Thurgood 2018-11-06 18:42:41 UTC
Possible DUP of bug 117527
Comment 6 Drew Jensen 2018-11-06 19:00:18 UTC
(In reply to Alex Thurgood from comment #5)
> Possible DUP of bug 117527

This is why I changed the summary. Table Wizard in Base to most of us refers to 'Create new Table Wizard' started from within the Base UI.

This issue is about the Import Wizard wich starts when the user drops (or pastes) row based data onto the Base UI. 

Those two 'wizards' are TTBOMK different beasts in the code base and yes there are issues with PK creation in the Base 'New Table Wizard' also.
Comment 7 Drew Jensen 2018-11-06 19:36:51 UTC
(In reply to Alex Thurgood from comment #3)
> I can confirm this with
> 
> Version: 6.1.2.1
> Build ID: 65905a128db06ba48db947242809d14d3f9a93fe
> Threads CPU : 8; OS : Mac OS X 10.14; UI Render : par défaut; 
> Locale : fr-FR (fr_FR.UTF-8); Calc: group threaded

In the Import Wizard (used to bring row data from an external source into a Base table) though this inability to set a PK with an existing column was IIRC a decision not an accident.

The ability is included, as you mention, to create an additional field for an IDENTITY column in the target database table if the table is being created during the current import session.

So why not include the ability to use a column of data from the source data? 

That discussion was now 14+ years behind us - // insert quote on aging 
so with human memory failings something along the lines of iirc

The import functionality was to begin with a limited amount of data validation/manipulation capabilities. (I don't see where that has changed to a significant degree) 

That a user may be dropping a collection of rows onto the application from any number of applications/sources and the wizard should target the most basic office software user. 

The first iteration of the Import Wizard when it came to physically importing the data would display an error box should a row fail to import, offering the user the two options; skip this record (with no explanation to why it failed) and continue or quit the import process completely. 

OK - so lets look at an office software user who found some tabular data on a website and copy a block of it. They paste that into Base and start the Import Wizard. So they read a couple of articles, browsed a manual and watched a video, they know it is good to have a primary key and they go about using what looks like a good candidate in that data.

Some of these imports will fail for different reasons, with any unique indexes created before the data is brought in the first time that number is going to go up. The user didn't see why the insert of the row failed and maybe they just gave up completely (not knowing not to use AUTOVALUE or set a PK checkbox for this data) or maybe they sat and said toss out the row and keep going. 

Did we do them any favors in this scenario?
Comment 8 Drew Jensen 2018-11-06 19:42:45 UTC
(In reply to Drew Jensen from comment #7)
> (In reply to Alex Thurgood from comment #3)
> > I can confirm this with
> > 
> > Version: 6.1.2.1
> > Build ID: 65905a128db06ba48db947242809d14d3f9a93fe
> > Threads CPU : 8; OS : Mac OS X 10.14; UI Render : par défaut; 
> > Locale : fr-FR (fr_FR.UTF-8); Calc: group threaded
> 
> In the Import Wizard (used to bring row data from an external source into a
> Base table) though this inability to set a PK with an existing column was
> IIRC a decision not an accident.
> 
> The ability is included, as you mention, to create an additional field for
> an IDENTITY column in the target database table if the table is being
> created during the current import session.
> 
> So why not include the ability to use a column of data from the source data? 
> 
> That discussion was now 14+ years behind us - // insert quote on aging 
> so with human memory failings something along the lines of iirc
> 
> The import functionality was to begin with a limited amount of data
> validation/manipulation capabilities. (I don't see where that has changed to
> a significant degree) 
> 
> That a user may be dropping a collection of rows onto the application from
> any number of applications/sources and the wizard should target the most
> basic office software user. 
> 
> The first iteration of the Import Wizard when it came to physically
> importing the data would display an error box should a row fail to import,
> offering the user the two options; skip this record (with no explanation to
> why it failed) and continue or quit the import process completely. 
> 
> OK - so lets look at an office software user who found some tabular data on
> a website and copy a block of it. They paste that into Base and start the
> Import Wizard. So they read a couple of articles, browsed a manual and
> watched a video, they know it is good to have a primary key and they go
> about using what looks like a good candidate in that data.
> 
> Some of these imports will fail for different reasons, with any unique
> indexes created before the data is brought in the first time that number is
> going to go up. The user didn't see why the insert of the row failed and
> maybe they just gave up completely (not knowing not to use AUTOVALUE or set
> a PK checkbox for this data) or maybe they sat and said toss out the row and
> keep going. 
> 
> Did we do them any favors in this scenario?

My answer to that question is no, the best thing we could do for that scenario and the user was to nudge them to add an additional column for use as a PK in the new table and maximize the chances of bringing in all the rows of data they attempted to import.
Comment 9 Robert Großkopf 2018-11-06 19:50:58 UTC
(In reply to Drew Jensen from comment #7)
> 
> Some of these imports will fail for different reasons, with any unique
> indexes created before the data is brought in the first time that number is
> going to go up. The user didn't see why the insert of the row failed and
> maybe they just gave up completely (not knowing not to use AUTOVALUE or set
> a PK checkbox for this data) or maybe they sat and said toss out the row and
> keep going. 

If there is a user who found where to create a primary key in the import-wizard (it is hidden in the contextmenu of the fields) he will know what he is doing when creating this. And only the combination of primary key and Integer should allow AutoValue to appear at this point.
Comment 10 Alex Thurgood 2018-11-07 08:29:01 UTC
(In reply to Drew Jensen from comment #7)


> 
> The first iteration of the Import Wizard when it came to physically
> importing the data would display an error box should a row fail to import,
> offering the user the two options; skip this record (with no explanation to
> why it failed) and continue or quit the import process completely. 

So, this error message, instead of being improved to point out the failed insert record, was simply "removed" in a subsequent iteration of the wizard ? I feel that to be a regression in, and of, itself. 


> Did we do them any favors in this scenario?
Almost certainly not, and therein lies the conundrum. Whichever solution gets decided though, it must work properly - currently, none of the suggested automagic implementations work, or have I misunderstood.
Comment 11 Alex Thurgood 2018-11-07 08:56:01 UTC
Removing bibsect and regression keywords as the AUTOVALUE keyword isn't set automatically when attempting the import into an embedded hsqldb, however, the PK is created and my test Calc data imported.
Comment 12 Alex Thurgood 2018-11-07 08:57:15 UTC
Lowering priority, unless someone can indicate that this used to work better in an earlier version of LO.

Tested against 

Version: 5.2.5.1
Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22
Threads CPU : 4; Version de l'OS :Mac OS X 10.14.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 13 Julien Nabet 2018-11-10 17:25:06 UTC
I already tried to understand the code part and got completely lost, uncc myself.
Comment 14 QA Administrators 2019-12-06 04:18:31 UTC Comment hidden (obsolete)
Comment 15 Robert Großkopf 2019-12-06 18:25:01 UTC
Bug is the same in LO 6.3.3.2 on OpenSUSE 15.1 64bit rpm Linux
Comment 16 QA Administrators 2021-12-06 04:05:20 UTC Comment hidden (obsolete)
Comment 17 Robert Großkopf 2021-12-06 15:27:21 UTC
Still the same in LO 7.2.3.2 on OpenSUSE 15.2 64bit rpm Linux.