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.
- /**
- * The generic DB access Class, Entry of all DB access
- * Only PG is supported--201210
- *
- * @author Anthony
- * 2010-2012 reserved
- */
- Class DB {
- Query types
- Const SELECT = 1;
- Const INSERT = 2;
- Const UPDATE = 3;
- Const DELETE = 4;
- /**
- * True Value
- */
- Const T = ' t ';
- /**
- * False Value
- */
- Const F = ' F ';
- /**
- * Null Value
- */
- Const N = ' n/a '; NULL Value
- /**
- * Specilize the value;
- * ' F ' as False, ' t ' as TRUE, ' N/a ' as NULL value
- *
- * @param String $s, orignal Value
- *
- * @return String, specilized value
- */
- public static function Specializevalue ($s) {
- if ($s = = = Self::n) {
- return NULL;
- }
- if ($s = = = Self::t) {
- return True;
- }
- if ($s = = = Self::f) {
- return False;
- }
- return $s;
- }
- /**
- * Batch INSERT INTO table
- * @param String $table _name table name
- * @param Array $cols columns of table
- * @param array $values, values array of data
- * @param string/array $return _cols return column[s], ' ID ' is return by Default,array for multi column
- * @param String $db Instance name of DB Connection
- *
- * @return Resultset return result set of Return_cols
- */
- public static function Insert_batch ($table _name, $cols, $values, $return _cols= ' id ', $db = ' default ') {
- $_sql = ' insert INTO '. Self::quote_table ($table _name, $db). ' ('. Self::quote_column ($cols, $db). ') Values ';
- $_vsql = Array ();
- foreach ($values as $value) {
- $_vsql[] = ' ('. Self::quote ($value). ') ';
- }
- $_sql. = Implode (', ', $_vsql);
- $_sql. = ' returning '. Self::quote_column ($return _cols);
- Return Self::query (Self::select,$_sql)->execute ($db)->as_array ();
- }
- /**
- * Insert into table from Array Data, and return Column[s], ID was return by default
- *
- * @param String $table _name table name
- * @param array $data array data of key value pairs.
- * @param string/array $return _cols return column[s], ' ID ' is return by Default,array for multi column
- * @param String $db Instance name of DB Connection
- *
- * @return Boolean/resultset True If success without return column, False if failed, value of column[s] if Return_cols pres ented.
- */
- public static function Insert_table ($table _name, $data, $return _cols= ' id ', $db = ' default ') {
- if (!is_array ($data)) {
- return false;
- }
- if (Is_null ($return _cols)) {
- $_sql = ' insert INTO '. Self::quote_table ($table _name, $db). ' ('. Self::quote_column (Array_keys ($data), $db). ') VALUES ('.
- Self::quote (Array_values ($data), $db).
- Return Self::query (Self::insert,$_sql)->execute ($DB);
- }
- Specialize value
- $data = Array_map (' Self::specializevalue ', $data);
- if (is_string ($return _cols)) {
- $_sql = ' insert INTO '. Self::quote_table ($table _name, $db). ' ('. Self::quote_column (Array_keys ($data), $db). ') VALUES ('.
- Self::quote (Array_values ($data), $db). " Returning ". $return _cols;
- $id = Self::query (self::select,$_sql)->execute ($db)->get ($return _cols);
- return $id;
- }else{
- if (Is_array ($return _cols)) {
- $ids = Implode (', ', $return _cols);
- $_sql = ' insert INTO '. Self::quote_table ($table _name, $db). ' ('. Self::quote_column (Array_keys ($data), $db). ') VALUES ('.
- Self::quote (Array_values ($data), $db). " Returning ". $ids;
- $r _ids = self::query (self::select,$_sql)->execute ($db)->current ();
- return $r _ids;
- }
- }
- return false;
- }
- /**
- * Update Table data, and compare with reference data
- *
- * @param String $table _name table name
- * @param Integer $id ID of data
- * @param array $data array data of key value pairs.
- * @param Array $refdata Reference data
- * @param String $id _name Column name of ID
- * @param String $db Instance name of DB Connection
- *
- * @return Integer affected Rows,false if failed!
- */
- public static function Update_data ($table _name, $id, $data, $refdata, $id _name= ' id ', $db = ' default ') {
- if (!is_array ($data)) {
- throw new Exception (' Data should be col=>val pairs array ');
- }
- foreach ($data as $k = = $v) {
- if (Is_array ($refdata)) {
- if (Isset ($refdata [$k])) {
- if ($v = = $refdata [$k]) {
- Unset ($data [$k]);
- }
- }
- }elseif (Is_object ($refdata)) {
- if (Isset ($refdata-$k)) {
- if ($v = = $refdata $k) {
- Unset ($data [$k]);
- }
- }
- }else{
- throw new Exception (' Refdata type error ');
- }
- }
- Specialize value
- $data = Array_map (' Self::specializevalue ', $data);
- if (count ($data) >0) {
- Return self::update_table ($table _name, $id, $data, ' id ', $db);
- }else{
- return 0;
- }
- }
- /**
- * Update table with data without checking the referenced data
- *
- * @param String $table _name table name
- * @param Integer $id ID of data
- * @param array $data array data of key value pairs.
- * @param String $id _name Column name of ID
- * @param String $db Instance name of DB Connection
- *
- * @return Integer affected Rows,false if failed!
- */
- public static function Update_table ($table _name, $id, $data, $id _name= ' id ', $db = ' default ') {
- if (!is_array ($data)) {
- return false;
- }
- $_sql = ' Update '. self::quote_table ($table _name, $db). ' Set '. Self::quote_assoicate ($data, ' = ', ', ', $db). ' WHERE '.
- Self::quote_column ($id _name, $db). ' = '. Self::quote ($id, $db);
- Return Self::query (Self::update,$_sql)->execute ($DB);
- }
- /**
- * Quote key value pair of Col = values
- *
- * @param Array $data, Col=>value pairs
- * @param String $concat, default ' = '
- * @param String Delimiter, default ', '
- * @param String Database Instance
- *
- * @return String
- */
- public static function Quote_assoicate ($data, $concat = ' = ', $delimiter = ', ', $db = ' default ') {
- $_sql = ";
- $_sqlarray = Array ();
- foreach ($data as $k = = $v) {
- $_sqlarray[] = Self::quote_column ($k, $db). $concat. Self::quote ($v, $db);
- }
- $_sql = Implode ($delimiter, $_sqlarray);
- return $_sql;
- }
- /**
- * Quote cols
- *
- * @param String $value, the Column[s] name
- * @param String $db, Database Instance Name
- */
- public static function Quote_column ($value, $db = ' default ') {
- if (!is_array ($value)) {
- Return Self::quote_identifier ($value, $db);
- }else{//quote_column Array and implode
- $_qs = Array ();
- foreach ($value as $ele) {
- $_qs[] = Self::quote_column ($ele, $db);
- }
- $_quote_column_string = Implode (', ', $_QS);
- return $_quote_column_string;
- }
- }
- /**
- * Quote the values to escape
- *
- * @param scalar/array $value
- *
- * @return Quote string or array
- */
- public static function quote ($value, $db = ' default ') {
- if (!is_array ($value)) {
- Return Database::instance ($db)->quote ($value);
- }else{//quote Array and implode
- $_qs = Array ();
- foreach ($value as $ele) {
- $_qs[] = Self::quote ($ele, $db);
- }
- $_quotestring = Implode (', ', $_QS);
- return $_quotestring;
- }
- }
- /**
- * Escape string of DB
- *
- * @param string $s table name
- * @param String $db Database Instance Name
- *
- * @return String
- */
- public static function Escape ($s, $db = ' default ') {
- Return Database::instance ($db)->escape ($s);
- }
- /**
- * Quote Table Name
- *
- * @param string $s table name
- * @param String $db Database Instance Name
- *
- * @return String
- */
- public static function quote_table ($s, $db = ' default ') {
- Return Database::instance ($db)->quote_table ($s);
- }
- /**
- * Quote A database identifier, such as a column name.
- *
- * $column = Db::quote_identifier ($column, ' Default ');
- *
- * You can also use SQL methods within identifiers.
- *
- *//The value of "column" would be quoted
- * $column = Db::quote_identifier (' COUNT (' column ') ');
- *
- * Objects passed to this function would be converted to strings.
- * [Database_query] objects is compiled and converted to a sub-query.
- * All other objects'll be converted using the ' __tostring ' method.
- *
- * @param mixed $value any identifier
- * @param String $db, Database instance
- * @return String
- */
- public static function Quote_identifier ($value, $db = ' default ') {
- Return Database::instance ($db)->quote_identifier ($value);
- }
- /**
- * Get Connection for Database instance
- *
- * @param String $db Database Instance Name
- *
- * @return Connection of databse
- */
- public static function getconnection ($db = ' default ') {
- Return Database::instance ($DB)->getconnection ();
- }
- /**
- * Get children of the current record
- *
- * @param String $table table name
- * @param Bollean $RETURNSQL
- * @param Integer $pid Parent Id of table record
- * @param String $idname ID column name
- * @param String $pidname Parent ID column name
- * @param String $db Database Instance Name
- *
- * @return Records of children
- */
- public static function GetChildren ($table, $returnSql = false, $pid = ' 0 ', $idname = ' id ', $pidname = ' pid ', $db = ' default ') {
- $_sql = ' select * from '. Self::quote_table ($table, $db). ' WHERE '. $pidname. ' = '. Self::quote ($pid, $db).
- "and $idname <>". Self::quote ($pid, $db);
- if ($RETURNSQL) {
- return $_sql;
- }
- $_res = Self::query (self::select,$_sql,true)->execute ($db)->as_array ();
- if ($_res) {
- return $_res;
- }else{
- return false;
- }
- }
- /**
- * Tree query for connect by,traverse all the records of Data
- *
- * @param String $tableName tableName
- * @param Boolean $returnSql Return SQL String if TURE
- * @param String $startWith Begin valueof Traverse
- * @param String $idCol ID Column Name
- * @param String $pidCol Parent ID Column Name
- * @param String $orderColOrder Column
- * @param Integer $maxDepth Depth of Traverse,
- * @param Integer $level Start level
- * @param String $delimiter delimiter of branch
- * @param String $db Database Configuration Instance
- *
- * @return record/string return Record array or String of SQL
- */
- public static function Gettree ($tableName, $returnSql =false, $startWith = ' 0 ', $idCol = ' id ', $pidCol = ' pid ', $orderCol = ' Id ', $maxDepth =0, $level = 0, $delimiter = '; ', $db = ' default ') {
- $_funcparas = Array ();
- $_funcparas[] = Self::quote ($tableName, $db); table| View
- $_funcparas[] = Self::quote ($idCol, $db); ID column
- $_funcparas[] = Self::quote ($pidCol, $db); Parent ID Column
- $_funcparas[] = Self::quote ($orderCol, $db); Default Order by ASC
- $_funcparas[] = Self::quote ($startWith, $db); Begin ID
- $_funcparas[] = Self::quote ($maxDepth, $db); Depth of Traverse
- $_funcparas[] = Self::quote ($delimiter, $db); Delimitor of Branch,default '; '
- $_sql = ' select * ' From Connectby ('
- . Implode (', ', $_funcparas). ') '
- . ' As t (id int, PID int, level int, branch text, pos int) ';
- if ($level > 0) {
- $_sql. = ' Where level >= '. Self::quote ($level, $db);
- }
- if ($RETURNSQL) return $_sql;
- $_res = Self::query (self::select,$_sql,true)->execute ($db)->as_array ();
- if ($_res) {
- return $_res;
- }else{
- return false;
- }
- }
- /**
- * Start Transaction
- *
- * @param String $db Instance name of DB
- *
- * @return Result Set
- */
- public static function begin ($db = ' default ') {
- Return Db::query (self::update, "BEGIN")->execute ($DB);
- }
- /**
- * Define SavePoint
- *
- * @param String $savepoint
- *
- * @param String $db
- */
- public static function SavePoint ($savepoint, $db = ' default ') {
- Return Db::query (self::update, "SavePoint". $savepoint)->execute ($DB);
- }
- /**
- * Rollback to SavePoint
- *
- * @param String $savepoint
- *
- * @param String $db Database Instance Name
- */
- public static function Rollpoint ($savepoint, $db = ' default ') {
- Return Db::query (Self::update, "ROLLBACK to". $savepoint)->execute ($DB);
- }
- /**
- * Commit an transaction
- * @param String DB Connection
- */
- public static function commit ($db = ' default ') {
- Return Db::query (self::update, "COMMIT")->execute ($DB);
- }
- public static function rollback ($db = ' default ') {
- Return Db::query (self::update, "ROLLBACK")->execute ($DB);
- }
- /**
- * Create a new [database_query] of the given type.
- *
- *//Create a new SELECT query
- * $query = Db::query (Self::select, ' SELECT * from users ');
- *
- *//Create a new DELETE query
- * $query = Db::query (self::D elete, ' DELETE from users WHERE id = 5 ');
- *
- * Specifying the type changes the returned result. When using
- * Self::select, a [Database_query_result] would be returned.
- * Self::insert queries would return the INSERT ID and number of rows.
- Queries, the number of affected rows is returned.
- *
- * @param integer type:self::select, self::update, etc
- * @param string SQL statement
- * @param Boolean $as _object Return Result Set As Object if True, default FALSE
- * @param array $params Query parameters of SQL, default array ()
- * @param String $stmt _name The query is Prepared Statement if TRUE,
- * Execute Prepared Statement when $param are not NULL
- * Prepare Statement when $param is NULL
- *
- * @return Database_query
- */
- public static function query ($type, $sql = null, $as _object = false, $params = Array (), $stmt _name = null)
- {
- return new Database_query ($type, $sql, $as _object, $params, $stmt _name);
- }
- /**
- * gettting paginated page from orignal SQL
- *
- * @param String $sql SQL query
- * @param UTL Object & $page UTL object of Tempalte
- * @param String $orderBy Order by column, default ' Updated desc '
- * @param String $dataPro Data Property Name, default ' data '
- * @param String $pagePro pagnation frament property Name, default ' pagination '
- * @param array $config pagination Configuration array Overider
- * @param String $db Database Instance Name, default ' default '
- * @param Boolean $as _object Populate Data As Object if True, default True
- * @param String $_paginclass Class Name of pagination
- * @return True if success
- */
- public static function GetPage ($_sql,& $page, $orderBy = ' Updated desc ', $dataPro = ' data ', $pagePro = ' pagination ',
- $config = NULL, $db = ' default ', $as _object= true,$_paginclass= ' pagination ') {
- $_csql = ' Select count (1) as C from ('. $_sql. ') St ';
- $_c = Db::query (self::select,$_csql)->execute ($db)->get (' C ');
- if ($config) {
- $config [' total_items '] = $_c;
- $_pagination = new $_paginclass ($config);
- }else{
- $config = Array ();
- $config [' total_items '] = $_c;
- $_pagination = new $_paginclass ($config);
- }
- $_sql. = ' ORDER by '. $orderBy;
- if ($_pagination->offset) {
- $_sql. = ' Offset ' .$_pagination->offset;
- }
- $_sql. = ' Limit ' .$_pagination->items_per_page;
- $_data = Db::query (Self::select,$_sql, $as _object)->execute ($db)->as_array ();
- if (!$_data) {
- $page->{$dataPro} = false;
- $page->{$pagePro} = false;
- return false;
- }
- $page->{$dataPro} = $_data;
- $page->{$pagePro} = $_pagination;
- return true;
- }
- /**
- * Get all roles of subordinate
- *
- * @param integer $role _id integer User role ID
- * @param Boolean $quote Quote The SQL if ture, reutrn orignal SQL if False
- * @param String $role _table Table of role hierarchy
- * @param Integer $level Start level of the tree traverse
- * @param String $db Database Instance Name
- * @return SQL String
- */
- public static function Getroletreesql ($role _id, $quote = False, $role _table, $level =0, $db = ' default ') {
- $_sql = ' Select id from ('. Self::gettree ($role _table,true, $role _id, ' id ', ' pid ', ' id ',
- 0,//maxdepth
- $level,//level
- '; ', $db). ') Utree ';
- if (! $quote) return $_sql;
- else return ' ('. $_sql. ') ';
- }
- /**
- * Getting SQL String to query Objects of subordinate and owned Objects
- * Child User Role Tree[curt]
- *
- * @param integer $role _id role ID of user
- * @param integer $user _id User ID
- * @param String $role _table Table of role
- * @param Boolean $quote Quote The SQL if ture, reutrn orignal SQL if False
- * @param String $roleCol Role ID column name
- * @param String $ownerCol Owner ID column name
- * @param String $db Database Instance Name
- * @return SQL String
- */
- public static function Getcurtreesql ($role _id, $user _id, $role _table, $quote = True,
- $roleCol = ' role_id ', $ownerCol = ' owner_id ', $db = ' default ') {
- $_sql = '. $roleCol. ' In '. Self::getroletreesql ($role _id,true, $role _table,
- 1,//level start with 1
- $DB). ' or '. $ownerCol. ' = '. Self::quote ($user _id, $db);
- if (! $quote) return $_sql;
- else return ' ('. $_sql. ') ';
- }
- /**
- * Array from the tree query to tree
- *
- * @param Array $eles, the record set from Self::gettree
- * @param String $elename, element name of node
- * @param String $cldname, child node name
- * @param String $delimiter, the delimiter of branch
- *
- * @return Object, Tree object of data
- */
- public static function Array2tree ($eles, $elename, $cldname, $delimiter = '; ') {
- if ($elename = = $cldname) {
- throw new Exception (' Ele name equals cldname! ');
- }
- $rtree = Array ();
- foreach ($eles as $ele) {
- $_branch = $ele->branch;
- Log::d ebug (' branch= '. $_branch);
- The depth in the array
- $_depths = Explode ($delimiter, $_branch);
- if (count ($_depths = = 1)) {
- $_root = $_depths[0];
- }
- $_cur = & $rtree;
- foreach ($_depths as $depth) {
- Create NODE
- if (!isset ($_cur[$cldname])) {
- $_cur[$cldname] = array ();
- }
- if (!isset ($_cur[$cldname [$depth])) {
- $_cur[$cldname] [$depth] = array ();
- $_cur = &$_cur[$cldname] [$depth];
- }else{
- $_cur = &$_cur[$cldname] [$depth];
- }
- }
- $_cur[$elename] = $ele;
- }
- return $rtree [$cldname][$_root];
- }
- }
Copy Code |