Re: [sqlite] Query optimisation

[David Wellman]

Hi Richard and David,

Many thanks for your responses, very useful.

I'll work with that (being careful to look at the OpCode documentation for my 
release of sqlite!) and see where I get to.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: 24 August 2018 15:43
To: SQLite mailing list
Subject: Re: [sqlite] Query optimisation

On 8/24/18, David Raymond  wrote:
> Running just "explain some query" will give you the virtual machine program
> that it plans on using. You can then scan through that to see what it's
> doing. Note that the descriptions on the below page for those op codes are
> sometimes really confusing and it can take a while to decypher what's going
> on.
>
> https://www.sqlite.org/opcode.html

To further confuse matters, the https://www.sqlite.org/opcode.html
page only describes the opcodes for the latest release (3.24.0)
whereas the OP is using an earlier release (3.20, I think).  Opcodes
and their meanings can change from one release to the next.

-- 
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] Query optimisation

[David Wellman]

HI all,

 

I would like to use the following example as a learning exercise for myself
to check my understanding of part of sqlite processing.

 

I have the following query which functionally works fine, and to be upfront
about it the volume of data is so small that performance is not an issue.

 

The query:

update relation

   set wastatsidcount = (select src.wastatsidcount

  from waSTATSINFO_VT as src

  where src.relationkey = relation.relationkey)

where wastatsidcount is null

  and queryid = 2;

 

The plan:

If I run 'explain query plan' on this command it gives the following:

SelectedID  OrderFrom Detail

0  0  0  SCAN TABLE
relation

0  0  0  EXECUTE
CORRELATED SCALAR SUBQUERY 0

0  0  0  SEARCH TABLE
waSTATSINFO_VT AS src USING INTEGER PRIMARY KEY (rowid=?)

 

My 'src' table is defined with a PRIMARY KEY on column RELATIONKEY. 

The same column is also the primary key on the 'relation' table.

 

My understanding of this plan is:

-  Read the relation table using a full table scan

-  (Assumption) Any row in the relation table that does NOT meet the
WHERE clause is ignored.

-  For each qualifying row in the relation table read from
waSTATSINFO_VT using the PK index to try and find a match

-  Where there is a match, update the relation table.

 

Questions:

-  Is my assumption above ("Any row in the relation table that does
NOT meet the WHERE clause is ignored") correct?

-  Is there any form of 'explain' or other diagnostic output which
would show me this?
I tried the 'explain query plan' with and without the full WHERE clause and
it didn't change the output (I didn't understand the output from the plain
'explain' command!)

 

In this particular example I need the WHERE clause as coded in order to give
me the correct answer, but as I said at the start I'm trying to deepen my
knowledge of SQLite performance and it's optimiser.

 

I'm currently using v3.20.1.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www: http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

[sqlite] Data types for date and time functions

[David Wellman]

Hi,

 

We're designing a new feature which will involve a lot of date/time
calculations which we intend to do within the SQLite engine. As far as we
can tell it has the functions that we need. Basically we'll be loading data
into SQLite and performing analysis and calculations using SQL.

 

The question has come up as to how we should store date/time values in our
tables? Basically how should we define our date/time columns?

 

For us an obvious choice is to store dates/times as REAL. We're working in a
Windows environment and so in the application code we're dealing with MS
serial date values.

 

