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