Now that I have a substantial number of posts and some good quality content, I often find the need to look back at prior posts. Unfortunately, this has consisted of paging through my blog or clicking on tags. To resolve this, I'm introducing the search feature. To visit the search page, hover over Blog in the header, and click Search Posts.

Probably, the easiest and most effective way to implement search is to use Google Site Search, but I thought that it would be more fun to use PostgreSQL's full text search capabilities. Plus, I'd rather keep my site ad-free, and I don't want to pay. From the title picture, one can see that PostgreSQL full text search has lots of features like the ability to understand grammar, rank matches, and extract the relevant text.

Under the hood, posts are stored as tsvectors.

phillypham::DATABASE=> SELECT setweight(to_tsvector('Post title'), 'A') || setweight(to_tsvector('The_quick_brown_fox_jumps_over_the_lazy_dog'), 'B') AS tsvector FROM posts LIMIT 1;
                                     tsvector                                      
-----------------------------------------------------------------------------------
 'brown':5B 'dog':11B 'fox':6B 'jump':7B 'lazi':10B 'post':1A 'quick':4B 'titl':2A
(1 row)

As you can see, there's the ability to weight the title more heavily than the body.

Now, to make this work properly, I added another column to the posts table. I indexed it and created a trigger to make sure that it's automatically updated. Then, to integrate properly with Sequelize, I added all these queries to an afterSync hook.

=> ALTER TABLE posts ADD COLUMN title_body_tsvector tsvector;
=> UPDATE posts SET title_body_tsvector=setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B');
=> CREATE INDEX IF NOT EXISTS title_body_search_idx ON posts USING gin(title_body_tsvector);
=> CREATE OR REPLACE FUNCTION posts_trigger() RETURNS trigger AS $$ begin new.title_body_tsvector := setweight(to_tsvector('english', new.title), 'A') || setweight(to_tsvector('english', new.body), 'B'); return new; end $$ LANGUAGE plpgsql;
=> CREATE TRIGGER posts_update BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE posts_trigger();

See Post.js for more details.

Then, to do a search, I execute a raw query and return it as a promise. The query looks like this:

SELECT id, 
    title, 
    ts_rank_cd(title_body_tsvector,$QUERY,1) AS rank, 
    ts_headline('english', title || ' ' || body,$QUERY, 'MaxWords=100') AS headline 
    FROM posts 
    WHERE published AND title_body_tsvector @@ $QUERY
    ORDER BY rank DESC, id DESC;

The Javascript looks like this:

function(db, tsquery) {                                
    var query = tsquery.indexOf(' ') == -1 ? "to_tsquery('english','" + tsquery + "')" : "plainto_tsquery('english','" + tsquery + "')";
    return db.sequelize.query("SELECT id, title, ts_rank_cd(title_body_tsvector," + query + ",1) AS rank, ts_headline('english', title || ' ' || body," + query + ", 'MaxWords=100') AS headline FROM posts WHERE published AND title_body_tsvector @@ " + query + " ORDER BY rank DESC, id DESC",
    { model: db.Post });
}

Note the ternary operator when defining query. PostgreSQL tsquerys allow searches of arbitrary complexity with nesting and various boolean operators. Unfortunately, most people will not have the syntax memorized, so plainto_tsquery lets one search with more natural syntax. If you get the syntax wrong (e.g., forget to close a parentheses), the error will be handled gracefully thanks to promises.

Try it and let me know what you think!

In Snapstream Searcher, a wep application I'm developing, I want to enable the user to search for specific lists of programs. Thus, this would require creating a database where users could manage and edit these lists. Rather than creating a nice RESTful API to manage the reading and editing of this database, I naturally decided to be lazy and just have users use Google Sheets instead.

Doing this wasn't actually as straightforward as I thought it would be, so I'm writing some notes to myself on the process. There are several moving parts here:

• the Google sheet itself
• App Script to create an API to get the sheet data
• Google Developer Console to create an OAuth token, so you can use the API
• client-side JavaScript to call the API

First, here's a working example. You can edit this sheet. When you edit it, you will see the changes reflected here, JSFiddle. Here's the API that I created in this app script. You'll probably need to authorize yourself with a Google account and refresh the page for it to work.

Directions

1. Create a Google Sheet in your Google Drive and make it editable by users with a link by clicking share in the upper right.

2. Go to the Script Editor and make your API to get the sheet data. You can find a detailed reference here, Spreadsheet Service. The API seemed pretty intuitive. You can copy and paste this code in and change the URL to your document's:

    function getData() {
        // link to your google doc
        var spreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/<YOUR DOCUMENT>/edit');
        var sheet = spreadSheet.getActiveSheet();
        var dataRange = sheet.getDataRange();
        var values = dataRange.getValues();
        return values;
    }
   

3. Now, follow Step 1 of this guide to make an OAuth token: JavaScript Quickstart
4. Then, deploy as an API according to this guide: Using the Execution API