Looking at the 'date and tine functions' page
(http://www.sqlite.org/lang_datefunc.html ) it says "All five date and time
functions take a time string as an argument". So my initial reaction is to
store dates and times as TEXT. I think this means that when passing such
date/time values into the functions there is one less conversion to do.

 

But then looking at some of the examples on that page I came across the
following:

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch'); 

 

In the sql syntax that I'm used to (which I thought was the same for SQLite)
a series of digits like that shown above is treated as a numeric data value
(and type), not text (i.e. a  "time string"). If that was meant to be string
data (i.e. text) then I'd have expected:

 

SELECT datetime('1092941466', 'unixepoch'); 

 

So to clarify: Is our idea of storing date/time values as TEXT data a
sensible one?

 

We're essentially thinking of performance and there are almost certainly
pros and cons to doing this.

Pro: (I think) more efficient processing as the data is supplied to these
functions as text which is what they're expecting/require - and therefore
there is no additional conversion required.

Con: Our full timestamps will be 19 bytes compared to a REAL which is only 8
bytes. So they will require more storage/disk space which ultimately means
more I/O to read the same number of rows and columns.

 

I accept that from a performance perspective there may not be much in it,
but I'd be interested in people's thoughts.

 

In anticipation, many thanks.

 

Cheers,

Dave

 

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

Re: [sqlite] [EXTERNAL] Number of rows in answer set

[David Wellman]

First, thanks to everyone for their input on this. It has resulted in a much 
bigger discussion than I had assumed would happen...

I obviously have a fundamental misunderstanding of how SQLite processes a 
request, (I'll read the link that Hick gave me - thanks for that).

So to answer my original question: there isn't an api that gives this value ** 
because ** SQLite doesn't build the full answer set before returning from that 
first sqlite3_step function call.

I'll do some reading and come back with any further questions

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: 18 September 2017 10:03
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 9/18/17, David Wellman <dwell...@ward-analytics.com> wrote:
> sqlite3_step - this one runs the sql, builds an answer set and then returns
> the first row

No.  sqlite3_step() does not "build the answer set".  It only begins
computing the answer, stopping at the first row.  The sqlite3_step()
routine has no idea how many more rows will follow at that point.

The only way to find out how many rows there are in the answer set is
to run sqlite3_step() repeatedly and count the number of times it
returns SQLITE_ROW before returning SQLITE_DONE.

-- 
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] [EXTERNAL] Number of rows in answer set

[David Wellman]

Hi Hick,

" The only way to come up with the exact number of result rows is to actually 
run the query." - agreed

" At which point you already know how many rows have been returned."  That's 
the point, the application doesn't know the exact number although presumably 
sqlite does. 

Let me explain a bit more.

To run a select statement the application code has to:
sqlite3_prepare: parse the sql, make sure it's valid, build the plan (using 
"nifty heuristics" :-) )
sqlite3_step - this one runs the sql, builds an answer set and then returns the 
first row
>>>   AT THIS POINT the application doesn't know how many rows are in the 
>>> answer set only that there is at least 1. <<<
(but presumably sqlite does know how many there are) 

AFAIK, if the application wants to know how many rows are in the answer set it 
has to 'sqlite3_step' to retrieve every row.

This isn't a major issue but I thought I would ask.

Cheers,
Dave

Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: 18 September 2017 09:37
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Number of rows in answer set

SQLite uses some nifty heuristics to estimate the number of rows it expects to 
process while formulating a query plan. The only way to come up with the exact 
number of result rows is to actually run the query. At which point you already 
know how many rows have been returned.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Wellman
Gesendet: Montag, 18. September 2017 10:27
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Number of rows in answer set

Hi,

If I run a SELECT statement that returns an answer set is there an api call 
that will tell me "number of rows in answer set" - something like 
"sqlite3_row_count"? I have looked at the calls but couldn't find any - sorry 
if I've missed it.



I know could use a "create temporary table xxx as my-select" and then "select 
count(*) from xxx" but was wondering if SQLite holds that information in an 
accessible place.



Cheers,

Dave



Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:  <http://www.ward-analytics.com> http://www.ward-analytics.com



Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.



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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
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] Number of rows in answer set

[David Wellman]

Hi,

If I run a SELECT statement that returns an answer set is there an api call
that will tell me "number of rows in answer set" - something like
"sqlite3_row_count"? I have looked at the calls but couldn't find any -
sorry if I've missed it.

 

I know could use a "create temporary table xxx as my-select" and then
"select count(*) from xxx" but was wondering if SQLite holds that
information in an accessible place.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

[sqlite] Download documentation for 3.20.1 still shows as Draft

[David Wellman]

Hi,

If you download the 3.20.1 documentation zip file from
http://www.sqlite.org/download.html the pages still show as 'Draft' (in big
red letters).

Not a major issue but it is a bit disconcerting when you first see it.

Cheers,

Dave

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

[sqlite] Check understanding of Prepare and Step processing

[David Wellman]

Hi,

 

I'm just trying to check my understanding of what happens when running a
query against SQLite and particularly how that might affect interaction
between an application and SQLite.

 

The SQL itself isn't really important for now, but assume it's a select
statement which will return some data.

 

From the "3 minute intro" (http://www.sqlite.org/quickstart.html )I
understand that the basic steps are:

1)  sqlite3_open_v2 - to open my database file for use by SQLite. This
returns a database handle.

