libschema.php

Go to the documentation of this file.

<?php
/*
 SPDX-FileCopyrightText: © 2008-2014 Hewlett-Packard Development Company, L.P.
 SPDX-FileCopyrightText: © 2014-2015, 2018 Siemens AG
 
 SPDX-License-Identifier: LGPL-2.1-only
*/
 
require_once(__DIR__ . '/../../vendor/autoload.php');
 
use Fossology\Lib\Db\DbManager;
use Fossology\Lib\Db\Driver;
use Fossology\Lib\Db\Driver\Postgres;
use Fossology\Lib\Db\ModernDbManager;
use Monolog\Handler\ErrorLogHandler;
use Monolog\Logger;
 
class fo_libschema
{
  public $debug = false;
 
  private $dbman;
 
  private $schema = array();
 
  private $currSchema = array();
 
  function __construct(DbManager &$dbManager)
  {
    $this->dbman = $dbManager;
  }
 
  function setDriver(Driver &$dbDriver)
  {
    $this->dbman->setDriver($dbDriver);
  }
 
  private function ensureDriver()
  {
    // If dbman already has a working driver, nothing to do
    $driver = $this->dbman->getDriver();
    if ($driver !== null && $driver instanceof Driver && $driver->isConnected()) {
      return;
    }
    global $PG_CONN;
    if (!empty($PG_CONN)) {
      $pgDriver = new Postgres($PG_CONN);
      $this->dbman->setDriver($pgDriver);
      return;
    }
    throw new \Exception(
      "No database connection available: \$PG_CONN is not set and no driver " .
      "was injected into \$dbManager before calling this function."
    );
  }
 
  function applyOrEchoOnce($sql, $stmt = '')
  {
    $this->ensureDriver();
    if ($this->debug) {
      print ("$sql\n");
    } else {
      $this->dbman->queryOnce($sql, $stmt);
    }
  }
 
 
  function applySchema($filename = NULL, $debug = false, $catalog = 'fossology', $migrateColumns = array())
  {
    global $PG_CONN;
 
    // first check to make sure we don't already have the plpgsql language installed
    $result = $this->dbman->getSingleRow(
      "SELECT lanname FROM pg_language WHERE lanname = 'plpgsql'",
      array(),
      __METHOD__ . '.checkPlpgsql'
    );
 
    // then create language plpgsql if not already created
    if (empty($result)) {
      $this->dbman->queryOnce("CREATE LANGUAGE plpgsql", __METHOD__ . '.createPlpgsql');
    }
 
    $result = $this->dbman->getSingleRow(
      "SELECT extname FROM pg_extension WHERE extname = 'uuid-ossp'",
      array(),
      __METHOD__ . '.checkUuid'
    );
 
    // then create extension uuid-ossp if not already created
    if (empty($result)) {
      $this->dbman->queryOnce('CREATE EXTENSION "uuid-ossp"', __METHOD__ . '.createUuid');
    }
 
    $this->debug = $debug;
    if (!file_exists($filename)) {
      return "$filename does not exist.";
    }
    $Schema = array(); /* will be filled in next line */
    require($filename); /* this cause Fatal Error if the file does not exist. */
    $this->schema = $Schema;
 
    /* Very basic sanity check (so we don't delete everything!) */
    if ((count($this->schema['TABLE']) < 5) || (count($this->schema['SEQUENCE']) < 5)
        || (count($this->schema['INDEX']) < 5) || (count($this->schema['CONSTRAINT']) < 5)
    ) {
      return "Schema from '$filename' appears invalid.";
    }
 
    if (!$debug) {
      $result = $this->dbman->getSingleRow("show statement_timeout", array(), $stmt = __METHOD__ . '.getTimeout');
      $statementTimeout = $result['statement_timeout'];
      $this->dbman->queryOnce("SET statement_timeout = 0", $stmt = __METHOD__ . '.setTimeout');
    }
 
    $this->applyOrEchoOnce('BEGIN');
    $this->getCurrSchema();
    $errlev = error_reporting(E_ERROR | E_WARNING | E_PARSE);
    $this->dropViews($catalog);
    $this->dropConstraints();
    $this->applySequences();
    $this->applyTables();
    $this->applyInheritedRelations();
    $this->getCurrSchema(); /* New tables created, recheck */
    $this->applyTables(true);
    $this->updateSequences();
    $this->applyViews();
    $this->dropConstraints();
    /* Reload current since the CASCADE may have changed things */
    $this->getCurrSchema(); /* constraints and indexes are linked, recheck */
    $this->dropIndexes();
    $this->applyIndexes();
    $this->applyConstraints();
    error_reporting($errlev); /* return to previous error reporting level */
    $this->makeFunctions();
    $this->applyClusters();
    /* Reload current since CASCADE during migration may have changed things */
    $this->getCurrSchema();
    $this->dropViews($catalog);
    foreach ($this->currSchema['TABLE'] as $table => $columns) {
      $skipColumns = array_key_exists($table, $migrateColumns) ? $migrateColumns[$table] : array();
      $dropColumns = array_diff(array_keys($columns), $skipColumns);
      $this->dropColumnsFromTable($dropColumns, $table);
    }
    $this->applyOrEchoOnce('COMMIT');
    flush();
    ReportCachePurgeAll();
    if (!$debug) {
      $this->dbman->getSingleRow("SET statement_timeout = $statementTimeout", array(), $stmt = __METHOD__ . '.resetTimeout');
      print "DB schema has been updated for $catalog.\n";
    } else {
      print "These queries could update DB schema for $catalog.\n";
    }
    return false;
  }
 
