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:

DateSource.php

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:

employees.js

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
Email	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:

index.php

<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:

ui.js

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.