Re: [sqlite] 3.17.0 does not read updated DB

[Hick Gunter]

The unix mv command does not affect currently open file handles, only the 
directory entry or entries (atomically, unless it is a cross-filesystem mv, 
which is accomplished by pretrending you said cp and is in no way atomic).

As long as the first command shell is running, it will continue to see (and 
modify) the original contents. A new command shell would open a different file 
(the one the directory entry now points to) and therefore operate on a 
different file than the first command shell.

A "file" on unix consists of an "inode" (the basic file system entity that 
allows allocation of disk space) and at least one "directory entry" (that 
assigns a name to an inode). An inode is freed when the last directory entry 
referring to it is removed and the last file handle referencing it is closed.

e.g.

x) run sqlite on a.db (let's assume this file is inode number 4711)
x) mv a.db b.db (now the directory entry for b.db points to inode 4711)
x) mv x.db a.db (now the directory entry for a.db points to the inode of x.db, 
let's assume this is 815)
x) run sqlite on a.db (this opens inode 815)

note: to access the former a.db you now have to use the name b.db


e.g.

x) run sqlite on a.db (again assumed inode number 4711)
x) mv b.db a.db (now the directory entry for a.db points to the inode of b.db, 
again assumed 815)
x) run sqlite on a.db (this opens inode 815)

note: the former contents of a.db (inode 4711) is now no longer referenced by 
any directory entry. It will continue to stick around until the (last) file 
handle is closed, after which it will be deleted.

In both cases, the two instances of sqlite are operating on different inodes 
i.e. file contents and therefore cannot see each others' changes.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Roman Fleysher
Gesendet: Donnerstag, 16. Februar 2017 06:52
An: SQLite mailing list 
Betreff: Re: [sqlite] 3.17.0 does not read updated DB

My mistake: I do not update DB. I rename (unix mv) the DB.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Thursday, February 16, 2017 12:33 AM
To: General Discussion of SQLite Database
Subject: [sqlite] 3.17.0 does not read updated DB

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up 
updated content of a DB file? I updated DB while it was attached in sqlite3 and 
SELECT did not produce new results. Instead it printed the same output as 
before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem 
to be able to replicate that.

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


___
 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

Re: [sqlite] 3.17.0 does not read updated DB

[Roman Fleysher]

My mistake: I do not update DB. I rename (unix mv) the DB.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Thursday, February 16, 2017 12:33 AM
To: General Discussion of SQLite Database
Subject: [sqlite] 3.17.0 does not read updated DB

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up 
updated content of a DB file? I updated DB while it was attached in sqlite3 and 
SELECT did not produce new results. Instead it printed the same output as 
before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem 
to be able to replicate that.

Roman
___
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] 3.17.0 does not read updated DB

[Roman Fleysher]

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up 
updated content of a DB file? I updated DB while it was attached in sqlite3 and 
SELECT did not produce new results. Instead it printed the same output as 
before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem 
to be able to replicate that.

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

Re: [sqlite] Error handling

[Igor Tandetnik]

On 2/15/2017 10:42 PM, Igor Korot wrote:

Now I presume that calling sqlite3_finalize() on the NULL handle is safe?


Yes; the documentation explicitly states that.
--
Igor Tandetnik

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

Re: [sqlite] Error handling

[Igor Korot]

Hi, Richard,

On Wed, Feb 15, 2017 at 8:20 PM, Richard Hipp  wrote:
> On 2/15/17, Igor Korot  wrote:
>>
>> Well, my question here is a bit different - if sqlite3_step () returns an
>> error
>> should the statement be released?
>>
>
> You still need to invoke either sqlite3_finalize() or sqlite3_reset()
> on the statement.  Use sqlite3_finalize() if you will never use that
> statement again, and sqlite3_reset() if you want to reuse it.  Just be
> cause it got an error on one go doesn' t mean that it cannot be
> reused, if that is what you are asking.

Basically I was asking if the failure on the sqlite3_step() release the
statement handle or not. So thank you for confirming it is not.

Now I presume that calling sqlite3_finalize() on the NULL handle is safe?
Because I am planning to call this function only once - after the loop.

Thank you.

>
> --
> 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] Error handling

[Richard Hipp]

On 2/15/17, Igor Korot  wrote:
>
> Well, my question here is a bit different - if sqlite3_step () returns an
> error
> should the statement be released?
>

You still need to invoke either sqlite3_finalize() or sqlite3_reset()
on the statement.  Use sqlite3_finalize() if you will never use that
statement again, and sqlite3_reset() if you want to reuse it.  Just be
cause it got an error on one go doesn' t mean that it cannot be
reused, if that is what you are asking.

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

Re: [sqlite] Error handling

[Igor Korot]

Hi, Igor,


On Feb 15, 2017 7:16 PM, "Igor Tandetnik"  wrote:

On 2/15/2017 7:02 PM, Igor Korot wrote:

> My question is: how many calls to "sqlite3_finalize() should be there?
>

For every successful call to sqlite3_prepare[_v2], there should eventually
be a call to sqlite3_finalize; otherwise, you'd leak a statement, and
prevent the database handle from closing cleanly.


Ok.



Do I need one inside lines 2-6?
>

You don't. If sqlite3_prepare_v2 fails, you wouldn't have a valid handle to
pass to sqlite3_finalize.


And how to properly handle failure with statement release?
>

sqlite3_finalize() itself never fails. Through a quirk of history, it may
return an error code from the most recent failed sqlite3_step() call (see
Goofy Interface Alert section at https://sqlite.org/c3ref/step.html ).
Since you are using sqlite3_prepare_v2, you may ignore the return value of
sqlite3_finalize.


Well, my question here is a bit different - if sqlite3_step () returns an
error
should the statement be released?


Thank you.


-- 
Igor Tandetnik

___
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] Error handling

[Igor Tandetnik]

On 2/15/2017 7:02 PM, Igor Korot wrote:

My question is: how many calls to "sqlite3_finalize() should be there?


For every successful call to sqlite3_prepare[_v2], there should 
eventually be a call to sqlite3_finalize; otherwise, you'd leak a 
statement, and prevent the database handle from closing cleanly.



Do I need one inside lines 2-6?


You don't. If sqlite3_prepare_v2 fails, you wouldn't have a valid handle 
to pass to sqlite3_finalize.



And how to properly handle failure with statement release?


sqlite3_finalize() itself never fails. Through a quirk of history, it 
may return an error code from the most recent failed sqlite3_step() call 
(see Goofy Interface Alert section at https://sqlite.org/c3ref/step.html 
). Since you are using sqlite3_prepare_v2, you may ignore the return 
value of sqlite3_finalize.

--
Igor Tandetnik

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

[sqlite] Error handling

[Igor Korot]

Hi, ALL,
Consider the following pseudo-code:

[code]
int ret = sqlite3_prepare_v2();
if( ret != SQLITE_OK )
{
// henerate error message
return 1;
}
while( ; ; )
{
ret = sqlite3_step();
if( ret == SQLITE_ROW )
{
// everything is good
}
else if( ret == SQLITE_DONE )
break;
else
{
result = 1;
break;
}
}
if( ret != SQLITE_DONE )
return 1;
// everything is good - continue processing
[/code]

My question is: how many calls to "sqlite3_finalize() should be there?

Do I need one inside lines 2-6? And how to properly handle failure with
statement release?

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

Re: [sqlite] Documentation error

[Richard Hipp]

On 2/15/17, Paul Sanderson  wrote:
> The process for calculating a checksum text on the SQLite file format page
> contains two errors.
>

Thanks for spotting this.  Should be fixed now.
-- 
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

Re: [sqlite] sqlite3 hangs on query

[Richard Hipp]

On 2/15/17, Richard Hipp  wrote:
>
> It is an optimization opportunity, not a bug.
>

That optimization is now on trunk.

-- 
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] Documentation error

[Paul Sanderson]

The process for calculating a checksum text on the SQLite file format page
contains two errors.

Currently reads:

The checksum is an unsigned 32-bit integer computed as follows:

   1. Initialize the checksum to the checksum nonce value found in the
   journal header at offset 12.
   2. Initialize index X to be N-200 (where N is the size of a database
   page in bytes.
   3. Interpret the four bytes at offset X into the page as a 4-byte
   big-endian unsigned integer. Add the value of that integer to the checksum.
   4. Subtrace 200 from X.
   5. If X is greater than or equal to zero, go back to step 3.


The source states:

** This is not a real checksum. It is really just the sum of the
** random initial value (pPager->cksumInit) and every 200th byte
** of the page data, starting with byte offset (pPager->pageSize%200).
** Each byte is interpreted as an 8-bit unsigned integer.


Should read somethng like:

The checksum is an unsigned 32-bit integer computed as follows:

   1. Initialize the checksum to the checksum nonce value found in the
   journal header at offset 12.
   2. Initialize index X to be N-200 (where N is the size of a database
   page in bytes.
   3. Interpret the* byte* at offset X into the page as *an 8-bit* unsigned
   integer. Add the value of that integer to the checksum.
   4. Subtrac*t* 200 from X.
   5. If X is greater than or equal to zero, go back to step 3.




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Reporting some strange behaviour OSX 10.8.5/sqlite3.17

[islaind]

Hi there and many thanks for sqlite!

I am reporting a problem compiling latest sqlite(3.17) on MAC OSX 10.8.5

$ ./configure && make

Compilation fails with the following messages:
sqlite3.c:28836:10: error: use of unknown builtin '__builtin_add_overflow'
  [-Wimplicit-function-declaration]
  return __builtin_add_overflow(*pA, iB, pA);
 ^
sqlite3.c:28856:10: error: use of unknown builtin '__builtin_sub_overflow'
  [-Wimplicit-function-declaration]
  return __builtin_sub_overflow(*pA, iB, pA);
 ^
sqlite3.c:28856:10: note: did you mean '__builtin_add_overflow'?
sqlite3.c:28836:10: note: '__builtin_add_overflow' declared here
  return __builtin_add_overflow(*pA, iB, pA);
 ^
sqlite3.c:28871:10: error: use of unknown builtin '__builtin_mul_overflow'
  [-Wimplicit-function-declaration]
  return __builtin_mul_overflow(*pA, iB, pA);
 ^
sqlite3.c:28871:10: note: did you mean '__builtin_sub_overflow'?
sqlite3.c:28856:10: note: '__builtin_sub_overflow' declared here
  return __builtin_sub_overflow(*pA, iB, pA);
 ^
3 errors generated.
--

The compilation settings/versions:
$ clang --version
Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn)
Target: x86_64-apple-darwin12.6.0
Thread model: posix

$ gcc --version
Configured with: --prefix=/Applications/Xcode.app/Contents/Developer/usr 
--with-gxx-include-dir=/usr/include/c++/4.2.1

Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn)
Target: x86_64-apple-darwin12.6.0
Thread model: posix

The following program (borrowed from sqlite3.c)
# define GCC_VERSION 
(__GNUC__*100+__GNUC_MINOR__*1000+__GNUC_PATCHLEVEL__)
# define CLANG_VERSION 
(__clang_major__*100+__clang_minor__*1000+__clang_patchlevel__)


