Re: [sqlite] Status of LSM1 extension

[Charles Leifer]

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/mklsm1c.tcl
>
> which will write an lsm1.c amalgamation in the current directory (ext/lsm1)
>
> You can append this to the amalgamation and use an EXTRA_INIT hook to
> initialize it, just like building in any other extension (though you need
> to define SQLITE_ENABLE_LSM1 in order for the extension code to be compiled)
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Charles Leifer]

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/thread/4281490

I'd love to know how to get in touch with those folks to help contribute
fixes like this one.

On Wed, Jul 24, 2019 at 3:34 PM Carlo Innocenti  wrote:

> To close this thread, it looks like this issue is about a sqlite bug
> which was fixed last year:
>
> http://www2.sqlite.org/cgi/src/info/7daa687340e47597
>
> Applying the changes described there to the BDB/SQLite code fixed the
> issue I was experiencing.
>
> Thanks for all the help,
> Minollo
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Charles Leifer]

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 in sqlite3VdbeExec () from ./lib/libdb_sql-18.1.so
No symbol table info available.

Unfortunately this is next-to-useless, but may provide a clue?

On Tue, Jul 23, 2019 at 11:04 AM Carlo Innocenti 
wrote:

> I'm using sqlite with BDB as the persistence store; if you can't
> reproduce it (same sqlite version), I suppose it means it's a BerkeleyDB
> issue, and I'll follow up with them.
> Thanks!
>
> On 7/23/2019 12:01, Richard Hipp wrote:
> > Unable to reproduce.  Are you sure you are sending the correct script?
> >   What operating system are you running on?  How did you compile
> > SQLite?
> >
> > On 7/23/19, Carlo Innocenti  wrote:
> >> I have a segmentation fault which happens when trying to run a subquery
> >> based on json_each() if the argument is the value of an outer
> >> json_each() row:
> >>
> >>  CREATE TABLE mimmo (key TEXT PRIMARY KEY, value TEXT, valueJson
> >>  TEXT, blockNo INTEGER, txnNo INTEGER, metadata TEXT);
> >>  INSER INTO mimmo (key, valueJson) VALUES ('key2',
> > Did you mean to say "INSERT" here, instead of "INSER"?
> >
> >>  '{"peppo":[["a","b"],["c","d"]]}');
> >>  SELECT s.key FROM mimmo AS s, json_each(json_extract(s.valueJson,
> >>  '$.peppo')) AS a WHERE (SELECT COUNT(*) FROM json_each(a.value)) >
> 0;
> >>
> >> sqlite> select sqlite_version();
> >> 3.18.2
> >>
> >> Am I doing anything obviously wrong? Or is this a known
> problem/limitation?
> >> Thanks!
> >> Minollo
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BLOB and TEXT comparisons

[Charles Leifer]

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:

* sqlite3_bind_text
* sqlite3_bind_blob

However, if I mix the types, e.g. sqlite3_bind_text("abcde") and
sqlite3_bind_blob("abc") then the comparison returns False.

Fom a byte-to-byte perspective, this comparison should always return True.

What's going on?

Thanks!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Charles Leifer]

Thanks, makes sense. I think what confused me is that in the doc for row
values, it states:

Two row values of the same size can be compared using operators <, <=, >,
>=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.

The row value doc example shows how to use BETWEEN, for instance:

SELECT * FROM info WHERE (year,month,day) BETWEEN (2015,9,12) AND
(2016,9,12);

Based on the above doc, I would have also thought it would be possible to
write something like:

SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1));

Perhaps the row-value doc could clarify 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 for the WHERE
> clause in this case?
>
> <https://sqlite.org/lang_expr.html#in_op>
>
> " If the right operand of an IN or NOT IN operator is a list of values,
> each of those values must be scalars and the left expression must also be a
> scalar. "
>
> So to use lists, you can't use pairs of values, you must use scalars.
> Further up the same paragraph
>
> " When the right operand of an IN or NOT IN operator is a subquery, the
> subquery must have the same number of columns as there are columns in the
> row value of the left operand. The subquery on the right of an IN or NOT IN
> operator must be a scalar subquery if the left expression is not a row
> value expression. "
>
> So what seems to be happening is that your use of VALUES is turning the
> comparator into a subquery rather than a list of values.  And SQLite is
> testing to see whether your search term satisfies the subquery.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Charles Leifer]

This is a question about SQL language support and the handling of
row-values, and I thought my question was quite clear. I appreciate your
offers to help, but they do not have anything to do with the question I am
asking.

As I wrote:

My question, though, is why is the VALUES bit needed for the WHERE clause
in this case? Reading the docs on row values, it seemed to me that
comparing two row values should work fine:

Two row values of the same size can be compared using operators <, <=, >,
>=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.

Is there a reason I need 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 clause is
> needed because, unless I would provide a CASE WHEN for every single (key,
> value), then the UPDATE would set the "extra" value of any unmatched key,
> value to NULL.
>
> You can use ELSE:
>
> UPDATE "bu" SET "extra" = CASE "key"||"value"
> WHEN 'k1'||1 THEN 100
> WHEN 'k2'||2 THEN -200
> WHEN 'k3'||3 THEN 30
> ELSE "extra"
> END;
>
> But I still think that using multiple UPDATE statements will still be far
> faster.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Charles Leifer]

Thanks Simon -- this is just a minimal example. The WHERE clause is needed
because, unless I would provide a CASE WHEN for every single (key, value),
then the UPDATE would set the "extra" value of any unmatched key, value to
NULL.

Please remember this is just an example. I'd like 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 "extra" = CASE ("key", "value")
> >  WHEN ('k1', 1) THEN 100
> >  WHEN ('k2', 2) THEN -200
> >  WHEN ('k3', 3) THEN 30
> > END
> > WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3));
>
> Your WHERE clause it not needed, and SQLite is not clever enough to
> realise it can be used to find specific rows in the table.  (At least
> that's what I think, I may be wrong.).
>
> I'm not certain that SQLite understands your use of the bracketed terms.
> This
>
> UPDATE "bu" SET "extra" = CASE "key"||"value"
>  WHEN 'k1'||1 THEN 100
>  WHEN 'k2'||2 THEN -200
>  WHEN 'k3'||3 THEN 30
> END;
>
> might work because || is the 'append' operator.  Of course this assumes
> that there are no ambiguities like 'k451' which might be key 'k4' or k45.
> It might be better to use something like "key"|'x'|"value" .
>
> But in fact this would be far faster
>
> BEGIN;
> UPDATE "bu" SET "extra" = 100 WHERE "key"='k1' AND "value"=1;
> UPDATE "bu" SET "extra" = -200 WHERE "key"='k2' AND "value"=2;
> UPDATE "bu" SET "extra" = 30 WHERE "key"='k3' AND "value"=3;
> END;
>
> because your WHERE clause matches a UNIQUE key, so SQLite could go
> straight to the correct row rather than iterating through the entire
> table.  So instead of one command that has to read every row of the table
> you have three commands which each execute almost instantly.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Row values with IN

[Charles Leifer]

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", "value", "extra")
VALUES ('k1', 1, 1), ('k2', 2, 2), ('k3', 3, 3);

Now -- I want to do a "bulk update":

UPDATE "bu" SET "extra" = CASE ("key", "value")
  WHEN ('k1', 1) THEN 100
  WHEN ('k2', 2) THEN -200
  WHEN ('k3', 3) THEN 30
END
WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3));

I run into a "row value misused" error triggered by the WHERE clause, which
is comparing the (key, value) tuple to a set of row-values.

I've found that I can get this to work by using explicitly using VALUES:

UPDATE "bu" SET "extra" = CASE ("key", "value") ... END
WHERE ("key", "value") IN (VALUES ('k1', 1), ('k2', 2), ('k3', 3));

