1 var log4js = require('log4js');
2 var http = require('http');
3 var async = require('async');
4 var properties = require(process.env.SDNC_CONFIG_DIR + '/netdb-updater.json');
5 var admProperties = require(process.env.SDNC_CONFIG_DIR + '/admportal.json');
6 var csvtojson = require('csvtojson');
7 var mysql = require('mysql');
8 var moment = require('moment');
9 var os = require('os');
10 var fs = require('fs.extra');
12 // Check to make sure SDNC_CONFIG_DIR is set
13 var sdnc_config_dir = process.env.SDNC_CONFIG_DIR;
14 if ( typeof sdnc_config_dir == 'undefined' )
16 console.log('ERROR the SDNC_CONFIG_DIR environmental variable is not set.');
21 log4js.configure(process.env.SDNC_CONFIG_DIR + '/netdb.log4js.json');
22 var logger = log4js.getLogger('netdb');
23 logger.setLevel(properties.netdbLogLevel);
25 var yargs = require('yargs')
26 .usage("\nUsage: node netdb_updater -t link_master|router_master")
29 .example("Example: node netdb_updater -t link_master","Update SDNC LINK_MASTER table from NetDB.")
32 var dbTable = yargs.table;
33 var debug = properties.netdbDebug;
34 var env = properties.netdbEnv;
35 var retSuccess = false;
39 var dbConnection = '';
46 dbtasks.push( function(callback) { checkParams(callback); } );
47 dbtasks.push( function(callback) { dbConnect(callback); } );
48 //dbtasks.push( function(callback) { netdb(callback); } );
50 logger.debug('\n\n********** START PROCESSING - Env=' + env + ' Debug=' + debug + ' **********');
52 async.series(dbtasks, function(err,result){
54 logger.error(err + ' COUNT: ' + count);
57 if ( errorMsg.length > 0 ){
58 logger.error(errorMsg);
64 function checkParams(scb){
65 if ( dbTable != 'link_master' && dbTable != 'router_master' ){
66 scb("Invalid parameter passed in '" + dbTable + " ' exiting.'");
75 function () { return count < properties.netdbRetryInterval },
77 if ( dbTable == 'link_master' ){
78 getLinkMaster(callback);
80 else if (dbTable == 'router_master'){
81 getRouterMaster(callback);
83 else{ // should never hit this condition
84 logger.debug("Invalid parameter passed in '" + dbTable + " ' exiting.'");
88 logger.debug('whilst err function errorMsg = ' + errorMsg);
90 if ( errorMsg.length > 0 ){
91 logger.debug(errorMsg + ' COUNT: ' + count);
95 logger.debug('success');
102 function returnError(emsg, cb){
105 if ( count == properties.netdbRetryInterval ) { logger.error(errorMsg); }
106 setTimeout( function(){
108 }, properties.netdbWaitTime);
111 function returnSuccess(cb){
112 logger.debug('inside returnSuccess');
114 //var cnt = properties.netdbRetryInterval;
115 //logger.debug('b4 inc returnSuccess count=' + count);
117 //logger.debug('after inc returnSuccess count=' + count);
123 function getRouterMaster(cb){
125 logger.info('getRouterMaster debug=' + debug + ' count=' + count);
128 var netdbEnv = properties.netdbEnv;
130 if ( netdbEnv == 'e2e' || netdbEnv == 'prod' ){
132 auth_param = '?auth=' + admProperties['ip-addresses']['eth2'] + ';'
135 auth_param = '?auth=' + admProperties['ip-addresses']['eth1:0'] + ';'
137 var username = properties.netdbUser;;
138 var password = properties.netdbPassword;
139 var date = moment().format('YYYYMMDD');
140 var auth = 'Basic ' + new Buffer(username + ':' + password).toString('base64');
141 var host = properties.netdbHost;
142 var port = properties.netdbPort;
143 var path = '/' + properties.netdbPath
144 + '/' + properties.netdbNetwork
145 + '/' + properties.netdbApiName
147 + 'client=' + properties.netdbClientName + ';'
148 + 'date=' + date + ';'
149 + 'reportName=' + dbTable + ';'
150 + 'type=' + properties.netdbType;
152 var header = { 'Content-Type': 'text/csv' };
153 //var header = {'Host': host, 'Authorization': auth, 'Content-Type': 'text/csv' };
162 logger.debug('options:\n' + JSON.stringify(options,null,2));
164 var request = http.request(options, function(response) {
166 var response_str = '';
167 if ( retSuccess == true ){
168 var cnt = properties.netdbRetryInterval;
175 logger.debug('STATUS: ' + response.statusCode + ' content-type=' + response.headers['content-type']);
177 // Read the response from ODL side
178 response.on('data', function(chunk) {
179 response_str += chunk;
182 response.on('end', function() {
184 logger.debug('HEADERS:' + JSON.stringify(response.headers));
186 if(response.statusCode == 200){
188 if(response_str.length > 0){
192 fs.writeFileSync('/sdncvar/sdnc/files/netdb-updater/' + moment().unix() + ".netdb." + dbTable + '.csv', response_str);
195 // this is not in reqs, if it fails keep on going.
196 logger.error('Error writing NetDB file:' + e);
199 if (response.headers['content-type'].indexOf('html') > 0){
200 returnError('Error:Unexpected content-type:' + response.headers['content-type'] + ' returned.\n', cb);
203 // need to parse csv file
204 var Converter=csvtojson.Converter;
205 var csvConverter = new Converter({
208 var routerMasterSQL = '';
210 // end_parsed will be emitted once parsing is finished
211 csvConverter.on("end_parsed", function(respObj){
213 routerMasterSQL = routerMasterSQL.concat("INSERT INTO ROUTER_MASTER (crs_name, loopback_ip)");
214 for ( var x=0; x < respObj.length; x++ ){
216 if ( respObj[x].field1.length == 0 ){
217 returnError('Required field [crs_name] is null.', cb);
221 routerMasterSQL = routerMasterSQL.concat(' union ');
223 routerMasterSQL = routerMasterSQL.concat(" SELECT "
224 + "'" + respObj[x].field1 + "',"
225 + "'" + respObj[x].field2 + "' FROM DUAL ");
227 //logger.debug('SQL: ' + routerMasterSQL);
229 if (debug != 'debug' && env != 'dev'){
232 tasks.push( function(callback) { updateRouterMaster(routerMasterSQL,callback); } );
233 async.series(tasks, function(err,result){
239 logger.info('*** Router Master Table Replaced ***');
246 logger.debug('*** debug ***');
252 csvConverter.on("error",function(errMsg,errData){
253 returnError(errMsg,cb);
256 csvConverter.fromString(response_str, function(err,result){
264 //logger.debug("no data");
265 returnError('no data',cb);
269 else if(response.statusCode == 404){
270 returnError('Router Master Table for ' + date + ' is not Available.',cb);
274 returnError('Status Code:' + response.statudCode + ' returned for Router Master Table query.',cb);
279 request.on('error', function(e) {
280 if ( retSuccess == true ){
281 var cnt = properties.netdbRetryInterval;
293 function getLinkMaster(cb){
295 logger.info('getLinkMaster debug=' + debug + ' count=' + count);
298 var netdbEnv = properties.netdbEnv;
300 if ( netdbEnv == 'e2e' || netdbEnv == 'prod' ){
302 auth_param = '?auth=' + admProperties['ip-addresses']['eth2'] + ';'
305 auth_param = '?auth=' + admProperties['ip-addresses']['eth1:0'] + ';'
307 var username = properties.netdbUser;;
308 var password = properties.netdbPassword;
309 var auth = 'Basic ' + new Buffer(username + ':' + password).toString('base64');
310 var host = properties.netdbHost;
311 var port = properties.netdbPort;
312 var date = moment().format('YYYYMMDD');
313 var path = '/' + properties.netdbPath
314 + '/' + properties.netdbNetwork
315 + '/' + properties.netdbApiName
317 + 'client=' + properties.netdbClientName + ';'
318 + 'date=' + date + ';'
319 + 'reportName=' + dbTable + ';'
320 + 'type=' + properties.netdbType;
322 var header = { 'Content-Type': 'text/csv' };
323 //var header = {'Host': host, 'Authorization': auth, 'Content-Type': 'text/csv' };
332 logger.debug('options:\n' + JSON.stringify(options,null,2));
334 var request = http.request(options, function(response) {
336 logger.debug('STATUS: ' + response.statusCode + ' content-type=' + response.headers['content-type']);
338 if ( retSuccess == true ){
339 var cnt = properties.netdbRetryInterval;
346 var response_str = '';
348 // Read the response from ODL side
349 response.on('data', function(chunk) {
350 response_str += chunk;
353 response.on('end', function() {
355 logger.debug('HEADERS:' + JSON.stringify(response.headers));
357 if(response.statusCode == 200){
359 if(response_str.length > 0){
361 //logger.debug('response_str=' + response_str);
364 fs.writeFileSync('/sdncvar/sdnc/files/netdb-updater/' + moment().unix() + ".netdb." + dbTable + '.csv', response_str);
367 // this is not in reqs, if it fails keep on going.
368 logger.error('Error writing NetDB file:' + e);
371 if (response.headers['content-type'].indexOf('html') > 0){
372 returnError('Error:Unexpected content-type:' + response.headers['content-type'] + ' returned.\n', cb);
375 // need to parse csv file
376 var Converter=csvtojson.Converter;
377 var csvConverter = new Converter({
381 var linkMasterSQL = '';
383 // end_parsed will be emitted once parsing is finished
384 csvConverter.on("end_parsed", function(jsonObj){
386 linkMasterSQL = linkMasterSQL.concat("INSERT INTO LINK_MASTER (link_interface_ip, source_crs_name, destination_crs_name, link_speed, default_cost, bundle_name, shutdown)");
387 for ( var x=0; x < jsonObj.length; x++ ){
388 if ( jsonObj[x].field1.length == 0 ){
389 returnError('Required field [link_interface_ip] is null.', cb);
392 if ( jsonObj[x].field2.length == 0 ){
393 returnError('Required field [source_crs_name] is null.', cb);
396 if ( jsonObj[x].field3.length == 0 ){
397 returnError('Required field [destination_crs_name] is null.', cb);
401 linkMasterSQL = linkMasterSQL.concat(' union ');
404 linkMasterSQL = linkMasterSQL.concat(" SELECT "
405 + "'" + jsonObj[x].field1 + "',"
406 + "'" + jsonObj[x].field2 + "',"
407 + "'" + jsonObj[x].field3 + "',"
408 + jsonObj[x].field4 + ","
409 + jsonObj[x].field5 + ","
410 + "'" + jsonObj[x].field6 + "',"
411 + "'" + jsonObj[x].field7 + "' FROM DUAL");
413 //logger.debug('SQL: ' + linkMasterSQL);
415 if (debug != 'debug' && env != 'dev'){
418 tasks.push( function(callback) { updateLinkMaster(linkMasterSQL,callback); } );
419 async.series(tasks, function(err,result){
427 logger.info('*** Link Master Table Replaced ***');
438 csvConverter.on("error",function(errMsg,errData){
439 returnError(errMsg,cb);
442 csvConverter.fromString(response_str, function(err,result){
444 returnError(errMsg,cb);
450 returnError('no data',cb);
454 else if(response.statusCode == 404){
455 returnError('Link Master Table for ' + date + ' is not Available.',cb);
459 returnError('Status Code:' + response.statudCode + ' returned for Link Master Table query.',cb);
464 request.on('error', function(e) {
465 if ( retSuccess == true ){
466 var cnt = properties.netdbRetryInterval;
479 function dbConnect(callback){
481 var l_db01 = admProperties['databases']['0'];
482 var db01Array = l_db01.split("|");
485 var l_db02 = admProperties['databases']['1'];
486 var db02Array = l_db02.split("|");
489 if ( admProperties.dbFabric == 'true' )
491 logger.debug('connectFabric()');
494 var fabric_connection = mysql.createConnection({
495 host : admProperties.dbFabricServer,
496 user : admProperties.dbFabricUser,
497 password : admProperties.dbFabricPassword,
498 database : admProperties.dbFabricDB,
499 port : admProperties.dbFabricPort
503 fabric_connection.connect( function(err) {
509 fabric_connection.query('CALL dump.servers()', function(err,rows) {
517 fabric_connection.end();
518 logger.debug('rows: ' + JSON.stringify(rows,null,2));
521 for ( var x=0; x<rows.length; x++)
524 for ( var y=0; y<rows[x].length; y++)
526 var row = rows[x][y];
527 if (row.group_id == admProperties.dbFabricGroupId)
529 if (row.status == '3' && row.mode == '3'){
535 logger.debug('currentDB: ' + currentDB);
536 logger.debug('masterDB: ' + masterDB);
538 if (masterDB.length <=0)
540 logger.debug('no writable master db');
541 callback('no writable master db');
545 if ( currentDB != masterDB )
547 currentDB = masterDB;
548 dbConnection = mysql.createConnection({
549 connectionLimit : admProperties.dbConnLimit,
551 user : admProperties.dbUser,
552 password : admProperties.dbPassword,
553 database : admProperties.dbName,
554 multipleStatements: true,
558 logger.debug('new currentDB: ' + currentDB);
559 logger.debug('new masterDB: ' + masterDB);
563 fabric_connection.on('error', function(err){
564 logger.debug(err.code);
574 var dbConn = mysql.createConnection({
575 connectionLimit : admProperties.dbConnLimit,
577 user : admProperties.dbUser,
578 password : admProperties.dbPassword,
579 database : admProperties.dbName,
580 multipleStatements: true,
583 logger.debug('initDB currentDB=' + currentDB);
585 dbConn.connect(function(err,connection){
588 logger.debug( String(err) ); // ALARM
592 var sql = 'select @@read_only';
593 dbConn.query(sql, function(err,result){
596 // @@read_only=0 means db is writable
597 logger.debug('@@read_only=' + result[0]['@@read_only']);
598 if ( result[0]['@@read_only'] != '0' )
600 if (currentDB == db01)
608 logger.debug('initDB reconnect to currentDB '+ currentDB);
609 var newConnection = mysql.createConnection({
610 connectionLimit : admProperties.dbConnLimit,
612 user : admProperties.dbUser,
613 password : admProperties.dbPassword,
614 database : admProperties.dbName,
615 multipleStatements: true,
618 dbConnection = newConnection;
622 dbConnection = dbConn;
630 function updateLinkMaster(linkMasterSQL,callback){
632 logger.debug('updateLinkMaster');
634 dbConnection.connect(function(err,connection)
637 logger.debug( String(err) ); // ALARM
638 callback(err, 'Unable to get database connection.');
641 }); // end connection
642 dbConnection.beginTransaction(function(err) {
644 //dbConnection.release();
645 callback(err,String(err));
648 var sql = "DELETE FROM LINK_MASTER";
649 dbConnection.query(sql,function(err,result)
652 //dbConnection.release();
653 dbConnection.rollback( {rollback: 'NO RELEASE'},function(){
654 callback(err,String(err));
658 dbConnection.query(linkMasterSQL,function(err,result)
661 //dbConnection.release();
662 dbConnection.rollback( {rollback: 'NO RELEASE'},function(){
663 callback(err,String(err));
667 dbConnection.commit(function(err){
669 //dbConnection.release();
670 dbConnection.rollback( {rollback: 'NO RELEASE'},function(){
671 callback(err,String(err));
675 //dbConnection.release();
680 }); // end transaction
683 function updateRouterMaster(routerMasterSQL,callback){
685 logger.debug('updateRouterMaster');
687 dbConnection.connect(function(err,connection)
690 logger.debug( String(err) ); // ALARM
691 callback(err, 'Unable to get database connection.');
694 }); // end connection
695 dbConnection.beginTransaction(function(err) {
697 //dbConnection.release();
698 callback(err,String(err));
701 var sql = "DELETE FROM ROUTER_MASTER";
702 dbConnection.query(sql,function(err,result)
705 //dbConnection.release();
706 dbConnection.rollback( function(){
707 callback(err,String(err));
711 dbConnection.query(routerMasterSQL,function(err,result)
714 //dbConnection.release();
715 dbConnection.rollback( function(){
716 callback(err,String(err));
720 dbConnection.commit(function(err){
722 //dbConnection.release();
723 dbConnection.rollback( function(){
724 callback(err,String(err));
728 //dbConnection.release();
733 }); // end transaction
738 logger.debug('getLinkMaster - count=' + count);
741 setTimeout( function(){
742 cb(null);}, properties.netdbWaitTime);