#include 

int main(void){
printf("CLANG_VERSION: %d\n", CLANG_VERSION);
printf("GCC_VERSION: %d\n", GCC_VERSION);
}

gives the following response on my system:
$ gcc a.c && a.out

CLANG_VERSION: 5001000
GCC_VERSION: 4002001

-

Your program compiles fine if I modify sqlite3.c not to use the builtin 
functions the compiler complains about:

Clearly the test (e.g. line 28856 of sqlite3.c) :
#if GCC_VERSION>=5004000 || CLANG_VERSION>=400
  return __builtin_mul_overflow(*pA, iB, pA);

is not the correct one UNLESS my clang (which is version 5.1 or should i 
say 'Apple' version 5.1?) does understands builtins and needs some 
special flags to use them.


I have posted a question on stackoverflow 
(http://stackoverflow.com/questions/42234112/osx-10-8-5-gcc-fails-to-recognise-clang5-extension-builtin-mul-overflow) 
about this problem as I am trying to figure out whether it is my system 
to be blamed and whether I have these builtins.


---

FYI, one response from stackoverflow might be helpful for your development:

"Testing the version of clang is usually a bad idea, upstream and Apple 
have completely different versioning schemes... __has_builtin is the 
recommended way to check for builtins on clang. – Marc Glisse"


-

Many thanks for providing sqlite and all the work you are doing,

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

Re: [sqlite] Thread safety of serialized mode

[Bob Friesenhahn]

It turns out that I have more data on the problem.  The error message 
reported reads something like:


SQLITE_CORRUPT: database disk image is malformed database corruption 
at line 70273 of [17efb4209f]


We are using version 3.10.2.

Looking at amalgamation code I see that the error is returned from 
handleDeferredMoveto() and is base on a value returned from 
sqlite3BtreeMovetoUnpacked():


  70259 ** The cursor "p" has a pending seek operation that has not yet been
  70260 ** carried out.  Seek the cursor now.  If an error occurs, return
  70261 ** the appropriate error code.
  70262 */
  70263 static int SQLITE_NOINLINE handleDeferredMoveto(VdbeCursor *p){
  70264   int res, rc;
  70265 #ifdef SQLITE_TEST
  70266   extern int sqlite3_search_count;
  70267 #endif
  70268   assert( p->deferredMoveto );
  70269   assert( p->isTable );
  70270   assert( p->eCurType==CURTYPE_BTREE );
  70271   rc = sqlite3BtreeMovetoUnpacked(p->uc.pCursor, 0, p->movetoTarget, 0, 
);
  70272   if( rc ) return rc;
  70273   if( res!=0 ) return SQLITE_CORRUPT_BKPT;
  70274 #ifdef SQLITE_TEST
  70275   sqlite3_search_count++;
  70276 #endif
  70277   p->deferredMoveto = 0;
  70278   p->cacheStatus = CACHE_STALE;
  70279   return SQLITE_OK;
  70280 }

Ideas?

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group contiguous rows (islands)

[E.Pasma]

Jean-Luc Hainaut:


On 15/02/2017 18:34, E.Pasma wrote:


Hello,  the query below is simpler. May be slower. But looks pretty  
relational. Thanks, E Pasma.


create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
   select t.*, max(t2.date) as key2
   from t
   left join t t2
   on t2.datet.test
   group by t.date
   )
group by key2


Quite nice solution indeed!
For those who may feel uncomfortable with outer joins, the from  
clause could be written as a subquery:


from (select date, test, (select  max(date)
 fromt t2
 where  t2.date < t.date
 and  t2.test <> t.test)  
as key2)


Thanks

J-L

this way you may also try to optimise speed by using ORDER BY & LIMIT  
1 instead of MAX


from (select date, test, (select t2.date
  from  t t2
  where t2.date < t.date
  and t2.test <>  t.test
  order by t2.date desc limit 1)  
as key2

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

Re: [sqlite] Difference between min and max time, especially for sys

[Simon Slavin]

On 15 Feb 2017, at 7:47pm, Cecil Westerhof  wrote:

> So the difference between minimum and maximum is about a factor two. What
> seems reasonable to me. But the difference between sys can be almost a
> factor twenty. What seems very big to me.

Caching affects sys.  Might be difference between time taken to fetch from disk 
and time taken when already in memory.

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

[sqlite] Difference between min and max time, especially for sys

[Cecil Westerhof]

I have several queries which I time with ‘.timer on’. I repeat the queries
350 times. I see a big difference between the minimum and maximum time
needed to run the queries. Especially for sys. Like:
Number of iterations 350
Timing OR version
real: 120.86, min: 0.2710, max: 0.5540, max/min:  2.04
user: 110.10, min: 0.2360, max: 0.4760, max/min:  2.02
sys :   9.87, min: 0.0040, max: 0.0640, max/min: 16.00


Timing ABS version
real: 108.96, min: 0.2730, max: 0.4850, max/min:  1.78
user:  97.67, min: 0.2320, max: 0.3720, max/min:  1.60
sys :  10.67, min: 0.0080, max: 0.0680, max/min:  8.50


Timing NOT BETWEEN version
GOT A ZERO VALUE FOR SYS!!!
real: 106.51, min: 0.2610, max: 0.4830, max/min:  1.85
user:  95.64, min: 0.2160, max: 0.4160, max/min:  1.93
sys :  10.14, min: 0.0040, max: 0.0760, max/min: 19.00


Timing NOT BETWEEN version 2
GOT A ZERO VALUE FOR SYS!!!
real: 113.77, min: 0.2770, max: 0.5370, max/min:  1.94
user: 101.76, min: 0.2320, max: 0.4520, max/min:  1.95
sys :  11.24, min: 0.0040, max: 0.0680, max/min: 17.00



So the difference between minimum and maximum is about a factor two. What
seems reasonable to me. But the difference between sys can be almost a
factor twenty. What seems very big to me.

Also it looks like SQLite suggests a precision it does not have. It shows
four digits after the point, but it looks like that the smallest value
greater as zero is 0.004. So why does SQLite not show three digits after
the point?



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

Re: [sqlite] .timer command missing from CLI ?

[Simon Slavin]

On 15 Feb 2017, at 7:39pm, Richard Hipp  wrote:

> Are you saying it is the one built into MacOS - not one you obtained
> from https://www.sqlite.org/download.html?  If so, can you provide me
> with details of what OS version you are running, please?

Answered privately because it’s a non-public release of macOS.  Sorry, folks, 
shouldn’t have posted in public.

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

Re: [sqlite] Group contiguous rows (islands)

[Jean-Luc Hainaut]

On 15/02/2017 18:34, E.Pasma wrote:


Hello,  the query below is simpler. May be slower. But looks pretty 
relational. Thanks, E Pasma.


create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
select t.*, max(t2.date) as key2
from t
left join t t2
on t2.datet.test
group by t.date
)
group by key2


Quite nice solution indeed!
For those who may feel uncomfortable with outer joins, the from clause 
could be written as a subquery:


from (select date, test, (select  max(date)
  fromt t2
  where  t2.date < t.date
  and  t2.test <> t.test) 
as key2)


Thanks

J-L

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

Re: [sqlite] .timer command missing from CLI ?

[Richard Hipp]

On 2/15/17, Simon Slavin  wrote:
>
> On 15 Feb 2017, at 7:17pm, Ben Newberg  wrote:
>
>> Is that a homebrew version of 3.16.0?
>
> Not in any way.  It’s one which comes with a very recent version of macOS:

Are you saying it is the one built into MacOS - not one you obtained
from https://www.sqlite.org/download.html?  If so, can you provide me
with details of what OS version you are running, please?

-- 
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] System.Data.SQLite: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column

[Burtsev, Dmitriy]

Windows 7 64bit
SQLite version 3.17.0
sqlite-netFx46-static-binary-x64-2015-1.0.104.0

Note: The code is working correctly with Devart ODBC driver for SQLite 2.1.4
The problem is with System.Data.SQLite

How to reproduce:
ON SQLite
Create database N:\SqLite\outStream.db

CREATE TABLE FromNum (id int NOT NULL, Num numeric(5, 5));
INSERT INTO FromNum (id, Num) VALUES (1, .0);

On SQL Server

CREATE TABLE [dbo].[ToNum](
[id] [int] NOT NULL,
[Num] [numeric](5, 5) NULL,
PRIMARY KEY CLUSTERED
([id] ASC) );

PowerShell script:

[string]ToTableName = "SERVER.Test.dbo.ToNum"
[string]FromFileName = 'N:\SqLite\outStream.db'

[string[]]ttname = ToTableName.Split(".")
[string]ServerName = ttname[0]
[string]dbName = ttname[1]
[string]schema = ttname[2]
[string]DestinationTable = ttname[3]

sqlString = "SELECT * FROM FromNum"
con = New-Object -TypeName System.Data.SQLite.SQLiteConnection -ArgumentList 
"Data Source=$FromFileName"
#$con = New-Object -TypeName System.Data.Odbc.OdbcConnection -ArgumentList 
"DSN=SqLite;Database=N:\SqLite\outStream.db;Direct=True" this is Devart ODBC

con.Open()

SourceSQLCommand = con.CreateCommand()
SourceSQLCommand.CommandText = sqlString

DestinationConnectionString = "Data Source=$ServerName;Initial 
Catalog=$dbName;integrated security=false"

try
{
reader = SourceSQLCommand.ExecuteReader()
bulkCopy = New-Object ("System.Data.SqlClient.SqlBulkCopy") 
DestinationConnectionString, 
([System.Data.SqlClient.SqlBulkCopyOptions]::TableLock -bor 
[System.Data.SqlClient.SqlBulkCopyOptions]::UseInternalTransaction)
bulkCopy.DestinationTableName = DestinationTable
bulkcopy.EnableStreaming = true;
bulkCopy.BatchSize = 5 #The number of rows in each batch sent 
to the server
bulkCopy.BulkCopyTimeout = 0 #the number of seconds before a 
time-out
bulkCopy.WriteToServer($reader) 
#copy all rows to the server
}
catch
{
ex = _.Exception
Write-Error ex.Message
}
finally
{
reader.Close()
con.Close()
}

Error Message:

Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be
converted to type decimal of the specified target column."

Dmitriy Burtsev


This message, and any of its attachments, is for the intended recipient(s) 
only, and it may contain information that is privileged, confidential, and/or 
proprietary and subject to important terms and conditions available at 
http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
intended recipient, please delete this message and immediately notify the 
sender. No confidentiality, privilege, or property rights are waived or lost by 
any errors in transmission.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 hangs on query

[Richard Hipp]

On 2/15/17, Simon Slavin  wrote:
> select * from albums, album_asset, cacheReferences, coreInfo, space limit 1;
>
> It’s using 100% CPU time (on a multicore CPU).

It is still a 5-way join.  It will terminate after looking at all
20,318,172,864 possible combinations of albums, album_assets,
cacheReferences, and coreInfo and realizing that the space table will
be empty in every case.

