DbHelper.php

Go to the documentation of this file.

<?php
/*
 SPDX-FileCopyrightText: © 2017 Siemens AG
 
 SPDX-License-Identifier: GPL-2.0-only
*/
 
namespace Fossology\UI\Api\Helper;
 
require_once dirname(dirname(dirname(dirname(__DIR__)))) .
  "/lib/php/common-db.php";
 
use Fossology\Lib\Auth\Auth;
use Fossology\Lib\Dao\FolderDao;
use Fossology\Lib\Dao\UploadDao;
use Fossology\Lib\Data\Folder\Folder;
use Fossology\Lib\Db\DbManager;
use Fossology\Lib\Db\ModernDbManager;
use Fossology\Lib\Exceptions\DuplicateTokenKeyException;
use Fossology\Lib\Exceptions\DuplicateTokenNameException;
use Fossology\Lib\Proxy\LicenseViewProxy;
use Fossology\Lib\Proxy\UploadBrowseProxy;
use Fossology\UI\Api\Models\ApiVersion;
use Fossology\UI\Api\Models\Hash;
use Fossology\UI\Api\Models\Job;
use Fossology\UI\Api\Models\Upload;
use Fossology\UI\Api\Models\User;
 
class DbHelper
{
  private $dbManager;
 
  private $folderDao;
 
  private $uploadDao;
 
  public function __construct(DbManager $dbManager, FolderDao $folderDao,
    UploadDao $uploadDao)
  {
    $this->dbManager = $dbManager;
    $this->folderDao = $folderDao;
    $this->uploadDao = $uploadDao;
  }
 
  public function getDbManager()
  {
    return $this->dbManager;
  }
 
