library/sql_upgrade_fx.php

   1 <?php
   2 /**
   3 * Upgrading and patching functions of database.
   4 *
   5 * Functions to allow safe database modifications
   6 * during upgrading and patches.
   7 *
   8 * Copyright (C) 2008-2012 Rod Roark <rod@sunsetsystems.com>
   9 *
  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>.
  20 *
  21 * @package   OpenEMR
  22 * @author    Rod Roark <rod@sunsetsystems.com>
  23 * @author    Brady Miller <brady@sparmy.com>
  24 * @link      http://www.open-emr.org
  25 */
  26
  27 /**
  28 * Check if a Sql table exists.
  29 *
  30 * @param  string  $tblname  Sql Table Name
  31 * @return boolean           returns true if the sql table exists
  32 */
  33 function tableExists($tblname) {
  34   $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
  35   if (empty($row)) return false;
  36   return true;
  37 }
  38
  39 /**
  40 * Check if a Sql column exists in a selected table.
  41 *
  42 * @param  string  $tblname  Sql Table Name
  43 * @param  string  $colname  Sql Column Name
  44 * @return boolean           returns true if the sql column exists
  45 */
  46 function columnExists($tblname, $colname) {
  47   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  48   if (empty($row)) return false;
  49   return true;
  50 }
  51
  52 /**
  53 * Check if a Sql column has a certain type.
  54 *
  55 * @param  string  $tblname  Sql Table Name
  56 * @param  string  $colname  Sql Column Name
  57 * @param  string  $coltype  Sql Column Type
  58 * @return boolean           returns true if the sql column is of the specified type
  59 */
  60 function columnHasType($tblname, $colname, $coltype) {
  61   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  62   if (empty($row)) return true;
  63   return (strcasecmp($row['Type'], $coltype) == 0);
  64 }
  65
  66 /**
  67 * Check if a Sql row exists. (with one value)
  68 *
  69 * @param  string  $tblname  Sql Table Name
  70 * @param  string  $colname  Sql Column Name
  71 * @param  string  $value    Sql value
  72 * @return boolean           returns true if the sql row does exist
  73 */
  74 function tableHasRow($tblname, $colname, $value) {
  75   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  76     "$colname LIKE '$value'");
  77   return $row['count'] ? true : false;
  78 }
  79
  80 /**
  81 * Check if a Sql row exists. (with two values)
  82 *
  83 * @param  string  $tblname   Sql Table Name
  84 * @param  string  $colname   Sql Column Name 1
  85 * @param  string  $value     Sql value 1
  86 * @param  string  $colname2  Sql Column Name 2
  87 * @param  string  $value2    Sql value 2
  88 * @return boolean            returns true if the sql row does exist
  89 */
  90 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2) {
  91   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  92     "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
  93   return $row['count'] ? true : false;
  94 }
  95
  96 /**
  97 * Check if a Sql row exists. (with three values)
  98 *
  99 * @param  string  $tblname   Sql Table Name
 100 * @param  string  $colname   Sql Column Name 1
 101 * @param  string  $value     Sql value 1
 102 * @param  string  $colname2  Sql Column Name 2
 103 * @param  string  $value2    Sql value 2
 104 * @param  string  $colname3  Sql Column Name 3
 105 * @param  string  $value3    Sql value 3
 106 * @return boolean            returns true if the sql row does exist
 107 */
 108 function tableHasRow3D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3) {
 109   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 110     "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3'");
 111   return $row['count'] ? true : false;
 112 }
 113
 114 /**
 115 * Check if a Sql row exists. (with four values)
 116 *
 117 * @param  string  $tblname   Sql Table Name
 118 * @param  string  $colname   Sql Column Name 1
 119 * @param  string  $value     Sql value 1
 120 * @param  string  $colname2  Sql Column Name 2
 121 * @param  string  $value2    Sql value 2
 122 * @param  string  $colname3  Sql Column Name 3
 123 * @param  string  $value3    Sql value 3
 124 * @param  string  $colname4  Sql Column Name 4
 125 * @param  string  $value4    Sql value 4
 126 * @return boolean            returns true if the sql row does exist
 127 */
 128 function tableHasRow4D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3, $colname4, $value4) {
 129   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 130     "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3' AND $colname4 LIKE '$value4'");
 131   return $row['count'] ? true : false;
 132 }
 133
 134 /**
 135 * Check if a Sql table has a certain index/key.
 136 *
 137 * @param  string  $tblname  Sql Table Name
 138 * @param  string  $colname  Sql Index/Key
 139 * @return boolean           returns true if the sql tables has the specified index/key
 140 */
 141 function tableHasIndex($tblname, $colname) {
 142   $row = sqlQuery("SHOW INDEX FROM `$tblname` WHERE `Key_name` = '$colname'");
 143   return (empty($row)) ? false : true;
 144 }
 145
 146 /**
 147 * Upgrade or patch the database with a selected upgrade/patch file.
 148 *
 149 * The following "functions" within the selected file will be processed:
 150 *
 151 * #IfNotTable
 152 *   argument: table_name
 153 *   behavior: if the table_name does not exist,  the block will be executed
 154 *
 155 * #IfTable
 156 *   argument: table_name
 157 *   behavior: if the table_name does exist, the block will be executed
 158 *
 159 * #IfMissingColumn
 160 *   arguments: table_name colname
 161 *   behavior:  if the table exists but the column does not,  the block will be executed
 162 *
 163 * #IfNotColumnType
 164 *   arguments: table_name colname value
 165 *   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
 166 *
 167 * #IfNotRow
 168 *   arguments: table_name colname value
 169 *   behavior:  If the table table_name does not have a row where colname = value, the block will be executed.
 170 *
 171 * #IfNotRow2D
 172 *   arguments: table_name colname value colname2 value2
 173 *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
 174 *
 175 * #IfNotRow3D
 176 *   arguments: table_name colname value colname2 value2 colname3 value3
 177 *   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.
 178 *
 179 * #IfNotRow4D
 180 *   arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
 181 *   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.
 182 *
 183 * #IfNotRow2Dx2
 184 *   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.
 185 *   arguments: table_name colname value colname2 value2 colname3 value3
 186 *   behavior:  The block will be executed if both statements below are true:
 187 *              1) The table table_name does not have a row where colname = value AND colname2 = value2.
 188 *              2) The table table_name does not have a row where colname = value AND colname3 = value3.
 189 *
 190 * #IfRow2D
 191 *   arguments: table_name colname value colname2 value2
 192 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
 193 *
 194 * #IfIndex
 195 *   desc:      This function is most often used for dropping of indexes/keys.
 196 *   arguments: table_name colname
 197 *   behavior:  If the table and index exist the relevant statements are executed, otherwise not.
 198 *
 199 * #IfNotIndex
 200 *   desc:      This function will allow adding of indexes/keys.
 201 *   arguments: table_name colname
 202 *   behavior:  If the index does not exist, it will be created
 203 *
 204 * #EndIf
 205 *   all blocks are terminated with a #EndIf statement.
 206 *
 207 * @param  string  $filename  Sql upgrade/patch filename
 208 */
 209 function upgradeFromSqlFile($filename) {
 210   global $webserver_root;
 211
 212   flush();
 213   echo "<font color='green'>Processing $filename ...</font><br />\n";
 214
 215   $fullname = "$webserver_root/sql/$filename";
 216
 217   $fd = fopen($fullname, 'r');
 218   if ($fd == FALSE) {
 219     echo "ERROR.  Could not open '$fullname'.\n";
 220     flush();
 221     break;
 222   }
 223
 224   $query = "";
 225   $line = "";
 226   $skipping = false;
 227
 228   while (!feof ($fd)){
 229     $line = fgets($fd, 2048);
 230     $line = rtrim($line);
 231
 232     if (preg_match('/^\s*--/', $line)) continue;
 233     if ($line == "") continue;
 234
 235     if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
 236       $skipping = tableExists($matches[1]);
 237       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 238     }
 239     else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
 240       $skipping = ! tableExists($matches[1]);
 241       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 242     }
 243     else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 244       if (tableExists($matches[1])) {
 245         $skipping = columnExists($matches[1], $matches[2]);
 246       }
 247       else {
 248         // If no such table then the column is deemed not "missing".
 249         $skipping = true;
 250       }
 251       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 252     }
 253     else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 254       if (tableExists($matches[1])) {
 255         $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
 256       }
 257       else {
 258         // If no such table then the column type is deemed not "missing".
 259         $skipping = true;
 260       }
 261       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 262     }
 263     else if (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 264       if (tableExists($matches[1])) {
 265         // If no such index then skip.
 266         $skipping = !tableHasIndex($matches[1], $matches[2]);
 267       }
 268       else {
 269         // If no such table then skip.
 270         $skipping = true;
 271       }
 272       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 273     }
 274     else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 275       if (tableExists($matches[1])) {
 276         $skipping = tableHasIndex($matches[1], $matches[2]);
 277       }
 278       else {
 279         // If no such table then the index is deemed not "missing".
 280         $skipping = true;
 281       }
 282       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 283     }
 284     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 285       if (tableExists($matches[1])) {
 286         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 287       }
 288       else {
 289         // If no such table then the row is deemed not "missing".
 290         $skipping = true;
 291       }
 292       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 293     }
 294     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 295       if (tableExists($matches[1])) {
 296         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 297       }
 298       else {
 299         // If no such table then the row is deemed not "missing".
 300         $skipping = true;
 301       }
 302       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 303     }
 304     else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 305       if (tableExists($matches[1])) {
 306         $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 307       }
 308       else {
 309         // If no such table then the row is deemed not "missing".
 310         $skipping = true;
 311       }
 312       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 313     }
 314     else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 315       if (tableExists($matches[1])) {
 316         $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 317       }
 318       else {
 319         // If no such table then the row is deemed not "missing".
 320         $skipping = true;
 321       }
 322       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 323     }
 324     else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 325       if (tableExists($matches[1])) {
 326         // If either check exist, then will skip
 327         $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 328         $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 329         if ($firstCheck || $secondCheck) {
 330           $skipping = true;
 331         }
 332         else {
 333           $skipping = false;
 334         }
 335       }
 336       else {
 337         // If no such table then the row is deemed not "missing".
 338         $skipping = true;
 339       }
 340       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 341     }
 342     else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 343       if (tableExists($matches[1])) {
 344         $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
 345       }
 346       else {
 347         // If no such table then should skip.
 348         $skipping = true;
 349       }
 350       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 351     }
 352     else if (preg_match('/^#EndIf/', $line)) {
 353       $skipping = false;
 354     }
 355
 356     if (preg_match('/^\s*#/', $line)) continue;
 357     if ($skipping) continue;
 358
 359     $query = $query . $line;
 360     if (substr($query, -1) == ';') {
 361       $query = rtrim($query, ';');
 362       echo "$query<br />\n";
 363       if (!sqlStatement($query)) {
 364         echo "<font color='red'>The above statement failed: " .
 365           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 366       }
 367       $query = '';
 368     }
 369   }
 370   flush();
 371 } // end function
 372
 373 ?>