Re: [sqlite] Performance vs. memory trade-off question

[Jonathan Moules]

I'd lean towards speed being preferable in the default. Reasoning:

* People who use SQLite in low-RAM (i.e. embedded) scenarios compile it 
themselves.


* People who use it on PCs (like me) almost never compile it, they just 
use whatever the distro/sqlite.org/language-of-choice provides, and that 
in turn uses the default.


The default therefore is much more likely to affect the later user than 
the former. 74kB of RAM on a machine that has some multiple of 
~4,000,000kB of RAM is a rounding error.


Add a note to the docs page about "low RAM use notes" for those who need 
to know (I'm guessing there is such a page; never looked for it).


My 2p,

Jonathan

On 2019-12-14 13:27, Richard Hipp wrote:

A new feature on a branch has the following disadvantages:

(1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
normally about 0.5% faster, so enabling this feature is sort of like
going back by one-half of a release cycle.)

(2)  The code space (the size of the library) is between 400 and 500
bytes larger (depending on compiler and optimization settings).

The this one advantage:

(3)  Each database connection uses about 72 KB less heap space.

QUESTION:  Should this feature be default-on or default-off?

What's more important to you?  0.25% fewer CPU cycles or about 72KB
less heap space used per database connection?

The feature can be activated or deactivated at start-time, but you
take the disadvantages (the performance hit and slightly larger
library size) regardless, unless you disable the feature at
compile-time.  If the feature is compile-time disabled, then the
corresponding code is omitted and and it cannot be turned on at
start-time.

If you have opinions, you can reply to this mailing list, or directly to me.



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

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[Jonathan Moules]

Thanks for the suggestion.
Changing the USING to ON makes absolutely no difference. The speed is 
the same and the query plans (both EXPLAIN and EXPLAIN QUERY PLAN) are 
absolutely identical. Same for if I convert it to WHERE:


WHERE joining_table.data_id = data_table.data_id;

On 2019-12-03 14:46, Simon Slavin wrote:

On 3 Dec 2019, at 8:48am, Jonathan Moules  wrote:


   SELECT
   count(1)
   FROM
   data_table
   JOIN joining_table USING (data_id);

   SELECT
   count(1)
   FROM data_table
   JOIN joining_table
   ON joining_table.data_id = data_table.data_id;

Given the rest of the structure you gave, including the indexes, compare the 
speeds of these two.

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] [EXTERNAL] Slow joining of tables with indexes

[Jonathan Moules]

Hi List,

So, I've altered my structure to be INTEGER primary keys, but I'm still 
seeing very slow query times when joining. The original query is faster:


  SELECT
  count(1)
  FROM
  data_table
  JOIN joining_table USING (data_id);

It takes ~2s, but if I then join on to the next table (ignore_me - only 
~200,000 records), it goes up to a whopping 27s - and this is on the SSD!


SELECT

count(1)

FROM

data_table

    JOIN joining_table USING (data_id)

    JOIN ignore_me USING (ignored_id)

;

I can see it's using the indexes, but strangely the index it's using on 
the ignore_me table isn't the PK index but a FK index (INTEGER) to the 
next table in the sequence (not included in this schema):


5    0    0    SCAN TABLE ignore_me USING COVERING INDEX 
ignored__e_id__fk_idx
7    0    0    SEARCH TABLE data_to_ignored USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx (s_id=?)

11    0    0    SEARCH TABLE data USING INTEGER PRIMARY KEY (rowid=?)

Any thoughts? This seems like relational-database bread-and-butter so 
I'm sure I'm doing something wrong to be getting these slow speeds but I 
can't see what.


Thanks,

Jonathan

=

Schema now:

-- 1.7 million items

CREATE TABLE data_table (

data_id INTEGER PRIMARY KEY,

data_1 TEXT,

data_2 TEXT );


-- 1.9 million items

CREATE TABLE joining_table (

data_id INTEGER REFERENCES data_table (data_id),

ignored_id INTEGER REFERENCES ignore_me (ignored_id),

misc_col_1 TEXT,

misc_col_2 TEXT

);


-- ~200,000 items

CREATE TABLE ignore_me (

ignored_id INTEGER PRIMARY KEY,

ignored_col TEXT

);


-- Allow quick joining from data_table to ignore_me

CREATE INDEX IF NOT EXISTS joining_table__data_ignored_id__fk_idx ON 
joining_table (


data_id ASC,

ignored_id ASC

);

-- Allow quick joining from ignore_me to data_table

CREATE INDEX IF NOT EXISTS joining_table__ignored_data_id__fk_idx ON 
joining_table (


ignored_id ASC,

data_id ASC

);


-- Example data:


INSERT INTO data_table (data_id) VALUES (1); INSERT INTO data_table 
(data_id) VALUES (2);



INSERT INTO ignore_me VALUES (1, 'words'); INSERT INTO ignore_me VALUES 
(2, 'more words'); INSERT INTO ignore_me VALUES (3, 'yet more words');



INSERT INTO joining_table (data_id, ignored_id) VALUES (1, 1); INSERT 
INTO joining_table (data_id, ignored_id) VALUES (1, 2); INSERT INTO 
joining_table (data_id, ignored_id) VALUES (2, 3);




SELECT

count(1)

FROM

data_table

    JOIN joining_table USING (data_id)

    JOIN ignore_me USING (ignored_id)

;






On 2019-12-02 13:42, Jonathan Moules wrote:
Thanks for the comments. I've done some testing. Results below for 
those interested.


* Unnecessary manual indexes on the Primary Key - good spot, I'd 
forgotten SQLite does that!


* I was indeed using a Hard Disk but that was intentional - this is 
for customers and I can't know their hardware.


* INTEGERs vs WITHOUT ROW_ID vs what I have now vs full-on 64 bit 
INTEGERs - Tested below


Non-scientific Timings are below (in seconds). "HDD" = Hard drive, 
otherwise it's a SSD. "Indexes" means an index built on the FK.


==

Original structure

-- original (16 character string PK/FK) - indexes
-- 4.04
-- 4.6 (hdd)
-- 4.1
-- 4.7 (hdd)
-- 4.14
-- 5.03 (hdd)

-- original (16 character string PK/FK) - no indexes
-- 4.03
-- 5.9 (hdd)
-- 5.1
-- 11.4 (hdd)
-- 4.18
-- 9.766 (hdd)

So not much speed difference with indexes between SSD and HDD.

===

Original structure but changing to WITHOUT ROW_ID

-- original (16 character string PK/FK) - WITHOUT ROW_ID - indexes
-- 3.69
-- 2.9 (hdd)
-- 3.8
-- 5.2 (hdd)
-- 3.74
-- 5.8 (hdd)

-- original (16 character string PK/FK) - WITHOUT ROW_ID - no indexes
-- 3.45
-- 3.4 (hdd)
-- 3.4
-- 3.4 (hdd)
-- 8.47
-- 18.4 (hdd)

Curiously with the with-indexes seems to on average be slower than 
without indexes for this on the HDD.


==

Auto-incrementing INTEGER as the ID and FK

-- integer_id (autoincrement INTEGER PK/FK) - indexes
-- 1.3
-- 1.21
-- 6.9 (hdd)
-- 1.2
-- 4.4 (hdd)
-- 2.45
-- 5.2 (hdd)

-- integer_id (autoincrement INTEGER PK/FK) - no indexes
-- 1.3
-- 19.3 (hdd)
-- 4.7
-- 9.1 (hdd)
-- 5.229
-- 18.98 (hdd)

no-index speeds seem to be very random.



The last test I did was to convert the hex strings to their 64bit 
INTEGER equivalents and use those as the keys. So still using a 64bit 
INTEGER as the keys, they could be anything rather than low value


So my keys are like:
-9223326038759585676
-5012230838021194131
-3961911462337065450
3423089283580538480
9221679147258515042
...

my integer (Hex to INTEGER PK/FK - negative PKs) - index
-- 2.02
-- 2.03
-- 1.9 (hdd)
-- 1.9 (hdd)
-- 6.1
-- 1.9 (hdd)

my integer (Hex to INTEGER PK/FK - negative PKs) - no indexes
-- 2.48s
-- 2.42s
-- 2.4 (hdd)
-- 2.4 (hdd)
-- 7.5
-- 20.1 (hdd)

The HDD was consistently good with these full-size 64bit keys which 
surprised me. I've seen that there are some optimisations assuming 
positive integers - 
http:/

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

[Jonathan Moules]

Thanks for the comments. I've done some testing. Results below for those 
interested.


* Unnecessary manual indexes on the Primary Key - good spot, I'd 
forgotten SQLite does that!


* I was indeed using a Hard Disk but that was intentional - this is for 
customers and I can't know their hardware.


* INTEGERs vs WITHOUT ROW_ID vs what I have now vs full-on 64 bit 
INTEGERs - Tested below


Non-scientific Timings are below (in seconds). "HDD" = Hard drive, 
otherwise it's a SSD. "Indexes" means an index built on the FK.


==

Original structure

-- original (16 character string PK/FK) - indexes
-- 4.04
-- 4.6 (hdd)
-- 4.1
-- 4.7 (hdd)
-- 4.14
-- 5.03 (hdd)

-- original (16 character string PK/FK) - no indexes
-- 4.03
-- 5.9 (hdd)
-- 5.1
-- 11.4 (hdd)
-- 4.18
-- 9.766 (hdd)

So not much speed difference with indexes between SSD and HDD.

===

Original structure but changing to WITHOUT ROW_ID

-- original (16 character string PK/FK) - WITHOUT ROW_ID - indexes
-- 3.69
-- 2.9 (hdd)
-- 3.8
-- 5.2 (hdd)
-- 3.74
-- 5.8 (hdd)

-- original (16 character string PK/FK) - WITHOUT ROW_ID - no indexes
-- 3.45
-- 3.4 (hdd)
-- 3.4
-- 3.4 (hdd)
-- 8.47
-- 18.4 (hdd)

Curiously with the with-indexes seems to on average be slower than 
without indexes for this on the HDD.


==

Auto-incrementing INTEGER as the ID and FK

-- integer_id (autoincrement INTEGER PK/FK) - indexes
-- 1.3
-- 1.21
-- 6.9 (hdd)
-- 1.2
-- 4.4 (hdd)
-- 2.45
-- 5.2 (hdd)

-- integer_id (autoincrement INTEGER PK/FK) - no indexes
-- 1.3
-- 19.3 (hdd)
-- 4.7
-- 9.1 (hdd)
-- 5.229
-- 18.98 (hdd)

no-index speeds seem to be very random.



The last test I did was to convert the hex strings to their 64bit 
INTEGER equivalents and use those as the keys. So still using a 64bit 
INTEGER as the keys, they could be anything rather than low value


So my keys are like:
-9223326038759585676
-5012230838021194131
-3961911462337065450
3423089283580538480
9221679147258515042
...

my integer (Hex to INTEGER PK/FK - negative PKs) - index
-- 2.02
-- 2.03
-- 1.9 (hdd)
-- 1.9 (hdd)
-- 6.1
-- 1.9 (hdd)

my integer (Hex to INTEGER PK/FK - negative PKs) - no indexes
-- 2.48s
-- 2.42s
-- 2.4 (hdd)
-- 2.4 (hdd)
-- 7.5
-- 20.1 (hdd)

The HDD was consistently good with these full-size 64bit keys which 
surprised me. I've seen that there are some optimisations assuming 
positive integers - 
http://peterhansen.ca/blog/sqlite-negative-integer-primary-keys.html - 
but it's odd that the HDD was better than the SSD for the most part with 
these.


Also the full-size 64bit integers were a fair percentage slower than the 
regular integers even though there were the exact same number of them.



Thanks again,

Jonathan


On 2019-11-26 14:40, David Raymond wrote:

Not the reason for the slowdown, but note that both of these are redundant:

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
  data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
  ignored_id
);

...because you declared them as the primary keys in the table creation. So you 
now have 2 different indexes on the exact same data for each of those.


The rest of it looks fine to me anyway, and I'm not sure why you'd be seeing 
such slow times. Old slow hard disk?

If you analyze and vacuum it does it get any better?

I think the CLI has something like ".scanstats on" to get a little more info, 
but I'm not sure how much more info it'll provide.


-Original Message-
From: sqlite-users  On Behalf Of 
Hick Gunter
Sent: Tuesday, November 26, 2019 4:57 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

You are using text columns as primary keys and referencing them directly in 
foreign keys. This is probably not what you want, because it duplicates the 
text key. Also, with foreign keys enabled, your join is not accomplishing 
anything more than a direct select from joining_table, just with more effort 
(and circumventing the count() optimization).

SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the 
table. This is what you should be using as a foreign key, because it is twice 
as fast as using an index.

OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those 
too

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jonathan Moules
Gesendet: Dienstag, 26. November 2019 10:25
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes

Hi List,
I have a relational table setup where I've built indexes but I'm still seeing 
very slow join times on middling amounts of data. I'm guessing I'm doing 
something wrong but I can't see what. (SQLite: 3.24.0)

Simplified schema as below.
The ids are 16 character hex strings. I've included the ignore_me table only 
because it's relevant to the indexes.
Note: I can *guarantee* that the 

[sqlite] Slow joining of tables with indexes

[Jonathan Moules]

Hi List,
I have a relational table setup where I've built indexes but I'm still 
seeing very slow join times on middling amounts of data. I'm guessing 
I'm doing something wrong but I can't see what. (SQLite: 3.24.0)


Simplified schema as below.
The ids are 16 character hex strings. I've included the ignore_me table 
only because it's relevant to the indexes.
Note: I can *guarantee* that the data inserted into `data_table` and 
`ignore_me` is ordered by their respective primary keys ASC. Entries in 
joining_table are ordered by one of either data_id ASC or ignored_id ASC 
depending on creation method.


--==

-- 1.7 million items
CREATE TABLE data_table (
    data_id                    TEXT     PRIMARY KEY
                                        NOT NULL
                                        COLLATE NOCASE,
    data_1                        TEXT,
    data_2                     TEXT
);

-- 1.9 million items
CREATE TABLE joining_table (
    data_id     TEXT     REFERENCES data_table (data_id)
                            NOT NULL
                            COLLATE NOCASE,
    ignored_id         TEXT    REFERENCES ignore_me (ignored_id)
                            NOT NULL
                            COLLATE NOCASE,
    misc_col_1        TEXT,
    misc_col_2        TEXT
);

-- ~200,000 items
CREATE TABLE ignore_me (
    ignored_id                    TEXT     PRIMARY KEY
                                        NOT NULL
                                        COLLATE NOCASE
);

CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table (
    data_id
);
CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me (
    ignored_id
);

-- Allow quick joining from data_table to ignore_me
CREATE INDEX IF NOT EXISTS joining_table__data_ignored_id__fk_idx ON 
joining_table (

    data_id ASC,
    ignored_id ASC
);
-- Allow quick joining from ignore_me to data_table
CREATE INDEX IF NOT EXISTS joining_table__ignored_data_id__fk_idx ON 
joining_table (

    ignored_id ASC,
    data_id ASC
);

