Using Kalkun and gammu-smsd with SQLite3

It’s been a long time ago since i’ve started to thinking adding other database backend support besides MySQL. The problem is CodeIgniter (Kalkun is developed using CodeIgniter) and gammu-smsd both must support the database engine used, for example if CodeIgniter support PostgreSQL, than gammu-smsd must support PostgreSQL too, and so on. To make this dream comes true, i’ll have to “convert” all native SQL query to Codeigniter Active Record to make the query independent to all database engine because it’s handled by database driver.

Imagine that you want to install a software, but you only have SQLite3/PostgreSQL on your server, installing MySQL just for that can be huge problem. That’s the basic idea to support all database engine.

OK, for the first time, i’ve added SQLite3 support to Kalkun. But, it’s not as easy as i imagine to implement it. First, CodeIgniter doesn’t have official support for SQLite3, the driver only compatible with SQLite2, fortunately there’s community contribution that add SQLite3 through PDO (PHP Data Objects). So, you need pdo_sqlite extension on your PHP installation. Second, gammu-smsd also doesn’t have native support for sqlite3, otherwise it use libdbi.

Now let’s configure gammu-smsd to works with SQLite3 first. FYI, i’m using Mac OS X and gammu 1.29.0. Let’s install SQLite3 and libdbi drivers, i’m installing it using MacPorts.

$ sudo port install sqlite3
$ sudo port install libdbi
$ sudo port install libdbi-drivers

Next, compiling gammu with libdbi support. Make sure you get “Found libdbi: …” when running configure otherwise you can’t run gammu-smsd with SQLite3.

Create Kalkun database and import gammu database schema
Because of Kalkun New Installation, gammu database schema is not included on Kalkun source, now you’ve to import gammu database schema on your own (it’s included on gammu source eg. docs/sql/sqlite.sql). You can do the import using sqlite3 console:

$ sqlite3 kalkun.sqlite < docs/sql/sqlite.sql

or, if somehow it’s throw error “Error: near “/”: syntax error“, use this:

$ cat docs/sql/sqlite.sql | sqlite3 kalkun.sqlite

Or you can the import using SQLite3 client with GUI. I’m using SQLite Manager Firefox add-ons, off course you can use other SQLite client.

Next is import Kalkun database schema to the database, you can import it using your SQLite3 client or by Kalkun Installation. I’ll show you how to import using Kalkun installation. But before that you have to config the database first (it’s located on /application/config/database.php)

$db[‘default’][‘username’] = “”;
$db[‘default’][‘password’] = “”;
$db[‘default’][‘database’] = “sqlite:/path/to/database/kalkun.sqlite”;
$db[‘default’][‘dbdriver’] = “pdo”;

You only need to change the database path, SQLite doesn’t need username and password, and the driver should be pdo. It’s recommended to place your SQLite3 database outside your web root folder because of security issue.

Setting database permission, before launch Kalkun installation make sure the database is writable by web server user, you can give it world writable.

$ chmod 666 /path/to/sqlite3_db/
$ chmod 666 kalkun.sqlite

Next fire up Kalkun installation and choose SQLite3 on Database Setup.

Kalkun installation with SQLite3

Next, let’s configure gammu-smsd, open up your gammu-smsd configuration file a.k.a smsdrc

Service = SQL
Driver = sqlite3
database = kalkun.sqlite
dbdir = /path/to/sqlite3_db/

That’s all, now run your gammu-smsd, check logs and see if it’s working (no error). You can try sending SMS using gammu-smsd-inject:

$ gammu-smsd-inject TEXT 123456 -text “Hi, this is gammu sending using sqlite3”

Next, try sending SMS using Kalkun 😀

..

PS: SQLite is famously knows for it’s liteness, but it’s may slower with network access and lots of activity/user using the same resource. So make sure you know what are you doing before using it.

Implemented on commit #fa845a4

Advertisement