Re: [sqlite] [EXTERNAL] Re: CASE optimization opportunity (was: filling a key/value table)

[Hick Gunter]

Tried both, no change with SQlite version 3.24. The reference t oval in the 
WHERE clause causes SQLite to recompute the value.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Tandetnik
Gesendet: Dienstag, 26. März 2019 21:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] CASE optimization opportunity (was: filling a 
key/value table)

See if something like this works better:

INSERT INTO logidx(keyid,value,location) SELECT id, val, location from (
   SELECT k.id id,
(case k.name when 'type' then l.type when 'name' then l.name when 'size' 
then l.size else NULL end) AS val,
l.location location
   FROM logfile l, logkey k
) WHERE val IS NOT NULL;

Or

WITH RawData AS (
   SELECT k.id id,
(case k.name when 'type' then l.type when 'name' then l.name when 'size' 
then l.size else NULL end) AS val,
l.location location
   FROM logfile l, logkey k
)
INSERT INTO logidx(keyid,value,location) SELECT id, val, location from RawData 
WHERE val IS NOT NULL;

Igor Tandetnik

On 3/26/2019 10:15 AM, Hick Gunter wrote:
> This works as expected, thanks.
>
> SQLite implements this as a pair of coroutines:
>
> Routine A) does a nested scan of the logfile (outer loop = just read
> the logifle once) and the logkey table (inner loop)
>
> Routine B) does the actual insert(s) into the logidx table
>
> Unfortunately, the necessary check of the generated val causes the CASE 
> expression to be evaluated twice (see bytecode).
>
> Instructions 16 thru 25 implement the CASE, leaving R12 holding the
> computed value Instruction 26 checks that something the evaluates to
> TRUE has been found Instructions 28 thru 37 duplicate the CASE,
> leaving R3 with the re-computed value
>
> The same effect could be achieved by copying R12 into R3 when buildung
> the record, just as R11 is copied to R4
>
> 9 VFilter0 437   00  iplan=r[7] zplan=''
> 10  VColumn0 4711   00  
> r[11]=vcolumn(47); atx_txlog.period_no
> 11  Lt 134211(BINARY)   53  if r[11] goto 42
> 12  Gt 144211(BINARY)   53  if r[11]>r[14] 
> goto 42
> 13  Explain130 0 SCAN TABLE keys AS k  00
> 14  Rewind 1 43000
> 15Column 1 1 15   00  r[15]=keys.name
> 16Ne 171915(NOCASE)   52  if r[15]!=r[17] 
> goto 19
> 17VColumn0 1512   00  
> r[12]=vcolumn(15); atx_txlog.event_type
> 18Goto   0 26000
> 19Ne 182215(NOCASE)   52  if r[15]!=r[18] 
> goto 22
> 20VColumn0 2812   00  
> r[12]=vcolumn(28); atx_txlog.retailer_loc_id
> 21Goto   0 26000
> 22Ne 192515(NOCASE)   52  if r[15]!=r[19] 
> goto 25
> 23VColumn0 6412   00  
> r[12]=vcolumn(64); atx_txlog.ticket_key_string
> 24Goto   0 26000
> 25Null   0 12000  r[12]=NULL
> 26IfNot  1241100
> 27Rowid  1 2 000  r[2]=rowid
> 28Ne 173115(NOCASE)   52  if r[15]!=r[17] 
> goto 31
> 29VColumn0 15300  
> r[3]=vcolumn(15); atx_txlog.event_type
> 30Goto   0 38000
> 31Ne 183415(NOCASE)   52  if r[15]!=r[18] 
> goto 34
> 32VColumn0 28300  
> r[3]=vcolumn(28); atx_txlog.retailer_loc_id
> 33Goto   0 38000
> 34Ne 193715(NOCASE)   52  if r[15]!=r[19] 
> goto 37
> 35VColumn0 64300  
> r[3]=vcolumn(64); atx_txlog.ticket_key_string
> 36Goto   0 38000
> 37Null   0 3 000  r[3]=NULL
> 38Copy   114 000  r[4]=r[11]
> 39VColumn0 6 500  
> r[5]=vcolumn(6); atx_txlog.sync_offset
> 40Yield  6 0 000
> 41  Next   1 15001
> 42VNext  0 10000
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Igor Tandetnik
> Gesendet: Donnerstag, 21. März 2019 17:29
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] filling a key/value ta

