fossinit.php

Go to the documentation of this file.

#!/usr/bin/php
<?php
/*
 SPDX-FileCopyrightText: © 2008-2015 Hewlett-Packard Development Company, L.P.
 SPDX-FileCopyrightText: © 2014-2015, 2019 Siemens AG
 
 SPDX-License-Identifier: GPL-2.0-only
*/
 
function explainUsage()
{
  global $argv;
 
  $usage = "Usage: " . basename($argv[0]) . " [options]
  Update FOSSology database. This should be used immediately after an install or update. Options are:
  -c  path to fossology configuration files
  -d  {database name} default is 'fossology'
  -f  {file} update the schema with file generated by schema-export.php
  -l  update the license_ref table with fossology supplied licenses
  -r  {prefix} drop database with name starts with prefix
  -v  enable verbose preview (prints sql that would happen, but does not execute it, DB is not updated)
  --force-decision force recalculation of SHA256 for decision tables
  --force-pfile    force recalculation of SHA256 for pfile entries
  --force-encode   force recode of copyright and sister tables
  -h  this help usage";
  print "$usage\n";
  exit(0);
}
 
 
use Fossology\Lib\Db\DbManager;
use Fossology\Lib\Db\Driver\Postgres;
 
/* Note: php 5 getopt() ignores options not specified in the function call, so add
 * dummy options in order to catch invalid options.
 */
$AllPossibleOpts = "abc:d:ef:ghijklmnopqr:stuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
$longOpts = [
  "force-decision",
  "force-pfile",
  "force-encode"
];
 
/* defaults */
$Verbose = false;
$DatabaseName = "fossology";
$UpdateLiceneseRef = false;
$sysconfdir = '';
$delDbPattern = 'the option -rfosstest will drop data bases with datname like "fosstest%"';
$forceDecision = false;
$forcePfile = false;
 
/* command-line options */
$Options = getopt($AllPossibleOpts, $longOpts);
foreach($Options as $optKey => $optVal)
{
  switch($optKey)
  {
    case 'c': /* set SYSCONFIDR */
      $sysconfdir = $optVal;
      break;
    case 'd': /* optional database name */
      $DatabaseName = $optVal;
      break;
    case 'f': /* schema file */
      $SchemaFilePath = $optVal;
      break;
    case 'h': /* help */
      explainUsage();
    case 'l': /* update the license_ref table */
      $UpdateLiceneseRef = true;
      break;
    case 'v': /* verbose */
      $Verbose = true;
      break;
    case 'r':
      $delDbPattern = $optVal ? "$optVal%" : "fosstest%";
      break;
    case "force-decision":
      $forceDecision = true;
      break;
    case "force-pfile":
      $forcePfile = true;
      break;
    case "force-encode":
      putenv('FOSSENCODING=1');
      break;
    default:
      echo "Invalid Option \"$optKey\".\n";
      explainUsage();
  }
}
 
require_once 'fossinit-common.php';
 
/* Set SYSCONFDIR and set global (for backward compatibility) */
$SysConf = bootstrap($sysconfdir);
$SysConf["DBCONF"]["dbname"] = $DatabaseName;
$GLOBALS["SysConf"] = array_merge($GLOBALS["SysConf"], $SysConf);
$projectGroup = $SysConf['DIRECTORIES']['PROJECTGROUP'] ?: 'fossy';
$gInfo = posix_getgrnam($projectGroup);
posix_setgid($gInfo['gid']);
$groups = `groups`;
if (!preg_match("/\s$projectGroup\s/",$groups) && (posix_getgid() != $gInfo['gid']))
{
  print "FATAL: You must be in group '$projectGroup'.\n";
  exit(1);
}
 
require_once("$MODDIR/vendor/autoload.php");
require_once("$MODDIR/lib/php/common-db.php");
require_once("$MODDIR/lib/php/common-container.php");
require_once("$MODDIR/lib/php/common-cache.php");
require_once("$MODDIR/lib/php/common-sysconfig.php");
require_once("$MODDIR/lib/php/fossdash-config.php");
 
/* Initialize global system configuration variables $SysConfig[] */
$GLOBALS["PG_CONN"] = get_pg_conn($SYSCONFDIR, $SysConf);
 
/* Initialize fossdash configuration variables */
FossdashConfigInit($SYSCONFDIR, $SysConf);
 
if (empty($SchemaFilePath)) {
  $SchemaFilePath = "$MODDIR/www/ui/core-schema.dat";
}
 
if (!file_exists($SchemaFilePath))
{
  print "FAILED: Schema data file ($SchemaFilePath) not found.\n";
  exit(1);
}
 
require_once("$MODDIR/lib/php/libschema.php");
$pgDriver = new Postgres($PG_CONN);
$libschema->setDriver($pgDriver);
$previousSchema = $libschema->getCurrSchema();
$isUpdating = array_key_exists('TABLE', $previousSchema) && array_key_exists('users', $previousSchema['TABLE']);
if ($dbManager->existsTable('sysconfig'))
{
  $sysconfig = $dbManager->createMap('sysconfig', 'variablename', 'conf_value');
  if(!array_key_exists('Release', $sysconfig))
  {
    $sysconfig['Release'] = 0;
  }
  print "Old release was $sysconfig[Release]\n";
}
 
$migrateColumns = array('clearing_decision'=>array('reportinfo','clearing_pk','type_fk','comment'),
        'license_ref_bulk'=>array('rf_fk','removing'));
if($isUpdating && !empty($sysconfig) && $sysconfig['Release'] == '2.6.3.1')
{
  $dbManager->queryOnce('begin;
    CREATE TABLE uploadtree_b AS (SELECT * FROM uploadtree_a);
    DROP TABLE uploadtree_a;
    CREATE TABLE uploadtree_a () INHERITS (uploadtree);
    ALTER TABLE uploadtree_a ADD CONSTRAINT uploadtree_a_pkey PRIMARY KEY (uploadtree_pk);
    INSERT INTO uploadtree_a SELECT * FROM uploadtree_b;
    DROP TABLE uploadtree_b;
    COMMIT;',__FILE__.'.rebuild.uploadtree_a');
}
 
if($dbManager->existsTable("author"))
{
  require_once("$LIBEXECDIR/resequence_author_table.php"); // If table exists, clean up for Schema
}
 
// Migration script to clear tables for new constraints
require_once("$LIBEXECDIR/dbmigrate_3.3-3.4.php");
Migrate_33_34($dbManager, $Verbose);
 
$FailMsg = $libschema->applySchema($SchemaFilePath, $Verbose, $DatabaseName, $migrateColumns);
if ($FailMsg)
{
  print "ApplySchema failed: $FailMsg\n";
  exit(1);
}
 
// Populate sysconfig table
Populate_sysconfig();
populate_from_sysconfig($PG_CONN, $SysConf);
 
$Filename = "$MODDIR/www/ui/init.ui";
$flagRemoved = !file_exists($Filename);
if (!$flagRemoved)
{
  if ($Verbose)
  {
    print "Removing flag '$Filename'\n";
  }
  if (is_writable("$MODDIR/www/ui/"))
  {
    $flagRemoved = unlink($Filename);
  }
}
if (!$flagRemoved)
{
  print "Failed to remove $Filename\n";
  print "Remove this file to complete the initialization.\n";
}
else
{
  print "Database schema update completed successfully.\n";
}
 
$FAILED_LICENSE_IMPORT = array();
 
if (array_key_exists('r', $Options))
{
  $dbManager->prepare(__METHOD__.".getDelDbNames",'SELECT datname FROM pg_database WHERE datistemplate = false and datname like $1');
  $resDelDbNames = $dbManager->execute(__METHOD__.".getDelDbNames",array($delDbPattern));
  $delDbNames=pg_fetch_all($resDelDbNames);
  pg_free_result($resDelDbNames);
  foreach ($delDbNames as $deleteDatabaseName)
  {
    $dbManager->queryOnce("DROP DATABASE $deleteDatabaseName[datname]");
  }
  if ($Verbose)
  {
    echo "dropped " . count($delDbNames) . " databases ";
  }
}
 
/* migration */
$currSchema = $libschema->getCurrSchema();
$sysconfig = $dbManager->createMap('sysconfig','variablename','conf_value');
global $LIBEXECDIR;
if($isUpdating && empty($sysconfig['Release'])) {
  require_once("$LIBEXECDIR/dbmigrate_2.0-2.1.php");  // this is needed for all new installs from 2.0 on
  Migrate_20_21($Verbose);
  require_once("$LIBEXECDIR/dbmigrate_2.1-2.2.php");
  print "Migrate data from 2.1 to 2.2 in $LIBEXECDIR\n";
  Migrate_21_22($Verbose);
  if($dbManager->existsTable('license_file_audit') && array_key_exists('clearing_pk', $currSchema['TABLE']['clearing_decision']))
  {
    require_once("$LIBEXECDIR/dbmigrate_2.5-2.6.php");
    migrate_25_26($Verbose);
  }
  if(!array_key_exists('clearing_pk', $currSchema['TABLE']['clearing_decision']) && $isUpdating)
  {
    $timeoutSec = 20;
    echo "Missing column clearing_decision.clearing_pk, you should update to version 2.6.2 before migration\n";
    echo "Enter 'i' within $timeoutSec seconds to ignore this warning and run the risk of losing clearing decisions: ";
    $handle = fopen ("php://stdin","r");
    stream_set_blocking($handle,0);
    for($s=0;$s<$timeoutSec;$s++)
    {
      sleep(1);
      $line = fread($handle,1);
      if ($line) {
        break;
      }
    }
    if(trim($line) != 'i')
    {
     echo "ABORTING!\n";
     exit(26);
    }
  }
  $sysconfig['Release'] = '2.6';
}
if (! $isUpdating) {
  require_once ("$LIBEXECDIR/dbmigrate_2.1-2.2.php");
  print "Creating default user\n";
  Migrate_21_22($Verbose);
} else {
  require_once ("$LIBEXECDIR/dbmigrate_3.5-3.6.php");
  migrate_35_36($dbManager, $forceDecision);
  updatePfileSha256($dbManager, $forcePfile);
}
 
$release = array_key_exists('Release', $sysconfig) ? $sysconfig['Release'] : 0;
if (version_compare($release, '4.3.0', '<')) {
  require_once("$LIBEXECDIR/dbmigrate_4.2-4.3.php");
  Migrate_42_43($Verbose);
}
 
require_once("$LIBEXECDIR/dbmigrate_cmu-mach.php");
Migrate_Cmu_Mach($dbManager, $Verbose);
 
require_once("$LIBEXECDIR/dbmigrate_legacy-license-names.php");
Migrate_Legacy_License_Names($dbManager, $Verbose);
 
/* initialize the license_ref table */
if ($UpdateLiceneseRef)
{
  if (version_compare($release, '4.8.0', '<')) {
    /* Normalize legacy all-caps values before per-license update runs. */
    $dbManager->queryOnce(
      "UPDATE license_ref SET rf_licensetype='Unknown'
         WHERE rf_licensetype IN ('UNKNOWN', 'PERMISSIVE')",
      'migrate.rf_licensetype.case');
  }
  $row = $dbManager->getSingleRow("SELECT count(*) FROM license_ref",array(),'license_ref.count');
  if ($row['count'] >  0) {
    print "Update reference licenses\n";
    initLicenseRefTable(false);
  }
  else if ($row['count'] ==  0) {
    insertInToLicenseRefTableUsingJson('license_ref');
 
    $row_max = $dbManager->getSingleRow("SELECT max(rf_pk) from license_ref",array(),'license_ref.max.rf_pk');
    $current_license_ref_rf_pk_seq = $row_max['max'];
    $dbManager->getSingleRow("SELECT setval('license_ref_rf_pk_seq', $current_license_ref_rf_pk_seq)",array(),
      'set next license_ref_rf_pk_seq value');
 
    print "fresh install, import licenseRef.json \n";
  }
}
 
if(!$isUpdating || $sysconfig['Release'] == '2.6')
{
  if(!$dbManager->existsTable('license_candidate'))
  {
    $dbManager->queryOnce("CREATE TABLE license_candidate (group_fk integer) INHERITS (license_ref)");
  }
  if ($isUpdating && array_key_exists('clearing_pk', $currSchema['TABLE']['clearing_decision']))
  {
    require_once("$LIBEXECDIR/dbmigrate_clearing-event.php");
    $libschema->dropColumnsFromTable(array('reportinfo','clearing_pk','type_fk','comment'), 'clearing_decision');
  }
  $sysconfig['Release'] = '2.6.3';
}
 
if($sysconfig['Release'] == '2.6.3')
{
  require_once("$LIBEXECDIR/dbmigrate_real-parent.php");
}
 
$expiredDbReleases = array('2.6.3', '2.6.3.1', '2.6.3.2');
if($isUpdating && (empty($sysconfig['Release']) || in_array($sysconfig['Release'], $expiredDbReleases)))
{
  require_once("$LIBEXECDIR/fo_mapping_license.php");
  print "Rename license (using $LIBEXECDIR) for SPDX validity\n";
  renameLicensesForSpdxValidation($Verbose);
}
 
$expiredDbReleases[] = '2.6.3.3';
$expiredDbReleases[] = '3.0.0';
if($isUpdating && (empty($sysconfig['Release']) || in_array($sysconfig['Release'], $expiredDbReleases)))
{
  require_once("$LIBEXECDIR/dbmigrate_bulk_license.php");
}
 
if(in_array($sysconfig['Release'], $expiredDbReleases))
{
  $sysconfig['Release'] = '3.0.1';
}
 
// Update '3dfx' licence shortname to 'Glide'. Since shortname is used as an
// identifier, this is not done as part of the licenseref updates.
if($isUpdating && (empty($sysconfig['Release']) || $sysconfig['Release'] == '3.0.1'))
{
  $dbManager->begin();
  $row = $dbManager->getSingleRow("
    SELECT rf1.rf_pk AS id_3dfx,
           rf2.rf_pk AS id_glide
    FROM license_ref rf1
      INNER JOIN license_ref rf2 USING (rf_fullname)
    WHERE rf1.rf_shortname='3DFX'
      AND rf2.rf_shortname='Glide'
    LIMIT 1", array(), 'old.3dfx.rf_pk');
  if (!empty($row))
  {
    $id_3dfx = intval($row['id_3dfx']);
    $id_glide = intval($row['id_glide']);
    $dbManager->queryOnce("DELETE FROM license_ref WHERE rf_pk=$id_glide");
    $dbManager->queryOnce("UPDATE license_ref SET rf_shortname='Glide' WHERE rf_pk=$id_3dfx");
  }
  $dbManager->commit();
 
  $sysconfig['Release'] = "3.0.2";
}
 
if($isUpdating && (empty($sysconfig['Release']) || $sysconfig['Release'] == '3.0.2'))
{
  require_once("$LIBEXECDIR/dbmigrate_multiple_copyright_decisions.php");
 
  $sysconfig['Release'] = "3.1.0";
}
 
// fix release-version datamodel-version missmatch
if($isUpdating && (empty($sysconfig['Release']) || $sysconfig['Release'] == "3.1.0")) {
  $sysconfig['Release'] = "3.3.0";
}
 
// Handle idempotency of this script by checking if the Release variable is set
$new_version = $SysConf["BUILD"]["VERSION"] ?? '';
if ($new_version !==''){
  $dbManager->begin();
  $dbManager->getSingleRow("DELETE FROM sysconfig WHERE variablename=$1",array('Release'),'drop.sysconfig.release');
  $dbManager->insertTableRow('sysconfig',
    array('variablename'=>'Release','conf_value'=>$SysConf["BUILD"]["VERSION"],
    'ui_label'=>'Release','vartype'=>2,'group_name'=>'Release','description'=>'')
  );
  $dbManager->commit();
}
 
/* email/url/author data migration to other table */
require_once("$LIBEXECDIR/dbmigrate_copyright-author.php");
 
// Migration script to move candidate licenses in obligations
require_once("$LIBEXECDIR/dbmigrate_3.6-3.7.php");
Migrate_36_37($dbManager, $Verbose);
 
/* instance uuid */
require_once("$LIBEXECDIR/instance_uuid.php");
 
// Migration script for 3.7 => 3.8
require_once("$LIBEXECDIR/dbmigrate_3.7-3.8.php");
Migrate_37_38($dbManager, $MODDIR);
 
// Migration script for 4.0 => 4.1
require_once("$LIBEXECDIR/dbmigrate_4.0-4.1.php");
Migrate_40_41($dbManager);
 
// Migration script for copyright_event table
require_once("$LIBEXECDIR/dbmigrate_copyright-event.php");
createCopyrightMigrationForCopyrightEvents($dbManager);
 
/* sanity check */
require_once ("$LIBEXECDIR/sanity_check.php");
$checker = new SanityChecker($dbManager,$Verbose);
$errors = $checker->check();
 
require_once("$LIBEXECDIR/dbmigrate_licensedb_compatibility.php");
LicenseDB_compatibility_migration();
 
require_once("$LIBEXECDIR/dbmigrate_token.php");
Token_timestamp_migration();
 
require_once("$LIBEXECDIR/dbmigrate_kotoba_candidate_licenses.php");
Kotoba_candidate_license_migration();
 
if($errors>0)
{
  echo "ERROR: $errors sanity check".($errors>1?'s':'')." failed\n";
}
if (! empty($FAILED_LICENSE_IMPORT)) {
  $failedInsert = array_filter($FAILED_LICENSE_IMPORT,
    function ($x){
      return $x[1] == "INSERT";
    });
  $failedUpdate = array_filter($FAILED_LICENSE_IMPORT,
    function ($x){
      return $x[1] == "UPDATE";
    });
  $failedPromote = array_filter($FAILED_LICENSE_IMPORT,
    function ($x){
      return $x[1] == "CANPROMOTE";
    });
  if (! empty($failedInsert)) {
    echo "*** Failed to insert following licenses ***\n";
    echo implode(",", array_map(function ($x) {
      return $x[0];
    }, $failedInsert)) . "\n";
  }
  if (! empty($failedUpdate)) {
    echo "*** Failed to update following licenses ***\n";
    echo implode(",", array_map(function ($x) {
      return $x[0];
    }, $failedUpdate)) . "\n";
  }
  if (! empty($failedPromote)) {
    echo "*** Failed to move following licenses from candidate table ***\n";
    echo implode(",", array_map(function ($x) {
      return $x[0];
    }, $failedPromote)) . "\n";
  }
}
updateLicenseTypesConfig();
exit($errors);
 
function updateLicenseTypesConfig()
{
  global $dbManager;
 
  $rows = $dbManager->getRows(
    "SELECT DISTINCT rf_licensetype FROM license_ref
     WHERE rf_licensetype IS NOT NULL AND rf_licensetype <> ''
     ORDER BY rf_licensetype",
    array(), 'updateLicenseTypesConfig.fetchTypes');
 
  if (empty($rows)) {
    return;
  }
  $dbTypes = array();
  foreach ($rows as $row) {
    $dbTypes[$row['rf_licensetype']] = true;
  }
 
  $confRow = $dbManager->getSingleRow(
    "SELECT conf_value FROM sysconfig WHERE variablename=$1",
    array('LicenseTypes'), 'updateLicenseTypesConfig.fetchConf');
 
  if ($confRow === false || $confRow === null) {
    return;
  }
 
  $existingTypes = array();
  $existingTypesLower = array();
  if (!empty($confRow['conf_value'])) {
    foreach (explode(',', $confRow['conf_value']) as $t) {
      $trimmed = trim($t);
      if ($trimmed !== '') {
        $existingTypes[$trimmed] = true;
        $existingTypesLower[strtolower($trimmed)] = true;
      }
    }
  }
 
  $newTypes = array();
  foreach ($dbTypes as $type => $_) {
    if (!isset($existingTypesLower[strtolower($type)])) {
      $newTypes[] = $type;
    }
  }
  if (empty($newTypes)) {
    return;
  }
 
  $merged = array_merge(array_keys($existingTypes), $newTypes);
  $newValue = implode(', ', $merged);
 
  $dbManager->getRows(
    "UPDATE sysconfig SET conf_value=$1 WHERE variablename=$2",
    array($newValue, 'LicenseTypes'), 'updateLicenseTypesConfig.update');
 
  print "LicenseTypes updated: $newValue\n";
}
 
function insertInToLicenseRefTableUsingJson($tableName)
{
  global $LIBEXECDIR;
  global $dbManager;
 
  if (!is_dir($LIBEXECDIR)) {
    print "FATAL: Directory '$LIBEXECDIR' does not exist.\n";
    return (1);
  }
 
  if (!is_readable($LIBEXECDIR)) {
    print "FATAL: Unable to access '$LIBEXECDIR'.\n";
    return (1);
  }
  $dbManager->begin();
  if ($tableName === 'license_ref_2') {
    $dbManager->queryOnce("DROP TABLE IF EXISTS license_ref_2",
      __METHOD__.'.dropAncientBackUp');
    $dbManager->queryOnce("CREATE TABLE license_ref_2 (LIKE license_ref INCLUDING DEFAULTS)",
      __METHOD__.'.backUpData');
  }
  $keysToBeChanged = array(
    'rf_OSIapproved' => '"rf_OSIapproved"',
    'rf_FSFfree'=> '"rf_FSFfree"',
    'rf_GPLv2compatible' => '"rf_GPLv2compatible"',
    'rf_GPLv3compatible'=> '"rf_GPLv3compatible"',
    'rf_Fedora' => '"rf_Fedora"'
    );
  $keysToReplicate = [
    "rf_spdx_id" => "rf_shortname",
  ];
  $keysToRemove = [
    "rf_spdx_compatible"
  ];
 
  $jsonData = json_decode(file_get_contents("$LIBEXECDIR/licenseRef.json"), true);
  foreach($jsonData as $licenseArray) {
    foreach ($keysToReplicate as $duplicateKey => $originalKey) {
      if ($licenseArray['rf_spdx_compatible'] == 't') {
        $licenseArray[$duplicateKey] = $licenseArray[$originalKey];
      } else {
        $licenseArray[$duplicateKey] = "";
      }
    }
    foreach ($keysToRemove as $key) {
      unset($licenseArray[$key]);
    }
    ksort($licenseArray);
    $arrayKeys = array_keys($licenseArray);
    $arrayValues = array_values($licenseArray);
    $keys = strtr(implode(",", $arrayKeys), $keysToBeChanged);
    $statementName = __METHOD__ . '.insertInTo' . $tableName . '.' . md5($tableName . $keys);
    $valuePlaceHolders = "$" . join(",$",range(1, count($arrayKeys)));
    $md5PlaceHolder = "$". (count($arrayKeys) + 1);
    $arrayValues[] = $licenseArray['rf_text'];
    $SQL = "INSERT INTO $tableName ( $keys,rf_md5 ) " .
      "VALUES ($valuePlaceHolders,md5($md5PlaceHolder));";
    $dbManager->prepare($statementName, $SQL);
    $dbManager->execute($statementName, $arrayValues);
  }
  $dbManager->commit();
  return (0);
}
 
function initLicenseRefTable($Verbose)
{
  global $dbManager;
 
  $dbManager->begin();
  insertInToLicenseRefTableUsingJson('license_ref_2');
  $dbManager->prepare(__METHOD__.".newLic", "SELECT * FROM license_ref_2");
  $result_new = $dbManager->execute(__METHOD__.".newLic");
 
  $dbManager->prepare(__METHOD__.'.licenseRefByShortname',
    'SELECT *,md5(rf_text) AS hash FROM license_ref WHERE rf_shortname=$1');
 
  while ($row = pg_fetch_assoc($result_new))
  {
    $rf_shortname = $row['rf_shortname'];
    $result_check = $dbManager->execute(__METHOD__.'.licenseRefByShortname', array($rf_shortname));
    $count = pg_num_rows($result_check);
 
    $rf_text = $row['rf_text'];
    $rf_url = $row['rf_url'];
    $rf_fullname = $row['rf_fullname'];
    $rf_notes = $row['rf_notes'];
    $rf_active = $row['rf_active'];
    $marydone = $row['marydone'];
    $rf_text_updatable = $row['rf_text_updatable'];
    $rf_detector_type = $row['rf_detector_type'];
    $rf_flag = $row['rf_flag'];
    $rf_spdx_id = $row['rf_spdx_id'];
    $rf_licensetype = $row['rf_licensetype'];
 
    if ($count) // update when it is existing
    {
      $row_check = pg_fetch_assoc($result_check);
      pg_free_result($result_check);
      $params = array();
      $rf_text_check = $row_check['rf_text'];
      $rf_url_check = $row_check['rf_url'];
      $rf_fullname_check = $row_check['rf_fullname'];
      $rf_notes_check = $row_check['rf_notes'];
      $rf_active_check = $row_check['rf_active'];
      $marydone_check = $row_check['marydone'];
      $rf_text_updatable_check = $row_check['rf_text_updatable'];
      $rf_detector_type_check = $row_check['rf_detector_type'];
      $rf_flag_check = $row_check['rf_flag'];
      $rf_spdx_id_check = $row_check['rf_spdx_id'];
      $rf_licensetype_check = $row_check['rf_licensetype'];
 
      $candidateLicense = isLicenseExists($dbManager, $rf_shortname, true);
      if ($candidateLicense) {
        mergeCandidateLicense($dbManager, $candidateLicense);
      }
 
      $statement = __METHOD__ . ".updateLicenseRef";
      $sql = "UPDATE license_ref SET ";
      if (($rf_flag_check == 1 && $rf_flag == 1) &&
          ($rf_text_check != $rf_text && !empty($rf_text) &&
          !(stristr($rf_text, 'License by Nomos')) &&
          !(stristr($rf_text, 'License by OJO')))) {
        $params[] = $rf_text;
        $position = "$" . count($params);
        $sql .= "rf_text=$position,rf_md5=md5($position),rf_flag=1,";
        $statement .= ".insertT";
      }
      if ($rf_url_check != $rf_url && !empty($rf_url)) {
        $params[] = $rf_url;
        $position = "$" . count($params);
        $sql .= "rf_url=$position,";
        $statement .= ".url";
      }
      if ($rf_fullname_check != $rf_fullname && !empty($rf_fullname)) {
        $params[] = $rf_fullname;
        $position = "$" . count($params);
        $sql .= "rf_fullname=$position,";
        $statement .= ".name";
      }
      if ($rf_notes_check != $rf_notes && !empty($rf_notes)) {
        $params[] = $rf_notes;
        $position = "$" . count($params);
        $sql .= "rf_notes=$position,";
        $statement .= ".notes";
      }
      if ($rf_active_check != $rf_active && !empty($rf_active)) {
        $params[] = $rf_active;
        $position = "$" . count($params);
        $sql .= "rf_active=$position,";
        $statement .= ".active";
      }
      if ($marydone_check != $marydone && !empty($marydone)) {
        $params[] = $marydone;
        $position = "$" . count($params);
        $sql .= "marydone=$position,";
        $statement .= ".marydone";
      }
      if ($rf_text_updatable_check != $rf_text_updatable && !empty($rf_text_updatable)) {
        $params[] = $rf_text_updatable;
        $position = "$" . count($params);
        $sql .= "rf_text_updatable=$position,";
        $statement .= ".tUpdate";
      }
      if ($rf_detector_type_check != $rf_detector_type && !empty($rf_detector_type)) {
        $params[] = $rf_detector_type;
        $position = "$" . count($params);
        $sql .= "rf_detector_type=$position,";
        $statement .= ".dType";
      }
      if (empty($rf_spdx_id_check) && !empty($rf_spdx_id) &&
          $rf_spdx_id_check != $rf_spdx_id) {
        $params[] = $rf_spdx_id;
        $position = "$" . count($params);
        $sql .= "rf_spdx_id=$position,";
        $statement .= ".spdxId";
      }
      if (!empty($rf_licensetype) && $rf_licensetype_check != $rf_licensetype) {
        $params[] = $rf_licensetype;
        $position = "$" . count($params);
        $sql .= "rf_licensetype=$position,";
        $statement .= ".licType";
      }
      $sql = substr_replace($sql, "", -1);
 
      if ($sql != "UPDATE license_ref SET") { // check if we have something to update
        $params[] = $rf_shortname;
        $position = "$" . count($params);
        $sql .= " WHERE rf_shortname=$position;";
        try {
          $dbManager->getSingleRow($sql, $params, $statement);
        } catch (\Exception $e) {
          global $FAILED_LICENSE_IMPORT;
          $FAILED_LICENSE_IMPORT[] = array($rf_shortname, "UPDATE");
        }
      }
    } else {  // insert when it is new
      pg_free_result($result_check);
      $params = array();
      $params['rf_shortname'] = $rf_shortname;
      $params['rf_text'] = $rf_text;
      $params['rf_url'] = $rf_url;
      $params['rf_fullname'] = $rf_fullname;
      $params['rf_notes'] = $rf_notes;
      $params['rf_active'] = $rf_active;
      $params['rf_text_updatable'] = $rf_text_updatable;
      $params['rf_detector_type'] = $rf_detector_type;
      $params['marydone'] = $marydone;
      $params['rf_spdx_id'] = $rf_spdx_id;
      $params['rf_licensetype'] = $rf_licensetype;
      insertNewLicense($dbManager, $params);
    }
  }
  pg_free_result($result_new);
 
  $dbManager->queryOnce("DROP TABLE IF EXISTS license_ref_2");
  $dbManager->commit();
 
  return (0);
} // initLicenseRefTable()
 
function isLicenseExists($dbManager, $rf_shortname, $isCandidate = true)
{
  $tableName = "license_ref";
  if ($isCandidate) {
    $tableName = "license_candidate";
  }
  $statement = __METHOD__ . ".$tableName";
  $sql = "SELECT * FROM ONLY $tableName WHERE rf_shortname = $1;";
  $licenseRow = $dbManager->getSingleRow($sql, array($rf_shortname),
    $statement);
  if (! empty($licenseRow)) {
    return $licenseRow;
  } else {
    return false;
  }
}
 
function mergeCandidateLicense($dbManager, $candidateLicense)
{
  $mainLicense = isLicenseExists($dbManager, $candidateLicense['rf_shortname'],
    false);
  $statement = __METHOD__ . ".md5Exists";
  $sql = "SELECT rf_pk FROM ONLY license_ref WHERE md5(rf_text) = md5($1);";
  $licenseRow = $dbManager->getSingleRow($sql,
    array($candidateLicense['rf_text']), $statement);
  if (! empty($licenseRow)) {
    $md5Exists = true;
  } else {
    $md5Exists = false;
  }
  if ($mainLicense !== false && $md5Exists) {
    $dbManager->begin();
    $updateStatements = __METHOD__ . ".updateCandidateToMain";
    $updateCeSql = "UPDATE clearing_event SET rf_fk = $1 WHERE rf_fk = $2;";
    $updateCeSt = $updateStatements . ".ce";
    $updateLsbSql = "UPDATE license_set_bulk SET rf_fk = $1 WHERE rf_fk = $2;";
    $updateLsbSt = $updateStatements . ".lsb";
    $updateUclSql = "UPDATE upload_clearing_license SET rf_fk = $1 " .
      "WHERE rf_fk = $2;";
    $updateUclSt = $updateStatements . ".ucl";
    $deleteOcmSql = "DELETE FROM obligation_candidate_map WHERE rf_fk = $1;";
    $deleteOcmSt = $updateStatements . ".ocm";
 
    $dbManager->getSingleRow($updateCeSql,
      array($mainLicense['rf_pk'], $candidateLicense['rf_pk']), $updateCeSt);
    $dbManager->getSingleRow($updateLsbSql,
      array($mainLicense['rf_pk'], $candidateLicense['rf_pk']), $updateLsbSt);
    $dbManager->getSingleRow($updateUclSql,
      array($mainLicense['rf_pk'], $candidateLicense['rf_pk']), $updateUclSt);
    $dbManager->getSingleRow($deleteOcmSql, array($candidateLicense['rf_pk']),
      $deleteOcmSt);
    $dbManager->commit();
  } elseif ($mainLicense !== false || $md5Exists) {
    // Short name exists and MD5 does not match
    // Or short name does not exists by MD5 match
    return -1;
  }
  $dbManager->begin();
  $deleteSql = "DELETE FROM license_candidate WHERE rf_pk = $1;";
  $deleteStatement = __METHOD__ . ".deleteCandidte";
  $dbManager->prepare($deleteStatement, $deleteSql);
  $dbManager->execute($deleteStatement, array($candidateLicense['rf_pk']));
  if ($mainLicense === false && $md5Exists === false) {
    // License does not exists
    insertNewLicense($dbManager, $candidateLicense, true);
  }
  $dbManager->commit();
}
 
function insertNewLicense($dbManager, $license, $wasCandidate = false)
{
  $insertStatement = __METHOD__ . ".insertNewLicense";
  $sql = "INSERT INTO license_ref (";
  if ($wasCandidate) {
    $sql .= "rf_pk, ";
    $insertStatement .= ".wasCandidate";
  }
  $sql .= "rf_shortname, rf_text, rf_url, rf_fullname, rf_notes, rf_active, " .
    "rf_text_updatable, rf_detector_type, marydone, rf_spdx_id, rf_licensetype, " .
    "rf_md5, rf_add_date) VALUES (";
  $params = array();
  if ($wasCandidate) {
    $params[] = $license['rf_pk'];
  }
  $params[] = $license['rf_shortname'];
  $params[] = $license['rf_text'];
  $params[] = $license['rf_url'];
  $params[] = $license['rf_fullname'];
  $params[] = $license['rf_notes'];
  $params[] = $license['rf_active'];
  $params[] = $license['rf_text_updatable'];
  $params[] = $license['rf_detector_type'];
  $params[] = $license['marydone'];
  $params[] = $license['rf_spdx_id'];
  $params[] = !empty($license['rf_licensetype']) ? $license['rf_licensetype'] : 'Unknown';
 
  for ($i = 1; $i <= count($params); $i++) {
    $sql .= "$" . $i . ",";
  }
 
  $params[] = $license['rf_text'];
  $textPos = "$" . count($params);
 
  $sql .= "md5($textPos),now())";
  $rfPk = -1;
  try {
    $rfPk = $dbManager->insertPreparedAndReturn($insertStatement, $sql, $params,
      "rf_pk");
  } catch (\Exception $e) {
    global $FAILED_LICENSE_IMPORT;
    $type = "INSERT";
    if ($wasCandidate) {
      $type = "CANPROMOTE";
    }
    $FAILED_LICENSE_IMPORT[] = array($license['rf_shortname'], $type);
  }
  return $rfPk;
}