Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> > I can imagine that a version of Sqlite which does not include its SQL
> > compiler and which uses precompiled VDBE code would provide similar
> > functionality to DeviceSQL, particularly if the Sqlite compiler were
> > extended to generate VDBE from PL/SQl. I can imagine that the higher
> > information density of the VDBE code could deliver the advantage =of a
> > smaller memory footprint.
>
> I thought that this already existed as a commercial product that Richard
> offered, but I can't find any reference to it on the paid support page
> at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was
> an offline compiler that ran on a PC and generated VDBE code to execute
> SQL statements, along with source code for a runtime execution engine
> that would execute those pre-compiled statements. The execution engine
> could be built for any target, and was much smaller than SQLite because
> it eliminated the parser and code generator functionality. Does this
> exist, or was I just imagining it?
>
That would be the Serialized Statement Extension, SSE.
The SSE provides the programmer with two new APIs:
int sqlite3_serialize(sqlite3_stmt*, void**, int*);
int sqlite3_deserialize(sqlite3*, void*, int, sqlite3_stmt**);
The first routine takes an SQL statement that was generated by
sqlite3_prepare() and converts it into a form that can be stored
on disk or compiled into a program. The second routine does the
reverse; it takes the serialization of a statement and converts it
back into a working SQL statement that can be used just like any
other statement created by sqlite3_prepare().
You compile SQLite normally on your development workstation, but
for you embedded target you add -DSQLITE_OMIT_PARSER to leave off
the parser. By omitting other optional features (date/time functions,
views, triggers) you can get the size of the library down to the 70KiB
range or less.
On a workstation, you can sqlite3_prepare() statements, then hand
them to sqlite3_serialize(). The results can be hard coded into
C programs to be manually deserialized later, if you like, though
that is a lot of work. A simpler approach is to use the special
sqlite_statement table:
CREATE TABLE sqlite_statement(
id INTEGER PRIMARY KEY,
sql TEXT,
serial BLOB
);
A new API is available that will automatically extract and deserialize
an SQL statement from the sqlite_statement table given its id number:
int sqlite3_fetch_statement(sqlite3*, int id, sqlite3_stmt**);
The idea here is that the SQL statements needed by an application can
be inserted as plain text into the sqlite_statement table. For
example:
INSERT INTO sqlite_statement(sql) VALUES('SELECT * FROM table1');
After many such statements are inserted, they can all be serialized
as follows:
UPDATE sqlite_statement SET serial = sqlite_serialize(sql,id);
Then the complete database can be moved from the development platform
over to the embedded device and the embedded device can use the
sqlite3_fetch_statement() API to extract the statements it needs to
execute.
To be useful, your precompiled statements will normally contain
parameters (ex: "INSERT INTO tx VALUES(?,?,?)") and the embedded
application will using sqlite3_bind_xxx() interfaces to attach
values to these parameter prior to invoking sqlite3_step().
The SSE has not been kept current with the base SQLite. But if there
is interest, we could resurrect it easily enough.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
