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

#はじめに
* [EtherVaporRe](http://edelweiss.skr.jp/works/ethervapor/)の時に実装したネットランキングはPHP+Jsonで管理してたが、<BR>
[アスタブリード](http://edelweiss.skr.jp/works/astebreed/index.html)になってから件数(と追加データ)が爆発的に増え、リアルタイム更新が難しくなってきたためSQLiteに変えてみた(・∀・) <BR>
* MySQLとか管理が面倒なので、とりあえずSQLiteで実装して、必要であれば他のDBに載せ替えようと考えとりあえずPDO越し(・∀・)

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

# 開発環境など
 * サクラのレンタルサーバー
 * PHP 5.2 / SQLite(PDO使用)
 * ローカルのXAMPP環境で試した後、鯖で動作検証。負荷計測は、Benchmark_Timerを使用。
 
#ネットランキングの概要
ランキングの登録・更新から取得の流れをざっくりと説明すると

* (ゲーム側 C++) ランキングデータをJson形式にしてHttpSendRequestで鯖のアップロード用PHP宛に送信<BR>
                             ↓
* (鯖 PHP)鯖のアップロード用PHPで受け取ったJsonを一旦フォルダに保存 & データベースに追加<BR>
                             ↓
* (鯖 PHP)ゲームに送るためのランキングデータ生成<BR>
                             ↓
* (ゲーム側 C++)生成されたランキングデータ(Json)をInternetReadFileで受け取って表示

という感じ。

#具体的な実装

* ゲーム側の実装を説明するとちょっと長くなるので割愛します。
  ※HttpSendRequestはWinInet直(Boost未使用)で、Json読み書きは[PicoJson](http://developer.cybozu.co.jp/archives/kazuho/2009/07/c-json-picojson.html)

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

## ゲームからアップロードされたスコア(Json)データを受け取る
 * HttpSendRequest にてゲームからPOSTされたスコアデータをスコアフォルダに移動する<BR>

```php: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を削除するオプションを有効に。

```php: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(重複キーの場合置き換え)を使いました。

```php: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位まで取得とか)も作る予定ですが、実機処理の変更が必要なのでまだ手をつけてません。。。

```php: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^)／

#処理時間計測コード

```php: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
  * これ以上件数が増えても耐えられるのか？ → [リアルタイム・ランキングを考える](http://labs.gree.jp/blog/2010/07/456/)
  * もっと汎用化してソースコードが公開できるぐらいにしないと・・・

# 参考ページ
 * [しゃいん☆のブログ PDO関数でSQLiteを使ってみた](http://shain.blog.conextivo.com/2007/10/phppdosqlite1.html)
 * [[PHP] PDO を利用し、SQLite を使う | unlinked log](http://log.noiretaya.com/38)
 * [PDOでサクサクDB開発 ](http://codezine.jp/article/detail/433)
 * [Benchmark_Timer について](http://pear.php.net/manual/ja/package.benchmarking.benchmark.timer.php)