1<?php 2/*********************************************************** 3 Copyright (C) 2011-2012 Hewlett-Packard Development Company, L.P. 4 Copyright (C) 2017, Siemens AG 5 6 This library is free software; you can redistribute it and/or 7 modify it under the terms of the GNU Lesser General Public 8 License version 2.1 as published by the Free Software Foundation. 9 10 This library 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 GNU 13 Lesser General Public License for more details. 14 15 You should have received a copy of the GNU Lesser General Public License 16 along with this library; if not, write to the Free Software Foundation, Inc.0 17 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 18***********************************************************/ 19 20/** 21 * \file 22 * \brief This file contains common database functions. 23 **/ 24 25 26/** 27 * \brief Connect to database engine. 28 * This is a no-op if $PG_CONN already has a value. 29 * 30 * \param string $sysconfdir fossology configuration directory (location of 31 * Db.conf) 32 * \param string $options an optional list of attributes for 33 * connecting to the database. E.g.: 34 * `"dbname=text host=text user=text password=text"` 35 * \param bool $exitOnFail true (default) to print error and call exit on 36 * failure false to return $PG_CONN === false on failure 37 * 38 * If $options is empty, then connection parameters will be read from Db.conf. 39 * 40 * \return 41 * Success: $PG_CONN, the postgres connection object \n 42 * Failure: Error message is printed 43 **/ 44function DBconnect($sysconfdir, $options="", $exitOnFail=true) 45{ 46 global $PG_CONN; 47 48 if (! empty($PG_CONN)) { 49 return $PG_CONN; 50 } 51 52 $path="$sysconfdir/Db.conf"; 53 if (empty($options)) { 54 $dbConf = file_get_contents($path); 55 if ($exitOnFail && (false === $dbConf)) { 56 $text = _("Could not connect to FOSSology database."); 57 echo "<h2>$text</h2>"; 58 echo _('permission denied for configuration file'); 59 exit(); 60 } 61 if (false === $dbConf) { 62 $PG_CONN = false; 63 return; 64 } 65 $options = $dbConf; 66 } 67 if (! empty($options)) { 68 $PG_CONN = pg_connect(str_replace(";", " ", $options)); 69 } 70 71 if (! empty($PG_CONN)) { 72 /* success */ 73 return $PG_CONN; 74 } 75 76 if ($exitOnFail) { 77 $text = _("Could not connect to FOSSology database."); 78 echo "<h2>$text</h2>"; 79 exit(); 80 } 81 $PG_CONN = false; 82} 83 84 85/** 86 \brief Retrieve a single database record. 87 88 This function does a: 89 \code 90 "SELECT * from $Table $Where limit 1" 91 \endcode 92 and returns the result as an associative array. 93 94 \param string $Table Table name 95 \param string $Where SQL where clause e.g. `"where uploadtree_pk=2"`. 96 Though a WHERE clause is the typical use, $Where 97 can really be any options following the sql tablename. 98 \return 99 Associative array for this record. 100 May be empty if no record found. 101 **/ 102function GetSingleRec($Table, $Where="") 103{ 104 global $PG_CONN; 105 106 $sql = "SELECT * from $Table $Where limit 1"; 107 $result = pg_query($PG_CONN, $sql); 108 DBCheckResult($result, $sql, __FILE__, __LINE__); 109 110 $row = pg_fetch_assoc($result); 111 pg_free_result($result); 112 return $row; 113} 114 115 116/** 117 * \brief Create an associative array by using table 118 * rows to source the key/value pairs. 119 * 120 * \param string $Table tablename 121 * \param string $KeyCol Key column name in $Table 122 * \param string $ValCol Value column name in $Table 123 * \param string $Where SQL where clause (optional) 124 * This can really be any clause following the 125 * table name in the sql 126 * 127 * \return 128 * Array[Key] = Val for each row in the table. 129 * May be empty if no table rows or Where results 130 * in no rows. 131 **/ 132function DB2KeyValArray($Table, $KeyCol, $ValCol, $Where="") 133{ 134 global $PG_CONN; 135 136 $ResArray = array(); 137 138 $sql = "SELECT $KeyCol, $ValCol from $Table $Where"; 139 $result = pg_query($PG_CONN, $sql); 140 DBCheckResult($result, $sql, __FILE__, __LINE__); 141 142 while ($row = pg_fetch_assoc($result)) { 143 $ResArray[$row[$KeyCol]] = $row[$ValCol]; 144 } 145 return $ResArray; 146} 147 148/** 149 * \brief Create an array by using table 150 * rows to source the values. 151 * 152 * \param string $Table tablename 153 * \param string $ValCol Value column name in $Table 154 * \param string $Uniq Sort out duplicates 155 * \param string $Where SQL where clause (optional) 156 * This can really be any clause following the 157 * table name in the sql 158 * 159 * \return 160 * Array[Key] = Val for each row in the table. 161 * May be empty if no table rows or Where results 162 * in no rows. 163 **/ 164function DB2ValArray($Table, $ValCol, $Uniq=false, $Where="") 165{ 166 global $PG_CONN; 167 168 $ResArray = array(); 169 170 if ($Uniq) { 171 $sql = "SELECT DISTINCT $ValCol from $Table $Where"; 172 } else { 173 $sql = "SELECT $ValCol from $Table $Where"; 174 } 175 $result = pg_query($PG_CONN, $sql); 176 DBCheckResult($result, $sql, __FILE__, __LINE__); 177 178 $i = 0; 179 while ($row = pg_fetch_assoc($result)) { 180 $ResArray[$i] = $row[$ValCol]; 181 ++ $i; 182 } 183 return $ResArray; 184} 185 186 187/** 188 * \brief Check the postgres result for unexpected errors. 189 * If found, treat them as fatal. 190 * 191 * \param $result command result object 192 * \param string $sql SQL command (optional) 193 * \param string $filenm File name (__FILE__) 194 * \param int $lineno Line number of the caller (__LINE__) 195 * 196 * \return None, prints error, sql and line number, then exits(1) 197 **/ 198function DBCheckResult($result, $sql, $filenm, $lineno) 199{ 200 global $PG_CONN; 201 202 if (! $result) { 203 echo "<hr>File: $filenm, Line number: $lineno<br>"; 204 if (pg_connection_status($PG_CONN) === PGSQL_CONNECTION_OK) { 205 echo pg_last_error($PG_CONN); 206 } else { 207 echo "FATAL: DB connection lost."; 208 } 209 echo "<br> $sql"; 210 debugbacktrace(); 211 echo "<hr>"; 212 exit(1); 213 } 214} 215 216 217/** 218 * \brief Check if table exists. 219 * \note This is postgresql specific. 220 * 221 * \param string $tableName Table to check 222 * 223 * \return 1 if table exists, 0 if not. 224**/ 225function DB_TableExists($tableName) 226{ 227 global $PG_CONN; 228 global $SysConf; 229 230 $sql = "select count(*) as count from information_schema.tables where " 231 . "table_catalog='{$SysConf['DBCONF']['dbname']}' and table_name='$tableName'"; 232 $result = pg_query($PG_CONN, $sql); 233 DBCheckResult($result, $sql, __FILE__, __LINE__); 234 $row = pg_fetch_assoc($result); 235 $count = $row['count']; 236 pg_free_result($result); 237 return($count); 238} /* DB_TableExists() */ 239 240 241/** 242 * \brief Check if a column exists. 243 * \note This is postgresql specific. 244 * 245 * \param string $tableName Table to check in 246 * \param string $colName Column to check 247 * \param string $DBName Database name, default "fossology" 248 * 249 * \return 1 if column exists, 0 if not. 250**/ 251function DB_ColExists($tableName, $colName, $DBName='fossology') 252{ 253 global $PG_CONN; 254 255 $sql = "select count(*) as count from information_schema.columns where " 256 . "table_catalog='$DBName' and table_name='$tableName' and column_name='$colName'"; 257 $result = pg_query($PG_CONN, $sql); 258 DBCheckResult($result, $sql, __FILE__, __LINE__); 259 $row = pg_fetch_assoc($result); 260 $count = $row['count']; 261 pg_free_result($result); 262 return($count); 263} /* DB_ColExists() */ 264 265 266/** 267 * \brief Check if a constraint exists. 268 * \note This is postgresql specific. 269 * 270 * \param string $ConstraintName Constraint to check 271 * \param string $DBName Database name, default "fossology" 272 * 273 * \return True if constraint exists, False if not. 274**/ 275function DB_ConstraintExists($ConstraintName, $DBName='fossology') 276{ 277 global $PG_CONN; 278 279 $sql = "select count(*) as count from information_schema.table_constraints " 280 . "where table_catalog='$DBName' and constraint_name='$ConstraintName' limit 1"; 281 $result = pg_query($PG_CONN, $sql); 282 DBCheckResult($result, $sql, __FILE__, __LINE__); 283 $row = pg_fetch_assoc($result); 284 $count = $row['count']; 285 pg_free_result($result); 286 if ($count == 1) { 287 return true; 288 } 289 return False; 290} /* DB_ColExists() */ 291 292 293/** 294 * \brief Get last sequence number. 295 * 296 * This is typically used to get the primary key of a newly inserted record. 297 * This must be called immediately after the insert. 298 * 299 * \param string $seqname Sequence Name of key just added 300 * \param string $tablename Table containing $seqname 301 * 302 * \return Current sequence number (i.e. the primary key of the rec just added) 303**/ 304function GetLastSeq($seqname, $tablename) 305{ 306 global $PG_CONN; 307 308 $sql = "SELECT currval('$seqname') as mykey FROM $tablename"; 309 $result = pg_query($PG_CONN, $sql); 310 DBCheckResult($result, $sql, __FILE__, __LINE__); 311 $row = pg_fetch_assoc($result); 312 $mykey = $row["mykey"]; 313 pg_free_result($result); 314 return($mykey); 315} 316