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
