Fusio

Convert a legacy PHP application to Fusio

In this tutorial we convert a legacy PHP script to Fusio. The following script comes from a stackoverflow post and it provides a great example how to build an API with Fusio.

Legacy code
<?php

header("Content-Type: application/json");
// get the HTTP method, path and body of the request
$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));
var_dump($request);
$input = json_decode(file_get_contents('php://input'),true);

// connect to the mysqli database
$link = mysqli_connect('localhost', 'root', 'hello', 'agita');
mysqli_set_charset($link,'utf8');

// retrieve the table and key from the path
$table = preg_replace('/[^a-z0-9_]+/i','',array_shift($request));
$key = array_shift($request)+0;

// escape the columns and values from the input object
$columns = preg_replace('/[^a-z0-9_]+/i','',array_keys($input));
$values = array_map(function ($value) use ($link) {
  if ($value===null) return null;
  return mysqli_real_escape_string($link,(string)$value);
},array_values($input));

// build the SET part of the SQL command
$set = '';
for ($i=0;$i<count($columns);$i++) {
  $set.=($i>0?',':'').'`'.$columns[$i].'`=';
  $set.=($values[$i]===null?'NULL':'"'.$values[$i].'"');
}

// create SQL based on HTTP method
switch ($method) {
  case 'GET':
    $sql = "select * from `$table`".($key?" WHERE id=$key":''); break;
  case 'PUT':
    $sql = "update `$table` set $set where id=$key"; break;
  case 'POST':
    $sql = "insert into `$table` set $set"; break;
  case 'DELETE':
    $sql = "delete `$table` where id=$key"; break;
}

// excecute SQL statement
$result = mysqli_query($link,$sql);

// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error());
}

// print results, insert id or affected row count
if ($method == 'GET') {
  if (!$key) echo '[';
  for ($i=0;$i<mysqli_num_rows($result);$i++) {
    echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
  }
  if (!$key) echo ']';
} elseif ($method == 'POST') {
  echo mysqli_insert_id($link);
} else {
  echo mysqli_affected_rows($link);
}

// close mysqli connection
mysqli_close($link);
?>
Build the deploy configuration

At first step we must build the Fusio deploy config from the script. Therefor we need to determine the available routes and request methods. If we take a look at the script it defines the following endpoints:

  • /[table_name]
    • GET: Get all rows from the table
    • POST: Create a new row in the table
  • /[table_name]/[id]
    • GET: Get an entry with the specified primary key
    • PUT: Update a row
    • DELETE: Delete a row

If we convert this into a Fusio deploy config it could look like this:

routes:
  "/:table":
    methods:
      GET:
        public: true
        action: Fusio\Custom\Action\Table\Collection
      POST:
        public: false
        action: Fusio\Custom\Action\Table\Insert
  "/:table/:id":
    methods:
      GET:
        public: true
        action: Fusio\Custom\Action\Table\Entity
      PUT:
        public: false
        action: Fusio\Custom\Action\Table\Update
      DELETE:
        public: false
        action: Fusio\Custom\Action\Table\Delete
connection:
  DefaultConnection:
    class: Fusio\Adapter\Sql\Connection\Sql
    config:
      type: pdo_mysql
      host: localhost
      username: root
      password: hello
      database: agita

We have added the fitting routes to our config. The POST, PUT and DELETE endpoints are not public so only users with an access token can access these endpoints.

Implement the endpoint logic

Now we only have to implement the actual endpoint logic.

<?php

namespace Fusio\Custom\Action\Table;

use Fusio\Engine\ActionAbstract;
use Fusio\Engine\ContextInterface;
use Fusio\Engine\ParametersInterface;
use Fusio\Engine\RequestInterface;
use PSX\Http\Exception as StatusCode;

class Collection extends ActionAbstract
{
    public function handle(RequestInterface $request, ParametersInterface $configuration, ContextInterface $context)
    {
        /** @var \Doctrine\DBAL\Connection $connection */
        $connection = $this->connector->getConnection('DefaultConnection');

        $tableName = $request->getUriFragment('table');
        if (!preg_match('/^[A-z0-9_]+$/', $tableName)) {
            throw new StatusCode\BadRequestException('Invalid table');
        }

        $result = $connection->fetchAll('SELECT * FROM ' . $tableName . ' LIMIT 16');

        return $this->response->build(200, [], [
            'result' => $result,
        ]);
    }
}
<?php

namespace Fusio\Custom\Action\Table;

