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 | ?> |
---|