Re: [sqlite] Row locking sqlite3

[Wout Mertens]

See also
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-August/074060.html
-
this branch has been around for a while and I think it was mentioned
elsewhere that it definitely is being considered for mainline inclusion at
some not immediate point.

Wout.


On Tue, Mar 26, 2019 at 4:37 PM Joshua Wise 
wrote:

> I’ve seen the server mode <
> https://sqlite.org/src/raw/README-server-edition.html?name=0c6bc6f55191b6900595fe37470bbe5772953ab5c64dae967d07a5d58a0c3508>
> branch, but to me it’s a deal-breaker that it requires SYNCHRONOUS = OFF.
>
> This BEGIN CONCURRENT idea really interests me. By using WAL mode, where I
> can have synchronous commits, the feature becomes much more valuable.
>
> Any word from Mr. Hipp on the status of this branch? Will it ever make it
> into the official version of SQLite3, perhaps under a compile-time option?
>
> > On Mar 22, 2019, at 5:07 PM, Jens Alfke  wrote:
> >
> >
> >
> >> On Mar 22, 2019, at 1:38 PM, Barry Smith 
> wrote:
> >>
> >> You might be interested in the BEGIN CONCURRENT branch. It does page
> level locking (not quite as granular as row level).
> >>
> >>
> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md
> <
> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md
> >
> >
> > Oh, this is interesting. Is this a feature in development that will be
> released soon?
> >
> > [And to be pedantic: according to those docs, this feature does not do
> page level locking; it’s optimistic not pessimistic concurrency.]
> >
> > —Jens
> > ___
> > 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] JSON1: queries on object keys

[Jens Alfke]

> On Mar 26, 2019, at 10:41 AM, Warren Young  wrote:
> 
> The biggest problem with these isn’t the use of JSON per se, it’s that each 
> one is unindexed, so they’ll all be a full table scan. 

Well, we don’t know that; the question was just about whether those are 
reasonable queries, not whether they can be indexed.

> You should be able to create indices on expressions like “json_extract(json, 
> '$.bar')”, but you’ll want to test it to find out whether the space it takes 
> is worth the speed increase.

The index takes exactly the same amount of space as it would if ‘bar’ were a 
regular SQL column.

> Even better would be to simply choose not to store all of your data in JSON 
> form

It depends on the use case. The nice thing about storing data as JSON is that 
there’s total flexibility of the schema of that data; you can change the way 
the data is structured without having to alter the database. (Except to add new 
indexes as needed for performance.)

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

Re: [sqlite] CASE optimization opportunity (was: filling a key/value table)

[Igor Tandetnik]

See if something like this works better:

INSERT INTO logidx(keyid,value,location)
SELECT id, val, location from (
  SELECT k.id id,
   (case k.name when 'type' then l.type when 'name' then l.name when 'size' 
then l.size else NULL end) AS val,
   l.location location
  FROM logfile l, logkey k
) WHERE val IS NOT NULL;

Or

WITH RawData AS (
  SELECT k.id id,
   (case k.name when 'type' then l.type when 'name' then l.name when 'size' 
then l.size else NULL end) AS val,
   l.location location
  FROM logfile l, logkey k
)
INSERT INTO logidx(keyid,value,location)
SELECT id, val, location from RawData
WHERE val IS NOT NULL;

Igor Tandetnik

On 3/26/2019 10:15 AM, Hick Gunter wrote:

This works as expected, thanks.

