OSDN Git Service

[add]スコア登録受付用コード
[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     private function update_statistics_tables() {
226         $statistics_list = ['race', 'class', 'personality'];
227
228         try {
229             foreach ($statistics_list as $stat) {
230                 $table_name = $stat."_statistics";
231                 $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
232                 $stmt = $this->dbh->query("CREATE TABLE ".$table_name." AS SELECT ".$stat."_id, count(sex=1 or NULL) AS male_count, count(sex=0 or NULL) AS female_count, count(*) AS total_count, count(winner=1 OR NULL) AS winner_count, avg(score) AS average_score, max(score) AS max_score from scores GROUP BY ".$stat."_id");
233             }
234
235             return TRUE;
236         } catch (PDOException $e) {
237             return FALSE;
238         }
239     }
240
241     public function get_statistics_tables() {
242         if (!$this->get_cache_status('statistics_cache')) {
243             $this->update_statistics_tables();
244             $this->update_cache_status('statistics_cache', 1);
245         }
246
247         $stat = [];
248
249         $this->dbh->beginTransaction();
250         foreach ([['race', 'races'], ['class', 'classes'], ['personality', 'personalities']] as $kind) {
251             $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 total_count DESC");
252             $stat[$kind[0]] = $stmt->fetchAll(PDO::FETCH_ASSOC);
253         }
254         $this->dbh->commit();
255
256         return $stat;
257     }
258
259     public function table_exists($table_name)
260     {
261         $stmt = $this->dbh->prepare("SELECT count(*) AS table_exists FROM sqlite_master WHERE type='table' AND name=?");
262         $stmt->execute([$table_name]);
263         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
264
265         return intval($res[0]['table_exists']) === 1;
266     }
267
268
269     public function get_cache_status($cache_table_name)
270     {
271         if (!$this->table_exists('cache_status_table')) {
272             $this->create_cache_status_table();
273         }
274
275         $stmt = $this->dbh->prepare("SELECT cached FROM cache_status_table WHERE table_name=?");
276         $stmt->execute([$cache_table_name]);
277         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
278
279         return count($res) === 1 ? intval($res[0]['cached']) : 0;
280     }
281
282     public function update_cache_status($cache_table_name, $status)
283     {
284         if (!$this->table_exists('cache_status_table')) {
285             $this->create_cache_status_table();
286         }
287
288         $stmt = $this->dbh->prepare("INSERT OR REPLACE INTO cache_status_table VALUES(?, ?)");
289         $stmt->execute([$cache_table_name, $status]);
290
291         return $stmt->rowCount();
292     }
293
294     public function create_cache_status_table()
295     {
296         $this->dbh->exec(<<<EOM
297 CREATE TABLE cache_status_table
298   (table_name TEXT PRIMARY KEY,
299    cached BOOLEAN)
300 EOM
301         );
302     }
303
304     public function register_new_score($score_data)
305     {
306         $insert_stmt = $this->dbh->prepare(
307             <<<EOM
308 INSERT INTO scores
309  (version, score, name,
310   race_id, class_id, personality_id,
311   sex, level, depth, maxlv, maxdp,
312   au, turns, winner, killer)
313 SELECT
314  :version, :score, :name,
315  race_id, class_id, personality_id,
316  :sex, :level, :depth, :maxlv, :maxdp,
317  :au, :turns, :winner, :killer
318 FROM
319   races, classes, personalities
320 WHERE
321   race_name = :race AND
322   class_name = :class AND
323   personality_name = :personality
324 EOM
325         );
326  
327         $realm_insert_stmt = $this->dbh->prepare(
328             <<<EOM
329 INSERT INTO score_realms
330  (score_id, realm_id)
331 SELECT
332   ?, realm_id
333 FROM
334   realms
335 WHERE
336   realm_name = ?
337 EOM
338         );
339
340         try {
341             $this->dbh->beginTransaction();
342             if ($insert_stmt->execute($score_data['character_info']) === FALSE ||
343                 $insert_stmt->rowCount() !== 1) {
344                 $dbh->rollBack();
345                 return FALSE;
346             }
347
348             // NOTE: score_idはINTEGER PRIMARY KEYなのでROWIDを参照している
349             //       したがってlastInsertIdで追加されたスコアのscore_idを取得可能
350             $score_id = $this->dbh->lastInsertId();
351
352             foreach ($score_data['realm_info'] as $realm_name) {
353                 if ($realm_insert_stmt->execute([$score_id, $realm_name]) === FALSE ||
354                     $realm_insert_stmt->rowCount() !== 1) {
355                     $dbh->rollBack();
356                     return FALSE;
357                 }
358             }
359
360             // スコアが追加されたので死因ランキング・人気ランキングのキャッシュをクリア
361             $this->update_cache_status('killers_cache', 0);
362             $this->update_cache_status('statistics_cache', 0);
363             $this->dbh->commit();
364
365             return $score_id;
366         } catch (PDOException $e) {
367             $dbh->rollBack();
368         }
369
370         return FALSE;
371     }
372 }