I built a new database in SQLite Spatialite database using QGIS. I loaded the tables in from my other database (wotbl, wo_civic_jtble, civic_tbl). the wo_tbl has work orders, the civic_tbl civic addresses, and the wo_civic_jtbl has a composite PK using the PK of the wo_tbl and the civic_tbl (this is probably sounding familiar to my other questions). When I try to build the table relations I get the error; “This database does not support relationships” which isn’t true SQLite supports relationships so I am wondering if this is an issue with the ODBC Driver and LibreBase cannot support relationships for SQLite databases yet or if I set it up wrong.
ODBC SQLite3.0
LibreBase 5.2.1.2
OS Win7
If anyone has any experience with SQLite and LibreBase and can provide some assistance that would be greatly appreciated. Further to that if you need any additional information let me know.
Thanks
Tyler
@Tyler1 I don’t have any experience with SQLite but found this post (click here) and a few others like it referring to Enable Foreign Key Support. It seems it may be off as a default and may need to be turned on. See "2. Enabling Foreign Key Support " in the post.
Edit (9/11/16):
Could not help much without the actual software so decided to install. Worst to get working without a doubt (have HSQLDB server, MySQL server, and PostgreSQL server). It certainly has limited capabilities. Except for testing (and maybe portability) not worth it in my book.
There are multiple things to adhere to. LO will NOT let you set up relations. That doesn’t mean it won’t honor them. Quite a bit of net searching and trial and error (even went so far as to completely compile SQLite) led me to these results. The tool I found most useful was DB Browser for SQLite (Synaptic Package Manager - sqlitebrowser). After many pages, I remembered seeing (can’t locate it again) where Foreign Keys setting needed to be set when CREATING the DB. So - created new DB with it on. Still no luck in LO but now the setting was on (couldn’t seem to change the setting in originally created DB). Next, again using DB Browser set up two tables with data. Visible/accessible in LO - OK! Created Foreign Key using DB Browser and tested with the two tables - got FOREIGN KEY constraint failed when appropriate - OK, foreign keys can be set. Went back to LO and tried same thing - got this result:
To me this means you can have foreign keys honored in LO but just can’t set them up within LO ( or see them using Tools->Relationships).
I enabled the FKey and it didn’t work, also created a new DB and connection when I enabled the FKey Constraints but no change in behavior. Used this link Main Page to understand the connection parameters of OBDC connection.
After trying OpenBase and a number of other drivers and settings I am starting to think the drivers or the application cannot support the relationships within an SQLite database.
The only other item I found was Bug Report 82688. In comment 11 a possible situation exists with the ODBC driver although not specifically mentioning your problem. It does indicate to me a potential connection to your problem.
@Tyler1 as still unwilling to just give in,on this post(click here) it mentions that with PostgreSQL you can also check the box to 'Also list tables with no geometry' and voila - you have your non-spatial PostgreSQL tables accessible too!!!. Is this an option in SQLite? Possibility of the cause of your problem (I have only todays searching of spatial DB’s to rely upon).
The option is in QGIS not LibreOffice using it allows you to import non spatial tables which is something I am doing as well in Q. I am using SQlite because it works well with QGIS, holds XYZM spatial values, is comparable to PostGIS, and is a mobile “file” like database package. The LibreOffice bit is suppose ton provide a interface to the database to update records and asset information but Libre isn’t playing along nice with SQLite, but neither is Open Office…
I appreciate your help on this but I was only giving up on it for the weekend lol.
I got that error as well when I tried to add a record to the jtbl_a (which is now jtbl_cwo because I had to create a new table to add the FKey constraints apparently ALTER will not work for this). Then I moved to my entry form to see if that worked, the form throws an error “The contents of combo box of list field could not be determined. RIGH and FULL OUTER JOINs are not currently supported (1)” I put this to the database administrators exchange but this is not looking good.
As stated in my answer, SQLite has limited capabilities. One is the requirement of ODBC which is not one of my choices. I believe there was/is a JDBC connector but I’ve seen where there were problems with the JDBC to ODBC Bridge in Java and I think that is dropped in Java 8.
I have both ODBC and JDBC connectors installed and have been testing both but they both seem to function nearly the same. You are right the SQLite is very limited and I might have to try another provider.
Thanks for your time on this, learned a few things from it at the least.