Re: [sqlite] WAL and pragma uncommitted

[Hannah Massey]

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:

> On 01/19/2018 11:26 PM, Hannah Massey wrote:
>
>> Currently we access a single SQLite database in a single thread but I am
>> working on changing this as performance has become a real problem. We will
>> be using WAL mode and there will be one thread for writes and multiple
>> threads for reads. For many cases, speed will be of a priority and it will
>> not matter if the data returned from a read is slightly out of date so I
>> can considering using #pragma uncommitted in some of the reader threads.
>> Will #pragma uncommitted work in WAL mode and will it have the effect I'm
>> looking for (where the read will be faster because it can ignore the
>> recently written information in the WAL File) and simply use the database
>> file only?
>>
>
> Don't use "PRAGMA read_uncommitted". It is a no-op unless you turn on
> shared-cache mode. And using shared-cache mode reduces the concurrency
> provided by using wal mode.
>
> Dan.
>
>
>
> ___
> 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] Feature request for the Shell Tool: .mode json

[Dominique Devienne]

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)) == SQLITE_ROW) {
> ...

for (int colIdx=0; colIdx ...

sqlite3_value *val = sqlite3_column_value(readStmt, colIdx);
> switch (int type = sqlite3_value_type(val)) {
> ...

}
>

Since it's based on on sqlite3_value_type(), that's not "type affinity"
exactly,
just the actual "storage" type of the value. AFAIK there's no way to know
the
affinity [1] of a column of a table using an SQLite API. Perhaps there's a
pragma?

There's sqlite3_value_numeric_type() which allows you to emulate SQLite's
numeric affinity, but that's not the same. FWIW. --DD

[1] https://www.sqlite.org/datatype3.html#type_affinity
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

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 table column j rather than the
nearer local column alias j.

WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS x FROM t
GROUP BY x;
x
3
4
7
8
--Correct answer when the local column alias happens to be unique.


I think technically allowing an expression alias to be used in the GROUP 
BY clause like that is not standard SQL. It just has to be supported for 
backwards-compatibility. So SQLite tries to process the standard as 
regular SQL before falling back to interpreting the identifier as an alias.


Dan.


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

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

[Eduardo]

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] you can convert from C structs and types to 
ucl and
export to any suportted formats, json, compact json, yaml and nginx like config 
files.

Licence BSD 2-clause "Simplified" License

> Simon.

[1] 
https://github.com/vstakhov/libucl/blob/master/doc/api.md#generation-functions-1

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

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

[Tony Papadimitriou]

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 number of significant digits in floating point 
literals on ".dump" output from the shell.


Tony
-Original Message- 
From: Iulian Onofrei


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.05" -> "0.050002775"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei

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

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

[Joe Mistachkin]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Cezary H. Noweta]

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 (i.e. range , which is divided linearly) 
into 1x2^52 equilengthy segments (binary/double) or 9x10^16 equilengthy 
segments (decimal/17). The segment represents a rational number.


Every decimal exponential range overlaps 4 or 5 binary exponential 
ranges. Let's consider decimal exponential range <0.1; 1> -- 17 digits 
mantissa gives a density of (9x10^16)/(9x10^-1)=10^17 numbers per one.


Let's take the densest binary exponential range which overlaps the above 
mentioned decimal one: <1/16;1/8> -- 53 digits binary mantissa gives a 
density of (1x2^52)/(1x2^-4)=2^56=7.2...x10^16<10^17 numbers per one.


Let's find a pair of binary/decimal exponential ranges which overlap 
each other and a ratio of binary numbers density to decimal numbers 
density is highest possible (if greater then one, then same binary 
numbers in the range cannot be represented by 17 digits decimal float).


Believe me or not, (for doubles) the ranges are: decimal: 
<10^-146;10^-145> -- density: (9x10^16)/(9x10^-146)=10^162 numbers per 
one; binary: <2^-486;2^-485> -- density: 
(1x2^52)/(1x2^-486)=2^538=8.99..x10^161<10^162 numbers per one. The 
ratio < 1. So 17 decimal digits is enough for an exact representation of 
IEEE64 float.


I suppose that 20 digits is taken from the fact: 64ln(2)/ln(10)=19.2..., 
however, for 64bit mantissa (long double) it is not enough (to be 
represented exactly), for 53bit mantissa it is too many. Besides that, 
17 digits + one rounding digit fit in i64, while 20 decimal digits do not.


The error of conversion depends on the width of intermediate floats -- 
not on the width of a integral part. Further increasing of number of 
meaningful digits does nothing. It is important to use ``long double'' 
for multiplication. Previous (<= 3.21) implementation of ``AtoF()'' 
would loose 6 bits (40+ multiplications in the worst case), if it was 
compiled with a compiler which mapped ``long double'' to ``double''. 
Integer had 18/19 digits -- it did not helped.


For example, let's look at the first OP's number: 0.05 -- it is 
1.Ax2^-5 => 0.050003 is enough for an exact 
conversion. Neighboring numbers are: 1.9x2^-5 => 
0.049996, and 1.Bx2^-5 => 0.050010.


17 digits is also enough for the densest range 
(<2^-486;2^-485>n<10^-146;10^-145>):
1.FF999x2^-486 => 1.0002594838824945E-146, 
1.FF99Ax2^-486 => 1.0002594838824946E-146, 
1.FF99Bx2^-486 => 1.0002594838824947E-146.


-- best regards

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

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

[petern]

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 then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Compile and enjoy.
Peter


On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou  wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/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 number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -Original Message- From: Iulian Onofrei
>
> 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.05" -> "0.050002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> ___
> 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] Unexpected column scoping in GROUP BY produces wrong answer.

[petern]

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.

https://www.postgresql.org/docs/9.5/static/sql-select.html
"In case of ambiguity, a GROUP BY name will be interpreted as an
input-column name rather than an output-column name."

