oracle 常用命令收集,持续更新

-----------------------------------------------oracle 修改字段类型 ,有数据情况下修改
-- 插入新的列 注意类型和不要和原来的列冲突
ALTER TABLE tb_test ADD permile_temp NUMBER (5, 2);

-- 更新老数据到新的列
UPDATE tb_test
SET permile_temp = permile;

-- 老列重命名或者删除即可(有数据的情况下可以修改名字)
ALTER TABLE DROP COLUMN permile;

-- 修改名字成自定的列,完成字段类型修改
ALTER TABLE TEST RENAME COLUMN permile_temp TO permile;

-- 没有数据,可能需要进行提交
COMMIT;
-----------------------------------------------
--当前的连接数 -- 需要dba权限
SELECT
	COUNT (*)
FROM
	v$process;

-- 查询出锁死的session -- 需要dba权限
SELECT
	object_name,
	machine,
	s. SID,
	s.serial#
FROM
	v$locked_object l, -- 需要dba权限
	dba_objects o,-- 需要dba权限
	v$session s -- 普通权限即可查询。不过无法进行相关处理.
WHERE
	l.object_id  =  o.object_id
AND l.session_id = s. SID;

-- 关闭锁死的session ( 使用两个参数,上面查询出来的 sid 和 serial )
ALTER SYSTEM KILL SESSION '319,1948'; -- 需要授权

 

yii kindeditor sessionid丢失问题

主要原因是kindeditor 中使用了swfupload ,而ff调用时,不会使用浏览器当前的cookie。

chrome也尝试过,通过切换不同的flash,会导致该情况发生。

如果flash上传不法传递cookie,则通过post该参数,并替换成原来的session即可.

http://kindeditor.net/docs/option.html#extrafileuploadparams

KindEditor.ready(function(K) {
        K.create('#id', {
                extraFileUploadParams : {
                        GDSESSION_ID : 'XXXXXXXXXXX'
                }
        });
});

yii 端根据session_id切换session代码

/**
 * 直接识别文件类型,修改上传文件扩展,防止缩略变形
 */
class EditorController extends CController {

	public function init() {
		// 切换session 修复部分不支持 flash cookie的浏览器,暂时发现ff
		if (isset ( $_REQUEST ['GDSESSION_ID'] ) && $_REQUEST ['GDSESSION_ID'] != Yii::app ()->session->getSessionID ()) {
			Yii::app ()->session->destroy ();
			Yii::app ()->session->setSessionID ( $_REQUEST ['GDSESSION_ID'] );
			// Yii::app ()->session->open ();
		}
	}
....

 

 

YII mysql 迁移 oracle 小写处理

因为框架内联绝大多数代码是未进行引号包裹的。

oracle 纯sql的情况下,默认当做大写,影响很大。只能统一数据库和字段,表名大写转移。

Ar 兼容小写处理
编写 CActiveRecordO 集成 CActiveRecord

修改以下函数进行小写兼容

$model->field

public function __get($name) {
		$upname = strtoupper ( $name );
		if (isset ( $this->_attributes [$upname] )) {
			return $this->_attributes [$upname];
		} elseif (isset ( $this->getMetaData ()->columns [$upname] ))
			return null;
		elseif (isset ( $this->_related [$name] ))
			return $this->_related [$name];
		elseif (isset ( $this->getMetaData ()->relations [$name] ))
			return $this->getRelated ( $name );
		else
			return parent::__get ( $name );
	}

 

$model->field=val;

public function __set($name, $value) {
		$upname = strtoupper ( $name );
		if (($this->setAttribute ( $name, $value ) === false && $this->setAttribute ( $upname, $value ) === false)) {
			if (isset ( $this->getMetaData ()->relations [$name] ))
				$this->_related [$name] = $value;
			else
				parent::__set ( $name, $value );
		}
		$name = $upname;
	}

 

empty($model->field) //判断

public function __isset($name) {
		$upname = strtoupper ( $name );
		if (isset ( $this->_attributes [$upname] ))
			return true;
		elseif (isset ( $this->getMetaData ()->columns [$upname] ))
			return false;
		elseif (isset ( $this->_related [$name] ))
			return true;
		elseif (isset ( $this->getMetaData ()->relations [$name] ))
			return $this->getRelated ( $name ) !== null;
		else
			return parent::__isset ( $name );
	}

默认 getAttributes setAttributes 抱成别的函数进行重写,因为内部调用太多地方,不可直接重写该函数,另外编写函数getAttributesWithO 之类的即可

