Bug 71224 - Insert new fields between existing fields not working (ALTER TABLE ADD BEFORE/AFTER)
Summary: Insert new fields between existing fields not working (ALTER TABLE ADD BEFORE...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsDevEval
: 105935 153340 154620 157190 (view as bug list)
Depends on:
Blocks: Database-Tables
  Show dependency treegraph
 
Reported: 2013-11-04 16:33 UTC by Wayne
Modified: 2023-09-15 21:15 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments
test database (2.39 KB, application/vnd.oasis.opendocument.database)
2014-07-17 10:28 UTC, Kevin Suo
Details
incomplete prerequisite patch (5.10 KB, text/plain)
2023-09-15 21:13 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Wayne 2013-11-04 16:33:08 UTC
The "Insert Rows" into a table doesn't do as it states. I want to insert new field names and spaces to group things together. Upon choosing the "Insert Rows" from the drop down menu the green pointing arrow goes to the bottom of the field names list to enter a new field there instead of where I wanted it to be entered.
Comment 1 Robert Großkopf 2013-11-04 17:02:20 UTC
You have opened a table for editing, right?
With the GUI you could only insert new rows at the end of the table. If you wish to position a new field in this table at another place, you have to do this by the possibilities of the database.
With the internal HSQLDB the following possibility is given:
ALTER TABLE "Tablename" ADD "Fieldname" VARCHAR(25) BEFORE "Existing Fieldname";
Open Tools → SQL and try this possibility.

The GUI could only offer, what the database allows. And there is no possibility in the database to resort existing fields.

What do you mean with "spaces" when creating a table?
Comment 2 Kevin Suo 2014-04-29 11:08:52 UTC
I can confirm this, this is a bug.

Steps to reproduce:
1. New table, define 3 fields, stay in table design view;
2. Right-click -> Insert Row between the 1st and 2nd field. (a new field is inserted as expected.)
3. Save the table;
4. Table design view, try to insert a field between the 1st and 2nd field.

Current behaviour:
Insert a field between two existing fields not possible.

Expected:
Users can insert new fields or sort existing fields (when the database allows).
As this can be done by sql command, this should also be done in GUI.
Comment 3 Robert Großkopf 2014-04-29 15:41:29 UTC
This never worked before. So I set the Version to "Inherited From OOo".

It was not implemented into the GUI for changing a table. There are very much SQL-statements, which doesn't work in the GUI, but would work with direct SQL (example: Default values in tabledesign and other default-values in  SQL). So this is a ask for an enhancement.
Comment 4 Kevin Suo 2014-07-17 10:10:20 UTC
I set this feature request to highest, because this is the main reason which blocks me from using libreoffice base.

To design a table, nobody can make all the fields done during the first time they create a table. They have to move/insert fields later if needed. Currently it's only possible to run SQL commands to re-arrange field order.

Almost all the database management software allows to insert fields between two fields which already exist, except libreoffice base.
Comment 5 Kevin Suo 2014-07-17 10:28:33 UTC
Created attachment 102977 [details]
test database

steps:
1. Try to insert a new field "CITY" before "NAME", AFTER "ID".

In the GUI, the inserted fields always go to the bottom.

From tools-sql, run:
ALTER TABLE "Table1" ADD "CITY" VARCHAR(25) BEFORE "NAME";

New field is correctly inserted.
Comment 6 Björn Michaelsen 2014-08-21 12:21:16 UTC Comment hidden (obsolete)
Comment 7 Alex Thurgood 2015-01-03 17:40:29 UTC Comment hidden (no-value)
Comment 8 Alex Thurgood 2016-09-16 09:31:36 UTC
It is not a bug, rather more a feature request, as the feature did not exist already when the LibreOffice project came into being. As Robert has correctly stated in comment 1, new fields are currently always added to the end of the table definition, so the UI does not currently cater for the possibility of adding new fields before/after other fields or changing the order of fields in a database table.

Should such a feature be enabled ? Yes, absolutely, as the UI seems, at least in appearance from the context menu, to allow this. Confirming that this indeed is a feature request. Whether or not this is high on the list of priorities for Base is another matter. The current feature deficit can be resolved by resorting to SQL commands via the Tools > SQL menu entry, so there is a workaround. Consequently, this RFE is probably not going to feature as a high priority, unless someone volunteers to do the coding.
Comment 9 Alex Thurgood 2016-09-16 09:37:09 UTC
The other alternative would be to remove the "Insert Rows" entry from the context menu, so that users would not be misled, leaving only the current UI direct mouse click addition of adding a new field to the end of the already available fields, however, it would be better to strive for a product that allows such changes as originally intended.
Comment 10 Lionel Elie Mamane 2016-09-16 15:38:33 UTC
Not all databases support adding new fields at an arbitrary place in the table. For example PostgreSQL does not, it can add only at the end.

Whether embedded HSQLDB (and embedded Firebird) supports it, I'm not sure out of the top of my head. If it does, yes, it would be nice for the GUI to support it.
Comment 11 Heiko Tietze 2016-12-29 09:00:46 UTC
Removing needsUXEval. Ideally "Insert Row" works as expected (and actually is effective when the new table hasn't been saved), as known from Kexi for instance (formerly part of the Calligra suite). Alternatively a simple renaming to "Add row" is also possible.
Comment 12 Robert Großkopf 2017-02-12 09:53:29 UTC
*** Bug 105935 has been marked as a duplicate of this bug. ***
Comment 13 Buovjaga 2017-02-12 16:34:16 UTC Comment hidden (obsolete)
Comment 14 Robert Großkopf 2017-02-12 16:59:35 UTC
HSQLDB supports adding a new field at a position before an old field.
ALTER TABLE "Table" ADD "Name" VARCHAR(25) BEFORE "Existing Fieldname";

Firebird supports this:
ALTER TABLE "Table" ADD "Name" VARCHAR(25);
ALTER TABLE "Table" ALTER "Name" POSITION 2;

With Firebird you have to insert the row first. Then you could change the position of the row afterwords. So it does work also with old and new added fields.

Would be good if GUI supports adding a *new* field at a special position, because both internal databases will support to set a field this way.
Comment 15 Stéphane Guillou (stragu) 2023-02-03 07:58:23 UTC
*** Bug 153340 has been marked as a duplicate of this bug. ***
Comment 16 Alex Thurgood 2023-04-05 15:06:56 UTC
*** Bug 154620 has been marked as a duplicate of this bug. ***
Comment 17 Alex Thurgood 2023-09-14 09:16:27 UTC
*** Bug 157190 has been marked as a duplicate of this bug. ***
Comment 18 Julien Nabet 2023-09-15 07:56:55 UTC
What about letting "Insert Rows" during creation of table since in this case it works and removing it when editing the table?

I mean, it seems there's no SQL standard to do this as it had been already indicated in various comments and some DBs like Postgresql still don't support this (see https://wiki.postgresql.org/wiki/Alter_column_position).
Mysql (and so I suppose Mariadb) doesn't support "BEFORE" but "FIRST" and "AFTER" (see https://dev.mysql.com/doc/refman/8.0/en/alter-table.html).
Comment 19 Robert Großkopf 2023-09-15 09:42:12 UTC
(In reply to Julien Nabet from comment #18)
> What about letting "Insert Rows" during creation of table since in this case
> it works and removing it when editing the table?

That is, what is already implemented. I could insert a new row at any position while creating a new table. If I try to insert a new row (by context menu of mouse) at a special position when reopening an existing table for editing the cursor will be set at last row.

Base is a GUI for many database connections. How should it be implemented when database engine won't support to insert new rows at a special position? We would need special code for every database.

OK, it looks nice to get the position I want, but I could do this in forms or queries.
Comment 20 Julien Nabet 2023-09-15 09:46:32 UTC
(In reply to Robert Großkopf from comment #19)
> (In reply to Julien Nabet from comment #18)
> > What about letting "Insert Rows" during creation of table since in this case
> > it works and removing it when editing the table?
> 
> That is, what is already implemented. I could insert a new row at any
> position while creating a new table. If I try to insert a new row (by
> context menu of mouse) at a special position when reopening an existing
> table for editing the cursor will be set at last row.
> ...
During creation everything is fine so I won't come back here.

But when editing, I meant perhaps there shouldn't be the entry "Insert Rows" (or should be grayed out) and for the moment, it's not the case.
It's this entry during editing (and only during editing) which is disturbing since we see the cursor jumping at the end of table instead of inserting.
Comment 21 Alex Thurgood 2023-09-15 10:32:04 UTC
(In reply to Julien Nabet from comment #20)
> (In reply to Robert Großkopf from comment #19)

> But when editing, I meant perhaps there shouldn't be the entry "Insert Rows"
> (or should be grayed out) and for the moment, it's not the case.
> It's this entry during editing (and only during editing) which is disturbing
> since we see the cursor jumping at the end of table instead of inserting.

This would be my preferred solution, pending someone being able to write the conditional code for handling the various supported insert DSL statements.
Comment 22 Julien Nabet 2023-09-15 14:58:46 UTC
Workaround here (I removed the entry "Insert fields" when the table already exists):
https://gerrit.libreoffice.org/c/core/+/156948
Comment 23 Commit Notification 2023-09-15 16:49:41 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/4bab6b0b870ae56b8d40eac22dbb0a433006789e

tdf#71224: Remove "Insert fields" option for an existing table (workaround)

It will be available in 24.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 24 Julien Nabet 2023-09-15 16:50:14 UTC
Let's remove target since it's just a workaround.
Comment 25 Julien Nabet 2023-09-15 21:13:57 UTC
Created attachment 189617 [details]
incomplete prerequisite patch

Lionel: just an idea about preparing implementations

First I reverted locally my workaround patch + did this change:
diff --git a/dbaccess/source/ui/tabledesign/TEditControl.cxx b/dbaccess/source/ui/tabledesign/TEditControl.cxx
index 55696fe36463..3e9d4d851315 100644
--- a/dbaccess/source/ui/tabledesign/TEditControl.cxx
+++ b/dbaccess/source/ui/tabledesign/TEditControl.cxx
-    sal_Int32 nPastePosition = GetView()->getController().getFirstEmptyRowPosition();
-    if ( !GetView()->getController().getTable().is() )
-        nPastePosition = GetSelectRowCount() ? FirstSelectedRow() : m_nDataPos;
+    //sal_Int32 nPastePosition = GetView()->getController().getFirstEmptyRowPosition();
+    //if ( !GetView()->getController().getTable().is() )
+    sal_Int32 nPastePosition = GetSelectRowCount() ? FirstSelectedRow() : m_nDataPos;

It allows to put the cursor to insert a field at the right pos (instead of it jumps to the end), then of course if you can type a name field and choose var type when saving, the new field is at the end.

Then I tried to add some columns and noticed this bt for hsqldb:
#0  connectivity::OColumnsHelper::appendObject(rtl::OUString const&, com::sun::star::uno::Reference<com::sun::star::beans::XPropertySet> const&)
    (this=0x56357a993d90, _rForName="test", descriptor=uno::Reference to (dbaccess::OTableColumnDescriptor *) 0x56357ab44d38) at connectivity/source/commontools/TColumnsHelper.cxx:176
#1  0x00007f64b0eeddd0 in dbaccess::OColumns::appendObject(rtl::OUString const&, com::sun::star::uno::Reference<com::sun::star::beans::XPropertySet> const&)
    (this=0x56357a993d90, _rForName="test", descriptor=uno::Reference to (dbaccess::OTableColumnDescriptor *) 0x56357ab44d38) at dbaccess/source/core/api/column.cxx:356
#2  0x00007f64d3973c3b in connectivity::sdbcx::OCollection::appendByDescriptor(com::sun::star::uno::Reference<com::sun::star::beans::XPropertySet> const&)
    (this=0x56357a993d90, descriptor=uno::Reference to (dbaccess::OTableColumnDescriptor *) 0x56357ab44d38) at connectivity/source/sdbcx/VCollection.cxx:373
#3  0x00007f64a3d59403 in dbaui::OTableController::alterColumns() (this=0x56357aa546e0) at dbaccess/source/ui/tabledesign/TableController.cxx:1197

appendObject is a generic method which can overloaded in the different DB drivers located in connectivity.

Now between these lines in dbaccess/source/ui/tabledesign/TableController.cxx:
   1196                 ::dbaui::setColumnProperties(xColumn,pField);
   1197                 xAppend->appendByDescriptor(xColumn);
we could add 3 more info in xColumn (which is an XPropertySet):
- column name before (or empty if current is first column)
- column name after (or empty if current is last column)
- position (Firebird begins with 1, https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-ddl-table.html#fblangref40-ddl-tbl-altraltrpos)

Now drivers could use these infos or not so could be a first step.

To give a more precise idea, I attached an incomplete prerequisite patch.
Incomplete because it seems the extra info must be declared in some interface.

I don't think I'll know how keep on this but just wonder if it could be a interesting way to follow for some coders.
Comment 26 Julien Nabet 2023-09-15 21:15:48 UTC
Argh, I did a git diff > patch instead of git diff dbaccess/source/ui/tabledesign/TableController.cxx > patch

so forget about "dbaccess/source/ui/tabledesign/TEditControl.cxx" part in the patch, the main point is in dbaccess/source/ui/tabledesign/TableController.cxx.