Automatically determine SQL column type from the field.

Also add more column types to ensure we are storing data in the
best format in the database.

2 files changed, 102 insertions, 26 deletions

diff --git a/modules/extra/mysql.cpp b/modules/extra/mysql.cpp
index 193427fdb..caa02d8f0 100644
--- a/modules/extra/mysql.cpp
+++ b/modules/extra/mysql.cpp
@@ -158,6 +158,29 @@ public:
 	Anope::string BuildQuery(const Query &q);
 
 	Anope::string FromUnixtime(time_t) override;
+
+	Anope::string GetColumnType(Serialize::DataType dt)
+	{
+		switch (dt)
+		{
+			case Serialize::DataType::BOOL:
+				return "TINYINT NOT NULL";
+
+			case Serialize::DataType::FLOAT:
+				return "DOUBLE PRECISION NOT NULL";
+
+			case Serialize::DataType::INT:
+				return "BIGINT NOT NULL";
+
+			case Serialize::DataType::TEXT:
+				return "TEXT";
+
+			case Serialize::DataType::UINT:
+				return "BIGINT UNSIGNED NOT NULL";
+		}
+
+		return "TEXT"; // Should never be reached
+	}
 };
 
 /** The SQL thread used to execute queries
@@ -410,17 +433,13 @@ std::vector<Query> MySQLService::CreateTable(const Anope::string &table, const D
 
 	if (known_cols.empty())
 	{
-		Anope::string query_text = "CREATE TABLE `" + table + "` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,"
+		Anope::string query_text = "CREATE TABLE `" + table + "` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,"
 			" `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
 		for (const auto &[column, _] : data.data)
 		{
 			known_cols.insert(column);
 
-			query_text += ", `" + column + "` ";
-			if (data.GetType(column) == Serialize::Data::DT_INT)
-				query_text += "int";
-			else
-				query_text += "text";
+			query_text += ", `" + column + "` " + GetColumnType(data.GetType(column));
 		}
 		query_text += ", PRIMARY KEY (`id`), KEY `timestamp_idx` (`timestamp`)) ROW_FORMAT=DYNAMIC";
 		queries.push_back(query_text);
@@ -434,11 +453,7 @@ std::vector<Query> MySQLService::CreateTable(const Anope::string &table, const D
 
 			known_cols.insert(column);
 
-			Anope::string query_text = "ALTER TABLE `" + table + "` ADD `" + column + "` ";
-			if (data.GetType(column) == Serialize::Data::DT_INT)
-				query_text += "int";
-			else
-				query_text += "text";
+			Anope::string query_text = "ALTER TABLE `" + table + "` ADD `" + column + "` " + GetColumnType(data.GetType(column));
 
 			queries.push_back(query_text);
 		}
@@ -474,11 +489,29 @@ Query MySQLService::BuildInsert(const Anope::string &table, unsigned int id, Dat
 		Anope::string buf;
 		*value >> buf;
 
-		bool escape = true;
-		if (buf.empty())
+		auto escape = true;
+		switch (data.GetType(field))
 		{
-			buf = "NULL";
-			escape = false;
+			case Serialize::DataType::BOOL:
+			case Serialize::DataType::FLOAT:
+			case Serialize::DataType::INT:
+			case Serialize::DataType::UINT:
+			{
+				if (buf.empty())
+					buf = "0";
+				escape = false;
+				break;
+			}
+
+			case Serialize::DataType::TEXT:
+			{
+				if (buf.empty())
+				{
+					buf = "NULL";
+					escape = false;
+				}
+				break;
+			}
 		}
 
 		query.SetValue(field, buf, escape);
diff --git a/modules/extra/sqlite.cpp b/modules/extra/sqlite.cpp
index 2454c838c..2c6ccc6db 100644
--- a/modules/extra/sqlite.cpp
+++ b/modules/extra/sqlite.cpp
@@ -68,6 +68,31 @@ public:
 	Anope::string BuildQuery(const Query &q);
 
 	Anope::string FromUnixtime(time_t) override;
+
+	Anope::string GetColumnType(Serialize::DataType dt)
+	{
+		switch (dt)
+		{
+			case Serialize::DataType::BOOL:
+				return "INTEGER";
+
+			case Serialize::DataType::FLOAT:
+				return "REAL";
+
+			case Serialize::DataType::INT:
+				return "INTEGER";
+
+			case Serialize::DataType::TEXT:
+				return "TEXT";
+
+			// SQLite lacks support for 64-bit unsigned integers so we have to
+			// store them as text columns instead.
+			case Serialize::DataType::UINT:
+				return "TEXT";
+		}
+
+		return "TEXT"; // Should never be reached
+	}
 };
 
 class ModuleSQLite final
@@ -237,11 +262,7 @@ std::vector<Query> SQLiteService::CreateTable(const Anope::string &table, const
 		{
 			known_cols.insert(column);
 
-			query_text += ", `" + column + "` ";
-			if (data.GetType(column) == Serialize::Data::DT_INT)
-				query_text += "int(11)";
-			else
-				query_text += "text";
+			query_text += ", `" + column + "` " + GetColumnType(data.GetType(column));
 		}
 
 		query_text += ")";
@@ -266,11 +287,7 @@ std::vector<Query> SQLiteService::CreateTable(const Anope::string &table, const
 
 			known_cols.insert(column);
 
-			Anope::string query_text = "ALTER TABLE `" + table + "` ADD `" + column + "` ";
-			if (data.GetType(column) == Serialize::Data::DT_INT)
-				query_text += "int(11)";
-			else
-				query_text += "text";
+			Anope::string query_text = "ALTER TABLE `" + table + "` ADD `" + column + "` " + GetColumnType(data.GetType(column));;
 
 			queries.push_back(query_text);
 		}
@@ -307,7 +324,33 @@ Query SQLiteService::BuildInsert(const Anope::string &table, unsigned int id, Da
 	{
 		Anope::string buf;
 		*value >> buf;
-		query.SetValue(field, buf);
+
+		auto escape = true;
+		switch (data.GetType(field))
+		{
+			case Serialize::DataType::BOOL:
+			case Serialize::DataType::FLOAT:
+			case Serialize::DataType::INT:
+			{
+				if (buf.empty())
+					buf = "0";
+				escape = false;
+				break;
+			}
+
+			case Serialize::DataType::TEXT:
+			case Serialize::DataType::UINT:
+			{
+				if (buf.empty())
+				{
+					buf = "NULL";
+					escape = false;
+				}
+				break;
+			}
+		}
+
+		query.SetValue(field, buf, escape);
 	}
 
 	return query;