欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

Postgresql DB的访问类

程序员文章站 2022-04-15 15:49:49
...
代码不是用来直接使用, 只是提供一个思路. 对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. }
复制代码