[sqlite] vtfunc wrapper for Python

Hi, I just wanted to share a new tool I wrote that makes it very easy to write table-valued functions with Python. Basically this is a wrapper around a virtual table, allowing the Python developer to write two methods (initialize and iterate) and get the full table-valued function benefits. The

[sqlite] Virtual tables and table-valued functions

In working on a Python wrapper around virtual tables, I thought it might be beneficial if SQLite provided an official C API for creating simple table-valued functions. The wrapper could build on the existing virtual table APIs and would consist of: * user supplied list of parameters, which would

[sqlite] sqlite3_column_name() issue with views

I was surprised to find that, depending on whether I queried a table or a view, sqlite3_column_name would return different values for the column name. Specifically, when the table is aliased and the columns in the SELECT clause are quoted, sqlite returns an unquoted, unaliased column name, e.g. "

[sqlite] lsm1

I see that there's a new `lsm1` extension directory in the `lsm-vtab` branch. May I ask what the intended use-cases are for the LSM vtab? I've done some writing and experimenting[1] with the lsm in sqlite4 and it's a pretty neat tool. Be curious what you all see as the uses as a virtual table.

[sqlite] lsm1

Looking at the table definition, it has a key, value and hidden `command` column, so guessing the intent is to provide a fast ordered key/value store? On Wed, Nov 18, 2015 at 11:31 AM, Charles Leifer wrote: > I see that there's a new `lsm1` extension directory in the `lsm-vtab` > branch.

[sqlite] Standalone use of b-tree?

Has anyone written an application using the SQLite btree as a standalone component?

[sqlite] FTS5 prefix index documentation may be incorrect

The FTS5 prefix index documentation[1] seems to not be working. I've tried with SQLite 3.9.0 and 3.10.0 (2015-11-06) and both show the same error messages. Examples: sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3'); Error: malformed prefix=... directive sqlite> CREATE VIRTUAL TABLE

[sqlite] FTS5 prefix index documentation may be incorrect

Thank you for the quick fix, and thank you so much for SQLite! On Nov 25, 2015 5:57 AM, "Dan Kennedy" wrote: > On 11/25/2015 05:11 AM, Charles Leifer wrote: > >> The FTS5 prefix index documentation[1] seems to not be working. I've tried >> with SQLite 3.9.0 and 3.10.

[sqlite] extension development

You could create a virtual table and use insert statements to generate new functions. Not sure if that's actually any better though. On Nov 25, 2015 3:10 PM, "Abilio Marques" wrote: > Hi, > > I've finished a version of an extension that let's you define new SQL > functions using JavaScript, > >

[sqlite] What's the status of SQLite4? Where can I find its source code?

There was a flurry of postings on HackerNews about SQLite4 that made it seem like it might be arriving soon, and people might have got excited and heard about it that way, I know I did. On Mon, Oct 5, 2015 at 9:47 AM, Scott Robison wrote: > It seems to me the reason these questions keep coming

[sqlite] Ideas on how to use backup API for remote, incremental backup?

You could mount a directory as a ramdisk. On Tue, Oct 6, 2015 at 7:52 AM, David Barrett wrote: > On Tue, Oct 6, 2015 at 2:57 PM, Clemens Ladisch > wrote: > > > It backs up to any disk that you can access. > > Do you have a network file system? > > > > Well yes, but I'd like to handle it at the

[sqlite] Fwd: OT: Oracle functions for SQlite

This is really cool! I wonder if anyone else has collections of neat user-defined functions/aggregates/virtual tables/etc? Is there a canonical repository of these? Anyone know of some interesting ones? On Tue, Sep 8, 2015 at 12:46 PM, Petite Abeille wrote: > Perhaps of interest: > >

[sqlite] Using the json1 extension with Python

I spent some time this week figuring out how to compile ghaering's `pysqlite` library with support for the new SQLite json extension. Finally got it working and I must say it's pretty damn exciting! Here's a blog post I wrote that covers everything from compiling sqlite with JSON to running

[sqlite] Querying nextchar extension

I've been playing around a bit with the `nextchar` extension and I must say it's pretty neat! Basically I create a table named `words` with a single TEXT column named `word` and populate it with a dictionary file. Then I can run: SELECT nextchar('partial word', 'words', 'word'); And it will

[sqlite] Querying nextchar extension

