mdd/lib/src/ModuleSQL.cpp

#include "ModuleSQL.h"
#include <boost/algorithm/string.hpp>

namespace mdd {
	void ModuleSQL::erase_keyword(std::string& str, std::string key) {
		while (true) {
			int start = (int)str.find(key);
			if (start != -1) {
				str.erase(start, key.length());
			}
			else {
				return;
			}
		}
	}

	ModuleSQL::ModuleSQL(std::string dbname) {
		int rc = sqlite3_open(dbname.c_str(), &_db);
		if (rc) {
			fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(_db));
			return;
		}
		else {
			fprintf(stderr, "Opened database successfully\n");
		}
		std::string sql;
		sqlite3_stmt* res;
		int step;


		// Create SQL statement
		sql = "SELECT tbl_name  "\
			"FROM   sqlite_master "\
			"WHERE type = 'table' ";

		// Execute SQL statement
		rc = sqlite3_prepare_v2(_db, sql.c_str(), -1, &res, 0);

		if (rc != SQLITE_OK) {
			fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(_db));
			sqlite3_close(_db);
		}
		else {
			fprintf(stdout, "Operation done successfully\n");
		}

		step = sqlite3_step(res);

		if (step == SQLITE_ROW) {
			int nCol = sqlite3_column_count(res);
			for (size_t i = 0; i < nCol; i++)
			{
				_tbname = (char*)sqlite3_column_text(res, i);
			}

		}
		sqlite3_finalize(res);


		// Create SQL statement
		sql = "SELECT sql "\
			"FROM   sqlite_master "\
			"WHERE type = 'table' AND tbl_name = '" + _tbname + "'";
		//std::cout << sql << std::endl;
		// Execute SQL statement
		rc = sqlite3_prepare_v2(_db, sql.c_str(), -1, &res, 0);

		if (rc != SQLITE_OK) {
			fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(_db));
			sqlite3_close(_db);
		}
		else {
			fprintf(stdout, "Operation done successfully\n");
		}

		step = sqlite3_step(res);

		if (step == SQLITE_ROW) {
			std::string sql_res = (char*)sqlite3_column_text(res, 0);
			int start = sql_res.find("(");
			int end = sql_res.find(")");
			sql_res = sql_res.substr(++start, (size_t)(--end) - start);

			erase_keyword(sql_res, "KEY");
			erase_keyword(sql_res, "PRIMARY");
			erase_keyword(sql_res, "AUTOINCREMENT");
			erase_keyword(sql_res, "NOT NULL");

			std::string::iterator new_end = std::unique(sql_res.begin(), sql_res.end(), [](char lhs, char rhs)->bool { return (lhs == rhs) && (lhs == ' '); });
			sql_res.erase(new_end, sql_res.end());

			_content.clear();
			while(true){
				end = sql_res.find(" ,");
				if (end == -1)
				{
					end = sql_res.length();
				}
				std::string sub_str = sql_res.substr(0, end);
				int mid = sub_str.find(" ");
				_entity enti;
				enti.key = sub_str.substr(0, mid);
				enti.type = sub_str.substr(mid + 1, sub_str.length() - (mid + 1));
				_content.push_back(enti);
				//std::cout << "'" << enti.key << "':  '" << enti.type << "'\n";
				if (end != sql_res.length())
				{
					sql_res.erase(0, end + 2);
				}
				else {
					break;
				}
			}

		}
		sqlite3_finalize(res);

		addInput(_content[0].key, {0});
		for (auto& cont :_content)
		{
			addOutput(cont.key, {1});
		}
		setType("SQL");
	}

	state ModuleSQL::update() {
		int rc;
		std::string sql;
		sqlite3_stmt* res;
		int step;
		// Create SQL statement
		sql = "SELECT * "\
			"FROM " + _tbname + " "\
			"WHERE " + _content[0].key + " = " + std::to_string(getInput(0)->getValue()[0]);

		std::cout << sql << std::endl;

		// Execute SQL statement
		rc = sqlite3_prepare_v2(_db, sql.c_str(), -1, &res, 0);

		if (rc != SQLITE_OK) {
			fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(_db));
			sqlite3_close(_db);
		}
		else {
			fprintf(stdout, "Operation done successfully\n");
		}

		step = sqlite3_step(res);
		state state = state::UNCHANGED;
		if (step == SQLITE_ROW) {
			int nCol = sqlite3_column_count(res);
			std::string parse_str = "{\"" + _tbname + "\" : {";
			for (size_t i = 0; i < nCol; i++)
			{
				//json val = (char*)sqlite3_column_text(res, i);
				getOutput(i)->setValue({ std::atof((char*)sqlite3_column_text(res, i)) });
			}
		}
		sqlite3_finalize(res);

		return state;
	}

	ModuleSQL::~ModuleSQL() {
		sqlite3_close(_db);
	}
}