Home > kalkun > Using Kalkun and gammu-smsd with SQLite3

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

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

Advertisements
Categories: kalkun Tags: , ,
  1. June 18, 2011 at 7:06 am

    Hi!
    I’ve problems to setup kalkun with sqlite3. When I click on “Run database setup”
    I have this error:

    “——-
    A Database Error Occurred

    Error Number: 1

    near “)”: syntax error

    CREATE TABLE “user_group” ( “id_group” INTEGER PRIMARY KEY AUTOINCREMENT, “id_pbk” INTEGER NOT NULL, “id_pbk_groups” INTEGER NOT NULL, “id_user” INTEGER NOT NULL, );
    ——-”
    Do you have any idea?
    My system is Debian Lenny and Gammu 1.29.0.
    Thak you in advance. 🙂

  2. June 18, 2011 at 10:50 am

    @Davide: Fixed on commit #7e77257. Thanks for reporting.

  3. June 23, 2011 at 7:37 am

    Thank you Azhari!!!
    In the end I’ve got some problems. I decided to use mysql
    (with libdbd-mysql it works).
    Now I can send sms, but when I receve one, I have notification(see here http://i52.tinypic.com/f087md.jpg) but I can’t see the sms! It appears in in “inbox” table
    of the database but not in “user_inbox” table.

    Have you any idea?

    When I received a sms, in the shell that started gammu-sms
    appears these warning:
    debian:/home/davide# gammu-smsd -c /etc/gammu-smsdrc
    gammu-smsd[3423]: Process failed with exit status 127
    gammu-smsd[3423]: Process failed with exit status 127

    Thank you!! 😉

  4. June 24, 2011 at 9:45 am

    It means that the daemon (scripts/daemon.sh) failed to executed, try to run it manually. Make sure you’ve already configure the daemon.sh and daemon.php. Also make sure daemon.sh is executable, check permission.

  5. July 2, 2011 at 5:33 pm

    Azhari Harahap :
    It means that the daemon (scripts/daemon.sh) failed to executed, try to run it manually. Make sure you’ve already configure the daemon.sh and daemon.php. Also make sure daemon.sh is executable, check permission.

    Thank you!!
    Now it’s all ok!!!
    I didn’t have the package php5-cli installed and the script daemon.sh was not configured!!

  6. August 17, 2011 at 8:14 am

    Great tutorial. THanks

  7. bungsuputra linan
    December 11, 2011 at 1:19 am

    I have successfully installed kalkun. I can also also do gammu-sms-inject and my message got sent successfully. I can receive new sms in kalkun, too. However, I still cannot send from inside kalkun.

    My configuration is:
    version: kalkun 0.40
    OS: ubuntu 10.04
    database type: sqlite3
    modem: huawei e220

    Is there a way to debug this problem?
    Thank you in advance.

  8. December 11, 2011 at 12:58 pm

    bungsuputra linan :

    Is there a way to debug this problem?
    Thank you in advance.

    Try to activate gammu-smsd log, it seems that the problem is there.

  9. spirou91
    April 19, 2013 at 8:34 am

    Hi!
    I’ve problems to setup kalkun with sqlite3. When I click on “Run database setup”
    I have this error:

    ——————
    table user_settings has 10 columns but 9 values were supplied

    INSERT INTO “user_settings” VALUES (1, ‘green’, ‘false;–\nPut your signature here’, ‘false’, 20, ‘true;background.jpg’, ‘default’, ‘english’, ‘asc’);
    ——————
    kalkun 0.7.1
    gammu 1.32

    what could I do?

    Thanks

  10. April 19, 2013 at 3:41 pm

    There should be a default value for `country_code` field, you can simply change it to:

    INSERT INTO “user_settings” VALUES (1, ‘green’, ‘false;–\nPut your signature here’, ‘false’, 20, ‘true;background.jpg’, ‘default’, ‘english’, ‘asc’, ‘US’);

  11. spirou91
    April 21, 2013 at 10:16 pm

    Thanks you!
    I still have en error during database setup:

    ————–
    A Database Error Occurred
    Error Number: 1

    near ” “: syntax error

    DELETE FROM kalkun WHERE
    ————–

    Any idea?
    I don’t know where search.

  12. April 22, 2013 at 4:21 pm

    No idea, can you try upgrade using manual way? just import the sql file located on media/db/sqlite_upgrade_kalkun.sql

  13. spirou91
    April 22, 2013 at 7:59 pm

    I don’t understand how to do this. It’s not an upgrade but a first installation.
    I have so much problem with kalkun. Maybe it’s better install an old version? What do you think?

    Thanks for all your help.

  14. spirou91
    April 22, 2013 at 8:37 pm

    Problem is the same with kalkun 0.6 😦
    near ” “: syntax error
    DELETE FROM kalkun WHERE

  15. April 23, 2013 at 4:13 pm

    You can import it using sqlite command line, it should be something like this:

    sqlite3 kalkun.sqlite3 < media/db/sqlite_kalkun.sql

  16. spirou91
    April 23, 2013 at 6:38 pm

    Ok but if I manualy import database, how can I say to kalkun: “that’s ok, setup is done”?

  17. April 24, 2013 at 4:40 pm

    Just delete “install” directory

  18. spirou91
    April 25, 2013 at 11:23 am

    Thanks a lot!
    I have one last problem:
    I’ve imported database as explain and deleted install folder.
    I tried to connect to kalkun account on the login page but I get a
    “Error HTTP 500 (Internal Server Error)”

    Where does it come from?
    I changed all chmod on 777 to be sure and all files belongs to www-data.
    (Database is correctly located in database.php)

    Sorry for all those questions, I feel like a noob 😦

  19. April 26, 2013 at 4:51 pm

    Check web server error log for more details

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: