From 42238034490fb5479d787bd1695750387d508200 Mon Sep 17 00:00:00 2001 From: Adam Date: Mon, 24 Nov 2014 14:27:23 -0500 Subject: Rewrite serializable to have field level granularity Represent serializable objects in a digraph, and as a result made most object relationships implicitly defined, and use the graph to trace references between objects to determine relationships. Edges may also be marked as having a dependency of the object they point to, which allows for automatic cleanup and deletion of most objects when no longer needed. Additionally, this allows not having to require in-memory copies of everything when using external databases. db_sql has been rewritten for this and now always requires a database to function. db_sql with MySQL now requires InnoDB to make use of transactions and foreign key constraints. --- modules/extra/m_sqlite.cpp | 281 +++++++++++++++++++++++++++------------------ 1 file changed, 171 insertions(+), 110 deletions(-) (limited to 'modules/extra/m_sqlite.cpp') diff --git a/modules/extra/m_sqlite.cpp b/modules/extra/m_sqlite.cpp index f07f6dbc2..19d95c248 100644 --- a/modules/extra/m_sqlite.cpp +++ b/modules/extra/m_sqlite.cpp @@ -14,17 +14,38 @@ using namespace SQL; class SQLiteResult : public Result { public: - SQLiteResult(unsigned int i, const Query &q, const Anope::string &fq) : Result(i, q, fq) + SQLiteResult(sqlite3 *sql, unsigned int id, const Query &q, const Anope::string &fq, sqlite3_stmt *stmt) : Result(id, q, fq) { - } + int cols = sqlite3_column_count(stmt); + for (int i = 0; i < cols; ++i) + this->columns.push_back(sqlite3_column_name(stmt, i)); - SQLiteResult(const Query &q, const Anope::string &fq, const Anope::string &err) : Result(0, q, fq, err) - { + int err; + while ((err = sqlite3_step(stmt)) == SQLITE_ROW) + { + std::vector values; + + for (int i = 0; i < cols; ++i) + { + const char *data = reinterpret_cast(sqlite3_column_text(stmt, i)); + + Value v; + v.null = !data; + v.value = data ? data : ""; + values.push_back(v); + } + + this->values.push_back(values); + } + + if (err != SQLITE_DONE) + { + error = sqlite3_errmsg(sql); + } } - void AddRow(const std::map &data) + SQLiteResult(const Query &q, const Anope::string &fq, const Anope::string &err) : Result(0, q, fq, err) { - this->entries.push_back(data); } }; @@ -32,7 +53,7 @@ class SQLiteResult : public Result */ class SQLiteService : public Provider { - std::map > active_schema; + std::map > active_schema, indexes; Anope::string database; @@ -49,15 +70,20 @@ class SQLiteService : public Provider Result RunQuery(const Query &query); - std::vector CreateTable(const Anope::string &table, const Data &data) override; + std::vector InitSchema(const Anope::string &prefix) override; + std::vector Replace(const Anope::string &table, const Query &, const std::set &) override; + std::vector CreateTable(const Anope::string &, const Anope::string &table) override; + std::vector AlterTable(const Anope::string &, const Anope::string &table, const Anope::string &field, bool) override; + std::vector CreateIndex(const Anope::string &table, const Anope::string &field) override; - Query BuildInsert(const Anope::string &table, unsigned int id, Data &data); + Query BeginTransaction() override; + Query Commit() override; + + Serialize::ID GetID(const Anope::string &) override; Query GetTables(const Anope::string &prefix); Anope::string BuildQuery(const Query &q); - - Anope::string FromUnixtime(time_t); }; class ModuleSQLite : public Module @@ -155,136 +181,164 @@ Result SQLiteService::RunQuery(const Query &query) sqlite3_stmt *stmt; int err = sqlite3_prepare_v2(this->sql, real_query.c_str(), real_query.length(), &stmt, NULL); if (err != SQLITE_OK) + { return SQLiteResult(query, real_query, sqlite3_errmsg(this->sql)); + } - std::vector columns; - int cols = sqlite3_column_count(stmt); - columns.resize(cols); - for (int i = 0; i < cols; ++i) - columns[i] = sqlite3_column_name(stmt, i); + int id = sqlite3_last_insert_rowid(this->sql); + SQLiteResult result(this->sql, id, query, real_query, stmt); - SQLiteResult result(0, query, real_query); + sqlite3_finalize(stmt); - while ((err = sqlite3_step(stmt)) == SQLITE_ROW) - { - std::map items; - for (int i = 0; i < cols; ++i) - { - const char *data = reinterpret_cast(sqlite3_column_text(stmt, i)); - if (data && *data) - items[columns[i]] = data; - } - result.AddRow(items); - } + return result; +} + +std::vector SQLiteService::InitSchema(const Anope::string &prefix) +{ + std::vector queries; - result.id = sqlite3_last_insert_rowid(this->sql); + Query t = "CREATE TABLE IF NOT EXISTS `" + prefix + "id` (" + "`id`" + ")"; + queries.push_back(t); - sqlite3_finalize(stmt); + t = "CREATE TABLE IF NOT EXISTS `" + prefix + "objects` (`id` PRIMARY KEY, `type`)"; + queries.push_back(t); - if (err != SQLITE_DONE) - return SQLiteResult(query, real_query, sqlite3_errmsg(this->sql)); + t = "CREATE TABLE IF NOT EXISTS `" + prefix + "edges` (" + "`id`," + "`field`," + "`other_id`," + "PRIMARY KEY (`id`, `field`)" + ")"; + queries.push_back(t); - return result; + t = "CREATE INDEX IF NOT EXISTS idx_edge ON `" + prefix + "edges` (other_id)"; + queries.push_back(t); + + return queries; } -std::vector SQLiteService::CreateTable(const Anope::string &table, const Data &data) +std::vector SQLiteService::Replace(const Anope::string &table, const Query &q, const std::set &keys) { std::vector queries; - std::set &known_cols = this->active_schema[table]; - if (known_cols.empty()) - { - Log(LOG_DEBUG) << "m_sqlite: Fetching columns for " << table; + Anope::string query_text = "INSERT OR IGNORE INTO `" + table + "` ("; + for (const std::pair &p : q.parameters) + query_text += "`" + p.first + "`,"; + query_text.erase(query_text.length() - 1); + query_text += ") VALUES ("; + for (const std::pair &p : q.parameters) + query_text += "@" + p.first + "@,"; + query_text.erase(query_text.length() - 1); + query_text += ")"; - Result columns = this->RunQuery("PRAGMA table_info(" + table + ")"); - for (int i = 0; i < columns.Rows(); ++i) - { - const Anope::string &column = columns.Get(i, "name"); + Query query(query_text); + query.parameters = q.parameters; + queries.push_back(query); - Log(LOG_DEBUG) << "m_sqlite: Column #" << i << " for " << table << ": " << column; - known_cols.insert(column); - } + query_text = "UPDATE `" + table + "` SET "; + for (const std::pair &p : q.parameters) + if (!keys.count(p.first)) + query_text += "`" + p.first + "` = @" + p.first + "@,"; + query_text.erase(query_text.length() - 1); + unsigned int i = 0; + for (const Anope::string &key : keys) + { + if (!i++) + query_text += " WHERE "; + else + query_text += " AND "; + query_text += "`" + key + "` = @" + key + "@"; } - if (known_cols.empty()) - { - Anope::string query_text = "CREATE TABLE `" + table + "` (`id` INTEGER PRIMARY KEY, `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP"; + query = query_text; + query.parameters = q.parameters; + queries.push_back(query); - for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it) - { - known_cols.insert(it->first); + return queries; +} - query_text += ", `" + it->first + "` "; - if (data.GetType(it->first) == Serialize::Data::DT_INT) - query_text += "int(11)"; - else - query_text += "text"; - } +std::vector SQLiteService::CreateTable(const Anope::string &prefix, const Anope::string &table) +{ + std::vector queries; - query_text += ")"; + if (active_schema.find(prefix + table) == active_schema.end()) + { + Query t = "CREATE TABLE IF NOT EXISTS `" + prefix + table + "` (`id` bigint(20) NOT NULL, PRIMARY KEY (`id`))"; + queries.push_back(t); - queries.push_back(query_text); + active_schema[prefix + table]; + } - query_text = "CREATE UNIQUE INDEX `" + table + "_id_idx` ON `" + table + "` (`id`)"; - queries.push_back(query_text); + return queries; +} - query_text = "CREATE INDEX `" + table + "_timestamp_idx` ON `" + table + "` (`timestamp`)"; - queries.push_back(query_text); +std::vector SQLiteService::AlterTable(const Anope::string &prefix, const Anope::string &table, const Anope::string &field, bool) +{ + std::vector queries; + std::set &s = active_schema[prefix + table]; - 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;"; - queries.push_back(query_text); + if (!s.count(field)) + { + Query t = "ALTER TABLE `" + prefix + table + "` ADD `" + field + "` COLLATE NOCASE"; + queries.push_back(t); + s.insert(field); } - else - for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it) - { - if (known_cols.count(it->first) > 0) - continue; - known_cols.insert(it->first); + return queries; +} - Anope::string query_text = "ALTER TABLE `" + table + "` ADD `" + it->first + "` "; - if (data.GetType(it->first) == Serialize::Data::DT_INT) - query_text += "int(11)"; - else - query_text += "text"; +std::vector SQLiteService::CreateIndex(const Anope::string &table, const Anope::string &field) +{ + std::vector queries; - queries.push_back(query_text); - } + if (indexes[table].count(field)) + return queries; + + Query t = "CREATE INDEX IF NOT EXISTS idx_" + field + " ON `" + table + "` (" + field + ")"; + queries.push_back(t); + + indexes[table].insert(field); return queries; } -Query SQLiteService::BuildInsert(const Anope::string &table, unsigned int id, Data &data) +Query SQLiteService::BeginTransaction() { - /* Empty columns not present in the data set */ - const std::set &known_cols = this->active_schema[table]; - for (std::set::iterator it = known_cols.begin(), it_end = known_cols.end(); it != it_end; ++it) - if (*it != "id" && *it != "timestamp" && data.data.count(*it) == 0) - data[*it] << ""; - - Anope::string query_text = "REPLACE INTO `" + table + "` ("; - if (id > 0) - query_text += "`id`,"; - for (Data::Map::const_iterator it = data.data.begin(), it_end = data.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 (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it) - query_text += "@" + it->first + "@,"; - query_text.erase(query_text.length() - 1); - query_text += ")"; + return Query("BEGIN TRANSACTION"); +} - Query query(query_text); - for (Data::Map::const_iterator it = data.data.begin(), it_end = data.data.end(); it != it_end; ++it) +Query SQLiteService::Commit() +{ + return Query("COMMIT"); +} + +Serialize::ID SQLiteService::GetID(const Anope::string &prefix) +{ + /* must be in a deferred or reserved transaction here for atomic row update */ + + Query query("SELECT `id` FROM `" + prefix + "id`"); + Serialize::ID id; + + Result res = RunQuery(query); + if (res.Rows()) { - Anope::string buf; - *it->second >> buf; - query.SetValue(it->first, buf); + id = convertTo(res.Get(0, "id")); + + Query update_query("UPDATE `" + prefix + "id` SET `id` = `id` + 1"); + RunQuery(update_query); + } + else + { + id = 0; + + Query insert_query("INSERT INTO `" + prefix + "id` (id) VALUES(@id@)"); + insert_query.SetValue("id", 1); + RunQuery(insert_query); } - return query; + return id; } Query SQLiteService::GetTables(const Anope::string &prefix) @@ -305,14 +359,21 @@ Anope::string SQLiteService::BuildQuery(const Query &q) Anope::string real_query = q.query; for (std::map::const_iterator it = q.parameters.begin(), it_end = q.parameters.end(); it != it_end; ++it) - real_query = real_query.replace_all_cs("@" + it->first + "@", (it->second.escape ? ("'" + this->Escape(it->second.data) + "'") : it->second.data)); + { + const QueryData& qd = it->second; + Anope::string replacement; - return real_query; -} + if (qd.null) + replacement = "NULL"; + else if (!qd.escape) + replacement = qd.data; + else + replacement = "'" + this->Escape(qd.data) + "'"; -Anope::string SQLiteService::FromUnixtime(time_t t) -{ - return "datetime('" + stringify(t) + "', 'unixepoch')"; + real_query = real_query.replace_all_cs("@" + it->first + "@", replacement); + } + + return real_query; } MODULE_INIT(ModuleSQLite) -- cgit