Postgresql Prepared Statement management

宏哥 发布于 2013/02/25 10:42
阅读 340
收藏 0
<?PHP
/**
 * Prepare statement loading from Config
 * The ctime on dabase instance config could be used to control the expire * of all prepared statments
 * Config ctime = now() could be used to force recompile for each bootstrap, 
 * date +%s could get the value of current seconds from EPOCH
 * stmts will be name => query pairs 
 * Expired will be recompile automatically, any update on config should update ctime as well
 *
 * @author Anthony.chen 
 * 2010-2012 reserved
 */
class PSTMT{

	public static $instances = array();

	/**
	 * Loading the prepare statment from DB Connection
	 * The stmts is config linked to DB instance with 'stmts' k=>v array
	 *
	 * @return Boolean
	 */
	public static function prepare($db='default'){
		//Getting statment from config
		$_config = Pexcel::config('database')->$db;
		$_ctime = Arr::get($_config,'ctime',0);
		$_configStmts = Arr::get($_config,'stmts',NULL);
		if($_configStmts != NULL){//There is statments configured
			if(!isset(self::$instances[$db])){
				$_sql = 'select name , EXTRACT(EPOCH FROM prepare_time) as ctime from pg_prepared_statements';
				$_pstmts = DB::query(DB::SELECT,$_sql,true)->execute($db)->as_array();
				if(!$_pstmts){
					self::$instances[$db] = array();
				}else{
					//Log::debug('Before Commpiling,statement Found');
					foreach($_pstmts as $_pstmt){
						self::$instances[$db][$_pstmt->name] = $_pstmt->ctime;
					}
				}
			}
			//Compile the statments
			foreach($_configStmts as $stmtName => $stmtQuery){
				if(isset(self::$instances[$db][$stmtName])){
					if( self::$instances[$db][$stmtName] < $_ctime){
						//Log::debug($stmtName.' Expires');
					}else{
						//Log::debug($stmtName.' Exists');
						continue;
					}
				}
				self::compile($stmtName,$stmtQuery,$db);
			}

		}else{
			throw new Error_Exception('stmts not in config!');
		}
		return True;
	}

	/**
	 * Compile the prepared statment 
	 *
	 * @param String $stmtName, Statement name 
	 * @param String $stmtQuery, Statement query
	 * @param String $db ,Instance name of database
	 *
	 * @return Boolean
	 */
	public static function compile($stmtName, $stmtQuery,$db ='default'){
		if(isset(self::$instances[$db][$stmtName])){ //already Compiled
			//Doing Nothing
			//Log::debug('DEALLOCATE '.$stmtName);
			DB::query(DB::UPDATE,'DEALLOCATE '.$stmtName)->execute($db);
		}
		$_ret = DB::query(DB::SELECT,$stmtQuery,False,NULL,$stmtName)->execute($db)->count();
		//Log::debug(__FUNCTION__.':compiling the pstat:'.$stmtQuery);
		self::$instances[$db][$stmtName] = time();
		return True;
	}

	/**
	 * Execute the prepared statement
	 *
	 * @param String $stmtName, Statement Name
	 * @param Array $params , The parameter to be transfered into query
	 * @param Boolean $as_object, True to fetch result as object
	 * @param String $db, Database Instance name
	 *
	 * @return Array of result set
	 */

	public static function execute($stmtName,$params = array(),$as_object = TRUE,$db = 'default'){
		if(isset(self::$instances[$db][$stmtName])){
			return DB::query(DB::SELECT,NULL,$as_object,$params,$stmtName)->execute($db);
		}else{
			$_config = Pexcel::config('database')->$db;
			$_configStmts = Arr::get($_config,'stmts',NULL);
			//Compile the prepared statment 
			if(isset($_configStmts[$stmtName])){
				self::compile($stmtName,$_configStmts[$stmtName],$db);
			}else{
				return false;
			}
			return DB::query(DB::SELECT,NULL,$as_object,$params,$stmtName)->execute($db);
		}
	}
}
加载中
0
mark35
mark35
Query预处理管理,适用场合是不是那种高度重复(且不限于一个请求生命周期内)写入DB的环境?
0
宏哥
宏哥

引用来自“mark35”的答案

Query预处理管理,适用场合是不是那种高度重复(且不限于一个请求生命周期内)写入DB的环境?

适合在整个连接会话当中, 重复查询的语句.

如果是长连接, 则贯穿整个生命周期.

返回顶部
顶部