LicenseCsvImport.php

Go to the documentation of this file.

<?php
/*
 SPDX-FileCopyrightText: © 2014-2015 Siemens AG
 
 SPDX-License-Identifier: GPL-2.0-only
*/
 
namespace Fossology\Lib\Application;
 
use Fossology\Lib\BusinessRules\LicenseMap;
use Fossology\Lib\Dao\UserDao;
use Fossology\Lib\Db\DbManager;
use Fossology\Lib\Util\ArrayOperation;
use Fossology\Lib\BusinessRules\ObligationMap;
 
class LicenseCsvImport
{
  protected $dbManager;
  protected $userDao;
  protected $delimiter = ',';
  protected $enclosure = '"';
  protected $headrow = null;
  protected $nkMap = array();
  protected $mdkMap = array();
  protected $alias = array(
      'shortname'=>array('shortname','Short Name'),
      'licensetype'=>array('licensetype','license_type','License Type'),
      'fullname'=>array('fullname','Long Name'),
      'spdx_id'=>array('spdx_id', 'SPDX ID'),
      'text'=>array('text','Full Text'),
      'parent_shortname'=>array('parent_shortname','Decider Short Name'),
      'report_shortname'=>array('report_shortname','Regular License Text Short Name'),
      'url'=>array('url','URL'),
      'notes'=>array('notes'),
      'source'=>array('source','Foreign ID'),
      'risk'=>array('risk','risk_level'),
      'group'=>array('group','License group'),
      'obligations'=>array('obligations','License obligations'),
      'external_id'=>array('id','LicenseDB Id'),
      );
 
  protected $obligationMap;
 
  public function __construct(DbManager $dbManager, UserDao $userDao, $obligationMap = null)
  {
    $this->dbManager = $dbManager;
    $this->userDao = $userDao;
    $this->obligationMap = $obligationMap;
  }
  public function setDelimiter($delimiter=',')
  {
    $this->delimiter = substr($delimiter,0,1);
  }
 
  public function setEnclosure($enclosure='"')
  {
    $this->enclosure = substr($enclosure,0,1);
  }
 
  public function handleFile($filename, $fileExtension)
  {
    if (!is_file($filename) || ($handle = fopen($filename, 'r')) === false) {
      return _('Internal error');
    }
    $cnt = -1;
    $msg = '';
    try {
      if ($fileExtension == 'csv') {
        while (($row = fgetcsv($handle,0,$this->delimiter,$this->enclosure)) !== false) {
          $log = $this->handleCsv($row);
          if (!empty($log)) {
            $msg .= "$log\n";
          }
          $cnt++;
        }
        $msg .= _('Read csv').(": $cnt ")._('licenses');
      } else {
        $jsonContent = fread($handle, filesize($filename));
        $data = json_decode($jsonContent, true);
        if ($data === null && json_last_error() !== JSON_ERROR_NONE) {
          $msg .= "Error decoding JSON: " . json_last_error_msg() . "\n";
        }
        $msg = $this->importJsonData($data, $msg);
        $msg .= _('Read json').(":". count($data) ." ")._('licenses');
      }
    } catch(\Exception $e) {
      fclose($handle);
      return $msg .= _('Error while parsing file').': '.$e->getMessage();
    }
    fclose($handle);
    return $msg;
  }
 
  function handleRowJson($row)
  {
    $defaultValues = array(
      'parent_shortname' => null,
      'report_shortname' => null,
      'url' => '',
      'notes' => '',
      'source' => '',
      'risk' => 0,
      'group' => null,
      'spdx_id' => null
    );
    $newArray = array();
    foreach ($row as $key => $value) {
      $newKey = $key;
      foreach ($this->alias as $aliasKey => $aliasValues) {
        if (in_array($key, $aliasValues)) {
          $newKey = $aliasKey;
          break;
        }
      }
      $newArray[$newKey] = $value;
    }
    foreach ($defaultValues as $key => $defaultValue) {
      if (!array_key_exists($key, $newArray)) {
        $newArray[$key] = $defaultValue;
      }
    }
    return $newArray;
  }
 
  private function handleCsv($row)
  {
    if ($this->headrow === null) {
      $this->headrow = $this->handleHeadCsv($row);
      return 'head okay';
    }
 
    $mRow = array();
    foreach (array('shortname','fullname','text') as $needle) {
      $mRow[$needle] = $row[$this->headrow[$needle]];
    }
    foreach (array('parent_shortname' => null, 'report_shortname' => null,
      'url' => '', 'notes' => '', 'source' => '', 'risk' => 0,
      'group' => null, 'spdx_id' => null, 'licensetype' => null
      ) as $optNeedle=>$defaultValue) {
      $mRow[$optNeedle] = $defaultValue;
      if ($this->headrow[$optNeedle]!==false && array_key_exists($this->headrow[$optNeedle], $row)) {
        $mRow[$optNeedle] = $row[$this->headrow[$optNeedle]];
      }
    }
 
    return $this->handleCsvLicense($mRow);
  }
 
  private function handleJsonLicense($row)
  {
    $mRow = $this->handleRowJson($row);
    foreach (array('parent_shortname' => null, 'report_shortname' => null,
      'url' => '', 'notes' => '', 'source' => '', 'risk' => 0, 'active' => true,
      'group' => null, 'spdx_id' => null, 'licensetype' => null,
      'obligation_ids' => array()
      ) as $optNeedle => $defaultValue) {
      if (!array_key_exists($optNeedle, $mRow)) {
        $mRow[$optNeedle] = $defaultValue;
      }
    }
 
    if (empty($mRow['external_id'])) {
      return $this->handleCsvLicense($mRow);
    }
 
    return $this->handleLicenseDBLicenseImport($mRow);
  }
 
  private function handleHeadCsv($row)
  {
    $headrow = array();
    $row[0] = trim($row[0], "\xEF\xBB\xBF");  // Remove BOM
    foreach (array('shortname','fullname','text') as $needle) {
      $col = ArrayOperation::multiSearch($this->alias[$needle], $row);
      if (false === $col) {
        throw new \Exception("Undetermined position of $needle");
      }
      $headrow[$needle] = $col;
    }
    foreach (array('parent_shortname', 'report_shortname', 'url', 'notes',
      'source', 'risk', 'group', 'spdx_id', 'licensetype') as $optNeedle) {
      $headrow[$optNeedle] = ArrayOperation::multiSearch($this->alias[$optNeedle], $row);
    }
    return $headrow;
  }
 
