database persistence with mysql, sqlite

[Bryan Olson]

Lawrence D'Oliveiro wrote:
> In Bryan Olson wrote:
> 
>> coldpizza wrote:
>>> It turned out that the method above ('SELECT * FROM TABLE LIMIT L1,
>>> L2') works ok both with mysql and sqlite3, therefore I have decided to
>>> stick with it until I find something better. With Sqlite3 you are
>>> supposed to use LIMIT 10 OFFSET NN, but it also apparently supports
>>> the mysql syntax (LIMIT NN, 10) for compatibility reasons.

>> A more reliable form is along the lines:
>>
>>      SELECT keyfield, stuff
>>      FROM table
>>      WHERE keyfield > ?
>>      ORDER BY keyfield
>>      LIMIT 10
>>
>> With the right index, it's efficient.
> 
> But that involves keeping track of the right starting keyfield value for the
> next batch of records, which is complicated and nontrivial.

I think you missed the idea here. Recall that we return a
web page showing 10 records and a 'Next' link. We write the
link so that the browser will send back the parameter we
need. If the largest keyfield value on the page is
"Two-Sheds" the link might read:

<A HREF="http://rfh.uk/tablnext.cgi?start=Two-Sheds">Next</A>

The solution is stateless. There's no "keeping track" on the
server side. When we respond to a request, we neither look up
a previous request nor store anything for a future response.


> Simpler to let
> the DBMS do the work for you.

Keeping a cursor with pending data across HTTP requests is
a world of hurt.


-- 
--Bryan