exports.data = function(req, res) {
var cql = require('node-cassandra-cql');
var client = new cql.Client({hosts: ['127.0.0.1'], keyspace: 'apitest', username: '******', password: '******'});
var consistency = cql.types.consistencies.one;
client.execute('DELETE FROM books WHERE id = ?;', [+req.query.book], consistency,
function(err) {
if (err) {
console.log('execute failed' + err);
res.render('getBook', {parsedJSON: 'execute failed' + err});
}
else {
console.log('Книжка удалена!!! ее номер был №: ' + req.query.book);
res.redirect('../read');
}
}
);
}
var insertTestBlob = function(prefix, title) {
console.log('insert called');
var query = 'insert into tests (test) values (?);';
client.execute(query, [createTestBlob(prefix, title)], 1, function(err, result) {
if (err) {
console.log(err);
} else {
}
});
};
var insertTestByScore = function(prefix, title) {
var query = "insert into test_by_score (commit, delta, test, score) values (?, ?, ?, ?);",
commit = new Buffer(DUMMYCOMMIT),
delta = 0,
test = createTestBlob(prefix, title),
score = Math.floor(Math.random() * (10000));
client.execute(query, [commit, delta, test, score], 1, function(err, result) {
if (err) {
console.log(err);
} else {
}
});
};
router.get('/user_last', function(req, res) {
var rows = [];
var query = " select user_id, user_ts, latitude, longitude from user_last;"
client.eachRow(query, [], consistency, function(n, row) {
//the callback will be invoked per each row as soon as they are received
var d = new Date(row.value_ts);
rows.push({"timestamp": row.user_ts, "user" : row.user_id, "latitude" : row.latitude, "longitude" : row.longitude});
},
function (err, rowLength) {
if (err) console.log(err);
res.json(rows);
}
);
});
router.get('/sensor_qtr_hour_avg/sensor_id/:name/qty/:qty', function(req, res) {
var rows = [];
var query = "select * from sensor_qtr_hour_avg where sensor_id = '" + req.name + "' limit " + req.qty
client.eachRow(query,function(n, row) {
//the callback will be invoked per each row as soon as they are received
var qtr_hour_avg = (row.qtr_hour_total / row.qtr_hour_count) / 100;
rows.push({"sensor_id": row.sensor_id, "event_date_qtr_hour": row.event_date_qtr_hour, "qtr_hour_avg": qtr_hour_avg});
},
function (err, rowLength) {
if (err) console.log(err);
res.jsonp(rows);
}
);
});
router.get('/sensor/:sensor_id/day/:day/qty/:qty', function(req, res) {
var rows = [];
var query = "select * from sensor_date where sensor_id = '" + req.sensor_id + "' and value_date = '" + req.day + "' limit " + req.qty
client.eachRow(query,function(n, row) {
//the callback will be invoked per each row as soon as they are received
// rows["ts:" + row.event_ts] = parseInt(row.event_count);
rows.push({"sensor_id": row.sensor_id, "sensor_date": row.value_date, "value_ts": row.value_ts, "value": parseFloat(row.value)});
},
function (err, rowLength) {
if (err) console.log(err);
res.jsonp(rows);
}
);
});
app.get('/cassandra',function(req, res){
client.execute("select * from system.schema_columns where keyspace_name='site';",[],function(err,result){
if (err)
{
res.send(err)
return
}
var str=""
for(var i=0;i<result.rows.length;i++)
{
str+="table:"+result.rows[i].columnfamily_name+"<br />column:"+result.rows[i].column_name+"<br />type:"+result.rows[i].validator+"<br />index:"+result.rows[i].index_name+"<br /><br /><br />"
}
res.send(str)
})
})
router.get('/sensor_last/qty/:qty', function(req, res) {
var rows = [];
var query = "select * from sensor_last limit " + req.qty;
client.eachRow(query, [], consistency, function(n, row) {
//the callback will be invoked per each row as soon as they are received
var d = new Date(row.value_ts);
rows.push({"timestamp": row.value_ts, "sensor" : row.sensor_type, "value" : row.value});
},
function (err, rowLength) {
if (err) console.log(err);
res.json(rows);
}
);
});
helper.runInTransaction(agent, function transactionInScope(tx) {
var transaction = agent.getTransaction()
t.ok(transaction, "transaction should be visible")
t.equal(tx, transaction, 'We got the same transaction')
var colVal = 'Jim'
var pkVal = 444
var insQuery = 'INSERT INTO ' + KS + '.' + FAM + ' (' + PK + ',' + COL
insQuery += ') VALUES(?, ?);'
client.executeAsPrepared(insQuery, [pkVal, colVal], function (error, ok) {
if (error) return t.fail(error)
t.ok(agent.getTransaction(), "transaction should still be visible")
t.ok(ok, "everything should be peachy after setting")
var selQuery = 'SELECT * FROM ' + KS + '.' + FAM + ' WHERE '
selQuery += PK + ' = ' + pkVal + ';'
client.execute(selQuery, function (error, value) {
if (error) return t.fail(error)
t.ok(agent.getTransaction(), "transaction should still still be visible")
t.equals(value.rows[0][COL], colVal, "Cassandra client should still work")
var trace = transaction.trace
t.ok(trace, "trace should exist")
t.ok(trace.root, "root element should exist")
t.equals(trace.root.children.length, 1,
"there should be only one child of the root")
var setSegment = trace.root.children[0]
t.ok(setSegment, "trace segment for set should exist")
t.equals(setSegment.name, "Datastore/operation/Cassandra/executeAsPrepared",
"should register the executeAsPrepared")
t.ok(setSegment.children.length >= 1,
"set should have a callback segment")
var getSegment = setSegment.children[0].children[0]
t.ok(getSegment, "trace segment for get should exist")
t.equals(getSegment.name, "Datastore/operation/Cassandra/execute",
"should register the execute")
t.ok(getSegment.children.length >= 1,
"should have a callback")
t.ok(getSegment.timer.hrDuration, "trace segment should have ended")
transaction.end(function() {
t.end()
})
})
})
})
loginevents.on("loginuidgot",function(req,res,uid,source,name,email,photo,access_token){
client.execute("select * from source where uid=? and site=? ;",[uid,source],function(err,result){
if (err){
res.send("内部错误，请重试2");
return ;
}
if (result.rows.length==0)
{
var uuidtmp=uuid.v4()
client.execute("insert into users (userid,username,email,photo,createtime) values(?,?,?,?,?)",[uuidtmp,name,email,photo,Date.parse(new Date())],function(insert_user_err,insert_user_result){
if (insert_user_err)
{
res.send("内部错误，请重试3");
return ;
}
session.uuid=uuidtmp
if (session.token==undefined)
{
session.token=[]
}
session.token[source]=access_token
client.execute("insert into source (uid,site,userid,access_token) values (?,?,?,?)",[uid,source,uuidtmp,access_token],function(insert_source_err,insert_source_result){
if (insert_source_err)
{
res.send("内部错误，请重试4");
return ;
}
res.redirect('http://www.itsounds.cool/userpanel/index');
})
})
} else
{
session.uuid=result.rows[0].userid;
if (session.token==undefined)
{
session.token=[]
}
session.token[source]=access_token;
res.redirect('http://www.itsounds.cool/userpanel/index');
}
})
})
router.get('/count/type/:name/qty/:qty', function(req, res) {
var now = new Date();
var now_utc = new Date(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate(),
now.getUTCHours(), now.getUTCMinutes(), now.getUTCSeconds());
var rows = [];
var query = "select event_ts, event_count from event_count_date where sensor_type = '" + req.name + "' and event_date = '" +
dateFormat( now_utc, "yyyymmdd") + "' limit " + req.qty
client.eachRow(query,function(n, row) {
//the callback will be invoked per each row as soon as they are received
// rows["ts:" + row.event_ts] = parseInt(row.event_count);
rows.push({"timestamp": row.event_ts, "value": parseInt(row.event_count)});
},
function (err, rowLength) {
if (err) console.log(err);
res.jsonp(rows);
}
);
});
router.get('/sensor/:sensor_id/day/:day/start/:start/end/:end', function(req, res) {
var rows = [];
var query = "select * from sensor_date where sensor_id = '" + req.sensor_id + "' and value_date = '" + req.day +
"' and value_ts > '" + req.start + "' and value_ts < '" + req.end + "'"
client.execute(query, function(err, result) {
if (err){
console.log('db error')
res.send(err);
}
else{
//this is pointless, but may want to restructure return values later...
for( var i = 0; i < result.rowCount; i++ ) {
rows.push(result.rows[i]);
console.log(result.rows[i]);
}
res.json(result);
}
});
});
client.execute("insert into users (userid,username,email,photo,createtime) values(?,?,?,?,?)",[uuidtmp,name,email,photo,Date.parse(new Date())],function(insert_user_err,insert_user_result){
if (insert_user_err)
{
res.send("内部错误，请重试3");
return ;
}
session.uuid=uuidtmp
if (session.token==undefined)
{
session.token=[]
}
session.token[source]=access_token
client.execute("insert into source (uid,site,userid,access_token) values (?,?,?,?)",[uid,source,uuidtmp,access_token],function(insert_source_err,insert_source_result){
if (insert_source_err)
{
res.send("内部错误，请重试4");
return ;
}
res.redirect('http://www.itsounds.cool/userpanel/index');
})
})
router.get('/sensor_hour_avg/sensor_type/:type/qty/:qty', function(req, res) {
var cols = [];
var rows = [];
cols.push({id: 'sensor_id', label: 'Sensor', type: 'string'});
cols.push({id: 'event_date_hour', label: 'Event Date Hour', type: 'string'});
cols.push({id: 'hour_avg', label: 'Hour Average', type: 'number'});
var query = "select * from sensor_type_hour_avg where sensor_type = '" + req.type + "' limit " + req.qty
client.eachRow(query,function(n, row) {
//the callback will be invoked per each row as soon as they are received
var hour_avg = (row.hour_total / row.hour_count) / 100;
rows.push({c:[{v: row.sensor_id}, { v: row.event_date_hour}, {v: hour_avg}]});
},
function (err, rowLength) {
if (err) console.log(err);
res.jsonp({cols: cols, rows: rows});
}
);
});
router.get("/index",function(req,res){
if (req.session.uuid==undefined || req.session.uuid=="")
{
res.send("您还木有登录，请去<a href=\"http://www.itsounds.cool/login\">登录</a>")
return ;
}
client.execute("select * from users where userid=? ;",[req.session.uuid],function(err,result){
if (err){
res.send("发生了一些错误，请重试");
return ;
}
if (result.rows.length>0) {
var str="<p>登录成功！</p>";
res.send(str)
} else
{
var str="<p>登录失败！</p>"
res.send(str)
}
})
})
loginevents.on("binduidgot",function(req,res,uid,source,name,email,photo,access_token){
client.execute("select * from source where uid=? and site=? ;",[uid,source],function(err,result){
if (err){
res.send("内部错误，请重试2");
return ;
}
if (result.rows.length==0)
{
var uuidtmp="";
try
{
uuidtmp=session.uuid;
}
catch (exception){
}
if (uuidtmp=="")
{
res.send("您还没有登录，不能绑定账号");
return ;
}
client.execute("insert into source (uid,site,userid,access_token) values (?,?,?,?)",[uid,source,uuidtmp,access_token],function(insert_source_err,insert_source_result){
if (insert_source_err)
{
res.send("内部错误，请重试4");
return ;
}
if (session.token==undefined)
{
session.token=[]
}
session.token[source]=access_token
res.redirect('http://www.itsounds.cool/userpanel/index');
})
} else
{
res.send("此外部账号已绑定过，请勿重复绑定");
}
})
})
router.get('/count/type/:name', function(req, res) {
var now = new Date();
var now_utc = new Date(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate(),
now.getUTCHours(), now.getUTCMinutes(), now.getUTCSeconds());
var cols = [];
var rows = [];
var query = "select sensor_type, event_ts, event_count from event_count_date where sensor_type = '" + req.name + "' and event_date = '" +
dateFormat( now_utc, "yyyymmdd") + "' limit 1"
cols.push({id: 'sensor_type', label: 'Sensor Type', type: 'string'});
cols.push({id: 'count', label: 'Count', type: 'number'});
client.eachRow(query,function(n, row) {
//the callback will be invoked per each row as soon as they are received
rows.push({c:[{v: row.sensor_type}, {v: row.event_count.low}]});
},
function (err, rowLength) {
if (err) console.log(err);
res.jsonp({cols: cols, rows: rows});
}
);
});
client.executeBatch(insArr, function (error, ok) {
if (error) return t.fail(error)
t.ok(agent.getTransaction(), "transaction should still be visible")
t.ok(ok, "everything should be peachy after setting")
var selQuery = 'SELECT * FROM ' + KS + '.' + FAM + ' WHERE '
selQuery += PK + ' = 111;'
client.execute(selQuery, function (error, value) {
if (error) return t.fail(error)
t.ok(agent.getTransaction(), "transaction should still still be visible")
t.equals(value.rows[0][COL], colValArr[0], "Cassandra client should still work")
var trace = transaction.trace
t.ok(trace, "trace should exist")
t.ok(trace.root, "root element should exist")
t.equals(trace.root.children.length, 1,
"there should be only one child of the root")
var setSegment = trace.root.children[0]
t.ok(setSegment, "trace segment for insert should exist")
t.equals(setSegment.name, "Datastore/operation/Cassandra/executeBatch",
"should register the executeBatch")
t.ok(setSegment.children.length >= 2,
"set should have atleast a dns lookup and callback child")
var getSegment = setSegment.children[1].children[0]
t.ok(getSegment, "trace segment for select should exist")
t.equals(getSegment.name, "Datastore/operation/Cassandra/execute",
"should register the execute")
t.ok(getSegment.children.length >= 1,
"get should have a callback segment")
t.ok(getSegment.timer.hrDuration, "trace segment should have ended")
transaction.end(function() {
t.end()
})
})
})
t.tearDown(function () {
helper.unloadAgent(agent)
client.shutdown()
})
helper.runInTransaction(agent, function transactionInScope(tx) {
var transaction = agent.getTransaction()
t.ok(transaction, "transaction should be visible")
t.equal(tx, transaction, 'We got the same transaction')
var colValArr = ['Jim', 'Bob', 'Joe']
var pkValArr = [111, 222, 333]
var insQuery = 'INSERT INTO ' + KS + '.' + FAM + ' (' + PK + ',' + COL
insQuery += ') VALUES(?, ?);'
var insArr = [
{
query: insQuery,
params: [pkValArr[0], colValArr[0]]
},
{
query: insQuery,
params: [pkValArr[1], colValArr[1]]
},
{
query: insQuery,
params: [pkValArr[2], colValArr[2]]
},
]
client.executeBatch(insArr, function (error, ok) {
if (error) return t.fail(error)
t.ok(agent.getTransaction(), "transaction should still be visible")
t.ok(ok, "everything should be peachy after setting")
var selQuery = 'SELECT * FROM ' + KS + '.' + FAM + ' WHERE '
selQuery += PK + ' = 111;'
client.execute(selQuery, function (error, value) {
if (error) return t.fail(error)
t.ok(agent.getTransaction(), "transaction should still still be visible")
t.equals(value.rows[0][COL], colValArr[0], "Cassandra client should still work")
var trace = transaction.trace
t.ok(trace, "trace should exist")
t.ok(trace.root, "root element should exist")
t.equals(trace.root.children.length, 1,
"there should be only one child of the root")
var setSegment = trace.root.children[0]
t.ok(setSegment, "trace segment for insert should exist")
t.equals(setSegment.name, "Datastore/operation/Cassandra/executeBatch",
"should register the executeBatch")
t.ok(setSegment.children.length >= 2,
"set should have atleast a dns lookup and callback child")
var getSegment = setSegment.children[1].children[0]
t.ok(getSegment, "trace segment for select should exist")
t.equals(getSegment.name, "Datastore/operation/Cassandra/execute",
"should register the execute")
t.ok(getSegment.children.length >= 1,
"get should have a callback segment")
t.ok(getSegment.timer.hrDuration, "trace segment should have ended")
transaction.end(function() {
t.end()
})
})
})
})
router.post('/sensor', function(req,res){
var sensorDate = new Date(req.body.value_ts);
var sensorType = req.body.sensor_id.substring( 0, req.body.sensor_id.indexOf("_"));
var quarterHour;
var x = parseInt(dateFormat(sensorDate, "MM"));
switch(true) {
case (x <= 15):
quarterHour = '0';
break;
case (x <= 30):
quarterHour = '1';
break;
case (x <= 45):
quarterHour = '2';
break;
case (x <= 60):
quarterHour = '3';
break;
}
var queries = [
{
query: "insert into sensor_last (sensor_type, sensor_id, value, value_ts) values('" +
sensorType +
"', '" + req.body.sensor_id + "', " + req.body.value + ", '" + dateFormat(sensorDate, "isoDateTime") + "')"
},
{
query: "insert into sensor_date (sensor_id, value_date, value_ts, value) values('" + req.body.sensor_id +
"', '" + dateFormat(sensorDate, "yyyymmdd") + "', '" + dateFormat(sensorDate, "isoDateTime") + "'," + req.body.value + ")"
},
{
query: "insert into sensor_48h (sensor_id, value, value_ts) values('" +
req.body.sensor_id + "', " + req.body.value + ", '" + dateFormat(sensorDate, "isoDateTime") + "')"
}
];
var query3 = "update event_count_date set event_count = event_count + 1 where sensor_type = '" +
sensorType + "' and event_date = '" +
dateFormat(sensorDate, "yyyymmdd") + "' and event_ts = '" + dateFormat(sensorDate, "isoDateTime") + "'"
var query4 = "update event_count_date set event_count = event_count + 1 where sensor_type = 'all' and event_date = '" +
dateFormat(sensorDate, "yyyymmdd") + "' and event_ts = '" + dateFormat(sensorDate, "isoDateTime") + "'"
var query5 = "update sensor_hour_avg set hour_count = hour_count + 1, hour_total = hour_total + " + parseInt(req.body.value * 100) +
" where sensor_id = '" + req.body.sensor_id + "' and event_date_hour = '" + dateFormat(sensorDate, "yyyymmddHH") + "'"
var query6 = "update sensor_type_hour_avg set hour_count = hour_count + 1, hour_total = hour_total + " + parseInt(req.body.value * 100) +
" where sensor_id = '" + req.body.sensor_id + "' and event_date_hour = '" +
dateFormat(sensorDate, "yyyymmddHH") + "' and sensor_type = '" + sensorType + "'"
var query7 = "update sensor_qtr_hour_avg set qtr_hour_count = qtr_hour_count + 1, qtr_hour_total = qtr_hour_total + " + parseInt(req.body.value * 100) +
" where sensor_id = '" + req.body.sensor_id + "' and event_date_qtr_hour = '" + dateFormat(sensorDate, "yyyymmddHH") + quarterHour + "'"
//var query5 = "update sensor_hour_avg set hour_count = hour_count + 1, hour_total = hour_total + ? where sensor_id = ? and event_date_hour = ?"
//var params5 = [req.body.value, req.body.sensor_id, dateFormat(sensorDate, "yyyymmddHH")];
var consistency = cql.types.consistencies.quorum;
client.executeBatch(queries, consistency, function(err) {
if (err) {
console.log(err);
res.send(err);
}
else {
}
});
client.execute(query3, consistency, function(err) {
if (err) {
console.log(err);
res.send(err);
}
});
client.execute(query4, consistency, function(err) {
if (err) {
console.log(err);
res.send("4: " + err);
}
});
client.execute(query5, consistency, function(err) {
if (err) {
console.log("5: " + err);
res.send(err);
}
});
client.execute(query6, consistency, function(err) {
if (err) {
console.log("6: " + err);
res.send(err);
}
});
client.execute(query7, consistency, function(err) {
if (err) {
console.log("7: " + err);
res.send(err);
}
});
res.send(200);
})
router.post('/netsensor', function(req,res){
var sensorDate = new Date(req.body.rssi_ts);
var sensorType = req.body.sensor_id.substring( 0, req.body.sensor_id.indexOf("_"));
var quarterHour;
var x = parseInt(dateFormat(sensorDate, "MM"));
switch(true) {
case (x <= 15):
quarterHour = '0';
break;
case (x <= 30):
quarterHour = '1';
break;
case (x <= 45):
quarterHour = '2';
break;
case (x <= 60):
quarterHour = '3';
break;
}
var queries = [
{
query: "insert into netmon_date (sensor_id, rssi_date, rssi_ts, device_id, rssi) values('" +
req.body.sensor_id + "', '" + dateFormat(sensorDate, "yyyymmdd") + "','" +
dateFormat(sensorDate, "isoDateTime") + "','" + req.body.device_id +
"'," + req.body.rssi + ")"
},
{
query: "insert into netmon_last (sensor_id, device_id, rssi) values('" +
req.body.sensor_id + "', '" + req.body.device_id +
"'," + req.body.rssi + ")"
},
{
query: "insert into device_date (sensor_id, rssi_date, rssi_ts, device_id, rssi) values('" +
req.body.sensor_id + "', '" + dateFormat(sensorDate, "yyyymmdd") + "','" +
dateFormat(sensorDate, "isoDateTime") + "','" + req.body.device_id +
"'," + req.body.rssi + ")"
},
{
query: "insert into device_last (sensor_id, device_id, rssi) values('" +
req.body.sensor_id + "', '" + req.body.device_id +
"'," + req.body.rssi + ")"
}
];
var consistency = cql.types.consistencies.quorum;
client.executeBatch(queries, consistency, function(err) {
if (err) {
console.log(err);
res.send(err);
}
else {
}
});
res.send(200);
})
//Creating a new connection pool to multiple hosts.
var cql = require('node-cassandra-cql');
var client = new cql.Client({hosts: ['localhost'], keyspace: 'recycling'});
client.execute('SELECT * FROM recycling.recycledMaterials', ['jbay'],
function(err, result) {
if (err) console.log('execute failed');
else console.log('got userid ' + result.rows[0].userID);
}
);
var cassandra = require('node-cassandra-cql');
var client = new cassandra.Client({hosts: ['127.0.0.1'], keyspace: 'eduskeyspace'});
client.connect(function (err){
});
client.execute("SELECT * FROM users_by_username", function (err, result){
var user = result.rows[0];
//The row is an Object with column names as property keys
console.log("My username is %s and my email is %s", user.username, user.email);
});