当前位置：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. */
  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. }

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！