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 tool/mkl
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:
https://community.oracle.com/thre
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 i
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:
* sqlite
fy 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 needed
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
e to 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 &quo
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", "
Love 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:
>
> >
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 z
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 z
pplication these
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 provid
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 exec
n Sun, Feb 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 p
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 :
> >
> >
n Sun, Feb 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
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, "ex
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, are
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?
https://sqlite.org/doclist
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 th
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
http:
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:
>>
>
> Thank
"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 iss
/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(
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 "
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 wrote:
> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer
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 has existed for a long t
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 2018051303 I have the following issue
> >
> >
> > |
>
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 d
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 wrote:
> On 05/02/2018 03:50 AM, Charles Leifer wrote:
>
>> Hi all,
>>
>> I'm notici
t_id", "t2"."depth" AS "depth"
FROM "category" AS "t1"
INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root")
WHERE (("t2"."id" = 4) AND ("t2".&
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-users
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 Leifer, wrote:
>
> &
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 NU
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.
___
s, even
if keys within that space are frequently updated, deleted, or re-inserted.
On Tue, Oct 31, 2017 at 11:16 AM, Dan Kennedy wrote:
> On 10/31/2017 10:50 PM, Charles Leifer wrote:
>
>> Is the LSM database append-only, as in the file size will always
>> grow/never
>> s
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinf
sqlite4's future is uncertain. It's not, as I understand, meant to replace
sqlite3 any time soon. I think it was more of a place to try out new ideas
for implementations.
On Wed, Aug 9, 2017 at 3:10 AM, x wrote:
> Thanks Charles. Is sqlite4 available yet?
>
> From: Charles
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 s
Fri, Aug 4, 2017 at 10:48 PM, Richard Hipp wrote:
> On 8/4/17, Charles Leifer wrote:
> >
> > My excitement quickly turned to disappointment as I realized that the
> > extension is unusable for all practical purposes:
> >
>
> Yes, it needs work. That is why it i
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 broken
Thanks for the clarification!
On Tue, Jun 20, 2017 at 2:41 AM Dan Kennedy 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-vtab branch). I ended up h
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
instru
Is it conceivable that this change could be rolled back?
On Tue, Apr 4, 2017 at 9:45 AM, Charles Leifer 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
> lot of fol
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
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 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 2017-03-12, you need Fossil 2.0 or later.
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 wrote:
>
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 received
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
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,
>
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!
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 wo
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 b
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 wa
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,
>
> h
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
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
Has anyone written an application using the SQLite btree as a standalone
component?
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`
> br
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.
Cha
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
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 u
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 recu
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 retur
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 querie
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:
>
> http://sqli
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. "
t
68 matches
Mail list logo
