smalltalk programming for the web

« Previous  |  Next »

SQLite Loadable Extensions

4 March 2018

SQLite has the ability to load extensions at run-time. I've now implemented this functionality in UDBC-SQLite.

An SQLite extension is built as a .so/dylib/dll shared library file. Let's use SQLite's rot13 extension as our example. The source file rot13.c is located in the SQLite source code's ext/misc directory. To build the rot13 extension, also download the amalgamation. Unzip the amalgamation and copy rot13.c into its directory. Build the extension:

% gcc -fPIC -shared -o -I. rot13.c

Verify that the extension works:

% sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select rot13('qwerty');
Error: no such function: rot13
sqlite> .load "./"
sqlite> select rot13('qwerty');
sqlite> select rot13('djregl');

For use with Pharo, copy into the Pharo VM directory where all the other .so files are.

Next steps are done in Pharo. For the purpose of this blog post, I downloaded a fresh Pharo 60536 64-bit image. Start the image and install GlorpSQLite from the Catalog browser, which installs the latest UDBC-SQLite. (This also installs Glorp, of course. If you run Glorp's unit tests from Test Runner you should get all 890 tests passed.)

In a playground, run this snippet and Transcript should show, first, the text "no such function: rot13" and then the rot13 outputs.

| db rs r1 r2 |
Transcript clear.
db := UDBCSQLite3Connection openOn: ':memory:'.
[   [ db execute: 'select rot13(''qwerty'')' ]
      on: UDBCSQLite3Error
      do: [ ex: | Transcript show: ex messageText; cr ].
    db enableExtensions;          "<== New stuff."
      loadExtension: ''.  "<== New stuff."
    rs := db execute: 'select rot13(''qwerty'') as r1'.
    r1 := rs next at: 'r1'.
    Transcript show: r1; cr. 
    rs := db execute: 'select rot13(?) as r2' with: (Array with: r1).
    r2 := rs next at: 'r2'.
    Transcript show: r2; cr.
] ensure: [ db close ]

Note the messages #enableExtensions and #loadExtension:. For security reasons, extension loading is disabled by default.

Tested on Linux latest Pharo 64-bit 60536.

Blog comments powered by Disqus