[sqlite] Magic number in sqlite source code

[Max Vasilyev]

Hello, Richard!

Probably PTR_SZ or PAGE_PTR_SZ would be good?
I feel confusing about OVFL_. Why size of the pointer to the first overflow
page could be different from size of another pointers?
Sorry, I'm far from the code and should not discourse. Just have an idea
that name should be as common (generic) as possible (to follow Occam's
razor). And it's interesting where does it(4) come from? sizeof(int), for
file we should fix it, right? (comment to define).

>OVFL_PTR_SZ might be a better name.  4 is the size (in bytes) used by
>the pointer to the first overflow page that occurs at the end of the
>on-page record.

Happy New Year!

Thanks,
Max


2015-12-31 2:54 GMT+03:00 Domingo Alvarez Duarte 
:

> Hello Duncan !
>
> I saw a very good point on your suggestion !
>
> I'll use it when writing/refactoring code.
>
> Thanks a lot !
>
>
> >  Wed Dec 30 2015 11:51:54 pm CET CET from "Darren Duncan"
> >  Subject: Re: [sqlite] Magic number in sqlite
> >source code
> >
> >  On 2015-12-30 12:51 PM, Richard Hipp wrote:
> >
> >>On 12/30/15, Richard Hipp  wrote:
> >>
> >>>I'll continue look for an alternative way to make the intent of the
> >>> code clearer.
> >>>
>
> >>  See https://www.sqlite.org/src/info/1541607d458069f5 for another
> >> attempt at removing magic numbers. But I don't like it It seems to
> >> complicate more than it clarifies. My current thinking is that the
> >> code should remain as it is on trunk.
> >>
>
> >  While kludgy itself, a possible compromise is to still use a named
> >constant /
> > macro but have '4' in the name of the macro, eg like 'SOME_FOO_4' where
> the
> >
> > SOME_FOO is a semblance of descriptive and the 4 says what the value is
> so
> >you
> > don't have to look it up. The key thing is that there may be multiple
> >reasons
> > to use the value 4 in a program and the named constant is illustrating
> >which
> > reason it is. If you change the value of the constant then you would also
> > rename this particular constant to match the new value, but the key thing
> >is you
> > have something easily look-upable that shows all the 4 are connected. --
> >Darren
> > Duncan
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

[Max Vasilyev]

Hi Keith,

2015-01-09 2:02 GMT+03:00 Keith Medcalf :

>
> The table you are creating is called a keyset snapshot.  That is how all
> relational databases databases which support scrollable cursors implement
> them (only navigable databases -- hierarchical or network or network
> extended for example) support navigation within the database.  Relational
> databases are, well, relational.
>
 Thank you, good to know. So, it seems I've re-invented 'keyset snapshot'
pattern or technique? It's great idea to document different pagination
approaches as I think it is a very popular qustion. And this page
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor is out of date and
very hard to find. I did not know term 'ScrollingCursor'.

I propose to add new article at the main doc list (Categorical Index Of
SQLite Documents). It can be called like 'Pagination in SQLite' or 'Best
way to paginate result set in SQLite'.
Also where OFFSET is described
https://www.sqlite.org/lang_select.html#limitoffset I'd like to add a note
describing that it iterates all the records and link to Pagination article.
First, it should warn against using OFFSET for pagination.
*Rule Number 1:* Do not try to implement a pagination using LIMIT and
OFFSET.
Then it should describe WHERE approach from old article, It's great, but it
does not support random page accees. It can be suitable for most cases. And
not unique column is not an issue:

SELECT * FROM tracks
WHERE (*title = :lasttitle AND rowid > :lastrowid* OR title > :lasttitle)
AND (singer='Madonna' OR singer='Rick Wakeman')
ORDER BY title DESC
LIMIT 5;

And next, describe keyset pagination and keyset snapshot. As I got it
involes rownum and allows random accees. But I think it's not suitable for
continues updatable DB. But for me it's suitable as I have no updates in
background.
BTW, cool material http://use-the-index-luke.com/no-offset

