Xataface  2.0alpha2
Xataface Application Framework
 All Data Structures Namespaces Files Functions Variables Groups Pages
wrapper.php
Go to the documentation of this file.
1 <?php
2 /*-------------------------------------------------------------------------------
3  * Xataface Web Application Framework
4  * Copyright (C) 2005-2008 Web Lite Solutions Corp (shannah@sfu.ca)
5  *
6  * This program is free software; you can redistribute it and/or
7  * modify it under the terms of the GNU General Public License
8  * as published by the Free Software Foundation; either version 2
9  * of the License, or (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program; if not, write to the Free Software
18  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19  *-------------------------------------------------------------------------------
20  */
21 define('SQL_PARSER_WRAPPER_ERROR', 101);
22 require_once 'SQL/Parser.php';
23 
24 class SQL_Parser_wrapper {
25 
26  var $_data;
27  var $_tableLookup;
28  var $_parser;
29 
30  function SQL_Parser_wrapper(&$data, $dialect='MySQL'){
31  $this->_data =& $data;
32  $this->_tableLookup = array();
33  $this->_parser = new SQL_Parser(null, $dialect);
34  }
35 
40  function getTableName($columnname){
41  if ( !isset( $this->_tableLookup[$columnname] ) ){
42  if ( strpos($columnname, '.') === false ) $this->_tableLookup[$columnname] = null;
43  else {
44  $data =& $this->_data;
45  list($table, $column) = explode('.', $columnname);
46  if ( isset( $data['table_aliases'] ) ){
47  for ($i=0; $i<count($data['table_aliases']); $i++){
48  if ( $data['table_aliases'][$i] == $table ){
49  $table = $data['table_names'][$i];
50  break;
51  }
52  }
53  }
54  $this->_tableLookup[$columnname] = $table;
55  }
56 
57  }
58  return $this->_tableLookup[$columnname];
59  }
60 
61 
66  function getTableAlias($tablename){
67  $index = array_search($tablename, $this->_data['table_names']);
68  if ( $index === false ){
69  return PEAR::raiseError("Table not found in query", SQL_PARSER_WRAPPER_ERROR, E_USER_WARNING, null, "The table '$tablename' was requested in SQL_Parser_wrapper::getTableAlias() and not found in the sql query");
70 
71  }
72  if ( isset( $this->_data['table_aliases']) and isset( $this->_data['table_aliases'][$index]) and
73  $this->_data['table_aliases'][$index] ) {
74  return $this->_data['table_aliases'][$index];
75  } else {
76  return $tablename;
77  }
78 
79  }
80 
81 
82 
87  function resolveColumnName($columnname){
88 
89  $table = $this->getTableName($columnname);
90  if ( $table === null ){
91  return $columnname;
92  } else {
93  if ( strpos($columnname, ".") !== false ){
94  list($junk, $col) = explode('.', $columnname);
95  return $table.'.'.$col;
96  } else {
97  return $col;
98  }
99  }
100 
101  }
102 
103 
113  function unresolveColumnName($columnname){
114  if ( strpos($columnname, '.') !== false ){
115  list($table,$column) = explode('.', $columnname);
116  $tablename = $this->getTableAlias($table);
117  if ( PEAR::isError($tablename) ){
118  /*
119  * There is no table by this name. Check to see if it is already
120  * an alias.
121  */
122  $index = array_search($table, $this->_data['table_aliases']);
123  if ( $index !== false ){
124  /*
125  * The tablename is an alias so we can leave it unchanged.
126  */
127  $tablename = $table;
128  } else {
129  /*
130  * The tablename is not an alias nor is it a valid table...
131  * propogate the error upwards.
132  */
133  $tablename->addUserInfo("In SQL_Parser_wrapper attempted to unresolve column '$columnname' but the table does not exist as either an alias or a column name.");
134  return $tablename;
135  }
136  }
137  return $tablename.'.'.$column;
138  } else {
139  $index = $this->array_ereg_search('/\.'.$columnname.'$/', $this->_data['column_names']);
140  if ( $index !== false ){
141  return $this->_data['column_names'][$index];
142  } else {
143  return $columnname;
144  }
145  }
146 
147  }
148 
153  function array_ereg_search($needle, $haystack){
154  foreach ( array_keys($haystack) as $index ){
155  if ( preg_match($needle, $haystack[$index]) ){
156  return $index;
157  }
158  }
159  return false;
160  }
161 
162  function unresolveWhereClauseColumns(&$clause){
163  if ( !is_array($clause) ) return;
164  if ( isset($clause['type']) and $clause['type'] === 'ident' ){
165  $clause['value'] = $this->unresolveColumnName($clause['value']);
166  }
167  foreach ( array_keys($clause) as $key){
168  $this->unresolveWhereClauseColumns($clause[$key]);
169  }
170 
171 
172  }
173 
174 
179  function removeColumn($columnname){
180  $columnNames =& $this->_data['column_names'];
181  $index = array_search($columnname, $columnNames);
182  if ( $index !== false ){
183  array_splice($columnNames, $index, 1);
184  if ( isset( $this->_data['column_aliases'] ) ){
185  array_splice($this->_data['column_aliases'], $index, 1);
186  }
187  return true;
188  } else {
189  return false;
190  }
191  }
192 
193 
198  function removeColumnsFromTable($tablename){
199  $columnNames =& $this->_data['column_names'];
200  $count = 0;
201  foreach ( $columnNames as $name ){
202  if ( $this->getTableName($name) == $tablename){
203  $res = $this->removeColumn($name);
204  if ( $res ) $count++;
205  }
206  }
207  return $count;
208  }
209 
213  function addColumn($columnname, $columnalias){
214  $this->_data['column_names'][] = $columnname;
215  $this->_data['column_aliases'][] = $columnalias;
216 
217  }
218 
222  function &appendClause($clause, $op='or'){
223  $data =& $this->_data;
224  if ( isset( $data['where_clause']) and $data['where_clause'] ) {
225 
226  if ( (isset( $data['where_clause']['type']) and $data['where_clause']['type'] == 'subclause')
227  or count($data['where_clause']) ===1 or
228  (isset($data['where_clause']['op']) and !in_array($data['where_clause']['op'], array('and','or')) )
229  ){
230  $arg1 = $data['where_clause'];
231  } else {
232  $arg1 = array("value"=>$data['where_clause'], "type"=>"subclause");
233  }
234 
235  if ( (isset($clause['type']) and $clause['type'] == 'subclause') or
236  count($clause) === 1 or
237  (isset($clause['op']) and !in_array($clause['op'], array('and','or')))
238  ){
239  $arg2 = $clause;
240  } else {
241  $arg2 = array("value"=>$clause, "type"=>"subclause");
242  }
243 
244  $data['where_clause'] = array( "arg_1"=> $arg1, 'op'=>$op, "arg_2"=> $arg2);
245  } else {
246  $data['where_clause'] = $clause;
247  }
248  return $data;
249 
250  }
251 
252 
253  function &addWhereClause($whereStr, $op='and'){
254  $sql = "SELECT * FROM foo WHERE $whereStr";
255  $parsed = $this->_parser->parse($sql);
256 
257 
258  $this->unresolveWhereClauseColumns($parsed['where_clause']);
259 
260  $this->appendClause($parsed['where_clause'], $op);
261  return $this->_data;
262 
263  }
264 
265 
266  function &setSortClause($sortStr){
267 
268  $sql = "SELECT * FROM foo ORDER BY $sortStr";
269  $parsed = $this->_parser->parse($sql);
270 
271  $sort_order = array();
272  foreach (array_keys($parsed['sort_order']) as $sort_col){
273  $this->unresolveWhereClauseColumns($parsed['sort_order'][$sort_col]);
274  $sort_order[] =& $parsed['sort_order'][$sort_col];
275 
276  }
277 
278  $this->_data['sort_order'] = $sort_order;
279  return $this->_data;
280 
281  }
282 
283 
284  function &addSortClause($sortStr){
285 
286  $sql = "SELECT * FROM foo ORDER BY $sortStr";
287  $parsed = $this->_parser->parse($sql);
288 
289  $sort_order =& $this->_data['sort_order'];
290  foreach (array_keys($parsed['sort_order']) as $sort_col){
291  $this->unresolveWhereClauseColumns($parsed['sort_order'][$sort_col]);
292  $sort_order[] =& $parsed['sort_order'][$sort_col];
293 
294  }
295 
296  //$this->_data['sort_order'] = $sort_order;
297  return $this->_data;
298 
299  }
300 
301 
302 
303 
304 
305  function &removeWhereClause($clause){
306  $null = null;
307  $this->_data['where_clause'] = $this->_removeClause_rec($clause, $this->_data['where_clause']);
308  if ( $this->_data['where_clause'] == null ) {
309  unset($this->_data['where_clause']);
310  return $null;
311  }
312  return $this->_data['where_clause'];
313  }
314 
315  function removeJoinClause($clause){
316  if ( is_array($this->_data['table_join_clause']) ){
317  $new_clauses = array();
318  $new_joins = array();
319 
320  foreach ( $this->_data['table_join_clause'] as $index=>$jc){
321  $new_clause = $this->_removeClause_rec($clause, $jc);
322  if ( $new_clause == null ) $new_clause = '';
323  $new_clauses[] = $new_clause;
324  if ( sizeof($new_clauses) > 1 ){
325  if ( $new_clause == '' ) $new_joins[] = ',';
326  else $new_joins[] = $this->_data['table_join'][$index-1];
327  }
328  }
329  $this->_data['table_join_clause'] = $new_clauses;
330  $this->_data['table_join'] = $new_joins;
331  }
332 
333  }
334 
335 
336  function _removeClause_rec($clause, $root){
337 
338  // Case 1: The current Node has "arg_1" and "arg_2" params
339  if ( isset( $root['arg_1'] ) and isset( $root['arg_2']) ){
340 
341  if ( $clause == $root ){
342  return null;
343  } else {
344  $root['arg_1'] = $this->_removeClause_rec($clause, $root['arg_1']);
345  $root['arg_2'] = $this->_removeClause_rec($clause, $root['arg_2']);
346 
347  if ( $root['arg_1'] == null and $root['arg_2'] == null ) return null;
348  else if ( $root['arg_1'] != null and $root['arg_2'] == null ) return $root['arg_1'];
349  else if ( $root['arg_2'] != null and $root['arg_1'] == null ) return $root['arg_2'];
350  else return $root;
351  }
352  }
353 
354  // There is only a single argument... this is kind of a lame case, but it exists.
355  else if ( isset( $root['arg_1'] ) ){
356 
357  $root['arg_1'] = $this->_removeClause_rec($clause, $root['arg_1']);
358  return $root['arg_1'];
359  }
360 
361  // Case 2: The current Node has a "type" param
362  else if ( isset( $root['type']) and $root['type'] == 'subclause' ){
363 
364  $root['value'] = $this->_removeClause_rec($clause, $root['value']);
365  if ( $root['value'] == null ) return null;
366  return $root;
367  }
368 
369  // Case 3: Anything else... return the root unchanged
370  else {
371 
372  return $root;
373  }
374 
375 
376  }
377 
378  function findWhereClausesWithTable($table){
379 
380  $clauses = array();
381  if ( isset($this->_data['where_clause']) and is_array($this->_data['where_clause']) ){
382  $this->_findClausesWithTable_rec($table, $this->_data['where_clause'], $clauses);
383  }
384  return $clauses;
385 
386  }
387 
388  function findJoinClausesWithTable($table){
389  $clauses = array();
390 
391  if ( is_array($this->_data['table_join_clause']) ){
392  foreach ( $this->_data['table_join_clause'] as $index=>$jc){
393  $this->_findClausesWithTable_rec($table, $jc, $clauses);
394 
395  }
396  }
397 
398  return $clauses;
399 
400  }
401 
402 
433  function addMetaDataColumn($columnName, $fullColumnNames=false){
434  if ( strpos($columnName, '.') !== false ){
435  list( $table, $shortName) = explode('.', $columnName);
436  } else {
437  $shortName = $columnName;
438  }
439 
440  $aliasName = str_replace('.','_',$columnName);
441 
442  // at this point $alias should hold the valid name of the column for which we want info.
443  $aliasColumnName = $this->unresolveColumnName($columnName);
444  if ( PEAR::isError($aliasColumnName) ){
445  return $aliasColumnName;
446  }
447  $func = array('name'=>'length', 'args'=>array(array('type'=>'ident', 'value'=>$aliasColumnName)), 'alias'=>'__'.($fullColumnNames ? $aliasName : $shortName).'_length');
448 
449  if ( !isset( $this->_data['set_function'] ) ){
450  $this->_data['set_function'] = array();
451  }
452 
453  /*
454  * Let's see if this function has already been added.
455  */
456  $index = array_search($func, $this->_data['set_function']);
457  if ( $index === false ){
458  $this->_data['set_function'][] = $func;
459  }
460 
461 
462  }
463 
468  function addMetaDataColumns($fullColumnNames = false){
469  if ( !isset( $this->_data['column_names']) ) return;
470  foreach ( $this->_data['column_names'] as $columnName){
471  $this->addMetaDataColumn($columnName, $fullColumnNames);
472  }
473  }
474 
475 
476  function _findClausesWithTable_rec($table, &$root, &$clauses){
477 
478  foreach ( array('arg_1','arg_2') as $arg){
479  if ( !isset( $root[$arg]) ) continue;
480  $type = (isset( $root[$arg]['type'] ) ? $root[$arg]['type'] : null);
481  switch ($type){
482  case 'subclause':
483  $this->_findClausesWithTable_rec($table, $root[$arg]['value'], $clauses);
484  break;
485 
486  case 'ident':
487  if ( $this->getTableName($root[$arg]['value']) == $table ) array_push($clauses, $root);
488  break;
489 
490  default:
491  $this->_findClausesWithTable_rec($table, $root[$arg], $clauses);
492 
493 
494  }
495  }
496  }
497 
498  function findWhereClausesWithPattern($regex){
499  $clauses = array();
500  if ( isset($this->_data['where_clause']) and is_array($this->_data['where_clause']) ){
501  $this->_findClausesWithPattern_rec($regex, $this->_data['where_clause'], $clauses);
502  }
503  return $clauses;
504  }
505 
506  function findJoinClausesWithPattern($regex){
507  $clauses = array();
508  if ( is_array($this->_data['table_join_clause']) ){
509  foreach ( $this->_data['table_join_clause'] as $jc ){
510  $this->_findClausesWithPattern_rec($regex, $jc, $clauses);
511  }
512  }
513 
514  return $clauses;
515 
516  }
517 
518  function _findClausesWithPattern_rec( $regex, &$root, &$clauses){
519 
520  foreach ( array('arg_1','arg_2') as $arg){
521 
522  if ( !isset( $root[$arg]) ) continue;
523  $type = (isset( $root[$arg]['type'] ) ? $root[$arg]['type'] : null);
524 
525  switch ($type){
526  case 'subclause':
527 
528  $this->_findClausesWithPattern_rec($regex, $root[$arg]['value'], $clauses);
529  break;
530 
531  case 'text_val':
532  case 'int_val':
533  case 'real_val':
534 
535  if ( preg_match($regex, $root[$arg]['value']) ) array_push($clauses, $root);
536  break;
537 
538  default:
539 
540  $this->_findClausesWithPattern_rec($regex, $root[$arg], $clauses);
541 
542 
543  }
544  }
545  }
546 
547  function removeWhereClausesWithTable($table){
548 
549  $clauses = $this->findWhereClausesWithTable($table);
550  foreach ($clauses as $clause){
551  $this->removeWhereClause($clause);
552  }
553 
554  }
555 
556 
557  function removeJoinClausesWithTable($table){
558  $clauses = $this->findJoinClausesWithTable($table);
559  foreach ($clauses as $clause){
560  $this->removeJoinClause($clause);
561  }
562 
563  }
564 
565  function removeWhereClausesWithPattern($regex){
566  $clauses = $this->findWhereClausesWithPattern($regex);
567  foreach ($clauses as $clause){
568  $this->removeWhereClause($clause);
569  }
570 
571  }
572 
573  function removeJoinClausesWithPattern($regex){
574  $clauses = $this->findJoinClausesWithPattern($regex);
575  foreach ($clauses as $clause){
576  $this->removeJoinClause($clause);
577  }
578  }
579 
585  function packTables($exempt=array()){
586  $selected_tables = array();
587 
588  // Find the tables that are selected -- these are absolutely necessary
589  foreach ($this->_data['column_names'] as $column){
590  $selected_tables[] = $this->getTableName($column);
591  $selected_tables = array_unique($selected_tables);
592  }
593 
594  $removed_tables = array();
595  foreach ( $this->_data['table_names'] as $index=>$table_name){
596 
597  // If this table is in the "exempt" list, we leave it alone
598  if ( in_array($table_name, $exempt) ) continue;
599 
600 
601  // If this table is selected, it is needed -- so skip it
602  if ( in_array($table_name, $selected_tables) ) continue;
603 
604 
605 
606  // IF this table is involved in a nontrivial join, it is exempt
607  $found = $this->findJoinClausesWithTable($table_name);
608  if ( count($found) > 0 ) continue;
609 
610  // If this table is involved in any where clauses, then it is needed
611  $found = $this->findWhereClausesWithTable( $table_name);
612  if ( count($found) > 0 ) continue;
613 
614 
615 
616  // At this point, the table appears to have no purpose in the query
617  $removed_tables[] = $table_name;
618  }
619 
620  $table_names = array();
621  $table_join = array();
622  $table_join_clause = array();
623  $table_aliases = array();
624  $newIndex = 0;
625  foreach ( $this->_data['table_names'] as $index=>$table_name){
626  if ( !in_array($table_name, $removed_tables) ){
627  $table_names[] = $table_name;
628  if ( $index > 0 and $newIndex > 0 ){
629  $table_join[] = $this->_data['table_join'][$index-1];
630  }
631  $table_join_clause[] = $this->_data['table_join_clause'][$index];
632  $table_aliases[] = $this->_data['table_aliases'][$index];
633  $newIndex++;
634  }
635  }
636 
637  $this->_data['table_names'] = $table_names;
638  $this->_data['table_aliases'] = $table_aliases;
639  $this->_data['table_join'] = $table_join;
640  $this->_data['table_join_clause'] = $table_join_clause;
641 
642  }
643 
644 
645  function fixColumns(){
646  if ( PEAR::isError($this->_data) ){
647  throw new Exception($this->_data->toString(), E_USER_ERROR);
648  }
649  for ($i=0; $i<count($this->_data['column_names']); $i++){
650  $name =& $this->_data['column_names'][$i];
651  if ( strpos($name, '.') === strlen($name)-1 ) $name .= '*';
652  unset($name);
653  }
654 
655  }
656 
657  function makeEquivalenceLabels(&$labels, &$values){
658 
659  $roots = array();
660  if ( isset( $this->_data['where_clause'] ) and is_array( $this->_data['where_clause'] )){
661  $roots[] =& $this->_data['where_clause'];
662  }
663  if ( isset( $this->_data['table_join_clause'] ) and is_array( $this->_data['table_join_clause']) ){
664  foreach ( $this->_data['table_join_clause'] as $clause ){
665  if ( is_array($clause) ){
666  $roots[] = $clause;
667  }
668  }
669  }
670  foreach ($roots as $root){
671  $this->_makeEquivalenceLabels($labels, $values, $root);
672  }
673 
674 
675  }
676 
677 
678  function _makeEquivalenceLabels_rec( &$labels, &$values, &$root){
679 
680 
681  }
682 
683 
684  function translate_select_query(){
685 
686  }
687 
688  function getTableNames(){
689  $tables = array();
690  $this->getTableNames_rec($this->_data, $tables);
691  return array_unique($tables);
692  }
693 
694  function getTableNames_rec(&$root, &$tables){
695  if ( isset($root['table_names']) ){
696  foreach ($root['table_names'] as $table){
697  $tables[] = $table;
698  }
699  }
700  foreach ( $root as $key=>$val ){
701  if ( is_array($val) ){
702  $this->getTableNames_rec($val, $tables);
703  }
704  }
705  return true;
706  }
707 
708 
709 
710 
711 
712 }
Generated on Fri Mar 22 2013 09:40:34 for Xataface by   doxygen 1.8.1.2