It is an optimization opportunity, not a bug.

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

Re: [sqlite] sqlite3 hangs on query

[Igor Tandetnik]

On 2/15/2017 1:53 PM, Jens-Heiner Rechtien wrote:

Please try the following with the restored database:

The query
*select * from albums, album_asset, cacheReferences, coreInfo, space
limit 1;*
will hang as well.


Ah, interesting. space is empty, which is what makes the difference. In 
the query plan, it ends up in the innermost loop. SQLite then goes 
through the full cross join of all the other tables, only to get to the 
inner loop and discover that there isn't going to be a row after all. 
"LIMIT 1" doesn't help any as the query is never going to produce a row.

--
Igor Tandetnik

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

Re: [sqlite] WAL journal mode & sqlite3_backup_step()

[Olivier Mascia]

> Le 15 févr. 2017 à 18:44, Clemens Ladisch  a écrit :
> 
> Olivier Mascia wrote:
>> A good approach ... is to drive the backup by a single call to 
>> sqlite3_backup_step()
> 
> This is indeed what you should do with WAL.
> 
>> The only downside is that I loose the capability to monitor (or inform users 
>> if needed) of the backup progress.
> 
> That was never the primary purpose of having multiple steps; it was intended 
> to allow
> concurrent read and write accesses (before WAL existed).

Indeed, the reporting was probably not the primary motivation for the stepping, 
anyway reporting progress was obviously considered by authors: 
sqlite3_backup_remaining() is useless except between steps.
:)

Thanks,
-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

Re: [sqlite] sqlite3 hangs on query

[Simon Slavin]

On 15 Feb 2017, at 6:53pm, Jens-Heiner Rechtien  wrote:

> Please try the following with the restored database:
> 
> The query
> *select * from albums, album_asset, cacheReferences, coreInfo, space limit 1;*
> will hang as well.
> 
> The query
> *select * from albums, album_asset, cacheReferences, coreInfo, coreMD5 limit 
> 1;*
> on the other hand runs very fast as only the first rows of each table are 
> concatenated. Run Time: real 0.005 user 0.000317 sys 0.000248.

Interesting.  I have verified that these all execute in the expected short 
times:

select * from albums, album_asset, cacheReferences, coreInfo limit 1;

select * from albums, album_asset, cacheReferences, coreInfo, coreMD5 limit 1;

select * from albums, album_asset, cacheReferences, space limit 1;

whereas this one appears to hang:

select * from albums, album_asset, cacheReferences, coreInfo, space limit 1;

It’s using 100% CPU time (on a multicore CPU).  Sampling suggests that the CLI 
is spending almost all its time doing paging and caching calls.

I’m using SQLite version 3.16.0 2016-11-04 19:09:39.

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

Re: [sqlite] .timer command missing from CLI ?

[Simon Slavin]

On 15 Feb 2017, at 7:17pm, Ben Newberg  wrote:

> Is that a homebrew version of 3.16.0?

Not in any way.  It’s one which comes with a very recent version of macOS:

180:Desktop simon$ which sqlite3
/usr/bin/sqlite3
180:Desktop simon$ uname -a
Darwin 180.192.187.81.in-addr.arpa 16.5.0 Darwin Kernel Version 16.5.0: Tue Jan 
31 18:57:20 PST 2017; root:xnu-3789.50.195.1.1~1/RELEASE_X86_64 x86_64

I am mystified why it doesn’t work.  I actually found the command in the .help 
display and copied and pasted it.  Tested to see whether it was the argument 
which was causing the problem.

sqlite> .changes on
sqlite> .timer on
Error: unknown command or invalid arguments:  "timer". Enter ".help" for help

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

Re: [sqlite] .timer command missing from CLI ?

[Ben Newberg]

Is that a homebrew version of 3.16.0?

SQLite version 3.16.0 2017-01-02 11:57:58
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite>

3.15.1 was released on 2016-11-04, but it works on that version too.

On Wed, Feb 15, 2017 at 1:09 PM, Simon Slavin  wrote:

> simon$ sqlite3 ~/Desktop/fred.sql
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
> sqlite> .timer off
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
>
>
> It’s still there in the .help command.  What happened ?  Has it been fixed
> in a later version ?
>
> 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] .timer command missing from CLI ?

[Richard Hipp]

On 2/15/17, Simon Slavin  wrote:
> simon$ sqlite3 ~/Desktop/fred.sql
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for

Unable to recreate the problem.

-- 
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] .timer command missing from CLI ?

[Simon Slavin]

simon$ sqlite3 ~/Desktop/fred.sql
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> .timer on
Error: unknown command or invalid arguments:  "timer". Enter ".help" for help
sqlite> .timer off
Error: unknown command or invalid arguments:  "timer". Enter ".help" for help


It’s still there in the .help command.  What happened ?  Has it been fixed in a 
later version ?

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

Re: [sqlite] sqlite3 hangs on query

[Jens-Heiner Rechtien]

Hi SQLite team,

sorry for replaying to my own email, the subscription to this list came 
only just through (got eaten by my SPAM folder), so I had to read your 
friendly replies via the list archive.


@Simon: thanks for pointing out that attachments do not work on the 
list. I should have known this. The dumped database (~800kB) in question 
can be found here:


https://dl.dropboxusercontent.com/u/92394185/db.dump.gz

I've checked the database for corruption with a PRAGMA integrity_check, 
it found nothing. Anyway the problem persists a dump and restore cycle, 
so I doubt that corruption plays a role here.


@Clemens, @Igor, @Richard and @Gunter: I know that the cross join with 
19 tables in itself is utter nonsense. The purpose is to name all tables 
in the database in one query - in the app there is special mechanism in 
place which intercepts queries, parses for the statement for table names 
and updates the tables from another data source upfront, before the 
query is executed. Naming all tables would force this update for whole 
database.


It turned out that the intercepting parser doesn't know about sql 
comments, so a simple "SELECT current_date -- names>" works just as well.


But the problem I reported here is genuine. The query doesn't take that 
long, the database isn't that sizeable. About 0.3s on my Mac if the 
select statement is shortened by the *spacefavorite* table.


Please try the following with the restored database:

The query
*select * from albums, album_asset, cacheReferences, coreInfo, space 
limit 1;*

will hang as well.

The query
*select * from albums, album_asset, cacheReferences, coreInfo, coreMD5 
limit 1;*
on the other hand runs very fast as only the first rows of each table 
are concatenated. Run Time: real 0.005 user 0.000317 sys 0.000248.


Note that the *space* table is empty, so the expected result of the 
cross join in the first query would be an empty result set. With a 
slightly different data set this is just what I get.


Thanks,
- Heiner



On 14/02/2017 13:33, Jens-Heiner Rechtien wrote:


Hi SQLite team,

please consider the attached dump of a sqlite3 database and the 
following - admittedly nonsensical, don't ask - query over a restored 
version of the database:


*SELECT count(*) FROM spacefavorite, album_asset, albums, 
assetProfileLinks, avatarCacheReferences, cacheReferences, comment, 
conflicts, coreInfo, coreMD5, errors, flags, importSource, 
missingBinariesOnOz, profileRegistration, quota_exceeded, 
renditionRevisions, space, space_album LIMIT 1;**

*

On iOS, MacOS and Linux this query will busy hang and never return. 
Happens with the command line tool and if used as a prepared statement 
in our iOS app. Changing certain aspects of the query will resolve the 
problem: leaving the table "spacefavorite" out of the query, or 
replacing count(*) with a just an asterisk.


I tried the following versions:

3.14.0, 3.16.2 (MacOSX Sierra), 3.13.0, 3.17.0 (Linux Fedora 24), 
3.14.0 (iOS 10), always the same behavior.


Many thanks for your consideration and especially for your great tool.

Heiner

--

Jens-Heiner Rechtien >

Computer Scientist

Adobe Systems Engineering GmbH

Große Elbstraße 27

22767 Hamburg


Registergericht: Hamburg HRB 745 37

Geschäftsführer: Michael D. Jamrosy, Christian Keim, Thomas Mührke, 
Keith San Felipe







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

Re: [sqlite] Seems that '-cmd .timer on' does not work in non interactive mode

[David Raymond]

I'm probably not the best to answer this since I don't know C, but the timing 
results are printed out in their own special thing.

The BEGIN_TIMER and END_TIMER functions only get used in the runOneSqlLine 
function, and inside the END_TIMER function is where the results actually get 
printed with a straight up printf that doesn't use any sort of shell callback. 
Any statements run through -cmd only get run through shell_exec there, without 
the BEGIN_TIMER or END_TIMER around them. I  then that simply adding 
those in before and after the shell_exec (line 6086 in shell.c) in the -cmd 
section will work, but I don't know for sure. Try it and let us know :)


--

