2 // Copyright (C) 2008-2011 Rod Roark <rod@sunsetsystems.com>
4 // This program is free software; you can redistribute it and/or
5 // modify it under the terms of the GNU General Public License
6 // as published by the Free Software Foundation; either version 2
7 // of the License, or (at your option) any later version.
9 // Functions to allow safe database and global modifications
10 // during upgrading and patches
13 function tableExists($tblname) {
14 $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
15 if (empty($row)) return false;
19 function columnExists($tblname, $colname) {
20 $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
21 if (empty($row)) return false;
25 function columnHasType($tblname, $colname, $coltype) {
26 $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
27 if (empty($row)) return true;
28 return (strcasecmp($row['Type'], $coltype) == 0);
31 function tableHasRow($tblname, $colname, $value) {
32 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
33 "$colname LIKE '$value'");
34 return $row['count'] ?
true : false;
37 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2) {
38 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
39 "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
40 return $row['count'] ?
true : false;
43 function tableHasRow3D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3) {
44 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
45 "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3'");
46 return $row['count'] ?
true : false;
49 function tableHasRow4D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3, $colname4, $value4) {
50 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
51 "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3' AND $colname4 LIKE '$value4'");
52 return $row['count'] ?
true : false;
55 function tableHasIndex($tblname, $colname) {
56 $row = sqlQuery("SHOW INDEX FROM `$tblname` WHERE `Key_name` = '$colname'");
57 return (empty($row)) ?
false : true;
60 function upgradeFromSqlFile($filename) {
61 global $webserver_root;
64 echo "<font color='green'>Processing $filename ...</font><br />\n";
66 $fullname = "$webserver_root/sql/$filename";
68 $fd = fopen($fullname, 'r');
70 echo "ERROR. Could not open '$fullname'.\n";
80 $line = fgets($fd, 2048);
83 if (preg_match('/^\s*--/', $line)) continue;
84 if ($line == "") continue;
86 if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
87 $skipping = tableExists($matches[1]);
88 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
90 else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
91 $skipping = ! tableExists($matches[1]);
92 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
94 else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
95 if (tableExists($matches[1])) {
96 $skipping = columnExists($matches[1], $matches[2]);
99 // If no such table then the column is deemed not "missing".
102 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
104 else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
105 if (tableExists($matches[1])) {
106 $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
109 // If no such table then the column type is deemed not "missing".
112 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
114 else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
115 if (tableExists($matches[1])) {
116 $skipping = tableHasIndex($matches[1], $matches[2]);
119 // If no such table then the index is deemed not "missing".
122 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
124 else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
125 if (tableExists($matches[1])) {
126 $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
129 // If no such table then the row is deemed not "missing".
132 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
134 else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
135 if (tableExists($matches[1])) {
136 $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
139 // If no such table then the row is deemed not "missing".
142 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
144 else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
145 if (tableExists($matches[1])) {
146 $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
149 // If no such table then the row is deemed not "missing".
152 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
154 else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
155 if (tableExists($matches[1])) {
156 $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
159 // If no such table then the row is deemed not "missing".
162 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
164 else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
165 if (tableExists($matches[1])) {
166 // If either check exist, then will skip
167 $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
168 $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
169 if ($firstCheck ||
$secondCheck) {
177 // If no such table then the row is deemed not "missing".
180 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
182 else if (preg_match('/^#EndIf/', $line)) {
186 if (preg_match('/^\s*#/', $line)) continue;
187 if ($skipping) continue;
189 $query = $query . $line;
190 if (substr($query, -1) == ';') {
191 $query = rtrim($query, ';');
192 echo "$query<br />\n";
193 if (!sqlStatement($query)) {
194 echo "<font color='red'>The above statement failed: " .
195 mysql_error() . "<br />Upgrading will continue.<br /></font>\n";