3 // creates schema file output
6 require_once('../isso-21x/kernel.php');
9 $isso->setApplication('Schema Creator');
10 $isso->setAppPath(getcwd());
12 define('ISSO_DB_LAYER', 'db_mysql');
13 $isso->load('db_mysql', 'db', true
);
15 if (empty($_REQUEST['submit']))
19 <form action
="create_schema.php" method
="post" name
="params">
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
>
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
>
33 <input name
="submit" type
="submit" value
="Create Schema" />
41 $db->connect($isso->in
['server'], $isso->in
['username'], $isso->in
['password'], $isso->in
['database'], false
);
43 $t = ($isso->in
['encase'] ?
'`' : '');
44 $prefix = ($isso->in
['prefix'] ?
'" . TABLE_PREFIX . "' : '');
46 $tables = $db->query("SHOW TABLES");
47 while ($table = $db->fetch_array($tables, false
))
53 $build = "CREATE TABLE $t$prefix$table$t\n(\n";
55 $indexes = $db->query("SHOW INDEX FROM $table");
56 while ($index = $db->fetch_array($indexes))
58 array_walk($index, 'trim');
60 if ($index['Sub_part'] AND $isso->in
['engine'] == 'postgresql')
62 $subpart = " ($index[Sub_part])";
65 if ($index['Key_name'] == 'PRIMARY')
67 $keys['primary'][] = "$t$index[Column_name]$t$subpart";
69 else if ($index['Index_type'] == 'FULLTEXT')
71 $keys['fulltext']["$index[Key_name]"][] = "$t$index[Column_name]$t$subpart";
73 else if ($index['Index_type'] == 'BTREE' AND $index['Non_unique'] == 0)
75 $keys['unique']["$index[Key_name]"][] = "$t$index[Column_name]$t$subpart";
77 else if ($index['Index_type'] == 'BTREE' AND $index['Non_unique'] == 1)
79 $keys['std']["$index[Key_name]"][] = "$t$index[Column_name]$t$subpart";
81 // we should never get here :-)
89 $fields = $db->query("DESCRIBE $table");
90 while ($field = $db->fetch_array($fields))
92 array_walk($field, 'trim');
94 if (preg_match('#^(tinyint|smallint|bigint|int)\(([0-9]+)\)( unsigned)?#i', $field['Type'], $matches))
98 $field['Type'] = 'bool';
100 else if ($matches[2] < 10)
102 $field['Type'] = 'smallint';
104 else if ($matches[2] > 12)
106 $field['Type'] = 'bigint';
110 $field['Type'] = 'int';
113 if ($isso->in
['engine'] == 'mysql' AND $field['Type'] != 'bool')
115 $field['Type'] .= $matches[3];
117 else if ($isso->in
['engine'] == 'postgresql')
119 if (preg_match('#AUTO_INCREMENT#i', $field['Extra']))
121 $field['Type'] = 'SERIAL';
122 $field['Extra'] = '';
126 else if (preg_match('#^mediumtext|longtext$#i', $field['Type']))
128 $field['Type'] = 'text';
130 else if (preg_match('#.+?blob$#i', $field['Type']))
132 $field['Type'] = 'blob';
133 if ($isso->in
['engine'] == 'postgresql')
135 $field['Type'] = 'bytea';
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]'" : "");
142 $build .= implode(",\n", $list);
144 if ($keys['primary'])
146 $build .= ",\n\tPRIMARY KEY (" . implode(', ', $keys['primary']) . ")";
148 if ($keys['fulltext'])
150 foreach ($keys['fulltext'] AS $name => $columns)
152 $build .= ",\n\tFULLTEXT KEY $t$name$t (" . implode(', ', $columns) . ")";
157 foreach ($keys['unique'] AS $name => $columns)
159 $build .= ",\n\tUNIQUE KEY $t$name$t (" . implode(', ', $columns) . ")";
164 foreach ($keys['std'] AS $name => $columns)
166 $build .= ",\n\tKEY $t$name$t (" . implode(', ', $columns) . ")";
172 $queries["$table"] = $build;
175 if ($isso->in
['variable'] != '')
177 foreach ($queries AS $table => $query)
179 $output[] = "\${$isso->in['variable']}['$table'] = \"\n$query\";";
187 $output = implode("\n\n", $output);
191 <textarea name
="output" rows
="50" cols
="60" style
="width: 100%"><?
= $output ?
></textarea
>