<?php
/*
 * SOAP SQL Demo
 * Created: Apr 18, 2007
 * License: LGPL (open source)
 * BungeeLabs, Inc.
 * 
 * This PHP script demonstrates the simplicity of creating rich web services
 * from simple SQL statements using SOAP SQL.
 * 
 * Dependencies: 
 * NuSOAP at http://dietrich.ganx4.com/nusoap  
 * PEAR MDB2 at http://pear.php.net/package/MDB2
 */
 
require_once("soapsql.php");
 
$soapsql = new soapsql();
$soapsql->setDebug(true);
 
$soapsql->register(
	'SQL', 		//Web service name 
	'http://testsoapsql/', //Name space 
	'mysqli', 	//Database type
	'root', 	//DB username
	'password',	//DB password
	'localhost',//DB host
	'test'); 	//Database name
if ($soapsql->isError()) 
	die ($soapsql->getError());

/**
 * Demonstrate SQL web service functions that query the database
 */

// We take as input a name and return an array of name, age pairs (the table row). 
$inParams = array('name' => 'xsd:string');
$outParams = array('name' => 'xsd:string', 'age' => 'xsd:int');

// Notice the $name value does NOT need '' around it, it will be *automatically* quoted and escaped.
$sqlQuery = 'select name, age from mytable where name = $name';

// Create a select-based web service function named Query using addQueryWSDLOperation().
// Three support WSDL structures will also be automatically created, called QueryRequestType, QueryResponseType, and QueryResultType,
// which contain the $inParams, an array of $outParams, and the $outParams stucture, respectively.

$soapsql->addQueryWSDLOperation('Query', $inParams, $outParams, $sqlQuery);
if ($soapsql->isError()) 
	die ($soapsql->getError());


// Create a select-based web service function with a custom callback using 
// addQueryWSDLOperation() with $autoGenCallback set to false. This allows for greater flexibility.

$soapsql->addQueryWSDLOperation('MyQuery', $inParams, $outParams, $sqlQuery, false);
if ($soapsql->isError()) 
	die ($soapsql->getError());

// Demonstrate the out parameter with a different name than the column name
$inParams = array('name' => 'xsd:string');
$outParams = array('name' => 'xsd:string', 'age' => 'xsd:int');

// Custom sql select callback. 
// Notice the 'mytable.name as the_name' in the SQL query below is needed if the out parameter is named differently than
// the column name. Also, with this routine, we can return the entire list if no name is specified.
function MyQuery($name) {
	global $soapsql;
	$name = $soapsql->escapeSQLParam($name, 'xsd:string');
	$sql = 'select mytable.name as name, mytable.age as age from mytable';
	if ($name != 'NULL')
		$sql .= ' where name = '.$name;
	return $soapsql->getSQLQueryResult($sql);
}


/**
 * Demonstrate SQL web service functions that modify the database
 */

// Create an insert-based web service function using createWSDLFunction().
// The output to this operation is (see the documentation for more details):
// array('success'=>'xsd:boolean', 'error'=>'xsd:string', 'affectedRows'=>'xsd:int', 'generatedId'=>'xsd:string');

$inParams = array('name' => 'xsd:string', 'age' => 'xsd:int');
$sqlModify = 'insert into mytable (name, age) values ($name, $age)';

$soapsql->addModifyWSDLOperation('Insert', $inParams, $sqlModify); 
if ($soapsql->isError()) 
	die ($soapsql->getError());
 

/**
 * Show how to use the security token with a custom callback method.
 * Usually the security token should be set before the create WSDL functions are called
 * to enforce security on all functions. 
 */
$soapsql->setSecurityToken('abcdefghijklmnopqrstuvwxyz');


$inParams = array('name' => 'xsd:string', 'age' => 'xsd:int');
$sqlModify = 'insert into mytable (name, age) values ($name, $age)';

// Create a insert-based web service function with a custom callback using 
// addModifyWSDLOperation() with $autoGenCallback set to false for greater flexibility.

$soapsql->addModifyWSDLOperation('SecureInsert', $inParams, $sqlModify, false);
if ($soapsql->isError()) 
	die ($soapsql->getError());


// Custom sql insert callback with security token
function SecureInsert($securityParams, $name, $age) {
	global $soapsql;
	if ($soapsql->verifySecurity($securityParams) == false) {
		return $soapsql->getError();
	}
	$name = $soapsql->escapeSQLParam($name, 'xsd:string');
	$age = $soapsql->escapeSQLParam($age, 'xsd:int');
	$sql = "insert into mytable (name, age) values ($name, $age)";
	return $soapsql->getSQLModifyResult($sql);
}

//Do the actual servicing of the request
$soapsql->serviceWSDL();
if ($soapsql->isDebug()) {
	file_put_contents("test_soapsql.log", $soapsql->getDebug()."\r\n".$soapsql->getSOAPDebug());
}
?>