use Fusio\Engine\ActionAbstract;
use Fusio\Engine\ContextInterface;
use Fusio\Engine\ParametersInterface;
use Fusio\Engine\RequestInterface;
use PSX\Http\Exception as StatusCode;

class Entity extends ActionAbstract
{
    public function handle(RequestInterface $request, ParametersInterface $configuration, ContextInterface $context)
    {
        /** @var \Doctrine\DBAL\Connection $connection */
        $connection = $this->connector->getConnection('DefaultConnection');

        $tableName = $request->getUriFragment('table');
        if (!preg_match('/^[A-z0-9_]+$/', $tableName)) {
            throw new StatusCode\BadRequestException('Invalid table');
        }

        $row = $connection->fetchAssoc('SELECT * FROM ' . $tableName . ' WHERE id = :id', [
            'id' => $request->getUriFragment('id')
        ]);

        if (empty($row)) {
            throw new StatusCode\NotFoundException('Entry not available');
        }

        return $this->response->build(200, [], $row);
    }
}
<?php

namespace Fusio\Custom\Action\Table;

use Fusio\Engine\ActionAbstract;
use Fusio\Engine\ContextInterface;
use Fusio\Engine\ParametersInterface;
use Fusio\Engine\RequestInterface;
use PSX\Http\Exception as StatusCode;

class Insert extends ActionAbstract
{
    public function handle(RequestInterface $request, ParametersInterface $configuration, ContextInterface $context)
    {
        /** @var \Doctrine\DBAL\Connection $connection */
        $connection = $this->connector->getConnection('DefaultConnection');

        $tableName = $request->getUriFragment('table');
        if (!preg_match('/^[A-z0-9_]+$/', $tableName)) {
            throw new StatusCode\BadRequestException('Invalid table');
        }

        $connection->insert($tableName, $request->getBody()->getProperties());

        return $this->response->build(201, [], [
            'success' => true,
            'message' => 'Insert successful',
        ]);
    }
}
<?php

namespace Fusio\Custom\Action\Table;

use Fusio\Engine\ActionAbstract;
use Fusio\Engine\ContextInterface;
use Fusio\Engine\ParametersInterface;
use Fusio\Engine\RequestInterface;
use PSX\Http\Exception as StatusCode;

class Update extends ActionAbstract
{
    public function handle(RequestInterface $request, ParametersInterface $configuration, ContextInterface $context)
    {
        /** @var \Doctrine\DBAL\Connection $connection */
        $connection = $this->connector->getConnection('DefaultConnection');

        $tableName = $request->getUriFragment('table');
        if (!preg_match('/^[A-z0-9_]+$/', $tableName)) {
            throw new StatusCode\BadRequestException('Invalid table');
        }

        $affected = $connection->update($tableName, $request->getBody()->getProperties(), [
            'id' => $request->getUriFragment('id')
        ]);

        if (empty($affected)) {
            throw new StatusCode\NotFoundException('Entry not available');
        }

        return $this->response->build(200, [], [
            'success' => true,
            'message' => 'Update successful',
        ]);
    }
}
<?php

namespace Fusio\Custom\Action\Table;

use Fusio\Engine\ActionAbstract;
use Fusio\Engine\ContextInterface;
use Fusio\Engine\ParametersInterface;
use Fusio\Engine\RequestInterface;
use PSX\Http\Exception as StatusCode;

class Delete extends ActionAbstract
{
    public function handle(RequestInterface $request, ParametersInterface $configuration, ContextInterface $context)
    {
        /** @var \Doctrine\DBAL\Connection $connection */
        $connection = $this->connector->getConnection('DefaultConnection');

        $tableName = $request->getUriFragment('table');
        if (!preg_match('/^[A-z0-9_]+$/', $tableName)) {
            throw new StatusCode\BadRequestException('Invalid table');
        }

        $affected = $connection->delete($tableName, [
            'id' => $request->getUriFragment('id')
        ]);

        if (empty($affected)) {
            throw new StatusCode\NotFoundException('Entry not available');
        }

        return $this->response->build(200, [], [
            'success' => true,
            'message' => 'Delete successful',
        ]);
    }
}
Deploy the endpoint

Now we can deploy the API through a simple command:

php bin/fusio deploy
Conclusion

With these simple steps we have transformed the legacy API script to Fusio. Beside a much clearer code base the API uses now all features of Fusio i.e. Oauth2 authentication for protected API endpoints or the option to handle rate limits.