My question, though, is why is the VALUES bit needed for the WHERE clause
in this case? Reading the docs on row values, it seemed to me that
comparing two row values should work fine:

Two row values of the same size can be compared using operators <, <=, >,
>=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.

Is there a reason I need to specify VALUES instead of simply passing in a
set of row tuples? It seems a little bit inconsistent to me. Thanks!

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Json paths

[Charles Leifer]

I still think that, in spite of the absence of a clear standard, sqlite
could benefit from more sophisticated path support.

Sqlite's json functions seem modeled after the MySQL json functions. Since
MySQL supports some nice features (wildcard, prefix/suffix match), it does
seem reasonable that sqlite might support similar behavior.

And, as you mentioned, sqlite tends to follow postgres as a kind of
standard, so once 12.0 lands it would be cool to see sqlite offer similar
json path support.

Anyway, I understand that there are perhaps many other things that are much
higher priority. 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:
>
> > I was wondering if there were any plans to support wildcard paths?
> >
>
> The main issue here IMHO is that there's no official standard, AFAIK.
>
>
> > Postgres v12 release looks like it has a pretty sophisticated jsonpath
> > type.
>
>
> SQLite does often follow PG's lead, so maybe that's the path forward.
>
> Haven't looked up the PG 12's JsonPath doc yet, especially since release
> later this year only.
>
> Here's one of the many JsonPath flavor I found:
> https://restfulapi.net/json-jsonpath/
>
> I'm used XPath2 (within XSLT2) extensively years ago, which is an actual
> standard,
> and has Michael Kay's [1] excellent book to explain it [2], but so such
> resources around "JsonPath".
>
> Again, doesn't prevent "inventing" one's semantic, or following an existing
> one. Still, it's unfortunate. --DD
>
> [1] https://en.wikipedia.org/wiki/Michael_Howard_Kay
> [2]
> https://www.amazon.com/XSLT-2-0-XPath-Programmers-Reference/dp/0470192747
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Json paths

[Charles Leifer]

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 zero or more
instances of ".objectlabel" or "[arrayindex]".

I was wondering if there were any plans to support wildcard paths?

Postgres v12 release looks like it has a pretty sophisticated jsonpath
type. Examples:

SELECT jsonb_path_query(x, '$.** ? (@.name == "a")') FROM test;
 SELECT jsonb_path_query(x, '$.sizes[1 to last]') FROM test;


MySQL allows wildcards in a couple flavors.

Do you anticipate anything like this landing in sqlite's json extension?

Thank you for the wonderful software,

Charles
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Wildcard with path in json extension

[Charles Leifer]

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 zero or more
instances of ".objectlabel" or "[arrayindex]".

I was wondering if there were any plans to support wildcard paths? There's
some useful information on the MySQL JSON doc:
https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax

Postgres seems to be doubling-down on json as well -- the v12 release looks
like it has a dedicated jsonpath type:
https://www.postgresql.org/docs/devel/datatype-json.html#DATATYPE-JSONPATH

What do you think?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

> However, suppose you had a partial index that keyed on
>
> "extra > 4"
>
> and your SELECT had the clause
>
> "WHERE extra > ?1"
>
> The parameter might be set to 7.  You and I know that this means SQLite
could use the partial index.  But SQLite will not compare the 7 and the 4
and decide it can use the partial index.  It does not know that 7 is
greater than 4, because it doesn't look at the parameter's value before
doing its optimization.

Right -- I definitely understand the implications. My confusion is a result
of not having realized that parameterized queries are somewhat of a
second-class-citizen. That is, the same query will be treated differently
depending on whether it uses literal values vs bind parameters. I'm not
trying to debate or even question the rationale for these things -- I just
am noting that I found this surprising, since using bind parameters is
*always* what I do and suggest others to do.

This is a bummer because in multiple apps I have created some partial
indexes and did all my EXPLAIN analysis using the CLI and literal values,
not realizing that once I started using the DB from my application 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 provided. Am I
> misunderstanding?
>
> SQLite can compare two comparators.  In Dr. Hipp's example the index says
>
> "extra IS NOT NULL"
>
> and the WHERE clause says
>
> "WHERE extra>?1"
>
> SQLite knows that for extra to be bigger than a number -- any number --
> extra cannot be NULL.  So it can deduce that it can use that partial
> index.  That's what Dr. Hipp wrote.
>
> However, suppose you had a partial index that keyed on
>
> "extra > 4"
>
> and your SELECT had the clause
>
> "WHERE extra > ?1"
>
> The parameter might be set to 7.  You and I know that this means SQLite
> could use the partial index.  But SQLite will not compare the 7 and the 4
> and decide it can use the partial index.  It does not know that 7 is
> greater than 4, because it doesn't look at the parameter's value before
> doing its optimization.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

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 executing the query I'm calling
> > sqlite3_bind...() to set the value of the parameter placeholder.
> Presumably SQLite would know at that point that a certain index is usable
> (or in this case, that a certain constraint is relevant)?
>
> SQLite knows before then.  The SQLite query optimizer does not do
> algebra.  It does its job without knowing what values you will plug into
> the parameters.
>
> Indexes are chosen by looking at how you are comparing certain columns:
> whether you compare a column using "<" or ">" or "=" or some other
> comparator.  The values you're comparing them with are ignored.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

Thanks for the explanation. So does this mean that two identical queries
can have completely different query plans depending on whether they use a
parameters vs literal values embedded in the SQL string?

> But in this case with the WHERE clause is incompletely specified, there
is no way for the query planner to do that.

At some point before actually executing the query I'm calling
sqlite3_bind...() to set the value of the parameter placeholder. Presumably
SQLite would know at that point that a certain index is usable (or in this
case, that a certain constraint is relevant)?

This seems to me to have the effect that partial indexes are in many ways
ineffective when mixed with parameterized queries. I understand that they
might be applicable in cases where very general assumptions about
null/not-null can be inferred, but that's not what I would expect as an
end-user.