-- Example data:

INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6');
INSERT INTO data_table (data_id) VALUES ('579c57f1268c0f5c');

INSERT INTO ignore_me VALUES ('c402eb3f05d433f2');
INSERT INTO ignore_me VALUES ('d827e58953265f63');
INSERT INTO ignore_me VALUES ('ec1d2e817f55b249');

INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'c402eb3f05d433f2');
INSERT INTO joining_table (data_id, ignored_id) VALUES 
('00196a21e8c0f9f6', 'd827e58953265f63');
INSERT INTO joining_table (data_id, ignored_id) VALUES 
('579c57f1268c0f5c', 'ec1d2e817f55b249');





-- Then to test the speed I'm simply doing:
    SELECT
        count(1)
    FROM
        data_table
        JOIN joining_table USING (data_id);

--==

The query plan says it's using the indexes:
    SCAN TABLE joining_table USING COVERING INDEX 
joining_table__ignored_data_id__fk_idx
    SEARCH TABLE data_table USING COVERING INDEX 
data_table__data_id__pk_idx (data_id=?)


But it takes about 20 seconds to do that count on the full dataset.

The full EXPLAIN from the full dataset:

0    Init    0    16    0        00
1    Null    0    1    1        00
2    OpenRead    2    771875    0    k(3,NOCASE,NOCASE,)    00
3    OpenRead    3    737715    0    k(2,NOCASE,)    02
4    Rewind    2    12    2    0    00
5    Column    2    1    2        00
6    SeekGE    3    11    2    1    00
7    IdxGT    3    11    2    1    00
8    Integer    1    3    0        00
9    AggStep0    0    3    1    count(1)    01
10    Next    3    7    1        00
11    Next    2    5    0        01
12    AggFinal    1    1    0    count(1)    00
13    Copy    1    4    0        00
14    ResultRow    4    1    0        00
15    Halt    0    0    0        00
16    Transaction    0    0    77    0    01
17    Goto    0    1    0        00

Thoughts? What (probably obvious) thing am I missing?

Thanks,
Jonathan



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

Re: [sqlite] Backing up a SQLite database without the CLI

[Jonathan Moules]

Hi Simon,

Thanks for your thoughts. Sorry, I should have been clearer: I have no 
way of knowing if there are other open connections to the file - there 
may be as it's a web-application. So I'll assume there are connections.


At this point I'm starting to think that the best option is to create a 
new database with the requisite structure and copy the data across via 
an ATTACH (there are only two tables and one will almost always be empty 
at this point).


Any other thoughts welcome though!
Cheers,
Jonathan

On 2019-03-18 13:37, Simon Slavin wrote:

On 18 Mar 2019, at 1:10pm, Jonathan Moules  wrote:


I was wondering if there was a good way of backing up an SQLite database if you 
do *not* have access to the SQLite command line tool (which I know has .backup 
- https://stackoverflow.com/a/25684912). [snip]
I've considered simply running "PRAGMA wal_checkpointer;" and then copying the 
file immediately after that, but that still seems prone to error.

Ideally, rather than force a WAL checkpoint, close the file, make the copy, 
then open it again.  This does not take significantly more time, and it ensures 
that you will copy the right thing no matter what caching and optimization your 
tools are trying to do.

In more general terms ...

Are you trying to backup while the database is being modified using SQLite 
function calls ?

If not, then the data is just a single file.  Assuming all programs using 
SQLite calls closed their connections properly, just copy the file using any 
file copy commands, or file copy primatives in your favourite programming 
language.  In PHP I'd use the built-in copy command:

<https://secure.php.net/manual/en/function.copy.php>

There may be a journal file there and you can copy that too, but just the 
database file is enough for a backup for emergency purposes.

If you're trying to copy a file while connections still have it open then you 
should use SQLite API calls to do it.  The obvious ones are in the SQLite 
Online Backup API, which is the set of calls underlying the '.backup' command 
you mentioned.  You can find documentation for this here:

<https://www.sqlite.org/backup.html>

Unfortunately I don't think the PHP sqlite3 tools give access to this API.

Hope that helps.  Don't hesitate to get back to us if we can help.
___
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] Backing up a SQLite database without the CLI

[Jonathan Moules]

Hi List,
I was wondering if there was a good way of backing up an SQLite database 
if you do *not* have access to the SQLite command line tool (which I 
know has .backup - https://stackoverflow.com/a/25684912).


The new VACUUM INTO (https://www.sqlite.org/lang_vacuum.html#vacuuminto) 
is not an option either because I'm using this via PHP on a remote host 
I have no control over, and its version of SQLite will definitely be 
older than 3.27.0.


I've considered simply running "PRAGMA wal_checkpointer;" and then 
copying the file immediately after that, but that still seems prone to 
error.


Any suggestions?

Thanks,
Jonathan


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

Re: [sqlite] Foreign Key constraint fails due to unrelated field

[Jonathan Moules]

Thanks Clemens, that was it (the comma). That was a mildly embarrassing 
oversight.


Thanks again,
Jonathan


On 2019-02-25 12:52, Clemens Ladisch wrote:

Jonathan Moules wrote:

UPDATE lookups set error_code=3 and exp_content_type='ogc_except' WHERE 
content_hash = '0027f2c9b80002a6';

This fails because "3 and exp_content_type='ogc_except'" is interpreted as
a boolean expression.

To update multiple fields, separate them with commas:

   UPDATE lookups set error_code=3, exp_content_type='ogc_except' WHERE ...
  ^


UPDATE lookups set error_code=3 and WHERE content_hash = '0027f2c9b80002a6';

This is not valid SQL ("and WHERE").


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

[sqlite] Foreign Key constraint fails due to unrelated field

[Jonathan Moules]

Hi List,
I'm seeing some oddness with Foreign Keys and was wondering what was 
going on.


A few days ago I did a refactor of my error codes, changing the numbers 
to be more logically consistent with groupings. They're in a separate 
table table which is referenced from a lookups table.
This was done using "ON UPDATE CASCADE," on the error_code field in 
lookups table - this was temporary and has since been removed. I then 
changed the error codes in the error table and SQLite automagically 
updated the lookups table. Brilliant.

But today I'm seeing oddness.

I start by running:
    pragma foreign_key_check;
    pragma integrity_check;

Both return fine.

The below sample data replicates it for me.
My error table schema (with a subset of data) is:

CREATE TABLE error_codes (
    error_code INTEGER PRIMARY KEY
   UNIQUE,
    error  TEXT
);

INSERT INTO error_codes (error_code, error) VALUES (0, 'No Error');
INSERT INTO error_codes (error_code, error) VALUES (3, 'badness');
INSERT INTO error_codes (error_code, error) VALUES (60, 'more badness');

etc.

Referencing this I have a table called lookups (shortened here):

CREATE TABLE lookups (
    lookup_id  INTEGER  PRIMARY KEY AUTOINCREMENT,
    content_hash   TEXT COLLATE NOCASE,
    error_code INTEGER  REFERENCES error_codes (error_code),
    exp_content_type   TEXT COLLATE NOCASE
);
CREATE INDEX idx__content_hash ON lookups (
    content_hash
);

INSERT INTO lookups (lookup_id, content_hash, error_code, 
exp_content_type) VALUES (661228, '0027f2c9b80002a6', 0, 'ogc_except');


(there are 4 million rows in the actual data)

So I try and do an update on my data:
    UPDATE lookups set error_code=3 and exp_content_type='ogc_except' 
WHERE content_hash = '0027f2c9b80002a6';


and it fails with:
"FOREIGN KEY constraint failed"

So here's where it gets weird, the following work:
-- error_code of 0
UPDATE lookups set error_code=0 and exp_content_type='ogc_except' WHERE 
content_hash = '0027f2c9b80002a6';


-- setting the exp_content_type to something else
UPDATE lookups set error_code=3 and exp_content_type='SOMETHING_ELSE' 
WHERE content_hash = '0027f2c9b80002a6';


-- Removing the exp_content_type field:
UPDATE lookups set error_code=3 and WHERE content_hash = '0027f2c9b80002a6';

For bonus oddness, the update script is actually meant to be running in 
Python, which did run it successfully (even though I'm sure FK 
constraints remain enabled), and yet when I select the row, it shows an 
error_code of 1! Despite the fact the UPDATE SQL being run is very 
definitely "set error_code = 60".

Note that error_code 1 USED to be what is now error_code 60!


TL;DR:
* For some reason the insertion value of an unrelated row seems to be 
affecting referential integrity.
* For some reason when updating to error_code = 60 via Python (and I've 
confirmed the SQL being run does have this) it actually gets updated to 
error_code = 1 (what the code used to be).


Any thoughts what's going on here? I think either SQLite has its wires 
crossed or maybe I'm missing something (probably simple).


SQLite 3.24.0

Thanks,
Jonathan


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

Re: [sqlite] Optimisation opportunity on subquery?

[Jonathan Moules]

Gah, sorry. Another typo. I really should be more awake when I post to 
this list. The non-simplified code does have the item_id on the subquery 
(otherwise it simply wouldn't execute at all of course). So:


SELECT *
  FROM item_info
  JOIN (
select
  count(1) as num,
  item_id
from users
group by item_id)
 USING (item_id)
 where item_id = ?;



On 2019-01-02 22:56, Keith Medcalf wrote:

When you are executing the query:

SELECT *
   FROM item_info
   JOIN (select count(1)
   from users
   group by item_id)
  USING (item_id)
  where item_id = ?;

You are telling the SQL Database Engine (whatever it may happen to be, in this 
case SQLite3) that you want to take the table produced by:

select * from item_info;

and join it (an equijoin) against the table produced by running the query:

select count(1) from users group by item_id;

based on the equality of the column item_id in both tables.  If you run the two 
queries you will see that the second table DOES NOT produce a column called 
item_id.  Therefore you cannot join those tables and instead you get an error 
message telling you that the column item_id does not exist in both tables.

FROM tables are siblings not correlates.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules
Sent: Wednesday, 2 January, 2019 15:21
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Optimisation opportunity on subquery?

Hi Simon,

Thanks for that - I'm always trying to improve my SQL. I think I see
what you mean now.

Assuming my original query:

     SELECT
         *
     FROM
         item_info
     JOIN (select count(1) from users group by item_id)
     USING (item_id)
     where item_id = ?;

There are three uses of "item_id".

* The first is the "group by", which unless my SQL is even worse than
I
imagine, can only be referencing the users table.

* The second is "USING" - which is referencing both.

* The third is the one I guess you mean is ambiguous? My thinking was
that because item_id is going through the USING it wasn't ambiguous
as
they're the same thing; though that's apparently not how the query
planner sees it, and hence your reference to ambiguity. That right?

So I tried using aliases (I'm assuming that removes the ambiguity),
but
the query times remained at about 0.5s for both versions (whether
i.item_id or u.item_id):

     SELECT
         *
     FROM
         item_info i
     JOIN (select count(1) from users group by item_id) u
     USING (item_id)
     where u.item_id = ?;

Thanks again for clarifying, but after checking, it doesn't seem like
it
was an ambiguity thing.
Cheers,
Jonathan

On 2019-01-02 22:04, Simon Slavin wrote:

On 2 Jan 2019, at 9:50pm, Jonathan Moules 
li...@lightpear.com> wrote:

Sorry, but which column is ambiguous? The users.item_id is a

foreign key to the item_info.item_id - that's why it's a "REFERENCES"
- why would I want to change it to be something else? Isn't the
convention for FK's to have the same name across tables? That's what
"USING" is for right? (or NATURAL, but I prefer to be explicit.)
Happy to be corrected.

It may be that our careers developed with different ideas about how

to use SQL.  You had a JOIN, both tables had a column "item_id", and
a reference inside the JOIN to "item_id" would be ambiguous.  Since
the SQL standard does not make it clear which table would be used, it
would be possible for different implementations of SQL to think you
meant different tables.

I understand that, in your example, the values would be the same.

But that doesn't explain to you what the optimizer thinks you're
trying to do.  The simplest way to tell the optimizer what you need
would be to rename one of the columns.  You could try both tables,
see which solution was faster, and use that one.

However, I see other posters have gained better clarity for your

problem.

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-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] Optimisation opportunity on subquery?

[Jonathan Moules]

Hi Simon,

Thanks for that - I'm always trying to improve my SQL. I think I see 
what you mean now.


Assuming my original query:

    SELECT
        *
    FROM
        item_info
    JOIN (select count(1) from users group by item_id)
    USING (item_id)
    where item_id = ?;

There are three uses of "item_id".

* The first is the "group by", which unless my SQL is even worse than I 
imagine, can only be referencing the users table.


* The second is "USING" - which is referencing both.

* The third is the one I guess you mean is ambiguous? My thinking was 
that because item_id is going through the USING it wasn't ambiguous as 
they're the same thing; though that's apparently not how the query 
planner sees it, and hence your reference to ambiguity. That right?


So I tried using aliases (I'm assuming that removes the ambiguity), but 
the query times remained at about 0.5s for both versions (whether 
i.item_id or u.item_id):


    SELECT
        *
    FROM
        item_info i
    JOIN (select count(1) from users group by item_id) u
    USING (item_id)
    where u.item_id = ?;

Thanks again for clarifying, but after checking, it doesn't seem like it 
was an ambiguity thing.

Cheers,
Jonathan

On 2019-01-02 22:04, Simon Slavin wrote:

On 2 Jan 2019, at 9:50pm, Jonathan Moules  wrote:


Sorry, but which column is ambiguous? The users.item_id is a foreign key to the item_info.item_id - 
that's why it's a "REFERENCES" - why would I want to change it to be something else? 
Isn't the convention for FK's to have the same name across tables? That's what "USING" is 
for right? (or NATURAL, but I prefer to be explicit.) Happy to be corrected.

It may be that our careers developed with different ideas about how to use SQL.  You had a JOIN, 
both tables had a column "item_id", and a reference inside the JOIN to 
"item_id" would be ambiguous.  Since the SQL standard does not make it clear which table 
would be used, it would be possible for different implementations of SQL to think you meant 
different tables.

I understand that, in your example, the values would be the same.  But that 
doesn't explain to you what the optimizer thinks you're trying to do.  The 
simplest way to tell the optimizer what you need would be to rename one of the 
columns.  You could try both tables, see which solution was faster, and use 
that one.

However, I see other posters have gained better clarity for your problem.

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] Optimisation opportunity on subquery?

[Jonathan Moules]

Sorry, but which column is ambiguous? The users.item_id is a foreign key 
to the item_info.item_id - that's why it's a "REFERENCES" - why would I 
want to change it to be something else? Isn't the convention for FK's to 
have the same name across tables? That's what "USING" is for right? (or 
NATURAL, but I prefer to be explicit.) Happy to be corrected.



I fixed the typo (a stray comma!), and of course, the REFERENCES table 
has to be second, not first. Sorry, should have checked


CREATE TABLE item_info (

item_id TEXT PRIMARY KEY ON CONFLICT IGNORE

NOT NULL

COLLATE NOCASE,

more_data TEXT

);


CREATE TABLE users (

item_id TEXT REFERENCES item_info (item_id)

NOT NULL

COLLATE NOCASE,

some_data TEXT

);


CREATE INDEX users__item_id__idx ON users (

item_id

);


---


In the end to get the desired result I had to invert the query by 
keeping the WHERE clause inside - this one gets the full speed without 
needing the WHERE clause twice:


SELECT
*
FROM
item_info
JOIN (select count(1) from users where item_id = ?)
USING (item_id);


Anyway, just an observation.


Thanks,
Jonathan


On 2019-01-02 20:33, Simon Slavin wrote:

On 2 Jan 2019, at 4:44pm, Jonathan Moules  wrote:


 SELECT
 *
 FROM
 item_info
 JOIN (select count(1) from users group by item_id)
 USING (item_id)
 where item_id = ?;

You have an ambiguous column name, and I don't think SQLite is doing what you 
think it's doing.

Please change the name of the column users.item_id to something else, then try 
your SELECT again.

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] Optimisation opportunity on subquery?

[Jonathan Moules]

Hi List,

The below seems to my very-non-expert mind like there's scope for 
query-plan optimisation.


I have two tables (simplified below):

CREATE TABLE users (
    item_id   TEXT REFERENCES item_info (item_id)
  NOT NULL
  COLLATE NOCASE,
    some_data TEXT,
);

CREATE INDEX users__item_id__idx ON users (
    item_id
);

CREATE TABLE item_info (
    item_id   TEXT PRIMARY KEY ON CONFLICT IGNORE
  NOT NULL
  COLLATE NOCASE,
    more_data     TEXT
);

There are about 1 million records in users and 100,000 records in item_info.

These queries are all fast, taking about 0.002s:
    select * from item_info where item_id = ?;

    select count(1) from users group by item_id;

    select count(1) from users where item_id = ?;

But when I try and join them together, they're much slower at about 0.5s.
    SELECT
        *
    FROM
        item_info
    JOIN (select count(1) from users group by item_id)
    USING (item_id)
    where item_id = ?;

I kind of expected SQLite would figure out that the outer WHERE clause 
also applies to the subquery given the combination of USING and GROUP BY 
means it has to apply anyway.


If I explicitly include the WHERE inside the subquery, the EXPLAIN QUERY 
PLAN is identical, but it's back to the expected fast speed (0.002s):

    SELECT
        *
    FROM
        item_info
    JOIN (select count(1) from users WHERE item_id = ?)
    USING (item_id)
    where item_id = ?;

sqlite 3.24.0

Cheers,

Jonathan



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

Re: [sqlite] Regarding CoC

[Jonathan Moules]

The one I usually see as being referred to as being "political" is the 
Contributor Covenant - 
https://www.contributor-covenant.org/version/1/4/code-of-conduct


From reading it, while it does have some specifics, it has all the 
exact same problems you're highlighting "Don't be evil" has. Why? 
Because it includes huge loopholes which are extremely subjective and 
based on whoever is interpreting the rules:


"Other conduct which could reasonably be considered inappropriate in a 
professional setting"


"Project maintainers are responsible for clarifying the standards of 
acceptable behavior[sic]"


And more potential retroactive changing of the rules: "Representation of 
a project may be further defined and clarified by project maintainers."


There's also this gem: "The use of sexualized language or imagery and 
unwelcome sexual attention or advances" - so by implication sexual 
attention/advances are fine as long as they're welcome?


While the SQLite CoC definitely fails at the religious inclusiveness 
component, as far as I can see it's better in most other ways. It's 
certainly more specific, there are no giant loopholes, it doesn't stop 
at "unwanted" advances ("chastity" is one of the rules), and with the 
ethos heading at the top, it's clear that it's only really interested in 
keeping things positive rather than going on witchhunts. I still prefer 
"be excellent", but SQLite could do worse, and I say all that as 
egalitarian atheist.



On 2018-10-24 16:17, Jens Alfke wrote:



On Oct 22, 2018, at 10:04 PM, Paul  wrote:

If my opinion has any value, even though being atheist, I prefer this CoC 100 
times over
the CoC that is being currently pushed onto the many open-source communities, 
that was
created by some purple-headed feminist with political motives.

As a purple-headed feminist (yes, literally; got it dyed last week, though the 
color is subtle) I am rolling my eyes at this.
I haven’t see any CoC with political motives being “pushed” to open-source 
communities. The ones I’ve seen basically boil down to Be Excellent Unto One 
Another, similarly to SQLite’s. The difference is that they go into 
_specifics_. And why do they do that? Because of many incidents of 
harassment/discrimination against people of specific minority [in the geek 
community] groups.

Vague blanket statements like “Don’t be evil” or “Be excellent to each other” 
don’t work (here or anywhere else.) *Everyone* believes they’re good, 
*everyone* believes they’re doing good, everyone believes that when they get 
snarky or take action against someone, that it’s because the *other person* 
deserved it, or maybe that it was just in fun and the other person shouldn’t be 
so sensitive. Even the [insert name of horrible group that committed 
atrocities] felt that way.

Since DRH got this CoC from a Christian monastic order, allow me to give an 
example: another order, the Dominicans, instigated and led some rather horrific 
acts of mass torture, murder and ethnic cleansing over the centuries (e.g. the 
Spanish Inquisition.) I’m sure that Savonarola felt himself to be a good person 
who was doing the right thing. (Of course, the same goes for horrifically evil 
people who were devout followers of other religions, and of course atheists. 
Only Disney villains actually believe they’re evil.)

Being specific is important. If you think it’s some kind of crazy political 
extremism to prohibit harassment based on race, religion, gender or sexual 
orientation, I can’t help you there, but just try to keep in mind that the 
majority of people do think so and have asked that you not do it. At least they 
have on other sites; I can’t tell about this one, because the original author 
of the CoC certainly felt it was OK, and I don’t know what DRH’s motives were 
for reproducing his words verbatim.

—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] Regarding CoC

