Access to the Database

From Pbxnsip Wiki

Contents 1 Purpose 2 Primary Functions 2.1 DBSearch function 2.2 DBGet Function 2.3 DBSet Function 2.4 CreateExtension 2.5 DeleteExtension 2.6 CreatePaging 2.7 DeletePaging 3 Abstract Code to access the pbxnsip database 4 Applicable Tables and respective columns 4.1 acds 4.2 adrbook 4.3 attendants 4.4 autocallback 4.5 callingcards 4.6 calls 4.7 cdr 4.8 colines 4.9 conferences 4.10 dial_plan 4.11 dial_plan_entry 4.12 domains 4.13 domain_alias 4.14 extensions 4.15 hoots 4.16 hunts 4.17 ivrnodes 4.18 lamps 4.19 messages 4.20 mohs 4.21 regidx 4.22 registrations 4.23 schedules 4.24 srvflags 4.25 trunks 4.26 users 4.27 user_alias

Purpose

Accessing to a number of PBX databases serves as an excellent purpose to manage PBX servers from a single remote host management system. The type of access protocol we currently provide is Simple Object Access Protocol (SOAP). This protocol borrows from the well-known Hyper-Text Transfer Protocol(HTTP) which gives web developers and even software engineering with much more power and extensibility with the web/stand-alone application than what the HTTP and basic object/modular collaboration provides.

In this article, the basic requirement is that you have at least the fundamental understanding of how HTTP functions and it's request/response mechanism, and follow the proper syntax as described below. SOAP can be pretty simple as HTTP and all of that HTML syntaxes, however it is much more evolved and is becoming a widly accepted standard. The Internet Engineering Task Force (IETF) and the Request For Comment (RFC) entities have incorporated the protocol along with their documentation. If you are interested in comprehensively understanding in what and how SOAP works in great details, it is recommended that you start [[1]]

The main SOAP request functions that accesses the PBX database are identified as:

• DBIndex -> DBIndexResponse
• DBSearch -> DBSearchResponse
• DBGet -> DBGetResponse
• DBSet -> DBSetResponse
• CreateExtension -> CreateExtensionResponse
• DeleteExtension -> DeleteExtensionResponse
• CreatePaging -> CreatePagingResponse (*)
• DeletePaging -> DeletePagingResponse (*)

(*) - Functionality supported on post 3.0.0.2998 version.

Applicable Table names and Column names can be found in the end of this article.

Note: After submitting with DBSet, it will return a DBSetResponse with the respective index tag e.g. <Index>56</Index>. DBSearch, DBIndex and DBGet will return meaningful data in the response. CreateExtension will return the index of the created extension if successful else it returns an index '0'. The DeleteExtension will return 'true' or 'false' as the response.

Note: There are currently no error-checking in this database access. For instance, if you submit a DBGet or DBSearch query with invalid table name and/or invalid column name(s), it will still return a DBResultset with the index tag (<Index>), in some case it may return a null value which usually means that the request is not being sent in correct text encoding such as using the ASCII format instead of UTF-8 format. This error-checking feature will be added soon.

There are also some helper function and an XML parser utilities to simplify such as encapsulating the required SOAP envelopes as well as generating DBIndex/DBSearch/DBGet/DBSet/CreateExtension/DeleteExtension text for the PBX server. This will give IT professionals much greater control of the system especially when:

• Another PBX needs to be created in a remote location on-th-fly
• Automate creation of accounts, trunks and dial plans
• Back-up all new data in real time
• Store an exact database replica into a RDBMS such as Oracle and MySQL, for better statistics and fast restoration in case of failure.

Note: In order to use the below functions/messages successfully, make sure that the IP address of the sending 
computer(device) is set under "Admin->Settings->General->SOAP Trusted IP".

Primary Functions

DBSearch function

DBSearch function is an index procedure that will search all matching entries of a given table. This function is important, because it will return a list of indexes of matching "where clauses", a parameter named args, so that you can get and set the value(s) by using the given identification provided by this function: pbx_search( table, max, skip, back, args, &result_sets ) equilavent of SQL command:

SELECT id from table WHERE args(key) = args(value) LIMIT( skip, max )

$args = array( cfn_timeout=>"10", name=>"localhost" );

pbx_search("domains", 10, 0, false, $args, $result_set );

echo "results:
"; print_r( $result_set );

will produce the request:

