tools/create_schema.php

   1 <?php
   2
   3 // creates schema file output
   4 // $Id$
   5
   6 ini_set('include_path', ini_get('include_path') . ':' . dirname(dirname(getcwd())));
   7 require_once('ISSO/Register.php');
   8
   9 $db = BSApp::LoadModule('DbMySQL');
  10 $input = BSApp::LoadModule('Input');
  11
  12 if (empty($_REQUEST['submit']))
  13 {
  14 ?>
  15
  16 <form action="create_schema.php" method="post" name="params">
  17
  18 <div>Database: <input name="database" type="text" size="35" /></div>
  19 <div>Username: <input name="username" type="text" size="35" value="mysql" /></div>
  20 <div>Password: <input name="password" type="password" size="35" /></div>
  21 <div>Server: <input name="server" type="text" size="35" value="localhost" /></div>
  22
  23 <br />
  24
  25 <div>Database engine: <input name="engine" type="radio" value="mysql" checked="checked" /> MySQL <input name="engine" type="radio" value="postgresql" /> PostgreSQL</div>
  26 <div>Array variable: <input name="variable" type="text" size="35" value="schema" /></div>
  27 <div>Encase names with ticks: <input name="encase" type="checkbox" value="1" /> Yes</div>
  28 <div>Add TABLE_PREFIX: <input name="prefix" type="checkbox" checked="checked" value="1" /> Yes</div>
  29
  30 <input name="submit" type="submit" value="Create Schema" />
  31
  32 </form>
  33
  34 <?php
  35 }
  36 else
  37 {
  38         $db->connect($input->in['server'], $input->in['username'], $input->in['password'], $input->in['database'], false);
  39
  40         $t = ($input->in['encase'] ? '`' : '');
  41         $prefix = ($input->in['prefix'] ? '" . TABLE_PREFIX . "' : '');
  42
  43         $tables = $db->query("SHOW TABLES");
  44         while ($table = $db->fetchArray($tables, false))
  45         {
  46                 $table = $table[0];
  47                 $list = array();
  48                 $keys = array();
  49
  50                 $build = "CREATE TABLE $t$prefix$table$t\n(\n";
  51
  52                 $indexes = $db->query("SHOW INDEX FROM $table");
  53                 while ($index = $db->fetchArray($indexes))
  54                 {
  55                         array_walk($index, 'trim');
  56
  57                         if ($index['Sub_part'] AND $input->in['engine'] == 'postgresql')
  58                         {
  59                                 $subpart = " ($index[Sub_part])";
  60                         }
  61
  62                         if ($index['Key_name'] == 'PRIMARY')
  63                         {
  64                                 $keys['primary'][] = "$t$index[Column_name]$t$subpart";
  65                         }
  66                         else if ($index['Index_type'] == 'FULLTEXT')
  67                         {
  68                                 $keys['fulltext']["$index[Key_name]"][] = "$t$index[Column_name]$t$subpart";
  69                         }
  70                         else if ($index['Index_type'] == 'BTREE' AND $index['Non_unique'] == 0)
  71                         {
  72                                 $keys['unique']["$index[Key_name]"][] = "$t$index[Column_name]$t$subpart";
  73                         }
  74                         else if ($index['Index_type'] == 'BTREE' AND $index['Non_unique'] == 1)
  75                         {
  76                                 $keys['std']["$index[Key_name]"][] = "$t$index[Column_name]$t$subpart";
  77                         }
  78                         // we should never get here :-)
  79                         else
  80                         {
  81                                 print_r($index);
  82                                 exit;
  83                         }
  84                 }
  85
  86                 $fields = $db->query("DESCRIBE $table");
  87                 while ($field = $db->fetchArray($fields))
  88                 {
  89                         array_walk($field, 'trim');
  90
  91                         if (preg_match('#^(tinyint|smallint|bigint|int)\(([0-9]+)\)( unsigned)?#i', $field['Type'], $matches))
  92                         {
  93                                 if ($matches[2] == 1)
  94                                 {
  95                                         $field['Type'] = 'bool';
  96                                 }
  97                                 else if ($matches[2] < 10)
  98                                 {
  99                                         $field['Type'] = 'smallint';
 100                                 }
 101                                 else if ($matches[2] > 12)
 102                                 {
 103                                         $field['Type'] = 'bigint';
 104                                 }
 105                                 else
 106                                 {
 107                                         $field['Type'] = 'int';
 108                                 }
 109
 110                                 if ($input->in['engine'] == 'mysql' AND $field['Type'] != 'bool')
 111                                 {
 112                                         $field['Type'] .= $matches[3];
 113                                 }
 114                                 else if ($input->in['engine'] == 'postgresql')
 115                                 {
 116                                         if (preg_match('#AUTO_INCREMENT#i', $field['Extra']))
 117                                         {
 118                                                 $field['Type'] = 'SERIAL';
 119                                                 $field['Extra'] = '';
 120                                         }
 121                                 }
 122                         }
 123                         else if (preg_match('#^mediumtext|longtext$#i', $field['Type']))
 124                         {
 125                                 $field['Type'] = 'text';
 126                         }
 127                         else if (preg_match('#.+?blob$#i', $field['Type']))
 128                         {
 129                                 $field['Type'] = 'blob';
 130                                 if ($input->in['engine'] == 'postgresql')
 131                                 {
 132                                         $field['Type'] = 'bytea';
 133                                 }
 134                         }
 135
 136                         // quote default values where appropriate
 137                         if (!empty($field['Default']))
 138                         {
 139                                 if ($field['Default'] != 'CURRENT_TIMESTAMP')
 140                                 {
 141                                         $field['Default'] = "'$field[Default]'";
 142                                 }
 143                         }
 144
 145                         $list[] = "\t$t$field[Field]$t " . $field['Type'] . " " . ($field['Null'] == 'YES' ? "NULL" : "NOT NULL") . ($field['Extra'] != '' ? " " . strtoupper($field['Extra']) : "") . (!empty($field['Default']) ? " DEFAULT $field[Default]" : "");
 146                 }
 147
 148                 $build .= implode(",\n", $list);
 149
 150                 if ($keys['primary'])
 151                 {
 152                         $build .= ",\n\tPRIMARY KEY (" . implode(', ', $keys['primary']) . ")";
 153                 }
 154                 if ($keys['fulltext'])
 155                 {
 156                         foreach ($keys['fulltext'] AS $name => $columns)
 157                         {
 158                                 $build .= ",\n\tFULLTEXT KEY $t$name$t (" . implode(', ', $columns) . ")";
 159                         }
 160                 }
 161                 if ($keys['unique'])
 162                 {
 163                         foreach ($keys['unique'] AS $name => $columns)
 164                         {
 165                                 $build .= ",\n\tUNIQUE KEY $t$name$t (" . implode(', ', $columns) . ")";
 166                         }
 167                 }
 168                 if ($keys['std'])
 169                 {
 170                         foreach ($keys['std'] AS $name => $columns)
 171                         {
 172                                 $build .= ",\n\tKEY $t$name$t (" . implode(', ', $columns) . ")";
 173                         }
 174                 }
 175
 176                 $build .= "\n);";
 177
 178                 $queries["$table"] = $build;
 179         }
 180
 181         if ($input->in['variable'] != '')
 182         {
 183                 foreach ($queries AS $table => $query)
 184                 {
 185                         $output[] = "\${$input->in['variable']}['$table'] = \"\n$query\";";
 186                 }
 187         }
 188         else
 189         {
 190                 $output = $queries;
 191         }
 192
 193         $output = implode("\n\n", $output);
 194
 195         ?>
 196
 197 <textarea name="output" rows="50" cols="60" style="width: 100%"><?= $output ?></textarea>
 198
 199         <?php
 200 }
 201
 202 ?>