aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorTrygve Laugstøl <trygvis@inamo.no>2012-07-12 22:01:54 +0200
committerTrygve Laugstøl <trygvis@inamo.no>2012-07-12 22:01:54 +0200
commit27fdccc050bbfe09beca29d8d50ca25e18c5102a (patch)
treedc4948e3e4edb40d725a2a3595c6f9d01a917a5c
parentffa799fa0fa762f74ac04b46b670ab981c715fbf (diff)
downloadexample-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.tar.gz
example-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.tar.bz2
example-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.tar.xz
example-collection-json-db-27fdccc050bbfe09beca29d8d50ca25e18c5102a.zip
o Moving most of the SQL code to a DAO.
-rw-r--r--lib/dao.js127
-rw-r--r--routes/index.js268
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);