POST /soap.xml HTTP/1.1 
Content-Type: application/xml 
Content-Length: 351 
<?xml version="1.0" standalone="yes"?> 
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
              xmlns:sns="http://www.pbxnsip.com/soap/pbx"><env:Body>
<sns:DBSearch> 
   <Table>domains</Table>
   <Max>10</Max>
   <Column>
      <Name>cfn_timeout</Name>
      <Value>10</Value>
   </Column>
   <Column>
      <Name>name</Name>
      <Value>localhost</Value>
   </Column>
</sns:DBSearch>
</env:Body>
</env:Envelope>

and in return:

HTTP/1.1 200 Ok 
Date: Wed, 27 Jun 2007 00:00:00 GMT 
Content-Length: 207 
Server: pbxnsip/2.0.3.1715 (Win32) 
Cache-Control: no-cache 
Content-Type: text/xml 
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
              xmlns:sns="http://pbxnsip.com/soap/pbx">
<env:Body>
<sns:DBSearchResponse>
   <Index>2</Index>
</sns:DBSearchResponse>
</env:Body>
</env:Envelope>

result: Array ( [2] => 2 )

As you can see, the script found a domain that has a domain.cfn_timeout that was equal to 2 and domain.name equal to "localhost", therefore the domain name "localhost" has an id of 2 as shown in the index tag's value as well as the command print_r( $result_set) (in the print_r( ARRAY ) function, both key and value reflects the domain's id/Index).

DBGet Function

function pbx_get( table, row, columns, &result_set ) equilavent of SQL command:

SELECT columns(value) from table WHERE id = row

To make a request for certain data, for example let's request the name and id of table domain_alias id #2, the following codes would look like the this:

