Help us understand the problem. What is going on with this article?

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
    • これ以上件数が増えても耐えられるのか? → リアルタイム・ランキングを考える
    • もっと汎用化してソースコードが公開できるぐらいにしないと・・・

参考ページ

DandyMania
ただのしがないゲームプログラマです。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした