acp_database.php

这些都是我以前学习是用到的源码

				$field_query = "SELECT a.attnum, a.attname as field, t.typname as type, a.attlen as length, a.atttypmod as lengthvar, a.attnotnull as notnull
					FROM pg_class c, pg_attribute a, pg_type t
					WHERE c.relname = '" . $db->sql_escape($table_name) . "'
						AND a.attnum > 0
						AND a.attrelid = c.oid
						AND a.atttypid = t.oid
					ORDER BY a.attnum";
				$result = $db->sql_query($field_query);

				$sql_data .= "CREATE TABLE $table_name(\n";
				$lines = array();
				while ($row = $db->sql_fetchrow($result))
				{
					// Get the data from the table
					$sql_get_default = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault
						FROM pg_attrdef d, pg_class c
						WHERE (c.relname = '" . $db->sql_escape($table_name) . "')
							AND (c.oid = d.adrelid)
							AND d.adnum = " . $row['attnum'];
					$def_res = $db->sql_query($sql_get_default);

					if (!$def_res)
					{
						unset($row['rowdefault']);
					}
					else
					{
						$row['rowdefault'] = $db->sql_fetchfield('rowdefault', 0, $def_res);
					}
					$db->sql_freeresult($def_res);

					if ($row['type'] == 'bpchar')
					{
						// Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement.
						$row['type'] = 'char';
					}

					$line = '  ' . $row['field'] . ' ' . $row['type'];

					if (strpos($row['type'], 'char') !== false)
					{
						if ($row['lengthvar'] > 0)
						{
							$line .= '(' . ($row['lengthvar'] - 4) . ')';
						}
					}

					if (strpos($row['type'], 'numeric') !== false)
					{
						$line .= '(';
						$line .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff));
						$line .= ')';
					}

					if (!empty($row['rowdefault']))
					{
						$line .= ' DEFAULT ' . $row['rowdefault'];
					}

					if ($row['notnull'] == 't')
					{
						$line .= ' NOT NULL';
					}
					
					$lines[] = $line;
				}
				$db->sql_freeresult($result);


				// Get the listing of primary keys.
				$sql_pri_keys = "SELECT ic.relname as index_name, bc.relname as tab_name, ta.attname as column_name, i.indisunique as unique_key, i.indisprimary as primary_key
					FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia
					WHERE (bc.oid = i.indrelid)
						AND (ic.oid = i.indexrelid)
						AND (ia.attrelid = i.indexrelid)
						AND	(ta.attrelid = bc.oid)
						AND (bc.relname = '" . $db->sql_escape($table_name) . "')
						AND (ta.attrelid = i.indrelid)
						AND (ta.attnum = i.indkey[ia.attnum-1])
					ORDER BY index_name, tab_name, column_name";

				$result = $db->sql_query($sql_pri_keys);

				$index_create = $index_rows = $primary_key = array();

				// We do this in two steps. It makes placing the comma easier
				while ($row = $db->sql_fetchrow($result))
				{
					if ($row['primary_key'] == 't')
					{
						$primary_key[] = $row['column_name'];
						$primary_key_name = $row['index_name'];
					}
					else
					{
						// We have to store this all this info because it is possible to have a multi-column key...
						// we can loop through it again and build the statement
						$index_rows[$row['index_name']]['table'] = $table_name;
						$index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? true : false;
						$index_rows[$row['index_name']]['column_names'][] = $row['column_name'];
					}
				}
				$db->sql_freeresult($result);

				if (!empty($index_rows))
				{
					foreach ($index_rows as $idx_name => $props)
					{
						$index_create[] = 'CREATE ' . ($props['unique'] ? 'UNIQUE ' : '') . "INDEX $idx_name ON $table_name (" . implode(', ', $props['column_names']) . ");";
					}
				}

				if (!empty($primary_key))
				{
					$lines[] = "  CONSTRAINT $primary_key_name PRIMARY KEY (" . implode(', ', $primary_key) . ")";
				}

				// Generate constraint clauses for CHECK constraints
				$sql_checks = "SELECT conname as index_name, consrc
					FROM pg_constraint, pg_class bc
					WHERE conrelid = bc.oid
						AND bc.relname = '" . $db->sql_escape($table_name) . "'
						AND NOT EXISTS (
							SELECT *
								FROM pg_constraint as c, pg_inherits as i
								WHERE i.inhrelid = pg_constraint.conrelid
									AND c.conname = pg_constraint.conname
									AND c.consrc = pg_constraint.consrc
									AND c.conrelid = i.inhparent
						)";
				$result = $db->sql_query($sql_checks);

				// Add the constraints to the sql file.
				while ($row = $db->sql_fetchrow($result))
				{
					if (!is_null($row['consrc']))
					{
						$lines[] = '  CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['consrc'];
					}
				}
				$db->sql_freeresult($result);

				$sql_data .= implode(", \n", $lines);
				$sql_data .= "\n);\n";

				if (!empty($index_create))
				{
					$sql_data .= implode("\n", $index_create) . "\n\n";
				}
			break;

			case 'mssql':
			case 'mssql_odbc':
				$sql_data .= "\nCREATE TABLE [$table_name] (\n";
				$rows = array();

				$text_flag = false;

				$sql = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY
					FROM INFORMATION_SCHEMA.COLUMNS
					WHERE TABLE_NAME = '$table_name'";
				$result = $db->sql_query($sql);

				while ($row = $db->sql_fetchrow($result))
				{
					$line = "\t[{$row['COLUMN_NAME']}] [{$row['DATA_TYPE']}]";

					if ($row['DATA_TYPE'] == 'text')
					{
						$text_flag = true;
					}

					if ($row['IS_IDENTITY'])
					{
						$line .= ' IDENTITY (1 , 1)';
					}

					if ($row['CHARACTER_MAXIMUM_LENGTH'] && $row['DATA_TYPE'] !== 'text')
					{
						$line .= ' (' . $row['CHARACTER_MAXIMUM_LENGTH'] . ')';
					}

					if ($row['IS_NULLABLE'] == 'YES')
					{
						$line .= ' NULL';
					}
					else
					{
						$line .= ' NOT NULL';
					}

					if ($row['COLUMN_DEFAULT'])
					{
						$line .= ' DEFAULT ' . $row['COLUMN_DEFAULT'];
					}

					$rows[] = $line;
				}
				$db->sql_freeresult($result);

				$sql_data .= implode(",\n", $rows);
				$sql_data .= "\n) ON [PRIMARY]";

				if ($text_flag)
				{
					$sql_data .= " TEXTIMAGE_ON [PRIMARY]";
				}

				$sql_data .= "\nGO\n\n";
				$rows = array();

				$sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME
					FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
					WHERE TABLE_NAME = '$table_name'";
				$result = $db->sql_query($sql);
				while ($row = $db->sql_fetchrow($result))
				{
					if (!sizeof($rows))
					{
						$sql_data .= "ALTER TABLE [$table_name] WITH NOCHECK ADD\n";
						$sql_data .= "\tCONSTRAINT [{$row['CONSTRAINT_NAME']}] PRIMARY KEY  CLUSTERED \n\t(\n";
					}
					$rows[] = "\t\t[{$row['COLUMN_NAME']}]";
				}
				if (sizeof($rows))
				{
					$sql_data .= implode(",\n", $rows);
					$sql_data .= "\n\t)  ON [PRIMARY] \nGO\n";
				}
				$db->sql_freeresult($result);

				$index = array();
				$sql = "EXEC sp_statistics '$table_name'";
				$result = $db->sql_query($sql);
				while ($row = $db->sql_fetchrow($result))
				{
					if ($row['TYPE'] == 3)
					{
						$index[$row['INDEX_NAME']][] = '[' . $row['COLUMN_NAME'] . ']';
					}
				}
				$db->sql_freeresult($result);

				foreach ($index as $index_name => $column_name)
				{
					$index[$index_name] = implode(', ', $index[$index_name]);
				}

				foreach ($index as $index_name => $columns)
				{
					$sql_data .= "\nCREATE  INDEX [$index_name] ON [$table_name]($columns) ON [PRIMARY]\nGO\n";
				}
			break;

			case 'firebird':

				$data_types = array(7 => 'SMALLINT', 8 => 'INTEGER', 10 => 'FLOAT', 12 => 'DATE', 13 => 'TIME', 14 => 'CHARACTER', 27 => 'DOUBLE PRECISION', 35 => 'TIMESTAMP', 37 => 'VARCHAR', 40 => 'CSTRING', 261 => 'BLOB', 701 => 'DECIMAL', 702 => 'NUMERIC');

				$sql_data .= "\nCREATE TABLE $table_name (\n";

				$sql  = 'SELECT DISTINCT R.RDB$FIELD_NAME as FNAME, R.RDB$NULL_FLAG as NFLAG, R.RDB$DEFAULT_SOURCE as DSOURCE, F.RDB$FIELD_TYPE as FTYPE, F.RDB$FIELD_SUB_TYPE as STYPE, F.RDB$FIELD_LENGTH as FLEN
					FROM RDB$RELATION_FIELDS R
					JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME
					LEFT JOIN RDB$FIELD_DIMENSIONS D ON R.RDB$FIELD_SOURCE = D.RDB$FIELD_NAME
					WHERE F.RDB$SYSTEM_FLAG = 0
						AND R.RDB$RELATION_NAME = \''. $table_name . '\'
					ORDER BY R.RDB$FIELD_POSITION';
				$result = $db->sql_query($sql);

				$rows = array();
				while ($row = $db->sql_fetchrow($result))
				{
					$line = "\t" . '"' . $row['fname'] . '" ' . $data_types[$row['ftype']];

					if ($row['ftype'] == 261 && $row['stype'] == 1)
					{
						$line .= ' SUB_TYPE TEXT';
					}

					if ($row['ftype'] == 37 || $row['ftype'] == 14)
					{
						$line .= ' (' . $row['flen'] . ')';
					}

					if (!empty($row['dsource']))
					{
						$line .= ' ' . $row['dsource'];
					}

					if (!empty($row['nflag']))
					{
						$line .= ' NOT NULL';
					}
					$rows[] = $line;
				}
				$db->sql_freeresult($result);

				$sql_data .= implode(",\n", $rows);
				$sql_data .= "\n);;\n";
				$keys = array();

				$sql  = 'SELECT I.RDB$FIELD_NAME as NAME
					FROM RDB$RELATION_CONSTRAINTS RC, RDB$INDEX_SEGMENTS I, RDB$INDICES IDX
					WHERE (I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
						AND (IDX.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
						AND (RC.RDB$RELATION_NAME = \''. $table_name . '\')
					ORDER BY I.RDB$FIELD_POSITION';
				$result = $db->sql_query($sql);

				while ($row = $db->sql_fetchrow($result))
				{
					$keys[] = $row['name'];
				}

				if (sizeof($keys))
				{
					$sql_data .= "\nALTER TABLE $table_name ADD PRIMARY KEY (" . implode(', ', $keys) . ');;';
				}

				$db->sql_freeresult($result);

				$sql = 'SELECT I.RDB$INDEX_NAME as INAME, I.RDB$UNIQUE_FLAG as UFLAG, S.RDB$FIELD_NAME as FNAME
					FROM RDB$INDICES I JOIN RDB$INDEX_SEGMENTS S ON S.RDB$INDEX_NAME=I.RDB$INDEX_NAME
					WHERE (I.RDB$SYSTEM_FLAG IS NULL  OR  I.RDB$SYSTEM_FLAG=0)
						AND I.RDB$FOREIGN_KEY IS NULL
						AND I.RDB$RELATION_NAME = \''. $table_name . '\'
						AND I.RDB$INDEX_NAME NOT STARTING WITH \'RDB$\'
					ORDER BY S.RDB$FIELD_POSITION';
				$result = $db->sql_query($sql);

				$index = array();
				while ($row = $db->sql_fetchrow($result))
				{
					$index[$row['iname']]['unique'] = !empty($row['uflag']);
					$index[$row['iname']]['values'][] = $row['fname'];
				}

				foreach ($index as $index_name => $data)
				{
					$sql_data .= "\nCREATE ";
					if ($data['unique'])
					{
						$sql_data .= 'UNIQUE ';
					}
					$sql_data .= "INDEX $index_name ON $table_name(" . implode(', ', $data['values']) . ");;";
				}
				$sql_data .= "\n";

				$db->sql_freeresult($result);

				$sql = 'SELECT D1.RDB$DEPENDENT_NAME as DNAME, D1.RDB$FIELD_NAME as FNAME, D1.RDB$DEPENDENT_TYPE, R1.RDB$RELATION_NAME
					FROM RDB$DEPENDENCIES D1
					LEFT JOIN RDB$RELATIONS R1 ON ((D1.RDB$DEPENDENT_NAME = R1.RDB$RELATION_NAME) AND (NOT (R1.RDB$VIEW_BLR IS NULL)))
					WHERE (D1.RDB$DEPENDED_ON_TYPE = 0)
						AND (D1.RDB$DEPENDENT_TYPE <> 3)
						AND (D1.RDB$DEPENDED_ON_NAME = \'' . $table_name . '\')
					UNION SELECT DISTINCT F2.RDB$RELATION_NAME, D2.RDB$FIELD_NAME, D2.RDB$DEPENDENT_TYPE, R2.RDB$RELATION_NAME FROM RDB$DEPENDENCIES D2, RDB$RELATION_FIELDS F2
					LEFT JOIN RDB$RELATIONS R2 ON ((F2.RDB$RELATION_NAME = R2.RDB$RELATION_NAME) AND (NOT (R2.RDB$VIEW_BLR IS NULL)))
					WHERE (D2.RDB$DEPENDENT_TYPE = 3)
						AND (D2.RDB$DEPENDENT_NAME = F2.RDB$FIELD_SOURCE)
						AND (D2.RDB$DEPENDED_ON_NAME = \'' . $table_name . '\')
					ORDER BY 1, 2';
				$result = $db->sql_query($sql);
				while ($row = $db->sql_fetchrow($result))
				{
					$sql = 'SELECT T1.RDB$DEPENDED_ON_NAME as GEN, T1.RDB$FIELD_NAME, T1.RDB$DEPENDED_ON_TYPE
						FROM RDB$DEPENDENCIES T1
						WHERE (T1.RDB$DEPENDENT_NAME = \'' . $row['dname'] . '\')
							AND (T1.RDB$DEPENDENT_TYPE = 2 AND T1.RDB$DEPENDED_ON_TYPE = 14)
						UNION ALL SELECT DISTINCT D.RDB$DEPENDED_ON_NAME, D.RDB$FIELD_NAME, D.RDB$DEPENDED_ON_TYPE
						FROM RDB$DEPENDENCIES D, RDB$RELATION_FIELDS F
						WHERE (D.RDB$DEPENDENT_TYPE = 3)
							AND (D.RDB$DEPENDENT_NAME = F.RDB$FIELD_SOURCE)
							AND (F.RDB$RELATION_NAME = \'' . $row['dname'] . '\')
						ORDER BY 1,2';
					$result2 = $db->sql_query($sql);
					$row2 = $db->sql_fetchrow($result2);
					$db->sql_freeresult($result2);
					$gen_name = $row2['gen'];

					$sql_data .= "\nCREATE GENERATOR " . $gen_name . ";;";
					$sql_data .= "\nSET GENERATOR  " . $gen_name . " TO 0;;\n";
					$sql_data .= "\nCREATE TRIGGER {$row['dname']} FOR $table_name";
					$sql_data .= "\nBEFORE INSERT\nAS\nBEGIN";
					$sql_data .= "\n  NEW.{$row['fname']} = GEN_ID(" . $gen_name . ", 1);";
					$sql_data .= "\nEND;;\n";
				}

				$db->sql_freeresult($result);
			break;

			case 'oracle':
				$sql_data .= "\nCREATE TABLE $table_name (\n";

				$sql  = "SELECT COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_LENGTH, NULLABLE, DATA_DEFAULT
					FROM ALL_TAB_COLS
					WHERE table_name = '{$table_name}'";
				$result = $db->sql_query($sql);

				$rows = array();
				while ($row = $db->sql_fetchrow($result))
				{
					$line = '  "' . $row['column_name'] . '" ' . $row['data_type'];

					if ($row['data_type'] !== 'CLOB')
					{
						if ($row['data_type'] !== 'VARCHAR2')
						{
							$line .= '(' . $row['data_precision'] . ')';
						}
						else
						{
							$line .= '(' . $row['data_length'] . ')';
						}
					}

					if (!empty($row['data_default']))
					{
						$line .= ' DEFAULT ' . $row['data_default'];
					}

					if ($row['nullable'] == 'N')
					{
						$line .= ' NOT NULL';
					}
					$rows[] = $line;
				}
				$db->sql_freeresult($result);

				$sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME
					FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
					WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
						AND B.CONSTRAINT_TYPE = 'P'
						AND A.TABLE_NAME = '{$table_name}'";
				$result = $db->sql_query($sql);

				while ($row = $db->sql_fetchrow($result))
				{
					$rows[] = "  CONSTRAINT {$row['constraint_name']} PRIMARY KEY ({$row['column_name']})";
				}
				$db->sql_freeresult($result);

				$sql = "SELECT A.CONSTRAINT_NAME, A.COLUMN_NAME
					FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
					WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
						AND B.CONSTRAINT_TYPE = 'U'
						AND A.TABLE_NAME = '{$table_name}'";
				$result = $db->sql_query($sql);

				while ($row = $db->sql_fetchrow($result))
				{
					$rows[] = "  CONSTRAINT {$row['constraint_name']} UNIQUE ({$row['column_name']})";
				}
				$db->sql_freeresult($result);

				$sql_data .= implode(",\n", $rows);
				$sql_data .= "\n)\n\\";

				$sql = "SELECT A.REFERENCED_NAME
					FROM USER_DEPENDENCIES A, USER_TRIGGERS B
					WHERE A.REFERENCED_TYPE = 'SEQUENCE'
						AND A.NAME = B.TRIGGER_NAME
						AND B. TABLE_NAME = '{$table_name}'";
				$result = $db->sql_query($sql);
				while ($row = $db->sql_fetchrow($result))
				{
					$sql_data .= "\nCREATE SEQUENCE {$row['referenced_name']}\\\n";
				}
				$db->sql_freeresult($result);

				$sql = "SELECT DESCRIPTION, WHEN_CLAUSE, TRIGGER_BODY
					FROM USER_TRIGGERS
					WHERE TABLE_NAME = '{$table_name}'";
				$result = $db->sql_query($sql);
				while ($row = $db->sql_fetchrow($result))
				{
					$sql_data .= "\nCREATE OR REPLACE TRIGGER {$row['description']}WHEN ({$row['when_clause']})\n{$row['trigger_body']}\\";
				}
				$db->sql_freeresult($result);

				$sql = "SELECT A.INDEX_NAME, B.COLUMN_NAME
					FROM USER_INDEXES A, USER_IND_COLUMNS B
					WHERE A.UNIQUENESS = 'NONUNIQUE'
						AND A.INDEX_NAME = B.INDEX_NAME
						AND B.TABLE_NAME = '{$table_name}'";
				$result = $db->sql_query($sql);

				$index = array();

				while ($row = $db->sql_fetchrow($result))
				{
					$index[$row['index_name']][] = $row['column_name'];
				}

				foreach ($index as $index_name => $column_names)
				{
					$sql_data .= "\nCREATE INDEX $index_name ON $table_name(" . implode(', ', $column_names) . ")\n\\";
				}
				$db->sql_freeresult($result);

			break;
		}

		return $sql_data;
	}
}

?>