OSDN Git Service

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