Re: [sqlite] Error handling

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

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

Re: [sqlite] Error handling

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

[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

Re: [sqlite] 3.17.0 does not read updated DB

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

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

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

Re: [sqlite] Group contiguous rows (islands)

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

Re: [sqlite] Thread safety of serialized mode

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

[sqlite] Why is this so much more efficient?

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

Re: [sqlite] Thread safety of serialized mode

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

Re: [sqlite] Thread safety of serialized mode

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

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

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

Re: [sqlite] Thread safety of serialized mode

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 >

Re: [sqlite] Group contiguous rows (islands)

> 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

Re: [sqlite] Thread safety of serialized mode

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

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

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

Re: [sqlite] Group contiguous rows (islands)

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

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

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,

[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

Re: [sqlite] Thread safety of serialized mode

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

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

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.

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

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

Re: [sqlite] Group contiguous rows (islands)

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.

Re: [sqlite] Group contiguous rows (islands)

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

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

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

Re: [sqlite] sqlite3 hangs on query

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

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

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

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

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

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

Re: [sqlite] Group contiguous rows (islands)

@ 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

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

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

Re: [sqlite] Error handling

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

Re: [sqlite] Error handling

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

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

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,

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'),

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

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

Re: [sqlite] sqlite3 hangs on query

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

[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

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

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

> 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

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

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

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

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

[sqlite] Fossil documentation edit

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

Re: [sqlite] Thread safety of serialized mode

> 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

Re: [sqlite] Group contiguous rows (islands)

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');

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

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

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

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

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

> 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

Re: [sqlite] Thread safety of serialized mode

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

Re: [sqlite] Thread safety of serialized mode

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

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

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]

[sqlite] Documentation error

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.

Re: [sqlite] sqlite3 hangs on query

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

Re: [sqlite] Documentation error

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

Re: [sqlite] sqlite3 hangs on query

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,

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

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

Re: [sqlite] Group contiguous rows (islands)

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'),

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

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

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

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

Re: [sqlite] sqlite3 hangs on query

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

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

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

[sqlite] .timer command missing from CLI ?

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

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

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.

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

> 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

Re: [sqlite] sqlite3 hangs on query

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

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

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

[sqlite] Error handling

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 )

Re: [sqlite] Error handling

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

Re: [sqlite] Thread safety of serialized mode

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.

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

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

Re: [sqlite] Thread safety of serialized mode

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

Re: [sqlite] Thread safety of serialized mode

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

[sqlite] WAL journal mode & sqlite3_backup_step()

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