mysql_vistable

The Inline Google Spreadsheet Viewer mysql vistable class.

Defined (1)

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

/lib/vistable.php  
  1. class mysql_vistable extends vistable { 
  2. private $tables; 
  3. private $mode = 0; 
  4. private $where = null; 
  5.  
  6. public function __construct($tqx, $tq, $tqrt, $tz, $locale, $extra=NULL) { 
  7. parent::__construct($tqx, $tq, $tqrt, $tz, $locale, $extra); 
  8.  
  9. public function setup_database($tables, $fields, $where) 
  10. $this->tables = $tables; 
  11. $this->fields = $fields; 
  12. $this->where = $where; 
  13.  
  14. $q = "SELECT"; 
  15. $fields = array(); 
  16. foreach ($this->fields as $key => $value) { 
  17. $f = ""; 
  18. $sql_field = NULL; 
  19. if (is_string($value)) { 
  20. $sql_field = $value; 
  21.  
  22. if (is_array($value)) { 
  23. if (isset($value["sql_field"])) { 
  24. $sql_field = $value["sql_field"]; 
  25. $value[TYPE] = SIMPLE; 
  26. $value[VALUE] = $key; 
  27. $this->fields[$key] = $value; 
  28. } else { 
  29. $this->fields[$key] = array(TYPE=>SIMPLE, VALUE=>$key); 
  30. if ($sql_field) { 
  31. $f .= " $sql_field AS"; 
  32. $this->fields[$key]["sql_field"] = $sql_field; 
  33. $f .= " $key"; 
  34. $fields[] = $f; 
  35. $q .= implode(", ", $fields)." FROM ".$this->tables; 
  36. $q .= " LIMIT 0"; 
  37. $result = mysql_query($q); 
  38. if ($result) { 
  39. $sql_types = array( 
  40. "number" => "INT|FLOAT|DOUBLE|REAL|DECIMAL|NUMERIC|BIT",  
  41. "boolean" => "BOOL",  
  42. "date" => '^DATE$',  
  43. "timeofday" => '^TIME$',  
  44. "datetime" => '^DATETIME|TIMESTAMP$',  
  45. ); 
  46. $ncol = mysql_num_fields($result); 
  47. for ($i=0; $i < $ncol; $i++) { 
  48. $fname = mysql_field_name($result, $i); 
  49. if (!isset($this->fields[$fname]['type'])) { 
  50. $ftype = mysql_field_type($result, $i); 
  51. $type = 'string'; 
  52. foreach ($sql_types as $t => $pat) { 
  53. if (preg_match("/$pat/i", $ftype)) { 
  54. $type = $t; 
  55. break; 
  56. $this->fields[$fname]['type'] = $type; 
  57. if (!isset($this->fields[$fname]['label'])) { 
  58. $this->fields[$fname]['label'] = $fname; 
  59.  
  60. protected function pre_write($q) 
  61. $v = $q[VALUE]; 
  62. if ($this->mode != 0) { 
  63. switch ($q[TYPE]) { 
  64. case OPERATOR: 
  65. switch ($v) { 
  66. case "matches": 
  67. $e0 = $this->write_expr($q[0]); 
  68. $e1 = $this->write_expr($q[1]); 
  69. return "$e0 REGEXP $e1"; 
  70. case "contains": 
  71. $r = $this->write_func("LOCATE", $q, TRUE); 
  72. $r .= "!=0"; 
  73. return $r; 
  74. case "starts_with": 
  75. $r = $this->write_func("LOCATE", $q, TRUE); 
  76. $r .= "=1"; 
  77. return $r; 
  78. case "ends_with": 
  79. $e0 = $this->write_expr($q[0]); 
  80. $e1 = $this->write_expr($q[1]); 
  81. return "RIGHT($e0, LENGTH($e1))=$e1"; 
  82. case FUNCT: 
  83. switch ($v) { 
  84. case 'timeofday': 
  85. return $this->write_func("time", $q); 
  86. case 'datetime': 
  87. return "(".$this->write_expr($q[0]).")"; 
  88. case 'date': 
  89. case 'todate': 
  90. return $this->write_func("date", $q); 
  91. case 'now': 
  92. if ($this->gmt_offset) { 
  93. return "(now()+INTERVAL "+$this->gmt_offset+" SECOND)"; 
  94. break; 
  95. break; 
  96. case SIMPLE: 
  97. if (isset($q["sql_field"])) { 
  98. return $q["sql_field"]; 
  99. } else { 
  100. return $v; 
  101. return NULL; 
  102.  
  103. private function sql_expr($q, $mode) 
  104. $this->mode = $mode; 
  105. $ret = $this->write_expr($q); 
  106. $this->mode = 0; 
  107. return $ret; 
  108.  
  109. private function vis_query2sql_query($query, &$cols) 
  110. $fields = array(); 
  111. $cols = array(); 
  112. $order = array(); 
  113. foreach ($query['select'] as $value) { 
  114. $f = $this->sql_expr($value, 1); 
  115. $as = $this->sql_expr($value, 0); 
  116. $type = isset($value['type']) ? $value['type'] : 'string'; 
  117. if ($f != $as) { 
  118. $f .= " AS `$as`"; 
  119. $label = isset($value['label']) ? $value['label'] : $as; 
  120. $fields[$as] = $f; 
  121. $col = array('id' => $as, 'label' => $label, 'type' => $type); 
  122. if ($type != 'string' && isset($value['format'])) { 
  123. $col['pattern'] = $value['format']; 
  124. $cols[] = $col; 
  125.  
  126. if ($this->debug) { 
  127. echo "\n\nsortcol: ", $this->params['sortcol'], "\n\n\n"; 
  128. if (isset($this->params['sortcol']) && 
  129. isset($fields[$this->params['sortcol']])) 
  130. $order[] = "`".$this->params['sortcol']."` ".(!strcasecmp($this->params['sortdir'], 'desc') ? 'desc' : 'asc'); 
  131.  
  132. if (isset($query['order'])) { 
  133. foreach ($query['order'] as $value) { 
  134. $dir = isset($value['dir']) && $value['dir'] == 'desc' ? 'desc' : 'asc'; 
  135. $as = $this->sql_expr($value, 0); 
  136. if (!isset($fields[$as])) { 
  137. $o = $this->sql_expr($value, 1); 
  138. } else { 
  139. $o = "`$as`"; 
  140. $order[] = "$o $dir"; 
  141.  
  142. $select = "SELECT " . implode(", ", $fields); 
  143. $q = " FROM ".$this->tables; 
  144. if ($query['where']) { 
  145. $q .= " WHERE(".$this->sql_expr($query['where'], 1).")"; 
  146. if ($this->where) { 
  147. $q .= "AND(".$this->where.")"; 
  148. } else if ($this->where) { 
  149. $q .= " WHERE ".$this->where; 
  150.  
  151. if ($query['group']) { 
  152. $q .= " GROUP BY "; 
  153. $fields = array(); 
  154. foreach ($query['group'] as $value) { 
  155. $fields[] = $this->sql_expr($value, 1); 
  156. $q .= implode(", ", $fields); 
  157. if ($query['having']) { 
  158. $q .= " HAVING ".$this->sql_expr($query['having'], 1); 
  159.  
  160. $total = -1; 
  161. if ($this->needs_total_rows) { 
  162. $t = "SELECT count(*)"; 
  163. if ($query['group']) { 
  164. $t .= " FROM ($select$q) AS t1"; 
  165. } else { 
  166. $t .= $q; 
  167. $data = mysql_query($t); 
  168. if (!$data || !($t = mysql_fetch_row($data))) { 
  169. $this->error("internal_error", "query_failed", "query `$t' failed:".mysql_error()); 
  170. return FALSE; 
  171. $total = (int)$t[0]; 
  172. $this->setup_rownums($query, $total); 
  173.  
  174. if (count($order)) { 
  175. $q .= " ORDER BY ".implode(", ", $order); 
  176.  
  177. $q = $select . $q; 
  178.  
  179. if ($this->num_rows >= 0 || $this->first_row > 0) { 
  180. $o = $this->first_row; 
  181. $l = $this->num_rows >= 0 ? $this->num_rows : 1000000000; 
  182. $q .= " LIMIT $o, $l"; 
  183.  
  184. if ($this->debug) { 
  185. echo "\n\nQ: $q\n\n\n"; 
  186. return $q; 
  187.  
  188. protected function fetch_table($query) 
  189. $use_query = isset($query['pivot']) || $this->params['nosql']; 
  190. if ($use_query) { 
  191. $q = "SELECT"; 
  192. $fields = array(); 
  193. foreach ($this->fields as $key => $value) { 
  194. $f = ""; 
  195. if (isset($value["sql_field"])) { 
  196. $f .= " {$value["sql_field"]} AS"; 
  197. $f .= " $key"; 
  198. $fields[] = $f; 
  199. $q .= implode(", ", $fields)." FROM ".$this->tables; 
  200. if ($query['where']) { 
  201. $q .= " WHERE ".$this->sql_expr($query['where'], 1); 
  202. } else { 
  203. $q = $this->vis_query2sql_query($query, $cols); 
  204. if ($q === FALSE) return FALSE; 
  205.  
  206. $data = mysql_query($q); 
  207.  
  208. if (!$data) { 
  209. $this->error("internal_error", "query failed", "query `$q' failed:".mysql_error()); 
  210. return FALSE; 
  211.  
  212. $rows = array(); 
  213. while ($row = mysql_fetch_assoc($data)) { 
  214. $rows[] = $row; 
  215.  
  216. if ($use_query) { 
  217. $cols = $this->query_filter($rows, $query); 
  218.  
  219. return array('cols' => $cols, 'rows' => $rows); 
  220. };