[4001] | 1 | <?php
|
---|
| 2 | require_once("tools.php");
|
---|
| 3 |
|
---|
| 4 | /*! manager of data request
|
---|
| 5 | **/
|
---|
| 6 | class DataRequestConfig{
|
---|
| 7 | private $filters; //!< array of filtering rules
|
---|
| 8 | private $relation=false; //!< ID or other element used for linking hierarchy
|
---|
| 9 | private $sort_by; //!< sorting field
|
---|
| 10 | private $start; //!< start of requested data
|
---|
| 11 | private $count; //!< length of requested data
|
---|
| 12 |
|
---|
| 13 | private $user;
|
---|
| 14 | private $version;
|
---|
| 15 |
|
---|
| 16 | //for render_sql
|
---|
| 17 | private $source; //!< souce table or another source destination
|
---|
| 18 | private $fieldset; //!< set of data, which need to be retrieved from source
|
---|
| 19 |
|
---|
| 20 | /*! constructor
|
---|
| 21 |
|
---|
| 22 | @param proto
|
---|
| 23 | DataRequestConfig object, optional, if provided then new request object will copy all properties from provided one
|
---|
| 24 | */
|
---|
| 25 | public function __construct($proto=false){
|
---|
| 26 | if ($proto)
|
---|
| 27 | $this->copy($proto);
|
---|
| 28 | else{
|
---|
| 29 | $start=0;
|
---|
| 30 | $this->filters=array();
|
---|
| 31 | $this->sort_by=array();
|
---|
| 32 | }
|
---|
| 33 | }
|
---|
| 34 |
|
---|
| 35 | /*! copy parameters of source object into self
|
---|
| 36 |
|
---|
| 37 | @param proto
|
---|
| 38 | source object
|
---|
| 39 | */
|
---|
| 40 | public function copy($proto){
|
---|
| 41 | $this->filters =$proto->get_filters();
|
---|
| 42 | $this->sort_by =$proto->get_sort_by();
|
---|
| 43 | $this->count =$proto->get_count();
|
---|
| 44 | $this->start =$proto->get_start();
|
---|
| 45 | $this->source =$proto->get_source();
|
---|
| 46 | $this->fieldset =$proto->get_fieldset();
|
---|
| 47 | $this->relation =$proto->get_relation();
|
---|
| 48 | $this->user = $proto->user;
|
---|
| 49 | $this->version = $proto->version;
|
---|
| 50 | }
|
---|
| 51 |
|
---|
| 52 | /*! convert self to string ( for logs )
|
---|
| 53 | @return
|
---|
| 54 | self as plain string,
|
---|
| 55 | */
|
---|
| 56 | public function __toString(){
|
---|
| 57 | $str="Source:{$this->source}\nFieldset:{$this->fieldset}\nWhere:";
|
---|
| 58 | for ($i=0; $i < sizeof($this->filters); $i++)
|
---|
| 59 | $str.=$this->filters[$i]["name"]." ".$this->filters[$i]["operation"]." ".$this->filters[$i]["value"].";";
|
---|
| 60 | $str.="\nStart:{$this->start}\nCount:{$this->count}\n";
|
---|
| 61 | for ($i=0; $i < sizeof($this->sort_by); $i++)
|
---|
| 62 | $str.=$this->sort_by[$i]["name"]."=".$this->sort_by[$i]["direction"].";";
|
---|
| 63 | $str.="\nRelation:{$this->relation}";
|
---|
| 64 | return $str;
|
---|
| 65 | }
|
---|
| 66 |
|
---|
| 67 | /*! returns set of filtering rules
|
---|
| 68 | @return
|
---|
| 69 | set of filtering rules
|
---|
| 70 | */
|
---|
| 71 | public function get_filters(){
|
---|
| 72 | return $this->filters;
|
---|
| 73 | }
|
---|
| 74 | public function &get_filters_ref(){
|
---|
| 75 | return $this->filters;
|
---|
| 76 | }
|
---|
| 77 | public function set_filters($data){
|
---|
| 78 | $this->filters=$data;
|
---|
| 79 | }
|
---|
| 80 |
|
---|
| 81 |
|
---|
| 82 | public function get_user(){
|
---|
| 83 | return $this->user;
|
---|
| 84 | }
|
---|
| 85 | public function set_user($user){
|
---|
| 86 | $this->user = $user;
|
---|
| 87 | }
|
---|
| 88 | public function get_version(){
|
---|
| 89 | return $this->version;
|
---|
| 90 | }
|
---|
| 91 | public function set_version($version){
|
---|
| 92 | $this->version = $version;
|
---|
| 93 | }
|
---|
| 94 |
|
---|
| 95 | /*! returns list of used fields
|
---|
| 96 | @return
|
---|
| 97 | list of used fields
|
---|
| 98 | */
|
---|
| 99 | public function get_fieldset(){
|
---|
| 100 | return $this->fieldset;
|
---|
| 101 | }
|
---|
| 102 | /*! returns name of source table
|
---|
| 103 | @return
|
---|
| 104 | name of source table
|
---|
| 105 | */
|
---|
| 106 | public function get_source(){
|
---|
| 107 | return $this->source;
|
---|
| 108 | }
|
---|
| 109 | /*! returns set of sorting rules
|
---|
| 110 | @return
|
---|
| 111 | set of sorting rules
|
---|
| 112 | */
|
---|
| 113 | public function get_sort_by(){
|
---|
| 114 | return $this->sort_by;
|
---|
| 115 | }
|
---|
| 116 | public function &get_sort_by_ref(){
|
---|
| 117 | return $this->sort_by;
|
---|
| 118 | }
|
---|
| 119 | public function set_sort_by($data){
|
---|
| 120 | $this->sort_by=$data;
|
---|
| 121 | }
|
---|
| 122 |
|
---|
| 123 | /*! returns start index
|
---|
| 124 | @return
|
---|
| 125 | start index
|
---|
| 126 | */
|
---|
| 127 | public function get_start(){
|
---|
| 128 | return $this->start;
|
---|
| 129 | }
|
---|
| 130 | /*! returns count of requested records
|
---|
| 131 | @return
|
---|
| 132 | count of requested records
|
---|
| 133 | */
|
---|
| 134 | public function get_count(){
|
---|
| 135 | return $this->count;
|
---|
| 136 | }
|
---|
| 137 | /*! returns name of relation id
|
---|
| 138 | @return
|
---|
| 139 | relation id name
|
---|
| 140 | */
|
---|
| 141 | public function get_relation(){
|
---|
| 142 | return $this->relation;
|
---|
| 143 | }
|
---|
| 144 |
|
---|
| 145 | /*! sets sorting rule
|
---|
| 146 |
|
---|
| 147 | @param field
|
---|
| 148 | name of column
|
---|
| 149 | @param order
|
---|
| 150 | direction of sorting
|
---|
| 151 | */
|
---|
| 152 | public function set_sort($field,$order=false){
|
---|
| 153 | if (!$field && !$order)
|
---|
| 154 | $this->sort_by=array();
|
---|
| 155 | else{
|
---|
| 156 | $order=strtolower($order)=="asc"?"ASC":"DESC";
|
---|
| 157 | $this->sort_by[]=array("name"=>$field,"direction" => $order);
|
---|
| 158 | }
|
---|
| 159 | }
|
---|
| 160 | /*! sets filtering rule
|
---|
| 161 |
|
---|
| 162 | @param field
|
---|
| 163 | name of column
|
---|
| 164 | @param value
|
---|
| 165 | value for filtering
|
---|
| 166 | @param operation
|
---|
| 167 | operation for filtering, optional , LIKE by default
|
---|
| 168 | */
|
---|
| 169 | public function set_filter($field,$value,$operation=false){
|
---|
| 170 | array_push($this->filters,array("name"=>$field,"value"=>$value,"operation"=>$operation));
|
---|
| 171 | }
|
---|
| 172 |
|
---|
| 173 | /*! sets list of used fields
|
---|
| 174 |
|
---|
| 175 | @param value
|
---|
| 176 | list of used fields
|
---|
| 177 | */
|
---|
| 178 | public function set_fieldset($value){
|
---|
| 179 | $this->fieldset=$value;
|
---|
| 180 | }
|
---|
| 181 | /*! sets name of source table
|
---|
| 182 |
|
---|
| 183 | @param value
|
---|
| 184 | name of source table
|
---|
| 185 | */
|
---|
| 186 | public function set_source($value){
|
---|
| 187 | $this->source=trim($value);
|
---|
| 188 | if (!$this->source) throw new Exception("Source of data can't be empty");
|
---|
| 189 | }
|
---|
| 190 | /*! sets data limits
|
---|
| 191 |
|
---|
| 192 | @param start
|
---|
| 193 | start index
|
---|
| 194 | @param count
|
---|
| 195 | requested count of data
|
---|
| 196 | */
|
---|
| 197 | public function set_limit($start,$count){
|
---|
| 198 | $this->start=$start;
|
---|
| 199 | $this->count=$count;
|
---|
| 200 | }
|
---|
| 201 | /*! sets name of relation id
|
---|
| 202 |
|
---|
| 203 | @param value
|
---|
| 204 | name of relation id field
|
---|
| 205 | */
|
---|
| 206 | public function set_relation($value){
|
---|
| 207 | $this->relation=$value;
|
---|
| 208 | }
|
---|
| 209 | /*! parse incoming sql, to fill other properties
|
---|
| 210 |
|
---|
| 211 | @param sql
|
---|
| 212 | incoming sql string
|
---|
| 213 | */
|
---|
| 214 | public function parse_sql($sql){
|
---|
| 215 | $sql= preg_replace("/[ \n\t]+limit[\n ,0-9]/i","",$sql);
|
---|
| 216 |
|
---|
| 217 | $data = preg_split("/[ \n\t]+\\_from\\_/i",$sql,2);
|
---|
| 218 | if (count($data)!=2)
|
---|
| 219 | $data = preg_split("/[ \n\t]+from/i",$sql,2);
|
---|
| 220 | $this->fieldset = preg_replace("/^[\s]*select/i","",$data[0],1);
|
---|
| 221 |
|
---|
| 222 | $table_data = preg_split("/[ \n\t]+where/i",$data[1],2);
|
---|
| 223 | if (sizeof($table_data)>1){ //where construction exists
|
---|
| 224 | $this->set_source($table_data[0]);
|
---|
| 225 | $where_data = preg_split("/[ \n\t]+order[ ]+by/i",$table_data[1],2);
|
---|
| 226 | $this->filters[]=$where_data[0];
|
---|
| 227 | if (sizeof($where_data)==1) return; //end of line detected
|
---|
| 228 | $data=$where_data[1];
|
---|
| 229 | } else {
|
---|
| 230 | $table_data = preg_split("/[ \n\t]+order[ ]+by/i",$table_data[0],2);
|
---|
| 231 | $this->set_source($table_data[0]);
|
---|
| 232 | if (sizeof($table_data)==1) return; //end of line detected
|
---|
| 233 | $data=$table_data[1];
|
---|
| 234 | }
|
---|
| 235 |
|
---|
| 236 | if (trim($data)){ //order by construction exists
|
---|
| 237 | $s_data = preg_split("/\\,/",trim($data));
|
---|
| 238 | for ($i=0; $i < count($s_data); $i++) {
|
---|
| 239 | $data=preg_split("/[ ]+/",trim($s_data[$i]),2);
|
---|
| 240 | $this->set_sort($data[0],$data[1]);
|
---|
| 241 | }
|
---|
| 242 |
|
---|
| 243 | }
|
---|
| 244 | }
|
---|
| 245 | }
|
---|
| 246 |
|
---|
| 247 | /*! manager of data configuration
|
---|
| 248 | **/
|
---|
| 249 | class DataConfig{
|
---|
| 250 | public $id;////!< name of ID field
|
---|
| 251 | public $relation_id;//!< name or relation ID field
|
---|
| 252 | public $text;//!< array of text fields
|
---|
| 253 | public $data;//!< array of all known fields , fields which exists only in this collection will not be included in dataprocessor's operations
|
---|
| 254 |
|
---|
| 255 |
|
---|
| 256 | /*! converts self to the string, for logging purposes
|
---|
| 257 | **/
|
---|
| 258 | public function __toString(){
|
---|
| 259 | $str="ID:{$this->id['db_name']}(ID:{$this->id['name']})\n";
|
---|
| 260 | $str.="Relation ID:{$this->relation_id['db_name']}({$this->relation_id['name']})\n";
|
---|
| 261 | $str.="Data:";
|
---|
| 262 | for ($i=0; $i<sizeof($this->text); $i++)
|
---|
| 263 | $str.="{$this->text[$i]['db_name']}({$this->text[$i]['name']}),";
|
---|
| 264 |
|
---|
| 265 | $str.="\nExtra:";
|
---|
| 266 | for ($i=0; $i<sizeof($this->data); $i++)
|
---|
| 267 | $str.="{$this->data[$i]['db_name']}({$this->data[$i]['name']}),";
|
---|
| 268 |
|
---|
| 269 | return $str;
|
---|
| 270 | }
|
---|
| 271 |
|
---|
| 272 | /*! removes un-used fields from configuration
|
---|
| 273 | @param name
|
---|
| 274 | name of field , which need to be preserved
|
---|
| 275 | */
|
---|
| 276 | public function minimize($name){
|
---|
| 277 | for ($i=0; $i < sizeof($this->text); $i++){
|
---|
| 278 | if ($this->text[$i]["name"]==$name){
|
---|
| 279 | $this->text[$i]["name"]="value";
|
---|
| 280 | $this->data=array($this->text[$i]);
|
---|
| 281 | $this->text=array($this->text[$i]);
|
---|
| 282 | return;
|
---|
| 283 | }
|
---|
| 284 | }
|
---|
| 285 | throw new Exception("Incorrect dataset minimization, master field not found.");
|
---|
| 286 | }
|
---|
| 287 |
|
---|
| 288 | public function limit_fields($data){
|
---|
| 289 | if (isset($this->full_field_list))
|
---|
| 290 | $this->restore_fields();
|
---|
| 291 | $this->full_field_list = $this->text;
|
---|
| 292 | $this->text = array();
|
---|
| 293 |
|
---|
| 294 | for ($i=0; $i < sizeof($this->full_field_list); $i++) {
|
---|
| 295 | if (array_key_exists($this->full_field_list[$i]["name"],$data))
|
---|
| 296 | $this->text[] = $this->full_field_list[$i];
|
---|
| 297 | }
|
---|
| 298 | }
|
---|
| 299 |
|
---|
| 300 | public function restore_fields(){
|
---|
| 301 | if (isset($this->full_field_list))
|
---|
| 302 | $this->text = $this->full_field_list;
|
---|
| 303 | }
|
---|
| 304 |
|
---|
| 305 | /*! initialize inner state by parsing configuration parameters
|
---|
| 306 |
|
---|
| 307 | @param id
|
---|
| 308 | name of id field
|
---|
| 309 | @param fields
|
---|
| 310 | name of data field(s)
|
---|
| 311 | @param extra
|
---|
| 312 | name of extra field(s)
|
---|
| 313 | @param relation
|
---|
| 314 | name of relation field
|
---|
| 315 |
|
---|
| 316 | */
|
---|
| 317 | public function init($id,$fields,$extra,$relation){
|
---|
| 318 | $this->id = $this->parse($id,false);
|
---|
| 319 | $this->text = $this->parse($fields,true);
|
---|
| 320 | $this->data = array_merge($this->text,$this->parse($extra,true));
|
---|
| 321 | $this->relation_id = $this->parse($relation,false);
|
---|
| 322 | }
|
---|
| 323 |
|
---|
| 324 | /*! parse configuration string
|
---|
| 325 |
|
---|
| 326 | @param key
|
---|
| 327 | key string from configuration
|
---|
| 328 | @param mode
|
---|
| 329 | multi names flag
|
---|
| 330 | @return
|
---|
| 331 | parsed field name object
|
---|
| 332 | */
|
---|
| 333 | private function parse($key,$mode){
|
---|
| 334 | if ($mode){
|
---|
| 335 | if (!$key) return array();
|
---|
| 336 | $key=explode(",",$key);
|
---|
| 337 | for ($i=0; $i < sizeof($key); $i++)
|
---|
| 338 | $key[$i]=$this->parse($key[$i],false);
|
---|
| 339 | return $key;
|
---|
| 340 | }
|
---|
| 341 | $key=explode("(",$key);
|
---|
| 342 | $data=array("db_name"=>trim($key[0]), "name"=>trim($key[0]));
|
---|
| 343 | if (sizeof($key)>1)
|
---|
| 344 | $data["name"]=substr(trim($key[1]),0,-1);
|
---|
| 345 | return $data;
|
---|
| 346 | }
|
---|
| 347 |
|
---|
| 348 | /*! constructor
|
---|
| 349 | init public collectons
|
---|
| 350 | @param proto
|
---|
| 351 | DataConfig object used as prototype for new one, optional
|
---|
| 352 | */
|
---|
| 353 | public function __construct($proto=false){
|
---|
| 354 | if ($proto!==false)
|
---|
| 355 | $this->copy($proto);
|
---|
| 356 | else {
|
---|
| 357 | $this->text=array();
|
---|
| 358 | $this->data=array();
|
---|
| 359 | $this->id=array("name"=>"dhx_auto_id", "db_name"=>"dhx_auto_id");
|
---|
| 360 | $this->relation_id=array("name"=>"", "db_name"=>"");
|
---|
| 361 | }
|
---|
| 362 | }
|
---|
| 363 |
|
---|
| 364 | /*! copy properties from source object
|
---|
| 365 |
|
---|
| 366 | @param proto
|
---|
| 367 | source object
|
---|
| 368 | */
|
---|
| 369 | public function copy($proto){
|
---|
| 370 | $this->id = $proto->id;
|
---|
| 371 | $this->relation_id = $proto->relation_id;
|
---|
| 372 | $this->text = $proto->text;
|
---|
| 373 | $this->data = $proto->data;
|
---|
| 374 | }
|
---|
| 375 |
|
---|
| 376 | /*! returns list of data fields (db_names)
|
---|
| 377 | @return
|
---|
| 378 | list of data fields ( ready to be used in SQL query )
|
---|
| 379 | */
|
---|
| 380 | public function db_names_list($db){
|
---|
| 381 | $out=array();
|
---|
| 382 | if ($this->id["db_name"])
|
---|
| 383 | array_push($out,$db->escape_name($this->id["db_name"]));
|
---|
| 384 | if ($this->relation_id["db_name"])
|
---|
| 385 | array_push($out,$db->escape_name($this->relation_id["db_name"]));
|
---|
| 386 |
|
---|
| 387 | for ($i=0; $i < sizeof($this->data); $i++){
|
---|
| 388 | if ($this->data[$i]["db_name"]!=$this->data[$i]["name"])
|
---|
| 389 | $out[]=$db->escape_name($this->data[$i]["db_name"])." as ".$this->data[$i]["name"];
|
---|
| 390 | else
|
---|
| 391 | $out[]=$db->escape_name($this->data[$i]["db_name"]);
|
---|
| 392 | }
|
---|
| 393 |
|
---|
| 394 | return $out;
|
---|
| 395 | }
|
---|
| 396 |
|
---|
| 397 | /*! add field to dataset config ($text collection)
|
---|
| 398 |
|
---|
| 399 | added field will be used in all auto-generated queries
|
---|
| 400 | @param name
|
---|
| 401 | name of field
|
---|
| 402 | @param aliase
|
---|
| 403 | aliase of field, optional
|
---|
| 404 | */
|
---|
| 405 | public function add_field($name,$aliase=false){
|
---|
| 406 | if ($aliase===false) $aliase=$name;
|
---|
| 407 |
|
---|
| 408 | //adding to list of data-active fields
|
---|
| 409 | if ($this->id["db_name"]==$name || $this->relation_id["db_name"] == $name){
|
---|
| 410 | LogMaster::log("Field name already used as ID, be sure that it is really necessary.");
|
---|
| 411 | }
|
---|
| 412 | if ($this->is_field($name,$this->text)!=-1)
|
---|
| 413 | throw new Exception('Data field already registered: '.$name);
|
---|
| 414 | array_push($this->text,array("db_name"=>$name,"name"=>$aliase));
|
---|
| 415 |
|
---|
| 416 | //adding to list of all fields as well
|
---|
| 417 | if ($this->is_field($name,$this->data)==-1)
|
---|
| 418 | array_push($this->data,array("db_name"=>$name,"name"=>$aliase));
|
---|
| 419 |
|
---|
| 420 | }
|
---|
| 421 |
|
---|
| 422 | /*! remove field from dataset config ($text collection)
|
---|
| 423 |
|
---|
| 424 | removed field will be excluded from all auto-generated queries
|
---|
| 425 | @param name
|
---|
| 426 | name of field, or aliase of field
|
---|
| 427 | */
|
---|
| 428 | public function remove_field($name){
|
---|
| 429 | $ind = $this->is_field($name);
|
---|
| 430 | if ($ind==-1) throw new Exception('There was no such data field registered as: '.$name);
|
---|
| 431 | array_splice($this->text,$ind,1);
|
---|
| 432 | //we not deleting field from $data collection, so it will not be included in data operation, but its data still available
|
---|
| 433 | }
|
---|
| 434 |
|
---|
| 435 | /*! check if field is a part of dataset
|
---|
| 436 |
|
---|
| 437 | @param name
|
---|
| 438 | name of field
|
---|
| 439 | @param collection
|
---|
| 440 | collection, against which check will be done, $text collection by default
|
---|
| 441 | @return
|
---|
| 442 | returns true if field already a part of dataset, otherwise returns true
|
---|
| 443 | */
|
---|
| 444 | private function is_field($name,$collection = false){
|
---|
| 445 | if (!$collection)
|
---|
| 446 | $collection=$this->text;
|
---|
| 447 |
|
---|
| 448 | for ($i=0; $i<sizeof($collection); $i++)
|
---|
| 449 | if ($collection[$i]["name"] == $name || $collection[$i]["db_name"] == $name) return $i;
|
---|
| 450 | return -1;
|
---|
| 451 | }
|
---|
| 452 |
|
---|
| 453 |
|
---|
| 454 | }
|
---|
| 455 |
|
---|
| 456 | /*! Base abstraction class, used for data operations
|
---|
| 457 | Class abstract access to data, it is a base class to all DB wrappers
|
---|
| 458 | **/
|
---|
| 459 | abstract class DataWrapper{
|
---|
| 460 | protected $connection;
|
---|
| 461 | protected $config;//!< DataConfig instance
|
---|
| 462 | /*! constructor
|
---|
| 463 | @param connection
|
---|
| 464 | DB connection
|
---|
| 465 | @param config
|
---|
| 466 | DataConfig instance
|
---|
| 467 | */
|
---|
| 468 | public function __construct($connection,$config){
|
---|
| 469 | $this->config=$config;
|
---|
| 470 | $this->connection=$connection;
|
---|
| 471 | }
|
---|
| 472 |
|
---|
| 473 | /*! insert record in storage
|
---|
| 474 |
|
---|
| 475 | @param data
|
---|
| 476 | DataAction object
|
---|
| 477 | @param source
|
---|
| 478 | DataRequestConfig object
|
---|
| 479 | */
|
---|
| 480 | abstract function insert($data,$source);
|
---|
| 481 |
|
---|
| 482 | /*! delete record from storage
|
---|
| 483 |
|
---|
| 484 | @param data
|
---|
| 485 | DataAction object
|
---|
| 486 | @param source
|
---|
| 487 | DataRequestConfig object
|
---|
| 488 | */
|
---|
| 489 | abstract function delete($data,$source);
|
---|
| 490 |
|
---|
| 491 | /*! update record in storage
|
---|
| 492 |
|
---|
| 493 | @param data
|
---|
| 494 | DataAction object
|
---|
| 495 | @param source
|
---|
| 496 | DataRequestConfig object
|
---|
| 497 | */
|
---|
| 498 | abstract function update($data,$source);
|
---|
| 499 |
|
---|
| 500 | /*! select record from storage
|
---|
| 501 |
|
---|
| 502 | @param source
|
---|
| 503 | DataRequestConfig object
|
---|
| 504 | */
|
---|
| 505 | abstract function select($source);
|
---|
| 506 |
|
---|
| 507 | /*! get size of storage
|
---|
| 508 |
|
---|
| 509 | @param source
|
---|
| 510 | DataRequestConfig object
|
---|
| 511 | */
|
---|
| 512 | abstract function get_size($source);
|
---|
| 513 |
|
---|
| 514 | /*! get all variations of field in storage
|
---|
| 515 |
|
---|
| 516 | @param name
|
---|
| 517 | name of field
|
---|
| 518 | @param source
|
---|
| 519 | DataRequestConfig object
|
---|
| 520 | */
|
---|
| 521 | abstract function get_variants($name,$source);
|
---|
| 522 |
|
---|
| 523 | /*! checks if there is a custom sql string for specified db operation
|
---|
| 524 |
|
---|
| 525 | @param name
|
---|
| 526 | name of DB operation
|
---|
| 527 | @param data
|
---|
| 528 | hash of data
|
---|
| 529 | @return
|
---|
| 530 | sql string
|
---|
| 531 | */
|
---|
| 532 | public function get_sql($name,$data){
|
---|
| 533 | return ""; //custom sql not supported by default
|
---|
| 534 | }
|
---|
| 535 |
|
---|
| 536 | /*! begins DB transaction
|
---|
| 537 | */
|
---|
| 538 | public function begin_transaction(){
|
---|
| 539 | throw new Exception("Data wrapper not supports transactions.");
|
---|
| 540 | }
|
---|
| 541 | /*! commits DB transaction
|
---|
| 542 | */
|
---|
| 543 | public function commit_transaction(){
|
---|
| 544 | throw new Exception("Data wrapper not supports transactions.");
|
---|
| 545 | }
|
---|
| 546 | /*! rollbacks DB transaction
|
---|
| 547 | */
|
---|
| 548 | public function rollback_transaction(){
|
---|
| 549 | throw new Exception("Data wrapper not supports transactions.");
|
---|
| 550 | }
|
---|
| 551 | }
|
---|
| 552 |
|
---|
| 553 | /*! Common database abstraction class
|
---|
| 554 | Class provides base set of methods to access and change data in DB, class used as a base for DB-specific wrappers
|
---|
| 555 | **/
|
---|
| 556 | abstract class DBDataWrapper extends DataWrapper{
|
---|
| 557 | private $transaction = false; //!< type of transaction
|
---|
| 558 | private $sequence=false;//!< sequence name
|
---|
| 559 | private $sqls = array();//!< predefined sql actions
|
---|
| 560 |
|
---|
| 561 |
|
---|
| 562 | /*! assign named sql query
|
---|
| 563 | @param name
|
---|
| 564 | name of sql query
|
---|
| 565 | @param data
|
---|
| 566 | sql query text
|
---|
| 567 | */
|
---|
| 568 | public function attach($name,$data){
|
---|
| 569 | $name=strtolower($name);
|
---|
| 570 | $this->sqls[$name]=$data;
|
---|
| 571 | }
|
---|
| 572 | /*! replace vars in sql string with actual values
|
---|
| 573 |
|
---|
| 574 | @param matches
|
---|
| 575 | array of field name matches
|
---|
| 576 | @return
|
---|
| 577 | value for the var name
|
---|
| 578 | */
|
---|
| 579 | public function get_sql_callback($matches){
|
---|
| 580 | return $this->escape($this->temp->get_value($matches[1]));
|
---|
| 581 | }
|
---|
| 582 | public function get_sql($name,$data){
|
---|
| 583 | $name=strtolower($name);
|
---|
| 584 | if (!array_key_exists($name,$this->sqls)) return "";
|
---|
| 585 |
|
---|
| 586 |
|
---|
| 587 | $str = $this->sqls[$name];
|
---|
| 588 | $this->temp = $data; //dirty
|
---|
| 589 | $str = preg_replace_callback('|\{([^}]+)\}|',array($this,"get_sql_callback"),$str);
|
---|
| 590 | unset ($this->temp); //dirty
|
---|
| 591 | return $str;
|
---|
| 592 | }
|
---|
| 593 |
|
---|
| 594 | public function insert($data,$source){
|
---|
| 595 | $sql=$this->insert_query($data,$source);
|
---|
| 596 | $this->query($sql);
|
---|
| 597 | $data->success($this->get_new_id());
|
---|
| 598 | }
|
---|
| 599 | public function delete($data,$source){
|
---|
| 600 | $sql=$this->delete_query($data,$source);
|
---|
| 601 | $this->query($sql);
|
---|
| 602 | $data->success();
|
---|
| 603 | }
|
---|
| 604 | public function update($data,$source){
|
---|
| 605 | $sql=$this->update_query($data,$source);
|
---|
| 606 | $this->query($sql);
|
---|
| 607 | $data->success();
|
---|
| 608 | }
|
---|
| 609 | public function select($source){
|
---|
| 610 | $select=$source->get_fieldset();
|
---|
| 611 | if (!$select){
|
---|
| 612 | $select=$this->config->db_names_list($this);
|
---|
| 613 | $select = implode(",",$select);
|
---|
| 614 | }
|
---|
| 615 |
|
---|
| 616 | $where=$this->build_where($source->get_filters(),$source->get_relation());
|
---|
| 617 | $sort=$this->build_order($source->get_sort_by());
|
---|
| 618 |
|
---|
| 619 | return $this->query($this->select_query($select,$source->get_source(),$where,$sort,$source->get_start(),$source->get_count()));
|
---|
| 620 | }
|
---|
| 621 | public function get_size($source){
|
---|
| 622 | $count = new DataRequestConfig($source);
|
---|
| 623 |
|
---|
| 624 | $count->set_fieldset("COUNT(*) as DHX_COUNT ");
|
---|
| 625 | $count->set_sort(null);
|
---|
| 626 | $count->set_limit(0,0);
|
---|
| 627 |
|
---|
| 628 | $res=$this->select($count);
|
---|
| 629 | $data=$this->get_next($res);
|
---|
| 630 | if (array_key_exists("DHX_COUNT",$data)) return $data["DHX_COUNT"];
|
---|
| 631 | else return $data["dhx_count"]; //postgresql
|
---|
| 632 | }
|
---|
| 633 | public function get_variants($name,$source){
|
---|
| 634 | $count = new DataRequestConfig($source);
|
---|
| 635 | $count->set_fieldset("DISTINCT ".$this->escape_name($name)." as value");
|
---|
| 636 | $count->set_sort(null);
|
---|
| 637 | $count->set_limit(0,0);
|
---|
| 638 |
|
---|
| 639 | return $this->select($count);
|
---|
| 640 | }
|
---|
| 641 |
|
---|
| 642 | public function sequence($sec){
|
---|
| 643 | $this->sequence=$sec;
|
---|
| 644 | }
|
---|
| 645 |
|
---|
| 646 |
|
---|
| 647 | /*! create an sql string for filtering rules
|
---|
| 648 |
|
---|
| 649 | @param rules
|
---|
| 650 | set of filtering rules
|
---|
| 651 | @param relation
|
---|
| 652 | name of relation id field
|
---|
| 653 | @return
|
---|
| 654 | sql string with filtering rules
|
---|
| 655 | */
|
---|
| 656 | protected function build_where($rules,$relation=false){
|
---|
| 657 | $sql=array();
|
---|
| 658 | for ($i=0; $i < sizeof($rules); $i++)
|
---|
| 659 | if (is_string($rules[$i]))
|
---|
| 660 | array_push($sql,$rules[$i]);
|
---|
| 661 | else
|
---|
| 662 | if ($rules[$i]["value"]!=""){
|
---|
| 663 | if (!$rules[$i]["operation"])
|
---|
| 664 | array_push($sql,$this->escape_name($rules[$i]["name"])." LIKE '%".$this->escape($rules[$i]["value"])."%'");
|
---|
| 665 | else
|
---|
| 666 | array_push($sql,$this->escape_name($rules[$i]["name"])." ".$rules[$i]["operation"]." '".$this->escape($rules[$i]["value"])."'");
|
---|
| 667 | }
|
---|
| 668 | if ($relation!==false)
|
---|
| 669 | array_push($sql,$this->escape_name($this->config->relation_id["db_name"])." = '".$this->escape($relation)."'");
|
---|
| 670 | return implode(" AND ",$sql);
|
---|
| 671 | }
|
---|
| 672 | /*! convert sorting rules to sql string
|
---|
| 673 |
|
---|
| 674 | @param by
|
---|
| 675 | set of sorting rules
|
---|
| 676 | @return
|
---|
| 677 | sql string for set of sorting rules
|
---|
| 678 | */
|
---|
| 679 | protected function build_order($by){
|
---|
| 680 | if (!sizeof($by)) return "";
|
---|
| 681 | $out = array();
|
---|
| 682 | for ($i=0; $i < sizeof($by); $i++)
|
---|
| 683 | if ($by[$i]["name"])
|
---|
| 684 | $out[]=$this->escape_name($by[$i]["name"])." ".$by[$i]["direction"];
|
---|
| 685 | return implode(",",$out);
|
---|
| 686 | }
|
---|
| 687 |
|
---|
| 688 | /*! generates sql code for select operation
|
---|
| 689 |
|
---|
| 690 | @param select
|
---|
| 691 | list of fields in select
|
---|
| 692 | @param from
|
---|
| 693 | table name
|
---|
| 694 | @param where
|
---|
| 695 | list of filtering rules
|
---|
| 696 | @param sort
|
---|
| 697 | list of sorting rules
|
---|
| 698 | @param start
|
---|
| 699 | start index of fetching
|
---|
| 700 | @param count
|
---|
| 701 | count of records to fetch
|
---|
| 702 | @return
|
---|
| 703 | sql string for select operation
|
---|
| 704 | */
|
---|
| 705 | protected function select_query($select,$from,$where,$sort,$start,$count){
|
---|
| 706 | $sql="SELECT ".$select." FROM ".$from;
|
---|
| 707 | if ($where) $sql.=" WHERE ".$where;
|
---|
| 708 | if ($sort) $sql.=" ORDER BY ".$sort;
|
---|
| 709 | if ($start || $count) $sql.=" LIMIT ".$start.",".$count;
|
---|
| 710 | return $sql;
|
---|
| 711 | }
|
---|
| 712 | /*! generates update sql
|
---|
| 713 |
|
---|
| 714 | @param data
|
---|
| 715 | DataAction object
|
---|
| 716 | @param request
|
---|
| 717 | DataRequestConfig object
|
---|
| 718 | @return
|
---|
| 719 | sql string, which updates record with provided data
|
---|
| 720 | */
|
---|
| 721 | protected function update_query($data,$request){
|
---|
| 722 | $sql="UPDATE ".$request->get_source()." SET ";
|
---|
| 723 | $temp=array();
|
---|
| 724 | for ($i=0; $i < sizeof($this->config->text); $i++) {
|
---|
| 725 | $step=$this->config->text[$i];
|
---|
| 726 |
|
---|
| 727 | if ($data->get_value($step["name"])===Null)
|
---|
| 728 | $step_value ="Null";
|
---|
| 729 | else
|
---|
| 730 | $step_value = "'".$this->escape($data->get_value($step["name"]))."'";
|
---|
| 731 | $temp[$i]= $this->escape_name($step["db_name"])."=". $step_value;
|
---|
| 732 | }
|
---|
| 733 | if ($relation = $this->config->relation_id["db_name"]){
|
---|
| 734 | $temp[]= $this->escape_name($relation)."='".$this->escape($data->get_value($relation))."'";
|
---|
| 735 | }
|
---|
| 736 | $sql.=implode(",",$temp)." WHERE ".$this->escape_name($this->config->id["db_name"])."='".$this->escape($data->get_id())."'";
|
---|
| 737 |
|
---|
| 738 | //if we have limited set - set constraints
|
---|
| 739 | $where=$this->build_where($request->get_filters(),$request->get_relation());
|
---|
| 740 | if ($where) $sql.=" AND (".$where.")";
|
---|
| 741 |
|
---|
| 742 | return $sql;
|
---|
| 743 | }
|
---|
| 744 |
|
---|
| 745 | /*! generates delete sql
|
---|
| 746 |
|
---|
| 747 | @param data
|
---|
| 748 | DataAction object
|
---|
| 749 | @param request
|
---|
| 750 | DataRequestConfig object
|
---|
| 751 | @return
|
---|
| 752 | sql string, which delete record
|
---|
| 753 | */
|
---|
| 754 | protected function delete_query($data,$request){
|
---|
| 755 | $sql="DELETE FROM ".$request->get_source();
|
---|
| 756 | $sql.=" WHERE ".$this->escape_name($this->config->id["db_name"])."='".$this->escape($data->get_id())."'";
|
---|
| 757 |
|
---|
| 758 | //if we have limited set - set constraints
|
---|
| 759 | $where=$this->build_where($request->get_filters(),$request->get_relation());
|
---|
| 760 | if ($where) $sql.=" AND (".$where.")";
|
---|
| 761 |
|
---|
| 762 | return $sql;
|
---|
| 763 | }
|
---|
| 764 |
|
---|
| 765 | /*! generates insert sql
|
---|
| 766 |
|
---|
| 767 | @param data
|
---|
| 768 | DataAction object
|
---|
| 769 | @param request
|
---|
| 770 | DataRequestConfig object
|
---|
| 771 | @return
|
---|
| 772 | sql string, which inserts new record with provided data
|
---|
| 773 | */
|
---|
| 774 | protected function insert_query($data,$request){
|
---|
| 775 | $temp_n=array();
|
---|
| 776 | $temp_v=array();
|
---|
| 777 | foreach($this->config->text as $k => $v){
|
---|
| 778 | $temp_n[$k]=$this->escape_name($v["db_name"]);
|
---|
| 779 | if ($data->get_value($v["name"])===Null)
|
---|
| 780 | $temp_v[$k]="Null";
|
---|
| 781 | else
|
---|
| 782 | $temp_v[$k]="'".$this->escape($data->get_value($v["name"]))."'";
|
---|
| 783 | }
|
---|
| 784 | if ($relation = $this->config->relation_id["db_name"]){
|
---|
| 785 | $temp_n[]=$this->escape_name($relation);
|
---|
| 786 | $temp_v[]="'".$this->escape($data->get_value($relation))."'";
|
---|
| 787 | }
|
---|
| 788 | if ($this->sequence){
|
---|
| 789 | $temp_n[]=$this->escape_name($this->config->id["db_name"]);
|
---|
| 790 | $temp_v[]=$this->sequence;
|
---|
| 791 | }
|
---|
| 792 |
|
---|
| 793 | $sql="INSERT INTO ".$request->get_source()."(".implode(",",$temp_n).") VALUES (".implode(",",$temp_v).")";
|
---|
| 794 |
|
---|
| 795 | return $sql;
|
---|
| 796 | }
|
---|
| 797 |
|
---|
| 798 | /*! sets the transaction mode, used by dataprocessor
|
---|
| 799 |
|
---|
| 800 | @param mode
|
---|
| 801 | mode name
|
---|
| 802 | */
|
---|
| 803 | public function set_transaction_mode($mode){
|
---|
| 804 | if ($mode!="none" && $mode!="global" && $mode!="record")
|
---|
| 805 | throw new Exception("Unknown transaction mode");
|
---|
| 806 | $this->transaction=$mode;
|
---|
| 807 | }
|
---|
| 808 | /*! returns true if global transaction mode was specified
|
---|
| 809 | @return
|
---|
| 810 | true if global transaction mode was specified
|
---|
| 811 | */
|
---|
| 812 | public function is_global_transaction(){
|
---|
| 813 | return $this->transaction == "global";
|
---|
| 814 | }
|
---|
| 815 | /*! returns true if record transaction mode was specified
|
---|
| 816 | @return
|
---|
| 817 | true if record transaction mode was specified
|
---|
| 818 | */
|
---|
| 819 | public function is_record_transaction(){
|
---|
| 820 | return $this->transaction == "record";
|
---|
| 821 | }
|
---|
| 822 |
|
---|
| 823 |
|
---|
| 824 | public function begin_transaction(){
|
---|
| 825 | $this->query("BEGIN");
|
---|
| 826 | }
|
---|
| 827 | public function commit_transaction(){
|
---|
| 828 | $this->query("COMMIT");
|
---|
| 829 | }
|
---|
| 830 | public function rollback_transaction(){
|
---|
| 831 | $this->query("ROLLBACK");
|
---|
| 832 | }
|
---|
| 833 |
|
---|
| 834 | /*! exec sql string
|
---|
| 835 |
|
---|
| 836 | @param sql
|
---|
| 837 | sql string
|
---|
| 838 | @return
|
---|
| 839 | sql result set
|
---|
| 840 | */
|
---|
| 841 | abstract protected function query($sql);
|
---|
| 842 | /*! returns next record from result set
|
---|
| 843 |
|
---|
| 844 | @param res
|
---|
| 845 | sql result set
|
---|
| 846 | @return
|
---|
| 847 | hash of data
|
---|
| 848 | */
|
---|
| 849 | abstract public function get_next($res);
|
---|
| 850 | /*! returns new id value, for newly inserted row
|
---|
| 851 | @return
|
---|
| 852 | new id value, for newly inserted row
|
---|
| 853 | */
|
---|
| 854 | abstract protected function get_new_id();
|
---|
| 855 | /*! escape data to prevent sql injections
|
---|
| 856 | @param data
|
---|
| 857 | unescaped data
|
---|
| 858 | @return
|
---|
| 859 | escaped data
|
---|
| 860 | */
|
---|
| 861 | abstract public function escape($data);
|
---|
| 862 |
|
---|
| 863 | /*! escape field name to prevent sql reserved words conflict
|
---|
| 864 | @param data
|
---|
| 865 | unescaped data
|
---|
| 866 | @return
|
---|
| 867 | escaped data
|
---|
| 868 | */
|
---|
| 869 | public function escape_name($data){
|
---|
| 870 | return $data;
|
---|
| 871 | }
|
---|
| 872 |
|
---|
| 873 | /*! get list of tables in the database
|
---|
| 874 |
|
---|
| 875 | @return
|
---|
| 876 | array of table names
|
---|
| 877 | */
|
---|
| 878 | public function tables_list() {
|
---|
| 879 | throw new Exception("Not implemented");
|
---|
| 880 | }
|
---|
| 881 |
|
---|
| 882 | /*! returns list of fields for the table in question
|
---|
| 883 |
|
---|
| 884 | @param table
|
---|
| 885 | name of table in question
|
---|
| 886 | @return
|
---|
| 887 | array of field names
|
---|
| 888 | */
|
---|
| 889 | public function fields_list($table) {
|
---|
| 890 | throw new Exception("Not implemented");
|
---|
| 891 | }
|
---|
| 892 |
|
---|
| 893 | }
|
---|
| 894 | /*! Implementation of DataWrapper for MySQL
|
---|
| 895 | **/
|
---|
| 896 | class MySQLDBDataWrapper extends DBDataWrapper{
|
---|
| 897 | protected $last_result;
|
---|
| 898 | public function query($sql){
|
---|
| 899 | LogMaster::log($sql);
|
---|
| 900 | $res=mysql_query($sql,$this->connection);
|
---|
| 901 | if ($res===false) throw new Exception("MySQL operation failed\n".mysql_error($this->connection));
|
---|
| 902 | $this->last_result = $res;
|
---|
| 903 | return $res;
|
---|
| 904 | }
|
---|
| 905 |
|
---|
| 906 | public function get_next($res){
|
---|
| 907 | if (!$res)
|
---|
| 908 | $res = $this->last_result;
|
---|
| 909 |
|
---|
| 910 | return mysql_fetch_assoc($res);
|
---|
| 911 | }
|
---|
| 912 |
|
---|
| 913 | protected function get_new_id(){
|
---|
| 914 | return mysql_insert_id($this->connection);
|
---|
| 915 | }
|
---|
| 916 |
|
---|
| 917 | public function escape($data){
|
---|
| 918 | return mysql_real_escape_string($data);
|
---|
| 919 | }
|
---|
| 920 |
|
---|
| 921 | public function tables_list() {
|
---|
| 922 | $result = mysql_query("SHOW TABLES");
|
---|
| 923 | if ($result===false) throw new Exception("MySQL operation failed\n".mysql_error($this->connection));
|
---|
| 924 |
|
---|
| 925 | $tables = array();
|
---|
| 926 | while ($table = mysql_fetch_array($result)) {
|
---|
| 927 | $tables[] = $table[0];
|
---|
| 928 | }
|
---|
| 929 | return $tables;
|
---|
| 930 | }
|
---|
| 931 |
|
---|
| 932 | public function fields_list($table) {
|
---|
| 933 | $result = mysql_query("SHOW COLUMNS FROM `".$table."`");
|
---|
| 934 | if ($result===false) throw new Exception("MySQL operation failed\n".mysql_error($this->connection));
|
---|
| 935 |
|
---|
| 936 | $fields = array();
|
---|
| 937 | $id = "";
|
---|
| 938 | while ($field = mysql_fetch_assoc($result)) {
|
---|
| 939 | if ($field['Key'] == "PRI")
|
---|
| 940 | $id = $field["Field"];
|
---|
| 941 | else
|
---|
| 942 | $fields[] = $field["Field"];
|
---|
| 943 | }
|
---|
| 944 | return array("fields" => $fields, "key" => $id );
|
---|
| 945 | }
|
---|
| 946 |
|
---|
| 947 | /*! escape field name to prevent sql reserved words conflict
|
---|
| 948 | @param data
|
---|
| 949 | unescaped data
|
---|
| 950 | @return
|
---|
| 951 | escaped data
|
---|
| 952 | */
|
---|
| 953 | public function escape_name($data){
|
---|
| 954 | if ((strpos($data,"`")!==false || intval($data)==$data) || (strpos($data,".")!==false))
|
---|
| 955 | return $data;
|
---|
| 956 | return '`'.$data.'`';
|
---|
| 957 | }
|
---|
| 958 | }
|
---|
| 959 | ?> |
---|