visparser

The Inline Google Spreadsheet Viewer visparser class.

Defined (1)

The class is defined in the following location(s).

/lib/visparser.php  
  1. class visparser { 
  2. private $reserved; 
  3. private $keywords; 
  4. private $result; 
  5. private $tok; 
  6. private $query; 
  7. private $fields; 
  8. private $tok_save; 
  9. private $query_save; 
  10. public $error_message; 
  11. private $null; 
  12.  
  13. private $expr_cache; 
  14.  
  15. public function __construct(&$fields) { 
  16. $this->reserved = array( 
  17. "select",  
  18. "where",  
  19. "group",  
  20. "pivot",  
  21. "order",  
  22. "by",  
  23. "limit",  
  24. "offset",  
  25. "label",  
  26. "format",  
  27. "options",  
  28. "asc",  
  29. "desc",  
  30. "true",  
  31. "false",  
  32. "and",  
  33. "or",  
  34. "not",  
  35. "date",  
  36. "timeofday",  
  37. "datetime",  
  38. "timestamp"); 
  39.  
  40. $this->reserved = array_combine($this->reserved, array_keys($this->reserved)); 
  41.  
  42. $this->fields =& $fields; 
  43. $this->expr_cache = array(); 
  44. $this->null = NULL; 
  45.  
  46. public function next_token() 
  47. if ($this->error_message) { 
  48. return $this->tok; 
  49.  
  50. $q = $this->query; 
  51. $start = 0; 
  52. while (($ch = substr($q, $start, 1)) !== FALSE && ctype_space($ch)) { 
  53. $start++; 
  54.  
  55. if ($ch === FALSE) { 
  56. $this->tok = array(TYPE => "eof"); 
  57. return FALSE; 
  58.  
  59. $ret = FALSE; 
  60.  
  61. $end = $start; 
  62. if ($ch == '`') { 
  63. while (($ch = substr($q, ++$end, 1)) !== FALSE && $ch != '`') 
  64.  
  65. $ret = array(TYPE => ID, VALUE => substr($q, $start+1, $end - $start - 1)); 
  66. ++$end; 
  67. } else if (ctype_digit($ch) || $ch == '.') { 
  68. $ndot = $ch == '.' ? 1 : 0; 
  69. while (($ch = substr($q, ++$end, 1)) !== FALSE) { 
  70. if (!ctype_digit($ch)) { 
  71. if ($ndot || $ch != '.') { 
  72. break; 
  73. $ndot = 1; 
  74. if ($ch == 'e' || $ch == 'E') { 
  75. $ch = substr($q, ++$end, 1); 
  76. if ($ch == '+' || $ch == '-') { 
  77. $ch = substr($q, ++$end, 1); 
  78. while (ctype_digit($ch)) { 
  79. $ch = substr($q, ++$end, 1); 
  80. $ret = array(TYPE => NUMBER, VALUE => substr($q, $start, $end-$start)); 
  81. } else if (ctype_punct($ch)) { 
  82. $end = $start + 1; 
  83. $pos = strpos(", ()=*+-/", $ch); 
  84. if (!($pos === FALSE)) { 
  85. $ret = array(TYPE => OPERATOR, VALUE => $ch); 
  86. $end = $start+1; 
  87. } else { 
  88. $two = substr($q, $start, 2); 
  89. if ($two == "!=" || $two == "<>" || $two == "<=" || $two == ">=") { 
  90. $ret = array(TYPE => OPERATOR, VALUE => $two); 
  91. $end++; 
  92. } else if ($ch == '<' || $ch == '>') { 
  93. $ret = array(TYPE => OPERATOR, VALUE => $ch); 
  94. } else if ($ch == "'" || $ch == '"') { 
  95. $quote = $ch; 
  96. $ret = ""; 
  97. while (($ch = substr($q, $end++, 1)) !== FALSE) { 
  98. if ($ch == $quote) break; 
  99. if ($ch == "\\") { 
  100. $ch = substr($q, $end++, 1); 
  101. $ret .= $ch; 
  102. $ret = array(TYPE => STRING, VALUE => $ret); 
  103. } else { 
  104. $ret = array(TYPE => UNKNOWN); 
  105. } else if (ctype_alpha($ch) || $ch == '_') { 
  106. while (($ch = substr($q, ++$end, 1)) !== FALSE && 
  107. (ctype_alnum($ch) || $ch == '_')) 
  108. $ret = substr($q, $start, $end-$start); 
  109.  
  110. if (isset($this->reserved[$ret])) { 
  111. $ret = array(TYPE => RESID, VALUE => $ret); 
  112. } else { 
  113. $ret = array(TYPE => ID, VALUE => $ret); 
  114. } else { 
  115. $ret = array(TYPE => UNKNOWN); 
  116.  
  117. $this->query = substr($q, $end); 
  118. $this->tok = $ret; 
  119. return $ret; 
  120.  
  121. public function parse($q) { 
  122. $this->query = $q; 
  123. $this->result = array(); 
  124. $this->next_token(); 
  125.  
  126. $this->selectClause(); 
  127. $this->whereClause(); 
  128. $this->exprListClause('group', 'by'); 
  129. $this->exprListClause('pivot'); 
  130. $this->orderByClause(); 
  131. $this->exprClause('limit'); 
  132. $this->exprClause('offset'); 
  133. $this->labelClause('label'); 
  134. $this->labelClause('format'); 
  135. $this->optionsClause(); 
  136.  
  137. if ($this->tok[TYPE] != 'eof') { 
  138. $this->error("Unexpected token '{$this->tok[VALUE]}'\n"); 
  139.  
  140. if ($this->error_message) { 
  141. return FALSE; 
  142.  
  143. return $this->result; 
  144.  
  145. private function &getColumnTerm() 
  146. $val = $this->getColumnTermInt(); 
  147. return $this->getSharedExpr($val); 
  148.  
  149. private function &getColumnExpr($prec = 0) 
  150. $val = $this->getColumnExprInt($prec); 
  151. return $this->getSharedExpr($val); 
  152.  
  153. private function &getSharedExpr($val) 
  154. $tag = $val[TYPE].":".$val[VALUE]; 
  155. if (isset($this->expr_cache[$tag])) { 
  156. foreach ($this->expr_cache[$tag] as &$v) { 
  157. if ($this->matchExpr($val, $v)) { 
  158. return $v; 
  159. for ($i = 0; isset($val[$i]); $i++) { 
  160. if (isset($val[$i]['is_aggregate'])) { 
  161. $val['is_aggregate'] = 1; 
  162. break; 
  163. $this->expr_cache[$tag][] =& $val; 
  164. return $val; 
  165.  
  166. private function getFuncTerm($id) 
  167. $ret = array(TYPE => FUNCT, VALUE => $id); 
  168. $type = 'string'; 
  169. $this->next_token(); 
  170. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ')') { 
  171. while (TRUE) { 
  172. $arg =& $this->getColumnExpr(); 
  173. $type = $arg['type']; 
  174. $ret[] =& $arg; 
  175. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ', ') { 
  176. break; 
  177. $this->next_token(); 
  178. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ')') { 
  179. $this->error("Missing ')'"); 
  180. $this->next_token(); 
  181. switch ($id) { 
  182. case 'max': 
  183. case 'min': 
  184. $ret['is_aggregate'] = 1; 
  185. break; 
  186. case 'count': 
  187. case 'sum': 
  188. case 'avg': 
  189. $ret['is_aggregate'] = 1; 
  190. case 'year': 
  191. case 'month': 
  192. case 'day': 
  193. case 'hour': 
  194. case 'minute': 
  195. case 'second': 
  196. case 'millisecond': 
  197. case 'quarter': 
  198. case 'dayofweek': 
  199. case 'datediff': 
  200. $type = 'number'; 
  201. break; 
  202. case 'now': 
  203. $type = 'datetime'; 
  204. break; 
  205. case 'todate': 
  206. $type = 'date'; 
  207. break; 
  208. case 'upper': 
  209. case 'lower': 
  210. $type = 'string'; 
  211. break; 
  212. case 'datetime': 
  213. case 'date': 
  214. case 'timeofday': 
  215. $type = $id; 
  216. break; 
  217. $ret['type'] = $type; 
  218. return $ret; 
  219.  
  220. private function getColumnTermInt() 
  221. if ($this->tok[TYPE] == ID) { 
  222. $id = $this->tok[VALUE]; 
  223. $this->next_token(); 
  224. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != '(') { 
  225. if (!isset($this->fields[$id])) { 
  226. $this->error("`$id' is not a known field"); 
  227. $this->fields[$id]['is_used'] = TRUE; 
  228. return $this->fields[$id]; 
  229. return $this->getFuncTerm($id); 
  230.  
  231. if ($this->tok[TYPE] == OPERATOR && $this->tok[VALUE] == '(') { 
  232. $this->next_token(); 
  233. $ret = $this->getColumnExprInt(); 
  234. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ')') { 
  235. $this->error("Missing ')': {$this->tok[TYPE]} {$this->tok[VALUE]}"); 
  236. return array(TYPE => UNKNOWN); 
  237. $this->next_token(); 
  238. return $ret; 
  239.  
  240. if ($this->tok[TYPE] == NUMBER || $this->tok[TYPE] == STRING) { 
  241. $ret = array(TYPE => LITERAL, VALUE => $this->tok[VALUE],  
  242. 'type' => $this->tok[TYPE] == NUMBER ? 'number' : 'string'); 
  243. $this->next_token(); 
  244. return $ret; 
  245.  
  246. if ($this->tok[TYPE] == RESID) { 
  247. $v = $this->tok[VALUE]; 
  248. switch ($v) { 
  249. case 'not': 
  250. $this->next_token(); 
  251. $term = & $this->getColumnTerm(); 
  252. return array(TYPE => OPERATOR, VALUE => $v, 'type' => 'boolean', &$term); 
  253. case 'true': 
  254. case 'false': 
  255. return array(TYPE => LITERAL, VALUE => $this->tok[VALUE] == 'true', 'type' => 'boolean'); 
  256. case 'timestamp': 
  257. $v = 'datetime'; 
  258. case 'date': 
  259. case 'timeofday': 
  260. case 'datetime': 
  261. $this->next_token(); 
  262. if ($this->tok[TYPE] == OPERATOR && $this->tok[VALUE] == '(') { 
  263. return $this->getFuncTerm($v); 
  264. if ($this->tok[TYPE] != STRING) break; 
  265. $ret = array(TYPE => LITERAL, VALUE=>$this->tok[VALUE], 'type' => $v); 
  266. $this->next_token(); 
  267. return $ret; 
  268.  
  269. $this->error("Unexpected token '{$this->tok[VALUE]}'"); 
  270. return array(TYPE => UNKNOWN); 
  271.  
  272. private function getColumnExprInt($prec = 0) 
  273. $lhs =& $this->getColumnTerm(); 
  274.  
  275. $type = isset($lhs['type']) ? $lhs['type'] : 'string'; 
  276. while ($this->tok[TYPE] == OPERATOR || 
  277. $this->tok[TYPE] == RESID || 
  278. $this->tok[TYPE] == ID) 
  279. $p2 = -1; 
  280. $op = $this->tok[VALUE]; 
  281. switch ($op) { 
  282. case '*': 
  283. case '/': $p2 = 10; break; 
  284. case '+': 
  285. case '-': $p2 = 9; break; 
  286. case 'starts': 
  287. case 'ends': 
  288. if (8 < $prec) break; 
  289. $this->tok_save = $this->tok; 
  290. $this->query_save = $this->query; 
  291. $this->next_token(); 
  292. if ($this->tok[TYPE] != ID || $this->tok[VALUE] != 'with') { 
  293. $this->tok = $this->tok_save; 
  294. $this->query = $this->query_save; 
  295. break; 
  296. $op .= "_with"; 
  297. case '<': 
  298. case '>': 
  299. case '<=': 
  300. case '>=': 
  301. case 'starts_with': 
  302. case 'ends_with': 
  303. case 'contains': 
  304. case 'matches': $p2 = 8; break; 
  305. case '=': 
  306. case '!=': 
  307. case '<>': $p2 = 7; break; 
  308. case 'and': $p2 = 6; break; 
  309. case 'or': $p2 = 5; break; 
  310. if ($p2 < $prec) break; 
  311. if ($p2 <= 8 || $p2 == 100) $type = 'boolean'; 
  312. if ($p2 >=9 && $p2 <= 10) $type = 'number'; 
  313.  
  314. $this->next_token(); 
  315. $rhs =& $this->getColumnExpr($p2); 
  316. unset($val); 
  317. $val = array(TYPE=>OPERATOR, VALUE=>$op, 'type'=>$type, &$lhs, &$rhs); 
  318. unset($lhs); 
  319. $lhs =& $val; 
  320. return $lhs; 
  321.  
  322. private function error($msg) 
  323. $this->tok = array(TYPE => UNKNOWN); 
  324. if (!$this->error_message) { 
  325. $this->error_message = $msg; 
  326.  
  327. private function checkType($first, $second = NULL) 
  328. if ($this->tok[TYPE] != RESID || $this->tok[VALUE] != $first) return FALSE; 
  329. $this->next_token(); 
  330. if ($second) { 
  331. if ($this->tok[TYPE] != RESID || $this->tok[VALUE] != $second) { 
  332. $this->error("Missing keyword `$second'"); 
  333. return FALSE; 
  334. $this->next_token(); 
  335. return TRUE; 
  336.  
  337. private function matchExpr($e1, $e2) 
  338. if ($e1[TYPE] != $e2[TYPE]) return FALSE; 
  339. if ($e1[VALUE] != $e2[VALUE]) return FALSE; 
  340. for ($i = 0; isset($e1[$i]); $i++) { 
  341. if (!isset($e2[$i])) return FALSE; 
  342. if (!$this->matchExpr($e1[$i], $e2[$i])) return FALSE; 
  343. return !isset($e2[$i]); 
  344.  
  345. private function &findField($f) 
  346. $len = count($this->result['select']); 
  347. for ($i = 0; $i < $len; $i++) { 
  348. if ($this->matchExpr($f, $this->result['select'][$i])) {  
  349. return $this->result['select'][$i]; 
  350. return $this->null; 
  351.  
  352. private function selectClause() 
  353. $this->exprListClause('select'); 
  354. if (!isset($this->result['select'])) { 
  355. $this->result['select'] = array(); 
  356. foreach ($this->fields as $name => $value) { 
  357. $this->result['select'][] = $value; 
  358.  
  359. private function exprListClause($first, $second = NULL) 
  360. if (!$this->checkType($first, $second)) return; 
  361. if ($first == 'select' && $this->tok[TYPE] == OPERATOR && $this->tok[VALUE] == '*') { 
  362. $this->next_token(); 
  363. return; 
  364.  
  365. $this->result[$first] = array(); 
  366. while (true) { 
  367. $col_id =& $this->getColumnExpr(); 
  368. $this->result[$first][] =& $col_id; 
  369. if ($first != 'select') { 
  370. $field =& $this->findField($col_id); 
  371. if ($field) { 
  372. $field["is_".$first] = 1; 
  373. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ', ') { 
  374. break; 
  375. $this->next_token(); 
  376.  
  377. private function exprClause($first, $second = NULL) 
  378. if (!$this->checkType($first, $second)) return; 
  379. $this->result[$first] =& $this->getColumnExpr(); 
  380.  
  381. static private function decompose(&$expr, &$where, &$having) 
  382. if ($expr[TYPE] == OPERATOR && $expr[VALUE] == 'and') { 
  383. self::decompose($expr[0], $where, $having); 
  384. self::decompose($expr[1], $where, $having); 
  385. } else { 
  386. if ($expr['is_aggregate']) { 
  387. $having[] =& $expr; 
  388. } else { 
  389. $where[] =& $expr; 
  390.  
  391. private function &compose($list) 
  392. $e = NULL; 
  393. foreach ($list as &$term) { 
  394. if (!$e) { 
  395. $e =& $term; 
  396. } else { 
  397. $t = array(TYPE => OPERATOR, VALUE => 'and', 'type' => 'boolean',  
  398. &$e, &$term); 
  399. unset($e); 
  400. $e =& $this->getSharedExpr($t); 
  401. return $e; 
  402.  
  403. private function whereClause() 
  404. $this->exprClause('where'); 
  405. if (isset($this->result['where'])) { 
  406. if ($this->result['where']['is_aggregate']) { 
  407. $where = array(); 
  408. $having = array(); 
  409. self::decompose($this->result['where'], $where, $having); 
  410. $this->result['where'] = $this->compose($where); 
  411. $this->result['having'] = $this->compose($having); 
  412.  
  413. private function orderByClause() 
  414. if (!$this->checkType('order', 'by')) return; 
  415. $ix = 0; 
  416. $this->result['order'] = array(); 
  417. while (true) { 
  418. $col_id =& $this->getColumnExpr(); 
  419. if ($this->tok[TYPE] == RESID && 
  420. ($this->tok[VALUE] == 'asc' || $this->tok[VALUE] == 'desc')) 
  421. $col_id['dir'] = $this->tok[VALUE]; 
  422. $this->next_token(); 
  423. } else if (!$col_id['dir']) { 
  424. $col_id['dir'] = 'asc'; 
  425. $this->result['order'][] = &$col_id; 
  426. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ', ') { 
  427. break; 
  428. $this->next_token(); 
  429.  
  430. private function labelClause($label) 
  431. if (!$this->checkType($label)) return; 
  432. while (true) { 
  433. $col_id =& $this->getColumnExpr(); 
  434. if ($this->tok[TYPE] != STRING) { 
  435. $this->error("Missing $label string"); 
  436. return; 
  437. $field =& $this->findField($col_id); 
  438. if (!$field) { 
  439. $this->error("$label names a field that does not exist"); 
  440. return; 
  441. } else { 
  442. $field[$label] = $this->tok[VALUE]; 
  443. $this->next_token(); 
  444. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ', ') { 
  445. break; 
  446. $this->next_token(); 
  447.  
  448. private function optionsClause() 
  449. if (!$this->checkType('options')) return; 
  450. $this->result['options'] = array(); 
  451. while (true) { 
  452. if ($this->tok[TYPE] != ID) { 
  453. $this->error("Bad option"); 
  454. return; 
  455. switch ($this->tok[VALUE]) { 
  456. case "no_format": 
  457. case "no_values": 
  458. $this->result['options'][$this->tok[VALUE]] = 1; 
  459. break; 
  460. default: 
  461. $this->error("Bad option"); 
  462. return; 
  463. $this->next_token(); 
  464. if ($this->tok[TYPE] != OPERATOR || $this->tok[VALUE] != ', ') { 
  465. break; 
  466. $this->next_token(); 
  467. };