/**
* Returns a string representing the SQL query corresonding to the specified Query object.
* @param object QueryInterface $query The object from which to generate the SQL string.
* @return mixed Either a string (this would be the case, normally) or an array of strings.
* Each string is corresponding to an SQL query.
* @static
* @access public
*/
static function generateSQLQuery(Query $query)
{
switch ($query->getType()) {
case INSERT:
$result = MySQL_SQLGenerator::generateInsertSQLQuery($query);
break;
case UPDATE:
$result = MySQL_SQLGenerator::generateUpdateSQLQuery($query);
break;
case DELETE:
$result = MySQL_SQLGenerator::generateDeleteSQLQuery($query);
break;
case SELECT:
$result = MySQL_SQLGenerator::generateSelectSQLQuery($query);
break;
case GENERIC:
$result = MySQL_SQLGenerator::generateGenericSQLQuery($query);
break;
default:
throw new DatabaseException("Unsupported query type.");
}
// switch
// echo "<pre>\n";
// echo $result;
// echo "</pre>\n";
return $result;
}
/**
* Tests the generateSQLQuery() with WHERE clause.
*/
function test_Delete_With_Where()
{
$table = "person";
$condition = "user_uname = 'dradichk'";
$this->query->reset();
$this->query->setTable($table);
$this->query->addWhere($condition);
$this->query->addWhere($condition);
$sql = "DELETE\n FROM person\n WHERE user_uname = 'dradichk'\n AND user_uname = 'dradichk'\n";
$sqlFromObject = MySQL_SQLGenerator::generateSQLQuery($this->query);
$this->assertEqual($sql, $sqlFromObject);
}
/**
* Tests many query.
*/
function test_Many_Query()
{
$this->query->reset();
$arr = array();
$sql = "SELECT shit1 FROM crap1";
$arr[] = $sql;
$this->query->addSQLQuery($sql);
$sql = "SELECT shit2 FROM crap2";
$this->query->addSQLQuery($sql);
$arr[] = $sql;
$sql = "SELECT shit3 FROM crap3";
$this->query->addSQLQuery($sql);
$arr[] = $sql;
$sqlFromObject = MySQL_SQLGenerator::generateSQLQuery($this->query);
$this->assertEqual($arr, $sqlFromObject);
}
/**
* Returns a string representing the SQL query corresonding to the specified Query object.
* @param object QueryInterface $query The object from which to generate the SQL string.
* @return mixed Either a string (this would be the case, normally) or an array of strings.
* Each string is corresponding to an SQL query.
* @static
* @access public
*/
static function generateSQLQuery(Query $query)
{
switch ($query->getType()) {
case INSERT:
return PostgreSQL_SQLGenerator::generateInsertSQLQuery($query);
break;
case UPDATE:
return PostgreSQL_SQLGenerator::generateUpdateSQLQuery($query);
break;
case DELETE:
return PostgreSQL_SQLGenerator::generateDeleteSQLQuery($query);
break;
case SELECT:
return PostgreSQL_SQLGenerator::generateSelectSQLQuery($query);
break;
case GENERIC:
return MySQL_SQLGenerator::generateGenericSQLQuery($query);
break;
default:
throw new DatabaseException("Unsupported query type.");
}
// switch
}
/**
* Tests the generateSQLQuery method for inserting many rows.
*/
function test_Generate_SQL_Query_Many_Inserts()
{
$table = "user";
$columns = array("user_id", "user_uname", "user_fname");
$this->query->reset();
$this->query->setTable($table);
$this->query->setColumns($columns);
$values = array("5", "'dradichk'", "'Dobromir'");
$this->query->addRowOfValues($values);
$values = array("6", "'afranco'", "'Adam'");
$this->query->addRowOfValues($values);
$values = array("7", "'movsjani'", "'Maks'");
$this->query->addRowOfValues($values);
$sql = "INSERT\n INTO user (user_id, user_uname, user_fname)\nVALUES (5, 'dradichk', 'Dobromir'),\n (6, 'afranco', 'Adam'),\n (7, 'movsjani', 'Maks')\n";
$sqlFromObject = MySQL_SQLGenerator::generateSQLQuery($this->query);
$this->assertEqual($sql, $sqlFromObject);
}
/**
* Print the MySQL version of a query
*
* @param object Query $query
* @return void
* @access public
* @since 3/2/05
* @static
*/
static function printQuery($query)
{
print "\n<pre>";
print_r(MySQL_SQLGenerator::generateSQLQuery($query));
print "\n</pre>";
}
/**
* Answer the string SQL for the query
*
* @param object $query
* @return string
* @access public
* @since 11/14/06
*/
function generateSQL($query)
{
return MySQL_SQLGenerator::generateSQLQuery($query);
}
/**
* Tests the generateSQLQuery() method for updating multiple columns that meet certain condition
*/
function test_Update_With_Where_And_Many_Update_Fields()
{
$table = "person";
$columns = array("user_uname", "user_fname", "user_id");
$values = array("'dradichk'", "'Dobo'", "5");
$condition = "user_id = 3";
$this->query->reset();
$this->query->setTable($table);
$this->query->setColumns($columns);
$this->query->setValues($values);
$this->query->addWhere($condition);
$this->query->addWhere($condition);
$sql = "UPDATE person\n SET user_uname = 'dradichk',\n user_fname = 'Dobo',\n user_id = 5\n WHERE user_id = 3\n AND user_id = 3\n";
$sqlFromObject = MySQL_SQLGenerator::generateSQLQuery($this->query);
$this->assertEqual($sql, $sqlFromObject);
}
/**
* Tests a SELECT with joins, and multiple columns and tables including WHERE, ORDER BY, and GROUP BY clauses.
*/
function test_Many_Tables_And_Many_Columns_All_Clauses_All_Joins()
{
$this->query->reset();
$this->query->setColumns(array("user_id AS 1"));
$this->query->addColumn("user_uname", "username", "db");
$this->query->addColumn("COUNT(*)", "c");
$this->query->addTable("user", NO_JOIN);
$this->query->addTable("class", INNER_JOIN, "user.user_weight = class.class_id");
$this->query->addTable("person", NO_JOIN, "", "PERSON");
$this->query->addTable("tree", LEFT_JOIN, "person.person_id = tree.tree_height - 10");
$this->query->addTable("bush", RIGHT_JOIN, "tree.tree_leaves = 3000", "BUSH");
$this->query->addTable("sand", NO_JOIN);
$this->query->addWhere("user_id = 5");
$this->query->addWhere("user_id = 8", _AND);
$this->query->addWhere("user_id = 10", _OR);
$this->query->addWhere("user_id = 12", _OR);
$this->query->setGroupBy(array("user_id", "user_sex"), "user_age = 38");
$this->query->addOrderBy("user_lname", ASCENDING);
$this->query->addOrderBy("user_fname", DESCENDING);
$this->query->setDistinct(true);
$this->query->limitNumberOfRows(100);
$this->query->startFromRow(10);
$tables = " user\n INNER JOIN class ON user.user_weight = class.class_id,\n person AS PERSON\n LEFT JOIN tree ON person.person_id = tree.tree_height - 10\n RIGHT JOIN bush AS BUSH ON tree.tree_leaves = 3000,\n sand";
$sql = "SELECT DISTINCT\n user_id AS 1,\n db.user_uname AS username,\n COUNT(*) AS c\n FROM{$tables}\n WHERE user_id = 5\n AND user_id = 8\n OR user_id = 10\n OR user_id = 12\n GROUP BY\n user_id,\n user_sex\nHAVING user_age = 38\n ORDER BY\n user_lname ASC,\n user_fname DESC\n LIMIT 9, 100\n";
$sqlFromObject = MySQL_SQLGenerator::generateSQLQuery($this->query);
$this->assertEqual($sql, $sqlFromObject);
}
