Sqlite3 Settings to use Virtual graphs

Support
1

:warning: The information in this thread and other thread related to SQLite have been consolidated into a Tips and Tricks topic. SQLite3 Information

Until now I use sqlite often, but only with stardog-admin virtual import.

Today tried experimenting with 'virtual add'. Initially the SQL generated use FETCH, which SQLite does not support. By perusing the community I found that I could easily resolve that by adding the property sql.dialect=MySQL which will use LIMIT in lieu of FETCH.

However now I am getting an "Out of bound" error from Stardog (logs attach)

@zachary.whitley from our last discussion SQLite Revisited, I got the sense you are using virtual add with sqlite. Do I need another options set in my property file.

jdbc.url=jdbc:sqlite:/var/opt/stardog/accounts.db
jdbc.username=blah
jdbc.password=blah
jdbc.driver=org.sqlite.JDBC
sql.default.schema=main
sql.dialect=MYSQL

log.txt (2.4 KB)

Can always use Postgres, but I love how light SQLite is.

-Serge

2

Hey Serge,

We do have some use of SQLite internally and plans to improve it. For the moment, can you try adding sql.dialect=POSTGRESQL? The out of bounds error is a MySQL specific behavior (brought on by sql.dialect=MYSQL) which attempts to set the fetch size to -1 to prevent the client driver from buffering the entire result set.

Jess

Jess

3

That dialect use FETCH which SQLite does not support.

Caused by: com.complexible.stardog.StardogException: Unable to execute virtual graph query. SQL string: SELECT "account_id", "apr"

FROM "main"."accounts"

WHERE "account_id" IS NOT NULL AND "apr" IS NOT NULL

FETCH NEXT 1000 ROWS ONLY

at com.complexible.stardog.virtual.vega.rdbms.RdbmsRelExecutor.lambda$execute$0(RdbmsRelExecutor.java:56) ~[stardog-virtual-core-7.6.0.jar:?]

at com.complexible.stardog.virtual.vega.rdbms.RdbmsRelExecutor$1.getResult(RdbmsRelExecutor.java:99) ~[stardog-virtual-core-7.6.0.jar:?]

at com.complexible.stardog.virtual.vega.VirtualServiceQuery.execute(VirtualServiceQuery.java:232) ~[stardog-virtual-core-7.6.0.jar:?]

at com.complexible.stardog.virtual.vega.VirtualServiceQuery.evaluate(VirtualServiceQuery.java:255) ~[stardog-virtual-core-7.6.0.jar:?]

at com.complexible.stardog.plan.eval.operator.impl.ServiceOperatorImpl.computeNext(ServiceOperatorImpl.java:96) ~[stardog-7.6.0.jar:?]

... 34 more

Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "FETCH": syntax error)
4

sql.dialect=Hive seem to work. Will perform more testing tomorrow.

@jess thanks for your help.

5

Hi @serge. I had messed around with that a while ago. There were only a couple of small tweaks I came across to get sqlite working. You found the first one with setting the dialect. The other was including sql.default.schema=main. You also have to be a little careful about table names. You'll either need to quote them or make sure they're in all caps. I played around with putting the properties file and mappings into the sqlite db so you could attache a sqlite vg with a single command. I was inspired with and wanted to work with data published by https://datasette.io . They're backed by a sqlite database. You also might want to look into DuckDB. https://duckdb.org . I've been meaning to do it myself but the published binaries use a newer version of glibc than I've got and I haven't gotten around to building it from scratch yet.

6

Here is a little summary of the dialect I tested

=============================================
| Dialect   |  LIMIT  |  CONCAT | Need Quote|
=============================================
|           |  No     |    Yes  |    Yes    |
| ORACLE    |  No     |    Yes  |    Yes    |
| HIVE      |  Yes    |     No  |    No     |
| MYSQL     |  Crash  |    Yes  |    ?      |
| ATHENA    |  Yes    |    Yes  |    No     |
| PROGRESQL |  Yes    |    Yes  |    No     |
-------------------------------------------

To use sqlite with stardog-admin virtual add you must have yes in the LIMIT column.

To use sqlite concat feature field1 || ' ' || field2 you must must have yes in the CONCAT column

The quote column is just a nice not to have to quote your column or table because there not in upper case.

So far, ATHENA, and PROGRESQL seem the best dialect to use with sqlite, at least until the next issue arise and we extend this table :smile:

1 Like
7

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.