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