/*
** Run a single line of SQL
*/
static int runOneSqlLine(ShellState *p, char *zSql, FILE *in, int startline){
...
  BEGIN_TIMER;
  rc = shell_exec(p->db, zSql, shell_callback, p, );
  END_TIMER;
...

--

/*
** Print the timing results.
*/
static void endTimer(void){
  if( enableTimer ){
sqlite3_int64 iEnd = timeOfDay();
struct rusage sEnd;
getrusage(RUSAGE_SELF, );
printf("Run Time: real %.3f user %f sys %f\n",
   (iEnd - iBegin)*0.001,
   timeDiff(_utime, _utime),
   timeDiff(_stime, _stime));
  }
}
#define END_TIMER endTimer()

--

...

}else if( strcmp(z,"-cmd")==0 ){
  /* Run commands that follow -cmd first and separately from commands
  ** that simply appear on the command-line.  This seems goofy.  It would
  ** be better if all commands ran in the order that they appear.  But
  ** we retain the goofy behavior for historical compatibility. */
...
open_db(, 0);
rc = shell_exec(data.db, z, shell_callback, , );
if( zErrMsg!=0 ){
...




-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Wednesday, February 15, 2017 7:05 AM
To: SQLite mailing list
Subject: [sqlite] Seems that '-cmd .timer on' does not work in non interactive 
mode

I want to do some timings. So I started with the following:
sqlite3 -cmd '.timer on' ~/Databases/general.sqlite '
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
WHERE  totaltime  < 99 OR totaltime > 101
;'

​This does display the query result, but not the runtime.

At the moment I just start sqlite with:
sqlite3 -cmd '.timer on' ~/Databases/general.sqlite

and then interactively enter:
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
WHERE  totaltime  < 99 OR totaltime > 101
;

I get beside the query result also:
Run Time: real 0.298 user 0.264000 sys 0.036000
​
​What is happening here?


I got around it with:
sqlite3 -batch -cmd '.timer on' ~/Databases/general.sqlite < 101
;
EOT
​
-- 
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] Why is this so much more efficient?

[Cecil Westerhof]

2017-02-15 14:18 GMT+01:00 Cecil Westerhof :

> The OR version is the least efficient and it look likes the BETWEEN
> version 2 is the most efficient. It looks like it uses less user and more
> sys.
>

​Which is the most efficient is also dependent on the state of the database
itself. I compacted the database and now sometimes NOT BETWEEN is more
efficient and sometimes NOT BETWEEN version 2 is more efficient.
The OR version is always the least efficient.

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

Re: [sqlite] WAL journal mode & sqlite3_backup_step()

[Clemens Ladisch]

Olivier Mascia wrote:
> A good approach ... is to drive the backup by a single call to 
> sqlite3_backup_step()

This is indeed what you should do with WAL.

> The only downside is that I loose the capability to monitor (or inform users 
> if needed) of the backup progress.

That was never the primary purpose of having multiple steps; it was intended to 
allow
concurrent read and write accesses (before WAL existed).

And the progress would look silly when it actually restarts ...


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

Re: [sqlite] Thread safety of serialized mode

[Jens Alfke]

> On Feb 15, 2017, at 3:44 AM, Cecil Westerhof  wrote:
> 
> ​As I said before: I did not work much with threads. Mostly for GUI
> performance. Do you (or anyone else) have any resources about those
> concurrency models​?


Theory:
https://en.wikipedia.org/wiki/Actor_model 

https://en.wikipedia.org/wiki/Communicating_sequential_processes 


also, the paper The Problem With Threads is definitely required reading!
https://www2.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf

Languages:
https://golang.org  — specifically ‘channels’, which are 
like generalized in-process streams or sockets.
https://www.rust-lang.org/  — Rust tracks memory 
ownership to enforce thread-safety at compile time.
http://www.ponylang.org  — Similar memory-safety to 
Rust, but adds garbage-collection and actors.
Other languages that support actors are Scala and Io.

You can build constructs like channels and actors on top of threads in other 
languages. I’m using actors in a C++ project right now; the C++ actor libraries 
I found were too heavyweight so I wrote my own. You do have to be careful 
(since C++ is basically one big double-edged razor blade) but it’s much easier 
than trying to work with mutexes and locks.

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

[sqlite] Fossil documentation edit

[don v nielsen]

On page http://fossil-scm.org/index.html/doc/trunk/www/quickstart.wiki 
it reads:


" To merge two branches back together, firstupdate 
to the branch you want to 
merge into.Then do amerge 
another branch that you 
want to incorporate the changes from."


Is it more appropriate for the second sentence to read: "Then do amerge 
[of] another branch..." or 
"Then merge another branch..."?


dvn

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

Re: [sqlite] Group contiguous rows (islands)

[E.Pasma]

15 feb 2017, Jean-Luc Hainaut:



You could try this, inspired by classic algorithms of temporal  
databases:


create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

create table TT(seq integer not null primary key autoincrement,date  
integer,test char(12));

insert into TT(date,test) select * from T order by date;

select T1.date, T3.date, T1.test
from   TT T1, TT T3
-- More efficient than "where  T1.date <= T3.date"
where  T1.seq <= T3.seq
andT1.test = T3.test
andnot exists(select * from TT where seq = T1.seq-1 and test =  
T1.test)
andnot exists(select * from TT where seq = T3.seq+1 and test =  
T3.test)

andnot exists(select *
from   TT T2
-- More efficient than "where  T2.date between  
T1.date and T3.date"

where  T2.seq between T1.seq and T3.seq
andT2.test <> T1.test);

Result:

+--+--+--+
| date | date | test |
+--+--+--+
| 1| 3| clim |
| 7| 10   | amb  |
| 12   | 12   | xxx  |
| 13   | 20   | clim |
| 22   | 25   | amb  |
+--+--+--+

Hello,  the query below is simpler. May be slower. But looks pretty  
relational. Thanks, E Pasma.


create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
select t.*, max(t2.date) as key2
from t
left join t t2
on t2.datet.test
group by t.date
)
group by key2
;


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

Re: [sqlite] Thread safety of serialized mode

[Jens Alfke]

> On Feb 14, 2017, at 11:58 PM, Clemens Ladisch  wrote:
> 
> But "go parallel" does not necessarily imply threads.  There are many
> ways to allow code running on different CPUs(/cores) to communicate
> with each other (e.g., files, sockets, message queues, pipes, shared
> memory, etc.), and almost all of them are safer than threading because
> they do not require that _all_ of the address space and the process
> context are shared.

Yes, but they’re also _much_ more expensive, for pretty much the same reason. A 
process context switch requires updating the MMU and a bunch of kernel state. A 
thread switch just requires swapping CPU registers. (Depending on the OS there 
may be a system call involved, but that can be avoided by using ‘green’ 
threads, which are basically just a wrapper around setjmp/longjmp.) There are 
several orders of magnitude of difference in performance (though the details 
depend on the CPU and the OS.)

>  When using threads, all memory accesses are unsafe
> by default, and it is then the job of the programmer to manually add
> some form of locking to make it safe again.

This depends on the language and/or the concurrency library. In a managed 
language it’s perfectly feasible to make it impossible for two threads to 
access the same memory (Rust and Pony do this.) In unmanaged code you can’t 
make strong guarantees, but you can get the same effect as long as the 
programmer doesn’t use unsafe techniques like global variables.

I think we have a problem with terminology. The issue here isn’t threads 
themselves, but with how threads communicate. In most languages, the default is 
that you use shared memory and some locking primitives. _That’s_ the nasty evil 
part. Alternative concurrency mechanisms like channels and actors still use 
threads under the hood; they just give the programmer safer and more 
deterministic ways to communicate.

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

Re: [sqlite] WAL journal mode & sqlite3_backup_step()

[Olivier Mascia]

> Le 15 févr. 2017 à 17:41, Olivier Mascia  a écrit :
> 
>> Le 15 févr. 2017 à 16:04, Olivier Mascia  a écrit :
>> 
>> Dear all,
>> 
>> https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep makes it 
>> clear that connections (other than the one used for the backup feature) 
>> which writes in between calls to sqlite3_backup_step() will force the next 
>> sqlite3_backup_step() to start again the whole copy processing.
>> 
>> What about databases set for journal_mode=WAL?
>> Is backup_step() also restarting the whole procedure after any write commit 
>> happen?
>> Or only if some checkpointing occurs?
>> 
>> Said differently, is the whole scheme of sqlite3_backup_* API meant to build 
>> a copy of the database, including whatever would be in the WAL journal, or 
>> is the goal to get a copy of what is seen by the implicit read transaction 
>> of the connection doing the backup?
> 
> Answering my own post, now that I have prepared and experimented with a test 
> program: backup_step() indeed restarts its work for *any* write occurring 
> during the backup, even though the test db is set for journal_mode=WAL and no 
> checkpointing occurs (verified) at the end of the commit of any of those test 
> writes.
> 
> Has anyone ever wanted the sqlite3_backup_* API to be able to proceed 
> differently, when journal_mode=WAL?
> Something along the lines of starting a transaction (deferred) on _init, 
> _step() copying the database as seen by this transaction, and _finish() 
> releasing that read-transaction?
> 
> It wouldn’t copy changes occurring after the backup/copy has started, of 
> course, but would copy a complete stable snapshot of the database, without 
> impacting (excepting the backup I/O) neither the readers or being 
> cancelled/restarted by the occasional writer? Passive checkpointing as done 
> by default by Sqlite3 wouldn’t even impact the process.
> 
> I for sure have a nice use case for this. :)

A good approach, which seems to run fine according to my additional tests is to 
drive the backup by a single call to sqlite3_backup_step() with a negative 
integer parameter.  This process all the database pages in one iteration, as 
described by the documentation.  Proceeding this way, I solve my primary need 
(using a journal_mode WAL database): making a backup of the database while it 
is possibly queried and updated by other connections.  Without any possibility 
for the backup to run endless due to restarts occurring too often.

I’ll use that scheme for now and it essentially kills the above proposal. The 
only downside is that I loose the capability to monitor (or inform users if 
needed) of the backup progress.  Some alternative sqlite3_backup_step() version 
could get a callback function (and a count of pages) and would call the 
callback function every such pages processed and one last time when complete. 
There might even be provision to allow the callback function to request 
premature termination of the copy, if needed.

int backup_callback(sqlite3_backup*, void* user_data);
Return value SQLITE_OK or SQLITE_ABORT (or any other convention) would allow 
the callback to request interruption or continuation.
Remaining and total pages could be obtained through the existing 
sqlite3_backup_remaining and sqlite3_backup_pagecount functions or they could 
be passed as parameters to the callback function as the callback probably has 
few interest if not to report or log on the progress of the task.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software




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

Re: [sqlite] OS X/Xcode build error: use of unknown builtin

[Anthony Chan (antchan2)]

Thank you for all the responses and a speedy resolution.  For complete-ness: I 
am using the older Xcode 6.4 – “Apple LLVM version 6.1.0 (clang-602.0.53) 
(based on LLVM 3.6.0svn)”.  

Now that the problem has been fixed in 3.17 branch I will use that.


On 2017-02-15, 8:20 AM, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:

On 2/14/17, Anthony Chan (antchan2)  wrote:
>
> I tried building SQLite 3.17.0 with OSX/Xcode and got the following 
errors:
>

Several possible fixes, any one of which will work:

(1) Update your Xcode to the latest from Apple

(2) Compile using the -DSQLITE_DISABLE_INTRINSIC compile-time option

