6. Make it data-driven with PHP
- Kris Jin (Unlicensed)
- Isomorphic Support
Description
This example takes the previous sample and makes it data driven and adds a way for the user to define new DataSource types. It will also be extended to define a new DataSource and change the user interface to allow the user to switch between the two DataSources.
Prerequisites
As this sample builds on the previous one, in order to be able to run it, please esnure you have the latest build of SmartClient (at least version 8.3p). This can be downloaded from here.
Add the method that gets primary key from the DataSource
In the previous articles, in many places the primary key is used (to get the record from the database). However, to make it generic, this cannot be hard-coded. It is possible to retrieve it from the DataSource definition. To do this, add a method to the DataSource which will search in the loaded fields and return the one which has a primaryKey attribute set to true:
private function getPrimaryKey()
{
$primaryKey = '';
$ds = $this->data_source;
$fields = $ds['fields'];
foreach($fields as $field)
{
if(array_key_exists("primaryKey", $field))
{
$primaryKey = $field['name'];
return $primaryKey;
}
}
}
Change the Add / Remove / Update methods
Use the new getPrimaryKey() method to make the logic work with any DataSource.
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 Primary key
$primary_key = $this->getPrimaryKey();
// 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)
{
if($key != $primary_key)
{
$set_value .= "$key = ?,";
array_push($values, $value);
}
}
array_push($values, $old_data[$primary_key]);
// 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 '.$primary_key.'= ?';
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;
// get Primary key
$primary_key = $this->getPrimaryKey();
// get the id by primary key
$value = $new_data[$primary_key];
// make the delete query
$delete_query = "DELETE FROM $tbl_name WHERE $primary_key = ?";
// Run the insert query
R::exec($delete_query, array($value));
//DSResponse
$response = new DSResponse();
$response->setData($new_data);
$response->setStatus(0);
return $response;
}
Adding a new DataSource
To test this new code, define a new DataSource instance for employee and later allow the user to switch between them. Firslyt, the definition of the DataSource itself:
isc.RestDataSource.create({
"ID": "employees",
"fields": [
{"name": "Name", "title": "Name", "type": "text", "length": "128" },
{"name": "EmployeeId", "title": "Employee ID", "type": "integer", "primaryKey": "true", "required": "true" },
{"name": "ReportsTo", "title": "Manager", "type": "integer", "required": "true", "foreignKey": "employees.EmployeeId", "rootValue": "1", "detail": "true" },
{ name": "Job", "title": "Title", "type": "text", "length": "128" },
{ "name": "Email", "title": "Email", "type": "text", "length": "128" },
{ "name": "EmployeeType", "title": "Employee Type","type": "text", "length": "40" },
{ "name": "EmployeeStatus", "title": "Status", "type": "text", "length": "40" },
{ "name": "Salary", "title": "Salary", "type": "float" },
{ "name": "OrgUnit", "title": "Org Unit", "type":"text", "length":"128" },
{ "name": "Gender", "title": "Gender", "type":"text", "length":"7",
"valueMap": ["male", "female"]
},
{ "name": "MaritalStatus", "title": "Marital Status", "type": "text", "length": "10",
"valueMap": ["married", "single"]
}
],
"dataFormat": "json",
"operationBindings": [
{ "operationtype": "fetch", "dataProtocol": "postMessage", "dataURL": "process.php" },
{ "operationtype": "add", "dataProtocol": "postMessage", "dataURL": "process.php" },
{ "operationtype": "update", "dataProtocol": "postMessage", "dataURL": "process.php" },
{ "operationtype": "remove", "dataProtocol": "postMessage", "dataURL": "process.php" }
]
});
Create the database table for this DataSource. For this, open the Database Explorer, select the connection to the database and right click 'Tables'. In the popup menu select 'Add new table'. Using the table editor, enter the fields for the table as follows:
Column name | Data Type | Allow Nulls | Additional |
|---|---|---|---|
EmployeeId | int | No | Identity and Primary Key |
Name | varchar(128) | No |
|
ReportsTo | int | No |
|
Job | varchar(128) | Yes |
|
varchar(128) | Yes |
| |
EmployeeType | varchar(40) | Yes |
|
EmployeeStatus | varchar(40) | Yes |
|
Salary | float | Yes |
|
OrgUnit | varchar(128) | Yes |
|
Gender | varchar(7) | Yes |
|
MaritalStatus | varchar(10) | Yes |
|
Save the table with the name "employee", then open the table data and add a couple of sample rows, so the table has data.
Finally, load this newly defined DataSource into the browser. For this edit the index.php and add the following code:
<SCRIPT SRC="ds/employees.js"></SCRIPT>
UI changes
On the user interface, a change is required to allow users to switch the current DataSource. Add a form with a drop-down with the DataSources to switch and place it in front of the grids below the ListGrid. This requires putting the form in the HStack layout used for the buttons:
isc.HStack.create({
"membersMargin": 10,
"ID": "gridButtons",
"members": [
isc.DynamicForm.create({
"values": { dataSource: "Change DataSource" },
"items": [
{ "name": "dataSource", show"title": false, "editortype": "select",
"valueMap": ["supplyItem", "employees"],
"change": function (form, item, value, oldValue) {
if (!this.valueMap.contains(value)) return false;
else {
supplyItemGrid.setDataSource(value);
advancedFilter.setDataSource(value);
supplyItemGrid.filterData(advancedFilter.getCriteria());
}
}
}
]
}),
isc.IButton.create({
"top": 250,
"title": "Edit New",
"click": "supplyItemGrid.startEditingNew()"
}),
isc.IButton.create({
"top": 250,
"left": 100,
"title": "Save all",
"click": "supplyItemGrid.saveAllEdits()"
}),
]
});
Notice that when the user changes the DataSource, the selected DataSource is set, both for the supplyItemGrid and for the advancedFilter FilterBuilder. Then filterData() is called on the grid to refresh the content.
This example now shows a data-driven DataSource that allows users to add/remove/update two DataSources with two different entity DataSource, and also apply various filter criteria built with the Filter Builder.
The complete code for this sample project can be downloaded from here.