Hi Phil and list!
Thank you for this very instructive post about SQLite's internals and btrees.
I'm just curious about what could be real-life use cases of having direct
access to the btree stuff. As I understand your example, you store (key,value)
pairs inside the btree and then get them back. Then, what's the purpose of not
using a normal SQLite table to do so?
On the other hand, since those "direct btree information" are necessarily
stored inside a reguar SQLite file, doesn't this introduce possible side
effects with other functions of the sqlite library, which would not be aware
that some btree roots inside the file are neither a table nor an index?
(vacuum, etc.)
Last but not least, I am currently developping a virtual table and I've just
realized that I could use SQLite btrees for indexing data coming from an
"external database" (cf recent thread about virtual tables and access to big
external databases).
According to you, would it be a good idea to use this technique in order to
implement an alternative indexing technique (for "external tables") based on
SQLite btrees?
Thanks a lot for sharing about that, any help would be greatly appreciated,
Aladdin
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Mon, 7 Apr 2008 19:28:30 -0500
> Subject: [sqlite] Direct access to Btree routines in SQLite
>
> I am developing an application that needs both SQL capability and also
> simple Btree functions. I searched the SQLite FAQ for information about
> direct access to the Btree level, but was unable to find any help or
> examples. I have successfully gained access to the Btree routines after
> making minor changes to the amalgamation source which I then compiled with
> MS Visual Studio and linked with my application.
>
> Note: Since the Btree routines are only semi-documented, and my procedure
> requires changes to the SQLite source, there is no guarantee that this will
> work with future versions.
>
> Changes to SQLite amalgamation code:
>
> 1. Insert the following definition to make the routines externally callable
> (rather than static):
>
> #define SQLITE_PRIVATE
>
> 2. Extract the embedded Btree.h header file from the amalgamation and create
> a Btree.h file. The embedded section to be extracted is enclosed in:
> _BTREE_H_ Use the Btree.h file with your application along with sqlite3.h.
>
> 3. Add the following routine which returns a pointer to the Btree structure
> connected to a specified database handle:
>
> /*--------------------------------------------------------------------------
> ------------
> * Get a pointer to the Btree structure associated with an entry in the
> database table.
> */
> int sqlite3GetBtreePointer(sqlite3 *db, int dbIndex, Btree **pBt) {
> Db *pDb;
> /*
> * Get the Btree handle out of the database table.
> */
> pDb = &db->aDb[dbIndex];
> *pBt = pDb->pBt;
> /*
> * Finished
> */
> return(SQLITE_OK);
>
> -- This is all the changes required to SQLite --
>
> Here is an example program that creates a database with a Btree table,
> writes a couple of records to it and then verifies that it can be accessed.
> The routine can be called to either create a new database or open and check
> an existing one.
>
> /*-----------------------------------------------------------------------
> * Btree test.
> *
> * Input arguments:
> * Create = true to create a new database with a Btree table.
> * Create = false to open an existing database and check it.
> */
> void BtreeTest(bool Create)
> {
> static char *FileName = "C:\\Test\\Btree.db";
> int status,Result;
> unsigned int DataSize;
> Btree *bt;
> sqlite3 *db;
> BtCursor *BtCursor;
> sqlite3_stmt *pStmt;
> int BtPage = -1;
> char *Key1 = "1";
> char *Data1 = "Record 1";
> char *Key2 = "2";
> char *Data2 = "Record 2";
> char buf[100],Command[200];
>
> /*
> * Decide if the test run should create the database and table or open an
> existing one.
> */
> if (Create) {
> /*
> * Create a database with a Btree table.
> */
> /* Start with a new database */
> DeleteFile(FileName);
> /* Create a new database */
> status =
> sqlite3_open_v2(FileName,&db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,0);
> /* Get a pointer to the Btree for the primary database (# 0) */
> status = sqlite3GetBtreePointer(db,0,&bt);
> /* Create a Btree table within the database. We get back the root
> page in BtPage */
> status = sqlite3BtreeBeginTrans(bt,1);
> status = sqlite3BtreeCreateTable(bt,&BtPage,0);
> status = sqlite3BtreeCommit(bt);
> /* Write the root page number of the Btree to a table so that we can
> find it later */
> status = sqlite3_exec(db,"CREATE TABLE Configuration (BtreeName TEXT
> UNIQUE, RootPage INTEGER)",0,0,0);
> sprintf(Command,"INSERT INTO Configuration VALUES
> (\'MyBtree\',%d)",BtPage);
> status = sqlite3_exec(db,Command,0,0,0);
> /* Create a cursor to go with the Btree (BtPage is the root page
> index #) */
> status = sqlite3BtreeCursor(bt,BtPage,1,0,0,&BtCursor);
> /* Write a couple of records to the Btree table */
> status = sqlite3BtreeBeginTrans(bt,1);
> status =
> sqlite3BtreeInsert(BtCursor,Key1,1,Data1,strlen(Data1)+1,0,0);
> status =
> sqlite3BtreeInsert(BtCursor,Key2,1,Data2,strlen(Data2)+1,0,0);
> status = sqlite3BtreeCommit(bt);
> } else {
> /*
> * Open an existing Btree.
> */
> /* Open an existing database */
> status = sqlite3_open_v2(FileName,&db,SQLITE_OPEN_READWRITE,0);
> /* Get a pointer to the Btree structure associated with database 0
> */
> status = sqlite3GetBtreePointer(db,0,&bt);
> /* Get the root page number of the Btree out of our Configuration
> table */
> sprintf(Command,"SELECT RootPage FROM Configuration WHERE
> BtreeName=\'MyBtree\'");
> status = sqlite3_prepare_v2(db,Command,-1,&pStmt,0);
> status = sqlite3_step(pStmt);
> ASSERT(status == SQLITE_ROW);
> BtPage = sqlite3_column_int(pStmt,0);
> sqlite3_finalize(pStmt);
> }
> /*
> * Read from the Btree and verify the data.
> */
> /* Create a cursor associated with the Btree (the BtPage index selects
> the Btree) */
> status = sqlite3BtreeCursor(bt,BtPage,1,0,0,&BtCursor);
> /* Check if we can read the first record */
> status = sqlite3BtreeMoveto(BtCursor,Key1,1,0,&Result);
> status = sqlite3BtreeDataSize(BtCursor,&DataSize);
> status = sqlite3BtreeData(BtCursor,0,DataSize,buf);
> ASSERT(strcmp(buf,Data1) == 0);
> /* Check if we can read the second record */
> status = sqlite3BtreeMoveto(BtCursor,Key2,1,0,&Result);
> status = sqlite3BtreeDataSize(BtCursor,&DataSize);
> status = sqlite3BtreeData(BtCursor,0,DataSize,buf);
> ASSERT(strcmp(buf,Data2) == 0);
> /* Close the cursor */
> status = sqlite3BtreeCloseCursor(BtCursor);
> /*
> * Close the database.
> */
> status = sqlite3_close(db);
> /*
> * Finished
> */
> return;
> }
>
> I would like to see the Btree routines documented and supported.
>
> Phil Sherrod
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_________________________________________________________________
Créez votre disque dur virtuel Windows Live SkyDrive, 5Go de stockage gratuit !
http://www.windowslive.fr/skydrive/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
