DEPRECATED: PLEASE USE: https://github.com/pharo-rdbms/Pharo-SQLite3 now

---

NBSQLite3 for Pharo

Project info

The "NBSQLite3 for Pharo" is a project to provide an API and access to the SQLite3 database from within the Pharo image.

With this tool you can use the small relational SQL engine into own Pharo projects.

Project location

The project is located on STHub at http://smalltalkhub.com/#!/~PharoExtras/NBSQLite3/settings

History

SQLite is a small SQL database engine that you can use if you are in need of a leightweight RDBMS solution for you application. There a various bindings to it in all the different programming languages.

For Smalltalk initially there was a project called "SQLite" with a binding to SQLite3 database engine. The native calls were implemented using the foreign function interface project "FFI". This work initially started in Squeak and was later ported over to Pharo and is now hosted on STHub in project "SQLite".

Afterwards, in March 2014 a blog post about wrapping the SQLite3 API using NativeBoost appeared on the Samadhiweb blog.

The author "Pierce" has provided the initial implementation as a package on SqueakSource3. Aftwards it was cleaned up and refactored a little bit and is now provided as a project "NBSQLite3" on SmalltalkHub.

License

NBSQLite3 is licensed under MIT License.

Installation

You can access NBSQLite3 from the Pharo configuration browser. Just select "Tools" -> "Configuration Browser" from the world menu, enter "NBSQLite3" and install the stable version.

Alternatively to install the packages manually into your Pharo image just evaluate:

Gofer new
    url: 'http://smalltalkhub.com/mc/PharoExtras/NBSQLite3/main';
    package: 'ConfigurationOfNBSQLite3';
    load.
((Smalltalk at: #ConfigurationOfNBSQLite3) project stableVersion) load.

If you need access to the SQLite3 database engine you need at a minimum the "NBSQLite3-Core" package which is loaded by the ConfigurationOfNBSQLite3 class.

This package includes all necessary API to access the sqlite engine.

Use in your own application

Providing the library files

To use the sqlite3 database engine in own Pharo projects you need to install the "sqlite3" database engine first. Basically it is only a shared library component that has to be found by the Pharo virtual machine. Note that you have to use the 32 bit version even when running on a 64 bit machine.

Library files on Windows

For instance on Windows operating system you can download the "sqlite3.dll" component from http://www.sqlite.org/ and copy it into the same directory where the Pharo VM (Pharo.exe) is.

Library files on CentOS (Linux)

On CentOS linux you can use

sudo yum install sqlite.i686

to load sqlite3 in CentOS linux. Then copy the lib from /usr/lib into the pharo-vm/ folder and create a link:

ln -s libsqlite3.so.0.8.6 libsqlite3

The 'sqlite3' name is defined in SqliteLibrary>>moduleName.

Troubleshooting and library files on other platforms

Basically install libsqlite3.so so that it is accessible to the Pharo virtual machine and make sure you used the 32 bit version.

Check installation

After providing the library and loading NBSQLite3 as described before you can run the unit tests to see if anything is correctly setup and the virtual machine is able to find the sqlite3 engine.

Use in your code

Creating a connection

The most important class to use for creating a database is the class NBSQLite3Connection. For instance the following expression:

NBSQLite3Connection on: 'myfile.db'.

will setup a new connection with the sqlite engine using the database file myfile.db.

Open a connection

To open a connection just send the message #open to the connection:

(NBSQLite3Connection on: 'myfile.db') open.

Execute a statement

To execute a statement just use the #execute: selector:

|db|
db := NBSQLite3Connection on: 'myfile.db'.
db open.
db execute: 'CREATE TABLE IF NOT EXISTS BLOGPOSTS (id integer primary key, post text);'.

Lets also add some data by inserting a new row:

    db execute: 'INSERT INTO BLOGPOSTS values (1, "HelloWorld");'.

And query the database again:

db execute: 'SELECT * FROM BLOGPOSTS;'.

Working with prepared statements

With the #prepare: message you are able to retrieve a prepared stament:

s := db prepare: 'select * from BLOGPOSTS'.
s basicExecute: [  :row |
    row dataValuesAvailable inspect ].
s finalize.

Working with transactions

If you need transactions use the messages #beginTransaction and #rollbackTransaction or #commitTransaction.

res := db beginTransaction.

rs := db execute: '...'.
rs close.

res := db commitTransaction.

Other

Checkout the test suite and the examples in the package NBSQLite3-Examples.

Packages

• NBSQLite3-Core - package with the core, contains anything you need in an own app
• NBSQLite3-Tests-Core - package with the SUnit tests for the core package
• NBSQLite3-Examples - package with examples
• NBSQLite3-Glorp - support for GLORP
• NBSQLite3-Tests-Glorp - tests for GLORP

Testing

The package comes with many tests in the package NBSQLite3-Tests-Core and NBSQLite3-Tests-Glorp. Just use the SUnit TestRunner to run them.

CI Jobs

The project is controlled by a CI server under: https://ci.inria.fr/dbxtalk/view/SQLite/job/NBSQLite3-NativeDriver-SQLite