From 27fdccc050bbfe09beca29d8d50ca25e18c5102a Mon Sep 17 00:00:00 2001 From: Trygve Laugstøl Date: Thu, 12 Jul 2012 22:01:54 +0200 Subject: o Moving most of the SQL code to a DAO. --- routes/index.js | 268 ++++++++++++++++++++++++++------------------------------ 1 file changed, 126 insertions(+), 142 deletions(-) (limited to 'routes/index.js') 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); -- cgit v1.2.3