(3) Use the latest SQLite code from trunk
(https://www.sqlite.org/src/info/trunk) or from branch-3.17
(https://www.sqlite.org/src/info/branch-3.17).  Click on one of the
"Tarball" or "ZIP Archive" links to download the complete source code.

(4) Apply the patch shown at
https://www.sqlite.org/src/vpatch?from=ada05cfa86ad7f56=8d3f485d86b2f2d8

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

Re: [sqlite] WAL journal mode & sqlite3_backup_step()

[Olivier Mascia]

> Le 15 févr. 2017 à 16:04, Olivier Mascia  a écrit :
> 
> Dear all,
> 
> https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep makes it 
> clear that connections (other than the one used for the backup feature) which 
> writes in between calls to sqlite3_backup_step() will force the next 
> sqlite3_backup_step() to start again the whole copy processing.
> 
> What about databases set for journal_mode=WAL?
> Is backup_step() also restarting the whole procedure after any write commit 
> happen?
> Or only if some checkpointing occurs?
> 
> Said differently, is the whole scheme of sqlite3_backup_* API meant to build 
> a copy of the database, including whatever would be in the WAL journal, or is 
> the goal to get a copy of what is seen by the implicit read transaction of 
> the connection doing the backup?

Answering my own post, now that I have prepared and experimented with a test 
program: backup_step() indeed restarts its work for *any* write occurring 
during the backup, even though the test db is set for journal_mode=WAL and no 
checkpointing occurs (verified) at the end of the commit of any of those test 
writes.

Has anyone ever wanted the sqlite3_backup_* API to be able to proceed 
differently, when journal_mode=WAL?
Something along the lines of starting a transaction (deferred) on _init, 
_step() copying the database as seen by this transaction, and _finish() 
releasing that read-transaction?

It wouldn’t copy changes occurring after the backup/copy has started, of 
course, but would copy a complete stable snapshot of the database, without 
impacting (excepting the backup I/O) neither the readers or being 
cancelled/restarted by the occasional writer? Passive checkpointing as done by 
default by Sqlite3 wouldn’t even impact the process.

I for sure have a nice use case for this. :)

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



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

Re: [sqlite] OS X/Xcode build error: use of unknown builtin

[Richard Hipp]

On 2/14/17, Anthony Chan (antchan2)  wrote:
>
> I tried building SQLite 3.17.0 with OSX/Xcode and got the following errors:
>

Several possible fixes, any one of which will work:

(1) Update your Xcode to the latest from Apple

(2) Compile using the -DSQLITE_DISABLE_INTRINSIC compile-time option

(3) Use the latest SQLite code from trunk
(https://www.sqlite.org/src/info/trunk) or from branch-3.17
(https://www.sqlite.org/src/info/branch-3.17).  Click on one of the
"Tarball" or "ZIP Archive" links to download the complete source code.

(4) Apply the patch shown at
https://www.sqlite.org/src/vpatch?from=ada05cfa86ad7f56=8d3f485d86b2f2d8

-- 
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] WAL journal mode & sqlite3_backup_step()

[Olivier Mascia]

Dear all,

https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep makes it 
clear that connections (other than the one used for the backup feature) which 
writes in between calls to sqlite3_backup_step() will force the next 
sqlite3_backup_step() to start again the whole copy processing.

What about databases set for journal_mode=WAL?
Is backup_step() also restarting the whole procedure after any write commit 
happen?
Or only if some checkpointing occurs?

Said differently, is the whole scheme of sqlite3_backup_* API meant to build a 
copy of the database, including whatever would be in the WAL journal, or is the 
goal to get a copy of what is seen by the implicit read transaction of the 
connection doing the backup?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

Re: [sqlite] Thread safety of serialized mode

[Keith Medcalf]

On Wednesday, 15 February, 2017 03:16, Cecil Westerhof  
said:

> 2017-02-15 8:58 GMT+01:00 Clemens Ladisch :
 
> > Jens Alfke wrote:
> > Threading is the most extreme method of achieving parallelism, and
> > therefore should be used only as the last resort.  (I'd compare it to
> > assembly code in this regard.)

> ​At the moment I am not using it much and I am certainly not an expert, but
> as I understood it one of the reasons to use threading is that it costs a
> lot less resources.

Which was very important a few years ago when Dynamic RAM cost more than a 
$1000 per megabyte, were having memory that could be measured in units bigger 
than Kilobytes meant you had a whopping expensive huge computer that probably 
cost more than your car, house, and yacht all added together.  Yet it was 
computationally as advanced as my wristwatch.

There is nothing wrong with multithreading.  Using "processes" is just 
multithreading -- with training wheels, belt, suspenders, diapers, and knee 
pads -- to prevent the foolish from, well, being foolish.  If you design your 
"multithreaded" program as if each thread were a separate process but without 
all the safety gear to prevent you from hurting yourself (and defeating Natural 
Selection in the process), you will have much less issue.

Note that for several modern OSes, the OS is nothing more than a discontiguous 
saved segment (DCSS) which is mapped into *every* process space and that 
process isolation is more of a myth than a reality.




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

Re: [sqlite] Thread safety of serialized mode

[Bob Friesenhahn]

On Tue, 14 Feb 2017, Jens Alfke wrote:



If we have two threads executing sqlite3_step() on the same connection and 
using their own prepared statement, is there any magic in sqlite3 which would 
keep sqlite3_step() and sqlite3_column_foo() from consuming (or disrupting) the 
results from the other thread?


Not if they’re using the same statement. A statement is a stateful 
object, so using it on multiple threads is probably going to cause 
problems.


To be clear, each thread is using its own prepared statement.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread safety of serialized mode

[Bob Friesenhahn]

On Tue, 14 Feb 2017, Richard Hipp wrote:


On 2/14/17, Bob Friesenhahn  wrote:

Due to memory constraints
(at least 1MB is consumed per connection!), only one database
connection is used.  Any thread may acquire and use this one database
connection at any time.


 This is yet another reason why I say "threads are evil".  For
whatever reason, programmers today think that "goto" and pointers and
assert() are the causes of all errors, but threads are cool and
healthful.  Entire programming languages are invited (I'm thinking of


Threads are a powerful tool but (like guns) they must be used very 
carefully.


In this particular case I think that the developer is making an 
assumption that more (partial) threading helps but with serialized 
access the database will still block and so perhaps it does not really 
help at all.



If we have two threads executing sqlite3_step() on the same connection
and using their own prepared statement, is there any magic in sqlite3
which would keep sqlite3_step() and sqlite3_column_foo() from
consuming (or disrupting) the results from the other thread?


Yes, that is suppose to work.  If you find a (reproducible) case where
it does not, we will look into it.


Thanks for this clarification.  It is quite possible that the bug 
is outside of sqlite.  The bug feels like a thread safety issue to me.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 3.17.0 fails to compile under OS X <= 10.10: use of unknown builtin

[Richard Hipp]

On 2/15/17, Marius Schamschula  wrote:
> Hi all,
>
> I’m the maintainer of sqlite3 for MacPorts. When I updated sqlite3 to
> version 3.17.0 on a machine running Sierra (10.12.3), everything went well.
> However, the next morning I woke up to the following ticket:
>
> https://trac.macports.org/ticket/53568
> 
>
> Upon further investigation, I found that sqlite3.c uses various workarounds,
> depending on the compiler version. Macs have defaulted to Apple’s clang
> since 10.6. However, Apple’s clang versions are not the same as LLVM’s.
>
> What happens is that CLANG_VERSION is set incorrectly, and sqlite3.c fails
> to compile under OS X 10.10 and all versions using clang below. The fact
> that the latest version of Xcode (8.2.1 available for macOS 10.11 and 10.12)
> built sqlite3 3.17.0 correctly is just dumb luck. This build failure is a
> serious issue for MacPorts as it is a blocker for numerous other ports
> depend on sqlite3.

Your work-around is to compile with -DSQLITE_DISABLE_INTRINSIC.

-- 
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] sqlite3 3.17.0 fails to compile under OS X <= 10.10: use of unknown builtin

[Marius Schamschula]

Hi all,

I’m the maintainer of sqlite3 for MacPorts. When I updated sqlite3 to version 
3.17.0 on a machine running Sierra (10.12.3), everything went well. However, 
the next morning I woke up to the following ticket:

https://trac.macports.org/ticket/53568 

Upon further investigation, I found that sqlite3.c uses various workarounds, 
depending on the compiler version. Macs have defaulted to Apple’s clang since 
10.6. However, Apple’s clang versions are not the same as LLVM’s.

What happens is that CLANG_VERSION is set incorrectly, and sqlite3.c fails to 
compile under OS X 10.10 and all versions using clang below. The fact that the 
latest version of Xcode (8.2.1 available for macOS 10.11 and 10.12) built 
sqlite3 3.17.0 correctly is just dumb luck. This build failure is a serious 
issue for MacPorts as it is a blocker for numerous other ports depend on 
sqlite3.

No surprisingly, my very old Leopard machine (10.5, using gcc-4.2) cleanly 
build sqlite 3.17.0.

Marius
--
Marius Schamschula



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

Re: [sqlite] Group contiguous rows (islands)

[Rossel, Jonathan]

@ Simon,

Thanks for the input ! I was afraid someone was going to mention the dreaded 
recursive CTEs.

Jonathan




***
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
***

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

Re: [sqlite] Why is this so much more efficient?

[Cecil Westerhof]

2017-02-15 13:40 GMT+01:00 Cecil Westerhof :

> I wrote the following Bash script:
>

​I wrote a better one. See end of post.

Inprinciple you are only interested in the totals. I also changed the runs
from 10 to 25.

The OR version is the least efficient and it look likes the BETWEEN version
2 is the most efficient. It looks like it uses less user and more sys.

The results of two runs:
Timing OR version
real: 7.345
user: 6.688
sys:  0.648


Timing ABS version
real: 6.403
user: 5.56
sys:  0.84


Timing NOT BETWEEN version
real: 6.382
user: 5.62
sys:  0.752


Timing NOT BETWEEN version 2
real: 6.291
user: 5.488
sys:  0.788

and:
Timing OR version
real: 7.228
user: 6.496
sys:  0.728


Timing ABS version
real: 6.647
user: 5.904
sys:  0.74


Timing NOT BETWEEN version
real: 6.431
user: 5.688
sys:  0.736


Timing NOT BETWEEN version 2
real: 6.263
user: 5.492
sys:  0.756


The script:
#!/usr/bin/env bash

set -o errexit
set -o nounset


declare -r COUNT=25
declare -r DISPLAY_ALL=F
declare -r SELECT='
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
'

declare -r ABS_VERSION="
${SELECT}
WHERE  ABS(100 - totaltime) > 1
;"
declare -r OR_VERSION="
${SELECT}
WHERE  totaltime  < 99 OR totaltime > 101
;"
declare -r NOT_BETWEEN_VERSION="
${SELECT}
WHERE totaltime NOT BETWEEN 99 AND 101
;"
declare -r NOT_BETWEEN_VERSION2="
${SELECT}
WHERE NOT (totaltime BETWEEN 99 AND 101)
;"


function timeQuery {
for i in $(seq ${COUNT}) ; do
sqlite3 ~/Databases/general.sqlite 

Re: [sqlite] Why is this so much more efficient?

[Cecil Westerhof]

2017-02-15 12:02 GMT+01:00 R Smith :

> Note however that this may not be entirely true. The Query might read data
> from the disk cache (or several memory caches may be in play) during the
> second run. Run each statement many times, and compare average return times.
>
> Also try this:
> ... WHERE totaltime NOT BETWEEN 99 AND 101;
> or
> ... WHERE NOT (totaltime BETWEEN 99 AND 101);
>

​I wrote the following Bash script:
#!/usr/bin/env bash

set -o errexit
set -o nounset


declare -r COUNT=10
declare -r SELECT='
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
'

declare -r ABS_VERSION="
${SELECT}
WHERE  ABS(100 - totaltime) > 1
;"
declare -r OR_VERSION="
${SELECT}
WHERE  totaltime  < 99 OR totaltime > 101
;"
declare -r NOT_BETWEEN_VERSION="
${SELECT}
WHERE totaltime NOT BETWEEN 99 AND 101
;"
declare -r NOT_BETWEEN_VERSION2="
${SELECT}
WHERE NOT (totaltime BETWEEN 99 AND 101)
;"


function timeQuery {
for i in $(seq ${COUNT}) ; do
sqlite3 ~/Databases/general.sqlite 

Re: [sqlite] Thread safety of serialized mode

[Simon Slavin]

On 15 Feb 2017, at 10:16am, Cecil Westerhof  wrote:

> 2017-02-15 8:58 GMT+01:00 Clemens Ladisch :
> 
>> Jens Alfke wrote:
>> Threading is the most extreme method of achieving parallelism, and
>> therefore should be used only as the last resort.  (I'd compare it to
>> assembly code in this regard.)
> 
> ​At the moment I am not using it much and I am certainly not an expert, but
> as I understood it one of the reasons to use threading is that it costs a
> lot less resources.

Compared with processes, yes.  Threads share stuff.  Processes have their own 
stuff.  Therefore threads are faster to start up and end (no resources to 
allocate or release) and don’t take any kind of resource space.

Two disadvantages are that threads are indistinguishable to anything but the 
owner and don’t know how to keep out of each-other’s way.  By the time you’ve 
devised some sort of mutex/locking/blocking mechanism you’re usually better-off 
using processes.

Graphics programs where you can assign one thread per pixel because each pixel 
has its own colour ?  Threads.
Database programs where everything has to access the same database file ?  
Processes.

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

Re: [sqlite] Group contiguous rows (islands)

[Rossel, Jonathan]

@ Clemens, Petite Abeille,

Thanks, that's what I thought, but it's comforting to know for sure...

@ Jean-Luc,

Thanks a lot for the detailed answer, that's awesome ! I'll give it a try and 
see how it compares with an external "manual" grouping


Jonathan 



--

Message: 79
Date: Wed, 15 Feb 2017 11:16:24 +0100
From: Clemens Ladisch 
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: 
Content-Type: text/plain; charset=us-ascii

Rossel, Jonathan wrote:
> Other database engines have solutions for this task (like windowing in
> postgre) but I wonder if there is an efficient recipe in SQLite.

SQLite does not have windowing functions.  So the most efficient method
would be to read the data with a simple ORDER BY, and do the grouping
in your code.


Regards,
Clemens


--

Message: 83
Date: Wed, 15 Feb 2017 12:02:32 +0100
From: Jean-Luc Hainaut 
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: <58a43548@unamur.be>
Content-Type: text/plain; charset=UTF-8; format=flowed


You could try this, inspired by classic algorithms of temporal databases:

create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

create table TT(seq integer not null primary key autoincrement,date 
integer,test char(12));
insert into TT(date,test) select * from T order by date;

select T1.date, T3.date, T1.test
from   TT T1, TT T3
-- More efficient than "where  T1.date <= T3.date"
where  T1.seq <= T3.seq
andT1.test = T3.test
andnot exists(select * from TT where seq = T1.seq-1 and test = T1.test)
andnot exists(select * from TT where seq = T3.seq+1 and test = T3.test)
andnot exists(select *
  from   TT T2
  -- More efficient than "where  T2.date between T1.date 
and T3.date"
  where  T2.seq between T1.seq and T3.seq
  andT2.test <> T1.test);

Result:

+--+--+--+
| date | date | test |
+--+--+--+
| 1| 3| clim |
| 7| 10   | amb  |
| 12   | 12   | xxx  |
| 13   | 20   | clim |
| 22   | 25   | amb  |
+--+--+--+

Working table TT is recommended to create an ordered sequence of rows in 
which "next" and "previous" rows are more easily described than in the 
source table. Avoid "order by" on views. It works in SQLite but it 
should not!

The idea is to identify maximal sequences of identical "test" values as 
follow:
- T1 denotes the first row of a sequence
- T3 the last row
- T2 any "disturbing" row lying between T1 and T3 but with a different 
value of "test"
- first "not exists" condition states that T1 must be the very first of 
the sequence: it must not be immediately preceded by a row with same 
value of "test"
- same for second "not exists" condition: T3 must be the last
- the third "not exists" condition states that there is no "disturbing" 
row between T1 and T3.

Valid if maximal sequences do not overlap. This query also detects 
single row sequences (e.g., 'xxx').
An index on TT.test may be useful to support T1*T3 join.

For large tables, an iterative procedure will be faster, though less 
elegant!

Regards

Jean-Luc Hainaut


--

Message: 89
Date: Wed, 15 Feb 2017 12:58:07 +0100
From: Petite Abeille 
To: SQLite mailing list 
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: <4b88b85b-75eb-4391-989e-198ebe31e...@gmail.com>
Content-Type: text/plain; charset=us-ascii


> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch  wrote:
> 
> SQLite does not have windowing functions.

A continuous/continual tragedy indeed :|

Still, worthwhile mentioning The Tabibitosan Method, for reference purpose:

http://www.orchestrapit.co.uk/?p=53
https://community.oracle.com/message/3991678

Rather nifty in its simplicity and power. Sadly, out of reach to SQLite 
dwellers.






--

Subject: Digest Footer

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


--

End of sqlite-users Digest, Vol 110, Issue 15
*
***
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any 

Re: [sqlite] Group contiguous rows (islands)

[Simon Slavin]

On 15 Feb 2017, at 11:58am, Petite Abeille  wrote:

> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch  wrote:
> 
>> SQLite does not have windowing functions.
> 
> A continuous/continual tragedy indeed :|

Windowing breaks the philosophy behind SQL.  Rows are meant to be members of a 
set, and your operations on them are meant to be set operations.  There is no 
implicit order for set elements.  That’s why bare-bones SQL implementations 
don’t have cursors or windowing.

> Still, worthwhile mentioning The Tabibitosan Method, for reference purpose:
> 
> http://www.orchestrapit.co.uk/?p=53
> https://community.oracle.com/message/3991678
> 
> Rather nifty in its simplicity and power. Sadly, out of reach to SQLite 
> dwellers.

Actually SQLite can do it, by iterating using recursive common table 
expressions:



.  It looks like an interesting programming exercise, though there’s a danger 
it will lead to unreadable code (a perpetual danger with WITH).  You would need 
a spare column in the table to store the 'group' values in.

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

Re: [sqlite] OS X/Xcode build error: use of unknown builtin

[Richard Hipp]

What does "clang -v" show on your machine?

On 2/15/17, Domingo Alvarez Duarte  wrote:
> Hello Anthony !
>
> I also got those errors and did a dirty change to sqlite3 to compile,
> your proposal makes an all or nothing use of builtins, probably a one by
> one check/enable could give better result.
>
> Cheers !
>
>
> On 14/02/17 21:38, Anthony Chan (antchan2) wrote:
>> Hello,
>>
>> I tried building SQLite 3.17.0 with OSX/Xcode and got the following
>> errors:
>>
>> -
>> sqlite3.c:28836:10: error: use of unknown builtin '__builtin_add_overflow'
>> [-Wimplicit-function-declaration]
>>return __builtin_add_overflow(*pA, iB, pA);
>>   ^
>> sqlite3.c:28856:10: error: use of unknown builtin '__builtin_sub_overflow'
>> [-Wimplicit-function-declaration]
>>return __builtin_sub_overflow(*pA, iB, pA);
>>   ^
>> sqlite3.c:28856:10: note: did you mean '__builtin_add_overflow'?
>> sqlite3.c:28836:10: note: '__builtin_add_overflow' declared here
>>return __builtin_add_overflow(*pA, iB, pA);
>>   ^
>> sqlite3.c:28871:10: error: use of unknown builtin '__builtin_mul_overflow'
>> [-Wimplicit-function-declaration]
>>return __builtin_mul_overflow(*pA, iB, pA);
>>   ^
>> sqlite3.c:28871:10: note: did you mean '__builtin_sub_overflow'?
>> sqlite3.c:28856:10: note: '__builtin_sub_overflow' declared here
>>return __builtin_sub_overflow(*pA, iB, pA);
>>   ^
>> 3 errors generated.
>> -
>>
>> I believe this is related to the recent change “Cleanup the usage of the
>> SQLITE_DISABLE_INTRINSIC compile-time option…”
>> (http://www.sqlite.org/src/info/798fb9d70d2e5f95) and the use of
>> CLANG_VERSION to decide whether to use builtin functions:
>>
>> #if defined(__clang__) && !defined(_WIN32) &&
>> !defined(SQLITE_DISABLE_INTRINSIC)
>> # define CLANG_VERSION \
>>
>> (__clang_major__*100+__clang_minor__*1000+__clang_patchlevel__)
>> #else
>> # define CLANG_VERSION 0
>> #endif
>>
>> …
>>
>> #elif SQLITE_BYTEORDER==1234 && (GCC_VERSION>=4003000 ||
>> CLANG_VERSION>=300)
>>u32 x;
>>memcpy(,p,4);
>>return __builtin_bswap32(x);
>>
>> According to Clang documentation
>> (http://clang.llvm.org/docs/LanguageExtensions.html): “marketing version
>> numbers should not be used to check for language features, as different
>> vendors use different numbering schemes. Instead, use the Feature Checking
>> Macros.”
>>
>> With this in mind, I suggest creating a new macro that uses feature
>> checking macros.  For example:
>>
>> #if defined(__clang__) && !defined(_WIN32) &&
>> !defined(SQLITE_DISABLE_INTRINSIC)
>> # if __has_builtin(__builtin_add_overflow) && \
>>   __has_builtin(__builtin_sub_overflow) && \
>>   __has_builtin(__builtin_mul_overflow) && \
>>   __has_builtin(__builtin_bswap32) && \
>>   __has_builtin(__builtin_bswap64)
>> #  define CLANG_USE_INTRINSIC 1
>> # else
>> #  define CLANG_USE_INTRINSIC 0
>> # endif
>> #else
>> # define CLANG_USE_INTRINSIC 0
>> #endif
>>
>> The tests would look like this:
>>
>> #elif SQLITE_BYTEORDER==1234 && (GCC_VERSION>=4003000 ||
>> CLANG_USE_INTRINSIC!=0)
>>u32 x;
>>memcpy(,p,4);
>>return __builtin_bswap32(x);
>>
>> Your comments are welcome.
>>
>> Thanks,
>>
>> Anthony
>> antch...@cisco.com
>>
>>
>>
>> ___
>> 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
>


-- 
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] Seems that '-cmd .timer on' does not work in non interactive mode

[Cecil Westerhof]

I want to do some timings. So I started with the following:
sqlite3 -cmd '.timer on' ~/Databases/general.sqlite '
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
WHERE  totaltime  < 99 OR totaltime > 101
;'

​This does display the query result, but not the runtime.

At the moment I just start sqlite with:
sqlite3 -cmd '.timer on' ~/Databases/general.sqlite

and then interactively enter:
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
WHERE  totaltime  < 99 OR totaltime > 101
;

I get beside the query result also:
Run Time: real 0.298 user 0.264000 sys 0.036000
​
​What is happening here?


I got around it with:
sqlite3 -batch -cmd '.timer on' ~/Databases/general.sqlite < 101
;
EOT
​
-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread safety of serialized mode

[ajm]

>  Mensaje original 
> De: Richard Hipp 
> Para:  SQLite mailing list 
> Fecha:  Tue, 14 Feb 2017 20:15:49 -0500
> Asunto:  Re: [sqlite] Thread safety of serialized mode
>>
> > On 2/14/17, Darren Duncan  wrote:
>>
> > There is nothing inherently wrong with threads in principle,

> Nor is there anything wrong with goto, pointers, and assert(), in
> principle.  And yet they are despised while threads are adored, in
> spite of the fact that goto/pointer/assert() errors are orders of
> magnitude easier to understand, find, and fix.
>
> -- 
> D. Richard Hipp

It seems that the problem of writing good multi-threaded programs lies in the 
limited ability of the human mind to think in parallel, which reminds me of the 
time when, after learning to use the unfortunate goto, they came to tell us 
that if we used them, the evil demon would come and burn our toenails, even 
though the compiler translates all those elegant programs to an enormous amount 
of jumps in the assembled code.

I suppose someday, programming languages can do an analogous translation in our 
limited but safe, sequential programs.

--
Adolfo J.M.

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

Re: [sqlite] Group contiguous rows (islands)

[Petite Abeille]

> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch  wrote:
> 
> SQLite does not have windowing functions.

A continuous/continual tragedy indeed :|

Still, worthwhile mentioning The Tabibitosan Method, for reference purpose:

http://www.orchestrapit.co.uk/?p=53
https://community.oracle.com/message/3991678

Rather nifty in its simplicity and power. Sadly, out of reach to SQLite 
dwellers.




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

Re: [sqlite] Thread safety of serialized mode

[Cecil Westerhof]

2017-02-15 12:40 GMT+01:00 Darren Duncan :

> Similarly with threads, for the vast majority of people, using other
> concurrency models with supported languages are better; they will still get
> the performance benefit of using multiple CPU cores but do it much more
> safely than if you are explicitly using "threads" in code.


​As I said before: I did not work much with threads. Mostly for GUI
performance. Do you (or anyone else) have any resources about those
concurrency models​?

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

Re: [sqlite] Thread safety of serialized mode

[Darren Duncan]

On 2017-02-15 2:40 AM, Clemens Ladisch wrote:

Cecil Westerhof wrote:

2017-02-15 8:58 GMT+01:00 Clemens Ladisch :

Threading is the most extreme method of achieving parallelism, and
therefore should be used only as the last resort.  (I'd compare it to
assembly code in this regard.)


​At the moment I am not using it much and I am certainly not an expert, but
as I understood it one of the reasons to use threading is that it costs a
lot less resources.


And just like with assembly code, you also have to count the time spent
writing it, and debugging the result.


Also, its a long time since hand-writing assembly code was any good for 
performance, unless you're a 1% top expert with a good reason.


If you want speed, write in C or something else that isn't assembly.  The odds 
are like 99% that the modern C compiler will generate faster code than you could 
ever write yourself in assembly, and it will be much less buggy.


Similarly with threads, for the vast majority of people, using other concurrency 
models with supported languages are better; they will still get the performance 
benefit of using multiple CPU cores but do it much more safely than if you are 
explicitly using "threads" in code.


-- Darren Duncan

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

Re: [sqlite] Why is this so much more efficient?

[Cecil Westerhof]

2017-02-15 12:02 GMT+01:00 R Smith :

>
> On 2017/02/15 12:33 PM, Cecil Westerhof wrote:
>
>> I have a table vmstat that I use to store vmstat info. ;-)
>> At the moment it has more as 661 thousand records.
>>
>> In principle the values of usertime, systemtime, idletime, waittime and
>> stolentime should add up to 100. I just wanted to check it. Of-course
>> there
>> could be a rounding error, so I wrote the following query:
>> SELECT date
>> ,  time
>> ,  usertime
>> ,  systemtime
>> ,  idletime
>> ,  waittime
>> ,  stolentime
>> ,  (usertime + systemtime + idletime + waittime + stolentime) AS
>> totaltime
>> FROM   vmstat
>> WHERE  totaltime  < 99 OR totaltime > 101
>>
>> I did not like that, so I rewrote the WHERE to:
>> WHERE  ABS(100 - totaltime) > 1
>>
>> The funny thing the second WHERE is more efficient as the first, where I
>> would have expected it to be the other way around.
>> The first takes around 1.050 milliseconds.
>> The second takes around  950 milliseconds.
>> So the second is around 10% more efficient. Why is this?
>>
>
> That's because the first one executes 2 checks mostly - it first checks to
> see if totaltime < 99, if so then it returns true, if not, then a second
> comparison has to be done... so 2 comparison functions on many items.
>
> The second check involves a single calculation and comparison - so what
> you have deduced is that the "minus" function is slightly more efficient
> than occasional extra comparison function.
>

​Minus function and abs function.​




> Note however that this may not be entirely true. The Query might read data
> from the disk cache (or several memory caches may be in play) during the
> second run. Run each statement many times, and compare average return times.
>

​I did. Not very much. But maybe I should try it a bit more often.​



Also try this:
> ... WHERE totaltime NOT BETWEEN 99 AND 101;
> or
> ... WHERE NOT (totaltime BETWEEN 99 AND 101);
>

​I will try those also.

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

Re: [sqlite] Storing a INTEGER in a TEXT field

[Cecil Westerhof]

2017-02-15 11:12 GMT+01:00 Cecil Westerhof :

> 2017-02-15 5:40 GMT+01:00 Darko Volaric :
>
>> The problem is that you're giving your column a type when you don't want
>> it
>> to have. If the second last line was "message NOT NULL" you'd get exactly
>> what you're asking for.
>>
>
> ​When the table was designed (many years ago) it was logical to make it a
> TEXT field, but a little over a month ago I began also storing data that is
> a count. I will convert the table and look what happens to the data.
>

​Just had to execute:
UPDATE messages
SETmessage = CAST(message AS INTEGER)
WHERE  type = 'download-count'


​I also use the table to store cpu temperature. (Which is a REAL.) I had to
change the code a bit, because it stored the temperature as a string.​

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

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

[Marek Wieckowski]

Hi James,

Thanks for your answer.

Yes, there are lots of different queries that you might want to write
_instead_ - in your example you have changed the logic of the update...

But you could also rewrite the original query to keep the original logic in
such a way that it would work in sqlite (see e.g. the stackoverflow page
http://stackoverflow.com/questions/39350537/sqlite-update-select-query-referencing-table-being-updated-in-order-by-clause/
- the simplest is to ... add an extra level of sub-select, select the value
you want to order by (so you use an "external" field in SELECT) and then
order by the value from sub-subselect... Super ugly and harder to
understand.).


The thing is that in principle there is nothing wrong with using test.xxx
fields in the subselect: there really should be no difference whether you
use them in "where" or "order by"... The fact that sqlite does not allow
them to be used in ORDER BY (while allowing in SELECT and WHERE) imho is
simply a bug. You don't want to force users to write ugly workarounds.

Oh, and btw: the same syntax (with using an external field in ORDER BY of a
subselect) simply works e.g. in update statements postgres.

Best,
Marek


On Tue, Feb 14, 2017 at 4:36 PM, James K. Lowden 
wrote:

> On Tue, 14 Feb 2017 15:06:16 +0100
> Marek Wieckowski  wrote:
>
> > UPDATE test
> > SET value = value + 100 *(
> > SELECT i.value
> > FROM test i
> > WHEREi.whereField = test.whereField
> > ORDER BY i.orderField = test.orderField
> > LIMIT 1
> > );
> >
> > Error: no such column: test.orderField
>
> There are other errors, too.  The update is nondeterministic and
> nonstandard.  Does this not serve the purpose better?
>
> UPDATE test
> SET value = value + 100 * (
>   SELECT min(i.value) -- or max, or something
>   FROM test i
>   WHEREi.whereField = test.whereField
>   );
>
> --jkl
> ___
> 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] Group contiguous rows (islands)

[Jean-Luc Hainaut]

You could try this, inspired by classic algorithms of temporal databases:

create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

create table TT(seq integer not null primary key autoincrement,date 
integer,test char(12));

insert into TT(date,test) select * from T order by date;

select T1.date, T3.date, T1.test
from   TT T1, TT T3
-- More efficient than "where  T1.date <= T3.date"
where  T1.seq <= T3.seq
andT1.test = T3.test
andnot exists(select * from TT where seq = T1.seq-1 and test = T1.test)
andnot exists(select * from TT where seq = T3.seq+1 and test = T3.test)
andnot exists(select *
 from   TT T2
 -- More efficient than "where  T2.date between T1.date 
and T3.date"

 where  T2.seq between T1.seq and T3.seq
 andT2.test <> T1.test);

Result:

+--+--+--+
| date | date | test |
+--+--+--+
| 1| 3| clim |
| 7| 10   | amb  |
| 12   | 12   | xxx  |
| 13   | 20   | clim |
| 22   | 25   | amb  |
+--+--+--+

Working table TT is recommended to create an ordered sequence of rows in 
which "next" and "previous" rows are more easily described than in the 
source table. Avoid "order by" on views. It works in SQLite but it 
should not!


The idea is to identify maximal sequences of identical "test" values as 
follow:

- T1 denotes the first row of a sequence
- T3 the last row
- T2 any "disturbing" row lying between T1 and T3 but with a different 
value of "test"
- first "not exists" condition states that T1 must be the very first of 
the sequence: it must not be immediately preceded by a row with same 
value of "test"

- same for second "not exists" condition: T3 must be the last
- the third "not exists" condition states that there is no "disturbing" 
row between T1 and T3.


Valid if maximal sequences do not overlap. This query also detects 
single row sequences (e.g., 'xxx').

An index on TT.test may be useful to support T1*T3 join.

For large tables, an iterative procedure will be faster, though less 
elegant!


Regards

Jean-Luc Hainaut

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

Re: [sqlite] Why is this so much more efficient?

[R Smith]

On 2017/02/15 12:33 PM, Cecil Westerhof wrote:

I have a table vmstat that I use to store vmstat info. ;-)
At the moment it has more as 661 thousand records.