2)  sqlite3_prepare_v2 - this routine results in the sql being "compiled
into a byte-code program" (http://www.sqlite.org/c3ref/prepare.html ). This
returns a statement handle.

3)  sqlite3_step - "this function must be called one or more times to
evaluate the statement." (http://www.sqlite.org/c3ref/step.html ) and also
return answer rows 
(the above api may be called multiple times to retrieve all data rows)

4)  sqlite3_finalize - to close the statement handle

5)  sqlite3_close_v2 - to close the database  handle

 

Based on the above and some testing (using 3.20.1):

 

Q1) I believe that the "sqlite3_prepare_v2" function parses the sql text to
check it is valid, the named objects exist etc. and builds the 'executable
program'. Does this function run the SQL? I don't think so.

 

Q2) At what point in the interaction between an application and the SQLite
engine is the sql actually executed?  I think this is the first
'sqlite_step' function call.

 

Q3) Are the 'sqlite_prepare' and 'sqlite_step' function calls synchronous?
(By which I mean when called, will they wait to finish their processing
before returning to the application).

 

Q4) If the function call where the SQLite engine runs the sql is
asynchronous then what is the "I'm still running and haven't finished yet"
return code?

(This question may not be a valid question depending on other answers, but I
thought I'd get it out here anyway).

 

Just out of interest:  Is the 'executable program' built by 'prepare' the
'virtual machine instructions' as referred to on the EXPLAIN documentation
page (http://www.sqlite.org/lang_explain.html )?

 

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

Re: [sqlite] Syntax error using CTE and UPDATE

[David Wellman]

Now that could be really useful for some things that I'm doing.

Many thanks.

Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: 04 September 2017 12:49
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Syntax error using CTE and UPDATE

One last thing I forgot to mention, on the topic of making INSERT and 
UPDATE easy -


If you are using SQLite 3.15 or later, you can use Row-value 
functionality to UPDATE several fields in one go from a sub query.

An example of how such an update query might look:

WITH CTE(ID, ta, tb, tc) AS (
 SELECT stuff...
)
UPDATE t SET (a, b, c) = (SELECT ta, tb, tc FROM CTE WHERE CTE.ID = t.ID)
;

INSERT of course can utilize a sub query or VALUES clause directly, 
which provide much the same functionality.

Read more here: http://sqlite.org/rowvalue.html

___
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] Syntax error using CTE and UPDATE

[David Wellman]

Hi Ryan,

Thanks for that. It is certainly valid syntax and I'll do some testing to check 
that it gives me the correct answer.

Your email has 'crossed in the post' with my second one and you've answered 
something that I asked in that (I'm impressed, how did you do that ?)

I understand that the UPDATE and INSERT can only have one main (or target) 
table, I completely agree with that. I didn't think of the CTE as being 'the 
main referenced' (i.e. target) table.

I have used a different dbms for 20+ years (Teradata) and that allows joins in 
UPDATEs (coded in a WHERE clause). It also allows use of a CTE albeit a 
slightly different syntax.

Once again, thanks for your help.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: 04 September 2017 11:37
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Syntax error using CTE and UPDATE

You are essentially trying to use a CTE (which for the intents and 
purposes of the UPDATE SQL is just the same as using any other table) 
inside an UPDATE statement as if it is the main referenced table.

In an UPDATE or INSERT however, there can only ever be 1 single main 
referenced table (i.e. no joins allowed) and any references to values in 
another table has to be by virtue of a sub-query (typically a correlated 
one).

I don't have your DB so I cannot test the answer, but it should work 
when changed to:

with dtls as (select distinct wrk.mapname as mapname2
 ,udb.udb_key
 ,rel.tablename
 ,rel.queryid
   from waPSM_TABLE_MAPNAME_WRK as wrk
   inner join relation as rel
  on wrk.queryid = 1
 and wrk.queryid = rel.queryid
 and wrk.tablename = rel.name
   inner join User_Database as udb
  on wrk.databasename = udb.udb_name
 and rel.udb_key = udb.udb_key)
update relation
   set wamapname = (
 SELECT dtls.mapname2 FROM dtls
  WHERE dtls.queryid = relation.queryid
AND dtls.udb_key = relation.udb_key
AND dtls.tablename = relation.tablename
  LIMIT 1
   )
;


Hope that works,
Cheers!
Ryan
  


On 2017/09/04 12:23 PM, David Wellman wrote:
> Hi,
>
>   
>
> (I have a feeling that this will be depressingly simple - but I just can't
> see it right now.)
>
>   
>
> The following code is failing with: Error: near line 3: no such column:
> dtls.mapname2
>
>   
>
> explain
>
> with dtls as (select distinct wrk.mapname as mapname2
>
>  ,udb.udb_key
>
>  ,rel.tablename
>
>  ,rel.queryid
>
>from waPSM_TABLE_MAPNAME_WRK as wrk
>
>inner join relation as rel
>
>   on wrk.queryid = 1
>
>  and wrk.queryid = rel.queryid
>
>  and wrk.tablename = rel.name
>
>inner join User_Database as udb
>
>   on wrk.databasename = udb.udb_name
>
>  and rel.udb_key = udb.udb_key)
>
> update relation
>
>set wamapname = dtls.mapname2
>
> where queryid = dtls.queryid
>
>and udb_key = dtls.udb_key
>
>and tablename = dtls.tablename;
>
>   
>
> I'm running it using the windows 32-bit shell program v3.20.1
>
>   
>
> If I just run the CTE component by itself it runs fine.
>
>   
>
> What am I missing?
>
>   
>
> All help greatly appreciated,
>
> Dave
>
>   
>
>   
>
>   
>
>   
>
> ___
> 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] Syntax error using CTE and UPDATE

[David Wellman]

I've been able to get my code runing by changing it to the following:

update relation
  set wamapname = (select wrk.mapname
  from waPSM_TABLE_MAPNAME_WRK as wrk
  inner join relation as rel
 on wrk.queryid = 1
and wrk.queryid = rel.queryid
and wrk.tablename = rel.name
  inner join User_Database as udb
 on wrk.databasename = udb.udb_name
and rel.udb_key = udb.udb_key
where relation.udb_key = udb.udb_key
  and relation.tablename = rel.tablename)
where queryid = 1;

This is using a correlated sub-query to ensure that I update the correct rows 
in the target table (the  'relation' table). I have to do some more testing but 
so far it looks to be working correctly.

So I guess now my question is when/how would I use a CTE in an UPDATE statement?
My reading of the syntax diagram (http://www.sqlite.org/lang_update.html ) 
shows that the CTE is valid syntax. 

Can anyone please give me an example (doesn’t need to use my tables) where a 
CTE can be used with the UPDATE?

Cheers,
Dave

Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Wellman
Sent: 04 September 2017 11:23
To: 'SQLite mailing list'
Subject: [sqlite] Syntax error using CTE and UPDATE

Hi,

 

(I have a feeling that this will be depressingly simple - but I just can't
see it right now.)

 

The following code is failing with: Error: near line 3: no such column:
dtls.mapname2

 

explain 

with dtls as (select distinct wrk.mapname as mapname2

,udb.udb_key

,rel.tablename

,rel.queryid

  from waPSM_TABLE_MAPNAME_WRK as wrk

  inner join relation as rel

 on wrk.queryid = 1

and wrk.queryid = rel.queryid

and wrk.tablename = rel.name

  inner join User_Database as udb

 on wrk.databasename = udb.udb_name

and rel.udb_key = udb.udb_key)

update relation

  set wamapname = dtls.mapname2

where queryid = dtls.queryid

  and udb_key = dtls.udb_key

  and tablename = dtls.tablename;

 

I'm running it using the windows 32-bit shell program v3.20.1

 

If I just run the CTE component by itself it runs fine.

 

What am I missing?

 

All help greatly appreciated,

Dave

 

 

 

 

___
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] Syntax error using CTE and UPDATE

[David Wellman]

Hi,

 

(I have a feeling that this will be depressingly simple - but I just can't
see it right now.)

 

The following code is failing with: Error: near line 3: no such column:
dtls.mapname2

 

explain 

with dtls as (select distinct wrk.mapname as mapname2

,udb.udb_key

,rel.tablename

,rel.queryid

  from waPSM_TABLE_MAPNAME_WRK as wrk

  inner join relation as rel

 on wrk.queryid = 1

and wrk.queryid = rel.queryid

and wrk.tablename = rel.name

  inner join User_Database as udb

 on wrk.databasename = udb.udb_name

and rel.udb_key = udb.udb_key)

update relation

  set wamapname = dtls.mapname2

where queryid = dtls.queryid

  and udb_key = dtls.udb_key

  and tablename = dtls.tablename;

 

I'm running it using the windows 32-bit shell program v3.20.1

 

If I just run the CTE component by itself it runs fine.

 

What am I missing?

 

All help greatly appreciated,

Dave

 

 

 

 

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

Re: [sqlite] Do you really need an ORDER BY after a GROUP BY

[David Wellman]

I always work on the premise that "if I want data returned to my application in 
a specific order then code an ORDER BY clause"

If the dbms 'happens to' return data in the desired order then that is 
obviously good, but if that behaviour is not documented (and I don't know about 
SQLite) then without the ORDER BY you have to recognise that this behaviour may 
change.

I typically work with a different dbms and the result set (almost gauranteed) 
will not be returned in 'group by' order.

Cheers,
Dave

Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: 24 August 2017 10:13
To: SQLite mailing list
Subject: [sqlite] Do you really need an ORDER BY after a GROUP BY

I always use an ORDER BY after a GROUP BY. For example:
SELECT   used
,COUNT(used)
FROM usedProverbs
GROUP BY used
ORDER BY used

But when I leave the ORDER BY out in this case, the result is the same, but
it looks like it is a bit faster.

I probably keep using it, because in my case the performance is not
important and if this works for SQLite3, but not for other databases, it is
better to keep using it.

-- 
Cecil Westerhof
___
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] Version 3.20.0 release candidate 2

