Postgresql DB的访问类

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

复制代码