On 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 parameterized query.
> >
> > For example:
> >
> > CREATE TABLE ukvp ("id" integer primary key, "key" text not null, "value"
> > int not null, "extra" int not null);
> > CREATE UNIQUE INDEX ukvp_partial ON ukvp (key, value) WHERE extra > 1;
> >
> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > ?) DO UPDATE SET
> > "extra" = EXCLUDED."extra"''
> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3, 1))
> >
> > But the following works:
> >
> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > 1) DO UPDATE SET
> > "extra" = EXCLUDED."extra"''
> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3))
> >
> > Is this a bug?
>
> No, this is not a bug.
>
> The purpose of the WHERE clause in an upsert is to match the
> constraint against a particular  UNIQUE index.  The query planner must
> know which index constraint is failing in order to generate correct
> code for the upsert.  But in this case with the WHERE clause is
> incompletely specified, there is no way for the query planner to do
> that.
>
> For the same reason, this query:
>
>SELECT value FROM ukvp WHERE key=?1 AND extra>?2;
>
> will *not* use the partial index, but this query:
>
>SELECT value FROM ukvp WHERE key=?1 AND extra>1;
>
> *will* use the partial index.  (Run each of the above with EXPLAIN
> QUERY PLAN to see for yourself.)
>
> The query planner will never use a partial index unless it can prove
> at SQL-statement compile-time that the WHERE clause of the query
> implies that the WHERE clause of the partial index.  With your partial
> index, you can never prove anything about the truth of the condition
> if the query contains "extra>?1".  However, if your partial index had
> said "WHERE extra IS NOT NULL", then the partial index would be usable
> in all of the above situations, since "extra>?1" does indeed imply
> that "extra IS NOT NULL".
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

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 :
> >
> > I run against the latest and greatest.
> >
> > Python:
> >
> > In [1]: import sqlite3
> >
> > In [2]: sqlite3.sqlite_version
> > Out[2]: '3.28.0'
> >
> >
> > Sqlite:
> >
> > sqlite> select sqlite_version(), sqlite_source_id();
> > 3.28.0|2019-02-12 22:58:32
> > 167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0
>
> The latest release is 3.27.1 (
> https://www.sqlite.org/releaselog/3_27_1.html).
>
> The code you use comes from a snapshot of the code (
> https://sqlite.org/src/info/167b91df77fff1a8) right in the middle of work
> in progress after 3.27.1 was released and (probably) long before 3.28 is.
>
> That may not be related to the issue you raised, but you'd probably be
> well advised to make sure you run the latest *released* code, because the
> releases go through a huge test/validation procedure.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

I run against the latest and greatest.

Python:

In [1]: import sqlite3

In [2]: sqlite3.sqlite_version
Out[2]: '3.28.0'


Sqlite:

sqlite> select sqlite_version(), sqlite_source_id();
3.28.0|2019-02-12 22:58:32
167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0


On 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 out using
>
> SELECT sqlite_version();
> SELECT sqlite_source_id();
>
> The second may give a blank result or an error, which is fine.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

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, "extra" int not null);
CREATE UNIQUE INDEX ukvp_partial ON ukvp (key, value) WHERE extra > 1;

INSERT INTO ukvp (key, value, extra) VALUES ('k1', 1, 1);
INSERT INTO ukvp (key, value, extra) VALUES ('k2', 2, 2);

INSERT INTO ukvp (key, value, extra) VALUES ('k1', 1, 2), ('k2', 2, 3)
  ON CONFLICT (key, value) WHERE extra > 1
  DO UPDATE set extra=EXCLUDED.extra;

This works OK in the SQLite CLI, but when attempting to execute such a
query using a prepared query I get the exception: "ON CONFLICT clause does
not match any PRIMARY KEY or UNIQUE constraint". I believe it is because
the "extra > 1" must be exactly and literal -- the "1" cannot be a
parameter in the query.

For example, using a Python SQLite library, this fails with the above error
message.

sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > ?) DO UPDATE SET
"extra" = EXCLUDED."extra"''
db.execute(sql, ('k1', 1, 2, 'k2', 2, 3, 1))

But the following works:

sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > 1) DO UPDATE SET
"extra" = EXCLUDED."extra"''
db.execute(sql, ('k1', 1, 2, 'k2', 2, 3))

Is this a bug?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding CoC

[Charles Leifer]

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
you even affected? Do you contribute bug reports or patches? Follow the
SQLite mailing list? Is anything here designed to prevent you from
continuing to do so?

SQLite's author is a spiritual guy. There's nothing wrong with him
borrowing from spiritual sources to describe his ideal for how he wants the
SQLite community to conduct itself.

...

Also, how can you tell if someone's an atheist?

Haha, don't worry friend, they'll be sure to tell you.

Keep up the amazing work, SQLite team. The good tree bears the good fruit,
and man SQLite is some good fruit.

On Mon, Oct 22, 2018 at 2:49 PM Petite Abeille 
wrote:

>
>
> > On Oct 22, 2018, at 9:43 PM, Rob Dixon  wrote:
> >
> > weird and antagonistic
>
> Thank you for the kind words of support. Your wisdom and insights will be
> missed. Farewell Rob Dixon. Godspeed.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Charles Leifer]

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.html

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Charles Leifer]

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 the "If not exists" to the Add
> > Column feature of SQLite.  There are quite common situations where
> > ensuring a column exists is important so that an update to remote
> > devices will not fail but it is not so important that deprecated fields
> > be removed.  This is often the case with backward compatibility.  New
> > columns will not affect old systems but allows all remote devices
> > running older software to be updated using the same process as new
> > devices.  Once the hardware reaches end of life it will be replaced and
> > the new hardware will use the new columns.  So having the ability to
> > Alter the table with a series of Add Column commands ensures that the
> > new records included in the update are added to the table.  These is
> > especially true for limited remote devices where full database
> > management is not feasible.  In this scenario all that is required is
> > that the required columns exist.  So to be able to alter the table with
> > a standard SQL command is the most efficient method on such devices.
> > Developing scripts to drop and re-create and re-load tables on hundreds
> > of remote devices greatly increases the risk of failures. Having the "if
> > not exists" would remove all of this potential complexity and allow a
> > quick and easy method to ensure the column exists in the table.
>
> I'd like to second this and for just the same reasons. Something like:
>
>alter table add column if not exists my_new_col ...;
>
> I would find very helpful.
>
>
> --
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Typo in window function docs

[Charles Leifer]

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://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segfault in window function

[Charles Leifer]

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 for reporting this. Should now be fixed here:
>
>   https://www.sqlite.org/src/info/693b4350d7413912
>
> Thanks also for the sum() bug report in the follow-up. That one is fixed
> here:
>
>   https://www.sqlite.org/src/info/693b4350d7413912
>
> Dan.
>
>
>
>
>
>> 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 "sample" table:
>>
>> CREATE TABLE IF NOT EXISTS "sample" (
>>"id" INTEGER NOT NULL PRIMARY KEY,
>>"counter" INTEGER NOT NULL,
>>"value" REAL NOT NULL);
>>
>> Test data:
>>
>> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
>> (2, 3.), (3, 100.);
>>
>> The expected output for the window query is:
>>
>> 1, 20., 1
>> 1, 10., 2
>> 2, 3., 1
>> 2, 1., 2
>> 3, 100., 1
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segfault in window function

[Charles Leifer]

Furthermore, I've found another example of a window function returning
incorrect results.

Using the above "sample" table and sample data, the following query
produces the wrong results.

Query:

