search

Home  >  Article  >  Backend Development  >  Access classes for Postgresql DB

Access classes for Postgresql DB

WBOY
WBOYOriginal
2016-07-25 09:01:45978browse
The code is not for direct use, but just to provide an idea. Various support for various features of PG, including but not limited to tree subqueries, prepared statements, and batch insert:

The code has been continuously revised for a long time and has been finalized. This thing will not be modified for a long time in the future.
  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. }
复制代码


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Convert Arabic numerals to Chinese characters (upper and lower case)Next article：Convert Arabic numerals to Chinese characters (upper and lower case)

Related articles

See more