keii.dev
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.
5
6namespace App\Models;
7
8use App\Models\Score\Best as ScoreBest;
9use DB;
10use Illuminate\Database\Schema\Blueprint;
11use Schema;
12
13/**
14 * @property string $acronym
15 * @property bool $active
16 * @property int $chart_id
17 * @property \Carbon\Carbon|null $chart_month
18 * @property \Carbon\Carbon|null $end_date
19 * @property bool $mode_specific
20 * @property string $name
21 * @property \Carbon\Carbon|null $start_date
22 * @property string $type
23 */
24class Spotlight extends Model
25{
26 const PERIODIC_TYPES = ['bestof', 'monthly'];
27 const SPOTLIGHT_MAX_RESULTS = 40;
28
29 public $timestamps = false;
30
31 protected $casts = [
32 'active' => 'boolean',
33 'chart_month' => 'datetime',
34 'end_date' => 'datetime',
35 'mode_specific' => 'boolean',
36 'start_date' => 'datetime',
37 ];
38 protected $primaryKey = 'chart_id';
39 protected $table = 'osu_charts';
40
41 public function beatmapsets(string $mode)
42 {
43 $tableName = DB::connection('mysql-charts')->getDatabaseName().'.'.$this->beatmapsetsTableName($mode);
44
45 return Beatmapset::whereIn('beatmapset_id', function ($q) use ($tableName) {
46 return $q->from($tableName)->select('beatmapset_id');
47 });
48 }
49
50 /**
51 * Returns a builder for best scores.
52 * IMPORTANT: The models returned by the query will have the incorrect table set.
53 *
54 * @param string $mode
55 * @return \Illuminate\Database\Eloquent\Builder
56 */
57 public function scores(string $mode)
58 {
59 $clazz = ScoreBest\Model::getClass($mode);
60 $model = new $clazz();
61 $model->setTable($this->bestScoresTableName($mode));
62 $model->setConnection('mysql-charts');
63
64 return $model->newQuery();
65 }
66
67 /**
68 * Returns a builder for user_stats.
69 * IMPORTANT: The models returned by the query will have the incorrect table set.
70 *
71 * @param string $mode
72 * @return \Illuminate\Database\Eloquent\Builder
73 */
74 public function userStats(string $mode)
75 {
76 $clazz = UserStatistics\Model::getClass($mode);
77 $model = new $clazz();
78 $model->setTable($this->userStatsTableName($mode));
79 $model->setConnection('mysql-charts');
80
81 return $model->newQuery();
82 }
83
84 public function hasMode(string $mode)
85 {
86 return Schema::connection('mysql-charts')->hasTable($this->userStatsTableName($mode));
87 }
88
89 public function participantCount(string $mode)
90 {
91 return $this->userStats($mode)->count();
92 }
93
94 public function ranking(string $mode)
95 {
96 // These models will not have the correct table name set on them
97 // as they get overriden when Laravel hydrates them.
98 return $this->userStats($mode)
99 ->with(['user', 'user.country', 'user.team'])
100 ->whereHas('user', function ($userQuery) {
101 $model = new User();
102 $userQuery
103 ->from($model->tableName(true))
104 ->default();
105 })
106 ->orderBy('ranked_score', 'desc')
107 ->limit(static::SPOTLIGHT_MAX_RESULTS);
108 }
109
110 //=========================
111 // Table helpers
112 //=========================
113
114 public function beatmapsetsTableName(string $mode)
115 {
116 if ($mode === 'osu' || !$this->mode_specific) {
117 $name = "{$this->acronym}_beatmapsets";
118 } else {
119 $name = "{$this->acronym}_beatmapsets_{$mode}";
120 }
121
122 return $name;
123 }
124
125 public function bestScoresTableName(string $mode)
126 {
127 if ($mode === 'osu') {
128 $name = "{$this->acronym}_scores_high";
129 } else {
130 $name = "{$this->acronym}_scores_{$mode}_high";
131 }
132
133 return $name;
134 }
135
136 public function userStatsTableName(string $mode)
137 {
138 if ($mode === 'osu') {
139 $name = "{$this->acronym}_user_stats";
140 } else {
141 $name = "{$this->acronym}_user_stats_{$mode}";
142 }
143
144 return $name;
145 }
146
147 public function createTables()
148 {
149 DB::connection('mysql-charts')->transaction(function () {
150 $modes = array_keys(Beatmap::MODES);
151 if ($this->mode_specific) {
152 foreach ($modes as $mode) {
153 static::createBeatmapsetTable($this->beatmapsetsTableName($mode));
154 }
155 } else {
156 static::createBeatmapsetTable($this->beatmapsetsTableName('osu'));
157 }
158
159 foreach ($modes as $mode) {
160 static::createBestScoresTable($this->bestScoresTableName($mode));
161 static::createUserStatsTable($this->userStatsTableName($mode));
162 }
163 });
164 }
165
166 private static function createBeatmapsetTable(string $name)
167 {
168 \Log::debug("create table {$name}");
169
170 Schema::connection('mysql-charts')->create($name, function (Blueprint $table) {
171 $table->charset = 'utf8';
172 $table->collation = 'utf8_general_ci';
173
174 $table->unsignedMediumInteger('beatmapset_id')->primary();
175 });
176 }
177
178 private static function createBestScoresTable(string $name)
179 {
180 \Log::debug("create table {$name}");
181
182 Schema::connection('mysql-charts')->create($name, function (Blueprint $table) {
183 $table->charset = 'utf8';
184 $table->collation = 'utf8_general_ci';
185
186 $table->increments('score_id');
187 $table->unsignedMediumInteger('beatmap_id')->default(0);
188 $table->unsignedMediumInteger('beatmapset_id')->default(0);
189 $table->mediumInteger('user_id')->default(0);
190 $table->integer('score')->default(0);
191 $table->unsignedSmallInteger('maxcombo')->default(0);
192 $table->enum('rank', ['A', 'B', 'C', 'D', 'S', 'SH', 'X', 'XH']);
193 $table->unsignedSmallInteger('count50')->default(0);
194 $table->unsignedSmallInteger('count100')->default(0);
195 $table->unsignedSmallInteger('count300')->default(0);
196 $table->unsignedSmallInteger('countmiss')->default(0);
197 $table->unsignedSmallInteger('countgeki')->default(0);
198 $table->unsignedSmallInteger('countkatu')->default(0);
199 $table->boolean('perfect')->default(0);
200 $table->unsignedMediumInteger('enabled_mods')->default(0);
201 $table->timestamp('date')->useCurrent();
202 $table->unique(['user_id', 'beatmap_id'], 'user_beatmap');
203 $table->index(['beatmap_id', 'score'], 'beatmap_score');
204 $table->index(['user_id', 'beatmapset_id'], 'user_beatmapset');
205 });
206 }
207
208 private static function createUserStatsTable(string $name)
209 {
210 \Log::debug("create table {$name}");
211
212 Schema::connection('mysql-charts')->create($name, function (Blueprint $table) {
213 $table->charset = 'utf8';
214 $table->collation = 'utf8_general_ci';
215
216 $table->mediumInteger('user_id')->primary();
217 $table->unsignedMediumInteger('count300')->default(0);
218 $table->unsignedMediumInteger('count100')->default(0);
219 $table->unsignedMediumInteger('count50')->default(0);
220 $table->unsignedMediumInteger('countMiss')->default(0);
221 $table->unsignedBigInteger('accuracy_total');
222 $table->unsignedBigInteger('accuracy_count');
223 $table->float('accuracy')->unsigned();
224 $table->mediumInteger('playcount');
225 $table->bigInteger('ranked_score');
226 $table->bigInteger('total_score');
227 $table->mediumInteger('x_rank_count');
228 $table->mediumInteger('s_rank_count');
229 $table->mediumInteger('a_rank_count');
230 $table->mediumInteger('rank');
231 $table->float('level')->unsigned();
232 $table->unsignedMediumInteger('replay_popularity')->default(0);
233 $table->unsignedMediumInteger('fail_count')->default(0);
234 $table->unsignedMediumInteger('exit_count')->default(0);
235 $table->unsignedSmallInteger('max_combo')->default(0);
236 $table->index('total_score', 'total_score');
237 $table->index('ranked_score', 'ranked_score');
238 $table->index('playcount', 'playcount');
239 $table->index('accuracy', 'accuracy');
240 $table->index('rank', 'rank');
241 });
242 }
243}