	public function getAttributesWithO($names = true) {
		$attributes = $this->getAttributes ( $names );
		$attributes = array_change_key_case ( $attributes, CASE_LOWER );
		return $attributes;
	}
	public function setAttributesWithO($attributes = array(), $safeOnly = true, $hasClob = false) {
		$attributes = array_change_key_case ( $attributes, CASE_UPPER );
		if ($hasClob && $this->getMetaData ()->columns && is_array ( $this->getMetaData ()->columns )) {
			foreach ( $this->getMetaData ()->columns as $key => $column ) {
				if (stristr ( $column->dbType, "CLOB" ) && empty ( $attributes [$key] )) {
					$attributes [$key] = '';
				}
			}
		}
		$this->setAttributes ( $attributes, $safeOnly );
	}
	public function getClobByKey($key) {
		if (! empty ( $this->$key ) && is_object ( $this->$key ) && get_class ( $this->$key ) == 'OCI-Lob') {
			$this->$key = $this->$key->read ( 50000 );
		}
	}

pdo 对clob各种奇葩问题。需要调整pdo为oci8相关处理

http://www.yiiframework.com/extension/oci8pdo/

	public function afterFind() {
		$this->getClobByKey ( 'description' );
		return true;
	}

内部组件需要进行特殊处理。

主要出现在需要调用数据库实现相关功能的组件,如:CDbAuthManager,CDbHttpSession

重写相关即可

主要为大小写冲突问题。

public function hasItemChild($itemName,$childName)
	{
		return $this->db->createCommand()
			->select('parent')
			->from($this->itemChildTable)
			->where('parent=:parent AND child=:child', array(
				':parent'=>$itemName,
				':child'=>$childName))
			->queryScalar() !== false;
	}

select (‘parent’) 会返回 select “parent” 导致大小写异常,where parent =又是小写处理,纠结吧。。。

所以只能将被小写了的地方进行大写转换处理。

将被大写返回的数组,需要进行小写转换下。