[Jonathan Moules]

I think the big problem with this CoC is that it triggers Poe's Law - 
it's impossible to tell if it's serious or a joke without further 
context. I know I spent a good 10 minutes trying to decide either way 
when I first saw this thread a few days ago; now I know from the below 
post that it's serious.


The consequence of this is that a good chunk of the criticism out there 
is because people think it's a joke and treat it accordingly. Some more 
clarification in the opening paragraph on the reasoning behind it and 
it's non-jokey nature - as below - would probably ameliorate this 
component of the CoC's contentiousness.



On 2018-10-22 16:29, Richard Hipp wrote:

On 10/22/18, Chris Brody  wrote:

Looks like that happened this morning.
https://news.ycombinator.com/item?id=18273530

I saw it coming, tried to warn you guys in private.

There is indeed a reactionary hate mob forming on twitter.  But most
of the thoughtful commentators have been supportive, even if they
disagree with the particulars of our CoC, They total get that we are
not being exclusive, but rather setting a standard of behavior for
participation in the SQLite community.

I have tried to make that point clear in the preface to the CoC, that
we have no intention of enforcing any particular religious system on
anybody, and that everyone is welcomed to participate in the community
regardless of ones religious proclivities.  The only requirement is
that while participating in the SQLite community, your behavior not be
in direct conflict with time-tested and centuries-old Christian
ethics.  Nobody has to adhere to a particular creed.  Merely
demonstrate professional behavior and all is well.

Many detractors appear to have not read the preface, or if they read
it, they did not understand it.  This might be because I have not
explained it well.  The preface has been revised, months ago, to
address prior criticism from the twitter crowd.  I think the current
preface is definitely an improvement over what was up at first.  But,
there might be ways of improving it further.  Thoughtful suggestions
are welcomed.

So the question then arises:  If strict adherence to the Rule of St.
Benedict is not required, why even have a CoC?

Several reasons:  First, "professional behavior" is ill-defined.  What
is professional to some might be unprofessional to others.  The Rule
attempts to clarify what "professional behavior" means.  When I was
first trying to figure out what CoC to use (under pressure from
clients) I also considered secular sources, such as Benjamin
Franklin's 13 virtues (http://www.thirteenvirtues.com/) but ended up
going with the Instruments of Good Works from St. Benedict's Rule as
it provide more examples.

Secondly, I view a CoC not so much as a legal code as a statement of
the values of the core developers.  All current committers to SQLite
approved the CoC before I published it.  A single dissent would have
been sufficient for me to change course.  Taking down the current CoC
would not change our values, it would merely obscure them.  Isn't it
better to be open and honest about who we are?

Thirdly, having a written CoC is increasingly a business requirement.
(I published the currrent CoC after two separate business requested
copies of our company CoC.  They did not say this was a precondition
for doing business with them, but there was that implication.) There
has been an implicit code of conduct for SQLite from the beginning,
and almost everybody has gotten along with it just fine.  Once or
twice I have had to privately reprove offenders, but those are rare
exceptions.  Publishing the current CoC back in February is merely
making explicit what has been implicit from the beginning.  Nothing
has really changed.  I did not draw attention to the CoC back in
February because all I really needed then was a hyperlink to send to
those who were specifically curious.

So then, why not use a more modern CoC?  I looked at that too, but
found the so-called "modern" CoCs to be vapid.  They are trendy
feel-good statements that do not really get to the heart of the matter
in the way the the ancient Rule does.  By way of analogy, I view
modern CoCs as being like pop music - selling millions of copies today
and completely forgotten next year.  I prefer something more enduring,
like Mozart.

One final reason for publishing the current CoC is as a preemptive
move, to prevent some future customer from imposing on us one of those
modern CoCs that I so dislike.

In summary: The values expressed by the current CoC have been
unchanged for decades and will not be changing as we move forward.  If
some people are uncomfortable with those values, then I am very sorry
for them, but that does not change the fact.  On the other hand, I am
open to suggestions on how to express those values in a way that
modern twitter-ites can better understand, so do not hesitate to speak
up if you have a plan.



___
sqlite-users 

Re: [sqlite] geopoly data input options

[Jonathan Moules]

More specifically, in the "Simple Features for SQL" specification:

http://www.opengeospatial.org/standards/sfs

and if you have access (or gobs of money), there's the ISO spec (I'm 
guessing it's the same) - 
https://webstore.ansi.org/RecordDetail.aspx?sku=ISO+19125-1%3A2004


I'd also suggest PostGIS (a PostGreSQL extension for spatial) given 
they're open-source you can take a gander at their code to get a feel 
for it.


PostGIS also has a superset called "EWKB", though the docs are fairly 
poor on it (I'm not sure what the "E" is for - Enhanced"?) - "PostGIS 
EWKB/EWKT add 3dm,3dz,4d coordinates support and embedded SRID 
information" - probably beyond the scope of what you want in geopoly at 
this point.


Cheers,

Jonathan


On 2018-10-19 21:56, Noel Frankinet wrote:

There a WKB and WKT (text) representation).
You can probably find everything : http://www.opengeospatial.org
Spatialite is also a good source
.


On Fri, 19 Oct 2018 at 22:47, Richard Hipp  wrote:


On 10/19/18, Thomas Kurz  wrote:

Beginning with the next release, polygons will always be stored in the
binary format.

Is the SQLite binary encoding identical to the Well-Known-Binary geometry
format?

That might have happened, except the WKB format was not known to me...
Where can I find information about WKB?

--
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] Glob documentation clarity

[Jonathan Moules]

I'm looking at https://sqlite.org/lang_corefunc.html#glob - and glob() 
seemed like it might help me with a problem.


But the docs don't actually say what X and Y are. Which is the needle 
and which is the haystack? It does say "The glob(X,Y) function is 
equivalent to the expression "Y GLOB X"." - but having never used GLOB 
before in SQL, that's no help.


Going on to the GLOB language page 
(https://sqlite.org/lang_expr.html#glob) doesn't provide any clarity either.


Could I suggest explicitly specifying which is the needle and which is 
the haystack as is done for "instr", "substr" etc.


Thanks,

Jonathan

p.s. (Apparently X is the needle and Y is the haystack, but I had to get 
that from a source external to the docs)



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

Re: [sqlite] Attachments and Views - suggested tweak

[Jonathan Moules]

Hi David,

Thanks for the recovery tip. Fortunately this was simple to recover from 
by re-attaching as the "approved" name and then dropping the offending 
view using a standard "DROP VIEW a_view;" query.



On 2018-09-25 17:25, David Raymond wrote:

I think I'd be inclined to have it give the error first rather than rename it. 
But that's just me.

If you need to recover, you can do it with pragma writable_schema to delete the 
line from sqlite_master.



D:\Temp>sqlite3
SQLite version 3.25.1 2018-09-18 20:20:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> attach database 'DeleteMe.sqlite' as att;

sqlite> create table att.a_table (id integer primary key, foo text);

sqlite> insert into a_table values (1, 'In attached table');

sqlite> create view att.a_view as select * from att.a_table;

sqlite> select * from a_view;
id|foo
1|In attached table

sqlite> .exit

D:\Temp>sqlite3 DeleteMe.sqlite
SQLite version 3.25.1 2018-09-18 20:20:44
Enter ".help" for usage hints.

sqlite> .tables
Error: malformed database schema (a_view) - view a_view cannot reference 
objects in database att

sqlite> select * from sqlite_master;
Error: malformed database schema (a_view) - view a_view cannot reference 
objects in database att

sqlite> pragma writable_schema = on;

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|a_table|a_table|2|CREATE TABLE a_table (id integer primary key, foo text)
view|a_view|a_view|0|CREATE VIEW a_view as select * from att.a_table

sqlite> drop view a_view;
Error: no such view: a_view

sqlite> delete from sqlite_master where name = 'a_view';

sqlite> pragma writable_schema = off;

sqlite> .tables
a_table

sqlite>



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jonathan Moules
Sent: Tuesday, September 25, 2018 11:55 AM
To: SQLite mailing list
Subject: [sqlite] Attachments and Views - suggested tweak

Hi List,
      I just caught myself in a little circle and accidentally created a
semi-invalid database. I was wondering if it's something SQLite's query
parser could self-resolve / alert about.

Attach a database as "my_attachment" schema name, and then run:

CREATE VIEW my_attachment.a_view AS
      SELECT * FROM my_attachment.a_table;

If you now disconnect from the database and then re-attach it, this time
attaching it as "hello_world" schema name, SQLite will fail to attach it
and give this error:
      malformed database schema (a_view) - view a_view cannot reference
objects in database my_attachment

I understand that I've been "over-specific" in my SQL, but could not the
query-parser strip off the schema name from the select statement in the
view given it's clearly meant to be self-referential. Or alternatively
give a warning/error at creation time if specifying schema names inside
of Views that are not TEMP given they're unnecessary for such views. The
former would self-solve the problem, the later would provide
transparency and stop you accidentally creating invalid schemas that
seem valid at the time.

Cheers,
Jonathan



___
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] Attachments and Views - suggested tweak

[Jonathan Moules]