  private function updateLicense($row, $rfPk)
  {
    $stmt = __METHOD__ . '.getOldLicense';
    $oldLicense = $this->dbManager->getSingleRow('SELECT ' .
      'rf_shortname, rf_fullname, rf_spdx_id, rf_text, rf_url, rf_notes, rf_source, rf_risk, rf_licensetype, rf_active ' .
      'FROM license_ref WHERE rf_pk = $1', array($rfPk), $stmt);
 
    $log = "License '$row[shortname]' already exists in DB (id = $rfPk)";
    $stmt = __METHOD__ . '.updateLicense';
    $sql = "UPDATE license_ref SET ";
    if (! empty($row['group'])) {
      $sql = "UPDATE license_candidate SET ";
    }
    $extraParams = array();
    $param = array($rfPk);
    if (isset($row['fullname']) && $row['fullname'] != $oldLicense['rf_fullname']) {
      $param[] = $row['fullname'];
      $stmt .= '.fullN';
      $extraParams[] = "rf_fullname=$" . count($param);
      $log .= ", updated fullname";
    }
    // update shortname only if import from licensedb
    if (isset($row['external_id']) && isset($row['shortname']) && $row['shortname'] !== $oldLicense['rf_shortname']) {
      $param[] = $row['shortname'];
      $stmt .= '.shortN';
      $extraParams[] = "rf_shortname=$" . count($param);
      $log .= ", updated shortname";
    }
    if (isset($row['spdx_id']) && $row['spdx_id'] != $oldLicense['rf_spdx_id']) {
      $param[] = $row['spdx_id'];
      $stmt .= '.spId';
      $extraParams[] = "rf_spdx_id=$" . count($param);
      $log .= ", updated SPDX ID";
    }
    if (isset($row['text']) && $row['text'] != $oldLicense['rf_text'] && $row['text'] != LicenseMap::TEXT_MAX_CHAR_LIMIT) {
      $param[] = $row['text'];
      $stmt .= '.text';
      $extraParams[] = "rf_text=$" . count($param) . ",rf_md5=md5($" .
        count($param) . ")";
      $log .= ", updated text";
    }
    if (isset($row['url']) && $row['url'] != $oldLicense['rf_url']) {
      $param[] = $row['url'];
      $stmt .= '.url';
      $extraParams[] = "rf_url=$" . count($param);
      $log .= ", updated URL";
    }
    if (isset($row['notes']) && $row['notes'] != $oldLicense['rf_notes']) {
      $param[] = $row['notes'];
      $stmt .= '.notes';
      $extraParams[] = "rf_notes=$" . count($param);
      $log .= ", updated notes";
    }
    if (isset($row['source']) && $row['source'] != $oldLicense['rf_source']) {
      $param[] = $row['source'];
      $stmt .= '.updSource';
      $extraParams[] = "rf_source=$".count($param);
      $log .= ', updated the source';
    }
    if (isset($row['risk']) && $row['risk'] != $oldLicense['rf_risk']) {
      $param[] = $row['risk'];
      $stmt .= '.updRisk';
      $extraParams[] = "rf_risk=$".count($param);
      $log .= ', updated the risk level';
    }
    if (isset($row['licensetype']) && $row['licensetype'] != $oldLicense['rf_licensetype']) {
      $param[] = $row['licensetype'];
      $stmt .= '.types';
      $extraParams[] = "rf_licensetype=$".count($param);
      $log .= ', updated the licensetype';
    }
    if (isset($row['active']) && $row['active'] !=
        $this->dbManager->booleanFromDb($oldLicense['rf_active'])) {
      $param[] = $this->dbManager->booleanToDb($row['active']);
      $stmt .= '.active';
      $extraParams[] = 'rf_active=$' . count($param);
      $log .= ', updated active';
    }
    if (count($param) > 1) {
      $sql .= join(",", $extraParams);
      $sql .= " WHERE rf_pk=$1;";
      $this->dbManager->getSingleRow($sql, $param, $stmt);
      if (!isset($row['external_id'])) {
        $this->mdkMap[md5($row['text'])] = $rfPk;
      }
    }
 
    // edit license maps only in case of normal import
    // In case of import from licensedb, the mapping info must come from licensedb
    if ($row['external_id'] === null) {
      $stmt = __METHOD__ . '.getOldMapping';
      $sql = 'SELECT rf_parent FROM license_map WHERE rf_fk = $1 AND usage = $2;';
      $oldParent = null;
      $oldParentRow = $this->dbManager->getSingleRow($sql, array($rfPk,
        LicenseMap::CONCLUSION), $stmt);
      if (!empty($oldParentRow)) {
        $oldParent = $oldParentRow['rf_parent'];
      }
      $oldReport = null;
      $oldReportRow = $this->dbManager->getSingleRow($sql, array($rfPk,
        LicenseMap::REPORT), $stmt);
      if (!empty($oldReportRow)) {
        $oldReport = $oldReportRow['rf_parent'];
      }
 
      $newParent = null;
      $newParent = ($row['parent_shortname'] == null) ? null :
        $this->getKeyFromShortname($row['parent_shortname']);
 
      $newReport = null;
      $newReport = ($row['report_shortname'] == null) ? null :
        $this->getKeyFromShortname($row['report_shortname']);
 
      if (($oldParent != $newParent) && $this->setMap($newParent, $rfPk, LicenseMap::CONCLUSION)) {
        $log .= " with conclusion '$row[parent_shortname]'";
      }
      if (($oldReport != $newReport) && $this->setMap($newReport, $rfPk, LicenseMap::REPORT)) {
        $log .= " reporting '$row[report_shortname]'";
      }
    }
    return $log;
  }
 
  private function handleCsvLicense($row)
  {
    if (empty($row['risk'])) {
      $row['risk'] = 0;
    }
    if (empty($row['external_id'])) {
      $row['external_id'] = null;
    }
    $rfPk = $this->getKeyFromShortname($row['shortname'], $row['group']);
    $md5Match = $this->getKeyFromMd5($row['text']);
 
    // If shortname exists, does not collide with other texts and is not
    // candidate
    if ($rfPk !== false) {
      if (! empty($row['group']) || ($md5Match == $rfPk || $md5Match === false)) {
        return $this->updateLicense($row, $rfPk);
      } else {
        return "Error: MD5 checksum of '" . $row['shortname'] .
          "' collides with license id=$md5Match";
      }
    }
    if ($md5Match !== false && empty($row['group'])) {
      return "Error: MD5 checksum of '" . $row['shortname'] .
        "' collides with license id=$md5Match";
    }
 
    $return = "";
    if (!empty($row['group'])) {
      $return = $this->insertNewLicense($row, "license_candidate");
    } else {
      $return = $this->insertNewLicense($row, "license_ref");
    }
    // insertNewLicense() returns a plain string on error (e.g. missing group)
    // or an array on success.
    if (is_string($return)) {
      return $return;
    }
    return $return['log'];
  }
 
