shiritori database (last character of word == first character of next word)

gistfile1.js

var pg = require('pg');
var async = require('async');


exports.DB_ERROR = 1;
exports.SAME_USER_CONTINUE = 2;
exports.WORD_NOT_CONTINUE = 3;
exports.RECORD_NOT_FOUND = 4;
exports.INVALID_URL_PARAM = 5;

function word_database(client, settings) {
	var self = this;
	self.client = client;
	self.client.connect();
	self.settings = settings;
}

var insert_into_entry = function(self, respond_cb, parent_msgid, word_id, user_id, stats) {
	//console.log("params:" + parent_msgid + "," + word_id + "," + user_id);
	self.client.query("INSERT INTO " + self.settings.entry_db_name + 
		" (prev_id, word_id, user_id, depth) " + 
		"VALUES(" + parent_msgid + "," + word_id + "," + user_id + "," + stats.depth + ");", 
		function (err, result) {
			if (err) {
				respond_cb({msg_id: null, error:{text:err.stack, code:exports.DB_ERROR}});
			}
			else {
				var qq = self.client.query("SELECT LASTVAL();");
				qq.on("row", function(row) {
					respond_cb({msg_id: row.lastval, stats:stats});
				});				
			}
		}
	);
}

var validate_word = function(self, respond_cb, word, parent_msgid, user_id, on_success) {
	if (!parent_msgid) {
		on_success(null);
		return;
	}
	var q = self.client.query("SELECT * FROM " + self.settings.entry_db_name + " WHERE id=" + parent_msgid + ";");
	var selected = null;
	q.on('row', function (row) {	
		selected = row;
	});
	q.on('end', function () {
		if (selected) {
			if (selected.user_id == user_id) {
				respond_cb({msg_id:null, error:{text:"cannot continue of same user's post",code:exports.SAME_USER_CONTINUE}});
				return;
			}
			var wq = self.client.query("SELECT * FROM " + self.settings.word_db_name + " WHERE id='" + selected.word_id + "';");
			var word_selected = null;
			wq.on('row', function (row) {
				word_selected = row;
			});
			wq.on('end', function () {
				console.log(JSON.stringify(word_selected));
				if (word_selected.word[word_selected.word.length - 1] != word[0]) {
					respond_cb({msg_id:null, 
						error:{text:'word is not continue:' + word_selected.word + ' and ' + word, code:exports.WORD_NOT_CONTINUE}});
					return;
				}
				on_success(selected);
			});
		}
		else {
			respond_cb({msg_id:null, 
				error:{text:'invalid id(' + parent_msgid + ') not found in database', code:exports.RECORD_NOT_FOUND}});
		}
	});
}

/* 
	register shiritori word into database
	args:	word		string:		word to insert db
			fb_user_id	int:		facebook user id
			msg_id		int:		which shiritori entry that arg 'word' try to continue?
			respond_cb	function:	both success and error, JSON is returned.
									format:
										{msg_id:integer(success) or nil(error),
										 error:{text:{error explanation}, code:{error code (integer)} (if error)
										 stats:{depth:(node depth ( == how many times continue - 1)),
										 		word:(registered word),
										 		word_count:(how many times appeard)}

 */
word_database.prototype.register_word = function(word, fb_user_id, msg_id, respond_cb) {
	var self = this;
	if (!word || !fb_user_id) {
		respond_cb({msg_id:null, error:{text:'required parameter not specified',code:exports.INVALID_URL_PARAM}});
		return;
	}
	if (!msg_id) {
		msg_id = 0;
	}
	console.log("params: " + word + "," + fb_user_id + "," + msg_id);
	
	validate_word(self, respond_cb, word, msg_id, fb_user_id, function (prev_entry) { 
		var q = self.client.query("SELECT * FROM " + self.settings.word_db_name + " WHERE word='" + word + "';");
		var selected = null;
		q.on('row', function(row) {
			selected = row;
		});
		q.on('end', function() {
			if (selected) {
				self.client.query("UPDATE " + self.settings.word_db_name + " SET count = count + 1 WHERE id = " + selected.id);
				insert_into_entry(self, respond_cb, msg_id, selected.id, fb_user_id, 
					{depth:(prev_entry ? (prev_entry.depth + 1) : 0), word_count:selected.count + 1, word:word});
			}
			else {
				self.client.query("INSERT INTO " + self.settings.word_db_name + "(word,count) VALUES('" + word + "', 1);");
				var qq = self.client.query("SELECT LASTVAL();");
				qq.on('row', function(row) {
					insert_into_entry(self, respond_cb, msg_id, row.lastval, fb_user_id, 
						{depth:(prev_entry ? (prev_entry.depth + 1) : 0), word_count:1, word:word});
				});
			}
		});
	});
}