Hi List,
    I just caught myself in a little circle and accidentally created a 
semi-invalid database. I was wondering if it's something SQLite's query 
parser could self-resolve / alert about.


Attach a database as "my_attachment" schema name, and then run:

CREATE VIEW my_attachment.a_view AS
    SELECT * FROM my_attachment.a_table;

If you now disconnect from the database and then re-attach it, this time 
attaching it as "hello_world" schema name, SQLite will fail to attach it 
and give this error:
    malformed database schema (a_view) - view a_view cannot reference 
objects in database my_attachment


I understand that I've been "over-specific" in my SQL, but could not the 
query-parser strip off the schema name from the select statement in the 
view given it's clearly meant to be self-referential. Or alternatively 
give a warning/error at creation time if specifying schema names inside 
of Views that are not TEMP given they're unnecessary for such views. The 
former would self-solve the problem, the later would provide 
transparency and stop you accidentally creating invalid schemas that 
seem valid at the time.


Cheers,
Jonathan



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

[sqlite] SELECT becomes very slow when converted to UPDATE

[Jonathan Moules]

Hi List,
I'm trying to find all hashes that are unique to a specific id (my_id), 
and then use a UPDATE-Join to update another table with that number.


After much tweaking, I've simplified the table down to a basic temp 
table (actually created using a CREATE AS SELECT ... GROUP BY my_id, hash):


CREATE TEMP TABLE t_distinct_hashes_by_id (
    my_id            INTEGER,
    hash            TEXT
);

And indexes in both directions because I'm still trying to optimise (and 
see what SQLite wants):


CREATE UNIQUE INDEX temp.idx__1 ON t_distinct_hashes_by_id (
    hash,
 my_id
);

CREATE UNIQUE INDEX temp.idx__2 ON t_distinct_hashes_by_id (
    my_id,
    hash
);

There are only 20 values for my_id, but several hundred thousand hashes.

-

I can do a SELECT query which gets what I want and runs in about 0.5 
seconds:


SELECT
   *
        FROM
            temp.t_distinct_hashes_by_id d
        WHERE
            hash NOT IN (SELECT hash FROM temp.t_distinct_hashes_by_id 
sub where 1 != sub.my_id and hash not NULL)

            AND
            1 = d.my_id

The EXPLAIN:
0    0    0    SEARCH TABLE t_distinct_hashes_by_id AS l USING COVERING 
INDEX idx__2 (my_id=?)

0    0    0    EXECUTE LIST SUBQUERY 1
1    0    0    SCAN TABLE t_distinct_hashes_by_id AS sub

-
So in theory, I'd hope that an UPDATE version using this select should 
take around 20 * 0.5 sec = 10 seconds. But it's actually taking... well 
I don't know how long, at least 10 minutes before I gave up waiting. 
This is the UPDATE:


UPDATE
    meta_table
SET
    distinct_hashes = (
        SELECT
            COUNT(hash) AS num
        FROM
            temp.t_distinct_hashes_by_id d
        WHERE
            hash NOT IN (SELECT hash FROM temp.t_distinct_hashes_by_id 
sub where meta_table.my_id != sub.my_id and hash not NULL)

            AND
            -- This one has to be at the bottom for some reason.
            meta_table.my_id = d.my_id
    )
;

The query plan for this UPDATE includes two CORRELATED Subqueries, which 
the docs say are reassessed on every run - that seems like the problem. 
I get that it'd need to do that 20 times (once per my_id), but the 
slowdown seems considerably longer than that needs to warrant.


0    0    0    SCAN TABLE meta_table
0    0    0    EXECUTE CORRELATED SCALAR SUBQUERY 0
0    0    0    SEARCH TABLE t_distinct_hashes_by_id AS l USING COVERING 
INDEX idx__2 (my_id=?)

0    0    0    EXECUTE CORRELATED LIST SUBQUERY 1
1    0    0    SCAN TABLE t_distinct_hashes_by_id AS sub

Does anyone have any thoughts on how I can speed this up (SQLite 3.15.0 
and confirmed in 3.24.0 (which uses about 3 times the disk IO / sec for 
the same query/data))?

Thanks,
Jonathan



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

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

[Jonathan Moules]

Hi Simon,
Yep, I too find the Query Plan's easier to read (sometimes I even think 
I understand bits of them!)


I do expect SQLite to get slower with an Order By - it has more work to 
do after all, but I thought I'd ask for this one because it's slowing 
down by almost two orders of magnitude (a bit less in 3.23.0) when I add 
the ORDER which seems quite a lot for ordering 86 items. I have no idea 
what the EXPLAIN /says/, but I can see it's very different between the 
two of them so this suggested the QP might be going astray.


Even if I delete all the indexes it's using, it still evidences, though 
the timings are higher (0.1s without, 0.2s with ORDER BY/LIMIT).

Cheers,
Jonathan

On 2018-03-22 22:13, Simon Slavin wrote:

On 22 Mar 2018, at 10:09pm, Jonathan Moules <jonathan-li...@lightpear.com> 
wrote:


Sure; I didn't include them because the only difference is the last line, and that just 
seems to be the standard "ordering" line. I figured the explain was more useful 
as a lot has changed in that.

I find EXPLAIN QUERY PLANs easier to read (probably unlike the development team 
who understand things at the level of EXPLAIN).

The situation as you describe it in the above post is that your query gets 
/slower/ when you add an ORDER BY clause.  This is not expected, and does not 
suggest anything wrong with SQLite.

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] How to get ORDER BY / LIMIT to stick to the fast-path?

[Jonathan Moules]

On 2018-03-22 22:08, Richard Hipp wrote:

Quick workaround: put a "+" on front of the first term of your ORDER BY
clause.


This gives me an ending of:
ORDER BY

+u.url_id ASC

LIMIT 1;


Alas it makes no difference to the speed. The sole difference in the 
EXPLAIN plan when that's added from the ORDER BY/LIMIT one posted in the 
question is this line:


59Copy2523000

Where the number 25 becomes the number 19.
(Note: The EXPLAINs are from 3.15)

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

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

[Jonathan Moules]

Hi Simon,
Sure; I didn't include them because the only difference is the last 
line, and that just seems to be the standard "ordering" line. I figured 
the explain was more useful as a lot has changed in that.


Cheers,
Jonathan

Fast version:
100SEARCH TABLE lookups USING COVERING INDEX sgdsfweer 
(url_id=?)

100EXECUTE LIST SUBQUERY 2
200SEARCH TABLE urls USING COVERING INDEX 
sqlite_autoindex_urls_1 (url=?)

002SCAN SUBQUERY 1 AS recent
011SCAN TABLE lookups AS l
020SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)


Slow version (Order by and LIMIT):
100SEARCH TABLE lookups USING COVERING INDEX sgdsfweer 
(url_id=?)

100EXECUTE LIST SUBQUERY 2
200SEARCH TABLE urls USING COVERING INDEX 
sqlite_autoindex_urls_1 (url=?)

002SCAN SUBQUERY 1 AS recent
011SCAN TABLE lookups AS l
020SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)
000USE TEMP B-TREE FOR ORDER BY

On 2018-03-22 22:01, Simon Slavin wrote:

On 22 Mar 2018, at 9:24pm, Jonathan Moules <jonathan-li...@lightpear.com> wrote:


But when I stick an "ORDER BY" on the end (either ASC or DESC), the processing 
time shoots up to 0.15s. The EXPLAIN between the two is considerably different so it 
seems the ORDER BY is getting it to use a sub-optimal query plan.

Hi, Jonathan.

Could you post details similar to the ones in that post, but instead of using 
EXPLAIN use EXPLAIN QUERY PLAN ?  This will give results on a scale which is 
easier to understand in the context of your queries and indexes.

<https://www.sqlite.org/eqp.html>

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] How to get ORDER BY / LIMIT to stick to the fast-path?

[Jonathan Moules]

Hi List,

The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 
3.23.0 (preview)) despite looking through hundreds of thousands of 
records in each table, and it returns 86 records in all. This is great!


But when I stick an "ORDER BY" on the end (either ASC or DESC), the 
processing time shoots up to 0.15s. The EXPLAIN between the two is 
considerably different so it seems the ORDER BY is getting it to use a 
sub-optimal query plan.
If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query 
plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN 
are also here (they start changing at item 36).


Any suggestions for what's going on here and how to coerce the planner 
to stick to the fast-path and then do a simple order by on those 86 (or 
however many - it'll always be a low number) results?

(ANALYZE has been run)

Thanks,
Jonathan

SELECT
u.url_id, u.url, l.error_code
FROM
urls u
JOIN
lookups l
USING(url_id)
JOIN (
SELECT
url_id,
MAX(retrieval_datetime) AS retrieval_datetime
FROM lookups
WHERE
url_id IN (
SELECT url_id FROM urls WHERE url = 'example.com'
)
) recent

ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
l.is_generic_flag = 1
AND
l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14 
days', 'start of day')




Fast EXPLAIN:

0Init063000
1Integer351000
2Once035000
3OpenEphemeral22000
4Null02400
5OpenRead5503150k(4,-,,,)02
6Once016000
7OpenEphemeral710k(1,B)00
8OpenRead880k(2,,)02
9String8060example.com00
10SeekGE8156100
11IdxGT8156100
12IdxRowid87000
13MakeRecord718C00
14IdxInsert78000
15Close80000
16Rewind728000
17Column70500
18IsNull527000
19SeekLE5275100
20IdxLT5275100
21Column51800
22CollSeq900(BINARY)00
23AggStep0083max(1)01
24If926000
25Column50200
26Prev520000
27NextIfOpen717000
28Close50000
29AggFinal310max(1)00
30SCopy210000
31SCopy311000
32MakeRecord102800
33NewRowid212000
34Insert281208
35Return10000
36OpenRead190800
37OpenRead070500
38Rewind260000
39Column211400
40Function001413datetime(-1)01
41Le15591351
42Rewind159000
43Column171300
44Ne165813(BINARY)53
45Column131400
46Column211700
47Lt175814(BINARY)53
48Column111800
49SeekRowid0581800
50Column041900
51Column202000
52Eq205419(BINARY)43
53Ne205818(BINARY)53
54Copy1822000
55Column012300
56Column162400
57ResultRow223000
58Next143001
59Next239001
60Close10000
61Close00000
62Halt00000
63Transaction00496001
64TableLock090lookups00
65TableLock070urls00
66String80250now00
67String80260-14 days00
68String80270start of day00
69Function072515datetime(-1)03
70Integer116000
71Goto01000


---
ORDER BY and LIMIT Explain:
0Init077000
1Integer351000
2Once035000
3OpenEphemeral22000
4Null02400
5OpenRead5503150k(4,-,,,)02
6Once016000
7OpenEphemeral710k(1,B)00
8OpenRead880k(2,,)02
9

Re: [sqlite] How to optimise a somewhat-recursive query?

[Jonathan Moules]

On 2018-03-22 12:03, Richard Hipp wrote:

On 3/21/18, Jonathan Moules <jonathan-li...@lightpear.com> wrote:

I've spent the last ~90 minutes trying to build this but to no avail

The query planner enhancements are now available in the pre-release
snapshot on the https://sqlite.org/download.html page.


Well, after a lot of fiddling, I finally got something that seems to work.

One observation that's relevant to you; the suggested:

./configure; make sqlite3.c

results in a make error (I'm running it in a Linux Mint VM):

config.status: executing libtool commands
make: Warning: File 'Makefile' has modification time 2.4 s in the future
make: Nothing to be done for 'sqlite3.c'.
make: warning: Clock skew detected. Your build may be incomplete.

No idea what it's on about - I have no clock issues on the host machine 
and the time appears to be correct in the VM. Got around it in the end 
by running it as two commands.


--

Back to the issue at hand using my modestly populated test database - 
all six variations of the query (Order BY ASC; ORDER BY DESC; No Order 
By or LIMIT; - each of those twice, once with JOINs once with LEFT 
JOINs) take the same amount of time in the 3.23.0 build - 0.33seconds. 
(I used the built in ".timer on").


This is the same speed as three of the queries, and faster than one of 
them in 3.15, BUT this is still several times slower than 3.15 on the 
same database (but in Windows) for two of the queries.


The below query, along with the no ORDER BY / LIMIT variant both take 
just 0.08s to run in 3.15.


SELECT
u.url_id, u.url, l.error_code
FROM
urls u
left JOIN
lookups l
USING(url_id)
left JOIN
(select * from v_most_recent_lookup_per_url where url_id in (
select url_id from urls where url = 
'http://catalogue.beta.data.wa.gov.au/api/3/action/resource_search?limit=100=0=format%3AWFS'

)) recent
   -- By definition url's can) recent
-- This is the important bit
-- Here we use the most recent lookup url_id to link to the 
source_seed_id, so we only find its children

-- Or alternatively itself
ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
-- For JSON-spider at least, Generic's are guaranteed to be 
generic pages.

l.is_generic_flag = 1
AND
-- Must be "or equal to" so we can get the lookup of the very 
base url.

l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14 
days', 'start of day')

ORDER BY
u.url_id aSC
LIMIT 1;


Although the two "fast" queries are much slower if a non-existent url is 
used (goes up to about 0.5s in 3.15 for the same query)


Cheers,
Jonathan

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

Re: [sqlite] How to optimise a somewhat-recursive query?

[Jonathan Moules]

Hi Richard,
I've spent the last ~90 minutes trying to build this but to no avail I'm 
afraid. I'm unable to find a version of nmake for Windows 7 (It seems to 
be a VS thing and that in turn is Windows 8/10 only). Then I tried 
inside a VM of Mint, managed to build it, and whilst I was trying to 
find some sort of sqlite front-end (I know SQLite has a CLI tool, but 
I'm happier with front-ends), the VM crashed (for the second time)!


So I'm afraid self-helping by trying that branch isn't happening. If you 
have the means to readily build one and send it to me off-list, feel 
free to and I'll happily test it. If you can't trust the creator of 
SQLite who can you trust? :-)


---

The good news is that Keith's suggestion of removing the "LEFT" from the 
JOIN fixed the ORDER BY DESC issue. The database and query are the same 
as the one's I provided you, just with a bunch of semi-random data in 
the "urls" table.


---

However, - I've now inserted 100,000 semi-random entries into the 
"lookups" table. If I run the same query again (which is unchanged 
except removing the LEFT's from in front of the JOINs), it's going slow 
again taking about 0.5s.
If I change the ORDER BY to ASC, it's also about 0.5s - so they're 
consistent.


That's with this index added which seems to be its preference from the 
numerous variants I created:


CREATE INDEX url_id_datetime_idx ON lookups (

url_id DESC,

retrieval_datetime

);


The things you're likely interested in though, and they may or may not 
be addressed by your branch:

a) If I remove the ORDER BY and LIMIT, the query takes 15 (fifteen) seconds!

b) And if I add the LEFT back in front of the JOIN's, the ORDER BY ASC 
query is back to being modestly speedy - 0.07s - and with no ORDER BY 
it's the same as well.


