Bug Hunting Session
Bug 103715 - TABLE CREATION blues, including AutoValue option and Primary Key issues
Summary: TABLE CREATION blues, including AutoValue option and Primary Key issues
Status: RESOLVED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.2.2.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-11-04 22:08 UTC by Howard Johnson
Modified: 2016-11-05 21:00 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Howard Johnson 2016-11-04 22:08:43 UTC
Since these are related, I am submitting them together:



ISSUE #1 - with both HSQLDB and MySQL (via MaraiDB 10.1) JDBC connector:

Create Table in Design View

Field-1 Name: ID
Field-1 Type: Integer [INTEGER]

Field-2 Name: txt
Field-2 Type: Text [VARCHAR]

Close dialog.

Do you want to save the changes?  Yes.  Table name:  foo
No Primary key ... Should a primary key be created now?  NO

Open the table.

Failed result:  I can not add any records to this table.

Suggestion: While many tables need a primary key for a variety of reasons, a table should be operable without a primary key.  Surely in MySQL you can have a table and not need a primary key.



ISSUE #2 - with HSQLDB:

Create Table in Design View

Field-1 Name: txt
Field-1 Type: Text [VARCHAR]

Close dialog.

Do you want to save the changes?  Yes.  Table name:  foo
No Primary key ... Should a primary key be created now?  YES

Result: Although a primary key is added to the table, it is not very usable by most application because ID does not AutoValue itself.  You must manually enter key values when you add records.

Suggestion: I think it would make sense to turn on the AutoValue when this Primary Key is added.  That is what you will need most of the time.  Fortunately with HSQLDB you can at this point manually set AutoValue to Yes to fix this, but not with MySQL connector, see ISSUE #3 below.



ISSUE #3 - with MySQL via JDBC:

Create Table in Design View

Field-1 Name: txt
Field-1 Type: Text [VARCHAR]

Close dialog.

Do you want to save the changes?  Yes.  Table name:  foo
No Primary key ... Should a primary key be created now?  YES

Comment: Note, that AutoValue is still set to NO.

Edit the table foo.
Select the ID field.
Set AutoValue to Yes.
Close and save.

Edit the table foo.
Select the ID field.

Result: AutoValue is still set to NO.  It should have been set to Yes!


Workarounds: There are two ways you can get AutoValue set to Yes with JDBC MySQL connector.

1) In LOB first delete the table and start over, making sure that you set AutoValue to YES when you first save the table.

2) Close LOB and use PHPMyAdmin to set A_I (Auto Incrument) on.  Then reopen LOB, and hit View | Refresh Tables.

IMHO, neither of these solutions are a very good way to deal with this bug.


Further failures: You can not fix the problem (without malfunction) trying as follows:

* Delete the ID field, save the table, re-edit, add new ID2 with AutoValue ON.
* Add a 2nd ID2 field, set it to the Primary Key and save the table.
* etc.


I would expect that this would work much better, given how fundamental it is to using SQL.


Thanks guys!  I'm very excited about Base and am glad to do my part to find the rough edges.
Comment 1 Alex Thurgood 2016-11-05 10:03:17 UTC
@Howard : 

First, please separate out your problems into separate reports, it makes answering and triaging easier for the QA people and conforms to our QA process ;-)

1) irrespective of whether the backend requires a primary key, the LibreOffice Base code responsible mandates the use of a primary key in order to be able to write data to the table. So this is not a bug from the LibreOffice application perspective. This is actually in the included help and relevant documentation (Introduction/Guide to Base).

2) You can set AUTOVALUE on in table design for hsqldb, which avoids you being asked this question. You can also set it manually from the menu Tools > SQL dialog window with the appropriate hsqldb DML/DDL. In fact you can pretty much alter anything about hsqldb tables/schemas from this dialog. I don't know/remember whether the same can be said for mysql.

3) In the past, one couldn't even set the AUTOVALUE for mysql via the table design UI on table creation, it could only be done manually outside of LO, so it is already a bonus that you can from within the app.

As all of the issues you have mentioned are either already known, or work as designed, I'm closing this report as INVALID.
Comment 2 Howard Johnson 2016-11-05 10:17:46 UTC
Issue 3 is clearly a bug.  

Issue 2 would sure make this easier to use.  

And Issue 1 is inconsistent with roughly 10 other databases I've used over the past 40 years.

But suit yourself.  If you want a system that is buggy, hard to use and inconsistent then so be it.
Comment 3 Alex Thurgood 2016-11-05 11:47:56 UTC
@Howard : as I mentioned in my previous reply, the limitations / problems are either already known, or work as currently designed.

If you want to create requests for enhancement bug reports for each separate issue you have identified initially, then please do so. They can always be confirmed, but that doesn't mean anything will happen to them, unless you are volunteering to provide the code as well.


Unless we have developers to correct them, alter them or improve current behaviour, or people to pay or cajole developers to do so, not a lot will change in this area.
Comment 4 Howard Johnson 2016-11-05 21:00:18 UTC
Thank you for looking at this.  I apologize for my impatience.

I hope to someday get to the point where I can help with the code.