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)
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
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
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.
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
Well, I submitted a PR, it was verified by JenKins. But without reviewer, nothing will happen. What about it?
Please find me a reviewers...
(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.
Thank you Robert
Patch is now at https://gerrit.libreoffice.org/c/core/+/166057
Thanks stragu. Normally the fix will be available with 24.2.4: https://gerrit.libreoffice.org/c/core/+/166093
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.