SearchAction.php

<?php

/*
 * This file is part of Anis Server.
 *
 * (c) Laboratoire d'Astrophysique de Marseille / CNRS
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */
declare(strict_types=1);

namespace App\Action;

use Psr\Http\Message\ResponseInterface as Response;
use Psr\Http\Message\ServerRequestInterface as Request;
use Slim\Exception\HttpBadRequestException;
use Slim\Exception\HttpNotFoundException;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\DBAL\Query\QueryBuilder;
use Doctrine\DBAL\Driver\Statement;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use App\Utils\DBALConnectionFactory;
use App\Utils\Operator\OperatorFactory;
use App\Utils\SearchException;
use App\Entity\Dataset;
use App\Entity\Attribute;

/**
 * Search action
 *
 * @author François Agneray <francois.agneray@lam.fr>
 * @package App\Action
 */
final class SearchAction extends AbstractAction
{
    private $connectionFactory;
    private $operatorFactory;

    /**
     * Create the classe before call __invoke to execute the action
     *
     * @param EntityManagerInterface $em Doctrine       Entity Manager Interface
     * @param DBALConnectionFactory  $connectionFactory Factory used to construct connection to business database
     */
    public function __construct(
        EntityManagerInterface $em,
        DBALConnectionFactory $connectionFactory,
        OperatorFactory $operatorFactory
    ) {
        parent::__construct($em);
        $this->connectionFactory = $connectionFactory;
        $this->operatorFactory = $operatorFactory;
    }

    /**
     * This action indicates that the service is responding
     *
     * @param  ServerRequestInterface $request  PSR-7 This object represents the HTTP request
     * @param  ResponseInterface      $response PSR-7 This object represents the HTTP response
     * @param  string[]               $args     This table contains information transmitted in the URL (see routes.php)
     *
     * @return ResponseInterface
     */
    public function __invoke(Request $request, Response $response, array $args): Response
    {
        if ($request->getMethod() === OPTIONS) {
            return $response->withHeader('Access-Control-Allow-Methods', 'GET, OPTIONS');
        }

        // Search the correct dataset with primary key
        $dataset = $this->em->find('App\Entity\Dataset', $args['dname']);

        // If dataset is not found 404
        if (is_null($dataset)) {
            throw new HttpNotFoundException(
                $request,
                'Dataset with name ' . $args['dname'] . ' is not found'
            );
        }

        // Create query builder with from clause using dataset information
        $connection = $this->connectionFactory->create($dataset->getProject()->getDatabase());
        $queryBuilder = $connection->createQueryBuilder();
        $queryBuilder->from($dataset->getTableRef());
        
        $queryParams = $request->getQueryParams();

        // The parameter "a" is mandatory
        if (!array_key_exists('a', $queryParams)) {
            throw new HttpBadRequestException(
                $request,
                'Param a is required for this request'
            );
        }

        try {
            // The parameter a represents the SQL select clause
            if ($queryParams['a'] === 'count') {
                $attributes = array();
                $queryBuilder->select('COUNT(*) as nb');
            } else {
                $attributes = $this->select($queryBuilder, $dataset, explode(';', $queryParams['a']));
            }

            // The parameter c is not mandatory and represents the SQL where clause
            if (array_key_exists('c', $queryParams)) {
                $this->where($queryBuilder, $dataset, explode(';', $queryParams['c']));
            }

            // The parameter o is not mandatory and represents the SQL order clause
            if (array_key_exists('o', $queryParams)) {
                $this->order($queryBuilder, $dataset, explode(';', $queryParams['o']));
            }

            // The parameter p is not mandatory and represents the SQL limit clause
            if (array_key_exists('p', $queryParams)) {
                $this->limit($queryBuilder, $queryParams['p']);
            }

            // The parameter f is not mandatory and represents the output format
            // By default the format is JSON
            if (array_key_exists('f', $queryParams)) {
                $format = $queryParams['f'];
            } else {
                $format = 'json';
            }

            // Create the response according to the format
            return $this->getFormattedResponse($response, $queryBuilder, $attributes, $format);
        } catch (SearchException $e) {
            throw new HttpBadRequestException(
                $request,
                $e->getMessage()
            );
        }
    }

    /**
     * Adds the select clause to the request
     *
     * @param QueryBuilder $queryBuilder Represents the query being built
     * @param Dataset      $dataset      Represents the requested dataset
     * @param string[]     $listOfIds    The substring of the url (parameter a)
     */
    private function select(QueryBuilder $queryBuilder, Dataset $dataset, array $listOfIds): array
    {
        $columns = array();
        $attributes = array();
        foreach ($listOfIds as $id) {
            $attribute = $this->getAttribute($dataset, (int) $id);
            $columns[] = $attribute->getTableName() . '.' . $attribute->getName() . ' as ' . $attribute->getLabel();
            $attributes[] = $attribute;
        }
        $queryBuilder->select($columns);
        return $attributes;
    }

