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 (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
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 Papadimitriouwrote: > 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] .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 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 Papadimitriouwrote: > 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] 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 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] 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 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 Beediewrote: >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)
On 1/22/18, Alexander Beediewrote: > 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
[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 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] 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, 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
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 "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
[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 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
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
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
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 Westerhofwrote: > 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
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 >
[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: 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
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
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 ... 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 listSubject: 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
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
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 '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] SQLite version 3.22.0
On 22 Jan 2018, at 21:21, Nnaemeka R Eguduwrote: > 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
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
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
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] [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 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
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] .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 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" ->
[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] 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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] 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 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] .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 Raymondwrote: > 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
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 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] sqlite command line tool fails to dump data
On 22 Jan 2018, at 4:46pm, J Deckerwrote: > 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
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
On Sun, Jan 21, 2018 at 11:48 PM, Clemens Ladischwrote: > 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] 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. 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 Kennedywrote: > 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
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 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
On Sun, 21 Jan 2018 05:54:13 + Simon Slavinescribió: > 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] 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 Kennedywrote: > 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
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