Reorderable ListGrid with persistent order

Owned by Joseph Trebbien (Unlicensed)
Last updated: Feb 24, 2012Version comment
14 min read

We have developed this SmartClient project to demonstrate a reorderable ListGrid with persistent order. The user can drag & drop items and their preferred order is saved to a relational database, allowing their preferred order to be restored the next time the application is loaded.

Initial project setup

Download the latest SmartClient SDK and extract it to a temporary directory. Inside, you will see a directory called smartclientRuntime and smartclientSDK. smartclientRuntime is a basic starter J2EE project which we will build off of to develop this sample. Note that in a Mavenized Java EE project, smartclientRuntime is copied to src/main/webapp and then the files at src/main/webapp/classes are moved to src/main/resources.

  1. Open a command prompt and change directory into a general projects directory.
  2. Run the following command to create a basic Maven J2EE project: 
    mvn archetype:create -DarchetypeArtifactId=maven-archetype-webapp -DgroupId=com.smartclient.sample -DartifactId=persistent-reorderable-ListGrid
    This creates a directory called persistent-reorderable-ListGrid in your current working directory. cd into this directory.
  3. Copy the files in smartclientRuntime to src/main/webapp and then rename the directory src/main/webapp/WEB-INF/classes to src/main/resources.
  4. Save http://smartgwt.googlecode.com/svn-history/r524/trunk/samples/showcase/src/com/smartgwt/sample/showcase/public/ds/employees.ds.xml to src/main/webapp/shared/ds/employees.ds.xml and http://smartgwt.googlecode.com/svn-history/r524/trunk/samples/showcase/src/com/smartgwt/sample/showcase/public/ds/test_data/employees.data.xml to src/main/webapp/shared/ds/test_data/employees.data.xml.
  5. Copy smartclientSDK/WEB-INF/db/hsqldb to src/main/webapp/WEB-INF/db/hsqldb.

The basic starter project smartclientRuntime does not include the isomorphic_sql JARs which we will need to be able to use an SQL database. Copy all JARs from smartclientSDK/WEB-INF/lib to src/main/webapp/WEB-INF/lib.

We will now configure SmartClient's embedded HyperSQL DB.

  1. Open src/main/resources/server.properties in a text editor. Before the "Other Settings" section (which is commented out by default), insert the following lines: 
    # -------------- PICK DATABASE TO USE --------------
sql.defaultDatabase: HSQLDB

# If you want to use Mysql instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
#sql.defaultDatabase: Mysql

# If you want to use Oracle instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
#sql.defaultDatabase: Oracle

# If you want to use Postgres instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
#sql.defaultDatabase: PostgreSQL

# If you want to use DB2 instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
#sql.defaultDatabase: DB2

# -------------- SETTINGS FOR HSQLDB ---------------

sql.HSQLDB.database.type: hsqldb
sql.HSQLDB.interface.type: driverManager

sql.HSQLDB.driver: org.hsqldb.jdbcDriver
sql.HSQLDB.driver.url: jdbc:hsqldb:file:$webRoot/WEB-INF/db/hsqldb/isomorphic
  2. Save.

Finally, we will start the HSQLShutdown servlet to make sure that redeploying the application does not corrupt the HyperSQL database. Open src/main/webapp/WEB-INF/web.xml in a text editor and add the following within the <web-app> element:

    <!-- this servlet cleanly shuts down the HSQL database whenever the container is reloaded.  If
         you want container reloading to work reliably with HSQL, this servlet must be enabled -->
    <servlet>
        <servlet-name>HSQLShutdown</servlet-name>
        <servlet-class>com.isomorphic.sql.HSQLShutdown</servlet-class>
        <load-on-startup>3</load-on-startup>
    </servlet>

Setting up the database