SQLite implements this as a pair of coroutines:

Routine A) does a nested scan of the logfile (outer loop = just read the 
logifle once) and the logkey table (inner loop)

Routine B) does the actual insert(s) into the logidx table

Unfortunately, the necessary check of the generated val causes the CASE 
expression to be evaluated twice (see bytecode).

Instructions 16 thru 25 implement the CASE, leaving R12 holding the computed 
value
Instruction 26 checks that something the evaluates to TRUE has been found
Instructions 28 thru 37 duplicate the CASE, leaving R3 with the re-computed 
value

The same effect could be achieved by copying R12 into R3 when buildung the 
record, just as R11 is copied to R4

9 VFilter0 437   00  iplan=r[7] zplan=''
10  VColumn0 4711   00  r[11]=vcolumn(47); 
atx_txlog.period_no
11  Lt 134211(BINARY)   53  if r[11]r[14] goto 
42
13  Explain130 0 SCAN TABLE keys AS k  00
14  Rewind 1 43000
15Column 1 1 15   00  r[15]=keys.name
16Ne 171915(NOCASE)   52  if r[15]!=r[17] 
goto 19
17VColumn0 1512   00  
r[12]=vcolumn(15); atx_txlog.event_type
18Goto   0 26000
19Ne 182215(NOCASE)   52  if r[15]!=r[18] 
goto 22
20VColumn0 2812   00  
r[12]=vcolumn(28); atx_txlog.retailer_loc_id
21Goto   0 26000
22Ne 192515(NOCASE)   52  if r[15]!=r[19] 
goto 25
23VColumn0 6412   00  
r[12]=vcolumn(64); atx_txlog.ticket_key_string
24Goto   0 26000
25Null   0 12000  r[12]=NULL
26IfNot  1241100
27Rowid  1 2 000  r[2]=rowid
28Ne 173115(NOCASE)   52  if r[15]!=r[17] 
goto 31
29VColumn0 15300  r[3]=vcolumn(15); 
atx_txlog.event_type
30Goto   0 38000
31Ne 183415(NOCASE)   52  if r[15]!=r[18] 
goto 34
32VColumn0 28300  r[3]=vcolumn(28); 
atx_txlog.retailer_loc_id
33Goto   0 38000
34Ne 193715(NOCASE)   52  if r[15]!=r[19] 
goto 37
35VColumn0 64300  r[3]=vcolumn(64); 
atx_txlog.ticket_key_string
36Goto   0 38000
37Null   0 3 000  r[3]=NULL
38Copy   114 000  r[4]=r[11]
39VColumn0 6 500  r[5]=vcolumn(6); 
atx_txlog.sync_offset
40Yield  6 0 000
41  Next   1 15001
42VNext  0 10000

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Tandetnik
Gesendet: Donnerstag, 21. März 2019 17:29
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table

On 3/21/2019 12:04 PM, Hick Gunter wrote:

I have an external data store that is accessible to sqlite as a virtual table. 
The equivalent SQL declaration is similar to:

CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, size
INTEGER, ...);

I would like to create an index as a native SQLite table declared like:

CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER,
primary key (keyid, value, location) ) WITHOUT ROWID;

The fields of interest are stored in a config table:

CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT ); INSERT INTO
logkey(name) VALUES ('type'),('name'),('size');

The naive method of inserting values is t

[sqlite] unsubscribe failed

[Phil Dagosto]

I submitted an unsubscribe request. I received the below message in my Spam
folder. I did not receive instructions on how to complete the unsubscribe
action.

This is the mail system at host sqlite.org.

I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.

For further assistance, please send mail to postmaster.

If you do so, please include this problem report. You can
delete your own text from the attached returned message.

   The mail system

:
user
unknown



-- Forwarded message --
From: pdagosto 
To: sqlite-users-unsubscribe-phildagosto=gmail@mailinglists.sqlite.org
Cc:
Bcc:
Date: Tue, 26 Mar 2019 13:27:27 -0700 (MST)
Subject:

ReplyForward




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

Re: [sqlite] JSON1: queries on object keys

[Warren Young]

On Mar 26, 2019, at 8:35 AM, Wout Mertens  wrote:
> 
> what would be the best way to answer these, given `CREATE TABLE foo(id
> TEXT, json JSON);` and json is always a json object:
> 
>   - all rows with a given key bar
>  - SELECT * FROM foo WHERE json_extract(json, '$.bar') IS NOT NULL;
>   - all rows where there are only any of the given keys a,b in the object
>  - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}';
>   - all rows where there are all the given keys a,b and no others in the
>   object
>  - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}' and
>  json_extract(json, '$.a') IS NOT NULL and json_extract(json,
> '$.b') IS NOT
>  NULL;

The biggest problem with these isn’t the use of JSON per se, it’s that each one 
is unindexed, so they’ll all be a full table scan.  That makes your use of 
SQLite here scarcely better than storing all the data in a single JSON object, 
stored in a simple text file, querying it with something like jq.

If we’re talking about hundreds or a few thousand records, the parsing cost 
probably doesn’t matter, but if we’re talking millions or billions of records, 
it’s a pretty expensive way to go.

You should be able to create indices on expressions like “json_extract(json, 
'$.bar')”, but you’ll want to test it to find out whether the space it takes is 
worth the speed increase.

You could also think about partially denormalizing the data: on record 
insertion, pre-parse the JSON and extract the key fields as SQLite fields.

Even better would be to simply choose not to store all of your data in JSON 
form: keep the key fields in normal SQLite fields.  Then you can do indexed 
lookups on the actual records you require and do JSON extraction only on those 
records for each query.

> these queries seem pretty onerous to me

SQLite’s JSON extension is pretty quick.  A recent test I did here did a full 
table scan with JSON parsing on each of ~1 records in about 80ms.

On the one hand, that’s impressively fast, but on the other, it’s up in 
human-scale time, so I ended up caching the query results for a few minutes to 
avoid making this expensive query too often.

Keep in mind that there’s nothing you can’t encode in plain old SQLite, in 
principle.  JSON is a luxury, not an absolute requirement.  Transforming the 
data to be more relational than hierarchical could be a very worthwhile 
investment, if you have enough records or a low enough latency budget.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Joshua Wise]

I’ve seen the server mode 

 branch, but to me it’s a deal-breaker that it requires SYNCHRONOUS = OFF.

This BEGIN CONCURRENT idea really interests me. By using WAL mode, where I can 
have synchronous commits, the feature becomes much more valuable.

Any word from Mr. Hipp on the status of this branch? Will it ever make it into 
the official version of SQLite3, perhaps under a compile-time option?

> On Mar 22, 2019, at 5:07 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Mar 22, 2019, at 1:38 PM, Barry Smith  wrote:
>> 
>> You might be interested in the BEGIN CONCURRENT branch. It does page level 
>> locking (not quite as granular as row level).
>> 
>> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md 
>> 
> 
> Oh, this is interesting. Is this a feature in development that will be 
> released soon?
> 
> [And to be pedantic: according to those docs, this feature does not do page 
> level locking; it’s optimistic not pessimistic concurrency.]
> 
> —Jens
> ___
> 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] read-only database in WAL mode and temporary files

[Joshua Wise]

From my understanding, the SHM file is needed to communicate to writers that 
they are in fact performing a read operation (i.e., locks). Without that 
communication, a writer might insert some data and then perform a checkpoint, 
without knowing that there is a database reader, and thus overwrite pages in 
the main database file out from under the reader.

Why is the WAL file needed? I’m not sure. It might be just to make the 
implementation simpler. Since the reader must create the SHM file regardless, 
it may as well create the WAL file too.