$columns = array( 0=>"name"); pbx_get( "domain_alias, 2, $columns, $result_set ); print_r( $result_set );

the function pbx_get() will query the PBX like the following:

POST /soap.xml HTTP/1.1 
Content-Type: application/xml 
Content-Length: 288 
<?xml version="1.0" standalone="yes"?> 
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
              xmlns:sns="http://www.pbxnsip.com/soap/pbx"><env:Body>
<sns:DBGet>
   $lt;Table>domain_alias</Table>
   <Index>2</Index>
   <Column>
      <Name>name</Name>
   </Column>
</sns:DBGet>
</env:Body>
</env:Envelope>

In response we would get the following:

HTTP/1.1 200 Ok 
Date: Tue, 26 Jun 2007 00:00:00 GMT 
Content-Length: 243 
Server: pbxnsip/2.0.3.1715 (Win32) 
Cache-Control: no-cache 
Content-Type: text/xml 
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
              xmlns:sns="http://pbxnsip.com/soap/pbx"><env:Body>
<sns:DBGetResponse>
   <Column>
      <Name>name</Name>
      <Value>localhost</Value>
   </Column>
</sns:DBGetResponse>
</env:Body>
</env:Envelope>

and print_r( $result_set ) will produce an output:

Array ( [name] => localhost )

DBSet Function

function pbx_set( table_name, row, columns_args ) To set number of values, such as changing the display_name and an email_address from table Users where id is 12: equilavent of SQL command:

UPDATE table SET columns_args(key) = columns_args(value) WHERE id = row

$columns_args = array( email_address=>"john@doe.com", display_name=>"John Doe" ); pbx_set( "users", "12", $columns_args, "localhost", "8080" );

These code will produce a request to the PBX like the following

POST /soap.xml HTTP/1.1 
Content-Type: application/xml 
Content-Length: 388 
<?xml version="1.0" standalone="yes"?> 
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
              xmlns:sns="http://www.pbxnsip.com/soap/pbx"><env:Body>
<sns:DBSet>
   <Table>extensions</Table>
   <Index>12</Index>
   <Column>
      <Name>email_address</Name>
      <Value>john@doe.com</Value>
   </Column>
   <Column>
      <Name>display_name</Name>
      <Value>John Doe</Value>
   </Column>
</sns:DBSet>
</env:Body>
</env:Envelope>

And in response:

HTTP/1.1 200 Ok 
Date: Tue, 26 Jun 2007 00:00:00 GMT 
Content-Length: 202 
Server: pbxnsip/2.0.3.1715 (Win32) 
Cache-Control: no-cache 
Content-Type: text/xml 
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
              xmlns:sns="http://pbxnsip.com/soap/pbx"><env:Body>
<sns:DBSetResponse>
   <Index>12</Index>
</sns:DBSetResponse>
</env:Body>
</env:Envelope>

CreateExtension

CreateExtension function is a special function to create the extensions in the PBX. Note that this function cannot be used to create any other type of the accounts. The <domain> and <Extension> tags are mandatory for CreateExtension function. You can also set other parameters while creating the extension. You can view the available Extension fields here

An example syntax is shown below. More information on the extensions can be found under the section for Extension

An example syntax is shown below.

POST /soap.xml HTTP/1.1
Content-Type: application/xml
Content-Length: 364
 
<?xml version="1.0" standalone="yes"?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://www.pbxnsip.com/soap/pbx">
 <env:Body>
  <sns:CreateExtension>
   <domain>localhost</domain>
   <Extension>633</Extension>
   <first_name>John</first_name>
   <display_name>Smith</display_name>
  </sns:CreateExtension>
 </env:Body>
</env:Envelope>

The response will contain the index of the created extension if successful. Otherwise, the response will contain 0 (zero).

HTTP/1.1 200 Ok
Content-Type: application/xml
Content-Length: 215

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://soap.com/pbx">
 <env:Body>
  <sns:CreateExtensionResponse>
   <Index>101</Index>
  </sns:CreateExtensionResponse>
 </env:Body>
</env:Envelope>

DeleteExtension

DeleteExtension function is a special function to delete the extensions in the PBX. Note that this function cannot be used to delete any other type of the accounts. The <domain> and <Extension> tags are mandatory for DeleteExtension function.

An example syntax is shown below.

POST /soap.xml HTTP/1.1
Content-Type: application/xml
Content-Length: 297

<?xml version="1.0" standalone="yes"?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://www.pbxnsip.com/soap/pbx">
 <env:Body>
  <sns:DeleteExtension>
   <Extension>633</Extension>
   <domain>localhost</domain>
  </sns:DeleteExtension>
 </env:Body>
</env:Envelope>

The response will contain 'true' if the extension is deleted successfully. Otherwise, the response will contain false.

HTTP/1.1 200 Ok
Content-Type: application/xml
Content-Length: 210

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://soap.com/pbx">
 <env:Body>
  <sns:DeleteExtensionResponse>
   <Ok>true</Ok>
  </sns:DeleteExtensionResponse>
 </env:Body>
</env:Envelope>

CreatePaging

CreatePaging function is a special function to create the paging groups in the PBX. Note that this function cannot be used to create any other type of the accounts. The <domain> and <Name> tags are mandatory for CreatePaging function. You can also set other parameters while creating the paging group. You can view the available paging fields here

An example syntax is shown below. More information on the paging can be found under the section for Paging

POST /soap.xml HTTP/1.1
Content-Type: application/xml
Content-Length: 372
 
<?xml version="1.0" standalone="yes"?>
 <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://www.pbxnsip.com/soap/pbx">
  <env:Body>
   <sns:CreatePaging>
    <domain>localhost</domain>
    <Name>805</Name>
    <mode>unicast</mode>
    <ext>601 602</ext>
    <perm>*</perm>
    <display>Hill-Rom</display>
   </sns:CreatePaging>
  </env:Body>
 </env:Envelope>

If the paging creation is successful, PBX will send back the index of the paging group. Otherwise, PBX will send back zero (0).

HTTP/1.1 200 Ok
Content-Type: application/xml
Content-Length: 208

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://soap.com/pbx">
 <env:Body>
  <sns:CreatePagingResponse>
   <Index>47</Index>
  </sns:CreatePagingResponse>
 </env:Body>
</env:Envelope>

DeletePaging

DeletePaging function is a special function to delete the paging group in the PBX. Note that this function cannot be used to delete any other type of the accounts. The <domain> and <Name> tags are mandatory for DeletePaging function.

An example syntax is shown below.

POST /soap.xml HTTP/1.1
Content-Type: application/xml
Content-Length: 285
 
<?xml version="1.0" standalone="yes"?>
 <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://www.pbxnsip.com/soap/pbx">
  <env:Body>
   <sns:DeletePaging>
    <Name>805</Name>
    <domain>localhost</domain>
   </sns:DeletePaging>
  </env:Body>
 </env:Envelope>

The response will contain 'true' if the delete successful. Otherwise, the response will contain 'false'.

HTTP/1.1 200 Ok
Content-Type: application/xml
Content-Length: 204

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://soap.com/pbx">
 <env:Body>
  <sns:DeletePagingResponse>
   <Ok>true</Ok>
  </sns:DeletePagingResponse>
 </env:Body>
</env:Envelope>

Abstract Code to access the pbxnsip database

Because the XML/SOAP functionality is platform-independent, the database access to the pbx can be written in any programming/web scripting language. So the following is an abstract code, which serves as a guideline to assist programmers in implementing the database access functionality.

<?php
// Functions that allow reading of the DB interface of the PBX

// Default values
$dbadr="localhost";
$dbport=8080;


// Temporary variables for the PHP xml parser
$dbparse="";
$dbname="";
$dbvalue="";
$dbdebug=0;



// Helper function
function pbx_xml_envelope( $action, $req ) 
{
	$r = '<?xml version="1.0" standalone="yes"?>';
	$r .= "\r\n";
	$r .= '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sns="http://www.pbxnsip.com/soap/pbx"><env:Body>';
	$r .= "<sns:$action>";
	$r .= $req;
	$r .= "</sns:$action></env:Body></env:Envelope>";
	return $r;
} // pbx_xml_envelope

// Another helper function, Escape necessary XML characters
function pbx_xml_encode( $t ) 
{
	$t = ereg_replace("'", "&apos;", $t);
	$t = ereg_replace('"', """, $t);
	$t = ereg_replace("&", "&", $t);
	$t = ereg_replace("<", "<", $t);
	$t = ereg_replace(">", ">", $t);
	return $t; 
} // pbx_xml_encode

// The main exection function, functions pbx_search(), pbx_get(), pbx_set() and pbx_index() uses this function to carry out the SOAP command providedd te pbx_envelope function
function pbx_soap( $req ) 
{
	global $dbadr, $dbport, $dbdebug, $debug_tbl;
	
	$dbhandle = fsockopen($dbadr, $dbport, $errno, $errstr);
	if (!$dbhandle) 
	{ 
		if($dbdebug) 
		{
			echo "<font color=red>$errstr ($errno)</font><br>\n"; 
		}
		return false;
  	}

	$request = "POST /soap.xml HTTP/1.1\r\n";
	$request .= "Content-Type: application/xml\r\n";
	$request .= "Content-Length: " . strlen($req) . "\r\n\r\n";
	$request .= $req;
	
	fputs($dbhandle, $request); 
	$data = "";
	while(!feof($dbhandle)) 
	{
		$data .= fread($dbhandle, 128);
	}
	fclose($dbhandle);
	
	$pos = strpos($data,"\r\n\r\n");
	if($pos != false) 
	{
		$body = substr($data, $pos);
		return $body;
	}
} // pbx_soap

// XML parser helper function provided by the PHP Zend engine
function pbx_start_element( $parser, $name, $attrs ) 
{
	global $dbparse, $dbdebug, $debug_tbl;

	if($dbdebug) 
	{
		echo "Start $dbparse $name<br>";
	}
	if($dbparse == "" && strstr($name, ':') == ":ENVELOPE")
	{
		$dbparse = "Envelope";
	} 
	else if($dbparse == "Envelope" && strstr($name, ':') == ":BODY") 
	{
		$dbparse = "Body";
	}
	else if($dbparse == "Body" && strstr($name, ':') == ":DBGETRESPONSE") 
	{
		$dbparse = "DBGetResponse";
	}
	else if($dbparse == "Body" && strstr($name, ':') == ":DBSEARCHRESPONSE") 
	{
		$dbparse = "DBSearchResponse";
	}
	else if($dbparse == "DBGetResponse" && $name == "COLUMN") 
	{
		$dbparse = "Column";
	}
	else if($dbparse == "DBSearchResponse" && $name == "INDEX") 
	{
		$dbparse = "Index";
	}
	else if($dbparse == "Column" && $name == "NAME") 
	{
		$dbparse = "Name";
	}
	else if($dbparse == "Column" && $name == "VALUE") 
	{ 
	 	$dbparse = "Value"; 
		$dbvalue = ""; 
	}
	else if($dbparse == "Search" && $name == "INDEX") 
	{ 
	 	$dbparse = "Index"; 
		$dbvalue = ""; 
	}
} 

// another XML parser helper function provided by the PHP Zend engine
function pbx_end_element( $parser, $name ) 
{
	  global $dbparse, $dbname, $dbvalue, $dbresult, $dbdebug, $debug_tbl;
	  
	if($dbdebug) 
	{
		echo "End $dbparse $name<br>";
	}
	if($dbparse == "Envelope") 
	{
		$dbparse = "";
	}
	else if($dbparse == "Body") 
	{
		$dbparse = "Envelope";
	}
	else if($dbparse == "DBGetResponse") 
	{
		$dbparse = "Body";
	}
	else if($dbparse == "DBSearchResponse") 
	{
		$dbparse = "Body";
	}
	else if($dbparse == "Column") 
	{
		if($dbdebug) 
		{
			echo "<B>Set $dbname=$dbvalue</B><br>";
		}
		$dbresult[$dbname] = $dbvalue;		
		$dbparse = "DBGetResponse";
		$dbname = null;
		$dbvalue = null;
	}
	else if($dbparse == "Index") 
	{
		if($dbdebug) 
		{
			echo "Set $dbvalue<br>";
		}
		$dbresult[$dbvalue] = $dbvalue;
		$dbparse = "DBSearchResponse";
		$dbvalue = null;
	}
	else if($dbparse == "Name") 
	{
		$dbparse = "Column";
	}
	else if($dbparse == "Value") 
	{
		$dbparse = "Column";
	}
	else if($dbparse == "Index") 
	{
		$dbparse = "Search";
	}	
} 

// Yet another XML parser helper function provided by the PHP Zend engine
function pbx_xml_data( $parser, $data ) 
{
	global $dbparse, $dbname, $dbvalue, $dbdebug, $debug_tbl;

	if($dbdebug)
	{
		echo "Data $dbparse $data<br>";
	}
	if($dbparse == "Name") 
	{
		$dbname = $data;
	}
	else if($dbparse == "Value") 
	{
		$dbvalue .= htmlentities($data);
	}
	else if($dbparse == "Index") 
	{
		$dbvalue .= htmlentities($data);	
	}
} 

/**
* Get a row of the table:
* This is one of the "getter" function that uses the pbx_soap() function. 
* The "$columns" parameter is a basic array where the array value is the column name.
*/
function pbx_get( $table, $row, $columns, &$result ) 
{
	global $dbresult;
	
	$dbparse = "";
	$dbname = "";
	$dbvalue = "";
	
	// Create a request:
	$req = "<Table>$table</Table>";
	$req .= "<Index>$row</Index>";
	foreach($columns as $col) $req .= "<Column><Name>$col</Name></Column>";
	$content = pbx_soap( pbx_xml_envelope("DBGet", $req) );
	
/**
* PHP will expect the following codes, however you may take advantage of the newest functionality provided by PHP. For more information and functionality, please visit http://us.php.net/soap
*/
	$dbresult = array();
	$xml_parser = xml_parser_create();
	xml_set_element_handler($xml_parser, "pbx_start_element", "pbx_end_element"); 
	xml_set_character_data_handler($xml_parser, "pbx_xml_data");
	if( !xml_parse($xml_parser, $content, true) ) 
	{ 
		die(sprintf(	"Get XML error parsing %s: %s at line %d", 
					htmlspecialchars($content),
	           		xml_error_string(xml_get_error_code($xml_parser)), 
	           		xml_get_current_line_number($xml_parser))); 
	} 
	xml_parser_free($xml_parser); 
	
	$result = $dbresult;
} // pbx_get


/**
* Set a row of the table:
* This is one of another "getter" function that uses the pbx_soap() function. 
* The "$args" parameter is an ASSOCIATIVE array where the array key is is the name of the
* column and the array value is the column value. Some programming and/or web scripting  
* language do not support this. If the case you can not use an associative array, ther solution 
* is to use TWO (2) arrays with equal length, one being the column name and the other being the 
* column value. This issue was encountered and solved when trying to migrate from PHP code to 
* Visual Basic .NET platform. Other programming/scripting language may need extra procedures to * provide this functionality.
*/
function pbx_set($table, $row, $args ) 
{
	global $dbresult, $dbparse, $dbname, $dbvalue;
	
	$dbparse = "";
	$dbname = "";
	$dbvalue = "";
	
	// Create a request:
	$req = "<Table>$table</Table>";
	$req .= "<Index>$row</Index>";
	if(count($args) > 0) 
	{
		foreach($args as $col => $val) 
		{
			$req .= "<Column><Name>$col</Name><Value>" . pbx_xml_encode($val) . "</Value></Column>";
		}
	}
	$content = pbx_soap(pbx_xml_envelope("DBSet", $req));
	return $content;
} // pbx_set

// Search all matching entries of a table:
function pbx_search($table, $max, $skip, $back, $args, &$result) 
{
	global $dbresult, $dbparse, $dbname, $dbvalue;
	
		
	$dbparse = "";
	$dbname = "";
	$dbvalue = "";
	
	// Create a request:
	$req = "<Table>$table</Table>";
	if($skip > 0) $req .= "<Skip>$skip</Skip>";
	if($back) $req .= "<Back>true</Back>";
	$req .= "<Max>$max</Max>";
	if(count($args) > 0) 
	{
		foreach($args as $col => $val) 
		{
			$req .= "<Column><Name>$col</Name><Value>" . pbx_xml_encode($val) . "</Value></Column>";
		}
	}
	$content = pbx_soap(pbx_xml_envelope("DBSearch", $req), $remote_adr, $remote_port );
	
	$dbresult = array();
	$xml_parser = xml_parser_create();
	xml_set_element_handler($xml_parser, "pbx_start_element", "pbx_end_element"); 
	xml_set_character_data_handler($xml_parser, "pbx_xml_data");
	
	if( !xml_parse($xml_parser, $content, true))
	{  			
	     if( xml_get_error_code($xml_parser) == XML_ERROR_NO_ELEMENTS )
	     {
	      	global $dbadr, $dbport;
			echo "<FONT COLOR=RED>Error: $dbadr:$dbport may be invalid, please check.</FONT><BR>";
		}
	     		
		die(	sprintf(	"Search XML error parsing %s: %s at line %d", 
					htmlspecialchars($content),
	     			xml_error_string(xml_get_error_code($xml_parser)), 
	      			xml_get_current_line_number($xml_parser))); 
	} 
	xml_parser_free($xml_parser); 
	
	$result = $dbresult;
} // pbx_search



?>

-----------------
REMARK pbx_address and pbx_port is a proper IP address to the pbx pbx_address may use DNS if the selected programming or web scripting language supports DNS resolving
REMARK It is best practice to use the pbx machines registered IP address or IP name instead of "localhost", this will prevent the pbxnsip software confusion in the long run

STRING pbx_address = "192.168.1.2"
INTEGER pbx_port = 80
XML_PARSER xml_parser


REMARK pbx_xml_envelope( STRING, STRING ) is a helper function to create an XML/SOAP envelope
function pbx_xml_envelope( PARAM_action, PARAM_request ) 
{
 	STRING soap
	soap =	'<?xml version="1.0" standalone="yes"?>' +
			NEW_LINE/CARRIAGE_RETURN +
			'<env:Envelope	xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" 					
						xmlns:sns="http://www.pbxnsip.com/soap/pbx">
			<env:Body>
			<sns:PARAM_action>' + PARAM_request + '</sns:PARAMaction>
			</env:Body>
			</env:Envelope>'
	return soap
} // pbx_xml_envelope

REMARK pbx_xml_encode( STRING ) is a helper function which escapes XML characters as required before sending the request to the pbx
function pbx_xml_encode( PARAM_string ) 
{
	PARAM_string = string_replace( "'", "&apos;", PARAM_string )
	PARAM_string = string_replace( '"', """, PARAM_string )
	PARAM_string = string_replace( "&", "&", PARAM_string )
	PARAM_string = string_replace( "<", "<", PARAM_string )
	PARAM_string = string_replace( ">", ">", PARAM_string )
	return PARAM_string
} // pbx_xml_encode

REMARK pbx_soap( STRING ) is the "execute" function that sends the request to the pbx
REMARK note that it uses the two helper functions pbx_soap() and pbx_xml_envelope()
function pbx_soap( PARAM_request ) 
{
	SOCKET_CONNECTOR pbx_socket
	STRING header_request
	STRING request
	STRING response
	
	pbx_socket = socket_connect( pbx_address, pbx_port )	

	header_request =	"POST /soap.xml HTTP/1.1" +
					NEW_LINE/CARRIAGE_RETURN +
					"Content-Type: application/xml" +
					NEW_LINE/CARRIAGE_RETURN +
					"Content-Length: " + STRING_LENGTH(request) +
					NEW_LINE/CARRIAGE_RETURN +
					NEW_LINE/CARRIAGE_RETURN

	request = header_request + PARAM_request
		
	debug_display( "<B>REQUEST</B><BR>" )
	debug_display( escape_html_characters( request ) + "<BR>" )

	pbx_socket->send( request )
	response = pbx_socket->retrieve()
	pbx_socket->close()
	
	debug_display( "<B>RESPONSE</B><BR>" )
	debug_display( escape_html_characters( response ) + "<BR>" )
	
	return response
} // pbx_soap

 
REMARK pbx_get( STRING table, INTEGER row, ARRAY_STRING columns ) gets the values (specified by PARAM_columns) at row (specified by PARAM_row) of the table (specified by PARAM_table) using the XML/SOAP DBGet function
REMARK It is preferable and simplifies a programmers job so that columns uses an array where the keys are 0 to maximum value and the values are column names which will return the value of the column names e.g.
columns[0] = first_name
columns[1] = last_name
columns[2] = display_name

function pbx_get( PARAM_table, PARAM_row, PARAM_columns ) 
{
	ARRAY_STRING result_set
	STRING request
	STRING response
	
	REMARK Create a request:
	request =	"<Table>" + PARAM_table + "</Table>
			 <Index>" + PARAM_row + "</Index>"
	for each argument in PARAM_columns as column_name
	request = request +
			"<Column>
				<Name>" + column_name + "</Name>
			 </Column>"

	response = pbx_soap( pbx_xml_envelope( "DBGet", request )

	debug_display( escape_html_characters( response )
	
	result_set = xml_parser->parse( result_set )
	return result_set
} // pbx_get



REMARK pbx_set( STRING table, INTEGER row, ARRAY_STRING args ) sets the values (specified by PARAM_args) at row (specified by PARAM_row) of the table (specified by PARAM_table) using the XML/SOAP DBSet function
REMARK It is preferable and simplifies a programmers job so that columns uses an array where the keys are column names and the values are used to set the value in the pbx database e.g.
columns[first_name] = "John"
columns[last_name] = "Doe"
columns[display_name] = "pbxnsip ROCKS!!!"

function pbx_set( PARAM_table, PARAM_row, PARAM_args ) 
{
 	REMARK note that there will be no ARRAY_STRING result_set
	STRING request
	STRING response
	
	REMARK Create a request:
	request =	"<Table>" + PARAM_table + "</Table>
			 <Index>" + PARAM_row + "</Index>"
	for each value in PARAM_args as column_name into column_value
	request = request + 
			"<Column>
				<Name>" + column_name + "</Name>
				<Value>" + pbx_xml_encode(column_value) + "</Value>
			 </Column>"

	respone = pbx_soap( pbx_xml_envelope( "DBSet", request ) )

	debug_display( escape_html_characters( response )
	
	REMARK this will only return the index value
	return response
} // pbx_set

REMARK pbx_search( STRING table, INTEGER max, INTEGER skip, BOOOLEAN back, ARRAY_STRING args ) searches for the indexes of the row where args (specified by PARAM_args) are found in the table (specified by PARAM_table). 
REMARK To limit the search to a given number, set the max (specified by PARAM_max) to the desired limits.
REMARK To skip over a certain index number, such as start from index no. 10, set the skip (specified by PARAM_skip) to the desired index number.
REMARK To search for indexes in a reverse fashion, set the back (specified by PARAM_back) equal to true, otherwise the default is false and it does *NOT* need to be included. 
REMARK It is preferable and simplifies a programmers job so that columns uses an array where the keys are 0 to maximum value and the values are column names which will return the indexes if found e.g.
columns[0] = first_name
columns[1] = last_name
columns[2] = display_name

function pbx_search( PARAM_table, PARAM_max, PARAM_skip, PARAM_back, PARAM_args )
{
	ARRAY_STRING result_set
	STRING request
	STRING response
	
	REMARK Create a request:
	request =	"<Table>" + PARAM_table + "</Table>"

	if( PARAM_skip > 0 ) 
		request = request + "<Skip>" + PARAM_skip + "</Skip>"
	
	if( PARAM_back = true )
		request = request + "<Back>true</Back>"
	
	request = request + "<Max>" + PARAM_max + "</Max>"
	
	if( has_elements( PARAM_args ) )
		for each argument in PARAM_args as column_name into column_value
			request = request + 
				"<Column>
					<Name>" + column_name + "</Name>
					<Value>" + pbx_xml_encode( column_value ) + "</Value>
				 </Column>"
	
	response = pbx_soap( pbx_xml_envelope("DBSearch", request )
	
	result_set = xml_parser->parse( response )
	return result_set
} // pbx_search

Applicable Tables and respective columns

acds

admin	agents	distribution	flag	gap_time	im	key0	key1	key2	key3
key4	key5	key6	key7	key8	key9	last_state	melody	night	recover_time
redirect	soap_agent	soap_welcome	speed_limit	timeout	wavfile0	wavfile1	wavfile2	wavfile3	wavfile4
wavfile5	wavfile6	wavfile7	wavfile8	wavfile9

adrbook

display_number

domain

first

name

number

speed

type

user

attendants

admin	code_0	code_1	code_2	code_3	code_4	code_5	code_6	code_7	code_8
code_9	completion	excluded	flag	hangup	input_0	input_1	input_2	input_3	input_4
input_5	input_6	input_7	input_8	input_9	min_digits	name_trigger	night	operator	prompt_0
prompt_1	prompt_2	prompt_3	prompt_4	prompt_5	prompt_6	prompt_7	prompt_8	prompt_9	record_code
record_ext	second_lang	timeout	verify	wavfile

autocallback

active

fuser

number

ts

tuser

callingcards

block_cid

num_id

calls

cdr_callid	cdr_dest	clip	codec	connected	diversion	domain	expires	extension	from
id	ivr_ext	ivr_files	ivr_state	ivr_user	ivr_var1	ivr_var2	ivr_var3	ivr_var4	ivr_var5
ivr_var6	lang	listener	loop	oext	picture	rec	redirect_charge	redirect_number	reference
secure	start	stat0	stat1	state	tap	tapi	to	trunk	type

cdr

ca	cn	d	f	ft	fu	l	sf	sr	t
tc	te	ts	tt	tu	y

colines

call

trunk

type

conferences

length

name

pin

type

dial_plan

domain

name

ua

dial_plan_entry

id

pattern

preference

replacement

trunk

domains

area_code	cfn_timeout	code_agent_login	code_agent_logout	code_banod	code_banoe	code_barge	code_bcidd
code_bcide	code_black	code_cfad	code_cfae	code_cfbd	code_cfbe	code_cfnd	code_cfne
code_cfr	code_clrvm	code_dndd	code_dnde	code_govm	code_intercom	code_listen	code_page
code_park	code_pickup	code_rec	code_redial	code_retrieve	code_return	code_teach	code_trace
code_transfer	code_vmd	code_vme	code_white	country_code	default_dialplan	display	dp
email_from	email_pass	email_smtp	email_user	from_style	lang_audio	lang_tones	lang_web
mailbox_escape	max_accounts	max_calls	max_extensions	max_mb_duration	mb_enter_pin	mb_pinsize	mb_prefix
mb_size	mb_timeout	moh	name	pickup_policy	record_annoucement	soap_extcall	to_style
tz	voicemail

domain_alias

domain

name

extensions

admin	agent_login	anonymous	auth_adr	avail	barge	block_cid	buddies	call_redial	call_return
cell	cell_acd	cell_hunt	cell_mwi	cell_time	cfa	cfb	cfn	cfn_timeout	cfr
dial_plan	display_name	disturb	dnd	email_address	email_missed	email_status	email_vmail	fax	first_name
has_name	lang_audio	lang_web	lines	listen	log_register	mac	mailbox_access	mailbox_escape	mb_enable
mb_pin	mb_size	mb_timeout	mwi	name_use	password	pass_used	pbody	picture	presence
ptag	ptype	reading	rec_acd	rec_extension	rec_external	rec_hunt	rec_internal	teach	tz
ua_type	vmail_option	vm_indicator	wavname	web_pass

hoots

display

ext

mode

perm

hunts

display	flag	melody	night	st1_dur	st1_ext	st2_dur	st2_ext	st3_dur	st3_ext
st4_ext	st9_ext	to_header

ivrnodes

admin

match

match_from

match_to

soap_uri

wavfile

lamps

from

number

state

to

user

messages

comment

duration

filename

from

new

start

urgent

user

mohs

file

name

string

type

regidx

event

reg

user

registrations

callid	contact	date	domain	dv	event	from	fuser	instance	parameter
state	to	tuser	user_agent	www

schedules

emails

end

id

moderator

name

pin

start

srvflags

allow_ext	display	fri	holiday	mode	mon	sat	state	sun	thu
tue	wed

trunks

codecs	dial_extension	domain	failover	frompat	global	minor	name	outbound_proxy	prefix
privacy	redirect	reg_account	reg_display	reg_keep	reg_pass	reg_registrar	reg_stun	reg_user	remote_party
request_timeout	require	rfcrtp	ring180	send_email	status	trusted	type	use_uuid	uuid

users

alias	dialog_state	dialog_subscribe	dialog_version	dial_plan	domain	id	param1	param2	param3
type

user_alias

domain

name

user