    /**
     * Adds the where clause to the request
     *
     * @param QueryBuilder $queryBuilder Represents the query being built
     * @param Dataset      $dataset      Represents the requested dataset
     * @param string[]     $criteria     The substring of the url (parameter c)
     */
    private function where(QueryBuilder $queryBuilder, Dataset $dataset, array $criteria): void
    {
        $expressions = array();
        foreach ($criteria as $criterion) {
            $params = $params = explode('::', $criterion);
            $attribute = $this->getAttribute($dataset, (int) $params[0]);
            $column = $attribute->getTableName() . '.' . $attribute->getName();
            $columnType = $attribute->getType();
            if (array_key_exists(2, $params)) {
                $values = explode('|', $params[2]);
            } else {
                $values = array();
            }
            $operator = $this->operatorFactory->create(
                $params[1],
                $queryBuilder->expr(),
                $column,
                $columnType,
                $values
            );
            $expressions[] = $operator->getExpression();
        }
        $queryBuilder->where(new CompositeExpression(CompositeExpression::TYPE_AND, $expressions));
    }

    /**
     * Adds the order clause to the request
     *
     * @param QueryBuilder $queryBuilder Represents the query being built
     * @param Dataset      $dataset      Represents the requested dataset
     * @param string[]     $orders       The substring of the url (parameter o)
     */
    private function order(QueryBuilder $queryBuilder, Dataset $dataset, array $orders): void
    {
        foreach ($orders as $order) {
            $o = explode(':', $order);
            if (count($o) != 2) {
                throw SearchException::badNumberOfParamsForOrder();
            }
            $attribute = $this->getAttribute($dataset, (int) $o[0]);
            if ($o[1] === 'a') {
                $aord = 'ASC';
            } elseif ($o[1] === 'd') {
                $aord = 'DESC';
            } else {
                throw SearchException::typeOfOrderDoesNotExist($o[1]);
            }
            $queryBuilder->orderBy($attribute->getTableName() . '.' . $attribute->getName(), $aord);
        }
    }

    /**
     * Adds the limit clause to the request
     *
     * @param QueryBuilder $queryBuilder Represents the query being built
     * @param string       $param        The substring of the url (parameter p)
     */
    private function limit(QueryBuilder $queryBuilder, string $param): void
    {
        $p = explode(':', $param);
        if (count($p) != 2) {
            throw SearchException::badNumberOfParamsForLimit();
        }
        $limit = $p[0];
        $offset = ($p[1] - 1) * $limit;
        $queryBuilder
            ->setFirstResult($offset)
            ->setMaxResults($limit);
    }

    /**
     * Returns the Attribute object of a dataset based on its ID (primary key)
     *
     * @throws SearchException Attribute with ID not found into the selected dataset
     * @return Attribute       Returns the attribute found
     */
    private function getAttribute(Dataset $dataset, int $id): Attribute
    {
        $attributes = $dataset->getAttributes();
        foreach ($attributes as $attribute) {
            if ($attribute->getId() === $id) {
                return $attribute;
            }
        }
        throw SearchException::attributeNotFound($id, $dataset->getLabel());
    }

    private function getFormattedResponse(
        Response $response,
        QueryBuilder $queryBuilder,
        array $attributes,
        string $format
    ): Response {
        // First of all execute the SQL query
        $stmt = $queryBuilder->execute();

        // Build and write the payload according to the format
        switch ($format) {
            case 'json':
                $payload = json_encode($this->decodeNestedJson($stmt, $attributes), JSON_UNESCAPED_SLASHES);
                $response->getBody()->write($payload);
                return $response;
            case 'csv':
                $payload = $this->transformArrayToCsv($stmt, $attributes, ',');
                $response->getBody()->write($payload);
                return $response->withHeader('Content-Type', 'text/csv');
            case 'ascii':
                $payload = $this->transformArrayToCsv($stmt, $attributes, ' ');
                $response->getBody()->write($payload);
                return $response->withHeader('Content-Type', 'text/plain');
            default:
                throw SearchException::typeOfFormatDoesNotExist($format);
        }
    }

    private function decodeNestedJson(Statement $stmt, array $attributes): array
    {
        $rows = array();
        $jsonAttributes = $this->getAttributesOfTypeJson($attributes);
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            foreach ($row as $key => $column) {
                if (array_search($key, $jsonAttributes)) {
                    if (!is_null($column)) {
                        $row[$key] = json_decode($column, true);
                    }
                }
            }
            $rows[] = $row;
        }
        return $rows;
    }

    private function getAttributesOfTypeJson(array $attributes): array
    {
        return array_map(function ($attribute) {
            return $attribute->getLabel();
        }, array_filter($attributes, function ($attribute) {
            return $attribute->getType() === 'json';
        }));
    }

    private function transformArrayToCsv(Statement $stmt, array $attributes, string $delimiter): string
    {
        $attributesLabel = array_map(function ($attribute) {
            return $attribute->getLabel();
        }, $attributes);
        $csv = implode($delimiter, $attributesLabel) . PHP_EOL;
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            $csv .= implode($delimiter, $row) . PHP_EOL;
        }
        return $csv;
    }
}