create_schema.php

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