Another fix to upgrade clickoptions in windows
[openemr.git] / library / sql_upgrade_fx.php
blobdc4262e49d1035dcd2fa3e946bd23d1f11838215
1 <?php
2 /**
3 * Upgrading and patching functions of database.
5 * Functions to allow safe database modifications
6 * during upgrading and patches.
8 * Copyright (C) 2008-2012 Rod Roark <rod@sunsetsystems.com>
10 * LICENSE: This program is free software; you can redistribute it and/or
11 * modify it under the terms of the GNU General Public License
12 * as published by the Free Software Foundation; either version 2
13 * of the License, or (at your option) any later version.
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>.
21 * @package OpenEMR
22 * @author Rod Roark <rod@sunsetsystems.com>
23 * @author Brady Miller <brady@sparmy.com>
24 * @author Teny <teny@zhservices.com>
25 * @link http://www.open-emr.org
28 /**
29 * Check if a Sql table exists.
31 * @param string $tblname Sql Table Name
32 * @return boolean returns true if the sql table exists
34 function tableExists($tblname) {
35 $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
36 if (empty($row)) return false;
37 return true;
40 /**
41 * Check if a Sql column exists in a selected table.
43 * @param string $tblname Sql Table Name
44 * @param string $colname Sql Column Name
45 * @return boolean returns true if the sql column exists
47 function columnExists($tblname, $colname) {
48 $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
49 if (empty($row)) return false;
50 return true;
53 /**
54 * Check if a Sql column has a certain type.
56 * @param string $tblname Sql Table Name
57 * @param string $colname Sql Column Name
58 * @param string $coltype Sql Column Type
59 * @return boolean returns true if the sql column is of the specified type
61 function columnHasType($tblname, $colname, $coltype) {
62 $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
63 if (empty($row)) return true;
64 return (strcasecmp($row['Type'], $coltype) == 0);
67 /**
68 * Check if a Sql row exists. (with one value)
70 * @param string $tblname Sql Table Name
71 * @param string $colname Sql Column Name
72 * @param string $value Sql value
73 * @return boolean returns true if the sql row does exist
75 function tableHasRow($tblname, $colname, $value) {
76 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
77 "$colname LIKE '$value'");
78 return $row['count'] ? true : false;
81 /**
82 * Check if a Sql row exists. (with two values)
84 * @param string $tblname Sql Table Name
85 * @param string $colname Sql Column Name 1
86 * @param string $value Sql value 1
87 * @param string $colname2 Sql Column Name 2
88 * @param string $value2 Sql value 2
89 * @return boolean returns true if the sql row does exist
91 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2) {
92 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
93 "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
94 return $row['count'] ? true : false;
97 /**
98 * Check if a Sql row exists. (with three values)
100 * @param string $tblname Sql Table Name
101 * @param string $colname Sql Column Name 1
102 * @param string $value Sql value 1
103 * @param string $colname2 Sql Column Name 2
104 * @param string $value2 Sql value 2
105 * @param string $colname3 Sql Column Name 3
106 * @param string $value3 Sql value 3
107 * @return boolean returns true if the sql row does exist
109 function tableHasRow3D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3) {
110 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
111 "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3'");
112 return $row['count'] ? true : false;
116 * Check if a Sql row exists. (with four values)
118 * @param string $tblname Sql Table Name
119 * @param string $colname Sql Column Name 1
120 * @param string $value Sql value 1
121 * @param string $colname2 Sql Column Name 2
122 * @param string $value2 Sql value 2
123 * @param string $colname3 Sql Column Name 3
124 * @param string $value3 Sql value 3
125 * @param string $colname4 Sql Column Name 4
126 * @param string $value4 Sql value 4
127 * @return boolean returns true if the sql row does exist
129 function tableHasRow4D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3, $colname4, $value4) {
130 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
131 "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3' AND $colname4 LIKE '$value4'");
132 return $row['count'] ? true : false;
136 * Check if a Sql table has a certain index/key.
138 * @param string $tblname Sql Table Name
139 * @param string $colname Sql Index/Key
140 * @return boolean returns true if the sql tables has the specified index/key
142 function tableHasIndex($tblname, $colname) {
143 $row = sqlQuery("SHOW INDEX FROM `$tblname` WHERE `Key_name` = '$colname'");
144 return (empty($row)) ? false : true;
148 * Function to migrate the Clickoptions settings (if exist) from the codebase into the database.
149 * Note this function is only run once in the sql upgrade script (from 4.1.1 to 4.1.2) if the
150 * issue_types sql table does not exist.
152 function clickOptionsMigrate() {
153 // If the clickoptions.txt file exist, then import it.
154 if (file_exists(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt")) {
155 $file_handle = fopen(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt", "rb");
156 $seq = 10;
157 $prev = '';
158 echo "Importing clickoption setting<br>";
159 while (!feof($file_handle) ) {
160 $line_of_text = fgets($file_handle);
161 if (preg_match('/^#/', $line_of_text)) continue;
162 if ($line_of_text == "") continue;
163 $parts = explode('::', $line_of_text);
164 $parts[0] = trim(str_replace("\r\n","",$parts[0]));
165 $parts[1] = trim(str_replace("\r\n","",$parts[1]));
166 if ($parts[0] != $prev) {
167 $sql1 = "INSERT INTO list_options (`list_id`,`option_id`,`title`) VALUES (?,?,?)";
168 SqlStatement($sql1, array('lists',$parts[0].'_issue_list',ucwords(str_replace("_"," ",$parts[0])).' Issue List') );
169 $seq = 10;
171 $sql2 = "INSERT INTO list_options (`list_id`,`option_id`,`title`,`seq`) VALUES (?,?,?,?)";
172 SqlStatement($sql2, array($parts[0].'_issue_list', $parts[1], $parts[1], $seq) );
173 $seq = $seq + 10;
174 $prev = $parts[0];
176 fclose($file_handle);
181 * Upgrade or patch the database with a selected upgrade/patch file.
183 * The following "functions" within the selected file will be processed:
185 * #IfNotTable
186 * argument: table_name
187 * behavior: if the table_name does not exist, the block will be executed
189 * #IfTable
190 * argument: table_name
191 * behavior: if the table_name does exist, the block will be executed
193 * #IfMissingColumn
194 * arguments: table_name colname
195 * behavior: if the table exists but the column does not, the block will be executed
197 * #IfNotColumnType
198 * arguments: table_name colname value
199 * behavior: If the table table_name does not have a column colname with a data type equal to value, then the block will be executed
201 * #IfNotRow
202 * arguments: table_name colname value
203 * behavior: If the table table_name does not have a row where colname = value, the block will be executed.
205 * #IfNotRow2D
206 * arguments: table_name colname value colname2 value2
207 * behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
209 * #IfNotRow3D
210 * arguments: table_name colname value colname2 value2 colname3 value3
211 * behavior: If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
213 * #IfNotRow4D
214 * arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
215 * behavior: If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3 AND colname4 = value4, the block will be executed.
217 * #IfNotRow2Dx2
218 * desc: This is a very specialized function to allow adding items to the list_options table to avoid both redundant option_id and title in each element.
219 * arguments: table_name colname value colname2 value2 colname3 value3
220 * behavior: The block will be executed if both statements below are true:
221 * 1) The table table_name does not have a row where colname = value AND colname2 = value2.
222 * 2) The table table_name does not have a row where colname = value AND colname3 = value3.
224 * #IfRow2D
225 * arguments: table_name colname value colname2 value2
226 * behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
228 * #IfIndex
229 * desc: This function is most often used for dropping of indexes/keys.
230 * arguments: table_name colname
231 * behavior: If the table and index exist the relevant statements are executed, otherwise not.
233 * #IfNotIndex
234 * desc: This function will allow adding of indexes/keys.
235 * arguments: table_name colname
236 * behavior: If the index does not exist, it will be created
238 * #IfNotMigrateClickOptions
239 * Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
241 * #EndIf
242 * all blocks are terminated with a #EndIf statement.
244 * @param string $filename Sql upgrade/patch filename
246 function upgradeFromSqlFile($filename) {
247 global $webserver_root;
249 flush();
250 echo "<font color='green'>Processing $filename ...</font><br />\n";
252 $fullname = "$webserver_root/sql/$filename";
254 $fd = fopen($fullname, 'r');
255 if ($fd == FALSE) {
256 echo "ERROR. Could not open '$fullname'.\n";
257 flush();
258 break;
261 $query = "";
262 $line = "";
263 $skipping = false;
265 while (!feof ($fd)){
266 $line = fgets($fd, 2048);
267 $line = rtrim($line);
269 if (preg_match('/^\s*--/', $line)) continue;
270 if ($line == "") continue;
272 if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
273 $skipping = tableExists($matches[1]);
274 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
276 else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
277 $skipping = ! tableExists($matches[1]);
278 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
280 else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
281 if (tableExists($matches[1])) {
282 $skipping = columnExists($matches[1], $matches[2]);
284 else {
285 // If no such table then the column is deemed not "missing".
286 $skipping = true;
288 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
290 else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
291 if (tableExists($matches[1])) {
292 $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
294 else {
295 // If no such table then the column type is deemed not "missing".
296 $skipping = true;
298 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
300 else if (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
301 if (tableExists($matches[1])) {
302 // If no such index then skip.
303 $skipping = !tableHasIndex($matches[1], $matches[2]);
305 else {
306 // If no such table then skip.
307 $skipping = true;
309 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
311 else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
312 if (tableExists($matches[1])) {
313 $skipping = tableHasIndex($matches[1], $matches[2]);
315 else {
316 // If no such table then the index is deemed not "missing".
317 $skipping = true;
319 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
321 else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
322 if (tableExists($matches[1])) {
323 $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
325 else {
326 // If no such table then the row is deemed not "missing".
327 $skipping = true;
329 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
331 else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
332 if (tableExists($matches[1])) {
333 $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
335 else {
336 // If no such table then the row is deemed not "missing".
337 $skipping = true;
339 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
341 else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
342 if (tableExists($matches[1])) {
343 $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
345 else {
346 // If no such table then the row is deemed not "missing".
347 $skipping = true;
349 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
351 else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
352 if (tableExists($matches[1])) {
353 $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
355 else {
356 // If no such table then the row is deemed not "missing".
357 $skipping = true;
359 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
361 else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
362 if (tableExists($matches[1])) {
363 // If either check exist, then will skip
364 $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
365 $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
366 if ($firstCheck || $secondCheck) {
367 $skipping = true;
369 else {
370 $skipping = false;
373 else {
374 // If no such table then the row is deemed not "missing".
375 $skipping = true;
377 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
379 else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
380 if (tableExists($matches[1])) {
381 $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
383 else {
384 // If no such table then should skip.
385 $skipping = true;
387 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
389 else if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
390 if (tableExists("issue_types")) {
391 $skipping = true;
393 else {
394 // Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
395 clickOptionsMigrate();
396 $skipping = false;
398 if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
400 else if (preg_match('/^#EndIf/', $line)) {
401 $skipping = false;
404 if (preg_match('/^\s*#/', $line)) continue;
405 if ($skipping) continue;
407 $query = $query . $line;
408 if (substr($query, -1) == ';') {
409 $query = rtrim($query, ';');
410 echo "$query<br />\n";
411 if (!sqlStatement($query)) {
412 echo "<font color='red'>The above statement failed: " .
413 mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
415 $query = '';
418 flush();
419 } // end function