[David Wellman]

Sorry, I should have said that item#4 in my last email should change 'facility' 
to 'facilitate'.

Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Wellman
Sent: 25 July 2017 16:46
To: 'SQLite mailing list'
Subject: Re: [sqlite] Version 3.20.0 release candidate 2

Also, I've just spotted the following (** characters surround the changes):

1) 
On the same page under " 2.1. Upping The Threat Level", the fourth paragraph 
starts:
 Because the pointer is passed in the t1.t1 column...

I think this is meant to be:
 Because the pointer is passed in the ** t1.cx ** column...

2)
On the same page under " 2.1. Upping The Threat Level", the first bullet under 
the sixth paragraph starts:
 The WebSQL interface to webkit allowed any webpage to to run arbitrary SQL 
in the browser for Chrome and Safari. That arbitrary SQL was suppose to be run 
inside a sandbox where it could do not harm even if exploited

I thin this is meant to say:
 The WebSQL interface to webkit allowed any webpage to to run arbitrary SQL 
in the browser for Chrome and Safari. That arbitrary SQL was suppose to be run 
inside a sandbox where it could do ** no ** harm even if exploited

3)
On the same page under " 2.3. Pointer Leaks"
The second sentence of the first para says:
   In other words, subtypes on pointer values prevents attacks using SQL 
