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
: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
(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
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
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
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
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
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
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
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
ike 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 t
rote:
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
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
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?
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
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
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"."
te> 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: [sq
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
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):
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 las
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
ent
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:
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
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/downloa
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
f 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
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
3-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 Y
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:
TE 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 <jon
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
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
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
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
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,
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
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
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
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
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
, 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 ty
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
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
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,
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
qlite.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 task
> 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
qlite.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 suggesti
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
: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?. Th
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
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
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
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
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
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.
> &
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.
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
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
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
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
62 matches
Mail list logo
