org.netfrag.elib/storage/lib_database_sql.php.inc

<?

// ===========================================================
class AbstractQueryStatement {

  var $sql_in_base;
  var $sql_out_build;
  var $sql_out_ready;
  
  var $bool_built;
  var $bool_ready_good;
  var $bool_hasToBeMapped;
  var $bool_data_set;

  var $criterias;
  var $orders;
  var $limits;
  
  var $map;
  var $map_new;
  var $map_reverse;
  var $data;
  var $crud;
  
  
  // ----------------------------------------------------
  function AbstractQueryStatement($map = array()) {

    $this->sql_in_base = '';
    $this->sql_out_build = '';
    $this->sql_out_ready = '';

    $this->bool_built = 0;
    $this->bool_ready_good = 0;
    $this->bool_hasToBeMapped = 0;
    $this->bool_data_set = 0;
    
    $this->criterias = array();
    $this->orders = array();
    $this->limits = array();

    $this->map = array();
    $this->map_new = array();
    $this->map_reverse = array();
    
    $this->data = array();
    $this->crud = '';
    /*
    if (count($map)) {
      $this->setMap($map);
    }
    */
  }

  // - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
  // getter- and setter-methods

    function setQuery($sql) {

      // strip bounding whitespaces
      $sql = trim($sql);
      
      // strip trailing semicolon, if present
      if (substr($sql, -1) == ';') {
        $sql = substr($sql, 0, -1);
      }
      
      // set sql-string locally
      $this->sql_in_base = $sql;
    }
    
    function setMap($map = array()) {

    // HACK/TODO:
    // right now we use here "PDLModelAttribute"-objects for building our map, we should definitely not! 
    // (logically yes, but not physically, since this is "lib_database_sql.php.inc", 
    // which has got absolutely nothing to do with "DataStore" or "PDL" - stuff!!!)

      if (count($map)) {
        $this->bool_hasToBeMapped = 1;
        $this->map = $map;

        // --- patch - begin
        // determine type of map, TODO: don't pass PDLAttributes in here any more!!!
        // this patch converts the map to the new format (plain hash)
        reset($map);
        if (strtolower(get_class(current($map))) == 'pdlmodelattribute') {
          while($attribute =& current($map)) {
            $attributename = $attribute->name;
            $mapname = $attribute->map_name;
            $map_new[$attributename] = $mapname;
            next($map);
          }
          $this->map_new = $map_new;
        } else {
          $this->map_new = $map;
        }
        $this->map_reverse = array_flip($this->map_new);
        // --- patch - end
        
      } else {
        $this->bool_hasToBeMapped = 0;
      }
    }
  
    function setData($data = array()) {
      if (count($data)) {
        $this->bool_data_set = 1;
        $this->data = $data;
      } else {
        $this->bool_data_set = 0;
      }
    }
  
    function setSql_createFromMap($map) {
      $this->sql_in_base = $sql;
      $this->setMap($map);
    }

    function getSql() {
      if (!$this->bool_built) { $this->_build(); }
      if ($this->bool_ready_good) {
        return $this->sql_out_ready;
      }
    }


  // - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
  // processor-methods

    function addCriteria($argument, $logicalOperator = 'AND') {
      $criteria = array('argument' => $argument, 'logicalOperator' => $logicalOperator);
      array_push($this->criterias, $criteria);
    }
    function clearCriterias() {
      $this->criterias = array();
    }
    
  
    function addOrder($argument, $modifier = 'ASC') {
      $order = array('argument' => $argument, 'modifier' => $modifier);
      array_push($this->orders, $order);
    }

    function addLimit($argument) {
      $limit = array('argument' => $argument);
      array_push($this->limits, $limit);
    }

  // - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
  // builder-methods
  