Also I've read your previous answer here:
http://sqlite.1065341.n5.nabble.com/I-m-trying-to-figure-out-how-to-td78018.html
The word 'cursor' looks like as awful as 'goto'. Ok, let's don't say
cursor, let's talk about _pagination_! Nobody wants to fetch million
records, so I think, pagination is 'must have' feature of all clients.
First solution for pagination every beginner comes to is OFFSET. It's great
that SQLite has OFFSET, I remember from MS SQL that we have to use some
magic CTE (common table expression) like this.

SELECT  *FROM( SELECTROW_NUMBER() OVER ( ORDER BY OrderDate )
AS RowNum, *
  FROM  Orders
  WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResultWHERE   RowNum >= 1
AND RowNum < 20ORDER BY RowNum

But it turns out that OFFSET work awful in SQLite and I guess in all DBMS
:) I consider OFFSET is a driver 'kludge' using your words. I don't know
when is it suitable. For small database?


> The only difference is that SQLite is, well, Lite.  It does not create the
> keyset for you by automagic, you have to do it yourself.

It's good, I don't complain.

It cannot take a parameter on the _prepare of a select statement that
> indicates to magically create the snapshot for you, just as it does not
> understand UPDATE  SET ... WHERE CURRENT OF CURSOR -- you have to
> retrieve the rowid youself and UPDATE  SET ... WHERE rowid= you retrieved> ...
>
> Sorry, I did not understand that. When I insert or delete something, I'm
going to drop my snapshot and recreate it again when I need the sorting. I
think that rowid does not changes and updatin snapshot is quite difficult.
What UPDATE do you propose?



Thanks,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

[Max Vasilyev]

Hi Clemens,

2015-01-08 13:34 GMT+03:00 Clemens Ladisch :

> >
> http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-how-to-scroll-correctly-with-duplicate-names
> > and yes, we can use title+rowid as lasttitle. But... it looks too complex
> > to be 'best practice' pattern.
>
> Feel free to propose something simpler that is still correct.
>
That's why I think to rownum... Now I ended up with simulating index by a
table and use it's PK as rownum.

CREATE TABLE BookTitleIdx (title_rownum INTEGER PRIMARY KEY AUTOINCREMENT,
BookID INTEGER NOT NULL);
INSERT INTO BookTitleIdx SELECT NULL, rowid FROM Book ORDER BY Title

Time: 0.369s Great! 126K records.

SELECT Book.Id, Author.Name as Author, Book.Title, Genre.Name as Genre
FROM BookTitleIdx
INNER JOIN Book ON BookTitleIdx.BookID = Book.Id
LEFT JOIN Author ON Book.AuthorID = Author.ID
INNER JOIN Genre ON Book.GenreID = Genre.ID
WHERE BookTitleIdx.title_rownum > 12
ORDER BY BookTitleIdx.title_rownum
LIMIT 30

Time: 0.001s and not surprising! As EXPLAIN QUERY PLAN looks excellent to
me!

000SEARCH TABLE BookTitleIdx USING INTEGER PRIMARY KEY (rowid>?)
011SEARCH TABLE Book USING INTEGER PRIMARY KEY (rowid=?)
022SEARCH TABLE Author USING INTEGER PRIMARY KEY (rowid=?)
033SEARCH TABLE Genre USING INTEGER PRIMARY KEY (rowid=?)

BTW, the query can be ...FROM Book INNER JOIN BookTitleIdx... no vice versa
is required.
But inner join order is critical. This query
SELECT ...
FROM Book
LEFT JOIN Author ON Book.AuthorID = Author.ID
INNER JOIN Genre ON Book.GenreID = Genre.ID
INNER JOIN BookTitleIdx ON Book.Id = BookTitleIdx.BookID
...
runs 0.5s and has Book table scan, etc... Just moving last join up makes
the query plan as I want.

So, for now I'm happy guys, thank you very much!


The latest SQLite supports the OR optimization for this query:
>
>   EXPLAIN QUERY PLAN SELECT ...;
>   0|0|0|SCAN TABLE MainBooksView USING INDEX TitleIndex
>
> Run ANALYZE, and update your SQLite.
>
> Updated and got 0.060s (x2 faster)!
 ANALYZE does not change anything.

Thank you very much for EXPLAIN QUERY PLAN! It's much more readable than
just EXPLAIN which i used but did not understand :)


Cheers,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index rownum

[Max Vasilyev]