SELECT "counter", "value", SUM("value") OVER (ORDER BY "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.

On Mon, Jul 2, 2018 at 10:55 AM, Charles Leifer  wrote:

> Oh, and gdb is reporting the segfault occurs in "dupedExprSize".
>
> Partial traceback:
>
> #0  0x74d4645b in dupedExprSize () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #1  0x74d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #2  0x74d5c091 in sqlite3ExprListDup () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #3  0x74dbfa4f in sqlite3Select () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #4  0x74df082b in sqlite3RunParser () from
> /usr/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() 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 "sample" table:
>>
>> CREATE TABLE IF NOT EXISTS "sample" (
>>   "id" INTEGER NOT NULL PRIMARY KEY,
>>   "counter" INTEGER NOT NULL,
>>   "value" REAL NOT NULL);
>>
>> Test data:
>>
>> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
>> (2, 3.), (3, 100.);
>>
>> The expected output for the window query is:
>>
>> 1, 20., 1
>> 1, 10., 2
>> 2, 3., 1
>> 2, 1., 2
>> 3, 100., 1
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segfault in window function

[Charles Leifer]

Oh, and gdb is reporting the segfault occurs in "dupedExprSize".

Partial traceback:

#0  0x74d4645b in dupedExprSize () from
/usr/local/lib/libsqlite3.so.0
No symbol table info available.
#1  0x74d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0
No symbol table info available.
#2  0x74d5c091 in sqlite3ExprListDup () from
/usr/local/lib/libsqlite3.so.0
No symbol table info available.
#3  0x74dbfa4f in sqlite3Select () from
/usr/local/lib/libsqlite3.so.0
No symbol table info available.
#4  0x74df082b in sqlite3RunParser () from
/usr/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() 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 "sample" table:
>
> CREATE TABLE IF NOT EXISTS "sample" (
>   "id" INTEGER NOT NULL PRIMARY KEY,
>   "counter" INTEGER NOT NULL,
>   "value" REAL NOT NULL);
>
> Test data:
>
> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
> (2, 3.), (3, 100.);
>
> The expected output for the window query is:
>
> 1, 20., 1
> 1, 10., 2
> 2, 3., 1
> 2, 1., 2
> 3, 100., 1
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Segfault in window function

[Charles Leifer]

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 "sample" table:

CREATE TABLE IF NOT EXISTS "sample" (
  "id" INTEGER NOT NULL PRIMARY KEY,
  "counter" INTEGER NOT NULL,
  "value" REAL NOT NULL);

Test data:

INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
(2, 3.), (3, 100.);

The expected output for the window query is:

1, 20., 1
1, 10., 2
2, 3., 1
2, 1., 2
3, 100., 1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Charles Leifer]

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:30 PM, Charles Leifer <colei...@gmail.com>
> wrote:
> > 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.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz <sqlite.2...@t-net.ruhr>
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[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 time.
>
> Yes, sorry, I mixed things up.
>
> The order of importance is imho:
> 1. RENAME COLUMN (shouldn't be too hard)
> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> 3. MODIFY COLUMN
>
> > What kind of MODIFY COLUMN changes do you have in mind?
>
> I understand this can be difficult as there are many possible operations
> that might be incompatible with the data already stored in that column.
> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE CASCADE
> action of a foreign key column. Also adding/removing a foreign key would be
> useful.
>
> Kind regards,
> Thomas
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fail building snapshot 201805081303

[Charles Leifer]

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
> >
> >
> > |
> > /Projects/development-1.8.6.999/T/forgetux_2.0/tmp/
> sysroots/x86_64-linux/usr/lib/libsqlite3.a(sqlite3.o):
> > In function `fts5Bm25Function':
> > | sqlite3.c:(.text+0x4b307): undefined reference to `log'
> > | collect2: ld returned 1 exit status
> > | make: *** [bin/pseudodb] Error 1
> > | make: *** Waiting for unfinished jobs
> > |
> > /Projects/development-1.8.6.999/T/forgetux_2.0/tmp/
> sysroots/x86_64-linux/usr/lib/libsqlite3.a(sqlite3.o):
> > In function `fts5Bm25Function':
> > | sqlite3.c:(.text+0x4b307): undefined reference to `log'
> > | collect2: ld returned 1 exit status
> > | make: *** [bin/pseudo] Error 1
> > |
> > /Projects/development-1.8.6.999/T/forgetux_2.0/tmp/
> sysroots/x86_64-linux/usr/lib/libsqlite3.a(sqlite3.o):
> > In function `fts5Bm25Function':
> > | sqlite3.c:(.text+0x4b307): undefined reference to `log'
> > | collect2: ld returned 1 exit status
> > | make: *** [bin/pseudolog] Error 1
> >
> >
> > --
> >
> > Powersoft logo 
> >
> > *Michele Dionisio |*Senior Embedded System Manager
> >
> > *skype:*  m.dionisio *| email:* michele.dioni...@powersoft.com
> > 
> >
> > *HQ Italy:* Via E. Conti, 5 *|* 50018 Scandicci (FI) Italy
> >
> > *direct phone:*  +39 055 735 0230 *| Fax:*   +39 055 735 6235
> >
> > *web site:* www.powersoft.it 
> >
> > Green Audio Power
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about sqlite3_prepare vs sqlite3_prepare_v2

[Charles Leifer]

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 do not think this is a sqlite bug. I would just like to
validate that the behavior I'm seeing is accounted for by the differences
in sqlite3_prepare and sqlite3_prepare_v2 (as I strongly suspect).

What I noticed was that if I executed the following query, binding the
timestamp value as a parameter, I got very slow execution time (400ms+):

 -- get count of distinct timestamps for given time period.
select count(1) from (
select 1 from events
where timestamp >= $the_timestamp
group by ip)

When I tried the query without binding the parameter and just specifying it
inline, the query became incredibly fast (i.e. 2ms).

Running EXPLAIN QUERY PLAN, I ended up getting two different plans for the
exact same query, depending on whether I passed the timestamp value as a
parameter or directly in the SQL string.

Parameterized query with timestamp as bound parameter:

 (2, 0, 0, 'CO-ROUTINE 0x1EB3C80'),
 (10, 2, 0, 'SCAN TABLE events AS t1 USING INDEX event_ip'),
 (36, 0, 0, 'SCAN SUBQUERY 0x1EB3C80 AS _wrapped')

Query with the timestamp expressed in-line:

 (2, 0, 0, 'CO-ROUTINE 0x1EAFAE0'),
 (10, 2, 0, 'SEARCH TABLE events AS t1 USING INDEX events_timestamp
(timestamp>?)'),
 (14, 2, 0, 'USE TEMP B-TREE FOR GROUP BY'),
 (43, 0, 0, 'SCAN SUBQUERY 0x1EAFAE0 AS _wrapped')

Now, about this table... it's got two indexes, as you could probably
gather: one on the IP column and one on the timestamp column. I also have
the stat4 module included in my build.

It seems that when the timestamp is passed as a bound parameter, SQLite
prefers the index on IP. But when the timestamp is expressed inline, the
query planner decides to filter on timestamp *first*, then do the group-by
IP (which ends up being a better plan for this use-case).

I do not think this is a bug in SQLite! I am simply curious if the behavior
differences between sqlite3_prepare and sqlite3_prepare_v2 could account
for this difference?

Thanks so much for the wonderful library.

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in transitive closure extension?

[Charles Leifer]

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,
>>
>> I'm noticing a bug in the transitive closure extension in the latest
>> version of SQLite.
>>
>
> Thanks for reporting this. Should now be fixed here:
>
> http://www.sqlite.org/src/info/0c67150749cb3d06
>
> Dan.
>
>
>
>
>> Reproducing requires the closure extension, which I compiled:
>>
>> gcc -g -fPIC -shared -lsqlite3 closure.c -o closure.so
>>
>> SQL to populate db:
>>
>> .load closure
>> -- create category table with self-referential parent fk.
>> CREATE TABLE "category" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL,
>> "parent_id" INTEGER, FOREIGN KEY ("parent_id") REFERENCES "category"
>> ("id"));
>> CREATE INDEX IF NOT EXISTS "category_parent_id" ON "category"
>> ("parent_id");
>>
>> -- create closure table vtable
>> CREATE VIRTUAL TABLE IF NOT EXISTS "categoryclosure" USING
>> transitive_closure (idcolumn=id, parentcolumn=parent_id,
>> tablename=category);
>>
>> -- populate some data for a book catalog
>> INSERT INTO "category" ("name", "parent_id") VALUES ('books', NULL); -- 1
>> INSERT INTO "category" ("name", "parent_id") VALUES ('fiction', 1); -- 2
>> INSERT INTO "category" ("name", "parent_id") VALUES ('scifi', 2); -- 3
>> INSERT INTO "category" ("name", "parent_id") VALUES ('hard scifi', 3); --
>> 4
>> INSERT INTO "category" ("name", "parent_id") VALUES ('dystopian', 3); -- 5
>>
>> -- get hard scifi and all its parents:
>> SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth"
>> FROM "category" AS "t1"
>> INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root")
>> WHERE ("t2"."id" = 4);
>>
>> -- results:
>> -- 1|books||3
>> -- 2|fiction|1|2
>> -- 3|scifi|2|1
>> -- 4|hard scifi|3|0
>>
>> -- get relations of "hard scifi" specifying depth > 0.
>> SELECT "t1"."id", "t1"."name", "t1"."parent_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"."depth" > 0));
>>
>> -- results:
>> -- 1|books||3
>> -- 2|fiction|1|2
>> -- 3|scifi|2|1
>>
>>
>> -- THIS BREAKS with: "Error: categoryclosure.xBestIndex malfunction"
>> -- get grand-parent category for hard-scifi (depth=2)
>> SELECT "t1"."id", "t1"."name", "t1"."parent_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"."depth" = 2));
>>
>> It seems that when we query for equality on the categoryclosure id *and*
>> depth, xBestIndex runs into trouble?
>>
>> Thanks,
>>
>> Charles Leifer
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in transitive closure extension?

