o Better error handling.

5 files changed, 111 insertions, 103 deletions

diff --git a/routes/index.js b/routes/index.js
index dcb6e23..c3cef8c 100644
--- a/routes/index.js
+++ b/routes/index.js
@@ -1,5 +1,6 @@
 var collection_json = require('collection_json')
   , pg = require('pg').native
+  , util = require('util')
   , _ = require('underscore');
 
 function mapRow(row) {
@@ -25,22 +26,32 @@ function mapEmployees(res, employees) {
   });
 }
 
-function pager(req) {
-  var pageSize = 10;
+function pager(req, count) {
+  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,
-    prevOffset: prevOffset >= 0 ? prevOffset : 0,
-    nextOffset: function(length) {
-      return length > 0 ? nextOffset : undefined;
-    }
+    count: count,
+    prevOffset: prevOffset >= 0 ? prevOffset : undefined,
+    nextOffset: nextOffset < count ? nextOffset : undefined
   };
 }
 
+function after(res, callback) {
+  return function(err, queryResult) {
+    if(err) {
+      res.writeHead(500, {"Content-Type" : "text/plain"});
+      return res.end("Error! " + util.inspect(err))
+    }
+    callback(queryResult)
+  }
+}
+
 exports.index = function(req, res) {
   if(req.accepts('html')) {
     res.render('index', {
@@ -68,29 +79,28 @@ exports.index = function(req, res) {
 exports.departments = function(req, res) {
   pg.connect(process.env.DATABASE_URL, function(err, client) {
     if(err) throw err;
-    var p = pager(req);
-    var departments = [];
-    client.query('SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2', [ p.offset, p.limit ] ).
-    on('row', function(row) {
-      departments.push(row);
-    }).
-    on('end', function() {
-      if(req.accepts('html')) {
-        res.render('departments', {
-          title: 'Department List',
-          urlgenerator: res.urlgenerator, pager: p,
-          departments: departments
-        });
-      }
-      else {
-        var c = {collection: {
-          href: res.urlgenerator.departments(),
-          items: mapDepartments(res, departments)
-        }};
-        res.contentType('application/vnd.collection+json');
-        res.send(JSON.stringify(collection_json.fromObject(c)), 200);
-      }
-    });
+    var sql = 'SELECT n_live_tup FROM pg_stat_user_tables WHERE relname=\'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].n_live_tup));
+      client.query(sql2, [ p.offset, p.limit ], after(res, function(rs2) {
+        if(req.accepts('html')) {
+          res.render('departments', {
+            title: 'Department List',
+            urlgenerator: res.urlgenerator, pager: p,
+            departments: rs2.rows
+          });
+        }
+        else {
+          var c = {collection: {
+            href: res.urlgenerator.departments(),
+            items: mapDepartments(res, departments.rows)
+          }};
+          res.contentType('application/vnd.collection+json');
+          res.send(JSON.stringify(collection_json.fromObject(c)), 200);
+        }
+      }));
+    }));
   });
 };
 
@@ -125,67 +135,66 @@ exports.employees_in_department = function(req, res) {
   pg.connect(process.env.DATABASE_URL, function(err, client) {
     if(err) throw err;
     var dept_no = req.params.dept_no;
-    var p = pager(req);
-    var employees = [];
     // TODO: Add dept_name to view
     // TODO: Add manager as a link
-    client.query('SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date OFFSET $2 LIMIT $3', [ dept_no, p.offset, p.limit ]).
-    on('row', function(row) {
-      employees.push(row);
-    }).
-    on('end', function() {
-      if(req.accepts('html')) {
-        res.render('employees_in_department', {
-          title: 'Department: #' + dept_no,
-          urlgenerator: res.urlgenerator, pager: p,
-          dept_no: dept_no,
-          employees: employees
-        });
-      }
-      else {
-        var c = {collection: {
-          href: res.urlgenerator.employees_in_department(dept_no),
-          links: [ {
-            rel: 'department',
-            prompt: 'Department: #' + dept_no,
-            href: res.urlgenerator.department(dept_no)
-          } ],
-          items: mapEmployees(res, employees)
-        }};
-        res.contentType('application/vnd.collection+json');
-        res.send(collection_json.fromObject(c));
-      }
-    });
+    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) {
+        if(req.accepts('html')) {
+          res.render('employees_in_department', {
+            title: 'Department: #' + dept_no,
+            urlgenerator: res.urlgenerator, pager: p,
+            dept_no: dept_no,
+            employees: rs2.rows
+          });
+        }
+        else {
+          var c = {collection: {
+            href: res.urlgenerator.employees_in_department(dept_no),
+            links: [ {
+              rel: 'department',
+              prompt: 'Department: #' + dept_no,
+              href: res.urlgenerator.department(dept_no)
+            } ],
+            items: mapEmployees(res, rs2.rows)
+          }};
+          res.contentType('application/vnd.collection+json');
+          res.send(collection_json.fromObject(c));
+        }
+      }));
+    }));
   });
 };
 
 exports.employees = function(req, res) {
   pg.connect(process.env.DATABASE_URL, function(err, client) {
     if(err) throw err;
-    var p = pager(req);
     var emp_no = req.params.emp_no;
-    var employees = [];
-    client.query('SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2', [ p.offset, p.limit ]).
-    on('row', function(row) {
-      employees.push(row);
-    }).
-    on('end', function() {
-      if(req.accepts('html')) {
-        res.render('employees', {
-          title: 'Employee List',
-          urlgenerator: res.urlgenerator, pager: p,
-          employees: employees
-        });
-      }
-      else {
-        var c = {collection: {
-          href: res.urlgenerator.employees(),
-          items: mapEmployees(res, employees)
-        }};
-        res.contentType('application/vnd.collection+json');
-        res.send(JSON.stringify(collection_json.fromObject(c)), 200);
-      }
-    });
+    var sql1 = 'SELECT n_live_tup FROM pg_stat_user_tables WHERE relname=\'employees\'';
+    var sql2 = 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2';
+    client.query(sql1, after(res, function(rs1) {
+      var p = pager(req, parseInt(rs1.rows[0].n_live_tup));
+      client.query(sql2, [ p.offset, p.limit ], after(res, function(rs2) {
+        if(req.accepts('html')) {
+          res.render('employees', {
+            title: 'Employee List',
+            urlgenerator: res.urlgenerator, pager: p,
+            employees: rs2.rows
+          });
+        }
+        else {
+          var c = {collection: {
+            href: res.urlgenerator.employees(),
+            items: mapEmployees(res, rs2.rows)
+          }};
+          res.contentType('application/vnd.collection+json');
+          res.send(JSON.stringify(collection_json.fromObject(c)), 200);
+        }
+      }));
+    }));
   });
 };
 