Hi Richard,
Thank you for your answer, much appreciated.
I guessed that insertion and deletion could be an issue. We need to
re-number keys (change a lot of Btree nodes) on each operation. Or at least
on REINDEX command (I say not strictly, just as idea).

What could you advise me?
Probably temporary table on each order by?
Or just avoid pagination? It's looks the best solution :) as my whole data
loads just for 4s.

If it's trade off question between 'viewing' and 'editing' (SELECT vs
INSERT,DELETE) my app has 'viewing' priority. I'm writing librarian
application (Books collection: Author, Title, Genre, Filename, etc). And
most time user will search the catalogue (filtering, sorting, scrolling up
and down). So I want excellent db viewer, and slow insert could be
acceptable. Or batch insert then reindex.

How is it difficult to change Btree design and introduce rownum field in
the key structure? It could be a great compile-time option!
Or should I choose another engine? Do you know about rownum for ms sql ce
or firebird? We've used these engines in work project for logging, but
ended up with SQLite ;) MS SQL ce is not very portable as it crashs on some
systems because some dlls. And firebird feels slow. So SQLite wins!


Thanks,
Max

2015-01-08 3:23 GMT+03:00 Richard Hipp <d...@sqlite.org>:

> On 1/7/15, Max Vasilyev <maxrea...@gmail.com> wrote:
> > Hi guys,
> > Is it possible to get key number from the index?
> > For example let's consider Figure 4: An Index On The Fruit Column from
> here
> > https://www.sqlite.org/queryplanner.html
> >
> > index_rownum, fruit, rowid
> > 1 Apple 2
> > 2 Grape 5
> > 3 Lemon 18
> > 4 Orange 1
> > 5 Orange 23
> > ...
> >
> > Is it possible to add rownum column and store it in the index? And get it
> > in the query?
> >
>
> No, that information is not available in the SQLite Btree design.
>
> When I was designing the Btree (back in 2003) I considered adding the
> capability to compute the "rownum" in O(logN) time.  That would have
> made things like "count(*)" much faster too.  But doing this also
> increases insertion and deletion cost, so I decided against doing it.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index rownum

[Max Vasilyev]

Hi guys,
Is it possible to get key number from the index?
For example let's consider Figure 4: An Index On The Fruit Column from here
https://www.sqlite.org/queryplanner.html

index_rownum, fruit, rowid
1 Apple 2
2 Grape 5
3 Lemon 18
4 Orange 1
5 Orange 23
...

Is it possible to add rownum column and store it in the index? And get it
in the query?

Here is my task. I show DataGrid to a user and he/she can click a column
header to change the sorting. And I want to preserve selected record
(user's cursor) when sorting changes.
I query visible data only (pagination).
I can remember rowid of selected record before sorting. But how can I find
which range of records to query so that selected record would be in this
range?

Thanks,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

[Max Vasilyev]

Hi gents,
I just want to show DataGrid with my table to a user with support of
sorting and filtering by arbitrary column.
As my table has ~200k records I'd like to implement data virtualization
(paged access).
Please, give me recommendations how to do pagination in the best way.

The following is my diging into it...
- OFFSET is slow at the end of the table (I understand why, it is
documented, no questions).
- I've read this article
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
and want to use WHERE, but what if 'title' is not unique?
- This is considered here:
http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-how-to-scroll-correctly-with-duplicate-names
and yes, we can use title+rowid as lasttitle. But... it looks too complex
to be 'best practice' pattern. And is x100 slower than simple WHERE.

SELECT * FROM "MainBooksView"
WHERE (Title = 'fff' AND Id > 101985)  OR Title > 'fff'
ORDER BY Title
 LIMIT 30
Query time: 0.102s
I have index for the Title column.

SELECT * FROM "MainBooksView"
WHERE Title > 'fff'
ORDER BY Title
 LIMIT 30
Query time: 0.001s

SELECT * FROM "MainBooksView"
ORDER BY Title
 LIMIT 30
 OFFSET 12
Query time: 1.133s

So, next what I want is Oracle ROWNUM analog, aka counter() function.
Here is a proposed implementation:
http://www.sqlite.org/cvstrac/tktview?tn=4004
but it was rejected. If you would say I want something strange or exotic,
please give me fast OFFSET implementation and I would be happy :)



Thanks,
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users