Bug 77154 - Other: Autovalue does not work with .csv files imported read/write
Summary: Other: Autovalue does not work with .csv files imported read/write
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.2.1 release
Hardware: Other Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2014-04-07 19:09 UTC by edonkey2001-libreoffice
Modified: 2015-06-11 18:56 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Example.csv (32 bytes, text/csv)
2014-04-07 19:09 UTC, edonkey2001-libreoffice
Details
Database and csv-file. Table in database with autovalue (3.53 KB, application/zip)
2014-04-13 17:26 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description edonkey2001-libreoffice 2014-04-07 19:09:07 UTC
Created attachment 97048 [details]
Example.csv

Problem description: 

Steps to reproduce:
1. Create a new folder.

2. Create a dummy Example.csv files in this folder with Calc inserting an ID column (integers from 1 to x) and a second Data column containing associated data (text)

3. Create a new Database and save it into the same folder as the Example.csv file

4. Tools/SQL:
CREATE TEXT TABLE "Example" ("ID" INTEGER PRIMARY KEY, "Data" VARCHAR(20));

5. View/Refresh Tables

6. Tools/SQL:
SET TABLE "Example" SOURCE "Example.csv;ignore_first=true;encoding=UTF-8";

7. Edit the table Example

8. Set Autovalue Yes for ID and save

Current behavior: A warning "The column "ID" could not be changed. Should the column instead be deleted and the new format appended?" appears

Clicking more the Error is "SQL Status: S1000
Error code: -73

This operation is not supported in statement [ALTER TABLE "Example" ALTER COLUMN "ID" INTEGER NOT NULL IDENTITY]"

If you click on yes, it says Error: This operation is not supported in statement [ALTER TABLE "Example" DROP "ID"]

Expected behavior:
The column is saved with Autovalue Yes.
              
Operating System: Debian
Version: 4.2.2.1 release
Comment 1 Robert Großkopf 2014-04-10 18:12:38 UTC
Base does only support the functions the database (like the internal HSQLDB) would support. Autovalue is supported for databases where such an incremented value is working with.
*.csv is a form of a text. You could use this to import data into an internal database (HSQLDB). If you want to use autovalue you have to use the internal HSQLDB with a table saved in the HSQLDB.

Please have a look for all, what is possible with 
SET TABL ... SOURCE
http://hsqldb.org/doc/guide/ch09.html#set_table_source-section
http://hsqldb.org/doc/guide/ch06.html

I couldn't find anything written there that it would be possible to set a field to autovalue. So it's not a bug of Base; could be a bug of HSQLDB, but this version (1.8.) wouldn't be changed.
Comment 2 Robert Großkopf 2014-04-12 17:29:11 UTC
Must correct myself. Seems to be the internal HSQLDB supports autovalues for texttables. 
I have created a table like you did. 
Then I have edited the table and set ID to autovalue.
Now I have connected to the *.csv-file.
I could add data to the *.csv-file and the autovalue works here.

My test-system: LO 4.2.3.3 and LO 4.1.6.1 - OpenSUSE 12.3 64bit rpm Linux. Both LO-versions would work with the *.csv-file and a autovalue.

So I can't confirm this bug.
Comment 3 edonkey2001-libreoffice 2014-04-13 16:27:57 UTC
Strange, I can reproduce it on Fedora 20 with LO 4.2.3.2 too.
Are you sure you followed the steps correctly, and, in that case, would you tell me what you have done so I can do it too and have it working? Thanks
Comment 4 Robert Großkopf 2014-04-13 17:26:56 UTC
Created attachment 97316 [details]
Database and csv-file. Table in database with autovalue

I have changed the order of the steps:
6. Tools/SQL:
SET TABLE "Example" SOURCE "Example.csv;ignore_first=true;encoding=UTF-8";
7. Edit the table Example
8. Set Autovalue Yes for ID and save

to
6. Edit the table Example
7. Set Autovalue Yes for ID and save
8. Tools/SQL:
SET TABLE "Example" SOURCE "Example.csv;ignore_first=true;encoding=UTF-8";

Seems it must be so:
http://hsqldb.org/doc/guide/ch06.html
"ALTER TABLE commands that add or drop columns are not supported with non-empty text tables."

You can try this way:
"  SET TABLE mytable SOURCE OFF
Subsequently, mytable will be empty and read-only. However, the data source description will be preserved, and the table can be re-connected to it with
  SET TABLE mytable SOURCE ON "
Comment 5 Terrence Enger 2014-08-23 15:24:18 UTC
robert,

Do you still have a problem after the actions you described in comment 4?  With your attachment and master from 2014-08-13, I added a row to table Adressen; the program generated the next ID, and the new line appeared in Adressen.csv.

Terry.
Comment 6 Robert Großkopf 2014-08-23 17:58:03 UTC
(In reply to comment #5)
> robert,
> 
> Do you still have a problem after the actions you described in comment 4? 
> With your attachment and master from 2014-08-13, I added a row to table
> Adressen; the program generated the next ID, and the new line appeared in
> Adressen.csv.
> 
> Terry.

Hi Terry,

I couldn't reproduce the bug as I have written in comment2. So I haven't set the bug to new. The steps I wrote in comment4 are changed steps from the bug-submitter edonkey2001. Would be good to read from edonkey2001 if it is working for him now so we could close this bug.

Regards

Robert
Comment 7 Alex Thurgood 2015-01-03 17:40:06 UTC
Adding self to CC if not already on
Comment 8 Robert Großkopf 2015-06-11 18:56:40 UTC
No reaction to comment4 for over 1 year. 
The way I described the connection to *.csv with autovalue works as described for HSQLDB. So I will set this bug to WORKSFORME. Feel free to reopen if the buggy behavior could be reproduced when following the described steps.