OSDN Git Service

[add] #37370 2.2.1までのページ追加。
[hengband/web.git] / score / db_common.inc
1 <?php
2 class ScoreDB
3 {
4     private static $sort_mode_list = ['default' => 'score', 'newcome'];
5
6     public function __construct()
7     {
8         $this->dbh = new PDO('sqlite:db/score.db');
9         $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
10
11         $this->set_sort_mode(filter_input(INPUT_GET, 'sort'));
12     }
13
14     public function get_defines()
15     {
16         $id_define_array = [];
17         $tables = [['race', 'races'], ['class', 'classes'], ['personality', 'personalities'], ['realm', 'realms']];
18         foreach ($tables as $t) {
19             $id_define_array[$t[0]] = [];
20             $stmt = $this->dbh->query("SELECT {$t[0]}_id, {$t[0]}_name FROM {$t[1]} ORDER BY {$t[0]}_id");
21             foreach ($stmt->fetchAll(PDO::FETCH_NUM) as $row) {
22                 $id_define_array[$t[0]][intval($row[0])] = $row[1];
23             }
24         }
25
26         return $id_define_array;
27     }
28
29     /**
30      * スコア表示モードを設定する
31      *
32      * @param string $mode 設定する表示モード
33      * 'score' - スコア順に表示(デフォルト)
34      * 'newcome' - 新着順に表示
35      * 存在しない表示モードが指定された場合デフォルトの表示モードが設定される
36      */
37     public function set_sort_mode($mode)
38     {
39         if ($mode !== null && in_array($mode, self::$sort_mode_list)) {
40             $this->sort_mode = $mode;
41         } else {
42             $this->sort_mode = self::$sort_mode_list['default'];
43         }
44     }
45
46
47     /**
48      * スコア表示モード名を取得する
49      *
50      * @return string スコア表示モード名
51      */
52     public function get_sort_mode_name()
53     {
54         switch ($this->sort_mode) {
55         case 'score':
56             return "スコア順";
57         case 'newcome':
58             return "新着順";
59         default:
60             return "不明";
61         }
62     }
63
64
65     /**
66      * スコア検索の絞り込み用WHERE句を取得する
67      *
68      * @return array スコア検索絞り込み用のWHERE句('where')とプレースホルダに渡すパラメータ配列('params')の連想配列
69      */
70     private function get_search_condition()
71     {
72         $last_days = filter_input(INPUT_GET, 'last_days', FILTER_VALIDATE_INT);
73         $params = [];
74
75         if ($last_days > 0) {
76             $wheres[] = "date >= datetime('now', 'localtime', '-{$last_days} days')";
77         }
78
79         $from_date = filter_input(INPUT_GET, 'fd');
80         $to_date = filter_input(INPUT_GET, 'td');
81
82         if ($from_date !== null && $to_date !== null &&
83             strlen($from_date) > 0 && strlen($to_date) > 0) {
84             $wheres[] = "(date >= date(:from_date) AND date < date(:to_date, '+1 days'))";
85             $params[":from_date"] = $from_date;
86             $params[":to_date"] = $to_date;
87         }
88
89         foreach (['race_id', 'class_id', 'personality_id'] as $key_column) {
90             $val = filter_input(INPUT_GET, $key_column, FILTER_VALIDATE_INT);
91             if ($val > 0) {
92                 $wheres[] = "{$key_column} = :{$key_column}";
93                 $params[":{$key_column}"] = $val;
94             }
95         }
96
97         foreach (['realm1_id', 'realm2_id'] as $idx => $key_column) {
98             $val = filter_input(INPUT_GET, $key_column, FILTER_VALIDATE_INT);
99             if ($val > 0) {
100                 $seq = $idx + 1;
101                 $wheres[] = "score_id IN (SELECT score_id FROM score_realms WHERE realm_id = :{$key_column} AND realm_seq = {$seq})";
102                 $params[":{$key_column}"] = $val;
103             }
104         }
105
106         foreach (['name'] as $key_column) {
107             $val = filter_input(INPUT_GET, $key_column);
108             $match_mode = filter_input(INPUT_GET, $key_column."_match");
109             if ($val !== null && strlen($val) > 0) {
110                 $wheres[] = ($match_mode == "strict") ? "{$key_column} = :{$key_column}" : "{$key_column} LIKE :{$key_column}";
111                 $params[":{$key_column}"] = ($match_mode == "strict") ? $val : "%".$val."%";
112             }
113         }
114
115         foreach (['sex'] as $key_column) {
116             $val = filter_input(INPUT_GET, $key_column, FILTER_VALIDATE_INT);
117             if ($val !== false && $val !== null) {
118                 $wheres[] = "{$key_column} = :{$key_column}";
119                 $params[":{$key_column}"] = $val;
120             }
121         }
122
123         foreach (['killer'] as $key_column) {
124             $val = filter_input(INPUT_GET, $key_column);
125             if ($val !== null && strlen($val) > 0) {
126                 $wheres[] = "({$key_column} LIKE :{$key_column} OR {$key_column} = :{$key_column}1 OR {$key_column} = :{$key_column}2)";
127                 $params[":{$key_column}"] = "%".$val."%";
128                 $params[":{$key_column}1"] = "麻痺状態で".$val;
129                 $params[":{$key_column}2"] = "彫像状態で".$val;
130             }
131         }
132
133         $result['where'] = isset($wheres) ? 'WHERE '.implode(' AND ', $wheres) : '';
134         $result['params'] = $params;
135
136         return $result;
137     }
138
139
140     /**
141      * スコアソート用のORDER BY句を取得する
142      *
143      * @return string スコアソート用のORDER BY句
144      */
145     private function get_order_by()
146     {
147         switch ($this->sort_mode) {
148         case "score":
149             $order_by = "ORDER BY score DESC";
150             break;
151         case "newcome":
152             $order_by = 'ORDER BY score_id DESC';
153             break;
154         }
155
156         return $order_by;
157     }
158
159
160     /**
161      * スコア検索用のSQLクエリを取得する
162      *
163      * @param integer $offset スコア取得開始位置
164      * @param integer $limit スコア取得最大件数
165      * @param string $where スコア検索に用いるWHERE句
166      * @param string $order_by スコアソートに用いるORDER BY句
167      * @return string スコア検索用SQLクエリ
168      */
169     private function get_search_query($offset, $limit, $where, $order_by)
170     {
171         $query = <<<EOM
172 WITH
173   target_score_ids
174 AS
175  (SELECT
176     score_id
177   FROM
178     scores
179   {$where}
180   {$order_by}
181   LIMIT {$offset}, {$limit})
182 SELECT
183   *,
184   CASE
185     WHEN killer = 'ripe' THEN '勝利の後引退'
186     WHEN killer = 'Seppuku' THEN '勝利の後切腹'
187     WHEN dead_place IS NULL THEN killer || 'に殺された'
188     ELSE dead_place || 'で' || killer || 'に殺された'
189   END AS death_reason
190 FROM
191  (SELECT
192     score_id,
193     group_concat(realm_name) AS realms_name
194   FROM
195     target_score_ids
196   NATURAL LEFT JOIN
197     score_realms
198   NATURAL LEFT JOIN
199     realms
200   GROUP BY
201     score_id)
202 NATURAL INNER JOIN
203   scores
204 NATURAL INNER JOIN
205   races
206 NATURAL INNER JOIN
207   classes
208 NATURAL INNER JOIN
209   personalities
210 {$order_by}
211 EOM;
212         return $query;
213     }
214
215
216     /**
217      * 検索でヒットしたスコアの総件数を取得する
218      *
219      * @param string $where スコア検索に用いるWHERE句
220      * @return integer スコア総件数
221      */
222     private function get_query_data_count($where, $params)
223     {
224         $stmt = $this->dbh->prepare("SELECT count(*) AS data_count from scores {$where}");
225         $stmt->execute($params);
226         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
227
228         return intval($res[0]['data_count']);
229     }
230
231
232     /**
233      * スコアを検索する
234      *
235      * @param integer $start_num 検索するスコアの開始位置
236      * @param integer $count 検索するスコア数
237      *
238      * @return array 検索したスコアの配列と、条件に合致するスコアの総件数の連想配列
239      */
240     public function search_score($start_num, $count)
241     {
242         $cond = $this->get_search_condition();
243         $order_by = $this->get_order_by();
244         $query_sql = $this->get_search_query(intval($start_num / $count) * $count, $count, $cond['where'], $order_by);
245
246         $search_start_time = microtime(true);
247         $this->dbh->beginTransaction();
248
249         $score_stmt = $this->dbh->prepare($query_sql);
250         $score_stmt->execute($cond['params']);
251         $result['scores'] = $score_stmt->fetchAll(PDO::FETCH_ASSOC);
252         $result['total_data_count'] = $this->get_query_data_count($cond['where'], $cond['params']);
253
254         $this->dbh->commit();
255         $result['elapsed_time'] = microtime(true) - $search_start_time;
256
257         return $result;
258     }
259
260     public function get_db_handle()
261     {
262         return $this->dbh;
263     }
264
265     private function update_killers_cache_table()
266     {
267         $this->dbh->exec("DROP TABLE IF EXISTS killers_cache");
268         $this->dbh->exec(
269             <<<EOM
270 CREATE TABLE
271   killers_cache
272 AS
273 SELECT
274   killer_name,
275   count(*) AS killer_count_total,
276   count(killed_status = 0 OR NULL) AS killer_count_normal,
277   count(killed_status != 0 OR NULL) AS killer_count_freeze
278 FROM
279  (SELECT
280     CASE
281       WHEN killer LIKE '麻痺状態で%' THEN substr(killer, 6)
282       WHEN killer LIKE '彫像状態で%' THEN substr(killer, 6)
283       WHEN killer = 'ripe' THEN '引退'
284       WHEN killer = 'Seppuku' THEN '切腹'
285       ELSE killer
286     END AS killer_name,
287     CASE
288       WHEN killer LIKE '麻痺状態で%' THEN 1
289       WHEN killer LIKE '彫像状態で%' THEN 2
290       ELSE 0
291     END AS killed_status
292   FROM
293     scores
294  )
295 GROUP BY
296   killer_name
297 ORDER BY
298   killer_count_total DESC
299 EOM
300         );
301     }
302
303     public function get_killers_table()
304     {
305         try {
306             $this->dbh->beginTransaction();
307             if (!$this->get_cache_status('killers_cache')) {
308                 $this->update_killers_cache_table();
309                 $this->update_cache_status('killers_cache', 1);
310             }
311             $this->dbh->commit();
312         } catch (PDOException $e) {
313             $this->dbh->rollBack();
314         }
315
316         $killers = $this->dbh->query("SELECT * FROM killers_cache ORDER BY killer_count_total DESC")->fetchAll(PDO::FETCH_ASSOC);
317
318         return $killers;
319     }
320
321
322     /**
323      * 統計情報のキャッシュテーブルを更新する
324      *
325      * 種族・職業・性格について各種統計情報を取得しキャッシュテーブルに保存する
326      * 通常の統計情報の取得はこのキャッシュテーブルから行う
327      */
328     private function update_statistics_cache_tables()
329     {
330         $statistics_list = ['race', 'class', 'personality'];
331
332         foreach ($statistics_list as $stat) {
333             $table_name = $stat."_statistics";
334             $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
335             $this->dbh->exec(
336                 <<<EOM
337 CREATE TABLE $table_name AS
338 SELECT
339   {$stat}_id,
340   count(sex=1 or NULL) AS male_count,
341   count(sex=0 or NULL) AS female_count,
342   count(*) AS total_count,
343   count(winner=1 OR NULL) AS winner_count,
344   CAST(avg(score) AS INTEGER) AS average_score,
345   max(score) AS max_score
346 FROM scores
347 GROUP BY ${stat}_id
348 EOM
349             );
350         }
351     }
352
353
354     /**
355      * 魔法領域統計情報のキャッシュテーブルを更新する
356      *
357      * 魔法領域1・魔法領域2について各種統計情報を取得しキャッシュテーブルに保存する
358      * 通常の統計情報の取得はこのキャッシュテーブルから行う
359      */
360     private function update_realm_statistics_cache_tables()
361     {
362         foreach (range(1, 2) as $seq) {
363             $table_name = "realm{$seq}_statistics";
364             $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
365             $this->dbh->exec(
366                 <<<EOM
367 CREATE TABLE $table_name AS
368 SELECT
369   class_id, class_name,
370   realm_id, realm_name,
371   count(*) AS total_count,
372   count(sex=1 OR NULL) AS male_count,
373   count(sex=0 OR NULL) AS female_count,
374   count(winner=1 OR NULL) AS winner_count,
375   CAST(avg(score) AS INTEGER) AS average_score,
376   max(score) AS max_score
377 FROM score_realms
378 NATURAL INNER JOIN scores
379 NATURAL INNER JOIN classes
380 NATURAL INNER JOIN realms
381 WHERE realm_seq={$seq}
382 GROUP BY class_id, realm_id
383 ORDER BY class_id, total_count DESC
384 EOM
385             );
386         }
387     }
388
389
390     /**
391      * 統計情報を取得する
392      *
393      * @param integer $sort_key_column 表示順序のキーとするカラムの名称
394      *
395      * @return array 統計情報
396      */
397     public function get_statistics_tables($sort_key_column)
398     {
399         try {
400             $this->dbh->beginTransaction();
401             if (!$this->get_cache_status('statistics_cache')) {
402                 $this->update_statistics_cache_tables();
403                 $this->update_realm_statistics_cache_tables();
404                 $this->update_cache_status('statistics_cache', 1);
405             }
406             $this->dbh->commit();
407         } catch (PDOException $e) {
408             $this->dbh->rollBack();
409         }
410
411         $stat = [];
412
413         $this->dbh->beginTransaction();
414         foreach ([['race', 'races'], ['class', 'classes'], ['personality', 'personalities']] as $kind) {
415             $stmt = $this->dbh->query("SELECT ${kind[0]}_id AS id, ${kind[0]}_name AS name, * FROM ${kind[0]}_statistics NATURAL JOIN ${kind[1]} ORDER BY ${sort_key_column} DESC");
416             $stat[$kind[0]] = $stmt->fetchAll(PDO::FETCH_ASSOC);
417         }
418         foreach (['realm1', 'realm2'] as $realm) {
419             $stmt = $this->dbh->query("SELECT * FROM {$realm}_statistics;");
420             $stat[$realm] = $stmt->fetchAll(PDO::FETCH_ASSOC);
421         }
422         $this->dbh->commit();
423
424         return $stat;
425     }
426
427     public function table_exists($table_name)
428     {
429         $stmt = $this->dbh->prepare("SELECT count(*) AS table_exists FROM sqlite_master WHERE type='table' AND name=?");
430         $stmt->execute([$table_name]);
431         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
432
433         return intval($res[0]['table_exists']) === 1;
434     }
435
436
437     public function get_cache_status($cache_table_name)
438     {
439         if (!$this->table_exists('cache_status_table')) {
440             $this->create_cache_status_table();
441         }
442
443         $stmt = $this->dbh->prepare("SELECT cached FROM cache_status_table WHERE table_name=?");
444         $stmt->execute([$cache_table_name]);
445         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
446
447         return count($res) === 1 ? intval($res[0]['cached']) : 0;
448     }
449
450     public function update_cache_status($cache_table_name, $status)
451     {
452         if (!$this->table_exists('cache_status_table')) {
453             $this->create_cache_status_table();
454         }
455
456         $stmt = $this->dbh->prepare("INSERT OR REPLACE INTO cache_status_table VALUES(?, ?)");
457         $stmt->execute([$cache_table_name, $status]);
458
459         return $stmt->rowCount();
460     }
461
462     public function create_cache_status_table()
463     {
464         $this->dbh->exec(
465             <<<EOM
466 CREATE TABLE cache_status_table
467   (table_name TEXT PRIMARY KEY,
468    cached BOOLEAN)
469 EOM
470         );
471     }
472
473     public function register_new_score($score_data)
474     {
475         $insert_stmt = $this->dbh->prepare(
476             <<<EOM
477 INSERT INTO scores
478  (version, score, name,
479   race_id, class_id, personality_id,
480   sex, level, depth, maxlv, maxdp,
481   au, turns, winner, killer)
482 SELECT
483  :version, :score, :name,
484  race_id, class_id, personality_id,
485  :sex, :level, :depth, :maxlv, :maxdp,
486  :au, :turns, :winner, :killer
487 FROM
488   races, classes, personalities
489 WHERE
490   race_name = :race AND
491   class_name = :class AND
492   personality_name = :personality
493 EOM
494         );
495
496         $realm_insert_stmt = $this->dbh->prepare(
497             <<<EOM
498 INSERT INTO score_realms
499  (score_id, realm_id, realm_seq)
500 SELECT
501   ?, realm_id, ?
502 FROM
503   realms
504 WHERE
505   realm_name = ?
506 EOM
507         );
508
509         try {
510             $this->dbh->beginTransaction();
511             if ($insert_stmt->execute($score_data['character_info']) === false ||
512                 $insert_stmt->rowCount() !== 1) {
513                 $dbh->rollBack();
514                 return false;
515             }
516
517             // NOTE: score_idはINTEGER PRIMARY KEYなのでROWIDを参照している
518             //       したがってlastInsertIdで追加されたスコアのscore_idを取得可能
519             $score_id = $this->dbh->lastInsertId();
520
521             foreach ($score_data['realm_info'] as $realm_seq => $realm_name) {
522                 if ($realm_insert_stmt->execute([$score_id, $realm_seq + 1, $realm_name]) === false ||
523                     $realm_insert_stmt->rowCount() !== 1) {
524                     $dbh->rollBack();
525                     return false;
526                 }
527             }
528
529             // スコアが追加されたので死因ランキング・人気ランキングのキャッシュをクリア
530             $this->update_cache_status('killers_cache', 0);
531             $this->update_cache_status('statistics_cache', 0);
532             $this->dbh->commit();
533
534             return $score_id;
535         } catch (PDOException $e) {
536             $dbh->rollBack();
537         }
538
539         return false;
540     }
541
542
543     /**
544      * 死亡場所を更新する
545      *
546      * @param integer $score_id 死亡場所を更新するスコアのscore_id
547      * @param string|FALSE $dead_place 死亡場所を表す文字列。
548      *                     FALSEの場合はなにもしない。
549      *
550      * @return integer 更新した行数。
551      */
552     public function update_dead_place($score_id, $dead_place)
553     {
554         if ($dead_place === false) {
555             return false;
556         }
557         $stmt = $this->dbh->prepare(
558             <<<EOM
559 UPDATE scores
560 SET dead_place = :dead_place
561 WHERE score_id = :score_id
562 EOM
563         );
564
565         $result = $stmt->execute(
566             [':score_id' => $score_id,
567              ':dead_place' => $dead_place,
568              ]
569         );
570
571         return $result;
572     }
573 }