Peter

On Mon, Jan 22, 2018 at 3:07 AM, Dan Kennedy  wrote:

> 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 table column j rather than the
>> nearer local column alias j.
>>
>> WITH t(j,k) AS (VALUES (2,4),(3,2),(3,8),(4,7)) SELECT max(j,k) AS x FROM
>> t
>> GROUP BY x;
>> x
>> 3
>> 4
>> 7
>> 8
>> --Correct answer when the local column alias happens to be unique.
>>
>
> I think technically allowing an expression alias to be used in the GROUP
> BY clause like that is not standard SQL. It just has to be supported for
> backwards-compatibility. So SQLite tries to process the standard as regular
> SQL before falling back to interpreting the identifier as an alias.
>
> Dan.
>
>
> ___
> 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] BEGIN IMMEDIATE fails with "cannot start a transaction within a transaction (1)" without nested transsactions

[Detlef Golze]

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 means each thread is using its own connection so that no synchronization 
is required nor performed.

Sometimes "BEGIN IMMEDIATE" fails with SQLITE_ERROR instead of the expected 
SQLITE_BUSY.

The behavior is present since 3.17 up to latest 3.22. We did not see this with 
earlier versions.

We are running on Windows x64 and SQLite is built from amalgamation with 
SQLITE_THREADSAFE set to 2.

I am posting to get a confirmation that my understanding is correct and that 
"BEGIN IMMEDIATE" should not return this error in this situation. In that case 
we can also try to narrow down the change in 3.17 which causes the failure.

Thank you,
Detlef.


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

Re: [sqlite] Can this be done with SQLite

[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":
 - this is TEXT (UTF-8 or something else)?
 - you specify check(length(key)) == 1  do you mean:
   - one character in some encoding (key between 0 and 0x)
   - one byte? (ie, ord(key) between 0 and 255)
   - something else entirely?
   - is it contiguous?
   - if not contiguous what are the rules defining the non-contiguousness?
 
 - what is the "rotation order" based on?
   - next arithmetic value, upper wraps to lower?
   - next used key (by some collation order?  Which collation order?)
   - based on "used" values?
   - based on "entire domain"?

The problem and its solution is rather simple, once you define problem to be 
solved with sufficient specificity to permit a solution.  

Your "example" below does not provide sufficient referents to generate a 
solution that is cohesive over any problem domain other than that covered by 
the example, and your referential constraints are inadequate to ensure 
integrity for your limited example.

Your example could be used to generate a solution set if and only if you 
changed the constraints such that:

  check(key) between '1' and '5'
and
  count(key) must be 5 (ie, there must be five records comprising exactly each 
key once).

Otherwise there is insufficient information to formulate a solution.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
>Sent: Monday, 22 January, 2018 13:30
>To: SQLite mailing list
>Subject: [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' becomes the value by key '2'.
>​- The value by key '2' becomes the value by key '3'.
>​- The value by key '3' becomes the value by key '4'.
>- The value by key '4' becomes the value by key '5'.
>- The value by key '5' becomes the value by key '6'.
>
>​I suppose that I need to do this programmatically, or can this be
>done
>with SQL?
>
>And optionally also:
>​- The value by key '1' becomes the value by key '5'.
>​
>--
>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

[sqlite] Check if SQLite Databases Are Locked

[Cecil Westerhof]

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 all those databases for being locked and print a line
for the databases that are locked. So when no databases are locked, there
is no output.

I have a cron entry that runs this script twice an hour, so I will be
notified if I have to unlock a database.

If you are interested you can find the script here:
https://wiki.tcl.tk/54781

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

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

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":
>  - this is TEXT (UTF-8 or something else)?
>  - you specify check(length(key)) == 1  do you mean:
>- one character in some encoding (key between 0 and 0x)
>- one byte? (ie, ord(key) between 0 and 255)
>- something else entirely?
>- is it contiguous?
>- if not contiguous what are the rules defining the non-contiguousness?
>

​I would like a general solution. So the type of key is not defined and it
is not necessary to be contiguous.
​



>  - what is the "rotation order" based on?
>- next arithmetic value, upper wraps to lower?
>- next used key (by some collation order?  Which collation order?)
>- based on "used" values?
>- based on "entire domain"?
>

​Rotation is either up or down. In my example it was up. (In my perception.)

Order is just the default order.
​



> The problem and its solution is rather simple, once you define problem to
> be solved with sufficient specificity to permit a solution.
>
> Your "example" below does not provide sufficient referents to generate a
> solution that is cohesive over any problem domain other than that covered
> by the example, and your referential constraints are inadequate to ensure
> integrity for your limited example.
>

​I think I can solve it generally. I will look into it and share it here.
​



> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
> >Sent: Monday, 22 January, 2018 13:30
> >To: SQLite mailing list
> >Subject: [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' becomes the value by key '2'.
> >​- The value by key '2' becomes the value by key '3'.
> >​- The value by key '3' becomes the value by key '4'.
> >- The value by key '4' becomes the value by key '5'.
> >- The value by key '5' becomes the value by key '6'.
> >
> >​I suppose that I need to do this programmatically, or can this be
> >done
> >with SQL?
> >
> >And optionally also:
> >​- The value by key '1' becomes the value by key '5'.
>

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

Re: [sqlite] Can this be done with SQLite

[Keith Medcalf]

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 into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> .head on
sqlite> .mode column

select * from x order by key;

key   value
  
-6374565986553047082  -6292999241545120883
-5989898834901854520  -9081225235206840749
-5612633422423030496  -3498480116044899177
-3719342152283010731  -4268175217960688953
-2423950719408034905  -3377215796687069970
3673537119323620073900645503222593618
883226292009397075-1973921941627299252
1707896441609026036   -2722166238737751675
6339993451314418730   -8055191930500241295
8898575339909083958   7883859772702047363

select oldkey,
   newkey,
   (select value from x where key = newkey) as value
  from (select key as oldkey, 
   coalesce((select min(x1.key) 
   from x as x1 
  where x1.key > x.key), (select min(key) from x)) as 
newkey 
  from x
  order by key) as xx;

oldkeynewkeyvalue
    
-6374565986553047082  -5989898834901854520  -9081225235206840749
-5989898834901854520  -5612633422423030496  -3498480116044899177
-5612633422423030496  -3719342152283010731  -4268175217960688953
-3719342152283010731  -2423950719408034905  -3377215796687069970
-2423950719408034905  3673537119323620073900645503222593618
367353711932362007883226292009397075-1973921941627299252
8832262920093970751707896441609026036   -2722166238737751675
1707896441609026036   6339993451314418730   -8055191930500241295
6339993451314418730   8898575339909083958   7883859772702047363
8898575339909083958   -6374565986553047082  -6292999241545120883

begin immediate;
create temporary table rotate as 
select oldkey,
   newkey,
   (select value from x where key = newkey) as value
  from (select key as oldkey, 
   coalesce((select min(x1.key) 
   from x as x1 
  where x1.key > x.key), (select min(key) from x)) as 
newkey 
  from x
  order by key) as xx;
update x
   set value = (select value from temp.rotate where oldkey=x.key);
drop table temp.rotate;
commit;

select * from x order by key;

key   value
  
-6374565986553047082  -9081225235206840749
-5989898834901854520  -3498480116044899177
-5612633422423030496  -4268175217960688953
-3719342152283010731  -3377215796687069970
-2423950719408034905  3900645503222593618
367353711932362007-1973921941627299252
883226292009397075-2722166238737751675
1707896441609026036   -8055191930500241295
6339993451314418730   7883859772702047363
8898575339909083958   -6292999241545120883

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
>Sent: Monday, 22 January, 2018 17:19
>To: SQLite mailing list
>Subject: 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":
>>  - this is TEXT (UTF-8 or something else)?
>>  - you specify check(length(key)) == 1  do you mean:
>>- one character in some encoding (key between 0 and
>0x)
>>- one byte? (ie, ord(key) between 0 and 255)
>>- something else entirely?
>>- is it contiguous?
>>- if not contiguous what are the rules defining the non-
>contiguousness?
>>
>
>​I would like a general solution. So the type of key is not defined
>and it
>is not necessary to be contiguous.
>​
>
>
>
>>  - what is the "rotation order" based on?
>>- next arithmetic value, upper wraps to lower?
>>- next used key (by some collation order?  Which collation
>order?)
>>- based on "used" values?
>>- based on "entire domain"?
>>
>
>​Rotation is either up or down. In my example it was up. (In my
>perception.)
>
>Order is just the default order.
>​
>
>
>
>> The problem and its solution is rather simple, once you define

Re: [sqlite] SQLite version 3.22.0

[Tim Streater]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[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
5|5.0

[snip]

sqlite> SELECT *
...> FROM   playYouTubeVideo
...> WHERE  key BETWEEN '1' AND '5'
...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.


Something along these lines, perhaps:

update playYouTubeVideo set key=char(61440+unicode(key));
update playYouTubeVideo set key=case when key=char(61440+unicode('1')) then '5' 
else char(unicode(key)-61440-1) end;

--
Igor Tandetnik

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

Re: [sqlite] Can this be done with SQLite

[Don V Nielsen]

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:

> 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
> >> 5|5.0
> >>
> >> [snip]
> >>
> >> sqlite> SELECT *
> >> ...> FROM   playYouTubeVideo
> >> ...> WHERE  key BETWEEN '1' AND '5'
> >> ...> ;
> >> 1|2.0
> >> 2|3.0
> >> 3|4.0
> >> 4|5.0
> >> 5|2.0
> >>
> >> But I want the last one needs to be 1.0.
> >>
> >
> > Something along these lines, perhaps:
> >
> > update playYouTubeVideo set key=char(61440+unicode(key));
> > update playYouTubeVideo set key=case when key=char(61440+unicode('1'))
> > then '5' else char(unicode(key)-61440-1) end;
> >
>
> ​This also expects the values to be constant. But what I want is that the
> record with key 1 gets the value from key 2, with key 2 from key 3, …
>
> --
> 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] Can this be done with SQLite

[Cecil Westerhof]

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 I am 'always'
looking at the general case.
​



> Are the keys all integers then? All positive? Continuous?
>

​In this case yes, but it does not need to be.
​



> begin transaction;
> create temp table t (key int primary key, speed real);
> insert into t select key, (select t1.speed from playYouTubeVideo as t1
> where t1.key = foo.key % 5 + 1) from playYouTubeVideo;
> update playYouTubeVideo set speed = (select speed from t where key =
> playYouTubeVideo.key);
> drop table t;
> commit;
>

​Comes a good end in the right direction, but I am thinking I am going to
do it programmatically​. Maybe write a general function for it.

Everyone thanks for the fast replies.



> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 4:37 PM
> To: SQLite mailing list
> Subject: 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 speed = (
> > select speed from playYouTubeVideo where key = '3')
> > where key = '2';
> > ...
> > update playYouTubeVideo set speed = (
> > select speed from playYouTubeVideo where key = '5')
> > where key = '1';
> > commit;
> >
>
> ​Nope. By the way I see that I did not write it correctly. :'-(
>
> 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
> sqlite> begin transaction;
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '2')
>...> where key = '1';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '3')
>...> where key = '2';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '4')
>...> where key = '3';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '5')
>...> where key = '4';
> sqlite> update playYouTubeVideo set speed = (
>...> select speed from playYouTubeVideo where key = '1')
>...> where key = '5';
> sqlite> commit;
> sqlite> SELECT *
>...> FROM   playYouTubeVideo
>...> WHERE  key BETWEEN '1' AND '5'
>...> ;
> 1|2.0
> 2|3.0
> 3|4.0
> 4|5.0
> 5|2.0
>
> But I want the last one needs to be 1.0.
> Also, when the range becomes big, it will be a lot of code.
>
>
> I was hoping I overlooked a smart trick, but I probably need to do it
> programmatically.
>
>
> -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Cecil Westerhof
> > Sent: Monday, January 22, 2018 3:30 PM
> > To: SQLite mailing list
> > Subject: [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' becomes the value by key '2'.
> > ​- The value by key '2' becomes the value by key '3'.
> > ​- The value by key '3' becomes the value by key '4'.
> > - The value by key '4' becomes the value by key '5'.
> > - The value by key '5' becomes the value by key '6'.
> >
> > ​I suppose that I need to do this programmatically, or can this be done
> > with SQL?
> >
> > And optionally also:
> > ​- The value by key '1' becomes the value by key '5'.
> > ​
> > --
> > 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
> >
>
>
>
> --
> 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
> 

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

[Keith Medcalf]

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 in particular) 
work and want "pretty" output that looks like what they typed in rather than 
the "true and accurate" representation.  

