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