Pull table schemas from SQL on startup so we can alter the schemas if we need to, fixed sqlite to work again

5 files changed, 99 insertions, 64 deletions

diff --git a/modules/database/db_sql.cpp b/modules/database/db_sql.cpp
index da891e587..9702bd400 100644
--- a/modules/database/db_sql.cpp
+++ b/modules/database/db_sql.cpp
@@ -80,33 +80,6 @@ class DBSQL : public Module, public Pipe
 			this->sql->RunQuery(q);
 	}
 
-	SQLQuery BuildInsert(const Anope::string &table, unsigned int id, const Serialize::Data &data)
-	{
-		if (this->sql)
-		{
-			std::vector<SQLQuery> create_queries = this->sql->CreateTable(table, data);
-			for (unsigned i = 0; i < create_queries.size(); ++i)
-				this->RunBackground(create_queries[i]);
-		}
-
-		Anope::string query_text = "INSERT INTO `" + table + "` (`id`";
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query_text += ",`" + it->first + "`";
-		query_text += ") VALUES (" + stringify(id);
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query_text += ",@" + it->first + "@";
-		query_text += ") ON DUPLICATE KEY UPDATE ";
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query_text += "`" + it->first + "`=VALUES(`" + it->first + "`),";
-		query_text.erase(query_text.end() - 1);
-
-		SQLQuery query(query_text);
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query.setValue(it->first, it->second.astr());
-
-		return query;
-	}
-	
  public:
 	DBSQL(const Anope::string &modname, const Anope::string &creator) : Module(modname, creator, DATABASE), sql("", ""), sqlinterface(this)
 	{
@@ -124,13 +97,20 @@ class DBSQL : public Module, public Pipe
 		{
 			dynamic_reference<Serializable> obj = *it;
 
-			if (obj)
+			if (obj && this->sql)
 			{
 				if (obj->IsCached())
 					continue;
 				obj->UpdateCache();
 
-				SQLQuery insert = this->BuildInsert(this->prefix + obj->serialize_name(), obj->id, obj->serialize());
+				const Serialize::Data &data = obj->serialize();
+
+				std::vector<SQLQuery> create = this->sql->CreateTable(this->prefix + obj->serialize_name(), data);
+				for (unsigned i = 0; i < create.size(); ++i)
+					this->RunBackground(create[i]);
+
+
+				SQLQuery insert = this->sql->BuildInsert(this->prefix + obj->serialize_name(), obj->id, data);
 				this->RunBackground(insert, new ResultSQLSQLInterface(this, obj));
 			}
 		}
diff --git a/modules/database/db_sql_live.cpp b/modules/database/db_sql_live.cpp
index 32cc2cda6..5ab04618e 100644
--- a/modules/database/db_sql_live.cpp
+++ b/modules/database/db_sql_live.cpp
@@ -67,33 +67,6 @@ class DBMySQL : public Module, public Pipe
 		throw SQLException("No SQL!");
 	}
 