This behaviour (described above) is a bug (in my opinion) because it encourages 
people to not understand what it is they are doing, and lay blame for their 
misunderstanding at some door other than their own.  However, it would seem 
that many (most) user interfaces are "buggy" in that they prefer to 
molly-coddle rather than be honest and truthful, thus encouraging by its own 
(rather unfortunate) feedback loop perpetual ignorance by those using the bug 
ridden products (which is almost everything that uses binary floating point).

Those interested in truth and accuracy realize that the entire binary floating 
point system is an approximation of base-10 and that (like everything else) 
should only be coddled at first input and last output, and not coddled anywhere 
in between.  Intermediate coddling should never be used for any purpose.

That said, however, the purpose of .DUMP is not to present information for the 
coddling of the user.  It is for the purpose of generating SQL which, when 
imported back in to an "empty" database, will result in *exactly* what was in 
the database which was dumped.  This means that the EXACT binary floating point 
value must be dumped and reloaded, not the coddled user ignorantificated 
version presented by other interfaces, since it is possible for the coddling to 
display values which, when reloaded, do *not* result in the same binary value 
as that which existed before the coddling operation.

That is, depending on the method used to coddle , the uncoddling my produce 
a different value of  that that which originally existed, even though it is 
possible that the new value of  may just happen to also coddle to the same 
coddled display as the actual true value of  before the "round tripping" 
through the coddling functions.  This "coddling error" may multiply each time 
the values are successively coddled and uncoddled until the resulting value of 
 no longer coddles to the same "apparent value" as the original .  The 
"standards" try to minimize the possibility of such errors, however, they still 
occur and cannot be prevented except by careful use of correctly designed 
numerical methods.

This is the nature of binary floating point and there have been many pages 
(probably in the hundreds of thousands or more) written over the last couple of 
millenia describing the difficulties of, and how to deal with, the problems 
created by the limited human cognition of arithmetic quantities in various 
bases, and more lately, on the peculiarities in particular of "binary floating 
point" representation of base-10 numbers and calculations with them.

Most of these issues had been addressed by the last quarter of the last 
century, however, there continues to be new people suddenly rediscovering that 
which was old and ancient news and making much ado out of it (take for example 
the latest so called meltdown and spectre CPU flaws that were recently 
"rediscovered" but were known since the 1950's and simply ignored by those 
hardware manufacturers and software developers affected by them in the here and 
now).

The long and the short of it is that the output of .DUMP is correct.  It 
contains the true and accurate binary floating point representation into 
base-10 and that particular representation does in fact exactly "round trip" 
through SQLite3 accurately, maintaining 100% true internal binary 
representation of the values that are stored in the database.  In fact, those 
representations round trip through almost all other binary-floating point 
representation converters with 100% binary accuracy.  And this is the goal of 
the .dump command -- it is for preservation of exact values, not for human 
consumption.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Iulian Onofrei
>Sent: Monday, 22 January, 2018 05:01
>To: sqlite-users@mailinglists.sqlite.org
>Subject: 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"
>"0.06" -> "0.059997779"
>"0.07" -> "0.070006661"
>"0.08" -> "0.080001665"
>"0.09" -> 

Re: [sqlite] Can this be done with SQLite

