Access classes for Postgresql db

Source: Internet
Author: User
Tags savepoint
The code is not intended to be used directly, just to provide a clue. Various features of PG, including not limited to tree subquery, prepared statements, batch insert support:

The code has been revised and finalized for quite a long time, and will not modify this thing for a long time to come.
  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 was 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 pres ented.
  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" would be quoted
  249. * $column = Db::quote_identifier (' COUNT (' column ') ');
  250. *
  251. * Objects passed to this function would be converted to strings.
  252. * [Database_query] objects is compiled and converted to a sub-query.
  253. * All other objects'll 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 the 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 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 $orderColOrder 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::D elete, ' DELETE from users WHERE id = 5 ');
  384. *
  385. * Specifying the type changes the returned result. When using
  386. * Self::select, a [Database_query_result] would be returned.
  387. * Self::insert queries would return the INSERT ID and number of rows.
  388. 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 are 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 the 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 the 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::d ebug (' 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. }
Copy Code
  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.