  private function handleLicenseDBLicenseImport($row)
  {
    if (empty($row["external_id"])) {
      return "Error: external_id cannot be empty";
    }
    $stmt = __METHOD__ . ".getExistingLicense";
    $rfPk = $this->dbManager->getSingleRow("SELECT rf_pk FROM " .
      "license_ref WHERE rf_external_id=$1", array($row["external_id"]), $stmt);
    $log = '';
    if (!empty($rfPk)) {
      // update license
      $log .= $this->updateLicense($row, $rfPk['rf_pk']);
 
      $this->dbManager->begin();
      // fetch new obligation associations
      $stmt = __METHOD__ . "getNewLicenseObligations";
      $newObIds = array();
      foreach ($row['obligation_ids'] as $obExternalId) {
        $obPk = $this->dbManager->getSingleRow("SELECT ob_pk FROM obligation_ref WHERE ob_external_id = $1;",
          array($obExternalId), $stmt);
        if (!empty($obPk)) {
          $newObIds[] = $obPk['ob_pk'];
        } else {
          $log .= \sprintf('obligation with ob_external_id %s not found', $obExternalId);
        }
      }
 
      // fetch old obligation associations
      $stmt = __METHOD__ . "getOldObligationAssociations";
      $oldObIdsDB = $this->dbManager->getRows("SELECT ob_fk FROM obligation_map WHERE rf_fk = $1", array($rfPk['rf_pk']), $stmt);
      $oldObIds = array();
      foreach ($oldObIdsDB as $obId) {
        $oldObIds[] = $obId['ob_fk'];
      }
 
      // create diff of obligations between current associated obligations and licensedb
      // associated obligations
      $diff = ArrayOperation::getArrayDiffs($oldObIds, $newObIds);
 
      if (!empty($diff['remove'])) {
        $log .= ' unlinked obligation ids [' . implode(',', $diff['remove']) . ']';
      }
      if (!empty($diff['add'])) {
        $log .= ' linked obligation ids [' . implode(',', $diff['add']) . ']';
      }
 
      // delete associations
      foreach ($diff['remove'] as $obid) {
        $this->obligationMap->unassociateLicenseFromObligation($obid, $rfPk['rf_pk']);
      }
 
      // insert associations
      foreach ($diff['add'] as $obid) {
        $this->obligationMap->associateLicenseWithObligation($obid, $rfPk['rf_pk']);
      }
      $this->dbManager->commit();
    } else {
      $licRetVal = $this->insertNewLicense($row, "license_ref");
      $log .= $licRetVal['log'];
 
      $this->dbManager->begin();
      $newObIds = array();
      $stmt = __METHOD__ . "getNewLicenseObligationsForInsert";
      foreach ($row['obligation_ids'] as $obExternalId) {
        $obPk = $this->dbManager->getSingleRow("SELECT ob_pk FROM obligation_ref WHERE ob_external_id = $1;",
          array($obExternalId), $stmt);
        if (!empty($obPk)) {
          $newObIds[] = $obPk['ob_pk'];
        } else {
          $log .= \sprintf('obligation with ob_external_id %s not found', $obExternalId);
        }
      }
 
      // insert associations
      if (!empty($newObIds)) {
        $log .= ' linked obligation ids [' . implode(',', $newObIds) . ']';
      }
      foreach ($newObIds as $obid) {
        $this->obligationMap->associateLicenseWithObligation($obid, $licRetVal['pkey']);
      }
      $this->dbManager->commit();
    }
 
    return $log;
  }
 
  private function insertMapIfNontrivial($fromName,$toName,$usage)
  {
    $isNontrivial = ($fromName!==null && $fromName!=$toName && $this->getKeyFromShortname($fromName)!==false);
    if ($isNontrivial) {
      $this->dbManager->insertTableRow('license_map',
        array('rf_fk'=>$this->getKeyFromShortname($toName),
            'rf_parent'=>$this->getKeyFromShortname($fromName),
            'usage'=> $usage));
    }
    return $isNontrivial;
  }
 
  private function getKeyFromShortname($shortname, $groupFk = null)
  {
    $keyName = $shortname;
    $tableName = "license_ref";
    $addCondition = "AND rf_external_id IS NULL";
    $statement = __METHOD__ . ".getId";
    $params = array($shortname);
 
    if ($groupFk != null) {
      $keyName .= $groupFk;
      $tableName = "license_candidate";
      $addCondition = "AND group_fk = $2";
      $statement .= ".candidate";
      $params[] = $this->userDao->getGroupIdByName($groupFk);
    }
    $sql = "SELECT rf_pk FROM ONLY $tableName WHERE rf_shortname = $1 $addCondition;";
 
    if (array_key_exists($keyName, $this->nkMap)) {
      return $this->nkMap[$keyName];
    }
    $row = $this->dbManager->getSingleRow($sql, $params, $statement);
    $this->nkMap[$keyName] = ($row===false) ? false : $row['rf_pk'];
    return $this->nkMap[$keyName];
  }
 
