PHP-CRUD-API v2 now supports SQLite

13 Apr 2020 - by 'Maurits van der Schee'

After 1 year of slowly growing PHP-CRUD-API v2 another milestone is reached. Today SQLite support, the fourth supported database engine, is (re)added to the project. It was removed with the introduction of v2. This feature is added in order to facilitate fast prototyping as SQLite is easy to install and configure. I want to thank Raymond Verbruggen for his feature request and his contribution to deliver this feature.

SQLite support on steroids

A few weeks ago I redid the research on the performance of a SQLite implementation. I found that since version 3.16 of SQLite there are new reflection methods available that allow better performance of my reflective REST API. Other DBMS systems provide reflection using the "information schema", which is a SQL standard. SQLite now has similar functionality, but non-standard with "pragma table functions". These functions differ from the "pragma statements" used to implement SQLite support in v1, as the new pragma table functions can be combined with "select" or "where" clauses, while the old pragma statements could not. This flexibility means we no longer need to create a "pseudo" information schema for SQLite. It also means we don't support older versions of SQLite, such as the version 3.11 that is used in Ubuntu 16.04.

New caching mechanism

In v1 a "pseudo" information schema was created for SQLite, which doubled as a structure cache. The "schema_version" pragma statement was used to retrieve a number indicating the version of the structure of your database. This number was then used to check whether or not the cache needed to be rebuild. This rebuild process was reported to take several seconds on large databases. This method is no longer used and the approach in v2 (for all database engines) is to use a structure caching mechanism that is time-based

Improved reliability

In v1 I needed to make writing the structure cache faster and for that I was using "PRAGMA synchronous = NORMAL". This avoids flushing to disk on every transaction. It was trading speed for reliability and this is no longer done (nor needed) in v2 as the cache is no longer stored in the database itself. In v2 the cache is stored (for all database engines) in a configurable backend that defaults to a temporary file on disk, but also supports "Memcache" for high performance applications.

Usage

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

blog.sqlite - a sample SQLite database file.
api.php - the single file PHP REST API.

Store "api.php" in your web root folder and "blog.sqlite" in a path that is not served (one directory up). In the bottom of "api.php" you need to adjust the config block. Set the "driver" to "sqlite" and point the "database" parameter to the "blog.sqlite" file you downloaded, like this:

    'driver' => 'sqlite',
    'address' => '../blog.sqlite',
    'username' => '',
    'password' => '',
    'database' => 'php-crud-api'

Now start "api.php/records/posts" in your browser and you will see the records from the SQLite "posts" table in JSON. You can play also play around with other examples from the README.

Limitations and future work

Although Spatialite could offer geospatial support in SQLite, this is not implemented. Implementing it means supporting the native SQLite3 driver instead of the PDO version. Binary fields are also not supported (for the same reason: lack of PDO support). Online structure changes are not supported by SQLite at all, so these are also not implemented (these tests are skipped). Last known issue is that you cannot set the type of an auto incrementing primary key to "bigint", it must be of type "integer".

Have fun and report issues and questions at Github.

PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.

TQ
dev.com