Using SQL and direct BTree interface to SQLite

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Using SQL and direct BTree interface to SQLite

Brian Roe
Hello.

I'm interested in using SQLite both at the SQL and BTree levels in an
application.  That is, I would like to create tables and be able to run
queries using SQL while also inserting into, querying and updating
tables directly via the BTree-level C API calls (thus bypassing the SQL
subsystem entirely).  Is there any information on how I can accomplish
this without corrupting data, confusing SQLite, etc?  If no
documentation currently exists on this, I would be grateful for any
pointers from those who are familiar with the guts of SQLite.

Thanks,
Brian


Reply | Threaded
Open this post in threaded view
|

Re: Using SQL and direct BTree interface to SQLite

D. Richard Hipp
On Tue, 2005-09-27 at 09:36 -0600, Brian Roe wrote:
> I'm interested in using SQLite both at the SQL and BTree levels in an
> application.  That is, I would like to create tables and be able to run
> queries using SQL while also inserting into, querying and updating
> tables directly via the BTree-level C API calls (thus bypassing the SQL
> subsystem entirely).  Is there any information on how I can accomplish
> this without corrupting data, confusing SQLite, etc?
>

The only documentation is comments in the code.

Note that the BTree-layer interface is not a published API.
It is subject to change in very incompatible ways without
notice.  Such changes may be semantic only - meaning that
after upgrading SQLite you find that your application
compiles and links fine but generates subtle and difficult
to trace errors. The BTree interface is also fragile and
is not designed for external use.  It lacks much of the
parameter checking that normally occurs on library routines
since the Btree interface is not intended for that use.
You will find that generating segfaults and corrupting
your database files will be quite easy if you call the
BTree layer directly. For these reasons, calls directly
into the BTree layer are strongly discouraged.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Using SQL and direct BTree interface to SQLite

Brian Roe
D. Richard Hipp wrote:

>On Tue, 2005-09-27 at 09:36 -0600, Brian Roe wrote:
>  
>
>>I'm interested in using SQLite both at the SQL and BTree levels in an
>>application.  That is, I would like to create tables and be able to run
>>queries using SQL while also inserting into, querying and updating
>>tables directly via the BTree-level C API calls (thus bypassing the SQL
>>subsystem entirely).  Is there any information on how I can accomplish
>>this without corrupting data, confusing SQLite, etc?
>>
>>    
>>
>
>The only documentation is comments in the code.
>
>Note that the BTree-layer interface is not a published API.
>It is subject to change in very incompatible ways without
>notice.  Such changes may be semantic only - meaning that
>after upgrading SQLite you find that your application
>compiles and links fine but generates subtle and difficult
>to trace errors. The BTree interface is also fragile and
>is not designed for external use.  It lacks much of the
>parameter checking that normally occurs on library routines
>since the Btree interface is not intended for that use.
>You will find that generating segfaults and corrupting
>your database files will be quite easy if you call the
>BTree layer directly. For these reasons, calls directly
>into the BTree layer are strongly discouraged.
>
>  
>
OK, excellent information; thank you.

Let me rephrase the question slightly:  I'm interesting in bypassing the
SQL layer when possible, while not breaking compatibility with SQL.  I
am not specifically interesting in using BTree-layer calls, that just
seemed to be the obvious next layer down below the SQL subsystem.  
Perhaps the use of EXPLAIN would show the way to implement certain types
of common accesses I expect to be done frequently, such as inserting one
row, selecting one row using a unique key or updating one row.  Then I
could correlate the virtual machine instructions with specific supported
API calls?  Meanwhile, SQL would still work for reporting or other types
of ad-hoc queries.  Or is there a better way?  Thanks.



Reply | Threaded
Open this post in threaded view
|

[OFF TOPIC] How to unsubscribe the mailing list?

Cláudio Leopoldino

Thanks,

Cl?udio Leopoldino

__________________________________________________
Fa?a liga??es para outros computadores com o novo Yahoo! Messenger
http://br.beta.messenger.yahoo.com/ 
Reply | Threaded
Open this post in threaded view
|

RE: Using SQL and direct BTree interface to SQLite

Thomas Briggs
In reply to this post by Brian Roe
 

> Perhaps the use of EXPLAIN would show the way to implement
> certain types
> of common accesses I expect to be done frequently, such as
> inserting one
> row, selecting one row using a unique key or updating one
> row.  Then I
> could correlate the virtual machine instructions with
> specific supported
> API calls?  Meanwhile, SQL would still work for reporting or
> other types
> of ad-hoc queries.  Or is there a better way?  Thanks.

   I think you'll find that you'd have to replicate so much of what's
done after the SQL is compiled that there would be little (almost
nothing) saved in terms of execution time and an immense increase in
complexity.  If you prepare a query and execute repeatedly there really
isn't any penalty to "using the SQL layer".

   -Tom
Reply | Threaded
Open this post in threaded view
|

Re: Using SQL and direct BTree interface to SQLite

developir@yahoo.com
In reply to this post by D. Richard Hipp
I was also looking to do something similar with tabular type data
that exists in my application and create "synthetic" Sqlite tables
without actually populating the Sqlite database. i.e., I just wanted to
leverage the excellent SQL select engine of Sqlite to query the transient
data that is already in RAM in my app (typically in large arrays).
This would not only save RAM, but increase the overall speed of the program.
But after looking at the Btree API, I concluded that making such a
"synthetic table" hook was far from trivial. Such logic would have to
be put between the VDBE layer and the Btree layer - a major code redesign.

--- "D. Richard Hipp" <[hidden email]> wrote:

> On Tue, 2005-09-27 at 09:36 -0600, Brian Roe wrote:
> > I'm interested in using SQLite both at the SQL and BTree levels in an
> > application.  That is, I would like to create tables and be able to run
> > queries using SQL while also inserting into, querying and updating
> > tables directly via the BTree-level C API calls (thus bypassing the SQL
> > subsystem entirely).  Is there any information on how I can accomplish
> > this without corrupting data, confusing SQLite, etc?
> >
>
> The only documentation is comments in the code.
>
> Note that the BTree-layer interface is not a published API.
> It is subject to change in very incompatible ways without
> notice.  Such changes may be semantic only - meaning that
> after upgrading SQLite you find that your application
> compiles and links fine but generates subtle and difficult
> to trace errors. The BTree interface is also fragile and
> is not designed for external use.  It lacks much of the
> parameter checking that normally occurs on library routines
> since the Btree interface is not intended for that use.
> You will find that generating segfaults and corrupting
> your database files will be quite easy if you call the
> BTree layer directly. For these reasons, calls directly
> into the BTree layer are strongly discouraged.
>
> --
> D. Richard Hipp <[hidden email]>
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com