Skip to content

Instantly share code, notes, and snippets.

@hborders
Last active March 1, 2019 06:22
Show Gist options
  • Save hborders/7a7e588192552a4cd46a581097cd367f to your computer and use it in GitHub Desktop.
Save hborders/7a7e588192552a4cd46a581097cd367f to your computer and use it in GitHub Desktop.
Moves a subscription row after a given reference subscription row
/*
Moves moving_id after reference_id by finding the next row after
reference_id and setting moving_id.sort to be the midpoint of
reference_id.sort and next_reference_id.sort
*/
UPDATE subscription
SET sort = (
CASE WHEN (
/*
Get the first sort value after the reference_id
Have to wrap in another SELECT to change an empty rowset
to a NULL value.
*/
SELECT (
/*
Find the next row after reference_id
I repeat this below. Is there a way to abstract it?
*/
SELECT sort
FROM subscription
WHERE sort > (
/*
I also repeat this 3 times. Is there a way to abstract it?
*/
SELECT sort
FROM subscription
WHERE _id = ? /* reference_id */
) LIMIT 1
) as max_sort
) IS NULL THEN (
/*
There is no sort value after the reference_id,
so moving_id's sort becomes MAX(SORT) + 1
*/
SELECT IFNULL(MAX(sort), 0) + 1
FROM subscription
) ELSE (
/*
There is a sort value after reference_id,
so moving_id's sort becomes:
( reference_id.sort + after_reference_id.sort ) / 2
*/
(
(
/* This is repeated from above */
SELECT sort
FROM subscription
WHERE sort > (
/* This is repeated from above */
SELECT sort
FROM subscription
WHERE _id = ? /* reference_id */
) LIMIT 1
) + (
/* This is repeated twice from above */
SELECT sort
FROM subscription
WHERE _id = ? /* reference_id */
)
) / 2
) END
) WHERE _id = ? /* moving_id */
CREATE TABLE subscription (
_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
sort REAL NOT NULL UNIQUE DEFAULT 0
)
@dgcopter
Copy link

How about this:

update subscription
    set sort =
        (select new_sort
         from (select id, cast (ifnull ((sort + lead (sort) over (partition by null order by sort)) / 2, max(sort) over (partition by null) + 1) as real) new_sort
               from subscription)
         where id = ?) /* reference_id */
    where id = ?  /* moving_id */

@hborders
Copy link
Author

hborders commented Mar 1, 2019

Sadly, SQLite only got window functions in 3.25.0, but even the latest Android (API 28) is only on 3.22.0:

$ adb -e shell sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt2

And obviously, older Android versions have older SQLites:

Android API SQLite Version
API 27 3.19
API 26 3.18
API 24 3.9
API 21 3.8
API 11 3.7
API 8 3.6
API 3 3.5
API 1 3.4

I'd still like to know if there's a way to abstract the SELECTs just so the query is simpler.

@hborders
Copy link
Author

hborders commented Mar 1, 2019

I did get this working with SQLite 3.27.0

sqlite> select next_sort 
        from ( select id, 
                      lead(sort, 1, -42) 
                        over (order by sort) as next_sort 
               from subscription
        ) where id = 2;
next_sort
4.0

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment