std::string Mysql::getOne(std::string sql){
Query query = _con.query(sql);
std::string str = "";
UseQueryResult res = query.use();
mysqlpp::Row row = res.fetch_row();
str = (std::string)row[0];
return str;
}
void
MySQLStorage::init (void)
{
vector<pair<string, string> > fields;
fields.push_back (make_pair ("id", "int(10) unsigned NOT NULL auto_increment"));
fields.push_back (make_pair ("eventId", "varchar(255) collate latin1_bin NOT NULL default ''"));
fields.push_back (make_pair ("dataId", "varchar(255) collate latin1_bin NOT NULL default ''"));
fields.push_back (make_pair ("message", "varchar(255) collate latin1_bin NOT NULL default ''"));
fields.push_back (make_pair ("errorCode", "int(10) unsigned NOT NULL default 0"));
fields.push_back (make_pair ("datastreamId", "varchar(255) collate latin1_bin NOT NULL default ''"));
fields.push_back (make_pair ("datastreamName", "varchar(255) collate latin1_bin NOT NULL default ''"));
fields.push_back (make_pair ("hostname", "varchar(255) collate latin1_bin NOT NULL default ''"));
fields.push_back (make_pair ("logLevel", "int(10) unsigned NOT NULL default 0")); // could be enum?
fields.push_back (make_pair ("pid", "int(10) unsigned NOT NULL default 0"));
fields.push_back (make_pair ("time", "DATETIME NOT NULL"));
fields.push_back (make_pair ("ms", "int(10) unsigned NOT NULL default 0"));
string table (MySQLStorage::defaultDbTable);
try {
this->dbCheck();
Query query (this->conn.query());
// Create the database if necessary
query << "CREATE TABLE IF NOT EXISTS `" << table << "` "
<< "(";
bool first (true);
auto iField (fields.begin()), fEnd (fields.end());
while (iField != fEnd) {
if (!first) {
query << ", ";
} else {
first = false;
}
query << "`" << iField->first << "` " << iField->second;
++iField;
}
query << ", PRIMARY KEY (`id`)"
<< ") "
<< "ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin; ";
if (query.exec() == false) {
stringstream ee;
ee << "Query failed, error: '" << query.error() << "'";
throw runtime_error (ee.str());
}
// Check that all of the columns exist
iField = fields.begin();
while (iField != fEnd) {
query.reset();
query << "SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'dt'"
<< " AND TABLE_NAME = '" << table << "' AND COLUMN_NAME = '" << iField->first << "';";
UseQueryResult res (query.use());
if (!res) {
stringstream ee;
ee << "Query failed, error: '" << query.error() << "'";
throw runtime_error (ee.str());
}
if (Row row = res.fetch_row()) {
} else {
// Need to add the column
//DBG ("Couldn't find the column '" << iField->first << "'");
query.reset();
query << "ALTER TABLE `" << table << "` ADD `" << iField->first << "` " << iField->second << ";";
//DBG (name << " ALTER query string: " << query.str());
if (query.exec() == false) {
stringstream ee;
ee << "Query failed, error: '" << query.error() << "'";
throw runtime_error (ee.str());
}
}
++iField;
}
}
catch (mysqlpp::BadQuery e) { this->handleBadQuery (e); }
catch (mysqlpp::Exception e) { this->handleException (e); }
}
void TestDatabase::testQuery() {
DatabaseAPI::connectToDatabase();
Query query = DatabaseAPI::getQuery();
try { // No errors on CREATE TABLE
query << "CREATE TABLE testTable ( num int, str varchar(4) )";
query.execute();
++testSuccesses;
}
catch (Exception e) {
++testFailures;
}
try { // No errors on INSERT
query.clear();
query << "INSERT INTO testTable VALUES (" << mysqlpp::quote << 1 << ", " << mysqlpp::quote << "one" << ")";
query.execute();
++testSuccesses;
}
catch (Exception e) {
++testFailures;
}
try { // No errors on SELECT
query.clear();
query << "SELECT * FROM testTable";
UseQueryResult result = query.use();
Row row;
if (row = result.fetch_row()) { // Row was inserted
++testSuccesses;
if (row[0] == "1") { // Proper int inserted
++testSuccesses;
}
else ++testFailures;
if (row[1] == "one") { // Proper string inserted
++testSuccesses;
}
else ++testFailures;
}
else ++testFailures;
}
catch (Exception e) {
++testFailures;
std::cout << "EXC" << std::endl;
}
try { // No errors on DROP TABLE
query.clear();
query << "DROP TABLE testTable";
query.execute();
++testSuccesses;
}
catch (Exception e) {
++testFailures;
}
std::cout << "Query tests complete. Successes: " << testSuccesses << "\tFailures: " << testFailures << std::endl;
resetTestCounts();
}