I was thinking of dynamically building up the strings using the `nextchar` extension but your example works just fine. On Fri, Sep 18, 2015 at 9:52 AM, Igor Tandetnik wrote: > On 9/18/2015 9:05 AM, Charles Leifer wrote: > >> As a challenge I was curious how one would write a r

[sqlite] FTS design document

Is anyone aware of a design doc for any of the FTS implementations? Looking for something a bit more technical than the docs. If not, where in the source would you recommend starting? Thanks!

[sqlite] Question about BerkeleyDB "SQLited"

My "dbsql" is reporting version 3.8.3.1: ./dbsql --version 3.8.3.1 2014-02-11 14:52:19 ea3317a4803d71d88183b29f1d3086f46d68a00e I grabbed the berkeleydb code from Oracle's website, then compiled it with SQL compatibility. On Sun, Feb 21, 2016 at 9:37 AM, javaj1811 at elxala.com wrote: > Hi,

[sqlite] FTS design document

Perfect, thank you! On Feb 22, 2016 1:55 AM, "Dan Kennedy" wrote: > On 02/21/2016 03:54 PM, Charles Leifer wrote: > >> Is anyone aware of a design doc for any of the FTS implementations? >> Looking >> for something a bit more technical than the docs. If not

[sqlite] Some FTS5 guidance

You can create a custom tokenizer as well then use the standard search APIs. I imagine that functionality would work well in this case: https://sqlite.org/fts5.html#section_7 On Thu, Jan 7, 2016 at 3:59 PM, Stadin, Benjamin < Benjamin.Stadin at heidelberg-mobil.com> wrote: > One such algorithm

Re: [sqlite] Unable to clone fossil repo

I was running 1.33, which was the version available in Ubuntu 16.04's repos. Doing a quick download form the website took care of the issue. Thank you! On Thu, Mar 16, 2017 at 11:51 AM, Richard Hipp wrote: > On 3/16/17, Richard Hipp wrote: > > > > As of

[sqlite] Unable to clone fossil repo

Hi, I'm attempting to clone the source tree using the instructions from the website (http://www.sqlite.org/getthecode.html#clone), but I'm getting an error when "Artifacts received" hits 58416: $ fossil clone http://www.sqlite.org/cgi/src sqlite.fossil Round-trips: 7 Artifacts sent: 0

Re: [sqlite] Unable to clone fossil repo

Nevermind, I just needed to upgrade from 1.33 to 2.0. On Ubuntu 16.04 the system version is 1.33, but I was able to download the binary from the fossil-scm site and everything worked like a charm. Thanks for the amazing, free software! On Thu, Mar 16, 2017 at 7:02 AM, Charles Leifer <co

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

This bit me... I fat-fingered a command and deleted my database. I had a backup dump taken earlier in the day. Go to restore it and all of a sudden this error starts cropping up. I'm stuck. I need to get this database back online, but even with 4000 limit I am losing INSERTs. I get segfaults when

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

I hate to be critical of Dr Hipp, but this commit stinks. Even if using the sqlite3 shell isn't the blessed way of producing a backup, I'm sure a lot of folks prefer it to the online backup API. It's this simple: "echo .dump | sqlite3 my_db.db | gzip > my_db.sql.gz" That's beautiful. And now it

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

Is it conceivable that this change could be rolled back? On Tue, Apr 4, 2017 at 9:45 AM, Charles Leifer <colei...@gmail.com> wrote: > I hate to be critical of Dr Hipp, but this commit stinks. Even if using > the sqlite3 shell isn't the blessed way of producing a backup, I'm sure a &g

[sqlite] LSM1 extension

Hi, I was pleased to see that 3.20 contained the source for the LSM1 virtual table, even if not included in the official announcements. My excitement quickly turned to disappointment as I realized that the extension is unusable for all practical purposes: - Single-key equality lookups are

Re: [sqlite] LSM1 extension

, 2017 at 10:48 PM, Richard Hipp <d...@sqlite.org> wrote: > On 8/4/17, Charles Leifer <colei...@gmail.com> wrote: > > > > My excitement quickly turned to disappointment as I realized that the > > extension is unusable for all practical purposes: > &g

Re: [sqlite] LSM1 extension

