Skip to content

Instantly share code, notes, and snippets.

@KEINOS KEINOS/class.cData.php
Last active Oct 23, 2016

Embed
What would you like to do?
SQLite3を使ったデータ保存クラス(SQLite2互換)
<?php
/**
* *********************************************************
* KEINOS Data engine class extended SQLite3 version
* *********************************************************
* 最新版
* https://gist.githubusercontent.com/KEINOS/05f43c81edff366b6e9b9e627a36eb6a/raw/class.cData.php
*/
if( ! class_exists( 'cData' )){
class cData{
const SQLITEVER = 3;
const IS_ENCRYPT = TRUE; /* SQLite3のみ有効 */
var $rLink;
var $db_name;
var $db_path;
var $pairkey_path;
var $bIsSQLite3;
var $aPairKey;
/* =================================
デストラクター
==================================== */
function __destruct(){
//DBのクローズ
if( is_resource( $this->rLink ) ){
If( self::SQLITEVER == 3 ){
$this->rLink->close( );
} else {
sqlite_close( $this->rLink );
}
}
$this->rLink = null;
}
/* =================================
コンストラクター
==================================== */
function __construct( $sDBName = "", $sDBSavePath = "" ){
$this->cData( $sDBName, $sDBSavePath );
}
function cData( $sDBName = "", $sDBSavePath = "" ){
/* ------------------------------------
DBの初期設定
--------------------------------------- */
//SQLite3モードで稼働かのフラグ
$this->bIsSQLite3 = ( self::SQLITEVER == 3 ) ? TRUE : FALSE;
$bIsSQLite3 = $this->bIsSQLite3;
//保存先ディレクトリの設定
if( ! Empty( $sDBSavePath ) && ! dir_exists( $sDBSavePath ) ){
die( "データの保存先が無効です。" );
}
switch( TRUE ){
case( dir_exists( $sDBSavePath ) ):
$sPath_temp = realpath( $sDBSavePath ) . DIRECTORY_SEPARATOR;
break;
case( dir_exists( "./_data/" ) ):
$sPath_temp = realpath( "./_data/" ) . DIRECTORY_SEPARATOR;
break;
case( dir_exists( "./_lib/_data/" ) ):
$sPath_temp = realpath( "./_lib/_data/" ) . DIRECTORY_SEPARATOR;
break;
default:
if( ! defined( 'KEINOS_DATA_DIR' ) ) {
die( 'データの保存先ディレクトリ(KEINOS_DATA_DIR)が定義されていません。' );
}
$sPath_temp = realpath( KEINOS_DATA_DIR ) . DIRECTORY_SEPARATOR;
break;
}
//DBのファイル名
$sDBName = Empty( $sDBName ) ? "database_default" : "database_" . rawurlencode( $sDBName );
$this->db_name = $sDBName;
//DBファイルの拡張子
$sDBExt = ( $bIsSQLite3 ) ? ".sqlite3" : ".db";
$sPairKeyExt = ".pairkey";
//DBファイルの保存先パス
$this->db_path = $sPath_temp . $sDBName . $sDBExt;
$this->pairkey_path = $sPath_temp . $sDBName . $sPairKeyExt;
//新規DBファイルであるかのフラグ
$bIsNewDB = ! file_exists( $this->db_path ); //新規DBの場合テーブルを作成する必要があるため
/* ------------------------------------
DBのオープン
--------------------------------------- */
/* SQLite3 */
If( $bIsSQLite3 ){
try{
$this->rLink = new SQLite3( $this->db_path ); /* 存在しなければDBの新規作成 */
} catch( Exception $e ) {
die( "データベースの接続失敗です。<hr><pre style='text-align:left;font-family:monospace; white-space: -moz-pre-wrap;white-space: -pre-wrap;white-space: -o-pre-wrap;white-space: pre-wrap;word-wrap: break-word;'>{$e->getTraceAsString}</pre>" );
}
/* SQLite2 */
} else {
$this->rLink = sqlite_open( $this->db_path, 0666, $sqliteerror );
if ( ! $this->rLink ) {
die( "データベースの接続失敗です。<hr><pre style='text-align:left;font-family:monospace; white-space: -moz-pre-wrap;white-space: -pre-wrap;white-space: -o-pre-wrap;white-space: pre-wrap;word-wrap: break-word;'>{$sqliteerror}</pre>" );
}
}
/* ------------------------------------
テーブルの新規作成
テーブル名: keinos_db -> データ保存用
テーブル名: ssl_pairkey -> ペアキー保存用
--------------------------------------- */
if( $bIsNewDB ){
$sSQL = "CREATE TABLE keinos_db(id TEXT PRIMARY KEY,value TEXT,time_create int,time_update int);";
/* SQLite3 */
if( $bIsSQLite3 ){
try{
/* データ保存用テーブルの作成 */
$this->rLink->exec( $sSQL );
/* データ保存用DBを閉じる */
$this->rLink->close();
$this->rLink = null;
$rPairKey = new SQLite3( $this->pairkey_path );
/* ------------------------------------
SSL用テーブル作成と情報セット
--------------------------------------- */
$iTime = time();
$aPairKey = $this->ssl_create_pairkey(); /* SSL用のペアキーの作成 */
$sSQL = ""; /* SQL文初期化 */
/* SSL情報保持用のテーブル作成 */
$sSQL .= "CREATE TABLE ssl_pairkey(id TEXT PRIMARY KEY,value TEXT,time_create int,time_update int);";
/* PRIVATE_KEY の保存 */
$sSQL .= "INSERT INTO ssl_pairkey(id,value,time_create,time_update) VALUES('PRIVATE_KEY','{$aPairKey[ "PRIVATE_KEY" ]}',{$iTime},{$iTime});";
/* PUBLIC_KEY の保存 */
$sSQL .= "INSERT INTO ssl_pairkey(id,value,time_create,time_update) VALUES('PUBLIC_KEY','{$aPairKey[ "PUBLIC_KEY" ]}',{$iTime},{$iTime});";
/* PASSWORD の保存 */
$sSQL .= "INSERT INTO ssl_pairkey(id,value,time_create,time_update) VALUES('PASSWORD','{$aPairKey[ "PASSWORD" ]}',{$iTime},{$iTime});";
/* SQL実行 */
$rPairKey->exec( $sSQL );
/* ペアキー用DBを閉じる */
$rPairKey->close();
$rPairKey = null;
$this->aPairKey = $this->ssl_get_pairkey(); /* ペアキーをクラス変数に代入 */
} catch( Exception $e ) {
die( "クエリーエラー:データベースのテーブル作成失敗です。<hr><pre style='text-align:left;font-family:monospace; white-space: -moz-pre-wrap;white-space: -pre-wrap;white-space: -o-pre-wrap;white-space: pre-wrap;word-wrap: break-word;'>{$e->getTraceAsString}</pre>" );
}
/* SQLite2 */
} else {
$bResult = sqlite_query( $this->rLink, $sSQL, SQLITE_ASSOC, $sqliteerror );
if ( ! $bResult ) {
die( "クエリーエラー:データベースのテーブル作成失敗です。<hr><pre style='text-align:left;font-family:monospace; white-space: -moz-pre-wrap;white-space: -pre-wrap;white-space: -o-pre-wrap;white-space: pre-wrap;word-wrap: break-word;'>{$sqliteerror}</pre>") ;
}
sqlite_query( $this->rLink, "begin;" ); //トランザクションの明示
}
/* not a New DB */
} else if( $bIsSQLite3 ){
try{
/* データ保存用DBを閉じる */
$this->rLink->close();
$this->rLink = null;
$this->aPairKey = $this->ssl_get_pairkey();
} catch( Exception $e ) {
die( "SSL情報の読み込み失敗です。<hr><pre style='text-align:left;font-family:monospace; white-space: -moz-pre-wrap;white-space: -pre-wrap;white-space: -o-pre-wrap;white-space: pre-wrap;word-wrap: break-word;'>{$e->getTraceAsString}</pre>" );
}
}
}
/* ==================================
クラス内暗号化・符号化
===================================== */
/**
* 新しい秘密鍵(および公開鍵)のペアを作成します
*/
function ssl_create_pairkey( $sPassword = "" ){
$sMagicword = defined( "KEINOS_MAGIC" ) ? KEINOS_MAGIC : "keinos5963";
$sPassword = Empty( $sPassword ) ? MD5( $this->db_name . $sMagicword ) : MD5( $sPassword . $sMagicword );
$rPrivateKey = openssl_pkey_new();
openssl_pkey_export( $rPrivateKey, $sPrivateKey, $sPassword );
$aPublicKey = openssl_pkey_get_details( $rPrivateKey );
$aResponce = array(
'PRIVATE_KEY' => $sPrivateKey,
'PUBLIC_KEY' => $aPublicKey[ 'key' ],
'PASSWORD' => $sPassword,
);
return $aResponce;
}
function ssl_get_pairkey(){
$bIsSQLite3 = $this->bIsSQLite3;
/* SQLite3 */
if( $bIsSQLite3 ){
$rPairKey = new SQLite3( $this->pairkey_path );
$sKey = "PRIVATE_KEY";
$sSQL = "SELECT * FROM ssl_pairkey WHERE id='{$sKey}'; ";
$rResult = $rPairKey->query( $sSQL );
if( $rResult ){
$row = $rResult->fetchArray( SQLITE3_ASSOC );
$aPairkey['PRIVATE_KEY'] = $row['value'];
} else {
$aPairkey['PRIVATE_KEY'] = "ERROR";
}
$sKey = "PUBLIC_KEY";
$sSQL = "SELECT * FROM ssl_pairkey WHERE id='{$sKey}'; ";
$rResult = $rPairKey->query( $sSQL );
if( $rResult ){
$row = $rResult->fetchArray( SQLITE3_ASSOC );
$aPairkey['PUBLIC_KEY'] = $row['value'];
} else {
$aPairkey['PUBLIC_KEY'] = "ERROR";
}
$sKey = "PASSWORD";
$sSQL = "SELECT * FROM ssl_pairkey WHERE id='{$sKey}'; ";
$rResult = $rPairKey->query( $sSQL );
if( $rResult ){
$row = $rResult->fetchArray( SQLITE3_ASSOC );
$aPairkey['PASSWORD'] = $row['value'];
} else {
$aPairkey['PASSWORD'] = "ERROR";
}
$mResult = $aPairkey;
/* ペアキー用DBのクローズ */
$rPairKey->close();
$rPairKey = null;
} else {
$mResult = "ERROR: Only using SQLite3";
}
return $mResult;
}
/**
* データを暗号化します
*/
function ssl_encrypt( $source, $type, $key ){
//Assumes 1024 bit key and encrypts in chunks.
$maxlength = 117;
$output = '';
while( $source ){
$input = substr( $source, 0,$maxlength );
$source = substr( $source, $maxlength );
if( $type == 'private' ){
$ok = openssl_private_encrypt( $input, $encrypted, $key);
} else {
$ok = openssl_public_encrypt( $input, $encrypted, $key );
}
$output .= $encrypted;
}
return $output;
}
/**
* データを復号します
*/
function ssl_decrypt( $source, $type, $key ){
// The raw PHP decryption functions appear to work
// on 128 Byte chunks. So this decrypts long text
// encrypted with ssl_encrypt().
$maxlength = 128;
$output = '';
while( $source ){
$input = substr( $source, 0, $maxlength );
$source = substr( $source, $maxlength );
if( $type == 'private' ){
$ok = openssl_private_decrypt( $input, $out, $key );
} else {
$ok = openssl_public_decrypt( $input, $out, $key );
}
$output .= $out;
}
return $output;
}
/* ==================================
保存データのシリアル化(文字化け対策)
※必要なら暗号化
===================================== */
function serialize( $mValue ){
$mValue = serialize( $mValue );
if( ( self::IS_ENCRYPT ) && ( self::SQLITEVER == 3 ) ){
/* encrypt */
$sPublicKey = $this->aPairKey['PUBLIC_KEY'];
$mValue = $this->ssl_encrypt( $mValue, 'public', $sPublicKey );
/* bin to ASCII */
$mValue = base64_encode( $mValue );
}
return $mValue;
}
function unserialize( $mValue ){
if( ( self::IS_ENCRYPT ) && ( self::SQLITEVER == 3 ) ){
/* ASCII to bin */
$mValue = base64_decode( $mValue );
/* decrypt */
$sPrivateKey = $this->aPairKey['PRIVATE_KEY'];
$sPassword = $this->aPairKey['PASSWORD'];
$rPrivateKey = openssl_get_privatekey( $sPrivateKey, $sPassword );
$mValue = $this->ssl_decrypt( $mValue, 'private', $rPrivateKey );
}
$mValue = unserialize( $mValue);
return $mValue;
}
/* =================================
データ保存処理
================================= */
function save( $sDataID, $mData ){
$bIsSQLite3 = $this->bIsSQLite3; /* SQLiteバージョン取得 */
/* DBのクローズ(念のため) */
if( is_resource( $this->rLink ) && $bIsSQLite3 ){
$this->rLink->close();
$this->rLink = null;
}
$sKey = rawurlencode( $sDataID );
$sData = $this->serialize( $mData );
$sSQL = "SELECT * FROM keinos_db WHERE id='{$sKey}'; ";
$iTime = time();
$bResult = FALSE;
/* SQLite3 */
If( $bIsSQLite3 ){
try {
$this->rLink = new SQLite3( $this->db_path );
$sData = $this->rLink->escapeString( $sData ); /* データをSQL文用にエスケープ */
$sSQL = "SELECT * FROM keinos_db WHERE id='{$sKey}'; "; /* 既存のデータがあるかのチェック */
$rResult = $this->rLink->query( $sSQL );
if( $rResult->fetchArray() ){
//データの上書き
$sSQL = "UPDATE keinos_db SET value='{$sData}',time_update={$iTime} WHERE id='{$sKey}';";
} else {
//データの追加
$sSQL = "INSERT INTO keinos_db(id,value,time_create,time_update) VALUES('{$sKey}','{$sData}',{$iTime},{$iTime});";
}
$bResult = $this->rLink->exec( $sSQL );
/* データ保存チェック デフラグ用 */
if( FALSE ){ // デフラグ時はTRUEにする
fPrint_r( "Result bool: <br>" . $bResult );
$sSQL = "SELECT * FROM keinos_db WHERE id='{$sKey}'; ";
$rResult = $this->rLink->query( $sSQL );
fPrint_r( "Data bump: <br>" . $rResult->fetchArray() );
/* DBのクローズ */
$this->rLink->close();
$this->rLink = null;
die("---<br>debug mode of <i>save</i> method for SQLite3 at line " . __LINE__ );
}
/* DBのクローズ */
$this->rLink->close();
$this->rLink = null;
} catch ( Exception $e ) {
die( "クエリーエラー:データベースのテーブル作成失敗です。<hr><pre style='text-align:left;font-family:monospace; white-space: -moz-pre-wrap;white-space: -pre-wrap;white-space: -o-pre-wrap;white-space: pre-wrap;word-wrap: break-word;'>{$e->getTraceAsString}</pre>" );
}
/* SQLite2 */
} else {
/* SQLite2の場合のリソース再取得 */
if( ! is_resource( $this->rLink ) ){
$this->cData( $this->db_name );
}
$sData = sqlite_escape_string( $sData ); /* データをSQL文用にエスケープ */
$rResult = sqlite_query( $this->rLink, $sSQL, SQLITE_BOTH, $sqliteerror );
//データの上書き
if( sqlite_num_rows( $rResult ) > 0 ){
$iTime = time();
$sSQL = "UPDATE keinos_db SET value='{$sData}',time_update={$iTime} WHERE id='{$sKey}';";
} else {
//データの追加
$iTime = time();
$sSQL = "INSERT INTO keinos_db(id,value,time_create,time_update) VALUES('{$sKey}','{$sData}',{$iTime},{$iTime});";
}
$bResult = sqlite_exec( $this->rLink, $sSQL, $sqliteerror );
}
return $bResult;
}
function load( $sDataID ){
$bIsSQLite3 = $this->bIsSQLite3;
if( ! is_resource( $this->rLink ) && ! $bIsSQLite3 ){
$this->cData( $this->db_name );
}
/* SQLite3の場合 DBのクローズ(念のため) */
if( is_resource( $this->rLink ) && $bIsSQLite3 ){
$this->rLink->close( );
$this->rLink = null;
}
//データの取得
$sKey = rawurlencode( $sDataID );
$sSQL = "SELECT id, value FROM keinos_db WHERE id='{$sKey}';";
/* SQLite3 */
if( $bIsSQLite3 ){
$this->rLink = new SQLite3( $this->db_path );
$rResult = $this->rLink->query( $sSQL );
if( $rResult ){
$row = $rResult->fetchArray( SQLITE3_ASSOC );
$this->rLink->close();
$this->rLink = null;
$mResult = $this->unserialize( $row['value'] );
} else {
$this->rLink->close();
$this->rLink = null;
$mResult = FALSE;
}
} else {
/* SQLite2 */
$rResult = sqlite_query( $this->rLink, $sSQL, SQLITE_BOTH, $sqliteerror );
if( sqlite_num_rows( $rResult ) == 1 ){
$row = sqlite_fetch_array( $rResult , SQLITE_ASSOC );
$mResult = $this->unserialize( $row['value'] );
} else {
$mResult = FALSE;
}
}
return $mResult;
}
function get_list(){
$bIsSQLite3 = $this->bIsSQLite3;
if( ! is_resource( $this->rLink ) && ! bIsSQLite3 ){
$this->cData( $this->db_name );
}
//データの取得
$sSQL = "SELECT id FROM keinos_db;";
/* SQLite3 */
if( $bIsSQLite3 ){
$this->rLink = new SQLite3( $this->db_path );
$rResult = $this->rLink->query( $sSQL );
while( $row = $rResult->fetchArray( SQLITE3_ASSOC ) ){
$mResult[] = rawurldecode( $row['id'] );
}
$this->rLink->close();
$this->rLink = null;
/* SQLite2 */
} else {
$rResult = sqlite_query($this->rLink, $sSQL, SQLITE_BOTH, $sqliteerror );
for( $i=0; $i < sqlite_num_rows( $rResult ); $i++){
$row = sqlite_fetch_array( $rResult, SQLITE_ASSOC );
$mResult[] = rawurldecode( $row['id'] );
}
}
return $mResult;
}
function dump( $sDataID="" ){
$bIsSQLite3 = $this->bIsSQLite3;
if( ! is_resource($this->rLink) && ! $bIsSQLite3 ){
$this->cData($this->db_name );
}
if( Empty( $sDataID ) ){
//全データの取得
$sSQL = "SELECT * FROM keinos_db;";
} else {
//データの取得
$sKey = rawurlencode( $sDataID );
$sSQL = "SELECT * FROM keinos_db WHERE id='{$sKey}';";
}
if( $bIsSQLite3 ){
$this->rLink = new SQLite3( $this->db_path );
$rResult = $this->rLink->query( $sSQL );
while( $row = $rResult->fetchArray( SQLITE3_ASSOC ) ){
$row['raw'] = $row;
$row['id'] = rawurldecode( $row['id'] );
$row['value'] = $this->unserialize( $row['value'] );
$mResult[] = $row;
}
$this->rLink->close();
$this->rLink = null;
} else {
$rResult = sqlite_query( $this->rLink, $sSQL, SQLITE_BOTH, $sqliteerror );
for( $i=0; $i < sqlite_num_rows( $rResult ); $i++ ){
$row = sqlite_fetch_array( $rResult, SQLITE_ASSOC );
$row['raw'] = $row;
$row['id'] = rawurldecode( $row['id'] );
$row['value'] = $this->unserialize( $row['value'] );
$mResult[] = $row;
}
}
return $mResult;
}
/* データの削除(DBの削除ではない) */
function delete( $sDataID ){
$bIsSQLite3 = $this->bIsSQLite3;
if( ! is_resource( $this->rLink ) && ! $bIsSQLite3 ){
$this->cData( $this->db_name );
}
$sKey = rawurlencode( $sDataID );
$sSQL = "DELETE FROM keinos_db WHERE id='{$sKey}';";
if( $bIsSQLite3 ){
$this->rLink = new SQLite3( $this->db_path );
$bResult = $this->rLink->exec( $sSQL );
$this->rLink->close();
$this->rLink = null;
} else {
//データの削除
$bResult = sqlite_exec( $this->rLink, $sSQL, $sqliteerror );
}
return ( ! $bResult ) ? FALSE : TRUE;
}
/* DBの削除 */
function unlink( $sDBName = "" ){
if( is_resource( $this->rLink ) ){
$this->__destruct();
}
//DBの基本設定
$sDBName = Empty( $sDBName ) ? $this->db_name : rawurlencode( $sDBName );
$sPath_DB = $this->db_path;
$sPath_PairKey = $this->pairkey_path;
if( File_Exists( $sPath_DB ) ){
$bIsDeleted_DB = rename( $sPath_DB, $sPath_DB . ".deleted" );
$bIsDeleted_PairKey = rename( $sPath_PairKey, $sPath_PairKey . ".deleted" );
return ( $bIsDeleted_DB and $bIsDeleted_PairKey );
} else {
return FALSE;
}
}
function get_time_create( $sDataID ){
$bIsSQLite3 = $this->bIsSQLite3;
if( ! is_resource( $this->rLink ) && ! $bIsSQLite3 ){
$this->cData( $this->db_name );
}
//データの取得
$sKey = rawurlencode( $sDataID );
$sSQL = "SELECT id, value FROM keinos_db WHERE id='{$sKey}';";
/* SQLite3 */
if( $bIsSQLite3 ){
$sSQL = "SELECT * FROM keinos_db WHERE id='{$sKey}'; ";
$this->rLink = new SQLite3( $this->db_path );
$rResult = $this->rLink->query( $sSQL );
$this->rLink->close();
$this->rLink = null;
if( $rResult ){
$row = $rResult->fetchArray( SQLITE3_ASSOC );
$mResult = $row['time_create'];
} else {
$mResult = FALSE;
}
} else {
/* SQLite2 */
$rResult = sqlite_query( $this->rLink, $sSQL, SQLITE_BOTH, $sqliteerror );
if( sqlite_num_rows( $rResult ) == 1 ){
$row = sqlite_fetch_array( $rResult , SQLITE_ASSOC );
$mResult = $row['time_create'];
} else {
$mResult = FALSE;
}
}
return $mResult;
}
function get_time_update( $sDataID ){
$bIsSQLite3 = $this->bIsSQLite3;
if( ! is_resource( $this->rLink ) && ! $bIsSQLite3 ){
$this->cData( $this->db_name );
}
//データの取得
$sKey = rawurlencode( $sDataID );
$sSQL = "SELECT id, value FROM keinos_db WHERE id='{$sKey}';";
/* SQLite3 */
if( $bIsSQLite3 ){
$sSQL = "SELECT * FROM keinos_db WHERE id='{$sKey}'; ";
$this->rLink = new SQLite3( $this->db_path );
$rResult = $this->rLink->query( $sSQL );
if( $rResult ){
$row = $rResult->fetchArray( SQLITE3_ASSOC );
$this->rLink->close();
$this->rLink = null;
$mResult = $row['time_update'];
} else {
$mResult = FALSE;
}
} else {
/* SQLite2 */
$rResult = sqlite_query( $this->rLink, $sSQL, SQLITE_BOTH, $sqliteerror );
if( sqlite_num_rows( $rResult ) == 1 ){
$row = sqlite_fetch_array( $rResult , SQLITE_ASSOC );
$mResult = $row['time_update'];
} else {
$mResult = FALSE;
}
}
return $mResult;
}
function close(){
$this->__destruct();
unset( $this );
}
}
} else {
//データ関連であるため、あえて表示
die( "クラス'cData'がすでに定義されています。" );
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.