diff options
author | Trygve Laugstøl <trygvis@inamo.no> | 2012-07-01 04:30:29 +0200 |
---|---|---|
committer | Trygve Laugstøl <trygvis@inamo.no> | 2012-07-01 04:30:29 +0200 |
commit | f895dfeb979f1a98ea38a6a88f99ff63422d4348 (patch) | |
tree | ab3707f819cca5cf33c91bb642485c4c8cead220 | |
parent | c66f8be25f230f8fcea2774e54a56ad43fba9b58 (diff) | |
download | example-collection-json-db-f895dfeb979f1a98ea38a6a88f99ff63422d4348.tar.gz example-collection-json-db-f895dfeb979f1a98ea38a6a88f99ff63422d4348.tar.bz2 example-collection-json-db-f895dfeb979f1a98ea38a6a88f99ff63422d4348.tar.xz example-collection-json-db-f895dfeb979f1a98ea38a6a88f99ff63422d4348.zip |
o Better error handling.
-rw-r--r-- | routes/index.js | 187 | ||||
-rw-r--r-- | views/departments.jade | 2 | ||||
-rw-r--r-- | views/employees.jade | 2 | ||||
-rw-r--r-- | views/employees_in_department.jade | 2 | ||||
-rw-r--r-- | views/lib/pager.jade | 21 |
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)) << 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 >> else - | next + | Next |