> On Mar 24, 2019, at 8:26 AM, Thomas Kurz  wrote:
> 
> But it's of zero size, so there cannot be anything inside. As far as I have 
> understood, it's just a journal containing the changes since the last 
> checkpoint. If I don't change anything, why should there be the need of this 
> journal? I would expect the journal to be created when the first write 
> operation (INSERT, DELETE, whatsoever) occurs, but not for a read operation.
> 
> And moreover, why is read-only-mode allowed to create a journal (this is 
> contradictory to the read-only-idea), but not delete it upon closing the 
> connection?
> 
> Actually, this leads to more problems: If the database itself has the 
> read-only-attribute set (by filesystem or ACL), SQLite would start journaling 
> changes which can be never be checkpointed. In commonsense, a write operation 
> should immediately terminate and return an error in this case.
> 
> Finally, I have noticed the immutable mode, but I have a bad feeling about 
> it. If I understand correctly, a second connection could still open the same 
> database in write-mode. I would instead expect that a read-only-connection 
> locked the database (using file locking mechanism) and a parallel 
> write-connection to be blocked until the locked is released. (No temporary 
> files should be required until this moment.)
> 
> 
> - Original Message - 
> From: Shawn Wagner 
> To: SQLite mailing list 
> Sent: Sunday, March 24, 2019, 11:29:58
> Subject: [sqlite] read-only database in WAL mode and temporary files
> 
> Even a read only database needs to create the wal journal if it doesn't
> exist at the moment for a database that uses that mode:
> https://www.sqlite.org/wal.html#read_only_databases
> 
> You might look into the immutable option mentioned there and see if it's
> appropriate for your needs.
> 
> 
> On Sun, Mar 24, 2019, 2:33 AM Thomas Kurz  wrote:
> 
>> When I open a database in read-only mode (?mode=ro), I observe that the
>> WAL and SHM temporary files are created anyway. Is there any possibility to
>> prevent the creation of these files? Aren't they useless?
> 
>> Even worse (using sqlite3.exe version 3.27.1):
> 
>> .open test.db
>> pragma journal_mode=wal;
>> create table a (b integer);
>> insert into a (1);
>> .quit
> 
>> This creates database with WAL and SHM files; those files are correctly
>> removed when exiting.
> 
>> .open --readonly test.db
>> select * from a;   <--- creates WAL and SHM -- why?
>> .quit
> 
>> Now, WAL and SHM files aren't deleted anymore.
> 
>> Proposal: Neither WAL nor SHM should be created at all when opening a
>> WAL-mode database in read-only mode.
> 
>> ___
>> 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] JSON1: queries on object keys

[Dominique Devienne]

On Tue, Mar 26, 2019 at 3:35 PM Wout Mertens  wrote:

> Hi amazing list,
>
> what would be the best way to answer these, given `CREATE TABLE foo(id
> TEXT, json JSON);` and json is always a json object:
>
>- all rows with a given key bar
>   - SELECT * FROM foo WHERE json_extract(json, '$.bar') IS NOT NULL;
>- all rows where there are only any of the given keys a,b in the object
>   - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}';
>- all rows where there are all the given keys a,b and no others in the
>object
>   - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}' and
>   json_extract(json, '$.a') IS NOT NULL and json_extract(json,
> '$.b') IS NOT
>   NULL;
>
> these queries seem pretty onerous to me, I hope there are better ways…
>

Maybe using https://www.sqlite.org/json1.html#jtype might be faster?

I think it's possible that when you use several json_ functions, notably in
your WHERE
clause, the document is reparsed several times. I haven't looked at the
detail for a while, but I think
internally JSON1 can work on an intermediate binary representation that's
more efficient,
but only when call-chaining (depends on subtype APIs I seem to remember),
which doesn't help here.
Unfortunately it doesn't work with ephemeral tables AFAIK, when using a CTE
for example, to force the parsing
once, and then process the different json_ function off that more efficient
representation. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] JSON1: queries on object keys

[Wout Mertens]

Hi amazing list,