Starting with the `isomorphic` HyperSQL 1.8.0.10 database that comes with the SmartClient SDK, we need to add a column to store the user's preferred ordering of each employee. We can use HyperSQL's Swing Manager tool to alter the sample database's employeeTable table.

  1. Download hsqldb_1_8_0_10.zip and unzip. Within the extracted contents, there is a Windows batch file at hsqldb\demo\runManagerSwing.bat. Windows users can double-click on this batch file to run the Swing Manager. Linux users will need to open a terminal, cd into hsqldb/data and run: 
    java -classpath ./../lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing
  2. Once the Swing Manager tool opens, you will see a dialog asking for connection information. For "Type:", select "HSQL Database Engine Standalone". In "URL:", replace «database/path?» with the path to src/main/webapp/WEB-INF/db/hsqldb/isomorphic. For example, if your persistent-reorderable-ListGrid project is at C:\Users\Me\projects\persistent-reorderable-ListGrid, then the full connection URL is jdbc:hsqldb:file:C:\Users\Me\projects\persistent-reorderable-ListGrid\src\main\webapp\WEB-INF\db\hsqldb\isomorphic.
  3. Click "OK" to connect.
  4. In the text area on top, paste in the following SQL: 
    CREATE SEQUENCE employeeTable_userOrder_seq;
ALTER TABLE employeeTable ADD COLUMN userOrder INTEGER DEFAULT 0 NOT NULL;
UPDATE employeeTable SET userOrder = NEXT VALUE FOR employeeTable_userOrder_seq;
  5. Click the "Execute SQL" button. This will add a userOrder column to employeeTable, which the sample will use to store the preferred ordering of employees.
  6. Quit the program via File → Exit.

We also need to update the `employees` data source to add a userOrder field.

  1. Open src/main/webapp/shared/ds/employees.ds.xml in a text editor.
  2. Before the definition of the "Name" field, add the following: 
            <field name="userOrder"       title="userOrder"       type="integer"  canEdit="false"    hidden="true"/>
  3. Also change the testFileName to shared/ds/test_data/employees.data.xml.

The contents of employees.ds.xml should now be:

employees.ds.xml
<DataSource
    ID="employees"
    serverType="sql"
    tableName="employeeTable"
    recordName="employee"
    testFileName="shared/ds/test_data/employees.data.xml"

>
    <fields>
        <field name="userOrder"       title="userOrder"       type="integer"  canEdit="false"    hidden="true"/>
        <field name="Name"            title="Name"            type="text"     length="128"/>
        <field name="EmployeeId"      title="Employee ID"     type="integer"  primaryKey="true"  required="true"/>
        <field name="ReportsTo"       title="Manager"         type="integer"  required="true"
               foreignKey="employees.EmployeeId"  rootValue="1"/>
        <field name="Job"             title="Title"           type="text"     length="128"/>
        <field name="Email"           title="Email"           type="text"     length="128"/>
        <field name="EmployeeType"    title="Employee Type"   type="text"     length="40"/>
        <field name="EmployeeStatus"  title="Status"          type="text"     length="40"/>
        <field name="Salary"          title="Salary"          type="float"/>
        <field name="OrgUnit"         title="Org Unit"        type="text"     length="128"/>
        <field name="Gender"          title="Gender"          type="text"     length="7">
            <valueMap>
                <value>male</value>
                <value>female</value>
            </valueMap>
        </field>
        <field name="MaritalStatus"   title="Marital Status"  type="text"     length="10">
            <valueMap>
                <value>married</value>
                <value>single</value>
            </valueMap>
        </field>
    </fields>
</DataSource>

Programming the application

We will start with the following HTML template, which should be saved to src/main/webapp/index.html:

index.html
<!DOCTYPE html>
<html>
  <head>
    <title>Persistent Reorderable ListGrid Sample</title>
    <script>var isomorphicDir = "isomorphic/";</script>
    <script src="isomorphic/system/modules/ISC_Core.js"></script>
    <script src="isomorphic/system/modules/ISC_Foundation.js"></script>
    <script src="isomorphic/system/modules/ISC_Containers.js"></script>
    <script src="isomorphic/system/modules/ISC_Grids.js"></script>
    <script src="isomorphic/system/modules/ISC_Forms.js"></script>
    <script src="isomorphic/system/modules/ISC_DataBinding.js"></script>
  </head>
  <body>
    <script src="isomorphic/skins/TreeFrog/load_skin.js"></script>
    <script src="isomorphic/DataSourceLoader?dataSource=employees"></script>
    <script src="app.js"></script>
  </body>
