Access classes for Postgresql db

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