In principle the values of usertime, systemtime, idletime, waittime and
stolentime should add up to 100. I just wanted to check it. Of-course there
could be a rounding error, so I wrote the following query:
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
WHERE  totaltime  < 99 OR totaltime > 101

I did not like that, so I rewrote the WHERE to:
WHERE  ABS(100 - totaltime) > 1

The funny thing the second WHERE is more efficient as the first, where I
would have expected it to be the other way around.
The first takes around 1.050 milliseconds.
The second takes around  950 milliseconds.
So the second is around 10% more efficient. Why is this?


That's because the first one executes 2 checks mostly - it first checks 
to see if totaltime < 99, if so then it returns true, if not, then a 
second comparison has to be done... so 2 comparison functions on many items.


The second check involves a single calculation and comparison - so what 
you have deduced is that the "minus" function is slightly more efficient 
than occasional extra comparison function.


Note however that this may not be entirely true. The Query might read 
data from the disk cache (or several memory caches may be in play) 
during the second run. Run each statement many times, and compare 
average return times.


Also try this:
... WHERE totaltime NOT BETWEEN 99 AND 101;
or
... WHERE NOT (totaltime BETWEEN 99 AND 101);

Cheers,
Ryan

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

Re: [sqlite] Thread safety of serialized mode

[Clemens Ladisch]

