table_cnt = 0;
$this->server = $aServer;
$this->db = $aDbName;
$this->owner = $anOwner;
$this->pw = $aPassword;
// connect only if all parameters are set
if (!($this->server == "" || $this->db == "" || $this->owner == "")) {
$this->connect();
}
}
function connect () {
if ($this->server == "" || $this->db == "" || $this->owner == "" ) {
echo "Could not connect to database. Some arguments are missing.
";
echo "server = '" . $this->server . "'; db = '" . $this->db . "'; owner = '" . $this->owner . "';
";
die();
}
else {
$this->con = db_connect($this->server,$this->owner,$this->pw);
if ($this->con) {
$this->selectDb();
}
}
# echo $this->server . "
";
# echo $this->db . "
";
# echo $this->owner . "
";
# echo $this->pw . "
";
}
function selectDb () {
if (!db_select_db($this->db)) {
echo "Error while selecting database '" . $this->db . "'
";
echo "server = '" . $this->server . "'; db = '" . $this->db . "'; owner = '" . $this->owner . "';
";
die();
}
}
// adds a table object to the database object
// the table name must be unique
function addTable($aTableName){
if ($this->getNumberOfTables() == 0 || !in_array($aTableName, $this->getTableNames())) {
$this->table[$this->table_cnt] = new database_table($aTableName);
$this->table_cnt++;
return (strval($this->table_cnt)-1);
}
else {
echo "Table '" . $aTableName . "' already in database!
";
}
}
// adds a column object to the table object (table index is known)
// the column name must be unique within its table object
function addColumnToTableAtIndex($aColumnName, $aTableIndex) {
$columnIndex = $this->table[$aTableIndex]->addColumn($aColumnName);
return $columnIndex;
}
// adds a column object to the table object (table index is unknown)
// the column name must be unique within its table object
function addKeyToTableAtIndex($aKeyName, $aColumnName, $isPrimary, $aTableIndex) {
$keyIndex = $this->table[$aTableIndex]->addKey($aKeyName, $aColumnName, $isPrimary);
return $keyIndex;
}
// adds a constraint object to the table object (table index is unknown)
function addConstraintToTableAtIndex ($conSymbol, $tableIndex, $conColumn, $conTableName, $conTableColumnName, $conOnDelete, $conOnUpdate) {
$this->table[$tableIndex]->addConstraint($conSymbol, $conColumn, $conTableName, $conTableColumnName, $conOnDelete, $conOnUpdate);
}
// returns an array of all table names in this database
function getTableNames() {
$tables = array();
for ($i=0; $i<$this->table_cnt; $i++) {
$tables[$i] = $this->table[$i]->getName();
}
if ($this->table_cnt>0) {
return $tables;
}
else {
return false;
}
}
// return a table object (index is known)
function getTableAtIndex($anIndex) {
return $this->table[$anIndex];
}
// return a table object (index is unknown)
function getTableWithName($aName) {
return $this->table[$this->getTableIndex($aName)];
}
// return the index of a table object
function getTableIndex($aTableName) {
for ($i=0; $i<$this->table_cnt; $i++) {
if ($aTableName == $this->table[$i]->getName()) {
return $i;
}
}
return false;
}
// returns the number of tables in this database object
function getNumberOfTables () {
return $this->table_cnt;
}
// sets the engine type (f.e. 'InnoDb')
function setTableType($anIndex, $aType) {
$this->table[$anIndex]->setType($aType);
}
// sets the column data type for a column objext in a table object (both indexes unknown)
function setColumnTypeInColumnAtIndexInTableAtIndex($aColumnType, $aColumnIndex, $aTableIndex) {
$this->table[$aTableIndex]->setColumnTypeAtIndex($aColumnType, $aColumnIndex);
}
// sets the column attributes (f.e. "NOT NULL")
function setColumnAttributesInColumnAtIndexInTableAtIndex($someColumnAttributes, $aColumnIndex, $aTableIndex) {
$this->table[$aTableIndex]->setColumnAttributesAtIndex($someColumnAttributes, $aColumnIndex);
}
// sets the column default (f.e. "DEFAULT ''")
function setColumnDefaultInColumnAtIndexInTableAtIndex($someColumnDefault, $aColumnIndex, $aTableIndex) {
$this->table[$aTableIndex]->setColumnDefaultAtIndex($someColumnDefault, $aColumnIndex);
}
// displays the whole database structure
function displayDatabase() {
print_r($this->table);
}
// imports the structure of an actual database specified by server, db, owner, pw
function mysqlDbToDatabase() {
if (!$this->con) {
$this->connect();
}
// collects all table names
$sql = "SHOW TABLES FROM " . $this->db;
$res = db_query($sql);
$cnt = 0;
while($table = db_fetch_row($res)){
$inst_tables[$cnt] = $table[0];
$cnt++;
}
$message = array();
$createTable = array();
// generates SHOW CREATE TABLE sqls for each table name
for ($i=0; $imysqlCleanDumpArray($createTableArray);
$this->mysqlDumpToDatabaseFromArray($cleanDump);
}
// compares this database object with another (remote) one
// returns sql statements that adds or changes tables/columns/constraints/keys
// so that this database has all fields of the remote database as well
function compareDbWithRemoteDb($remoteDb, &$messageArray) {
$sql = "";
$message = "";
$sqlCreateTable = "";
$messageCreateTable = "";
$sqlColumn = "";
$messageColumn = "";
$sqlKey = "";
$messageKey = "";
$sqlConstraint = "";
$messageConstraint = "";
$splitString = "::::";
for ($i=0; $i < $remoteDb->getNumberOfTables(); $i++) {
$t = $remoteDb->getTableAtIndex($i);
$table = $t->getName();
// check tables
// check if all tables from the remote DB are in the local DB
// if a table is missing, generate a SQL containg a CREATE TABLE statement
if (!in_array($table, $this->getTableNames())) {
$messageCreateTable .= "Table '" . $table . "' is missing.";
$messageCreateTable .= $splitString;
$sqlCreateTable .= $t->getMysql();
}
//table exists, now checking columns
else {
// check columns
// check if all columns of a table from the remote DB are in the local DB
// if a column is missing, generate a SQL containg a ALTER TABLE statement
//php4
$table_i = $t;
$table_i_nr_cols = $table_i->getNumberOfColumns();
$tableWithName_table = $this->getTableWithName($table);
for ($j=0; $j < $table_i_nr_cols; $j++) {
$c = $table_i->getColumnAtIndex($j);
$column = $c->getName();
if (!in_array($column, $tableWithName_table->getColumnNames())) {
#PHP5 if (!in_array($column, $this->getTableWithName($table)->getColumnNames())) {
$messageColumn .= "Column '".$column."' is missing.";
$messageColumn .= $splitString;
$sqlColumn .= "ALTER TABLE " . $table . " ADD COLUMN";
$sqlColumn .= $c->getMysql();
}
//column exists, now checking type and attributes
else {
// check if type (varchar etc) AND attributes are equal
$colWithName_column = $tableWithName_table->getColumnWithName($column);
if (trim(strtoupper($colWithName_column->getType())) != trim(strtoupper($c->getType()))
|| trim(strtoupper($colWithName_column->getAttributes())) != trim(strtoupper($c->getAttributes()))) {
#php5 if (trim(strtoupper($this->getTableWithName($table)->getColumnWithName($column)->getType())) != trim(strtoupper($remoteDb->getTableAtIndex($i)->getColumnAtIndex($j)->getType()))
#php5 || trim(strtoupper($this->getTableWithName($table)->getColumnWithName($column)->getAttributes())) != trim(strtoupper($remoteDb->getTableAtIndex($i)->getColumnAtIndex($j)->getAttributes()))) {
$messageColumn .= "Column '".$column."' has incorrect type or attributes.";
$messageColumn .= $splitString;
$sqlColumn .= "ALTER TABLE " . $table . " CHANGE COLUMN " . $column;
$sqlColumn .= $c->getMysql();
}
/*
* checking DEFAULTs doesn't work properly
*
*
if (trim(strtoupper($this->getTableWithName($table)->getColumnWithName($column)->getDefaultValue())) != trim(strtoupper($remoteDb->getTableAtIndex($i)->getColumnAtIndex($j)->getDefaultValue()))) {
$messageColumn .= "Column '".$remoteDb->getTableAtIndex($i)->getColumnAtIndex($j)->getName()."' has incorrect default value.";
$messageColumn .= $splitString;
#echo trim(strtoupper($this->getTableWithName($table)->getColumnWithName($column)->getDefaultValue())) . "
";
#echo trim(strtoupper($remoteDb->getTableAtIndex($i)->getColumnAtIndex($j)->getDefaultValue())) . "
";
$sqlColumn .= "ALTER TABLE " . $table . " ALTER COLUMN " . $column;
$newDef = trim(strtoupper($remoteDb->getTableAtIndex($i)->getColumnAtIndex($j)->getDefaultValue()));
if ($newDef) {
$sqlColumn .= " SET DEFAULT " . trim(strtoupper($remoteDb->getTableAtIndex($i)->getColumnAtIndex($j)->getDefaultValue())) . "\n\n";
}
else {
$sqlColumn .= " DROP DEFAULT\n\n";
}
}
*/
}
}
// check keys
for ($j=0; $j < $table_i->getNumberOfKeys(); $j++) {
$k = $table_i->getKeyAtIndex($j);
$key = $k->getKey();
if (!in_array($key, $tableWithName_table->getAllKeys())) {
#php5 if (!in_array($key, $this->getTableWithName($table)->getAllKeys())) {
$messageKey .= "Key '".$k->getName()."' is missing.";
$messageKey .= $splitString;
$sqlKey .= "ALTER TABLE " . $table . " ADD";
$sqlKey .= $k->getMysql();
}
}
// check constraints
// FIXME
// InnoDB: Let InnoDB's FOREIGN KEY parser to remove the latin1 character 0xA0
// from the end of an unquoted identifier. The EMS MySQL Manager in ALTER TABLE
// adds that character after a table name, which caused error 121 when we tried
// to add a new constraint.
// May work with Mysql >= 4.1
// http://bugs.mysql.com/bug.php?id=6340
for ($j=0; $j < $table_i->getNumberOfConstraints(); $j++) {
$k = $table_i->getConstraintAtIndex($j);
$key = $k->getConstraint();
$someArray = $tableWithName_table->getAllConstraints();
if (!in_array($key, $someArray)) {
$messageConstraint .= "Constraint is missing.";
$messageConstraint .= $splitString;
$sqlConstraint .= "ALTER TABLE " . $table . " ADD";
$sqlConstraint .= $k->getMysql();
}
}
}
}
//order in which the sql statements will be processed
// create table
// add / change column
// add / change key
// add / change constraints
$sql = $sqlCreateTable . $sqlColumn . $sqlKey . $sqlConstraint;
$message = $messageCreateTable . $messageColumn . $messageKey . $messageConstraint;
// remove last "\n\n"
$sql = substr($sql, 0, strlen($sql)-2);
// remove last $splitString
$message = substr($message, 0, strlen($message)-strlen($splitString));
// break sql string into single sql statements
$sqlArray = explode("\n\n", $sql);
// break message string into single message statements
$messageArray = explode($splitString, $message);
return $sqlArray;
}
// executes a single sql statement
// connection details have to be specified in this database object
// returns a string with error message
function executeMysql($sql) {
$error = "";
if (!$this->con) {
$this->connect();
}
$res = db_query($sql);
if (db_errno()) {
$error .= $sql . "
" . db_error() . "
";
}
return $error;
}
// executes sql statements
// connection details have to be specified in this database object
// returns a string with all error messages
function executeMysqlArray($sqlArray) {
$error = "";
for ($i=0; $iexecuteMysql($sqlArray[$i]);
}
}
return $error;
}
// executes sql transaction
// connection details have to be specified in this database object
// returns a string with all error messages
function executeMysqlTransaction($sqlArray) {
$error = "";
$error .= $this->executeMysql("START TRANSACTION");
for ($i=0; $iexecuteMysql($sqlArray[$i]);
}
//echo $error;
}
$error .= $this->executeMysql("COMMIT");
return $error;
}
// ONLY NEEDED IN function mysqlDumpToDatabaseFromArrayNew($dumpArray)
// this function extracts attributes of a constraint from an excerpt of an sql statement
// this is needed in two segments of the algorithm and is therefore put in a function
function addConstraintFromString($row, $tableIndex) {
//retrieve constraint data
$row = str_replace("(", "", $row);
$row = str_replace(")", "", $row);
$row = str_replace(", ", " ", $row);
while (ereg("([:space:]){2}")) {
ereg_replace("([:space:]){2}", "([:space:])", $row);
}
$row = str_replace("on delete", "ondelete", strtolower($row));
$row = str_replace("on update", "onupdate", strtolower($row));
$row = str_replace("foreign key", "foreignkey", strtolower($row));
$values = explode(" ", $row);
$cnt = 0;
$conSymbol = "";
$conOnDelete = "";
$conOnUpdate = "";
$conColumn = "";
$conTableName = "";
$conTableColumnName = "";
if ($cnt < count($values) && strtolower($values[$cnt]) == "constraint") {
$cnt++;
}
if ($cnt < count($values) && strtolower($values[$cnt]) != "foreignkey") {
$conSymbol = $values[$cnt];
$cnt++;
}
if ($cnt < count($values) && strtolower($values[$cnt]) == "foreignkey") {
$cnt++;
while ($cnt < count($values) && strtolower($values[$cnt]) != "references") {
if (!$conColumn) {
$conColumn = $values[$cnt];
}
else {
$conColumn .= "," . $values[$cnt];
}
$cnt++;
}
}
if ($cnt < count($values) && strtolower($values[$cnt]) == "references") {
$cnt++;
$conTableName = $values[$cnt];
$cnt++;
}
while ($cnt < count($values) && strtolower($values[$cnt]) != "ondelete" && strtolower($values[$cnt]) != "onupdate" ) {
if (!$conTableColumnName) {
$conTableColumnName = $values[$cnt];
}
else {
$conTableColumnName .= "," . $values[$cnt];
}
$cnt++;
}
if ($cnt < count($values) && strtolower($values[$cnt]) == "ondelete") {
$cnt++;
$conOnDelete = $values[$cnt];
$cnt++;
}
if ($cnt < count($values) && strtolower($values[$cnt]) == "onupdate") {
$cnt++;
$conOnUpdate = $values[$cnt];
$cnt++;
}
$this->addConstraintToTableAtIndex($conSymbol, $tableIndex, $conColumn, $conTableName, $conTableColumnName, $conOnDelete, $conOnUpdate);
}
// creates a database object from a mysql dump
// the dump is parsed and the objects are generated accordingly
//
// needs some testing!
//
// restrictions:
// only 'foreign key' constraints are implemented, no other constraints.
function mysqlDumpToDatabaseFromArray($commandArray) {
//
//
// now the parsing begins
//
//
$tableIndex = -1;
$keyIndex = -1;
$tablename = "";
$verbose = "";
for ($i=0; $iaddTable($tablename);
//remove tablename and "(" from string
$line = trim(substr($line, $pos+1));
//get the last ")" in the string and get the type
$type = trim(substr(strrchr($line, ")"), 1));
$type = trim(substr(strstr($line, '='), 1));
$this->setTableType($tableIndex, $type);
//remove the type bit from the line
$line = substr($line, 0, strlen($line)-strlen(strrchr($line, ")")));
//replace , that are not seperating column definitions (or key definitions)
// these may be , that seperate two columns forming a key
$regexp = "(\([A-Za-z0-9[:space:]_\-\,;]+)\,([A-Za-z0-9[:space:]_\-\,;]+\))";
$regexp_replace = "\\1;;;\\2";
$replace = false;
while (ereg($regexp, $line)) {
#if (!$replace) echo "
" . $line . "
";
$line = ereg_replace($regexp, $regexp_replace, $line);
$replace = true;
}
$rows = explode(",", $line);
$rows = str_replace(";;;", ",", $rows);
for ($j=0; $jaddKeyToTableAtIndex("", $row, true, $tableIndex);
}
// key
elseif (strtolower(substr($row,0,3)) == "key" ) {
//retrieve key data
$row = trim(substr($row, 4));
$row = trim(str_replace("(", "", $row));
$row = trim(str_replace(")", "", $row));
$keys = explode(" ", $row);
//add key to object
$keyIndex = $this->addKeyToTableAtIndex($keys[0], $keys[1], false, $tableIndex);
}
elseif (strtolower(substr($row,0,10)) == "constraint" ) {
$this->addConstraintFromString(substr($row, 11), $tableIndex);
}
else {
//retrieve column information
$column = explode(" ", $row);
$col_name = $column[0];
$col_type = $column[1];
$col_attributes = substr($row,strlen($column[0])+strlen($column[1])+2);
$col_attr_array = explode(" ", $col_attributes);
$col_attributes = "";
for ($r=0; $raddColumnToTableAtIndex($col_name, $tableIndex);
$this->setColumnTypeInColumnAtIndexInTableAtIndex($col_type, $columnIndex, $tableIndex);
$this->setColumnAttributesInColumnAtIndexInTableAtIndex($col_attributes, $columnIndex, $tableIndex);
$this->setColumnDefaultInColumnAtIndexInTableAtIndex($defaultValue, $columnIndex, $tableIndex);
}
}
}
// CHECK FOR ALTER TABLE (for constraints, like in PHPMyAdmin dumps)
elseif (strtolower(substr($line, 0, 11)) == "alter table") {
// remove "alter table" from the string
$line = trim(substr($line, 12));
// identify the tableIndex of the constraint
$pos = strpos($line, " ");
$tablename = trim(substr($line, 0, $pos));
$tableIndex = $this->getTableIndex($tablename);
// delete table name from line
$line = trim(substr($line, $pos+1));
// identify the command, f.e. ADD
$pos = strpos($line, " ");
$cmd = trim(substr($line, 0, $pos));
$line = trim(substr($line, $pos+1));
$line = trim(str_replace(",", "", $line));
$constraintStrings = explode($cmd, $line);
for ($k=0; $kaddConstraintFromString($con, $tableIndex);
}
}
}
}
function mysqlCleanDumpArray ($dumpArray) {
// remove commentary and name wrappers
$cnt=0;
$newDumpArray = array();
for ($i=0; $imysqlCleanDumpArray($dumpArray);
$this->mysqlDumpToDatabaseFromArray($cleanDump);
}
else {
echo "MySQL dump not found!";
die();
}
}
function mysqlDumpFromArray($dumpArray) {
$cleanDump = $this->mysqlCleanDumpArray($dumpArray);
return $cleanDump;
}
function mysqlDumpFromUrl($url) {
$dumpArray=array();
if ($dumpArray = file($url)) {
$cleanDump = $this->mysqlCleanDumpArray($dumpArray);
return $cleanDump;
}
return false;
}
// removes a table object at a given index
function removeTableAtIndex ($aTableIndex) {
if ($aTableIndex && $aTableIndex < $this->table_cnt && $aTableIndex >= 0) {
$this->table[$aTableIndex]->destroyTable();
//fill gap in table array with last entry
if ($this->table_cnt > 1 && $aTableIndex != $this->table_cnt-1) {
$this->table[$aTableIndex] = $this->table[$this->table_cnt-1];
$this->table[$this->table_cnt-1] = null;
}
$this->table_cnt--;
}
else {
echo "Table '" . $aTableIndex . "' not in database!
";
}
}
// removes a table object (table index unknown)
function removeTableWithName($aTableName){
$index = $this->getTableIndex($aTableName);
$this->removeTableAtIndex($index);
}
// removes a column object in a table object (table index unknown)
function removeColumnWithNameInTableWithName($aColumnName, $aTableName){
$index = $this->getTableIndex($aTableName);
$this->removeColumnWithNameInTableAtIndex($aColumnName, $index);
}
// removes a key object in a table object (table index unknown)
function removeKeyWithNameInTableWithName($aKeyName, $aTableName){
$index = $this->getTableIndex($aTableName);
$this->removeKeyWithNameInTableAtIndex($aKeyName, $index);
}
// removes a constraint object in a table object (table index unknown)
function removeConstraintWithNameInTableWithName($aConstraintName, $aTableName){
$index = $this->getTableIndex($aTableName);
$this->removeConstraintWithNameInTableAtIndex($aConstraintName, $index);
}
// removes a column object in a table object at a given table index
function removeKeyWithNameInTableAtIndex($aKeyName, $aTableIndex){
if ($aTableIndex < $this->table_cnt && $aTableIndex >= 0 && $aKeyName != "") {
$this->table[$aTableIndex]->removeKeyWithName($aKeyName);
}
else {
echo "Table index out of range!
";
}
}
// removes a constraint object in a table object at a given table index
function removeConstraintWithNameInTableAtIndex($aConstraintName, $aTableIndex){
if ($aTableIndex < $this->table_cnt && $aTableIndex >= 0 && $aConstraintName != "") {
$this->table[$aTableIndex]->removeConstraintWithName($aConstraintName);
}
else {
echo "Table index out of range!
";
}
}
// removes a column object in a table object at a given table index
function removeColumnWithNameInTableAtIndex($aColumnName, $aTableIndex){
if ($aTableIndex < $this->table_cnt && $aTableIndex >= 0 && $aColumnName != "") {
$this->table[$aTableIndex]->removeColumnWithName($aColumnName);
}
else {
echo "Table index out of range!
";
}
}
}
//
//
// EXAMPLES
//
// this is how the database object may be accessed
//
//creates a database from an actual database
//$myDb = new database($server, $db, $owner, $pw);
//$myDb->mysqlDbToDatabase();
//creates a database from a mysql dump
//$myDb = new database("", "", "", "");
//$myDb->mysqlDumpToDatabaseFromUrl("http://localhost/mapbender/mapbender_PHPMyAdmin.sql");
//removes the table "wms" from the database
//$myDb->removeTableWithName("mb_user_mb_group");
//(intended to be a private method)
//removes the column "gui_id" from table "gui"
//$myDb->removeColumnWithNameInTableWithName("gui_id", "gui");
//(intended to be a private method)
//removes the key "mb_Group_index" from table "mb_user_mb_group"
//$myDb->removeKeyWithNameInTableWithName("mb_group_index", "mb_user_mb_group");
//(intended to be a private method)
//removes the constraint "mb_user_mb_group_ibfk_1" from table "mb_user_mb_group"
//$myDb->removeConstraintWithNameInTableWithName("mb_user_mb_group_ibfk_1", "mb_user_mb_group");
//(intended to be a private method)
//validates ownDb against remoteDb and returns an SQL string
//$sql = $myDb->compareDbWithRemoteDb($theirDb);
// executes all statements and displays occuring mysql errors
//echo $myDb->executeMysqlArray($sqlArray);
//displays the complete database structure
//$myDb->displayDatabase();
/*
* MySQL - Definition CREATE TABLE
*
*
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(] LIKE old_tbl_name [)];
create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr)
column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
| NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| CHAR(length) [BINARY | ASCII | UNICODE]
| VARCHAR(length) [BINARY]
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]
| LONGTEXT [BINARY]
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type
index_col_name:
col_name [(length)] [ASC | DESC]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
table_options: table_option [table_option] ...
table_option:
{ENGINE|TYPE} = engine_name
| AUTO_INCREMENT = value
| AVG_ROW_LENGTH = value
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| CHECKSUM = {0 | 1}
| COMMENT = 'string'
| MAX_ROWS = value
| MIN_ROWS = value
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = 'string'
| DELAY_KEY_WRITE = {0 | 1}
| ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| RAID_TYPE = { 1 | STRIPED | RAID0 }
RAID_CHUNKS = value
RAID_CHUNKSIZE = value
| UNION = (tbl_name[,tbl_name]...)
| INSERT_METHOD = { NO | FIRST | LAST }
| DATA DIRECTORY = 'absolute path to directory'
| INDEX DIRECTORY = 'absolute path to directory'
select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
*/
?>