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
 226     /**
 227      * 統計情報のキャッシュテーブルを更新する
 228      *
 229      * 種族・職業・性格について各種統計情報を取得しキャッシュテーブルに保存する
 230      * 通常の統計情報の取得はこのキャッシュテーブルから行う
 231      *
 232      * @return boolean 生成に成功したらTRUE、失敗したらFALSE
 233      */
 234     private function update_statistics_cache_tables() {
 235         $statistics_list = ['race', 'class', 'personality'];
 236
 237         try {
 238             foreach ($statistics_list as $stat) {
 239                 $table_name = $stat."_statistics";
 240                 $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
 241                 $stmt = $this->dbh->query(
 242                     <<<EOM
 243 CREATE TABLE $table_name AS
 244   SELECT
 245     {$stat}_id,
 246     count(sex=1 or NULL) AS male_count,
 247     count(sex=0 or NULL) AS female_count,
 248     count(*) AS total_count,
 249     count(winner=1 OR NULL) AS winner_count,
 250     avg(score) AS average_score,
 251     max(score) AS max_score
 252   FROM scores
 253 GROUP BY ${stat}_id
 254 EOM
 255                 );
 256             }
 257
 258             return TRUE;
 259         } catch (PDOException $e) {
 260             return FALSE;
 261         }
 262     }
 263
 264
 265     /**
 266      * 統計情報を取得する
 267      *
 268      * @param integer $sort_key_column 表示順序のキーとするカラムの名称
 269      *
 270      * @return array 統計情報
 271      */
 272     public function get_statistics_tables($sort_key_column) {
 273         if (!$this->get_cache_status('statistics_cache')) {
 274             $result = $this->update_statistics_cache_tables();
 275             $this->update_cache_status('statistics_cache', $result ? 1 : 0);
 276         }
 277
 278         $stat = [];
 279
 280         $this->dbh->beginTransaction();
 281         foreach ([['race', 'races'], ['class', 'classes'], ['personality', 'personalities']] as $kind) {
 282             $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");
 283             $stat[$kind[0]] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 284         }
 285         $this->dbh->commit();
 286
 287         return $stat;
 288     }
 289
 290     public function table_exists($table_name)
 291     {
 292         $stmt = $this->dbh->prepare("SELECT count(*) AS table_exists FROM sqlite_master WHERE type='table' AND name=?");
 293         $stmt->execute([$table_name]);
 294         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
 295
 296         return intval($res[0]['table_exists']) === 1;
 297     }
 298
 299
 300     public function get_cache_status($cache_table_name)
 301     {
 302         if (!$this->table_exists('cache_status_table')) {
 303             $this->create_cache_status_table();
 304         }
 305
 306         $stmt = $this->dbh->prepare("SELECT cached FROM cache_status_table WHERE table_name=?");
 307         $stmt->execute([$cache_table_name]);
 308         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
 309
 310         return count($res) === 1 ? intval($res[0]['cached']) : 0;
 311     }
 312
 313     public function update_cache_status($cache_table_name, $status)
 314     {
 315         if (!$this->table_exists('cache_status_table')) {
 316             $this->create_cache_status_table();
 317         }
 318
 319         $stmt = $this->dbh->prepare("INSERT OR REPLACE INTO cache_status_table VALUES(?, ?)");
 320         $stmt->execute([$cache_table_name, $status]);
 321
 322         return $stmt->rowCount();
 323     }
 324
 325     public function create_cache_status_table()
 326     {
 327         $this->dbh->exec(<<<EOM
 328 CREATE TABLE cache_status_table
 329   (table_name TEXT PRIMARY KEY,
 330    cached BOOLEAN)
 331 EOM
 332         );
 333     }
 334
 335     public function register_new_score($score_data)
 336     {
 337         $insert_stmt = $this->dbh->prepare(
 338             <<<EOM
 339 INSERT INTO scores
 340  (version, score, name,
 341   race_id, class_id, personality_id,
 342   sex, level, depth, maxlv, maxdp,
 343   au, turns, winner, killer)
 344 SELECT
 345  :version, :score, :name,
 346  race_id, class_id, personality_id,
 347  :sex, :level, :depth, :maxlv, :maxdp,
 348  :au, :turns, :winner, :killer
 349 FROM
 350   races, classes, personalities
 351 WHERE
 352   race_name = :race AND
 353   class_name = :class AND
 354   personality_name = :personality
 355 EOM
 356         );
 357
 358         $realm_insert_stmt = $this->dbh->prepare(
 359             <<<EOM
 360 INSERT INTO score_realms
 361  (score_id, realm_id)
 362 SELECT
 363   ?, realm_id
 364 FROM
 365   realms
 366 WHERE
 367   realm_name = ?
 368 EOM
 369         );
 370
 371         try {
 372             $this->dbh->beginTransaction();
 373             if ($insert_stmt->execute($score_data['character_info']) === FALSE ||
 374                 $insert_stmt->rowCount() !== 1) {
 375                 $dbh->rollBack();
 376                 return FALSE;
 377             }
 378
 379             // NOTE: score_idはINTEGER PRIMARY KEYなのでROWIDを参照している
 380             //       したがってlastInsertIdで追加されたスコアのscore_idを取得可能
 381             $score_id = $this->dbh->lastInsertId();
 382
 383             foreach ($score_data['realm_info'] as $realm_name) {
 384                 if ($realm_insert_stmt->execute([$score_id, $realm_name]) === FALSE ||
 385                     $realm_insert_stmt->rowCount() !== 1) {
 386                     $dbh->rollBack();
 387                     return FALSE;
 388                 }
 389             }
 390
 391             // スコアが追加されたので死因ランキング・人気ランキングのキャッシュをクリア
 392             $this->update_cache_status('killers_cache', 0);
 393             $this->update_cache_status('statistics_cache', 0);
 394             $this->dbh->commit();
 395
 396             return $score_id;
 397         } catch (PDOException $e) {
 398             $dbh->rollBack();
 399         }
 400
 401         return FALSE;
 402     }
 403 }