[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 speed = (
select speed from playYouTubeVideo where key = '3')
where key = '2';
...
update playYouTubeVideo set speed = (
select speed from playYouTubeVideo where key = '5')
where key = '1';
commit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 3:30 PM
To: SQLite mailing list
Subject: [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' becomes the value by key '2'.
​- The value by key '2' becomes the value by key '3'.
​- The value by key '3' becomes the value by key '4'.
- The value by key '4' becomes the value by key '5'.
- The value by key '5' becomes the value by key '6'.

​I suppose that I need to do this programmatically, or can this be done
with SQL?

And optionally also:
​- The value by key '1' becomes the value by key '5'.
​
-- 
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] [sqlite-announce] SQLite version 3.22.0

[Egudu, Nnaemeka R]

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

SQLite version 3.22.0 is now available on the website:


https://urldefense.proofpoint.com/v2/url?u=https-3A__sqlite.org_=DwIGaQ=LFYZ-o9_HUMeMTSQicvjIg=3G4KhP2J4jC74HhJtsqErA=hMD2fxYxSpxYs3t8ZBzq9A-45goPCVH_jPUaI-0fRZY=YIjjYuivJ-g2lY8LMYA_rVX7UeEkb9VFWd1ii2f5AUk=
 

https://urldefense.proofpoint.com/v2/url?u=https-3A__sqlite.org_download.html=DwIGaQ=LFYZ-o9_HUMeMTSQicvjIg=3G4KhP2J4jC74HhJtsqErA=hMD2fxYxSpxYs3t8ZBzq9A-45goPCVH_jPUaI-0fRZY=4Ux3OY683nt8PcpQ5JnoY_8jU7Zfo0RhWhEtOngmjFY=
 

https://urldefense.proofpoint.com/v2/url?u=https-3A__sqlite.org_releaselog_3-5F22-5F0.html=DwIGaQ=LFYZ-o9_HUMeMTSQicvjIg=3G4KhP2J4jC74HhJtsqErA=hMD2fxYxSpxYs3t8ZBzq9A-45goPCVH_jPUaI-0fRZY=bNuV8MvyQsvy-piBq5DkLcZOAnje-fNfDjcmGVuOvg8=
 

This is a regularly scheduled maintenance release of SQLite.  See the change 
log (the last link above) for details.

Please send email to the sqlite-users@mailinglists.sqlite.org mailing list, or 
directly to me, if you encounter any problems with this release.
--
D. Richard Hipp
d...@sqlite.org



___
sqlite-announce mailing list
sqlite-annou...@sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__sqlite.org-3A8080_cgi-2Dbin_mailman_listinfo_sqlite-2Dannounce=DwIGaQ=LFYZ-o9_HUMeMTSQicvjIg=3G4KhP2J4jC74HhJtsqErA=hMD2fxYxSpxYs3t8ZBzq9A-45goPCVH_jPUaI-0fRZY=dIKqSz2Idi4hafPamumitadI6jo2bhE5CE_GmLaEq4U=
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

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
>> 5|5.0
>>
>> [snip]
>>
>> sqlite> SELECT *
>> ...> FROM   playYouTubeVideo
>> ...> WHERE  key BETWEEN '1' AND '5'
>> ...> ;
>> 1|2.0
>> 2|3.0
>> 3|4.0
>> 4|5.0
>> 5|2.0
>>
>> But I want the last one needs to be 1.0.
>>
>
> Something along these lines, perhaps:
>
> update playYouTubeVideo set key=char(61440+unicode(key));
> update playYouTubeVideo set key=case when key=char(61440+unicode('1'))
> then '5' else char(unicode(key)-61440-1) end;
>

​This also expects the values to be constant. But what I want is that the
record with key 1 gets the value from key 2, with key 2 from key 3, …

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

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

[David Raymond]

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: sqlite-annou...@mailinglists.sqlite.org
Subject: [sqlite-announce] SQLite version 3.22.0

SQLite version 3.22.0 is now available on the website:

https://sqlite.org/
https://sqlite.org/download.html
https://sqlite.org/releaselog/3_22_0.html

This is a regularly scheduled maintenance release of SQLite.  See the change 
log (the last link above) for details.

Please send email to the sqlite-users@mailinglists.sqlite.org mailing list, or 
directly to me, if you encounter any problems with this release.
--
D. Richard Hipp
d...@sqlite.org



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

Re: [sqlite] Can this be done with SQLite

[Marc L. Allen]

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 ... when key = 1)

I can't recall if the update will actually do that atomically (so that the 
speed for key = 1 is still available).

Option 2:

Since I believe SQLite doesn't support UPDATE FROM, you'll need temporary 
variables somewhere.

If the table is small enough, copy it off and do the updates using the copy as 
a source.  If the table is large, then maybe add in an extra staging field in 
the record?

CREATE TABLE playYouTubeVideo (
key TEXTNOT NULL,
speed   FLOAT   NOT NULL,
tmpSpeed FLOAT NULL,
CONSTRAINT key   CHECK(length(key)  == 1),
CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
PRIMARY KEY(key)
);

Then before doing the cycle,

update playYouTubeVideo set tmpSpeed = speed

Then perform the update as David suggested, but using the tmpSpeed variable.

You can probably optimize that by using just tmpSpeed variable from the start 
or end of the cycle.

Marc


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 4:37 PM
To: SQLite mailing list 
Subject: 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 speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>

​Nope. By the way I see that I did not write it correctly. :'-(

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
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '2')
   ...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '3')
   ...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '4')
   ...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '5')
   ...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '1')
   ...> where key = '5';
sqlite> commit;
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.


I was hoping I overlooked a smart trick, but I probably need to do it 
programmatically.


-Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [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' becomes the value by key '2'.
> ​- The value by key '2' becomes the value by key '3'.
> ​- The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> ​I suppose that I need to do this programmatically, or can this be 
> done with SQL?
>
> And optionally also:
> ​- The value by key '1' becomes the value by key '5'.
> ​
> --
> 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
>



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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or 

Re: [sqlite] Can this be done with SQLite

[David Raymond]

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 select key, (select t1.speed from playYouTubeVideo as t1 where 
t1.key = foo.key % 5 + 1) from playYouTubeVideo;
update playYouTubeVideo set speed = (select speed from t where key = 
playYouTubeVideo.key);
drop table t;
commit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 4:37 PM
To: SQLite mailing list
Subject: 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 speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>

​Nope. By the way I see that I did not write it correctly. :'-(

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
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '2')
   ...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '3')
   ...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '4')
   ...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '5')
   ...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '1')
   ...> where key = '5';
sqlite> commit;
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.


I was hoping I overlooked a smart trick, but I probably need to do it
programmatically.


-Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [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' becomes the value by key '2'.
> ​- The value by key '2' becomes the value by key '3'.
> ​- The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> ​I suppose that I need to do this programmatically, or can this be done
> with SQL?
>
> And optionally also:
> ​- The value by key '1' becomes the value by key '5'.
> ​
> --
> 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
>



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

[sqlite] Can this be done with SQLite

[Cecil Westerhof]

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' becomes the value by key '2'.
​- The value by key '2' becomes the value by key '3'.
​- The value by key '3' becomes the value by key '4'.
- The value by key '4' becomes the value by key '5'.
- The value by key '5' becomes the value by key '6'.

​I suppose that I need to do this programmatically, or can this be done
with SQL?

And optionally also:
​- The value by key '1' becomes the value by key '5'.
​
-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Jim Morris]

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')
 where key = '1';
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '3')
 where key = '2';
