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("CREATE TABLE killers_cache (killer_name TEXT PRIMARY KEY, killer_count_total INTEGER, killer_count_normal INTEGER, killer_count_freeze INTEGER)");
 171
 172             // 通常状態、麻痺・彫像状態で別々に取得し、それぞれの回数と合計回数を計算しkillers_cacheテーブルに格納する
 173             $killers['normal'] = $this->dbh->query("SELECT killer, count(*) AS killer_count FROM scores WHERE killer NOT LIKE '麻痺状態で%' AND killer NOT LIKE '彫像状態で%' GROUP BY killer")->fetchAll(PDO::FETCH_ASSOC);
 174             $killers['freeze'] = $this->dbh->query("SELECT killer, count(*) AS killer_count FROM scores WHERE killer LIKE '麻痺状態で%' OR killer LIKE '彫像状態で%' GROUP BY killer")->fetchAll(PDO::FETCH_ASSOC);
 175
 176             $killer_count = [];
 177             foreach ($killers['normal'] as $row) {
 178                 $killer = self::killer_normalize($row['killer']);
 179                 $killer_count[$killer]['normal'] = intval($row['killer_count']);
 180             }
 181
 182             foreach ($killers['freeze'] as $row) {
 183                 $killer = self::killer_normalize($row['killer']);
 184                 $killer_count[$killer]['freeze'] = intval($row['killer_count']);
 185             }
 186
 187             foreach ($killer_count as $k => $v) {
 188                 if (!isset($killer_count[$k]['normal'])) {
 189                     $killer_count[$k]['normal'] = 0;
 190                 }
 191                 if (!isset($killer_count[$k]['freeze'])) {
 192                     $killer_count[$k]['freeze'] = 0;
 193                 }
 194             }
 195             foreach ($killer_count as $k => $v) {
 196                 $killer_count[$k]['total'] = $v['normal'] + $v['freeze'];
 197             }
 198
 199             $insert_stmt = $this->dbh->prepare("INSERT INTO killers_cache VALUES(?, ?, ?, ?)");
 200             foreach ($killer_count as $k => $v) {
 201                 $insert_stmt->execute([$k, $v['total'], $v['normal'], $v['freeze']]);
 202             }
 203
 204             $this->dbh->commit();
 205
 206             return TRUE;
 207         } catch (PDOException $e) {
 208             $this->dbh->rollBack();
 209
 210             return FALSE;
 211         }
 212     }
 213
 214     public function get_killers_table()
 215     {
 216         //$this->update_cache_status('killers_cache', 0);
 217         if (!$this->get_cache_status('killers_cache')) {
 218             $this->update_killers_cache_table();
 219             $this->update_cache_status('killers_cache', 1);
 220         }
 221
 222         $killers = $this->dbh->query("SELECT * FROM killers_cache ORDER BY killer_count_total DESC")->fetchAll(PDO::FETCH_ASSOC);
 223
 224         return $killers;
 225     }
 226
 227     private function update_statistics_tables() {
 228         $statistics_list = ['race', 'class', 'personality'];
 229
 230         try {
 231             foreach ($statistics_list as $stat) {
 232                 $table_name = $stat."_statistics";
 233                 $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
 234                 $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");
 235             }
 236
 237             return TRUE;
 238         } catch (PDOException $e) {
 239             return FALSE;
 240         }
 241     }
 242
 243     public function get_statistics_tables() {
 244         if (!$this->get_cache_status('statistics_cache')) {
 245             $this->update_statistics_tables();
 246             $this->update_cache_status('statistics_cache', 1);
 247         }
 248
 249         $stat = [];
 250
 251         $this->dbh->beginTransaction();
 252         $stmt = $this->dbh->query("SELECT race_name AS name, * from race_statistics NATURAL INNER JOIN races ORDER BY total_count DESC");
 253         $stat['race'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 254         $stmt = $this->dbh->query("SELECT class_name AS name, * from class_statistics NATURAL INNER JOIN classes ORDER BY total_count DESC");
 255         $stat['class'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 256         $stmt = $this->dbh->query("SELECT personality_name AS name, * from personality_statistics NATURAL INNER JOIN personalities ORDER BY total_count DESC");
 257         $stat['personality'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 258
 259         $this->dbh->commit();
 260
 261         return $stat;
 262     }
 263
 264     public function table_exists($table_name)
 265     {
 266         $stmt = $this->dbh->prepare("SELECT count(*) AS table_exists FROM sqlite_master WHERE type='table' AND name=?");
 267         $stmt->execute([$table_name]);
 268         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
 269
 270         return intval($res[0]['table_exists']) === 1;
 271     }
 272
 273
 274     public function get_cache_status($cache_table_name)
 275     {
 276         if (!$this->table_exists('cache_status_table')) {
 277             $this->create_cache_status_table();
 278         }
 279
 280         $stmt = $this->dbh->prepare("SELECT cached FROM cache_status_table WHERE table_name=?");
 281         $stmt->execute([$cache_table_name]);
 282         $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
 283
 284         return count($res) === 1 ? intval($res[0]['cached']) : 0;
 285     }
 286
 287     public function update_cache_status($cache_table_name, $status)
 288     {
 289         if (!$this->table_exists('cache_status_table')) {
 290             $this->create_cache_status_table();
 291         }
 292
 293         $stmt = $this->dbh->prepare("INSERT OR REPLACE INTO cache_status_table VALUES(?, ?)");
 294         $stmt->execute([$cache_table_name, $status]);
 295
 296         return $stmt->rowCount();
 297     }
 298
 299     public function create_cache_status_table()
 300     {
 301         $this->dbh->exec(<<<EOM
 302 CREATE TABLE cache_status_table
 303   (table_name TEXT PRIMARY KEY,
 304    cached BOOLEAN)
 305 EOM
 306         );
 307     }
 308
 309     private static function killer_normalize($killer)
 310     {
 311         if ($killer === "ripe") {
 312             return "引退";
 313         } else if ($killer === "Seppuku") {
 314             return "切腹";
 315         }
 316
 317         $n = str_replace("麻痺状態で", "", $killer);
 318         $n = str_replace("彫像状態で", "", $n);
 319
 320         return $n;
 321     }
 322
 323 }