SearchAction.php 11.5 KB
Newer Older
1
2
<?php

François Agneray's avatar
François Agneray committed
3
/*
4
 * This file is part of Anis Server.
François Agneray's avatar
François Agneray committed
5
 *
6
 * (c) Laboratoire d'Astrophysique de Marseille / CNRS
François Agneray's avatar
François Agneray committed
7
8
9
10
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */
11
12
13
declare(strict_types=1);

namespace App\Action;
François Agneray's avatar
François Agneray committed
14

15
16
17
18
19
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;
François Agneray's avatar
François Agneray committed
20
use Doctrine\DBAL\Query\QueryBuilder;
21
use Doctrine\DBAL\Driver\Statement;
François Agneray's avatar
François Agneray committed
22
use Doctrine\DBAL\Query\Expression\CompositeExpression;
23
use App\Utils\DBALConnectionFactory;
24
use App\Utils\Operator\OperatorFactory;
François Agneray's avatar
François Agneray committed
25
use App\Utils\SearchException;
26
27
use App\Entity\Dataset;
use App\Entity\Attribute;
28

François Agneray's avatar
François Agneray committed
29
/**
30
 * Search action
François Agneray's avatar
François Agneray committed
31
32
 *
 * @author François Agneray <francois.agneray@lam.fr>
33
 * @package App\Action
François Agneray's avatar
François Agneray committed
34
 */
35
final class SearchAction extends AbstractAction
François Agneray's avatar
François Agneray committed
36
{
37
    private $connectionFactory;
François Agneray's avatar
François Agneray committed
38
    private $operatorFactory;
39
40

    /**
41
     * Create the classe before call __invoke to execute the action
François Agneray's avatar
François Agneray committed
42
     *
43
44
     * @param EntityManagerInterface $em Doctrine       Entity Manager Interface
     * @param DBALConnectionFactory  $connectionFactory Factory used to construct connection to business database
François Agneray's avatar
François Agneray committed
45
     */
François Agneray's avatar
François Agneray committed
46
47
48
49
50
    public function __construct(
        EntityManagerInterface $em,
        DBALConnectionFactory $connectionFactory,
        OperatorFactory $operatorFactory
    ) {
51
52
        parent::__construct($em);
        $this->connectionFactory = $connectionFactory;
François Agneray's avatar
François Agneray committed
53
        $this->operatorFactory = $operatorFactory;
54
55
    }

François Agneray's avatar
François Agneray committed
56
    /**
57
     * This action indicates that the service is responding
François Agneray's avatar
François Agneray committed
58
     *
59
60
61
     * @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)
François Agneray's avatar
François Agneray committed
62
63
64
     *
     * @return ResponseInterface
     */
François Agneray's avatar
François Agneray committed
65
66
    public function __invoke(Request $request, Response $response, array $args): Response
    {
67
        if ($request->getMethod() === OPTIONS) {
François Agneray's avatar
François Agneray committed
68
69
70
            return $response->withHeader('Access-Control-Allow-Methods', 'GET, OPTIONS');
        }

71
72
        // Search the correct dataset with primary key
        $dataset = $this->em->find('App\Entity\Dataset', $args['dname']);
François Agneray's avatar
François Agneray committed
73

74
        // If dataset is not found 404
François Agneray's avatar
François Agneray committed
75
        if (is_null($dataset)) {
76
77
            throw new HttpNotFoundException(
                $request,
François Agneray's avatar
François Agneray committed
78
                'Dataset with name ' . $args['dname'] . ' is not found'
79
            );
80
81
        }

82
83
        // Create query builder with from clause using dataset information
        $connection = $this->connectionFactory->create($dataset->getProject()->getDatabase());
François Agneray's avatar
François Agneray committed
84
        $queryBuilder = $connection->createQueryBuilder();
François Agneray's avatar
François Agneray committed
85
        $queryBuilder->from($dataset->getTableRef());
86
        
François Agneray's avatar
François Agneray committed
87
        $queryParams = $request->getQueryParams();
François Agneray's avatar
François Agneray committed
88

François Agneray's avatar
François Agneray committed
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
        // 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']));
François Agneray's avatar
François Agneray committed
104
            }
105

François Agneray's avatar
François Agneray committed
106
107
108
109
            // 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']));
            }