Cecil Westerhof wrote:
> 2017-02-15 8:58 GMT+01:00 Clemens Ladisch :
>> Threading is the most extreme method of achieving parallelism, and
>> therefore should be used only as the last resort.  (I'd compare it to
>> assembly code in this regard.)
>
> ​At the moment I am not using it much and I am certainly not an expert, but
> as I understood it one of the reasons to use threading is that it costs a
> lot less resources.

And just like with assembly code, you also have to count the time spent
writing it, and debugging the result.


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

[sqlite] Why is this so much more efficient?

[Cecil Westerhof]

I have a table vmstat that I use to store vmstat info. ;-)
At the moment it has more as 661 thousand records.

In principle the values of usertime, systemtime, idletime, waittime and
stolentime should add up to 100. I just wanted to check it. Of-course there
could be a rounding error, so I wrote the following query:
SELECT date
,  time
,  usertime
,  systemtime
,  idletime
,  waittime
,  stolentime
,  (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM   vmstat
WHERE  totaltime  < 99 OR totaltime > 101

I did not like that, so I rewrote the WHERE to:
WHERE  ABS(100 - totaltime) > 1

The funny thing the second WHERE is more efficient as the first, where I
would have expected it to be the other way around.
The first takes around 1.050 milliseconds.
The second takes around  950 milliseconds.
So the second is around 10% more efficient. Why is this?

​In case it is important: I did this in sqlitebrowser 3.7.0​, which uses
SQLite 3.8.10.2.

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

Re: [sqlite] Group contiguous rows (islands)

[Clemens Ladisch]

Rossel, Jonathan wrote:
> Other database engines have solutions for this task (like windowing in
> postgre) but I wonder if there is an efficient recipe in SQLite.

SQLite does not have windowing functions.  So the most efficient method
would be to read the data with a simple ORDER BY, and do the grouping
in your code.


Regards,
Clemens


> This e-mail message is intended only ...

This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in
those e-mails.  Additionally, confidentiality notices in those e-mails
will incur legal processing fees of $42 per line; you have agreed to
this by reading this confidentiality notice.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread safety of serialized mode

[Cecil Westerhof]

2017-02-15 8:58 GMT+01:00 Clemens Ladisch :

> Jens Alfke wrote:
> Threading is the most extreme method of achieving parallelism, and
> therefore should be used only as the last resort.  (I'd compare it to
> assembly code in this regard.)
>

​At the moment I am not using it much and I am certainly not an expert, but
as I understood it one of the reasons to use threading is that it costs a
lot less resources.

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

Re: [sqlite] Storing a INTEGER in a TEXT field

[Cecil Westerhof]

2017-02-15 5:40 GMT+01:00 Darko Volaric :

> The problem is that you're giving your column a type when you don't want it
> to have. If the second last line was "message NOT NULL" you'd get exactly
> what you're asking for.
>

​When the table was designed (many years ago) it was logical to make it a
TEXT field, but a little over a month ago I began also storing data that is
a count. I will convert the table and look what happens to the data.

The view is still handy, but I could drop the cast.

​Thank you for the idea.

​


> On Wed, Feb 15, 2017 at 1:22 AM, Cecil Westerhof 
> wrote:
>
> > I have the following table:
> > CREATE  TABLE messages(
> > messageID   INTEGER PRIMARY KEY AUTOINCREMENT,
> > dateTEXT NOT NULL DEFAULT CURRENT_DATE,
> > timeTEXT NOT NULL DEFAULT CURRENT_TIME,
> > typeTEXT NOT NULL,
> > message TEXT NOT NULL
> > );
> >
> > But for some data the field message is filled with an integer. An integer
> > takes less room as its text representation and it sorts differently also.
> > Is there a way to store an INTEGER in a TEXT field? Not very important,
> > more nice to have.
> >
> > I just created the following view:
> > CREATE VIEW downloadCount AS
> > SELECT   date AS Date
> > ,time AS Time
> > ,CAST(message AS INTEGER) AS DownloadCount
> > FROM messages
> > WHEREtype = 'download-count'
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] OS X/Xcode build error: use of unknown builtin

