TQ
dev.com

Blog about software development

Subscribe

PHP-CRUD-API now supports SQLite

07 Apr 2016 - by 'Maurits van der Schee'

After 1 year of slowly growing PHP-CRUD-API another milestone is reached. Today SQLite support, the fourth supported database engine, is added to the project. This feature is added in order to facilitate fast prototyping as SQLite is easy to install and configure. I want to thank Antoine Weber for his feature request and contribution to deliver this feature.

A few months ago I did some research on the feasibility of a SQLite implementation. I found that there were sufficient reflection methods available for the full functionality of the reflective REST API. Other DBMS systems provide reflection using the INFORMATION_SCHEMA, which is a SQL standard. I found out that SQLite has similar functionality, but with non-standard with "pragma" statements. These statements are equally powerful, but less flexible as they cannot be combined with "SELECT" or "WHERE" clauses.

A "pseudo" INFORMATION_SCHEMA for SQLite

I decided to use the pragma statements to make a "pseudo" INFORMATION_SCHEMA for SQLite. It consists of a set of 4 tables:

Note that the tables have a forward slash in their name. This is allowed in SQLite and may prevent name collisions. The name is inspired by the "sys" schema in SQL Server. Ideally these 4 tables are always up-to-date, but it is very costly to run all these pragma queries on every API request. Fortunately SQLite supports a "schema_version" pragma statement. This is a number indicating the version of the structure of your database.

On every web request we check whether or not the version of the reflection tables (stored in "sys/version") equals the version returned by the "schema_version" pragma statement. As long as they are the same, the table contents do not need to be updated. This is quite an efficient caching mechanism, because normally the structure of the database does not change that often.

Another trick that is applied to speed up the SQLite engine is that "PRAGMA synchronous = NORMAL" is used to avoid flushing to disk on every transaction.

Usage

In order to get started with PHP-CRUD-API and SQLite you need a web host that supprts SQLite 3 and PHP. If you have that you need to download these two files:

  1. blog.db - a sample SQLite database file.
  2. api.php - the single file PHP REST API.

Store "api.php" in your web root folder and "blog.php" in a path that is not served (one directory up). In the bottom of "api.php" you need to uncomment the SQLite config block. Point to "database" parameter to the "blog.db" file you downloaded and start "api.php" in your browser. Now you can play around with the examples from the README. You can use Swagger Editor to view the documentation and try out some REST API calls. Choose "File" - "Import URL" and paste the URL of your "api.php" file.

Have fun and report issues and questions at Github.