Reddit MySQL API

reddit.js

var bcrypt = require('bcrypt');
var HASH_ROUNDS = 10;

module.exports = function RedditAPI(conn) {
  return {
    createUser: function createUser(user, callback) {

      // first we have to hash the password...
      bcrypt.hash(user.password, HASH_ROUNDS, function(err, hashedPassword) {
        if (err) {
          callback(err);
        }
        else {
          conn.query(
            'INSERT INTO users (username,password, createdAt) VALUES (?, ?, ?)', [user.username, hashedPassword, new Date()],
            function(err, result) {
              if (err) {
                /*
                There can be many reasons why a MySQL query could fail. While many of
                them are unknown, there's a particular error about unique usernames
                which we can be more explicit about!
                */
                if (err.code === 'ER_DUP_ENTRY') {
                  callback(new Error('A user with this username already exists'));
                }
                else {
                  callback(err);
                }
              }
              else {
                /*
                Here we are INSERTing data, so the only useful thing we get back
                is the ID of the newly inserted row. Let's use it to find the user
                and return it
                */
                conn.query(
                  'SELECT id, username, createdAt, updatedAt FROM users WHERE id = ?', [result.insertId],
                  function(err, result) {
                    if (err) {
                      callback(err);
                    }
                    else {
                      /*
                      Finally! Here's what we did so far:
                      1. Hash the user's password
                      2. Insert the user in the DB
                      3a. If the insert fails, report the error to the caller
                      3b. If the insert succeeds, re-fetch the user from the DB
                      4. If the re-fetch succeeds, return the object to the caller
                      */
                      callback(null, result[0]);
                    }
                  }
                );
              }
            }
          );
        }
      });
    },
    createPost: function(post, subredditId, callback) {
      conn.query(
        'INSERT INTO posts (userId, title, url, subredditId, createdAt) VALUES (?, ?, ?, ?, ?)', [post.userId, post.title, post.url, subredditId, new Date()],
        function(err, result) {
          if (err) {
            callback(err);
          }
          else {
            /*
            Post inserted successfully. Let's use the result.insertId to retrieve
            the post and send it to the caller!
            */
            conn.query(
              'SELECT id,title,url,userId, subredditId, createdAt, updatedAt FROM posts WHERE id = ?', [result.insertId],
              function(err, result) {
                if (err) {
                  callback(err);
                }
                else {
                  callback(null, result[0]);
                }
              }
            );
          }
        }
      );
    },
    createSubreddit: function(name, callback) {
      conn.query(
        'INSERT INTO subreddits (name, createdAt) VALUES (?, ?)', [name, new Date()],
        function(err, result) {
          if (err) {
            callback(err);
          }
          else {
            /*
            Subreddit inserted successfully. Let's use the result.insertId to retrieve
            the subreddit and send it to the caller!
            */
            conn.query(
              'SELECT id, name, createdAt, updatedAt FROM subreddits WHERE id = ?', [result.insertId],
              function(err, result) {
                if (err) {
                  callback(err);
                }
                else {
                  callback(null, result[0]);
                }
              }
            );
          }
        }
      );
    },
    createComment: function(comment, callback) {
      if (!comment.userId || !comment.postId) {
        callback(null, new Error('userId and postId required'));
        return;
      }

      if (!comment.parentId) {
        comment.parentId = null;
      }

      var date = new Date();
      conn.query(`
        INSERT INTO comments 
          (text, createdAt, updatedAt, userId, postId, parentId)
        VALUES
          (?, ?, ?, ?, ?, ?)
        `, [comment.text, date, date, comment.userId, comment.postId, comment.parentId],
        function(err, result) {
          if (err) {
            callback(err);
          }
          else {
            conn.query(`
              SELECT
                id, text, createdAt, updatedAt, userId, postId, parentId
              FROM comments
              WHERE id = ?
              `, [result.insertId],
              function(err, result) {
                if (err) {
                  callback(err);
                }
                else {
                  callback(null, result[0]);
                }
              }
            )
          }
        }
      );
    },
    getAllSubreddits: function(callback) {
      var query = `SELECT id, name, createdAt, updatedAt FROM subreddits ORDER BY createdAt DESC`;
      conn.query(query, function(err, subs) {
        callback(err, subs);
      });
    },
    getAllPosts: function(options, callback) {
      // In case we are called without an options parameter, shift all the parameters manually
      if (!callback) {
        callback = options;
        options = {};
      }
      var limit = options.numPerPage || 25; // if options.numPerPage is falsy then use 25
      var offset = (options.page || 0) * limit;
      var sorting = options.sorting || 'hotness';

      conn.query(`
        SELECT
          p.id, p.title, p.url, p.createdAt, p.updatedAt,
          
          u.id AS u_id,
          u.username as u_username,
          u.createdAt as u_createdAt,
          u.updatedAt as u_updatedAt,
          
          s.id as s_id,
          s.name as s_name,
          s.createdAt as s_createdAt,
          s.updatedAt as s_updatedAt,
          
          SUM(IF(vote = 1, 1, 0)) as numUpvotes,
          SUM(IF(vote = -1, 1, 0)) as numDownvotes,
          SUM(IF(vote != 0, 1, 0))  as totalVotes,
          SUM(vote) as voteScore,
          SUM(vote) / (NOW() - p.createdAt) as hotness
          
        FROM posts p
          LEFT JOIN users u ON p.userId = u.id
          LEFT JOIN subreddits s on p.subredditId = s.id
          LEFT JOIN votes v ON v.postId = p.id
        GROUP BY p.id
        ORDER BY ?? DESC
        LIMIT ? OFFSET ?`, [sorting, limit, offset],
        function(err, posts) {
          if (err) {
            callback(err);
          }
          else {
            posts = posts.map(function(post) {
              return {
                id: post.id,
                title: post.title,
                url: post.url,
                voteScore: post.voteScore,
                createdAt: post.createdAt,
                updatedAt: post.updatedAt,
                user: {
                  id: post.u_id,
                  username: post.u_username,
                  createdAt: post.u_createdAt,
                  updatedAt: post.u_updatedAt
                },
                subreddit: {
                  id: post.s_id,
                  name: post.s_name,
                  createdAt: post.s_createdAt,
                  updatedAt: post.s_updatedAt
                }
              };
            });

            callback(null, posts);
          }
        }
      );
    },
    getSinglePost: function(postId, callback) {

      conn.query(`
        SELECT
          p.id, p.title, p.url, p.createdAt, p.updatedAt,
          
          u.id AS u_id,
          u.username as u_username,
          u.createdAt as u_createdAt,
          u.updatedAt as u_updatedAt,
          
          s.id as s_id,
          s.name as s_name,
          s.createdAt as s_createdAt,
          s.updatedAt as s_updatedAt
          
        FROM posts p
          JOIN users u ON p.userId = u.id
          JOIN subreddits s on p.subredditId = s.id
        ORDER BY createdAt DESC`,
        function(err, post) {
          if (err) {
            callback(err);
          }
          else {
            post = post[0];
            if (!post) {
              callback(null, undefined);
            }
            else {
              post = {
                id: post.id,
                title: post.title,
                url: post.url,
                createdAt: post.createdAt,
                updatedAt: post.updatedAt,
                user: {
                  id: post.u_id,
                  username: post.u_username,
                  createdAt: post.u_createdAt,
                  updatedAt: post.u_updatedAt
                },
                subreddit: {
                  id: post.s_id,
                  name: post.s_name,
                  createdAt: post.s_createdAt,
                  updatedAt: post.s_updatedAt
                }
              };

              callback(null, post);
            }
          }
        }
      );
    },
    getCommentsForPost: function(postId, callback) {

      conn.query(
        `
    SELECT
      c1.id as c1_id, c1.text as c1_text, c1.parentId as c1_parentId,
      c2.id as c2_id, c2.text as c2_text, c2.parentId as c2_parentId,
      c3.id as c3_id, c3.text as c3_text, c3.parentId as c3_parentId
    
    FROM comments c1
      LEFT JOIN comments c2 ON c1.id = c2.parentId
      LEFT JOIN comments c3 ON c2.id = c3.parentId
    
    WHERE c1.postId = ? AND c1.parentId IS NULL;
    `, [postId],
        function(err, comments) {

          var finalComments = [];

          var commentsIndex = {};

          comments.forEach(function(commentGroup) {
            var comment1;
            if (commentsIndex[commentGroup.c1_id]) {
              comment1 = commentsIndex[commentGroup.c1_id];
            }
            else {
              comment1 = {
                id: commentGroup.c1_id,
                text: commentGroup.c1_text,
                parentId: commentGroup.c1_parentId,
                replies: []
              };

              // put the comment in the index by its id
              commentsIndex[commentGroup.c1_id] = comment1;

              // put it in the final result array
              finalComments.push(comment1);
            }

            if (commentGroup.c2_id === null) {
              return;
            }

            var comment2;
            if (commentsIndex[commentGroup.c2_id]) {
              comment2 = commentsIndex[commentGroup.c2_id];
            }
            else {
              comment2 = {
                id: commentGroup.c2_id,
                text: commentGroup.c2_text,
                parentId: commentGroup.c2_parentId,
                replies: []
              }

              // put the comment in the index by its id
              commentsIndex[commentGroup.c2_id] = comment2;

              // put it in the replies of its parent
              comment1.replies.push(comment2);
            }

            // comment3 will always be new! why?
            if (commentGroup.c3_id !== null) {
              comment2.replies.push({
                id: commentGroup.c3_id,
                text: commentGroup.c3_text,
                parentId: commentGroup.c3_parentId
              });
            }

          });

          callback(null, finalComments);

        });
    },
    createOrUpdateVote: function(vote, callback) {
      // make sure vote is +1, 0 or -1
      if (Math.abs(vote.vote) > 1) {
        callback(new Error('vote has to be +1, 0 or -1'));
        return;
      }
      
      conn.query(
        'INSERT INTO votes (userId, postId, vote) VALUES (?, ?, ?) ON UPDATE SET vote=?', [vote.userId, vote.postId, vote.vote, vote.vote],
        function(err, result) {
          if (err) {
            callback(err);
          }
          else {
            callback(null, vote);
          }
        }
      );
    }
  }
};