keii.dev
the browser-facing portion of osu!
1<?php
2
3// Copyright (c) ppy Pty Ltd <contact@ppy.sh>. Licensed under the GNU Affero General Public License v3.0.
4// See the LICENCE file in the repository root for full licence text.
5use Illuminate\Database\Migrations\Migration;
6use Illuminate\Database\Schema\Blueprint;
7
8class BaseTables extends Migration
9{
10 /**
11 * Run the migrations.
12 *
13 * @return void
14 */
15 public function up()
16 {
17 Schema::create('osu_achievements', function (Blueprint $table) {
18 $table->mediumIncrements('achievement_id');
19 $table->string('name', 40);
20 $table->string('image', 50);
21 $table->string('grouping', 30)->default('-');
22 $table->unsignedTinyInteger('ordering');
23 $table->unsignedTinyInteger('progression');
24 $table->tinyInteger('quest_ordering')->nullable();
25 $table->text('quest_instructions')->nullable();
26
27 $table->index(['grouping', 'ordering'], 'display_order');
28 $table->index('quest_ordering', 'quest_ordering');
29 });
30
31 Schema::create('osu_apikeys', function (Blueprint $table) {
32 $table->increments('key');
33 $table->unsignedInteger('user_id');
34 $table->string('app_name', 100)->default('');
35 $table->string('app_url', 100)->default('');
36 $table->string('api_key', 52)->default('');
37 $table->boolean('enabled')->default(true);
38 $table->UnsignedBigInteger('hit_count')->default(0);
39 $table->UnsignedInteger('miss_count')->default(0);
40 $table->tinyInteger('revoked')->default(0);
41
42 $table->unique('api_key', 'api_key');
43 });
44
45 Schema::create('osu_badges', function (Blueprint $table) {
46 $table->unsignedMediumInteger('user_id');
47 $table->string('image', 255);
48 $table->string('description', 255);
49 $table->timestamp('awarded')->nullable()->useCurrent();
50 $table->primary(['user_id', 'image']);
51 });
52
53 Schema::create('osu_beatmap_difficulty', function (Blueprint $table) {
54 $table->unsignedInteger('beatmap_id');
55 $table->tinyInteger('mode')->default(0);
56 $table->unsignedInteger('mods');
57 $table->float('diff_unified', null, null); // creates a double instead of float.
58 $table->timestamp('last_update')->useCurrent();
59
60 $table->primary(['beatmap_id', 'mode', 'mods'], 'osu_beatmap_difficulty_primary');
61 $table->index(['mode', 'mods', 'diff_unified'], 'diff_sort');
62 });
63
64 Schema::create('osu_beatmap_difficulty_attribs', function (Blueprint $table) {
65 $table->unsignedMediumInteger('beatmap_id');
66 $table->unsignedTinyInteger('mode');
67 $table->unsignedInteger('mods');
68 $table->unsignedTinyInteger('attrib_id')->comment('see osu_difficulty_attribs table');
69
70 $table->primary(['beatmap_id', 'mode', 'mods', 'attrib_id'], 'attribs_primary');
71 });
72
73 DB::statement('ALTER TABLE osu_beatmap_difficulty_attribs ADD value float null');
74
75 Schema::create('osu_beatmaps', function (Blueprint $table) {
76 $table->mediumIncrements('beatmap_id');
77 $table->unsignedMediumInteger('beatmapset_id')->nullable();
78 $table->unsignedMediumInteger('user_id')->default('0');
79 $table->string('filename', 150)->nullable();
80 $table->string('checksum', 32)->nullable();
81 $table->string('version', 80)->default('');
82 $table->UnsignedMediumInteger('total_length')->default(0);
83 $table->UnsignedMediumInteger('hit_length')->default(0);
84 $table->UnsignedSmallInteger('countTotal')->default(0);
85 $table->UnsignedSmallInteger('countNormal')->default(0);
86 $table->UnsignedSmallInteger('countSlider')->default(0);
87 $table->UnsignedSmallInteger('countSpinner')->default(0);
88 $table->float('diff_drain')->unsigned()->default(0);
89 $table->float('diff_size')->unsigned()->default(0);
90 $table->float('diff_overall')->unsigned()->default(0);
91 $table->float('diff_approach')->unsigned()->default(0);
92 $table->UnsignedTinyInteger('playmode')->default(0);
93 $table->TinyInteger('approved')->default(0);
94 $table->timestamp('last_update')->useCurrent();
95 $table->float('difficultyrating')->default(0);
96 $table->UnsignedMediumInteger('playcount')->default(0);
97 $table->UnsignedMediumInteger('passcount')->default(0);
98 $table->boolean('orphaned')->default(false);
99 $table->string('youtube_preview', 50)->nullable();
100 $table->index('beatmapset_id', 'beatmapset_id');
101 $table->index('filename', 'filename');
102 $table->index('checksum', 'checksum');
103 $table->index('user_id', 'user_id');
104 });
105
106
107 Schema::create('osu_beatmapsets', function (Blueprint $table) {
108 $table->mediumIncrements('beatmapset_id');
109 $table->mediumInteger('user_id')->unsigned()->default(0);
110 $table->mediumInteger('thread_id')->unsigned()->default(0);
111 $table->string('artist', 80)->default('');
112 $table->string('artist_unicode', 80)->nullable();
113 $table->string('title', 80)->default('');
114 $table->string('title_unicode', 80)->nullable();
115 $table->string('creator', 80)->default('');
116 $table->string('source', 200)->default('');
117 $table->string('tags', 1000)->default('');
118 $table->boolean('video')->default(0);
119 $table->boolean('storyboard')->default(0);
120 $table->boolean('epilepsy')->default(0);
121 $table->float('bpm')->default(0);
122 $table->boolean('versions_available')->unsigned()->default(1);
123 $table->boolean('approved')->default(0);
124 $table->mediumInteger('approvedby_id')->unsigned()->nullable();
125 $table->dateTime('approved_date')->nullable();
126 $table->dateTime('submit_date')->nullable();
127 $table->timestamp('last_update')->useCurrent();
128 $table->string('filename', 120)->nullable()->index('filename');
129 $table->boolean('active')->default(1);
130 $table->float('rating')->unsigned()->default(0);
131 $table->smallInteger('offset')->default(0);
132 $table->string('displaytitle', 200)->default('');
133 $table->smallInteger('genre_id')->unsigned()->default(1);
134 $table->smallInteger('language_id')->unsigned()->default(1);
135 $table->smallInteger('star_priority')->default(0);
136 $table->bigInteger('filesize')->default(0);
137 $table->bigInteger('filesize_novideo')->nullable();
138 // $table->binary('body_hash', 16)->nullable();
139 // $table->binary('header_hash', 16)->nullable();
140 // $table->binary('osz2_hash', 16)->nullable();
141 $table->boolean('download_disabled')->unsigned()->default(0);
142 $table->string('download_disabled_url', 100)->nullable();
143 $table->dateTime('thread_icon_date')->nullable();
144 $table->mediumInteger('favourite_count')->unsigned()->default(0);
145 $table->mediumInteger('play_count')->unsigned()->default(0);
146 $table->string('difficulty_names', 1024)->nullable();
147 $table->index('user_id', 'user_id');
148 $table->index('thread_id', 'thread_id');
149 $table->index('genre_id', 'genre_id');
150 $table->index(['approved', 'star_priority'], 'approved_2');
151 $table->index(['approved', 'active', 'approved_date'], 'approved');
152 $table->index('favourite_count', 'favourite_count');
153 $table->index(['approved', 'active', 'last_update'], 'approved_3');
154 });
155 $this->addBinary('osu_beatmapsets', 'body_hash', 16, true, 'filesize_novideo');
156 $this->addBinary('osu_beatmapsets', 'header_hash', 16, true, 'body_hash');
157 $this->addBinary('osu_beatmapsets', 'osz2_hash', 16, true, 'header_hash');
158
159 Schema::create('osu_user_beatmapset_ratings', function (Blueprint $table) {
160 $table->unsignedMediumInteger('user_id');
161 $table->unsignedMediumInteger('beatmapset_id');
162 $table->unsignedTinyInteger('rating');
163 $table->timestamp('date')->useCurrent();
164
165 $table->primary(['user_id', 'beatmapset_id']);
166 $table->index(['beatmapset_id', 'rating'], 'split_ratings');
167 });
168 $this->setRowFormat('osu_user_beatmapset_ratings', 'COMPRESSED');
169
170 Schema::create('osu_changelog', function (Blueprint $table) {
171 $table->mediumIncrements('changelog_id');
172 $table->mediumInteger('user_id')->unsigned();
173 $table->char('prefix', 1)->default('*');
174 $table->string('category', 50)->default('');
175 $table->string('message', 8000)->default('');
176 $table->string('checksum', 40)->default('');
177 $table->timestamp('date')->useCurrent();
178 $table->boolean('private')->default(0);
179 $table->boolean('major')->default(0);
180 $table->boolean('tweet')->default(0);
181 $table->string('build', 50)->nullable();
182 $table->integer('thread_id')->unsigned()->nullable();
183 $table->string('url', 1024)->nullable();
184 $table->boolean('stream_id')->unsigned()->nullable()->index('stream_id');
185 $table->unique('checksum', 'unique_checksum');
186 $table->index('date', 'time');
187 $table->index(['build', 'date'], 'major_release');
188 $table->index(['category', 'changelog_id'], 'category');
189 });
190
191 Schema::create('osu_charts', function (Blueprint $table) {
192 $table->unsignedSmallInteger('chart_id', true);
193 $table->string('acronym', 10)->default('');
194 $table->string('name', 50)->default('');
195 $table->date('start_date')->nullable();
196 $table->date('end_date')->nullable();
197 $table->boolean('mode_specific')->default(0);
198 $table->string('type', 50)->default('monthly');
199 $table->boolean('active')->default(1);
200 $table->date('chart_month')->nullable();
201 $table->unique('acronym', 'acronym');
202 $table->index('end_date', 'enddate');
203 $table->index(['type', 'chart_month'], 'type');
204 });
205
206 Schema::create('osu_builds', function ($table) {
207 $table->mediumIncrements('build_id');
208 $table->string('version', 40)->nullable();
209 $table->timestamp('date')->useCurrent();
210 $table->tinyInteger('allow_ranking')->default(1);
211 $table->tinyInteger('allow_bancho')->default(1);
212 $table->tinyInteger('test_build')->default(0);
213 $table->string('comments', 200)->nullable();
214 $table->unsignedMediumInteger('users')->default(0);
215 $table->unsignedTinyInteger('stream_id')->nullable();
216 });
217 DB::statement('ALTER TABLE osu_builds ADD hash BINARY(16)');
218 DB::statement('ALTER TABLE osu_builds ADD last_hash BINARY(16)');
219
220 Schema::create('osu_countries', function (Blueprint $table) {
221 $table->char('acronym', 2)->primary();
222 $table->string('name', 150);
223 $table->bigInteger('rankedscore');
224 $table->bigInteger('playcount');
225 $table->bigInteger('usercount')->default(0);
226 $table->bigInteger('pp')->default(0);
227 $table->boolean('display')->default(1);
228 $table->float('shipping_rate')->default(1);
229 $table->index('rankedscore', 'rankedscore');
230 $table->index('playcount', 'playcount');
231 $table->index(['display', 'name'], 'display');
232 });
233
234 Schema::create('osu_counts', function (Blueprint $table) {
235 $table->string('name', 200)->primary();
236 $table->bigInteger('count')->unsigned();
237 });
238
239 Schema::create('osu_downloads', function (Blueprint $table) {
240 $table->unsignedMediumInteger('user_id');
241 $table->integer('timestamp');
242 $table->mediumInteger('beatmapset_id');
243 $table->tinyInteger('fulfilled')->default(0);
244 $table->unsignedTinyInteger('mirror_id')->default(0);
245
246 $table->index(['user_id', 'timestamp', 'beatmapset_id'], 'user_id');
247 });
248
249 Schema::create('osu_events', function (Blueprint $table) {
250 $table->increments('event_id');
251 $table->string('text', 1000);
252 $table->string('text_clean', 8000)->nullable();
253 $table->mediumInteger('beatmap_id')->unsigned()->nullable();
254 $table->mediumInteger('beatmapset_id')->unsigned()->nullable();
255 $table->mediumInteger('user_id')->unsigned()->nullable();
256 $table->timestamp('date')->useCurrent();
257 $table->boolean('epicfactor')->unsigned()->default(0);
258 $table->boolean('private')->unsigned()->default(0);
259 // $table->primary(['event_id', 'date']);
260 $table->index(['user_id', 'event_id'], 'user_id');
261 });
262 DB::statement('ALTER TABLE `osu_events` DROP PRIMARY KEY, ADD PRIMARY KEY (`event_id`, `date`)');
263 $this->comment('osu_events', 'holds events up to one month in the past');
264 $this->setRowFormat('osu_events', 'COMPRESSED');
265
266 Schema::create('osu_favouritemaps', function (Blueprint $table) {
267 $table->mediumInteger('user_id')->unsigned();
268 $table->mediumInteger('beatmapset_id')->unsigned()->index('beatmapset_id');
269 $table->timestamp('dateadded')->useCurrent();
270 $table->primary(['user_id', 'beatmapset_id']);
271 });
272
273 Schema::create('osu_genres', function (Blueprint $table) {
274 $table->smallIncrements('genre_id');
275 $table->string('name', 200);
276 });
277
278 Schema::create('osu_kudos_exchange', function (Blueprint $table) {
279 $table->mediumIncrements('exchange_id');
280 $table->mediumInteger('giver_id')->unsigned();
281 $table->mediumInteger('receiver_id')->unsigned();
282 $table->mediumInteger('post_id')->unsigned();
283 $table->enum('action', ['give', 'revoke', 'reset']);
284 $table->boolean('amount')->default(1);
285 $table->timestamp('date')->useCurrent();
286 $table->unique(['receiver_id', 'exchange_id'], 'history_display');
287 $table->index('giver_id', 'giver_id');
288 $table->index(['receiver_id', 'date'], 'receiver_id');
289 });
290 $this->setRowFormat('osu_kudos_exchange', 'COMPRESSED');
291
292 Schema::create('osu_languages', function (Blueprint $table) {
293 $table->integer('language_id', true);
294 $table->string('name', 50);
295 $table->boolean('display_order')->default(0)->index('order');
296 });
297
298 Schema::create('osu_leaders_fruits', function (Blueprint $table) {
299 $table->mediumInteger('beatmap_id')->unsigned()->primary();
300 $table->mediumInteger('user_id')->unsigned();
301 $table->integer('score_id')->nullable();
302 $table->index(['user_id', 'score_id'], 'user_id');
303 });
304
305 Schema::create('osu_leaders_mania', function (Blueprint $table) {
306 $table->mediumInteger('beatmap_id')->unsigned()->primary();
307 $table->mediumInteger('user_id')->unsigned();
308 $table->integer('score_id')->nullable();
309 $table->index(['user_id', 'score_id'], 'user_id');
310 });
311
312 Schema::create('osu_leaders', function (Blueprint $table) {
313 $table->mediumInteger('beatmap_id')->unsigned()->primary();
314 $table->mediumInteger('user_id')->unsigned();
315 $table->integer('score_id')->nullable();
316 $table->index(['user_id', 'score_id'], 'user_id');
317 });
318
319 Schema::create('osu_leaders_taiko', function (Blueprint $table) {
320 $table->mediumInteger('beatmap_id')->unsigned()->primary();
321 $table->mediumInteger('user_id')->unsigned();
322 $table->integer('score_id')->nullable();
323 $table->index(['user_id', 'score_id'], 'user_id');
324 });
325
326 Schema::create('osu_login_attempts', function (Blueprint $table) {
327 $table->string('ip', 128)->primary();
328 $table->mediumInteger('failed_attempts')->unsigned()->default(1);
329 $table->smallInteger('total_attempts')->unsigned()->default(1);
330 $table->smallInteger('unique_ids')->unsigned()->default(1);
331 $table->text('failed_ids', 65535);
332 $table->timestamp('last_attempt')->nullable()->useCurrent()->index('last_attempt');
333 $table->timestamp('created_date')->useCurrent();
334 });
335
336 Schema::create('osu_scores_fruits', function (Blueprint $table) {
337 $table->unsignedInteger('score_id'); // autoincrement is set!
338 // $table->binary('scorechecksum', 16)->index('scorechecksum');
339 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
340 $table->mediumInteger('beatmapset_id')->unsigned()->default(0)->index('beatmapset_id');
341 $table->mediumInteger('user_id')->default(0);
342 $table->integer('score')->default(0);
343 $table->smallInteger('maxcombo')->unsigned()->default(0);
344 $table->enum('rank', ['0', 'A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH', 'F'])->default('F');
345 $table->smallInteger('count50')->unsigned()->default(0);
346 $table->smallInteger('count100')->unsigned()->default(0);
347 $table->smallInteger('count300')->unsigned()->default(0);
348 $table->smallInteger('countmiss')->unsigned()->default(0);
349 $table->smallInteger('countgeki')->unsigned()->default(0);
350 $table->smallInteger('countkatu')->unsigned()->default(0);
351 $table->boolean('perfect')->default(0);
352 $table->smallInteger('enabled_mods')->unsigned()->default(0);
353 $table->boolean('pass')->default(0);
354 $table->timestamp('date')->useCurrent();
355 $table->bigInteger('high_score_id')->unsigned()->nullable();
356 $table->primary(['score_id', 'date']);
357 // $table->index(['user_id','date'], 'user_id');
358 });
359 // can't create sized blob directly in the migration
360 DB::statement('ALTER TABLE `osu_scores_fruits` ADD `scorechecksum` BINARY(16) NOT NULL AFTER `score_id`');
361 DB::statement('ALTER TABLE `osu_scores_fruits` ADD KEY (`scorechecksum`)');
362 DB::statement('ALTER TABLE `osu_scores_fruits` ADD KEY `user_id` (`user_id`, `date`)');
363 DB::statement('ALTER TABLE `osu_scores_fruits` MODIFY COLUMN `score_id` INT UNSIGNED AUTO_INCREMENT');
364
365 Schema::create('osu_mirrors', function (Blueprint $table) {
366 $table->tinyIncrements('mirror_id');
367 $table->string('base_url', 255);
368 $table->bigInteger('traffic_used')->default(0);
369 $table->bigInteger('traffic_limit')->default(0);
370 $table->string('secret_key', 50)->default('');
371 $table->integer('provider_user_id');
372 $table->tinyInteger('enabled')->default(1);
373 $table->decimal('version', 4, 2)->nullable();
374 $table->string('pending_purge', 6000)->nullable(true);
375 $table->tinyInteger('pending_updates')->default(1);
376 $table->string('regions', 6000)->nullable(true);
377 $table->bigInteger('disk_space_free')->nullable();
378 });
379
380 Schema::create('osu_replays', function (Blueprint $table) {
381 $table->unsignedInteger('score_id')->default(0)->primary();
382 $table->unsignedInteger('play_count')->default(0);
383 $table->integer('version')->nullable();
384 });
385
386 Schema::create('osu_replays_fruits', function (Blueprint $table) {
387 $table->unsignedInteger('score_id')->default(0)->primary();
388 $table->unsignedInteger('play_count')->default(0);
389 $table->integer('version')->nullable();
390 });
391
392 Schema::create('osu_replays_mania', function (Blueprint $table) {
393 $table->unsignedInteger('score_id')->default(0)->primary();
394 $table->unsignedInteger('play_count')->default(0);
395 $table->integer('version')->nullable();
396 });
397
398 Schema::create('osu_replays_taiko', function (Blueprint $table) {
399 $table->unsignedInteger('score_id')->default(0)->primary();
400 $table->unsignedInteger('play_count')->default(0);
401 $table->integer('version')->nullable();
402 });
403
404 Schema::create('osu_scores_fruits_high', function (Blueprint $table) {
405 $table->increments('score_id');
406 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
407 $table->mediumInteger('beatmapset_id')->unsigned()->default(0);
408 $table->mediumInteger('user_id')->default(0);
409 $table->integer('score')->default(0);
410 $table->smallInteger('maxcombo')->unsigned()->default(0);
411 $table->enum('rank', ['A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH']);
412 $table->smallInteger('count50')->unsigned()->default(0);
413 $table->smallInteger('count100')->unsigned()->default(0);
414 $table->smallInteger('count300')->unsigned()->default(0);
415 $table->smallInteger('countmiss')->unsigned()->default(0);
416 $table->smallInteger('countgeki')->unsigned()->default(0);
417 $table->smallInteger('countkatu')->unsigned()->default(0);
418 $table->boolean('perfect')->default(0);
419 $table->smallInteger('enabled_mods')->unsigned()->default(0);
420 $table->timestamp('date')->useCurrent();
421 $table->float('pp')->nullable();
422 $table->boolean('replay')->unsigned()->default(0);
423 $table->index(['beatmap_id', 'score', 'user_id'], 'beatmap_score_lookup');
424 $table->index(['user_id', 'beatmap_id', 'rank'], 'user_beatmap_rank');
425 });
426
427 Schema::create('osu_scores_high', function (Blueprint $table) {
428 $table->increments('score_id');
429 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
430 $table->mediumInteger('beatmapset_id')->unsigned()->default(0);
431 $table->mediumInteger('user_id')->default(0);
432 $table->integer('score')->default(0);
433 $table->smallInteger('maxcombo')->unsigned()->default(0);
434 $table->enum('rank', ['A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH']);
435 $table->smallInteger('count50')->unsigned()->default(0);
436 $table->smallInteger('count100')->unsigned()->default(0);
437 $table->smallInteger('count300')->unsigned()->default(0);
438 $table->smallInteger('countmiss')->unsigned()->default(0);
439 $table->smallInteger('countgeki')->unsigned()->default(0);
440 $table->smallInteger('countkatu')->unsigned()->default(0);
441 $table->boolean('perfect')->default(0);
442 $table->smallInteger('enabled_mods')->unsigned()->default(0);
443 $table->timestamp('date')->useCurrent();
444 $table->float('pp')->nullable();
445 $table->boolean('replay')->unsigned()->default(0);
446 $table->index(['beatmap_id', 'score', 'user_id'], 'beatmap_score_lookup');
447 $table->index(['user_id', 'beatmap_id', 'rank'], 'user_beatmap_rank');
448 });
449
450 Schema::create('osu_scores_mania_high', function (Blueprint $table) {
451 $table->increments('score_id');
452 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
453 $table->mediumInteger('beatmapset_id')->unsigned()->default(0);
454 $table->mediumInteger('user_id')->default(0);
455 $table->integer('score')->default(0);
456 $table->smallInteger('maxcombo')->unsigned()->default(0);
457 $table->enum('rank', ['A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH']);
458 $table->smallInteger('count50')->unsigned()->default(0);
459 $table->smallInteger('count100')->unsigned()->default(0);
460 $table->smallInteger('count300')->unsigned()->default(0);
461 $table->smallInteger('countmiss')->unsigned()->default(0);
462 $table->smallInteger('countgeki')->unsigned()->default(0);
463 $table->smallInteger('countkatu')->unsigned()->default(0);
464 $table->boolean('perfect')->default(0);
465 $table->integer('enabled_mods')->unsigned()->default(0);
466 $table->timestamp('date')->useCurrent();
467 $table->float('pp')->nullable();
468 $table->boolean('replay')->unsigned()->default(0);
469 $table->index(['beatmap_id', 'score', 'user_id'], 'beatmap_score_lookup');
470 $table->index(['user_id', 'beatmap_id', 'rank'], 'user_beatmap_rank');
471 });
472
473 Schema::create('osu_scores_mania', function (Blueprint $table) {
474 $table->integer('score_id', false, true);
475 // $table->binary('scorechecksum', 16)->index('scorechecksum');
476 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
477 $table->mediumInteger('beatmapset_id')->unsigned()->default(0)->index('beatmapset_id');
478 $table->mediumInteger('user_id')->default(0);
479 $table->integer('score')->default(0);
480 $table->smallInteger('maxcombo')->unsigned()->default(0);
481 $table->enum('rank', ['0', 'A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH', 'F'])->default('F');
482 $table->smallInteger('count50')->unsigned()->default(0);
483 $table->smallInteger('count100')->unsigned()->default(0);
484 $table->smallInteger('count300')->unsigned()->default(0);
485 $table->smallInteger('countmiss')->unsigned()->default(0);
486 $table->smallInteger('countgeki')->unsigned()->default(0);
487 $table->smallInteger('countkatu')->unsigned()->default(0);
488 $table->boolean('perfect')->default(0);
489 $table->integer('enabled_mods')->unsigned()->default(0);
490 $table->boolean('pass')->default(0);
491 $table->timestamp('date')->useCurrent();
492 $table->bigInteger('high_score_id')->unsigned()->nullable();
493 $table->primary(['score_id', 'date']);
494 // $table->index(['user_id','date'], 'user_id');
495 });
496 DB::statement('ALTER TABLE `osu_scores_mania` ADD `scorechecksum` BINARY(16) NOT NULL AFTER `score_id`');
497 DB::statement('ALTER TABLE `osu_scores_mania` ADD KEY (`scorechecksum`)');
498 DB::statement('ALTER TABLE `osu_scores_mania` ADD KEY `user_id` (`user_id`, `date`)');
499 DB::statement('ALTER TABLE `osu_scores_mania` MODIFY COLUMN `score_id` INT UNSIGNED AUTO_INCREMENT');
500
501 Schema::create('osu_scores', function (Blueprint $table) {
502 $table->unsignedInteger('score_id');
503 // $table->binary('scorechecksum', 16)->index('scorechecksum');
504 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
505 $table->mediumInteger('beatmapset_id')->unsigned()->default(0);
506 $table->mediumInteger('user_id')->default(0);
507 $table->integer('score')->default(0);
508 $table->smallInteger('maxcombo')->unsigned()->default(0);
509 $table->enum('rank', ['0', 'A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH', 'F'])->default('F');
510 $table->smallInteger('count50')->unsigned()->default(0);
511 $table->smallInteger('count100')->unsigned()->default(0);
512 $table->smallInteger('count300')->unsigned()->default(0);
513 $table->smallInteger('countmiss')->unsigned()->default(0);
514 $table->smallInteger('countgeki')->unsigned()->default(0);
515 $table->smallInteger('countkatu')->unsigned()->default(0);
516 $table->boolean('perfect')->default(0);
517 $table->smallInteger('enabled_mods')->unsigned()->default(0);
518 $table->boolean('pass')->default(0);
519 $table->timestamp('date')->useCurrent();
520 $table->bigInteger('high_score_id')->unsigned()->nullable();
521 $table->primary(['score_id', 'date']);
522 // $table->index(['user_id','date'], 'user_id');
523 });
524 DB::statement('ALTER TABLE `osu_scores` ADD `scorechecksum` BINARY(16) NOT NULL AFTER `score_id`');
525 DB::statement('ALTER TABLE `osu_scores` ADD KEY (`scorechecksum`)');
526 DB::statement('ALTER TABLE `osu_scores` MODIFY COLUMN `score_id` INT UNSIGNED AUTO_INCREMENT');
527 DB::statement('ALTER TABLE `osu_scores` ADD KEY `user_id` (`user_id`, `date`)');
528 DB::statement('ALTER TABLE `osu_scores` ADD KEY (`beatmapset_id`)');
529
530 Schema::create('osu_scores_taiko_high', function (Blueprint $table) {
531 $table->increments('score_id');
532 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
533 $table->mediumInteger('beatmapset_id')->unsigned()->default(0);
534 $table->mediumInteger('user_id')->default(0);
535 $table->integer('score')->default(0);
536 $table->smallInteger('maxcombo')->unsigned()->default(0);
537 $table->enum('rank', ['A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH']);
538 $table->smallInteger('count50')->unsigned()->default(0);
539 $table->smallInteger('count100')->unsigned()->default(0);
540 $table->smallInteger('count300')->unsigned()->default(0);
541 $table->smallInteger('countmiss')->unsigned()->default(0);
542 $table->smallInteger('countgeki')->unsigned()->default(0);
543 $table->smallInteger('countkatu')->unsigned()->default(0);
544 $table->boolean('perfect')->default(0);
545 $table->smallInteger('enabled_mods')->unsigned()->default(0);
546 $table->timestamp('date')->useCurrent();
547 $table->float('pp')->nullable();
548 $table->boolean('replay')->unsigned()->default(0);
549 $table->index(['beatmap_id', 'score', 'user_id'], 'beatmap_score_lookup');
550 $table->index(['user_id', 'beatmap_id', 'rank'], 'user_beatmap_rank');
551 });
552
553 Schema::create('osu_scores_taiko', function (Blueprint $table) {
554 $table->integer('score_id', false, true);
555 // $table->binary('scorechecksum', 16)->index('scorechecksum');
556 $table->mediumInteger('beatmap_id')->unsigned()->default(0);
557 $table->mediumInteger('beatmapset_id')->unsigned()->default(0)->index('beatmapset_id');
558 $table->mediumInteger('user_id')->default(0);
559 $table->integer('score')->default(0);
560 $table->smallInteger('maxcombo')->unsigned()->default(0);
561 $table->enum('rank', ['0', 'A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH', 'F'])->default('F');
562 $table->smallInteger('count50')->unsigned()->default(0);
563 $table->smallInteger('count100')->unsigned()->default(0);
564 $table->smallInteger('count300')->unsigned()->default(0);
565 $table->smallInteger('countmiss')->unsigned()->default(0);
566 $table->smallInteger('countgeki')->unsigned()->default(0);
567 $table->smallInteger('countkatu')->unsigned()->default(0);
568 $table->boolean('perfect')->default(0);
569 $table->smallInteger('enabled_mods')->unsigned()->default(0);
570 $table->boolean('pass')->default(0);
571 $table->timestamp('date')->useCurrent();
572 $table->bigInteger('high_score_id')->unsigned()->nullable();
573 $table->primary(['score_id', 'date']);
574 // $table->index(['user_id','date'], 'user_id');
575 });
576 DB::statement('ALTER TABLE `osu_scores_taiko` ADD `scorechecksum` BINARY(16) NOT NULL AFTER `score_id`');
577 DB::statement('ALTER TABLE `osu_scores_taiko` ADD KEY (`scorechecksum`)');
578 DB::statement('ALTER TABLE `osu_scores_taiko` ADD KEY `user_id` (`user_id`, `date`)');
579 DB::statement('ALTER TABLE `osu_scores_taiko` MODIFY COLUMN `score_id` INT UNSIGNED AUTO_INCREMENT');
580
581 Schema::create('osu_user_achievements', function (Blueprint $table) {
582 $table->mediumInteger('user_id');
583 $table->mediumInteger('achievement_id');
584 $table->timestamp('date')->useCurrent();
585 $table->mediumInteger('beatmap_id')->nullable();
586 $table->primary(['user_id', 'achievement_id']);
587 $table->index(['user_id', 'date'], 'user_id');
588 });
589
590 Schema::create('osu_user_banhistory', function (Blueprint $table) {
591 $table->increments('ban_id');
592 $table->integer('user_id')->unsigned()->nullable();
593 $table->string('reason', 8000)->nullable()->default('Blanket Cheating Action');
594 $table->string('supporting_url')->nullable();
595 $table->boolean('ban_status')->nullable()->default(1);
596 $table->integer('period')->unsigned()->default(0);
597 $table->timestamp('timestamp')->nullable()->useCurrent();
598 $table->integer('banner_id')->unsigned()->nullable();
599 $table->index(['user_id', 'timestamp'], 'user_id_2');
600 });
601 $this->setRowFormat('osu_user_banhistory', 'COMPRESSED');
602
603 Schema::create('osu_user_beatmap_playcount', function (Blueprint $table) {
604 $table->mediumInteger('user_id')->unsigned();
605 $table->mediumInteger('beatmap_id')->unsigned();
606 $table->smallInteger('playcount')->unsigned();
607 $table->primary(['user_id', 'beatmap_id']);
608 });
609 $this->setRowFormat('osu_user_beatmap_playcount', 'COMPRESSED');
610
611 Schema::create('osu_user_donations', function (Blueprint $table) {
612 $table->mediumInteger('user_id')->unsigned();
613 $table->string('transaction_id', 250);
614 $table->mediumInteger('target_user_id')->unsigned()->default(0);
615 $table->boolean('length');
616 $table->smallInteger('amount');
617 $table->timestamp('timestamp')->useCurrent();
618 $table->boolean('cancel')->default(0);
619 $table->primary(['user_id', 'transaction_id']);
620 $table->index('timestamp', 'timestamp');
621 $table->index('transaction_id', 'transaction_id');
622 });
623
624 Schema::create('osu_username_change_history', function (Blueprint $table) {
625 $table->increments('change_id');
626 $table->integer('user_id')->unsigned()->index('user_id'); // medium???
627 $table->string('username', 30);
628 $table->enum('type', ['support', 'paid', 'admin', 'revert', 'inactive']);
629 $table->timestamp('timestamp')->nullable()->useCurrent();
630 $table->string('username_last', 30)->nullable()->index('username_last');
631 });
632 $this->comment('osu_username_change_history', 'Stores historical changes to user\'\'s usernames over time.');
633
634 Schema::create('osu_user_month_playcount', function (Blueprint $table) {
635 $table->unsignedMediumInteger('user_id');
636 $table->char('year_month', 4);
637 $table->unsignedSmallInteger('playcount');
638
639 $table->primary(['user_id', 'year_month']);
640 });
641 $this->setRowFormat('osu_user_month_playcount', 'COMPRESSED');
642
643 Schema::create('osu_user_performance_rank', function (Blueprint $table) {
644 $table->integer('user_id')->unsigned();
645 $table->boolean('mode');
646 $table->integer('r0')->default(0);
647 $table->integer('r1')->default(0);
648 $table->integer('r2')->default(0);
649 $table->integer('r3')->default(0);
650 $table->integer('r4')->default(0);
651 $table->integer('r5')->default(0);
652 $table->integer('r6')->default(0);
653 $table->integer('r7')->default(0);
654 $table->integer('r8')->default(0);
655 $table->integer('r9')->default(0);
656 $table->integer('r10')->default(0);
657 $table->integer('r11')->default(0);
658 $table->integer('r12')->default(0);
659 $table->integer('r13')->default(0);
660 $table->integer('r14')->default(0);
661 $table->integer('r15')->default(0);
662 $table->integer('r16')->default(0);
663 $table->integer('r17')->default(0);
664 $table->integer('r18')->default(0);
665 $table->integer('r19')->default(0);
666 $table->integer('r20')->default(0);
667 $table->integer('r21')->default(0);
668 $table->integer('r22')->default(0);
669 $table->integer('r23')->default(0);
670 $table->integer('r24')->default(0);
671 $table->integer('r25')->default(0);
672 $table->integer('r26')->default(0);
673 $table->integer('r27')->default(0);
674 $table->integer('r28')->default(0);
675 $table->integer('r29')->default(0);
676 $table->integer('r30')->default(0);
677 $table->integer('r31')->default(0);
678 $table->integer('r32')->default(0);
679 $table->integer('r33')->default(0);
680 $table->integer('r34')->default(0);
681 $table->integer('r35')->default(0);
682 $table->integer('r36')->default(0);
683 $table->integer('r37')->default(0);
684 $table->integer('r38')->default(0);
685 $table->integer('r39')->default(0);
686 $table->integer('r40')->default(0);
687 $table->integer('r41')->default(0);
688 $table->integer('r42')->default(0);
689 $table->integer('r43')->default(0);
690 $table->integer('r44')->default(0);
691 $table->integer('r45')->default(0);
692 $table->integer('r46')->default(0);
693 $table->integer('r47')->default(0);
694 $table->integer('r48')->default(0);
695 $table->integer('r49')->default(0);
696 $table->integer('r50')->default(0);
697 $table->integer('r51')->default(0);
698 $table->integer('r52')->default(0);
699 $table->integer('r53')->default(0);
700 $table->integer('r54')->default(0);
701 $table->integer('r55')->default(0);
702 $table->integer('r56')->default(0);
703 $table->integer('r57')->default(0);
704 $table->integer('r58')->default(0);
705 $table->integer('r59')->default(0);
706 $table->integer('r60')->default(0);
707 $table->integer('r61')->default(0);
708 $table->integer('r62')->default(0);
709 $table->integer('r63')->default(0);
710 $table->integer('r64')->default(0);
711 $table->integer('r65')->default(0);
712 $table->integer('r66')->default(0);
713 $table->integer('r67')->default(0);
714 $table->integer('r68')->default(0);
715 $table->integer('r69')->default(0);
716 $table->integer('r70')->default(0);
717 $table->integer('r71')->default(0);
718 $table->integer('r72')->default(0);
719 $table->integer('r73')->default(0);
720 $table->integer('r74')->default(0);
721 $table->integer('r75')->default(0);
722 $table->integer('r76')->default(0);
723 $table->integer('r77')->default(0);
724 $table->integer('r78')->default(0);
725 $table->integer('r79')->default(0);
726 $table->integer('r80')->default(0);
727 $table->integer('r81')->default(0);
728 $table->integer('r82')->default(0);
729 $table->integer('r83')->default(0);
730 $table->integer('r84')->default(0);
731 $table->integer('r85')->default(0);
732 $table->integer('r86')->default(0);
733 $table->integer('r87')->default(0);
734 $table->integer('r88')->default(0);
735 $table->integer('r89')->default(0);
736 $table->primary(['user_id', 'mode']);
737 });
738 $partitions = 'PARTITION p0 VALUES LESS THAN (1),';
739 $partitions .= 'PARTITION p1 VALUES LESS THAN (2),';
740 $partitions .= 'PARTITION p2 VALUES LESS THAN (3),';
741 $partitions .= 'PARTITION p3 VALUES LESS THAN (4)';
742 DB::statement("ALTER TABLE `osu_user_performance_rank` PARTITION BY RANGE (mode) ({$partitions});");
743
744 Schema::create('osu_user_reports', function (Blueprint $table) {
745 $table->increments('report_id');
746 $table->integer('user_id');
747 $table->unsignedInteger('score_id')->default(0);
748 $table->tinyInteger('mode')->default(0);
749 $table->enum('reason', ['Insults', 'Spam', 'Cheating', 'UnwantedContent', 'Nonsense', 'Other'])->default('Cheating');
750 $table->integer('reporter_id');
751 $table->text('comments');
752 $table->timestamp('timestamp')->useCurrent();
753
754 $table->unique(['reporter_id', 'user_id', 'mode', 'score_id'], 'unique-new');
755 $table->index('timestamp', 'timestamp');
756 $table->index('user_id', 'user_lookup');
757 });
758
759
760 Schema::create('osu_user_replayswatched', function (Blueprint $table) {
761 $table->unsignedMediumInteger('user_id');
762 $table->char('year_month', 4);
763 $table->unsignedMediumInteger('count');
764
765 $table->primary(['user_id', 'year_month']);
766 });
767 $this->setRowFormat('osu_user_replayswatched', 'COMPRESSED');
768
769 Schema::create('osu_user_security', function (Blueprint $table) {
770 $table->unsignedMediumInteger('user_id');
771 });
772 $this->addBinary('osu_user_security', 'osu_md5', 16, true);
773 $this->addBinary('osu_user_security', 'unique_md5', 16, true);
774 $this->addBinary('osu_user_security', 'disk_md5', 16, true);
775 $this->addBinary('osu_user_security', 'mac_md5', 16, true);
776 Schema::table('osu_user_security', function (Blueprint $table) {
777 $table->timestamp('timestamp')->useCurrent();
778 $table->boolean('verified')->default(false);
779
780 $table->primary(['user_id', 'osu_md5', 'unique_md5']);
781 $table->index('disk_md5', 'disk_md5');
782 $table->index('unique_md5', 'unique_md5');
783 });
784
785 Schema::create('osu_user_stats_fruits', function (Blueprint $table) {
786 $table->mediumInteger('user_id')->primary();
787 $table->integer('count300')->default(0);
788 $table->integer('count100')->default(0);
789 $table->integer('count50')->default(0);
790 $table->integer('countMiss')->default(0);
791 $table->bigInteger('accuracy_total')->unsigned();
792 $table->bigInteger('accuracy_count')->unsigned();
793 $table->float('accuracy');
794 $table->mediumInteger('playcount');
795 $table->bigInteger('total_seconds_played')->default(0);
796 $table->bigInteger('ranked_score');
797 $table->bigInteger('total_score');
798 $table->mediumInteger('x_rank_count');
799 $table->mediumInteger('s_rank_count');
800 $table->mediumInteger('a_rank_count');
801 $table->mediumInteger('rank');
802 $table->float('level')->unsigned();
803 $table->mediumInteger('replay_popularity')->unsigned()->default(0);
804 $table->mediumInteger('fail_count')->unsigned()->default(0);
805 $table->mediumInteger('exit_count')->unsigned()->default(0);
806 $table->smallInteger('max_combo')->unsigned()->default(0);
807 $table->char('country_acronym', 2)->default('');
808 $table->float('rank_score')->unsigned();
809 $table->integer('rank_score_index')->unsigned();
810 $table->float('accuracy_new')->unsigned();
811 // $table->timestamp('last_update')->useCurrent();
812 $table->timestamp('last_played')->useCurrent();
813 $table->index('ranked_score', 'ranked_score');
814 $table->index('playcount', 'playcount');
815 $table->index('rank_score', 'rank_score');
816 $table->index(['country_acronym', 'rank_score'], 'country_acronym');
817 });
818 DB::statement('ALTER TABLE `osu_user_stats_fruits` ADD COLUMN `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `accuracy_new`');
819
820 Schema::create('osu_user_stats_mania', function (Blueprint $table) {
821 $table->mediumInteger('user_id')->primary();
822 $table->integer('count300')->default(0);
823 $table->integer('count100')->default(0);
824 $table->integer('count50')->default(0);
825 $table->integer('countMiss')->default(0);
826 $table->bigInteger('accuracy_total')->unsigned();
827 $table->bigInteger('accuracy_count')->unsigned();
828 $table->float('accuracy');
829 $table->mediumInteger('playcount');
830 $table->bigInteger('total_seconds_played')->default(0);
831 $table->bigInteger('ranked_score');
832 $table->bigInteger('total_score');
833 $table->mediumInteger('x_rank_count');
834 $table->mediumInteger('s_rank_count');
835 $table->mediumInteger('a_rank_count');
836 $table->mediumInteger('rank');
837 $table->float('level')->unsigned();
838 $table->mediumInteger('replay_popularity')->unsigned()->default(0);
839 $table->mediumInteger('fail_count')->unsigned()->default(0);
840 $table->mediumInteger('exit_count')->unsigned()->default(0);
841 $table->smallInteger('max_combo')->unsigned()->default(0);
842 $table->char('country_acronym', 2)->default('');
843 $table->float('rank_score')->unsigned();
844 $table->integer('rank_score_index')->unsigned();
845 $table->float('accuracy_new')->unsigned();
846 // $table->timestamp('last_update')->useCurrent();
847 $table->timestamp('last_played')->useCurrent();
848 $table->index('ranked_score', 'ranked_score');
849 $table->index('rank_score', 'rank_score');
850 $table->index(['country_acronym', 'rank_score'], 'country_acronym_2');
851 $table->index('playcount', 'playcount');
852 });
853 DB::statement('ALTER TABLE `osu_user_stats_mania` ADD COLUMN `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `accuracy_new`');
854
855 Schema::create('osu_user_stats', function (Blueprint $table) {
856 $table->mediumInteger('user_id')->primary();
857 $table->integer('count300')->default(0);
858 $table->integer('count100')->default(0);
859 $table->integer('count50')->default(0);
860 $table->integer('countMiss')->default(0);
861 $table->bigInteger('accuracy_total')->unsigned();
862 $table->bigInteger('accuracy_count')->unsigned();
863 $table->float('accuracy');
864 $table->mediumInteger('playcount');
865 $table->bigInteger('total_seconds_played')->default(0);
866 $table->bigInteger('ranked_score');
867 $table->bigInteger('total_score');
868 $table->mediumInteger('x_rank_count');
869 $table->mediumInteger('s_rank_count');
870 $table->mediumInteger('a_rank_count');
871 $table->mediumInteger('rank');
872 $table->float('level')->unsigned();
873 $table->mediumInteger('replay_popularity')->unsigned()->default(0);
874 $table->mediumInteger('fail_count')->unsigned()->default(0);
875 $table->mediumInteger('exit_count')->unsigned()->default(0);
876 $table->smallInteger('max_combo')->unsigned()->default(0);
877 $table->char('country_acronym', 2)->default('');
878 $table->float('rank_score')->unsigned();
879 $table->integer('rank_score_index')->unsigned();
880 $table->float('accuracy_new')->unsigned();
881 // $table->timestamp('last_update')->useCurrent();
882 $table->timestamp('last_played')->useCurrent();
883 $table->index('ranked_score', 'ranked_score');
884 $table->index('rank_score', 'rank_score');
885 $table->index(['country_acronym', 'rank_score'], 'country_acronym_2');
886 $table->index('playcount', 'playcount');
887 });
888 DB::statement('ALTER TABLE `osu_user_stats` ADD COLUMN `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `accuracy_new`');
889
890 Schema::create('osu_user_stats_taiko', function (Blueprint $table) {
891 $table->mediumInteger('user_id')->primary();
892 $table->integer('count300')->default(0);
893 $table->integer('count100')->default(0);
894 $table->integer('count50')->default(0);
895 $table->integer('countMiss')->default(0);
896 $table->bigInteger('accuracy_total')->unsigned();
897 $table->bigInteger('accuracy_count')->unsigned();
898 $table->float('accuracy');
899 $table->mediumInteger('playcount');
900 $table->bigInteger('total_seconds_played')->default(0);
901 $table->bigInteger('ranked_score');
902 $table->bigInteger('total_score');
903 $table->mediumInteger('x_rank_count');
904 $table->mediumInteger('s_rank_count');
905 $table->mediumInteger('a_rank_count');
906 $table->mediumInteger('rank');
907 $table->float('level')->unsigned();
908 $table->mediumInteger('replay_popularity')->unsigned()->default(0);
909 $table->mediumInteger('fail_count')->unsigned()->default(0);
910 $table->mediumInteger('exit_count')->unsigned()->default(0);
911 $table->smallInteger('max_combo')->unsigned()->default(0);
912 $table->char('country_acronym', 2)->default('');
913 $table->float('rank_score')->unsigned();
914 $table->integer('rank_score_index')->unsigned();
915 $table->float('accuracy_new')->unsigned();
916 // $table->timestamp('last_update')->useCurrent();
917 $table->timestamp('last_played')->useCurrent();
918 $table->index('ranked_score', 'ranked_score');
919 $table->index('playcount', 'playcount');
920 $table->index('rank_score', 'rank_score');
921 $table->index(['country_acronym', 'rank_score'], 'country_acronym');
922 });
923 DB::statement('ALTER TABLE `osu_user_stats_taiko` ADD COLUMN `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `accuracy_new`');
924
925 Schema::create('phpbb_acl_groups', function (Blueprint $table) {
926 $table->mediumInteger('group_id')->unsigned()->default(0)->index('group_id');
927 $table->mediumInteger('forum_id')->unsigned()->default(0);
928 $table->mediumInteger('auth_option_id')->unsigned()->default(0)->index('auth_opt_id');
929 $table->mediumInteger('auth_role_id')->unsigned()->default(0)->index('auth_role_id');
930 $table->boolean('auth_setting')->default(0);
931 });
932
933 Schema::create('phpbb_acl_options', function (Blueprint $table) {
934 $table->mediumIncrements('auth_option_id', true);
935
936 $table->string('auth_option', 50)->default('');
937 $table->unsignedTinyInteger('is_global')->default(0);
938 $table->unsignedTinyInteger('is_local')->default(0);
939 $table->unsignedTinyInteger('founder_only')->default(0);
940
941 $table->index('auth_option', 'auth_option');
942 });
943
944 Schema::create('phpbb_acl_roles_data', function (Blueprint $table) {
945 $table->unsignedMediumInteger('role_id')->default('0');
946 $table->unsignedMediumInteger('auth_option_id')->default(0);
947 $table->tinyInteger('auth_setting')->default(0);
948
949 $table->primary(['role_id', 'auth_option_id']);
950
951 $table->index('auth_option_id', 'ath_op_id');
952 });
953
954 Schema::create('phpbb_disallow', function (Blueprint $table) {
955 $table->mediumIncrements('disallow_id');
956 $table->string('disallow_username')->default('');
957 });
958
959 Schema::create('phpbb_forums', function (Blueprint $table) {
960 $table->mediumIncrements('forum_id');
961 $table->mediumInteger('parent_id')->unsigned()->default(0);
962 $table->mediumInteger('left_id')->unsigned()->default(0);
963 $table->mediumInteger('right_id')->unsigned()->default(0);
964 $table->mediumText('forum_parents');
965 $table->string('forum_name')->default('');
966 $table->text('forum_desc', 65535);
967 $table->string('forum_desc_bitfield')->default('');
968 $table->integer('forum_desc_options')->unsigned()->default(7);
969 $table->string('forum_desc_uid', 5)->default('');
970 $table->string('forum_link')->default('');
971 $table->string('forum_password', 40)->default('');
972 $table->smallInteger('forum_style')->unsigned()->default(0);
973 $table->string('forum_image')->default('');
974 $table->text('forum_rules', 65535);
975 $table->string('forum_rules_link')->default('');
976 $table->string('forum_rules_bitfield')->default('');
977 $table->integer('forum_rules_options')->unsigned()->default(7);
978 $table->string('forum_rules_uid', 5)->default('');
979 $table->boolean('forum_topics_per_page')->default(0);
980 $table->boolean('forum_type')->default(0);
981 $table->boolean('forum_status')->default(0);
982 $table->mediumInteger('forum_posts')->unsigned()->default(0);
983 $table->mediumInteger('forum_topics')->unsigned()->default(0);
984 $table->mediumInteger('forum_topics_real')->unsigned()->default(0);
985 $table->mediumInteger('forum_last_post_id')->unsigned()->default(0)->index('forum_lastpost_id');
986 $table->mediumInteger('forum_last_poster_id')->unsigned()->default(0);
987 $table->string('forum_last_post_subject', 100)->default('');
988 $table->integer('forum_last_post_time')->unsigned()->default(0);
989 $table->string('forum_last_poster_name')->default('');
990 $table->string('forum_last_poster_colour', 6)->default('');
991 $table->boolean('forum_flags')->default(32);
992 $table->boolean('display_on_index')->unsigned()->default(1);
993 $table->boolean('enable_indexing')->unsigned()->default(1);
994 $table->boolean('enable_icons')->unsigned()->default(1);
995 $table->boolean('enable_prune')->unsigned()->default(0);
996 $table->boolean('enable_sigs')->unsigned()->default(1);
997 $table->integer('prune_next')->unsigned()->default(0);
998 $table->mediumInteger('prune_days')->unsigned()->default(0);
999 $table->mediumInteger('prune_viewed')->unsigned()->default(0);
1000 $table->mediumInteger('prune_freq')->unsigned()->default(0);
1001 $table->index(['left_id', 'right_id'], 'left_right_id');
1002 });
1003
1004 Schema::create('phpbb_forums_track', function (Blueprint $table) {
1005 $table->mediumInteger('user_id')->unsigned()->default(0);
1006 $table->mediumInteger('forum_id')->unsigned()->default(0);
1007 $table->integer('mark_time')->unsigned()->default(0);
1008 $table->primary(['user_id', 'forum_id']);
1009 });
1010
1011 Schema::create('phpbb_posts', function (Blueprint $table) {
1012 $table->mediumIncrements('post_id');
1013 $table->mediumInteger('topic_id')->unsigned()->default(0);
1014 $table->mediumInteger('forum_id')->unsigned()->default(0);
1015 $table->mediumInteger('poster_id')->unsigned()->default(0);
1016 $table->mediumInteger('icon_id')->unsigned()->default(0);
1017 $table->string('poster_ip', 40)->default('');
1018 $table->integer('post_time')->unsigned()->default(0);
1019 $table->boolean('post_approved')->unsigned()->default(1);
1020 $table->boolean('post_reported')->unsigned()->default(0);
1021 $table->boolean('enable_bbcode')->unsigned()->default(1);
1022 $table->boolean('enable_smilies')->unsigned()->default(1);
1023 $table->boolean('enable_magic_url')->unsigned()->default(1);
1024 $table->boolean('enable_sig')->unsigned()->default(1);
1025 $table->string('post_username')->default('');
1026 $table->string('post_subject', 100)->default('');
1027 $table->mediumText('post_text');
1028 $table->boolean('post_attachment')->unsigned()->default(0);
1029 $table->string('bbcode_bitfield')->default('');
1030 $table->string('bbcode_uid', 5)->default('');
1031 $table->boolean('post_postcount')->unsigned()->default(1);
1032 $table->integer('post_edit_time')->unsigned()->default(0);
1033 $table->string('post_edit_reason')->default('');
1034 $table->mediumInteger('post_edit_user')->unsigned()->default(0);
1035 $table->smallInteger('post_edit_count')->unsigned()->default(0);
1036 $table->boolean('post_edit_locked')->unsigned()->default(0);
1037 $table->boolean('osu_kudosobtained')->default(0);
1038 $table->index('forum_id', 'forum_id');
1039 $table->index('topic_id', 'topic_id');
1040 $table->index('poster_id', 'poster_id');
1041 $table->index(['topic_id', 'post_time'], 'tid_post_time');
1042 });
1043
1044 Schema::create('phpbb_ranks', function (Blueprint $table) {
1045 $table->mediumIncrements('rank_id');
1046 $table->string('rank_title')->default('');
1047 $table->mediumInteger('rank_min')->unsigned()->default(0);
1048 $table->boolean('rank_special')->unsigned()->default(0);
1049 $table->string('rank_image')->default('');
1050 });
1051
1052 Schema::create('phpbb_smilies', function (Blueprint $table) {
1053 $table->mediumIncrements('smiley_id');
1054 $table->string('code', 50)->default('');
1055 $table->string('emotion', 50)->default('');
1056 $table->string('smiley_url', 50)->default('');
1057 $table->smallInteger('smiley_width')->unsigned()->default(0);
1058 $table->smallInteger('smiley_height')->unsigned()->default(0);
1059 $table->mediumInteger('smiley_order')->unsigned()->default(0);
1060 $table->boolean('display_on_posting')->unsigned()->default(1)->index('display_on_post');
1061 });
1062
1063 Schema::create('phpbb_topics', function (Blueprint $table) {
1064 $table->mediumIncrements('topic_id');
1065 $table->mediumInteger('forum_id')->unsigned()->default(0);
1066 $table->mediumInteger('icon_id')->unsigned()->default(0);
1067 $table->boolean('topic_attachment')->unsigned()->default(0);
1068 $table->boolean('topic_approved')->unsigned()->default(1);
1069 $table->boolean('topic_reported')->unsigned()->default(0);
1070 $table->string('topic_title', 100)->default('');
1071 $table->mediumInteger('topic_poster')->unsigned()->default(0);
1072 $table->integer('topic_time')->unsigned()->default(0);
1073 $table->integer('topic_time_limit')->unsigned()->default(0);
1074 $table->mediumInteger('topic_views')->unsigned()->default(0);
1075 $table->mediumInteger('topic_replies')->unsigned()->default(0);
1076 $table->mediumInteger('topic_replies_real')->unsigned()->default(0);
1077 $table->boolean('topic_status')->default(0);
1078 $table->boolean('topic_type')->default(0);
1079 $table->mediumInteger('topic_first_post_id')->unsigned()->default(0);
1080 $table->string('topic_first_poster_name')->default('');
1081 $table->string('topic_first_poster_colour', 6)->default('');
1082 $table->mediumInteger('topic_last_post_id')->unsigned()->default(0);
1083 $table->mediumInteger('topic_last_poster_id')->unsigned()->default(0);
1084 $table->string('topic_last_poster_name')->default('');
1085 $table->string('topic_last_poster_colour', 6)->default('');
1086 $table->string('topic_last_post_subject', 100)->default('');
1087 $table->integer('topic_last_post_time')->unsigned()->default(0);
1088 $table->integer('topic_last_view_time')->unsigned()->default(0);
1089 $table->mediumInteger('topic_moved_id')->unsigned()->default(0);
1090 $table->boolean('topic_bumped')->unsigned()->default(0);
1091 $table->mediumInteger('topic_bumper')->unsigned()->default(0);
1092 $table->string('poll_title')->default('');
1093 $table->integer('poll_start')->unsigned()->default(0);
1094 $table->integer('poll_length')->unsigned()->default(0);
1095 $table->boolean('poll_max_options')->default(1);
1096 $table->integer('poll_last_vote')->unsigned()->default(0);
1097 $table->boolean('poll_vote_change')->unsigned()->default(0);
1098 $table->smallInteger('osu_starpriority')->default(0);
1099 $table->enum('osu_lastreplytype', ['none', 'creator', 'bat'])->default('none');
1100 $table->index('topic_last_post_time', 'last_post_time');
1101 $table->index(['forum_id', 'topic_approved', 'topic_last_post_id'], 'forum_appr_last');
1102 $table->index(['forum_id', 'topic_last_post_time', 'topic_moved_id'], 'fid_time_moved');
1103 $table->index(['topic_id', 'forum_id', 'icon_id'], 'tid_fid_iconid');
1104 $table->index(['forum_id', 'topic_type', 'topic_last_post_time'], 'forum_id_type');
1105 $table->index(['forum_id', 'topic_type', 'osu_starpriority', 'topic_last_post_time'], 'star_sort');
1106 });
1107
1108 Schema::create('phpbb_topics_stars', function (Blueprint $table) {
1109 $table->mediumIncrements('star_id');
1110 $table->unsignedMediumInteger('topic_id');
1111 $table->unsignedMediumInteger('user_id');
1112 $table->enum('type', ['user', 'supporter']);
1113 $table->timestamp('date')->useCurrent();
1114
1115 $table->index(['user_id'], 'user_id');
1116 $table->index(['topic_id', 'user_id'], 'topic_id');
1117 });
1118
1119 Schema::create('phpbb_topics_track', function (Blueprint $table) {
1120 $table->mediumInteger('user_id')->unsigned()->default(0);
1121 $table->mediumInteger('topic_id')->unsigned()->default(0);
1122 $table->mediumInteger('forum_id')->unsigned()->default(0);
1123 $table->integer('mark_time')->unsigned()->default(0);
1124 $table->primary(['user_id', 'topic_id']);
1125 $table->index('forum_id', 'forum_id');
1126 $table->index('topic_id', 'topic_id');
1127 });
1128
1129 Schema::create('phpbb_user_group', function (Blueprint $table) {
1130 $table->mediumInteger('group_id')->unsigned()->default(0);
1131 $table->mediumInteger('user_id')->unsigned()->default(0)->index('user_id');
1132 $table->boolean('group_leader')->unsigned()->default(0);
1133 $table->boolean('user_pending')->unsigned()->default(1);
1134 $table->primary(['group_id', 'user_id']);
1135 });
1136
1137 Schema::create('phpbb_users', function (Blueprint $table) {
1138 $table->mediumIncrements('user_id')->from(2);
1139 $table->tinyInteger('user_type')->default(0);
1140 $table->mediumInteger('group_id')->unsigned()->default(2);
1141 $table->mediumText('user_permissions');
1142 $table->mediumInteger('user_perm_from')->unsigned()->nullable()->default(0);
1143 $table->string('user_ip', 50)->default('');
1144 $table->integer('user_regdate')->unsigned()->default(0);
1145 $table->string('username')->default('');
1146 $table->string('username_clean')->default('');
1147 $table->string('user_password', 64)->default('');
1148 $table->integer('user_passchg')->unsigned()->default(0);
1149 $table->string('user_email', 100)->nullable()->unique('user_email_unique');
1150 $table->string('user_birthday', 10)->default('');
1151 $table->integer('user_lastvisit')->unsigned()->default(0);
1152 $table->integer('user_lastmark')->unsigned()->default(0);
1153 $table->integer('user_lastpost_time')->unsigned()->default(0);
1154 $table->string('user_lastpage', 200)->default('');
1155 $table->string('user_last_confirm_key', 10)->default('');
1156 $table->integer('user_last_search')->unsigned()->default(0);
1157 $table->boolean('user_warnings')->default(0);
1158 $table->integer('user_last_warning')->unsigned()->default(0);
1159 $table->boolean('user_login_attempts')->default(0);
1160 $table->boolean('user_inactive_reason')->default(0);
1161 $table->integer('user_inactive_time')->unsigned()->default(0);
1162 $table->mediumInteger('user_posts')->unsigned()->default(0);
1163 $table->string('user_lang', 30)->default('');
1164 $table->decimal('user_timezone', 5)->default(0.00);
1165 $table->boolean('user_dst')->unsigned()->default(0);
1166 $table->string('user_dateformat', 30)->default('d M Y H:i');
1167 $table->smallInteger('user_style')->unsigned()->default(0);
1168 $table->mediumInteger('user_rank')->unsigned()->default(0);
1169 $table->string('user_colour', 6)->default('');
1170 $table->smallInteger('user_new_privmsg')->default(0);
1171 $table->smallInteger('user_unread_privmsg')->default(0);
1172 $table->integer('user_last_privmsg')->unsigned()->default(0);
1173 $table->boolean('user_message_rules')->unsigned()->default(0);
1174 $table->integer('user_full_folder')->default(-3);
1175 $table->integer('user_emailtime')->unsigned()->default(0);
1176 $table->smallInteger('user_topic_show_days')->unsigned()->default(0);
1177 $table->char('user_topic_sortby_type', 1)->default('t');
1178 $table->char('user_topic_sortby_dir', 1)->default('d');
1179 $table->smallInteger('user_post_show_days')->unsigned()->default(0);
1180 $table->char('user_post_sortby_type', 1)->default('t');
1181 $table->char('user_post_sortby_dir', 1)->default('a');
1182 $table->boolean('user_notify')->unsigned()->default(0);
1183 $table->boolean('user_notify_pm')->unsigned()->default(1);
1184 $table->boolean('user_notify_type')->default(0);
1185 $table->boolean('user_allow_pm')->unsigned()->default(1);
1186 $table->boolean('user_allow_viewonline')->unsigned()->default(1);
1187 $table->boolean('user_allow_viewemail')->unsigned()->default(1);
1188 $table->boolean('user_allow_massemail')->unsigned()->default(1);
1189 $table->integer('user_options')->unsigned()->default(895);
1190 $table->string('user_avatar')->default('');
1191 $table->boolean('user_avatar_type')->default(0);
1192 $table->smallInteger('user_avatar_width')->unsigned()->default(0);
1193 $table->smallInteger('user_avatar_height')->unsigned()->default(0);
1194 $table->mediumText('user_sig');
1195 $table->string('user_sig_bbcode_uid', 5)->default('');
1196 $table->string('user_sig_bbcode_bitfield')->default('');
1197 $table->string('user_from', 100)->default('');
1198 $table->string('user_lastfm')->default('');
1199 $table->string('user_lastfm_session')->default('');
1200 $table->string('user_twitter')->default('');
1201 $table->string('user_msnm')->default('');
1202 $table->string('user_jabber')->default('');
1203 $table->string('user_website', 200)->default('');
1204 $table->text('user_occ', 65535);
1205 $table->text('user_interests', 65535);
1206 $table->string('user_actkey', 32)->default('');
1207 $table->string('user_newpasswd', 32)->default('');
1208 $table->float('osu_mapperrank')->default(0)->index('osu_mapperrank');
1209 $table->boolean('osu_testversion')->default(0);
1210 $table->boolean('osu_subscriber')->default(0);
1211 $table->date('osu_subscriptionexpiry')->nullable();
1212 $table->smallInteger('osu_kudosavailable')->default(0);
1213 $table->smallInteger('osu_kudosdenied')->unsigned()->default(0);
1214 $table->smallInteger('osu_kudostotal')->default(0)->index('osu_kudostotal');
1215 $table->char('country_acronym', 2)->default('')->index('country_acronym');
1216 $table->mediumInteger('userpage_post_id')->unsigned()->nullable();
1217 $table->string('username_previous', 1024)->nullable();
1218 $table->smallInteger('osu_featurevotes')->unsigned()->default(0);
1219 $table->tinyInteger('osu_playstyle')->unsigned()->default(0);
1220 $table->tinyInteger('osu_playmode')->default(0);
1221 $table->string('remember_token', 100)->nullable();
1222 $table->unique('username_clean', 'username_clean');
1223 $table->unique(['username', 'user_id'], 'username_id');
1224 });
1225
1226 Schema::create('phpbb_groups', function (Blueprint $table) {
1227 $table->mediumIncrements('group_id');
1228 $table->tinyInteger('group_type')->default(1);
1229 $table->unsignedTinyInteger('group_founder_manage')->default(0);
1230
1231 $table->string('group_name', 255)->default('');
1232 $table->text('group_desc');
1233 $table->string('group_desc_bitfield', 255)->default('');
1234 $table->unsignedInteger('group_desc_options')->default(7);
1235 $table->string('group_desc_uid', 5)->default('');
1236 $table->unsignedTinyInteger('group_display')->default(0);
1237 $table->string('group_avatar', 255)->default('');
1238 $table->tinyInteger('group_avatar_type')->default(0);
1239 $table->unsignedSmallInteger('group_avatar_width')->default(0);
1240 $table->unsignedSmallInteger('group_avatar_height')->default(0);
1241 $table->unsignedMediumInteger('group_rank')->default(0);
1242 $table->string('group_colour', 6)->default('');
1243 $table->unsignedMediumInteger('group_sig_chars')->default(0);
1244 $table->unsignedTinyInteger('group_receive_pm')->default(0);
1245 $table->unsignedMediumInteger('group_message_limit')->default(0);
1246 $table->unsignedTinyInteger('group_legend')->default(0);
1247
1248 $table->index('group_legend', 'group_legend');
1249 });
1250
1251 Schema::create('phpbb_log', function (Blueprint $table) {
1252 $table->mediumIncrements('log_id');
1253 $table->tinyInteger('log_type')->default(0);
1254 $table->unsignedMediumInteger('user_id')->default(0);
1255 $table->unsignedMediumInteger('forum_id')->default(0);
1256 $table->unsignedMediumInteger('topic_id')->default(0);
1257 $table->unsignedMediumInteger('reportee_id')->default(0);
1258 $table->string('log_ip', 40)->default('');
1259 $table->unsignedInteger('log_time')->default(0);
1260 $table->text('log_operation');
1261 $table->mediumText('log_data');
1262 $table->index('log_type', 'log_type');
1263 $table->index('forum_id', 'forum_id');
1264 $table->index('topic_id', 'topic_id');
1265 $table->index('reportee_id', 'reportee_id');
1266 $table->index('user_id', 'user_id');
1267 });
1268 $this->setRowFormat('phpbb_log', 'COMPRESSED');
1269
1270 Schema::create('phpbb_topics_watch', function (Blueprint $table) {
1271 $table->unsignedMediumInteger('user_id')->default(0);
1272 $table->unsignedMediumInteger('topic_id')->default(0);
1273 $table->unsignedTinyInteger('notify_status')->default(0);
1274
1275 $table->index('topic_id', 'topic_id');
1276 $table->index('notify_status', 'notify_stat');
1277 $table->primary(['user_id', 'topic_id']);
1278 });
1279 $this->setRowFormat('phpbb_topics_watch', 'COMPRESSED');
1280
1281 DB::statement('
1282 CREATE TABLE weak_passwords
1283 (hash binary(16) NOT NULL, PRIMARY KEY (hash))
1284 ');
1285 }
1286
1287 /**
1288 * Reverse the migrations.
1289 *
1290 * @return void
1291 */
1292 public function down()
1293 {
1294 Schema::drop('osu_achievements');
1295 Schema::drop('osu_apikeys');
1296 Schema::drop('osu_badges');
1297 Schema::drop('osu_beatmap_difficulty');
1298 Schema::drop('osu_beatmap_difficulty_attribs');
1299 Schema::drop('osu_beatmaps');
1300 Schema::drop('osu_beatmapsets');
1301 Schema::drop('osu_user_beatmapset_ratings');
1302 Schema::drop('osu_changelog');
1303 Schema::drop('osu_builds');
1304 Schema::drop('osu_countries');
1305 Schema::drop('osu_counts');
1306 Schema::drop('osu_downloads');
1307 Schema::drop('osu_events');
1308 Schema::drop('osu_favouritemaps');
1309 Schema::drop('osu_genres');
1310 Schema::drop('osu_kudos_exchange');
1311 Schema::drop('osu_languages');
1312 Schema::drop('osu_leaders_fruits');
1313 Schema::drop('osu_leaders_mania');
1314 Schema::drop('osu_leaders');
1315 Schema::drop('osu_leaders_taiko');
1316 Schema::drop('osu_login_attempts');
1317 Schema::drop('osu_mirrors');
1318 Schema::drop('osu_replays');
1319 Schema::drop('osu_replays_fruits');
1320 Schema::drop('osu_replays_mania');
1321 Schema::drop('osu_replays_taiko');
1322 Schema::drop('osu_scores_fruits_high');
1323 Schema::drop('osu_scores_fruits');
1324 Schema::drop('osu_scores_high');
1325 Schema::drop('osu_scores_mania_high');
1326 Schema::drop('osu_scores_mania');
1327 Schema::drop('osu_scores');
1328 Schema::drop('osu_scores_taiko_high');
1329 Schema::drop('osu_scores_taiko');
1330 Schema::drop('osu_user_achievements');
1331 Schema::drop('osu_user_banhistory');
1332 Schema::drop('osu_user_beatmap_playcount');
1333 Schema::drop('osu_user_donations');
1334 Schema::drop('osu_username_change_history');
1335 Schema::drop('osu_user_month_playcount');
1336 Schema::drop('osu_user_performance_rank');
1337 Schema::drop('osu_user_replayswatched');
1338 Schema::drop('osu_user_reports');
1339 Schema::drop('osu_user_security');
1340 Schema::drop('osu_user_stats_fruits');
1341 Schema::drop('osu_user_stats_mania');
1342 Schema::drop('osu_user_stats');
1343 Schema::drop('osu_user_stats_taiko');
1344 Schema::drop('phpbb_acl_groups');
1345 Schema::drop('phpbb_acl_options');
1346 Schema::drop('phpbb_acl_roles_data');
1347 Schema::drop('phpbb_disallow');
1348 Schema::drop('phpbb_forums');
1349 Schema::drop('phpbb_posts');
1350 Schema::drop('phpbb_ranks');
1351 Schema::drop('phpbb_smilies');
1352 Schema::drop('phpbb_topics');
1353 Schema::drop('phpbb_topics_stars');
1354 Schema::drop('phpbb_topics_track');
1355 Schema::drop('phpbb_user_group');
1356 Schema::drop('phpbb_users');
1357 Schema::drop('phpbb_groups');
1358 Schema::drop('phpbb_log');
1359 Schema::drop('phpbb_topics_watch');
1360 Schema::drop('weak_passwords');
1361 }
1362
1363 private function setRowFormat($table, $format)
1364 {
1365 DB::statement("ALTER TABLE `{$table}` ROW_FORMAT={$format};");
1366 }
1367
1368 private function addBinary($table, $columnname, $size, $nullable = false, $after = null)
1369 {
1370 $null = $nullable ? 'NULL' : 'NOT NULL';
1371 $after = $after ? 'AFTER '.$after : '';
1372 $statement = "ALTER TABLE `{$table}` ADD `{$columnname}` BINARY({$size}) {$null} {$after};";
1373 DB::statement($statement);
1374 }
1375
1376 private function comment($table, $comment)
1377 {
1378 DB::statement("ALTER TABLE `{$table}` COMMENT = '{$comment}';");
1379 }
1380}