2

I'm writing my own SQLIteBrowser and I have one final problem, which apparently is quite often discussed on the web, but it doesn't seem to have a good general solution.

So currently I store the SQL which the user entered. Whenever I need to fetch rows, I execute the SQL by adding "Limit n, m` at the end of the SQL.

For normal SQLs, which I mostly use, this seems good enough. However if I want to use limit myself in the query, this will obviously give an error, because the resulting sql can look like this:

 select * from table limit 30 limit 1,100

which is obviously wrong. Is there some better way to do this?

My idea was, that I could scan the SQL and check if there is a limit clause already used and then ignore it. Of course it's not as simlpe as that, because if I have an sql like this:

 select * from a where a.b = ( select x from z limit 1)

it obviously should still apply my limit in such a case, so I could scan the string from the end and look if there is a limit somehwere. My question now is, how feasable this is. As I don't know who the SQL parser works, I'm not sure if LIMIT has to be at the end of SQL or if there can be other commands at the end as well.

I tested it with order byand group by and I get SQL errors if limit is not at the end, so my assumption seems to be true.

Devolus
  • 21,661
  • 13
  • 66
  • 113
  • Doesn't `LIMIT n,m` work in sqlite ? http://stackoverflow.com/questions/3325515/sqlite-limit-offset-query-doubt?rq=1 – Marcello Romani Jun 27 '13 at 10:42
  • Of course it works. I'm using it after all. The question is if it must always be at the end of an SQL, as I'm writing a browser and I don't know which kind of queries – Devolus Jun 27 '13 at 10:50
  • Seems I found a solution. I simply wrap the whole SQL into `select * from (UserSQL) limit n, m` So far I hav not found any problem with that. – Devolus Jun 27 '13 at 11:03
  • Oh, maybe you just want to vary `n, m` based on current user selection (most probably the "current page"). Is that right ? – Marcello Romani Jun 27 '13 at 12:18
  • @MarcelloRomani, The user can enter an arbitrary sql which I watnt o execute. Since I don't want to load all records at once I wrap it in a limit and retrieve only N rows. Whenever the user scrolls down and reaches the bottom, I fetch additional n rows, until the select delivers no more. So it acts as a page of size N. – Devolus Jun 27 '13 at 12:36
  • This is quite different from the original question. I suggest you edit it to include this text, which is a lot clearer IMHO. A good title could be "Pagination of arbitrary SQL query". My 2 cents :) – Marcello Romani Jun 27 '13 at 12:45
  • @MarcelloRomani, the solution I found made the question a bit obsolet. However I found a lot of questions regarding pagination, using limit, so I think it might help somebody if he wants to implement a similar approach. I can also delete the question, as I already found a solution. – Devolus Jun 27 '13 at 12:47
  • My understanding is the established practice is to answer your own question. That way you'd still have a problem with a solution, which could benefit others in future. – Marcello Romani Jun 27 '13 at 12:50

1 Answers1

6

I found now a much better solution which is quite simple and doesn't require me to parse the SQL.

The user can enter an arbitrary sql. The result is loaded into a table. Since we don't want to load the whole result at once, as this can return millions of records, only N records are retriueved. When the user scroll to the bottom of the table the next N items are fetched and loaded into the table.

The solution is, to wrapt the SQL into an outer sql with my page size limits.

 select * from (arbitrary UserSQL) limit PageSize, CurrentOffset

I tested it with SQLs I regularly use, and this seem to work quite nicely and is also fast enough for my purpose.

However, I don't know wether SQLite has a mechanism to fetch the new rows faster, or if the sql has to be rerun every time. In that case it might not be a good solution fo rrealy complex queries with a long response time.

Devolus
  • 21,661
  • 13
  • 66
  • 113
  • 2
    According to the following SO article, limit X, Y has the database actually reading everything up until Y, then stripping everything before X, which makes this very inefficient. Better to store some value from a column, like timestamp or id: http://stackoverflow.com/questions/14468586/efficient-paging-in-sqlite-with-millions-of-records – Design by Adrian May 05 '15 at 17:51
  • There is no way around that, unless you know what you are searching for and can limit it in a better way. That's the nature of SQL databases and also the same with other vendors (like i.e. Oracle) – Devolus May 06 '15 at 06:38
  • But OP does know what he's looking for, at least that's what I interpret when he writes "select * from a where a.b" as an example. – Design by Adrian May 07 '15 at 13:27
  • [Efficient paging in SQLite with millions of records](https://stackoverflow.com/questions/14468586/efficient-paging-in-sqlite-with-millions-of-records/14468878) – ggorlen Dec 19 '20 at 18:34