110

François Agneray's avatar
François Agneray committed
111
112
113
114
            // 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']));
            }
115

François Agneray's avatar
François Agneray committed
116
117
118
119
            // The parameter p is not mandatory and represents the SQL limit clause
            if (array_key_exists('p', $queryParams)) {
                $this->limit($queryBuilder, $queryParams['p']);
            }
120

François Agneray's avatar
François Agneray committed
121
122
123
124
125
126
            // 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';
François Agneray's avatar
François Agneray committed
127
            }
François Agneray's avatar
François Agneray committed
128

François Agneray's avatar
François Agneray committed
129
130
131
132
133
134
135
            // Create the response according to the format
            return $this->getFormattedResponse($response, $queryBuilder, $attributes, $format);
        } catch (SearchException $e) {
            throw new HttpBadRequestException(
                $request,
                $e->getMessage()
            );
François Agneray's avatar
François Agneray committed
136
137
138
        }
    }

François Agneray's avatar
François Agneray committed
139
140
141
142
143
144
145
    /**
     * 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)
     */
François Agneray's avatar
François Agneray committed
146
    private function select(QueryBuilder $queryBuilder, Dataset $dataset, array $listOfIds): array
François Agneray's avatar
François Agneray committed
147
148
    {
        $columns = array();
François Agneray's avatar
François Agneray committed
149
        $attributes = array();
François Agneray's avatar
François Agneray committed
150
151
152
        foreach ($listOfIds as $id) {
            $attribute = $this->getAttribute($dataset, (int) $id);
            $columns[] = $attribute->getTableName() . '.' . $attribute->getName() . ' as ' . $attribute->getLabel();
François Agneray's avatar
François Agneray committed
153
            $attributes[] = $attribute;
François Agneray's avatar
François Agneray committed
154
155
        }
        $queryBuilder->select($columns);
François Agneray's avatar
François Agneray committed
156
        return $attributes;
François Agneray's avatar
François Agneray committed
157
158
    }

François Agneray's avatar
François Agneray committed
159
160
161
162
163
164
165
    /**
     * 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)
     */
François Agneray's avatar
François Agneray committed
166
167
168
169
    private function where(QueryBuilder $queryBuilder, Dataset $dataset, array $criteria): void
    {
        $expressions = array();
        foreach ($criteria as $criterion) {
François Agneray's avatar
François Agneray committed
170
            $params = $params = explode('::', $criterion);
François Agneray's avatar
François Agneray committed
171
172
173
174
175
176
177
178
            $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();
            }
François Agneray's avatar
François Agneray committed
179
180
181
182
183
184
185
            $operator = $this->operatorFactory->create(
                $params[1],
                $queryBuilder->expr(),
                $column,
                $columnType,
                $values
            );
François Agneray's avatar
François Agneray committed
186
187
188
189
190
            $expressions[] = $operator->getExpression();
        }
        $queryBuilder->where(new CompositeExpression(CompositeExpression::TYPE_AND, $expressions));
    }

François Agneray's avatar
François Agneray committed
191
192
193
194
195
196
197
    /**
     * 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)
     */
François Agneray's avatar
François Agneray committed
198
199
200
201
    private function order(QueryBuilder $queryBuilder, Dataset $dataset, array $orders): void
    {
        foreach ($orders as $order) {
            $o = explode(':', $order);
François Agneray's avatar
François Agneray committed
202
203
204
            if (count($o) != 2) {
                throw SearchException::badNumberOfParamsForOrder();
            }
François Agneray's avatar
François Agneray committed
205
206
207
            $attribute = $this->getAttribute($dataset, (int) $o[0]);
            if ($o[1] === 'a') {
                $aord = 'ASC';
François Agneray's avatar
François Agneray committed
208
            } elseif ($o[1] === 'd') {
François Agneray's avatar
François Agneray committed
209
                $aord = 'DESC';
François Agneray's avatar
François Agneray committed
210
211
            } else {
                throw SearchException::typeOfOrderDoesNotExist($o[1]);
François Agneray's avatar
François Agneray committed
212
213
214
215
216
            }
            $queryBuilder->orderBy($attribute->getTableName() . '.' . $attribute->getName(), $aord);
        }
    }

