OSDN Git Service

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