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 th
: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
_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
. 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
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
st 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
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 o
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 = '0027f2c9b
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
ables 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:
ecking, 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 &qu
-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 S
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?
B
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 ser
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 Po
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"."
r 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
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):
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
wrote:
Sure; I didn't include them because the only difference is the last line,
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
S 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 wrote:
But when I stick an "ORDER BY&quo
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
processin
On 2018-03-22 12:03, Richard Hipp wrote:
On 3/21/18, Jonathan Moules 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 l
RDER 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 hav
n 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 wrote:
So, I'm back to b
n'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
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
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
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
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
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
Bea
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, 0
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 locati
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 usi
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 list
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 bei
rg> 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 potentiall
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'
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 wrote
>> As for publ
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 Ber
te.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 datab
> 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/assembl
inglists.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 simpl
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
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 th
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 lo
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 thin
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,
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
extra ones had no
foreign keys at all).
Cheers,
Jonathan
On 16 August 2014 20:04, Richard Hipp wrote:
> On Sat, Aug 16, 2014 at 2:41 PM, Jonathan Moules <
> jonathanmou...@warwickshire.gov.uk> wrote:
>
> > Hi List,
> > More of a curiosity.
> > I'
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 the
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 separat
riable
($date_string)?
Thanks,
Jonathan
On 28 July 2014 14:37, Simon Slavin 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
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_d
62 matches
Mail list logo
