mdd/lib/src/ModuleSQL.cpp

#include "ModuleBase.h"
#include <iostream>
#include <string>
#include <sqlite3.h>
#include <boost/algorithm/string.hpp>

namespace mdd {
	class ModuleSQL : public ModuleBase {
	private:
		std::string _dbname;
		sqlite3* _db;
		std::string _tbname;
		void erase_keyword(std::string& str, std::string key);
		struct _entity {
			std::string key;
			std::string type;
		};
		std::vector<_entity> _content;

	public:
		ModuleSQL();
		~ModuleSQL();
		bool configure(const std::string& config) override;
		std::string getConfiguration() override;
		state update() override;
	};

	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()
	: ModuleBase(R"JSON(
        [{
            "name":"database",
            "value":""
        }])JSON")
	{
		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);
	}

	bool ModuleSQL::configure(const std::string& config) {
		json config_parsed = json::parse(config);
		
		for (size_t i = 0; i < config_parsed.size(); i++)
		{
			if (config_parsed[i].contains("name"))
			{
				if (config_parsed[i]["name"].get<std::string>() == "database")
				{
					_dbname = config_parsed[i]["value"].get<std::string>();
				}
			}
		}
		
		int rc = sqlite3_open(_dbname.c_str(), &_db);
		if (rc) {
			fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(_db));
			return false;
		}
		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);

		inputs.push_back(std::make_shared<Input>(this, _content[0].key, inputs.size(), std::vector<double>{0}));
		for (auto& cont : _content)
		{
			outputs.push_back(std::make_shared<Output>(this, cont.key, outputs.size(), std::vector<double>{1}));
		}
		
		return true;
	}

	std::string ModuleSQL::getConfiguration() {
		json ret = json::parse(ModuleBase::getConfiguration());
		for (size_t i = 0; i < ret.size(); i++)
		{
			if (ret[i].contains("name"))
			{
				if (ret[i]["name"].get<std::string>() == "database")
				{
					ret[i]["value"] = _dbname;
				}
			}
		}
		return ret.dump();
	}
}