src/CoreBundle/Migrations/Schema/V200/Version20170626122900.php
<?php
declare(strict_types=1);
/* For licensing terms, see /license.txt */
namespace Chamilo\CoreBundle\Migrations\Schema\V200;
use Chamilo\CoreBundle\Migrations\AbstractMigrationChamilo;
use Doctrine\DBAL\Schema\Schema;
class Version20170626122900 extends AbstractMigrationChamilo
{
public function getDescription(): string
{
return 'User changes';
}
public function up(Schema $schema): void
{
$table = $schema->getTable('user');
if ($table->hasIndex('idx_user_uid')) {
$this->addSql('DROP INDEX idx_user_uid ON user;');
}
if ($table->hasIndex('UNIQ_8D93D649C05FB297')) {
$this->addSql('DROP INDEX UNIQ_8D93D649C05FB297 ON user;');
}
if ($table->hasColumn('user_id')) {
$this->addSql('ALTER TABLE user DROP user_id');
}
if (false === $table->hasColumn('resource_node_id')) {
$this->addSql('ALTER TABLE user ADD resource_node_id INT DEFAULT NULL;');
$this->addSql(
'ALTER TABLE user ADD CONSTRAINT FK_8D93D6491BAD783F FOREIGN KEY (resource_node_id) REFERENCES resource_node (id) ON DELETE CASCADE;'
);
$this->addSql('CREATE UNIQUE INDEX UNIQ_8D93D6491BAD783F ON user (resource_node_id);');
}
if ($table->hasColumn('salt')) {
$this->addSql('ALTER TABLE user CHANGE salt salt VARCHAR(255) NOT NULL');
}
if ($table->hasColumn('created_at')) {
$this->addSql(
'UPDATE user SET created_at = registration_date WHERE CAST(created_at AS CHAR(20)) = "0000-00-00 00:00:00"'
);
$this->addSql('UPDATE user SET created_at = registration_date WHERE created_at IS NULL');
// $this->addSql('UPDATE user SET created_at = NOW() WHERE created_at = NULL OR created_at = ""');
$this->addSql('ALTER TABLE user CHANGE created_at created_at DATETIME NOT NULL');
}
if ($table->hasColumn('updated_at')) {
$this->addSql(
'UPDATE user SET updated_at = registration_date WHERE CAST(updated_at AS CHAR(20)) = "0000-00-00 00:00:00"'
);
$this->addSql('UPDATE user SET updated_at = registration_date WHERE updated_at IS NULL');
// $this->addSql('UPDATE user SET updated_at = NOW() WHERE updated_at = NULL OR updated_at = ""');
$this->addSql('ALTER TABLE user CHANGE updated_at updated_at DATETIME NOT NULL');
}
if ($table->hasColumn('confirmation_token')) {
$this->addSql('ALTER TABLE user CHANGE confirmation_token confirmation_token VARCHAR(255) DEFAULT NULL');
}
if ($table->hasColumn('website')) {
$this->addSql('ALTER TABLE user CHANGE website website VARCHAR(255) DEFAULT NULL');
} else {
$this->addSql('ALTER TABLE user ADD website VARCHAR(255) DEFAULT NULL');
}
if (false === $table->hasColumn('api_token')) {
$this->addSql('ALTER TABLE user ADD api_token VARCHAR(255) DEFAULT NULL');
$this->addSql('CREATE UNIQUE INDEX UNIQ_8D93D6497BA2F5EB ON user (api_token);');
}
if (false === $table->hasColumn('date_of_birth')) {
$this->addSql('ALTER TABLE user ADD date_of_birth DATETIME DEFAULT NULL');
}
if (false === $table->hasColumn('biography')) {
$this->addSql('ALTER TABLE user ADD biography LONGTEXT DEFAULT NULL');
}
if (false === $table->hasColumn('gender')) {
$this->addSql('ALTER TABLE user ADD gender VARCHAR(1) DEFAULT NULL');
}
if (false === $table->hasColumn('locale')) {
$this->addSql('ALTER TABLE user ADD locale VARCHAR(10) NOT NULL');
$this->addSql('UPDATE user SET language = "english" WHERE language IS NULL OR language = "" ');
$this->addSql('UPDATE user SET locale = (SELECT isocode FROM language WHERE english_name = language)');
}
if (false === $table->hasColumn('timezone')) {
$this->addSql('ALTER TABLE user ADD timezone VARCHAR(64) NOT NULL');
}
if (false === $table->hasColumn('confirmation_token')) {
$this->addSql('ALTER TABLE user ADD confirmation_token VARCHAR(255) DEFAULT NULL');
$this->addSql('CREATE UNIQUE INDEX UNIQ_8D93D649C05FB297 ON user (confirmation_token)');
} else {
$this->addSql('ALTER TABLE user CHANGE confirmation_token confirmation_token VARCHAR(255) DEFAULT NULL');
}
if ($table->hasColumn('enabled')) {
$this->addSql('ALTER TABLE user DROP enabled');
}
$this->addSql('ALTER TABLE user CHANGE username_canonical username_canonical VARCHAR(180) NOT NULL');
$this->addSql('ALTER TABLE user CHANGE lastname lastname VARCHAR(64) DEFAULT NULL');
$this->addSql('ALTER TABLE user CHANGE firstname firstname VARCHAR(64) DEFAULT NULL');
$this->addSql('ALTER TABLE user CHANGE phone phone VARCHAR(64) DEFAULT NULL');
$table = $schema->getTable('admin');
$this->addSql('ALTER TABLE admin CHANGE user_id user_id INT DEFAULT NULL');
if (false === $table->hasForeignKey('FK_880E0D76A76ED395')) {
$this->addSql(
'ALTER TABLE admin ADD CONSTRAINT FK_880E0D76A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE'
);
}
if ($table->hasIndex('user_id')) {
// $this->addSql('DROP INDEX user_id ON admin');
}
if (false === $table->hasIndex('UNIQ_880E0D76A76ED395')) {
$this->addSql('CREATE UNIQUE INDEX UNIQ_880E0D76A76ED395 ON admin (user_id)');
}
$table = $schema->getTable('user_course_category');
if (false === $table->hasColumn('collapsed')) {
$this->addSql('ALTER TABLE user_course_category ADD collapsed TINYINT(1) DEFAULT NULL');
}
$this->addSql('ALTER TABLE user_course_category CHANGE user_id user_id INT DEFAULT NULL');
if (false === $table->hasForeignKey('FK_BD241818A76ED395')) {
$this->addSql(
'ALTER TABLE user_course_category ADD CONSTRAINT FK_BD241818A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE'
);
}
$table = $schema->getTable('user_rel_course_vote');
$this->addSql('ALTER TABLE user_rel_course_vote CHANGE user_id user_id INT DEFAULT NULL');
$this->addSql('DELETE FROM user_rel_course_vote WHERE user_id NOT IN (SELECT id FROM user)');
$this->addSql('ALTER TABLE user_rel_course_vote CHANGE c_id c_id INT DEFAULT NULL');
$this->addSql('DELETE FROM user_rel_course_vote WHERE c_id NOT IN (SELECT id FROM course)');
$this->addSql('ALTER TABLE user_rel_course_vote CHANGE session_id session_id INT DEFAULT NULL');
$this->addSql('UPDATE user_rel_course_vote SET session_id = NULL WHERE session_id = 0 ');
$this->addSql('DELETE FROM user_rel_course_vote WHERE session_id IS NOT NULL AND session_id NOT IN (SELECT id FROM session)');
$this->addSql('ALTER TABLE user_rel_course_vote CHANGE url_id url_id INT DEFAULT NULL');
if (!$table->hasForeignKey('FK_4038AA47A76ED395')) {
$this->addSql(
'ALTER TABLE user_rel_course_vote ADD CONSTRAINT FK_4038AA47A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE'
);
}
if (!$table->hasForeignKey('FK_4038AA4791D79BD3')) {
$this->addSql(
'ALTER TABLE user_rel_course_vote ADD CONSTRAINT FK_4038AA4791D79BD3 FOREIGN KEY (c_id) REFERENCES course (id) ON DELETE CASCADE'
);
}
if (!$table->hasForeignKey('FK_4038AA47613FECDF')) {
$this->addSql(
'ALTER TABLE user_rel_course_vote ADD CONSTRAINT FK_4038AA47613FECDF FOREIGN KEY (session_id) REFERENCES session (id) ON DELETE CASCADE'
);
}
if (!$table->hasForeignKey('FK_4038AA4781CFDAE7')) {
$this->addSql(
'ALTER TABLE user_rel_course_vote ADD CONSTRAINT FK_4038AA4781CFDAE7 FOREIGN KEY (url_id) REFERENCES access_url (id) ON DELETE CASCADE'
);
}
if (!$table->hasIndex('IDX_4038AA47613FECDF')) {
$this->addSql('CREATE INDEX IDX_4038AA47613FECDF ON user_rel_course_vote (session_id)');
}
if (!$table->hasIndex('IDX_4038AA4781CFDAE7')) {
$this->addSql('CREATE INDEX IDX_4038AA4781CFDAE7 ON user_rel_course_vote (url_id)');
}
$table = $schema->getTable('user_rel_tag');
$this->addSql('ALTER TABLE user_rel_tag CHANGE user_id user_id INT DEFAULT NULL');
$this->addSql('DELETE FROM user_rel_tag WHERE user_id NOT IN (SELECT id FROM user)');
if (!$table->hasForeignKey('FK_D5CB75B6A76ED395')) {
$this->addSql(
'ALTER TABLE user_rel_tag ADD CONSTRAINT FK_D5CB75B6A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE'
);
}
$this->addSql('ALTER TABLE user_rel_tag CHANGE tag_id tag_id INT DEFAULT NULL');
if (!$table->hasForeignKey('FK_D5CB75B6BAD26311')) {
$this->addSql(
'ALTER TABLE user_rel_tag ADD CONSTRAINT FK_D5CB75B6BAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE'
);
}
$table = $schema->getTable('user_rel_user');
$this->addSql('DELETE FROM user_rel_user WHERE user_id = 0 OR friend_user_id = 0');
$this->addSql('DELETE FROM user_rel_user WHERE user_id IS NULL OR friend_user_id IS NULL');
$this->addSql('DELETE FROM user_rel_user WHERE user_id NOT IN (SELECT id FROM user)');
$this->addSql('DELETE FROM user_rel_user WHERE friend_user_id NOT IN (SELECT id FROM user)');
$this->addSql('ALTER TABLE user_rel_user CHANGE friend_user_id friend_user_id INT NOT NULL');
$this->addSql('ALTER TABLE user_rel_user CHANGE user_id user_id INT NOT NULL');
if ($table->hasColumn('last_edit')) {
$this->addSql('UPDATE user_rel_user SET last_edit = NOW() WHERE last_edit IS NULL');
}
// Remove duplicates.
$sql = 'SELECT max(id) id, user_id, friend_user_id, relation_type, count(*) as count
FROM user_rel_user
GROUP BY user_id, friend_user_id, relation_type
HAVING count > 1';
$result = $this->connection->executeQuery($sql);
$items = $result->fetchAllAssociative();
foreach ($items as $item) {
$userId = $item['user_id'];
$friendId = $item['friend_user_id'];
$relationType = $item['relation_type'];
$sql = "SELECT id
FROM user_rel_user
WHERE user_id = $userId AND friend_user_id = $friendId AND relation_type = $relationType ";
$result = $this->connection->executeQuery($sql);
$subItems = $result->fetchAllAssociative();
$counter = 0;
foreach ($subItems as $subItem) {
$id = $subItem['id'];
if (0 === $counter) {
$counter++;
continue;
}
$sql = "DELETE FROM user_rel_user WHERE id = $id";
$this->addSql($sql);
$counter++;
}
}
$this->addSql(
'UPDATE user_rel_user SET last_edit = NOW() WHERE CAST(last_edit AS CHAR(20)) = "0000-00-00 00:00:00"'
);
if (!$table->hasIndex('user_friend_relation')) {
$this->addSql('CREATE UNIQUE INDEX user_friend_relation ON user_rel_user (user_id, friend_user_id, relation_type)');
}
if (!$table->hasColumn('created_at')) {
$this->addSql("ALTER TABLE user_rel_user ADD created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '(DC2Type:datetime)' ");
$this->addSql('UPDATE user_rel_user SET created_at = last_edit');
}
if (!$table->hasColumn('updated_at')) {
$this->addSql("ALTER TABLE user_rel_user ADD updated_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '(DC2Type:datetime)' ");
$this->addSql('UPDATE user_rel_user SET updated_at = last_edit');
}
if (!$table->hasForeignKey('FK_DBF650A893D1119E')) {
$this->addSql(
' ALTER TABLE user_rel_user ADD CONSTRAINT FK_DBF650A893D1119E FOREIGN KEY (friend_user_id) REFERENCES user (id) ON DELETE CASCADE;'
);
}
if (false === $table->hasForeignKey('FK_DBF650A8A76ED395')) {
$this->addSql(
'ALTER TABLE user_rel_user ADD CONSTRAINT FK_DBF650A8A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE'
);
}
}
public function down(Schema $schema): void {}
}