Re: [sqlite] WAL and pragma uncommitted

ok thanks. So looks like I'm going to try WAL mode with one connection to the database per thread and accessing the database using SQLITE_OPEN_NOMUTEX., no shared-cache mode, no pragma read_uncommitted. Thanks for the advice. On 20 January 2018 at 19:49, Dan Kennedy wrote:

Re: [sqlite] Feature request for the Shell Tool: .mode json

On Mon, Jan 22, 2018 at 12:50 AM, Stadin, Benjamin < benjamin.sta...@heidelberg-mobil.com> wrote: > wrote a tool to convert an arbitrary SQLite result set to properly typed > json key/value pairs, using the SQLite type affinity of the objects. > ... > while ((rc = sqlite3_step(readStmt)) ==

Re: [sqlite] Unexpected column scoping in GROUP BY produces wrong answer.

On 01/21/2018 07:21 AM, petern wrote: SQLite 3.22.0 2018-01-12 23:38:10 dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS j FROM t GROUP BY j; j 4 8 7 --Wrong answer. --GROUP BY unexpectedly scopes outer source

Re: [sqlite] Feature request for the Shell Tool: .mode json

On Sun, 21 Jan 2018 05:54:13 + Simon Slavin escribió: > Feature request for the Shell Tool: ".mode json". > Others has pointed to libraries to export to json, so I point to the one I use: libucl https://github.com/vstakhov/libucl Using the generation functions [1]

Re: [sqlite] .DUMP displays floats differently from SELECT

I reported this same issue in May 2017 (http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html) I too consider this a problem (diffing dumps is one good reason why) but, unfortunately, this was an intentional change by this check-in: [7359fcac] Increase the

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.107.0 have begun...

If you have any issues with the current trunk code, please report them via this mailing list (and/or by creating a ticket on "https://system.data.sqlite.org/;) prior to Friday, January 26th. Thanks. -- Joe Mistachkin ___ sqlite-users mailing list

Re: [sqlite] .DUMP displays floats differently from SELECT

Hello, On 2018-01-22 21:33, Keith Medcalf wrote: The long and the short of it is that the output of .DUMP is correct. I would say that the output is ``too correct''. ``.dump'' displays 20 meaningful digits. Let's look at the 17 meaningful digits. Mantissa divides every exponential range

Re: [sqlite] .DUMP displays floats differently from SELECT

FYI. There's no need to pin back the whole db version just to get the old style dbdump format. The original dump is available from the distribution as a standalone program here: http://www.sqlite.org/src/artifact/819eb33f6ff788a4 --dbdump.c-- ** If this file is compiled with -DDBDUMP_STANDALONE

Re: [sqlite] Unexpected column scoping in GROUP BY produces wrong answer.

OK. The fact is still surprising considering the near column alias has precedence in every other situation [including the bug fix for CREATE TABLE ... AS SELECT] The SQLite documentation could use a sentence about how SQLite's enhanced SQL GROUP BY name precedence works.

[sqlite] BEGIN IMMEDIATE fails with "cannot start a transaction within a transaction (1)" without nested transsactions

Hello, we have a situation where "BEGIN IMMEDIATE" unexpectedly returns SQLITE_ERROR with message "cannot start a transaction within a transaction (1)". We have multiple threads running which repeatedly do the following: BEGIN IMMEDIATE INSERT ... INSERT ... COMMIT This

Re: [sqlite] Can this be done with SQLite

Part of the problem is going to be that you have not defined the problem sufficiently for a "solution" to be proposed. Based on your somewhat silly example one can deduce the following constraints: With respect to "key": - this is TEXT (UTF-8 or something else)? - you specify

[sqlite] Check if SQLite Databases Are Locked

I am using a lot of SQLite databases. The problem is that I sometimes do things in a DB browser, but do not write or revert changes. This can give problems with my cron scripts that use the locked database. Because of this I wrote a script that accept a series of databases as argument and check

Re: [sqlite] Can this be done with SQLite

2018-01-23 1:02 GMT+01:00 Keith Medcalf : > > Part of the problem is going to be that you have not defined the problem > sufficiently for a "solution" to be proposed. Based on your somewhat silly > example one can deduce the following constraints: > > With respect to "key":

Re: [sqlite] Can this be done with SQLite

Completely generic solution. You change the direction of rotation by changing the SQL that builds your temp table ... sqlite> create table x (key integer primary key, value); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert

Re: [sqlite] SQLite version 3.22.0

On 22 Jan 2018, at 21:21, Nnaemeka R Egudu wrote: > Please unsubscribe me from this mailing list. > Thanks. Do it yourself using this URL: > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Cheers -- Tim

Re: [sqlite] Can this be done with SQLite

On 1/22/2018 4:36 PM, Cecil Westerhof wrote: When I do this, I get: sqlite> SELECT * ...> FROM playYouTubeVideo ...> WHERE key BETWEEN '1' AND '5' ...> ; 1|1.0 2|2.0 3|3.0 4|4.0 5|5.0 [snip] sqlite> SELECT * ...> FROM playYouTubeVideo ...> WHERE key BETWEEN '1' AND

Re: [sqlite] Can this be done with SQLite

Cecil, you need to make a backup of the values before they are altered, and then alter them from the backup. That way, by the time you have come full circle, you are not working with the changed values. On Mon, Jan 22, 2018 at 4:11 PM, Cecil Westerhof wrote: >

Re: [sqlite] Can this be done with SQLite

2018-01-22 23:15 GMT+01:00 David Raymond : > Ok, so you're looking for a "rotate" sort of thing? > ​Yes. ;-) ​ > (The schema with a text key with length of 1 made me think it wasn't going > to get too big) > ​In this particular case it is a string with length 1, but

Re: [sqlite] .DUMP displays floats differently from SELECT

When you use a "select" from the shell to output a value converted to text, it outputs the value "doctored up" (coddled) for display to humans rather than display the true (as in actual) floating point value. This is because "most people" do not understand how computers (binary floating point

Re: [sqlite] Can this be done with SQLite

Unless I'm reading you wrong then just do the normal begin transaction; update playYouTubeVideo set speed = ( select speed from playYouTubeVideo where key = '2') where key = '1'; update playYouTubeVideo set speed = ( select speed from playYouTubeVideo where key = '3') where key =

Re: [sqlite] [sqlite-announce] SQLite version 3.22.0

Please unsubscribe me from this mailing list. Thanks. -Original Message- From: sqlite-announce [mailto:sqlite-announce-boun...@sqlite.org] On Behalf Of D. Richard Hipp Sent: Monday, January 22, 2018 3:48 PM To: sqlite-annou...@mailinglists.sqlite.org Subject: [sqlite-announce] SQLite

Re: [sqlite] Can this be done with SQLite

2018-01-22 23:07 GMT+01:00 Igor Tandetnik : > On 1/22/2018 4:36 PM, Cecil Westerhof wrote: > >> >> When I do this, I get: >> sqlite> SELECT * >> ...> FROM playYouTubeVideo >> ...> WHERE key BETWEEN '1' AND '5' >> ...> ; >> 1|1.0 >> 2|2.0 >> 3|3.0 >> 4|4.0 >>

[sqlite] FW: [sqlite-announce] SQLite version 3.22.0

You guys put into the default CLI the Ctrl-C stopping the statement but staying in the session behavior. Sweet! -Original Message- From: sqlite-announce [mailto:sqlite-announce-boun...@sqlite.org] On Behalf Of D.Richard Hipp Sent: Monday, January 22, 2018 3:48 PM To:

Re: [sqlite] Can this be done with SQLite

Two options (one based on Igor's answer) update playYouTubVideo set speed = case when key = 1 then (select speed from playYouTubeVIdeo when key = 2) when key = 2 then (select ... when key = 3) ... when key = 5 then (select ...

Re: [sqlite] Can this be done with SQLite

Ok, so you're looking for a "rotate" sort of thing? (The schema with a text key with length of 1 made me think it wasn't going to get too big) Are the keys all integers then? All positive? Continuous? begin transaction; create temp table t (key int primary key, speed real); insert into t

[sqlite] Can this be done with SQLite

I have the following table: CREATE TABLE playYouTubeVideo ( key TEXTNOT NULL, speed FLOAT NOT NULL, CONSTRAINT key CHECK(length(key) == 1), CONSTRAINT speed CHECK(TYPEOF(speed) = "real"), PRIMARY KEY(key) ); Say I want to rotate a part: - The value by key '1'

Re: [sqlite] Can this be done with SQLite

Wouldn't the mod operator do this? Do an update and set key = 1 + (5 + key)%5 On 1/22/2018 12:38 PM, David Raymond wrote: Unless I'm reading you wrong then just do the normal begin transaction; update playYouTubeVideo set speed = ( select speed from playYouTubeVideo where key = '2')

Re: [sqlite] Can this be done with SQLite

2018-01-22 21:38 GMT+01:00 David Raymond : > Unless I'm reading you wrong then just do the normal > > begin transaction; > update playYouTubeVideo set speed = ( > select speed from playYouTubeVideo where key = '2') > where key = '1'; > update playYouTubeVideo set

Re: [sqlite] Can this be done with SQLite

2018-01-22 22:36 GMT+01:00 Jim Morris : > Wouldn't the mod operator do this? > > Do an update and set key = 1 + (5 + key)%5 ​Only when the values are: 1.0, 2.0, 3.0, 4.0 and 5.0. But not when they are: 1.25, 1.5, 1.75, 2.0 and 1.0. On 1/22/2018 12:38 PM, David Raymond

Re: [sqlite] Can this be done with SQLite

If you want it fast even for huge tables then force the creation of an index on the temporary rotate table: begin immediate; create temporary table rotate as select oldkey, newkey, (select value from x where key = newkey) as value from (select key as oldkey,

[sqlite] Minor JSON output bug (infinity)

Test-case / repro: “SELECT JSON_ARRAY(1e,-1e,NULL)” Actual output: ‘[Inf,-Inf,null]’ Expected output: ‘[Infinity,-Infinity,null]’ All JSON parsers I have tried fail on “Inf”, but the majority will succeed with “Infinity” (as this is the standard JS property name) eg: in standard

Re: [sqlite] Minor JSON output bug (infinity)

On 1/22/18, Alexander Beedie wrote: > Test-case / repro: > “SELECT JSON_ARRAY(1e,-1e,NULL)” > > Actual output: > ‘[Inf,-Inf,null]’ > > Expected output: > ‘[Infinity,-Infinity,null]’ > > All JSON parsers I have tried fail on “Inf”, but the majority will

Re: [sqlite] Minor JSON output bug (infinity)

RFC 8259 states: > Numeric values that cannot be represented in the grammar below (such as > Infinity and NaN) are not permitted. This is one of the cases that expose the fallacy of the "JS" part of "JSON". While SQLite should not be producing Inf as a bare word, it should not be producing

Re: [sqlite] Minor JSON output bug (infinity)

On Mon, Jan 22, 2018 at 6:00 PM, Alexander Beedie < alexander.m.bee...@gmail.com> wrote: > Test-case / repro: > “SELECT JSON_ARRAY(1e,-1e,NULL)” > > Actual output: > ‘[Inf,-Inf,null]’ > > Expected output: > ‘[Infinity,-Infinity,null]’ > > All JSON parsers I have tried fail on “Inf”, but

Re: [sqlite] .DUMP displays floats differently from SELECT

FYI. There's no need to pin the whole db version back to get original dbdump formatting. The original distribution standalone dump is still available in its full glory here: http://www.sqlite.org/src/artifact/819eb33f6ff788a4 --dbdump.c-- ** If this file is compiled with -DDBDUMP_STANDALONE

Re: [sqlite] sqlite command line tool fails to dump data

On Sun, Jan 21, 2018 at 11:48 PM, Clemens Ladisch wrote: > J Decker wrote: > >> *If any NUL characters occur at byte| offsets less than the value of the > >> fourth parameter then the resulting| string value will contain embedded > >> NULs.* > > > > So it's best used as data,

Re: [sqlite] .DUMP displays floats differently from SELECT

Hi, This is clearly a bug, as it outputs incorrect and different output from the previous versions. I have a "REAL" column with float values having up to 2 decimals, and using ".dump" with the latest version incorrectly converts them like this: "0.0" -> "0.0" "0.05" -> "0.050002775"

Re: [sqlite] sqlite command line tool fails to dump data

On 22 Jan 2018, at 4:46pm, J Decker wrote: > create table [with\0nul] ( `col\0``umn` ) Could you not ? In fact, could everybody not ? [Goes to look for ice bag or strong alcohol, whichever appears first.] Simon. ___ sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

While maybe different formatting, how is that wrong? Remember, if you've declared the columns to be of type real then they're stored as an 8 byte binary floating point number. There is no exact representation of .05 in binary: so it's giving you all the digits that are stored. See #16 in the

Re: [sqlite] .DUMP displays floats differently from SELECT

There was work to 'speed up float formatting' which although fast is apparently inaccurate. One I Was working on was pretty fast, but didn't format things correctly because of minor decimals. On Mon, Jan 22, 2018 at 9:14 AM, David Raymond wrote: > While maybe different