statements like this:

I think it should say:
   In other words, subtypes on pointer values ** prevent ** attacks using SQL 
statements like this:

Or possibly:
   In other words, ** using ** subtypes on pointer values prevents attacks 
using SQL statements like this:

4)
On the same page, under " 5. Summary"

The second sentence of key take-awy #3 starts:
   Instead, use the interfaces designed to facility secure pointer passing:

I think it should be:
   Instead, use the interfaces designed to facilitate secure pointer passing:


Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Wellman
Sent: 25 July 2017 16:31
To: 'SQLite mailing list'
Subject: Re: [sqlite] Version 3.20.0 release candidate 2

Hi,

Minor doc error at https://sqlite.org/draft/bindptr.html 

The first paragraph under "2. A Brief History Of Pointer Passing In SQLite" 
starts with:
   It is sometimes convenient for SQLite extensions to communicatin non-SQL 
values...

I'm not sure what it is meant to say, but probably not that :-) Possibly 
something like:
It is sometimes convenient for SQLite extensions to communicate non-SQL 
values...

Cheers,
Dave



Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: 25 July 2017 16:23
To: General Discussion of SQLite Database; sqlite-dev
Subject: [sqlite] Version 3.20.0 release candidate 2

We are restarting the test process for SQLite 3.20.0.  There is a new
release candidate on the website at https://sqlite.org/download.html
and on the newly reopened branch-3.20 of the source tree at
https://sqlite.org/src/timeline?r=branch-3.20

A new draft change log is at https://sqlite.org/draft/releaselog/3_20_0.html

