OSDN Git Service

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