</html>

What it does is load the SmartClient foundation modules as well as the `employees` DataSource.

When the browser requests isomorphic/DataSourceLoader?dataSource=employees, the server will invoke the DataSourceLoader servlet, which parses the employees.ds.xml and returns a block of JavaScript code to construct a corresponding isc.DataSource object.

Also create a file at src/main/webapp/app.js containing the following:

app.js
var listGrid = isc.ListGrid.create({
    width:900,
    height:500,
    dataSource:window.employees,
    autoFetchData:true,
    canEdit:true,
    canReorderRecords:true,
    autoSaveEdits:true
});

This creates a basic ListGrid backed by the `employees` data source.

At this point, we can run mvn install to build a WAR. Deploying this WAR to a servlet container such as Tomcat and visiting index.html, you will see something similar to:

Try playing around with the order of records. You can drag and drop individual records or several by selecting multiple records while holding the Ctrl key and dropping the selection. At this point, the order of records is lost upon refreshing the page, but we will fix this in the next section.

Handling the recordDrop event

We would now like to add the ability to persist the user's preferred order; after the user drops a selection of records, the "userOrder" field should update automatically.

The event that is triggered when a user drops records is recordDrop. The first parameter to this callback is an array of ListGridRecord objects. The second parameter is the ListGridRecord of the target row. The third parameter is the index of the target row. Finally, the fourth parameter is the ListGrid widget.

When a user drops a selection of records, the app will need to update not only the userOrder values of the dragged records, but all non-selected records between dragged records and the target record. In this sample, we will compute the range of indices of records that need to be updated as all records at indexMin through indexMax, inclusive:

var data = this.data;
var dropRecordIndices = dropRecords.map(function (record) {
    return data.findIndex("EmployeeId", record.EmployeeId);
});

var indicesMin = Math.min(dropRecordIndices.min(), targetIndex);
var indicesMax = Math.max(dropRecordIndices.max(), targetIndex - 1);

To update records, we can use the updateData method of DataSource.

We would like to update many records at once in a transaction. To accomplish this, we can call RPCManager.startQueue to start a queue, or batch, of update requests that are sent at once to the IDACall servlet for executing. IDACall will in turn translate the requests to a set of UPDATE statements and execute them within a transaction.

To update a record's userOrder field, we use the updateData method of the list grid's data source as follows:

//var ds = isc.DS.get(this.dataSource);

//var recordToUpdate, request, newUserOrder;

var updates = ds.filterPrimaryKeyFields(recordToUpdate);
updates.userOrder = newUserOrder;
ds.updateData(updates, null, request);

The calls to updateData are combined with the algorithm to correctly permute the userOrder values of each record at indexMin through indexMax, inclusive, as follows:

// Update the 'userOrder' fields for all records at indices [indicesMin, indicesMax].
var userOrders = new Array(indicesMax + 1 - indicesMin);
var i;
for (i = indicesMin; i <= indicesMax; ++i) {
    var record = data.get(i);
    userOrders[i - indicesMin] = record.userOrder;
}
var numDropRecordsAfterOrAtTargetIndex = 0;
for (i = indicesMax; i >= targetIndex; --i) {
    if (dropRecordIndices.contains(i)) {
        ++numDropRecordsAfterOrAtTargetIndex;
    }
}
var numDropRecordsBeforeTargetIndex = dropRecords.length - numDropRecordsAfterOrAtTargetIndex;
var j = 0; // how many drop records have been encountered so far.
for (i = indicesMin; i < targetIndex; ++i) {
    var record = data.get(i);
    request.oldValues.userOrder = record.userOrder;
    request._originalRecord = isc.shallowClone(record);

    var updates = ds.filterPrimaryKeyFields(record);
    if (dropRecordIndices.contains(i)) {
        updates.userOrder = record.userOrder = userOrders[targetIndex - numDropRecordsBeforeTargetIndex + j - indicesMin];
        ++j;
    } else {
        updates.userOrder = record.userOrder = userOrders[i - j - indicesMin];
    }
    ds.updateData(updates, null, request);
}
j = 0;
for (i = indicesMax; i >= targetIndex; --i) {
    var record = data.get(i);
    request.oldValues.userOrder = record.userOrder;
    request._originalRecord = isc.shallowClone(record);

    var updates = ds.filterPrimaryKeyFields(record);
    if (dropRecordIndices.contains(i)) {
        updates.userOrder = record.userOrder = userOrders[targetIndex + numDropRecordsAfterOrAtTargetIndex - 1 - j - indicesMin];
        ++j;
    } else {
        updates.userOrder = record.userOrder = userOrders[i + j - indicesMin];
    }
    ds.updateData(updates, null, request);
}

The final app.js is:

app.js
var listGrid = isc.ListGrid.create({
    width:900,
    height:500,
    dataSource:window.employees,
    autoFetchData:true,
    canEdit:true,
    canGroupBy:false,
    canReorderRecords:true,
    canSort:false, // Disable user sorting because we rely on records being sorted by 'userOrder'.
    autoSaveEdits:true,

    recordDrop : function (dropRecords, targetRecord, targetIndex, sourceWidget) {
        if (this == sourceWidget && dropRecords.length != 0) {
            var data = this.data;
            var dropRecordIndices = dropRecords.map(function (record) {
                return data.findIndex("EmployeeId", record.EmployeeId);
            });

            var indicesMin = Math.min(dropRecordIndices.min(), targetIndex);
            var indicesMax = Math.max(dropRecordIndices.max(), targetIndex - 1);

            var startedQueue = !isc.RPCManager.startQueue();
            var ds = isc.DS.get(this.dataSource);
            var request = {
                operation:this.updateOperation,
                application:this.application,
                willHandleError:true,
                oldValues:{ userOrder:0 },
                componentId:this.ID
            };

            // Update the 'userOrder' fields for all records at indices [indicesMin, indicesMax].
            var userOrders = new Array(indicesMax + 1 - indicesMin);
            var i;
            for (i = indicesMin; i <= indicesMax; ++i) {
                var record = data.get(i);
                userOrders[i - indicesMin] = record.userOrder;
            }
            var numDropRecordsAfterOrAtTargetIndex = 0;
            for (i = indicesMax; i >= targetIndex; --i) {
                if (dropRecordIndices.contains(i)) {
                    ++numDropRecordsAfterOrAtTargetIndex;
                }
            }
            var numDropRecordsBeforeTargetIndex = dropRecords.length - numDropRecordsAfterOrAtTargetIndex;
            var j = 0; // how many drop records have been encountered so far.
            for (i = indicesMin; i < targetIndex; ++i) {
                var record = data.get(i);
                request.oldValues.userOrder = record.userOrder;
                request._originalRecord = isc.shallowClone(record);

                var updates = ds.filterPrimaryKeyFields(record);
                if (dropRecordIndices.contains(i)) {
                    updates.userOrder = record.userOrder = userOrders[targetIndex - numDropRecordsBeforeTargetIndex + j - indicesMin];
                    ++j;
                } else {
                    updates.userOrder = record.userOrder = userOrders[i - j - indicesMin];
                }
                ds.updateData(updates, null, request);
            }
            j = 0;
            for (i = indicesMax; i >= targetIndex; --i) {
                var record = data.get(i);
                request.oldValues.userOrder = record.userOrder;
                request._originalRecord = isc.shallowClone(record);

                var updates = ds.filterPrimaryKeyFields(record);
                if (dropRecordIndices.contains(i)) {
                    updates.userOrder = record.userOrder = userOrders[targetIndex + numDropRecordsAfterOrAtTargetIndex - 1 - j - indicesMin];
                    ++j;
                } else {
                    updates.userOrder = record.userOrder = userOrders[i + j - indicesMin];
                }
                ds.updateData(updates, null, request);
            }

            // If we're queuing, send the queue now.
            if (startedQueue) {
                isc.RPCManager.sendQueue(null, null, null, true);
            }
        }

        // Call the super implementation of recordDrop() to update the order of rows in the ListGrid.
        this.Super("recordDrop", arguments);
    }
});
listGrid.addSort({ property:"userOrder" });

You can download the entire source code of this project (excluding the libraries in src/main/webapp/WEB-INF/lib) here.