From 350bfdb5ea9b4f7e50746c50a46d8032cbc5a104 Mon Sep 17 00:00:00 2001 From: Kelly Rauchenberger Date: Wed, 8 Nov 2017 14:53:26 -0500 Subject: Created database versioning system Also added an ANALYZE statement to the end of the datafile generation process. This generates information that allows sqlite to sometimes come up with a better query plan, and in many cases can significant speed up queries. This constitutes a minor database update, but because this is the first version that uses the database versioning system, older versions are essentially incompatible. refs #2 --- generator/generator.cpp | 23 +++++++++++++++++++++++ generator/generator.h | 4 ++++ generator/schema.sql | 5 +++++ lib/database.cpp | 42 ++++++++++++++++++++++++++++++++++++++++++ lib/database.h | 44 ++++++++++++++++++++++++++++++++++++++++++++ lib/version.h | 11 +++++++++++ 6 files changed, 129 insertions(+) create mode 100644 lib/version.h diff --git a/generator/generator.cpp b/generator/generator.cpp index e125b4a..d774bd9 100644 --- a/generator/generator.cpp +++ b/generator/generator.cpp @@ -11,6 +11,7 @@ #include "part.h" #include "field.h" #include "../lib/util.h" +#include "../lib/version.h" namespace verbly { namespace generator { @@ -112,6 +113,9 @@ namespace verbly { // Writes the database schema writeSchema(); + // Writes the database version + writeVersion(); + // Dumps data to the database dumpObjects(); @@ -154,6 +158,9 @@ namespace verbly { // Populates the adjective similarity relationship from WordNet readWordNetSimilarity(); + + // Generates analysis data to assist in query planning. + analyzeDatabase(); } void generator::readWordNetSynsets() @@ -581,6 +588,15 @@ namespace verbly { } } + void generator::writeVersion() + { + std::list fields; + fields.emplace_back("major", DATABASE_MAJOR_VERSION); + fields.emplace_back("minor", DATABASE_MINOR_VERSION); + + db_.insertIntoTable("version", std::move(fields)); + } + void generator::dumpObjects() { { @@ -1110,6 +1126,13 @@ namespace verbly { } } + void generator::analyzeDatabase() + { + std::cout << "Analyzing data..." << std::endl; + + db_.runQuery("ANALYZE"); + } + std::list generator::readFile(std::string path) { std::ifstream file(path); diff --git a/generator/generator.h b/generator/generator.h index 43b3272..5458c97 100644 --- a/generator/generator.h +++ b/generator/generator.h @@ -60,6 +60,8 @@ namespace verbly { void writeSchema(); + void writeVersion(); + void dumpObjects(); void readWordNetAntonymy(); @@ -88,6 +90,8 @@ namespace verbly { void readWordNetSimilarity(); + void analyzeDatabase(); + // Helpers std::list readFile(std::string path); diff --git a/generator/schema.sql b/generator/schema.sql index c0f2bcb..d97c06e 100644 --- a/generator/schema.sql +++ b/generator/schema.sql @@ -1,3 +1,8 @@ +CREATE TABLE `version` ( + `major` INTEGER NOT NULL, + `minor` INTEGER NOT NULL +); + CREATE TABLE `notions` ( `notion_id` INTEGER PRIMARY KEY, `part_of_speech` SMALLINT NOT NULL, diff --git a/lib/database.cpp b/lib/database.cpp index a4d056d..fe64763 100644 --- a/lib/database.cpp +++ b/lib/database.cpp @@ -1,7 +1,9 @@ #include "database.h" #include #include +#include #include "query.h" +#include "version.h" namespace verbly { @@ -17,6 +19,35 @@ namespace verbly { throw database_error("Could not open verbly datafile", errmsg); } + + std::string queryString = "SELECT major, minor FROM version"; + + sqlite3_stmt* ppstmt; + if (sqlite3_prepare_v2(ppdb_, queryString.c_str(), queryString.length(), &ppstmt, NULL) != SQLITE_OK) + { + std::string errorMsg = sqlite3_errmsg(ppdb_); + sqlite3_finalize(ppstmt); + + throw database_error("Error reading database version", errorMsg); + } + + if (sqlite3_step(ppstmt) != SQLITE_ROW) + { + std::string errorMsg = sqlite3_errmsg(ppdb_); + sqlite3_finalize(ppstmt); + + throw database_error("Error reading database version", errorMsg); + } + + major_ = sqlite3_column_int(ppstmt, 0); + minor_ = sqlite3_column_int(ppstmt, 1); + + sqlite3_finalize(ppstmt); + + if (major_ != DATABASE_MAJOR_VERSION) + { + throw database_version_mismatch(DATABASE_MAJOR_VERSION, major_); + } } database::database(database&& other) : database() @@ -135,4 +166,15 @@ namespace verbly { return result; } + std::string database_version_mismatch::generateMessage(int right, int wrong) + { + std::ostringstream msgbuilder; + msgbuilder << "Incompatible database version: needed "; + msgbuilder << right; + msgbuilder << ", found "; + msgbuilder << wrong; + + return msgbuilder.str(); + } + }; diff --git a/lib/database.h b/lib/database.h index ef5ff87..efb54e1 100644 --- a/lib/database.h +++ b/lib/database.h @@ -3,6 +3,7 @@ #include #include +#include #include #include #include "notion.h" @@ -45,6 +46,18 @@ namespace verbly { ~database(); + // Information + + int getMajorVersion() const + { + return major_; + } + + int getMinorVersion() const + { + return minor_; + } + // Queries query notions(filter where, order sortOrder = {}, int limit = 1) const; @@ -69,6 +82,37 @@ namespace verbly { sqlite3* ppdb_ = nullptr; + int major_; + int minor_; + + }; + + class database_version_mismatch : public std::logic_error { + public: + + database_version_mismatch(int right, int wrong) : + std::logic_error(generateMessage(right, wrong)), + right_(right), + wrong_(wrong) + { + } + + int getRightVersion() const noexcept + { + return right_; + } + + int getWrongVersion() const noexcept + { + return wrong_; + } + + private: + + static std::string generateMessage(int right, int wrong); + + int right_; + int wrong_; }; }; diff --git a/lib/version.h b/lib/version.h new file mode 100644 index 0000000..41ab79e --- /dev/null +++ b/lib/version.h @@ -0,0 +1,11 @@ +#ifndef VERSION_H_3CACFC4B +#define VERSION_H_3CACFC4B + +namespace verbly { + + const int DATABASE_MAJOR_VERSION = 1; + const int DATABASE_MINOR_VERSION = 0; + +}; + +#endif /* end of include guard: VERSION_H_3CACFC4B */ -- cgit 1.4.1