/* 
	get data of specified shiritori entry
	args:	msg_id:		integer:	id of entry which you want to retrieve its content.
			respond_cb 	function(JSON):	callback function
						format:	{
									id:(id of entry, which should be equals to msg_id)
									prev_id:(parent entry id)
									word_id:(primary key of words table)
									user_id:(facebook user_id)
									wdata:{
										id:(primary key of words table, which should be equals to word_id)
										word:(actual word value)
										count:(appeared count of entire shiritori tree)
									}
								}

*/
word_database.prototype.get_entry = function (msg_id, respond_cb) {
	var self = this;
	var q = self.client.query("SELECT * FROM " + self.settings.entry_db_name + " WHERE id=" + msg_id + ";");
	var selected = null;
	q.on('row', function (row) {	
		selected = row;
	});
	q.on('end', function () {
		if (selected) {
			var wq = self.client.query("SELECT * FROM " + self.settings.word_db_name + " WHERE id=" + selected.word_id + ";");
			wq.on('row', function (row) {	
				selected.wdata = row;
			});
			wq.on('end', function () {
				respond_cb(selected);
			});
		}
		else {
			respond_cb(null);
		}
	});
}


function get_history_helper(self, msgid, context, respond_cb) { 
	var selected = null;
	var q = self.client.query("SELECT * FROM " + self.settings.entry_db_name + " WHERE id=" + msgid + ";");
	q.on('row', function (row) {
		selected = row;
	});
	q.on('end', function () {
		if (selected) {
			var wq = self.client.query("SELECT * FROM " + self.settings.word_db_name + " WHERE id=" + selected.word_id + ";");
			wq.on('row', function (row) {	
				selected.wdata = row;
			});
			wq.on('end', function () {
				context.histories.push(selected);
				context.remain = (context.remain - 1);
				if (context.remain <= 0 || selected.prev_id == 0) {
					respond_cb(context.histories);
				}
				else {
					get_history_helper(self, selected.prev_id, context, respond_cb);
				}
			});			
		}
		else {
			respond_cb(null);
		}
	});
}

/* 
	get shiritori history of anccester of specified entry
	args:	start_msgid:	integer:	specify start entry
			count:			integer > 0:how many history of entry retrieved?
			respond_cb		function(JSON):	callback
							format: same as get_entry's, but array.
*/
word_database.prototype.get_history = function (start_msgid, count, respond_cb) {
	var self = this;
	var context = { histories:[], remain:count };
	get_history_helper(self, start_msgid, context, respond_cb);
}


exports.create = function (ns, re_create, callback) {
	var word_seq_name = "word_id_seq";
	var word_db_name = "words";
	var entry_seq_name = "entry_id_seq";
	var entry_db_name = "entries";
	if (ns) {
		word_seq_name = (ns + "_word_id_seq");
		word_db_name = (ns + "_words");
		entry_seq_name =(ns + "_entry_id_seq");
		entry_db_name = (ns + "_entries");
	}

	pg.connect(process.env.DATABASE_URL, function(err, client) {
		function init_word_database(cb) {
			client.query((re_create ? "DROP SEQUENCE IF EXISTS " + word_seq_name + ";" : "") +  
				"CREATE SEQUENCE " + word_seq_name + ";", 
		    	function (err, result) {
					 client.query(
					 	(re_create ? "DROP TABLE IF EXISTS " + word_db_name + ";" : "") + 
					    "CREATE TABLE " + word_db_name + " (" + 
					    "id INTEGER DEFAULT nextval('" + word_seq_name + "') PRIMARY KEY," + 
					    "word VARCHAR(32) NOT NULL UNIQUE," + 
					    "count int NOT NULL" + 
					    ");",
					 	cb);
				}
		 	);
	 	}
	 	function init_entry_database(cb) {
			client.query((re_create ? "DROP SEQUENCE IF EXISTS " + entry_seq_name + ";" : "") + 
				"CREATE SEQUENCE " + entry_seq_name + ";", 
				function (err, result) {
					client.query(
					 	(re_create ? "DROP TABLE IF EXISTS " + entry_db_name + ";" : "") + 
				    	"CREATE TABLE " + entry_db_name +" (" + 
				    	"id INTEGER DEFAULT nextval('" + entry_seq_name + "') PRIMARY KEY," + 
						"prev_id INT NOT NULL DEFAULT 0," + 
						"word_id INT NOT NULL," + 
						"user_id INT NOT NULL," +
						"depth INT NOT NULL DEFAULT 0" +
						");", 
				    	cb);
		    	}
		 	);
		}
		async.parallel([
			init_word_database,
			init_entry_database
		], function(err, result) {
			if (err) {
				callback(err, null);
				return;
			}
		 	console.log("word_database module: create finished for namespace:" + ns);
		 	if (!re_create) {
		 		var wq = client.query("SELECT * FROM " + word_db_name +";");
		 		wq.on('row', function(row) {
		 			console.log("word_row:" + JSON.stringify(row));
		 		})
		 		var eq = client.query("SELECT * FROM " + entry_db_name + ";");
		 		eq.on('row', function(row) {
		 			console.log("entry_row:" + JSON.stringify(row));
		 		})
		 	}
		 	callback(null, new word_database(new pg.Client(process.env.DATABASE_URL), {
				word_seq_name: word_seq_name,
				word_db_name: word_db_name,
				entry_seq_name: entry_seq_name,
				entry_db_name: entry_db_name,
		 	}));
		});
	});
}