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
367353711932362007 3900645503222593618
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;
oldkey newkey value
-------------------- -------------------- --------------------
-6374565986553047082 -5989898834901854520 -9081225235206840749
-5989898834901854520 -5612633422423030496 -3498480116044899177
-5612633422423030496 -3719342152283010731 -4268175217960688953
-3719342152283010731 -2423950719408034905 -3377215796687069970
-2423950719408034905 367353711932362007 3900645503222593618
367353711932362007 883226292009397075 -1973921941627299252
883226292009397075 1707896441609026036 -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 <kmedc...@dessus.com>:
>
>>
>> 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
>0xffffffffffffffff)
>> - 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 TEXT NOT 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
