supersqlite for sementantic-sql and transitive closure extension

[wdduncan]

I was doing some poking around sqlite extensions and ran across and ran across the supersqlite project. Of particular interest, where the JSON1 and LSM1 (for key/value) pairs the are included.

My thoughts for these extensions are centered around reducing complexity for some queries (e.g., complex axiom expressions).

Also, this transitive closure extension might be of interest too.

[jamesaoverton]

I've been doing research in this area as well, but I missed supersqlite. Thanks!

Pretty much every programming language has SQLite bindings, so I see big advantages to sticking to the stock SQLite features.

• JSON: I think this has been included in SQLite since 3.9.0. I checked just now and was able to call some JSON functions from the sqlite3 CLI shell 3.32 and Python 3.9 sqlite3 module.
• Trees: I've been fairly happy using WITH RECURSIVE to work with tree structures, example from Gizmos.
• LSM: I don't now about this.

What I miss most are proper regular expressions. SQLite has a REGEXP operator but by default it is not bound to an implementation. I think that supersqlite implements it. It only takes a couple of lines in Python but you have to do this each time you connect from whichever language you're using, so you lose the portability that I would like.

[wdduncan]

I've been wary to use recursion with relational databases, but I've never tried it with sqlite. I did some experimenting on another system a few years ago (MySQL maybe ... can't recall) and the performance suffered (alot). Perhaps recursion is more efficient in sqlite.

I mentioned adding functions via python a while ago (see #10). @cmungall didn't seem to like the dependency on python. What I found interesting about supersqlite is that many extensions are already baked in. Perhaps we could bake in more extensions and make a "super-duper-sqlite" :) (or better .. "semantic-sqlite").

LSM is new to me too.

[diekhans]

SQLean (https://github.com/nalgeon/sqlean) has regular expression as an sqlite3 extension. I have never used it, but it looks like a very useful package.

[jamesaoverton]

Thanks. I also see mentions of a sqlite3-pcre package for Debian/Ubuntu (and maybe other Linux distributions), but it's not ubiquitous.

Python's import sqlite3 uses its own SQLite, not the system version, which is a pain. I recently spent a few hours trying to compile a newer version and get Python to use it, without much luck.

[diekhans]

My hand-compiled python3.9 on Linux is using my hand-compiled sqlite3: % ldd lib/python3.9/lib-dynload/_sqlite3.cpython-39-x86_64-linux-gnu.so linux-vdso.so.1 => (0x00007fff9fafc000) libsqlite3.so.0 => /cluster/home/markd/opt/centos7.0/x86_64/lib/libsqlite3.so.0 (0x00007f1cd5fc3000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1cd5da7000) libc.so.6 => /lib64/libc.so.6 (0x00007f1cd59d9000) libz.so.1 => /lib64/libz.so.1 (0x00007f1cd57c3000) libm.so.6 => /lib64/libm.so.6 (0x00007f1cd54c1000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f1cd52bd000) /lib64/ld-linux-x86-64.so.2 (0x00007f1cd64d4000) I don't remember having any issues build this. If you take it on again and have problems, feel free to ping me for another set of eyes. James A. Overton ***@***.***> writes:

…

Thanks. I also see mentions of a `sqlite3-pcre` package for Debian/Ubuntu (and maybe other Linux distributions), but it's not ubiquitous. Python's `import sqlite3` uses its own SQLite, not the system version, which is a pain. I recently spent a few hours trying to compile a newer version and get Python to use it, without much luck. -- You are receiving this because you commented. Reply to this email directly or view it on GitHub: #21 (comment) Thanks. I also see mentions of a sqlite3-pcre package for Debian/Ubuntu (and maybe other Linux distributions), but it's not ubiquitous. Python's import sqlite3 uses its own SQLite, not the system version, which is a pain. I recently spent a few hours trying to compile a newer version and get Python to use it, without much luck. -- You are receiving this because you commented. Reply to this email directly, [1]view it on GitHub, or [2]unsubscribe. References 1. #21 (comment) 2. https://github.com/notifications/unsubscribe-auth/AAA23ZIUUV3KC2RNPPQ4KHLUEDJZBANCNFSM5E27Q5FQ

[jamesaoverton]

Cool!