Page 1 of 1

Coding Challenge

Posted: Sun Oct 17, 2004 11:28 pm
by McGruff
Various discussions on sitepoint about OOP v non-OOP programming finally spilled over into a coding challenge: build an sql generator for a boolean search option in your chosen style.

Details here: http://www.sitepoint.com/forums/showthread.php?t=202647

Posted: Mon Oct 18, 2004 12:53 am
by m3mn0n
When will those damn non-OOP guys realize their technique is inferior? :roll:


;)

Posted: Mon Oct 18, 2004 2:08 am
by timvw
I'm in between camps. I think OOP should be used where it can be useful, but that does not mean that everything should be a class.

Here is the dml_read method from the SQL_Data class i'm working on, it's an implementation of my abstract class Data.

Code: Select all

/**
	* Performs the actual read using Data Manipulating Language.
	* 
	* @param $what An array with attribute=value pairs as requirements
	* @param $order the order that should be used on the returned pairs
	* @return returns the generated query
        *
        * $requirements = array();
        * $requirements[] = array('surname' => 'Tim', 'name' => 'Van Wassenhove');
        * $requirements[] = array('name' => 'Mc%');
        *  echo dml_read($requirements);
        *
        * -> SELECT * FROM foo WHERE ('surname'='Tim' AND 'name'='Van Wassenhove') OR ('name' LIKE 'MC%');
        *
	*/
	protected function dml_read($what, $order = null)
	{
		$sql = $this->sql;
		
		// build WHAT clause
		if (!empty($sql['what']))
		{
			$sql_what = "SELECT {$sql['what']}";
		}
		else
		{
			$sql_what = "SELECT *";
		}
		
		// build FROM clause
		if (!empty($sql['from']))
		{
			$sql_from = "FROM {$sql['from']}";
		}
		else
		{
			$sql_from = "FROM {$sql['table']}";
		}
		
		// build WHERE clause
		$sql_where = "";
		if (!empty($sql['where']))
		{
			$sql_where = "({$sql['where']}) AND (";
		}
		
		// make sure $what is a multidimensional array
		if (!array_key_exists(0, $what) || !is_array($what[0]))
		{
			$clean_what[] = $what;
		}
		else
		{
			$clean_what = $what;
		}
		
		// now loop through the multidimensional $what
		foreach($clean_what as $what)
		{
			if (count($what) > 0)
			{
				$sql_where .= "(";
		
				foreach($what as $key => $val)
				{
					// if the tablename is not specified yet, 
					// check if we can prepend it to the key
					if (!strstr($key, "."))
					{
						$dictionary = $this->getDictionary();
						$specifications = $dictionary->getSpecifications();
						
						if (array_key_exists($key, $specifications))
						{
							$nkey = $this->dbms_escape_column($sql['table']);
							$nkey .= ".";
							$nkey .= $this->dbms_escape_column($key);
							$key = $nkey;
						}
						else
						{
							$key = $this->dbms_escape_column($key);
						}
					}
					else
					{
						$key = $this->dbms_escape_column($key);
					}
					
					if(preg_match("/^LIKE (.*)/i", $val, $matches))
					{
						$val = $this->dbms_escape_value($matches[1]);
						$sql_where .= "$key LIKE $val";
					}

					elseif (preg_match("/\%/", $val))
					{
						$val = $this->dbms_escape_value($val);
						$sql_where .= "$key LIKE $val";
					}
					
					elseif(preg_match("/^BETWEEN (.*) AND (.*)/i", $val, $matches))
        			{
						$val1 = $this->dbms_escape_value($matches[1]);
						$val2 = $this->dbms_escape_value($matches[1]);
        				$sql_where .= "$key BETWEEN $val1 AND $val2";
        			}
        			
        			elseif (preg_match("/^<> (.*)/", $val, $matches))
        			{
						$val = $this->dbms_escape_value($val);						
        				$sql_where .= "$key <> $val";
        			}
        	
        			elseif (preg_match("/^>= (.*)/", $val, $matches))
        			{
						$val = $this->dbms_escape_value($matches[1]);        			
        				$sql_where .= "$key >= $val";         	
        			}
        	
        			elseif (preg_match("/^> (.*)/", $val, $matches))
        			{
						$val = $this->dbms_escape_value($matches[1]); 
        				$sql_where .= "$key > $val";        	
        			}
        	
        			elseif (preg_match("/^<= (.*)/", $val, $matches))
        			{
						$val = $this->dbms_escape_value($matches[1]); 
        				$sql_where .= "$key <= $val"; 
        			}

					elseif (preg_match("/^< (.*)/", $val, $matches))
        			{
						$val = $this->dbms_escape_value($matches[1]); 
        				$sql_where .= "$key < $val";        	
        			}        	
					
					else
					{
						$val = $this->dbms_escape_value($val); 
						$sql_where .= "$key=$val";
					}
					
					$sql_where .= " AND ";
				}
				$sql_where = rtrim($sql_where, " AND ");
				$sql_where .= ") OR ";
			}
		}
		$sql_where = rtrim($sql_where, " OR ");
				
		if (!empty($sql['where']))
		{
			$sql_where .= ") ";
		}
		
		$sql_where = str_replace("AND ()", "", $sql_where);
					
		if (!empty($sql_where))
		{
			$sql_where = "WHERE $sql_where";
		}
		
		// build GROUP BY clause
		if (!empty($sql['groupby']))
		{
			$sql_groupby = "GROUP BY {$sql['groupby']}";
		}
		else
		{
			$sql_groupby = "";
		}

		// build HAVING clause
		if (!empty($sql['having']))
		{
			$sql_having = "HAVING {$sql['having']}";
		}
		else
		{
			$sql_having = "";
		}
		
		// build ORDER BY clause
		if (!empty($order))
		{
			$sql_order = "ORDER BY {$order['column']} {$order['direction']}";
		}	
		else
		{
			$sql_order = "";
		}
		
		$query = "$sql_what $sql_from $sql_where $sql_groupby $sql_having $sql_order"; 	
		return $query;
	}

Posted: Mon Oct 18, 2004 2:19 am
by timvw
crap, after finding and reading the challenge, my code is irrelevant :p


Imho, McGruff's code looks more like an implementation of the State Pattern than the IteratorWhatever :)

Anyway, if i see a parsing problem, i always think of compilers, and cheat with having a look at the code/discussions in news://comp.compilers ;)