• Welcome, Guest. Please login.
 
September 17, 2019, 01:58:34 pm

News:

Welcome to the SQLitening support forums!


SQLite, Cursors & B-Tree API

Started by Marty Francom, January 21, 2008, 07:06:24 pm

Previous topic - Next topic

Marty Francom

Paul,
    I have been reading the the book you recommened "Definitive Guide to SQLite" and in chapter 9 SQLite Internals that the SQLite database consists of seqentlially numbered pages which are indexed by multiple balanced B-trees.  (p. 349)   Then on page 353 it talks about "The B-Tree API" .   It sounds to me that it is possible to utilize this API to move through the indexes in a "Cheetah" like manor.  Some of the function mensioned on page 354 are:

    sqlite3BtreeCursor
    sqlite3BtreeCloseCursor
    sqlite3BtreeFirst
    sqlite3BtreeLast
    sqlite3BtreeNext
    sqlite3BtreePrevious
    sqlite3BtreeMoveTo

Will your server development project include the ability to call this API?

Paul Squires

Hi Marty,

The project will not jump down that deep into the SQLite api. The SQLite3 C/S needs to remain at the higher level of handling incoming SQL statements, sending the request to SQLite, and finally returning the results to the client. The guts of handling the creation of the results and moving through them in the btrees is better left to SQLite. It is something that I don't want to interfere with (at least not at this point in the project). Maybe someday down the road we may see a definitive advantage to moving into that territory - until then, I like the direction the project is in. Simplicity is the key.   We need to get away from Cheetah thinking..... SQLite thinking will prove to be just as beneficial.   :)

Paul Squires

... oh, and I read that book just about everyday (it has become a great 'bathroom' book :) ). It seems like I learn something new everyday about SQLite that amazes me. For such a small database engine, it packs an incredible wallop of power.

Have you read and understood the way SQLite handles Locks and how they are escalated from each state? It finally sunk in to me today. :D


Marty Francom

Paul,
    I get the jest of it, but fully understand..... Let's just say I
am still learning and I've got a lot to learn.
    Mark, seems real impressed with SQLite and your server implimentation.
If a way can be worked out that the Apothesoft database can be
browsed the way Cheetah allows then migration to SQLite will be
a definate go.
     That's why I was excited to read about SQLites B-tree routines.  I guess I
didn't realize those are for internal use only.   It talks about "Cursor" functions
some implimentations of SQL allow use of the Cursor statement.  Does SQLite?

Paul Squires

When you get your hands on the new code you will see that when a SELECT is ran on the server that a temp table is created to hold the results immediately. Those results are fed to the client as needed. People will ask why a temp table is being created rather than simply getting the rows directly from the compiled query as needed. The reason for the temp table is because reading from a database involves placing a SHARED lock. As long as people are reading from the database then other connections can not write to it (well, they can start writing to the cache but they will not get committed to the database until the writer can get an EXCLUSIVE lock. In order to get EXCLUSIVE then all other connections must relinquish their locks). The temp table allows us to deal with the client's request immediately and hold the lock on the main database only for the time it takes to execute the SELECT. Subsequent reading of the rows comes from the temp table that only the client has access to so locking is not an issue. Subsequent connections have access to the main database immediately and will not get starved waiting for previous readers to finish retrieving their rows.

In practice, I can see the SQLite c/s being faster and more versatile than our current Cheetah implementation. I think what we need to do is create some sort of real life simulation using your pharmacy data and see the whole thing in practice to see if it will fly or fall on its face. Once we know where the problems are then we should be work around them.  :)