[Domingo Alvarez Duarte]

Hello Anthony !

I also got those errors and did a dirty change to sqlite3 to compile, 
your proposal makes an all or nothing use of builtins, probably a one by 
one check/enable could give better result.


Cheers !


On 14/02/17 21:38, Anthony Chan (antchan2) wrote:

Hello,

I tried building SQLite 3.17.0 with OSX/Xcode and got the following errors:

-
sqlite3.c:28836:10: error: use of unknown builtin '__builtin_add_overflow' 
[-Wimplicit-function-declaration]
   return __builtin_add_overflow(*pA, iB, pA);
  ^
sqlite3.c:28856:10: error: use of unknown builtin '__builtin_sub_overflow' 
[-Wimplicit-function-declaration]
   return __builtin_sub_overflow(*pA, iB, pA);
  ^
sqlite3.c:28856:10: note: did you mean '__builtin_add_overflow'?
sqlite3.c:28836:10: note: '__builtin_add_overflow' declared here
   return __builtin_add_overflow(*pA, iB, pA);
  ^
sqlite3.c:28871:10: error: use of unknown builtin '__builtin_mul_overflow' 
[-Wimplicit-function-declaration]
   return __builtin_mul_overflow(*pA, iB, pA);
  ^
sqlite3.c:28871:10: note: did you mean '__builtin_sub_overflow'?
sqlite3.c:28856:10: note: '__builtin_sub_overflow' declared here
   return __builtin_sub_overflow(*pA, iB, pA);
  ^
3 errors generated.
-

I believe this is related to the recent change “Cleanup the usage of the 
SQLITE_DISABLE_INTRINSIC compile-time option…” 
(http://www.sqlite.org/src/info/798fb9d70d2e5f95) and the use of CLANG_VERSION 
to decide whether to use builtin functions:

#if defined(__clang__) && !defined(_WIN32) && !defined(SQLITE_DISABLE_INTRINSIC)
# define CLANG_VERSION \
 (__clang_major__*100+__clang_minor__*1000+__clang_patchlevel__)
#else
# define CLANG_VERSION 0
#endif

…

#elif SQLITE_BYTEORDER==1234 && (GCC_VERSION>=4003000 || CLANG_VERSION>=300)
   u32 x;
   memcpy(,p,4);
   return __builtin_bswap32(x);

According to Clang documentation 
(http://clang.llvm.org/docs/LanguageExtensions.html): “marketing version 
numbers should not be used to check for language features, as different vendors 
use different numbering schemes. Instead, use the Feature Checking Macros.”

With this in mind, I suggest creating a new macro that uses feature checking 
macros.  For example:

#if defined(__clang__) && !defined(_WIN32) && !defined(SQLITE_DISABLE_INTRINSIC)
# if __has_builtin(__builtin_add_overflow) && \
  __has_builtin(__builtin_sub_overflow) && \
  __has_builtin(__builtin_mul_overflow) && \
  __has_builtin(__builtin_bswap32) && \
  __has_builtin(__builtin_bswap64)
#  define CLANG_USE_INTRINSIC 1
# else
#  define CLANG_USE_INTRINSIC 0
# endif
#else
# define CLANG_USE_INTRINSIC 0
#endif

The tests would look like this:

#elif SQLITE_BYTEORDER==1234 && (GCC_VERSION>=4003000 || CLANG_USE_INTRINSIC!=0)
   u32 x;
   memcpy(,p,4);
   return __builtin_bswap32(x);

Your comments are welcome.

Thanks,

Anthony
antch...@cisco.com



___
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] Group contiguous rows (islands)

[Rossel, Jonathan]

Dear all,

I need to perform a kind of partial GROUP BY to determine the beginnings and 
ends of sets of identical data. I can't use a full GROUP BY because these sets 
can be repeated and their repetition must be conserved. Other database engines 
have solutions for this task (like windowing in postgre) but I wonder if there 
is an efficient recipe in SQLite.

Example:
===

Table: mytable


date test
-- --
1   clim
3   clim
7   amb
10  amb
13  clim
15  clim
20  clim
22  amb
25  amb

Desired result
-

date_fromdate_totest
---   --
1 3  clim
7 10amb
13   15 clim
22   25 amb


(non optimal) solution found
=

CREATE VIEW mytablebydate
AS  -- Pre-order table to avoid ordering it twice in sub-queries
SELECT * FROM mytable ORDER BY date

CREATE VIEW mytablenext
AS
SELECT  date,
test,
(
-- first row > this row
SELECT date   -- NULL if not exists
FROM mytablebydate
WHERE date > MT.date
LIMIT 1
) as date_next,
(
-- first row > this row
SELECT test   -- NULL if not exists
FROM mytablebydate
WHERE date > MT.date
LIMIT 1
) as test_next  
FROM mytable MT
WHERE test != test_next

-- Get desired results
SELECT  (
--  Date of the previous row
SELECT MAX( date_next )
FROM mytablenext
WHERE date_next < mt.date
) AS date_from,

date AS date_to,   -- this row
test
FROM mytablenext mt


Comments


This method returns a Null for the first date_from and the last group is not 
returned. It is therefore incomplete. In addition, it involves quite a lot of 
subqueries. For completeness, it is inspired by 
http://stackoverflow.com/questions/30455227/date-range-for-set-of-same-data/30460263#30460263.
 So, is there a better / official way in SQLite ?

Any help will be welcome,

Jonathan

***
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
***
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 hangs on query

[Hick Gunter]

Maybe the original intent was to count all the rows in all the tables 
separately and return a vector of record counts, as a poor man's integrity 
check to make sure no rows got lost. Like

Select (select count() from t1) as t1,(select count() from t2) as t2, ...;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Dienstag, 14. Februar 2017 17:04
An: SQLite mailing list 
Betreff: Re: [sqlite] sqlite3 hangs on query

On 2/14/17, Igor Tandetnik  wrote:
> On 2/14/2017 7:33 AM, Jens-Heiner Rechtien wrote:
>> please consider the attached dump of a sqlite3 database and the
>> following - admittedly nonsensical, don't ask - query over a restored
>> version of the database:
>>
>> *SELECT count(*) FROM spacefavorite, album_asset, albums,
>> assetProfileLinks, avatarCacheReferences, cacheReferences, comment,
>> conflicts, coreInfo, coreMD5, errors, flags, importSource,
>> missingBinariesOnOz, profileRegistration, quota_exceeded,
>> renditionRevisions, space, space_album LIMIT 1;**
>> *
>
> You are asking SQLite to count an enormous number of rows, so don't be
> surprised if that takes an enormous amount of time.
>
> You have a cross-join of 19 tables. Even if each one contains just 2
> rows, that's 2^19 ~ 500K rows to work through. And it grows
> exponentially from there.

I suppose a query planner optimization is possible here.  SQLite could rewrite 
queries of the form:

SELECT count(*) FROM t1,t2,t3,t4,...,tN;

Into something like this:

   SELECT (SELECT count(*) FROM t1)*(SELECT count(*) FROM t2)*...*(SELECT 
count(*) FROM tN);

I say that it is possible to do this.  But it seems like a low-value 
optimization - just something to complicate testing and increase the library 
footprint without actually adding value.  So there is nothing like this on the 
To-Do list.
--
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


___
 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