portal/patient/fwk/libs/verysimple/DB/Reflection/DBTable.php

   1 <?php
   2 /** @package    verysimple::DB::Reflection */
   3
   4 /**
   5  * import supporting libraries
   6  */
   7 require_once("DBColumn.php");
   8 require_once("DBConstraint.php");
   9 require_once("DBSet.php");
  10 require_once("DBKey.php");
  11
  12 /**
  13  * DBTable is an object representation of a MySQL Table
  14  *
  15  * @package verysimple::DB::Reflection
  16  * @author Jason Hinkle
  17  * @copyright 1997-2007 VerySimple, Inc.
  18  * @license http://www.gnu.org/licenses/lgpl.html LGPL
  19  * @version 1.0
  20  */
  21 class DBTable
  22 {
  23     public $Schema;
  24     public $Name;
  25     public $Engine;
  26     public $Comment;
  27     public $DefaultCharacterSet;
  28     public $Columns;
  29     public $ColumnPrefix;
  30     public $PrimaryKeys;
  31     public $ForeignKeys;
  32     public $Constraints;
  33     public $Sets;
  34     public $IsView = false;
  35     static $NUMERIC_TABLE_PREFIX = 'TBL_';
  36     static $NUMERIC_COLUMN_PREFIX = 'COL_';
  37
  38     /**
  39      * Instantiate new DBTable
  40      *
  41      * @access public
  42      * @param DBSchema $schema
  43      * @return Array $row array that is result from "show tables" statement
  44      */
  45     function __construct($schema, $row)
  46     {
  47         $this->Schema = $schema;
  48         $this->Name = $row ["Tables_in_" . $this->Schema->Name];
  49         $this->Columns = array ();
  50         $this->PrimaryKeys = array ();
  51         $this->ForeignKeys = array ();
  52         $this->Constraints = array ();
  53         $this->Sets = array ();
  54
  55         $this->LoadColumns();
  56         $this->DiscoverColumnPrefix();
  57     }
  58
  59     /**
  60      * Returns the number of columns involved in the primary key
  61      *
  62      * @return number
  63      */
  64     function NumberOfPrimaryKeyColumns()
  65     {
  66         return count($this->PrimaryKeys);
  67     }
  68
  69     /**
  70      * Returns name of the primary key
  71      * TODO: If there are multiple keys, this is no accurate.
  72      * Only returns the first key found
  73      *
  74      * @access public
  75      * @param bool $remove_prefix
  76      * @return string
  77      */
  78     function GetPrimaryKeyName($remove_prefix = true)
  79     {
  80         foreach ($this->PrimaryKeys as $key) {
  81             return ($remove_prefix) ? $this->RemovePrefix($key->KeyColumn) : $key->KeyColumn;
  82         }
  83
  84         // views don't technically have a primary key but we will return the first column if anybody asks
  85         if ($this->IsView) {
  86             return $this->GetColumnNameByIndex(0, $remove_prefix);
  87         }
  88     }
  89
  90     /**
  91      * Returns the name of the column at the given index
  92      *
  93      * @access public
  94      * @param int $index
  95      *          (zero based)
  96      * @param bool $remove_prefix
  97      * @return string
  98      */
  99     function GetColumnNameByIndex($index, $remove_prefix = true)
 100     {
 101         $count = 0;
 102         foreach ($this->Columns as $column) {
 103             if ($count == $index) {
 104                 return ($remove_prefix) ? $column->NameWithoutPrefix : $column->Name;
 105             }
 106         }
 107
 108         throw new Exception('Index out of bounds');
 109     }
 110
 111     /**
 112      * Returns true if the primary key for this table is an auto_increment
 113      * TODO: Only checks the first key if there are multiple primary keys
 114      *
 115      * @access public
 116      * @return bool
 117      */
 118     function PrimaryKeyIsAutoIncrement()
 119     {
 120         $pk = $this->GetPrimaryKeyName(false);
 121         return $pk && $this->Columns [$pk]->Extra == "auto_increment";
 122     }
 123
 124     /**
 125      * Returns name of the first varchar field which could be used as a "label"
 126      *
 127      * @access public
 128      * @return string
 129      */
 130     function GetDescriptorName($remove_prefix = true)
 131     {
 132         foreach ($this->Columns as $column) {
 133             if ($column->Type == "varchar") {
 134                 return ($remove_prefix) ? $this->RemovePrefix($column->Name) : $column->Name;
 135             }
 136         }
 137
 138         // give up because there are no varchars in this table
 139         return $this->GetPrimaryKeyName($remove_prefix);
 140     }
 141
 142     /**
 143      * Inspects all columns to see if there is a common prefix in the format: XXX_
 144      *
 145      * @access private
 146      */
 147     private function DiscoverColumnPrefix()
 148     {
 149         $prev_prefix = "";
 150         $has_prefix = true;
 151         foreach ($this->Columns as $column) {
 152             $curr_prefix = substr($column->Name, 0, strpos($column->Name, "_") + 1);
 153
 154             if ($prev_prefix == "") {
 155                 // first time through the loop
 156                 $prev_prefix = $curr_prefix ? $curr_prefix : "#NONE#";
 157             } elseif ($prev_prefix != $curr_prefix) {
 158                 $has_prefix = false;
 159             }
 160         }
 161
 162         if ($has_prefix) {
 163             // set the table column prefix property
 164             $this->ColumnPrefix = $curr_prefix;
 165
 166             // update the columns to reflect the prefix as well
 167             foreach ($this->Columns as $column) {
 168                 $column->NameWithoutPrefix = substr($column->Name, strlen($curr_prefix));
 169             }
 170         }
 171
 172         // if a column begins with a numeric character then prepend a string to prevent generated code errors
 173         if (self::$NUMERIC_COLUMN_PREFIX) {
 174             foreach ($this->Columns as $column) {
 175                 if (is_numeric(substr($column->NameWithoutPrefix, 0, 1))) {
 176                     $column->NameWithoutPrefix = self::$NUMERIC_COLUMN_PREFIX . $column->NameWithoutPrefix;
 177                 }
 178             }
 179         }
 180     }
 181
 182     /**
 183      * Returns a name that is acceptable to be used as the "object" name in generated code
 184      */
 185     public function GetObjectName()
 186     {
 187         if (is_numeric(substr($this->Name, 0, 1))) {
 188             return self::$NUMERIC_TABLE_PREFIX . $this->Name;
 189         }
 190
 191         return $this->Name;
 192     }
 193
 194     /**
 195      * Given a column name, removes the prefix
 196      *
 197      * @access private
 198      */
 199     public function RemovePrefix($name)
 200     {
 201         // print "remove prefix $name: " . $this->ColumnPrefix . "<br>";
 202         return substr($name, strlen($this->ColumnPrefix));
 203     }
 204
 205     /**
 206      * Inspects the current table and loads all Columns
 207      *
 208      * @access private
 209      */
 210     private function LoadColumns()
 211     {
 212         // get the colums
 213         $sql = "describe `" . $this->Name . "`";
 214
 215         $rs = $this->Schema->Server->Connection->Select($sql);
 216
 217         while ($row = $this->Schema->Server->Connection->Next($rs)) {
 218             $this->Columns [$row ["Field"]] = new DBColumn($this, $row);
 219         }
 220
 221         $this->Schema->Server->Connection->Release($rs);
 222     }
 223
 224     /**
 225      * Load the keys and constraints for this table and populate the sets for
 226      * all tables on which this table is dependents
 227      *
 228      * @access public
 229      */
 230     public function LoadKeys()
 231     {
 232
 233         // get the keys and constraints
 234         $sql = "show create table `" . $this->Name . "`";
 235
 236         $create_table = "";
 237
 238         $rs = $this->Schema->Server->Connection->Select($sql);
 239
 240         if ($row = $this->Schema->Server->Connection->Next($rs)) {
 241             if (isset($row ["Create Table"])) {
 242                 $create_table = $row ["Create Table"];
 243             } else if (isset($row ["Create View"])) {
 244                 $this->IsView = true;
 245                 $create_table = $row ["Create View"];
 246
 247                 // treat the 1st column in a view as the primary key
 248                 $this->Columns [$this->GetColumnNameByIndex(0, false)]->Key = 'PRI';
 249             } else {
 250                 throw new Exception("Unknown Table Type");
 251             }
 252         }
 253
 254         $this->Schema->Server->Connection->Release($rs);
 255
 256         $lines = explode("\n", $create_table);
 257
 258         foreach ($lines as $line) {
 259             $line = trim($line);
 260             if (substr($line, 0, 11) == "PRIMARY KEY") {
 261                 preg_match_all("/`(\w+)`/", $line, $matches, PREG_PATTERN_ORDER);
 262                 // print "<pre>"; print_r($matches); die(); // DEBUG
 263                 $this->PrimaryKeys [$matches [1] [0]] = new DBKey($this, "PRIMARY KEY", $matches [0] [0]);
 264             } elseif (substr($line, 0, 3) == "KEY") {
 265                 preg_match_all("/`(\w+)`/", $line, $matches, PREG_PATTERN_ORDER);
 266                 // print "<pre>"; print_r($matches); die(); // DEBUG
 267                 $this->ForeignKeys [$matches [1] [0]] = new DBKey($this, $matches [1] [0], $matches [1] [1]);
 268
 269                 // Add keys to the column for convenience
 270                 $this->Columns [$matches [1] [1]]->Keys [] = $matches [1] [0];
 271             } elseif (substr($line, 0, 10) == "CONSTRAINT") {
 272                 preg_match_all("/`(\w+)`/", $line, $matches, PREG_PATTERN_ORDER);
 273                 // print "<pre>"; print_r($matches); die(); // DEBUG
 274                 $this->Constraints [$matches [1] [0]] = new DBConstraint($this, $matches [1]);
 275
 276                 // the set is basically the reverse of the constraint, but we want to add it to the
 277                 // constraining table so we don't have to do reverse-lookup looking for child relationships
 278                 $this->Schema->Tables [$matches [1] [2]]->Sets [$matches [1] [0]] = new DBSet($this, $matches [1]);
 279
 280                 // print "<pre>##########################\r\n" . print_r($matches,1) . "\r\n##########################\r\n";
 281
 282                 // Add constraints to the column for convenience
 283                 $this->Columns [$matches [1] [1]]->Constraints [] = $matches [1] [0];
 284             } elseif (strstr($line, "COMMENT ")) {
 285                 // TODO: this is pretty fragile... ?
 286                 // table comments and column comments are seemingly differentiated by "COMMENT=" vs "COMMENT "
 287                 $parts = explode("`", $line);
 288                 $column = $parts [1];
 289                 $comment = strstr($line, "COMMENT ");
 290                 $comment = substr($comment, 9, strlen($comment) - 11);
 291                 $comment = str_replace("''", "'", $comment);
 292                 $this->Columns [$column]->Comment = $comment;
 293
 294                 if ($this->Columns [$column]->Default == "" && substr($comment, 0, 8) == "default=") {
 295                     $this->Columns [$column]->Default = substr($comment, 9, strlen($comment) - 10);
 296                 }
 297
 298                 // print "<pre>" . $column . "=" . htmlspecialchars( $this->Columns[$column]->Default );
 299             }
 300
 301             // TODO: look for COMMENT
 302         }
 303     }
 304 }