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