@@ -193,27 +202,23 @@ exports.employee = function(req, res) {
   pg.connect(process.env.DATABASE_URL, function(err, client) {
     if(err) throw err;
     var emp_no = req.params.emp_no;
-    var employee;
-    client.query('SELECT * FROM employees WHERE employees.emp_no=$1', [ emp_no ]).
-    on('row', function(row) {
-      employee = row;
-    }).
-    on('end', function() {
+    var sql = 'SELECT * FROM employees WHERE employees.emp_no=$1';
+    client.query(sql, [ emp_no ], after(res, function(rs) {
       if(req.accepts('html')) {
         res.render('employee', {
           title: 'Employee: #' + emp_no,
           urlgenerator: res.urlgenerator,
-          employee: employee
+          employee: rs.rows[0]
         });
       }
       else {
         var c = {collection: {
           href: res.urlgenerator.employee(emp_no),
-          items: [ mapRow(employee) ],
+          items: [ mapRow(rs.rows[0]) ],
         }};
         res.contentType('application/vnd.collection+json');
         res.send(JSON.stringify(collection_json.fromObject(c)), 200);
       }
-    });
+    }))
   });
-};
+}
diff --git a/views/departments.jade b/views/departments.jade
index bdcb0ae..14d4a31 100644
--- a/views/departments.jade
+++ b/views/departments.jade
@@ -12,4 +12,4 @@ block content
       tr
         td: a(href=urlgenerator.department(department.dept_no)) #{department.dept_name}
 
-  mixin pager(pager, departments.length, urlgenerator.departments)
+  mixin pager(pager, urlgenerator.departments)
diff --git a/views/employees.jade b/views/employees.jade
index d7d9005..cea80ed 100644
--- a/views/employees.jade
+++ b/views/employees.jade
@@ -12,4 +12,4 @@ block content
       tr
         td: a(href=urlgenerator.employee(employee.emp_no)) #{employee.first_name} #{employee.last_name}
 
-  mixin pager(pager, employees.length, urlgenerator.employees)
+  mixin pager(pager, urlgenerator.employees)
diff --git a/views/employees_in_department.jade b/views/employees_in_department.jade
index c341647..8313b2c 100644
--- a/views/employees_in_department.jade
+++ b/views/employees_in_department.jade
@@ -18,4 +18,4 @@ block content
       tr
         td: a(href=urlgenerator.employee(employee.emp_no)) #{employee.first_name} #{employee.last_name}
 
-  mixin pager(pager, employees.length, function(offset) { return urlgenerator.employees_in_department(dept_no, offset) })
+  mixin pager(pager, function(offset) { return urlgenerator.employees_in_department(dept_no, offset) })
diff --git a/views/lib/pager.jade b/views/lib/pager.jade
index a762735..1a47e12 100644
--- a/views/lib/pager.jade
+++ b/views/lib/pager.jade
@@ -1,14 +1,17 @@
-mixin pager(pager, length, f)
+mixin pager(pager, f)
   p
-    if pager.offset > 0
-      a(href=f(pager.prevOffset)) prev
-      |  
+    if typeof pager.prevOffset == 'number'
+      a(href=f(pager.prevOffset)) &lt;&lt; Previous
     else
-      | prev 
+      | Previous
 
-    - var nextOffset = pager.nextOffset(length)
-    if nextOffset > 0
-      a(href=f(nextOffset)) next
+    |  
+    | #{pager.offset / pager.pageSize + 1} /
+    | #{Math.round(pager.count / pager.pageSize)}
+    |  
+
+    if typeof pager.nextOffset == 'number'
+      a(href=f(pager.nextOffset)) Next &gt;&gt;
     else
-      | next
+      | Next