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