François Agneray's avatar
François Agneray committed
217
218
219
220
221
222
    /**
     * 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)
     */
François Agneray's avatar
François Agneray committed
223
224
225
    private function limit(QueryBuilder $queryBuilder, string $param): void
    {
        $p = explode(':', $param);
François Agneray's avatar
François Agneray committed
226
227
228
        if (count($p) != 2) {
            throw SearchException::badNumberOfParamsForLimit();
        }
François Agneray's avatar
François Agneray committed
229
230
231
232
233
234
235
        $limit = $p[0];
        $offset = ($p[1] - 1) * $limit;
        $queryBuilder
            ->setFirstResult($offset)
            ->setMaxResults($limit);
    }

236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
    /**
     * 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());
    }

François Agneray's avatar
François Agneray committed
253
254
255
256
257
258
    private function getFormattedResponse(
        Response $response,
        QueryBuilder $queryBuilder,
        array $attributes,
        string $format
    ): Response {
259
        // First of all execute the SQL query
François Agneray's avatar
François Agneray committed
260
        $stmt = $queryBuilder->execute();
François Agneray's avatar
François Agneray committed
261
262
263
264

        // Build and write the payload according to the format
        switch ($format) {
            case 'json':
265
                $payload = json_encode($this->decodeNestedJson($stmt, $attributes), JSON_UNESCAPED_SLASHES);
François Agneray's avatar
François Agneray committed
266
267
268
                $response->getBody()->write($payload);
                return $response;
            case 'csv':
269
                $payload = $this->transformArrayToCsv($stmt, $attributes, ',');
François Agneray's avatar
François Agneray committed
270
271
272
                $response->getBody()->write($payload);
                return $response->withHeader('Content-Type', 'text/csv');
            case 'ascii':
273
                $payload = $this->transformArrayToCsv($stmt, $attributes, ' ');
François Agneray's avatar
François Agneray committed
274
275
276
277
278
279
280
                $response->getBody()->write($payload);
                return $response->withHeader('Content-Type', 'text/plain');
            default:
                throw SearchException::typeOfFormatDoesNotExist($format);
        }
    }

281
    private function decodeNestedJson(Statement $stmt, array $attributes): array
François Agneray's avatar
François Agneray committed
282
    {
283
        $rows = array();
François Agneray's avatar
François Agneray committed
284
        $jsonAttributes = $this->getAttributesOfTypeJson($attributes);
285
286
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            foreach ($row as $key => $column) {
François Agneray's avatar
François Agneray committed
287
288
289
290
291
292
                if (array_search($key, $jsonAttributes)) {
                    if (!is_null($column)) {
                        $row[$key] = json_decode($column, true);
                    }
                }
            }
293
            $rows[] = $row;
François Agneray's avatar
François Agneray committed
294
295
296
297
298
299
        }
        return $rows;
    }

    private function getAttributesOfTypeJson(array $attributes): array
    {
François Agneray's avatar
François Agneray committed
300
        return array_map(function ($attribute) {
François Agneray's avatar
François Agneray committed
301
            return $attribute->getLabel();
François Agneray's avatar
François Agneray committed
302
        }, array_filter($attributes, function ($attribute) {
François Agneray's avatar
François Agneray committed
303
304
            return $attribute->getType() === 'json';
        }));
François Agneray's avatar
François Agneray committed
305
    }
François Agneray's avatar
François Agneray committed
306

307
    private function transformArrayToCsv(Statement $stmt, array $attributes, string $delimiter): string
François Agneray's avatar
François Agneray committed
308
309
310
311
312
    {
        $attributesLabel = array_map(function ($attribute) {
            return $attribute->getLabel();
        }, $attributes);
        $csv = implode($delimiter, $attributesLabel) . PHP_EOL;
313
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
François Agneray's avatar
François Agneray committed
314
315
316
317
            $csv .= implode($delimiter, $row) . PHP_EOL;
        }
        return $csv;
    }
François Agneray's avatar
François Agneray committed
318
}