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