o Moving most of the SQL code to a DAO.

2 files changed, 253 insertions, 142 deletions

diff --git a/lib/dao.js b/lib/dao.js
new file mode 100644
index 0000000..5ebb38e
--- /dev/null
+++ b/lib/dao.js
@@ -0,0 +1,127 @@
+var _ = require('underscore');
+
+function EmployeeDao(con) {
+  this.con = con;
+  this.getDepartments = getDepartments;
+  this.getEmployeesInDepartment = getEmployeesInDepartment;
+  this.getEmployeesByName = getEmployeesByName;
+  this.getEmployee = getEmployee;
+  this.deleteEmployee = deleteEmployee;
+}
+
+function getDepartments(pager, cb) {
+  var con = this.con;
+  var departments_count = {
+    text: 'SELECT COUNT(dept_no) AS count FROM departments',
+    values: []
+  } 
+
+  var departments = {
+    text: 'SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2',
+    values: [ pager.offset, pager.limit ]
+  } 
+
+  con.query(departments_count, function(err, rs1) {
+    if(err) return cb(err);
+    con.query(departments, function(err, rs2) {
+      if(err) return cb(err);
+      cb(undefined, rs2.rows, rs1.rows[0].count);
+    });
+  });
+}
+
+function getEmployeesInDepartment(dept_no, pager, cb) {
+  var con = this.con;
+  var where = 'dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date'
+  var employees_in_department_count = {
+    text: 'SELECT COUNT(emp_no) AS count FROM dept_emp WHERE ' + where,
+    values: [ dept_no ]
+  };
+
+  var employees_in_department = {
+    text: 'SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND ' + where + ' OFFSET $2 LIMIT $3',
+    values: [ dept_no, pager.offset, pager.limit ]
+  };
+
+  con.query(employees_in_department_count, function(err, rs1) {
+    if(err) return cb(err);
+    con.query(employees_in_department, function(err, rs2) {
+      if(err) return cb(err);
+      cb(undefined, rs2.rows, rs1.rows[0].count);
+    });
+  });
+}
+
+function getEmployeesByName(query, pager, cb) {
+  var con = this.con;
+  var where = 'WHERE UPPER(first_name) LIKE UPPER($1) OR UPPER(last_name) LIKE UPPER($1)';
+  var employees_by_name_count = {
+    text: 'SELECT count(emp_no) AS count FROM employees ' + where,
+    values: []
+  };
+  var employees_by_name = {
+    text: 'SELECT * FROM employees ' + where + ' ORDER BY emp_no OFFSET $2 LIMIT $3',
+    values: []
+  };
+
+  var employee_count = {
+    text: 'SELECT COUNT(emp_no) AS count FROM employees',
+    values: []
+  };
+  var employees = {
+    text: 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2',
+    values: []
+  };
+
+  var q1, q2;
+  if(_.isString(query)) {
+    if(query.charAt(0) == '*') {
+      query = '%' + query.slice(1);
+    }
+    if(query.charAt(query.length - 1) == '*') {
+      query = query.slice(0, -1) + '%';
+    }
+    employees_by_name_count.values.push(query);
+    employees_by_name.values.push(query);
+    q1 = employees_by_name_count;
+    q2 = employees_by_name;
+  } else {
+    q1 = employee_count;
+    q2 = employees;
+  }
+  con.query(q1, function(err, rs1) {
+    if(err) return cb(err);
+    q2.values.push(pager.offset);
+    q2.values.push(pager.limit);
+    con.query(q2, function(err, rs2) {
+      if(err) return cb(err);
+      cb(undefined, rs2.rows, rs1.rows[0].count);
+    });
+  });
+}
+
+function getEmployee(emp_no, cb) {
+  var employee_by_emp_no = {
+    text: 'SELECT * FROM employees WHERE employees.emp_no=$1',
+    values: []
+  }
+  employee_by_emp_no.values.push(emp_no);
+  this.con.query(employee_by_emp_no, function(err, rs) {
+    if(err) return cb(err);
+    cb(undefined, rs.rows[0]);
+  });
+}
+
+function deleteEmployee(emp_no, cb) {
+  var delete_employee_by_emp_no = {
+    text: 'DELETE FROM employees WHERE emp_no=$1',
+    values: []
+  }
+  delete_employee_by_emp_no.values.push(emp_no);
+  this.con.query(delete_employee_by_emp_no, function(err, rs) {
+    if(err) return cb(err);
+    cb(undefined, rs.rowCount);
+  });
+}
+
+module.exports = EmployeeDao;
diff --git a/routes/index.js b/routes/index.js
index 7c9d170..49db10c 100644
--- a/routes/index.js
+++ b/routes/index.js
@@ -1,6 +1,7 @@
 var collection_json = require('collection_json')
   , pg = require('pg').native
   , util = require('util')