[Charles Leifer]

Hi all,

I'm noticing a bug in the transitive closure extension in the latest
version of SQLite.

Reproducing requires the closure extension, which I compiled:

gcc -g -fPIC -shared -lsqlite3 closure.c -o closure.so

SQL to populate db:

.load closure
-- create category table with self-referential parent fk.
CREATE TABLE "category" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL,
"parent_id" INTEGER, FOREIGN KEY ("parent_id") REFERENCES "category"
("id"));
CREATE INDEX IF NOT EXISTS "category_parent_id" ON "category" ("parent_id");

-- create closure table vtable
CREATE VIRTUAL TABLE IF NOT EXISTS "categoryclosure" USING
transitive_closure (idcolumn=id, parentcolumn=parent_id,
tablename=category);

-- populate some data for a book catalog
INSERT INTO "category" ("name", "parent_id") VALUES ('books', NULL); -- 1
INSERT INTO "category" ("name", "parent_id") VALUES ('fiction', 1); -- 2
INSERT INTO "category" ("name", "parent_id") VALUES ('scifi', 2); -- 3
INSERT INTO "category" ("name", "parent_id") VALUES ('hard scifi', 3); -- 4
INSERT INTO "category" ("name", "parent_id") VALUES ('dystopian', 3); -- 5

-- get hard scifi and all its parents:
SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth"
FROM "category" AS "t1"
INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root")
WHERE ("t2"."id" = 4);

-- results:
-- 1|books||3
-- 2|fiction|1|2
-- 3|scifi|2|1
-- 4|hard scifi|3|0

-- get relations of "hard scifi" specifying depth > 0.
SELECT "t1"."id", "t1"."name", "t1"."parent_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"."depth" > 0));

-- results:
-- 1|books||3
-- 2|fiction|1|2
-- 3|scifi|2|1


-- THIS BREAKS with: "Error: categoryclosure.xBestIndex malfunction"
-- get grand-parent category for hard-scifi (depth=2)
SELECT "t1"."id", "t1"."name", "t1"."parent_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"."depth" = 2));

It seems that when we query for equality on the categoryclosure id *and*
depth, xBestIndex runs into trouble?

Thanks,

Charles Leifer
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Window functions

[Charles Leifer]

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 mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with json1 query?

[Charles Leifer]

No, the keys would be arbitrarily chosen by the user. The rtree extension
could be a possibility, I'll check it out.

On Thu, Mar 15, 2018 at 12:56 AM, Wout Mertens <wout.mert...@gmail.com>
wrote:

> Can you elaborate on the metadata? Are the keys always the same, in which
> case you 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 Leifer, <colei...@gmail.com> 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 NULL,
> > "metadata" JSON,
> > FOREIGN KEY ("src") REFERENCES "vertex" ("key"),
> > FOREIGN KEY ("dest") REFERENCES "vertex" ("key"));
> >
> > What I'd like to do is allow querying of edges (or vertices) using a
> > *partial* metadata object. So if I had the following JSON object stored
> in
> > an edge's metadata:
> >
> > {"k1": "v1", "k2": "v2", "k3": "v3"}
> >
> > The user could provide me an object like {"k1": "v1", "k3": "v3"} and I
> > would be able to match the above edge's metadata.
> >
> > I can see decomposing the user-provided dictionary and building up
> multiple
> > equality tests using the json_extract() function, e.g.:
> >
> > select * from edge where json_extract(metadata, '$.k1') = 'v1' AND
> > json_extract(metadata, '$.k3') = 'v3';
> >
> > But I was hoping there would be a more elegant way to express this that
> > someone would be able to share? It seems as though I should be able to
> use
> > `json_each()` (or even `json_tree()` if metadata could be nested?), but
> I'm
> > not sure how to formulate the query.
> >
> > It'd be great if there were a JSON function like "json_contains()" where
> I
> > could write:
> >
> > select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3":
> > "v3"}');
> >
> > Any help appreciated!
> >
> > Charlie
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with json1 query?

[Charles Leifer]

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 NULL,
"metadata" JSON,
FOREIGN KEY ("src") REFERENCES "vertex" ("key"),
FOREIGN KEY ("dest") REFERENCES "vertex" ("key"));

What I'd like to do is allow querying of edges (or vertices) using a
*partial* metadata object. So if I had the following JSON object stored in
an edge's metadata:

{"k1": "v1", "k2": "v2", "k3": "v3"}

The user could provide me an object like {"k1": "v1", "k3": "v3"} and I
would be able to match the above edge's metadata.

I can see decomposing the user-provided dictionary and building up multiple
equality tests using the json_extract() function, e.g.:

select * from edge where json_extract(metadata, '$.k1') = 'v1' AND
json_extract(metadata, '$.k3') = 'v3';

But I was hoping there would be a more elegant way to express this that
someone would be able to share? It seems as though I should be able to use
`json_each()` (or even `json_tree()` if metadata could be nested?), but I'm
not sure how to formulate the query.

It'd be great if there were a JSON function like "json_contains()" where I
could write:

select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3":
"v3"}');

Any help appreciated!

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Next release add flag for building LSM1?

[Charles Leifer]

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.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM database file growth?

[Charles Leifer]

Thanks so much, this explains things neatly. I was aware of the tree
compaction and the use of delete tombstones, but wasn't sure how it all
played out in terms of space reclamation. In other words, if the total size
of my keyspace is fixed, then the database won't grow without bounds, even
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
>> grow/never
>> shrink (even if there are deletions/overwrites)?
>>
>
> An LSM database is basically a series of tree structures on disk. When you
> write to an LSM database you add the new key to an in-memory tree. Once
> that tree is full it is flushed out to disk. To query the database for a
> key, you query each of these trees from newest to oldest until you find a
> match or run out of trees. To prevent the number of trees on disk from
> growing indefinitely, two or more old trees are periodically merged
> together to create a single, larger tree structure.
>
> A DELETE operation is handled like an INSERT, except a special
> "delete-marker" key is added to the in-memory tree structure.
>
> When two old trees are merged together, if a delete-marker is merged with
> a real insert-key, both are discarded. So that the new tree contains
> neither the delete-marker or the insert key.
>
> So if you delete a bunch of data space is reclaimed eventually, but it can
> take a while to happen. Optimizing the database involves merging all trees
> on disk together, so this has the effect of reclaiming all unused space:
>
> http://sqlite.org/src4/doc/trunk/www/lsmusr.wiki#database_
> file_optimization
>
> Dan.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LSM database file growth?

[Charles Leifer]

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/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[Charles Leifer]

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 <tam118...@hotmail.com> wrote:

