_Tt_db_client::_Tt_db_client()
{
_Tt_string db_hostname = _tt_gethostname();
setTtDBDefaults();
connectToDB(db_hostname);
}
Query::Query(TableSchema *tableSchema, QObject *parent) : QObject(parent)
{
schema = tableSchema;
clear();
queryModel = new Model();
connectToDB();
}
_Tt_db_client::_Tt_db_client (_Tt_db_results & status)
{
_Tt_string db_hostname = _tt_gethostname();
setTtDBDefaults();
connectToDB(db_hostname);
status = dbConnectionResults;
}
/**
* save record to database
*/
void IpfixDbWriter::processDataDataRecord(const IpfixRecord::SourceID& sourceID,
TemplateInfo& dataTemplateInfo, uint16_t length,
IpfixRecord::Data* data)
{
string rowString;
time_t flowStartSeconds;
DPRINTF("Processing data record");
if (dbError) {
connectToDB();
if (dbError) return;
}
/* get new insert */
if(srcId.observationDomainId != 0) {
// use default source id
rowString = getInsertString(rowString, flowStartSeconds, srcId, dataTemplateInfo, length, data);
} else {
rowString = getInsertString(rowString, flowStartSeconds, sourceID, dataTemplateInfo, length, data);
}
// if current table is not ok, write to db and get new table name
if(!(flowStartSeconds >= currentTable.startTime && flowStartSeconds <= currentTable.endTime)) {
if(numberOfInserts > 0) {
msg(MSG_DEBUG, "IpfixDbWriter: Writing buffered records to database");
writeToDb();
numberOfInserts = 0;
}
if (setCurrentTable(flowStartSeconds) != 0) {
return;
}
}
// start new insert statement if necessary
if (numberOfInserts == 0) {
// start insert statement
insertStatement.str("");
insertStatement.clear();
insertStatement << "INSERT INTO " << currentTable.name << " (" << tableColumnsString << ") VALUES " << rowString;
numberOfInserts = 1;
} else {
// append insert statement
insertStatement << "," << rowString;
numberOfInserts++;
}
// write to db if maxInserts is reached
if(numberOfInserts == maxInserts) {
msg(MSG_DEBUG, "IpfixDbWriter: Writing buffered records to database");
writeToDb();
numberOfInserts = 0;
}
}
/**
* Constructor
*/
IpfixDbWriter::IpfixDbWriter(const string& hostname, const string& dbname,
const string& username, const string& password,
unsigned port, uint32_t observationDomainId, unsigned maxStatements,
const vector<string>& columns)
: currentExporter(NULL), numberOfInserts(0), maxInserts(maxStatements),
dbHost(hostname), dbName(dbname), dbUser(username), dbPassword(password), dbPort(port), conn(0)
{
int i;
// set default source id
srcId.exporterAddress.len = 0;
srcId.observationDomainId = observationDomainId;
srcId.exporterPort = 0;
srcId.receiverPort = 0;
srcId.protocol = 0;
srcId.fileDescriptor = 0;
// invalide start settings for current table (to enforce table create)
currentTable.startTime = 1;
currentTable.endTime = 0;
if(columns.empty())
THROWEXCEPTION("IpfixDbWriter: cannot initiate with no columns");
/* get columns */
bool first = true;
for(vector<string>::const_iterator col = columns.begin(); col != columns.end(); col++) {
i = 0;
while(identify[i].columnName != 0) {
if(col->compare(identify[i].columnName) == 0) {
Column c = identify[i];
tableColumns.push_back(c);
// update tableColumnsString
if(!first)
tableColumnsString.append(",");
tableColumnsString.append(identify[i].columnName);
// update tableColumnsCreateString
if(!first)
tableColumnsCreateString.append(", ");
tableColumnsCreateString.append(identify[i].columnName);
tableColumnsCreateString.append(" ");
tableColumnsCreateString.append(identify[i].columnType);
first = false;
break;
}
i++;
}
}
msg(MSG_INFO, "IpfixDbWriter: columns are %s", tableColumnsString.c_str());
if(connectToDB() != 0)
THROWEXCEPTION("IpfixDbWriter creation failed");
}
void OfferModel::getHeadline()
{
auto db = connectToDB();
QSqlQuery query("SELECT headline FROM T_offer");
// auto idIndex = query.record().indexOf("ID");
// auto headlineIndex = query.record().indexOf("headline");
while (query.next())
{
// QString id = query.value(idIndex).toString();
headlines.push_back(query.value(0).toString());
qDebug() << headlines.last();
}
}
std::pair<double, double> SentiDictionary::search(std::string term){
std::pair<double, double> score = std::pair<double, double>();
connectToDB();
std::string pre_query = "SELECT * FROM dev.sentiword WHERE term = '";
pre_query.append(term);
pre_query.append("'");
const char *query = pre_query.c_str();
const char **returnArray;
printf("QUERY: %s \n", query);
if(mysql_query(sqlStruct, query) != 0){
printf("ERROR OCCURRED ON SQL QUERY");
throw new std::exception();
}
MYSQL_RES *sqlResults;
MYSQL_ROW currentRow;
MYSQL_FIELD *field;
unsigned int fieldCount = mysql_field_count(sqlStruct);
printf("MySQL Field Count: %d \n", fieldCount);
if(fieldCount > 0){
sqlResults = mysql_store_result(sqlStruct);
unsigned long long numRows = mysql_num_rows(sqlResults);
printf("Number of rows: %lu\n", numRows);
returnArray = (const char **)calloc(numRows, 12);
unsigned long numFields = mysql_num_fields(sqlResults);
printf("Number of fields: %lu\n", numFields);
while((field = mysql_fetch_field(sqlResults))){
printf("field name %s\n", field->name);
}
while((currentRow = mysql_fetch_row(sqlResults))){
score.first = atof(currentRow[2]);
score.second = atof(currentRow[3]);
}
mysql_close(sqlStruct);
}
return score;
}
/**
* function receive the DataRecord or DataDataRecord when callback is started
*/
int IpfixDbWriter::onDataDataRecord(IpfixRecord::SourceID* sourceID, IpfixRecord::DataTemplateInfo* dataTemplateInfo, uint16_t length, IpfixRecord::Data* data)
{
DPRINTF("Processing data record\n");
if(dbError)
if(connectToDB() == -1)
return -1;
/** check if statement buffer is not full*/
if(statements.statemBuffer[statements.maxStatements-1][0] != '\0') {
THROWEXCEPTION("IpfixDbWriter: Statement buffer is full, this should never happen.");
}
/** sourceid null ? use default*/
/* overwrite sourceid if defined */
if(srcId.observationDomainId != 0 || sourceID == NULL) {
sourceID = &srcId;
}
/** if statement counter lower as max count, insert record in statement buffer*/
if(statements.statemReceived < statements.maxStatements) {
/** make an sql insert statement from the record data */
statements.statemBuffer[statements.statemReceived] = getInsertStatement(
statements.statemBuffer[statements.statemReceived],
sourceID, dataTemplateInfo, length, data, statements.lockTables, statements.maxLocks);
/* check if we got a statement */
if(statements.statemBuffer[statements.statemReceived][0] == '\0') {
msg(MSG_ERROR,"IpfixDbWriter: Could not generate statement from record.");
} else {
DPRINTF("Insert statement: %s\n", statements.statemBuffer[statements.statemReceived]);
/** statemBuffer is filled -> insert in table*/
if(statements.statemReceived == statements.maxStatements-1) {
msg(MSG_INFO, "Writing buffered records to database");
writeToDb();
} else {
statements.statemReceived++;
msg(MSG_DEBUG, "Buffering record. Need %i more records before writing to database.", statements.maxStatements - statements.statemReceived);
}
}
}
// try reconnect to DB if error occurred
return 0;
}
/*
Locking is necessary when multiple queries are executed
simulatenously (e.g. via Lua)
*/
int MySQLDB::exec_sql_query(MYSQL *conn, char *sql,
bool doReconnect, bool ignoreErrors,
bool doLock) {
int rc;
if(!db_operational)
return(-2);
if(doLock && m) m->lock(__FILE__, __LINE__);
if((rc = mysql_query(conn, sql)) != 0) {
if(!ignoreErrors)
ntop->getTrace()->traceEvent(TRACE_ERROR, "MySQL error: [%s][%s]",
get_last_db_error(conn), sql);
switch(mysql_errno(conn)) {
case CR_SERVER_GONE_ERROR:
case CR_SERVER_LOST:
if(doReconnect) {
mysql_close(conn);
if(doLock && m) m->unlock(__FILE__, __LINE__);
connectToDB(conn, true);
return(exec_sql_query(conn, sql, false));
} else
ntop->getTrace()->traceEvent(TRACE_INFO, "%s", sql);
break;
default:
ntop->getTrace()->traceEvent(TRACE_INFO, "%s", sql);
break;
}
rc = -1;
} else {
ntop->getTrace()->traceEvent(TRACE_INFO, "Successfully executed '%s'", sql);
rc = 0;
}
if(doLock && m) m->unlock(__FILE__, __LINE__);
return(rc);
}
/**
* Constructor
*/
IpfixDbWriterMongo::IpfixDbWriterMongo(const string& hostname, const string& database,
const string& username, const string& password,
unsigned port, uint32_t observationDomainId, uint16_t maxStatements,
const vector<string>& propertyNames, bool beautifyProperties, bool allProperties)
: currentExporter(NULL), numberOfInserts(0), maxInserts(maxStatements),
dbHost(hostname), dbName(database), dbUser(username), dbPassword(password), dbPort(port), con(0),
beautyProp(beautifyProperties), allProp(allProperties)
{
int i;
// set default source id
srcId.exporterAddress.len = 0;
srcId.observationDomainId = observationDomainId;
srcId.exporterPort = 0;
srcId.receiverPort = 0;
srcId.protocol = 0;
srcId.fileDescriptor = 0;
// Set Values for Collections
dbCollectionFlows.append(dbName).append(".flows");
dbCollectionExporters.append(dbName).append(".exporters");
dbCollectionCounters.append(dbName).append(".counters");
/* get properties */
for(vector<string>::const_iterator prop = propertyNames.begin(); prop != propertyNames.end(); prop++) {
i = 0;
while(identify[i].propertyName != 0) {
if(prop->compare(identify[i].propertyName) == 0) {
Property p = identify[i];
documentProperties.push_back(p);
break;
}
i++;
}
}
if(propertyNames.empty() && ! allProp)
THROWEXCEPTION("IpfixDbWriterMongo: cannot initiate with no properties");
if(connectToDB() != 0)
THROWEXCEPTION("IpfixDbWriterMongo creation failed");
}
/**
* save record to database
*/
void IpfixDbWriterMongo::processDataDataRecord(const IpfixRecord::SourceID& sourceID,
TemplateInfo& dataTemplateInfo, uint16_t length,
IpfixRecord::Data* data)
{
mongo::BSONObj obj;
msg(MSG_DEBUG, "IpfixDbWriter: Processing data record");
if (dbError) {
msg(MSG_DEBUG, "IpfixDbWriter: reconnecting to DB");
connectToDB();
if (dbError) return;
}
/* get new insert */
if(srcId.observationDomainId != 0) {
// use default source id
obj = getInsertObj(srcId, dataTemplateInfo, length, data);
} else {
obj = getInsertObj(sourceID, dataTemplateInfo, length, data);
}
// start new insert statement if necessary
if (numberOfInserts == 0) {
// start insert statement
bufferedObjects.clear();
bufferedObjects.push_back(obj);
numberOfInserts = 1;
} else {
// append object
bufferedObjects.push_back(obj);
numberOfInserts++;
}
// write to db if maxInserts is reached
if(numberOfInserts == maxInserts) {
msg(MSG_DEBUG, "IpfixDbWriter: Writing buffered records to database");
writeToDb();
numberOfInserts = 0;
}
}
void firstTimeWizard::accept()
{
QSqlDatabase db;
QString driver;
switch(field("driver").toInt())
{
case 0:
driver = "QSQLITE";
break;
case 1:
driver = "QMYSQL";
break;
case 2:
driver = "QPSQL";
break;
}
db = QSqlDatabase::addDatabase(driver);
if(connectToDB(field("hostname").toString(),
field("database").toString(),
field("username").toString(),
field("password").toString()))
{
Settings::setConnectionInfo(driver, field("hostname").toString(),
field("database").toString(),
field("username").toString(),
QString::fromStdString(encrypt(field("password").toString().toStdString(), KEY)));
Settings::setFirstTime(false);
db.close();
parentWidget()->setEnabled(true);
QDialog::accept();
}
else
{
QMessageBox::critical(this, "Connection Error", db.lastError().text());
}
}
void* MySQLDB::queryLoop() {
Redis *r = ntop->getRedis();
MYSQL mysql_alt;
if(connectToDB(&mysql_alt, true) == false)
return(NULL);
while(!ntop->getGlobals()->isShutdown()) {
char sql[2048];
int rc = r->lpop(CONST_SQL_QUEUE, sql, sizeof(sql));
if(rc == 0) {
if(exec_sql_query(&mysql_alt, sql, true, true, false) != 0) {
ntop->getTrace()->traceEvent(TRACE_ERROR, "MySQL error: %s", get_last_db_error(&mysql));
ntop->getTrace()->traceEvent(TRACE_ERROR, "%s", sql);
}
} else
sleep(1);
}
mysql_close(&mysql_alt);
return(NULL);
}
int MySQLDB::exec_sql_query(lua_State *vm, char *sql, bool limitRows) {
MYSQL_RES *result;
MYSQL_ROW row;
char *fields[MYSQL_MAX_NUM_FIELDS] = { NULL };
int num_fields, rc, num = 0;
if(!db_operational)
return(-2);
if(m) m->lock(__FILE__, __LINE__);
if((rc = mysql_query(&mysql, sql)) != 0) {
rc = mysql_errno(&mysql);
ntop->getTrace()->traceEvent(TRACE_ERROR, "MySQL error: [%s][%d]",
get_last_db_error(&mysql), rc);
mysql_close(&mysql);
if(m) m->unlock(__FILE__, __LINE__);
connectToDB(&mysql, true);
if(!db_operational)
return(-2);
if(m) m->lock(__FILE__, __LINE__);
rc = mysql_query(&mysql, sql);
}
if((rc != 0) || ((result = mysql_store_result(&mysql)) == NULL)) {
lua_pushstring(vm, get_last_db_error(&mysql));
if(m) m->unlock(__FILE__, __LINE__);
return(rc);
}
num_fields = min_val(mysql_num_fields(result), MYSQL_MAX_NUM_FIELDS);
lua_newtable(vm);
num = 0;
while((row = mysql_fetch_row(result))) {
lua_newtable(vm);
if(num == 0) {
for(int i = 0; i < num_fields; i++) {
MYSQL_FIELD *field = mysql_fetch_field(result);
fields[i] = field->name;
}
}
for(int i = 0; i < num_fields; i++)
lua_push_str_table_entry(vm, (const char*)fields[i], row[i] ? row[i] : (char*)"");
lua_pushnumber(vm, ++num);
lua_insert(vm, -2);
lua_settable(vm, -3);
if(limitRows && num >= MYSQL_MAX_NUM_ROWS) break;
}
mysql_free_result(result);
if(m) m->unlock(__FILE__, __LINE__);
return(0);
}
int main(void){
/************************** Initializing variables ***************************/
int i, j; //Misc counter
int workforcePercentage; // Total amount of the population that is
// workforce.
int growthPercentage; // Total amount of population growth
float taxCollection; // Money Collected from Taxes.
float productionCost; // Cost to produce resources
int consumed; // Amount of the Good Consumed.
float goodReserve; // Amount of a Good to Reserve
float fluxFactor; // Fluctuation Factor
float fluxPrice; // Good price based on the fluctuation
systemDemographics SystemData; // System Demographics
production Products; // Produced Product data
/******************************* Main Program ********************************/
// Seeding Randomizer
srand(time(NULL));
// Loading Settings
printf("Loading Config.ini....");
if(loadConfig()){
exit(0);
}
printf("Done.\n");
// Connecting to DB
printf("Connecting to DB...");
if(connectToDB()){
exit(0);
}
printf("Done.\n");
// Loading Goods
printf("Loading Goods...");
loadGoods();
printf("Done.\n");
// Setting Good Rates
productionRate = malloc(numberOfGoods * sizeof(goodRate));
consumptionRate = malloc(numberOfGoods * sizeof(goodRate));
productionFactor = malloc(numberOfGoods * sizeof(goodRate));
// Loading Rates
printf("Loading Consumption and Production Rates...");
if(loadRates()){
exit(0);
}
printf("Done.\n");
// Loading Markets
printf("Loading Markets...");
loadDestinations();
if(loadMarkets()){
exit(0);
}
printf("Done.\n");
// Cycling through each market
for(i = 0; i < numberOfDestinations; i++){
// Loading Demographic Data
SystemData = loadSystemDemographics(destinations[i].destinationID);
// Setting Growth
growthPercentage = ((rand() % 10) + 1) * ((rand() % 2) ? 1:-1);
SystemData.pop += round(SystemData.pop * ((float)growthPercentage/100));
// Setting Workforce
workforcePercentage = ((rand() % 25) + 1);
SystemData.workPop = round(SystemData.pop * ((float)workforcePercentage / 100));
// Collecting Taxes
taxCollection = (SystemData.workPop * SystemData.taxes);
SystemData.funds += (SystemData.workPop * taxCollection);
updateDemographics(SystemData);
//Producing/Consuming Goods
productionCost = 0;
for(j = 0; j < numberOfGoods; j++){
Products = produceGood(SystemData.systemID, goodsID[j], SystemData.workPop);
// Removing workers used to produce this good from work popultion
(SystemData.workPop -= Products.workers);
if(SystemData.workPop < 0){
SystemData.workPop = 0;
}
// Updating Total Production Cost for the System
productionCost += Products.cost;
// Calculating Consumption Amount of the Good
consumed = ((SystemData.pop * consumptionRateForGood(goodsID[j])) * (0 -1));
// Updating System's Stock
updateStock(SystemData.systemID, goodsID[j], consumed);
// Determining Amount to reserve
goodReserve = ((float)consumed * (0 - 1)) * TICKS_TO_RESERVE_FOR;
// Calculating Factor
fluxFactor = 1 - ((stockLevel(SystemData.systemID, goodsID[j]) - goodReserve)/goodReserve);
// Calculating New Sell Price
fluxPrice = fluxFactor * goodPrice(goodsID[j], SystemData.systemID);
// Did it cost more to produce then the current Sell Price
if(goodPrice(goodsID[j], SystemData.systemID) - calculateProductionCost(SystemData.systemID, goodsID[j]) < 0){
// Add the difference to the new Sell Price.
fluxPrice += (calculateProductionCost(SystemData.systemID, goodsID[j]) - goodPrice(goodsID[j], SystemData.systemID));
}
// Update New Sell Price
updatePrice(SystemData.systemID, goodsID[j], fluxPrice);
} // Good Cycle for()
// Update Taxes
if(taxCollection - productionCost < 0){
updateTaxes(SystemData.systemID, 1);
}
else {
updateTaxes(SystemData.systemID, -1);
}
}// Markets Cycle for()
// Clearing memory
printf("Clearing Memory...");
mysql_free_result(result);
iniparser_freedict(settings);
free(destinations);
free(markets);
printf("Done.\n");
return 0;
}
_Tt_db_client::_Tt_db_client (const _Tt_string &hostname, _Tt_db_results & status)
{
setTtDBDefaults();
connectToDB(hostname);
status = dbConnectionResults;
}
/**
* Creates a new ipfixDbWriter. Do not forget to call @c startipfixDbWriter() to begin writing to Database
* @return handle to use when calling @c destroyipfixDbWriter()
*/
IpfixDbWriter::IpfixDbWriter(const char* host, const char* db,
const char* user, const char* pw,
unsigned int port, uint16_t observationDomainId,
int maxStatements)
{
setSinkOwner("IpfixWriter");
/**Initialize structure members IpfixDbWriter*/
hostName = host;
dbName = db;
userName = user;
password = pw;
portNum = port;
socketName = 0;
flags = 0;
srcId.exporterAddress.len = 0;
srcId.observationDomainId = observationDomainId;
srcId.exporterPort = 0;
srcId.receiverPort = 0;
srcId.protocol = 0;
srcId.fileDescriptor = 0;
/**Initialize table cache*/
cache.countBuffTable = 0;
cache.countExpTable = 0;
int i ;
for(i = 0; i < MAX_TABLE; i++) {
cache.tableBuffer[i].startTableTime = 0;
cache.tableBuffer[i].endTableTime = 0;
cache.tableBuffer[i].TableName[0] = '\0';
}
for(i = 0; i < MAX_EXP_TABLE; i++) {
cache.exporterBuffer[i].Id = 0;
cache.exporterBuffer[i].observationDomainId = 0;
cache.exporterBuffer[i].expIp = 0;
}
/**count columns*/
numberOfColumns = 0;
for(i=0; identify[i].cname!=0; i++)
numberOfColumns++;
/**Initialize structure members Statement*/
statements.statemBuffer = (char**)malloc(sizeof(char**)*maxStatements);
statements.maxStatements = maxStatements;
statements.statemReceived = 0;
for( i = 0; i != statements.maxStatements; i++) {
statements.statemBuffer[i] = (char*) malloc((STARTLEN+(numberOfColumns * INS_WIDTH)) * sizeof(char));
statements.statemBuffer[i][0] = '\0';
}
statements.lockTables = (char**)malloc(sizeof(char**)*maxStatements);
statements.maxLocks = maxStatements; // worst case: every entry in another table
for( i = 0; i != statements.maxLocks; i++) {
statements.lockTables[i] = (char*) malloc(TABLE_WIDTH * sizeof(char));
statements.lockTables[i][0] = '\0';
}
connectToDB();
return;
//out:
// THROWEXCEPTION("IpfixDbWriter creation failed");
// return;
}
MySQLDB::MySQLDB(NetworkInterface *_iface) : DB(_iface) {
char sql[2048];
if(connectToDB(&mysql, false) == false)
return;
if(iface) {
/* 1 - Create database if missing */
snprintf(sql, sizeof(sql), "CREATE DATABASE IF NOT EXISTS %s", ntop->getPrefs()->get_mysql_dbname());
if(exec_sql_query(&mysql, sql, true) != 0) {
ntop->getTrace()->traceEvent(TRACE_ERROR, "MySQL error: %s\n", get_last_db_error(&mysql));
return;
}
if(mysql_select_db(&mysql, ntop->getPrefs()->get_mysql_dbname())) {
ntop->getTrace()->traceEvent(TRACE_ERROR, "MySQL error: %s\n", get_last_db_error(&mysql));
return;
}
/* 2.1 - Create table if missing [IPv6] */
snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS `%sv6` ("
"`idx` int(11) NOT NULL AUTO_INCREMENT,"
"`VLAN_ID` smallint(5) unsigned DEFAULT NULL,"
"`L7_PROTO` smallint(5) unsigned DEFAULT NULL,"
"`IP_SRC_ADDR` varchar(48) DEFAULT NULL,"
"`L4_SRC_PORT` smallint(5) unsigned DEFAULT NULL,"
"`IP_DST_ADDR` varchar(48) DEFAULT NULL,"
"`L4_DST_PORT` smallint(5) unsigned DEFAULT NULL,"
"`PROTOCOL` tinyint(3) unsigned DEFAULT NULL,"
"`BYTES` int(10) unsigned DEFAULT NULL,"
"`PACKETS` int(10) unsigned DEFAULT NULL,"
"`FIRST_SWITCHED` int(10) unsigned DEFAULT NULL,"
"`LAST_SWITCHED` int(10) unsigned DEFAULT NULL,"
"`INFO` varchar(255) DEFAULT NULL,"
"`JSON` blob,"
#ifdef NTOPNG_PRO
"`PROFILE` varchar(255) DEFAULT NULL,"
#endif
"`NTOPNG_INSTANCE_NAME` varchar(256) DEFAULT NULL,"
"`INTERFACE` varchar(64) DEFAULT NULL,"
"KEY `idx` (`idx`,`IP_SRC_ADDR`,`IP_DST_ADDR`,`FIRST_SWITCHED`,`LAST_SWITCHED`,`INFO`(200)),"
#ifdef NTOPNG_PRO
"KEY `ix_flowsv6_4_profile` (`PROFILE`),"
#endif
"KEY `ix_flowsv6_4_ntopng_instance_name` (`NTOPNG_INSTANCE_NAME`(255)),"
"KEY `ix_flowsv6_4_ntopng_interface` (`INTERFACE`)"
") ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8"
"/*!50100 PARTITION BY HASH (`FIRST_SWITCHED`)"
"PARTITIONS 32 */",
ntop->getPrefs()->get_mysql_tablename());
if(exec_sql_query(&mysql, sql, true) != 0) {
ntop->getTrace()->traceEvent(TRACE_ERROR, "MySQL error: %s\n", get_last_db_error(&mysql));
return;
}
/* 2.2 - Create table if missing [IPv4] */
snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS `%sv4` ("
"`idx` int(11) NOT NULL AUTO_INCREMENT,"
"`VLAN_ID` smallint(5) unsigned DEFAULT NULL,"
"`L7_PROTO` smallint(5) unsigned DEFAULT NULL,"
"`IP_SRC_ADDR` int(10) unsigned DEFAULT NULL,"
"`L4_SRC_PORT` smallint(5) unsigned DEFAULT NULL,"
"`IP_DST_ADDR` int(10) unsigned DEFAULT NULL,"
"`L4_DST_PORT` smallint(5) unsigned DEFAULT NULL,"
"`PROTOCOL` tinyint(3) unsigned DEFAULT NULL,"
"`BYTES` int(10) unsigned DEFAULT NULL,"
"`PACKETS` int(10) unsigned DEFAULT NULL,"
"`FIRST_SWITCHED` int(10) unsigned DEFAULT NULL,"
"`LAST_SWITCHED` int(10) unsigned DEFAULT NULL,"
"`INFO` varchar(255) DEFAULT NULL,"
"`JSON` blob,"
#ifdef NTOPNG_PRO
"`PROFILE` varchar(255) DEFAULT NULL,"
#endif
"`NTOPNG_INSTANCE_NAME` varchar(256) DEFAULT NULL,"
"`INTERFACE` varchar(64) DEFAULT NULL,"
"KEY `idx` (`idx`,`IP_SRC_ADDR`,`IP_DST_ADDR`,`FIRST_SWITCHED`,`LAST_SWITCHED`,`INFO`(200)),"
#ifdef NTOPNG_PRO
"KEY `ix_flowsv4_4_profile` (`PROFILE`),"
#endif
"KEY `ix_flowsv4_4_ntopng_instance_name` (`NTOPNG_INSTANCE_NAME`(255)),"
"KEY `ix_flowsv4_4_ntopng_interface` (`INTERFACE`)"
") ENGINE=InnoDB AUTO_INCREMENT=520 DEFAULT CHARSET=utf8"
"/*!50100 PARTITION BY HASH (`FIRST_SWITCHED`)"
"PARTITIONS 32 */",
ntop->getPrefs()->get_mysql_tablename());
if(exec_sql_query(&mysql, sql, true) != 0) {
ntop->getTrace()->traceEvent(TRACE_ERROR, "MySQL error: %s\n", get_last_db_error(&mysql));
return;
}
// the remainder of this method has the purpose of MIGRATING old table structures to
// the most recent one.
// FIRST: we adapt old table structures to the new schema using alter tables
/* Add fields if not present */
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv4_%u` ADD `INFO` varchar(255)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv6_%u` ADD `INFO` varchar(255)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
#ifdef NTOPNG_PRO
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv4_%u` "
"ADD `PROFILE` varchar(255) DEFAULT NULL,"
"ADD INDEX `ix_%sv4_%u_profile` (PROFILE)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv6_%u` "
"ADD `PROFILE` varchar(255) DEFAULT NULL,"
"ADD INDEX `ix_%sv6_%u_profile` (PROFILE)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
#endif
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv4_%u` "
"ADD `NTOPNG_INSTANCE_NAME` varchar(256) DEFAULT NULL,"
"ADD INDEX `ix_%sv4_%u_ntopng_instance_name` (NTOPNG_INSTANCE_NAME)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv6_%u` "
"ADD `NTOPNG_INSTANCE_NAME` varchar(256) DEFAULT NULL,"
"ADD INDEX `ix_%sv6_%u_ntopng_instance_name` (NTOPNG_INSTANCE_NAME)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv4_%u` "
"ADD `INTERFACE` varchar(64) DEFAULT NULL,"
"ADD INDEX `ix_%sv4_%u_ntopng_interface` (INTERFACE)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv6_%u` "
"ADD `INTERFACE` varchar(64) DEFAULT NULL,"
"ADD INDEX `ix_%sv6_%u_ntopng_interface` (INTERFACE)",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
// SECOND: we trasfer old table contents into the new schema
snprintf(sql, sizeof(sql), "INSERT IGNORE INTO `%sv4` "
"SELECT * FROM `%sv4_%u`",
ntop->getPrefs()->get_mysql_tablename(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "INSERT IGNORE INTO `%sv6` "
"SELECT * FROM `%sv6_%u`",
ntop->getPrefs()->get_mysql_tablename(),
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
// THIRD: drop old tables (their contents have been transferred)
}
snprintf(sql, sizeof(sql), "DROP TABLE IF EXISTS `%sv4_%u` ",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "DROP TABLE IF EXISTS `%sv6_%u` ",
ntop->getPrefs()->get_mysql_tablename(), iface->get_id());
exec_sql_query(&mysql, sql, true, true);
// FOURTH: add extra indices to speedup queries
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv4` "
"ADD INDEX `ix_%sv4_ntopng_first_src_dst` (FIRST_SWITCHED, IP_SRC_ADDR, IP_DST_ADDR)",
ntop->getPrefs()->get_mysql_tablename(),
ntop->getPrefs()->get_mysql_tablename());
exec_sql_query(&mysql, sql, true, true);
snprintf(sql, sizeof(sql), "ALTER TABLE `%sv6` "
"ADD INDEX `ix_%sv6_ntopng_first_src_dst` (FIRST_SWITCHED, IP_SRC_ADDR, IP_DST_ADDR)",
ntop->getPrefs()->get_mysql_tablename(),
ntop->getPrefs()->get_mysql_tablename());
exec_sql_query(&mysql, sql, true, true);
pthread_create(&queryThreadLoop, NULL, ::queryLoop, (void*)this);
}
