SQLite

Category:Core | Category:Core::Community | Category:Core::Database

The following was written and tested using CodeIgniter 1.7.2.

This page doesn't serve as a comprehensive guide to using sqlite with CodeIgniter, but instead as a foundation to getting started.

Versions Supported

Upon my testing, SQLite version 3 databases weren't recognized by CodeIgniter and returned the following error when trying to use one: [pre]A Database Error Occurred file is encrypted or is not a database[/pre]

Upon creating a SQLite v.2 database file, it worked fine without errors, so it appears v.3 isn't supported yet, although there is a page on PDO_SQLite3 in this wiki.

CodeIgniter SQLite v.2 Quick Setup

As of PHP 5, the php_sqlite extension is enabled by default. In Linux or Unix operating systems, you must build SQLite as a shared extension, although most pre-built packages are already configured correctly.

More information can be found at http://us.php.net/manual/en/sqlite.installation.php.

Getting Started

Create a /sqlite/ subdirectory, as in:

CodeIgniter/system/database/sqlite/

Open your system/application/config/database.php file for editing.

You only need to define two things here, the database filename and the database driver:

$db['default']['hostname'] = "";
$db['default']['username'] = "";
$db['default']['password'] = "";
$db['default']['database'] = "./system/database/sqlite/blog.sdb";
$db['default']['dbdriver'] = "sqlite";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

SQLite databases do not require username/password/host. And you also do not have to create the file first, but instead it will create it automatically if it doesn't exist.

File Permissions

You MUST ensure that PHP has proper read/write permissions to that directory or else it will cause errors.
I recommend using 774 for Linux or g+w and change the group to the correct user. In Ubuntu, the group is www-data and you would use this:

chgrp www-data blog.sdb
chmod g+w blog.sdb

You can find your user/group for PHP by using phpinfo() and look under the apache2handler section.

Once you have made the changes, try loading your CodeIgniter page and see if you get any errors. If not, then everything worked correctly and you're ready to start using your SQLite database!

The Database File Path

Just as a FYI, CodeIgniter calls the database file from the root path of the CodeIgniter's installation. This is important when using relative paths instead of absolute paths. If you noticed in my settings example above, I placed a "." just before my forward slash. This is very important because it basically states "from here" relating to where it's called from.

You can however specify absolute paths if you like. That's it for the setup.

Additional Notes

If you're looking for a phpmyadmin equivalent for sqlite, the best I've found yet is http://sqliteadmin.orbmu2k.de/.

I also recommend reading the notes on SQLite databases on the PDO_SQLite3 page of this wiki.

New: For CodeIgniter 2.x use this to make SQLite work.

$db['default']['hostname'] = 'sqlite:/var/my.db';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'pdo';
$db['default']['dbprefix'] = '';

Credit: http://stackoverflow.com/questions/8715041/using-sqlite-with-codeigniter-strange-error