OSDN Git Service

[add]種族・職業・性格・領域のIDと名称を結びつける定義配列を得るメソッド
[hengband/web.git] / 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         foreach (['race_id', 'class_id', 'personality_id'] as $key_column) {
79             $val = filter_input(INPUT_GET, $key_column, FILTER_VALIDATE_INT); 
80             if ($val > 0) {
81                 $wheres[] = "{$key_column} = :{$key_column}";
82                 $params[":{$key_column}"] = $val;
83             }
84         }
85
86         foreach (['name'] as $key_column) {
87             $val = filter_input(INPUT_GET, $key_column);
88             if ($val !== NULL && strlen($val) > 0) {
89                 $wheres[] = "{$key_column} = :{$key_column}";
90                 $params[":{$key_column}"] = $val;
91             }
92         }
93
94         foreach (['killer'] as $key_column) {
95             $val = filter_input(INPUT_GET, $key_column);
96             if ($val !== NULL && strlen($val) > 0) {
97                 $wheres[] = "({$key_column} LIKE :{$key_column} OR {$key_column} = :{$key_column}1 OR {$key_column} = :{$key_column}2)";
98                 $params[":{$key_column}"] = "%".$val."%";
99                 $params[":{$key_column}1"] = "麻痺状態で".$val;
100                 $params[":{$key_column}2"] = "彫像状態で".$val;
101             }
102         }
103
104         $result['where'] = isset($wheres) ? 'WHERE '.implode(' AND ', $wheres) : '';
105         $result['params'] = $params;
106
107         return $result;
108     }
109
110
111     /**
112      * スコアソート用のORDER BY句を取得する
113      *
114      * @return string スコアソート用のORDER BY句
115      */
116     private function get_order_by()
117     {
118         switch ($this->sort_mode) {
119         case "score":
120             $order_by = "ORDER BY score DESC";
121             break;
122         case "newcome":
123             $order_by = 'ORDER BY score_id DESC';
124             break;
125         }
126
127         return $order_by;
128     }
129
130
131     /**
132      * スコア検索用のSQLクエリを取得する
133      *
134      * @param integer $offset スコア取得開始位置
135      * @param integer $limit スコア取得最大件数
136      * @param string $where スコア検索に用いるWHERE句
137      * @param string $order_by スコアソートに用いるORDER BY句
138      * @return string スコア検索用SQLクエリ
139      */
140     private function get_search_query($offset, $limit, $where, $order_by)
141     {
142         $query = <<<EOM
143 SELECT
144   *,
145   group_concat(realm_name) AS realms_name,
146   CASE
147     WHEN killer = 'ripe' THEN '勝利の後引退'
148     WHEN killer = 'Seppuku' THEN '勝利の後切腹'
149     ELSE killer || 'に殺された'
150   END AS death_reason
151 FROM
152  (SELECT
153     *
154   FROM
155     scores
156   {$where}
157   {$order_by}
158   LIMIT {$offset}, {$limit}) AS s
159 NATURAL INNER JOIN
160   races
161 NATURAL INNER JOIN
162   classes
163 NATURAL INNER JOIN
164   personalities
165 NATURAL LEFT JOIN
166   score_realms
167 NATURAL LEFT JOIN
168   realms
169 GROUP BY
170   score_id
171 {$order_by}
172 EOM;
173         return $query;
174     }
175
176
177     /**
178      * 検索でヒットしたスコアの総件数を取得する
179      *
180      * @param string $where スコア検索に用いるWHERE句
181      * @return integer スコア総件数
182      */
183     private function get_query_data_count($where, $params)
184     {
185         $stmt = $this->dbh->prepare("SELECT count(*) AS data_count from scores {$where}");
186         $stmt->execute($params);
187         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
188
189         return intval($res[0]['data_count']);
190     }
191
192
193     /**
194      * スコアを検索する
195      *
196      * @param integer $start_num 検索するスコアの開始位置
197      * @param integer $count 検索するスコア数
198      *
199      * @return array 検索したスコアの配列と、条件に合致するスコアの総件数の連想配列
200      */
201     public function search_score($start_num, $count)
202     {
203         $cond = $this->get_search_condition();
204         $order_by = $this->get_order_by();
205         $query_sql = $this->get_search_query(intval($start_num / $count) * $count, $count, $cond['where'], $order_by);
206
207         $search_start_time = microtime(true);
208         $this->dbh->beginTransaction();
209
210         $score_stmt = $this->dbh->prepare($query_sql);
211         $score_stmt->execute($cond['params']);
212         $result['scores'] = $score_stmt->fetchAll(PDO::FETCH_ASSOC);
213         $result['total_data_count'] = $this->get_query_data_count($cond['where'], $cond['params']);
214
215         $this->dbh->commit();
216         $result['elapsed_time'] = microtime(true) - $search_start_time;
217
218         return $result;
219     }
220
221     public function get_db_handle() {
222         return $this->dbh;
223     }
224
225     private function update_killers_cache_table()
226     {
227         try {
228             $this->dbh->beginTransaction();
229
230             $this->dbh->exec("DROP TABLE IF EXISTS killers_cache");
231             $this->dbh->exec(<<<EOM
232 CREATE TABLE
233   killers_cache
234 AS
235 SELECT
236   killer_name,
237   count(*) AS killer_count_total,
238   count(killed_status = 0 OR NULL) AS killer_count_normal,
239   count(killed_status != 0 OR NULL) AS killer_count_freeze
240 FROM
241  (SELECT
242     CASE
243       WHEN killer LIKE '麻痺状態で%' THEN substr(killer, 6)
244       WHEN killer LIKE '彫像状態で%' THEN substr(killer, 6)
245       WHEN killer = 'ripe' THEN '引退'
246       WHEN killer = 'Seppuku' THEN '切腹'
247       ELSE killer
248     END AS killer_name,
249     CASE
250       WHEN killer LIKE '麻痺状態で%' THEN 1
251       WHEN killer LIKE '彫像状態で%' THEN 2
252       ELSE 0
253     END AS killed_status
254   FROM
255     scores
256  )
257 GROUP BY
258   killer_name
259 ORDER BY
260   killer_count_total DESC
261 EOM
262             );
263             $this->dbh->commit();
264
265             return TRUE;
266         } catch (PDOException $e) {
267             $this->dbh->rollBack();
268
269             return FALSE;
270         }
271     }
272
273     public function get_killers_table()
274     {
275         //$this->update_cache_status('killers_cache', 0);
276         if (!$this->get_cache_status('killers_cache')) {
277             $this->update_killers_cache_table();
278             $this->update_cache_status('killers_cache', 1);
279         }
280
281         $killers = $this->dbh->query("SELECT * FROM killers_cache ORDER BY killer_count_total DESC")->fetchAll(PDO::FETCH_ASSOC);
282
283         return $killers;
284     }
285
286
287     /**
288      * 統計情報のキャッシュテーブルを更新する
289      *
290      * 種族・職業・性格について各種統計情報を取得しキャッシュテーブルに保存する
291      * 通常の統計情報の取得はこのキャッシュテーブルから行う
292      *
293      * @return boolean 生成に成功したらTRUE、失敗したらFALSE
294      */
295     private function update_statistics_cache_tables() {
296         $statistics_list = ['race', 'class', 'personality'];
297
298         try {
299             foreach ($statistics_list as $stat) {
300                 $table_name = $stat."_statistics";
301                 $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
302                 $stmt = $this->dbh->query(
303                     <<<EOM
304 CREATE TABLE $table_name AS
305   SELECT
306     {$stat}_id,
307     count(sex=1 or NULL) AS male_count,
308     count(sex=0 or NULL) AS female_count,
309     count(*) AS total_count,
310     count(winner=1 OR NULL) AS winner_count,
311     avg(score) AS average_score,
312     max(score) AS max_score
313   FROM scores
314 GROUP BY ${stat}_id
315 EOM
316                 );
317             }
318
319             return TRUE;
320         } catch (PDOException $e) {
321             return FALSE;
322         }
323     }
324
325
326     /**
327      * 統計情報を取得する
328      *
329      * @param integer $sort_key_column 表示順序のキーとするカラムの名称
330      *
331      * @return array 統計情報
332      */
333     public function get_statistics_tables($sort_key_column) {
334         if (!$this->get_cache_status('statistics_cache')) {
335             $result = $this->update_statistics_cache_tables();
336             $this->update_cache_status('statistics_cache', $result ? 1 : 0);
337         }
338
339         $stat = [];
340
341         $this->dbh->beginTransaction();
342         foreach ([['race', 'races'], ['class', 'classes'], ['personality', 'personalities']] as $kind) {
343             $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");
344             $stat[$kind[0]] = $stmt->fetchAll(PDO::FETCH_ASSOC);
345         }
346         $this->dbh->commit();
347
348         return $stat;
349     }
350
351     public function table_exists($table_name)
352     {
353         $stmt = $this->dbh->prepare("SELECT count(*) AS table_exists FROM sqlite_master WHERE type='table' AND name=?");
354         $stmt->execute([$table_name]);
355         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
356
357         return intval($res[0]['table_exists']) === 1;
358     }
359
360
361     public function get_cache_status($cache_table_name)
362     {
363         if (!$this->table_exists('cache_status_table')) {
364             $this->create_cache_status_table();
365         }
366
367         $stmt = $this->dbh->prepare("SELECT cached FROM cache_status_table WHERE table_name=?");
368         $stmt->execute([$cache_table_name]);
369         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
370
371         return count($res) === 1 ? intval($res[0]['cached']) : 0;
372     }
373
374     public function update_cache_status($cache_table_name, $status)
375     {
376         if (!$this->table_exists('cache_status_table')) {
377             $this->create_cache_status_table();
378         }
379
380         $stmt = $this->dbh->prepare("INSERT OR REPLACE INTO cache_status_table VALUES(?, ?)");
381         $stmt->execute([$cache_table_name, $status]);
382
383         return $stmt->rowCount();
384     }
385
386     public function create_cache_status_table()
387     {
388         $this->dbh->exec(<<<EOM
389 CREATE TABLE cache_status_table
390   (table_name TEXT PRIMARY KEY,
391    cached BOOLEAN)
392 EOM
393         );
394     }
395
396     public function register_new_score($score_data)
397     {
398         $insert_stmt = $this->dbh->prepare(
399             <<<EOM
400 INSERT INTO scores
401  (version, score, name,
402   race_id, class_id, personality_id,
403   sex, level, depth, maxlv, maxdp,
404   au, turns, winner, killer)
405 SELECT
406  :version, :score, :name,
407  race_id, class_id, personality_id,
408  :sex, :level, :depth, :maxlv, :maxdp,
409  :au, :turns, :winner, :killer
410 FROM
411   races, classes, personalities
412 WHERE
413   race_name = :race AND
414   class_name = :class AND
415   personality_name = :personality
416 EOM
417         );
418  
419         $realm_insert_stmt = $this->dbh->prepare(
420             <<<EOM
421 INSERT INTO score_realms
422  (score_id, realm_id)
423 SELECT
424   ?, realm_id
425 FROM
426   realms
427 WHERE
428   realm_name = ?
429 EOM
430         );
431
432         try {
433             $this->dbh->beginTransaction();
434             if ($insert_stmt->execute($score_data['character_info']) === FALSE ||
435                 $insert_stmt->rowCount() !== 1) {
436                 $dbh->rollBack();
437                 return FALSE;
438             }
439
440             // NOTE: score_idはINTEGER PRIMARY KEYなのでROWIDを参照している
441             //       したがってlastInsertIdで追加されたスコアのscore_idを取得可能
442             $score_id = $this->dbh->lastInsertId();
443
444             foreach ($score_data['realm_info'] as $realm_name) {
445                 if ($realm_insert_stmt->execute([$score_id, $realm_name]) === FALSE ||
446                     $realm_insert_stmt->rowCount() !== 1) {
447                     $dbh->rollBack();
448                     return FALSE;
449                 }
450             }
451
452             // スコアが追加されたので死因ランキング・人気ランキングのキャッシュをクリア
453             $this->update_cache_status('killers_cache', 0);
454             $this->update_cache_status('statistics_cache', 0);
455             $this->dbh->commit();
456
457             return $score_id;
458         } catch (PDOException $e) {
459             $dbh->rollBack();
460         }
461
462         return FALSE;
463     }
464 }