Can this be done with SQLite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Can this be done with SQLite

Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

David Raymond
Unless I'm reading you wrong then just do the normal

begin transaction;
update playYouTubeVideo set speed = (
    select speed from playYouTubeVideo where key = '2')
    where key = '1';
update playYouTubeVideo set speed = (
    select speed from playYouTubeVideo where key = '3')
    where key = '2';
...
update playYouTubeVideo set speed = (
    select speed from playYouTubeVideo where key = '5')
    where key = '1';
commit;


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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     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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Jim Morris
Wouldn't the mod operator do this?

Do an update and set key = 1 + (5 + key)%5


On 1/22/2018 12:38 PM, David Raymond wrote:

> Unless I'm reading you wrong then just do the normal
>
> begin transaction;
> update playYouTubeVideo set speed = (
>      select speed from playYouTubeVideo where key = '2')
>      where key = '1';
> update playYouTubeVideo set speed = (
>      select speed from playYouTubeVideo where key = '3')
>      where key = '2';
> ...
> update playYouTubeVideo set speed = (
>      select speed from playYouTubeVideo where key = '5')
>      where key = '1';
> commit;
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] 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     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'.
> ​

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Cecil Westerhof-5
In reply to this post by David Raymond
2018-01-22 21:38 GMT+01:00 David Raymond <[hidden email]>:

> 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:[hidden email]]
> 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     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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Cecil Westerhof-5
In reply to this post by Jim Morris
2018-01-22 22:36 GMT+01:00 Jim Morris <[hidden email]>:

> 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:[hidden email]]
>> 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     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'.
>> ​
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Igor Tandetnik-2
In reply to this post by Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Cecil Westerhof-5
2018-01-22 23:07 GMT+01:00 Igor Tandetnik <[hidden email]>:

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Marc L. Allen
In reply to this post by Cecil Westerhof-5
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     TEXT    NOT 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:[hidden email]] On Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 4:37 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Can this be done with SQLite

2018-01-22 21:38 GMT+01:00 David Raymond <[hidden email]>:

> 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:[hidden email]]
> 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     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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

David Raymond
In reply to this post by Cecil Westerhof-5
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:[hidden email]] 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 <[hidden email]>:

> 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:[hidden email]]
> 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     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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Cecil Westerhof-5
2018-01-22 23:15 GMT+01:00 David Raymond <[hidden email]>:

> 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:[hidden email]]
> 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 <[hidden email]>:
>
> > 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:[hidden email]]
> > 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     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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Cecil Westerhof
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Don V Nielsen
In reply to this post by Cecil Westerhof-5
Cecil, you need to make a backup of the values before they are altered, and
then alter them from the backup. That way, by the time you have come full
circle, you are not working with the changed values.


On Mon, Jan 22, 2018 at 4:11 PM, Cecil Westerhof <[hidden email]>
wrote:

> 2018-01-22 23:07 GMT+01:00 Igor Tandetnik <[hidden email]>:
>
> > 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Keith Medcalf
In reply to this post by Cecil Westerhof-5

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?
 
 - 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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Cecil Westerhof-5
2018-01-23 1:02 GMT+01:00 Keith Medcalf <[hidden email]>:

>
> 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-
> >[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Keith Medcalf
Completely generic solution.  You change the direction of rotation by changing the SQL that builds your temp table ...

sqlite> create table x (key integer primary key, value);
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> insert into x values (random(), random());
sqlite> .head on
sqlite> .mode column

select * from x order by key;

key                   value
--------------------  --------------------
-6374565986553047082  -6292999241545120883
-5989898834901854520  -9081225235206840749
-5612633422423030496  -3498480116044899177
-3719342152283010731  -4268175217960688953
-2423950719408034905  -3377215796687069970
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-
>[hidden email]] 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 <[hidden email]>:
>
>>
>> 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-
>> >[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can this be done with SQLite

Keith Medcalf

If you want it fast even for huge tables then force the creation of an index on the temporary rotate table:

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


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


>-----Original Message-----
>From: Keith Medcalf [mailto:[hidden email]]
>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-
>>[hidden email]] 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 <[hidden email]>:
>>
>>>
>>> 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-
>>> >[hidden email]] 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
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Can this be done with SQLite

Hick Gunter
In reply to this post by Cecil Westerhof-5
You need to use a temporary table because by the time you select the key 1 value it has already been overwritten.

BEGIN;
CREATE TEMP TABLE new_speed AS SELECT (key +4) % 5 AS key, speed FROM playYouTubeVideo;
UPDATE playYouTubeVideo SET speed = SELECT speed FROM new_speed WHERE new_speed.key = playYouTubeVideo.key;
DROP TABLE new_speed;
COMMIT;

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Cecil Westerhof
Gesendet: Montag, 22. Jänner 2018 23:12
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Can this be done with SQLite

2018-01-22 23:07 GMT+01:00 Igor Tandetnik <[hidden email]>:

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users