DB Write-Back Part 5: Putting it All Together to Write a SQL Azure Write-Back Extension

In the first four parts of this series, we incrementally built up an understanding of the technologies, libraries, and concepts needed to author a Tableau Extension that writes to a database. To wrap it all up, we will now build an extension that will persist a user’s stack-ranking of a list of items to a SQL Azure table. The name of the user, name of the item, and numeric rank will be inserted.

The working code for part5 is checked in on the Tabblogs repository on github.

First, we’ll generate a new skeleton for our project in the same way we did for part 4:

  • In a command prompt at your root directory, type “express –no-view part5”. This is assuming you have Express installed from earlier articles.
  • navigate to the part5 directory
  • type “npm install” to initialize the environment
  • Confirm that everything is working:
    • type “npm start” to start the web server with the new environment
    • browse to http://localhost:3000. You should see “Welcome to Express”.

We have some work to do on index.html! We will need a place to show the user the items they have selected and a way for them to express their stack-rank of those items.

  • Add a script source tag for Tableau Extensions.
  • Add a script source tag for JQuery.
  • Add script source tags for datatables. Datatables controls make attractive and functional table controls very easy to build.
  • Add a reference to the mark selection style sheet.

The head section of index.html:

<head>
  <title>TabBlogs.com DB Write-Back Tableau Extension: Soup To Nuts</title>
  <link rel="stylesheet" href="/stylesheets/style.css">
  
  <!-- Tableau extension -->
  <script src="tableau.extensions.1.2.0.js"></script>
  
  <!-- jQuery -->                                                    
  <script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>

  <!-- Copied from Tableau Extensions Tutorial -->
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <!-- These two libraries are copied from the download builder here: https://datatables.net/download/ -->
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs-3.3.7/jq-3.2.1/jq-3.2.1/dt-1.10.16/b-1.4.2/b-colvis-1.4.2/cr-1.4.1/fh-3.1.3/r-2.2.0/sc-1.4.3/datatables.min.css"/>
  <script type="text/javascript" src="https://cdn.datatables.net/v/bs-3.3.7/jq-3.2.1/jq-3.2.1/dt-1.10.16/b-1.4.2/b-colvis-1.4.2/cr-1.4.1/fh-3.1.3/r-2.2.0/sc-1.4.3/datatables.min.js"></script>
  <!-- Include the rowReorder datatables extension. (https://datatables.net/extensions/rowreorder/) -->
  <script type="text/javascript" src="https://cdn.datatables.net/rowreorder/1.2.5/js/dataTables.rowReorder.min.js"></script>
  <!-- Custom styling from Tableau Extensions Tutorial -->
  <link rel="stylesheet" href="./stylesheets/MarksSelection.css">

  <!-- references to our code -->
  <script src="./javascripts/part5.js"></script>
  <script src="./javascripts/appDB.js"></script>
</head>

The body section of index.html (below) contains five div regions. The outermost is just there to apply the “container-fluid” bootstrap format to everything. The div id “selected_marks” is not used. There’s a caption formatted as h4 with an id of “selected_marks_title”. We will use that id in part5.js to write the name of the “Sale Map” worksheet. The Tableau Extensions tutorial on which this html is based allows the user to select which worksheet they want to use. I’ve pulled that functionality out to simplify this page.

The next div has an id of “data_table_wrapper”. This is where we will place the datatable in our JavaScript in part5.js. The next div called “no_data_message” will only show if there is no datatable because the user has not selected any marks on the sale map.

We then have one last div section where we obtain the user’s name (id userName) to persist to the database and give the user a button to push when they have finished selecting and ranking. This button has an onclick tag with a value of “postRank()”. This means that the postRank function, over in appDB.js, will be called when the user pushes the button.

<body>
    <div class="container-fluid">
      <div id="selected_marks">
        <h4>
          Favorite states from <span class="sheet_name" id="selected_marks_title">Sheet Name</span>
        </h4>
        <div id="data_table_wrapper"></div>
        <div id="no_data_message">
          <h5>No marks selected. Please select your top five favorite states.</h5>
        </div>
      </div>
      <div>
        <label>What's your name? </label>
        <input type="text" id="userName" placeholder="Your name here"></input>
        <button id="submitRank" onclick="postRank()">Submit My Ranking</button>
      </div>
    </div>
</body>

Add a part5.js file to the javascripts directory. I’ve re-used and simplified some code from the Tableau Extensions Tutorial to pull data from the selected marks in the “Sale Map” worksheet and display it in a datatable control.

All of the code in part5.js is surrounded by an empty function. This common practice isolates the scope of the variables and functions within its scope, avoiding the risk of contaminating the JavaScript global namespace in our browser instance, potentially overwriting something we did not know existed. Part5.js is just going to load and run when index.html runs, so there is no need for these functions to be callable from outside of the file.

The code in part5.js is copied from the Tableau Extensions Tutorial, except for the “rowReorder: true” attribute of the data_table. This attribute actives the “dataTables.rowReorder.min.js” library that we pulled in in the index.html head section, and enables the user to drag and drop table rows to reorder them. It’s a little fiddly, only allowing a row to me moved up or down one row at a time, but it’s a useful feature.

(function() {
    //load tableau extension
    tableau.extensions.initializeDialogAsync().then(()=> {
    loadSelectedMarks("Sale Map");
  });
     
  // This variable will save off the function we can call to unregister listening to marks-selected events
  let unregisterEventHandlerFunction;
  function loadSelectedMarks (worksheetName) {
    // Remove any existing event listeners
    if (unregisterEventHandlerFunction) {
      unregisterEventHandlerFunction();
    }
    
    // Get the worksheet object we want to get the selected marks for
    const worksheet = getSelectedSheet(worksheetName);

    // Set our title to an appropriate value
    $('#selected_marks_title').text(worksheet.name);

    // Call to get the selected marks for our sheet
    worksheet.getSelectedMarksAsync().then( (marks) => {
      // Get the first DataTable for our selected marks (usually there is just one)
      const worksheetData = marks.data[0];

      // Map our data into the format which the data table component expects it
      const data = worksheetData.data.map((row, index)=> {
        const rowData = row.map((cell) => {
          return cell.formattedValue;
        });

        return rowData;
      });

      const columns = worksheetData.columns.map( (column)=> {
        return { title: column.fieldName };
      });

      // Populate the data table with the rows and columns we just pulled out
      populateDataTable(data, columns);
    });

      // Add an event listener for the selection changed event on this sheet.
    unregisterEventHandlerFunction = worksheet.addEventListener(tableau.TableauEventType.MarkSelectionChanged, (selectionEvent) => {
      // When the selection changes, reload the data
      loadSelectedMarks(worksheetName);
    });
  }

  function populateDataTable (data, columns) {
    // Do some UI setup here to change the visible section and reinitialize the table
    $('#data_table_wrapper').empty();

    if (data.length > 0) {
      $('#no_data_message').css('display', 'none');
      $('#data_table_wrapper').append(`<table id='data_table' class='table table-striped table-bordered'></table>`);

      // Do some math to compute the height we want the data table to be
      var top = $('#data_table_wrapper')[0].getBoundingClientRect().top;
      var height = $(document).height() - top - 130;

      // Initialize our data table with what we just gathered
      $('#data_table').DataTable({
        data: data,
        columns: columns,
        autoWidth: true,
        authHeight: true,
        deferRender: true,
        scroller: true,
        scrollY: height,
        scrollX: true,
        dom: "<'row'<'col-sm-6'i><'col-sm-6'f>><'row'<'col-sm-12'tr>>", // Do some custom styling
        rowReorder: true
      });
    } else {
      // If we didn't get any rows back, there must be no marks selected
      $('#no_data_message').css('display', 'inline');
    }
  }

  function getSelectedSheet (worksheetName) {
    // Go through all the worksheets in the dashboard and find the one we want
    return tableau.extensions.dashboardContent.dashboard.worksheets.find((sheet)=> {
      return sheet.name === worksheetName;
    });
  }
})();

AppDB.js is a library containing two functions: postData and postRank. postData is almost identical to the fetchData function from appDB.js of part4. postRank is the function called when the user presses the button on index.html. The first line of postRank takes the list of state names from the first column of the datatable that was created by part5.js and places them neatly into an array. The next line declares an object (called postObject) that we will need to pass our data through the fetch in postData. We also declare an array called insertData to store the data set we are going to build and pass to the database code.

The for loop in PostRank will iterate through the array of statenames, filling out a new array containing three name/value pairs for each row of data to be inserted into our database table. We then place our new array into the object, use the built-in function JSON.stringify to prepare it to be sent through a fetch, and call postData with the stringified object.

Copy AppDB.js from part4 and modify it as below, or bring it in from the github part5 folder.

let stateList = [];

postData = (insertData) => {
    return new Promise( (resolve, reject) => {
    fetch("/postData", {
      method: "POST",
      headers: {
        "Content-type": "application/json"
      },
      body: insertData
    })
      .then(function(response) {
        const r = response.json();
        return r;
      })
      .then(function(myJson) {
        $('#result').text(JSON.stringify(myJson));
        return(myJson);
      })
      .catch(function(err) {
        if (err) {
          throw err;
        }
      });
    });
}

function postRank(){
  var stateNames = $('#data_table').DataTable().columns(0).data().toArray()[0]; //push the first column of the html table into an array
  var postObject = {};
  var insertData = []; 
  const name = $('#userName').val();

  for(i=0;i<stateNames.length;i++){
    const state = stateNames[i];
    const index = i+1;
    insertData.push({userName:name});
    insertData.push({stateName:state});
    insertData.push({stateRank:index});
  }

  postObject.data = insertData;
  postData(JSON.stringify(postObject));
};

DB.js is modified from part4 in order to format the data passed from AppDB.js into a TSQL INSERT statement.

const sql = require('mssql');

// Enter your own credenials in the credentialsExample.js and rename it to credentials.js
const credentials = require("./credentials.js");

exports.insertData = (req, res)=>{  
    let column = [];
    let values = [];
    
    let data = req.body.data;
  
    let jsondata = JSON.stringify(data);
    JSON.parse(jsondata, (key,value)=>{
        switch(key){
            case "userName":
                values += "('"+value+"',"
                break;
            case "stateName":
                values += "'"+value+"',"
                break;
            case "stateRank":
                values += "'"+value+"'),"
                break;
        }
    });

    valueArray = values.substr(0,values.length-1);
  
    let insertQuery = `INSERT INTO ranking.dbo.states (userName,stateName,stateRank) VALUES ${valueArray};`;
  
    //console.log(insertQuery);
  
    //send the insertQuery
    new sql.ConnectionPool(credentials.config)
      .connect()
      .then((pool)=> {
        return pool.request().query(insertQuery);
      })
      .then((result)=> {
        console.log(result);
        sql.close();
      })
      .catch((err)=> {
        if (err) throw err;
        sql.close();
      });
    res.end(); 
  };
  
  function addQuote(val) {return val.length ? "'" + val.join("','") + "'" : "";
}

module.exports = exports;

You will need a credentials.js file containing your SQL Azure credentials. Rename the credentialsExample.js file and edit it with your information. The state.sql file contains a CREATE TABLE script to build the simple schema you will need.

With index.html creating the UI of our extension, app.js establishing the Express routes that we need, part5.js reading the selected marks from the dashboard and creating a datatable in our extension UI, appDB.js reading the sorted table and creating a data set with which to call SQL Azure, credentials.js supplying your credentials and db.js doing the database call, everything is in place.

Select a few of your favorite US states, sort your table by something other than the state name, then you will be able to drag and drop the rows of the table according to your preference of US states. Enter your name, then hit the “Submit My Ranking” button. Go query your SQL Azure table and you should see: