1#!/usr/bin/php
2<?php
3/***********************************************************
4 Copyright (C) 2008-2015 Hewlett-Packard Development Company, L.P.
5 Copyright (C) 2014-2015,2019 Siemens AG
6
7 This program is free software; you can redistribute it and/or
8 modify it under the terms of the GNU General Public License
9 version 2 as published by the Free Software Foundation.
10
11 This program is distributed in the hope that it will be useful,
12 but WITHOUT ANY WARRANTY; without even the implied warranty of
13 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14 GNU General Public License for more details.
15
16 You should have received a copy of the GNU General Public License along
17 with this program; if not, write to the Free Software Foundation, Inc.,
18 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
19 ***********************************************************/
20
21/** \brief Print Usage statement.
22 *  \return No return, this calls exit.
23 **/
24function explainUsage()
25{
26  global $argv;
27
28  $usage = "Usage: " . basename($argv[0]) . " [options]
29  Update FOSSology database. This should be used immediately after an install or update. Options are:
30  -c  path to fossology configuration files
31  -d  {database name} default is 'fossology'
32  -f  {file} update the schema with file generated by schema-export.php
33  -l  update the license_ref table with fossology supplied licenses
34  -r  {prefix} drop database with name starts with prefix
35  -v  enable verbose preview (prints sql that would happen, but does not execute it, DB is not updated)
36  --force-decision force recalculation of SHA256 for decision tables
37  --force-pfile    force recalculation of SHA256 for pfile entries
38  --force-encode   force recode of copyright and sister tables
39  -h  this help usage";
40  print "$usage\n";
41  exit(0);
42}
43
44
45/**
46 * @file fossinit.php
47 * @brief This program applies core-schema.dat to the database (which
48 *        must exist) and updates the license_ref table.
49 * @return 0 for success, 1 for failure.
50 **/
51
52use Fossology\Lib\Db\DbManager;
53use Fossology\Lib\Db\Driver\Postgres;
54
55/* Note: php 5 getopt() ignores options not specified in the function call, so add
56 * dummy options in order to catch invalid options.
57 */
58$AllPossibleOpts = "abc:d:ef:ghijklmnopqr:stuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
59$longOpts = [
60  "force-decision",
61  "force-pfile",
62  "force-encode"
63];
64
65/* defaults */
66$Verbose = false;
67$DatabaseName = "fossology";
68$UpdateLiceneseRef = false;
69$sysconfdir = '';
70$delDbPattern = 'the option -rfosstest will drop data bases with datname like "fosstest%"';
71$forceDecision = false;
72$forcePfile = false;
73
74/* command-line options */
75$Options = getopt($AllPossibleOpts, $longOpts);
76foreach($Options as $optKey => $optVal)
77{
78  switch($optKey)
79  {
80    case 'c': /* set SYSCONFIDR */
81      $sysconfdir = $optVal;
82      break;
83    case 'd': /* optional database name */
84      $DatabaseName = $optVal;
85      break;
86    case 'f': /* schema file */
87      $SchemaFilePath = $optVal;
88      break;
89    case 'h': /* help */
90      explainUsage();
91    case 'l': /* update the license_ref table */
92      $UpdateLiceneseRef = true;
93      break;
94    case 'v': /* verbose */
95      $Verbose = true;
96      break;
97    case 'r':
98      $delDbPattern = $optVal ? "$optVal%" : "fosstest%";
99      break;
100    case "force-decision":
101      $forceDecision = true;
102      break;
103    case "force-pfile":
104      $forcePfile = true;
105      break;
106    case "force-encode":
107      putenv('FOSSENCODING=1');
108      break;
109    default:
110      echo "Invalid Option \"$optKey\".\n";
111      explainUsage();
112  }
113}
114
115require_once 'fossinit-common.php';
116
117/* Set SYSCONFDIR and set global (for backward compatibility) */
118$SysConf = bootstrap($sysconfdir);
119$SysConf["DBCONF"]["dbname"] = $DatabaseName;
120$GLOBALS["SysConf"] = array_merge($GLOBALS["SysConf"], $SysConf);
121$projectGroup = $SysConf['DIRECTORIES']['PROJECTGROUP'] ?: 'fossy';
122$gInfo = posix_getgrnam($projectGroup);
123posix_setgid($gInfo['gid']);
124$groups = `groups`;
125if (!preg_match("/\s$projectGroup\s/",$groups) && (posix_getgid() != $gInfo['gid']))
126{
127  print "FATAL: You must be in group '$projectGroup'.\n";
128  exit(1);
129}
130
131require_once("$MODDIR/vendor/autoload.php");
132require_once("$MODDIR/lib/php/common-db.php");
133require_once("$MODDIR/lib/php/common-container.php");
134require_once("$MODDIR/lib/php/common-cache.php");
135require_once("$MODDIR/lib/php/common-sysconfig.php");
136require_once("$MODDIR/lib/php/fossdash-config.php");
137
138/* Initialize global system configuration variables $SysConfig[] */
139ConfigInit($SYSCONFDIR, $SysConf);
140
141/* Initialize fossdash configuration variables */
142FossdashConfigInit($SYSCONFDIR, $SysConf);
143
144/** delete from copyright where pfile_fk not in (select pfile_pk from pfile) */
145/** add foreign constraint on copyright pfile_fk if not exist */
146/** comment out for 2.5.0
147require_once("$LIBEXECDIR/dbmigrate_2.0-2.5-pre.php");
148Migrate_20_25($Verbose);
149*/
150
151if (empty($SchemaFilePath)) {
152  $SchemaFilePath = "$MODDIR/www/ui/core-schema.dat";
153}
154
155if (!file_exists($SchemaFilePath))
156{
157  print "FAILED: Schema data file ($SchemaFilePath) not found.\n";
158  exit(1);
159}
160
161require_once("$MODDIR/lib/php/libschema.php");
162$pgDriver = new Postgres($PG_CONN);
163$libschema->setDriver($pgDriver);
164$previousSchema = $libschema->getCurrSchema();
165$isUpdating = array_key_exists('TABLE', $previousSchema) && array_key_exists('users', $previousSchema['TABLE']);
166/** @var DbManager $dbManager */
167if ($dbManager->existsTable('sysconfig'))
168{
169  $sysconfig = $dbManager->createMap('sysconfig', 'variablename', 'conf_value');
170  if(!array_key_exists('Release', $sysconfig))
171  {
172    $sysconfig['Release'] = 0;
173  }
174  print "Old release was $sysconfig[Release]\n";
175}
176
177$migrateColumns = array('clearing_decision'=>array('reportinfo','clearing_pk','type_fk','comment'),
178        'license_ref_bulk'=>array('rf_fk','removing'));
179if($isUpdating && !empty($sysconfig) && $sysconfig['Release'] == '2.6.3.1')
180{
181  $dbManager->queryOnce('begin;
182    CREATE TABLE uploadtree_b AS (SELECT * FROM uploadtree_a);
183    DROP TABLE uploadtree_a;
184    CREATE TABLE uploadtree_a () INHERITS (uploadtree);
185    ALTER TABLE uploadtree_a ADD CONSTRAINT uploadtree_a_pkey PRIMARY KEY (uploadtree_pk);
186    INSERT INTO uploadtree_a SELECT * FROM uploadtree_b;
187    DROP TABLE uploadtree_b;
188    COMMIT;',__FILE__.'.rebuild.uploadtree_a');
189}
190
191if($dbManager->existsTable("author"))
192{
193  require_once("$LIBEXECDIR/resequence_author_table.php"); // If table exists, clean up for Schema
194}
195
196// Migration script to clear tables for new constraints
197require_once("$LIBEXECDIR/dbmigrate_3.3-3.4.php");
198Migrate_33_34($dbManager, $Verbose);
199
200$FailMsg = $libschema->applySchema($SchemaFilePath, $Verbose, $DatabaseName, $migrateColumns);
201if ($FailMsg)
202{
203  print "ApplySchema failed: $FailMsg\n";
204  exit(1);
205}
206$Filename = "$MODDIR/www/ui/init.ui";
207$flagRemoved = !file_exists($Filename);
208if (!$flagRemoved)
209{
210  if ($Verbose)
211  {
212    print "Removing flag '$Filename'\n";
213  }
214  if (is_writable("$MODDIR/www/ui/"))
215  {
216    $flagRemoved = unlink($Filename);
217  }
218}
219if (!$flagRemoved)
220{
221  print "Failed to remove $Filename\n";
222  print "Remove this file to complete the initialization.\n";
223}
224else
225{
226  print "Database schema update completed successfully.\n";
227}
228
229$FAILED_LICENSE_IMPORT = array();
230
231/* initialize the license_ref table */
232if ($UpdateLiceneseRef)
233{
234  $row = $dbManager->getSingleRow("SELECT count(*) FROM license_ref",array(),'license_ref.count');
235  if ($row['count'] >  0) {
236    print "Update reference licenses\n";
237    initLicenseRefTable(false);
238  }
239  else if ($row['count'] ==  0) {
240    insertInToLicenseRefTableUsingJson('license_ref');
241
242    $row_max = $dbManager->getSingleRow("SELECT max(rf_pk) from license_ref",array(),'license_ref.max.rf_pk');
243    $current_license_ref_rf_pk_seq = $row_max['max'];
244    $dbManager->getSingleRow("SELECT setval('license_ref_rf_pk_seq', $current_license_ref_rf_pk_seq)",array(),
245            'set next license_ref_rf_pk_seq value');
246
247    print "fresh install, import licenseRef.json \n";
248  }
249}
250
251if (array_key_exists('r', $Options))
252{
253  $dbManager->prepare(__METHOD__.".getDelDbNames",'SELECT datname FROM pg_database WHERE datistemplate = false and datname like $1');
254  $resDelDbNames = $dbManager->execute(__METHOD__.".getDelDbNames",array($delDbPattern));
255  $delDbNames=pg_fetch_all($resDelDbNames);
256  pg_free_result($resDelDbNames);
257  foreach ($delDbNames as $deleteDatabaseName)
258  {
259    $dbManager->queryOnce("DROP DATABASE $deleteDatabaseName[datname]");
260  }
261  if ($Verbose)
262  {
263    echo "dropped " . count($delDbNames) . " databases ";
264  }
265}
266
267/* migration */
268$currSchema = $libschema->getCurrSchema();
269$sysconfig = $dbManager->createMap('sysconfig','variablename','conf_value');
270global $LIBEXECDIR;
271if($isUpdating && empty($sysconfig['Release'])) {
272  require_once("$LIBEXECDIR/dbmigrate_2.0-2.1.php");  // this is needed for all new installs from 2.0 on
273  Migrate_20_21($Verbose);
274  require_once("$LIBEXECDIR/dbmigrate_2.1-2.2.php");
275  print "Migrate data from 2.1 to 2.2 in $LIBEXECDIR\n";
276  Migrate_21_22($Verbose);
277  if($dbManager->existsTable('license_file_audit') && array_key_exists('clearing_pk', $currSchema['TABLE']['clearing_decision']))
278  {
279    require_once("$LIBEXECDIR/dbmigrate_2.5-2.6.php");
280    migrate_25_26($Verbose);
281  }
282  if(!array_key_exists('clearing_pk', $currSchema['TABLE']['clearing_decision']) && $isUpdating)
283  {
284    $timeoutSec = 20;
285    echo "Missing column clearing_decision.clearing_pk, you should update to version 2.6.2 before migration\n";
286    echo "Enter 'i' within $timeoutSec seconds to ignore this warning and run the risk of losing clearing decisions: ";
287    $handle = fopen ("php://stdin","r");
288    stream_set_blocking($handle,0);
289    for($s=0;$s<$timeoutSec;$s++)
290    {
291      sleep(1);
292      $line = fread($handle,1);
293      if ($line) {
294        break;
295      }
296    }
297    if(trim($line) != 'i')
298    {
299     echo "ABORTING!\n";
300     exit(26);
301    }
302  }
303  $sysconfig['Release'] = '2.6';
304}
305if (! $isUpdating) {
306  require_once ("$LIBEXECDIR/dbmigrate_2.1-2.2.php");
307  print "Creating default user\n";
308  Migrate_21_22($Verbose);
309} else {
310  require_once ("$LIBEXECDIR/dbmigrate_3.5-3.6.php");
311  migrate_35_36($dbManager, $forceDecision);
312  updatePfileSha256($dbManager, $forcePfile);
313}
314
315if(!$isUpdating || $sysconfig['Release'] == '2.6')
316{
317  if(!$dbManager->existsTable('license_candidate'))
318  {
319    $dbManager->queryOnce("CREATE TABLE license_candidate (group_fk integer) INHERITS (license_ref)");
320  }
321  if ($isUpdating && array_key_exists('clearing_pk', $currSchema['TABLE']['clearing_decision']))
322  {
323    require_once("$LIBEXECDIR/dbmigrate_clearing-event.php");
324    $libschema->dropColumnsFromTable(array('reportinfo','clearing_pk','type_fk','comment'), 'clearing_decision');
325  }
326  $sysconfig['Release'] = '2.6.3';
327}
328
329if($sysconfig['Release'] == '2.6.3')
330{
331  require_once("$LIBEXECDIR/dbmigrate_real-parent.php");
332}
333
334$expiredDbReleases = array('2.6.3', '2.6.3.1', '2.6.3.2');
335if($isUpdating && (empty($sysconfig['Release']) || in_array($sysconfig['Release'], $expiredDbReleases)))
336{
337  require_once("$LIBEXECDIR/fo_mapping_license.php");
338  print "Rename license (using $LIBEXECDIR) for SPDX validity\n";
339  renameLicensesForSpdxValidation($Verbose);
340}
341
342$expiredDbReleases[] = '2.6.3.3';
343$expiredDbReleases[] = '3.0.0';
344if($isUpdating && (empty($sysconfig['Release']) || in_array($sysconfig['Release'], $expiredDbReleases)))
345{
346  require_once("$LIBEXECDIR/dbmigrate_bulk_license.php");
347}
348
349if(in_array($sysconfig['Release'], $expiredDbReleases))
350{
351  $sysconfig['Release'] = '3.0.1';
352}
353
354// Update '3dfx' licence shortname to 'Glide'. Since shortname is used as an
355// identifier, this is not done as part of the licenseref updates.
356if($isUpdating && (empty($sysconfig['Release']) || $sysconfig['Release'] == '3.0.1'))
357{
358  $dbManager->begin();
359  $row = $dbManager->getSingleRow("
360    SELECT rf1.rf_pk AS id_3dfx,
361           rf2.rf_pk AS id_glide
362    FROM license_ref rf1
363      INNER JOIN license_ref rf2 USING (rf_fullname)
364    WHERE rf1.rf_shortname='3DFX'
365      AND rf2.rf_shortname='Glide'
366    LIMIT 1", array(), 'old.3dfx.rf_pk');
367  if (!empty($row))
368  {
369    $id_3dfx = intval($row['id_3dfx']);
370    $id_glide = intval($row['id_glide']);
371    $dbManager->queryOnce("DELETE FROM license_ref WHERE rf_pk=$id_glide");
372    $dbManager->queryOnce("UPDATE license_ref SET rf_shortname='Glide' WHERE rf_pk=$id_3dfx");
373  }
374  $dbManager->commit();
375
376  $sysconfig['Release'] = "3.0.2";
377}
378
379if($isUpdating && (empty($sysconfig['Release']) || $sysconfig['Release'] == '3.0.2'))
380{
381  require_once("$LIBEXECDIR/dbmigrate_multiple_copyright_decisions.php");
382
383  $sysconfig['Release'] = "3.1.0";
384}
385
386// fix release-version datamodel-version missmatch
387if($isUpdating && (empty($sysconfig['Release']) || $sysconfig['Release'] == "3.1.0")) {
388  $sysconfig['Release'] = "3.3.0";
389}
390
391$dbManager->begin();
392$dbManager->getSingleRow("DELETE FROM sysconfig WHERE variablename=$1",array('Release'),'drop.sysconfig.release');
393$dbManager->insertTableRow('sysconfig',
394        array('variablename'=>'Release','conf_value'=>$sysconfig['Release'],'ui_label'=>'Release','vartype'=>2,'group_name'=>'Release','description'=>''));
395$dbManager->commit();
396/* email/url/author data migration to other table */
397require_once("$LIBEXECDIR/dbmigrate_copyright-author.php");
398
399// Migration script to move candidate licenses in obligations
400require_once("$LIBEXECDIR/dbmigrate_3.6-3.7.php");
401Migrate_36_37($dbManager, $Verbose);
402
403/* instance uuid */
404require_once("$LIBEXECDIR/instance_uuid.php");
405
406// Migration script for 3.7 => 3.8
407require_once("$LIBEXECDIR/dbmigrate_3.7-3.8.php");
408Migrate_37_38($dbManager, $MODDIR);
409
410// Migration script for copyright_event table
411require_once("$LIBEXECDIR/dbmigrate_copyright-event.php");
412createCopyrightMigrationForCopyrightEvents($dbManager);
413
414/* sanity check */
415require_once ("$LIBEXECDIR/sanity_check.php");
416$checker = new SanityChecker($dbManager,$Verbose);
417$errors = $checker->check();
418
419if($errors>0)
420{
421  echo "ERROR: $errors sanity check".($errors>1?'s':'')." failed\n";
422}
423if (! empty($FAILED_LICENSE_IMPORT)) {
424  $failedInsert = array_filter($FAILED_LICENSE_IMPORT,
425    function ($x){
426      return $x[1] == "INSERT";
427    });
428  $failedUpdate = array_filter($FAILED_LICENSE_IMPORT,
429    function ($x){
430      return $x[1] == "UPDATE";
431    });
432  $failedPromote = array_filter($FAILED_LICENSE_IMPORT,
433    function ($x){
434      return $x[1] == "CANPROMOTE";
435    });
436  if (! empty($failedInsert)) {
437    echo "*** Failed to insert following licenses ***\n";
438    echo implode(",", array_map(function ($x) {
439      return $x[0];
440    }, $failedInsert)) . "\n";
441  }
442  if (! empty($failedUpdate)) {
443    echo "*** Failed to update following licenses ***\n";
444    echo implode(",", array_map(function ($x) {
445      return $x[0];
446    }, $failedUpdate)) . "\n";
447  }
448  if (! empty($failedPromote)) {
449    echo "*** Failed to move following licenses from candidate table ***\n";
450    echo implode(",", array_map(function ($x) {
451      return $x[0];
452    }, $failedPromote)) . "\n";
453  }
454}
455exit($errors);
456
457/**
458 * \brief insert into license_ref table using json file.
459 *
460 * \param $tableName
461 **/
462function insertInToLicenseRefTableUsingJson($tableName)
463{
464  global $LIBEXECDIR;
465  global $dbManager;
466
467  if (!is_dir($LIBEXECDIR)) {
468    print "FATAL: Directory '$LIBEXECDIR' does not exist.\n";
469    return (1);
470  }
471
472  $dir = opendir($LIBEXECDIR);
473  if (!$dir) {
474    print "FATAL: Unable to access '$LIBEXECDIR'.\n";
475    return (1);
476  }
477  $dbManager->begin();
478  if ($tableName === 'license_ref_2') {
479    $dbManager->queryOnce("DROP TABLE IF EXISTS license_ref_2",
480      __METHOD__.'.dropAncientBackUp');
481    $dbManager->queryOnce("CREATE TABLE license_ref_2 (LIKE license_ref INCLUDING DEFAULTS)",
482      __METHOD__.'.backUpData');
483  }
484  /** import licenseRef.json */
485  $keysToBeChanged = array(
486    'rf_OSIapproved' => '"rf_OSIapproved"',
487    'rf_FSFfree'=> '"rf_FSFfree"',
488    'rf_GPLv2compatible' => '"rf_GPLv2compatible"',
489    'rf_GPLv3compatible'=> '"rf_GPLv3compatible"',
490    'rf_Fedora' => '"rf_Fedora"'
491    );
492
493  $jsonData = json_decode(file_get_contents("$LIBEXECDIR/licenseRef.json"), true);
494  $statementName = __METHOD__.'.insertInTo'.$tableName;
495  foreach($jsonData as $licenseArray) {
496    $arrayKeys = array_keys($licenseArray);
497    $arrayValues = array_values($licenseArray);
498    $keys = strtr(implode(",", $arrayKeys), $keysToBeChanged);
499    $valuePlaceHolders = "$" . join(",$",range(1, count($arrayKeys)));
500    $md5PlaceHolder = "$". (count($arrayKeys) + 1);
501    $arrayValues[] = $licenseArray['rf_text'];
502    $SQL = "INSERT INTO $tableName ( $keys,rf_md5 ) " .
503      "VALUES ($valuePlaceHolders,md5($md5PlaceHolder));";
504    $dbManager->prepare($statementName, $SQL);
505    $dbManager->execute($statementName, $arrayValues);
506  }
507  $dbManager->commit();
508  return (0);
509}
510
511/**
512 * \brief Load the license_ref table with licenses.
513 *
514 * \param $Verbose display database load progress information.  If $Verbose is false,
515 * this function only prints errors.
516 *
517 * \return 0 on success, 1 on failure
518 **/
519function initLicenseRefTable($Verbose)
520{
521  global $dbManager;
522
523  $dbManager->begin();
524  insertInToLicenseRefTableUsingJson('license_ref_2');
525  $dbManager->prepare(__METHOD__.".newLic", "SELECT * FROM license_ref_2");
526  $result_new = $dbManager->execute(__METHOD__.".newLic");
527
528  $dbManager->prepare(__METHOD__.'.licenseRefByShortname',
529    'SELECT *,md5(rf_text) AS hash FROM license_ref WHERE rf_shortname=$1');
530  /** traverse all records in user's license_ref table, update or insert */
531  while ($row = pg_fetch_assoc($result_new))
532  {
533    $rf_shortname = $row['rf_shortname'];
534    $result_check = $dbManager->execute(__METHOD__.'.licenseRefByShortname', array($rf_shortname));
535    $count = pg_num_rows($result_check);
536
537    $rf_text = $row['rf_text'];
538    $rf_md5 = $row['rf_md5'];
539    $rf_url = $row['rf_url'];
540    $rf_fullname = $row['rf_fullname'];
541    $rf_notes = $row['rf_notes'];
542    $rf_active = $row['rf_active'];
543    $marydone = $row['marydone'];
544    $rf_text_updatable = $row['rf_text_updatable'];
545    $rf_detector_type = $row['rf_detector_type'];
546    $rf_flag = $row['rf_flag'];
547
548    if ($count) // update when it is existing
549    {
550      $row_check = pg_fetch_assoc($result_check);
551      pg_free_result($result_check);
552      $params = array();
553      $rf_text_check = $row_check['rf_text'];
554      $rf_md5_check = $row_check['rf_md5'];
555      $hash_check = $row_check['hash'];
556      $rf_url_check = $row_check['rf_url'];
557      $rf_fullname_check = $row_check['rf_fullname'];
558      $rf_notes_check = $row_check['rf_notes'];
559      $rf_active_check = $row_check['rf_active'];
560      $marydone_check = $row_check['marydone'];
561      $rf_text_updatable_check = $row_check['rf_text_updatable'];
562      $rf_detector_type_check = $row_check['rf_detector_type'];
563      $rf_flag_check = $row_check['rf_flag'];
564
565      $candidateLicense = isLicenseExists($dbManager, $rf_shortname, true);
566      if ($candidateLicense) {
567        mergeCandidateLicense($dbManager, $candidateLicense);
568      }
569
570      $statement = __METHOD__ . ".updateLicenseRef";
571      $sql = "UPDATE license_ref set ";
572      if (($rf_flag_check == 2 && $rf_flag == 1) ||
573        ($hash_check != $rf_md5_check)) {
574        $params[] = $rf_text_check;
575        $position = "$" . count($params);
576        $sql .= "rf_text=$position,rf_md5=md5($position),";
577        $statement .= ".text";
578      } else {
579        if ($rf_text_check != $rf_text && !empty($rf_text) &&
580          !(stristr($rf_text, 'License by Nomos'))) {
581          $params[] = $rf_text;
582          $position = "$" . count($params);
583          $sql .= "rf_text=$position,rf_md5=md5($position),rf_flag=1,";
584          $statement .= ".insertT";
585        }
586      }
587      if ($rf_url_check != $rf_url && !empty($rf_url)) {
588        $params[] = $rf_url;
589        $position = "$" . count($params);
590        $sql .= "rf_url=$position,";
591        $statement .= ".url";
592      }
593      if ($rf_fullname_check != $rf_fullname && !empty($rf_fullname)) {
594        $params[] = $rf_fullname;
595        $position = "$" . count($params);
596        $sql .= "rf_fullname=$position,";
597        $statement .= ".name";
598      }
599      if ($rf_notes_check != $rf_notes && !empty($rf_notes)) {
600        $params[] = $rf_notes;
601        $position = "$" . count($params);
602        $sql .= "rf_notes=$position,";
603        $statement .= ".notes";
604      }
605      if ($rf_active_check != $rf_active && !empty($rf_active)) {
606        $params[] = $rf_active;
607        $position = "$" . count($params);
608        $sql .= "rf_active=$position,";
609        $statement .= ".active";
610      }
611      if ($marydone_check != $marydone && !empty($marydone)) {
612        $params[] = $marydone;
613        $position = "$" . count($params);
614        $sql .= "marydone=$position,";
615        $statement .= ".marydone";
616      }
617      if ($rf_text_updatable_check != $rf_text_updatable && !empty($rf_text_updatable)) {
618        $params[] = $rf_text_updatable;
619        $position = "$" . count($params);
620        $sql .= "rf_text_updatable=$position,";
621        $statement .= ".tUpdate";
622      }
623      if ($rf_detector_type_check != $rf_detector_type && !empty($rf_detector_type)) {
624        $params[] = $rf_detector_type;
625        $position = "$" . count($params);
626        $sql .= "rf_detector_type=$position,";
627        $statement .= ".dType";
628      }
629      $sql = substr_replace($sql, "", -1);
630
631      if ($sql != "UPDATE license_ref set") { // check if have something to update
632        $params[] = $rf_shortname;
633        $position = "$" . count($params);
634        $sql .= " WHERE rf_shortname=$position;";
635        try {
636          $dbManager->getSingleRow($sql, $params, $statement);
637        } catch (\Exception $e) {
638          global $FAILED_LICENSE_IMPORT;
639          $FAILED_LICENSE_IMPORT[] = array($rf_shortname, "UPDATE");
640        }
641      }
642    } else {  // insert when it is new
643      pg_free_result($result_check);
644      $params = array();
645      $params['rf_shortname'] = $rf_shortname;
646      $params['rf_text'] = $rf_text;
647      $params['rf_url'] = $rf_url;
648      $params['rf_fullname'] = $rf_fullname;
649      $params['rf_notes'] = $rf_notes;
650      $params['rf_active'] = $rf_active;
651      $params['rf_text_updatable'] = $rf_text_updatable;
652      $params['rf_detector_type'] = $rf_detector_type;
653      $params['marydone'] = $marydone;
654      insertNewLicense($dbManager, $params);
655    }
656  }
657  pg_free_result($result_new);
658
659  $dbManager->queryOnce("DROP TABLE IF EXISTS license_ref_2");
660  $dbManager->commit();
661
662  return (0);
663} // initLicenseRefTable()
664
665/**
666 * Check if the given shortname exists in DB.
667 *
668 * @param DbManager $dbManager DbManager used
669 * @param string $rf_shortname Shortname of the license to check
670 * @param boolean $isCandidate check given shortname in candidate table
671 * @returns False if the license does not exists else DB row
672 */
673function isLicenseExists($dbManager, $rf_shortname, $isCandidate = true)
674{
675  $tableName = "license_ref";
676  if ($isCandidate) {
677    $tableName = "license_candidate";
678  }
679  $statement = __METHOD__ . ".$tableName";
680  $sql = "SELECT * FROM ONLY $tableName WHERE rf_shortname = $1;";
681  $licenseRow = $dbManager->getSingleRow($sql, array($rf_shortname),
682    $statement);
683  if (! empty($licenseRow)) {
684    return $licenseRow;
685  } else {
686    return false;
687  }
688}
689
690/**
691 * Merge the candidate license to the main license_ref table.
692 *
693 * @param DbManager $dbManager    DbManager used
694 * @param array $candidateLicense Shortname of the license to check
695 * @return integer License ID
696 */
697function mergeCandidateLicense($dbManager, $candidateLicense)
698{
699  $mainLicense = isLicenseExists($dbManager, $candidateLicense['rf_shortname'],
700    false);
701  $statement = __METHOD__ . ".md5Exists";
702  $sql = "SELECT rf_pk FROM ONLY license_ref WHERE md5(rf_text) = md5($1);";
703  $licenseRow = $dbManager->getSingleRow($sql,
704    array($candidateLicense['rf_text']), $statement);
705  if (! empty($licenseRow)) {
706    $md5Exists = true;
707  } else {
708    $md5Exists = false;
709  }
710  if ($mainLicense !== false && $md5Exists) {
711    $dbManager->begin();
712    $updateStatements = __METHOD__ . ".updateCandidateToMain";
713    $updateCeSql = "UPDATE clearing_event SET rf_fk = $1 WHERE rf_fk = $2;";
714    $updateCeSt = $updateStatements . ".ce";
715    $updateLsbSql = "UPDATE license_set_bulk SET rf_fk = $1 WHERE rf_fk = $2;";
716    $updateLsbSt = $updateStatements . ".lsb";
717    $updateUclSql = "UPDATE upload_clearing_license SET rf_fk = $1 " .
718      "WHERE rf_fk = $2;";
719    $updateUclSt = $updateStatements . ".ucl";
720    $deleteOcmSql = "DELETE FROM obligation_candidate_map WHERE rf_fk = $1;";
721    $deleteOcmSt = $updateStatements . ".ocm";
722
723    $dbManager->getSingleRow($updateCeSql,
724      array($mainLicense['rf_pk'], $candidateLicense['rf_pk']), $updateCeSt);
725    $dbManager->getSingleRow($updateLsbSql,
726      array($mainLicense['rf_pk'], $candidateLicense['rf_pk']), $updateLsbSt);
727    $dbManager->getSingleRow($updateUclSql,
728      array($mainLicense['rf_pk'], $candidateLicense['rf_pk']), $updateUclSt);
729    $dbManager->getSingleRow($deleteOcmSql, array($candidateLicense['rf_pk']),
730      $deleteOcmSt);
731    $dbManager->commit();
732  } elseif ($mainLicense !== false || $md5Exists) {
733    // Short name exists and MD5 does not match
734    // Or short name does not exists by MD5 match
735    return -1;
736  }
737  $dbManager->begin();
738  $deleteSql = "DELETE FROM license_candidate WHERE rf_pk = $1;";
739  $deleteStatement = __METHOD__ . ".deleteCandidte";
740  $dbManager->prepare($deleteStatement, $deleteSql);
741  $dbManager->execute($deleteStatement, array($candidateLicense['rf_pk']));
742  if ($mainLicense === false && $md5Exists === false) {
743    // License does not exists
744    insertNewLicense($dbManager, $candidateLicense, true);
745  }
746  $dbManager->commit();
747}
748
749/**
750 * Insert new license to license_ref
751 *
752 * @param DbManager $dbManager  DbManager to be used
753 * @param array $license        License row to be added
754 * @param boolean $wasCandidate Was the new license already a candidate?
755 *        (required for rf_pk)
756 * @return integer New license ID
757 */
758function insertNewLicense($dbManager, $license, $wasCandidate = false)
759{
760  $insertStatement = __METHOD__ . ".insertNewLicense";
761  $sql = "INSERT INTO license_ref (";
762  if ($wasCandidate) {
763    $sql .= "rf_pk, ";
764    $insertStatement .= ".wasCandidate";
765  }
766  $sql .= "rf_shortname, rf_text, rf_url, rf_fullname, rf_notes, rf_active, " .
767    "rf_text_updatable, rf_detector_type, marydone, rf_md5, rf_add_date" .
768    ") VALUES (";
769  $params = array();
770  if ($wasCandidate) {
771    $params[] = $license['rf_pk'];
772  }
773  $params[] = $license['rf_shortname'];
774  $params[] = $license['rf_text'];
775  $params[] = $license['rf_url'];
776  $params[] = $license['rf_fullname'];
777  $params[] = $license['rf_notes'];
778  $params[] = $license['rf_active'];
779  $params[] = $license['rf_text_updatable'];
780  $params[] = $license['rf_detector_type'];
781  $params[] = $license['marydone'];
782
783  for ($i = 1; $i <= count($params); $i++) {
784    $sql .= "$" . $i . ",";
785  }
786
787  $params[] = $license['rf_text'];
788  $textPos = "$" . count($params);
789
790  $sql .= "md5($textPos),now())";
791  $rfPk = -1;
792  try {
793    $rfPk = $dbManager->insertPreparedAndReturn($insertStatement, $sql, $params,
794      "rf_pk");
795  } catch (\Exception $e) {
796    global $FAILED_LICENSE_IMPORT;
797    $type = "INSERT";
798    if ($wasCandidate) {
799      $type = "CANPROMOTE";
800    }
801    $FAILED_LICENSE_IMPORT[] = array($license['rf_shortname'], $type);
802  }
803  return $rfPk;
804}
805