  public function getUploads($userId, $groupId, $limit, $page = 1,
    $uploadId = null, $options = null, $recursive = true, $apiVersion=ApiVersion::V1)
  {
    $uploadProxy = new UploadBrowseProxy($groupId, 0, $this->dbManager);
    $folderId = $options["folderId"];
    if ($folderId === null) {
      $users = $this->getUsers($userId);
      $folderId = $users[0]->getRootFolderId();
    }
    $folders = [$folderId];
 
    if ($uploadId !== null) {
      $recursive = true;
      $users = $this->getUsers($userId);
      $folderId = $users[0]->getRootFolderId();
      $folders = [$folderId];
    }
 
    if ($recursive) {
      $tree = $this->folderDao->getFolderStructure($folderId);
      $folders = array_map(function ($folder) {
        return $folder[$this->folderDao::FOLDER_KEY]->getId();
      }, $tree);
    }
 
    $params = [$folders];
    $partialQuery = $uploadProxy->getFolderPartialQuery($params);
 
    $where = "";
    $statementCount = __METHOD__ . ".countAllUploads";
    $statementGet = __METHOD__ . ".getAllUploads.$limit";
    if ($uploadId !== null) {
      $params[] = $uploadId;
      $where .= " AND upload.upload_pk = $" . count($params);
      $statementGet .= ".upload";
      $statementCount .= ".upload";
    }
    if (! empty($options["name"])) {
      $params[] = strtolower("%" . $options["name"] . "%");
      $where .= " AND (LOWER(upload_desc) LIKE $" . count($params) .
        " OR LOWER(ufile_name) LIKE $" . count($params) .
        " OR LOWER(upload_filename) LIKE $" . count($params) . ")";
      $statementGet .= ".name";
      $statementCount .= ".name";
    }
    if (! empty($options["status"])) {
      $params[] = $options["status"];
      $where .= " AND status_fk = $" . count($params);
      $statementGet .= ".stat";
      $statementCount .= ".stat";
    }
    if (! empty($options["assignee"])) {
      $params[] = $options["assignee"];
      $where .= " AND assignee = $" . count($params);
      $statementGet .= ".assi";
      $statementCount .= ".assi";
    }
    if (! empty($options["since"])) {
      $params[] = $options["since"];
      $where .= " AND upload_ts >= to_timestamp($" . count($params) . ")";
      $statementGet .= ".since";
      $statementCount .= ".since";
    }
    $sql = "SELECT count(*) AS cnt FROM $partialQuery $where;";
    $totalResult = $this->dbManager->getSingleRow($sql, $params, $statementCount);
    $totalResult = intval($totalResult['cnt']);
    $totalResult = intval(ceil($totalResult / $limit));
 
    $params[] = ($page - 1) * $limit;
 
    $sql = "SELECT
upload.upload_pk, upload.upload_desc, upload.upload_ts, upload.upload_filename, upload_clearing.assignee
FROM $partialQuery $where ORDER BY upload_pk ASC LIMIT $limit OFFSET $" .
      count($params) . ";";
    $results = $this->dbManager->getRows($sql, $params, $statementGet);
    $uploads = [];
    foreach ($results as $row) {
      $uploadId = $row["upload_pk"];
      $pfile_size = null;
      $pfile_sha1 = null;
      $pfile_md5 = null;
      $pfile_sha256 = null;
      $pfile = $this->getPfileInfoForUpload($uploadId);
      if ($pfile !== null) {
        $pfile_size = $pfile['pfile_size'];
        $pfile_sha1 = $pfile['pfile_sha1'];
        $pfile_md5 = $pfile['pfile_md5'];
        $pfile_sha256 = $pfile['pfile_sha256'];
      }
 
      $folder = $this->getFolderForUpload($uploadId);
      if ($folder === null) {
        continue;
      }
      $folderId = $folder->getId();
      $folderName = $folder->getName();
 
      $hash = new Hash($pfile_sha1, $pfile_md5, $pfile_sha256, $pfile_size);
      $upload = new Upload($folderId, $folderName, $uploadId,
        $row["upload_desc"], $row["upload_filename"], $row["upload_ts"], $row["assignee"], $hash);
      if (! empty($row["assignee"]) && $row["assignee"] != 1) {
        $upload->setAssigneeDate($this->uploadDao->getAssigneeDate($uploadId));
      }
      $upload->setClosingDate($this->uploadDao->getClosedDate($uploadId));
      $uploads[] = $upload->getArray($apiVersion);
    }
    return [$totalResult, $uploads];
  }
 
