Composr Tutorial: Using SQLite with Composr

Written by Patrick Schmalstig
This tutorial provides some advice targeted towards SQLite.

Composr has internal 'support' for many different database backends and is intentionally written with a combination of simple common-denominator SQL and use of an abstraction API. However, officially, only MySQL is supported due to the significant effort testing across new versions of Composr and the database backends involved. Different database systems vary in all kinds of surprising and subtle ways. Those wanting to do extra testing and bug fixing can put fixes back into the mainline version of Composr if they choose to.
Composr functionality that only works on MySQL can be identified by looking at the Composr feature tracker.

Table of contents

  1. Composr Tutorial: Using SQLite with Composr
    1. Installing with SQLite
    2. Stability
    3. Security
    4. Scalability
    5. Upgrade limitations
    6. See also

Installing with SQLite

SQLite is a free and open source flat-file database library. PHP has a native extension, sqlite3, to use it. All you have to do is enable this extension and install your Composr site while selecting SQLite3 as your driver. Because SQLite uses flat files, you do not have to install database servers. Composr will create your database under data_custom/sqlitedb/. This makes SQLite a great option for those who have limited to no support for database services from their web host. It is also great for use with basic Composr sites.

Stability

When used with its default settings, SQLite is very stable. It is ACID-compliant and highly resistant to corruption.

Be aware that SQLite does not have strict data type checking. It is theoretically possible, for example, to save a string in an integer field. Composr has its own software-level type checking. So in most cases, this should not be an issue.

Security

SQLite is only as secure as the database file(s). Unlike most database servers, SQLite does not have user accounts to control access or privileges to the database. Therefore, assume that anyone who can read the database file can read the contents, and anyone who can write to the file can write to anything in the database. Also, be aware that the contents are not encrypted; if someone downloads your database file, then they can browse the contents using any SQLite browsing tool.

Scalability

We do not recommend using SQLite for sites beyond simple complexity or light traffic. While SQLite has very fast read speeds, it has slow write speeds. And only one write operation can be performed on the database as a whole at a time.

Upgrade limitations

SQLite does not support modifications to the database schema; entire tables have to be re-created when a field is renamed, or its type or primary key changed. Due to the complexity, Composr does not currently support these operations. You may experience issues after upgrading between versions of Composr when changes to the database schema are necessary (the upgrader cannot currently apply them to your site if you are running SQLite).


See also


Feedback

Please rate this tutorial:

Have a suggestion? Report an issue on the tracker.