    function _build() {
      
      $this->sql_out_build = $this->sql_in_base;
      $bool_whereAppended = 0;
      

      // . . . . . . . . . . . . . . . . . . .
      // 1. determine type of query (CRUD)
      
        // determine "CRUD" (create, retrieve, update, delete)
        $this->crud = "";
        if (stristr($this->sql_out_build, 'INSERT INTO')) { $this->crud = "C"; }
        if (stristr($this->sql_out_build, 'SELECT')) { $this->crud = "R"; }
        if (stristr($this->sql_out_build, 'UPDATE')) { $this->crud = "U"; }
        if (stristr($this->sql_out_build, 'DELETE')) { $this->crud = "D"; }

      
      // . . . . . . . . . . . . . . . . . . .
      // 2. decoration

        // ---
        // criterias
          if (count($this->criterias)) {
            
            // do we need a "WHERE"?
            // HACK: do more sophisticated, for now, no "where" can appear in data regularly!!!   :-(
            if (!stristr($this->sql_out_build, 'where')) {
              $bool_whereAppended = 1;
              $this->sql_out_build .= ' WHERE ';
            }
  
            $criteriastring = '';
            $bool_first = 1;
            reset($this->criterias);
            while($criteria = current($this->criterias)) {
              if (!$bool_first || !$bool_whereAppended) {
                $criteriastring .= ' ' . $criteria['logicalOperator'] . ' ';
              }
              $criteriastring .= $criteria['argument'];
              next($this->criterias);
              $bool_first = 0;
            }
            $this->sql_out_build .= $criteriastring;
            
          }
        
        // ---
        // orders
          if (count($this->orders)) {
            $orderstring = ' ORDER BY ';
            $orderlist = array();
            reset($this->orders);
            while($order = current($this->orders)) {
              array_push($orderlist, $order['argument'] . ' ' . $order['modifier']);
              next($this->orders);
            }
            $orderstring .= join($orderlist, ', ');
            $this->sql_out_build .= $orderstring;
          }

        // ---
        // limits
          if (count($this->limits)) {
            $limitstring = ' LIMIT ';
            $limitlist = array();
            reset($this->limits);
            while($limit = current($this->limits)) {
              array_push($limitlist, $limit['argument']);
              next($this->limits);
            }
            $limitstring .= join($limitlist, ', ');
            $this->sql_out_build .= $limitstring;
          }


      // . . . . . . . . . . . . . . . . . . .
      // 3. mapping

        // if we have ...
        if ($this->bool_hasToBeMapped) {
          // ... we should check for having a map ...
          if ($this->map) {
            // ... if we do, start mapping!
            $this->_doMapping();
          } else {
            // ... trigger an error!
            print "<font color=\"red\">In \"SqlStatement->_build()\" there is an \"bool_hasToBeMapped\", but no \"map\".</font><br>";
          }
          
        }

      // finish sql-statement with semicolon
      $this->sql_out_build .= ';';

      // set "ready"-statement
      $this->sql_out_ready = $this->sql_out_build;

      // analyze, what we've built
      $this->_analyzeOutputQuery();
      
    }
    

  function _doMapping() {
  
    $map_attributes = array();
    $map_values = array();

    // - - - - - - - - - - - - - - - - - - - - -
    // 1. build attribute- and value- mapping-lists

      reset($this->map_new);
      while(list($attributename, $mapname) = each($this->map_new)) {
        
        // before we continue, we should check if "$map_value" is valid, else: continue with entry
        if ($mapname) {

          // we have two different kinds of maps here:

          // a) the "normal" map, which maps attribute-names to map-names (for "SELECT"s)
          $map_attributes[$attributename] = $mapname;

          // b) the "value" map, which maps attribute-names to attribute-values (for "INSERT"s or "UPDATE"s)
          if ( ($this->crud == 'C') || ($this->crud == 'U') ) {
            if (!$this->bool_data_set) {
              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>";
            }
            // was:
            // $map_values[$attributename] = $this->data[$attributename];
            // is:
            $value = $this->data[$attributename];
            //print "val: $value" . "\n";
            // HACK/TODO
            if ($value == 'null') {
              $value = '<Null>';
            } else {
              $value = addslashes($value);
            }
            $map_values[$attributename] = $value;
          }

        }
        
      }

    //print "out_build: " . $this->sql_out_build . "<br>";
    

    // - - - - - - - - - - - - - - - - - - - - -
    // 2. do mapping

      // --------------------------------
      // a) attribute-name-mapping
        // build arrays of patterns and replacement-strings from lists of all attributes/mapnames
        $preg_patterns = array_keys($map_attributes);
        $preg_replacements = array_values($map_attributes);
        // modify pattern- and replacement- arrays to be PCRE-compatible expressions
        modifyArray($preg_patterns, array('prefix' => '/(:', 'postfix' => ')(\W)/'));
        modifyArray($preg_replacements, array('prefix' => "", 'postfix' => "\\2"));
        // do a regular-expression-replace on our still "unready"-sql-string
        $this->sql_out_build = preg_replace($preg_patterns, $preg_replacements, $this->sql_out_build);

      // --------------------------------
      // b) value-mapping
        if ( ($this->crud == 'C') || ($this->crud == 'U') && (count($map_values)) ) {
          // build arrays of patterns and replacement-strings from lists of all attributes/values
          $use_list_attributes = array_keys($map_values);
          $use_list_values = array_values($map_values);
          // modify pattern- and replacement- arrays to be PCRE-compatible expressions
          modifyArray($use_list_attributes, array('prefix' => '/(!', 'postfix' => ')(\W)/'));
          modifyArray($use_list_values, array('prefix' => "'", 'postfix' => "'\\2"));
          // do a regular-expression-replace on our still "unready"-sql-string
          $this->sql_out_build = preg_replace($use_list_attributes, $use_list_values, $this->sql_out_build);
          $this->sql_out_build = str_replace("'<Null>'", "Null", $this->sql_out_build);
        }
      
    //print "out_build: " . $this->sql_out_build . "<br>";

  }    


  // ----------------------------------------------------
  function _analyzeOutputQuery() {

    // we assume a "good" query
    $bool_good = 1;
    
    // get ready query
    $oq = $this->sql_out_build;
    
    if (preg_match('/group by/i', $oq) && preg_match('/where/i', $oq)) {
      $oq = str_replace('WHERE', 'AND', $oq);
      $oq = str_replace('ds_anmelder.', '', $oq);
      //print $oq . "<br>";
      $this->sql_out_ready = $this->sql_out_build = $oq;
    }
    
    // replace all value-stuff
    //$oq = str_replace('', '', $oq);
    
    // TODO: enable this again (more sophisticated!)
    /*
    if ($this->bool_hasToBeMapped) {
      // check for unmapped items
        // was:
        //if (preg_match('/(:\w+)/', $oq, $regs)) {
        // is:
        if (preg_match("/([^'](:\w+)[^'])/", $oq, $regs)) {
          print "<font color=\"red\">_analyzeOutputQuery: Detected unmapped item(s) in query \"$oq\", query will be set invalid.</font><br>";
          print "Item(s) was/were: \"{$regs[1]}\".</font><br>";
          $bool_good = 0;
        }
    }
    */
    
    // check for deletes without criteria ("where")
      if ( (stristr($oq, 'DELETE FROM')) && (!stristr($oq, 'WHERE')) ) {
        print "<font color=\"red\">_analyzeOutputQuery: Detected \"DELETE\" without \"WHERE\" in query \"$oq\", query will be set invalid.</font><br>";
        $bool_good = 0;
      }

    // set "bool_ready_good" if the query is valid
    $this->bool_ready_good = $bool_good;

  }


}

?>
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			?>
MailToCvsAdmin">MailToCvsAdmin	ViewVC Help
Powered by ViewVC 1.1.26