Edited at

PHP+SQLiteでさくさくリアルタイムランキングの実装

More than 5 years have passed since last update.


はじめに



  • EtherVaporReの時に実装したネットランキングはPHP+Jsonで管理してたが、

    アスタブリードになってから件数(と追加データ)が爆発的に増え、リアルタイム更新が難しくなってきたためSQLiteに変えてみた(・∀・)


  • MySQLとか管理が面倒なので、とりあえずSQLiteで実装して、必要であれば他のDBに載せ替えようと考えとりあえずPDO越し(・∀・)


  • 掲載されているPHPコードは、 一部処理を抜粋したものなのでそのままでは動きません。



開発環境など


  • サクラのレンタルサーバー

  • PHP 5.2 / SQLite(PDO使用)

  • ローカルのXAMPP環境で試した後、鯖で動作検証。負荷計測は、Benchmark_Timerを使用。


ネットランキングの概要

ランキングの登録・更新から取得の流れをざっくりと説明すると


  • (ゲーム側 C++) ランキングデータをJson形式にしてHttpSendRequestで鯖のアップロード用PHP宛に送信


  • (鯖 PHP)鯖のアップロード用PHPで受け取ったJsonを一旦フォルダに保存 & データベースに追加


  • (鯖 PHP)ゲームに送るためのランキングデータ生成


  • (ゲーム側 C++)生成されたランキングデータ(Json)をInternetReadFileで受け取って表示

という感じ。


具体的な実装


  • ゲーム側の実装を説明するとちょっと長くなるので割愛します。

    ※HttpSendRequestはWinInet直(Boost未使用)で、Json読み書きはPicoJson


  • 送られてきたスコア用のJsonデータを保存してるのは、バックアップとスコア以外の付随データ(テンションデータ)をDBから追い出すため



ゲームからアップロードされたスコア(Json)データを受け取る


  • HttpSendRequest にてゲームからPOSTされたスコアデータをスコアフォルダに移動する



upload.php


require('ranking.php');

// $dir : スコアを保存するフォルダ
function fileupload($dir){

$uploadfile = $dir . basename($_FILES['upfile']['name']);

if (move_uploaded_file($_FILES['upfile']['tmp_name'], $uploadfile)) {
// 移動後のファイルパス
return $uploadfile;
} else {
debug_log( "Error:\t" . "Possible file upload attack!");
header("HTTP/1.0 500 Possible file upload attack!");
die("score upload error");
}
return false;

}

$upload = fileupload("./score/");

// スコアをDBに追加
AddScoreDB($upload);



スコアDBを新規作成


  • テーブルは、ID・名前・ロケール・登録日のテーブルと、難易度毎スコアのテーブルに分けました。

  • 固有IDは被ることが無いのでそのまま主キー(primary key)にしました。固有IDの生成方法は秘密です。

  • データベース読み込み後、テーブルがなければ生成する。作りなおす場合は、.dbを削除するオプションを有効に。


ranking.php

//-----------------------------------

// スコアDB新規作成
//-----------------------------------
function SetupScoreDB($init=false)
{

global $g_level,$g_db,$g_scoreDataPath;

try {
// 作りなおす場合
if( $init == true ){
unlink("./score/ranking.db");
}

// データベース生成
if ( !($g_db = new PDO("sqlite:./score/ranking.db", null, null)) ) {
die("DB Connection Failed.");
}
// 警告をちゃんと出す
$g_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
// フェッチモード
$g_db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

//---------------------------------------
// いろいろOFFにしてパフォーマンスUP
$g_db->query("PRAGMA journal_mode=OFF");
$g_db->query("PRAGMA synchronous=OFF");
$g_db->query("PRAGMA count_changes=OFF");
$g_db->query("PRAGMA temp_store=OFF");
//---------------------------------------

//----------------
// IDテーブル作成
//----------------
$sql = "CREATE TABLE IF NOT EXISTS ID(" .
"ID text primary key,".
"Name text," .
"Locale text," .
"RegistDate integer," .
"Version integer," .
"Status text," .
"\"Check\" text".
")";

if (!$g_db->query($sql)) {
return false;
}

//----------------
// 難易度ごとにSCOREテーブル作成
//----------------
foreach ($g_level as $level) {
$sql = "CREATE TABLE IF NOT EXISTS ". $level . "SCORE(" .
"ID text primary key,".
"Date integer," .
"FrameRate float," .
"PlaySec integer," .
"Score integer," .
"Stage integer," .
"Stage02Score integer,Stage03Score integer,Stage04Score integer,Stage05Score integer,Stage06Score integer,Stage07Score integer" .
")";
if (!$g_db->query($sql)) {
return false;
}
}

} catch (PDOException $e){
//var_dump($e->getMessage());
// ここでエラーログに書き出したり
}
return true;
}



