For all I know, sqlite is a relational database (cf. https://www.sqlite.org/foreignkeys.html). I was toying around with an sqlite database connected via ODBC on Ubuntu trusty (LO version 4.2.8.2) and went to the menu "Tools - Relationships" only to receive an error "The database does not support relations" which it seems is certainly wrong. Is this a problem of the ODBC connector?
@Rolf: can provide a test sqlite db file that defines relations so we can test ? Setting to NEEDINFO
@Alex, thank you for the quick response. I only saw it now. Well, I can't enter the relationship manager so I can't define relationships. I'll try to produce a rudimentary test case but it won't have relationships.
Created attachment 117473 [details] Very simplistic sqlite database Here is a very simplistic database with two tables that should be related on the Person ID field. Libreoffice tells me I cannot do it because the database does not support it.
Created attachment 117474 [details] screenshotof the error message
@Rolf : with a jdbc driver from Xerial, so far I have been unsuccessful at even opening your tables for reading data, let alone anything else. It appears however, that your tables don't have any primary keys ? That is a minimum for anything to work with LO. I get a : "SQLite supports TYPE_FORWARD_ONLY cursors" error message when I try to open any of the tables. Even if I create a table myself and add a primary key save the table and attempt to reopen the table, I get the same error message. I even tried altering the table definition from Tools > SQL, to no avail. As for relations support, this was only implemented in SQLite with version 3.6.19. I don't know which version of sqlite LO supports currently, but quite possibly only earlier than that. Adding usual db people to CC, perhaps they can shed some light on this. I can't unless I find a way to actually be able to manipulate the database from within LO.
My tests were carried out on master 5.1 alpha with the 3.8.10.2 version of sqlite.jar
Forgot to add that I see the same error message as you do when one clicks on Relations, but I thought that might be linked to the absence of primary keys in the table definitions, or alternatively, if there are primary keys, these are not seen by LO.
Is there *any* database where Tools/Relations works with ODBC?
(In reply to Lionel Elie Mamane from comment #8) > Is there *any* database where Tools/Relations works with ODBC? OUCH! What's the use of ODBC for any RDBMS without the relations? A simple table in Calc would likely be a better option in that case. Are you seriously saying that the ODBC connector does not support relations for *any* of the DB?
(In reply to Rolf Leggewie from comment #9) > (In reply to Lionel Elie Mamane from comment #8) > > Is there *any* database where Tools/Relations works with ODBC? > > OUCH! > > What's the use of ODBC for any RDBMS without the relations? A simple table > in Calc would likely be a better option in that case. Are you seriously > saying that the ODBC connector does not support relations for *any* of the > DB? Please read the content of a comment before answering: Tools → Relations works with the internal HSQLDB. Could be it works with some external databases. It doesn't say anything about the ODBC-connector. You could define relations directly by Tools → SQL and the relations will work well. Only the GUI won't offer a way to create this relations. Don't know if there is any database, connected with ODBC, where Tools → Relations will work.
(In reply to Rolf Leggewie from comment #9) > (In reply to Lionel Elie Mamane from comment #8) >> Is there *any* database where Tools/Relations works with ODBC? > OUCH! > What's the use of ODBC for any RDBMS without the relations? If the RDBMS supports relations and they are created "not through the LibreOffice GUI", then the relations are still in full effect... What is being discussed here is showing, modifying and creating relations from the LibreOffice GUI. That's the same difference between writing a SQL query and using the graphical query builder. > Are you seriously saying that the ODBC connector does not support > relations for *any* of the DB? I'm wondering whether that is the case, not saying it is.
Thank you for the clarifications. I guess then we should discuss what bug there is if any. I think I see three a) the failure message is misleading and incorrect b) RFE to get the ODBC driver to support Tools/Relationship c) apparent incompatibilities in the sqlite DB created by ODBC and JDBC driver I am not sure if b) is possible after all and if were an LO bug since the driver is maintained separately. BTW, what is the recommended sqlite driver to use these days? Alex, how do you activate the JDBC driver (classpath?) and what is the syntax for connecting to an sqlite database via JDBC?
FWIW, testing on LO 4452 production release with Xerial JDBC driver produces the same error message Confirming therefore on current release.
just wanted to point out that the very simplistic sqlite database provided here for testing purposes DOES have primary keys. As we've found out by now, it seems the Xerial driver has a problem to deal with primary keys in sqlite. $ sqlite3 /tmp/contact.sqlite .dump|grep KEY CREATE TABLE "Person" ("PersonID" integer NOT NULL,"Name" varchar(100), PRIMARY KEY ("PersonID")); CREATE TABLE "contact" ("PersonID" integer,"Telephone" bigint NOT NULL, PRIMARY KEY ("Telephone"));
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.5 or 5.2.1 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
Still present in master 5400 alpha
Also tested against : Version: 5.2.3.3 Build ID: d54a8868f08a7b39642414cf2c8ef2f228f780cf Threads CPU : 2; Version de l'OS :Mac OS X 10.12.1; UI Render : par défaut; Locale : fr-FR (fr_FR.UTF-8); Calc: group using Christian Werner's ODBC (libsqliteobdc) driver 0.9994 version for OSX. The error message is the same.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
(In reply to Lionel Elie Mamane from comment #8) > Is there *any* database where Tools/Relations works with ODBC? Well, it appears to work with MyODBC and a mysql database (at least on MacOSX). If I click on Relations with an open MyODBC connected ODB file, then the relations definition window is displayed and I can add tables, define the relationship, etc.
Tested against : Version: 5.4.3.2 Build ID: 92a7159f7e4af62137622921e809f8546db437e5 Threads CPU : 8; OS : Mac OS X 10.13.1; UI Render : par défaut; Locale : fr-FR (fr_FR.UTF-8); Calc: group with Xerial sqlite-jdbc-3.21.0.jar The same error message is still there when one clicks on the Relations menu entry.
Dear Rolf Leggewie, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Dear Rolf Leggewie, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Dear Rolf Leggewie, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
With the test database provided by the OP, I get an error message "This database does not support relations". Using JDBC driver 3.40 LO Dev Version: 7.6.3.0.0+ (X86_64) / LibreOffice Community Build ID: 9395171aa8641341316f87e2537dcdfa3df4ef78 CPU threads: 8; OS: Mac OS X 14.0; UI render: Skia/Raster; VCL: osx Locale: fr-FR (fr_FR.UTF-8); UI: en-US Calc: threaded The error message is wrong though with regard to the sqlite file, as : PRAGMA foreign_keys = '1' is defined. What the driver is returning and what the database supports, appear to be two different things.