Allow apsw to be used as an extension about apsw HOT 10 CLOSED

From rogerbinns on September 25, 2008 18:11:29

On further thought this is practically impossible to do safely. The connections are
allocated outside of apsw so we have no way of tracking when they are destroyed or to
prevent the destruction. That would result in memory errors if there is Python code
hanging onto any connection references.

from apsw.

From rogerbinns on October 05, 2009 11:11:37

Issue 78 has been merged into this issue.

from apsw.

From rogerbinns on October 05, 2009 11:24:14

For collations and virtual tables it isn't a problem. I really need functions to
have a destructor. See http://www.sqlite.org/src/info/0322cd49c0 where I have
documented a hacky workaround.

In answer to issue #78 this is possible but hard.

from apsw.

From rogerbinns on October 05, 2009 11:45:51

For #78 note that extension loading is usually disabled by default. You can check to
see if you Java etc environments support it with my test extension. The code is at http://code.google.com/p/apsw/source/browse/src/testextension.c (choose "View raw
file") and you need sqlite3ext.h around. Compile with:

gcc -shared -o testextension.so textextension.c

Try to load the result into whatever other language you are using. Normally you will
have to call a function to enable extension loading and then a second one to actually
do the loading providing the filename ("testextension.so" in this case). You should
then be able to do this:

SELECT half(4);

I have looked at some random SQLite wrappers to see if they provide extension loading.

No SQLiteJDBC
Yes DBD::SQLite
No sqlite3-ruby

For C and C++ code the SQLite library APIs can be called directly. It is also
possible to get APSW to create the connections and also use them from C/C++.

from apsw.

From phrrngtn on October 05, 2009 14:22:40

Thanks Roger. We can compile the languages bindings so that extensions are enabled by
default and then load them via SQL:

  select load_extension('apsw.so');
  select apsw_boot('/path/to/python/code.py');

as opposed to sqlite3_load_extension()

It would be handy to have a factory function in apsw that turned a sqlite3 pointer
into a Connection. This would allow us to poke in all the various entry-points via
Python.

Your point about the function garbage-collection seems correct but I am not so sure
it would turn out to be a problem for us in practice. Is there any SQLite API call
that allows one to test the validity of the sqlite3 pointer in a safe/non-destructive
way? If so, I would be happy to stash all such pointers and test them for validity
each time we call apsw_boot().

pjjH

from apsw.

From rogerbinns on October 05, 2009 14:42:10

You still have to call sqlite3_enable_load_extension at the C api level before being
able to load an extension (via C api or SQL). However the specific point I was
making is that the vast majority of the time SQLite is compiled with
-DSQLITE_OMIT_LOAD_EXTENSION (for example look at the libraries shipped with your
Linux or Mac operating systems.) Fortunately this issue won't affect you since you
compile SQL yourself.

Making APSW a loadable extension will take quite a bit of time. It can't
simultaneously be a Python module and SQLite extension due to the way SQLite symbols
are resolved (os library and via structure indirection respectively) although I could
possibly do some silly tricks with ELF to join both implementations into one .so.
Anyway this would be quite a while off.

There is already a function that will return a sqlite3* from an APSW connection - see http://apsw.googlecode.com/svn/publish/connection.html#apsw.Connection.sqlite3pointer It would be relatively trivial for you to code the following in C:

• Initialize Python
• Import apsw
• Provide an API to your C code for opening a Connection that does it via APSW

This approach also helps deal with all the memory issues. I'd make your C routines
have an opaque type that is really the APSW Connection object under the hood. You
would never need to call sqlite3pointer on each use within C and abort if it returns
zero (means someone closed the Connection).

There is no way to tell if a random number is a valid sqlite3 pointer just as you
can't tell if a random number is a valid char*.

I have created related issue #79 which is to allow Connections to wrap a sqlite3
pointer supplied by someone else. This is also pretty tricky again because C has no
memory/object lifetime management functionality at all. However some solution would
have to be arrived at for that as a prerequisite for this ticket anyway.

As for the apsw_boot function, that approach is frowned on since it allows any SQL to
load any arbitrary Python. This is the same reason extension loading is disabled by
default in SQLite and one reason why many just compile it out completely. I had been
considering something like environment variables although maybe a reasonable
compromise is an environment variable specifying directories that eligible extension
code could be loaded from ...

from apsw.

From phrrngtn on October 05, 2009 15:27:32

I would like to emphasize using apsw within an extension as opposed to making apsw
itself an extension. I am assuming that a sqlite3 language binding (such as Perl,
Java, C, Ruby, whatever) consists of some C code which dynamically links to the
sqlite library. I also assume that if I create a Python interpreter within a sqlite
extension module and then load up apsw that apsw will use the same code in the sqlite
shared library and that that the database handle which loads the extension module
would be usable by the apsw code (assuming that we could cons up a Connection from a
sqlite3 pointer passed in to apsw)

Of course, things always look this simple when one has not attempted to code it up!

Thank you for the very useful information.

Maybe I am just all loosey-goosey but I look at SQL being able to load arbitrary
Python as a good thing. Can't Python load arbitrary Python, do 'eval' and all that
other fun stuff? What is different about letting SQL do it? I promise I am not trying
to be obtuse here. Given that all this is implemented client-side, I don't understand
how the issues here are much different from, say, being careful that code doesn't
manipulate PATH, rc files, LD_LIBRARY_PATH etc.

pjjH

from apsw.

From rogerbinns on October 05, 2009 21:43:49

Using APSW within an extension vs making it a loadable extension is a trivial amount
of code difference.

Your assumptions about load order and library usage are not true for SQLite loadable
extensions. You would however get lucky most of the time but have one of the world's
worst debug sessions if there was a mismatch or funky stuff happening with threading.
And experience on one platform doesn't mean the exact same thing will happen on another.

A SQLite extension is not supposed to use the SQLite library directly. If it did
there is the possibility of the library being loaded into memory twice at different
addresses. The initialization routine for an extension is handed a pointer to a
structure of function pointers and should only use SQLite routines that way. That
ensures with 100% certainty that the exact same routines are used for a particular
handle. See the file sqlite3ext.h to get code level details. This is why I keep
mentioning having to compile APSW twice. The first time is as is now where routines
are called directly and the second is where the routines are called via the passed in
pointer (which is also missing several methods).

Lest you think having multiple copies of SQLite in memory won't happen, it actually
does especially on Mac where CoreData and several other libraries include SQLite.
Until the amalgamation it was a really nasty problem where you could link against one
version and location of the library and at runtime end up using a different one
despite your best efforts. This is why the build instructions go to such great
lengths to ensure you use the amalgamation which embeds it statically inside APSW and
no other SQLite in the process has any effect.

The arbitrary loading stuff concerns are because people could be supplying you with
SQL. Maybe a customer gives you a dump or someone else gives you their bookmarks
from Firefox. Extension loading is off by default because then there is no way for
that SQL to cause you grief. (We'll ignore attaching databases :-) I take the same
attitude for using APSW as a Python extension. You should be able to then load
arbitrary SQL and not have it be able to execute arbitrary Python unless you
specifically allowed it. While it may seem easy to run 'grep' on provided SQL that
is a dangerous hole to go down - bad guys will always think of ingenious ways around
whatever you look for. In summary the attitude is closed by default rather than open
by default.

from apsw.

From phrrngtn on October 06, 2009 02:58:04

very good explanation. I had totally missed the point about extensions using the jump
table, struct sqlite3_api_routines, defined in sqlite3ext.h

pjjH

from apsw.

From rogerbinns on May 23, 2010 21:56:32

I have decided not to try and implement this for the following reasons:

• It would seriously complicate the code
• It would require explaining why you have to compile twice and have two apsw's lying
  around
• It would be restricted to the subset of people who compile their own SQLite so that
  extension loading is enabled
• Those same people would have to be unable to use Connection.sqlite3pointer

That said if someone else does the implementation then I'll gladly accept it.

Note that issue 79 (create a Connection from a supplied sqlite3 pointer) is still
open at this time and is probably what I'll do.

Status: WontFix

from apsw.