DBにスコア追加


  • アップロード用のPHPがコールされたタイミングで以下の処理をコールしています。

  • スコアの挿入は、REPLACE(重複キーの場合置き換え)を使いました。


ranking.php

//-----------------------------------

// スコアDBに追加
//-----------------------------------
function AddScoreDB($filename,$replace = true)
{
global $g_level,$ban_id,$g_db,$g_scoreDataPath ;
global $timer;

try {
$data = file_get_contents($filename, FILE_USE_INCLUDE_PATH);
$item = json_decode($data, true);

$path_parts = pathinfo($filename);
$item['ID'] = $path_parts['filename'];

$item['Status'] = "";

// BANチェック
$bankey = array_search( $item['ID'], $ban_id );
if( $bankey != NULL ){

// ここで、ファイルを消すもよし、ログを出すもよし。

return NULL;
}

$command = "INSERT";
if( $replace ){
$command = "REPLACE";
}

// IDテーブルに追加
$stmt = $g_db->prepare( $command . " INTO ID(ID, Name, Locale,RegistDate,Version,Status,\"Check\") ".
"VALUES(:ID, :Name, :Locale, :RegistDate, :Version, :Status, :Check)");

// 名前がないときは適当な名前
if( $item['Name'] == "" ){
$item['Name'] = "NoName";
}

$stmt->bindValue(':ID', $item['ID']);
$stmt->bindValue(':Name', $item['Name']);
$stmt->bindValue(':Locale', $item['Locale']);
$stmt->bindValue(':RegistDate', intval($item['RegistDate']));
$stmt->bindValue(':Version', intval($item['Version']));
$stmt->bindValue(':Status', $item['Status']);
$stmt->bindValue(':Check', $item['Check']);
$stmt->execute();

// スコアテーブル追加
foreach ($g_level as $level) {

$stmt = $g_db->prepare( $command . " INTO " . $level . "SCORE" . "(ID, Date, FrameRate,OnePlayF,PlaySec,Score,Stage,Stage02Score,Stage03Score,Stage04Score,Stage05Score,Stage06Score,Stage07Score) " .
"VALUES(:ID, :Date, :FrameRate, :OnePlayF, :PlaySec, :Score, :Stage, :Stage02Score, :Stage03Score, :Stage04Score, :Stage05Score, :Stage06Score, :Stage07Score)"
);

// 正常なデータの場合はそのまま追加
if( $item['Status'] !== "ban" && $item[$level. "PlaySec"] !== "0" && isset($item[$level . "PlaySec"]) ){

$stmt->bindValue(':ID', $item[ 'ID']);
$stmt->bindValue(':Date', intval($item[$level . 'Date']));
$stmt->bindValue(':FrameRate', floatval($item[$level . 'FrameRate']));
$stmt->bindValue(':OnePlayF', intval($item[$level . 'OnePlayF']));
$stmt->bindValue(':PlaySec', intval($item[$level . 'PlaySec']));
$stmt->bindValue(':Score', intval($item[$level . 'Score']));
$stmt->bindValue(':Stage', intval($item[$level . 'Stage']));

// Jsonからステージ毎のデータ取得
$stage_max = $item[$level . "Stage"];
for($i = 2;$i<=$stage_max;$i++){
$stageNo = sprintf("%02d",$i);
$stage_score_name = 'Stage' . $stageNo .'Score';
if( isset($item[$level . $stage_score_name]) ) $stmt->bindValue(':' . $stage_score_name, intval($item[$level . $stage_score_name]));
}

$stmt->execute();

}

}

return $item;

} catch (PDOException $e){
//var_dump($e->getMessage());
// ここでエラーログに書き出したり
}

return NULL;

}