There's some information that may be of interest on the sqlite4 wiki: * Design overview: https://sqlite.org/src4/doc/trunk/www/lsm.wiki * User guide: https://sqlite.org/src4/doc/trunk/www/lsmusr.wiki * API: https://sqlite.org/src4/doc/trunk/www/lsmapi.wiki My interest in this particular feature

Re: [sqlite] LSM1 extension

> > From: Charles Leifer<mailto:colei...@gmail.com> > Sent: 08 August 2017 21:21 > To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] LSM1 extension > > There's some information that may be of interest on the sqlite4 wiki: > >

Re: [sqlite] lsm1 extension

Thanks for the clarification! On Tue, Jun 20, 2017 at 2:41 AM Dan Kennedy <danielk1...@gmail.com> wrote: > On 06/20/2017 12:42 PM, Charles Leifer wrote: > > Hi, > > > > I'm not quite sure of the proper way to compile the lsm1 extension (in > the > > lsm-v

[sqlite] lsm1 extension

Hi, I'm not quite sure of the proper way to compile the lsm1 extension (in the lsm-vtab branch). I ended up hand-hacking the makefile to replace $(TCCX) and add the appropriate flags for gcc (-fPIC) to get it to build. I'd like to do it the "right way", though, as I was hoping to share some

[sqlite] Next release add flag for building LSM1?

I was wondering if there were plans for adding a simple toggle for building the LSM virtual table? For example, to build json1, one can: export CFLAGS="... -DSQLITE_ENABLE_JSON1 ..." Be handy to have a SQLITE_ENABLE_LSM1 as well. Thanks for all your hard work.

Re: [sqlite] LSM database file growth?

if keys within that space are frequently updated, deleted, or re-inserted. On Tue, Oct 31, 2017 at 11:16 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 10/31/2017 10:50 PM, Charles Leifer wrote: > >> Is the LSM database append-only, as in the file size will always >> gr

[sqlite] LSM database file growth?

Is the LSM database append-only, as in the file size will always grow/never shrink (even if there are deletions/overwrites)? Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Question about sqlite3_prepare vs sqlite3_prepare_v2

Hi, I was debugging some discrepancies in execution times of some queries using the Python 3.6 standard library sqlite3 driver. I was wondering if these discrepancies could be explained by the fact that the Python sqlite3 driver is using the legacy sqlite3_prepare interface? Just to be clear: I

Re: [sqlite] fail building snapshot 201805081303

Try specifying "-lm" flag to include math library. On Fri, May 11, 2018 at 2:24 PM, Richard Hipp wrote: > I am unable to reproduce the problem. What system are you doing this on? > > On 5/10/18, Michele Dionisio wrote: > > building snapshot

Re: [sqlite] ALTER TABLE

As a workaround, you can always rename the existing table, create the new table with desired attributes, and do a INSERT INTO ... SELECT FROM old_table. Then you can safely drop the old table. On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz wrote: > > ALTER TABLE ADD COLUMN

Re: [sqlite] ALTER TABLE

SQLite supports renaming tables, so in my experience you move the old table out of the way, and create the new table with the desired schema and the original name. On Tue, May 22, 2018 at 2:34 PM, Igor Korot <ikoro...@gmail.com> wrote: > Hi, Charles, > > On Tue, May 22, 2018 at 2

Re: [sqlite] Segfault in window function

Beautiful, working on my end now as well. Thanks so much. On Mon, Jul 2, 2018 at 12:49 PM, Dan Kennedy wrote: > On 07/02/2018 10:54 PM, Charles Leifer wrote: > >> I'm getting a segfault with the following query, which uses window >> functions: >> > > Thanks

Re: [sqlite] Segfault in window function

/local/lib/libsqlite3.so.0 No symbol table info available. On Mon, Jul 2, 2018 at 10:54 AM, Charles Leifer wrote: > I'm getting a segfault with the following query, which uses window > functions: > > SELECT "t1"."counter", "t1"."value", RANK() OVE

[sqlite] Segfault in window function

I'm getting a segfault with the following query, which uses window functions: SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank" FROM "sample" AS "t1" WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC) ORDER BY "t1"."counter", RANK() OVER w The sql used to create the

Re: [sqlite] Segfault in window function

