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?

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
