src/structure/builder/TableBuilder.php
<?php
declare(strict_types = 1);
namespace mheinzerling\commons\database\structure\builder;
use mheinzerling\commons\database\structure\Database;
use mheinzerling\commons\database\structure\Field;
use mheinzerling\commons\database\structure\index\Index;
use mheinzerling\commons\database\structure\index\LazyForeignKey;
use mheinzerling\commons\database\structure\index\LazyIndex;
use mheinzerling\commons\database\structure\index\LazyPrimary;
use mheinzerling\commons\database\structure\index\LazyUnique;
use mheinzerling\commons\database\structure\index\Primary;
use mheinzerling\commons\database\structure\index\ReferenceOption;
use mheinzerling\commons\database\structure\Table;
use mheinzerling\commons\StringUtils;
class TableBuilder
{
/**
* @var Table
*/
private $table;
/**
* @var DatabaseBuilder
*/
private $db;
/**
* @var Index[];
*/
private $indexes = [];
/**
* @var string
*/
private $engine;
/**
* @var string
*/
private $charset;
/**
* @var string
*/
private $collation;
/**
* @var int|null
*/
private $currentAutoincrement = null;
public function __construct(DatabaseBuilder $db, string $name)
{
$this->db = $db;
$this->table = new Table($name);
$this->db->addTable($this->table);
}
public function build(): Database
{
return $this->complete()->build();
}
public function table(string $name): TableBuilder
{
return $this->complete()->table($name);
}
public function engine(string $engine = null): TableBuilder
{
$this->engine = $engine;
return $this;
}
public function charset(string $charset = null): TableBuilder
{
$this->charset = $charset;
return $this;
}
public function collation(string $collation): TableBuilder
{
$this->collation = $collation;
return $this;
}
public function autoincrement(int $currentAutoincrement = null): TableBuilder
{
$this->currentAutoincrement = $currentAutoincrement;
return $this;
}
public function complete(): DatabaseBuilder
{
$this->table->init($this->engine != null ? $this->engine : $this->db->getDefaultEngine(),
$this->charset != null ? $this->charset : $this->db->getDefaultCharset(),
$this->collation != null ? $this->collation : $this->db->getDefaultCollation(),
$this->currentAutoincrement);
foreach ($this->indexes as $name => &$index) {
if ($index instanceof LazyUnique) {
$index = $index->toUnique($this->table);
} else if ($index instanceof LazyIndex) {
$index = $index->toIndex($this->table);
} else if ($index instanceof LazyPrimary) {
$index = $index->toPrimary($this->table);
}
$this->table->addIndex($index);
}
return $this->db;
}
public function field(string $name): FieldBuilder
{
return new FieldBuilder($this, $name);
}
public function addField(Field $field): void
{
$this->table->addField($field);
}
/**
* @param string[] $fields
* @param null $name
* @return TableBuilder
*/
public function unique(array $fields, $name = null): TableBuilder
{
return $this->addIndex(new LazyUnique($this->table->getName(), $fields, $name));
}
/**
* @param string[] $fields
* @param null $name
* @return TableBuilder
*/
public function index(array $fields, $name = null): TableBuilder
{
return $this->addIndex(new LazyIndex($this->table->getName(), $fields, $name));
}
/**
* @param string[] $fields
* @param string $referenceTable
* @param string[] $referenceFields
* @param ReferenceOption $onUpdate
* @param ReferenceOption $onDelete
* @param string|null $name
* @return TableBuilder
*/
public function foreign(array $fields, string $referenceTable, array $referenceFields, ReferenceOption $onUpdate, ReferenceOption $onDelete, string $name = null): TableBuilder
{
return $this->addIndex(new LazyForeignKey($this->table->getName(), $fields, $name, $referenceTable, $referenceFields, $onUpdate, $onDelete));
}
/**
* @param string[] $fields
* @return TableBuilder
*/
public function primary(array $fields): TableBuilder
{
return $this->addIndex(new LazyPrimary($fields));
}
public function appendPrimary(Field $field): void
{
if (isset($this->indexes[Primary::PRIMARY])) {
/**
* @var $primary Primary
*/
$primary = $this->indexes[Primary::PRIMARY];
if ($primary instanceof LazyPrimary) throw new \Exception("Unsupported");
$primary->append($field);
} else $this->indexes[Primary::PRIMARY] = new Primary([$field->getName() => $field]);
}
public function addIndex(Index $index): TableBuilder
{
$index->setTable($this->table);
if (isset($this->indexes[$index->getName()]) && $index instanceof LazyForeignKey) {
/**
* @var $lazyFk LazyForeignKey
*/
$lazyFk = $this->indexes[$index->getName()];
$lazyFk->append($index);
} else {
$this->indexes[$index->getName()] = $index;
}
return $this;
}
/**
* @param \PDO $pdo
* @param DatabaseBuilder $db
* @param string $tableName
* @param string $engine
* @param string $collation
* @param int $currentAutoincrement
* @param string[] $booleanFields
*/
public static function fromDatabase(\PDO $pdo, DatabaseBuilder $db, string $tableName, string $engine, string $collation, string $currentAutoincrement = null, array $booleanFields = []): void
{
$tb = $db->table($tableName)->engine($engine)->charset(explode("_", $collation, 2)[0])->collation($collation)->autoincrement(intval($currentAutoincrement));
$fks = $pdo->query('SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k, INFORMATION_SCHEMA.referential_constraints AS r
WHERE k.CONSTRAINT_NAME=r.CONSTRAINT_NAME AND
k.TABLE_SCHEMA = "' . $db->getName() . '" AND r.constraint_schema = "' . $db->getName() . '" AND
k.TABLE_NAME = "' . $tableName . '" AND r.TABLE_NAME = "' . $tableName . '" AND
k.REFERENCED_COLUMN_NAME IS NOT NULL
')->fetchAll(\PDO::FETCH_ASSOC);
$ignore = [Primary::PRIMARY];
foreach ($fks as $fk) $ignore[] = $fk['CONSTRAINT_NAME'];
$stmt = $pdo->query('SHOW INDEXES FROM `' . $tableName . '` WHERE `Key_name` NOT IN ("' . implode('","', $ignore) . '")');
$indexes = [];
/** @noinspection PhpAssignmentInConditionInspection */
while ($indexRow = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$name = $indexRow['Key_name'];
if (!isset($indexes[$name])) $indexes[$name] = ['unique' => $indexRow['Non_unique'] == 0, 'fieldNames' => []];
$indexes[$name]['fieldNames'][] = $indexRow['Column_name'];
}
foreach ($indexes as $name => $index) {
if ($index['unique'] === true) $tb->unique($index['fieldNames'], $name);
else $tb->index($index['fieldNames'], $name);
}
$stmt = $pdo->query("SHOW FULL COLUMNS FROM `" . $tableName . "`");
/** @noinspection PhpAssignmentInConditionInspection */
while ($fieldRow = $stmt->fetch(\PDO::FETCH_ASSOC)) {
FieldBuilder::fromDatabase($tb, $fieldRow, $fks, $booleanFields);
}
$tb->complete();
}
/**
* @param DatabaseBuilder $db
* @param string $sql
* @return void
* @throws \Exception
*/
public static function fromSqlCreate(DatabaseBuilder $db, string $sql): void
{
list($tableName, $remaining) = explode('(', $sql, 2);
$tableName = trim(str_replace(["CREATE TABLE", "IF NOT EXISTS", "`"], "", $tableName));
$tb = $db->table($tableName);
list($allFields, $remaining) = self::splitFieldsAndConfiguration($remaining);
$tb->engine(StringUtils::findAndRemove($remaining, "@ENGINE\s*=\s*(\w+)@i"))
->charset(StringUtils::findAndRemove($remaining, "@DEFAULT CHARSET\s*=\s*(\w+)@i"))
->autoincrement(intval(StringUtils::findAndRemove($remaining, "@AUTO_INCREMENT\s*=\s*(\d+)@i")));
$remaining = trim($remaining);
if (!empty($remaining)) throw new \Exception("TODO: unhandled SQL CREATE TABLE statement configuration parts >" . $remaining . "<");
$carry = "";
foreach (StringUtils::trimExplode(",", $allFields) as $field) {
if (!empty($carry)) $field = $carry . "," . $field;
if (substr_count($field, "(") != substr_count($field, ")")) //comma in enum
{
$carry = $field;
continue;
}
$field = preg_replace('/\s\s+/', ' ', $field);
FieldBuilder::fromSql($tb, $field);
$carry = "";
}
$tb->complete();
}
/**
* @param $remaining
* @return string[]
*/
protected static function splitFieldsAndConfiguration(string $remaining): array
{
$pos = StringUtils::findMatchingBracketPos("(" . $remaining) - 1;
$allFields = substr($remaining, 0, $pos);
$remaining = trim(substr($remaining, $pos), ")");
return [$allFields, $remaining];
}
}