  public function getFilenameFromUploadTree($uploadTreePk)
  {
    return $this->dbManager->getSingleRow(
      "SELECT DISTINCT ufile_name FROM uploadtree
WHERE uploadtree_pk=$1", [$uploadTreePk])["ufile_name"];
  }
 
  public function doesIdExist($tableName, $idRowName, $id)
  {
    return (0 < (intval($this->getDbManager()->getSingleRow("SELECT COUNT(*)
FROM $tableName WHERE $idRowName = $1", [$id],
      __METHOD__ . $tableName . $idRowName)["count"])));
  }
 
  public function deleteUser($id)
  {
    require_once dirname(dirname(__DIR__)) . "/user-del-helper.php";
    deleteUser($id, $this->getDbManager());
  }
 
  public function getUsers($id = null)
  {
    if ($id == null) {
      $usersSQL = "SELECT user_pk, user_name, user_desc, user_email,
                  email_notify, root_folder_fk, group_fk, user_perm, user_agent_list, default_bucketpool_fk FROM users;";
      $statement = __METHOD__ . ".getAllUsers";
    } else {
      $usersSQL = "SELECT user_pk, user_name, user_desc, user_email,
                email_notify, root_folder_fk, group_fk, user_perm, user_agent_list, default_bucketpool_fk FROM users
                WHERE user_pk = $1;";
      $statement = __METHOD__ . ".getSpecificUser";
    }
    $users = [];
    if ($id === null) {
      $result = $this->dbManager->getRows($usersSQL, [], $statement);
    } else {
      $result = $this->dbManager->getRows($usersSQL, [$id], $statement);
    }
    $currentUser = Auth::getUserId();
    $userIsAdmin = Auth::isAdmin();
    foreach ($result as $row) {
      $user = null;
      if ($userIsAdmin ||
        ($row["user_pk"] == $currentUser)) {
        $user = new User($row["user_pk"], $row["user_name"], $row["user_desc"],
          $row["user_email"], $row["user_perm"], $row["root_folder_fk"],
          $row["email_notify"], $row["user_agent_list"], $row["group_fk"], $row["default_bucketpool_fk"]);
      } else {
        $user = new User($row["user_pk"], $row["user_name"], $row["user_desc"],
          null, null, null, null, null, null);
      }
      $users[] = $user;
    }
 
    return $users;
  }
 
 
  private function getJobStatusCteSQLStatement()
  {
    return "WITH job_with_status_cte AS (
      SELECT j.job_pk, j.job_queued, j.job_name, j.job_upload_fk, j.job_user_fk, j.job_group_fk,
        CASE
          WHEN COUNT(CASE WHEN jq.jq_endtext = 'Failed' THEN 1 END) > 0 THEN 'Failed'
          WHEN COUNT(CASE WHEN jq.jq_endtext in ('Started', 'Restarted', 'Paused') THEN 1 END) > 0 THEN 'Processing'
          WHEN COUNT(CASE WHEN jq.jq_endtext IS NULL THEN 1 END) > 0 THEN 'Queued'
          ELSE 'Completed'
        END AS job_status
      FROM job j
      LEFT JOIN jobqueue jq ON j.job_pk=jq.jq_job_fk
      GROUP BY j.job_pk, j.job_queued, j.job_name, j.job_upload_fk, j.job_user_fk, j.job_group_fk
    )";
  }
 
  public function getJobs($id = null, $status = null, $sort = "ASC", $limit = 0, $page = 1, $uploadId = null, $userId = null)
  {
    $jobsWithStatusCteSQL = $this->getJobStatusCteSQLStatement();
    $jobSQL = "$jobsWithStatusCteSQL SELECT * FROM job_with_status_cte";
    $totalJobSql = "$jobsWithStatusCteSQL SELECT count(*) AS cnt FROM job_with_status_cte";
 
    $pagination = "";
    $params = [];
    $filter = [];
    $statement = $userId !== null ? __METHOD__ . ".getUserJobs" : __METHOD__ . ".getJobs";
    $countStatement = __METHOD__ . ".getJobCount";
 
    if ($id !== null) {
      $params[] = $id;
      $filter[] = "job_pk = $" . count($params);
      $statement .= ".withJobFilter";
      $countStatement .= ".withJobFilter";
    } elseif ($uploadId !== null) {
      $params[] = $uploadId;
      $filter[] = "job_upload_fk = $" . count($params);
      $statement .= ".withUploadFilter";
      $countStatement .= ".withUploadFilter";
    }
 
    // if userId was given, add it to the where filter
    if ($userId !== null) {
      $params[] = $userId;
      $filter[] = "job_user_fk = $" . count($params);
    }
 
    // if status was given, add it to the where filter
    if ($status !== null && in_array($status, ["Failed", "Processing", "Queued", "Completed"])) {
      $params[] = $status;
      $filter[] = "job_status = $" . count($params);
    }
    // build where filter query
    $filterSQL = $filter ? "WHERE " . implode(" AND ", $filter) : "";
 
    // get result for total count
    $result = $this->dbManager->getSingleRow("$totalJobSql $filterSQL;", $params,
      $countStatement);
    $totalResult = $result['cnt'];
 
    // sort results in given order, make sure only "ASC" and "DESC" are accepted
    $sort = strtoupper($sort);
    $orderBy = in_array($sort, ["ASC", "DESC"]) ? "ORDER BY job_queued $sort" : "";
 
    $offset = ($page - 1) * $limit;
    if ($limit > 0) {
      $params[] = $limit;
      $pagination = "LIMIT $" . count($params);
      $params[] = $offset;
      $pagination .= " OFFSET $" . count($params);
      $statement .= ".withLimit";
      $totalResult = ceil($totalResult / $limit);
    } else {
      $totalResult = 1;
    }
 
    // get result for jobs
    $jobs = [];
    $result = $this->dbManager->getRows("$jobSQL $filterSQL $orderBy $pagination;", $params,
      $statement);
    foreach ($result as $row) {
      $job = new Job($row["job_pk"]);
      $job->setName($row["job_name"]);
      $job->setQueueDate($row["job_queued"]);
      $job->setUploadId($row["job_upload_fk"]);
      $job->setUserId($row["job_user_fk"]);
      $job->setGroupId($row["job_group_fk"]);
      $job->setStatus($row["job_status"]);
      $jobs[] = $job;
    }
    return [$jobs, $totalResult];
  }
 
