Coding Challenge

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Coding Challenge

Post 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
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

When will those damn non-OOP guys realize their technique is inferior? :roll:


;)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;
	}
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ;)
Post Reply