SQLite Forum

How do I create a "rolling cursor" ?
Login

How do I create a "rolling cursor" ?

(1) By Rudy Wieser (RWieser) on 2021-02-26 09:43:36 [link] [source]

Maybe my last question was too specific. So, I'm asking it a bit broader :

How do I write a "rolling cursor" query with which I can browse(1) a random sqlite3 table ?

(1) Meaning that I, while browsing, can change the direction at will. A few page downs, than a page up or two and than page downs again. You know, like flipping thru a book.

A simple question, yes ? No, not really. :-|

.

I know that I can "just" provide all fields of the current record and than use a WHERE to get the next or previous set of records, but ...

it has to keep working with columns containing NULL entries.

It has to keep working even the stored datatype doesn't match that of the column.

I need to be able to sort on at least one column - even when that column contains screenfulls of rows containing the same data. (using the rowid as the last WHERE colum is an easy solution - but that doesn't work for a 'without rowid' table)

Also, I have no intention to store upto a megs worth of (possible) data for each displayed field, just so I can send that data back in next query. IOW, I will store/display a limited ammount of data for each field and the search query needs to reflect that.

(2) By curmudgeon on 2021-02-26 13:09:15 in reply to 1 [link] [source]

Store the rowids of the table in a temp table and use them to retrieve sections of the table. e.g. suppose you want to view a table t sorted on col s.

create temp table ids as select rowid as id from t order by s;

If you want to view records i..i+n of the sorted table use this

select * from t where rowid in
(select id from ids where rowid between ?1 and ?1+n order by rowid);

Use bind to set ?1 to i.

If you'd rather store the id's (faster) in an array (a) or vector (v) you can do the same thing using the carray extension

https://sqlite.org/carray.html#:~:text=The%20carray%20()%20function%20is%20not%20compiled%20into,first%20added%20to%20SQLite%20in%20version%203.14%20(2016-08-08)

and execute

select * from t where rowid in carray(?1,n);

binding ?1 to a+i (array case) or v.data()+i (vector case).

Cons:
You're taking a snapshot of t so if others are using separate connections to insert or delete records from t there will be id values missing or no longer valid in the ids table.
For large tables (as in tens of millions of records) storing the rowids can sometimes be sluggish.

(3) By anonymous on 2021-02-26 13:12:25 in reply to 1 [link] [source]

    select textcol, numcol, rowid from tablename
        where (coalesce(textcol, ''), coalesce(numcol, 0), rowid) > (coalesce(?1, ''), coalesce(?2, 0), ?3)
        order by coalesce(textcol, ''), coalesce(numcol, 0), rowid
        limit ?4;

For without rowid tables, every column of the primary key is guaranteed not to be null, so append all primary key columns that the order clause doesn't already mention.

(4) By Ryan Smith (cuz) on 2021-02-26 15:04:44 in reply to 2 [link] [source]

This is by far the best/easiest solution to do what you want to do.
I'm adding just a pointer or two upon curmudgeon's suggestion:

I wish to point out that in that CREATE query (for the ids) you are free to sort, filter, limit in any way imaginable to satisfy all your listed requirements. An example might be:

CREATE TEMP TABLE ids AS
SELECT rowid as id
  FROM t
 WHERE t.somecolumn IS NOT NULL AND t.someothercolumn != 'John'
 ORDER BY t.yetanothercolumn DESC;

For reading the page/section of rows needed, this was suggested:

select * from t where rowid in
(select id from ids where rowid between ?1 and ?1+n order by rowid);

but I think the next example might be better (not depending on the IN optimization, more legible, and at least in my testing proven to be the fastest of what I've tried - if someone can show a faster method, I would like to know please!):

SELECT t.*
  FROM ids
  JOIN t ON t.rowid = ids.id
 WHERE ids.rowid >= ?1 
 ORDER BY ids.rowid
 LIMIT n
;

I'd also like to add that the stated CONs are very much true, but unlikely to be factors in a program/app where this kind of view query would typically be used.
If they are factors for your intended project, you need to revisit the entire idea of doing this kind of pagination thing.

(5) By anonymous on 2021-02-26 15:12:51 in reply to 1 [link] [source]

I might have missed the point but it seems to me that you want to select records at random from a given table. If no, ignore else, try this:

SELECT * FROM tblThisTable ORDER BY RANDOM() LIMIT 2;

The LIMIT argument specifies the number of records; you can randomize this also.

(6) By curmudgeon on 2021-02-26 15:23:39 in reply to 4 [source]

SELECT t.*
  FROM ids
  JOIN t ON t.rowid = ids.id
 WHERE ids.rowid >= ?1 
 ORDER BY ids.rowid
 LIMIT n
;

Agreed Ryan, that is better and maybe less confusing than what I suggested.

(7) By Keith Medcalf (kmedcalf) on 2021-02-26 16:20:41 in reply to 4 [link] [source]

Some products do this "automagically" and call it a "keyset driven cursor".

Basically, for any query, one generates a temporary "keyset" which comprises the primary keys of all the tables involved in the query which one wants to scroll, and then uses this simple keyset to regurgitate the results on request.

The keyset may be stored on the client side or on the server side. The result is stable as of the time of generation of the keyset. New rows created subsequent to the creation of the keyset will not be included in the keyset. Rows deleted from the source tables may leave holes.

The query which generates the "keyset" would look like this (for a server-side keyset):

CREATE TEMPORARY TABLE keyset AS
SELECT <list of primary key columns from each table involved in cursor>
  FROM <list of tables wanted to cursor over>
 WHERE <list of join conditions between tables>
ORDER BY <list of fields you want the keyset ordered by>
;

Then, each time you want a specific "row" or "page of rows" from the keyset driven cursor you use the following query:

SELECT <the columns I want>
  FROM keyset
LEFT JOIN <table> ON <primary key for table>
LEFT JOIN <table> ON <primary key for table>
... for as many tables as participated in the original keyset generation ...
 WHERE keyset.rowid >= :startrow
ORDER BY keyset.rowid
 LIMIT :numrows
;

When you are done with the keyset, you drop the temporary table.

So, for example, say the query you want to paginate/scroll is:

SELECT a.doofus, w, x, y, z
  FROM a, b
 WHERE a.doofus == b.dingbat
ORDER BY w, x, y, z;

then you generate the keyset as follows:

CREATE TEMPORARY TABLE keyset AS
SELECT a.rowid as "a.rowid",
       b.rowid as "b.rowid"
  FROM a, b
 WHERE a.doofus == b.dingbat
ORDER BY w, x, y, z;

and you retrieve your paginated results as:

SELECT a.doofus, w, x, y, z
  FROM keyset
LEFT JOIN a ON a.rowid == "a.rowid"
LEFT JOIN b ON b.rowid == "b.rowid"
 WHERE keyset.rowid >= :startat
ORDER BY keyset.rowid
 LIMIT :numrows
;

When you are done you can delete the keyset. The data actually displayed in the paginated results reflects the "current view" of the data although the cursorination reflects the point-in-time at which the keyset was created.

If you wish the keyset result to be stable, you have to hold open a transaction during the whole process (not recommended).

(8) By Wout Mertens (wmertens) on 2021-02-26 17:39:01 in reply to 7 [link] [source]

Keysets won't work for sorting in different directions. They're also unnecessary, syntactic sugar (unless they can be optimized somehow).

I implemented general cursoring using nested and/or, you can see the code at https://github.com/StratoKit/strato-db/blob/master/src/JsonModel/JsonModel.js#L375-L390

The way it works is as follows: given 3 columns a, b, c you sort on, you keep values v0, v1, v2 of the last returned result, and use it to find the next value with

a >= v0 && (a != v0 || (b >= v1 && (b != v1 || (c > v2))))

if b is sorted descending the condition becomes

a >= v0 && (a != v0 || (b <= v1 && (b != v1 || (c > v2))))

if you sort on an extra column d you need to replace the last condition with

c >= v2 || (c != v2 || (d > v3))

This is in one direction. If you want to page backwards, you have to invert the cursor conditions and sorting.

So basically, you have to do a limit query, remember the last values and page by generating a nested comparision condition while retaining the sorting.

Cheers,

Wout.