...
update playYouTubeVideo set speed = (
 select speed from playYouTubeVideo where key = '5')
 where key = '1';
commit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 3:30 PM
To: SQLite mailing list
Subject: [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' becomes the value by key '2'.
​- The value by key '2' becomes the value by key '3'.
​- The value by key '3' becomes the value by key '4'.
- The value by key '4' becomes the value by key '5'.
- The value by key '5' becomes the value by key '6'.

​I suppose that I need to do this programmatically, or can this be done
with SQL?

And optionally also:
​- The value by key '1' becomes the value by key '5'.
​


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

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

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 speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>

​Nope. By the way I see that I did not write it correctly. :'-(

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
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '2')
   ...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '3')
   ...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '4')
   ...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '5')
   ...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '1')
   ...> where key = '5';
sqlite> commit;
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.


I was hoping I overlooked a smart trick, but I probably need to do it
programmatically.


-Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [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' becomes the value by key '2'.
> ​- The value by key '2' becomes the value by key '3'.
> ​- The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> ​I suppose that I need to do this programmatically, or can this be done
> with SQL?
>
> And optionally also:
> ​- The value by key '1' becomes the value by key '5'.
> ​
> --
> 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
>



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

Re: [sqlite] Can this be done with SQLite

[Cecil Westerhof]

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 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')
>>  where key = '1';
>> update playYouTubeVideo set speed = (
>>  select speed from playYouTubeVideo where key = '3')
>>  where key = '2';
>> ...
>> update playYouTubeVideo set speed = (
>>  select speed from playYouTubeVideo where key = '5')
>>  where key = '1';
>> commit;
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Cecil Westerhof
>> Sent: Monday, January 22, 2018 3:30 PM
>> To: SQLite mailing list
>> Subject: [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' becomes the value by key '2'.
>> ​- The value by key '2' becomes the value by key '3'.
>> ​- The value by key '3' becomes the value by key '4'.
>> - The value by key '4' becomes the value by key '5'.
>> - The value by key '5' becomes the value by key '6'.
>>
>> ​I suppose that I need to do this programmatically, or can this be done
>> with SQL?
>>
>> And optionally also:
>> ​- The value by key '1' becomes the value by key '5'.
>> ​
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Can this be done with SQLite

[Keith Medcalf]

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,
   coalesce((select min(x1.key)
   from x as x1
  where x1.key > x.key), (select min(key)
from x)) as newkey
  from x
  order by key) as xx;
create unique index temp.idxRotate on rotate (oldkey);
update x
   set value = (select value from temp.rotate where oldkey=x.key);
drop table temp.rotate;
commit;


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: Keith Medcalf [mailto:kmedc...@dessus.com]
>Sent: Monday, 22 January, 2018 18:00
>To: 'SQLite mailing list'
>Subject: 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 into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> insert into x values (random(), random());
>sqlite> .head on
>sqlite> .mode column
>
>select * from x order by key;
>
>key   value
>  
>-6374565986553047082  -6292999241545120883
>-5989898834901854520  -9081225235206840749
>-5612633422423030496  -3498480116044899177
>-3719342152283010731  -4268175217960688953
>-2423950719408034905  -3377215796687069970
>3673537119323620073900645503222593618
>883226292009397075-1973921941627299252
>1707896441609026036   -2722166238737751675
>6339993451314418730   -8055191930500241295
>8898575339909083958   7883859772702047363
>
>select oldkey,
>   newkey,
>   (select value from x where key = newkey) as value
>  from (select key as oldkey,
>   coalesce((select min(x1.key)
>   from x as x1
>  where x1.key > x.key), (select min(key)
>from x)) as newkey
>  from x
>  order by key) as xx;
>
>oldkeynewkeyvalue
>    
>-6374565986553047082  -5989898834901854520  -9081225235206840749
>-5989898834901854520  -5612633422423030496  -3498480116044899177
>-5612633422423030496  -3719342152283010731  -4268175217960688953
>-3719342152283010731  -2423950719408034905  -3377215796687069970
>-2423950719408034905  3673537119323620073900645503222593618
>367353711932362007883226292009397075-1973921941627299252
>8832262920093970751707896441609026036   -2722166238737751675
>1707896441609026036   6339993451314418730   -8055191930500241295
>6339993451314418730   8898575339909083958   7883859772702047363
>8898575339909083958   -6374565986553047082  -6292999241545120883
>
>begin immediate;
>create temporary table rotate as
>select oldkey,
>   newkey,
>   (select value from x where key = newkey) as value
>  from (select key as oldkey,
>   coalesce((select min(x1.key)
>   from x as x1
>  where x1.key > x.key), (select min(key)
>from x)) as newkey
>  from x
>  order by key) as xx;
>update x
>   set value = (select value from temp.rotate where oldkey=x.key);
>drop table temp.rotate;
>commit;
>
>select * from x order by key;
>
>key   value
>  
>-6374565986553047082  -9081225235206840749
>-5989898834901854520  -3498480116044899177
>-5612633422423030496  -4268175217960688953
>-3719342152283010731  -3377215796687069970
>-2423950719408034905  3900645503222593618
>367353711932362007-1973921941627299252
>883226292009397075-2722166238737751675
>1707896441609026036   -8055191930500241295
>6339993451314418730   7883859772702047363
>8898575339909083958   -6292999241545120883
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
>>Sent: Monday, 22 January, 2018 17:19
>>To: SQLite mailing list
>>Subject: 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 

[sqlite] Minor JSON output bug (infinity)

[Alexander Beedie]

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

>> import json
>> json.loads( ‘[Inf,-Inf,null]’ )
ValueError: No JSON object could be decoded
>> json.loads( ‘[Infinity,-Infinity,null]’ )
[inf, -inf, None]


Regards,

-Alex
--
iPhoneから送信
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 succeed
> with “Infinity” (as this is the standard JS property name)

A strict reading of https://json.org/ suggests that neither "Inf" nor
"Infinity" ought to work.  I'm not sure how we ought to deal with
this

>
> eg: in standard python -
>
>>> import json
>>> json.loads( ‘[Inf,-Inf,null]’ )
> ValueError: No JSON object could be decoded
>>> json.loads( ‘[Infinity,-Infinity,null]’ )
> [inf, -inf, None]
>
>
> Regards,
>
> -Alex
> --
> iPhoneから送信
> ___
> 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

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

[J. King]

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 Infinity, either, as a conforming parser would reject both. 

What to do in such a case is undefined, but for the stated case there is 
actually a very sensible conforming output:

'[1e,-1e,null]'

I realize it is impractical for SQLite to do so, but given that JSON numbers 
convey arbitrary precision, only explicit infinity should, ideally, result in 
undefined behaviour. 

On January 22, 2018 9:00:35 PM EST, 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
>succeed with “Infinity” (as this is the standard JS property name)
>
>eg: in standard python -
>
>>> import json
>>> json.loads( ‘[Inf,-Inf,null]’ )
>ValueError: No JSON object could be decoded
>>> json.loads( ‘[Infinity,-Infinity,null]’ )
>[inf, -inf, None]
>
>
>Regards,
>
>-Alex
>--
>iPhoneから送信
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[J Decker]

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 the majority will succeed
> with “Infinity” (as this is the standard JS property name)
>
JSON5 or JSON6 handle it.
http://json5.org/  https://github.com/d3x0r/json6  (
https://github.com/d3x0r/SACK/blob/master/src/netlib/html5.websocket/json/json6_parser.c
)

(also NaN)
but yes that is a deficiency in JSON.


> eg: in standard python -
>
> >> import json
> >> json.loads( ‘[Inf,-Inf,null]’ )
> ValueError: No JSON object could be decoded
> >> json.loads( ‘[Infinity,-Infinity,null]’ )
> [inf, -inf, None]
>
>
> Regards,
>
> -Alex
> --
> iPhoneから送信
> ___
> 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] .DUMP displays floats differently from SELECT

[petern]

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 then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Peter




On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou  wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/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 number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -Original Message- From: Iulian Onofrei
>
> 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.05" -> "0.050002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> ___
> 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] sqlite command line tool fails to dump data

[J Decker]

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, and not keys
> > and I see unless custom aggregate()s or function()s ...
>
> If you want embedded NULs, use blobs.
>
> But it's not a blob, it's text that I'm saving.


> > insert into ? (?,?) values(?,?)
> > with bind ( 'ta\0le', '\0','\1', 'hello\0','\0world' )
> >
> > bad things happen :)  but what if I ?
>
> In this case, the bad thing that happens is a syntax error; you cannot
> use parameters for table/column names.
>
> And SQL statements cannot contain embedded NULs; parsing stops at the
> detected end of the string.
>
> by SQL you mean  PSSQL and Sqlite

MySQL https://dev.mysql.com/doc/refman/5.7/en/string-literals.html
TSQL
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql
Oracle can use Chr(0); but its tools can often be misleading

And sqlite internally has no problems storing and retrieving the data
faithfully; it's just the command line tool (sqlite3) and TCL tests that
have issues.



I can do this to insert NUL character...

sqlite test.db
create table test (a)
insert into test (a) values ( "test"||char(0)||"one" )
.dump test

So I CAN escape NUL chars in sqlite by replacing them with '||char(0)||'


From the standard.
The stand makes no mention of NUL or \0 either to allow or disallow, so
it's undefined but it's not that 'cannot contain NUL'

create table [with\0nul] ( `col\0``umn` )

is easily parsable, and all that has to be done is keep the tokens as a
whole (string,length) and not fall back to strlen, and trust the original
character count given to prepare.


http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

spaces are used to separate syntactic elements. Multiple spaces and
 line breaks are treated as a single space. Apart from those symbols
 to which special functions were given above, other characters and
 character strings in a formula stand for themselves. In addition,
 if the symbols to the right of the definition operator in a produc-
 tion consist entirely of BNF symbols, then those symbols stand for
 themselves and do not take on their special meaning.


For every portion of the string enclosed in square brackets,
either delete the brackets and their contents or change the
brackets to braces.




(from SQL 92) By this, I shouldn't also be able to use ~, `, Γειά σου Κόσμ,
Привет мир, or any other UNICODE character. (that is if you say things not
listed are " cannot contain embedded ;"

5.1  

 Define the terminal symbols of the SQL language and the elements of
 strings.

 Format

  ::=

  | 

  ::=

  | 

  ::=

  | 
  | 

  ::=

  | 

  ::=
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
  | P | Q | R | S | T | U | V | W | X | Y | Z

  ::=
a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
  | p | q | r | s | t | u | v | w | x | y | z

  ::=
  0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

  ::=

  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 

  ::= !! space character in character set in use
  ::= "
  ::= %
  ::= &
  ::= '
  ::= (
  ::= )
  ::= *
  ::= +
  ::= ,
  ::= -
  ::= .
  ::= /
  ::= :
  ::= ;
  ::= <
  ::= =
  ::= >
  ::= ?
  ::= [
  ::= ]
  ::= _
  ::= |

 General Rules

 1) There is a one-to-one correspondence between the symbols con-
tained in  and the symbols
contained in  such that, for
all i, the symbol defined as the i-th alternative for  corresponds to the symbol defined as
the i-th alternative for .


> Regards,
> Clemens
> ___
> 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] .DUMP displays floats differently from SELECT

