1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
<?php
namespace Application\Migrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
use Symfony\Component\DependencyInjection\ContainerAwareInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;
/**
* Initial database structure.
*/
class Version20160401000000 extends AbstractMigration implements ContainerAwareInterface
{
/**
* @var ContainerInterface
*/
private $container;
public function setContainer(ContainerInterface $container = null)
{
$this->container = $container;
}
/**
* @param Schema $schema
*/
public function up(Schema $schema)
{
$this->skipIf($schema->hasTable($this->getTable('entry')), 'Database already initialized');
$databaseTablePrefix = $this->container->getParameter('database_table_prefix');
switch ($this->connection->getDatabasePlatform()->getName()) {
case 'sqlite':
$sql = <<<SQL
CREATE TABLE ${databaseTablePrefix}craue_config_setting (name VARCHAR(255) NOT NULL, value VARCHAR(255) DEFAULT NULL, section VARCHAR(255) DEFAULT NULL, PRIMARY KEY(name));
CREATE UNIQUE INDEX UNIQ_5D9649505E237E06 ON ${databaseTablePrefix}craue_config_setting (name);
CREATE TABLE "${databaseTablePrefix}tagging_rule" (id INTEGER NOT NULL, config_id INTEGER DEFAULT NULL, rule VARCHAR(255) NOT NULL, tags CLOB NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_2D9B3C5424DB0683 FOREIGN KEY (config_id) REFERENCES "${databaseTablePrefix}config" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE INDEX IDX_2D9B3C5424DB0683 ON "${databaseTablePrefix}tagging_rule" (config_id);
CREATE TABLE "${databaseTablePrefix}tag" (id INTEGER NOT NULL, label CLOB NOT NULL, slug VARCHAR(128) NOT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_4CA58A8C989D9B62 ON "${databaseTablePrefix}tag" (slug);
CREATE TABLE "${databaseTablePrefix}entry" (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, title CLOB DEFAULT NULL, url CLOB DEFAULT NULL, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content CLOB DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype CLOB DEFAULT NULL, language CLOB DEFAULT NULL, reading_time INTEGER DEFAULT NULL, domain_name CLOB DEFAULT NULL, preview_picture CLOB DEFAULT NULL, is_public BOOLEAN DEFAULT '0', PRIMARY KEY(id), CONSTRAINT FK_F4D18282A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE INDEX IDX_F4D18282A76ED395 ON "${databaseTablePrefix}entry" (user_id);
CREATE TABLE ${databaseTablePrefix}entry_tag (entry_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(entry_id, tag_id), CONSTRAINT FK_C9F0DD7CBA364942 FOREIGN KEY (entry_id) REFERENCES "${databaseTablePrefix}entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_C9F0DD7CBAD26311 FOREIGN KEY (tag_id) REFERENCES "${databaseTablePrefix}tag" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE INDEX IDX_C9F0DD7CBA364942 ON ${databaseTablePrefix}entry_tag (entry_id);
CREATE INDEX IDX_C9F0DD7CBAD26311 ON ${databaseTablePrefix}entry_tag (tag_id);
CREATE TABLE "${databaseTablePrefix}config" (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, theme VARCHAR(255) NOT NULL, items_per_page INTEGER NOT NULL, language VARCHAR(255) NOT NULL, rss_token VARCHAR(255) DEFAULT NULL, rss_limit INTEGER DEFAULT NULL, reading_speed DOUBLE PRECISION DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_87E64C53A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE UNIQUE INDEX UNIQ_87E64C53A76ED395 ON "${databaseTablePrefix}config" (user_id);
CREATE TABLE ${databaseTablePrefix}oauth2_refresh_tokens (id INTEGER NOT NULL, client_id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INTEGER DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_20C9FB2419EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_20C9FB24A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE UNIQUE INDEX UNIQ_20C9FB245F37A13B ON ${databaseTablePrefix}oauth2_refresh_tokens (token);
CREATE INDEX IDX_20C9FB2419EB6921 ON ${databaseTablePrefix}oauth2_refresh_tokens (client_id);
CREATE INDEX IDX_20C9FB24A76ED395 ON ${databaseTablePrefix}oauth2_refresh_tokens (user_id);
CREATE TABLE ${databaseTablePrefix}oauth2_access_tokens (id INTEGER NOT NULL, client_id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INTEGER DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_368A420919EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_368A4209A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE UNIQUE INDEX UNIQ_368A42095F37A13B ON ${databaseTablePrefix}oauth2_access_tokens (token);
CREATE INDEX IDX_368A420919EB6921 ON ${databaseTablePrefix}oauth2_access_tokens (client_id);
CREATE INDEX IDX_368A4209A76ED395 ON ${databaseTablePrefix}oauth2_access_tokens (user_id);
CREATE TABLE ${databaseTablePrefix}oauth2_auth_codes (id INTEGER NOT NULL, client_id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri CLOB NOT NULL, expires_at INTEGER DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_EE52E3FA19EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_EE52E3FAA76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE UNIQUE INDEX UNIQ_EE52E3FA5F37A13B ON ${databaseTablePrefix}oauth2_auth_codes (token);
CREATE INDEX IDX_EE52E3FA19EB6921 ON ${databaseTablePrefix}oauth2_auth_codes (client_id);
CREATE INDEX IDX_EE52E3FAA76ED395 ON ${databaseTablePrefix}oauth2_auth_codes (user_id);
CREATE TABLE ${databaseTablePrefix}oauth2_clients (id INTEGER NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris CLOB NOT NULL, secret VARCHAR(255) NOT NULL, allowed_grant_types CLOB NOT NULL, PRIMARY KEY(id));
CREATE TABLE "${databaseTablePrefix}user" (id INTEGER NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled BOOLEAN NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked BOOLEAN NOT NULL, expired BOOLEAN NOT NULL, expires_at DATETIME DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles CLOB NOT NULL, credentials_expired BOOLEAN NOT NULL, credentials_expire_at DATETIME DEFAULT NULL, name CLOB DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, authCode INTEGER DEFAULT NULL, twoFactorAuthentication BOOLEAN NOT NULL, trusted CLOB DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_1D63E7E592FC23A8 ON "${databaseTablePrefix}user" (username_canonical);
CREATE UNIQUE INDEX UNIQ_1D63E7E5A0D96FBF ON "${databaseTablePrefix}user" (email_canonical);
CREATE UNIQUE INDEX UNIQ_1D63E7E5C05FB297 ON "${databaseTablePrefix}user" (confirmation_token);
CREATE TABLE ${databaseTablePrefix}annotation (id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, entry_id INTEGER DEFAULT NULL, text CLOB NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, quote VARCHAR(255) NOT NULL, ranges CLOB NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_A7AED006A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_A7AED006BA364942 FOREIGN KEY (entry_id) REFERENCES "${databaseTablePrefix}entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
CREATE INDEX IDX_A7AED006A76ED395 ON ${databaseTablePrefix}annotation (user_id);
CREATE INDEX IDX_A7AED006BA364942 ON ${databaseTablePrefix}annotation (entry_id);
SQL
;
foreach (explode("\n", $sql) as $query) {
$this->addSql($query);
}
break;
case 'mysql':
$sql = <<<SQL
CREATE TABLE ${databaseTablePrefix}craue_config_setting (name VARCHAR(255) NOT NULL, value VARCHAR(255) DEFAULT NULL, section VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_5D9649505E237E06 (name), PRIMARY KEY(name)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE `${databaseTablePrefix}entry` (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, title LONGTEXT DEFAULT NULL, url LONGTEXT DEFAULT NULL, is_archived TINYINT(1) NOT NULL, is_starred TINYINT(1) NOT NULL, content LONGTEXT DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, mimetype LONGTEXT DEFAULT NULL, language LONGTEXT DEFAULT NULL, reading_time INT DEFAULT NULL, domain_name LONGTEXT DEFAULT NULL, preview_picture LONGTEXT DEFAULT NULL, is_public TINYINT(1) DEFAULT '0', INDEX IDX_F4D18282A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE ${databaseTablePrefix}entry_tag (entry_id INT NOT NULL, tag_id INT NOT NULL, INDEX IDX_C9F0DD7CBA364942 (entry_id), INDEX IDX_C9F0DD7CBAD26311 (tag_id), PRIMARY KEY(entry_id, tag_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE `${databaseTablePrefix}config` (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, theme VARCHAR(255) NOT NULL, items_per_page INT NOT NULL, language VARCHAR(255) NOT NULL, rss_token VARCHAR(255) DEFAULT NULL, rss_limit INT DEFAULT NULL, reading_speed DOUBLE PRECISION DEFAULT NULL, UNIQUE INDEX UNIQ_87E64C53A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE `${databaseTablePrefix}tagging_rule` (id INT AUTO_INCREMENT NOT NULL, config_id INT DEFAULT NULL, rule VARCHAR(255) NOT NULL, tags LONGTEXT NOT NULL COMMENT '(DC2Type:simple_array)', INDEX IDX_2D9B3C5424DB0683 (config_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE `${databaseTablePrefix}tag` (id INT AUTO_INCREMENT NOT NULL, `label` LONGTEXT NOT NULL, slug VARCHAR(128) NOT NULL, UNIQUE INDEX UNIQ_4CA58A8C989D9B62 (slug), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE ${databaseTablePrefix}oauth2_clients (id INT AUTO_INCREMENT NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris LONGTEXT NOT NULL COMMENT '(DC2Type:array)', secret VARCHAR(255) NOT NULL, allowed_grant_types LONGTEXT NOT NULL COMMENT '(DC2Type:array)', PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE ${databaseTablePrefix}oauth2_access_tokens (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_368A42095F37A13B (token), INDEX IDX_368A420919EB6921 (client_id), INDEX IDX_368A4209A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE ${databaseTablePrefix}oauth2_refresh_tokens (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_20C9FB245F37A13B (token), INDEX IDX_20C9FB2419EB6921 (client_id), INDEX IDX_20C9FB24A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE ${databaseTablePrefix}oauth2_auth_codes (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri LONGTEXT NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_EE52E3FA5F37A13B (token), INDEX IDX_EE52E3FA19EB6921 (client_id), INDEX IDX_EE52E3FAA76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE `${databaseTablePrefix}user` (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, locked TINYINT(1) NOT NULL, expired TINYINT(1) NOT NULL, expires_at DATETIME DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT '(DC2Type:array)', credentials_expired TINYINT(1) NOT NULL, credentials_expire_at DATETIME DEFAULT NULL, name LONGTEXT DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, authCode INT DEFAULT NULL, twoFactorAuthentication TINYINT(1) NOT NULL, trusted LONGTEXT DEFAULT NULL COMMENT '(DC2Type:json_array)', UNIQUE INDEX UNIQ_1D63E7E592FC23A8 (username_canonical), UNIQUE INDEX UNIQ_1D63E7E5A0D96FBF (email_canonical), UNIQUE INDEX UNIQ_1D63E7E5C05FB297 (confirmation_token), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE ${databaseTablePrefix}annotation (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, entry_id INT DEFAULT NULL, text LONGTEXT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, quote VARCHAR(255) NOT NULL, ranges LONGTEXT NOT NULL COMMENT '(DC2Type:array)', INDEX IDX_A7AED006A76ED395 (user_id), INDEX IDX_A7AED006BA364942 (entry_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE `${databaseTablePrefix}entry` ADD CONSTRAINT FK_F4D18282A76ED395 FOREIGN KEY (user_id) REFERENCES `${databaseTablePrefix}user` (id);
ALTER TABLE ${databaseTablePrefix}entry_tag ADD CONSTRAINT FK_C9F0DD7CBA364942 FOREIGN KEY (entry_id) REFERENCES `${databaseTablePrefix}entry` (id);
ALTER TABLE ${databaseTablePrefix}entry_tag ADD CONSTRAINT FK_C9F0DD7CBAD26311 FOREIGN KEY (tag_id) REFERENCES `${databaseTablePrefix}tag` (id);
ALTER TABLE `${databaseTablePrefix}config` ADD CONSTRAINT FK_87E64C53A76ED395 FOREIGN KEY (user_id) REFERENCES `${databaseTablePrefix}user` (id);
ALTER TABLE `${databaseTablePrefix}tagging_rule` ADD CONSTRAINT FK_2D9B3C5424DB0683 FOREIGN KEY (config_id) REFERENCES `${databaseTablePrefix}config` (id);
ALTER TABLE ${databaseTablePrefix}oauth2_access_tokens ADD CONSTRAINT FK_368A420919EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id);
ALTER TABLE ${databaseTablePrefix}oauth2_access_tokens ADD CONSTRAINT FK_368A4209A76ED395 FOREIGN KEY (user_id) REFERENCES `${databaseTablePrefix}user` (id);
ALTER TABLE ${databaseTablePrefix}oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB2419EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id);
ALTER TABLE ${databaseTablePrefix}oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB24A76ED395 FOREIGN KEY (user_id) REFERENCES `${databaseTablePrefix}user` (id);
ALTER TABLE ${databaseTablePrefix}oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FA19EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id);
ALTER TABLE ${databaseTablePrefix}oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FAA76ED395 FOREIGN KEY (user_id) REFERENCES `${databaseTablePrefix}user` (id);
ALTER TABLE ${databaseTablePrefix}annotation ADD CONSTRAINT FK_A7AED006A76ED395 FOREIGN KEY (user_id) REFERENCES `${databaseTablePrefix}user` (id);
ALTER TABLE ${databaseTablePrefix}annotation ADD CONSTRAINT FK_A7AED006BA364942 FOREIGN KEY (entry_id) REFERENCES `${databaseTablePrefix}entry` (id);
SQL
;
foreach (explode("\n", $sql) as $query) {
$this->addSql($query);
}
break;
case 'postgresql':
$sql = <<<SQL
CREATE TABLE ${databaseTablePrefix}craue_config_setting (name VARCHAR(255) NOT NULL, value VARCHAR(255) DEFAULT NULL, section VARCHAR(255) DEFAULT NULL, PRIMARY KEY(name));
CREATE UNIQUE INDEX UNIQ_5D9649505E237E06 ON ${databaseTablePrefix}craue_config_setting (name);
CREATE TABLE "${databaseTablePrefix}entry" (id INT NOT NULL, user_id INT DEFAULT NULL, title TEXT DEFAULT NULL, url TEXT DEFAULT NULL, is_archived BOOLEAN NOT NULL, is_starred BOOLEAN NOT NULL, content TEXT DEFAULT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, mimetype TEXT DEFAULT NULL, language TEXT DEFAULT NULL, reading_time INT DEFAULT NULL, domain_name TEXT DEFAULT NULL, preview_picture TEXT DEFAULT NULL, is_public BOOLEAN DEFAULT 'false', PRIMARY KEY(id));
CREATE INDEX IDX_F4D18282A76ED395 ON "${databaseTablePrefix}entry" (user_id);
CREATE TABLE ${databaseTablePrefix}entry_tag (entry_id INT NOT NULL, tag_id INT NOT NULL, PRIMARY KEY(entry_id, tag_id));
CREATE INDEX IDX_C9F0DD7CBA364942 ON ${databaseTablePrefix}entry_tag (entry_id);
CREATE INDEX IDX_C9F0DD7CBAD26311 ON ${databaseTablePrefix}entry_tag (tag_id);
CREATE TABLE "${databaseTablePrefix}config" (id INT NOT NULL, user_id INT DEFAULT NULL, theme VARCHAR(255) NOT NULL, items_per_page INT NOT NULL, language VARCHAR(255) NOT NULL, rss_token VARCHAR(255) DEFAULT NULL, rss_limit INT DEFAULT NULL, reading_speed DOUBLE PRECISION DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_87E64C53A76ED395 ON "${databaseTablePrefix}config" (user_id);
CREATE TABLE "${databaseTablePrefix}tagging_rule" (id INT NOT NULL, config_id INT DEFAULT NULL, rule VARCHAR(255) NOT NULL, tags TEXT NOT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_2D9B3C5424DB0683 ON "${databaseTablePrefix}tagging_rule" (config_id);
COMMENT ON COLUMN "${databaseTablePrefix}tagging_rule".tags IS '(DC2Type:simple_array)';
CREATE TABLE "${databaseTablePrefix}tag" (id INT NOT NULL, label TEXT NOT NULL, slug VARCHAR(128) NOT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_4CA58A8C989D9B62 ON "${databaseTablePrefix}tag" (slug);
CREATE TABLE ${databaseTablePrefix}oauth2_clients (id INT NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris TEXT NOT NULL, secret VARCHAR(255) NOT NULL, allowed_grant_types TEXT NOT NULL, PRIMARY KEY(id));
COMMENT ON COLUMN ${databaseTablePrefix}oauth2_clients.redirect_uris IS '(DC2Type:array)';
COMMENT ON COLUMN ${databaseTablePrefix}oauth2_clients.allowed_grant_types IS '(DC2Type:array)';
CREATE TABLE ${databaseTablePrefix}oauth2_access_tokens (id INT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_368A42095F37A13B ON ${databaseTablePrefix}oauth2_access_tokens (token);
CREATE INDEX IDX_368A420919EB6921 ON ${databaseTablePrefix}oauth2_access_tokens (client_id);
CREATE INDEX IDX_368A4209A76ED395 ON ${databaseTablePrefix}oauth2_access_tokens (user_id);
CREATE TABLE ${databaseTablePrefix}oauth2_refresh_tokens (id INT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_20C9FB245F37A13B ON ${databaseTablePrefix}oauth2_refresh_tokens (token);
CREATE INDEX IDX_20C9FB2419EB6921 ON ${databaseTablePrefix}oauth2_refresh_tokens (client_id);
CREATE INDEX IDX_20C9FB24A76ED395 ON ${databaseTablePrefix}oauth2_refresh_tokens (user_id);
CREATE TABLE ${databaseTablePrefix}oauth2_auth_codes (id INT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri TEXT NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_EE52E3FA5F37A13B ON ${databaseTablePrefix}oauth2_auth_codes (token);
CREATE INDEX IDX_EE52E3FA19EB6921 ON ${databaseTablePrefix}oauth2_auth_codes (client_id);
CREATE INDEX IDX_EE52E3FAA76ED395 ON ${databaseTablePrefix}oauth2_auth_codes (user_id);
CREATE TABLE "${databaseTablePrefix}user" (id INT NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled BOOLEAN NOT NULL, salt VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, last_login TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, locked BOOLEAN NOT NULL, expired BOOLEAN NOT NULL, expires_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, confirmation_token VARCHAR(255) DEFAULT NULL, password_requested_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, roles TEXT NOT NULL, credentials_expired BOOLEAN NOT NULL, credentials_expire_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, name TEXT DEFAULT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, authCode INT DEFAULT NULL, twoFactorAuthentication BOOLEAN NOT NULL, trusted TEXT DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX UNIQ_1D63E7E592FC23A8 ON "${databaseTablePrefix}user" (username_canonical);
CREATE UNIQUE INDEX UNIQ_1D63E7E5A0D96FBF ON "${databaseTablePrefix}user" (email_canonical);
CREATE UNIQUE INDEX UNIQ_1D63E7E5C05FB297 ON "${databaseTablePrefix}user" (confirmation_token);
COMMENT ON COLUMN "${databaseTablePrefix}user".roles IS '(DC2Type:array)';
COMMENT ON COLUMN "${databaseTablePrefix}user".trusted IS '(DC2Type:json_array)';
CREATE TABLE ${databaseTablePrefix}annotation (id INT NOT NULL, user_id INT DEFAULT NULL, entry_id INT DEFAULT NULL, text TEXT NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, quote VARCHAR(255) NOT NULL, ranges TEXT NOT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_A7AED006A76ED395 ON ${databaseTablePrefix}annotation (user_id);
CREATE INDEX IDX_A7AED006BA364942 ON ${databaseTablePrefix}annotation (entry_id);
COMMENT ON COLUMN ${databaseTablePrefix}annotation.ranges IS '(DC2Type:array)';
CREATE SEQUENCE "entry_id_seq" INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE "config_id_seq" INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE "tagging_rule_id_seq" INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE "tag_id_seq" INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE oauth2_clients_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE oauth2_access_tokens_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE oauth2_refresh_tokens_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE oauth2_auth_codes_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE "user_id_seq" INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE annotation_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
ALTER TABLE "${databaseTablePrefix}entry" ADD CONSTRAINT FK_F4D18282A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}entry_tag ADD CONSTRAINT FK_C9F0DD7CBA364942 FOREIGN KEY (entry_id) REFERENCES "${databaseTablePrefix}entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}entry_tag ADD CONSTRAINT FK_C9F0DD7CBAD26311 FOREIGN KEY (tag_id) REFERENCES "${databaseTablePrefix}tag" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE "${databaseTablePrefix}config" ADD CONSTRAINT FK_87E64C53A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE "${databaseTablePrefix}tagging_rule" ADD CONSTRAINT FK_2D9B3C5424DB0683 FOREIGN KEY (config_id) REFERENCES "${databaseTablePrefix}config" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}oauth2_access_tokens ADD CONSTRAINT FK_368A420919EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}oauth2_access_tokens ADD CONSTRAINT FK_368A4209A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB2419EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}oauth2_refresh_tokens ADD CONSTRAINT FK_20C9FB24A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FA19EB6921 FOREIGN KEY (client_id) REFERENCES ${databaseTablePrefix}oauth2_clients (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}oauth2_auth_codes ADD CONSTRAINT FK_EE52E3FAA76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}annotation ADD CONSTRAINT FK_A7AED006A76ED395 FOREIGN KEY (user_id) REFERENCES "${databaseTablePrefix}user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE ${databaseTablePrefix}annotation ADD CONSTRAINT FK_A7AED006BA364942 FOREIGN KEY (entry_id) REFERENCES "${databaseTablePrefix}entry" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
SQL
;
foreach (explode("\n", $sql) as $query) {
$this->addSql($query);
}
break;
}
}
/**
* @param Schema $schema
*/
public function down(Schema $schema)
{
$databaseTablePrefix = $this->container->getParameter('database_table_prefix');
$this->addSql("DROP TABLE ${databaseTablePrefix}craue_config_setting");
$this->addSql("DROP TABLE ${databaseTablePrefix}tagging_rule");
$this->addSql("DROP TABLE ${databaseTablePrefix}config");
$this->addSql("DROP TABLE ${databaseTablePrefix}entry");
$this->addSql("DROP TABLE ${databaseTablePrefix}entry_tag");
$this->addSql("DROP TABLE ${databaseTablePrefix}tag");
$this->addSql("DROP TABLE ${databaseTablePrefix}oauth2_refresh_tokens");
$this->addSql("DROP TABLE ${databaseTablePrefix}oauth2_access_tokens");
$this->addSql("DROP TABLE ${databaseTablePrefix}oauth2_clients");
$this->addSql("DROP TABLE ${databaseTablePrefix}oauth2_auth_codes");
$this->addSql("DROP TABLE ${databaseTablePrefix}user");
$this->addSql("DROP TABLE ${databaseTablePrefix}annotation");
}
private function getTable($tableName)
{
return $this->container->getParameter('database_table_prefix') . $tableName;
}
}
|