c) But with the LEFT JOIN's the query takes about 1.1s for ORDER BY DESC

I can provide another copy of the database with the new data in if you 
wish. Or test the fix if you have a dll you want to send me off list.

Thanks,
Jonathan





On 2018-03-21 17:58, Richard Hipp wrote:

On 3/21/18, Jonathan Moules <jonathan-li...@lightpear.com> wrote:

So, I'm back to being stuck on this.
I have inserted 500,000 random urls (no extra lookups - still just
1000), and now the query (as per the below reply) is back to being
somewhat slow (I'm using sqlite 3.15) at about 0.6s per request.

Do you have the ability to compile SQLite from canonical sources?  If
so, please try again with the tip of the join-strength-reduction
branch (https://www.sqlite.org/src/timeline?r=join-strength-reduction).

To compile on Windows:

(1) Download a tarball or ZIP archive (or SQLite Archive) and unpack it.
(2) Run "nmake /f makefile.msc sqlite3.c"

On unix:

(1) Download and unpack as before
(2) ./configure; make sqlite3.c

The only dependence for the above is having a "tclsh" somewhere on your $PATH.




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

Re: [sqlite] How to optimise a somewhat-recursive query?

[Jonathan Moules]

Hi Richard, Simon
Re: Compiling - I'm afraid I wouldn't really know where to begin. A 
quick google finds 
https://superuser.com/questions/146577/where-do-i-find-nmake-for-windows-7-x64 
- but the answers/links there don't seem to work. I've got to go-out now 
but can take another look later and see if I can find a copy (Microsoft 
(I'm on Windows) never make it easy to find stuff).


Simon - I suspected the ORDER BY thing was wrong but wanted to check 
first rather than simply come out with "SQLite is broken!". This may be 
related to the 3.22 regression I brought up a couple of days ago (and 
why I'm using 3.15) - probably why Dr H is suggesting I try his branch.

I'm executing the query using SQLiteStudio (Or Python).
Thanks,
Jonathan


On 2018-03-21 17:58, Richard Hipp wrote:

On 3/21/18, Jonathan Moules <jonathan-li...@lightpear.com> wrote:

So, I'm back to being stuck on this.
I have inserted 500,000 random urls (no extra lookups - still just
1000), and now the query (as per the below reply) is back to being
somewhat slow (I'm using sqlite 3.15) at about 0.6s per request.

Do you have the ability to compile SQLite from canonical sources?  If
so, please try again with the tip of the join-strength-reduction
branch (https://www.sqlite.org/src/timeline?r=join-strength-reduction).

To compile on Windows:

(1) Download a tarball or ZIP archive (or SQLite Archive) and unpack it.
(2) Run "nmake /f makefile.msc sqlite3.c"

On unix:

(1) Download and unpack as before
(2) ./configure; make sqlite3.c

The only dependence for the above is having a "tclsh" somewhere on your $PATH.




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

Re: [sqlite] How to optimise a somewhat-recursive query?

[Jonathan Moules]

So, I'm back to being stuck on this.
I have inserted 500,000 random urls (no extra lookups - still just 
1000), and now the query (as per the below reply) is back to being 
somewhat slow (I'm using sqlite 3.15) at about 0.6s per request.


After a couple of hours of investigation, it's only slow when there is 
either no ORDER BY, or if I use DESC (which is what I need). If I use 
ORDER BY u.url ASC - it's near instantaneous.


I've tried every possible combination of indexes I can think up, 
including of course with url_id DESC. I've also removed the ORDER BY in 
the view (and set it to DESC as well), but that made absolutely no 
difference.


I'm a little confused as to why I'm seeing this behaviour - my limited 
understanding of the query-planner and reading 
https://sqlite.org/queryplanner.html - suggests that at least when using 
indexes, it'll simply scan an index backwards if that looks like it'll 
help. I appreciate this is a result-set not an index, but in this case 
could not the query planner realise that reading through the results 
backwards would be faster than whatever it's doing?


And for that matter, shouldn't no ORDER BY be at least the same speed as 
ORDER BY u.url_id ASC?


Thoughts welcome; Thanks!
Jonathan


On 2018-03-19 00:24, Jonathan Moules wrote:

Thanks Simon and Quan.
I'm not sure it's the view itself per-se - It takes 0.000s (time too 
small to measure) for just the full View to be run on this dataset.


It turns out the problem is simpler than that and no data changes are 
needed. I did consider Quan Yong Zhai's option and gave it a try, but 
couldn't fathom out the necessary SQL to get what I wanted (it's 
getting late).


Instead prompted by the replies here, I've changed the query very 
slightly to the below which solves the problem:


SELECT
u.url, l.error_code
FROM
urls u
LEFT JOIN
lookups l
USING(url_id)
LEFT JOIN
(select * from v_most_recent_lookup_per_url where url_id in (
select url_id from urls where url = 'example.com'
)) recent
-- By definition url's can) recent
-- This is the important bit
-- Here we use the most recent lookup url_id to link to the 
source_seed_id, so we only find its children

-- Or alternatively itself
ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
-- For JSON-spider at least, Generic's are guaranteed to be 
generic pages.

l.is_generic_flag = 1
AND
-- Must be "or equal to" so we can get the lookup of the very 
base url.

l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-5 days')
ORDER BY
u.url_id DESC
LIMIT 1;


To save readers having to compare manually, the difference is this: I 
turned the "recent" alias item from the View into a subquery (still 
using the view), and then moved the "where url = example.com" part in 
to there.
The query is now literally two orders of magnitude faster, from 0.2s 
to 0.004s. No new indexes or anything, just that change.


Hopefully this will scale to full datasets; if it doesn't I may have 
to consider the other suggestions, but for now this is a 
minimum-effort solution.


I'm not actually sure what SQLite was doing in the previous query to 
make it take so long. , so I imagine there was some hideous recursing 
going on or something.


Scope for optimisation?

Thanks again,
Jonathan



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

[sqlite] Query speed Regression: 3.15 much faster than 3.22 (Was: How to optimise a somewhat-recursive query? )

[Jonathan Moules]

Hi List,
So, I thought I'd solved my little problem, but upon testing it in my 
application it subjectively didn't seem any faster.


I upgraded the SQLite in my IDE to 3.22, and it is confirming my 
suspicions. It turns out that when I run the below in 3.22, it takes 
about 0.150s. But in 3.15 it was taking 0.004s!


The original query also takes 0.15s in 3.22 - so that has been mildly 
optimised (from ~0.2s). My general thinking-aloud notion is that my 
"fix" is getting optimised away in 3.22.


I can provide a small replication database if desired.

Thanks,
Jonathan

On 2018-03-19 00:24, Jonathan Moules wrote:

Thanks Simon and Quan.
I'm not sure it's the view itself per-se - It takes 0.000s (time too 
small to measure) for just the full View to be run on this dataset.


It turns out the problem is simpler than that and no data changes are 
needed. I did consider Quan Yong Zhai's option and gave it a try, but 
couldn't fathom out the necessary SQL to get what I wanted (it's 
getting late).


Instead prompted by the replies here, I've changed the query very 
slightly to the below which solves the problem:


SELECT
u.url, l.error_code
FROM
urls u
LEFT JOIN
lookups l
USING(url_id)
LEFT JOIN
(select * from v_most_recent_lookup_per_url where url_id in (
select url_id from urls where url = 'example.com'
)) recent
-- By definition url's can) recent
-- This is the important bit
-- Here we use the most recent lookup url_id to link to the 
source_seed_id, so we only find its children

-- Or alternatively itself
ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
-- For JSON-spider at least, Generic's are guaranteed to be 
generic pages.

l.is_generic_flag = 1
AND
-- Must be "or equal to" so we can get the lookup of the very 
base url.

l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-5 days')
ORDER BY
u.url_id DESC
LIMIT 1;


To save readers having to compare manually, the difference is this: I 
turned the "recent" alias item from the View into a subquery (still 
using the view), and then moved the "where url = example.com" part in 
to there.
The query is now literally two orders of magnitude faster, from 0.2s 
to 0.004s. No new indexes or anything, just that change.


Hopefully this will scale to full datasets; if it doesn't I may have 
to consider the other suggestions, but for now this is a 
minimum-effort solution.


I'm not actually sure what SQLite was doing in the previous query to 
make it take so long. , so I imagine there was some hideous recursing 
going on or something.


Scope for optimisation?

Thanks again,
Jonathan

On 2018-03-18 23:37, Simon Slavin wrote:
On 18 Mar 2018, at 11:13pm, Jonathan Moules 
<jonathan-li...@lightpear.com> wrote:


Given there's such a small amount of data at this point, I suspect 
the issue is more related to the recursion. I've tried creating 
these two indexes to facilicate that

Nice idea but I can see why it's not working.

You have an underlying problem: the format you're using to store your 
data makes it extremely difficult to extract the figures you want.  
Quan Yong Zhai has the best idea I've seen: get rid of almost ¾ of 
the work you're doing by storing the last retrieval date in your 
"urls" table.


As an alternative, store the start your retrieval process by JOINing 
the two tables together.  Consider the result of this query


SELECT url_id, lookups.error_code
 FROM urls
 JOIN lookups ON lookups.url_id = urls.url_id AND 
lookup.retrieval_datetime = urls.retrieval_datetime
 WHERE DATETIME(urls.retrieval_datetime) > DATETIME('now', '-5 
days')


and figure out what you would add to that to get your desired result.

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] How to optimise a somewhat-recursive query?

[Jonathan Moules]

Thanks Simon and Quan.
I'm not sure it's the view itself per-se - It takes 0.000s (time too 
small to measure) for just the full View to be run on this dataset.


It turns out the problem is simpler than that and no data changes are 
needed. I did consider Quan Yong Zhai's option and gave it a try, but 
couldn't fathom out the necessary SQL to get what I wanted (it's getting 
late).


Instead prompted by the replies here, I've changed the query very 
slightly to the below which solves the problem:


SELECT
u.url, l.error_code
FROM
urls u
LEFT JOIN
lookups l
USING(url_id)
LEFT JOIN
(select * from v_most_recent_lookup_per_url where url_id in (
select url_id from urls where url = 'example.com'
)) recent
-- By definition url's can) recent
-- This is the important bit
-- Here we use the most recent lookup url_id to link to the 
source_seed_id, so we only find its children

-- Or alternatively itself
ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
-- For JSON-spider at least, Generic's are guaranteed to be 
generic pages.

l.is_generic_flag = 1
AND
-- Must be "or equal to" so we can get the lookup of the very 
base url.

l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-5 days')
ORDER BY
u.url_id DESC
LIMIT 1;


To save readers having to compare manually, the difference is this: I 
turned the "recent" alias item from the View into a subquery (still 
using the view), and then moved the "where url = example.com" part in to 
there.
The query is now literally two orders of magnitude faster, from 0.2s to 
0.004s. No new indexes or anything, just that change.


Hopefully this will scale to full datasets; if it doesn't I may have to 
consider the other suggestions, but for now this is a minimum-effort 
solution.


I'm not actually sure what SQLite was doing in the previous query to 
make it take so long. , so I imagine there was some hideous recursing 
going on or something.


Scope for optimisation?

Thanks again,
Jonathan

On 2018-03-18 23:37, Simon Slavin wrote:

On 18 Mar 2018, at 11:13pm, Jonathan Moules <jonathan-li...@lightpear.com> 
wrote:


Given there's such a small amount of data at this point, I suspect the issue is 
more related to the recursion. I've tried creating these two indexes to 
facilicate that

Nice idea but I can see why it's not working.

You have an underlying problem: the format you're using to store your data makes it 
extremely difficult to extract the figures you want.  Quan Yong Zhai has the best idea 
I've seen: get rid of almost ¾ of the work you're doing by storing the last retrieval 
date in your "urls" table.

As an alternative, store the start your retrieval process by JOINing the two 
tables together.  Consider the result of this query

SELECT url_id, lookups.error_code
 FROM urls
 JOIN lookups ON lookups.url_id = urls.url_id AND lookup.retrieval_datetime 
= urls.retrieval_datetime
 WHERE DATETIME(urls.retrieval_datetime) > DATETIME('now', '-5 days')

and figure out what you would add to that to get your desired result.

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] How to optimise a somewhat-recursive query?

[Jonathan Moules]

Hi Both,
Thanks for your thoughts.

> SQLite has no DATETIME or BOOLEAN datatype

I'm aware that DATETIME (and BOOLEAN) isn't an actual datatype in 
SQLite, but it functions exactly as you'd expect - I've used them 
extensively in the past and never had a problem - I'm actually storing 
ISO8601 strings and so it's simply a synonym for the TEXT - I find 
having human-readable datetimes infinitely more practical for 
development purposes. It's one of the things I most like about SQLite - 
datetimes are ridiculously easy compared to "proper" databases.


> Drop the ORDER BY; it is useless in a view used in another query,
I figured sqlite would optimise it out if it didn't think it needed it 
(it's needed by some of the other things that use that view). A quick 
test seems to confirm it. It seems that the ORDER BY becomes the "3
Noop54000 " line in the EXPLAIN report - otherwise 
the explain is identical.


> If SQLite thinks that an AUTOMATIC index is useful, you should 
consider creating it explicitly: CREATE INDEX lookup_generid_id_idx ON 
lookups(is_generic_flag, url_id);


It does make the EXPLAIN plan 6 lines shorter, but no apparent speed 
difference unfortunately.


Given there's such a small amount of data at this point, I suspect the 
issue is more related to the recursion. I've tried creating these two 
indexes to facilicate that:

CREATE INDEX url_id_source_id_idx ON urls (

url_id,

source_seed_id

);


CREATE INDEX source_id_url_id_idx ON urls (

url_id,

source_seed_id

);


SQLite will use the former (url_id, then source_seed_id), but it makes 
absolutely no difference to the speed.



So I'm still stumped.



On 2018-03-18 22:30, Tim Streater wrote:

On 18 Mar 2018, at 21:48, Jonathan Moules <jonathan-li...@lightpear.com> wrote:


CREATE TABLE lookups (
  url_id   INTEGER  REFERENCES urls (url_id),
  retrieval_datetime   DATETIME,
  error_code   INTEGER,
  is_generic_flag  BOOLEAN  -- about one in 100 will have this
flagged
);

SQLite has no DATETIME or BOOLEAN datatype, see:

<http://www.sqlite.org/datatype3.html>

Given this, I don't know what your attempts to compare date/times will do. 
Personally I store all date/times as seconds since the epoch; it simplifies 
life a lot.






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

[sqlite] How to optimise a somewhat-recursive query?

[Jonathan Moules]

Hi List,
I have a query that's quite slow on a small sample of data and I'd like 
to optimise it before larger datasets get in there. With just 1000 rows 
in each table I'm currently seeing query times of ~0.2 seconds. My 
attempts to optimise it so far by putting in carefully thought out 
indexes have _increased_ the time to 0.4s! H, so much for "carefully 
thought out". So I've removed all the indexes again and am asking the 
experts here for some guidance.


The reduced table structure is as below. It's for some web-crawling - 
basically each URL will be stored once in the "urls" table. It also 
keeps a reference to what the original source url that ended up coming 
to this url was (that's source_seed_id - it's used in this query), and 
the direct parent (parent_id).
Then any time the application scrapes one of the url's, an entry is 
written into the lookups table.


The idea behind the query is to see whether during the last crawl the 
application got to the last page in the paginated "chain" (it will have 
a specific error code which the application is looking for). This means 
it's somewhat recursive.

SQLite IDE version (where the timings are coming from): 3.15.0
SQlite version behind the application itself (not timed the query here, 
but it's still slow): 3.22.0



CREATE TABLE urls (
url_id INTEGER  PRIMARY KEY AUTOINCREMENT,
urlTEXT UNIQUE,
parent_idINTEGER  REFERENCES urls (url_id)
source_seed_id INTEGER  REFERENCES urls (url_id)
);


CREATE TABLE lookups (
url_id   INTEGER  REFERENCES urls (url_id),
retrieval_datetime   DATETIME,
error_code   INTEGER,
is_generic_flag  BOOLEAN  -- about one in 100 will have this 
flagged

);


-- This view is used as a convenience, including in the problem query below

CREATE VIEW v_most_recent_lookup_per_url AS
SELECT url_id,
   MAX(retrieval_datetime) AS retrieval_datetime
  FROM lookups
 GROUP BY url_id
 ORDER BY url_id;


The query itself:


SELECT
u.url, l.error_code
FROM
urls u
LEFT JOIN
lookups l
USING(url_id)
LEFT JOIN
v_most_recent_lookup_per_url recent
-- This is the important bit
-- Here we use the most recent lookup url_id to link to the 
source_seed_id, so we only find its children

-- Or alternatively itself
ON u.source_seed_id = recent.url_id
OR u.url_id = recent.url_id
WHERE
l.is_generic_flag = 1
AND
-- Must be "or equal to" so we can get the lookup of the very 
base url.

l.retrieval_datetime >= recent.retrieval_datetime
AND
DATETIME(recent.retrieval_datetime) > DATETIME('now', '-5 days')
AND
recent.url_id in (
select url_id from urls where url = 'example.com'
)
-- By definition url's can only be inserted in pagination order, so 
we can order by url_id.

ORDER BY
u.url_id DESC
LIMIT 1;



The current EXPLAIN QUERY PLAN:

100SCAN TABLE lookups
100USE TEMP B-TREE FOR GROUP BY
000SCAN TABLE urls AS u
011SEARCH TABLE lookups AS l USING AUTOMATIC COVERING INDEX 
(is_generic_flag=? AND url_id=?)

022SCAN SUBQUERY 1 AS recent
000EXECUTE LIST SUBQUERY 2
200SEARCH TABLE urls USING COVERING INDEX 
sqlite_autoindex_urls_1 (url=?)



And the EXPLAIN:


0Init0118000
1Integer501000
2Once050000
3Noop54000
4OpenEphemeral22000
5SorterOpen620k(1,B)00
6Integer06000
7Integer05000
8Null09900
9Gosub848000
10OpenRead370300
11Rewind317000
12Column301100
13Column321200
14MakeRecord1121300
15SorterInsert613000
16Next312001
17Close30000
18OpenPseudo713200
19SorterSort650000
20SorterData613700
21Column701000
22Compare9101k(1,B)00
23Jump24282400
24Move109100
25Gosub739000
26IfPos650000
27Gosub848000
28Column711400
29CollSeq1500(BINARY)00
30AggStep00143max(1)01
31If1533000
32Column70200
33Integer15000
34SorterNext620000
35Gosub739000
36Goto050000
37Integer16

[sqlite] Why doesn't SQLite optimise this subselect?

[Jonathan Moules]

Hi All,
  This is more of an academic question as I've come up with a better 
query, but I was wondering why SQLite doesn't optimise this query.


Lets say I have two tables, simplified here. One contains webpage 
contents and a unique hash of those contents (the primary key), the 
other contains a history of lookups. The hash is used as a foreign key 
between the two tables.


Table: webpage_contents
content_hash -- Primary key
post_processing_info
page_content

This table has about 10,000 rows

crawling_lookups
content_hash -- foreign key, has an index
is_json -- a flag indicating if it's JSON
is_html -- a flag indicating if it's HTML

This table has 500,000 rows

(Note: I appreciate this is bad design now and the flags should be in 
webpage content; it evolved organically).


Now, I want to query the content table post_processing_info, and join in 
the flags from the lookups at the same time. After a bit of fiddling 
around to stop duplication happening, I got this:


SELECT
w.post_processing_info,
l.is_json
FROM
webpage_contents w
JOIN
(SELECT * from lookup_tables.lookups GROUP BY content_xxhash) l
USING (content_hash)
WHERE
content_hash = 'abc'


This takes about 2 seconds to run, which is quite slow given 
content_hash is indexed in both tables and it's running from an SSD.


I appreciate there are better ways to write this (and I'm using one now, 
I was a wee bit meandering getting there though), but it I was left 
wondering - why did SQLite not optimise the subquery?


The Explain Query Plan for that is:

100SCAN TABLE crawling_lookups USING INDEX content_hash_idx
000SEARCH TABLE webpage_contents AS c USING INDEX 
sqlite_autoindex_page_contents_1 (content_hash=?)

011SCAN SUBQUERY 1 AS l

It seems to be doing the group on the subselect using the index (fast - 
about 0.1 seconds), then at step 3 doing a scan of the subquery results 
for the content_hash. This would be the slow part as that doesn't have 
an index.
I figured SQL would spot that the where clause in the outermost scope 
would also apply to the subselect and put it in there automatically so 
the group-by would only be for the things the WHERE clause affected.


Is this an optimisation opportunity? Or is my SQL so bad it was 
inevitable (more likely)?


Cheers,
Jonathan

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

Re: [sqlite] Emulate right-join

[Jonathan Moules]

In lieu of adding the syntactic sugar, might it be worth documenting the 
alternative(s)? Currently the docs for these are 
"https://sqlite.org/omitted.html; - which simply says: "LEFT OUTER JOIN is 
implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN."
A couple of lines saying why this isn't really a problem because it can be 
worked around apparently fairly easily could be helpful to us lay-users of SQL.


 On Wed, 06 Dec 2017 18:07:37 + R Smith rsm...@rsweb.co.za 
wrote  


On 2017/12/06 6:35 PM, Christian Schmitz wrote:
 Actually, the left outer join is sufficient to execute all the 
outer
 join operators:
 - right outer join: just swap the "from" arguments

 - full outer joins: union of left and right outer joins
 Couldn’t SQLite implement that and do the swap for us?
 As well as the union thing?

You mean make SQLite less Lite, but with Zero computational advantage, 
by simply adding syntactic sugar bloat? - I'm going to have to vote No 
on that. (Luckily my vote counts extremely little.)

I think the reason SQLite never implemented it is precisely because of 
the fact that it simply amounts to syntactic specialization and no real 
computational advantage. That said, I'm not against adding those joins, 
just perhaps implemented in a most-efficient way rather than a simple 
transcription of my lazy-code. (Unless of course that ends up being the 
most efficient way to do it.)

___
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] "Responsive" website revamp at www.sqlite.org

[Jonathan Moules]

I'm not aware of any log files that record screen size.

Screen size works well at render time using JavaScript (how Bootstrap etc work 
I believe), but for this sort of post-event analysis, user-agent is pretty much 
the only information there is to work with - with the caveat that as Stephen 
Beal noted - it's unreliable.


 On Tue, 06 Sep 2016 13:43:02 +0100 Marco Bambinima...@sqlabs.net 
wrote  

You could just filter by screen size instead of user agent.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs


 On 06 Sep 2016, at 14:26, Richard Hipp d...@sqlite.org wrote:
 
 On 9/6/16, Nelson, Erik - 2 erik.l.nel...@bankofamerica.com wrote:
 
 What percentage of sqlite.org hits are mobile browsers?
 
 How do I tell?
 
 The website saw 2748 distinct UserAgent strings within just the past
 24 hours (a holiday in the USA, FWIW). How do I tell which of those
 are bots, mobile devices, and/or desktops?
 
 -- 
 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] "Responsive" website revamp at www.sqlite.org

[Jonathan Moules]

This page may have the solution:
http://detectmobilebrowsers.com/
There are a bunch of scripts in different languages to detect if the user is on 
a mobile or not using some absurdly complicated RegExps.

I've not tried them myself, but they look like they do the right kind of thing.
 On Tue, 06 Sep 2016 13:26:17 +0100 Richard Hippd...@sqlite.org 
wrote  

On 9/6/16, Nelson, Erik - 2 erik.l.nel...@bankofamerica.com wrote:

 What percentage of sqlite.org hits are mobile browsers?

How do I tell?

The website saw 2748 distinct UserAgent strings within just the past
24 hours (a holiday in the USA, FWIW). How do I tell which of those
are bots, mobile devices, and/or desktops?

-- 
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] Setting temp location with Python

[Jonathan Moules]

Hi List,

To approach this question from another direction:

Why was this Pragma deprecated (temp_store_directory)? Aside from the 
possibility of it disappearing in future builds (or current builds built 
without deprecateds), are there any problems I should anticipate in using it 
for these purposes?

Thanks,
Jonathan



Hi Kevin, 
Yep, Item 5.0 was the one I was looking at, but as best I can tell there 
is no OS-agnostic way of doing it except the PRAGMA. But the pragma has 
been deprecated so I was wondering what the new, post-deprecation, 
OS-agnostic solution. 
 
Cheers, 
Jonathan 
 
On 11/08/2016 15:42, Kevin O'Gorman wrote: 
 On Wed, Aug 10, 2016 at 6:50 AM, Jonathan Moules  
 jonathan-li...@lightpear.com wrote: 
 
 Hi List, 
 I'm using Python's sqlite3 library to access a SQLite db. I'd like to 
 set the location for the temporary databases in a platform agnostic 
fashion 
 (*nix or Windows). 
 
 This page - https://www.sqlite.org/tempfiles.html - gives a number of 
 options, but the only good one I can see for using it via Python would 
be 
 the PRAGMA. 
 But the docs for PRAGMA temp_store_directory; ( 
 https://www.sqlite.org/pragma.html#pragma_temp_store_directory ) say 
this 
 is deprecated. 
 
 So what's the recommended way to achieve this? 
 
 
 Take a look at https://www.sqlite.org/tempfiles.html, specifically 
item 
 5.0. which addresses this question. It varies a bit depending on your 
 environment, so there's no single answer. On my Linux system, I fiddle 
 with the SQLITE_TMPDIR environment variable. 
 
 You might want to describe your environment so that folks with a similar 
 environment can respond more helpfully. 
 
 





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

[sqlite] Setting temp location with Python

[Jonathan Moules]

Hi List,
   I'm using Python's sqlite3 library to access a SQLite db. I'd like to set 
the location for the temporary databases in a platform agnostic fashion (*nix 
or Windows).

This page - https://www.sqlite.org/tempfiles.html - gives a number of options, 
but the only good one I can see for using it via Python would be the PRAGMA.
But the docs for PRAGMA temp_store_directory; ( 
https://www.sqlite.org/pragma.html#pragma_temp_store_directory ) say this is 
deprecated.

So what's the recommended way to achieve this?

Thanks,
Jonathan

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

Re: [sqlite] Messages posted on Nabble not getting to list

[Jonathan Moules]

I think there are two different use cases for a mailing list such as this, and 
they're each better served by different access method; either email or forums.

One use case is the individual with a long-term interest in a 
project/technology. Because of the long-term interest, an email list (which is 
a "push" technology - it comes to you) is ideal - you sign up once, set up mail 
filtering, and then read at your leisure in the client of your choice.

The other use case is people with a one-off question. "Why isn't this query 
working?", "What's the SQLite function for XYZ?" etc. For me at least, whenever 
I have had one of these in the past for a particular technology, signing up to 
a mailing list is a chore. You sign up, wait for the confirmation email, get 
spammed by tons of stuff you're not interested in (you don't plan on being 
subscribed long enough to spend the time setting up filters), then have to 
unsubscribe when you've got the answer, and then confirm the unsubscription.
Then two months later you have another query and have to do the entire process 
again. Personally I loathe going through this process for one-off questions.

In this second use-case, a forum (being "pull" based - you get it when you want 
it) is better. Sign-up, correspond, then forget about it. If there's another 
question a few months later, you can login again using the old credentials and 
continue where you left off.

Unfortunately I don't think there's much forum/list software out there that 
facilitates *both* use-cases. Google Groups tries to, but I've not encountered 
anything else (not that I've gone looking).
I've seen some lists try and punt this "forum" component off to domain specific 
stackexchange sub-sites, but that has it's own problem (splitting the community 
between venues).
Just my 2p,
Cheers,
Jonathan




 On Fri, 27 May 2016 10:55:30 +0100 Rob Willett 
rob.sql...@robertwillett.com wrote  

I agree with Tim.

I filter all my SQLite messages to its own folder and read as needed.

I prefer this method to a forum as I can then keep the messages with me. 
I’m often off the internet and its good to have them for reading. Also 
I can look back through them and get hints and tips about issues.

Its not perfect but it works for me. I get around the distraction of 
emails coming in through client filtering, I did start filtering at the 
server so that only important stuff came through immediately but that 
didn’t work well. I now use Mailmate and like the power and lack of 
graphical eye candy it offers.

I’d actively vote against a SQLite forum rather than e-mail (if 
anybody asked me, which I doubt…) SQLite is important to our work so 
having it around is fine by me,

YMMV

Rob


On 27 May 2016, at 10:34, Tim Streater wrote:

 On 27 May 2016 at 08:56, Darren Duncan dar...@darrenduncan.net 
 wrote:

 On 2016-05-26 9:00 PM, Balaji Ramanathan wrote:
 The main advantage of forums, and I follow a bunch of them, is 
that 
 I choose
 when I want to stop my regular day job and be distracted by them 
 rather than
 emails coming in and distracting me all the time.

 That's not an argument for web forums, rather that's an argument for 
 not using
 your work email to subscribe to non-work discussion lists; use a 
 non-work email
 for the discussion lists instead. You can also configure your email 
 client to
 only check email when you tell it to rather than constantly. -- 
 Darren Duncan

 Filter the sqlite mails into their own mailbox. They can then be read 
 at a convenient moment.

 I certainly don't want a whole lot of sub-forums and the like, each of 
 which has to be checked in case there's something interesting there.

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

Re: [sqlite] Searching this mailing lsit

[Jonathan Moules]

Hi Simon,
But then what would the alternative be?
The poster doesn't choose to have the absurd signature; it's an absurd 
corporate policy because their lawyers insist on erring on the side of caution 
and have a relatively spineless interpretation of the law.

I'm not sure it is right for the list to ask (let alone require) people to send 
work-related emails from their personal accounts. Unless you're a politician 
trying to get around various FOI and/or data retention laws, I personally think 
a separation of work and personal emails is healthy.

Of course that's ignoring the fact that many organisations actually block 
"personal" email access from work anyway and/or forbid using them for work 
related purposes.
Cheers,
Jonathan


 On Tue, 24 May 2016 20:23:56 +0100 Simon Slavin 
slav...@bigfraud.org wrote  

 
On 24 May 2016, at 7:46pm, Kasajian, Kenneth 
kenneth.kasaj...@schneider-electric.com wrote: 
 
 It's funny how many messages there are here about the signature of my 
e-mail (which I cannot control) 
 
Then please stop posting to public fora from that account. 
 
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] Podcast with Dr Hipp: SQLite history, success and funding

[Jonathan Moules]

I've not heard of fossil so this thread piqued my interest; I currently use 
Mercurial where I have a choice.
I don't seem to be able to find much about Fossil v's Mercurial. This blog post 
looked interesting though:
http://www.omiyagames.com/farewell-fossil-version-control/

Despite Mercurial being less ... opaque than Git, I guess many of the points 
remain the same for that comparison.


 On Wed, 18 May 2016 16:55:15 +0100 Warren Youngwyml at etr-usa.com 
wrote  

On May 18, 2016, at 4:43 AM, Kees Nuyt k.nuyt at zonnet.nl wrote:
 
 On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
 cldwesterhof at gmail.com wrote:
 
 I would be interested what you find wrong about Git and is better in 
your
 version control system.
 
 Check the archives of the fossil-users mailing list

Links to a few of the wider-ranging Git vs Fossil threads in recent years:

 https://goo.gl/rVzYTx
 https://goo.gl/8xKoZy
 https://goo.gl/RPJLEq
 https://goo.gl/Gq3Cga

One of those threads didn?t start out as ?Fossil vs Git,? but ended up there 
eventually. It?s nearly inevitable when someone brings up Git on the Fossil 
mailing list. :)
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Summing values by date, returning 0 for missing dates

[Jonathan Moules]

Hi Simon,
  Sorry, maybe we're crossing wires, but I'm not sure to what you're referring. 
How is defining the type as DATE impeding my attempt to get a value of 0 for 
non-existent rows?
Ryan's response with a CTE seems to probably be what I want (not had the 
opportunity to test it yet - CTE's are entirely new to me), but looking at it 
quickly now I see that the type is actually NUMERIC there rather than my DATE - 
perhaps that's to what you're referring.

Cheers,
Jonathan

 On Thu, 12 May 2016 18:29:47 +0100 Simon Slavinslavins at 
bigfraud.org wrote  


On 12 May 2016, at 3:55pm, Jonathan Moules jonathan-lists at 
lightpear.com wrote: 

 I know that the "DATE" type isn't actually a type in SQLite and that there 
are no date-specific constraints, but I find it a convenient indicator (to me 
and potentially anyone else who's going to see the code) as to the type of data 
that is to be held in that column. I figured that's why those "type" synonyms 
exist (I use DATETIME as well!) 

What you don't mention there is that defining that column as DATE is what's 
causing the weird-looking results you asked about. 

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

[sqlite] Summing values by date, returning 0 for missing dates

[Jonathan Moules]

Hi Simon,
   I know that the "DATE" type isn't actually a type in SQLite and that there 
are no date-specific constraints, but I find it a convenient indicator (to me 
and potentially anyone else who's going to see the code) as to the type of data 
that is to be held in that column. I figured that's why those "type" synonyms 
exist (I use DATETIME as well!)
Cheers,
Jonathan


 On Wed, 11 May 2016 23:45:44 +0100 Simon Slavinslavins at 
bigfraud.org wrote  


On 11 May 2016, at 11:20pm, Jonathan jonathan-lists at lightpear.com 
wrote: 

 // time_date DATE,// 

SQLite has no DATE type. Put your data in that table and try this command: 

SELECT time_date,typeof(time_date) FROM my_table 

Then try it again, this time defining that column as TEXT. 

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

[sqlite] C API reference manpages

[Jonathan Moules]

How about the CC0 license?
I think it's designed for these sorts of things (you want to make something 
public domain even if you're not allowed to) - 
https://creativecommons.org/about/cc0/

 On Fri, 01 Apr 2016 00:05:30 +0100 Kristaps Dzonsons kristaps at 
bsd.lv wrote  

 As for public domain, I'm happy to put the sources under a similar 
 license. I can't speak for the voodoo of the public domain and the EU 
 (or is it country-by-country?), however. 
 
 From an English translation I found of the Latvian law includes moral 
 rights and is closer to the droit d'auteur than copyright. So it seems 
 difficult to place your work in the public domain. 
 
 I would advise you to contact the FSF, FSFE Legal or SFLC in this matter 
 if you want a general advice without legal fees. This entire topic as 
 been discussed in context of Wikimedia's projects, especially Wikipedia 
 and Wikimedia Commons, and CC0, so you might find information about 
 Latvia there. 

Matthias-Christian, 

I'm afraid I can't say anything useful about licenses, but in searching 
around, it appears that you're correct[1]. 

[sqlite] Outdated section of docs?

[Jonathan Moules]

Hi,
I was reading this page (http://sqlite.org/lang_datefunc.html), and at 
the very bottom it says:
/
/

/"Non-Vista Windows platforms only support one set of DST rules.
Vista only supports two. Therefore, on these platforms, historical
DST calculations will be incorrect. For example, in the US, in 2007
the DST rules changed. Non-Vista Windows platforms apply the new
2007 DST rules to all previous years as well. Vista does somewhat
better getting results correct back to 1986, when the rules were
also changed."/


I wonder if that's a section that was written years ago, and the bits 
that apply to Vista also apply to the Windows releases since then? I 
don't know enough about Windows Timezone things to be able to find out 
easily but this reads like it was written back in the era of Vista and 
probably holds for newer releases too.
Cheers,
Jonathan

[sqlite] Doc page revision request

[Jonathan Moules]

For a slightly broader brushed overview of why the web-filter is wrong (a false 
positive), see: https://en.wikipedia.org/wiki/Scunthorpe_problem

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bernardo 
Sulzbach
Sent: Tuesday, July 21, 2015 4:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Doc page revision request

> The problem seems to be with the web filter and not the abbreviation cnt.

Exactly. Let's not forget to mention that dick is a synonym for detective and 
that bitch is a female dog. "cnt" is fine in that context and the filter should 
likely be deactivated or updated.
Although it may be easier to just ask someone to replace stuff on that page, as 
I said before, "cnt" is used to mean "count" in many places, making it even 
clearer that the filter is the problem.

Good luck asking Oracle to update this:
http://docs.oracle.com/javase/7/docs/api/java/awt/List.html

On Tue, Jul 21, 2015 at 12:46 PM, Paul Sanderson  wrote:
> The problem seems to be with the web filter and not the abbreviation
> cnt. I would suggest that the onus should be on them to adjust their
> filter to prevent filtering of an innocuous word (its only
> rude/offensive if the u is added).
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-To
> olkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 21 July 2015 at 16:34, Jim Callahan  
> wrote:
>> I Simon's point about idiotic web filters is valid.
>>
>> "Cnt" is innocuous in formal documentation where by context it
>> clearly means "count", but think of how people type text messages. If
>> an online chat board in html had text like messages then a machine
>> learning algorithm (for a web filter) would tend to associate "cnt"
>> with sexually explicit and racially offensive language that would not
>> be appropriate for an elementary school aged child.
>>
>> By middle school the student and their friends are probably
>> experimenting with the language
>>
>> Web  filters are sometimes used in corporations, government agencies
>> and public facilities, so I can see why it might be an issue, even though 
>> "cnt"
>> is completely innocuous in formal documentation in a way it would not
>> be in a "how many ... does it take to change light bulb" joke or in a
>> string of offensive expletives.
>>
>> It is a matter of context. And to a crudely programmed machine
>> learning algorithm it is all html text with no context.
>>
>> Jim
>>
>>
>> On Tue, Jul 21, 2015 at 10:52 AM, Bernardo Sulzbach <
>> mafagafogigante at gmail.com> wrote:
>>
>>> About using "cnt", it is by far not just this page. There are tons
>>> of documentation and programming pages out there that use "cnt"
>>> instead of "count".
>>>
>>> The last part of your message seems more valid, though.
>>> ___
>>> 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



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


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/OrCU7GDpjEnGX2PQPOmvUkyXKmclf+CPM8I9WrCzLAIW8IbtuGHEGrarocFh0QsteImqX2ifBx2n9BVBzDB66w==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Mozilla wiki 'avoid SQLite'

[Jonathan Moules]

p.s. The spellchecker just kindly advised that maybe changing the name of 
"andl" might help reduce future unfortunate typos / "corrections". :-)

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jonathan 
Moules
Sent: Tuesday, June 16, 2015 4:33 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

> The question is: what should a database language do? Andl can already match 
> or surpass SQL on database programming tasks, but is that interesting enough?

As much as anything, that depends on what problem you're targeting, and even 
your audience. At the risk of rekindling the High/low/assembly level 
discussion, certainly at the high level, languages generally all have different 
design goals, and because of this they appeal to different people.

This list appears to have a very high proportion of computer science types, so 
we see discussions about recursions and "syntactic sugar" and all that jazz; 
conversely, ask on a list for web-developers and you'll get a very different 
set of answers.

Personally, as someone whose SQL-fu is weak, and who isn't a computer 
scientist, one of the best things about SQL is that it's English-like and 
there's a very low barrier to entry.

Consider this statement, which is about as complex as 90% of my SQL ever gets:

SELECT * from buildings where height > 30 and colour = 'mauve'

With just 2 minutes of explaining I could probably get my mum to understand 
what was going on there. Ok, that's an unrealistically low bar, but many people 
who use SQL just have simple queries/problems. While I appreciate andl doesn't 
have documentation yet, it doesn't look like it will pass the "not a computer 
scientist" test for usability.
Just my 2c.

Cheers,
Jonathan


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
da...@andl.org
Sent: Tuesday, June 16, 2015 12:57 AM
To: ajm at zator.com; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

>>>I think the best database language should reflect how earthlings
>>>think
about the data, and the best computer programming language would reflect easily 
the result we want get from them.

Care to expand on that?

I'm developing a new database language: Andl. My starting point has been the 
relational model, Codd-Date-Darwen and The Third Manifesto.  My (only) 
competitor seems to be SQL, which has a 40+ year lead. Nothing like a challenge!

The question is: what should a database language do? Andl can already match or 
surpass SQL on database programming tasks, but is that interesting enough?

What would make a database programming better, or best?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org




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


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/43VYmEOYO7bGX2PQPOmvUj!GOBh06pKK8EdQhM6i4FvmoG0cFV2y0WTEr3dfsj1m6uKlINFphbL3KeH4!zzvzA==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in 
England No. 02562099


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



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Mozilla wiki 'avoid SQLite'

[Jonathan Moules]

> The question is: what should a database language do? Andl can already match 
> or surpass SQL on database programming tasks, but is that interesting enough?

As much as anything, that depends on what problem you're targeting, and even 
your audience. At the risk of rekindling the High/low/assembly level 
discussion, certainly at the high level, languages generally all have different 
design goals, and because of this they appeal to different people.

This list appears to have a very high proportion of computer science types, so 
we see discussions about recursions and "syntactic sugar" and all that jazz; 
conversely, ask on a list for web-developers and you'll get a very different 
set of answers.

Personally, as someone whose SQL-fu is weak, and who isn't a computer 
scientist, one of the best things about SQL is that it's English-like and 
there's a very low barrier to entry.

Consider this statement, which is about as complex as 90% of my SQL ever gets:

SELECT * from buildings where height > 30 and colour = 'mauve'

With just 2 minutes of explaining I could probably get my mum to understand 
what was going on there. Ok, that's an unrealistically low bar, but many people 
who use SQL just have simple queries/problems. While I appreciate andl doesn't 
have documentation yet, it doesn't look like it will pass the "not a computer 
scientist" test for usability.
Just my 2c.

Cheers,
Jonathan


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
da...@andl.org
Sent: Tuesday, June 16, 2015 12:57 AM
To: ajm at zator.com; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

>>>I think the best database language should reflect how earthlings
>>>think
about the data, and the best computer programming language would reflect easily 
the result we want get from them.

Care to expand on that?

I'm developing a new database language: Andl. My starting point has been the 
relational model, Codd-Date-Darwen and The Third Manifesto.  My (only) 
competitor seems to be SQL, which has a 40+ year lead. Nothing like a challenge!

The question is: what should a database language do? Andl can already match or 
surpass SQL on database programming tasks, but is that interesting enough?

What would make a database programming better, or best?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org




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


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/43VYmEOYO7bGX2PQPOmvUj!GOBh06pKK8EdQhM6i4FvmoG0cFV2y0WTEr3dfsj1m6uKlINFphbL3KeH4!zzvzA==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Docs suggestion - Attach

[Jonathan Moules]

Thanks! Hopefully that'll save someone 5-10 minutes one day. :-)
It's surprising how few examples of "ATTACH" that are out there include 
"DETACH" too.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Friday, May 15, 2015 3:55 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Docs suggestion - Attach

Change implemented now on the website.

On 5/15/15, Jonathan Moules  wrote:
> Hi,
> A relatively simple suggestion for the ATTACH doc page -
> https://sqlite.org/lang_attach.html - can it include a link to DETACH
> (https://www.sqlite.org/lang_detach.html)? I ask because if you don't
> know what the syntax is (the word "DETACH"), it's a pain to find out
> (in my case I was googling for "UNATTACH" which obviously didn't find 
> anything).
>
> The DETACH page does link to ATTACH.
>
> Cheers,
> Jonathan
>
>
> 
> ___
> 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


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/oNkkIZjY!8DGX2PQPOmvUqW!RI4xIDLrS1LwEYepsSmDlIUyi8o8fHZ8RLGGB+2zyD3azgKGzdXCXaTERp6oIw==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Docs suggestion - Attach

[Jonathan Moules]

Hi,
A relatively simple suggestion for the ATTACH doc page - 
https://sqlite.org/lang_attach.html - can it include a link to DETACH 
(https://www.sqlite.org/lang_detach.html)? I ask because if you don't know what 
the syntax is (the word "DETACH"), it's a pain to find out (in my case I was 
googling for "UNATTACH" which obviously didn't find anything).

The DETACH page does link to ATTACH.

Cheers,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Best way to temporarily store data before processing

[Jonathan Moules]

Thanks for all the responses. In the end I went with the CREATE TEMP TABLE ... 
option which has worked very well and seems to have negligible overhead (on the 
order of 0.006s to create ~40 temporary tables and similarly quick to drop 
them).


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Petite 
Abeille
Sent: Wednesday, April 15, 2015 6:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Best way to temporarily store data before processing


> On Apr 14, 2015, at 11:40 AM, Jonathan Moules  
> wrote:
>
> Options that have come to mind (probably missed a lot):

I personally use temp tables, e.g. 'create temporary table if not exists foo?, 
coupled with 'pragma temp_store = memory?, and drop/create them as necessary, 
e.g. 'drop table if exists?. There is a set of ETL (Extract, Transform and 
Load) operations to massage the data just so to their final resting place.

Will not qualify this as ?best?, but it?s very workable and without much of a 
headache. As always, your milage may vary.

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


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/qYyWV6h9JtHGX2PQPOmvUj!GOBh06pKK75cHS8CBt56rbyGA4ThgbelonLeAq9lKW2cQtZ6+iOpzr!jqbv!p6A==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Best way to temporarily store data before processing

[Jonathan Moules]

Thanks for the thoughts, useful to know.

In relation to your question Joseph, historically the scripts were written to 
only put the raw data into the database and then the Views read it, but that 
was too slow. So now I've got an extra step to turn the raw data into easily 
Viewable data, with the bonus it uses considerably less disk space. The 
alternative of hard-coding the analysis and getting Python to write only the 
Processed Tables seemed like an ugly design, so I went with this.

Cheers



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joseph T.
Sent: Tuesday, April 14, 2015 12:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Best way to temporarily store data before processing


Mr. Moules, why not skip the raw tables entirely? Or failing that a separate in 
memory db would probably be the best and quickest option.



Sent from my Samsung Epic? 4G TouchJonathan Moules  wrote:Hi List, I'm wondering if anyone can offer me a "best 
practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
- Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
- And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
- And/Or place it into :memory:.
- And/Or just CREATE the Raw Tables for each file?
- And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in 
England No. 02562099


___
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


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/OtIwePPILzPGX2PQPOmvUtzroFmuoUEuyo!5uyiNNRAv6vRKBGbHjbCMljIjlDona9wHUfmUgVH3KeH4!zzvzA==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Best way to temporarily store data before processing

[Jonathan Moules]

Hi List,
I'm wondering if anyone can offer me a "best practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
- Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
- And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
- And/Or place it into :memory:.
- And/Or just CREATE the Raw Tables for each file?
- And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Appropriate Uses For SQLite

[Jonathan Moules]

Hi Richard,
How about mentioning extensions as a whole? I can't seem to find a list of 
SQLite extensions on sqlite.org, but it seems like it'd be useful information, 
and not just for those deciding on whether the language is right for them.
(When I use the word "extensions", I'm referring to things like Spatialite).

I appreciate the extensions are separate projects, but a list would probably be 
useful (PostGres has one, but it seems short - 
http://www.postgresql.org/download/products/6-postgresql-extensions/ ). It 
might be worth creating such a page for SQLite too.


Another thought - the rich ecosystem of administrative GUI's (Both open source 
and commercial). Given most folks on this list appear to be Guru's who breathe 
SQL, I can see why it was missed, but they're important to us lay-users.

Cheers,
Jonathan



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Wednesday, February 18, 2015 2:34 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Appropriate Uses For SQLite

In a feeble effort to do "marketing", I have revised the "Appropriate Uses For 
SQLite" webpage to move trendy buzzwords like "Internet of Things" and "Edge of 
the Network" above the break.  See:

https://www.sqlite.org/whentouse.html

Please be my "focus group", and provide feedback, comments,
suggestions, and/or criticism about the revised document.   Send your
remarks back to this mailing list, or directly to me at the email in the 
signature.

Thank you for your help.

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


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/YnvGRjO1h!fGX2PQPOmvUkWM85sEKD4+AxKwKR2OO3rUCZRh4ynTU2SGHzny8KZl+wI!vZXG5UPCP4z!bwErJQ==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Jonathan Moules]

Thanks for the comments.
I appreciate there are nuances of their differences that are likely obvious to 
a developer during development based on the current phrasing, but I'm putting 
this forward from a user's perspective.

Depending on the application, an end user likely won't see the error code, but 
instead just the error message (at least the application where I encountered 
this issue). They then google for "sqlite database locked" but get back 
information about SQLITE_LOCKED which isn't the same thing as SQLITE_BUSY. It 
doesn't particularly matter too much what the message is so long as it helps 
the user correctly find what it actually relates to in the documentation.

Using my simplistic understanding of the differences, what about these two:

SQLITE_BUSY - "Database is busy, locked by another connection" (some use of 
"busy" which should make googling for the actual problem easier to find).
SQLITE_LOCKED - "Database table is locked"

Cheers,
Jonathan

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Keith Medcalf
Sent: Wednesday, December 03, 2014 3:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] "database is locked" for SQLITE_BUSY

>From https://www.sqlite.org/rescode.html#busy

In both cases there are specific extended codes that may further pinpoint the 
source just in case you do not know what you are doing at the time the result 
code was returned.  Interpretation is only difficult if you do not know what 
you are doing when the result code was returned.


(5) SQLITE_BUSY
The SQLITE_BUSY result code indicates that the database file could not be 
written (or in some cases read) because of concurrent activity by some other 
database connection, usually a database connection in a separate process.

For example, if process A is in the middle of a large write transaction and at 
the same time process B attempts to start a new write transaction, process B 
will get back an SQLITE_BUSY result because SQLite only supports one writer at 
a time. Process B will need to wait for process A to finish its transaction 
before starting a new transaction. The sqlite3_busy_timeout() and 
sqlite3_busy_handler() interfaces and the busy_timeout pragma are available to 
process B to help it deal with SQLITE_BUSY errors.

An SQLITE_BUSY error can occur at any point in a transaction: when the 
transaction is first started, during any write or update operations, or when 
the transaction commits. To avoid encountering SQLITE_BUSY errors in the middle 
of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN 
to start a transaction. The BEGIN IMMEDIATE command might itself return 
SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent 
operations on the same database through the next COMMIT will return SQLITE_BUSY.

See also: SQLITE_BUSY_RECOVERY and SQLITE_BUSY_SNAPSHOT.

The SQLITE_BUSY result code differs from SQLITE_LOCKED in that SQLITE_BUSY 
indicates a conflict with a separate database connection, probably in a 
separate process, whereas SQLITE_LOCKED indicates a conflict within the same 
database connection (or sometimes a database connection with a shared cache).

(6) SQLITE_LOCKED
The SQLITE_LOCKED result code indicates that a write operation could not 
continue because of a conflict within the same database connection or a 
conflict with a different database connection that uses a shared cache.

For example, a DROP TABLE statement cannot be run while another thread is 
reading from that table on the same database connection because dropping the 
table would delete the table out from under the concurrent reader.

The SQLITE_LOCKED result code differs from SQLITE_BUSY in that SQLITE_LOCKED 
indicates a conflict on the same database connection (or on a connection with a 
shared cache) whereas SQLITE_BUSY indicates a conflict with a different 
database connection, probably in a different process.

(261) SQLITE_BUSY_RECOVERY
The SQLITE_BUSY_RECOVERY error code is an extended error code for SQLITE_BUSY 
that indicates that an operation could not continue because another process is 
busy recovering a WAL mode database file following a crash. The 
SQLITE_BUSY_RECOVERY error code only occurs on WAL mode databases.

(517) SQLITE_BUSY_SNAPSHOT
The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY 
that occurs on WAL mode databases when a database connection tries to promote a 
read transaction into a write transaction but finds that another database 
connection has already written to the database and thus invalidated prior reads.

The following scenario illustrates how an SQLITE_BUSY_SNAPSHOT error might 
arise:

Process A starts a read transaction on the database and does one or more 
SELECT statement. Process A keeps the transaction open.
Process B updates the database, changing values previous read by process A.
Process A now tries to write 

[sqlite] "database is locked" for SQLITE_BUSY

[Jonathan Moules]

Hi,
Just a quick request/suggestion.

Currently SQLITE_BUSY events return an error of "Database is locked". Is it 
possible to change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked", 
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I 
can tell is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.

Thanks,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099


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

Re: [sqlite] Long time to drop tables.

[Jonathan Moules]

HI,
Thanks for the replies.

The entire thing on happening on a single desktop machine. The database is
on a standard HDD. Using SQLiteStudio; sqlite version 3.7.16.1.

There are some foreign keys.

Autovacuum is off I think (don't know how to check, but the table size
never automatically shrinks of its own accord).

You can download the database here (compressed down to ~30mb).
https://drive.google.com/file/d/0B22cYd3gwE-6MTdwUzczUlptdk0/edit?usp=sharing

I was actually trying to delete all of these tables (this version of the
database doesn't have the extra tables I created; the extra ones had no
foreign keys at all).

Cheers,
Jonathan



On 16 August 2014 20:04, Richard Hipp <d...@sqlite.org> wrote:

> On Sat, Aug 16, 2014 at 2:41 PM, Jonathan Moules <
> jonathanmou...@warwickshire.gov.uk> wrote:
>
> > Hi List,
> > More of a curiosity.
> > I'm doing some general data munging and set off a query that consists
> > entirely of 37 DROP TABLEs in it. The database it's running against is
> > a bit less than 1GB made of about 5 million rows, and the tables being
> > dropped constitute about 99% of the content.
> >
> > My questions is - why does it take so long? The total time
> required
> > to create this dataset (most of which was processing on the Python
> > side) was about 11 minutes.
> >
> > The total time required to perform these drops is ... well I
> > cancelled it at 20mins - it had deleted 20 of the 37. For that entire
> > period SQLite has been reading at a rate of 170MB/s - by my maths it
> > had read about 200GB!
> >
> > The tables don't have indexes, the settings are all whatever the
> > defaults are.
> >
> > Any suggestions what's going on? Is this normal behavior?
> >
>
>
> I made a copy of a 2.3GB database that contained two tables that comprised
> 90% of the database space.  I did a DROP TABLE on each.  Each DROP TABLE
> took about 100 milliseconds, real-time.
>
> DROP TABLE bmdTileTable;
> Run Time: real 0.109 user 0.052133 sys 0.043098
> DROP TABLE bmdTileTable_with_out;
> Run Time: real 0.102 user 0.052688 sys 0.029648
>
> Maybe you have autovacuum turned on in your database?  Autovacuum makes
> doing things like DROP TABLE much slower because it has to rearrange
> content in order to move it all to the front of the file then truncate the
> file.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Long time to drop tables.

[Jonathan Moules]

Hi List,
More of a curiosity.
I'm doing some general data munging and set off a query that consists
entirely of 37 DROP TABLEs in it. The database it's running against is
a bit less than 1GB made of about 5 million rows, and the tables being
dropped constitute about 99% of the content.

My questions is - why does it take so long? The total time required
to create this dataset (most of which was processing on the Python
side) was about 11 minutes.

The total time required to perform these drops is ... well I
cancelled it at 20mins - it had deleted 20 of the 37. For that entire
period SQLite has been reading at a rate of 170MB/s - by my maths it
had read about 200GB!

The tables don't have indexes, the settings are all whatever the 
defaults are.

Any suggestions what's going on? Is this normal behavior?
Thanks,
Jonathan

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What average is avg()?

[Jonathan Moules]

Hi List,
A question and possible suggestion.

Which type of average does avg() calculate? The documentation doesn't say -
https://www.sqlite.org/lang_aggfunc.html

I guess it's the mean, but it could be median or mode, so worth asking.

My suggestion would be to include an explicit statement in the docs saying
which it is to make it clearer for users.

Cheers,
Jonathan

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variable values in Views

[Jonathan Moules]

Hi List,
Thanks for the responses.
I don't think TCL will work for me - I want to use less languages, not more.

As to the structure - I am considering using ATTACH as a method, but
haven't gotten to the point where I need to decide which of the three
options (keys in tables, table sets, or separate ATTACHed databases) to
use. I think personally I prefer the logical separation of  the ATTACH
method but haven't fully looked into it yet.

Cheers,
Jonathan

On 30 July 2014 07:00, Noel Frankinet  wrote:

> There a tcl binding to sqlite, maybe it could help you ?
>
> Noël
>
>
> On 30 July 2014 08:44, Sylvain Pointeau 
> wrote:
>
> > It is called parameterized view in sqlserver.
> > Actually it is extremely useful in order to have a good reusability in
> the
> > code.
> > I was actually missing it in Oracle, although I found a workaround of
> using
> > the pipelined functions.
> >
> > Unfortunately, it is missing in sqlite, as well as the merge statement,
> > also very useful (insert or replace has just to be avoided)
> >
> > What I used to make is to use a shell script, and to use sed to replace
> my
> > variable before executing the script... Far from ideal but it worked ok,
> it
> > is just annoying because we have to prepare the file first.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Noël Frankinet
> Strategis sprl
> 0478/90.92.54
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variable values in Views

[Jonathan Moules]

Hi Simon,
Thanks for your response. Good point about the preparation, didn't know
that.

> On the other hand, the fact that you're numbering tables suggests a badly
formed schema.  Are you sure you shouldn't merge the tables and move that
number to a column inside the table ?

Fair question, but I'm doing log analysis. Each set of tables will be for a
given server that's being analysed. The application that uses the data is a
read-only web-app, so my database needs to be optimised for SELECT queries.
I don't anticipate there being many servers in an install so the number of
table sets should be small. However the number of rows can be fairly large;
I figure this method should offer a small speed up on the potentially
larger datasets even if it's not strictly best-practice.

That said, I don't suppose there's any option for the other variable
($date_string)?

Thanks,
Jonathan


On 28 July 2014 14:37, Simon Slavin <slav...@bigfraud.org> wrote:

>
> > On 28 Jul 2014, at 12:41pm, Jonathan Moules <
> jonathanmou...@warwickshire.gov.uk> wrote:
> >
> > *$table_prefix* which will be a number indicating which table set to look
>
> You can't have variable table names in a prepared statement.  The process
> which does preparation has to know which table it will be accessing.  If
> you don't know which table you're be using, you can't use a system like
> that.
>
> On the other hand, the fact that you're numbering tables suggests a badly
> formed schema.  Are you sure you shouldn't merge the tables and move that
> number to a column inside the table ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Variable values in Views

[Jonathan Moules]

Hi List,
I have a view that works fine as-is, but I want to make it dynamic,
replacing a few values with variables.


SELECT
> service,
> sum( num ) AS num,
> round( ( sum( num ) * 100.0 ) /(
> SELECT sum( num )
> FROM* [$table_prefix]*_wms__getmap
> WHERE time_date > (SELECT datetime(max(time_date),
> *[$date_string]* ) FROM *[$table_prefix]*_all__request_type)
> ), 2 ) AS percent
> FROM *[$table_prefix]*_all_wms__getmap
> WHERE time_date > (SELECT datetime(max(time_date), *[$date_string]* )
> FROM *[$table_prefix]*_all__request_type)
> GROUP BY service
> ORDER BY sum( num ) DESC
>

There are two variables:
*$date_string* which will contain a string such as "-7 days" or "-1 month".

and
*$table_prefix* which will be a number indicating which table set to look
in.

I know SQLite can't do variables (for example
http://sqlite.1065341.n5.nabble.com/Using-local-variables-through-sqlite-td21270.html)
but does anyone have any suggestions for how this can be done? I don't want
to have to create tons of extra views if I don't have to.

Thanks,
Jonathan

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users