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());
}
}
}
}
}
