docs/tools/create_schema.php

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