Bug 160375 - Base does not manage relationships outside the default catalog/schema
Summary: Base does not manage relationships outside the default catalog/schema
Status: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.3.7.2 release
Hardware: All All
: medium normal
Assignee: prrvchr
URL:
Whiteboard: target:24.8.0 target:24.2.4
Keywords:
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2024-03-26 11:05 UTC by prrvchr
Modified: 2024-05-06 08:24 UTC (History)
4 users (show)

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 prrvchr 2024-03-26 11:05:47 UTC
Description:
I try to create relationships between two tables in different schema in Base with: Tools -> Relationships...

If the tables are in the same catalog / schema it works but if the tables are not in the same schema then it does not work.

Steps to Reproduce:
1. Open Base on connect to a database supporting catalog and/or schema
2. Create a new table: Table1, in  default schema PUBLIC with a primary key column ID auto-increment and a second column UID same type of ID.
2. create a new schema in Base with: Tools -> SQL... (ie: CREATE SCHEMA TEST AUTHORIZATION DBA;)
3. Create a new table: Table1, in schema TEST with a primary key column ID auto-increment with same type as PUBLIC.Table1.UID and a second column UID same type of ID.
4. In Base open the foreign key management tool with: Tools -> Relationships... and try to create a foreign key between the two tables PUBLIC.Table1 and TEST.Table1

Actual Results:
You can not

Expected Results:
It would be better to be able to do this...


Reproducible: Always


User Profile Reset: No

Additional Info:
I found a way to make this work, but it's really a hack.
You have to start again at step 4 of how to reproduce.
4. create a new foreign key in Base with: Tools -> SQL... : ALTER TABLE "PUBLIC"."PUBLIC"."Table1" ADD FOREIGN KEY ("UID") REFERENCES "PUBLIC"."TEST"."Table1" ("ID") ON UPDATE CASCADE ON DELETE CASCADE;
5. Important: after creating this foreign key, close Base.
6. If you return to the management of foreign keys in Base then everything works as it should (ie: Base manages the full name of the tables)
Comment 1 prrvchr 2024-03-26 11:44:20 UTC
I can reproduce with the LibreOffice JDBC driver, this does not seem linked to the driver that Base accesses.
In fact this is the ReferencedTable[1] property of the KeyDescriptor that Base passes to the underlying driver when creating a new foreign key.
In this property the underlying driver only gets a table name which is not fully qualified (no catalog name or schema name) and so it doesn't work.
Additionally, I can note that when this works, Base displays the full qualified name of the tables in its relationship management.

[1] https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sdbcx_1_1KeyDescriptor.html#a8b70e9396f149c51bfb5a5fca4a7b3b1
Comment 2 Robert Großkopf 2024-03-26 15:39:44 UTC
Have tested this with LO 24.2.2.2 on OpenSUSE:

Created two tables in different schema.
Tried to link this tables in Tools → Relationship.
Doesn't work with the code, which has been send by Base:

ALTER TABLE "public"."tbl_user" ADD FOREIGN KEY ("Town_ID") REFERENCES "tbl_town" ("ID")

Changed this code and send it through Tools → SQL:

ALTER TABLE "public"."tbl_user" ADD  FOREIGN KEY ("Town_ID") REFERENCES "accounting"."tbl_town" ("ID")

Will work. After closing the Base file and reopening it the relation will be shown in Tools → Relationship.

I'm using direct connection to PostgreSQL here.
Think this never worked.

Version: 24.2.2.2 (X86_64) / LibreOffice Community
Build ID: d56cc158d8a96260b836f100ef4b4ef25d6f1a01
CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded
Comment 3 prrvchr 2024-03-26 17:25:44 UTC
Hi Robert,

Thank you for your confirmation.

I think I found where this is happening in the code. I'm in the process of compiling and if it works I will submit a PR.
Comment 4 prrvchr 2024-03-27 09:52:36 UTC
Well apparently that fixed the problem. I had to make two changes:
- Return the full name of the table in the ReferencedTable property.
- Modify the title of a table window to display the full name of the table.

see PR 165367[1] 

[1] https://gerrit.libreoffice.org/c/core/+/165367
Comment 5 prrvchr 2024-03-28 09:20:52 UTC
Well, I submitted a PR, it was verified by JenKins.
But without reviewer, nothing will happen. What about it?
Comment 6 prrvchr 2024-04-12 21:31:40 UTC
Please find me a reviewers...
Comment 7 Robert Großkopf 2024-04-13 06:44:33 UTC
(In reply to prrvchr from comment #6)
> Please find me a reviewers...

Don't know how it works. I will try to put 2 persons into CC. Hope it helps.
Comment 8 prrvchr 2024-04-13 08:08:49 UTC
Thank you Robert
Comment 9 Stéphane Guillou (stragu) 2024-04-15 02:12:16 UTC
Patch is now at https://gerrit.libreoffice.org/c/core/+/166057
Comment 10 prrvchr 2024-04-15 11:46:10 UTC
Thanks stragu.

Normally the fix will be available with 24.2.4: https://gerrit.libreoffice.org/c/core/+/166093
Comment 11 Commit Notification 2024-05-03 13:14:56 UTC
Pierre committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/548fc442f56d9b456e48246caaffe13d996c841f

tdf#160375 Base displays and gives the full name of the table

It will be available in 24.8.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 12 Commit Notification 2024-05-06 08:24:00 UTC
Pierre committed a patch related to this issue.
It has been pushed to "libreoffice-24-2":

https://git.libreoffice.org/core/commit/eb045cc529f0e7b17e7e3bc913f2ab1748247966

tdf#160375 Base displays and gives the full name of the table

It will be available in 24.2.4.

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.