> Thanks Charles. Is sqlite4 available yet?
>
> 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:
>
> * 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 stems from my having written a set
> of python bindings to the sqlite4 implementation (to be used as a sort of
> embedded ordered key/value storage engine). I've wondered about the
> possibility of exposing these APIs as a virtual table, and so seeing this
> extension really excited me. So obviously my interest is much greater than
> that of the average SQLite user :)
>
> On Tue, Aug 8, 2017 at 12:54 PM, x <tam118...@hotmail.com> wrote:
>
> > Thanks Richard.
> >
> > From: Richard Hipp<mailto:d...@sqlite.org>
> > Sent: 08 August 2017 18:47
> > To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
> > Subject: Re: [sqlite] LSM1 extension
> >
> > On 8/8/17, x <tam118...@hotmail.com> wrote:
> > > I keep reading about this LSM1 but can’t find anything about it beyond
> > > tickets. Any links?
> >
> > Baseline technology: https://en.wikipedia.org/wiki/
> > Log-structured_merge-tree
> >
> > No documentation yet on the LSM1 implementation in SQLite.
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[Charles Leifer]

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 stems from my having written a set
of python bindings to the sqlite4 implementation (to be used as a sort of
embedded ordered key/value storage engine). I've wondered about the
possibility of exposing these APIs as a virtual table, and so seeing this
extension really excited me. So obviously my interest is much greater than
that of the average SQLite user :)

On Tue, Aug 8, 2017 at 12:54 PM, x  wrote:

> Thanks Richard.
>
> From: Richard Hipp
> Sent: 08 August 2017 18:47
> To: SQLite mailing list
> Subject: Re: [sqlite] LSM1 extension
>
> On 8/8/17, x  wrote:
> > I keep reading about this LSM1 but can’t find anything about it beyond
> > tickets. Any links?
>
> Baseline technology: https://en.wikipedia.org/wiki/
> Log-structured_merge-tree
>
> No documentation yet on the LSM1 implementation in SQLite.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[Charles Leifer]

Right -- my question is still unanswered as to why the code was merged in.
I have tremendous respect for this project and everyone involved in it, so
please understand I'm not trying to call into question your decisions --
I'd just like clarification as to why this was included now?

On Fri, Aug 4, 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:
> >
>
> Yes, it needs work.  That is why it is unannounced.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LSM1 extension

[Charles Leifer]

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 (unless you hand-craft the
encoding of the key according to the encodeKey rules).
- Everything else is a full-database scan.

I suppose I can understand releasing the code without all the xBestIndex
optimizations, but I was really quite surprised to see single-key lookups
weren't working as I'd have expected.

I don't want to be presumptuous, but I'm having a hard time understanding
why this code is included alongside extensions like FTS5 and RTREE. Because
it wasn't in the release notes are we to ignore it? That doesn't make sense
to me because, given the wonderful tools we have for things like version
control and all that, why not just hold off on merging it until it's
actually ready for release?

Looking forward to hearing your thoughts on the issue,

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lsm1 extension

[Charles Leifer]

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-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
> > instructions on this exciting new extension with a colleague.
>
> That's how I usually build it. The Makefile in my build directory has:
>
>TCC = gcc -g -fPIC -Wall -DSQLITE_DEBUG -DSQLITE_NO_SYNC
>
>...
>
>include $(TOP)/main.mk
>LSMDIR=$(TOP)/ext/lsm1
>include $(LSMDIR)/Makefile
>
> Then just [make lsm.so]. There is no way to use the configure script to
> build lsm1 at the moment.
>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] lsm1 extension

[Charles Leifer]

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
instructions on this exciting new extension with a colleague.

Anyone able to help? Thanks!

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Charles Leifer]

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
> 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 doesn't work.
>
> What a horrible shock it was to discover that my backups were all of a
> sudden failing. I had no idea why until I checked the mailing list, which I
> luckily happen to be subscribed to, otherwise I'd still be puzzling over
> what happened.
>
> Please consider reverting this, or hiding this behavior behind a flag.
>
> On Tue, Apr 4, 2017 at 1:55 AM, Clemens Ladisch <clem...@ladisch.de>
> wrote:
>
>> James K. Lowden wrote:
>> >>> Why not use vis(3) instead?
>> >>
>> >> Because vis() is a nonstandard function that is not available
>> >> everywhere,
>> >
>> > "everywhere" is a high standard, but vis is freely available and
>> > included or packaged with almost anything not Windows.
>>
>> It is not included in the distribution that I happen to use.
>>
>> >> and in any case it does not support SQL.
>> >
>> > Entirely irrelevant, as you surely know.
>>
>> What?!?  The .dump output _is_ SQL, and is intended to be read again
>> by SQLite, so whatever escaping mechanism it uses must be supported by
>> SQLite itself.
>>
>> > To me, the most objectionable aspect of using char() is that the SQL is
>> > munged.  I see no reason to modify the user's text and introduce
>> > further SQL interpretation.
>>
>> The INSERT statements _already_ are interpreted; that's the whole point
>> of generating them in the first place.
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Charles Leifer]

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 doesn't work.

What a horrible shock it was to discover that my backups were all of a
sudden failing. I had no idea why until I checked the mailing list, which I
luckily happen to be subscribed to, otherwise I'd still be puzzling over
what happened.

Please consider reverting this, or hiding this behavior behind a flag.

On Tue, Apr 4, 2017 at 1:55 AM, Clemens Ladisch  wrote:

> James K. Lowden wrote:
> >>> Why not use vis(3) instead?
> >>
> >> Because vis() is a nonstandard function that is not available
> >> everywhere,
> >
> > "everywhere" is a high standard, but vis is freely available and
> > included or packaged with almost anything not Windows.
>
> It is not included in the distribution that I happen to use.
>
> >> and in any case it does not support SQL.
> >
> > Entirely irrelevant, as you surely know.
>
> What?!?  The .dump output _is_ SQL, and is intended to be read again
> by SQLite, so whatever escaping mechanism it uses must be supported by
> SQLite itself.
>
> > To me, the most objectionable aspect of using char() is that the SQL is
> > munged.  I see no reason to modify the user's text and introduce
> > further SQL interpretation.
>
> The INSERT statements _already_ are interpreted; that's the whole point
> of generating them in the first place.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Charles Leifer]

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 disable the limit
completely.

What to do?

On Mon, Apr 3, 2017 at 7:16 PM, James K. Lowden 
wrote:

> On Sun, 2 Apr 2017 09:40:36 +0200
> Clemens Ladisch  wrote:
>
> > > Why not use vis(3) instead?
> >
> > Because vis() is a nonstandard function that is not available
> > everywhere,
>
> "everywhere" is a high standard, but vis is freely available and
> included or packaged with almost anything not Windows.  By using the
> nonstandard char() function, you introduce a constraint that is
> otherwise avoided.
>
> $ for S in c h m o
> do
> printf '%s: %s\n' -$S $(echo hello | vis -w -$S)
> done
> -c: hello\n
> -h: hello%0a
> -m: hello\012
> -o: hello\012
>
>
> For the intended purpose, it would be trivial to support some
> form of VIS_WHITE.  That could be a fallback position.
>
> Because the library is widely available, SQLite users who can take
> advantage of it can decode the vis-encoded text outside the aegis of
> SQLite.
>
> > and in any case it does not support SQL.
>
> Entirely irrelevant, as you surely know.
>
> To me, the most objectionable aspect of using char() is that the SQL is
> munged.  I see no reason to modify the user's text and introduce
> further SQL interpretation.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to clone fossil repo

[Charles Leifer]

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.  What does "fossil -v"
> > say for you?
> >
>
> I gave you the wrong command.  "fossil version", not "fossil -v".
> What does it show?
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to clone fossil repo

[Charles Leifer]

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 <colei...@gmail.com> 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: 58284
> malformed cfile line
> Round-trips: 8   Artifacts sent: 0  received: 58416
> Clone done, sent: 1939  received: 35121878  ip: 67.18.92.124
> server returned an error - clone aborted
>
> I get errors when I try www2 and www3, or if I try HTTPS.
>
> With verbose logging, the output isn't much different:
>
> Bytes  Cards  Artifacts Deltas
> Sent:  53  1  0  0
> Received: 5000352  26717293  13096
> Sent:  72  2  0  0
> Received: 5069927  24897257  12181
> Sent:  57  1  0  0
> Received: 5000173  15434836   6892
> Sent:  57  1  0  0
> Received: 5000385  19604834   8987
> Sent:  57  1  0  0
> Received: 5002648  19884874   9081
> Sent:  57  1  0  0
> Received: 5046326   9825299   4623
> Sent:  57  1  0  0
> processed: 40%
> malformed cfile line
> Received: 5000745263  3128
> Sent:  43  0  0  0
> Received:  32  1  0  0
> Clone done, sent: 1936  received: 35121878  ip: 67.18.92.124
> server returned an error - clone aborted
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unable to clone fossil repo

