Raphaël G. Git Repositories - treebundle/blob - Repository/AlbumRepository.php

   1 <?php declare(strict_types=1);
   2
   3 /*
   4  * This file is part of the Rapsys TreeBundle package.
   5  *
   6  * (c) Raphaël Gertz <symfony@rapsys.eu>
   7  *
   8  * For the full copyright and license information, please view the LICENSE
   9  * file that was distributed with this source code.
  10  */
  11
  12 namespace Rapsys\TreeBundle\Repository;
  13
  14 use Doctrine\ORM\Query\ResultSetMapping;
  15
  16 use Rapsys\TreeBundle\Repository;
  17
  18 /**
  19  * Album repository
  20  */
  21 class AlbumRepository extends Repository {
  22         /**
  23          * Find album count as int
  24          *
  25          * @param ?integer $uid The user id
  26          * @return integer The albums count
  27          */
  28         public function countByUidAsInt(?int $uid): int {
  29                 //Set user sql
  30                 $userSql = <<<SQL
  31 a.user_id = :uid
  32 SQL;
  33
  34                 //With null uid
  35                 if ($uid === null) {
  36                         //Set user sql
  37                         $userSql = <<<SQL
  38 a.user_id IS NULL
  39 SQL;
  40                 }
  41
  42                 //Set the request
  43                 $req = <<<SQL
  44 SELECT COUNT(DISTINCT a.album_id) AS count
  45 FROM Rapsys\TreeBundle\Entity\Element AS a
  46 WHERE $userSql
  47 SQL;
  48
  49                 //Get result set mapping instance
  50                 $req = $this->replace($req);
  51
  52                 //Get result set mapping instance
  53                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  54                 $rsm = new ResultSetMapping();
  55
  56                 //Declare all fields
  57                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  58                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  59                 $rsm->addScalarResult('count', 'count', 'integer');
  60
  61                 //Get result
  62                 return $this->_em
  63                         ->createNativeQuery($req, $rsm)
  64                         ->setParameter('uid', $uid)
  65                         ->getSingleScalarResult();
  66         }
  67
  68         /**
  69          * Find albums as array
  70          *
  71          * @param ?integer $uid The user id
  72          * @param integer $page The page
  73          * @param integer $count The count
  74          * @return array The albums array
  75          */
  76         public function findByUidAsArray(?int $uid, int $page, int $count): array {
  77                 //Set the request
  78                 $req = <<<SQL
  79 SELECT
  80         a.id,
  81         a.path,
  82         a.slug,
  83         GREATEST(a.created, e.created) AS created,
  84         GREATEST(a.updated, e.updated) AS updated,
  85         GREATEST(a.created, e.created, a.updated, e.updated) AS modified,
  86         GROUP_CONCAT(e.id ORDER BY e.id SEPARATOR "\\n") AS e_ids,
  87         GROUP_CONCAT(e.path ORDER BY e.id SEPARATOR "\\n") AS e_paths
  88 FROM Rapsys\TreeBundle\Entity\Album AS a
  89 JOIN Rapsys\TreeBundle\Entity\Element AS e ON (e.album_id = a.id AND e.user_id IN (NULL, :uid))
  90 GROUP BY a.id
  91 ORDER BY updated, created DESC, a.id
  92 LIMIT :offset, :count
  93 SQL;
  94
  95                 //Replace bundle entity name by table name
  96                 $req = $this->replace($req);
  97
  98                 //Get result set mapping instance
  99                 $rsm = new ResultSetMapping();
 100
 101                 //Declare all fields
 102                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 103                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 104                 $rsm->addScalarResult('id', 'id', 'integer')
 105                         ->addScalarResult('path', 'path', 'string')
 106                         ->addScalarResult('slug', 'slug', 'string')
 107                         ->addScalarResult('created', 'created', 'datetime')
 108                         ->addScalarResult('updated', 'updated', 'datetime')
 109                         ->addScalarResult('modified', 'modified', 'datetime')
 110                         ->addScalarResult('e_ids', 'e_ids', 'string')
 111                         ->addScalarResult('e_paths', 'e_paths', 'string')
 112                         ->addIndexByScalar('id');
 113
 114                 //Get result
 115                 $result = $this->_em
 116                         ->createNativeQuery($req, $rsm)
 117                         ->setParameter('offset', $page * $count)
 118                         ->setParameter('count', $count)
 119                         ->setParameter('uid', $uid)
 120                         ->getArrayResult();
 121
 122                 //Set return
 123                 $return = [];
 124
 125                 //Iterate on each album
 126                 foreach($result as $data) {
 127                         //Append album
 128                         $return[$data['id']] = [
 129                                 'id' => $id = $data['id'],
 130                                 'slug' => $slug = $data['slug'],
 131                                 'path' => $data['path'],
 132                                 'created' => $data['created'],
 133                                 'updated' => $data['updated'],
 134                                 'modified' => $data['modified'],
 135                                 'link' => $this->router->generate('rapsystree_album', [ 'id' => $id, 'slug' => $slug ]),
 136                                 'elements' => []
 137                         ];
 138
 139                         //Explode element ids
 140                         $data['e_ids'] = explode("\n", $data['e_ids']);
 141
 142                         //Explode element paths
 143                         $data['e_paths'] = explode("\n", $data['e_paths']);
 144
 145                         foreach($data['e_ids'] as $e => $eid) {
 146                                 $return[$data['id']]['elements'][$eid] = [
 147                                         'id' => $eid,
 148                                         'name' => trim(str_replace('/', ' / ', '/'.($path = $data['e_paths'][$e]))),
 149                                         'path' => $path,
 150                                         'link' => $this->router->generate('rapsystree_element', [ 'id' => $eid, 'path' => $path ]),
 151                                 ];
 152                         }
 153                 }
 154
 155                 //Return return
 156                 return $return;
 157         }
 158
 159         /**
 160          * Find album as array
 161          *
 162          * @param integer $id The album id
 163          * @param string $path The album path
 164          * @return ?array The album array
 165          */
 166         public function findOneByIdPathAsArray(int $id, string $path): ?array {
 167                 //Set the request
 168                 $req = <<<SQL
 169 SELECT
 170         a.id,
 171         a.path,
 172         a.slug,
 173         GREATEST(a.created, e.created) AS created,
 174         GREATEST(a.updated, e.updated) AS updated,
 175         GREATEST(a.created, e.created, a.updated, e.updated) AS modified,
 176         GROUP_CONCAT(e.id ORDER BY e.id SEPARATOR "\\n") AS e_ids,
 177         GROUP_CONCAT(e.path ORDER BY e.id SEPARATOR "\\n") AS e_paths,
 178         GROUP_CONCAT(e.user_id ORDER BY e.id SEPARATOR "\\n") AS e_uids
 179 FROM Rapsys\TreeBundle\Entity\Album AS a
 180 JOIN Rapsys\TreeBundle\Entity\Element AS e ON (e.album_id = a.id)
 181 WHERE a.id = :id
 182 SQL;
 183
 184                 //Replace bundle entity name by table name
 185                 $req = $this->replace($req);
 186
 187                 //Get result set mapping instance
 188                 $rsm = new ResultSetMapping();
 189
 190                 //Declare all fields
 191                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 192                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 193                 $rsm->addScalarResult('id', 'id', 'integer')
 194                         ->addScalarResult('path', 'path', 'string')
 195                         ->addScalarResult('slug', 'slug', 'string')
 196                         ->addScalarResult('created', 'created', 'datetime')
 197                         ->addScalarResult('updated', 'updated', 'datetime')
 198                         ->addScalarResult('modified', 'modified', 'datetime')
 199                         ->addScalarResult('e_ids', 'e_ids', 'string')
 200                         ->addScalarResult('e_paths', 'e_paths', 'string')
 201                         ->addScalarResult('e_uids', 'e_uids', 'string')
 202                         ->addIndexByScalar('id');
 203
 204                 //Get result
 205                 $result = $this->_em
 206                         ->createNativeQuery($req, $rsm)
 207                         ->setParameter('id', $id)
 208                         ->getOneOrNullResult();
 209
 210                 //Check result
 211                 if (
 212                         //Without result
 213                         $result === null ||
 214                         //Without realpath
 215                         !($realpath = realpath($result['path'].($path?DIRECTORY_SEPARATOR.$path:''))) ||
 216                         //Realpath not matching element path
 217                         $result['path'] !== substr($realpath, 0, strlen($result['path']))
 218                 ) {
 219                         //Return null
 220                         return null;
 221                 }
 222
 223                 //Set breadcrumbs
 224                 $breadcrumbs = [
 225                         //Add album
 226                         [
 227                                 'name' => $name = ucfirst($slug = $result['slug']),
 228                                 'link' => $link = $this->router->generate('rapsystree_album', [ 'id' => $result['id'], 'path' => '', 'slug' => $slug ])
 229                         ]
 230                 ];
 231
 232                 //Set base
 233                 $base = '';
 234
 235                 //Iterate on intermediate breadcrumbs
 236                 foreach(array_slice(explode('/', substr($realpath, strlen($result['path']))), 1) as $value) {
 237                         //Add breadcrumb
 238                         $breadcrumbs[] = [
 239                                 'name' => '/ '.$value,
 240                                 'link' => $this->router->generate('rapsystree_album', [ 'id' => $result['id'], 'path' => ($base .= ($base == '' ? '' : '/').$value), 'slug' => $slug ])
 241                         ];
 242                 }
 243
 244                 //Set directories
 245                 $directories = [];
 246
 247                 //Set files
 248                 $files = [];
 249
 250                 //Set file
 251                 $file = [];
 252
 253                 //With directory
 254                 if (is_dir($realpath)) {
 255                         //Iterate on directory
 256                         foreach(array_diff(scandir($realpath), ['.', '..']) as $item) {
 257                                 //TODO: exclude .svn, .git, .passwd, .*.un~, etc... (if not already protected by haproxy/apache)
 258
 259                                 //Check item
 260                                 if (
 261                                         //Without item realpath
 262                                         !($itempath = realpath($realpath.DIRECTORY_SEPARATOR.$item)) ||
 263                                         //Item realpath not matching album path
 264                                         $result['path'] !== substr($itempath, 0, strlen($result['path']))
 265                                 ) {
 266                                         //Skip
 267                                         continue;
 268                                 }
 269
 270                                 //With directory
 271                                 if (is_dir($itempath)) {
 272                                         //Append directory
 273                                         $directories[$item.'/'] = $this->router->generate('rapsystree_album', [ 'id' => $result['id'], 'path' => ($path ? $path.'/' : '').$item, 'slug' => $slug ]);
 274                                 //With file
 275                                 } elseif (is_file($itempath)) {
 276                                         //Get file infos
 277                                         $fileinfos = $this->file->infos($itempath);
 278
 279                                         //Append file
 280                                         $files[$fileinfos['name']] = [
 281                                                 //Set link
 282                                                 'link' => $this->router->generate('rapsystree_album', [ 'id' => $result['id'], 'path' => ($path ? $path.'/' : '').$item, 'slug' => $slug ])
 283                                         ]+$fileinfos;
 284                                 //With unknown type
 285                                 } else {
 286                                         //Throw 404
 287                                         throw new \Exception('Unknown element item type');
 288                                 }
 289                         }
 290                 //With file
 291                 } elseif (is_file($realpath)) {
 292                         //Get file infos
 293                         $fileinfos = $this->file->infos($realpath);
 294
 295                         //Append file
 296                         $file = [
 297                                 //Set link
 298                                 'link' => $this->router->generate('rapsystree_album', [ 'id' => $result['id'], 'path' => $path, 'slug' => $slug ])
 299                         ]+$fileinfos;
 300                 //With unknown type
 301                 } else {
 302                         //Throw 404
 303                         throw new \Exception('Unknown element type');
 304                 }
 305
 306                 //Set album
 307                 $album = [
 308                         'id' => $result['id'],
 309                         'name' => $name,
 310                         'path' => $result['path'],
 311                         'slug' => $slug,
 312                         'created' => $result['created'],
 313                         'updated' => $result['updated'],
 314                         'modified' => $result['modified'],
 315                         'link' => $link,
 316                         'elements' => [],
 317                         'breadcrumbs' => $breadcrumbs,
 318                         'directories' => $directories,
 319                         'files' => $files,
 320                         'file' => $file
 321                 ];
 322
 323                 //Explode element ids
 324                 $result['e_ids'] = explode("\n", $result['e_ids']);
 325
 326                 //Explode element paths
 327                 $result['e_paths'] = explode("\n", $result['e_paths']);
 328
 329                 //Explode element uids
 330                 $result['e_uids'] = explode("\n", $result['e_uids']);
 331
 332                 //Iterate on elements
 333                 foreach($result['e_ids'] as $e => $eid) {
 334                         //Append element
 335                         $album['elements'][$eid] = [
 336                                 'id' => $eid,
 337                                 'name' => trim(str_replace('/', ' / ', '/'.($epath = $result['e_paths'][$e]))),
 338                                 'path' => $epath,
 339                                 'uid' => $result['e_uids'][$e],
 340                                 'link' => $this->router->generate('rapsystree_album', ['id' => $result['id'], 'path' => $epath, 'slug' => $slug]),
 341                         ];
 342                 }
 343
 344                 //Return album
 345                 return $album;
 346         }
 347 }