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. --- lib/dao.js | 127 +++++++++++++++++++++++++++ routes/index.js | 268 ++++++++++++++++++++++++++------------------------------ 2 files changed, 253 insertions(+), 142 deletions(-) create mode 100644 lib/dao.js 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); -- cgit v1.2.3