		$row=array_change_key_case ( $row, CASE_LOWER );

oci 修复个setFetchModel的小异常问题,兼容有点问题,不过只在权限判断中出现,其它暂时未发现

http://www.php.net/manual/en/pdostatement.setfetchmode.php

public function setFetchMode($mode, $colClassOrObj = null, array $ctorArgs = array()) {
		// yii 内部调用
// 		parent::setFetchMode($mode,$colClassOrObj,$ctorArgs);
// 		return true;
		if ($mode == PDO::FETCH_COLUMN||$mode==PDO::FETCH_ASSOC) {
			$this->_fetchMode = $mode;
			return true;
		}
		// 52: $this->_statement->setFetchMode(PDO::FETCH_ASSOC);
		if ($colClassOrObj !== null || ! empty ( $ctorArgs )) {
			throw new PDOException ( 'Second and third parameters are not implemented for Oci8PDO_Statement::setFetchMode()' );
			// see http://www.php.net/manual/en/pdostatement.setfetchmode.php
		}
		$this->_fetchMode = $mode;
		return true;
	}

 

 

 

php 序列生成工具

<?php
/**
 * 
 * 现在简单实现,可以处理成,根据对应的表,生成对应的序列进行生成,主要同步自增id
 *
 */
class OracleTools {
	/**
	 * 通过序列获取最新的id
	 * 如果制定表,如果表不存在,则创建新表。并以原表的id+1000的数据添加.
	 * 可以单独指定需要序列的表名和id
	 */
	public static function getNewId($seqname = "common", $id = "id", $tablename = "", $maxvalue = 0, $startbyzero = false) {
		$sseqname = $seqname;
		if (empty ( $tablename ))
			$tablename = $seqname;
		$seqname = "O2O_SEQ_{$seqname}";

		$exists = sql_fetch ( "SELECT
	count(1)  existsrow
FROM
	all_sequences
WHERE
	sequence_name = '$seqname'
AND sequence_owner = '" . Yii::app ()->db->username . "'", 'existsrow' );
		if (! $exists) {
			$lastid = 1;
			if (! $startbyzero) {
				$lastid = sql_fetch ( "SELECT {$id} FROM {$tablename} WHERE  ROWNUM =1 ORDER BY {$id} DESC ", $id );
				$lastid += 1000;
			}
			if (! $maxvalue) {
				$maxvalue = '9999999999999999';
			}
			sql_execute ( "CREATE SEQUENCE {$seqname} start WITH {$lastid} increment BY 1 MAXVALUE {$maxvalue} minvalue 1 cycle" );
		}
		$id = sql_fetch ( "SELECT {$seqname}.nextVal newid FROM dual", 'newid' );
		return $id;
	}
	public static function dropSeq($seqname = "common") {
		$seqname = "O2O_SEQ_{$seqname}";
		sql_execute ( "DROP SEQUENCE {$seqname} " );
	}
}

 

chinapay 兼容 php5.2-5.5

http://www.zhaoyuanma.com/

<?php
define ( "DES_KEY", "SCUBEPGW" );
define ( "HASH_PAD", "0001ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff003021300906052b0e03021a05000414" );
bcscale ( 0 );
$private_key = array ();
// 兼容 php5.4+
if (! function_exists ( 'hex2bin' )) {
	function hex2bin($hexdata) {
		$bindata = '';
		if (strlen ( $hexdata ) % 2 == 1) {
			$hexdata = '0' . $hexdata;
		}
		for($i = 0; $i < strlen ( $hexdata ); $i += 2) {
			$bindata .= chr ( hexdec ( substr ( $hexdata, $i, 2 ) ) );
		}
		return $bindata;
	}
}
function padstr($src, $len = 256, $chr = '0', $d = 'L') {
	$ret = trim ( $src );
	$padlen = $len - strlen ( $ret );
	if ($padlen > 0) {
		$pad = str_repeat ( $chr, $padlen );
		if (strtoupper ( $d ) == 'L') {
			$ret = $pad . $ret;
		} else {
			$ret = $ret . $pad;
		}
	}
	return $ret;
}
function bin2int($bindata) {
	$hexdata = bin2hex ( $bindata );
	return bchexdec ( $hexdata );
}
function bchexdec($hexdata) {
	$ret = '0';
	$len = strlen ( $hexdata );
	for($i = 0; $i < $len; $i ++) {
		$hex = substr ( $hexdata, $i, 1 );
		$dec = hexdec ( $hex );
		$exp = $len - $i - 1;
		$pow = bcpow ( '16', $exp );
		$tmp = bcmul ( $dec, $pow );
		$ret = bcadd ( $ret, $tmp );
	}
	return $ret;
}
function bcdechex($decdata) {
	$s = $decdata;
	$ret = '';
	while ( $s != '0' ) {
		$m = bcmod ( $s, '16' );
		$s = bcdiv ( $s, '16' );
		$hex = dechex ( $m );
		$ret = $hex . $ret;
	}
	return $ret;
}
function sha1_128($string) {
	$hash = sha1 ( $string );
	$sha_bin = hex2bin ( $hash );
	$sha_pad = hex2bin ( HASH_PAD );
	return $sha_pad . $sha_bin;
}
function mybcpowmod($num, $pow, $mod) {
	if (function_exists ( 'bcpowmod' )) {
		return bcpowmod ( $num, $pow, $mod );
	}
	return emubcpowmod ( $num, $pow, $mod );
}
function emubcpowmod($num, $pow, $mod) {
	$result = '1';
	do {
		if (! bccomp ( bcmod ( $pow, '2' ), '1' )) {
			$result = bcmod ( bcmul ( $result, $num ), $mod );
		}
		$num = bcmod ( bcpow ( $num, '2' ), $mod );
		$pow = bcdiv ( $pow, '2' );
	} while ( bccomp ( $pow, '0' ) );
	return $result;
}
function rsa_encrypt($private_key, $input) {
	$p = bin2int ( $private_key ["prime1"] );
	$q = bin2int ( $private_key ["prime2"] );
	$u = bin2int ( $private_key ["coefficient"] );
	$dP = bin2int ( $private_key ["prime_exponent1"] );
	$dQ = bin2int ( $private_key ["prime_exponent2"] );
	$c = bin2int ( $input );
	$cp = bcmod ( $c, $p );
	$cq = bcmod ( $c, $q );
	$a = mybcpowmod ( $cp, $dP, $p );
	$b = mybcpowmod ( $cq, $dQ, $q );
	if (bccomp ( $a, $b ) >= 0) {
		$result = bcsub ( $a, $b );
	} else {
		$result = bcsub ( $b, $a );
		$result = bcsub ( $p, $result );
	}
	$result = bcmod ( $result, $p );
	$result = bcmul ( $result, $u );
	$result = bcmod ( $result, $p );
	$result = bcmul ( $result, $q );
	$result = bcadd ( $result, $b );
	$ret = bcdechex ( $result );
	$ret = strtoupper ( padstr ( $ret ) );
	return (strlen ( $ret ) == 256) ? $ret : false;
}
function rsa_decrypt($input) {
	global $private_key;
	$check = bchexdec ( $input );
	$modulus = bin2int ( $private_key ["modulus"] );
	$exponent = bchexdec ( "010001" );
	$result = bcpowmod ( $check, $exponent, $modulus );
	$rb = bcdechex ( $result );
	return strtoupper ( padstr ( $rb ) );
}
function buildKey($key) {
	global $private_key;
	if (count ( $private_key ) > 0) {
		foreach ( $private_key as $name => $value ) {
			unset ( $private_key [$name] );
		}
	}
	$ret = false;
	$key_file = parse_ini_file ( $key );
	if (! $key_file) {
		return $ret;
	}
	$hex = "";
	if (array_key_exists ( "MERID", $key_file )) {
		$ret = $key_file ["MERID"];
		$private_key ["MERID"] = $ret;
		$hex = substr ( $key_file ["prikeyS"], 80 );
	} else if (array_key_exists ( "PGID", $key_file )) {
		$ret = $key_file ["PGID"];
		$private_key ["PGID"] = $ret;
		$hex = substr ( $key_file ["pubkeyS"], 48 );
	} else {
		return $ret;
	}
	$bin = hex2bin ( $hex );
	$private_key ["modulus"] = substr ( $bin, 0, 128 );
	$cipher = MCRYPT_DES;
	$iv = str_repeat ( "x00", 8 );
	$prime1 = substr ( $bin, 384, 64 );
	// 兼容 php5.5+
	// $enc = mcrypt_cbc ( $cipher, DES_KEY, $prime1, MCRYPT_DECRYPT, $iv );
	$enc = mcrypt_decrypt ( $cipher, DES_KEY, $prime1, 'cbc', $iv );
	$private_key ["prime1"] = $enc;
	$prime2 = substr ( $bin, 448, 64 );

	// $enc = mcrypt_cbc ( $cipher, DES_KEY, $prime2, MCRYPT_DECRYPT, $iv );
	$enc = mcrypt_decrypt ( $cipher, DES_KEY, $prime2, 'cbc', $iv );
	$private_key ["prime2"] = $enc;
	$prime_exponent1 = substr ( $bin, 512, 64 );
	// $enc = mcrypt_cbc ( $cipher, DES_KEY, $prime_exponent1, MCRYPT_DECRYPT,
	// $iv );
	$enc = mcrypt_decrypt ( $cipher, DES_KEY, $prime_exponent1, 'cbc', $iv );
	$private_key ["prime_exponent1"] = $enc;
	$prime_exponent2 = substr ( $bin, 576, 64 );
	// $enc = mcrypt_cbc ( $cipher, DES_KEY, $prime_exponent2, MCRYPT_DECRYPT,
	// $iv );
	$enc = mcrypt_decrypt ( $cipher, DES_KEY, $prime_exponent2, 'cbc', $iv );
	$private_key ["prime_exponent2"] = $enc;
	$coefficient = substr ( $bin, 640, 64 );
	// $enc = mcrypt_cbc ( $cipher, DES_KEY, $coefficient, MCRYPT_DECRYPT, $iv
	// );
	$enc = mcrypt_decrypt ( $cipher, DES_KEY, $coefficient, 'cbc', $iv );
	$private_key ["coefficient"] = $enc;
	return $ret;
}
function sign($msg) {
	global $private_key;
	if (! array_key_exists ( "MERID", $private_key )) {
		return false;
	}
	$hb = sha1_128 ( $msg );
	return rsa_encrypt ( $private_key, $hb );
}
function signOrder($merid, $ordno, $amount, $curyid, $transdate, $transtype) {
	if (strlen ( $merid ) != 15)
		return false;
	if (strlen ( $ordno ) != 16)
		return false;
	if (strlen ( $amount ) != 12)
		return false;
	if (strlen ( $curyid ) != 3)
		return false;
	if (strlen ( $transdate ) != 8)
		return false;
	if (strlen ( $transtype ) != 4)
		return false;
	$plain = $merid . $ordno . $amount . $curyid . $transdate . $transtype;
	return sign ( $plain );
}
function verify($plain, $check) {
	global $private_key;
	if (! array_key_exists ( "PGID", $private_key )) {
		return false;
	}
	if (strlen ( $check ) != 256) {
		return false;
	}
	$hb = sha1_128 ( $plain );
	$hbhex = strtoupper ( bin2hex ( $hb ) );
	$rbhex = rsa_decrypt ( $check );
	return $hbhex == $rbhex ? true : false;
}
function verifyTransResponse($merid, $ordno, $amount, $curyid, $transdate, $transtype, $ordstatus, $check) {
	if (strlen ( $merid ) != 15)
		return false;
	if (strlen ( $ordno ) != 16)
		return false;
	if (strlen ( $amount ) != 12)
		return false;
	if (strlen ( $curyid ) != 3)
		return false;
	if (strlen ( $transdate ) != 8)
		return false;
	if (strlen ( $transtype ) != 4)
		return false;
	if (strlen ( $ordstatus ) != 4)
		return false;
	if (strlen ( $check ) != 256)
		return false;
	$plain = $merid . $ordno . $amount . $curyid . $transdate . $transtype . $ordstatus;
	return verify ( $plain, $check );
}