1<?php
2/***********************************************************
3 Copyright (C) 2018 Siemens AG
4 Author: Gaurav Mishra <mishra.gaurav@siemens.com>
5
6 This program is free software; you can redistribute it and/or
7 modify it under the terms of the GNU General Public License
8 version 2 as published by the Free Software Foundation.
9
10 This program is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13 GNU General Public License for more details.
14
15 You should have received a copy of the GNU General Public License along
16 with this program; if not, write to the Free Software Foundation, Inc.,
17 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
18 ***********************************************************/
19
20/**
21 * @file
22 * @brief Migrate DB from release 3.3.0 to 3.4.0 with new constraints
23 */
24
25/**
26 * @brief Delete all rows from the table which does not have reference.
27 *
28 * For foreign key constraints.
29 * @param DbManager $dbManager DB Manager to use
30 * @param string $tableToClean    Dirty table
31 * @param string $foreignKey      Foreign key of dirty table
32 * @param string $referenceTable  Table to be referenced
33 * @param string $referenceKey    Reference key of referenced table
34 * @param boolean $dryRun         Set TRUE to make a dry run
35 * @return integer Number of entries deleted
36 */
37function cleanTableForeign($dbManager, $tableToClean, $foreignKey, $referenceTable, $referenceKey, $dryRun)
38{
39  if($dbManager == NULL){
40    echo "No connection object passed!\n";
41    return false;
42  }
43  if(!(DB_TableExists($tableToClean) == 1 && DB_TableExists($referenceTable) == 1)) {
44    // Table does not exists (migrating from old version)
45    echo "Table $tableToClean or $referenceTable does not exists, not cleaning!\n";
46    return 0;
47  }
48
49  $sql = "";
50  if($dryRun) {
51    $sql = "
52SELECT count(*) AS count FROM $tableToClean
53WHERE NOT EXISTS (
54  SELECT 1 FROM $referenceTable
55  WHERE $tableToClean.$foreignKey = $referenceTable.$referenceKey
56);
57";
58  } else {
59    $sql = "
60WITH deleted AS (
61  DELETE FROM $tableToClean
62  WHERE NOT EXISTS (
63    SELECT 1 FROM $referenceTable
64    WHERE $tableToClean.$foreignKey = $referenceTable.$referenceKey
65  ) RETURNING 1
66) SELECT count(*) AS count FROM deleted;
67";
68  }
69  return intval($dbManager->getSingleRow($sql, [],
70    "cleanTableForeign." . $tableToClean . $foreignKey . "." . $referenceTable . $referenceKey)['count']);
71}
72
73/**
74 * @brief Remove redundant rows based on values in columnNames.
75 *
76 * For unique constraints.
77 * @param DbManager $dbManager
78 * @param string $tableName
79 * @param string $primaryKey
80 * @param string[] $columnNames
81 * @param boolean $dryRun
82 * @return integer Number of entries deleted
83 */
84function cleanWithUnique($dbManager, $tableName, $primaryKey, $columnNames, $dryRun)
85{
86  if($dbManager == NULL){
87    echo "No connection object passed!\n";
88    return false;
89  }
90  if(DB_TableExists($tableName) != 1) {
91    // Table does not exists (migrating from old version)
92    echo "Table $tableName does not exists, not cleaning!\n";
93    return 0;
94  }
95
96  $sql = "";
97  if($dryRun) {
98    $sql = "
99SELECT count(*) AS count
100FROM (
101  SELECT $primaryKey, ROW_NUMBER() OVER (
102    PARTITION BY " . implode(",", $columnNames) .
103    " ORDER BY $primaryKey
104  ) AS rnum
105  FROM $tableName
106) a
107WHERE a.rnum > 1;
108";
109  } else {
110    $sql = "
111WITH deleted AS (
112  DELETE FROM $tableName
113  WHERE $primaryKey IN (
114    SELECT $primaryKey
115    FROM (
116      SELECT $primaryKey, ROW_NUMBER() OVER (
117        PARTITION BY " . implode(",", $columnNames) .
118        " ORDER BY $primaryKey
119      ) AS rnum
120      FROM $tableName
121    ) a
122    WHERE a.rnum > 1
123  ) RETURNING 1
124) SELECT count(*) AS count FROM deleted;
125";
126  }
127  return intval($dbManager->getSingleRow($sql, [],
128    "cleanWithUnique." . $tableName . "." . implode(".", $columnNames))['count']);
129}
130
131/**
132 * Migration from FOSSology 3.3.0 to 3.4.0
133 * @param DbManager $dbManager
134 * @param boolean $dryRun
135 */
136function Migrate_33_34($dbManager, $dryRun)
137{
138  if(DB_ConstraintExists('group_user_member_user_group_ukey', $GLOBALS["SysConf"]["DBCONF"]["dbname"])) {
139    // The last constraint also cleared, no need for re-run
140    return;
141  }
142  try {
143    echo "*** Cleaning tables for new constraints ***\n";
144    $count = 0;
145    $tableMap = [
146      ["author", "agent_fk", "agent", "agent_pk"],
147      ["author", "pfile_fk", "pfile", "pfile_pk"],
148      ["bucket_container", "bucket_fk", "bucket_def", "bucket_pk"],
149      ["bucket_file", "bucket_fk", "bucket_def", "bucket_pk"],
150      ["bucket_file", "pfile_fk", "pfile", "pfile_pk"],
151      ["copyright", "agent_fk", "agent", "agent_pk"],
152      ["copyright_decision", "pfile_fk", "pfile", "pfile_pk"],
153      ["ecc", "agent_fk", "agent", "agent_pk"],
154      ["ecc", "pfile_fk", "pfile", "pfile_pk"],
155      ["ecc_decision", "pfile_fk", "pfile", "pfile_pk"],
156      ["highlight_keyword", "pfile_fk", "pfile", "pfile_pk"],
157      ["keyword", "agent_fk", "agent", "agent_pk"],
158      ["keyword", "pfile_fk", "pfile", "pfile_pk"],
159      ["keyword_decision", "pfile_fk", "pfile", "pfile_pk"],
160      ["pkg_deb_req", "pkg_fk", "pkg_deb", "pkg_pk"],
161      ["pkg_rpm_req", "pkg_fk", "pkg_rpm", "pkg_pk"],
162      ["report_cache", "report_cache_uploadfk", "upload", "upload_pk"],
163      ["report_info", "upload_fk", "upload", "upload_pk"],
164      ["reportgen", "upload_fk", "upload", "upload_pk"],
165      ["upload", "pfile_fk", "pfile", "pfile_pk"],
166      ["upload_clearing_license", "upload_fk", "upload", "upload_pk"]
167    ];
168    $dbManager->queryOnce("BEGIN;");
169
170    // Foreign key constraints
171    foreach ($tableMap as $mapRow) {
172      $count += cleanTableForeign($dbManager, $mapRow[0], $mapRow[1], $mapRow[2], $mapRow[3], $dryRun);
173    }
174
175    // Primary constraints
176    $count += cleanWithUnique($dbManager, "obligation_ref", "ctid", ["ob_pk"], $dryRun);
177    $count += cleanWithUnique($dbManager, "report_info", "ctid", ["ri_pk"], $dryRun);
178
179    // Unique constraints
180    $count += cleanWithUnique($dbManager, "obligation_ref", "ob_pk", ["ob_md5"], $dryRun);
181    $count += cleanWithUnique($dbManager, "group_user_member", "group_user_member_pk",
182      ["user_fk", "group_fk"], $dryRun);
183    $dbManager->queryOnce("COMMIT;");
184    echo "Removed $count rows from tables with new constraints\n";
185  } catch (Exception $e) {
186    echo "Something went wrong. Try running postinstall again!\n";
187    $dbManager->queryOnce("ROLLBACK;");
188  }
189}
190