OSDN Git Service

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