難易度ごとにJsonファイル作成


  • 指定の難易度のテーブルからランキングデータをソートして取得

  • スコアとIDテーブルの情報をマージしてJsonに書き書き

  • ランキングの部分取得処理(1位から100位まで取得とか)も作る予定ですが、実機処理の変更が必要なのでまだ手をつけてません。。。


ranking.php

//-----------------------------------

// DBからランキング用Jsonファイル生成
//-----------------------------------
function CreateJson($level)
{
global $g_db,$g_levelsufix,$g_enable_tension_data_rank;
global $timer;

if($timer )$timer->setMarker("Select and Sort");

$select_score = $g_db->query("SELECT * FROM " . $level . "SCORE " . "ORDER BY Score DESC" );
$stmt = $g_db->prepare("SELECT * FROM ID where ID = ?");

// スコアをソート・IDテーブルとマージ
$ranking = array();
$index = 0;
foreach ($select_score as $row) {
$UniqueId = $row['ID'];
if ($stmt->execute(array($UniqueId))) {

// スコアデータの固有IDは消しとく
unset($row['ID']);

// スコアが入ってないやつ消しとく
for($i = 2;$i<=7 ;$i++){
$stageNo = sprintf("%02d",$i);
$stage_score_name = "Stage" . $stageNo . "Score";

if( is_null($row[$stage_score_name]) ){
unset($row[$stage_score_name] );
}
}

// テンションデータ取得
$stage_score = array();
if( $index < $g_enable_tension_data_rank ){
$stage_score = GetStageTension($UniqueId,$level);
}

//時間変換周り
$row['Date'] = date('Y-m-d G:i:s',$row['Date']);

// ステージスコア
$param = array_merge($row,$stage_score);
// prefixをつける
$combine = array();
foreach($param as $key => $value ){
$combine[] = $level . $key;
}
$param = array_combine($combine, $param);

// IDテーブルをマージ
$ids = $stmt->fetch();

// 登録日時間変換
$ids['RegistDate'] = date('Y-m-d G:i:s',$ids['RegistDate']);

$param = array_merge($ids,$param);

// いらんデータ消す
unset($param['Status']);

$ranking[] = $param;

$index++;

}
}

$timer->setMarker("CreateJson");

// json生成
$fp = fopen("./score/ranking" . $g_levelsufix[$level] . ".json",'w+');
fputs($fp,json_encode($ranking));
fclose($fp);

}


あとは、実機側で上記のJsonファイルにアクセスして粛々と表示していけば完成です。

ほら簡単\(^o^)/


処理時間計測コード


ranking.php

    require_once("Benchmark/Timer.php");

$timer = new Benchmark_Timer;
$timer->start();
$timer->setMarker("開始");

$timer->setMarker("なんかの処理");
// なんかの処理

$fp = fopen("profile.log",'w+');
$profile = print_r($timer->getProfiling(),true);
//var_dump($profile);
fputs($fp,$profile);
fclose($fp);



まとめ


  • スコアをJsonで管理してた頃は、1万件程度のスコアを更新するのに15分とかかかってました(タイムアウトして更新出来てなかったorz)が数秒で終わるようになりました\(^o^)/

  • PHPコードがスッキリしたかも?

  • 今後の課題としては、


    • ランキングデータ取得用のJsonデータが膨れてきた(2MB)のでランキングの部分取得の実装しないと・・・でも実機側の実装も変えないといけないので面倒orz

    • これ以上件数が増えても耐えられるのか? → リアルタイム・ランキングを考える

    • もっと汎用化してソースコードが公開できるぐらいにしないと・・・




参考ページ