Changes in the release candidate since the previous release attempt include:

* Improved documentation for the new pointer-passing interface
* Date/time functions can now be used in CHECK constraints and indexes
* Added the UNION virtual table extension

The target release date is one week from today.  If you have concerns
with anything in this release, please raise them now.

The checklist at https://sqlite.org/checklists/320/index has been
reset.  The release will occur when that checklist goes all-green.
-- 
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@mailingl

Re: [sqlite] Version 3.20.0 release candidate 2

[David Wellman]

Also, I've just spotted the following (** characters surround the changes):

1) 
On the same page under " 2.1. Upping The Threat Level", the fourth paragraph 
starts:
 Because the pointer is passed in the t1.t1 column...

I think this is meant to be:
 Because the pointer is passed in the ** t1.cx ** column...

2)
On the same page under " 2.1. Upping The Threat Level", the first bullet under 
the sixth paragraph starts:
 The WebSQL interface to webkit allowed any webpage to to run arbitrary SQL 
in the browser for Chrome and Safari. That arbitrary SQL was suppose to be run 
inside a sandbox where it could do not harm even if exploited

I thin this is meant to say:
 The WebSQL interface to webkit allowed any webpage to to run arbitrary SQL 
in the browser for Chrome and Safari. That arbitrary SQL was suppose to be run 
inside a sandbox where it could do ** no ** harm even if exploited

3)
On the same page under " 2.3. Pointer Leaks"
The second sentence of the first para says:
   In other words, subtypes on pointer values prevents attacks using SQL 
statements like this:

I think it should say:
   In other words, subtypes on pointer values ** prevent ** attacks using SQL 
statements like this:

Or possibly:
   In other words, ** using ** subtypes on pointer values prevents attacks 
using SQL statements like this:

4)
On the same page, under " 5. Summary"

The second sentence of key take-awy #3 starts:
   Instead, use the interfaces designed to facility secure pointer passing:

I think it should be:
   Instead, use the interfaces designed to facilitate secure pointer passing:


Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Wellman
Sent: 25 July 2017 16:31
To: 'SQLite mailing list'
Subject: Re: [sqlite] Version 3.20.0 release candidate 2

Hi,

Minor doc error at https://sqlite.org/draft/bindptr.html 

The first paragraph under "2. A Brief History Of Pointer Passing In SQLite" 
starts with:
   It is sometimes convenient for SQLite extensions to communicatin non-SQL 
values...

I'm not sure what it is meant to say, but probably not that :-) Possibly 
something like:
It is sometimes convenient for SQLite extensions to communicate non-SQL 
values...

Cheers,
Dave



Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: 25 July 2017 16:23
To: General Discussion of SQLite Database; sqlite-dev
Subject: [sqlite] Version 3.20.0 release candidate 2

We are restarting the test process for SQLite 3.20.0.  There is a new
release candidate on the website at https://sqlite.org/download.html
and on the newly reopened branch-3.20 of the source tree at
https://sqlite.org/src/timeline?r=branch-3.20

A new draft change log is at https://sqlite.org/draft/releaselog/3_20_0.html

Changes in the release candidate since the previous release attempt include:

* Improved documentation for the new pointer-passing interface
* Date/time functions can now be used in CHECK constraints and indexes
* Added the UNION virtual table extension

The target release date is one week from today.  If you have concerns
with anything in this release, please raise them now.

The checklist at https://sqlite.org/checklists/320/index has been
reset.  The release will occur when that checklist goes all-green.
-- 
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] Version 3.20.0 release candidate 2

[David Wellman]

Hi,

Minor doc error at https://sqlite.org/draft/bindptr.html 

The first paragraph under "2. A Brief History Of Pointer Passing In SQLite" 
starts with:
   It is sometimes convenient for SQLite extensions to communicatin non-SQL 
values...

I'm not sure what it is meant to say, but probably not that :-) Possibly 
something like:
It is sometimes convenient for SQLite extensions to communicate non-SQL 
values...

Cheers,
Dave



Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: 25 July 2017 16:23
To: General Discussion of SQLite Database; sqlite-dev
Subject: [sqlite] Version 3.20.0 release candidate 2

We are restarting the test process for SQLite 3.20.0.  There is a new
release candidate on the website at https://sqlite.org/download.html
and on the newly reopened branch-3.20 of the source tree at
https://sqlite.org/src/timeline?r=branch-3.20

