/**
* Execute an alteration query of the pattern of the database according to setted parameters
*
* @return object result of the query
*/
public function alter()
{
if (!DB::getAlterSchema()) {
return;
}
$new = false;
# Check if table doesn't exists
if (!Schema::hasTable($this->getTable())) {
# Alter database
$this->query(DB::SQL()::CREATE_TABLE($this->getTable(), [$this->SQL_column($this->schema)]));
# Update table schema
Schema::addTable($this->getTable());
Schema::getTable($this->getTable())->addColumn(clone $this->schema);
$new = true;
}
# Check if column doesn't exists
if (!$this->hasColumn($this->schema->getName())) {
$this->query(DB::SQL()::ADD_COLUMN($this->getTable(), $this->SQL_column($this->schema)));
# Update Schema
Schema::getTable($this->getTable())->addColumn(clone $this->schema);
$new = true;
}
# Actual schema
$a = Schema::getTable($this->getTable())->getColumn($this->schema->getName());
if ($new) {
$a->setIndex(null);
$a->setForeign('', '');
}
# New schema
$n = $this->schema;
# Update schema
self::$tables[$this->getTable()]->setColumn($n);
/*
print_r("DB:");
print_r($a);
print_r("PHP:");
print_r($n);
*/
$dropped_foreign = [];
# Check if there is any difference between actual schema and new
if (!$n->equals($a)) {
# Drop X Primary key if
# Actual: primary, New: Not primary
# There is a primary key that isn't new. (There can be only one primary key)
$p = Schema::getTable($this->getTable())->getPrimary();
if ($a->getPrimary() && !$n->getPrimary() || $n->getPrimary() && $p != null && !$p->equals($n)) {
$primary = $p !== null ? $p : $n;
foreach (Schema::getAllForeignKeyToColumn($this->getTable(), $this->schema->getName()) as $k) {
$dropped_foreign[] = $k;
$this->query(DB::SQL()::DROP_FOREIGN_KEY($k->getTable(), $k->getConstraint()));
}
$this->query($this->SQL_editColumnBasics($this->getTable(), $primary));
$this->query(DB::SQL()::DROP_PRIMARY_KEY($this->getTable()));
Schema::getTable($this->getTable())->dropPrimary();
}
# Update foreign column
if (!$n->equalsForeign($a) && $a->getForeign()) {
$this->query(DB::SQL()::DROP_FOREIGN_KEY($this->getTable(), $a->getConstraint()));
# Update Schema actual
$a->setConstraint(null);
$a->setForeign(null, null);
$a->setForeignDelete(null);
$a->setForeignUpdate(null);
}
if ($n->getForeign()) {
$this->query(DB::SQL()::DISABLE_CHECKS_FOREIGN());
$this->query($this->SQL_addForeign($this->getTable(), $this->schema));
$this->query(DB::SQL()::ENABLE_CHECKS_FOREIGN());
# Get new name of constraint
$constraint = DB::first(DB::SQL()::SELECT_CONSTRAINT(DB::getName(), $this->getTable(), $a->getName()))['CONSTRAINT_NAME'];
# Update Schema actual
$a->setConstraint($constraint);
$a->setForeign($this->schema->getForeignTable(), $this->schema->getForeignColumn());
$a->setForeignDelete($this->schema->getForeignDelete());
$a->setForeignUpdate($this->schema->getForeignUpdate());
}
# Update index column
if ($a->hasIndex() && !$n->hasIndex()) {
$this->query(DB::SQL()::DROP_INDEX_KEY($this->getTable(), $a->getIndex()));
# Update Schema actual
$a->setIndex(null);
}
if (!$a->hasIndex() && $n->hasIndex()) {
$this->query(DB::SQL()::ADD_INDEX_KEY($this->getTable(), $this->schema->getName()));
# Update Schema actual
$a->setIndex($a->getName());
}
}
foreach ($dropped_foreign as $k) {
$this->query($this->SQL_addColumnForeign($k->getTable(), $k->schema));
}
if (!$n->equals($a)) {
# Update column
# Remove tem
$this->query(DB::SQL()::DISABLE_CHECKS_FOREIGN());
$this->query(DB::SQL()::EDIT_COLUMN($this->getTable(), $this->schema->getName(), $this->SQL_column($this->schema)));
$this->query(DB::SQL()::ENABLE_CHECKS_FOREIGN());
# Update Schema actual
Schema::getTable($this->getTable())->setColumn(clone $n);
}
}
/**
* Add a SQL code for a JOIN|LEFT JOIN|RIGHT JOIN
*
* @param string $ACT type of JOIN
* @param string $table name of the secondary table
* @return object clone of $this
*/
public function _join(string $ACT, $table, $table_fun = null, $last = null, $on = true)
{
$t = $this;
if ($table_fun == null) {
$last = $table_fun;
}
$s_last = $last == null ? $t->builder->getLastJoinTable() : $last;
if (is_array($table)) {
foreach ($table as $tab_n => $tab_val) {
$t = $t->_join($ACT, is_int($tab_n) ? $tab_val : $tab_n, is_int($tab_n) ? null : $tab_val, $s_last);
}
return $t;
}
if (is_object($table_fun) && $table_fun instanceof Closure) {
$n = static::__construct($t->getBuilderTable());
$n->builder->prepare = $t->builder->prepare;
$n->builder->orOn = $t->builder->orOn;
$n->builder->andOn = $t->builder->andOn;
$n = $table_fun($n);
$t->builder->orOn = $n->builder->orOn;
$t->builder->andOn = $n->builder->andOn;
$t->builder->prepare = $n->builder->prepare;
if ($last == null) {
$last = $table;
}
} else {
list($table_g, $table_alias) = DB::SQL()::GET_ALIAS($table);
if (!Schema::hasTable($table_g)) {
throw new \Exception("Schema: {$table} doesn't exists");
}
}
if (empty($t->builder->orOn) && empty($t->builder->andOn)) {
list($table_g, $table_alias) = DB::SQL()::GET_ALIAS($table);
if ($on) {
list($s_last_g, $s_last_alias) = DB::SQL()::GET_ALIAS($s_last);
$k1 = Schema::getTable($table_g)->getForeignKeyTo($s_last_g);
$k2 = Schema::getTable($s_last_g)->getForeignKeyTo($table_g);
if ($k1 !== null) {
$k = $k1;
} else {
if ($k2 !== null) {
$k = $k2;
} else {
throw new \Exception("<br>\nCannot relate {$s_last} with {$table}: Error with foreign key\n<br>");
}
}
$c1 = ($k1 == null ? $table_alias : $s_last_alias) . "." . $k->getForeignColumn();
$c2 = ($k1 !== null ? $table_alias : $s_last_alias) . "." . $k->getName();
$t = $t->on($c1, '=', $c2);
$last = $k1 != null ? $table : $s_last;
} else {
$last = $table_g;
}
}
$t->builder->setLastJoinTable($last);
$t->builder->join[] = DB::SQL()::JOIN($ACT, $table, $t->SQL_ON_EXP());
$t->builder->andOn = [];
$t->builder->orOn = [];
return $t;
}