Bug 100167

Ok, let's set as enhancement.

You create this in the form-navigator, you could connect this to the mainform. Works here with LO 5.1.3.2 on OpenSUSE 64bit rpm Linux.

I couldn't reproduce any buggy behavior here.

Robert: do you see any benefit with also being able to create it in the design view?

Design view is more powerful and more efficient for experienced developers.  

The wizard does not allow all the options which the design view suggests are available - eg. no option for "SQL command" data source.  

I had initially expected a subform object to appear on the main form, within the bounds of which subform fields could be added/edited.

Even using the navigator-based approach, I'm still having problems...

If someone could try to replicate this and report, I would be grateful.

I wonder if there are just problems with the Ubuntu version?  14.04.4 / LO Version: 5.1.3.2Build ID: 1:5.1.3~rc2-0ubuntu1~trusty1
 
I have created a table structure to log and list issues (things that need attention) in a software project I'm working on, the categories which apply to them, and vice-versa.
 
issue --< issue_cat >-- cat
 
issue(issue integer PK autoincrement, issuedescr text)
cat(cat integer PK autoincrement, catdescr text)
issue_cat(issue integer, cat integer)
 
Issue and Cat tables have some dummy records, issue_cat is as-yet empty.
 
Upon opening issue_cat (table) to edit data, there is no grid and records cannot be added.  Record navigation/new record buttons are missing. Looks as if the window hasn't been drawn fully/properly.  Moving or maximising the window causes them to appear, but they are disabled.
 
