/** * 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."); } }