14 Oct 2018 - by 'Maurits van der Schee'
Yesterday I released version 2 of PHP-CRUD-API, the TreeQL reference implementation in a single PHP file. About 6 months ago I wrote about my progress on version 2. I was expecting it would take me about 2 months until I could release a final version 2. It took 6 months and I created 8 beta releases in the process. I'm very thankful to the people that were trying these beta versions out and for the issue reports that I have received.
JSON support looked really promising when it was introduced. Unfortunately I never got it working on a level that I was happy about. The database support was limited as SQL Server and older MariaDB versions do not have a JSON data type. The filtering on JSON properties was not standardized and hard to implement. In version 2 JSON fields are supported as strings. This means you can still read and write them as strings containing JSON, but there are no structural or performance benefits.
SQLite does not support geospatial (GIS) queries. It also has problems supporting the boolean (and the JSON) data type. It was not used a lot as it had performance problems in high performance use cases. Although I understand SQLite use case (quick mock-ups) I decided to remove support for SQLite for now. This allows me to focus on other (more important) features.
Version 1 was doing database reflection calls (requesting a table's column definitions) on every request. In version 2 these calls are cached to facilitate high performance usage.
In order to extend the software you can now implement "middleware". Middleware allows you to define a handler that gets to modify the request that the browser is sending to the router. It also allows to modify the response before it gets send back to the browser.
The most requested feature was an easier to use authentication system. For this the "basic authentication" middleware is now provided. In order to easily hook up social (such as Facebook) authentication systems there is support for an authentication provider (such as Auth0) with the provided JWT authentication middleware. You can also look at PHP-API-AUTH: a single file PHP script that acts as an authentication provider.
Other improvements that have been implemented are:
In version 1 the REST end-point the "users" table was "api.php/users" and in version 2 it is "api.php/records/users". This is because there is also support for adjusting the column definitions of the "users" table on the "api.php/columns/users" end-point. This functionality is still work in progress, but the read-only part is fully working. Since the data types are standardized (based on ODBC), they are not specific to the underlying database engine.
Get it here: https://github.com/mevdschee/php-crud-api