[Iulian Onofrei]

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"
"0.06" -> "0.059997779"
"0.07" -> "0.070006661"
"0.08" -> "0.080001665"
"0.09" -> "0.089996669"
"0.1" -> "0.1555"
"0.11" -> "0.1155"
"0.12" -> "0.11999555"
"0.13" -> "0.13000444"
"0.15" -> "0.14999444"
"0.16" -> "0.16000333"
"0.17" -> "0.17001221"
"0.18" -> "0.17999333"
"0.19" -> "0.19000222"
"0.21" -> "0.20999222"
"0.22" -> "0.22000111"
"0.23" -> "0.23000999"
"0.24" -> "0.23999111"
"0.25" -> "0.25"
"0.26" -> "0.26000888"
"0.27" -> "0.27001776"
"0.28" -> "0.28002664"
"0.29" -> "0.28998001"
"0.3" -> "0.29998889"
"0.32" -> "0.32000666"
"0.33" -> "0.33001554"
"0.37" -> "0.36999555"
"0.38" -> "0.38000444"
"0.4" -> "0.4000222"
"0.41" -> "0.40997557"
"0.43" -> "0.42999333"
"0.44" -> "0.44000222"
"0.45" -> "0.4500111"
"0.46" -> "0.46001998"
"0.49" -> "0.48999111"
"0.5" -> "0.5"
"0.51" -> "0.51000888"
"0.52" -> "0.52001776"
"0.54" -> "0.54003552"
"0.56" -> "0.56005329"
"0.57" -> "0.56995115"
"0.58" -> "0.57996003"
"0.6" -> "0.59997779"
"0.61" -> "0.60998667"
"0.65" -> "0.6500222"
"0.67" -> "0.67003996"
"0.7" -> "0.69995559"
"0.73" -> "0.72998223"
"0.75" -> "0.75"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[David Raymond]

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 FAQ 
http://www.sqlite.org/faq.html#q16


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Iulian Onofrei
Sent: Monday, January 22, 2018 7:01 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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"
"0.06" -> "0.059997779"
"0.07" -> "0.070006661"
"0.08" -> "0.080001665"
"0.09" -> "0.089996669"
"0.1" -> "0.1555"
"0.11" -> "0.1155"
"0.12" -> "0.11999555"
"0.13" -> "0.13000444"
"0.15" -> "0.14999444"
"0.16" -> "0.16000333"
"0.17" -> "0.17001221"
"0.18" -> "0.17999333"
"0.19" -> "0.19000222"
"0.21" -> "0.20999222"
"0.22" -> "0.22000111"
"0.23" -> "0.23000999"
"0.24" -> "0.23999111"
"0.25" -> "0.25"
"0.26" -> "0.26000888"
"0.27" -> "0.27001776"
"0.28" -> "0.28002664"
"0.29" -> "0.28998001"
"0.3" -> "0.29998889"
"0.32" -> "0.32000666"
"0.33" -> "0.33001554"
"0.37" -> "0.36999555"
"0.38" -> "0.38000444"
"0.4" -> "0.4000222"
"0.41" -> "0.40997557"
"0.43" -> "0.42999333"
"0.44" -> "0.44000222"
"0.45" -> "0.4500111"
"0.46" -> "0.46001998"
"0.49" -> "0.48999111"
"0.5" -> "0.5"
"0.51" -> "0.51000888"
"0.52" -> "0.52001776"
"0.54" -> "0.54003552"
"0.56" -> "0.56005329"
"0.57" -> "0.56995115"
"0.58" -> "0.57996003"
"0.6" -> "0.59997779"
"0.61" -> "0.60998667"
"0.65" -> "0.6500222"
"0.67" -> "0.67003996"
"0.7" -> "0.69995559"
"0.73" -> "0.72998223"
"0.75" -> "0.75"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei



--
Sent from: http://sqlite.1065341.n5.nabble.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

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

[J Decker]

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 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
> FAQ http://www.sqlite.org/faq.html#q16
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Iulian Onofrei
> Sent: Monday, January 22, 2018 7:01 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: 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"
> "0.06" -> "0.059997779"
> "0.07" -> "0.070006661"
> "0.08" -> "0.080001665"
> "0.09" -> "0.089996669"
> "0.1" -> "0.1555"
> "0.11" -> "0.1155"
> "0.12" -> "0.11999555"
> "0.13" -> "0.13000444"
> "0.15" -> "0.14999444"
> "0.16" -> "0.16000333"
> "0.17" -> "0.17001221"
> "0.18" -> "0.17999333"
> "0.19" -> "0.19000222"
> "0.21" -> "0.20999222"
> "0.22" -> "0.22000111"
> "0.23" -> "0.23000999"
> "0.24" -> "0.23999111"
> "0.25" -> "0.25"
> "0.26" -> "0.26000888"
> "0.27" -> "0.27001776"
> "0.28" -> "0.28002664"
> "0.29" -> "0.28998001"
> "0.3" -> "0.29998889"
> "0.32" -> "0.32000666"
> "0.33" -> "0.33001554"
> "0.37" -> "0.36999555"
> "0.38" -> "0.38000444"
> "0.4" -> "0.4000222"
> "0.41" -> "0.40997557"
> "0.43" -> "0.42999333"
> "0.44" -> "0.44000222"
> "0.45" -> "0.4500111"
> "0.46" -> "0.46001998"
> "0.49" -> "0.48999111"
> "0.5" -> "0.5"
> "0.51" -> "0.51000888"
> "0.52" -> "0.52001776"
> "0.54" -> "0.54003552"
> "0.56" -> "0.56005329"
> "0.57" -> "0.56995115"
> "0.58" -> "0.57996003"
> "0.6" -> "0.59997779"
> "0.61" -> "0.60998667"
> "0.65" -> "0.6500222"
> "0.67" -> "0.67003996"
> "0.7" -> "0.69995559"
> "0.73" -> "0.72998223"
> "0.75" -> "0.75"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users