/**
* With parameters, initialize the table and returns $this.
* Without parameters : returns table.
*
* @param string $table
* @return \Glue\DB\Fragment_Query_Insert
*/
public function table($table = null)
{
if (func_num_args() > 0) {
$this->table = \Glue\DB\DB::table($table, null);
return $this;
} else {
return $this->table;
}
}
/**
* Adds elements at the end of the update list.
*
* @param mixed $arg1 A column name, or a names => values mapping array.
* @param mixed $arg2 A value to be assigned to the column (can also be a fragment).
* @return \Glue\DB\Fragment_Item_UpdateList
*/
public function set($arg1, $arg2 = null)
{
if (is_string($arg1)) {
// Name, value pair given :
$this->push(new \Glue\DB\Fragment_Item_UpdateList($arg1, $arg2 instanceof \Glue\DB\Fragment ? $arg2 : \Glue\DB\DB::val($arg2)));
} else {
// Names => values mapping given :
foreach ($arg1 as $col => $val) {
$this->set($col, $val);
}
}
return $this;
}
/**
* Compiles Fragment_SQL fragments into an SQL string.
*
* @param \Glue\DB\Fragment_SQL $fragment
*
* @return string
*/
protected function compile_sql(\Glue\DB\Fragment_SQL $fragment)
{
// Get data from fragment :
$template = $fragment->sql();
$replacements = $fragment->replacements();
// Split template according to inline string litterals and identifiers :
$matches = preg_split("/('(?:''|[^'])*'|`(?:``|[^`])*`)/", $template, -1, PREG_SPLIT_DELIM_CAPTURE);
// Loop over matches and generate SQL :
$cn = $this;
$sql = '';
for ($i = 0; $i < count($matches); $i++) {
// Get string :
$part = $matches[$i];
// Tell apart delimiters from pieces :
if ($i % 2 === 0) {
// In-between string, we must make replacements :
$sql .= preg_replace_callback('/[?!]/', function ($matches) use($cn, &$replacements) {
// Get next replacement :
$replacement = array_shift($replacements);
// Replacement is a fragment ?
if ($replacement instanceof \Glue\DB\Fragment) {
return $cn->compile($replacement);
} else {
// Tell appart value from identifier replacements :
if ($matches[0] === '?') {
// Value :
return $cn->quote_value($replacement);
} else {
// Identifier :
if (is_array($replacement)) {
$replacement = array_map(array($cn, 'quote_identifier'), $replacement);
return implode('.', $replacement);
} else {
return $cn->quote_identifier($replacement);
}
}
}
}, $part);
} else {
// Delimiter string, we must quote it according to current connection conventions :
if ($part[0] === "'") {
// String litteral :
$sql .= $this->quote(\Glue\DB\DB::unquote($part));
} else {
// Identifier :
$sql .= $this->quote_identifier(\Glue\DB\DB::unquote_identifier($part));
}
}
}
return $sql;
}
private static function test_fragments()
{
$tests = array('value - string' => array(db::val("test'test"), "'test\\'test'"), 'value - integer' => array(db::val(10), "10"), 'value - array' => array(db::val(array("test'test", 10)), "('test\\'test',10)"), 'value - float' => array(db::val(10.5), "10.5"), 'value - boolean' => array(db::val(false), "FALSE"), 'value - null' => array(db::val(null), "NULL"), 'template - simple' => array(db::sql("test template"), "test template"), 'template - complex' => array(db::sql("test `q'sdf``a'zer``` testtest 'q`sdf''a`zer'''"), "test `q'sdf``a'zer``` testtest 'q`sdf\\'a`zer\\''"), 'template - complex with replacements' => array(db::sql("? test ? `q'?sd!f``a'zer``` test ! test ? 'q`sdf''a`zer''' !", 'a', 'b', 'c', 'd', array('e', 'f')), "'a' test 'b' `q'?sd!f``a'zer``` test `c` test 'd' 'q`sdf\\'a`zer\\'' `e`.`f`"), 'template - nested' => array(db::sql("? test ? ?", db::sql('toast'), db::sql('toast'), 10), "toast test toast 10"), 'boolean - simple' => array(db::bool("'test' = ?", "qsdf")->or("'test' IN ?", array('azer', 'qsdf')), "('test' = 'qsdf') OR ('test' IN ('azer','qsdf'))"), 'boolean - nested' => array(db::bool(db::bool("1=1")->or("2=2"))->and("3=3"), "((1=1) OR (2=2)) AND (3=3)"), 'boolean - not' => array(db::bool("1=1")->not(), "NOT ((1=1))"), 'boolean - not not' => array(db::bool("1=1")->not()->not(), "(1=1)"), 'table' => array($t = db::table('glusers', 'myalias'), "`glusers` AS `myalias`"), 'template - columns' => array(db::sql("{$t->id} < {$t->password} qsdf"), "`myalias`.`id` < `myalias`.`password` qsdf"));
$join = db::join(db::table('glusers', 't1'))->left(db::table('glprofiles', 't2'))->on('?=?', 'test1', 'test2')->or('2=2')->and('3=3')->right(db::table('glposts', 't3'))->on('1=1');
$tests['join simple'] = array($join, "`glusers` AS `t1` LEFT OUTER JOIN `glprofiles` AS `t2` ON ('test1'='test2') OR (2=2) AND (3=3) RIGHT OUTER JOIN `glposts` AS `t3` ON (1=1)");
$join2 = db::join(db::table('glusers', 't3'))->left($join)->on('5=5');
$tests['join nested'] = array($join2, "`glusers` AS `t3` LEFT OUTER JOIN (`glusers` AS `t1` LEFT OUTER JOIN `glprofiles` AS `t2` ON ('test1'='test2') OR (2=2) AND (3=3) RIGHT OUTER JOIN `glposts` AS `t3` ON (1=1)) ON (5=5)");
$alias = db::table('glusers', 'myalias');
$join3 = db::join(db::table('glprofiles', 't3'))->left($alias)->on('1=1');
$tests['join alias'] = array($join3, "`glprofiles` AS `t3` LEFT OUTER JOIN `glusers` AS `myalias` ON (1=1)");
$orderby = new \Glue\DB\Fragment_Builder_Orderby();
$orderby->orderby($t->login, array($t->password, \Glue\DB\DB::DESC))->orderby(array($t->email, \Glue\DB\DB::ASC));
$tests['orderby'] = array($orderby, "`myalias`.`login`, `myalias`.`password` DESC, `myalias`.`email` ASC");
$groupby = new \Glue\DB\Fragment_Builder_Groupby();
$groupby->groupby($t->login, DB::sql("({$t->password} || 'qsdf')"))->groupby($t->email);
$tests['groupby'] = array($groupby, "`myalias`.`login`, (`myalias`.`password` || 'qsdf'), `myalias`.`email`");
$select = new \Glue\DB\Fragment_Builder_SelectList();
$select->columns($t->login, DB::sql($t->password))->columns(array($t->email, 'myemail'))->columns($t->login);
$tests['select'] = array($select, "`myalias`.`login` AS ```myalias``.``login```, `myalias`.`password`, `myalias`.`email` AS `myemail`");
$select1 = db::select(array('users', 'test'))->where("1=1")->andwhere("2=2")->orwhere("3=3")->distinct();
$tests['query select basic'] = array($select1, "SELECT DISTINCT 1 FROM `users` AS `test` WHERE (1=1) AND (2=2) OR (3=3)");
$select2 = db::select(array('users', 'myusers'), $u)->where("{$u->login} = 'mylogin'");
$tests['query select alias'] = array($select2, "SELECT 1 FROM `users` AS `myusers` WHERE (`myusers`.`login` = 'mylogin')");
$select3 = db::select(array('users', null), $a)->left(array('users', 'myusers'), $b)->on("{$a->login} = {$b->login}");
$tests['query select no alias'] = array($select3, "SELECT 1 FROM `users` LEFT OUTER JOIN `users` AS `myusers` ON (`users`.`login` = `myusers`.`login`)");
$select4 = db::select(array('users', 'myusers'), $a)->orderby($a->login)->limit(30)->offset(20);
$tests['query select limit offset'] = array($select4, "SELECT 1 FROM `users` AS `myusers` ORDER BY `myusers`.`login` LIMIT 30 OFFSET 20");
$select5 = db::select(array('users', 'myusers'), $a)->groupby($a->login, $a->password)->having("count(*) > 1")->orderby($a->login, $a->password)->columns($a->login, $a->password);
$tests['query select group by having'] = array($select5, "SELECT `myusers`.`login` AS ```myusers``.``login```, `myusers`.`password` AS ```myusers``.``password``` FROM `users` AS `myusers` GROUP BY `myusers`.`login`, `myusers`.`password` HAVING (count(*) > 1) ORDER BY `myusers`.`login`, `myusers`.`password`");
$select5 = db::select(array('users', 'myusers'), $a)->groupby($a->login, $a->password)->having("count(*) > 1")->orderby($a->login, $a->password)->columns($a->login, $a->password);
$tests['query select group by having'] = array($select5, "SELECT `myusers`.`login` AS ```myusers``.``login```, `myusers`.`password` AS ```myusers``.``password``` FROM `users` AS `myusers` GROUP BY `myusers`.`login`, `myusers`.`password` HAVING (count(*) > 1) ORDER BY `myusers`.`login`, `myusers`.`password`");
$select6 = db::select(array('users', 'a'), $a)->left(array('users', 'b'), $b)->on("1=1")->andon("2=2")->right(array('users', 'c'), $c)->on("3=3")->oron("4=4");
$tests['query select andon oron'] = array($select6, "SELECT 1 FROM `users` AS `a` LEFT OUTER JOIN `users` AS `b` ON (1=1) AND (2=2) RIGHT OUTER JOIN `users` AS `c` ON (3=3) OR (4=4)");
$delete1 = db::delete('users', $a)->where("{$a->login} = 'test'")->orderby($a->login)->limit(30)->offset(20);
$tests['query delete'] = array($delete1, "DELETE FROM `users` WHERE (`users`.`login` = 'test') ORDER BY `users`.`login` LIMIT 30 OFFSET 20");
$update1 = db::update('users', $a)->set('login', 'test')->set('password', \Glue\DB\DB::sql(':pass'))->where("{$a->login} = 'test'")->orderby($a->login)->limit(30)->offset(20);
$tests['query update'] = array($update1, "UPDATE `users` SET `login` = 'test', `password` = :pass WHERE (`users`.`login` = 'test') ORDER BY `users`.`login` LIMIT 30 OFFSET 20");
$update2 = db::update('users', $a)->set(array('login' => 'test', 'password' => \Glue\DB\DB::sql(':pass')))->where("{$a->login} = 'test'")->orderby($a->login)->limit(30)->offset(20);
$tests['query update array'] = array($update2, "UPDATE `users` SET `login` = 'test', `password` = :pass WHERE (`users`.`login` = 'test') ORDER BY `users`.`login` LIMIT 30 OFFSET 20");
$insert1 = db::insert('users')->columns('login', 'password')->columns(array('id'))->values("test'1", "test'2", \Glue\DB\DB::sql(':test'))->values(array("a", "b", "c"), array("d", "e", "f"))->values(array(array("a", "b", "c"), array("d", "e", "f")));
$tests['query insert'] = array($insert1, "INSERT INTO `users` (`login`, `password`, `id`) VALUES ('test\\'1','test\\'2',:test),('a','b','c'),('d','e','f'),('a','b','c'),('d','e','f')");
// Checks :
foreach ($tests as $type => $data) {
list($f, $target) = $data;
echo "Testing fragments : " . $type . " ...";
if (db::cn()->compile($f) === $target) {
echo "ok \n";
} else {
echo '<b style="color:blue">error ! ' . db::cn()->compile($f) . " doesn't match target " . $target . "\n</b>";
return false;
}
}
return true;
}
/**
* Returns the connection of this table.
*
* @return \Glue\DB\Connection
*/
public function cn()
{
return \Glue\DB\DB::cn($this->cnid);
}
/**
* Returns identifier of given column quoted for inclusion in a template.
*
* @param string $column
*
* @return string
*/
public function column($column)
{
return \Glue\DB\DB::quote_identifier(array(empty($this->alias) ? $this->table : $this->alias, $column));
}