  private function getKeyFromMd5($licenseText)
  {
    $md5 = md5($licenseText);
    if (array_key_exists($md5, $this->mdkMap)) {
      return $this->mdkMap[$md5];
    }
    $row = $this->dbManager->getSingleRow("SELECT rf_pk " .
      "FROM ONLY license_ref WHERE rf_md5=md5($1) AND rf_external_id IS NULL",
      array($licenseText));
    $this->mdkMap[$md5] = (empty($row)) ? false : $row['rf_pk'];
    return $this->mdkMap[$md5];
  }
 
  private function setMap($from, $to, $usage)
  {
    $return = false;
    if (!empty($from)) {
      $sql = "SELECT license_map_pk, rf_parent FROM license_map WHERE rf_fk = $1 AND usage = $2;";
      $statement = __METHOD__ . ".getCurrentMapping";
      $row = $this->dbManager->getSingleRow($sql, array($to, $usage), $statement);
      if (!empty($row) && $row['rf_parent'] != $from) {
        $this->dbManager->updateTableRow("license_map", array(
          'rf_fk' => $to,
          'rf_parent' => $from,
          'usage' => $usage
        ), 'license_map_pk', $row['license_map_pk']);
        $return = true;
      } elseif (empty($row)) {
        $this->dbManager->insertTableRow('license_map', array(
          'rf_fk' => $to,
          'rf_parent' => $from,
          'usage' => $usage
        ));
        $return = true;
      }
    }
    return $return;
  }
 
  private function insertNewLicense($row, $tableName = "license_ref")
  {
    $stmtInsert = __METHOD__ . '.insert.' . $tableName;
    $columns = array(
      "rf_shortname"     => $row['shortname'],
      "rf_licensetype"   => $row['licensetype'] ?: 'Unknown',
      "rf_fullname"      => $row['fullname'],
      "rf_spdx_id"       => $row['spdx_id'],
      "rf_text"          => $row['text'],
      "rf_md5"           => md5($row['text']),
      "rf_detector_type" => 1,
      "rf_url"           => $row['url'],
      "rf_notes"         => $row['notes'],
      "rf_source"        => $row['source'],
      "rf_risk"          => $row['risk'],
      "rf_external_id"   => $row['external_id'],
      "rf_active"        => $this->dbManager->booleanToDb($row['active'] ?: true),
    );
 
    $as = "";
    if ($tableName == "license_candidate") {
      $groupId = $this->userDao->getGroupIdByName($row['group']);
      if (empty($groupId)) {
        return "Error: Unable to insert candidate license " . $row['shortname'] .
          " as group " . $row['group'] . " does not exist";
      }
      $columns["group_fk"] = $groupId;
      $columns["marydone"] = $this->dbManager->booleanToDb(true);
      $as = " as candidate license under group " . $row["group"];
    }
 
    $newPk = $this->dbManager->insertTableRow($tableName, $columns, $stmtInsert, 'rf_pk');
 
    // populate license maps and cache only when the import is not from licensedb
    $log = "Inserted '$row[shortname]' in DB" . $as;
    if ($row['external_id'] === null) {
      if ($tableName == "license_candidate") {
        $this->nkMap[$row['shortname'].$row['group']] = $newPk;
      } else {
        $this->nkMap[$row['shortname']] = $newPk;
      }
      $this->mdkMap[md5($row['text'])] = $newPk;
 
      if ($this->insertMapIfNontrivial($row['parent_shortname'], $row['shortname'], LicenseMap::CONCLUSION)) {
        $log .= " with conclusion '$row[parent_shortname]'";
      }
      if ($this->insertMapIfNontrivial($row['report_shortname'], $row['shortname'], LicenseMap::REPORT)) {
        $log .= " reporting '$row[report_shortname]'";
      }
    }
 
    $return = array();
    $return['log'] = $log;
    $return['pkey'] = $newPk;
    return $return;
  }
 
  public function importJsonData($data, string $msg): string
  {
    foreach ($data as $row) {
      $log = $this->handleJsonLicense($row);
      if (!empty($log)) {
        $msg .= "$log\n";
      }
    }
    return $msg;
  }
}