1 |
cvsjoko |
1.1 |
<? |
2 |
|
|
|
3 |
|
|
// =========================================================== |
4 |
|
|
class AbstractQueryStatement { |
5 |
|
|
|
6 |
|
|
var $sql_in_base; |
7 |
|
|
var $sql_out_build; |
8 |
|
|
var $sql_out_ready; |
9 |
|
|
|
10 |
|
|
var $bool_built; |
11 |
|
|
var $bool_ready_good; |
12 |
|
|
var $bool_hasToBeMapped; |
13 |
|
|
var $bool_data_set; |
14 |
|
|
|
15 |
|
|
var $criterias; |
16 |
|
|
var $orders; |
17 |
|
|
var $limits; |
18 |
|
|
|
19 |
|
|
var $map; |
20 |
|
|
var $map_new; |
21 |
|
|
var $map_reverse; |
22 |
|
|
var $data; |
23 |
|
|
var $crud; |
24 |
|
|
|
25 |
|
|
|
26 |
|
|
|
27 |
|
|
// ---------------------------------------------------- |
28 |
|
|
function AbstractQueryStatement($map = array()) { |
29 |
|
|
|
30 |
|
|
$this->sql_in_base = ''; |
31 |
|
|
$this->sql_out_build = ''; |
32 |
|
|
$this->sql_out_ready = ''; |
33 |
|
|
|
34 |
|
|
$this->bool_built = 0; |
35 |
|
|
$this->bool_ready_good = 0; |
36 |
|
|
$this->bool_hasToBeMapped = 0; |
37 |
|
|
$this->bool_data_set = 0; |
38 |
|
|
|
39 |
|
|
$this->criterias = array(); |
40 |
|
|
$this->orders = array(); |
41 |
|
|
$this->limits = array(); |
42 |
|
|
|
43 |
|
|
$this->map = array(); |
44 |
|
|
$this->map_new = array(); |
45 |
|
|
$this->map_reverse = array(); |
46 |
|
|
|
47 |
|
|
$this->data = array(); |
48 |
|
|
$this->crud = ''; |
49 |
|
|
/* |
50 |
|
|
if (count($map)) { |
51 |
|
|
$this->setMap($map); |
52 |
|
|
} |
53 |
|
|
*/ |
54 |
|
|
} |
55 |
|
|
|
56 |
|
|
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
57 |
|
|
// getter- and setter-methods |
58 |
|
|
|
59 |
|
|
function setQuery($sql) { |
60 |
|
|
|
61 |
|
|
// strip bounding whitespaces |
62 |
|
|
$sql = trim($sql); |
63 |
|
|
|
64 |
|
|
// strip trailing semicolon, if present |
65 |
|
|
if (substr($sql, -1) == ';') { |
66 |
|
|
$sql = substr($sql, 0, -1); |
67 |
|
|
} |
68 |
|
|
|
69 |
|
|
// set sql-string locally |
70 |
|
|
$this->sql_in_base = $sql; |
71 |
|
|
} |
72 |
|
|
|
73 |
|
|
function setMap($map = array()) { |
74 |
|
|
|
75 |
|
|
// HACK/TODO: |
76 |
|
|
// right now we use here "PDLModelAttribute"-objects for building our map, we should definitely not! |
77 |
|
|
// (logically yes, but not physically, since this is "lib_database_sql.php.inc", |
78 |
|
|
// which has got absolutely nothing to do with "DataStore" or "PDL" - stuff!!!) |
79 |
|
|
|
80 |
|
|
if (count($map)) { |
81 |
|
|
$this->bool_hasToBeMapped = 1; |
82 |
|
|
$this->map = $map; |
83 |
|
|
|
84 |
|
|
// --- patch - begin |
85 |
|
|
// determine type of map, TODO: don't pass PDLAttributes in here any more!!! |
86 |
|
|
// this patch converts the map to the new format (plain hash) |
87 |
|
|
reset($map); |
88 |
|
|
if (strtolower(get_class(current($map))) == 'pdlmodelattribute') { |
89 |
|
|
while($attribute =& current($map)) { |
90 |
|
|
$attributename = $attribute->name; |
91 |
|
|
$mapname = $attribute->map_name; |
92 |
|
|
$map_new[$attributename] = $mapname; |
93 |
|
|
next($map); |
94 |
|
|
} |
95 |
|
|
$this->map_new = $map_new; |
96 |
|
|
} else { |
97 |
|
|
$this->map_new = $map; |
98 |
|
|
} |
99 |
|
|
$this->map_reverse = array_flip($this->map_new); |
100 |
|
|
// --- patch - end |
101 |
|
|
|
102 |
|
|
} else { |
103 |
|
|
$this->bool_hasToBeMapped = 0; |
104 |
|
|
} |
105 |
|
|
} |
106 |
|
|
|
107 |
|
|
function setData($data = array()) { |
108 |
|
|
if (count($data)) { |
109 |
|
|
$this->bool_data_set = 1; |
110 |
|
|
$this->data = $data; |
111 |
|
|
} else { |
112 |
|
|
$this->bool_data_set = 0; |
113 |
|
|
} |
114 |
|
|
} |
115 |
|
|
|
116 |
|
|
function setSql_createFromMap($map) { |
117 |
|
|
$this->sql_in_base = $sql; |
118 |
|
|
$this->setMap($map); |
119 |
|
|
} |
120 |
|
|
|
121 |
|
|
function getSql() { |
122 |
|
|
if (!$this->bool_built) { $this->_build(); } |
123 |
|
|
if ($this->bool_ready_good) { |
124 |
|
|
return $this->sql_out_ready; |
125 |
|
|
} |
126 |
|
|
} |
127 |
|
|
|
128 |
|
|
|
129 |
|
|
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
130 |
|
|
// processor-methods |
131 |
|
|
|
132 |
|
|
function addCriteria($argument, $logicalOperator = 'AND') { |
133 |
|
|
$criteria = array('argument' => $argument, 'logicalOperator' => $logicalOperator); |
134 |
|
|
array_push($this->criterias, $criteria); |
135 |
|
|
} |
136 |
|
|
function clearCriterias() { |
137 |
|
|
$this->criterias = array(); |
138 |
|
|
} |
139 |
|
|
|
140 |
|
|
|
141 |
|
|
function addOrder($argument, $modifier = 'ASC') { |
142 |
|
|
$order = array('argument' => $argument, 'modifier' => $modifier); |
143 |
|
|
array_push($this->orders, $order); |
144 |
|
|
} |
145 |
|
|
|
146 |
|
|
function addLimit($argument) { |
147 |
|
|
$limit = array('argument' => $argument); |
148 |
|
|
array_push($this->limits, $limit); |
149 |
|
|
} |
150 |
|
|
|
151 |
|
|
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
152 |
|
|
// builder-methods |
153 |
|
|
|
154 |
|
|
function _build() { |
155 |
|
|
|
156 |
|
|
$this->sql_out_build = $this->sql_in_base; |
157 |
|
|
$bool_whereAppended = 0; |
158 |
|
|
|
159 |
|
|
|
160 |
|
|
// . . . . . . . . . . . . . . . . . . . |
161 |
|
|
// 1. determine type of query (CRUD) |
162 |
|
|
|
163 |
|
|
// determine "CRUD" (create, retrieve, update, delete) |
164 |
|
|
$this->crud = ""; |
165 |
|
|
if (stristr($this->sql_out_build, 'INSERT INTO')) { $this->crud = "C"; } |
166 |
|
|
if (stristr($this->sql_out_build, 'SELECT')) { $this->crud = "R"; } |
167 |
|
|
if (stristr($this->sql_out_build, 'UPDATE')) { $this->crud = "U"; } |
168 |
|
|
if (stristr($this->sql_out_build, 'DELETE')) { $this->crud = "D"; } |
169 |
|
|
|
170 |
|
|
|
171 |
|
|
// . . . . . . . . . . . . . . . . . . . |
172 |
|
|
// 2. decoration |
173 |
|
|
|
174 |
|
|
// --- |
175 |
|
|
// criterias |
176 |
|
|
if (count($this->criterias)) { |
177 |
|
|
|
178 |
|
|
// do we need a "WHERE"? |
179 |
|
|
// HACK: do more sophisticated, for now, no "where" can appear in data regularly!!! :-( |
180 |
|
|
if (!stristr($this->sql_out_build, 'where')) { |
181 |
|
|
$bool_whereAppended = 1; |
182 |
|
|
$this->sql_out_build .= ' WHERE '; |
183 |
|
|
} |
184 |
|
|
|
185 |
|
|
$criteriastring = ''; |
186 |
|
|
$bool_first = 1; |
187 |
|
|
reset($this->criterias); |
188 |
|
|
while($criteria = current($this->criterias)) { |
189 |
|
|
if (!$bool_first || !$bool_whereAppended) { |
190 |
|
|
$criteriastring .= ' ' . $criteria['logicalOperator'] . ' '; |
191 |
|
|
} |
192 |
|
|
$criteriastring .= $criteria['argument']; |
193 |
|
|
next($this->criterias); |
194 |
|
|
$bool_first = 0; |
195 |
|
|
} |
196 |
|
|
$this->sql_out_build .= $criteriastring; |
197 |
|
|
|
198 |
|
|
} |
199 |
|
|
|
200 |
|
|
// --- |
201 |
|
|
// orders |
202 |
|
|
if (count($this->orders)) { |
203 |
|
|
$orderstring = ' ORDER BY '; |
204 |
|
|
$orderlist = array(); |
205 |
|
|
reset($this->orders); |
206 |
|
|
while($order = current($this->orders)) { |
207 |
|
|
array_push($orderlist, $order['argument'] . ' ' . $order['modifier']); |
208 |
|
|
next($this->orders); |
209 |
|
|
} |
210 |
|
|
$orderstring .= join($orderlist, ', '); |
211 |
|
|
$this->sql_out_build .= $orderstring; |
212 |
|
|
} |
213 |
|
|
|
214 |
|
|
// --- |
215 |
|
|
// limits |
216 |
|
|
if (count($this->limits)) { |
217 |
|
|
$limitstring = ' LIMIT '; |
218 |
|
|
$limitlist = array(); |
219 |
|
|
reset($this->limits); |
220 |
|
|
while($limit = current($this->limits)) { |
221 |
|
|
array_push($limitlist, $limit['argument']); |
222 |
|
|
next($this->limits); |
223 |
|
|
} |
224 |
|
|
$limitstring .= join($limitlist, ', '); |
225 |
|
|
$this->sql_out_build .= $limitstring; |
226 |
|
|
} |
227 |
|
|
|
228 |
|
|
|
229 |
|
|
// . . . . . . . . . . . . . . . . . . . |
230 |
|
|
// 3. mapping |
231 |
|
|
|
232 |
|
|
// if we have ... |
233 |
|
|
if ($this->bool_hasToBeMapped) { |
234 |
|
|
// ... we should check for having a map ... |
235 |
|
|
if ($this->map) { |
236 |
|
|
// ... if we do, start mapping! |
237 |
|
|
$this->_doMapping(); |
238 |
|
|
} else { |
239 |
|
|
// ... trigger an error! |
240 |
|
|
print "<font color=\"red\">In \"SqlStatement->_build()\" there is an \"bool_hasToBeMapped\", but no \"map\".</font><br>"; |
241 |
|
|
} |
242 |
|
|
|
243 |
|
|
} |
244 |
|
|
|
245 |
|
|
// finish sql-statement with semicolon |
246 |
|
|
$this->sql_out_build .= ';'; |
247 |
|
|
|
248 |
|
|
// set "ready"-statement |
249 |
|
|
$this->sql_out_ready = $this->sql_out_build; |
250 |
|
|
|
251 |
|
|
// analyze, what we've built |
252 |
|
|
$this->_analyzeOutputQuery(); |
253 |
|
|
|
254 |
|
|
} |
255 |
|
|
|
256 |
|
|
|
257 |
|
|
function _doMapping() { |
258 |
|
|
|
259 |
|
|
$map_attributes = array(); |
260 |
|
|
$map_values = array(); |
261 |
|
|
|
262 |
|
|
// - - - - - - - - - - - - - - - - - - - - - |
263 |
|
|
// 1. build attribute- and value- mapping-lists |
264 |
|
|
|
265 |
|
|
reset($this->map_new); |
266 |
|
|
while(list($attributename, $mapname) = each($this->map_new)) { |
267 |
|
|
|
268 |
|
|
// before we continue, we should check if "$map_value" is valid, else: continue with entry |
269 |
|
|
if ($mapname) { |
270 |
|
|
|
271 |
|
|
// we have two different kinds of maps here: |
272 |
|
|
|
273 |
|
|
// a) the "normal" map, which maps attribute-names to map-names (for "SELECT"s) |
274 |
|
|
$map_attributes[$attributename] = $mapname; |
275 |
|
|
|
276 |
|
|
// b) the "value" map, which maps attribute-names to attribute-values (for "INSERT"s or "UPDATE"s) |
277 |
|
|
if ( ($this->crud == 'C') || ($this->crud == 'U') ) { |
278 |
|
|
if (!$this->bool_data_set) { |
279 |
|
|
print "<font color=\"red\">_doMapping: \"INSERT\"- or \"UPDATE\"-query requested, but no data was set. Further object-operations on this object will be aborted.</font><br>"; |
280 |
|
|
} |
281 |
|
|
// was: |
282 |
|
|
// $map_values[$attributename] = $this->data[$attributename]; |
283 |
|
|
// is: |
284 |
|
|
$value = $this->data[$attributename]; |
285 |
|
|
//print "val: $value" . "\n"; |
286 |
|
|
// HACK/TODO |
287 |
|
|
if ($value == 'null') { |
288 |
|
|
$value = '<Null>'; |
289 |
|
|
} else { |
290 |
|
|
$value = addslashes($value); |
291 |
|
|
} |
292 |
|
|
$map_values[$attributename] = $value; |
293 |
|
|
} |
294 |
|
|
|
295 |
|
|
} |
296 |
|
|
|
297 |
|
|
} |
298 |
|
|
|
299 |
|
|
//print "out_build: " . $this->sql_out_build . "<br>"; |
300 |
|
|
|
301 |
|
|
|
302 |
|
|
// - - - - - - - - - - - - - - - - - - - - - |
303 |
|
|
// 2. do mapping |
304 |
|
|
|
305 |
|
|
// -------------------------------- |
306 |
|
|
// a) attribute-name-mapping |
307 |
|
|
// build arrays of patterns and replacement-strings from lists of all attributes/mapnames |
308 |
|
|
$preg_patterns = array_keys($map_attributes); |
309 |
|
|
$preg_replacements = array_values($map_attributes); |
310 |
|
|
// modify pattern- and replacement- arrays to be PCRE-compatible expressions |
311 |
|
|
modifyArray($preg_patterns, array('prefix' => '/(:', 'postfix' => ')(\W)/')); |
312 |
|
|
modifyArray($preg_replacements, array('prefix' => "", 'postfix' => "\\2")); |
313 |
|
|
// do a regular-expression-replace on our still "unready"-sql-string |
314 |
|
|
$this->sql_out_build = preg_replace($preg_patterns, $preg_replacements, $this->sql_out_build); |
315 |
|
|
|
316 |
|
|
// -------------------------------- |
317 |
|
|
// b) value-mapping |
318 |
|
|
if ( ($this->crud == 'C') || ($this->crud == 'U') && (count($map_values)) ) { |
319 |
|
|
// build arrays of patterns and replacement-strings from lists of all attributes/values |
320 |
|
|
$use_list_attributes = array_keys($map_values); |
321 |
|
|
$use_list_values = array_values($map_values); |
322 |
|
|
// modify pattern- and replacement- arrays to be PCRE-compatible expressions |
323 |
|
|
modifyArray($use_list_attributes, array('prefix' => '/(!', 'postfix' => ')(\W)/')); |
324 |
|
|
modifyArray($use_list_values, array('prefix' => "'", 'postfix' => "'\\2")); |
325 |
|
|
// do a regular-expression-replace on our still "unready"-sql-string |
326 |
|
|
$this->sql_out_build = preg_replace($use_list_attributes, $use_list_values, $this->sql_out_build); |
327 |
|
|
$this->sql_out_build = str_replace("'<Null>'", "Null", $this->sql_out_build); |
328 |
|
|
} |
329 |
|
|
|
330 |
|
|
//print "out_build: " . $this->sql_out_build . "<br>"; |
331 |
|
|
|
332 |
|
|
} |
333 |
|
|
|
334 |
|
|
|
335 |
|
|
// ---------------------------------------------------- |
336 |
|
|
function _analyzeOutputQuery() { |
337 |
|
|
|
338 |
|
|
// we assume a "good" query |
339 |
|
|
$bool_good = 1; |
340 |
|
|
|
341 |
|
|
// get ready query |
342 |
|
|
$oq = $this->sql_out_build; |
343 |
|
|
|
344 |
|
|
if (preg_match('/group by/i', $oq) && preg_match('/where/i', $oq)) { |
345 |
|
|
$oq = str_replace('WHERE', 'AND', $oq); |
346 |
|
|
$oq = str_replace('ds_anmelder.', '', $oq); |
347 |
|
|
//print $oq . "<br>"; |
348 |
|
|
$this->sql_out_ready = $this->sql_out_build = $oq; |
349 |
|
|
} |
350 |
|
|
|
351 |
|
|
// replace all value-stuff |
352 |
|
|
//$oq = str_replace('', '', $oq); |
353 |
|
|
|
354 |
|
|
// TODO: enable this again (more sophisticated!) |
355 |
|
|
/* |
356 |
|
|
if ($this->bool_hasToBeMapped) { |
357 |
|
|
// check for unmapped items |
358 |
|
|
// was: |
359 |
|
|
//if (preg_match('/(:\w+)/', $oq, $regs)) { |
360 |
|
|
// is: |
361 |
|
|
if (preg_match("/([^'](:\w+)[^'])/", $oq, $regs)) { |
362 |
|
|
print "<font color=\"red\">_analyzeOutputQuery: Detected unmapped item(s) in query \"$oq\", query will be set invalid.</font><br>"; |
363 |
|
|
print "Item(s) was/were: \"{$regs[1]}\".</font><br>"; |
364 |
|
|
$bool_good = 0; |
365 |
|
|
} |
366 |
|
|
} |
367 |
|
|
*/ |
368 |
|
|
|
369 |
|
|
// check for deletes without criteria ("where") |
370 |
|
|
if ( (stristr($oq, 'DELETE FROM')) && (!stristr($oq, 'WHERE')) ) { |
371 |
|
|
print "<font color=\"red\">_analyzeOutputQuery: Detected \"DELETE\" without \"WHERE\" in query \"$oq\", query will be set invalid.</font><br>"; |
372 |
|
|
$bool_good = 0; |
373 |
|
|
} |
374 |
|
|
|
375 |
|
|
// set "bool_ready_good" if the query is valid |
376 |
|
|
$this->bool_ready_good = $bool_good; |
377 |
|
|
|
378 |
|
|
} |
379 |
|
|
|
380 |
|
|
|
381 |
|
|
} |
382 |
|
|
|
383 |
|
|
?> |