  public function getUserJobs($userId = null, $status = null, $sort = "ASC", $limit = 0, $page = 1)
  {
    return $this->getJobs(null, $status, $sort, $limit, $page, null, $userId);
  }
 
  public function getTokenKey($tokenId)
  {
    $sql = "SELECT token_key, client_id, created_on, expire_on, user_fk, active, token_scope " .
      "FROM personal_access_tokens WHERE pat_pk = $1;";
    return $this->dbManager->getSingleRow($sql, [$tokenId],
      __METHOD__ . ".getTokenSecret");
  }
 
  public function invalidateToken($tokenId)
  {
    $sql = "UPDATE personal_access_tokens SET active = false WHERE pat_pk = $1;";
    $this->dbManager->getSingleRow($sql, [$tokenId], __METHOD__ . ".invalidateToken");
  }
 
  public function insertNewTokenKey($userId, $expire, $scope, $name, $key)
  {
    if (! $this->checkTokenNameUnique($userId, $name)) {
      throw new DuplicateTokenNameException(
        "Already have a token with same name.", 409);
    }
    if (! $this->checkTokenKeyUnique($userId, $name)) {
      throw new DuplicateTokenKeyException();
    }
    $sql = "INSERT INTO personal_access_tokens " .
      "(user_fk, created_on, expire_on, token_scope, token_name, token_key, active) " .
      "VALUES ($1, NOW(), $2, $3, $4, $5, true) " .
      "RETURNING pat_pk || '.' || user_fk AS jti, created_on";
    return $this->dbManager->getSingleRow($sql, [
      $userId, $expire, $scope, $name, $key
    ], __METHOD__ . ".insertNewToken");
  }
 
  public function addNewClient($name, $userId, $clientId, $scope)
  {
    $sql = "INSERT INTO personal_access_tokens" .
      "(user_fk, created_on, token_scope, token_name, client_id, active)" .
      "VALUES ($1, NOW(), $2, $3, $4, true);";
    $this->dbManager->getSingleRow($sql, [
      $userId, $scope, $name, $clientId
    ], __METHOD__);
  }
 
  private function checkTokenNameUnique($userId, $tokenName)
  {
    $tokenIsUnique = true;
    $sql = "SELECT count(*) AS cnt FROM personal_access_tokens " .
      "WHERE user_fk = $1 AND token_name = $2;";
    $result = $this->dbManager->getSingleRow($sql, [$userId, $tokenName],
      __METHOD__ . ".checkTokenNameUnique");
    if ($result['cnt'] != 0) {
      $tokenIsUnique = false;
    }
    return $tokenIsUnique;
  }
 
