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
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
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. "
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.
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.
Has anyone written an application using the SQLite btree as a standalone
component?
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
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.
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,
>
>
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
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
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:
>
>
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
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
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
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!
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,
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
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
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
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
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
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
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
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
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
, 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
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
>
> 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:
>
>
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
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
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.
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
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
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
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
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
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
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
/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
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
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.
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
-
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
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
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
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
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
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
___
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?
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,
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,
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
>
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 :
> >
> >
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
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
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
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
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
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
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:
*
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
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
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
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",
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:
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
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
68 matches
Mail list logo