TQ
dev.com
Blog about software development
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 "pragma" statements. These statements are equally powerful, but less flexible as they cannot be combined with "SELECT" or "WHERE" clauses.
I decided to use the pragma statements to make a "pseudo" INFORMATION_SCHEMA for SQLite. It consists of a set of 4 tables:
Subscribesys/version" from "pragma schema_version;".sys/tables" from "select name from sqlite_master where type='table';".sys/columns" from "pragma table_info('table');" for all tables.sys/foreign_keys" from "pragma foreign_key_list('table');" for all 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.
In order to get started with PHP-CRUD-API and SQLite you need a web host that supports SQLite 3 and PHP. If you have that you need to download these two files:
Store "api.php" in your web root folder and "blog.db" 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 the "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.
PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.