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 }