3. Adding other CRUD operations with PHP
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": "suppyItem",
"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" },
{"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",
"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)
{
// get the file content from the js file of the ds directory
$ds_contents = file_get_contents("ds/".$dataSourceId.".js",true);
// replace string to parse JSON format
$data_source = str_replace("isc.RestDataSource.create(", "", $ds_contents);
$data_source = str_replace(");", "", $data_source);
// this pattern make key: = "value" to "key" = "value" (ID: "supplyItem" => "ID": "supplyItem",)
$pattern = '/[^ ]+:/';
$result = preg_replace_callback($pattern,array($this,'preg_pattern'),$data_source);
// remove new line and tab etc.
$result = preg_replace('/\r|\n|\t/', '', $result);
// make json format
$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.
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.
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;
// get the table name from data source
$tbl_name = $ds['ID'];
$new_data = $request->data;
$insert_values = array();
// make the query
$columns = '';
$values = '';
foreach($new_data as $key => $value)
{
$columns .= $key.',';
$values .= "?,";
array_push($insert_values, $value);
}
// remove the last comma
$columns = substr($columns, 0, strlen($columns) - 1);
$values = substr($values, 0, strlen($values) - 1);
// result insert query
$insert_query = 'INSERT INTO '.$tbl_name.' ('.$columns.') VALUES ('.$values.')';
// Run the insert query
R::exec($insert_query, $insert_values);
//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;
}
}
// get the id by primary key
$values = array();
// get the table name from data source
$tbl_name = $ds['ID'];
$set_value = '';
// make the update query
foreach($old_data as $key => $value)
{
$set_value .= "$key = ?,";
array_push($values, $value);
}
array_push($values, $old_data['itemID']);
// remove the last comma
$set_value = substr($set_value, 0, strlen($set_value) - 1);
// result insert query
$update_query = 'UPDATE '.$tbl_name.' SET '.$set_value.' WHERE itemID = ?';
R::exec($update_query, $values);
// build the criteria
//DSResponse
$response = new DSResponse();
$response->setData($old_data);
$response->setStatus(0);
return $response;
}
private function remove($request)
{
$ds = $this->data_source;
// get the table name from data source
$tbl_name = $ds['ID'];
$new_data = $request->data;
// make the delete query
$itemID = $new_data['itemID'];
$delete_query = "DELETE FROM $tbl_name WHERE itemID = ?";
// Run the insert query
R::exec($delete_query, array($itemID));
//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'];
// check the advanced cretira
$query_result = $this->buildStandardCriteria($request);
$query = "select * from $tbl_name ";
$query .= $query_result['query'];
// 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 = ',';
}
}
//DSResponse
$response = new DSResponse();
$products = R::getAll($query, $query_result['value']);
// get the count
$count = count($products);
$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;
}
Note: move out the existing criteria building code into a separate method called buildStandardCriteria():
private function buildStandardCriteria($request)
{
$result = array();
$query = '';
$values = array();
if ( count($request->getDataKeys()) != 0 )
{
$query .= ' 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 " ;
$query .= $key . " like ? and " ;
array_push($values, "%".$value."%");
} else
{
"s." . $key . " = ? and " ;
array_push($values, $value);
}
}
}
// remove 'and' of the query
$query = substr($query, 0, strrpos($query, 'and'));
}
$result['query'] = $query;
$result['value'] = $values;
return $result;
}
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": "supplyItem",
"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: "supplyItem.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.