1<?php
2/*
3Copyright (C) 2014-2018, Siemens AG
4Authors: Andreas Würl, Steffen Weber
5
6This program is free software; you can redistribute it and/or
7modify it under the terms of the GNU General Public License
8version 2 as published by the Free Software Foundation.
9
10This program is distributed in the hope that it will be useful,
11but WITHOUT ANY WARRANTY; without even the implied warranty of
12MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13GNU General Public License for more details.
14
15You should have received a copy of the GNU General Public License along
16with this program; if not, write to the Free Software Foundation, Inc.,
1751 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
18*/
19
20namespace Fossology\Lib\Dao;
21
22use Fossology\Lib\Data\Tree\Item;
23use Fossology\Lib\Data\Tree\ItemTreeBounds;
24use Fossology\Lib\Data\Upload\Upload;
25use Fossology\Lib\Data\UploadStatus;
26use Fossology\Lib\Db\DbManager;
27use Fossology\Lib\Exception;
28use Fossology\Lib\Proxy\UploadTreeProxy;
29use Fossology\Lib\Proxy\UploadTreeViewProxy;
30use Monolog\Logger;
31
32require_once(dirname(dirname(__FILE__)) . "/common-dir.php");
33
34class UploadDao
35{
36
37  const REUSE_NONE = 0;
38  const REUSE_ENHANCED = 2;
39  const REUSE_MAIN = 4;
40  const REUSE_CONF = 16;
41  const REUSE_COPYRIGHT = 128;
42  const UNIFIED_REPORT_HEADINGS = array(
43    "assessment" => array("Assessment Summary" => true),
44    "compliancetasks" => array("Required license compliance tasks" => true),
45    "acknowledgements" => array("Acknowledgements" => true),
46    "exportrestrictions" => array("Export Restrictions" => true),
47    "notes" => array("Notes" => true),
48    "scanresults" => array("Results of License Scan" => true),
49    "mainlicenses" => array("Main Licenses" => true),
50    "redlicense" => array("Other OSS Licenses (red) - Do not Use Licenses" => true),
51    "yellowlicense" => array("Other OSS Licenses (yellow) - additional obligations to common rules (e.g. copyleft)" => true),
52    "whitelicense" => array("Other OSS Licenses (white) - only common rules" => true),
53    "overviewwithwithoutobligations" => array("Overview of All Licenses with or without Obligations" => true),
54    "copyrights" => array("Copyrights" => true),
55    "copyrightsuf" => array("Copyrights (User Findings)" => true),
56    "bulkfindings" => array("Bulk Findings" => true),
57    "licensenf" => array("Non-Functional Licenses" => true),
58    "irrelevantfiles" => array("Irrelevant Files" => true),
59    "dnufiles" => array("Do not use Files" => true),
60    "changelog" => array("Clearing Protocol Change Log" => true)
61  );
62
63  /** @var DbManager */
64  private $dbManager;
65  /** @var Logger */
66  private $logger;
67  /** @var UploadPermissionDao */
68  private $permissionDao;
69
70  public function __construct(DbManager $dbManager, Logger $logger, UploadPermissionDao $uploadPermissionDao)
71  {
72    $this->dbManager = $dbManager;
73    $this->logger = $logger;
74    $this->permissionDao = $uploadPermissionDao;
75  }
76
77
78  /**
79   * @param $uploadTreeId
80   * @param string $uploadTreeTableName
81   * @return array
82   */
83  public function getUploadEntry($uploadTreeId, $uploadTreeTableName = "uploadtree")
84  {
85    $stmt = __METHOD__ . ".$uploadTreeTableName";
86    $uploadEntry = $this->dbManager->getSingleRow("SELECT * FROM $uploadTreeTableName WHERE uploadtree_pk = $1",
87        array($uploadTreeId), $stmt);
88    if ($uploadEntry) {
89      $uploadEntry['tablename'] = $uploadTreeTableName;
90    }
91    return $uploadEntry;
92  }
93
94  /**
95   * Get the first entry for uploadtree_pk for a given pfile in a given upload.
96   * @param integer $uploadFk Upload id
97   * @param integer $pfileFk  Pfile id
98   * @return integer Uploadtree_pk
99   */
100  public function getUploadtreeIdFromPfile($uploadFk, $pfileFk)
101  {
102    $uploadTreeTableName = $this->getUploadtreeTableName($uploadFk);
103    $stmt = __METHOD__ . ".$uploadTreeTableName";
104    $uploadEntry = $this->dbManager->getSingleRow("SELECT uploadtree_pk " .
105      "FROM $uploadTreeTableName " .
106      "WHERE upload_fk = $1 AND pfile_fk = $2",
107      array($uploadFk, $pfileFk), $stmt);
108    return intval($uploadEntry['uploadtree_pk']);
109  }
110
111  /**
112   * @param int $uploadId
113   * @return Upload|null
114   */
115  public function getUpload($uploadId)
116  {
117    $stmt = __METHOD__;
118    $row = $this->dbManager->getSingleRow("SELECT * FROM upload WHERE upload_pk = $1",
119        array($uploadId), $stmt);
120
121    return $row ? Upload::createFromTable($row) : null;
122  }
123
124  public function getActiveUploadsArray()
125  {
126    $stmt = __METHOD__;
127    $queryResult = $this->dbManager->getRows("SELECT * FROM upload where pfile_fk IS NOT NULL",
128        array(), $stmt);
129
130    $results = array();
131    foreach ($queryResult as $row) {
132      $results[] = Upload::createFromTable($row);
133    }
134
135    return $results;
136  }
137
138  /**
139   * @param $itemId
140   * @param $uploadTreeTableName
141   * @return ItemTreeBounds
142   */
143  public function getItemTreeBounds($itemId, $uploadTreeTableName = "uploadtree")
144  {
145    $uploadEntryData = $this->getUploadEntry($itemId, $uploadTreeTableName);
146    return $this->createItemTreeBounds($uploadEntryData, $uploadTreeTableName);
147  }
148
149  /**
150   * @param $uploadTreeId
151   * @param $uploadId
152   * @return ItemTreeBounds
153   */
154  public function getItemTreeBoundsFromUploadId($uploadTreeId, $uploadId)
155  {
156    $uploadTreeTableName = $this->getUploadtreeTableName($uploadId);
157    return $this->getItemTreeBounds($uploadTreeId, $uploadTreeTableName);
158  }
159
160  /**
161   * @param int $uploadId
162   * @param string|null
163   * @throws Exception
164   * @return ItemTreeBounds
165   */
166  public function getParentItemBounds($uploadId, $uploadTreeTableName = null)
167  {
168    if ($uploadTreeTableName === null) {
169      $uploadTreeTableName = $this->getUploadtreeTableName($uploadId);
170    }
171
172    $stmt = __METHOD__ . ".$uploadTreeTableName";
173    $parameters = array();
174    $uploadCondition = $this->handleUploadIdForTable($uploadTreeTableName, $uploadId, $parameters);
175
176    $uploadEntryData = $this->dbManager->getSingleRow("SELECT * FROM $uploadTreeTableName
177        WHERE parent IS NULL
178              $uploadCondition
179          ",
180        $parameters, $stmt);
181
182    return $uploadEntryData ? $this->createItemTreeBounds($uploadEntryData, $uploadTreeTableName) : false;
183  }
184
185  /**
186   * @param ItemTreeBounds $itemTreeBounds
187   * @return int
188   */
189  public function countPlainFiles(ItemTreeBounds $itemTreeBounds)
190  {
191    $uploadTreeTableName = $itemTreeBounds->getUploadTreeTableName();
192
193    $stmt = __METHOD__ . ".$uploadTreeTableName";
194    $parameters = array($itemTreeBounds->getLeft(), $itemTreeBounds->getRight());
195    $uploadCondition = $this->handleUploadIdForTable($uploadTreeTableName, $itemTreeBounds->getUploadId(), $parameters);
196
197    $row = $this->dbManager->getSingleRow("SELECT count(*) as count FROM $uploadTreeTableName
198        WHERE  lft BETWEEN $1 AND $2
199          $uploadCondition
200          AND ((ufile_mode & (3<<28))=0)
201          AND pfile_fk != 0",
202        $parameters, $stmt);
203    $fileCount = intval($row["count"]);
204    return $fileCount;
205  }
206
207  private function handleUploadIdForTable($uploadTreeTableName, $uploadId, &$parameters)
208  {
209    if ($uploadTreeTableName === "uploadtree" || $uploadTreeTableName === "uploadtree_a") {
210      $parameters[] = $uploadId;
211      return " AND upload_fk = $" . count($parameters) . " ";
212    } else {
213      return "";
214    }
215  }
216
217  /**
218   * @return array
219   */
220  public function getStatusTypeMap()
221  {
222    global $container;
223    /** @var UploadStatus */
224    $uploadStatus = $container->get('upload_status.types');
225    return $uploadStatus->getMap();
226  }
227
228  /**
229   * @brief Get the upload status.
230   * @param int $uploadId Upload to get status for
231   * @param int $groupId  Effective group
232   * @return integer Status fk or 1 if not found
233   * @throws Exception if upload not accessible.
234   */
235  public function getStatus($uploadId, $groupId)
236  {
237    if ($this->isAccessible($uploadId, $groupId)) {
238      $row = $this->dbManager->getSingleRow("SELECT status_fk " .
239        "FROM upload_clearing WHERE upload_fk=$1 AND group_fk=$2;",
240        array($uploadId, $groupId));
241      if (false === $row) {
242        return 1;
243      }
244      return $row['status_fk'];
245    } else {
246      throw new \Exception("permission denied");
247    }
248  }
249
250
251  /**
252   * @brief Get the upload assignee id.
253   * @param int $uploadId Upload to get assignee id
254   * @param int $groupId  Effective group
255   * @return integer 1 if not found
256   * @throws Exception if upload not accessible.
257   */
258  public function getAssignee($uploadId, $groupId)
259  {
260    if ($this->isAccessible($uploadId, $groupId)) {
261      $row = $this->dbManager->getSingleRow("SELECT assignee FROM upload_clearing WHERE upload_fk=$1 AND group_fk=$2;",
262       array($uploadId, $groupId));
263      if (false === $row) {
264        return 1;
265      }
266      return $row['assignee'];
267    } else {
268      throw new \Exception("permission denied");
269    }
270  }
271
272
273  /**
274   * \brief Get the uploadtree table name for this upload_pk
275   *        If upload_pk does not exist, return "uploadtree".
276   *
277   * \param $upload_pk
278   *
279   * \return uploadtree table name
280   */
281  public function getUploadtreeTableName($uploadId)
282  {
283    if (!empty($uploadId)) {
284      $statementName = __METHOD__;
285      $row = $this->dbManager->getSingleRow(
286          "SELECT uploadtree_tablename FROM upload WHERE upload_pk=$1",
287          array($uploadId),
288          $statementName
289      );
290      if (!empty($row['uploadtree_tablename'])) {
291        return $row['uploadtree_tablename'];
292      }
293    }
294    return "uploadtree";
295  }
296
297  /**
298   * @param int $uploadId
299   * @param int $itemId
300   * @return Item|null
301   */
302  public function getNextItem($uploadId, $itemId, $options = null)
303  {
304    return $this->getItemByDirection($uploadId, $itemId, self::DIR_FWD, $options);
305  }
306
307  /**
308   * @param $uploadId
309   * @param $itemId
310   * @return mixed
311   */
312  public function getPreviousItem($uploadId, $itemId, $options = null)
313  {
314    return $this->getItemByDirection($uploadId, $itemId, self::DIR_BCK, $options);
315  }
316
317  const DIR_FWD = 1;
318  const DIR_BCK = -1;
319  const NOT_FOUND = null;
320
321
322  /**
323   * @param $uploadId
324   * @param $itemId
325   * @param $direction
326   * @return Item|null
327   */
328  public function getItemByDirection($uploadId, $itemId, $direction, $options)
329  {
330    $uploadTreeTableName = $this->getUploadtreeTableName($uploadId);
331    $originItem = $this->getUploadEntry($itemId, $uploadTreeTableName);
332    $originLft = $originItem['lft'];
333
334    $options[UploadTreeProxy::OPT_ITEM_FILTER] = " AND ut.ufile_mode & (3<<28) = 0";
335    $uploadTreeViewName = 'items2care';
336
337    if ($direction == self::DIR_FWD) {
338      $uploadTreeViewName .= 'fwd';
339      $options[UploadTreeProxy::OPT_ITEM_FILTER] .= " AND lft>$1";
340      $order = 'ASC';
341    } else {
342      $uploadTreeViewName .= 'bwd';
343      $options[UploadTreeProxy::OPT_ITEM_FILTER] .= " AND lft<$1";
344      $order = 'DESC';
345    }
346
347    $uploadTreeView = new UploadTreeProxy($uploadId, $options, $uploadTreeTableName, $uploadTreeViewName);
348    $statementName = __METHOD__ . ".$uploadTreeViewName.";
349    $query = $uploadTreeView->getDbViewQuery()." ORDER BY lft $order";
350
351    $newItemRow = $this->dbManager->getSingleRow("$query LIMIT 1", array($originLft), $statementName);
352    if ($newItemRow) {
353      return $this->createItem($newItemRow, $uploadTreeTableName);
354    } else {
355      return self::NOT_FOUND;
356    }
357  }
358
359
360  /**
361   * @param $uploadId
362   * @return int uploadtreeId of top item
363   */
364  public function getUploadParent($uploadId)
365  {
366    $uploadTreeTableName = $this->getUploadtreeTableName($uploadId);
367    $statementname = __METHOD__ . $uploadTreeTableName;
368
369    $parent = $this->dbManager->getSingleRow(
370        "SELECT uploadtree_pk
371            FROM $uploadTreeTableName
372            WHERE upload_fk=$1 AND parent IS NULL", array($uploadId), $statementname);
373    if (false === $parent) {
374      throw new \Exception("Missing upload tree parent for upload");
375    }
376    return $parent['uploadtree_pk'];
377  }
378
379  public function getLeftAndRight($uploadtreeID, $uploadTreeTableName = "uploadtree")
380  {
381    $statementName = __METHOD__ . $uploadTreeTableName;
382    $leftRight = $this->dbManager->getSingleRow(
383        "SELECT lft,rgt FROM $uploadTreeTableName WHERE uploadtree_pk = $1",
384        array($uploadtreeID), $statementName
385    );
386
387    return array($leftRight['lft'], $leftRight['rgt']);
388  }
389
390  /**
391   * @var ItemTreeBounds $itemTreeBounds
392   * @param $uploadTreeView
393   * @return int
394   */
395  public function getContainingFileCount(ItemTreeBounds $itemTreeBounds, UploadTreeProxy $uploadTreeView)
396  {
397    $sql = "SELECT count(*) FROM " . $uploadTreeView->getDbViewName() . " WHERE lft BETWEEN $1 AND $2";
398    $result = $this->dbManager->getSingleRow($sql
399        , array($itemTreeBounds->getLeft(), $itemTreeBounds->getRight()), __METHOD__ . $uploadTreeView->asCTE());
400    $output = $result['count'];
401    return $output;
402  }
403
404  /**
405   * @var ItemTreeBounds $itemTreeBounds
406   * @param string $addCondition
407   * @param array $addParameters
408   * @return Item[]
409   */
410  public function getContainedItems(ItemTreeBounds $itemTreeBounds, $addCondition = "", $addParameters = array())
411  {
412    $uploadTreeTableName = $itemTreeBounds->getUploadTreeTableName();
413
414    $statementName = __METHOD__ . ".$uploadTreeTableName";
415
416    $view = new UploadTreeViewProxy($itemTreeBounds, array(UploadTreeViewProxy::CONDITION_PLAIN_FILES));
417
418    $this->dbManager->prepare($statementName,
419        $view->asCTE() . " SELECT * FROM " . $view->getDbViewName() ." ut ".
420        ($addCondition ? " WHERE " . $addCondition : ''));
421    $res = $this->dbManager->execute($statementName, $addParameters);
422    $items = array();
423
424    while ($row = $this->dbManager->fetchArray($res)) {
425      $items[] = $this->createItem($row, $uploadTreeTableName);
426    }
427    $this->dbManager->freeResult($res);
428    return $items;
429  }
430
431  /**
432   * @param int $uploadId
433   * @param int $reusedUploadId
434   * @param int $groupId
435   * @param int $reusedGroupId
436   * @param int $reuseMode
437   */
438  public function addReusedUpload($uploadId, $reusedUploadId, $groupId, $reusedGroupId, $reuseMode=0)
439  {
440    $this->dbManager->insertTableRow('upload_reuse',
441            array('upload_fk'=>$uploadId, 'group_fk'=> $groupId, 'reused_upload_fk'=>$reusedUploadId, 'reused_group_fk'=>$reusedGroupId,'reuse_mode'=>$reuseMode));
442  }
443
444  /**
445   * @param int $uploadId
446   * @param int $groupId
447   * @return int
448   */
449  public function getReusedUpload($uploadId, $groupId)
450  {
451    $statementName = __METHOD__;
452
453    $this->dbManager->prepare($statementName,
454        "SELECT reused_upload_fk, reused_group_fk, reuse_mode FROM upload_reuse WHERE upload_fk = $1 AND group_fk=$2 ORDER BY date_added DESC");
455    $res = $this->dbManager->execute($statementName, array($uploadId, $groupId));
456    $reusedPairs = $this->dbManager->fetchAll($res);
457    $this->dbManager->freeResult($res);
458    return $reusedPairs;
459  }
460
461  /**
462   * @param array $uploadEntry
463   * @param string $uploadTreeTableName
464   * @return Item
465   */
466  protected function createItem($uploadEntry, $uploadTreeTableName)
467  {
468    $itemTreeBounds = new ItemTreeBounds(
469        intval($uploadEntry['uploadtree_pk']),
470        $uploadTreeTableName,
471        intval($uploadEntry['upload_fk']),
472        intval($uploadEntry['lft']), intval($uploadEntry['rgt']));
473
474    $parent = $uploadEntry['parent'];
475    $item = new Item(
476        $itemTreeBounds, $parent !== null ? intval($parent) : null, intval($uploadEntry['pfile_fk']), intval($uploadEntry['ufile_mode']), $uploadEntry['ufile_name']
477    );
478    return $item;
479  }
480
481  /**
482   * @param array $uploadEntryData
483   * @param string $uploadTreeTableName
484   * @throws Exception
485   * @return ItemTreeBounds
486   */
487  protected function createItemTreeBounds($uploadEntryData, $uploadTreeTableName)
488  {
489    if ($uploadEntryData === false) {
490      throw new Exception("did not find uploadTreeId in $uploadTreeTableName");
491    }
492    return new ItemTreeBounds(intval($uploadEntryData['uploadtree_pk']), $uploadTreeTableName, intval($uploadEntryData['upload_fk']), intval($uploadEntryData['lft']), intval($uploadEntryData['rgt']));
493  }
494
495  /**
496   * @param ItemTreeBounds $itemTreeBounds
497   * @param bool $isFlat plain files from sub*folders instead of folders
498   * @return array
499   */
500  public function countNonArtifactDescendants(ItemTreeBounds $itemTreeBounds, $isFlat=true)
501  {
502    $stmt=__METHOD__;
503    $sql = "SELECT count(*) FROM ".$itemTreeBounds->getUploadTreeTableName()." ut "
504         . "WHERE ut.upload_fk=$1";
505    $params = array($itemTreeBounds->getUploadId());
506    if (!$isFlat) {
507      $stmt = __METHOD__.'.parent';
508      $params[] = $itemTreeBounds->getItemId();
509      $sql .= " AND ut.ufile_mode & (1<<28) = 0 AND ut.realparent = $2";
510    } else {
511      $params[] = $itemTreeBounds->getLeft();
512      $params[] = $itemTreeBounds->getRight();
513      $sql .= " AND ut.ufile_mode & (3<<28) = 0 AND (ut.lft BETWEEN $2 AND $3)";
514    }
515
516    $descendants = $this->dbManager->getSingleRow($sql,$params);
517    return $descendants['count'];
518  }
519
520
521  public function isAccessible($uploadId, $groupId)
522  {
523    return $this->permissionDao->isAccessible($uploadId, $groupId);
524  }
525
526  public function isEditable($uploadId, $groupId)
527  {
528    return $this->permissionDao->isEditable($uploadId, $groupId);
529  }
530
531  public function makeAccessibleToGroup($uploadId, $groupId, $perm=null)
532  {
533    $this->permissionDao->makeAccessibleToGroup($uploadId, $groupId, $perm);
534  }
535
536  public function makeAccessibleToAllGroupsOf($uploadId, $userId, $perm=null)
537  {
538    $this->permissionDao->makeAccessibleToAllGroupsOf($uploadId, $userId, $perm);
539  }
540
541  /**
542   * @param int $uploadId
543   * @return array with keys sha1, md5, sha256
544   */
545  public function getUploadHashes($uploadId)
546  {
547    $pfile = $this->dbManager->getSingleRow('SELECT pfile.* FROM upload, pfile WHERE upload_pk=$1 AND pfile_fk=pfile_pk',
548        array($uploadId), __METHOD__);
549    return array('sha1'=>$pfile['pfile_sha1'],'md5'=>$pfile['pfile_md5'],'sha256'=>$pfile['pfile_sha256']);
550  }
551
552  /**
553   * @param int $itemId
554   * @param string $uploadId
555   * @param string $uploadtreeTablename
556   * @return array
557   */
558  public function getFatItemArray($itemId,$uploadId,$uploadtreeTablename)
559  {
560    $sqlChildrenOf = "SELECT COUNT(*) FROM $uploadtreeTablename s
561         WHERE ufile_mode&(1<<28)=0 and s.upload_fk=$2 AND s.realparent=";
562    $sql="WITH RECURSIVE item_path (item_id,num_children,depth,ufile_mode,ufile_name) AS (
563        SELECT uploadtree_pk item_id, ($sqlChildrenOf $1) num_children, 0 depth, ufile_mode, ufile_name
564          FROM $uploadtreeTablename WHERE upload_fk=$2 AND uploadtree_pk=$1
565        UNION
566        SELECT uploadtree_pk item_id, ($sqlChildrenOf ut.uploadtree_pk) num_children,
567               item_path.depth+1 depth, ut.ufile_mode, item_path.ufile_name||'/'||ut.ufile_name ufile_name
568          FROM $uploadtreeTablename ut INNER JOIN item_path ON item_id=ut.realparent
569          WHERE upload_fk=$2 AND ut.ufile_mode&(1<<28)=0 AND num_children<2
570        )
571        SELECT * FROM item_path WHERE num_children!=1 OR ufile_mode&(1<<29)=0 ORDER BY depth DESC LIMIT 1";
572    return $this->dbManager->getSingleRow($sql,array($itemId, $uploadId),__METHOD__.$uploadtreeTablename);
573  }
574
575  /**
576   * @param int $itemId
577   * @param string $uploadId
578   * @param string $uploadtreeTablename
579   * @return int
580   */
581  public function getFatItemId($itemId,$uploadId,$uploadtreeTablename)
582  {
583    $itemRow = $this->getFatItemArray($itemId,$uploadId,$uploadtreeTablename);
584    return $itemRow['item_id'];
585  }
586
587  /**
588   * @param ItemTreeBounds $itemTreeBounds
589   * @return array
590   */
591  public function getPFileDataPerFileName(ItemTreeBounds $itemTreeBounds)
592  {
593    $uploadTreeTableName = $itemTreeBounds->getUploadTreeTableName();
594    $statementName = __METHOD__ . '.' . $uploadTreeTableName;
595    $param = array();
596
597    $param[] = $itemTreeBounds->getLeft();
598    $param[] = $itemTreeBounds->getRight();
599    $condition = " lft BETWEEN $1 AND $2";
600    $condition .= " AND (ufile_mode & (1<<28)) = 0";
601
602    if ('uploadtree_a' == $uploadTreeTableName) {
603      $param[] = $itemTreeBounds->getUploadId();
604      $condition .= " AND upload_fk=$".count($param);
605    }
606
607    $sql = "
608SELECT ufile_name, uploadtree_pk, lft, rgt, ufile_mode,
609       pfile_pk, pfile_md5, pfile_sha1, pfile_sha256
610FROM $uploadTreeTableName
611  LEFT JOIN pfile
612    ON pfile_fk = pfile_pk
613WHERE $condition
614ORDER BY lft asc
615";
616
617    $this->dbManager->prepare($statementName, $sql);
618    $result = $this->dbManager->execute($statementName, $param);
619    $pfilePerFileName = array();
620
621    $row = $this->dbManager->fetchArray($result);
622    $pathStack = array($row['ufile_name']);
623    $rgtStack = array($row['rgt']);
624    $lastLft = $row['lft'];
625    $this->addToPFilePerFileName($pfilePerFileName, $pathStack, $row);
626    while ($row = $this->dbManager->fetchArray($result)) {
627      if ($row['lft'] < $lastLft) {
628        continue;
629      }
630
631      $this->updateStackState($pathStack, $rgtStack, $lastLft, $row);
632      $this->addToPFilePerFileName($pfilePerFileName, $pathStack, $row);
633    }
634    $this->dbManager->freeResult($result);
635    return $pfilePerFileName;
636  }
637
638  private function updateStackState(&$pathStack, &$rgtStack, &$lastLft, $row)
639  {
640    if ($row['lft'] >= $lastLft) {
641      while (count($rgtStack) > 0 && $row['lft'] > $rgtStack[count($rgtStack)-1]) {
642        array_pop($pathStack);
643        array_pop($rgtStack);
644      }
645      if ($row['lft'] > $lastLft) {
646        array_push($pathStack, $row['ufile_name']);
647        array_push($rgtStack, $row['rgt']);
648        $lastLft = $row['lft'];
649      }
650    }
651  }
652
653  private function addToPFilePerFileName(&$pfilePerFileName, $pathStack, $row)
654  {
655    if (($row['ufile_mode'] & (1 << 29)) == 0) {
656      $path = implode($pathStack,'/');
657      $pfilePerFileName[$path]['pfile_pk'] = $row['pfile_pk'];
658      $pfilePerFileName[$path]['uploadtree_pk'] = $row['uploadtree_pk'];
659      $pfilePerFileName[$path]['md5'] = $row['pfile_md5'];
660      $pfilePerFileName[$path]['sha1'] = $row['pfile_sha1'];
661      $pfilePerFileName[$path]['sha256'] = $row['pfile_sha256'];
662    }
663  }
664
665  /**
666   * @param ItemTreeBounds $itemTreeBounds
667   * @param String $hashAlgo
668   * @return array
669   */
670  public function getPFilesDataPerHashAlgo(ItemTreeBounds $itemTreeBounds, $hashAlgo="sha1")
671  {
672    $uploadTreeTableName = $itemTreeBounds->getUploadTreeTableName();
673    $statementName = __METHOD__ . '.' . $uploadTreeTableName;
674    $param = array();
675
676    $param[] = $itemTreeBounds->getLeft();
677    $param[] = $itemTreeBounds->getRight();
678    $condition = " lft BETWEEN $1 AND $2";
679    $condition .= " AND (ufile_mode & (1<<28)) = 0";
680
681    if ('uploadtree_a' == $uploadTreeTableName) {
682      $param[] = $itemTreeBounds->getUploadId();
683      $condition .= " AND upload_fk=$".count($param);
684    }
685    $condition .= " AND pfile_$hashAlgo IS NOT NULL";
686
687    $sql = "
688SELECT pfile_fk, uploadtree_pk, ufile_mode, pfile_$hashAlgo as hash
689FROM $uploadTreeTableName
690  LEFT JOIN pfile
691    ON pfile_fk = pfile_pk
692WHERE $condition
693ORDER BY lft asc
694";
695
696    $this->dbManager->prepare($statementName, $sql);
697    $result = $this->dbManager->execute($statementName, $param);
698
699    $pfilePerHashAlgo = array();
700    while ($row = $this->dbManager->fetchArray($result)) {
701      if (($row['ufile_mode']&(1<<29)) == 0) {
702        $pfilePerHashAlgo[strtolower($row['hash'])][] = array('pfile_pk' => $row['pfile_fk'],
703                                                              'uploadtree_pk' => $row['uploadtree_pk']);
704      }
705    }
706    $this->dbManager->freeResult($result);
707    return $pfilePerHashAlgo;
708  }
709
710
711   /* @param int $uploadId
712   * @return array
713   */
714  public function getReportInfo($uploadId)
715  {
716    $stmt = __METHOD__;
717    $sql = "SELECT * FROM report_info WHERE upload_fk = $1";
718    $row = $this->dbManager->getSingleRow($sql, array($uploadId), $stmt);
719
720    if (empty($row)) {
721      $this->dbManager->begin();
722      $stmt = __METHOD__.'ifempty';
723      $sql = "INSERT INTO report_info (upload_fk) VALUES ($1) RETURNING *";
724      $row = $this->dbManager->getSingleRow($sql, array($uploadId), $stmt);
725      $this->dbManager->commit();
726    }
727    return $row;
728  }
729
730  /**
731   * @brief Get Pfile hashes from the pfile id
732   * @param $pfilePk
733   * @return array
734   */
735  public function getUploadHashesFromPfileId($pfilePk)
736  {
737    $stmt = __METHOD__."getUploadHashesFromPfileId";
738    $sql = "SELECT * FROM pfile WHERE pfile_pk = $1";
739    $row = $this->dbManager->getSingleRow($sql, array($pfilePk), $stmt);
740
741    return ["sha1" => $row["pfile_sha1"], "md5" => $row["pfile_md5"], "sha256" => $row["pfile_sha256"]];
742  }
743
744  /**
745   * @param int $uploadId
746   * @param int $reusedUploadId
747   * @return bolean
748   */
749  public function insertReportConfReuse($uploadId, $reusedUploadId)
750  {
751    $stmt = __METHOD__;
752    $sql = "SELECT exists(SELECT 1 FROM report_info WHERE upload_fk = $1 LIMIT 1)::int";
753    $row = $this->dbManager->getSingleRow($sql, array($reusedUploadId), $stmt);
754
755    if ($row['exists']) {
756      $stmt = __METHOD__."CopyinsertReportConfReuse";
757      $this->dbManager->prepare($stmt,
758        "INSERT INTO report_info(
759           upload_fk, ri_ga_checkbox_selection, ri_spdx_selection,
760           ri_excluded_obligations, ri_reviewed, ri_footer, ri_report_rel,
761           ri_community, ri_component, ri_version, ri_release_date,
762           ri_sw360_link, ri_general_assesment, ri_ga_additional, ri_ga_risk)
763        SELECT $1, ri_ga_checkbox_selection, ri_spdx_selection,
764           ri_excluded_obligations, ri_reviewed, ri_footer, ri_report_rel,
765           ri_community, ri_component, ri_version, ri_release_date,
766           ri_sw360_link, ri_general_assesment, ri_ga_additional, ri_ga_risk
767           FROM report_info WHERE upload_fk = $2"
768      );
769      $this->dbManager->freeResult($this->dbManager->execute($stmt, array($uploadId, $reusedUploadId)));
770      return true;
771    }
772    return false;
773  }
774}
775
776