1. create a new form, with a textbox
2. bind form to "issue" table and textbox to "issue" field (using right-click context menu)
3. navigator > right-click "form" > "New" > "Form"
...form appears as child of "Form", rename child to "subform"...
click "subform"
click "more controls..." on toolbox
add table control
cancel wizard (I don't want to show a table or query, but want to use an SQL statement to provide the data)
in subform properties, set "content type" to SQL command
set "content" to:
 
SELECT "issue_cat"."issue", "issue_cat"."cat", "cat"."catdescr" FROM "issue_cat", "cat", "issue" WHERE "issue_cat"."cat" = "cat"."cat" AND "issue"."issue" = "issue_cat"."issue"
 
Set link master and slave fields to "issue"
 
There now seems to be no way to edit the empty table control.
 
So...
 
Delete the table control and create another (having set-up the parent and subform in the steps above)...
 
wizard appears showing fields from subform sql command
added successfully
table control is blank when scrolling through records on parent form (there is, as yet, no data in issue_cat, so this may not be regarded as wrong...) ...but there is no way to add records - no default "blank/null" line waiting to be filled as might be expected, and the navigation controls/new record button are disabled.  Subform properties apparently allow additions and modifications. 

Subform table control doesn't allow additions/deletions (with disabled nav/new controls) even if a table source and fields are selected from the wizard.

I can't test how behaviour might differ if issue_cat were populated, because I can't enter any data in the table view. 

Many thanks
Gareth

Gareth: Could you please attach a database-example to this bug. You could also send it directly to me as attachment of a mail. So I will have a look at it. Here, with OpenSuse, I could not reproduce your problems. You are using a version, which is provided by Ubuntu in Ubuntu repositories. So could be, this is a special bug there ...

Bouvjaga: With the wizard you could only create a form with only one subform. Creating a form in design view allows to create a form with different subforms and subsubforms and parallel forms. Creating forms in this way is the "normal" way. The wizard is usable for creating a form in a fast way or creating a form for non-experienced users.

Created attachment 125517 [details]
db with behaviour described in my comment at 2016-06-06 18:53:14 UTC

(In reply to donotspam from comment #6)
> Created attachment 125517 [details]
> db with behaviour described in my comment at 2016-06-06 18:53:14 UTC

1. You have forgotten to set primary keys for the table "issue_cat". Open the table for editing, mark both fields while pressing CTRL and set them both as primary key.

2. You have to test your SQL-code. Copy the code of your subform to a query, created in SQL-mode. This query couldn't be edited.
SELECT "issue_cat"."issue", "issue_cat"."cat", "cat"."catdescr" FROM "issue_cat", "cat" WHERE "issue_cat"."cat" = "cat"."cat"

Now add "cat"."cat" to the query. This query could be edited, because the primary keys of both tables will be content of the query.

3. You don't need this SQL-Code for the subform. You would need the table "issue_cat" instead. From this table is only needed the field "cat". You have to change this field to a listfield. This field will show "catdescr" instead of "cat". So you could see the description and save the primary key.

I will add the changed database as attachment. I will also set this bug to unconfirmed.

Created attachment 125527 [details]
Added primary keys, changed datasource and changed tablecontrol of form - works

Thanks for your help.

Although I agree that the lack of a PK causes certain problems when querying/updating/deleting, a PK should not be a requirement and the db should still work - at least to the extent that one record can be distinguished from others... or at least give an error message if it is to be "required".

It shouldn't be necessary to add a PK in order to be able to view and edit a table, within certain constraints (no unique value so duplicate rows are vulnerable) but a "quick and dirty" implementation in this way should be possible.

"SELECT ic.issue, ic.cat,catdescr FROM issue_cat ic, cat WHERE ic.cat=cat.cat"

...works in MariaDB (and Access) where issue_cat has no PK.  

If this, however, is an underlying requirement of HSQLDB, is there some kind of error condition that can be detected and a message displayed to let the user know what the problem is?  The current situation doesn't seem ideal.

Thanks for your help.

> Now add "cat"."cat" to the query. This query could be edited, because the primary keys of both tables will be content of the query.

No PK of either table should be required to edit the FKs of the intermediate table. The query field "catdescr" depends on (cat.cat--<issue_cat.cat) which is implemented in the WHERE clause.  If cat.cat were editable from the subform, wouldn't that expose it to accidental changes?  Surely only the FKs are required?

> 3. You don't need this SQL-Code for the subform. You would need the table "issue_cat" instead. From this table is only needed the field "cat". You have to change this field to a listfield. This field will show "catdescr" instead of "cat". So you could see the description and save the primary key.

But it is a valid approach. Also, isn't issue_cat.issue required to implement the relationship between form and subform?

I have set issue_cat.cat and issue_cat.issue as PKs, and recreated the subform and table control but it still isn't working on Ubuntu - going to try on something else (Fedora or OpenSuse) when I have time later.

Thanks again.

Apologies I had non-existent cat values in issue_cat - still can't seem to add a record in the subform which seems to work in other databases.

Anyway thanks again for your help.

(In reply to donotspam from comment #9)
> Although I agree that the lack of a PK causes certain problems when
> querying/updating/deleting, a PK should not be a requirement and the db
> should still work - at least to the extent that one record can be
> distinguished from others... or at least give an error message if it is to
> be "required".

If you create a table you will be asked for a primary-key. If you won't chose a primary key there should appear a hint you couldn't input data into this table.
The internal HSQLDB wouldn't work without a primary key in a table. Have tested this with your example-database: There appears 
"No Primary Key
A unique index or primary key ....
You could only enter data ...
Should a primary key be created now?"
This dialog appears. Could be, you have ignored it?

> 
> ...works in MariaDB (and Access) where issue_cat has no PK. 

I don't know anything about Access, but MariaDB won't need a primary key. 

For the internal HSQLDB you have to define the relations with Tools > Relations. This would only work with relational databases - and MariaDB isn't a relational database for all table-formats, which are provided by MariaDB.

(In reply to donotspam from comment #11)
> Apologies I had non-existent cat values in issue_cat - still can't seem to
> add a record in the subform which seems to work in other databases.
> 
> Anyway thanks again for your help.

Did you try it with the attachment I made? There is a listbox in the tablecontrol. I had added one value for the first row in the subform ...

Thanks Robert, I have seen your attachment and I understand how your subform works.  I'm afraid I didn't read the warning properly about not being able to enter data without a PK, I assumed it was like Access's warning about not being able to identify records uniquely - my mistake, and my apologies.

What I don't understand is why the query (or a subform table control based on)

SELECT "issue_cat"."issue", "issue_cat"."cat", "cat"."catdescr" FROM "issue_cat", "cat" WHERE "issue_cat"."cat" = "cat"."cat"

doesn't allow edits or additions (issue_cat.issue and issue_cat.cat are a composite primary key.)

If this is the way that HSQLDB is supposed to work, then it is not a bug, and not an LO bug in any case - but it seems to be at odds with every other relational database I've used, and I don't understand why this shouldn't be possible - it seems to me to be broken.  If anyone can explain why this is the correct approach I'd be grateful.  

On another note, is there any way to do without quotation marks within SQL statements?  That's a real nuisance.

Thanks Robert, particularly, for your time and assistance.  

Just out of interest, is your Base 5.1 handbook available in English?  

Thanks
Gareth

@Gareth :

So far, I see no definable bug in any of the comments here, just usage issues and bugzilla is not the place for those. Those kind of questions belong on the user mailing list or the Ask libreoffice forum.

It would also be helpful if the problems, such as they are reported and verified, could be limited to one problem per report instead of amalgamating a series of problems into a flow, which makes it impossible to triage this report correctly.

Please identify clearly only one single behaviour that you consider buggy, with steps to reproduce, and submit separate reports for the other issues that you consider to be bugs.

As Robert has indicated, primary keys are a necessity for every table for which you wish to carry out update or insert operations. This is actually indicated in the built-in help (and the online help), but probably not in the most obvious of ways.

I am going to close this bug, as adding a sub-form manually remains possible providing your tables are set up properly beforehand as Robert has demonstrated.

If you feel that there is a particular issue which is still problematic, please open a new report and specify just that one issue there.

For anyone reporting this in future:

https://sourceforge.net/p/hsqldb/discussion/73674/thread/08cb8076/#7bde

From Fred's comment on the hsqldb thread, this is clearly a limitation of hsqldb and so quite rightly not our bug.