  private function checkTokenKeyUnique($userId, $tokenKey)
  {
    $tokenIsUnique = true;
    $sql = "SELECT count(*) AS cnt FROM personal_access_tokens " .
      "WHERE user_fk = $1 AND token_key = $2;";
    $result = $this->dbManager->getSingleRow($sql, [$userId, $tokenKey],
      __METHOD__ . ".checkTokenKeyUnique");
    if ($result['cnt'] != 0) {
      $tokenIsUnique = false;
    }
    return $tokenIsUnique;
  }
 
  public function getMaxTokenValidity()
  {
    $sql = "SELECT conf_value FROM sysconfig WHERE variablename = $1;";
    $result = $this->dbManager->getSingleRow($sql, ["PATMaxExipre"],
      __METHOD__ . ".tokenMaxValidFromSysconfig");
    $validity = 30;
    if (! empty($result['conf_value'])) {
      $validity = intval($result['conf_value']);
    }
    return $validity;
  }
 
  public function getPfileInfoForUpload($uploadId)
  {
    $sql = "SELECT pfile.* FROM upload INNER JOIN pfile " .
      "ON pfile_fk = pfile_pk WHERE upload_pk = $1;";
    $result = $this->dbManager->getSingleRow($sql, [$uploadId],
      __METHOD__ . ".getPfileFromUpload");
    if (! empty($result)) {
      return $result;
    }
    return null;
  }
 
  private function getFolderForUpload($uploadId)
  {
    $contentId = $this->folderDao->getFolderContentsId($uploadId,
      $this->folderDao::MODE_UPLOAD);
    $content = $this->folderDao->getContent($contentId);
    return $this->folderDao->getFolder($content['parent_fk']);
  }
 
  public function getLicensesPaginated($page, $limit, $kind, $groupId, $active)
  {
    $statementName = __METHOD__;
    $rfTable = 'license_all';
    $options = ['columns' => ['rf_pk', 'rf_shortname', 'rf_fullname', 'rf_text',
      'rf_url', 'rf_risk', 'group_fk']];
    if ($active) {
      $options['extraCondition'] = "rf_active = '" .
        $this->dbManager->booleanToDb($active) . "'";
    }
    if ($kind == "candidate") {
      $options['diff'] = true;
    } elseif ($kind == "main") {
      $groupId = 0;
    }
    $licenseViewDao = new LicenseViewProxy($groupId, $options, $rfTable);
    $withCte = $licenseViewDao->asCTE();
 
    return $this->dbManager->getRows($withCte .
      " SELECT * FROM $rfTable ORDER BY LOWER(rf_shortname) " .
      "LIMIT $1 OFFSET $2;",
      [$limit, ($page - 1) * $limit], $statementName);
  }
 
  public function getLicenseCount($kind, $groupId)
  {
    $sql = "SELECT sum(cnt) AS total FROM (";
    $mainLicSql = " SELECT count(*) AS cnt FROM ONLY license_ref ";
    $candidateLicSql = " SELECT count(*) AS cnt FROM license_candidate WHERE group_fk = $1";
    $params = [];
 
    if ($kind == "main") {
      $sql .= $mainLicSql;
    } elseif ($kind == "candidate") {
      $sql .= $candidateLicSql;
      $params[] = $groupId;
    } else {
      $sql .= $mainLicSql . " UNION ALL " . $candidateLicSql;
      $params[] = $groupId;
    }
    $sql .= ") as all_lic;";
 
    $statement = __METHOD__ . ".getLicenseCount.$kind";
    $result = $this->dbManager->getSingleRow($sql, $params, $statement);
    return intval($result['total']);
  }
 
  /*
   * Get the OAuth token ID from a client id
   *
   * @param string $clientId Client ID to get info for
   * @return integer Token ID
   */
  public function getTokenIdFromClientId($clientId)
  {
    $sql = "SELECT pat_pk FROM personal_access_tokens " .
      "WHERE client_id = $1;";
    $result = $this->dbManager->getSingleRow($sql, [$clientId], __METHOD__);
    if (!empty($result)) {
      return $result['pat_pk'];
    }
    return null;
  }
}