Ask Your Question
0

LibreBase and SQLite/Spatilite

asked 2016-09-09 15:56:20 +0100

Tyler gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2016-09-09 18:18:08 +0100

Ratslinger gravatar image

updated 2016-09-12 03:26:29 +0100

@Tyler 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:

image description

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).

edit flag offensive delete link more

Comments

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 http://ch-werner.de/sqliteodbc/html/i... to understand the connection parameters of OBDC connection.

Tyler gravatar imageTyler ( 2016-09-09 22:51:01 +0100 )edit

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.

Tyler gravatar imageTyler ( 2016-09-10 00:05:52 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2016-09-10 00:06:15 +0100 )edit

@Tyler 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).

Ratslinger gravatar imageRatslinger ( 2016-09-10 03:29:13 +0100 )edit

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...

Tyler gravatar imageTyler ( 2016-09-11 19:28:43 +0100 )edit

I appreciate your help on this but I was only giving up on it for the weekend lol.

Tyler gravatar imageTyler ( 2016-09-11 19:29:48 +0100 )edit

@Tyler - got it to work but answer too long for this space will edit my original answer.

Ratslinger gravatar imageRatslinger ( 2016-09-12 02:59:31 +0100 )edit

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.

Tyler gravatar imageTyler ( 2016-09-12 14:18:31 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2016-09-12 15:30:27 +0100 )edit

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.

Tyler gravatar imageTyler ( 2016-09-12 16:53:46 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-09 15:56:20 +0100

Seen: 2,266 times

Last updated: Sep 12 '16