3. Adding other CRUD operations with PHP1
Description
In this article the code will be refactored from the previous sample (Adding simple criteria, sorting and data paging), the correct filtering functions will be implemented for the text columns and additional functionality will be added to have a fully working DataSource implementation.
Moving the DataSource to an external file
In the previous sample, filtering by a text value would not select rows which contained the filter term, only those that were specifically equal to it. To correctly filter by a text value, it is necessary to identify which columns are of type text and which are other types. To achieve this, the declaration of the DataSource needs to be moved to an external file, which will be loaded on both the client and server side.
Firstly, create a new folder, called 'ds' in the project directory, below App3 (as with sample 1 and 2 a new project has been created for this sample). In this folder, create a javascript file which defines the DataSource itself. Give this file the same name as the ID of the DataSource, to have an easy method of identifying which DataSource is associated with which table.
Now, modify the view file for the application to load this additional DataSource. You should edit the 'index.html' file and change it's content to:
<SCRIPT SRC="ds/supplyItem.js"></SCRIPT> <SCRIPT SRC="Scripts/ui.js"></SCRIPT>
The additional script tag that loads the DataSource definition which was moved to the newly created supplyItem.js file.
Making use of the DataSource definition file
At this stage, the definition of the DataSource (now in a separate file). The DataSource definition needs to be updated to properly manage the requests for add, update and remove operations.After these changes, the DataSource definition should looks like this:
isc.RestDataSource.create({
ID: "supplyItem",
fields: [
{ name: "itemID", type: "sequence", hidden: "true", primaryKey: "true" },
{ name: "itemName", type: "text", title: "Item", length: "128", required: "true" },
{ name: "SKU", type: "text", title: "SKU", length: "10", required: "true" },
{ name: "description", type: "text", title: "Description", length: "2000" },
{ name: "category", type: "text", title: "Category", length: "128", required: "true", foreignKey: "supplyCategory.categoryName" },
{ name: "units", type: "enum", title: "Units", length: "5",
valueMap: ["Roll", "Ea", "Pkt", "Set", "Tube", "Pad", "Ream", "Tin", "Bag", "Ctn", "Box"]
},
{ name: "unitCost", type: "float", title: "Unit Cost", required: "true",
validators: [
{ type: "floatRange", min: "0", errorMessage: "Please enter a valid (positive) cost" },
{ type: "floatPrecision", precision: 2, errorMessage: "The maximum allowed precision is 2" }
]
},
{ name: "inStock", type: "boolean", title: "In Stock" },
{ name: "nextShipment", type: "date", title: "Next Shipment" }
],
dataFormat: "json",
criteriaPolicy:"dropOnChange",
operationBindings: [
{ operationType: "fetch", dataProtocol: "postMessage", dataURL: "fetch.php" },
{ operationType: "add", dataProtocol: "postMessage", dataURL: "add.php" },
{ operationType: "update", dataProtocol: "postMessage", dataURL: "update.php" },
{ operationType: "remove", dataProtocol: "postMessage", dataURL: "remove.php" }
]
});
Notice that this definition is almost a JSON file, with a small addendum at the beginning and the end. If the 'isc.RestDataSource.create(' part from the beginning and ');' part from the end were removed, this would give a valid JSON object which will be deserialized as a JSON object.
To do this an object is required to deserialize this into. At the moment, only the fields are of interest, so the object would look like this:
<?php
class DataSource
{
private $data_source;
function __construct($dataSourceID)
{
$this->data_source = $this->getDataSource($dataSourceID);
}
/// <summary>
/// Load a DataSource specified by it's ID
/// </summary>
/// <param name="datasourceId">the ID of the DataSource to be loaded</param>
/// <returns>the DataSource object corresponding to the DataSource with the specified ID or null if not found</returns>
public function getDataSource($dataSourceId)
{
$ds_contents = file_get_contents("ds/".$dataSourceId.".js",true);
$data_source = str_replace("isc.RestDataSource.create(", "", $ds_contents);
$data_source = str_replace(");", "", $data_source);
$pattern = '/[^ ]+:/';
$result = preg_replace_callback($pattern,array($this,'preg_pattern'),$data_source);
$result = preg_replace('/\r|\n|\t/', '', $result);
$json = $result;
return json_decode($json, true);
}
// pattern regular
private function preg_pattern($match)
{
return '"'.substr($match[0],0,strlen($match[0])-1).'":';
}
/// <summary>
/// Get a field's data by it's name
/// </summary>
/// <param name="name">The name of the field</param>
/// <returns>A Dictionary with the attributes of the field, or null if field was not found</returns>
private function getField($name)
{
$ds = $this->data_source;
$fields = $ds['fields'];
foreach($fields as $field)
{
if ($field['name'] == $name)
return $field;
}
return null;
}
The fields themselves are loaded into a collection of dictionaries. Additionally,a method has been added to get a field by it's name.
Refactoring DSRequest
All the request and response processing will be moved to a different specialized class, called RPCManager. The processing flow will also change accordingly. Any action of the user with the DataSource will only call the RPCManager and will delegate all responsibility to it. The RPCManager will parse the payload and setup the DSRequest request and will call for the request's execute() method which will return the DSResponse object. The RPCManager will then convert this DSResponse into a suitable response and return it to the front-end. DSRequest in turn will delegate the calls to the DataSource itself (explained later in this article).
So, firstly, refactor the existing classes to be more generic and fit this new flow. As it is likely the data member of the DSRequest object will be required in various formats, it should be made generic.
<?php
require 'DataSource.php';
class DSRequest
{
var $dataSource;
var $operationType;
var $startRow;
var $endRow;
var $textMatchStyle;
var $componentId;
var $data;
var $sortBy;
var $oldValues;
function __construct($params)
{
// json to array
$request = json_decode($params, TRUE);
// extract the $request array
extract($request);
// set componentId
if(isset($componentId))
$this->componentId = $componentId;
// set dataSource
if(isset($dataSource))
$this->dataSource = $dataSource;
// set startRow
if(isset($startRow))
$this->startRow = $startRow;
// set sortBy
if(isset($sortBy))
$this->sortBy = $sortBy;
// set endRow
if(isset($endRow))
$this->endRow = $endRow;
// set oldValues
if(isset($oldValues))
$this->oldValues = $oldValues;
// set textMatchStyle
if(isset($textMatchStyle))
$this->textMatchStyle = $textMatchStyle;
// set data
if(isset($data))
$this->data = $data;
// set operationType
if(isset($operationType))
$this->operationType = $operationType;
}
public function setData($data)
{
$this->data = $data;
}
public function getDataKeys()
{
$data = $this->data;
$keys = array_keys($data);
return $keys;
}
public function execute()
{
$ds = new DataSource($this->dataSource);
if(empty($ds))
{
return null;
}
return $ds->execute($this);
}
}
A Couple of noteworthy comments regarding the newly refactored code:
- A reference to the RPCManager executing this request will be stored in DSRequest .This has to be done because, while the request is being.executed, access will be required to various items such as the DataSource object, etc - These items will all be provided by the RPCManager class.
- The execute() method itself only loads the DataSource object then calls the DataSource's execute method for processing the request.
Refactoring DSResponse
In the DSResponse object, notice that it contains an object wrapped inside another just to mimic the structure of the JSON required by the front-end, with all the properties forwarded to the internal object.
<?php
require 'bean.php';
class DSResponse extends Bean
{
// variables
/* Read/Write property*/
var $data;
/* Read/Write property*/
var $startRow;
/* Read/Write property*/
var $endRow;
/* Read/Write property*/
var $totalRows;
/* Read/Write property*/
var $status;
public function setData($value) {
$this->data = empty($value) ? null : (Array)$value;
return $this;
}
public function getData( )
{
return $this->data;
}
public function setStartRow($value) {
$this->startRow = (int)$value;
return $this;
}
public function getStartRow( )
{
return $this->startRow;
}
public function setEndRow($value) {
$this->endRow = (int)$value;
return $this;
}
public function getEndRow( )
{
return $this->endRow;
}
public function setTotalRows($value) {
$this->totalRows = (int)$value;
return $this;
}
public function getTotalRows( )
{
return $this->totalRows;
}
public function setStatus($value) {
$this->status = (int)$value;
return $this;
}
public function getStatus( )
{
return $this->status;
}
}
The RPCManager
Now implement the RPCManager class. This class needs to include the DSRequest and DSResponse classes to process the request and response.
<?php
require 'DSResponse.php';
require 'DSRequest.php';
class RPCManager
{
var $_request;
var $_response;
function __construct($request)
{
$this->_request = $request;
}
When the user calls for any method relative to the CRUD such as fetch, add, update etc, the method called processRequest() will be executed with the request parameters.
/// <summary>
/// Process the request for which this RPCManager was created for
/// </summary>
/// <returns></returns>
public function processRequest()
{
// retrieve the requests with data
$request = new DSRequest($this->_request);
// set the response variable
$response = $request->execute();
// safeguard, if was null, create an empty response with failed status
if (empty($response))
{
$response = new DSResponse();
$response->setStatus(-1);
}
$this->_response = $response;
return $this->buildResult();
}
This method makes use of various helper methods. One for parsing a DSRequest object from the payload and one for building the response to be sent to the front-end. As discussed earlier in the article, this is being created as an anonymous object to be serialized to JSON (an object which mimics the layout of the required JSON). The object is created with the appropriate values, retrieved from the DSResponse object which the DSRequest method returned:
/// <summary>
/// Transforms a object object into a JsonNetResult. Will setup the serializer with the
/// appropriate converters, attributes,etc.
/// </summary>
/// <param name="dsresponse">the object object to be transformed to JsonNetResult</param>
/// <returns>the created JsonNetResult object</returns>
private function buildResult()
{
$data = array();
$data['response'] = (Array)$this->_response;
echo json_encode($data);
}
For more details and the full source code of the RPCManager please review the attached archive at the end of this article.
Processing the Request
With all the functionality delegated to the RPCManager, the code becomes extremely simple:
require_once 'RPCManager.php'; $rpc_manager = new RPCManager($HTTP_RAW_POST_DATA); return $rpc_manager->processRequest();
In order to get the parameters from the user, we should parse $HTTP_RAW_POST_DATA and then we can parse it to the JSON format.
The code that has been removed is no longer required here, as the functionality for processing the requests has been moved inside the DataSource class. As discussed earlier, the functionality from the controller is now resident in the DataSource class itself:
public function execute($request)
{
switch($request->operationType)
{
case 'fetch':
return $this->fetch($request);
case 'add':
return $this->add($request);
case 'update':
return $this->update($request);
case 'remove':
return $this->remove($request);
}
}
private function add($request)
{
$ds = $this->data_source;
$new_data = $request->data;
//DSResponse
$response = new DSResponse();
$response->setData($new_data);
$response->setStatus(0);
return $response;
}
private function update($request)
{
$old_data = $request->oldValues;
$update_data = $request->data;
$ds = $this->data_source;
// update all fields which have changed. they are defined in the data property
if ( count($request->getDataKeys()) != 0 )
{
foreach($request->data as $key => $value)
{
$old_data[$key] = $value;
}
}
// build the criteria
//DSResponse
$response = new DSResponse();
$response->setData($old_data);
$response->setStatus(0);
return $response;
}
private function remove($request)
{
$ds = $this->data_source;
$new_data = $request->data;
//DSResponse
$response = new DSResponse();
$response->setData($new_data);
$response->setStatus(0);
return $response;
}
private function fetch($request)
{
// get the DataSource
$ds = $this->data_source;
$tbl_name = $ds['ID'];
$query = "select * from $tbl_name s ";
$query_count = "select count(*) from $tbl_name s";
// build up the query
if ( count($request->getDataKeys()) != 0 )
{
$query .= ' where ';
$query_count .= ' where ';
foreach($request->data as $key => $value)
{
// get the field
$field = $this->getField($key);
if(!empty($field))
{
$type = $field['type'];
if( $type == "text" ||
$type == "link" ||
$type == "enum" ||
$type == "image" ||
$type == "ntext" )
{
$query .= "s." . $key . " like " . " '%" . $value . "%' and " ;
} else
{
"s." . $key . "=" . " '" . $value . "' and " ;
}
}
$query_count .= "s." . $key . " like " . "'%" . $value . "%' and " ;
}
// remove 'and' of the query
$query = substr($query, 0, strlen($query)- 4);
$query_count = substr($query_count, 0, strlen($query_count)- 4);
}
// sort by
if( !empty($request->sortBy) )
{
// set the orderBy
$query .= " order by ";
// we start to build a coma separated list of items. First item won't have coma
// but every possible next will do
$seperator = "";
foreach($request->sortBy as $index => $sort)
{
// if column name is with -, then ordering is descending, otherwise ascending
if ( strpos($index, '-') === 0 )
{
$query .= $seperator . $sort . " ASC";
} else
{
$query .= $seperator . substr($sort, 1) . " DESC";
}
$separator = ',';
}
}
// convert the payload to our DRequest object
// get the count
$products_count = R::getAll($query_count);
$count = $products_count[0]['count(*)'];
//DSResponse
$response = new DSResponse();
$products = R::getAll($query);
$response->setData($products);
$response->setStartRow($request->startRow);
$response->setEndRow($request->endRow);
$response->setTotalRows($count);
$response->setStatus(0);
// sanity check, if no rows, return 0
if ($response->getEndRow() < 0 )
{
$response->setEndRow(0);
}
return $response;
}
In this article, the RedBean library was used to get the data from the database on the line 185 and the line 191.
Adding additional CRUD operations
At this point, this is the same functional sample as the previous example (but using criteria this time, and making use of the DataSource definition on the server side). To build on this, add the missing operations to make this a fully functioning DataSource.
Client side changes
Firstly, make the grid editable. Add the canEdit:true property to the ListGrid. Then, to allow for removing rows, add the canRemoveRecords:true property. The ListGrid definition will now look like this:
isc.ListGrid.create({
ID: "supplyItemGrid",
width: 700, height: 224, alternateRecordStyles: true,
dataSource: supplyItem,
showFilterEditor: true,
autoFetchData:true,
dataPageSize:20,
canEdit:true,
canRemoveRecords:true
});
Next, There needs to be a method for adding new records. Add a button which, on click, will trigger a new record being available for editing in the grid. The source for this is:
isc.IButton.create({
top: 250,
title: "Edit New",
click: "supplyItemGrid.startEditingNew()"
});
At this point, all four operations are implemented (add, update, remove, fetch). However please note these are just plain samples,and do not contain anything other than basic type error validation.
The complete code for this sample project can be downloaded from here.