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