public function buildCreateTableQuery(Table $table)
{
$columnQuery = array();
foreach ($table->columnNames() as $columnName) {
$column = $table->column($columnName);
$query = $this->buildColumnQuery($column);
$columnQuery[] = $query;
}
$res = "create table " . $table->getName();
$res .= "(\n";
$res .= "\t" . implode(",\n\t", $columnQuery);
$res .= "\n)";
return $res;
}
private function prepareTable(Table $table)
{
$ds = $this->getDataSource();
$res = $ds->executeQuery("show table status where name = :table", [":table" => $table->getName()]);
$res = $res[0];
$table->engine($res["Engine"]);
$table->comment($res["Comment"]);
$res = $ds->executeQuery("show create table " . $table->getName());
$res = $res[0];
$createSql = $res["Create Table"];
$tmp = array();
if (preg_match("/DEFAULT CHARSET=(.+)/", $createSql, $tmp)) {
$table->charset($tmp[1]);
}
$res = $ds->executeQuery("show columns from " . $table->getName());
foreach ($res as $row) {
$columnName = $row["Field"];
$type = $row["Type"];
$nullable = $row["Null"] == "YES";
$default = $row["Default"];
$key = $row["Key"];
$length = null;
$primary = $key == "PRI";
$unique = $key == "UNI";
if ($columnName == "id") {
$table->id();
continue;
}
if (preg_match("/(.+)\\((\\d+)\\)/", $type, $tmp)) {
$type = $tmp[1];
$length = $tmp[2];
}
$column = null;
switch ($type) {
case "int":
$column = $table->integer($columnName);
$length = null;
break;
case "varchar":
$column = $table->varchar($columnName);
break;
case "text":
$column = $table->text($columnName);
break;
case "longtext":
$column = $table->text($columnName, "long");
break;
}
if (!$column) {
die("unknown type:" . $type);
}
$column->length = $length;
$column->primary = $primary;
$column->nullable = $nullable;
$column->default = $default;
$column->unique = $unique;
}
$res = $ds->executeQuery("select column_name, " . "referenced_table_name,referenced_column_name " . "from information_schema.key_column_usage " . "where referenced_table_name is not null " . "and table_schema = :dbname and table_name = :table", [":dbname" => $ds->getDataBaseName(), ":table" => $table->getName()]);
foreach ($res as $row) {
$column = $row["column_name"];
$targetTable = $row["referenced_table_name"];
$targetColumn = $row["referenced_column_name"];
$table->column($column)->reference($targetTable, $targetColumn);
}
}
private function prepareTable(Table $table)
{
$ds = $this->getDataSource();
$res = $ds->executeQuery("PRAGMA table_info(" . $table->getName() . ")");
foreach ($res as $row) {
$columnName = $row["name"];
$type = $row["type"];
$length = null;
if (preg_match("#(.+)\\(([0-9]+)\\)#", $type, $tmp)) {
$type = $tmp[1];
$length = $tmp[2];
}
//SQLiteのバージョンによってはpkに上手く入らないパターンが有る
if (strpos($type, " ") !== false) {
list($type, $suffix) = explode(" ", $type);
if ($suffix == "primary_id") {
$row["pk"] = 1;
}
}
switch ($type) {
case "integer":
$column = $table->integer($columnName);
$length = null;
break;
case "varchar":
$column = $table->varchar($columnName);
break;
case "text":
$column = $table->text($columnName);
break;
case "longtext":
$column = $table->text($columnName, "long");
break;
default:
$column = $table->text($columnName);
break;
}
$primary = $row["pk"] == "1";
$nullable = $row["notnull"] == "0";
$default = $row["dflt_value"];
$unique = false;
$column->length = $length;
$column->primary = $primary;
$column->nullable = $nullable;
$column->default = $default;
$column->unique = $unique;
}
$res = $ds->executeQuery("select * from sqlite_master where tbl_name = :table", [":table" => $table->getName()]);
foreach ($res as $row) {
$type = $row["type"];
if ($type == "table") {
$tableName = $row["name"];
$sql = str_replace(["\r", "\n"], "", $row["sql"]);
preg_match("#(" . $tableName . ")\\((.*)\\)#im", $sql, $tmp);
$lines = explode(",", $tmp[2]);
foreach ($lines as $line) {
$line = strtolower(trim($line));
$each = explode(" ", $line);
$columnName = $each[0];
$column = $table->column($columnName);
if (!$column) {
continue;
}
if (in_array("unique", $each)) {
$column->unique = true;
}
}
}
}
}
