torniojaws/vortech-api

This implements a RESTful API for the Vortech website using PHP 7

Vortech API

The idea is to build a mostly RESTful API (no resource browser per-se - at least yet) that will be used in the Vortech website. As PHP7 is available on the webhost, that will be the target version. Most of the things should work in PHP5.6 also, but there are some PHP7-features in use that probably will not work in PHP5.x, such as typed parameters.

I would have gone with Python, but unfortunately the webhost does not allow installing programs in a shared environment, so no Python packages (like Flask and SQLAlchemy) can be installed.

Versions (dictated by the 3rd party webhost)

• PHP 7.1
• MySQL 5.5.48
• Apache 2.4

Project versioning

Until the first production release is done, the versions will stay as 0.x.x. The first production release will be tagged 1.0.0. The versioning will keep to the usual convention:

1.2.3
^ ^ ^
| | |- Small update - adds non-breaking extra features, does minor refactoring, or adds documentation
| |--- Big update - non-breaking major updates or database schema changes
|----- Major release - Something changed drastically, or there were breaking changes

The numbering will be incremental beyond 9, so after 0.9.x comes 0.10.x. A change in a value will reset the counter on the right side of it, so 0.4.2 becomes 0.5.0 and 1.2.10 becomes 2.0.0.

Starting idea

Create a normal RESTful API with the standard CRUD way for paths and access, eg.

• Create new things: POST /news with a JSON attached
• Read data: GET /albums/:id which will return a JSON
• Update existing data: PUT /guestbook/:id/comment with a JSON attached, and return result JSON
• Delete something: DELETE /users/:id which will return HTTP status 204

User's own actions happen via /me eg.

• GET /me/guestbook to get all guestbook posts from the user logged in
• POST /me/releases/:id to post a comment about a release
• PUT /me/news/:id/comments/:id to update a news comment I already posted before
• DELETE /me/shows/:id/comments/:id for example when deleting a comment

Updated ideas

After starting to implement some of the features, the need for extra features has come up:

• Updating only partial data is quite common. We'll implement eg. PATCH /releases/:id with a JSON
• Some endpoints will build from a few different tables. Should implement that also.

URL

The URL will be https://www.vortechmusic.com/api/1.0 with future versions being either /1.1 or /2.0

Documentation

The API documentation is also at: https://www.vortechmusic.com/api/1.0

Auth

Since we cannot install anything on the 3rd party webhost, a less-than-optimal way must be implemented using user login instead of eg. OAuth2. But this is not yet implemented, and must be implemented before putting to production. Probably by logging in, and if login is valid, we create a new session ID and then use that session to keep track.

Database

All queries will be done using PDO. User passwords will be hashed using a PBKDF2 implementation, which should be very secure.

Testing

Everything possible will be covered by PHPUnit 6.* tests with 100 % code coverage as the guideline. Run the tests in the project root with phpunit tests. For coding standards, PSR2 is used. Run the check in the project root with phpcs apps/ --standard=PSR2

Frontend

The frontend will be in a separate repository. It will be done using ReactJS and Bootstrap.

Setup instructions

See the instructions to see how to setup from nothing.

vortech-api's Issues

Using ->limit(1) will make it appear in the next SQL even if not added

The value remains set in the instance:

$sql = $this->select->select()->from('Videos')->where('VideoID = :id')->order('VideoID ASC')
    ->limit(1)->result();
$pdo = array('id' => $this->validVideoID);
$query = $this->database->run($sql, $pdo)[0];

$sql = $this->select->select('VideoCategoryID')->from('VideosTags')->where('VideoID = :id')
    ->order('VideoID ASC')->result();
// At this point, $sql does not contain:
// SELECT VideoCategoryID FROM VideosTags WHERE VideoID = :id ORDER BY VideoID ASC
// It contains:
// SELECT VideoCategoryID FROM VideosTags WHERE VideoID = :id ORDER BY VideoID ASC LIMIT 1
$tagResult = $this->database->run($sql, $pdo);
$tags = $this->arrays->flattenArray($tagResult, 'VideoCategoryID');
sort($tags);

The LIMIT 1 remains from the previous call (row 2). It should be removed from there before each new $this->select because it will break the query when we expect multiple results (eg. categories).

PHP 7.x compatibility

It appears the host does support php7, so let's see if the codebase can be converted to PHP7.

In the initial test with php7.1 and PHPUnit 6.2.3, the only error in the test cases was due to using __construct() within the tests, which caused a non-descript array_merge(): Argument #1 is not an array error in every test where __construct() was used. This did work with php5.4 and PHPUnit 4.8, but does not work anymore in php7 and PHPUnit 6.2.3 (mostly due to PHPUnit, though). It is very easy to fix by changing all __construct() entries to the recommended way of using setUp() instead in the test cases.

Implement action-based Patches

At the moment, patches are quite simple replacements or changes. It would be better to implement the proper action/instruction based patching like this:

[
    {"op": "add", "target": "categories", "value": 1},
    {"op": "replace", "target": "title", "value": "New value"},
    {"op": "remove", "target": "urls"}
]

Rename relational tables

At the moment, they are a bit varied depending on the tables they relate to. Change them to use a standardized way. This looks okay:

Photos 1..* PhotosAlbumsMapping 1:1 PhotoAlbums

Do the same for eg. News, Releases, etc. using "Mapping" as the identifying name.

Something in the Shows endpoints inserts wrong data

There are a lot of entries in the test table People when running the tests with Shows endpoints. The name of the people is from the "instruments" property in some cases. Some of them are valid names but not cleaned up by the unit tests tearDown()s.

Update setup instructions for php7

There was a problem running Apache in vagrant after updating to php7. The issue appeared in Apache not being able to start.

sudo service apache2 start
 * Starting web server apache2                                                                                                               Segmentation fault (core dumped)
Action 'start' failed.
The Apache error log may have more information.

$ sudo service apache2 reload
 * Reloading web server apache2                                                                                                               *
 * Apache2 is not running

Which causes:

$ curl localhost:5666
curl: (7) Failed to connect to localhost port 5666: Connection refused

The fix is to do:

1. sudo a2dismod php5
2. sudo a2enmod php7.0
3. sudo service apache2 restart
   Then it works:

$ sudo service apache2 restart
 * Restarting web server apache2                                                                                                      [ OK ]

$ curl localhost:5666
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<html>
 <head>