[Charles Leifer]

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: 58284
malformed cfile line
Round-trips: 8   Artifacts sent: 0  received: 58416
Clone done, sent: 1939  received: 35121878  ip: 67.18.92.124
server returned an error - clone aborted

I get errors when I try www2 and www3, or if I try HTTPS.

With verbose logging, the output isn't much different:

Bytes  Cards  Artifacts Deltas
Sent:  53  1  0  0
Received: 5000352  26717293  13096
Sent:  72  2  0  0
Received: 5069927  24897257  12181
Sent:  57  1  0  0
Received: 5000173  15434836   6892
Sent:  57  1  0  0
Received: 5000385  19604834   8987
Sent:  57  1  0  0
Received: 5002648  19884874   9081
Sent:  57  1  0  0
Received: 5046326   9825299   4623
Sent:  57  1  0  0
processed: 40%
malformed cfile line
Received: 5000745263  3128
Sent:  43  0  0  0
Received:  32  1  0  0
Clone done, sent: 1936  received: 35121878  ip: 67.18.92.124
server returned an error - clone aborted
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS design document

[Charles Leifer]

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, where in the
>> source would you recommend starting? Thanks!
>>
>
> There is no such document unfortunately.
>
> The FTS5 code is cleaner than FTS3/4. I guess to decode it, start by
> understanding SQLite's virtual table interface, then look at fts5Int.h to
> figure out how the code fits together, then the on-disk format documented
> near the top of fts5_index.c.
>
> Dan.
>
>
>
> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Question about BerkeleyDB "SQLited"

[Charles Leifer]

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,
>
> Oracle is distributing its BerkeleyDB including an interesting fusion with
> SQLite
> where they take all from SQLite except the final storage engine b-tree and
> pager included.
> As a product it is possible to obtain a dbsql binary that is a clone of
> sqlite3 command line
> but working with BDB databases.
>
>
> http://www.oracle.com/technetwork/products/berkeleydb/overview/sql-160887.html
>
> http://www.oracle.com/technetwork/products/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf
>
> unfortunately they are using a quite old version of SQLite, I think SQLite
> 3.5.9 according to the delivered sources
>
> I have not found the reason why this "freeze" of the SQLite in the BDB sql
> variant.
> Probably this fusion between the two engines is difficult to mantain due
> to changes in sqlite opcodes etc
>
> does someone have more information, links etc about this upgrade
> possibilities ?
> (e.g. compile dbsql using the lastest sqlite version)
>
> thanks, regards
> Alejandro
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] FTS design document

[Charles Leifer]

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] Some FTS5 guidance

[Charles Leifer]

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 would be a (generalized) Ukkonnen suffix tree (
> https://en.m.wikipedia.org/wiki/Ukkonen%27s_algorithm).
> It allows you to search efficiently for substrings.
> It would be possible to do some match weigthing based on match distance
> within words. But a general solution for a database is probably not trivial
> to implement.
>
> Ben
>
> Von meinem iPad gesendet
>
> > Am 07.01.2016 um 21:46 schrieb Matthias-Christian Ott :
> >
> >> On 2016-01-07 19:31, Mario M. Westphal wrote:
> >> I hence wonder if this problem has been tackled already and if there is
> a
> >> "standard" solution.
> >
> > If I understand you correctly, it seems that you are looking for a
> > compound splitting or decompounding algorithm. Unfortunately there is
> > not a "standard solution" for this. There are many languages in the
> > world and for some usable compound splitting algorithms exist. There are
> > also attempts to create statistical universal algorithms.
> >
> > As you said, for English a simple sub-string search might suffice but
> > for other languages it more complex. I assume that you speak German. If
> > you have a document that contains the term "Verkehrsleitsystem" and your
> > search query is "Verkehr leiten", it's reasonable to assume that the
> > document is relevant to the search query. Unfortunately a sub-string
> > search could not find the document. Other languages are even more
> > difficult (a textbook on linguistics will explain this better than I
> can).
> >
> > Even if you have such algorithm, it's not trivial to score the results
> > and there are more aspects to consider to create a simple search
> > algorithm. For example, in English you will also have to do some
> > analysis of the phrase structure to identify open compounds.
> >
> > Perhaps it helps to mention the languages you are interested in and the
> > application you have in mind to evaluate whether the SQLite FTS5 could
> > meet your requirements.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Virtual tables and table-valued functions

[Charles Leifer]

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 be converted to HIDDEN
columns in a virtual table.
* user supplied list of column names, which would constitute the return
values of the table-valued function
* user supplied initialization function which would accept the parameters
passed, i.e. SELECT * FROM user_func(p1, p2, p3), the init function would
receive (p1, p2, p3).
* user supplied iterate function which would accept a zero-based index and
a result context, returning 1 to indicate EOF and 0 to indicate more
results exist.

I think this might lead to a larger ecosystem of quality open-source
table-valued functions. Given the fact that creating even a simple vtable
is cumbersome, providing an API that required the bare minimum seems like a
good idea to me.

Thoughts?

PS if you're curious about the Python wrapper, I posted on it a day or two
ago.

[sqlite] vtfunc wrapper for Python

[Charles Leifer]

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 way it works is your Python implementation defines a list of columns
(return values) and params (hidden columns). When the function is invoked
via SQLite, the param values are passed in to the Python initialize method.

Here is how the "generate series" implementation looks:

from vtfunc import TableFunction

class GenerateSeries(TableFunction):
params = ['start', 'stop', 'step']
columns = ['output']
name = 'series'

def initialize(self, start=0, stop=None, step=1):
self.start = start
self.stop = stop or float('inf')
self.step = step
self.curr = self.start

def iterate(self, idx):
if self.curr > self.stop:
raise StopIteration

ret = self.curr
self.curr += self.step
return (ret,)

If you're interested, the source code is up on github:
https://github.com/coleifer/sqlite-vtfunc

I've also written a blog post:
http://charlesleifer.com/blog/sqlite-table-valued-functions-with-python/

Lastly, I wonder if the SQLite development team would be amenable to the
idea of creating a standard API for creating table-valued functions,
something along the lines of the one used in this Python project? Basically
a wrapper around the current virtual table interface.

Thanks for reading! Any feedback would be most welcome,

Charlie

[sqlite] extension development

[Charles Leifer]

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,
>
> https://github.com/abiliojr/sqlite-js
>
>
> But I have 2 questions to see if someone can give me ideas:
>
> 1. So far, to call the function that defines new function, I'm using
> SELECT, e.g.,
> ??
>
> SELECT createjs('cos', 'Math.cos(arg[0])');
>
> Is there a better way to call a function defined with
> sqlite3_create_function,
> as in this case, I'm not really performing an SQL operation. Something like
> a pragma would be useful, but I can't seem to find a way to define a new
> one from within an extension.
>
> 2. That function (createjs) returns errors in case of error, but, is there
> a cleaner, more standard way to return an OK status? So far I've checked
> the C API, but I didn't manage to find something like that. Do functions
> need to return anything at all?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] FTS5 prefix index documentation may be incorrect