5. Now, on the client-side create an HTML file, something like this, where you substitute your project key and client id. If you need other scopes, check Project Properties in the Script Editor.

    <!DOCTYPE html>
    <html>
      <head>
        <style type="text/css">
         table {
           border-collapse: collapse;
         }
         td, th {
           padding: 10px;       
           border: 1px solid gray;
         }
        </style>
        <script>
         var CLIENT_ID = '<CLIENT_ID>';
         var SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
         /**
          * Check if current user has authorized this application.
          */
         function checkAuth() {
           gapi.auth.authorize(
             {
               'client_id': CLIENT_ID,
               'scope': SCOPES.join(' '),
               'immediate': true
             }, handleAuthResult);
         }
         /**
          * Handle response from authorization server.
          *
          * @param {Object} authResult Authorization result.
          */
         function handleAuthResult(authResult) {
           var authorizeDiv = document.getElementById('authorize-div');
           if (authResult && !authResult.error) {
             // Hide auth UI, then load client library.
             authorizeDiv.style.display = 'none';
             callScriptFunction();
           } else {
             // Show auth UI, allowing the user to initiate authorization by
             // clicking authorize button.
             authorizeDiv.style.display = 'inline';
           }
         }
   
         /**
          * Initiate auth flow in response to user clicking authorize button.
          *
          * @param {Event} event Button click event.
          */
         function handleAuthClick(event) {
           gapi.auth.authorize(
             {client_id: CLIENT_ID, scope: SCOPES, immediate: false},
             handleAuthResult);
           return false;
         }
   
         function callScriptFunction() {
           var scriptId = "<PROJECT_KEY>"; // aka Project key
           // Create an execution request object.
           var request = {
             'function': 'getData'
           };
   
           var op = gapi.client.request({
             'root': 'https://script.googleapis.com',
             'path': 'v1/scripts/' + scriptId + ':run',
             'method': 'POST',
             'body': request
           });
   
           op.execute(function(res) {
             var htmlTable = '<table>';
             res.response.result.forEach(function(row, idx, rows) {         
               if (idx == 0) {
                 htmlTable += '<tr><th>' + row.join('</th><th>') + '</th></tr>';
               } else {
                 htmlTable += '<tr><td>' + row.join('</td><td>') + '</td></tr>';
               }
             });
             htmlTable += '</table>';
             document.getElementById('output').innerHTML = htmlTable;
           });
         }
        </script>    
        <script src="https://apis.google.com/js/client.js?onload=checkAuth">     
        </script>
      </head>
      <body>
        <div id="authorize-div" style="display: none">
          <span>Authorize access to Google Apps Script Execution API</span>
          <!--Button for the user to click to initiate auth sequence -->
          <button id="authorize-button" onclick="handleAuthClick(event)">
            Authorize
          </button>
        </div>
        <div id="output"> 
        </div>
      </body>
    </html>
   

6. To test it out, you can run run a local server with python -m http.server 8000. Make sure you've added http://localhost:8000 to your Authorized JavaScript origins when creating the OAuth token.
7. You should see your sheet in the form of a HTML table.

Happy New Years, everyone! As a way to start off the year, I thought that it would be interesting to write about something that has evolved a lot over the past year: the Republican field of presidential candidates.

At Penn, I've been working on Snapstream Searcher, which searches through closed captioning television scripts. I've decided to see how often a candidate is mentioned on TV has changed over the year. Check out the chart and do your own analysis here.

As you can see in the title picture, Donald Trump has surged in popularity since he announced his candidicy in June. In general every candidate, experiences a surge in mentions upon announcing his or her candidacy. Usually the surge is not sustained, though.

Many candidates lost popularity over the course of 2015. Jeb Bush lost quite a bit of ground, but perhaps no one has suffered as much as Chris Christie.

Other candidates like Ben Carson are passing fads with a bump from Octorber to November before fading away:

Some cool features I added are the ability to zoom. The D3 brush calculates the coordinates, and then, I update the scales and axes. The overflow is hidden with SVG clipping. To illustrate the usefulness of this feature, we can focus in on the September debate. Here, we see Carly Fiorina's bump in popularity due to her strong debate performance.

Another cool feature that I added was the ability to see actual data points. If one holds the Control key and hovers over the point, we can see a tooltip.

Play around with it here, and let me know what you think!

I've made two updates recently to my blog. First, io.js and Node.js finally merged, and so I've upgraded to Node v4.1.1.

Also, in order to make my blog more user-friendly, I've added tags and pages. Now 5 posts are shown per page, and you can click on tags to see posts that belong to the tag. For example, you see all my posts about

• Cooking: http://www.phillypham.com/?tag=cooking
• Math: http://www.phillypham.com/?tag=math
• Algorithms: http://www.phillypham.com/?tag=algorithm

by clicking on the corresponding link. The tags shown below each post are now clickable links, and if you scroll all the way to the bottom, you can find pagination buttons. I'm really starting to like writing tests in Mocha, too. It was pretty easy to make changes and ensure that nothing broke.

For those that are wondering how my life is going, recently, I'm wondering, too. I feel that I don't have much direction or purpose as of late.

Perhaps the funnest part about this blog was writing the commenting system. One may want to comment on a comment. I found a way to support this behaviour with recursion.

In the layout, using Jade mixins, we have something like

mixin commentView(comment)
    - var children = comments.filter(function(child) { return comment.id === child.commentId; })
    .wmd-preview!= comment.bodyHtml
    .children
        .inner-children
            each child in children
                +commentView(child)

We need the inner-children div to create the smooth transitions when you expand the sub-comments. Apparently, CSS transitions only work when the height is specified, so you can calculate the new height of children with inner-children.

We can also expand comments recursively with JavaScript:

function expandParent(node) {
    if (node.classList.contains('comments')) return; // we've reached the top level
    if (node.classList.contains('children')) {
        node.parentNode.querySelector('.reply-expander').classList.add('expanded');
        node.classList.add('expanded');
    }
    expandParent(node.parentNode);
}

I've left some sample comments below to show off the features. Apparently, transitions are only smooth in Chrome and Internet Explorer. Leave a comment below and try it out!