Loading ...
Sorry, an error occurred while loading the content.

[SOLUTION] How to implement a DataTable CRUD with Ruby on Rails

Expand Messages
  • bradkieser
    This post is really about the update part of the CRUD solution but describes the delete and insert functions too. Having implemented CRUD on a datatable using
    Message 1 of 1 , Dec 30, 2009
    • 0 Attachment
      This post is really about the update part of the CRUD solution but describes the delete and insert functions too.

      Having implemented CRUD on a datatable using XHRDataSource in a Ruby on Rails system (actually JRuby) I thought that others may find the example useful. It build on Satyam's excellent work so full credit to him for most of the ideas here. It's a bit fiddly to get it going in Ruby on Rails because the solutions, whilst easy once they are identified, do not have very well documented examples and finding them is time consuming.

      Let me first describe the problem that I solved by giving some background to the CRUD solution and why an update is so tricky.

      In order of trickiness, the easiest to implement is delete. Then insert and finally the most tricky is update.

      The first port of call is to use YAHOO.util.XHRDataSource and JSON. This makes life very easy for you because Rails supports rendering JSON natively and XHRDataSource allows you to set up your data exchange configuration (meta data) nicely in some JavaScript arrays and then the Yahoo library will handle all the tricky bits for you.

      The trick is to leverage that Yahoo library's handling of the JSON strings so that you don't end up writing (and debugging) loads of JavaScript yourself and having the associated browser compatibility nightmares that Yahoo have so kindly solved for us if we use their tools!

      The issue here is that you can quite easily send requests to the Rails back end, so a delete is easy to implement. But when you are receiving JSON data and you need that data to be parsed and inserted into a datatable, you either have a long winded, instantiation-specific bug-ridden and browser-incompatible nightmare of a journey or you have the intelligent, generic, leverage Yahoo's hard work route with a little bit of a compromise in the Rails controllers because it's not quite as clean as Rails code normally is. But only a little bit of a compromise, I promise!

      Okay, so here's an example of many that I have coded.  The situation here is that I am maintaining Twitter accounts. Just a word on this example: Don't try this at home, folk! This is merely an example. If you are doing Twitter work for real you do NOT store screennames and passwords in a database! You implement OAuth and do it the correct and most importantly secure way, so please do not base an application on this code!

      So why do this example? Because it's fun to have the word "Twitter" in an example that's why. No other reason. This was originally an example based on client blacklists handling, but the word "blacklist" isn't as fun as the word "Twitter".

      Example only, not for production use!

      Right, now that I have the disclaimer out the way, let's have a look at a sample database setup.

      Here's my twitter_accounts table (in Postgres):

      create table twitter_accounts (id serial primary key,
      user_id integer references auth_user(user_id),
      screenname varchar(255),
      password varchar(255),
      active boolean not null default false,
      created_on timestamp,
      updated_on timestamp
      );
      Of course you would usually use a migration to create and maintain the table. At the risk of starting a flame war, I have to say that I prefer, for production systems, to NOT use migrations. I prefer to have well documented, DBA-controlled SQL scripts because in any large enterprise you have a team of DBAs looking after the database and your changes need to go through signoff procedures, change reviews, mitigation planning, etc. Presenting a DBA team with a Rails migration is not enterprise-friendly. There are other reasons why I don't use migrations for database changes but that's out of scope of this post.

      So now we have the database structure, here is the Yahoo meta data. I am not going to describe everything in detail as it's based on Satyam's ideas and he's done an excellent tutorial here: http://www.yuiblog.com/blog/2008/10/27/datatable-260-part-two/

      I place this in a layout so that it can be reused easily across logical groups of screens that need the same datatable:

      <table border="0" cellspacing="0" cellpadding="3" align="center">
                <thead>
                    <tr>
                        <th>Twitter Accounts</th>
                    </tr>
                </thead>
                <tr>
                  <td valign="top">
                      <div id="accountPanel" style="COLOR: #FF0000">
                        <table id="accountTable">
                            <thead>
                                <tr>
                                    <th>ID</th>
                                    <th>Screen name</th>
                                    <th>Password</th>
                                    <th>active</th>
                                </tr>
                            </thead>
                        </table>
                    </div>
                        <p id="account_paginator"></p>

                    <script type="text/javascript">
                      // We wrap this in a function so that YUI doesn't break prototype.js (same class in both: Event)'
                      (function() {
                            var Dom = YAHOO.util.Dom,
                                Event = YAHOO.util.Event;

                            // We do this to enable all the screen to be constructed first. Else the containers may not exist when this fires up the constructors.
                            Event.onDOMReady(function() {
                                var accountDataSource = new YAHOO.util.XHRDataSource("<%= url_for(:controller => :twitter_account, :action => :list_accounts, :id=> :all) %>.json?"); // Note that the ? is needed because the YUI stuff just adds to the request dumbly, not checking is parameters are there or not.
                                accountDataSource.responseType = YAHOO.util.XHRDataSource.TYPE_JSON;
                                accountDataSource.connXhrMode = "queueRequests";

                                accountDataSource.responseSchema = {
                                    metaFields: { totalRecords: 'totalRecords' ,
                                                  pageSize: 'pageSize' ,
                                                  startIndex: 'startIndex' ,
                                                  reqStatus: 'reqStatus' },
                                    resultsList : "accounts",
                                    fields: [{key:"twitter_account.id"},
                                             {key:"twitter_account.screenname"},
                                             {key:"twitter_account.password"},
                                             {key:"twitter_account.active"}
                                    ]
                                };

                                var accountColumnDefs = [
                                    {key:"twitter_account.id", label: "ID", abbr: "Row ID", sortable:true, resizeable: true, isPrimaryKey: true},
                                    {key:"twitter_account.screenname", label: "Screenname", abbr: "User", sortable:true, resizeable: true,
                                              editor: new YAHOO.widget.TextboxCellEditor({asyncSubmitter:  function (callback, newValue) {asyncUpdateHandler(callback, newValue, this)}})},
                                    {key:"twitter_account.password", label: "Password", abbr: "Pass", sortable:true, resizeable: true,
                                              editor: new YAHOO.widget.TextboxCellEditor({asyncSubmitter:  function (callback, newValue) {asyncUpdateHandler(callback, newValue, this)}})},
                                    {key:"twitter_account.active", label: "Active", abbr: "Active", sortable:true, resizeable: true, editor: new YAHOO.widget.CheckboxCellEditor()},
                                    {key:'delete', label:' ', className:'delete-button', action:'delete'},
                                    {key:'insert', label:' ', className:'insert-button', action:'insert'},
                                ];

                                var accountConfigs = {
                                  paginator : new YAHOO.widget.Paginator({
                                      rowsPerPage    : 10,
                                      totalRecords   : accountDataSource.length, // OPTIONAL

                                      // use an existing container element
                                      containers : 'account_paginator',

                                      // use a custom layout for pagination controls
                                      template       : "<div align=\"center\">{FirstPageLink} {PreviousPageLink} {PageLinks} {NextPageLink} {LastPageLink}</div></br>Page: {CurrentPageReport} Show {RowsPerPageDropdown} rows per page",

                                      // show all links
                                      // pageLinks : YAHOO.widget.Paginator.VALUE_UNLIMITED,
                                      pageLinks : 4,


                                      // use these in the rows-per-page dropdown
                                      rowsPerPageOptions : [10,25,50,100,250,500],

                                      // use custom page link labels
                                      pageLabelBuilder : function (page,paginator) {
                                          var recs = paginator.getPageRecords(page);
                                          return (recs[0] + 1) + ' - ' + (recs[1] + 1);
                                      }

                                  }),

                                  dynamicData: true, // This tells the paginator to refresh after an autocomplete update
                                  // width: "50em", // width as a string value but we want it to auto-scale so it's commented out'
                                  height: "200px" // height as a string value
                              };

                               // If we are on a table cell, highlight it
                               var highlightEditableCell = function(oArgs) {
                                    var elCell = oArgs.target;
                                    if(YAHOO.util.Dom.hasClass(elCell, "yui-dt-editable")) {
                                        this.highlightCell(elCell);
                                    }
                                };


                                var accountDataTable = new YAHOO.widget.ScrollingDataTable("accountPanel", accountColumnDefs, accountDataSource,  accountConfigs);


                                // This is used to make sure that the paginator gets the meta data info
                                accountDataTable.handleDataReturnPayload = function(req, res, payload) {
                                    payload = payload || {};
                                    payload.totalRecords = res.meta.totalRecords;
                                    return payload;
                                };

                                // ------------------------------------------
                                // Now set up the autocomplete search faclity
                                // ------------------------------------------


                                // This function will be called to reload the data into the dataTable from the AutoComplete widget
                                var getMobile = function(query) {
                                  accountDataSource.sendRequest('&search=' + query,accountDataTable.onDataReturnInitializeTable, accountDataTable);
                                };

                                var oACDS = new YAHOO.util.FunctionDataSource(getMobile);
                                var oAutoComp = new YAHOO.widget.AutoComplete("dt_input_mobile","dt_ac_container", oACDS);
                                oAutoComp.minQueryLength = 0;

                                // Subscribe to the events for the cells in the table that makes it all happen
                                // These must be after the autocomplete else autocomplete is broken by them,

                                // This next line would be used to simply implement editing, but as there is a delete button column, we need to bang in a function
                                // accountDataTable.subscribe("cellClickEvent", accountDataTable.onEventShowCellEditor);

                                // ... and here is that cellClickEvent with a function to handle deletes!
                                accountDataTable.subscribe('cellClickEvent',onCellClickInsertDeleteHandler);

                                accountDataTable.subscribe("cellMouseoverEvent", highlightEditableCell);
                                accountDataTable.subscribe("cellMouseoutEvent", accountDataTable.onEventUnhighlightCell);


                            })
                    })( );
                </script>
                  </td>
                </tr>
              </table>

      Note how the XHR handler is set up:
           var accountDataSource = new YAHOO.util.XHRDataSource("<%= url_for(:controller => :twitter_account, :action => :list_accounts, :id=> :all) %>.json?");

      This is done because of the vagaries of the driving searching and column ordering from within the DataTable. The Yahoo widget simply adds parameters to the string, for example when a user is searching or if they click on a column header to change the ordering.  However, this creates a problem for the insert feature as this breaks the Rails /:controller/:action/:id URL format. We will come to that in a minute.

      For now, what we have above sets up the ability to select from the database.

      This secion:

                               accountDataSource.responseSchema = {
                                    metaFields: { totalRecords: 'totalRecords' ,
                                                  pageSize: 'pageSize' ,
                                                  startIndex: 'startIndex' ,
                                                  reqStatus: 'reqStatus' },
                                    resultsList : "accounts",
                                    fields: [{key:"twitter_account.id"},
                                             {key:"twitter_account.screenname"},
                                             {key:"twitter_account.password"},
                                             {key:"twitter_account.active"}
                                    ]
                                };

      Defines the fomat that the data will come back from the Rails back end. The meta data are items needed for the pagination process to work properly when driving pagination against the database, essential for large datasets. Yahoo will use this config to be able to accept payloads from the Rails back and and correctly parse the JSON into usable JavaScript structures. We don't need to worry about how it does it.

      This section tells the DataTable how the JSON payload translates into displayed rows and columns on the screen:
                                var accountColumnDefs = [
                                    {key:"twitter_account.id", label: "ID", abbr: "Row ID", sortable:true, resizeable: true, isPrimaryKey: true},
                                    {key:"twitter_account.screenname", label: "Screenname", abbr: "User", sortable:true, resizeable: true,
                                              editor: new YAHOO.widget.TextboxCellEditor({asyncSubmitter:  function (callback, newValue) {asyncUpdateHandler(callback, newValue, this)}})},
                                    {key:"twitter_account.password", label: "Password", abbr: "Pass", sortable:true, resizeable: true,
                                              editor: new YAHOO.widget.TextboxCellEditor({asyncSubmitter:  function (callback, newValue) {asyncUpdateHandler(callback, newValue, this)}})},
                                    {key:"twitter_account.active", label: "Active", abbr: "Active", sortable:true, resizeable: true, editor: new YAHOO.widget.CheckboxCellEditor()},
                                    {key:'delete', label:' ', className:'delete-button', action:'delete'},
                                    {key:'insert', label:' ', className:'insert-button', action:'insert'},
                                ];

      Of particular note is the use of Async handlers in the editors for the text fields. This is the key to enabling updates from the back end.
      asyncUpdateHandler is defined in a layout because this is generic and can apply across the entire web site. Its call has to be wrapped in the function definition because of scoping issues.

      This section is what implements the insert and delete handling:

                                accountDataTable.subscribe('cellClickEvent',onCellClickInsertDeleteHandler);

                                accountDataTable.subscribe("cellMouseoverEvent", highlightEditableCell);
                                accountDataTable.subscribe("cellMouseoutEvent", accountDataTable.onEventUnhighlightCell);

      The onCellClickInsertDeleteHandler is also defined in a layout because it's generic and can be universally used across the web site.

      So let's look at the layout that defines these functions (actually, I implement this in a shared partial that layouts render):

      <script type="text/javascript">
      // -------------------------------------
      // Generic delete handler for DataTables
      // -------------------------------------

      // This builds the "where" clause to add the key to be passed to the delete
      var appendPrimaryKey = function(datatable,record) {
          var url = '';
          var cols = datatable.getColumnSet().keys;
          for (var i = 0; i < cols.length; i++) {
              if (cols[i].isPrimaryKey) {
                  // The next line is rails-compatible. We could prepend the '&' if other params were being passed before this
                  // For other systems that expect normal parameter passing, we need to have cols[i].key as the parameter name passed
                  url += '/'+escape(record.getData(cols[i].key));
              }
          }
          return url;
      };

      // Now we specify the click handler that will be added as a listner to the cell click event on the data table
      var onCellClickInsertDeleteHandler = function(oArgs) {
          var target = oArgs.target,
              column = this.getColumn(target),
              record = this.getRecord(target);
          switch (column.action) {
              case 'delete':
                  if (confirm('Are you sure?')) {

                      YAHOO.util.Connect.asyncRequest(
                          'GET',
                          '<%= url_for(:action => :destroy) %>' + appendPrimaryKey(this,record)+'.json',
                          {
                              success: function (o) {
                                  try {
                                      var r = YAHOO.lang.JSON.parse(o.responseText);
                                      if (r.reqStatus == 200) {
                                          this.deleteRow(target);
                                      } else {
                                          var r = YAHOO.lang.JSON.parse(o.responseText);
                                          alert(r.errorText);
                                      }
                                  } catch (e){
                                      alert ("Parsing error");
                                      alert("Data is:"+o.responseText)
                                  }
                              },
                              failure: function (o) {
                                  alert(r.statusText);
                              },
                              scope:this
                          }
                      );
                  }
                  break;
             case 'insert':
                      this.getDataSource().sendRequest('new=true',
                          {
                              success: function (req,res) {
                                  if (res.meta.reqStatus == 201) {
                                      this.onDataReturnInsertRows(req,res,{insertIndex: 0});
                                      this.scrollIntoView(alignWithTop);
                                  } else {
                                      alert(res.errorText);
                                  }
                              },
                              failure: function (o) {
                                  alert(o.statusText);
                              },
                              scope:this
                          }
                      );
              break;

              default:
                  this.onEventShowCellEditor(oArgs);
                  break;
          }
      };

      // This is a generic update handler. It gets called from the editor in a column definition for a data table.
      var asyncUpdateHandler = function(callback, newValue, oScope) {
              var record = oScope.getRecord(),
                  column = oScope.getColumn(),
                  oldValue = oScope.value,
                  datatable = oScope.getDataTable();
              YAHOO.util.Connect.asyncRequest(
                  'POST',
                  '<%= url_for(:action => :update) %>' + appendPrimaryKey(datatable,record) + '.json',
                  {
                      success:function(o) {
                          var r = YAHOO.lang.JSON.parse(o.responseText);
                          if (r.reqStatus == 201) {
                              callback(true, r.data);
                          } else {
                              alert(r.errorText);
                              callback();
                          }
                      },
                      failure:function(o) {
                          alert(o.errorText);
                          callback();
                      },
                      scope:oScope
                  },
                  'column=' + column.key + '&newValue=' +
                      escape(newValue) + '&oldValue=' + escape(oldValue)
              );
      }

      </script>
      This is where things get a bit fiddly so let's look at the easy cause, delete. You can see that is uses:
      YAHOO.util.Connect.asyncRequest
      This is great for Rails because you can pass the URL in raw and dispatch to the back end. However, when it comes to inserting, this is a different story. You have two choices. Pass use the same mechanism as the delete function but then you have to do all the work yourself adding the new row into the data table, or else you can use Yahoo's tools which will read your meta data setup (per above) by using the sendRequst function in the configured DataSource but then you have the problem that the DataSource URL has been set up with a full search URL so that it supports searching and ordering parameters being appended by the Yahoo widgets at the end of the URL.

      I don't like reinventing the wheel and more importantly, I trust Yahoo to have browser compatibility issues sorted and optimised JavaScript, so I go with using the Yahoo widgets.

      This means that the controller needs to be aware that a search may not be a search, it may be an insert depending on whether the parameter "new=true" has been appended the URL or not.

      So here is what the controller for all the above looks like, including support for searches:

      class TwitterAccountController < ApplicationController
        def show
          render :twitter_account
        end

        def twitter_account

        end

        def list_accounts
          # In order to use the Yahoo data parsing and record processing set up for the data source
          # the inserts use the datasource which is set up for query, so the parameter "new=true" is appended
          # to mark an insert rather than a query.
          if params[:new]
            new
          else
            query
          end

        end

        def query
            searchterm=params["search"]||''

            # These are added to the query automatically by YUI's paginator
            startindex=params["startIndex"]||0 # What row number to start at
            results=params["results"]||50   # How many to return
            sort=params["sort"]||"screenname"
            dir=params["dir"]||"asc"

            get_accounts(dir, searchterm, sort)

            if @... == 0
              create_new_record
              @...
              get_accounts(dir, searchterm, sort)
            end

            respond_to do |format|
              format.html  # index.html.erb
              format.xml  { render :xml => @accounts }
              format.json  { data = {"accounts" => @accounts,
                                     "totalRecords" => @...,
                                     "reqStatus" => 201,
                                     "pageSize"=>results,
                                     "startIndex"=>startindex}
                             render :json =>  data}
            end

        end

        def get_accounts(dir, searchterm, sort)
                  @accounts = TwitterAccount.find(:all, :conditions=>["screenname ~* ?",searchterm], :order=>sort.sub("twitter_account.","")+' '+dir)
        end

        def new
          create_new_record

          if @...
            data = {"accounts" => @account,
                    "totalRecords" => 1,
                    "reqStatus" => 201,
                    "startIndex"=>0}
            render :json =>  data
          else
            data = {"reqStatus" => 500,
                    "errorText"=>"Cannot insert new record"}
            render :json =>  data
          end
        end

        def create_new_record
          @account = TwitterAccount.new
          @... ="UserName"
          @... ="Password"
          @currUser=current_user
          @..._id =@..._id
        end

        def destroy
          @account = TwitterAccount.find(params[:id])
          if @...
            respond_to do |format|
              format.json  { data = {"reqStatus" => 200}
                             render :json =>  data}
              end
          else
            respond_to do |format|
              format.json  { data = {"reqStatus" => 500,
                                     "errorText"=>"Record not found"}
                             render :json =>  data}
              end
          end
        end

        def update
          @account = TwitterAccount.find(params[:id])
          colname=params[:column].sub("twitter_account.","")
          value=params[:newValue]
          @account[colname]=value

          if @...
            data = {"accounts" => @account,
                    "data" => value,
                    "reqStatus" => 201}
            render :json =>  data
          else
            data = {"reqStatus" => 500,
                    "errorText"=>"Cannot insert new record"}
            render :json =>  data
          end
        end


      end

      Nice and easy!
      This method arbitrates between inserts and searches:

        def list_accounts
          if params[:new]
            new
          else
            query
          end

        end
      Whereas this method handles the update requests from the asyc calls in the cell editors:
        def update
          @account = TwitterAccount.find(params[:id])
          colname=params[:column].sub("twitter_account.","")
          value=params[:newValue]
          @account[colname]=value

          if @...
            data = {"accounts" => @account,
                    "data" => value,
                    "reqStatus" => 201}
            render :json =>  data
          else
            data =

      (Message over 64 KB, truncated)
    Your message has been successfully submitted and would be delivered to recipients shortly.