  function applySequences()
  {
    if (empty($this->schema['SEQUENCE'])) {
      return;
    }
    foreach ($this->schema['SEQUENCE'] as $name => $import) {
      if (empty($name)) {
        continue;
      }
 
      if (!array_key_exists('SEQUENCE', $this->currSchema)
        || !array_key_exists($name, $this->currSchema['SEQUENCE'])) {
        $createSql = is_string($import) ? $import : $import['CREATE'];
        $this->applyOrEchoOnce($createSql, $stmt = __METHOD__ . "." . $name . ".CREATE");
      }
    }
  }
  function applyClusters()
  {
    if (empty($this->schema['CLUSTER'])) {
      return;
    }
    foreach ($this->schema['CLUSTER'] as $name => $sql) {
      if (empty($name)) {
        continue;
      }
 
      if (!array_key_exists('CLUSTER', $this->currSchema)
        || !array_key_exists($name, $this->currSchema['CLUSTER'])) {
        $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . "." . $name . ".CREATE");
      }
    }
  }
 
  function updateSequences()
  {
    if (empty($this->schema['SEQUENCE']) ||
        !(array_key_exists('SEQUENCE', $this->currSchema))) {
      return;
    }
    foreach ($this->schema['SEQUENCE'] as $name => $import) {
      if (empty($name)) {
        continue;
      }
 
      if (is_array($import) && array_key_exists('UPDATE', $import)) {
        $this->applyOrEchoOnce($import['UPDATE'], $stmt = __METHOD__ . "." . $name);
      }
    }
  }
 
  function applyTables($inherits=false)
  {
    if (empty($this->schema['TABLE'])) {
      return;
    }
    foreach ($this->schema['TABLE'] as $table => $columns) {
      if (empty($table) || $inherits^array_key_exists($table,$this->schema['INHERITS']) ) {
        continue;
      }
      $newTable = false;
      if (!DB_TableExists($table)) {
        $sql = "CREATE TABLE IF NOT EXISTS \"$table\" ()";
        $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . $table);
        $newTable = true;
      } elseif (!array_key_exists($table, $this->currSchema['TABLE'])) {
        $newTable = true;
      }
      /* Drop any leftover _old columns from previous incomplete migrations. */
      $this->applyOrEchoOnce(
        "DO \$cleanup\$ DECLARE r RECORD; BEGIN
           FOR r IN
             SELECT column_name FROM information_schema.columns
             WHERE table_name = '$table' AND column_name LIKE '%_old'
           LOOP
             EXECUTE 'ALTER TABLE \"$table\" DROP COLUMN IF EXISTS \"' || r.column_name || '\"';
           END LOOP;
         END \$cleanup\$;",
        $stmt = __METHOD__ . ".$table.purge_old_cols"
      );
 
      foreach ($columns as $column => $modification) {
        if (!$newTable && !array_key_exists($column, $this->currSchema['TABLE'][$table])) {
          $colNewTable = true;
        } else {
          $colNewTable = $newTable;
        }
        if ($colNewTable ||
            $this->currSchema['TABLE'][$table][$column]['ADD'] != $modification['ADD']) {
          $rename = "";
          if (DB_ColExists($table, $column)) {
            /* The column exists, but it looks different!
             Solution: Delete the column! */
            $rename = $column . '_old';
            $sql = "ALTER TABLE \"$table\" RENAME COLUMN \"$column\" TO \"$rename\"";
            $this->applyOrEchoOnce($sql);
            // Check constraints on the renamed column
            $constraints_on_column = DB_ColumnConstraints($table, $rename);
            if (!empty($constraints_on_column)) {
              // Drop constraints on the renamed column
              foreach ($constraints_on_column as $conname) {
                $sql = "ALTER TABLE \"$table\" DROP CONSTRAINT \"$conname\"";
                $this->applyOrEchoOnce($sql);
              }
            }
          }
 
          $sql = $modification['ADD'];
          if ($this->debug) {
            print "$sql\n";
          } else {
            // Add the new column which sets the default value
            $this->dbman->queryOnce($sql);
          }
          if (!empty($rename)) {
            /* copy over the old data */
            $this->applyOrEchoOnce($sql = "UPDATE \"$table\" SET \"$column\" = \"$rename\"");
            $this->applyOrEchoOnce($sql = "ALTER TABLE \"$table\" DROP COLUMN \"$rename\"");
          }
        }
        if ($colNewTable ||
            $this->currSchema['TABLE'][$table][$column]['ALTER'] != $modification['ALTER'] && isset($modification['ALTER'])) {
          $sql = $modification['ALTER'];
          if ($this->debug) {
            print "$sql\n";
          } else if (!empty ($sql)) {
            $this->dbman->queryOnce($sql);
          }
        }
        if ($colNewTable ||
            $this->currSchema['TABLE'][$table][$column]['DESC'] != $modification['DESC']) {
          $sql = empty($modification['DESC']) ? "COMMENT ON COLUMN \"$table\".\"$column\" IS ''" : $modification['DESC'];
          $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . "$table.$column.comment");
        }
      }
    }
  }
 
  function applyViews()
  {
    if (empty($this->schema['VIEW'])) {
      return;
    }
    $newViews = !array_key_exists('VIEW', $this->currSchema);
    foreach ($this->schema['VIEW'] as $name => $sql) {
      if (empty($name) || (!$newViews &&
          array_key_exists($name, $this->currSchema['VIEW']) &&
          $this->currSchema['VIEW'][$name] == $sql)) {
        continue;
      }
      if (!$newViews && !empty($this->currSchema['VIEW'][$name])) {
        $sqlDropView = "DROP VIEW IF EXISTS $name";
        $this->applyOrEchoOnce($sqlDropView);
      }
      $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . $name);
    }
  }
 
  function dropConstraints()
  {
    if (!array_key_exists('CONSTRAINT', $this->currSchema) || // Empty DB
        empty($this->currSchema['CONSTRAINT'])) {
      return;
    }
    foreach ($this->currSchema['CONSTRAINT'] as $name => $sql) {
      // skip if constraint name is empty or does not exist
      if (empty($name) || !array_key_exists($name, $this->schema['CONSTRAINT'])
          || ($this->schema['CONSTRAINT'][$name] == $sql)
          || !DB_ConstraintExists($name)) {
        continue;
      }
 
      /* Only process tables that I know about */
      $table = preg_replace("/^ALTER TABLE \"(.*)\" ADD CONSTRAINT.*/", '${1}', $sql);
      $TableFk = preg_replace("/^.*FOREIGN KEY .* REFERENCES \"(.*)\" \‍(.*/", '${1}', $sql);
      if ($TableFk == $sql) {
        $TableFk = $table;
      }
      /* If I don't know the primary or foreign table... */
      if (empty($this->schema['TABLE'][$table]) && empty($this->schema['TABLE'][$TableFk])) {
        continue;
      }
      $sql = "ALTER TABLE \"$table\" DROP CONSTRAINT \"$name\" CASCADE";
      $this->applyOrEchoOnce($sql);
    }
  }
 
  function dropIndexes()
  {
    if (!array_key_exists('INDEX', $this->currSchema) ||
        empty($this->currSchema['INDEX'])) {
      return;
    }
    foreach ($this->currSchema['INDEX'] as $table => $IndexInfo) {
      if (empty($table) || (empty($this->schema['TABLE'][$table]) && empty($this->schema['INHERITS'][$table]))) {
        continue;
      }
      foreach ($IndexInfo as $name => $sql) {
        if (empty($name) || $this->schema['INDEX'][$table][$name] == $sql) {
          continue;
        }
        $sql = "DROP INDEX \"$name\"";
        $this->applyOrEchoOnce($sql);
      }
    }
  }
 
  function applyIndexes()
  {
    if (empty($this->schema['INDEX'])) {
      return;
    }
    foreach ($this->schema['INDEX'] as $table => $indexInfo) {
      if (empty($table)) {
        continue;
      }
      if (!array_key_exists($table, $this->schema["TABLE"]) && !array_key_exists($table, $this->schema['INHERITS'])) {
        echo "skipping orphan table: $table\n";
        continue;
      }
      $newIndexes = false;
      if (!array_key_exists('INDEX', $this->currSchema) ||
          !array_key_exists($table, $this->currSchema['INDEX'])) {
        $newIndexes = true;
      }
      foreach ($indexInfo as $name => $sql) {
        if (empty($name) || (!$newIndexes &&
            array_key_exists($name, $this->currSchema['INDEX'][$table]) &&
            $this->currSchema['INDEX'][$table][$name] == $sql)) {
          continue;
        }
        $this->applyOrEchoOnce($sql);
        $sql = "REINDEX INDEX \"$name\"";
        $this->applyOrEchoOnce($sql);
      }
    }
  }
 
  function applyConstraints()
  {
    $this->currSchema = $this->getCurrSchema(); /* constraints and indexes are linked, recheck */
    if (empty($this->schema['CONSTRAINT'])) {
      return;
    }
    /* Constraints must be added in the correct order! */
    $orderedConstraints = array('primary' => array(), 'unique' => array(), 'foreign' => array(), 'other' => array());
    foreach ($this->schema['CONSTRAINT'] as $Name => $sql) {
      $newConstraint = false;
      if (!array_key_exists('CONSTRAINT', $this->currSchema) ||
          !array_key_exists($Name, $this->currSchema['CONSTRAINT'])) {
        $newConstraint = true;
      }
      if (empty($Name) || (!$newConstraint &&
            $this->currSchema['CONSTRAINT'][$Name] == $sql)) {
        continue;
      }
      if (preg_match("/PRIMARY KEY/", $sql)) {
        $orderedConstraints['primary'][] = $sql;
      } elseif (preg_match("/UNIQUE/", $sql)) {
        $orderedConstraints['unique'][] = $sql;
      } elseif (preg_match("/FOREIGN KEY/", $sql)) {
        $orderedConstraints['foreign'][] = $sql;
      } else {
        $orderedConstraints['other'][] = $sql;
      }
    }
    foreach ($orderedConstraints as $type => $constraints) {
      foreach ($constraints as $sql) {
        $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . ".constraint.$type");
      }
    }
  }
 
  function dropViews($catalog)
  {
    $sql = "SELECT view_name,vcs.table_name,column_name
            FROM information_schema.view_column_usage AS vcs
            INNER JOIN information_schema.views AS v
              ON vcs.view_name = v.table_name
            WHERE vcs.table_catalog='$catalog'
              AND v.table_schema = 'public'
            ORDER BY view_name,vcs.table_name,column_name;";
    $stmt = __METHOD__;
    $this->dbman->prepare($stmt, $sql);
    $result = $this->dbman->execute($stmt);
    while ($row = $this->dbman->fetchArray($result)) {
      $View = $row['view_name'];
      $table = $row['table_name'];
      $column = $row['column_name'];
      if (empty($this->schema['TABLE'][$table]) || empty($this->schema['TABLE'][$table][$column])) {
        $sql = "DROP VIEW IF EXISTS \"$View\";";
        $this->applyOrEchoOnce($sql);
      }
    }
    $result = $this->dbman->freeResult($result);
  }
 
  function dropColumnsFromTable($columns, $table)
  {
    if (empty($table) || empty($this->schema['TABLE'][$table])) {
      return;
    }
    foreach ($columns as $column) {
      if (empty($column)) {
        continue;
      }
      if (empty($this->schema['TABLE'][$table][$column])) {
        $sql = "ALTER TABLE \"$table\" DROP COLUMN \"$column\";";
        $this->applyOrEchoOnce($sql);
      }
    }
  }
 
 
  function getCurrSchema()
  {
    $this->ensureDriver();
    global $SysConf, $PG_CONN;
    $this->currSchema = array();
    $this->addInheritedRelations();
    $referencedSequencesInTableColumns = $this->addTables();
    if (!empty($SysConf['DBCONF']['user'])) {
      $viewowner = $SysConf['DBCONF']['user'];
    } elseif (!empty($PG_CONN)) {
      $viewowner = pg_parameter_status($PG_CONN, 'session_authorization');
    }
    if (empty($viewowner)) {
      throw new \Exception(
        "Unable to load schema views: could not determine the view owner. " .
        "Ensure \$SysConf['DBCONF']['user'] is set or a valid \$PG_CONN is available."
      );
    }
    $this->addViews($viewowner);
    $this->addSequences($referencedSequencesInTableColumns);
    $this->addConstraints();
    $this->addIndexes();
    unset($this->currSchema['TABLEID']);
    return $this->currSchema;
  }
 
  function addInheritedRelations()
  {
    $sql = "SELECT class.relname AS \"table\", daddy.relname AS inherits_from
      FROM pg_class AS class
      INNER JOIN pg_catalog.pg_inherits ON pg_inherits.inhrelid = class.oid
      INNER JOIN pg_class daddy ON pg_inherits.inhparent = daddy.oid";
    $this->dbman->prepare($stmt=__METHOD__, $sql);
    $res = $this->dbman->execute($stmt);
    $relations = array();
    while ($row=$this->dbman->fetchArray($res)) {
      $relations[$row['table']] = $row['inherits_from'];
    }
    $this->dbman->freeResult($res);
    $this->currSchema['INHERITS'] = $relations;
  }
 
  function addTables()
  {
    $referencedSequencesInTableColumns = array();
 
    $sql = "SELECT
    table_name AS \"table\", ordinal_position AS ordinal, column_name,
    udt_name AS type, character_maximum_length AS modifier,
    CASE is_nullable WHEN 'YES' THEN false WHEN 'NO' THEN true END AS \"notnull\",
    column_default AS \"default\",
    col_description(table_name::regclass, ordinal_position) AS description
  FROM information_schema.columns
  WHERE table_schema = 'public'
  ORDER BY table_name, ordinal_position;";
    $stmt = __METHOD__;
    $this->dbman->prepare($stmt, $sql);
    $result = $this->dbman->execute($stmt);
    while ($R = $this->dbman->fetchArray($result)) {
      $Table = $R['table'];
      $Column = $R['column_name'];
      if (array_key_exists($Table, $this->currSchema['INHERITS'])) {
        $this->currSchema['TABLEID'][$Table][$R['ordinal']] = $Column;
        continue;
      }
      $Type = $R['type'];
      if ($Type == 'bpchar') {
        $Type = "char";
      }
      if ($R['modifier'] > 0) {
        $Type .= '(' . $R['modifier'] . ')';
      }
      if (!empty($R['description'])) {
        $Desc = str_replace("'", "''", $R['description']);
      } else {
        $Desc = "";
      }
      $this->currSchema['TABLEID'][$Table][$R['ordinal']] = $Column;
      if (!empty($Desc)) {
        $this->currSchema['TABLE'][$Table][$Column]['DESC'] = "COMMENT ON COLUMN \"$Table\".\"$Column\" IS '$Desc'";
      } else {
        $this->currSchema['TABLE'][$Table][$Column]['DESC'] = "";
      }
      $this->currSchema['TABLE'][$Table][$Column]['ADD'] = "ALTER TABLE \"$Table\" ADD COLUMN \"$Column\" $Type";
      $this->currSchema['TABLE'][$Table][$Column]['ALTER'] = "ALTER TABLE \"$Table\"";
      $Alter = "ALTER COLUMN \"$Column\"";
      if ($R['notnull'] == 't') {
        $this->currSchema['TABLE'][$Table][$Column]['ALTER'] .= " $Alter SET NOT NULL";
      } else {
        $this->currSchema['TABLE'][$Table][$Column]['ALTER'] .= " $Alter DROP NOT NULL";
      }
      if ($R['default'] != '') {
        $R['default'] = preg_replace("/::bpchar/", "::char", $R['default']);
        $R['default'] = str_replace("public.", "", $R['default']);
        $this->currSchema['TABLE'][$Table][$Column]['ALTER'] .= ", $Alter SET DEFAULT " . $R['default'];
        $this->currSchema['TABLE'][$Table][$Column]['ADD'] .= " DEFAULT " . $R['default'];
 
        $rgx = "/nextval\‍('([a-z_]*)'.*\‍)/";
        $matches = array();
        if (preg_match($rgx, $R['default'], $matches)) {
           $sequence = $matches[1];
           $referencedSequencesInTableColumns[$sequence] = array("table" => $Table, "column" => $Column);
        }
      }
    }
    $this->dbman->freeResult($result);
 
    return $referencedSequencesInTableColumns;
  }
 
  function addViews($viewowner)
  {
    $sql = "SELECT viewname,definition FROM pg_views WHERE viewowner = $1";
    $stmt = __METHOD__;
    $this->dbman->prepare($stmt, $sql);
    $result = $this->dbman->execute($stmt, array($viewowner));
    while ($row = $this->dbman->fetchArray($result)) {
      $sql = "CREATE VIEW \"" . $row['viewname'] . "\" AS " . $row['definition'];
      $this->currSchema['VIEW'][$row['viewname']] = $sql;
    }
    $this->dbman->freeResult($result);
  }
 
  function addSequences($referencedSequencesInTableColumns)
  {
    $sql = "SELECT relname
      FROM pg_class
      WHERE relkind = 'S'
        AND relnamespace IN (
             SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema'
            )";
 
    $stmt = __METHOD__;
    $this->dbman->prepare($stmt, $sql);
    $result = $this->dbman->execute($stmt);
 
    while ($row = $this->dbman->fetchArray($result)) {
      $sequence = $row['relname'];
      if (empty($sequence)) {
         continue;
      }
 
      $sqlCreate = "CREATE SEQUENCE \"" . $sequence . "\"";
      $this->currSchema['SEQUENCE'][$sequence]['CREATE'] = $sqlCreate;
 
      if (array_key_exists($sequence, $referencedSequencesInTableColumns)) {
        $table = $referencedSequencesInTableColumns[$sequence]['table'];
        $column = $referencedSequencesInTableColumns[$sequence]['column'];
 
        $sqlUpdate = "SELECT setval('$sequence',(SELECT greatest(1,max($column)) val FROM $table))";
        $this->currSchema['SEQUENCE'][$sequence]['UPDATE'] = $sqlUpdate;
      }
    }
 
    $this->dbman->freeResult($result);
  }
 
  function addConstraints()
  {
    $sql = "SELECT c.conname AS constraint_name,
        CASE c.contype
          WHEN 'c' THEN 'CHECK'
          WHEN 'f' THEN 'FOREIGN KEY'
          WHEN 'p' THEN 'PRIMARY KEY'
          WHEN 'u' THEN 'UNIQUE'
        END AS type,
        CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,
        CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,
        t.relname AS table_name, array_to_string(c.conkey, ' ') AS constraint_key,
        CASE confupdtype
          WHEN 'a' THEN 'NO ACTION'
          WHEN 'r' THEN 'RESTRICT'
          WHEN 'c' THEN 'CASCADE'
          WHEN 'n' THEN 'SET NULL'
          WHEN 'd' THEN 'SET DEFAULT'
        END AS on_update,
        CASE confdeltype
          WHEN 'a' THEN 'NO ACTION'
          WHEN 'r' THEN 'RESTRICT'
          WHEN 'c' THEN 'CASCADE'
          WHEN 'n' THEN 'SET NULL'
          WHEN 'd' THEN 'SET DEFAULT' END AS on_delete,
        CASE confmatchtype
          WHEN 'u' THEN 'UNSPECIFIED'
          WHEN 'f' THEN 'FULL'
          WHEN 'p' THEN 'PARTIAL'
        END AS match_type,
        t2.relname AS references_table,
        array_to_string(c.confkey, ' ') AS fk_constraint_key
      FROM pg_constraint AS c
      LEFT JOIN pg_class AS t ON c.conrelid = t.oid
      INNER JOIN information_schema.tables AS tab ON t.relname = tab.table_name
      LEFT JOIN pg_class AS t2 ON c.confrelid = t2.oid
      ORDER BY constraint_name,table_name
    ";
    $stmt = __METHOD__;
    $this->dbman->prepare($stmt, $sql);
    $result = $this->dbman->execute($stmt);
    $Results = $this->dbman->fetchAll($result);
    $this->dbman->freeResult($result);
    /* Constraints use indexes into columns.  Covert those to column names. */
    for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
      $Key = "";
      $Keys = explode(" ", $Results[$i]['constraint_key']);
      foreach ($Keys as $K) {
        if (empty($K)) {
          continue;
        }
        if (!empty($Key)) {
          $Key .= ",";
        }
        if (!empty($this->currSchema['TABLEID'][$Results[$i]['table_name']][$K])) {
          $Key .= '"' . $this->currSchema['TABLEID'][$Results[$i]['table_name']][$K] . '"';
        }
      }
      $Results[$i]['constraint_key'] = $Key;
      $Key = "";
      if (!empty($Results[$i]['fk_constraint_key'])) {
        $Keys = explode(" ", $Results[$i]['fk_constraint_key']);
      } else {
        $Keys = [];
      }
      foreach ($Keys as $K) {
        if (empty($K)) {
          continue;
        }
        if (!empty($Key)) {
          $Key .= ",";
        }
        $Key .= '"' . $this->currSchema['TABLEID'][$Results[$i]['references_table']][$K] . '"';
      }
      $Results[$i]['fk_constraint_key'] = $Key;
    }
    /* Save the constraint */
    /* There are different types of constraints that must be stored in order */
    /* CONSTRAINT: PRIMARY KEY */
    for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
      if ($Results[$i]['type'] != 'PRIMARY KEY') {
        continue;
      }
      $sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
      $sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
      $sql .= " " . $Results[$i]['type'];
      $sql .= " (" . $Results[$i]['constraint_key'] . ")";
      if (!empty($Results[$i]['references_table'])) {
        $sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
        $sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
      }
      $sql .= ";";
      $this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
      $Results[$i]['processed'] = 1;
    }
    /* CONSTRAINT: UNIQUE */
    for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
      if ($Results[$i]['type'] != 'UNIQUE') {
        continue;
      }
      $sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
      $sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
      $sql .= " " . $Results[$i]['type'];
      $sql .= " (" . $Results[$i]['constraint_key'] . ")";
      if (!empty($Results[$i]['references_table'])) {
        $sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
        $sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
      }
      $sql .= ";";
      $this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
      $Results[$i]['processed'] = 1;
    }
 
    /* CONSTRAINT: FOREIGN KEY */
    for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
      if ($Results[$i]['type'] != 'FOREIGN KEY') {
        continue;
      }
      $sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
      $sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
      $sql .= " " . $Results[$i]['type'];
      $sql .= " (" . $Results[$i]['constraint_key'] . ")";
      if (!empty($Results[$i]['references_table'])) {
        $sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
        $sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
      }
 
      if (!empty($Results[$i]['on_update'])) {
        $sql .= " ON UPDATE " . $Results[$i]['on_update'];
      }
      if (!empty($Results[$i]['on_delete'])) {
        $sql .= " ON DELETE " . $Results[$i]['on_delete'];
      }
 
      $sql .= ";";
      $this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
      $Results[$i]['processed'] = 1;
    }
 
    /* CONSTRAINT: ALL OTHERS */
    for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
      if (!empty($Results[$i]['processed']) && $Results[$i]['processed'] == 1) {
        continue;
      }
 
      $sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
      $sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
      $sql .= " " . $Results[$i]['type'];
      $sql .= " (" . $Results[$i]['constraint_key'] . ")";
      if (!empty($Results[$i]['references_table'])) {
        $sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
        $sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
      }
      $sql .= ";";
      $this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
      $Results[$i]['processed'] = 1;
    }
  }
 
  function addIndexes()
  {
    $sql = "SELECT tablename AS \"table\", indexname AS index, indexdef AS define
      FROM pg_indexes
      INNER JOIN information_schema.tables ON table_name = tablename
        AND table_type = 'BASE TABLE'
        AND table_schema = 'public'
        AND schemaname = 'public'
      ORDER BY tablename,indexname;
    ";
    $stmt = __METHOD__;
    $this->dbman->prepare($stmt, $sql);
    $result = $this->dbman->execute($stmt);
    while ($row = $this->dbman->fetchArray($result)) {
      /* UNIQUE constraints also include indexes. */
      if (empty($this->currSchema['CONSTRAINT'][$row['index']])) {
        $this->currSchema['INDEX'][$row['table']][$row['index']] = str_replace("public.", "", $row['define']) . ";";
      }
    }
    $this->dbman->freeResult($result);
  }
 
  function addFunctions($schema)
  {
    // prosrc
    // proretset == setof
    $sql = "SELECT proname AS name,
        pronargs AS input_num,
        proargnames AS input_names,
        proargtypes AS input_type,
        proargmodes AS input_modes,
        proretset AS setof,
        prorettype AS output_type
      FROM pg_proc AS proc
      INNER JOIN pg_language AS lang ON proc.prolang = lang.oid
      WHERE lang.lanname = 'plpgsql'
      ORDER BY proname;";
    $stmt = __METHOD__;
    $this->dbman->prepare($stmt, $sql);
    $result = $this->dbman->execute($stmt);
    while ($row = $this->dbman->fetchArray($result)) {
      $sql = "CREATE or REPLACE function " . $row['proname'] . "()";
      $sql .= ' RETURNS ' . "TBD" . ' AS $$';
      $sql .= " " . $row['prosrc'];
      $schema['FUNCTION'][$row['proname']] = $sql;
    }
    $this->dbman->freeResult($result);
    return $schema;
  }
 
  function writeArrayEntries($fout, $key, $value, $varname)
  {
    $varname .= '["' . str_replace('"', '\"', $key) . '"]';
    if (!is_array($value)) {
      $value = str_replace('"', '\"', $value);
      fwrite($fout, "$varname = \"$value\";\n");
      return;
    }
    foreach ($value as $k => $v) {
      $this->writeArrayEntries($fout, $k, $v, $varname);
    }
    fwrite($fout, "\n");
  }
 
  function exportSchema($filename = NULL)
  {
    if (empty($filename)) {
      $filename = 'php://stdout';
    }
    $Schema = $this->getCurrSchema();
    $fout = fopen($filename, "w");
    if (!$fout) {
      return ("Failed to write to $filename\n");
    }
    global $Name;
    fwrite($fout, "<?php\n");
    fwrite($fout, "/* This file is generated by " . $Name . " */\n");
    fwrite($fout, "/* Do not manually edit this file */\n\n");
    fwrite($fout, '  $Schema=array();' . "\n\n");
    foreach ($Schema as $K1 => $V1) {
      $this->writeArrayEntries($fout, $K1, $V1, '  $Schema');
    }
    fclose($fout);
    return false;
  }
 
  function makeFunctions()
  {
    print "  Applying database functions\n";
    flush();
    /* *******************************************
     * uploadtree2path is a DB function that returns the non-artifact parents of an uploadtree_pk.
     * drop and recreate to change the return type.
     */
    $sql = 'drop function if exists uploadtree2path(integer);';
    $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . '.uploadtree2path.drop');
 
    $sql = '
    CREATE function uploadtree2path(uploadtree_pk_in int) returns setof uploadtree as $$
    DECLARE
      UTrec   uploadtree;
      UTpk    integer;
      sql     varchar;
    BEGIN
      UTpk := uploadtree_pk_in;
      WHILE UTpk > 0 LOOP
        sql := ' . "'" . 'select * from uploadtree where uploadtree_pk=' . "'" . ' || UTpk;
        execute sql into UTrec;
        IF ((UTrec.ufile_mode & (1<<28)) = 0) THEN RETURN NEXT UTrec; END IF;
        UTpk := UTrec.parent;
      END LOOP;
      RETURN;
    END;
    $$
    LANGUAGE plpgsql;
      ';
    $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . '.uploadtree2path.create');
 
    /*
     * getItemParent is a DB function that returns the non-artifact parent of an uploadtree_pk.
     * drop and recreate to change the return type.
     */
    $sql = 'drop function if exists getItemParent(integer);';
    $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . '.getItemParent.drop');
 
    $sql = '
    CREATE OR REPLACE FUNCTION getItemParent(itemId Integer) RETURNS Integer AS $$
    WITH RECURSIVE file_tree(uploadtree_pk, parent, jump, path, cycle) AS (
        SELECT ut.uploadtree_pk, ut.parent,
          true,
          ARRAY[ut.uploadtree_pk],
          false
        FROM uploadtree ut
        WHERE ut.uploadtree_pk = $1
      UNION ALL
        SELECT ut.uploadtree_pk, ut.parent,
          ut.ufile_mode & (1<<28) != 0,
          path || ut.uploadtree_pk,
        ut.uploadtree_pk = ANY(path)
        FROM uploadtree ut, file_tree ft
        WHERE ut.uploadtree_pk = ft.parent AND jump AND NOT cycle
      )
   SELECT uploadtree_pk from file_tree ft WHERE NOT jump
   $$
   LANGUAGE SQL
   STABLE
   RETURNS NULL ON NULL INPUT
      ';
    $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . '.getItemParent.create');
    return;
  }
 
  function applyInheritedRelations()
  {
    if (empty($this->schema['INHERITS'])) {
      return;
    }
    foreach ($this->schema['INHERITS'] as $table => $fromTable) {
      if (empty($table)) {
        continue;
      }
      if (!$this->dbman->existsTable($table) && $this->dbman->existsTable($fromTable)) {
        $sql = "CREATE TABLE \"$table\" () INHERITS (\"$fromTable\")";
        $this->applyOrEchoOnce($sql, $stmt = __METHOD__ . $table);
      }
    }
  }
 
  // MakeFunctions()
}
 
if (empty($dbManager) || !($dbManager instanceof DbManager)) {
  $logLevel = Logger::INFO;
  $logger = new Logger(__FILE__);
  $logger->pushHandler(new ErrorLogHandler(ErrorLogHandler::OPERATING_SYSTEM, $logLevel));
  $dbManager = new ModernDbManager($logger);
}
global $PG_CONN;
if (empty($PG_CONN)) {
  $sysconfdir = getenv('SYSCONFDIR');
  if (empty($sysconfdir)) {
    $sysconfdir = "/usr/local/etc/fossology";
  }
  $foConf = $sysconfdir . "/fossology.conf";
  if (file_exists($foConf)) {
    require_once(__DIR__ . "/common-db.php");
    $PG_CONN = DBconnect($sysconfdir);
    $GLOBALS['PG_CONN'] = $PG_CONN;
    if (!isset($GLOBALS['SysConf']) && isset($SysConf)) {
      $GLOBALS['SysConf'] = $SysConf;
    }
  }
}
/* simulate the old functions*/
$libschema = new fo_libschema($dbManager);
function ApplySchema($Filename = NULL, $Debug = false, $Catalog = 'fossology')
{
  global $libschema;
  return $libschema->applySchema($Filename, $Debug, $Catalog);
}
 
function GetSchema()
{
  global $libschema;
  return $libschema->getCurrSchema();
}
 
function ExportSchema($filename = NULL)
{
  global $libschema;
  return $libschema->exportSchema($filename);
}
 
function MakeFunctions($Debug)
{
  global $libschema;
  $libschema->makeFunctions($Debug);
}