<?
// for execution uses a mysql wrapper class //

class ST_Proc {
	
	var $collection; //an array of stores procedure definitions
	// each element is an associative array with keys (name, params_in, params_out, values, comment, body)
	var $file;
	
	function ST_Proc ($file = 'default.sp') {
		$this->collection = array ();
		$this->file = $file;
	}
	
	function define_sp ($name = '', $params = array (), $body = '') {
		return false;
	}
	
	function alter_sp ($name = '', $params = array (), $body = '') {
		return false;
	}
	
	function delete_sp ($name = '') {
		return false;
	}
	
	function execute ($name = '', &$connection) {
		$found = false;
		$i=0;
		while (!$found and $i < sizeof ($this->collection)) {
			$sp = $this->collection[$i];
			if ($sp['name'] == $name) {
				$params = $sp['params_in'];
				$values = $sp['values'];
				$query = $sp['body'];
				$found = true;
			}
			$i++;
		}
		
		if ($found) {
			// preparing for replace
			for ($i=0; $i<sizeof($params); $i++) {
				$params[$i] = "/".$params[$i]."/";
				$values[$i] = "'".preg_replace ("/'/", "''", $values[$i])."'";
			}
			$query = trim (preg_replace ($params, $values, $query));
			
			$queries = preg_split ("/(select|insert|update|delete)/", $query, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
			
			for ($i=0; $i<sizeof ($queries); $i+=2) {
				$query = $queries[$i].$queries[$i+1];
				$query = trim ($query);
				// $connection is a sql wrapper object over mysql functions
				// this method executes the queries within the stored procedure
				// the result can be found in the connection object
				$connection->query ($query);
			}
		}
		return $found;
	}
	
	function set_query ($name = '', $query) {
		return false;
	}
	
	function get_query ($name = '') {
		$query = false;
		$found = false;
		$i=0;
		while (!$found and $i < sizeof ($this->collection)) {
			$sp = $this->collection[$i];
			if ($sp['name'] == $name) {
				$query = $sp['body'];
				$found = true;
			}
			$i++;
		}
		return $query;
	}
	
	function set_params ($name = '', $values = array ()) {
		$found = false;
		$i=0;
		while (!$found and $i < sizeof ($this->collection)) {
			$sp = $this->collection[$i];
			if ($sp['name'] == $name) {
				$this->collection[$i]['values'] = $values;
				$found = true;
			} else
				$i++;
		}
		
		return $found;
	}
	
	function get_params ($name = '') {
		return false;
	}
	
	function load ($name = '') {
		$fp = file ($this->file, "r");
		$ind = 0;
		for ($i = 0; $i < sizeof ($fp); $i++ ) {
//			preg_replace 
			if (preg_match ("/^create procedure ([a-zA-Z0-9_]+) ?\((.*)\)[[:space:]]?as([[:space:]]*\/\/.*)*/", $fp[$i], $matches)) {
				$buffer = '';
				$name = $matches[1];
				$params = $matches[2];
			} elseif (preg_match ("/^go/", $fp[$i])) {
				$content = $buffer;
				$sp['name'] = $name;
				$params = explode (",", $params);
				$params_in = array();
				$params_out = array();
				for ($j=0; $j<sizeof ($params); $j++) {
					$tmp = explode (" ", trim ($params[$j]));
					if ($tmp[1] == "in" or $tmp[1] == "")
						array_push ($params_in, $tmp[0]);
					else
						array_push ($params_out, $tmp[0]);
				}
				$sp['params_in'] = $params_in;
				$sp['params_out'] = $params_out;
				$sp['body'] = $content;
				$this->collection[$ind] = $sp;
				$ind++;
			} elseif (preg_match ("/[[:space:]]*\/\/.*/", $fp[$i])) {
			} elseif (preg_match ("/(.*)([[:space:]]*\/\/.*)*/", $fp[$i], $matches)) {
				$buffer .= $matches[1];
			}
		}
	}
	
	function save ($file, $name = '') {
		return true;
	}
	
	function save_to ($file = 'default.sp', $name = '') {
		// take care, this erases all contents of old file
		$buffer = '';
		for ($i = 0; $i < sizeof ($this->collection); $i++) {
			$sp = $this->collection[$i];
			$params_in = $sp['params_in'];
			$params_out = $sp['params_out'];
			array_walk ($params_in, 'add_postfix', ' in');
			array_walk ($params_out, 'add_postfix', ' out');
			$params = array_merge ($params_in, $params_out);
			$buffer .= "create procedure ".$sp['name']." (".implode (", ", $params).") as\n".$sp['comment']."\n".$sp['body']."\n"."go\n\n";
		}
		$fp = fopen ($file, "w");
		fwrite ($fp, $buffer);
		fclose ($fp);
	}
	
	function dump () {
		for ($i = 0; $i < sizeof ($this->collection); $i++) {
			$sp = $this->collection[$i];
			print '<table width="100%" border="1" cellspacing="0" cellpadding="0" style="font-family: Verdana; font-size: 12px;" bgcolor="#ffffff">';
			print '<tr><td width="100"><strong>Name:</strong> '.$sp['name'].'</td></tr>';
			$params_in = $sp['params_in'];
			$params_out = $sp['params_out'];
			array_walk ($params_in, 'add_postfix', ' in');
			array_walk ($params_out, 'add_postfix', ' out');
			print '<tr><td><strong>Parameters:</strong> '.implode (", ", array_merge ($params_in, $params_out)).'</td></tr>';
			print '<tr><td><strong>Body</strong></td></tr>';
			print '<tr><td><pre>'.$sp['body'].'</pre></td></tr>';
			print '</table><br>';
		}
	}
	
}

function add_postfix (&$value, $key, $postfix) {
	$value .= $postfix;
}

?>