exports.executeStatement = function(string, params, callback) {
if(conn === null) {
sql.open( config.conn_str, function( err, new_conn ) {
if (err)
console.log('[DB.createConnection]Error: ' + err);
c = new_conn;
});
}
//queryRaw
var colums =[]; var data = [];
for (var p in params) {
}
sql.query(config.conn_str, string, params, function (err, rows) {
if (err)
console.log('[DB.executeStatement]Error: ' + err);
//return this.onError(err, callback);
//if (rows.length == 0)
// return callback(403, "MUST SPECIFY VALID COUNTRY CODE");
//var buffer = new Buffer('0123456789abcdef', 'hex');
return callback(200, "OK", {}, rows);
});
};
exports.select = function(table, condition, callback) {
// select * from table where cust_id=99 and..
/*
condition = { select : [fname, lname],
where : { cust_id:99, email:x@gmail.com}
}
*/
console.log('select ' + condition.select.toString());
var qstring = 'SELECT ' + condition.select.toString() + ' FROM ' + table + ' WHERE ';
var len = Object.keys(condition.where).length;
console.log('Length: ' + len);
for(var p in condition.where) {
qstring += p+'=\''+condition.where[p]+'\'';
len--;
if(len != 0)
qstring += ' and ';
}
console.log('STATEMENT: ' + qstring);
sql.query(config.conn_str, qstring, function (err, rows) {
if (err)
console.log('[DB.executeStatement]Error: ' + err);
return callback(200, "OK", {}, rows);
});
};
this.removeStaleClients = function () {
sql.query( conn_str, "exec LobbyUser_RemoveAll", function ( err, results) {
if ( err ) {
console.log( err );
return;
}
});
}
function removeClientFromLobby( client ) {
sql.query( conn_str, "exec dbo.User_RemoveFromLobby '" + client.id + "'", function ( err, results ) {
if ( err ) {
console.log( err );
return;
}
broadcastClientList(); //on a successful remove broadcast out the new contents of the lobby
});
}
this.addClientToLobby = function ( username, socket ) {
sql.query( conn_str, "exec dbo.User_AddToLobby '" + username + "', '" + socket + "'", function ( err, results ) {
if ( err ) {
console.log( err );
return;
}
broadcastClientList(); //on a successful add broadcast out the new contents of the lobby
});
}
function broadcastClientList() {
sql.query( conn_str, "exec dbo.User_GetAllInLobby", function ( err, results ) {
if ( err ) {
console.log( err );
return;
}
io.sockets.emit( 'ClientList', {
clients: results
});
});
}
exports.parseReceivedData(req, function(work) {
mssql.query(conn_str, "DELETE FROM workitems WHERE ID=?",
[work.ID], function(err) {
if (err){
mssql.diagnostic('error','dataservices.delete', err);
exports.show(conn_str, response) ;
throw err;
}
}
);
exports.show( conn_str, response);
});
app.get('/', function (req, res) {
sql.query(conn_str, 'SELECT [User] as [user], [Text] as [text] FROM bbs.Message', function (err, results) {
if (err) {
console.log(JSON.stringify(err));
res.render('error.jade', {pagetitle: 'Error', error: err});
return;
}
res.render('messages.jade', {
pagetitle:'Messages', messages: results
});
});
});
// mark task as 'FINISHED' in the database
// onDone(err)
function markTaskFinishedDB(conn_str, sql, task, stdout, stderr, onDone) {
var params =
[
task.job_id
,task.index
,task.pid
,task.ret_code
,stdout.length > 0 ? stdout : null
,stderr.length > 0 ? stderr : null
];
sqlserver.query(conn_str, sql, params, function(err, dataTable) {
if (typeof onDone === 'function') onDone(err);
});
}
var iv = setInterval(function () {
var databasecdc = "SELECT * FROM cdc." + schema + "_" + tblname + "_CT where __$start_lsn > " + ldn;
var datagram = [];
var metadata = [];
var currentObject = {};
var rowcount = 0;
var stmt = sql.query(conn_str, databasecdc);
stmt.on('meta', function (meta) {
metadata = meta;
});
stmt.on('row', function (idx) {
currentObject = {};
rowcount++;
});
stmt.on('column', function (idx, data, more) {
if(idx == 0){
ldn = parseldn(data);
}
currentObject[metadata[idx].name] = data;
if(idx == (Object.keys(metadata).length - 1)){
(function(currObj){
saveldn(dbname, tblname, ldn, function(err){
if(err){
stream.emit('error', err);
stream.close();
}
currentObject.tablename = tblname;
stream.emit('data', JSON.stringify(currObj));
});
})(currentObject);
}
});
stmt.on('done', function () {
});
stmt.on('error', function (err) {
stream.emit('error', err);
});
}, interval);
exports.parseReceivedData( req, function(work) {
// todo: create validity test for workitems existance and values
mssql.query(conn_str, "INSERT INTO workitems (Hours, TaskDate, Description) VALUES (?, ?, ?)",
[work.Hours, work.TaskDate, work.Description], function ( err, response) {
exports.show( conn_str, response);
if (err){
mssql.diagnostic('error','dataservices.parseReceivedData mssql.query', err, doDiag);
throw err;
}
});
exports.show(conn_str, response);
});
exports.parseReceivedData(req, function(workitems) {
// todo: code logic to avoid duplicate archived records
mssql.query(conn_str, "UPDATE workitems SET archived=1 WHERE ID=?", [workitems.ID],
function(err) {
if (err) {
mssql.diagnostic('error','dataservices.archive', err, doDiag);
throw err;
}
// exports.show( conn_str, response);
}
);
exports.show( conn_str, response);
});
exports.show = function( conn_str, response, showArchived) {
var query = "SELECT * FROM workitems WHERE archived=? ORDER BY TaskDate DESC, ID DESC";
var archiveValue = (showArchived) ? 1 : 0;
//mssql.diagnostic('info','dataservices.show', conn_str, doDiag);
mssql.query( conn_str, query, [archiveValue],
function(err, rows) {
if (err){
mssql.diagnostic('error','dataservices.show', err);
throw err;
}
html = (showArchived) ? '' : '<a href="/archived">Archived Work</a><br/>';
html += exports.WorkHitlistHtml(rows);
html += exports.WorkFormHtml();
exports.sendHtml(response, html);
}
);
}
function saveldn(dbname, tblname, ldn, cb){
var sqlcdc_conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=sqlcdc;Trusted_Connection={Yes}"
var sqlcdc_databasecdc = "MERGE INTO [sqlcdc].[dbo].[tablestatus]";
sqlcdc_databasecdc += "USING (SELECT '" + dbname + "' AS dbname, '" + tblname + "' as tblname ) AS SRC ";
sqlcdc_databasecdc += "ON tablestatus.databasename = SRC.dbname AND tablestatus.tablename = SRC.tblname ";
sqlcdc_databasecdc += "WHEN MATCHED THEN UPDATE SET ";
sqlcdc_databasecdc += "currentLSN = " + ldn + " ";
sqlcdc_databasecdc += "WHEN NOT MATCHED THEN ";
sqlcdc_databasecdc += "INSERT (databasename, tablename, currentLSN) ";
sqlcdc_databasecdc += "VALUES (SRC.dbname, SRC.tblname, " + ldn + ");";
var sqlcdc_stmt = sql.query(sqlcdc_conn_str, sqlcdc_databasecdc);
sqlcdc_stmt.on('error', function (err) {
return cb(new Error("Can't divide by zero"));
});
sqlcdc_stmt.on('done', function () {
cb();
});
}
exports.streamquery = function(req, res) {
var stmt = sql.query(conn_str, "SELECT TOP 10 * FROM Person.Person");
stmt.on('meta', function (meta) {
console.log("We've received the metadata");
res.json({
meta: meta
});
});
stmt.on('row', function (idx) {
console.log("We've started receiving a row");
res.json({
row: idx
});
});
stmt.on('column', function (idx, data, more) {
console.log(idx + ":" + data);
res.json({
column: idx + ":" + data
});
});
stmt.on('done', function () {
console.log("All done!");
res.json({
status: "All done!"
});
});
stmt.on('error', function (err) {
console.log("We had an error :-( " + err);
res.json({
error: "We had an error :-( " + err
});
});
res.json({
status: "stream done."
});
};
exports.insert = function(table, condition, callback) {
// INSERT INTO table (cust_id, first_name, last_name...) VALUES(6754,'fundu','*****@*****.**',CAST(1 AS BIT),CAST(1 AS BIT),'0123456789','333031',1234,5678,1)";
/*
condition = { values: { cust_id:99,
first_name:'Krishna',
last_name:'Chaitanya'
}
*/
var qstring = 'INSERT INTO ' + table + ' ';
var len = Object.keys(condition.values).length;
console.log('Length: ' + len);
var columns = "(", data = "(";
for(var p in condition.values) {
columns += p;
data += "'"+condition.values[p]+"'";
len--;
if(len != 0) {
columns += ','; data += ',';
} else {
columns += ')'; data += ')';
}
}
qstring += columns + ' VALUES ' + data;
console.log('STATEMENT: ' + qstring);
//queryRaw
sql.query(config.conn_str, qstring, function (err, rows) {
if (err)
console.log('[DB.executeStatement]Error: ' + err);
return callback(200, "OK", {}, rows);
});
};
exports.update = function(table, condition, callback) {
// UPDATE table SET(cust_id='999', first_name='Kotesh', last_name='Rao' WHERE phone='0123456789' AND last_name='Rao'
/*
condition = { set: { cust_id:99, first_name:'Krishna', last_name:'Chaitanya'},
where : { cust_id:99, email:x@gmail.com}
}
*/
var qstring = 'UPDATE ' + table;
for(var q in condition) {
var len = Object.keys(condition[q]).length;
console.log('Length: ' + len);
var columns = "(", data = "(";
qstring += ' '+q+' ';
for(var p in condition[q]) {
qstring += p+'=\''+condition[q][p]+'\'';
len--;
if(len != 0)
if(q === 'set')
qstring += ' , ';
else
qstring += ' and ';
}
}
console.log('STATEMENT: ' + qstring);
//queryRaw
sql.query(config.conn_str, qstring, function (err, rows) {
if (err)
console.log('[DB.executeStatement]Error: ' + err);
return callback(200, "OK", {}, rows);
});
};
// Get the task info from the database so it can be launched.
// At the same time, mark the task as being 'DISPATCHED' to the node.
// If the task is not found or the task has already been aborted, the onError() handler will be called instead.
// onDone(err)
function getJobTaskInfoDB(conn_str, sql, task, onDone) {
sqlserver.query(conn_str, sql, [task.job_id, task.index, task.node], function(err, dataTable) {
if (err) {
if (typeof onDone === 'function') onDone(err);
} else {
if (dataTable.length < 1) {
if (typeof onDone === 'function') onDone(task_toString(task) + ' cannnot be found');
return;
} else {
var row = dataTable[0];
task.cmd = row['cmd'];
task.cookie = row['cookie'];
task.stdin_file = row['stdin_file'];
task.aborted = row['aborted'];
if (task.aborted) {
if (typeof onDone === 'function') onDone(task_toString(task) + ' was aborted');
return;
} else {
if (typeof onDone === 'function') onDone(null);
}
}
}
});
}
// mark task as 'RUNNING' in the database
function markTaskStartDB(conn_str, sql, task, onDone) {
sqlserver.query(conn_str, sql, [task.job_id, task.index, task.pid], function(err, dataTable) {
if (typeof onDone === 'function') onDone(err);
});
}
module.exports = function (conn_str, schema, tblname, interval) {
//Validate table exists
var databasecdc = "SELECT sys.schemas.name, sys.tables.name AS tablename, sys.tables.create_date, sys.tables.modify_date, sys.tables.is_tracked_by_cdc, sys.tables.type_desc "
+ "FROM sys.schemas INNER JOIN "
+ "sys.tables ON sys.schemas.schema_id = sys.tables.schema_id where sys.tables.name = '" + tblname + "'";
sql.open(conn_str, function (err, conn) {
if (err) {
throw new Error(err);
}
conn.queryRaw(databasecdc, function (err, results) {
if (err) {
throw new Error(err);
}
//console.log(results);
if(results.rows.length == 0){
throw new Error('Table : "' + tblname + '" Does Not exist ');
}
if(results.rows[0][4] != 1){
throw new Error('Change Data Capture for ' + tblname + " is currently set to " + results.rows[0][4] + "\n Consider EXEC sys.sp_cdc_enable_table @source_schema, @source_name @role_name");
}
});
});
var re = new RegExp("database=([^;]*);");
var dbnameArray = conn_str.toLowerCase().match("database=([^;]*);");
var dbnametotal = dbnameArray.join("").split(";");
var dbname = dbnametotal[1];
var sqlcdc_conn_str = conn_str.toLowerCase().replace(dbnametotal[0], "database=sqlcdc");
console.log(sqlcdc_conn_str);
var times = 0;
var ldn = 0;
var cdcdata = "SELECT * FROM tablestatus where tablename = '" + tblname + "' AND databasename = '" + dbname + "';";
var stmt = sql.query(sqlcdc_conn_str, cdcdata);
stmt.on('meta', function (meta) { });
stmt.on('column', function (idx, data, more) {
if(idx == 2)
ldn = parseldn(data);
});
stmt.on('done', function () {
if(ldn == undefined){
trkObj.ldn = 0;
}
});
stmt.on('error', function (err) { throw new Error('error',"Error finding table status had an error :-( " + err); });
var stream = new Stream();
stream.readable = true
var iv = setInterval(function () {
var databasecdc = "SELECT * FROM cdc." + schema + "_" + tblname + "_CT where __$start_lsn > " + ldn;
var datagram = [];
var metadata = [];
var currentObject = {};
var rowcount = 0;
var stmt = sql.query(conn_str, databasecdc);
stmt.on('meta', function (meta) {
metadata = meta;
});
stmt.on('row', function (idx) {
currentObject = {};
rowcount++;
});
stmt.on('column', function (idx, data, more) {
if(idx == 0){
ldn = parseldn(data);
}
currentObject[metadata[idx].name] = data;
if(idx == (Object.keys(metadata).length - 1)){
(function(currObj){
saveldn(dbname, tblname, ldn, function(err){
if(err){
stream.emit('error', err);
stream.close();
}
currentObject.tablename = tblname;
stream.emit('data', JSON.stringify(currObj));
});
})(currentObject);
}
});
stmt.on('done', function () {
});
stmt.on('error', function (err) {
stream.emit('error', err);
});
}, interval);
stream.end = function (data) {
if(data)
stream.write(data);
stream.emit('end');
}
stream.destroy = function () {
stream.emit('close');
}
return stream;
}