what would be the best way to answer these, given `CREATE TABLE foo(id
TEXT, json JSON);` and json is always a json object:

   - all rows with a given key bar
  - SELECT * FROM foo WHERE json_extract(json, '$.bar') IS NOT NULL;
   - all rows where there are only any of the given keys a,b in the object
  - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}';
   - all rows where there are all the given keys a,b and no others in the
   object
  - SELECT * FROM foo WHERE json_remove(json,'$.a','$.b') ='{}' and
  json_extract(json, '$.a') IS NOT NULL and json_extract(json,
'$.b') IS NOT
  NULL;

these queries seem pretty onerous to me, I hope there are better ways…

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

[sqlite] CASE optimization opportunity (was: filling a key/value table)

[Hick Gunter]

This works as expected, thanks.

SQLite implements this as a pair of coroutines:

Routine A) does a nested scan of the logfile (outer loop = just read the 
logifle once) and the logkey table (inner loop)

Routine B) does the actual insert(s) into the logidx table

Unfortunately, the necessary check of the generated val causes the CASE 
expression to be evaluated twice (see bytecode).

Instructions 16 thru 25 implement the CASE, leaving R12 holding the computed 
value
Instruction 26 checks that something the evaluates to TRUE has been found
Instructions 28 thru 37 duplicate the CASE, leaving R3 with the re-computed 
value

The same effect could be achieved by copying R12 into R3 when buildung the 
record, just as R11 is copied to R4

9 VFilter0 437   00  iplan=r[7] zplan=''
10  VColumn0 4711   00  r[11]=vcolumn(47); 
atx_txlog.period_no
11  Lt 134211(BINARY)   53  if r[11]r[14] goto 
42
13  Explain130 0 SCAN TABLE keys AS k  00
14  Rewind 1 43000
15Column 1 1 15   00  r[15]=keys.name
16Ne 171915(NOCASE)   52  if r[15]!=r[17] 
goto 19
17VColumn0 1512   00  
r[12]=vcolumn(15); atx_txlog.event_type
18Goto   0 26000
19Ne 182215(NOCASE)   52  if r[15]!=r[18] 
goto 22
20VColumn0 2812   00  
r[12]=vcolumn(28); atx_txlog.retailer_loc_id
21Goto   0 26000
22Ne 192515(NOCASE)   52  if r[15]!=r[19] 
goto 25
23VColumn0 6412   00  
r[12]=vcolumn(64); atx_txlog.ticket_key_string
24Goto   0 26000
25Null   0 12000  r[12]=NULL
26IfNot  1241100
27Rowid  1 2 000  r[2]=rowid
28Ne 173115(NOCASE)   52  if r[15]!=r[17] 
goto 31
29VColumn0 15300  r[3]=vcolumn(15); 
atx_txlog.event_type
30Goto   0 38000
31Ne 183415(NOCASE)   52  if r[15]!=r[18] 
goto 34
32VColumn0 28300  r[3]=vcolumn(28); 
atx_txlog.retailer_loc_id
33Goto   0 38000
34Ne 193715(NOCASE)   52  if r[15]!=r[19] 
goto 37
35VColumn0 64300  r[3]=vcolumn(64); 
atx_txlog.ticket_key_string
36Goto   0 38000
37Null   0 3 000  r[3]=NULL
38Copy   114 000  r[4]=r[11]
39VColumn0 6 500  r[5]=vcolumn(6); 
atx_txlog.sync_offset
40Yield  6 0 000
41  Next   1 15001
42VNext  0 10000

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Tandetnik
Gesendet: Donnerstag, 21. März 2019 17:29
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table

