当前位置:Gxlcms > PHP教程 > PostgresqlDB的访问类

PostgresqlDB的访问类

时间:2021-07-01 10:21:17 帮助过:11人阅读

代码不是用来直接使用, 只是提供一个思路. 对PG的各种特性, 包括不限于 树子查询, prepared statements, batch insert的各种支持:

代码经过了相当长时间的不断修正, 最终定稿, 将来相当长的时间内, 都不会去修改这个玩意了.
  1. /**
  2. * The generic DB access Class, Entry of all DB Access
  3. * Only PG is supported -- 201210
  4. *
  5. * @author Anthony
  6. * 2010-2012 reserved
  7. */
  8. class DB {
  9. // Query types
  10. const SELECT = 1;
  11. const INSERT = 2;
  12. const UPDATE = 3;
  13. const DELETE = 4;
  14. /**
  15. * True Value
  16. */
  17. const T = 't';
  18. /**
  19. * False Value
  20. */
  21. const F = 'f';
  22. /**
  23. * Null Value
  24. */
  25. const N = 'N/A'; //NULL Value
  26. /**
  27. * Specilize the value;
  28. * 'f' as False, 't' as TRUE, 'N/A' as NULL value
  29. *
  30. * @param String $s, Orignal Value
  31. *
  32. * @return String, specilized value
  33. */
  34. public static function specializeValue($s){
  35. if($s === self::N){
  36. return NULL;
  37. }
  38. if($s === self::T){
  39. return True;
  40. }
  41. if($s === self::F){
  42. return False;
  43. }
  44. return $s;
  45. }
  46. /**
  47. * Batch insert into table
  48. * @param String $table_name Table Name
  49. * @param Array $cols columns of table
  50. * @param Array $values, values array of data
  51. * @param String/Array $return_cols Return column[s], 'id' is return by default,Array for multi Column
  52. * @param String $db Instance name of DB Connection
  53. *
  54. * @return Resultset return result set of return_cols
  55. */
  56. public static function insert_batch($table_name,$cols,$values,$return_cols='id',$db='default'){
  57. $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column($cols,$db).') values ';
  58. $_vsql = array();
  59. foreach ($values as $value){
  60. $_vsql[] = '('.self::quote($value).')';
  61. }
  62. $_sql .= implode(',',$_vsql);
  63. $_sql .= ' returning '.self::quote_column($return_cols);
  64. return self::query(self::SELECT,$_sql)->execute($db)->as_array();
  65. }
  66. /**
  67. * Insert into table from Array Data, and return column[s], ID is return by default
  68. *
  69. * @param String $table_name Table Name
  70. * @param Array $data Array Data Of key value pairs.
  71. * @param String/Array $return_cols Return column[s], 'id' is return by default,Array for multi Column
  72. * @param String $db Instance name of DB Connection
  73. *
  74. * @return Boolean/Resultset True if success without return column, False if failed, value of column[s] if return_cols presented.
  75. */
  76. public static function insert_table($table_name,$data,$return_cols='id',$db='default'){
  77. if (!is_array($data)){
  78. return false;
  79. }
  80. if (is_null($return_cols)){
  81. $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') values ('.
  82. self::quote(array_values($data),$db).')';
  83. return self::query(self::INSERT,$_sql)->execute($db);
  84. }
  85. //Specialize value
  86. $data = array_map('self::specializeValue',$data);
  87. if (is_string($return_cols)){
  88. $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') values ('.
  89. self::quote(array_values($data),$db).')'." returning ".$return_cols;
  90. $id = self::query(self::SELECT,$_sql)->execute($db)->get($return_cols);
  91. return $id;
  92. }else{
  93. if (is_array($return_cols)){
  94. $ids = implode(',',$return_cols);
  95. $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') values ('.
  96. self::quote(array_values($data),$db).')'." returning ".$ids;
  97. $r_ids = self::query(self::SELECT,$_sql)->execute($db)->current();
  98. return $r_ids;
  99. }
  100. }
  101. return false;
  102. }
  103. /**
  104. * Update Table data, and compare with reference data
  105. *
  106. * @param String $table_name Table Name
  107. * @param Integer $id ID of data
  108. * @param Array $data Array Data Of key value pairs.
  109. * @param Array $refdata Reference data
  110. * @param String $id_name Column name of ID
  111. * @param String $db Instance name of DB Connection
  112. *
  113. * @return Integer Affected Rows,False if failed!
  114. */
  115. public static function update_data($table_name,$id,$data,$refdata,$id_name='id',$db='default'){
  116. if (!is_array($data)){
  117. throw new exception('Data should be col=>val pairs array');
  118. }
  119. foreach($data as $k => $v){
  120. if(is_array($refdata)){
  121. if(isset($refdata[$k])){
  122. if($v == $refdata[$k]){
  123. unset($data[$k]);
  124. }
  125. }
  126. }elseif(is_object($refdata)){
  127. if(isset($refdata->$k)){
  128. if($v == $refdata->$k){
  129. unset($data[$k]);
  130. }
  131. }
  132. }else{
  133. throw new exception('refdata type error');
  134. }
  135. }
  136. //Specialize value
  137. $data = array_map('self::specializeValue',$data);
  138. if(count($data)>0){
  139. return self::update_table($table_name,$id,$data,'id',$db);
  140. }else{
  141. return 0;
  142. }
  143. }
  144. /**
  145. * Update table with data without checking the referenced Data
  146. *
  147. * @param String $table_name Table Name
  148. * @param Integer $id ID of data
  149. * @param Array $data Array Data Of key value pairs.
  150. * @param String $id_name Column name of ID
  151. * @param String $db Instance name of DB Connection
  152. *
  153. * @return Integer Affected Rows,False if failed!
  154. */
  155. public static function update_table($table_name,$id,$data,$id_name='id',$db='default'){
  156. if (!is_array($data)){
  157. return false;
  158. }
  159. $_sql = 'update '.self::quote_table($table_name,$db).' set '.self::quote_assoicate($data,'=',',',$db).' where '.
  160. self::quote_column($id_name,$db).'='.self::quote($id,$db);
  161. return self::query(self::UPDATE,$_sql)->execute($db);
  162. }
  163. /**
  164. * quote key value pair of col => values
  165. *
  166. * @param Array $data, col=>value pairs
  167. * @param String $concat, default '='
  168. * @param String Delimiter, default ','
  169. * @param String Database instance
  170. *
  171. * @return String
  172. */
  173. public static function quote_assoicate($data,$concat='=',$delimiter=',',$db='default'){
  174. $_sql = '';
  175. $_sqlArray = array();
  176. foreach ($data as $k => $v){
  177. $_sqlArray[] = self::quote_column($k,$db).$concat.self::quote($v,$db);
  178. }
  179. $_sql = implode($delimiter,$_sqlArray);
  180. return $_sql;
  181. }
  182. /**
  183. * Quote cols
  184. *
  185. * @param String $value, The column[s] name
  186. * @param String $db, Database Instance Name
  187. */
  188. public static function quote_column($value,$db='default'){
  189. if(!is_array($value)){
  190. return self::quote_identifier($value,$db);
  191. }else{ //quote_column array and implode
  192. $_qs = array();
  193. foreach ($value as $ele){
  194. $_qs[] = self::quote_column($ele,$db);
  195. }
  196. $_quote_column_String = implode(',',$_qs);
  197. return $_quote_column_String;
  198. }
  199. }
  200. /**
  201. * Quote the values to escape
  202. *
  203. * @param Scalar/Array $value
  204. *
  205. * @return quote string or array
  206. */
  207. public static function quote($value,$db='default'){
  208. if(!is_array($value)){
  209. return Database::instance($db)->quote($value);
  210. }else{ //Quote array and implode
  211. $_qs = array();
  212. foreach ($value as $ele){
  213. $_qs[] = self::quote($ele,$db);
  214. }
  215. $_quoteString = implode(',',$_qs);
  216. return $_quoteString;
  217. }
  218. }
  219. /**
  220. * Escape string of DB
  221. *
  222. * @param string $s table name
  223. * @param String $db Database instance name
  224. *
  225. * @return String
  226. */
  227. public static function escape($s,$db='default'){
  228. return Database::instance($db)->escape($s);
  229. }
  230. /**
  231. * Quote Table name
  232. *
  233. * @param string $s table name
  234. * @param String $db Database instance name
  235. *
  236. * @return String
  237. */
  238. public static function quote_table($s,$db='default'){
  239. return Database::instance($db)->quote_table($s);
  240. }
  241. /**
  242. * Quote a database identifier, such as a column name.
  243. *
  244. * $column = DB::quote_identifier($column,'default');
  245. *
  246. * You can also use SQL methods within identifiers.
  247. *
  248. * // The value of "column" will be quoted
  249. * $column = DB::quote_identifier('COUNT("column")');
  250. *
  251. * Objects passed to this function will be converted to strings.
  252. * [Database_Query] objects will be compiled and converted to a sub-query.
  253. * All other objects will be converted using the '__toString' method.
  254. *
  255. * @param mixed $value any identifier
  256. * @param String $db, Database instance
  257. * @return string
  258. */
  259. public static function quote_identifier($value,$db='default'){
  260. return Database::instance($db)->quote_identifier($value);
  261. }
  262. /**
  263. * Get Connection for Database instance
  264. *
  265. * @param String $db Database Instance name
  266. *
  267. * @return Connection of Databse
  268. */
  269. public static function getConnection($db = 'default'){
  270. return Database::instance($db)->getConnection();
  271. }
  272. /**
  273. * Get Children of current record
  274. *
  275. * @param String $table Table name
  276. * @param Bollean $returnSql
  277. * @param Integer $pid Parent Id of table record
  278. * @param String $idname ID column name
  279. * @param String $pidname Parent ID column name
  280. * @param String $db Database Instance name
  281. *
  282. * @return Records of Children
  283. */
  284. public static function getChildren($table,$returnSql = false ,$pid= '0',$idname='id',$pidname='pid' ,$db='default'){
  285. $_sql = 'select * from '.self::quote_table($table,$db).' where '.$pidname.'='.self::quote($pid,$db).
  286. " and $idname <>".self::quote($pid,$db);
  287. if($returnSql){
  288. return $_sql;
  289. }
  290. $_res = self::query(self::SELECT,$_sql,true)->execute($db)->as_array();
  291. if($_res){
  292. return $_res;
  293. }else{
  294. return false;
  295. }
  296. }
  297. /**
  298. * Tree query for connect by,traverse all the child records of Data
  299. *
  300. * @param String $tableName Tablename
  301. * @param Boolean $returnSql Return SQL String if TURE
  302. * @param String $startWith Begin valueof traverse
  303. * @param String $idCol ID Column name
  304. * @param String $pidCol Parent ID Column name
  305. * @param String $orderCol Order Column
  306. * @param Integer $maxDepth Depth of traverse,
  307. * @param Integer $level Start Level
  308. * @param String $delimiter Delimiter of branch
  309. * @param String $db Database configuration instance
  310. *
  311. * @return Record/String Return Record array or String of SQL
  312. */
  313. public static function getTree($tableName,$returnSql=false,$startWith='0',$idCol='id',$pidCol='pid', $orderCol='id', $maxDepth=0,$level = 0,$delimiter = ';',$db='default'){
  314. $_funcParas = array();
  315. $_funcParas[] = self::quote($tableName,$db); //Table|View
  316. $_funcParas[] = self::quote($idCol,$db); //ID column
  317. $_funcParas[] = self::quote($pidCol,$db); //Parent ID Column
  318. $_funcParas[] = self::quote($orderCol,$db); //Default Order by ASC
  319. $_funcParas[] = self::quote($startWith,$db); //Begin ID
  320. $_funcParas[] = self::quote($maxDepth,$db); //Depth of traverse
  321. $_funcParas[] = self::quote($delimiter,$db); //Delimitor of Branch,default ';'
  322. $_sql = 'select * from connectby('
  323. .implode(',',$_funcParas).')'
  324. .' as t(id int, pid int, level int, branch text, pos int)';
  325. if($level > 0){
  326. $_sql .= ' where level >='.self::quote($level,$db);
  327. }
  328. if($returnSql) return $_sql;
  329. $_res = self::query(self::SELECT,$_sql,true)->execute($db)->as_array();
  330. if($_res){
  331. return $_res;
  332. }else{
  333. return false;
  334. }
  335. }
  336. /**
  337. * Start transaction
  338. *
  339. * @param String $db Instance name of DB
  340. *
  341. * @return Result set
  342. */
  343. public static function begin($db='default'){
  344. return DB::query(self::UPDATE, "BEGIN")->execute($db);
  345. }
  346. /**
  347. * Define Savepoint
  348. *
  349. * @param String $savepoint
  350. *
  351. * @param String $db
  352. */
  353. public static function savepoint($savepoint, $db='default'){
  354. return DB::query(self::UPDATE, "SAVEPOINT ".$savepoint)->execute($db);
  355. }
  356. /**
  357. * Rollback to Savepoint
  358. *
  359. * @param String $savepoint
  360. *
  361. * @param String $db Database Instance name
  362. */
  363. public static function rollpoint($savepoint, $db='default'){
  364. return DB::query(self::UPDATE, "ROLLBACK TO ".$savepoint)->execute($db);
  365. }
  366. /**
  367. * Commit an transaction
  368. * @param String DB connection
  369. */
  370. public static function commit($db='default'){
  371. return DB::query(self::UPDATE, "COMMIT")->execute($db);
  372. }
  373. public static function rollback($db='default'){
  374. return DB::query(self::UPDATE, "ROLLBACK")->execute($db);
  375. }
  376. /**
  377. * Create a new [Database_Query] of the given type.
  378. *
  379. * // Create a new SELECT query
  380. * $query = DB::query(self::SELECT, 'SELECT * FROM users');
  381. *
  382. * // Create a new DELETE query
  383. * $query = DB::query(self::DELETE, 'DELETE FROM users WHERE id = 5');
  384. *
  385. * Specifying the type changes the returned result. When using
  386. * self::SELECT, a [Database_Query_Result] will be returned.
  387. * self::INSERT queries will return the insert id and number of rows.
  388. * For all other queries, the number of affected rows is returned.
  389. *
  390. * @param integer type: self::SELECT, self::UPDATE, etc
  391. * @param string SQL statement
  392. * @param Boolean $as_object Return Result set as Object if true, default FALSE
  393. * @param Array $params Query parameters of SQL, default array()
  394. * @param String $stmt_name The query is Prepared Statement if TRUE,
  395. * Execute Prepared Statement when $param is Not NULL
  396. * Prepare Statement when $param is NULL
  397. *
  398. * @return Database_Query
  399. */
  400. public static function query($type, $sql = NULL ,$as_object = false,$params = array(),$stmt_name = NULL)
  401. {
  402. return new Database_Query($type, $sql,$as_object,$params,$stmt_name);
  403. }
  404. /**
  405. * Gettting paginated page from Orignal SQL
  406. *
  407. * @param String $sql SQL query
  408. * @param UTL Object &$page UTL object of tempalte
  409. * @param String $orderBy Order by column, default 'updated desc'
  410. * @param String $dataPro Data Property Name, default 'data'
  411. * @param String $pagePro Pagnation Frament property Name, default 'pagination'
  412. * @param Array $config Pagination Configuration Array overider
  413. * @param String $db Database Instance Name, default 'default'
  414. * @param Boolean $as_object Populate Data as Object if TRUE, default TRUE
  415. * @param String $_paginClass Class Name of pagination
  416. * @return True if success
  417. */
  418. public static function getPage($_sql,&$page,$orderBy ='updated desc', $dataPro='data',$pagePro = 'pagination',
  419. $config = NULL,$db = 'default',$as_object= true,$_paginClass='Pagination'){
  420. $_csql = 'select count(1) as c from ('.$_sql.') st';
  421. $_c = DB::query(self::SELECT,$_csql)->execute($db)->get('c');
  422. if($config){
  423. $config['total_items'] = $_c;
  424. $_pagination = new $_paginClass($config);
  425. }else{
  426. $config = array();
  427. $config['total_items'] = $_c;
  428. $_pagination = new $_paginClass($config);
  429. }
  430. $_sql .= ' order by '.$orderBy;
  431. if($_pagination->offset){
  432. $_sql .= ' offset '.$_pagination->offset;
  433. }
  434. $_sql .= ' limit '.$_pagination->items_per_page;
  435. $_data = DB::query(self::SELECT,$_sql,$as_object)->execute($db)->as_array();
  436. if(!$_data){
  437. $page->{$dataPro} = false;
  438. $page->{$pagePro} = false;
  439. return false;
  440. }
  441. $page->{$dataPro} = $_data;
  442. $page->{$pagePro} = $_pagination;
  443. return true;
  444. }
  445. /**
  446. * Get All roles of subordinate
  447. *
  448. * @param Integer $role_id Integer User Role ID
  449. * @param Boolean $quote Quote the SQL if ture, reutrn orignal SQL if false
  450. * @param String $role_table Table of role hierarchy
  451. * @param Integer $level Start Level of tree traverse
  452. * @param String $db Database Instance name
  453. * @return SQL String
  454. */
  455. public static function getRoleTreeSql($role_id,$quote = false,$role_table,$level=0,$db='default'){
  456. $_sql = 'select id from ('.self::getTree($role_table,true,$role_id,'id','pid','id',
  457. 0, //Maxdepth
  458. $level, //Level
  459. ';',$db).') utree';
  460. if(!$quote) return $_sql;
  461. else return '('.$_sql.')';
  462. }
  463. /**
  464. * Getting SQL String to query Objects of subordinate and owned objects
  465. * Child User Role Tree[CURT]
  466. *
  467. * @param integer $role_id Role ID of user
  468. * @param integer $user_id User ID
  469. * @param String $role_table Table of Role
  470. * @param Boolean $quote Quote the SQL if ture, reutrn orignal SQL if false
  471. * @param String $roleCol Role ID column name
  472. * @param String $ownerCol Owner ID column name
  473. * @param String $db Database instance name
  474. * @return SQL String
  475. */
  476. public static function getCURTreeSql($role_id,$user_id,$role_table,$quote = true,
  477. $roleCol='role_id',$ownerCol = 'owner_id' ,$db='default'){
  478. $_sql = ' '.$roleCol.' in '.self::getRoleTreeSql($role_id,true,$role_table,
  479. 1, //Level start with 1
  480. $db). ' or '.$ownerCol.'='.self::quote($user_id,$db);
  481. if(!$quote) return $_sql;
  482. else return '('.$_sql.')';
  483. }
  484. /**
  485. * Array from tree query to tree
  486. *
  487. * @param Array $eles , the record set from self::getTree
  488. * @param String $elename, element name of node
  489. * @param String $cldname, Child node name
  490. * @param String $delimiter, The delimiter of branch
  491. *
  492. * @return Object , Tree object of data
  493. */
  494. public static function array2tree($eles,$elename,$cldname,$delimiter=';'){
  495. if($elename == $cldname){
  496. throw new Exception('Ele name equals cldname!');
  497. }
  498. $rtree = array();
  499. foreach ($eles as $ele){
  500. $_branch = $ele->branch;
  501. //Log::debug('branch='.$_branch);
  502. //The depth in the array
  503. $_depths = explode($delimiter,$_branch);
  504. if(count($_depths == 1)){
  505. $_root = $_depths[0];
  506. }
  507. $_cur = &$rtree;
  508. foreach ($_depths as $depth){
  509. //Create NODE
  510. if(!isset($_cur[$cldname])){
  511. $_cur[$cldname] = array();
  512. }
  513. if(!isset($_cur[$cldname][$depth])){
  514. $_cur[$cldname][$depth] = array();
  515. $_cur = &$_cur[$cldname][$depth];
  516. }else{
  517. $_cur = &$_cur[$cldname][$depth];
  518. }
  519. }
  520. $_cur[$elename] = $ele;
  521. }
  522. return $rtree[$cldname][$_root];
  523. }
  524. }

人气教程排行