uot;id" ROWS 2 PRECEDING) FROM "sample" ORDER BY "id" Expected results: 1, 10., 10. 1, 20., 30. 2, 1., 31. 2, 3., 24. 3, 100., 104. SQLite is reporting the following results: 1, 10., 10. 1, 20., 30. 2, 1., 31. 2, 3., 44. 3, 100., 164. I hope that helps diagnose the issue.

[sqlite] Bug in transitive closure extension?

epth" FROM "category" AS "t1" INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") WHERE (("t2"."id" = 4) AND ("t2"."depth" > 0)); -- results: -- 1|books||3 -

Re: [sqlite] Bug in transitive closure extension?

Thank you so much. I'm always impressed by the responsiveness of the SQLite team. Very grateful for your help and hard work. On Wed, May 2, 2018 at 3:13 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 05/02/2018 03:50 AM, Charles Leifer wrote: > >> Hi all, >&g

Re: [sqlite] Add Column with "If Not Exists"

You can simply use: PRAGMA table_info('my_table') To get a list of columns, which you can check against and then conditionally add your column. On Wed, Aug 1, 2018 at 9:13 AM, Tim Streater wrote: > On 01 Aug 2018, at 14:34, Simon White > wrote: > > > I would like to suggest the addition of

[sqlite] Typo in window function docs

In section 2, the docs read: The default is: RANGE BETWEEN UNBOUNDED PRECEDING TO CURRENT ROW I believe it should read instead: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Help with json1 query?

Hi, I'm prototyping a little graph library using SQLite. My idea is to store vertices in a simple table like this: CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON); CREATE TABLE "edge" ( "id" INTEGER NOT NULL PRIMARY KEY, "src" TEXT NOT NULL, "dest" TEXT NOT

Re: [sqlite] Help with json1 query?

could store them as columns? > > There's also the https://sqlite.org/rtree.html extension which lets you > efficiently query multidimensional range data. > > If there is truly no schema, what you propose is the only way AFAIK. > > On Wed, Feb 28, 2018, 10:52 PM Charles L

[sqlite] Window functions

Hi, I'm sure this has been asked before, but are window functions on the roadmap? Is it the authors' experience that the implementation would significantly complicate sqlite? Just curious. Thanks so much for a fantastic library. Charlie ___

[sqlite] Strip leading "The" in alphabetical TOC?

Hi, In the documentation alphabetical listing, it threw me off when I was lookup up the JSON1 docs and didn't find them under "J", due to the title being "The JSON1 Extension". Just a suggestion: you might strip leading "The " from the titles in the documentation list?

Re: [sqlite] Regarding CoC