+  , EmployeeDao = require('../lib/dao.js')
   , _ = require('underscore');
 
 function mapRow(row) {
@@ -26,22 +27,26 @@ function mapEmployees(res, employees) {
   });
 }
 
-function pager(req, count) {
+function pager(req) {
   var pageSize = 5;
   var offset = parseInt(req.query.offset) || 0;
   var limit = parseInt(req.query.limit) || pageSize;
   var prevOffset = offset - pageSize;
   var nextOffset = offset + pageSize;
-  return {
-    pageSize: pageSize,
-    offset: offset,
-    limit: limit,
-    page: offset / pageSize + 1,
-    pageCount: Math.round(count / pageSize),
-    count: count,
-    prevOffset: prevOffset >= 0 ? prevOffset : undefined,
-    nextOffset: nextOffset < count ? nextOffset : undefined
-  };
+  var self = {};
+  self.pageSize = pageSize;
+  self.offset = offset;
+  self.limit = limit;
+  self.withCount = function(count) {
+    self.pageCount = Math.round(count / pageSize);
+    self.count = count;
+    self.page = offset / pageSize + 1;
+    self.prevOffset = prevOffset >= 0 ? prevOffset : undefined;
+    self.nextOffset = nextOffset < count ? nextOffset : undefined;
+    return self;
+  }
+
+  return self;
 }
 
 function connect(res, cb) {
@@ -50,18 +55,19 @@ function connect(res, cb) {
       res.send('Unable to connect to PostgreSQL.', {'Content-Type': 'text/plain'}, 500);
     }
     else {
-      cb(client);
+      cb(new EmployeeDao(client));
     }
   });
 }
 
 function after(res, callback) {
-  return function(err, queryResult) {
+  return function() {
+    var err = arguments[0];
     if(err) {
       res.writeHead(500, {'Content-Type' : 'text/plain'});
       return res.end('Error! ' + util.inspect(err))
     }
-    callback(queryResult)
+    callback.apply(this, _.rest(arguments));
   }
 }
 
@@ -167,33 +173,30 @@ exports.index = method({
 });
 
 function getDepartments(req, res) {
-  connect(res, function(client) {
-    var sql = 'SELECT COUNT(dept_no) AS count FROM departments';
-    var sql2 = 'SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2';
-    client.query(sql, after(res, function(rs) {
-      var p = pager(req, parseInt(rs.rows[0].count));
-      client.query(sql2, [ p.offset, p.limit ], after(res, function(rs2) {
-        switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
-        case 'text/html':
-            send_html(req, res, _.bind(res.render, res, 'departments', {
-            title: 'Department List',
-            urlgenerator: res.urlgenerator, pager: p, query: req.query,
-            departments: rs2.rows
-          }));
-          break;
-        case 'application/vnd.collection+json':
-        case '*/*':
-          var c = {collection: {
-            href: res.urlgenerator.departments(),
-            items: mapDepartments(res, rs2.rows),
-            links: linksFromPager(req, p, res.urlgenerator.departments),
-          }};
-          send_cj(200, req, res, c);
-          break;
-        default:
-          res.send(406);
-        }
-      }));
+  connect(res, function(dao) {
+    var p = pager(req);
+    dao.getDepartments(p, after(res, function(departments, count) {
+      p.withCount(count);
+      switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
+      case 'text/html':
+          send_html(req, res, _.bind(res.render, res, 'departments', {
+          title: 'Department List',
+          urlgenerator: res.urlgenerator, pager: p, query: req.query,
+          departments: departments
+        }));
+        break;
+      case 'application/vnd.collection+json':
+      case '*/*':
+        var c = {collection: {
+          href: res.urlgenerator.departments(),
+          items: mapDepartments(res, departments),
+          links: linksFromPager(req, p, res.urlgenerator.departments),
+        }};
+        send_cj(200, req, res, c);
+        break;
+      default:
+        res.send(406);
+      }
     }));
   });
 };
@@ -238,42 +241,38 @@ exports.department = method({
 });
 
 function getEmployeesInDepartment(req, res) {
-  connect(res, function(client) {
+  connect(res, function(dao) {
     var dept_no = req.params.dept_no;
     // TODO: Add dept_name to view
     // TODO: Add manager as a link
-    var where = 'dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date'
-    var sql1 = 'SELECT count(emp_no) as count FROM dept_emp WHERE ' + where;
-    var sql2 = 'SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND ' + where + ' OFFSET $2 LIMIT $3';
-    client.query(sql1, [ dept_no ], after(res, function(rs1) {
-      var p = pager(req, parseInt(rs1.rows[0].count));
-      client.query(sql2, [ dept_no, p.offset, p.limit ], after(res, function(rs2) {
-        switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
-        case 'text/html':
-          send_html(req, res, _.bind(res.render, res, 'employeesInDepartment', {
-            title: 'Department: #' + dept_no,
-            urlgenerator: res.urlgenerator, pager: p, query: req.query,
-            dept_no: dept_no,
-            employees: rs2.rows
-          }));
-          break;
-        case 'application/vnd.collection+json':
-        case '*/*':
-          var c = {collection: {
-            href: res.urlgenerator.employeesInDepartment(dept_no),
-            links: [ {
-              rel: 'department',
-              prompt: 'Department: #' + dept_no,
-              href: res.urlgenerator.department(dept_no)
-            } ],
-            items: mapEmployees(res, rs2.rows)
-          }};
-          send_cj(200, req, res, c);
-          break;
-        default:
-          res.send(406);
-        }
-      }));
+    var p = pager(req);
+    dao.getEmployeesInDepartment(dept_no, p, after(res, function(employees, count) {
+      p.withCount(count);
+      switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
+      case 'text/html':
+        send_html(req, res, _.bind(res.render, res, 'employeesInDepartment', {
+          title: 'Department: #' + dept_no,
+          urlgenerator: res.urlgenerator, pager: p, query: req.query,
+          dept_no: dept_no,
+          employees: employees
+        }));
+        break;
+      case 'application/vnd.collection+json':
+      case '*/*':
+        var c = {collection: {
+          href: res.urlgenerator.employeesInDepartment(dept_no),
+          links: _.flatten([{
+            rel: 'department',
+            prompt: 'Department: #' + dept_no,
+            href: res.urlgenerator.department(dept_no)
+          }, linksFromPager(req, p, res.urlgenerator.employees)]),
+          items: mapEmployees(res, employees)
+        }};
+        send_cj(200, req, res, c);
+        break;
+      default:
+        res.send(406);
+      }
     }));
   });
 };
@@ -283,65 +282,51 @@ exports.employeesInDepartment = method({
 });
 
 function getEmployees(req, res) {
-  connect(res, function(client) {
-    var emp_no = req.params.emp_no;
-    var sql1, sql2;
-    var sql1Params = [];
-    var sql2Params = [];
+  connect(res, function(dao) {
+    // TODO: Support query by emp_no. var emp_no = req.params.emp_no;
     var qname = req.query.name;
-    if(_.isString(qname)) {
-      if(qname.charAt(0) == '*') {
-        qname = '%' + qname.slice(1);
-      }
-      if(qname.charAt(qname.length - 1) == '*') {
-        qname = qname.slice(0, -1) + '%';
+    var p = pager(req);
+    dao.getEmployeesByName(qname, p, after(res, function(employees, count) {
+      p.withCount(count);
+      switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
+      case 'text/html':
+        send_html(req, res, _.bind(res.render, res, 'employees', {
+          title: 'Employee List',
+          urlgenerator: res.urlgenerator, pager: p, query: req.query,
+          employees: employees,
+          query: req.query
+        }));
+        break;
+      case 'application/vnd.collection+json':
+      case '*/*':
+        var c = {collection: {
+          href: res.urlgenerator.employees(req.query),
+          links: linksFromPager(req, p, res.urlgenerator.employees),
+          queries: [{
+            href: res.urlgenerator.employees(),
+            rel: 'search',
+            name: 'employee-search',
+            prompt: 'Employee search',
+            data: [
+              { name: 'name', prompt: 'Name' }
+            ]
+          }],
+          template: {
+            data: [
+              { name: 'birth_date', prompt: 'Birth Date' },
+              { name: 'first_name', prompt: 'First Name' },
+              { name: 'last_name', prompt: 'Last Name' },
+              { name: 'gender', prompt: 'Gender' },
+              { name: 'hire_date', prompt: 'Hire Date. Defaults to today.' }
+            ]
+          },
+          items: mapEmployees(res, employees)
+        }};
+        send_cj(200, req, res, c);
+        break;
+      default:
+        res.send(406);
       }
-      var where = 'WHERE UPPER(first_name) LIKE UPPER($1) OR UPPER(last_name) LIKE UPPER($1)';
-      sql1 = 'SELECT count(emp_no) AS count FROM employees ' + where;
-      sql1Params.push(qname);
-      sql2 = 'SELECT * FROM employees ' + where + ' OFFSET $2 LIMIT $3';
-      sql2Params.push(qname);
-    }
-    else {
-      sql1 = 'SELECT n_live_tup AS count FROM pg_stat_user_tables WHERE relname=\'employees\'';
-      sql2 = 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2';
-    }
-    client.query(sql1, sql1Params, after(res, function(rs1) {
-      var p = pager(req, parseInt(rs1.rows[0].count));
-      sql2Params.push(p.offset);
-      sql2Params.push(p.limit);
-      client.query(sql2, sql2Params, after(res, function(rs2) {
-        switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
-        case 'text/html':
-          send_html(req, res, _.bind(res.render, res, 'employees', {
-            title: 'Employee List',
-            urlgenerator: res.urlgenerator, pager: p, query: req.query,
-            employees: rs2.rows,
-            query: req.query
-          }));
-          break;
-        case 'application/vnd.collection+json':
-        case '*/*':
-          var c = {collection: {
-            href: res.urlgenerator.employees(req.query),
-            links: linksFromPager(req, p, res.urlgenerator.employees),
-            queries: [{
-              href: res.urlgenerator.employees(),
-              rel: 'search',
-              name: 'employee-search',
-              prompt: 'Employee search',
-              data: [
-                { name: 'name' }
-              ]
-            }],
-            items: mapEmployees(res, rs2.rows)
-          }};
-          send_cj(200, req, res, c);
-          break;
-        default:
-          res.send(406);
-        }
-      }));
     }));
   });
 };
@@ -351,17 +336,16 @@ exports.employees = method({
 });
 
 function getEmployee(req, res) {
-  connect(res, function(client) {
+  connect(res, function(dao) {
     var emp_no = req.params.emp_no;
-    var sql = 'SELECT * FROM employees WHERE employees.emp_no=$1';
-    client.query(sql, [ emp_no ], after(res, function(rs) {
+    dao.getEmployee(emp_no, after(res, function(employee) {
       switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json', '*/*'])) {
       case 'text/html':
-        if(rs.rowCount > 0) {
+        if(typeof employee == 'object') {
           send_html(req, res, _.bind(res.render, res, 'employee', {
             title: 'Employee: #' + emp_no,
             urlgenerator: res.urlgenerator,
-            employee: rs.rows[0]
+            employee: employee
           }));
         }
         else {
@@ -371,10 +355,10 @@ function getEmployee(req, res) {
       case 'application/vnd.collection+json':
       case '*/*':
         var c;
-        if(rs.rowCount > 0) {
+        if(typeof employee == 'object') {
           c = {collection: {
             href: res.urlgenerator.employee(emp_no),
-            items: mapEmployees(res, rs.rows)
+            items: mapEmployees(res, [ employee ])
           }};
         }
         else {
@@ -384,7 +368,7 @@ function getEmployee(req, res) {
             }
           }};
         }
-        send_cj(rs.rowCount > 0 ? 200 : 404, req, res, c);
+        send_cj(typeof employee == 'object' ? 200 : 404, req, res, c);
         break;
       default:
         res.send(406);
@@ -394,13 +378,13 @@ function getEmployee(req, res) {
 }
 
 function deleteEmployee(req, res) {
-  connect(res, function(client) {
+  connect(res, function(dao) {
     var emp_no = parseInt(req.params.emp_no) || 0;
     if(emp_no < 10000 || emp_no > 999999) {
       return send_text(res, 404, 'Illegal emp_no.');
     }
-    client.query('DELETE FROM employees WHERE emp_no=$1', [ emp_no ], after(res, function(rs) {
-      if(rs.rowCount == 0) {
+    dao.deleteEmployee(emp_no, after(res, function(rowCount) {
+      if(rowCount == 0) {
         return send_text(res, 404, 'No employee with emp_no=' + emp_no + '.');
       }
       res.send(204);