function select_query($table, $fields, $where = array(), $opt = array()) { if (SQLMakerUtil::ref($fields) != 'ARRAY') { trigger_error("SQLMaker::select_query: \$fields should be ARRAY", E_USER_ERROR); } $stmt = $this->new_select(array('select' => $fields)); $stmt->add_from($table); if (isset($opt['prefix'])) { $stmt->prefix($opt['prefix']); } if (!empty($where)) { $stmt->add_where($where); } if (isset($opt['order_by'])) { $o = $opt['order_by']; if (SQLMakerUtil::ref($o) == 'ARRAY') { foreach ($o as $order) { $stmt->add_order_by($order); } } else { $stmt->add_order_by($o); } } if (isset($opt['limit'])) { $stmt->limit($opt['limit']); } if (isset($opt['offset'])) { $stmt->offset($opt['offset']); } if (isset($opt['having'])) { $stmt->add_having($opt['having']); } if (isset($opt['for_update']) && $opt['for_update']) { $stmt->for_update(true); } return $stmt; }
protected function quote($label) { if (is_a($label, 'SQLMakerRawString')) { return $label->raw_string(); } return SQLMakerUtil::quote_identifier($label, $this->quote_char, $this->name_sep); }
protected function _add_index_hint($table, $alias = null) { if (SQLMakerUtil::ref($table) == 'ARRAY') { list($table, $alias) = $table; } $quoted = empty($alias) ? $this->quote($table) : "{$this->quote($table)} {$this->quote($alias)}"; if (!isset($this->index_hint[$table])) { return $quoted; } $hint = $this->index_hint[$table]; if (SQLMakerUtil::ref($hint['list']) == 'ARRAY' && !empty($hint['list'])) { $list = array(); foreach ($hint['list'] as &$l) { $list[] = $l; } return $quoted . ' ' . (isset($hint['type']) ? strtoupper($hint['type']) : 'USE') . ' INDEX (' . implode(', ', $list) . ')'; } return $quoted; }
protected function make_term($col, $val) { $ref_val = SQLMakerUtil::ref($val); if ($ref_val == 'ARRAY') { # make_term( array('foo', array(1, 2, 3)) ) => foo IN (1, 2, 3) $term = $this->quote($col) . " IN (" . implode(', ', array_fill(0, sizeof($val), '?')) . ')'; return array($term, $val); } else { if ($ref_val == 'HASH') { list($op, $v) = each($val); $op = strtoupper($op); $ref_v = SQLMakerUtil::ref($v); if (($op == 'IN' || $op == 'NOT IN') && $ref_v == 'ARRAY') { if (sizeof($v) == 0) { if ($op == 'IN') { # make_term('foo', array('in' => array( ))) => 0=1 return array('0=1', array()); } else { # make_term('foo', array('not in' => array( ))) => 1=1 return array('1=1', array()); } } else { # make_term('foo', array('in' => array(1, 2, 3))) => foo IN (1, 2, 3) $term = "{$this->quote($col)} {$op} (" . implode(', ', array_fill(0, sizeof($v), '?')) . ')'; return array($term, $v); } } else { if (($op == 'OR' || $op == 'AND') && $ref_v == 'ARRAY') { $bind = array(); $terms = array(); foreach ($v as $v2) { list($t, $b) = $this->make_term($col, $v2); $terms[] = "({$t})"; $bind = array_merge($bind, $b); } $term = implode(" {$op} ", $terms); return array($term, $bind); } else { if (($op == 'IN' || $op == 'NOT IN') && $ref_v == 'HASH') { list($t, $b) = each($v); $term = "{$this->quote($col)} {$op} ({$t})"; return array($term, $b); } else { if (($op == 'IN' || $op == 'NOT IN') && $ref_v == 'SQLMakerStatement') { $term = "{$this->quote($col)} {$op} ({$v->statement()})"; return array($term, $v->bind()); } else { if (($op == 'BETWEEN' || $op == 'NOT BETWEEN') && $ref_v == 'ARRAY') { if (sizeof($v) != 2) { trigger_error("USAGE: make_term('foo', array('between' => array(\$a, \$b)))", E_USER_ERROR); } $term = "{$this->quote($col)} {$op} ? AND ?"; return array($term, $v); } else { $term = "{$this->quote($col)} {$op} ?"; return array($term, array($v)); } } } } } } else { if ($ref_val == 'SQLMakerStatement') { $term = "{$this->quote($col)} {$val->statement()}"; return array($term, $val->bind()); } else { if (isset($val)) { # make_term('foo', true) => foo IS TRUE if (is_bool($val)) { $term = "{$this->quote($col)} IS " . ($val ? 'TRUE' : 'FALSE'); return array($term, array()); } else { $term = "{$this->quote($col)} = ?"; return array($term, array($val)); } } else { $term = "{$this->quote($col)} IS NULL"; return array($term, array()); } } } } }