[Charles Leifer]

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.0 (2015-11-06) and both show the same error
>> messages.
>>
>
> Thanks for reporting this. Now fixed here:
>
>   http://sqlite.org/src/info/11eb8e877e2ba859
>
> Dan.
>
>
>
>> Examples:
>>
>> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
>> Error: malformed prefix=... directive
>> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);
>> Error: multiple prefix=... directives
>>
>> What does appear to work is using a comma:
>>
>> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
>> sqlite> .schema ft
>> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
>>
>> 1: https://sqlite.org/fts5.html#section_4_2
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] FTS5 prefix index documentation may be incorrect

[Charles Leifer]

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 ft USING fts5(a, b, prefix=2, prefix=3);
Error: multiple prefix=... directives

What does appear to work is using a comma:

sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
sqlite> .schema ft
CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');

1: https://sqlite.org/fts5.html#section_4_2

[sqlite] Standalone use of b-tree?

[Charles Leifer]

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

[sqlite] lsm1

[Charles Leifer]

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. 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.
>
> Charles
>
> 1:
> http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-engine-as-a-stand-alone-nosql-database-with-python/
>

[sqlite] lsm1

[Charles Leifer]

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.

Charles

1:
http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-engine-as-a-stand-alone-nosql-database-with-python/

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

[Charles Leifer]

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 application layer.  Basically,
> we operate a custom replication layer atop sqlite.  It replicates
> individual transactions great with 2-phase commit, but right now you need
> to manually "bootstrap" a new server by copying the database from a
> different server.  I'd like to auto-bootstrap a new node by just starting
> it, it'd connect to a peer, and then download the entire database.
>
>
> > > how to use this API to do an incremental backup
> >
> > This API is not incremental; it always copies the entire database.
> >
>
> Agreed it will copy the entire database, but incrementally -- one bit at a
> time.  Each call to sqlite3_backup_step() backs up a bit more to the target
> file on disk.  My goal is to instead have sqlite3_backup_step() copy some
> pages to some block of RAM, so I can send that data over the wire and write
> it to the remote disk.
>
> -david
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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

[Charles Leifer]

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 up because people are
> encouraged to use the latest and greatest version of SQLite 3 at all times,
> and SQLite 4 sounds like something even later and greater. I wonder if a
> name like SQLite X might reduce the number of queries. Well, maybe not
> SQLite X, but something different that doesn't imply what 4 implies.
>
> On Mon, Oct 5, 2015 at 4:30 AM, Richard Hipp  wrote:
>
> > Jerry & Scott:  What are you hoping to achieve with SQLite4 that you
> > cannot do with SQLite3?
> >
> > On 10/5/15, Stephen Chrzanowski  wrote:
> > > There was a thread sometime last year about this.  Basically, SQLite4
> > isn't
> > > planned for a release at this time, and from what I understood then,
> > > chances are there isn't going to be a thought towards release for at
> few
> > > more years.  It basically is a playground to test theories, it is
> buggy,
> > > and such.  It shouldn't be considered for use in a production
> > environment.
> > >
> > > On Mon, Oct 5, 2015 at 1:18 AM, Scott Doctor 
> > wrote:
> > >
> > >>
> > >> Is there a release date set for sqlite4?
> > >>
> > >> 
> > >> Scott Doctor
> > >> scott at scottdoctor.com
> > >> --
> > >>
> > >>
> > >> On 10/4/2015 9:14 PM, Stephen Chrzanowski wrote:
> > >>
> > >>> https://sqlite.org/src4/tree?ci=trunk
> > >>>
> > >>> On Mon, Oct 5, 2015 at 12:02 AM, Jerry  wrote:
> > >>>
> > >>> Seems I could not find its source code ...
> >  https://sqlite.org/src4/doc/trunk/www/index.wiki
> > 
> >  Thanks.
> >  ___
> >  sqlite-users mailing list
> >  sqlite-users at mailinglists.sqlite.org
> > 
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > 
> >  ___
> > >>> sqlite-users mailing list
> > >>> sqlite-users at mailinglists.sqlite.org
> > >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >>>
> > >>>
> > >>>
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users at mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Querying nextchar extension

[Charles Leifer]

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 recursive CTE to take a
>> substring and recursively calculate all possible matches. I realize I
>> could
>> just use 'LIKE xxx%' to accomplish the same, but if anyone has any
>> thoughts
>> on writing such a query I'd love to hear them!
>>
>
> Something along these lines (untested):
>
> select group_concat(substr(word, length(:prefix)+1, 1), '')
> from words where substr(word, 1, length(:prefix)) = :prefix
>
> Not sure what recursive CTE has to do with it - what is there to recurse
> over?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Querying nextchar extension

[Charles Leifer]

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 return a string containing all the characters that could
possibly be the "next char" of a valid word.

For example:

nextchar('foota') -> 'g'
nextchar('footag') -> 'e'
nextchar('footage') -> no result

As a challenge I was curious how one would write a recursive CTE to take a
substring and recursively calculate all possible matches. I realize I could
just use 'LIKE xxx%' to accomplish the same, but if anyone has any thoughts
on writing such a query I'd love to hear them!

[sqlite] Using the json1 extension with Python

[Charles Leifer]

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 queries from Python:

http://charlesleifer.com/blog/using-the-sqlite-json-extension-with-python/

Hope you all find it helpful.

Charlie

[sqlite] Fwd: OT: Oracle functions for SQlite

[Charles Leifer]

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://sqlite-libs.cis.ksu.edu
>
>
>
> > Begin forwarded message:
> >
> > From: St?phane Faroult 
> > Subject: OT: Oracle functions for SQlite
> > Date: September 8, 2015 at 2:30:24 AM GMT+2
> > To: "Oracle-L (E-mail)" 
> > Reply-To: sfaroult at roughsea.com
> >
> > I don't know if there are many people on the list using SQLite, but I
> use it more and more often; teaching SQL is one reason (give a master file
> to students, and let them create, drop tables, run DML at will without any
> worry, and no need to bother about having a conveniently set server),
> another one is consulting, whenever I'd *like* to store some data but I am
> either unauthorized or unwilling to create my stuff on the database I'm
> working on. Great also for implementing the poor man's performance pack -
> dump your v$ every so often to a SQLite file, and you have something far
> more flexible than statspack.
> > The only snag is that SQLite is a bit weak function-wise. I have last
> spring given as assignment to the students in one of my classes the writing
> for SQLite of functions available in other products. Making everything
> homogeneous, writing a few functions I couldn't decently ask of
> undergraduates (even if I usually set the bar rather high), substituting my
> own date functions to the standard Unix ones so as to have the same
> behavior as Oracle in October 1582 and so forth has been a huge endeavor
> (not finished), it may still be a bit rough here and there but I have
> started publishing this collective effort as an open source library.
> >
> > It's at http://sqlite-libs.cis.ksu.edu/  >
> >
> > There isn't EVERYTHING, but all the classic functions are there.
> >
> > Enjoy.
> >
> > St?phane Faroult
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite3_column_name() issue with views

[Charles Leifer]

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. "
t1.name" -> name. When querying a view, though, sqlite will return the
quoted, aliased column name, e.g. "t1.name" -> "t1.name". Does this qualify
as a bug, or is this intended behavior?

I observed this with sqlite versions 3.8.2, 3.7.13 and 3.7.9.

Here is a short python script exemplifying the behavior:

import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('create table people (name);')
cursor.execute('insert into people values (?)', ('charlie',))

cursor.execute('select t1.name from people as t1')
print cursor.description
# (('name', None, None, None, None, None, None),)

cursor.execute('create view people_view as select * from people')
cursor.execute('select t1.name from people_view as t1')
print cursor.description
# (('t1.name', None, None, None, None, None, None),)

Thanks in advance,

Charlie
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users