Created database versioning system d1.0

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

6 files changed, 129 insertions, 0 deletions

@@ -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<field> 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<std::string> generator::readFile(std::string path)
     {
       std::ifstream file(path);
@@ -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<std::string> readFile(std::string path);
@@ -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,
@@ -1,7 +1,9 @@
 #include "database.h"
 #include <sqlite3.h>
 #include <stdexcept>
+#include <sstream>
 #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();
+  }
+
 };
@@ -3,6 +3,7 @@
 
 #include <string>
 #include <exception>
+#include <stdexcept>
 #include <list>
 #include <set>
 #include "notion.h"
@@ -45,6 +46,18 @@ namespace verbly {
 
     ~database();
 
+    // Information
+
+    int getMajorVersion() const
+    {
+      return major_;
+    }
+
+    int getMinorVersion() const
+    {
+      return minor_;
+    }
+
     // Queries
 
     query<notion> 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_;
   };
 
 };
@@ -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 */