4 public function __construct() {
5 $this->dbh = new PDO('sqlite:db/score.db');
6 $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
9 public function get_db_handle() {
13 private function update_killers_cache_table()
16 $this->dbh->beginTransaction();
18 $this->dbh->exec("DROP TABLE IF EXISTS killers_cache");
19 $this->dbh->exec("CREATE TABLE killers_cache (killer_name TEXT PRIMARY KEY, killer_count_total INTEGER, killer_count_normal INTEGER, killer_count_freeze INTEGER)");
21 // 通常状態、麻痺・彫像状態で別々に取得し、それぞれの回数と合計回数を計算しkillers_cacheテーブルに格納する
22 $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);
23 $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);
26 foreach ($killers['normal'] as $row) {
27 $killer = self::killer_normalize($row['killer']);
28 $killer_count[$killer]['normal'] = intval($row['killer_count']);
31 foreach ($killers['freeze'] as $row) {
32 $killer = self::killer_normalize($row['killer']);
33 $killer_count[$killer]['freeze'] = intval($row['killer_count']);
36 foreach ($killer_count as $k => $v) {
37 if (!isset($killer_count[$k]['normal'])) {
38 $killer_count[$k]['normal'] = 0;
40 if (!isset($killer_count[$k]['freeze'])) {
41 $killer_count[$k]['freeze'] = 0;
44 foreach ($killer_count as $k => $v) {
45 $killer_count[$k]['total'] = $v['normal'] + $v['freeze'];
48 $insert_stmt = $this->dbh->prepare("INSERT INTO killers_cache VALUES(?, ?, ?, ?)");
49 foreach ($killer_count as $k => $v) {
50 $insert_stmt->execute([$k, $v['total'], $v['normal'], $v['freeze']]);
56 } catch (PDOException $e) {
57 $this->dbh->rollBack();
63 public function get_killers_table()
65 //$this->update_cache_status('killers_cache', 0);
66 if (!$this->get_cache_status('killers_cache')) {
67 $this->update_killers_cache_table();
68 $this->update_cache_status('killers_cache', 1);
71 $killers = $this->dbh->query("SELECT * FROM killers_cache ORDER BY killer_count_total DESC")->fetchAll(PDO::FETCH_ASSOC);
76 private function update_statistics_tables() {
77 $statistics_list = ['race', 'class', 'personality'];
80 foreach ($statistics_list as $stat) {
81 $table_name = $stat."_statistics";
82 $this->dbh->exec("DROP TABLE IF EXISTS ".$table_name);
83 $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");
87 } catch (PDOException $e) {
92 public function get_statistics_tables() {
93 if (!$this->get_cache_status('statistics_cache')) {
94 $this->update_statistics_tables();
95 $this->update_cache_status('statistics_cache', 1);
100 $this->dbh->beginTransaction();
101 $stmt = $this->dbh->query("SELECT race_name AS name, * from race_statistics NATURAL INNER JOIN races ORDER BY total_count DESC");
102 $stat['race'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
103 $stmt = $this->dbh->query("SELECT class_name AS name, * from class_statistics NATURAL INNER JOIN classes ORDER BY total_count DESC");
104 $stat['class'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
105 $stmt = $this->dbh->query("SELECT personality_name AS name, * from personality_statistics NATURAL INNER JOIN personalities ORDER BY total_count DESC");
106 $stat['personality'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
108 $this->dbh->commit();
113 public function table_exists($table_name)
115 $stmt = $this->dbh->prepare("SELECT count(*) AS table_exists FROM sqlite_master WHERE type='table' AND name=?");
116 $stmt->execute([$table_name]);
117 $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
119 return intval($res[0]['table_exists']) === 1;
123 public function get_cache_status($cache_table_name)
125 if (!$this->table_exists('cache_status_table')) {
126 $this->create_cache_status_table();
129 $stmt = $this->dbh->prepare("SELECT cached FROM cache_status_table WHERE table_name=?");
130 $stmt->execute([$cache_table_name]);
131 $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
133 return count($res) === 1 ? intval($res[0]['cached']) : 0;
136 public function update_cache_status($cache_table_name, $status)
138 if (!$this->table_exists('cache_status_table')) {
139 $this->create_cache_status_table();
142 $stmt = $this->dbh->prepare("INSERT OR REPLACE INTO cache_status_table VALUES(?, ?)");
143 $stmt->execute([$cache_table_name, $status]);
145 return $stmt->rowCount();
148 public function create_cache_status_table()
150 $this->dbh->exec(<<<EOM
151 CREATE TABLE cache_status_table
152 (table_name TEXT PRIMARY KEY,
158 private static function killer_normalize($killer)
160 if ($killer === "ripe") {
162 } else if ($killer === "Seppuku") {
166 $n = str_replace("麻痺状態で", "", $killer);
167 $n = str_replace("彫像状態で", "", $n);