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     private function update_statistics_tables() {
 226         $statistics_list = ['race', 'class', 'personality'];
 227
 228         try {
 229             foreach ($statistics_list as $stat) {
 230                 $table_name = $stat."_statistics";
 231                 $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
 232                 $stmt = $this->dbh->query("CREATE TABLE ".$table_name." AS SELECT ".$stat."_id, count(sex=1 or NULL) AS male_count, count(sex=0 or NULL) AS female_count, count(*) AS total_count, count(winner=1 OR NULL) AS winner_count, avg(score) AS average_score, max(score) AS max_score from scores GROUP BY ".$stat."_id");
 233             }
 234
 235             return TRUE;
 236         } catch (PDOException $e) {
 237             return FALSE;
 238         }
 239     }
 240
 241     public function get_statistics_tables() {
 242         if (!$this->get_cache_status('statistics_cache')) {
 243             $this->update_statistics_tables();
 244             $this->update_cache_status('statistics_cache', 1);
 245         }
 246
 247         $stat = [];
 248
 249         $this->dbh->beginTransaction();
 250         $stmt = $this->dbh->query("SELECT race_name AS name, * from race_statistics NATURAL INNER JOIN races ORDER BY total_count DESC");
 251         $stat['race'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 252         $stmt = $this->dbh->query("SELECT class_name AS name, * from class_statistics NATURAL INNER JOIN classes ORDER BY total_count DESC");
 253         $stat['class'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 254         $stmt = $this->dbh->query("SELECT personality_name AS name, * from personality_statistics NATURAL INNER JOIN personalities ORDER BY total_count DESC");
 255         $stat['personality'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 256
 257         $this->dbh->commit();
 258
 259         return $stat;
 260     }
 261
 262     public function table_exists($table_name)
 263     {
 264         $stmt = $this->dbh->prepare("SELECT count(*) AS table_exists FROM sqlite_master WHERE type='table' AND name=?");
 265         $stmt->execute([$table_name]);
 266         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
 267
 268         return intval($res[0]['table_exists']) === 1;
 269     }
 270
 271
 272     public function get_cache_status($cache_table_name)
 273     {
 274         if (!$this->table_exists('cache_status_table')) {
 275             $this->create_cache_status_table();
 276         }
 277
 278         $stmt = $this->dbh->prepare("SELECT cached FROM cache_status_table WHERE table_name=?");
 279         $stmt->execute([$cache_table_name]);
 280         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
 281
 282         return count($res) === 1 ? intval($res[0]['cached']) : 0;
 283     }
 284
 285     public function update_cache_status($cache_table_name, $status)
 286     {
 287         if (!$this->table_exists('cache_status_table')) {
 288             $this->create_cache_status_table();
 289         }
 290
 291         $stmt = $this->dbh->prepare("INSERT OR REPLACE INTO cache_status_table VALUES(?, ?)");
 292         $stmt->execute([$cache_table_name, $status]);
 293
 294         return $stmt->rowCount();
 295     }
 296
 297     public function create_cache_status_table()
 298     {
 299         $this->dbh->exec(<<<EOM
 300 CREATE TABLE cache_status_table
 301   (table_name TEXT PRIMARY KEY,
 302    cached BOOLEAN)
 303 EOM
 304         );
 305     }
 306
 307     private static function killer_normalize($killer)
 308     {
 309         if ($killer === "ripe") {
 310             return "引退";
 311         } else if ($killer === "Seppuku") {
 312             return "切腹";
 313         }
 314
 315         $n = str_replace("麻痺状態で", "", $killer);
 316         $n = str_replace("彫像状態で", "", $n);
 317
 318         return $n;
 319     }
 320
 321 }