/**
  * 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);
 }
Example #6
0
 /**
  * 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);
 }