migrations/Version20171109170243.php line 1
<?php
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Another edit of tables and create NEWS, NEWS_CAT and NEWS_NEWS_CAT
*/
class Version20171109170243 extends AbstractMigration
{
/**
* @param Schema $schema
*/
public function up(Schema $schema) : void
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');
$this->addSql('CREATE SEQUENCE seq_users_id INCREMENT BY 1 MINVALUE 1 START 1;');
$this->addSql('CREATE SEQUENCE seq_scenario_id INCREMENT BY 1 MINVALUE 1 START 1;');
$this->addSql('CREATE SEQUENCE seq_node_id INCREMENT BY 1 MINVALUE 1 START 1;');
$this->addSql('CREATE SEQUENCE seq_media_id INCREMENT BY 1 MINVALUE 1 START 1;');
$this->addSql('CREATE SEQUENCE seq_link_id INCREMENT BY 1 MINVALUE 1 START 1;');
$this->addSql('CREATE SEQUENCE seq_mcq_id INCREMENT BY 1 MINVALUE 1 START 1;');
$this->addSql('CREATE SEQUENCE seq_player_sessionhist_id INCREMENT BY 1 MINVALUE 1 START 1;');
$this->addSql('CREATE TABLE users (
id int4 NOT NULL,
username varchar(96) NOT NULL,
password varchar(128) NOT NULL,
salt varchar(96) NOT NULL,
first_name varchar(64),
last_name varchar(64),
email varchar(128),
phone varchar(64),
institution varchar(128),
address varchar(255),
city varchar(128),
postalcode varchar(15),
settings json,
auth_modules text,
auth_role varchar(20) NOT NULL,
lastvisit_at timestamp(6),
created_at timestamp(6) NOT NULL,
lastmod_at timestamp(6),
is_deleted boolean DEFAULT FALSE NOT NULL,
PRIMARY KEY (id));
');
$this->addSql('CREATE TABLE scenario (
id int4 NOT NULL,
users_id int4 NOT NULL,
title varchar(255) NOT NULL,
summary_intro text NOT NULL,
summary_epilog text NOT NULL,
state int2 DEFAULT 0 NOT NULL,
is_deleted boolean DEFAULT FALSE NOT NULL,
created_at timestamp(6) NOT NULL,
lastmod_at timestamp(6),
PRIMARY KEY (id));
');
$this->addSql('CREATE TABLE node (
id int4 NOT NULL,
scenario_id int4,
title varchar(255) NOT NULL,
description text NOT NULL,
links_order int4 DEFAULT 0 NOT NULL,
parameters json NOT NULL,
priority int4 DEFAULT 0 NOT NULL,
active int2 DEFAULT 0 NOT NULL,
is_deleted boolean DEFAULT FALSE NOT NULL,
created_at timestamp(6) NOT NULL,
lastmod_at timestamp(6),
PRIMARY KEY (id));
');
$this->addSql('CREATE TABLE media (
id int4 NOT NULL,
scenario_id int4,
node_id int4,
link_id int4,
mcq_id int4,
category int4,
filename varchar(255),
node_priority int4 DEFAULT 0 NOT NULL,
active int2 DEFAULT 0 NOT NULL,
is_deleted boolean DEFAULT FALSE NOT NULL,
created_at timestamp(6) NOT NULL,
lastmod_at timestamp(6),
PRIMARY KEY (id));
');
$this->addSql('CREATE TABLE link (
id int4 NOT NULL,
node_id int4 NOT NULL,
description varchar(255) NOT NULL,
feedback text NOT NULL,
score int4 DEFAULT 0 NOT NULL,
preferred_order int4 DEFAULT 0 NOT NULL,
correct int2 DEFAULT 0 NOT NULL,
active int2 DEFAULT 0 NOT NULL,
is_deleted boolean DEFAULT FALSE NOT NULL,
created_at timestamp(6) NOT NULL,
lastmod_at timestamp(6),
PRIMARY KEY (id));
');
$this->addSql('CREATE TABLE mcq (
id int4 NOT NULL,
node_id int4 NOT NULL,
description varchar(255) NOT NULL,
options json NOT NULL,
active int2 DEFAULT 0 NOT NULL,
is_deleted boolean DEFAULT FALSE NOT NULL,
created_at timestamp(6) NOT NULL,
lastmod_at timestamp(6),
PRIMARY KEY (id));
');
$this->addSql('CREATE TABLE player_sessionhist (
id int4 NOT NULL,
users_id int4,
scenario_id int4,
node_id int4,
mcq_id int4,
link_id int4,
session_id varchar(64),
score int4 DEFAULT 0 NOT NULL,
mcq_score int4 DEFAULT 0 NOT NULL,
correct int2 DEFAULT 0 NOT NULL,
created_at timestamp(6) NOT NULL,
PRIMARY KEY (id));
');
$this->addSql('ALTER TABLE media ADD CONSTRAINT fk_media_scenario FOREIGN KEY (scenario_id) REFERENCES scenario (id);');
$this->addSql('ALTER TABLE node ADD CONSTRAINT fk_node_scenario FOREIGN KEY (scenario_id) REFERENCES scenario (id);');
$this->addSql('ALTER TABLE scenario ADD CONSTRAINT fk_scenario_users FOREIGN KEY (users_id) REFERENCES users (id);');
$this->addSql('ALTER TABLE media ADD CONSTRAINT fk_media_node FOREIGN KEY (node_id) REFERENCES node (id);');
$this->addSql('ALTER TABLE link ADD CONSTRAINT fk_link_node FOREIGN KEY (node_id) REFERENCES node (id);');
$this->addSql('ALTER TABLE media ADD CONSTRAINT fk_media_link FOREIGN KEY (link_id) REFERENCES link (id);');
$this->addSql('ALTER TABLE mcq ADD CONSTRAINT fk_mcq_node FOREIGN KEY (node_id) REFERENCES node (id);');
$this->addSql('ALTER TABLE media ADD CONSTRAINT fk_media_mcq FOREIGN KEY (mcq_id) REFERENCES mcq (id);');
$this->addSql('ALTER TABLE player_sessionhist ADD CONSTRAINT fk_player_ses_users FOREIGN KEY (users_id) REFERENCES users (id);');
$this->addSql('ALTER TABLE player_sessionhist ADD CONSTRAINT fk_player_ses_scenario FOREIGN KEY (scenario_id) REFERENCES scenario (id);');
$this->addSql('ALTER TABLE player_sessionhist ADD CONSTRAINT fk_player_ses_node FOREIGN KEY (node_id) REFERENCES node (id);');
$this->addSql('ALTER TABLE player_sessionhist ADD CONSTRAINT fk_player_ses_mcq FOREIGN KEY (mcq_id) REFERENCES mcq (id);');
$this->addSql('ALTER TABLE player_sessionhist ADD CONSTRAINT fk_player_ses FOREIGN KEY (link_id) REFERENCES link (id);');
/* $this->addSql('CREATE SEQUENCE seq_news_cat_id INCREMENT BY 1 MINVALUE 1 START 1');
$this->addSql('CREATE SEQUENCE seq_news_id INCREMENT BY 1 MINVALUE 1 START 1');
$this->addSql('CREATE TABLE news_cat (id INT NOT NULL, name VARCHAR(255) NOT NULL, type INT DEFAULT 0 NOT NULL, auth_type INT DEFAULT 0 NOT NULL, state BOOLEAN DEFAULT \'false\' NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, lastmod_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL NOT NULL, is_deleted BOOLEAN DEFAULT \'false\' NOT NULL, PRIMARY KEY(id))');
$this->addSql('CREATE TABLE news_news_cat (news_cat_id INT NOT NULL, news_id INT NOT NULL, PRIMARY KEY(news_cat_id, news_id))');
$this->addSql('CREATE INDEX IDX_6AD78FDB6A046A37 ON news_news_cat (news_cat_id)');
$this->addSql('CREATE INDEX IDX_6AD78FDBB5A459A0 ON news_news_cat (news_id)');
$this->addSql('CREATE TABLE news (id INT NOT NULL, users_id INT DEFAULT NULL, title VARCHAR(255) NOT NULL, header TEXT DEFAULT NULL, body TEXT DEFAULT NULL, footer VARCHAR(255) DEFAULT NULL, keywords VARCHAR(255) DEFAULT NULL, attach_file VARCHAR(255) DEFAULT NULL, start_from TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, end_to TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, place VARCHAR(255) DEFAULT NULL, price_info VARCHAR(255) DEFAULT NULL, limits INT DEFAULT NULL, valid_from DATE DEFAULT NULL, valid_to DATE DEFAULT NULL, special_fnc BOOLEAN DEFAULT NULL, special_fnc_val TEXT DEFAULT NULL, language VARCHAR(2) DEFAULT \'cs\', state BOOLEAN DEFAULT \'false\' NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, lastmod_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, is_deleted BOOLEAN NOT NULL, PRIMARY KEY(id))');
$this->addSql('CREATE INDEX users_id ON news (users_id)');
$this->addSql('ALTER TABLE news_news_cat ADD CONSTRAINT fk_news_news_cat_news_cat FOREIGN KEY (news_cat_id) REFERENCES news_cat (id) NOT DEFERRABLE INITIALLY IMMEDIATE');
$this->addSql('ALTER TABLE news_news_cat ADD CONSTRAINT fk_news_news_cat_news FOREIGN KEY (news_id) REFERENCES news (id) NOT DEFERRABLE INITIALLY IMMEDIATE');
$this->addSql('ALTER TABLE news ADD CONSTRAINT fk_news_users FOREIGN KEY (users_id) REFERENCES users (id) NOT DEFERRABLE INITIALLY IMMEDIATE');*/
}
/**
* @param Schema $schema
*/
public function down(Schema $schema) : void
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');
$this->addSql('DROP SEQUENCE seq_users_id');
$this->addSql('DROP SEQUENCE seq_scenario_id');
$this->addSql('DROP SEQUENCE seq_node_id');
$this->addSql('DROP SEQUENCE seq_media_id');
$this->addSql('DROP SEQUENCE seq_link_id');
$this->addSql('DROP SEQUENCE seq_mcq_id');
$this->addSql('DROP SEQUENCE seq_player_sessionhist_id');
$this->addSql('DROP TABLE users');
$this->addSql('DROP TABLE scenario');
$this->addSql('DROP TABLE node');
$this->addSql('DROP TABLE media');
$this->addSql('DROP TABLE link');
$this->addSql('DROP TABLE mcq');
$this->addSql('DROP TABLE player_sessionhist');
//$this->addSql('CREATE SCHEMA public');
/* $this->addSql('ALTER TABLE news_news_cat DROP CONSTRAINT fk_news_news_cat_news_cat');
$this->addSql('ALTER TABLE news_news_cat DROP CONSTRAINT fk_news_news_cat_news');
$this->addSql('DROP SEQUENCE seq_news_cat_id CASCADE');
$this->addSql('DROP SEQUENCE seq_news_id CASCADE');
$this->addSql('DROP TABLE news_cat');
$this->addSql('DROP TABLE news_news_cat');
$this->addSql('DROP TABLE news');*/
}
}