A new draft change log is at https://sqlite.org/draft/releaselog/3_20_0.html

Changes in the release candidate since the previous release attempt include:

* Improved documentation for the new pointer-passing interface
* Date/time functions can now be used in CHECK constraints and indexes
* Added the UNION virtual table extension

The target release date is one week from today.  If you have concerns
with anything in this release, please raise them now.

The checklist at https://sqlite.org/checklists/320/index has been
reset.  The release will occur when that checklist goes all-green.
-- 
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] Sequential numbers

[David Wellman]

HI,
That sounds a neat solution, I'll have a look at implementing one.
Many thanks.
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille
Sent: 24 June 2014 21:57
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sequential numbers


On Jun 24, 2014, at 10:47 PM, Dave Wellman 
wrote:

> I need the values to be sequential.

Well. if your data set is as small as you mentioned (20 records or less).
you could roll your own numbering schema with the simple expedient of
attaching a trigger to your tables to auto -number them with 'select count(
* ) + 1 from table' or something.

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

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

Re: [sqlite] Combining two databases into one

[David Wellman]

Hi Simon,
Thanks for that.
Can I just check something. When you say " Don't forget to DELETE FROM
before you insert your new rows " I assume that you're saying to make sure
that the target table is empty before I do the insert. Yes?

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: 10 November 2013 18:32
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Combining two databases into one


On 10 Nov 2013, at 5:49pm, Dave Wellman  wrote:

> Ah! I may have just found the answer. If I've got databases db1 and 
> db2 attached, can I use something like 'insert into db1.t1 select * 
> from db2.t1;' (assuming that the 't1' definitions are the same !)?

That's the best way I know of to do it, bearing in mind your entire
requirements.  I don't see anything else wrong with your proposed procedure.
Don't forget to DELETE FROM before you insert your new rows.

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

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

Re: [sqlite] Some basic questions

[David Wellman]

Hi all,

Many thanks for the info. I'll look into using the changes function for what
I need.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: 24 April 2013 15:03
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Some basic questions


On 23 Apr 2013, at 7:57pm, David Wellman <david.well...@ward-analytics.com>
wrote:

> Q1) Is it possible to execute sql commands asynchronously ? i.e. my 
> program issues the sql command and then 'loops' whilst waiting for the 
> command to finish. The main need for this is so that my user has a 
> chance to cancel the processing should they want to.

SQLite commands return with the data you requested.  There's no asynchronous
mechanism.  You can implement one in threads or processes yourself, but it's
almost never needed.  If your application performs any SQLite calls that
take more than a couple of seconds, it's usually a sign that you didn't make
the right indexes to let your SQL commands execute quickly.  Fix it by
looking at your commands and figuring out what indexes they want, rather
than by implementing multitasking.

The exception is for a SELECT command which returns multiple rows.  You step
through the results row by bow by calling _step().  So after any initial
sorting needed your program can just check to see whether the user has
aborted yet before deciding to call _step() again, all without worrying
about doing any asynchronous calls.  If the user choses to abort you just
stop calling _step() and skip straight to the _finalize() to release any
memory used by the query.

> Q2) For sql commands that do not return any data (really just the DML 
> commands Update, Delete and Insert/Select) is there any way to find 
> out how many rows were affected by the command? So if my Update 
> command changes 57 rows, then can I find that '57' number anywhere?

<http://www.sqlite.org/c3ref/changes.html>

If you don't have access to this function because you're using a non-SQLite
database library you can find the same value using the SQL function
"changes()":

<http://www.sqlite.org/lang_corefunc.html#changes>

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

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

[sqlite] Some basic questions

[David Wellman]

Hi,

 

I've just started using SQLite and having come from another dbms environment
I'm trying to find out if some of the features that I'm used to  using in my
coding are available with Sqlite. I've done a lot of searching through the
documentation and I apologise if the information is there but I couldn't
find it.

 

Q1) Is it possible to execute sql commands asynchronously ? i.e. my program
issues the sql command and then 'loops' whilst waiting for the command to
finish. The main need for this is so that my user has a chance to cancel the
processing should they want to.

 

Q2) For sql commands that do not return any data (really just the DML
commands Update, Delete and Insert/Select) is there any way to find out how
many rows were affected by the command? So if my Update command changes 57
rows, then can I find that '57' number anywhere?

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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