/**
* 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 reset() method and error conditions.
*/
function test_Reset()
{
$table = "person";
$condition = "user_uname = 'dradichk'";
$this->query->reset();
$this->query->setTable($table);
$this->query->setWhere($condition);
$this->query->reset();
try {
$sqlFromObject = PostgreSQL_SQLGenerator::generateSQLQuery($this->query);
} catch (DatabaseException $e) {
}
if (isset($result)) {
$this->assertTrue(false, "\$sqlFromObject should be null.");
}
}
/**
* 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 = "\n\tuser\n\t\tINNER JOIN\n\tclass\n\t\tON user.user_weight = class.class_id,\n\tperson AS PERSON\n\t\tLEFT JOIN\n\ttree\n\t\tON person.person_id = tree.tree_height - 10\n\t\tRIGHT JOIN\n\tbush AS BUSH\n\t\tON tree.tree_leaves = 3000,\n\tsand";
$sql = "SELECT DISTINCT\n\tuser_id AS 1,\n\tdb.user_uname AS username,\n\tCOUNT(*) AS c\nFROM{$tables}\nWHERE\n\tuser_id = 5\n\t\tAND\n\tuser_id = 8\n\t\tOR\n\tuser_id = 10\n\t\tOR\n\tuser_id = 12\nGROUP BY\n\tuser_id,\n\tuser_sex\nHAVING\n\tuser_age = 38\nORDER BY\n\tuser_lname ASC,\n\tuser_fname DESC\nLIMIT 100\nOFFSET 9\n";
$sqlFromObject = PostgreSQL_SQLGenerator::generateSQLQuery($this->query);
$this->assertEqual($sql, $sqlFromObject);
}
/**
* Tests the reset() method and error conditions.
*/
function test_Reset()
{
$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->setWhere($condition);
$this->query->reset();
try {
$sqlFromObject = PostgreSQL_SQLGenerator::generateSQLQuery($this->query);
} catch (DatabaseException $e) {
}
if (isset($result)) {
$this->assertTrue(false, "\$sqlFromObject should be null.");
}
// ----- test exception when # fields does not match # columns
$table = "person";
$columns = array("user_uname", "user_fname", "user_id");
$values = array("'dradichk'", "'Dobo'", "5", "6");
$condition = "user_id = 3";
$this->query->reset();
$this->query->setTable($table);
$this->query->setColumns($columns);
$this->query->setValues($values);
$this->query->setWhere($condition);
try {
$sqlFromObject = PostgreSQL_SQLGenerator::generateSQLQuery($this->query);
} catch (DatabaseException $e) {
}
if (isset($result)) {
$this->assertTrue(false, "\$sqlFromObject should be null.");
}
}
/**
* Answer the string SQL for the query
*
* @param object $query
* @return string
* @access public
* @since 11/14/06
*/
function generateSQL($query)
{
return PostgreSQL_SQLGenerator::generateSQLQuery($query);
}
/**
* Tests the reset() method and error conditions.
*/
function test_Reset()
{
$table = "user";
$columns = array("user_id", "user_uname", "user_fname");
$values = array("5", "'dradichk'", "'Dobromir'");
$this->query->reset();
$this->query->setTable($table);
$this->query->setColumns($columns);
$this->query->addRowOfValues($values);
$this->query->reset();
try {
$sqlFromObject = PostgreSQL_SQLGenerator::generateSQLQuery($this->query);
} catch (DatabaseException $e) {
}
if (isset($result)) {
$this->assertTrue(false, "\$sqlFromObject should be null.");
}
// ------- now test reset with many insert rows
$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);
$this->query->reset();
try {
$sqlFromObject = PostgreSQL_SQLGenerator::generateSQLQuery($this->query);
} catch (DatabaseException $e) {
}
if (isset($result)) {
$this->assertTrue(false, "\$sqlFromObject should be null.");
}
// ----- test exception when # fields does not match # columns
$table = "user";
$columns = array("user_id", "user_uname", "user_fname", "user_lname");
$values = array("5", "'dradichk'", "'Dobromir'");
$this->query->reset();
$this->query->setTable($table);
$this->query->setColumns($columns);
$this->query->addRowOfValues($values);
try {
$sqlFromObject = PostgreSQL_SQLGenerator::generateSQLQuery($this->query);
} catch (DatabaseException $e) {
}
if (isset($result)) {
$this->assertTrue(false, "\$sqlFromObject should be null.");
}
}