On 3/21/2019 12:04 PM, Hick Gunter wrote:
> I have an external data store that is accessible to sqlite as a virtual 
> table. The equivalent SQL declaration is similar to:
>
> CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, size
> INTEGER, ...);
>
> I would like to create an index as a native SQLite table declared like:
>
> CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER,
> primary key (keyid, value, location) ) WITHOUT ROWID;
>
> The fields of interest are stored in a config table:
>
> CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT ); INSERT INTO
> logkey(name) VALUES ('type'),('name'),('size');
>
> The naive method of inserting values is thus:
>
> INSERT INTO logidx(keyid,value,location) SELECT k.id,l.type,l.location
> from logkey k, logfile l where k.name = 'type'AND l.type IS NOT NULL;
> INSERT INTO logidx(keyid,value,location) SELECT k.id,l.name,l.location
> from logkey k, logfile l where k.name = 'name'AND l.name IS NOT NULL;
> INSERT INTO logidx(keyid,value,location) SELECT k.id,l.size,l.location
> from logkey k, logfile l where k.name = 'size'AND l.size IS NOT NULL;
>
> This hast he disadvantage of requiring a complete scan of the virtual logidx 
> table for each kind of entry.
>
> Any ideas on how to create all the tuples with only one pass of the logidx 
> table?

Something like 

Re: [sqlite] sqlar: makeDirectory called with permission bits of file

[Winter, Martin]

Hi Richard, Dominique,

I downloaded and compiled the latest trunk and can confirm that it fixes this 
issue.

Thanks!
Martin

> -Ursprüngliche Nachricht-
> Von: sqlite-users  Im Auftrag 
> von Dominique Devienne
> Gesendet: Dienstag, 26. März 2019 11:57
> An: SQLite mailing list 
> Betreff: Re: [sqlite] sqlar: makeDirectory called with permission bits of file
> 
> On Mon, Mar 25, 2019 at 4:07 PM Richard Hipp  wrote:
> 
> > Please try the latest trunk check-in and let use know whether or not
> > it fixes your issue.
> >
> 
> For reference:
> https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Fsrc%2Finfo%2Ff11c89595dc65f89&data=
> 02%7C01%7Cmartin.winter%40siemens.com%7C1113089fb4244966380f08d6b1d9dc0e%7C38ae3bcd95794fd4addab42e1495d55a%7C1
> %7C0%7C636891946588855618&sdata=wfFUEcCLLZdHUnrma8v1H20CD21yWtbYjgzXOz1Qgcw%3D&reserved=0
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://eur01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-
> users&data=02%7C01%7Cmartin.winter%40siemens.com%7C1113089fb4244966380f08d6b1d9dc0e%7C38ae3bcd95794fd4addab42
> e1495d55a%7C1%7C0%7C636891946588855618&sdata=037IGJUPbMFKR%2B74Sc9zYU6okBgB7GEbUuWZWQQbFH4%3D&r
> eserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] proposal: make the archive option -Au to update only newer files. /Patch included/

[Dominique Devienne]

On Mon, Mar 25, 2019 at 8:35 AM Захар Малиновский <
zakhar.malinovs...@gmail.com> wrote:

> I would like to know is there a way to see if this proposal rejected or
> something? Is this a write ML to propose with patch?
>

Looks like Richard implemented what you need?
https://www.sqlite.org/src/info/191a023ae708490e
https://www.sqlite.org/src/info/919fec91dac4cb0d

Note that he typically does not accept patches, but re implements his own
version,
for code attribution reasons, among others. Please review and report
feedback, if you want.

And yes I saw  suggested to use checksums, but this
> means we read all the files in a source directory, that is huge.  This is
> a reason for relying on timestamps. If some os does not update its files
> timestumps why we suffer.


Content-based checks are more robust and safer, at the cost of more reading
of course.
timestamps can easily be changed. But sure, this is off-topic for SQLar.
--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlar: makeDirectory called with permission bits of file

[Dominique Devienne]

On Mon, Mar 25, 2019 at 4:07 PM Richard Hipp  wrote:

> Please try the latest trunk check-in and let use know whether or not it
> fixes your issue.
>

For reference: https://www.sqlite.org/src/info/f11c89595dc65f89
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users