-	SQLQuery BuildInsert(const Anope::string &table, unsigned int id, const Serialize::Data &data)
-	{
-		if (this->SQL)
-		{
-			std::vector<SQLQuery> create_queries = this->SQL->CreateTable(table, data);
-			for (unsigned i = 0; i < create_queries.size(); ++i)
-				this->RunQuery(create_queries[i]);
-		}
-
-		Anope::string query_text = "INSERT INTO `" + table + "` (`id`";
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query_text += ",`" + it->first + "`";
-		query_text += ") VALUES (" + stringify(id);
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query_text += ",@" + it->first + "@";
-		query_text += ") ON DUPLICATE KEY UPDATE ";
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query_text += "`" + it->first + "`=VALUES(`" + it->first + "`),";
-		query_text.erase(query_text.end() - 1);
-
-		SQLQuery query(query_text);
-		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
-			query.setValue(it->first, it->second.astr());
-
-		return query;
-	}
-
  public:
 	DBMySQL(const Anope::string &modname, const Anope::string &creator) : Module(modname, creator, DATABASE), SQL("", "")
 	{
@@ -116,7 +89,7 @@ class DBMySQL : public Module, public Pipe
 		{
 			dynamic_reference<Serializable> obj = *it;
 
-			if (obj)
+			if (obj && this->SQL)
 			{
 				if (obj->IsCached())
 					continue;
@@ -127,7 +100,13 @@ class DBMySQL : public Module, public Pipe
 					continue;
 				working_objects.insert(obj);
 
-				SQLResult res = this->RunQueryResult(BuildInsert(this->prefix + obj->serialize_name(), obj->id, obj->serialize()));
+				const Serialize::Data &data = obj->serialize();
+
+				std::vector<SQLQuery> create = this->SQL->CreateTable(this->prefix + obj->serialize_name(), data);
+				for (unsigned i = 0; i < create.size(); ++i)
+					this->RunQueryResult(create[i]);
+
+				SQLResult res = this->RunQueryResult(this->SQL->BuildInsert(this->prefix + obj->serialize_name(), obj->id, data));
 				if (res.GetID() > 0)
 					obj->id = res.GetID();
 				SerializeType *stype = SerializeType::Find(obj->serialize_name());
diff --git a/modules/extra/m_mysql.cpp b/modules/extra/m_mysql.cpp
index 8a7c21e64..2edf21745 100644
--- a/modules/extra/m_mysql.cpp
+++ b/modules/extra/m_mysql.cpp
@@ -126,6 +126,8 @@ class MySQLService : public SQLProvider
 
 	std::vector<SQLQuery> CreateTable(const Anope::string &table, const Serialize::Data &data) anope_override;
 
+	SQLQuery BuildInsert(const Anope::string &table, unsigned int id, const Serialize::Data &data);
+
 	SQLQuery GetTables(const Anope::string &prefix) anope_override;
 
 	void Connect();
@@ -351,7 +353,21 @@ std::vector<SQLQuery> MySQLService::CreateTable(const Anope::string &table, cons
 
 	if (known_cols.empty())
 	{
-		Anope::string query_text = "CREATE TABLE IF NOT EXISTS `" + table + "` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
+		Log(LOG_DEBUG) << "m_mysql: Fetching columns for " << table;
+
+		SQLResult columns = this->RunQuery("SHOW COLUMNS FROM `" + table + "`");
+		for (int i = 0; i < columns.Rows(); ++i)
+		{
+			const Anope::string &column = columns.Get(i, "Field");
+
+			Log(LOG_DEBUG) << "m_mysql: Column #" << i << " for " << table << ": " << column;
+			known_cols.insert(column);
+		}
+	}
+
+	if (known_cols.empty())
+	{
+		Anope::string query_text = "CREATE TABLE `" + table + "` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
 			" `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
 		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
 		{
@@ -390,6 +406,26 @@ std::vector<SQLQuery> MySQLService::CreateTable(const Anope::string &table, cons
 	return queries;
 }
 
+SQLQuery MySQLService::BuildInsert(const Anope::string &table, unsigned int id, const Serialize::Data &data)
+{
+	Anope::string query_text = "INSERT INTO `" + table + "` (`id`";
+	for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
+		query_text += ",`" + it->first + "`";
+	query_text += ") VALUES (" + stringify(id);
+	for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
+		query_text += ",@" + it->first + "@";
+	query_text += ") ON DUPLICATE KEY UPDATE ";
+	for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
+		query_text += "`" + it->first + "`=VALUES(`" + it->first + "`),";
+	query_text.erase(query_text.end() - 1);
+
+	SQLQuery query(query_text);
+	for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
+		query.setValue(it->first, it->second.astr());
+	
+	return query;
+}
+
 SQLQuery MySQLService::GetTables(const Anope::string &prefix)
 {
 	return SQLQuery("SHOW TABLES LIKE '" + prefix + "%';");
diff --git a/modules/extra/m_sqlite.cpp b/modules/extra/m_sqlite.cpp
index 5102c003b..fff3e67d1 100644
--- a/modules/extra/m_sqlite.cpp
+++ b/modules/extra/m_sqlite.cpp
@@ -48,6 +48,8 @@ class SQLiteService : public SQLProvider
 
 	std::vector<SQLQuery> CreateTable(const Anope::string &table, const Serialize::Data &data) anope_override;
 
+	SQLQuery BuildInsert(const Anope::string &table, unsigned int id, const Serialize::Data &data);
+
 	SQLQuery GetTables(const Anope::string &prefix);
 
 	Anope::string BuildQuery(const SQLQuery &q);
@@ -187,9 +189,23 @@ std::vector<SQLQuery> SQLiteService::CreateTable(const Anope::string &table, con
 	std::vector<SQLQuery> queries;
 	std::set<Anope::string> &known_cols = this->active_schema[table];
 
-	if (active_schema.empty())
+	if (known_cols.empty())
 	{
-		Anope::string query_text = "CREATE TABLE IF NOT EXISTS `" + table + "` (id INTEGER PRIMARY KEY, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ";
+		Log(LOG_DEBUG) << "m_sqlite: Fetching columns for " << table;
+
+		SQLResult columns = this->RunQuery("PRAGMA table_info(" + table + ")");
+		for (int i = 0; i < columns.Rows(); ++i)
+		{
+			const Anope::string &column = columns.Get(i, "name");
+
+			Log(LOG_DEBUG) << "m_sqlite: Column #" << i << " for " << table << ": " << column;
+			known_cols.insert(column);
+		}
+	}
+
+	if (known_cols.empty())
+	{
+		Anope::string query_text = "CREATE TABLE `" + table + "` (`id` INTEGER PRIMARY KEY, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP";
 
 		for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
 		{
@@ -202,15 +218,14 @@ std::vector<SQLQuery> SQLiteService::CreateTable(const Anope::string &table, con
 				query_text += "text";
 		}
 
-		query_text.erase(query_text.end() - 1);
 		query_text += ")";
 
 		queries.push_back(query_text);
 
-		query_text = "CREATE UNIQUE INDEX IF NOT EXISTS `id_idx` ON `" + table + "` (`id`)";
+		query_text = "CREATE UNIQUE INDEX `" + table + "_id_idx` ON `" + table + "` (`id`)";
 		queries.push_back(query_text);
 
-		query_text = "CREATE INDEX IF NOT EXISTS `timestamp_idx` ON `" + table + "` (`timestamp`)";
+		query_text = "CREATE INDEX `" + table + "_timestamp_idx` ON `" + table + "` (`timestamp`)";
 		queries.push_back(query_text);
 
 		query_text = "CREATE TRIGGER `" + table + "_trigger` AFTER UPDATE ON `" + table + "` FOR EACH ROW BEGIN UPDATE `" + table + "` SET `timestamp` = CURRENT_TIMESTAMP WHERE `id` = `old.id`; end;";
@@ -236,6 +251,29 @@ std::vector<SQLQuery> SQLiteService::CreateTable(const Anope::string &table, con
 	return queries;
 }
 
+SQLQuery SQLiteService::BuildInsert(const Anope::string &table, unsigned int id, const Serialize::Data &data)
+{
+	Anope::string query_text = "REPLACE INTO `" + table + "` (";
+	if (id > 0)
+		query_text += "`id`,";
+	for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
+		query_text += "`" + it->first + "`,";
+	query_text.erase(query_text.length() - 1);
+	query_text += ") VALUES (";
+	if (id > 0)
+		query_text += stringify(id) + ",";
+	for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
+		query_text += "@" + it->first + "@,";
+	query_text.erase(query_text.length() - 1);
+	query_text += ")";
+
+	SQLQuery query(query_text);
+	for (Serialize::Data::const_iterator it = data.begin(), it_end = data.end(); it != it_end; ++it)
+		query.setValue(it->first, it->second.astr());
+	
+	return query;
+}
+
 SQLQuery SQLiteService::GetTables(const Anope::string &prefix)
 {
 	return SQLQuery("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '" + prefix + "%';");
diff --git a/modules/extra/sql.h b/modules/extra/sql.h
index 2e2e1840c..03c3c7694 100644
--- a/modules/extra/sql.h
+++ b/modules/extra/sql.h
@@ -129,6 +129,8 @@ class SQLProvider : public Service
 
 	virtual std::vector<SQLQuery> CreateTable(const Anope::string &table, const Serialize::Data &data) = 0;
 
+	virtual SQLQuery BuildInsert(const Anope::string &table, unsigned int id, const Serialize::Data &data) = 0;
+
 	virtual SQLQuery GetTables(const Anope::string &prefix) = 0;
 };