I dig the new CoC (not that anyone's counting). I'll share my comment from HN: If the code of conduct angers you, stop and think -- how did you feel one minute before you read the CoC? Is the problem really the CoC, or is it your collection of beliefs that is causing the problem? Furthermore,

[sqlite] ON CONFLICT with partial indexes

I'm having trouble executing an INSERT ... ON CONFLICT with a partial index. It works fine in the SQLite shell, but it fails when I express the conflict constraint using a parameterized query. For example: CREATE TABLE ukvp ("id" integer primary key, "key" text not null, "value" int not null,

Re: [sqlite] ON CONFLICT with partial indexes

17, 2019 at 2:58 PM Simon Slavin wrote: > On 17 Feb 2019, at 8:51pm, Charles Leifer wrote: > > > Is this a bug? > > Just to make things easier, which version of SQLite are you using in your > Python SQLite library ? If you don't know, you can find this out using >

Re: [sqlite] ON CONFLICT with partial indexes

Olivier, what do you mean "snapshot"? What is a release? How can I find out about the SQLite "releases" you are talking about? On Sun, Feb 17, 2019 at 3:23 PM Olivier Mascia wrote: > > Le 17 févr. 2019 à 22:05, Charles Leifer a écrit : > > > >

Re: [sqlite] ON CONFLICT with partial indexes

Simon, I appreciate that, but your comment seems to contradict the example I provided, as well as the example Dr. Hipp provided. Am I misunderstanding? On Mon, Feb 18, 2019, 9:44 AM Simon Slavin On 18 Feb 2019, at 3:23pm, Charles Leifer wrote: > > > At some point before actually

Re: [sqlite] ON CONFLICT with partial indexes

hese indexes would be unusable. On Mon, Feb 18, 2019 at 10:54 AM Simon Slavin wrote: > On 18 Feb 2019, at 4:41pm, Charles Leifer wrote: > > > Simon, I appreciate that, but your comment seems to contradict the > example I provided, as well as the example Dr. Hipp provided. Am I > m

Re: [sqlite] ON CONFLICT with partial indexes

17, 2019 at 5:26 PM Richard Hipp wrote: > On 2/17/19, Charles Leifer wrote: > > I'm having trouble executing an INSERT ... ON CONFLICT with a partial > > index. It works fine in the SQLite shell, but it fails when I express the > > conflict constraint using a parameterized qu

[sqlite] Wildcard with path in json extension

Many of the sqlite json1 functions accept a path parameter, which the documents describe as: For functions that accept PATH arguments, that PATH must be well-formed or else the function will throw an error. A well-formed PATH is a text value that begins with exactly one '$' character followed by

[sqlite] Json paths

Many of the sqlite json1 functions accept a path parameter, which the documents describe as: For functions that accept PATH arguments, that PATH must be well-formed or else the function will throw an error. A well-formed PATH is a text value that begins with exactly one '$' character followed by

Re: [sqlite] Json paths

the window functions, on conflict, etc which have been added in the last few releases. Amazing stuff. Just wanted to see if json path would be even on the road map. On Mon, Apr 15, 2019, 4:57 AM Dominique Devienne wrote: > On Mon, Apr 15, 2019 at 6:34 AM Charles Leifer wrote: > > > I w

[sqlite] BLOB and TEXT comparisons

I ran into a somewhat surprising result and wanted to just get a little clarification. I'll use the following statement as an example: SELECT SUBSTR(?, 1, 3) == ? And the parameters will be: * "abcde" * "abc" If I bind both parameters using the same type, the comparison returns True: *

Re: [sqlite] Row values with IN

arify the behavior of IN with row values? SELECT * FROM info WHERE (year,month,day) IN (VALUES (2019, 1, 1), (2019, 2, 1)); On Tue, Apr 23, 2019 at 10:03 AM Simon Slavin wrote: > On 23 Apr 2019, at 3:52pm, Charles Leifer wrote: > > > My question, though, is why is the VALUES bit

Re: [sqlite] Row values with IN

generalize this kind of approach. It works fine for scalar primary keys, but is having issues when the key is composed of multiple columns. On Tue, Apr 23, 2019 at 9:35 AM Simon Slavin wrote: > On 23 Apr 2019, at 3:11pm, Charles Leifer wrote: > > > UPDATE "bu" SET "ext

Re: [sqlite] Row values with IN

to specify VALUES instead of simply passing in a set of row tuples? It seems a little bit inconsistent to me. Thanks! On Tue, Apr 23, 2019 at 9:49 AM Simon Slavin wrote: > On 23 Apr 2019, at 3:42pm, Charles Leifer wrote: > > > Thanks Simon -- this is just a minimal example. The WHERE clau

[sqlite] Row values with IN

Let's say I have a simple table with a composite primary key (key, value) and an extra field: CREATE TABLE IF NOT EXISTS "bu" ( "key" TEXT NOT NULL, "value" INTEGER NOT NULL, "extra" INTEGER NOT NULL, PRIMARY KEY ("key", "value")); I'll put 3 rows in the table: INSERT INTO "bu" ("key",

Re: [sqlite] Seg fault using json_each() subquery

Carlo -- how do you get in touch with the BerkeleyDB folks? I've found and fixed a couple issues with their SQLite build and have found no better option that to post it on their "forum" and hope someone finds it. Example I posted a few weeks ago with no response:

Re: [sqlite] Seg fault using json_each() subquery

Partial traceback (bdb 18.1.32): Program received signal SIGSEGV, Segmentation fault. 0x7798e560 in jsonEachColumn () from ./lib/libdb_sql-18.1.so (gdb) bt full #0 0x7798e560 in jsonEachColumn () from ./lib/libdb_sql-18.1.so No symbol table info available. #1 0x779b29b6

Re: [sqlite] Status of LSM1 extension

Keith, if you could share a bit more details on how you do that, I'd be interested. On Thu, Mar 5, 2020 at 8:43 AM Keith Medcalf wrote: > > On Thursday, 5 March, 2020 05:51, Dominique Devienne > wrote: > > >PS: I'd still very much appreciate an LSM1 amalgamation > > cd ext/lsm1 > tclsh