summary refs log tree commit diff stats
diff options
context:
space:
mode:
authorKelly Rauchenberger <fefferburbia@gmail.com>2017-11-08 14:53:26 -0500
committerKelly Rauchenberger <fefferburbia@gmail.com>2017-11-08 14:53:26 -0500
commit350bfdb5ea9b4f7e50746c50a46d8032cbc5a104 (patch)
treee339d118669f85de1afb858828a7affd0b47c745
parentb92db337bf7cbe2ea0564be6c63c336d2bcd4567 (diff)
downloadverbly-350bfdb5ea9b4f7e50746c50a46d8032cbc5a104.tar.gz
verbly-350bfdb5ea9b4f7e50746c50a46d8032cbc5a104.tar.bz2
verbly-350bfdb5ea9b4f7e50746c50a46d8032cbc5a104.zip
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
-rw-r--r--generator/generator.cpp23
-rw-r--r--generator/generator.h4
-rw-r--r--generator/schema.sql5
-rw-r--r--lib/database.cpp42
-rw-r--r--lib/database.h44
-rw-r--r--lib/version.h11
6 files changed, 129 insertions, 0 deletions
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 @@
11#include "part.h" 11#include "part.h"
12#include "field.h" 12#include "field.h"
13#include "../lib/util.h" 13#include "../lib/util.h"
14#include "../lib/version.h"
14 15
15namespace verbly { 16namespace verbly {
16 namespace generator { 17 namespace generator {
@@ -112,6 +113,9 @@ namespace verbly {
112 // Writes the database schema 113 // Writes the database schema
113 writeSchema(); 114 writeSchema();
114 115
116 // Writes the database version
117 writeVersion();
118
115 // Dumps data to the database 119 // Dumps data to the database
116 dumpObjects(); 120 dumpObjects();
117 121
@@ -154,6 +158,9 @@ namespace verbly {
154 158
155 // Populates the adjective similarity relationship from WordNet 159 // Populates the adjective similarity relationship from WordNet
156 readWordNetSimilarity(); 160 readWordNetSimilarity();
161
162 // Generates analysis data to assist in query planning.
163 analyzeDatabase();
157 } 164 }
158 165
159 void generator::readWordNetSynsets() 166 void generator::readWordNetSynsets()
@@ -581,6 +588,15 @@ namespace verbly {
581 } 588 }
582 } 589 }
583 590
591 void generator::writeVersion()
592 {
593 std::list<field> fields;
594 fields.emplace_back("major", DATABASE_MAJOR_VERSION);
595 fields.emplace_back("minor", DATABASE_MINOR_VERSION);
596
597 db_.insertIntoTable("version", std::move(fields));
598 }
599
584 void generator::dumpObjects() 600 void generator::dumpObjects()
585 { 601 {
586 { 602 {
@@ -1110,6 +1126,13 @@ namespace verbly {
1110 } 1126 }
1111 } 1127 }
1112 1128
1129 void generator::analyzeDatabase()
1130 {
1131 std::cout << "Analyzing data..." << std::endl;
1132
1133 db_.runQuery("ANALYZE");
1134 }
1135
1113 std::list<std::string> generator::readFile(std::string path) 1136 std::list<std::string> generator::readFile(std::string path)
1114 { 1137 {
1115 std::ifstream file(path); 1138 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 {
60 60
61 void writeSchema(); 61 void writeSchema();
62 62
63 void writeVersion();
64
63 void dumpObjects(); 65 void dumpObjects();
64 66
65 void readWordNetAntonymy(); 67 void readWordNetAntonymy();
@@ -88,6 +90,8 @@ namespace verbly {
88 90
89 void readWordNetSimilarity(); 91 void readWordNetSimilarity();
90 92
93 void analyzeDatabase();
94
91 // Helpers 95 // Helpers
92 96
93 std::list<std::string> readFile(std::string path); 97 std::list<std::string> 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 @@
1CREATE TABLE `version` (
2 `major` INTEGER NOT NULL,
3 `minor` INTEGER NOT NULL
4);
5
1CREATE TABLE `notions` ( 6CREATE TABLE `notions` (
2 `notion_id` INTEGER PRIMARY KEY, 7 `notion_id` INTEGER PRIMARY KEY,
3 `part_of_speech` SMALLINT NOT NULL, 8 `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 @@
1#include "database.h" 1#include "database.h"
2#include <sqlite3.h> 2#include <sqlite3.h>
3#include <stdexcept> 3#include <stdexcept>
4#include <sstream>
4#include "query.h" 5#include "query.h"
6#include "version.h"
5 7
6namespace verbly { 8namespace verbly {
7 9
@@ -17,6 +19,35 @@ namespace verbly {
17 19
18 throw database_error("Could not open verbly datafile", errmsg); 20 throw database_error("Could not open verbly datafile", errmsg);
19 } 21 }
22
23 std::string queryString = "SELECT major, minor FROM version";
24
25 sqlite3_stmt* ppstmt;
26 if (sqlite3_prepare_v2(ppdb_, queryString.c_str(), queryString.length(), &ppstmt, NULL) != SQLITE_OK)
27 {
28 std::string errorMsg = sqlite3_errmsg(ppdb_);
29 sqlite3_finalize(ppstmt);
30
31 throw database_error("Error reading database version", errorMsg);
32 }
33
34 if (sqlite3_step(ppstmt) != SQLITE_ROW)
35 {
36 std::string errorMsg = sqlite3_errmsg(ppdb_);
37 sqlite3_finalize(ppstmt);
38
39 throw database_error("Error reading database version", errorMsg);
40 }
41
42 major_ = sqlite3_column_int(ppstmt, 0);
43 minor_ = sqlite3_column_int(ppstmt, 1);
44
45 sqlite3_finalize(ppstmt);
46
47 if (major_ != DATABASE_MAJOR_VERSION)
48 {
49 throw database_version_mismatch(DATABASE_MAJOR_VERSION, major_);
50 }
20 } 51 }
21 52
22 database::database(database&& other) : database() 53 database::database(database&& other) : database()
@@ -135,4 +166,15 @@ namespace verbly {
135 return result; 166 return result;
136 } 167 }
137 168
169 std::string database_version_mismatch::generateMessage(int right, int wrong)
170 {
171 std::ostringstream msgbuilder;
172 msgbuilder << "Incompatible database version: needed ";
173 msgbuilder << right;
174 msgbuilder << ", found ";
175 msgbuilder << wrong;
176
177 return msgbuilder.str();
178 }
179
138}; 180};
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 @@
3 3
4#include <string> 4#include <string>
5#include <exception> 5#include <exception>
6#include <stdexcept>
6#include <list> 7#include <list>
7#include <set> 8#include <set>
8#include "notion.h" 9#include "notion.h"
@@ -45,6 +46,18 @@ namespace verbly {
45 46
46 ~database(); 47 ~database();
47 48
49 // Information
50
51 int getMajorVersion() const
52 {
53 return major_;
54 }
55
56 int getMinorVersion() const
57 {
58 return minor_;
59 }
60
48 // Queries 61 // Queries
49 62
50 query<notion> notions(filter where, order sortOrder = {}, int limit = 1) const; 63 query<notion> notions(filter where, order sortOrder = {}, int limit = 1) const;
@@ -69,6 +82,37 @@ namespace verbly {
69 82
70 sqlite3* ppdb_ = nullptr; 83 sqlite3* ppdb_ = nullptr;
71 84
85 int major_;
86 int minor_;
87
88 };
89
90 class database_version_mismatch : public std::logic_error {
91 public:
92
93 database_version_mismatch(int right, int wrong) :
94 std::logic_error(generateMessage(right, wrong)),
95 right_(right),
96 wrong_(wrong)
97 {
98 }
99
100 int getRightVersion() const noexcept
101 {
102 return right_;
103 }
104
105 int getWrongVersion() const noexcept
106 {
107 return wrong_;
108 }
109
110 private:
111
112 static std::string generateMessage(int right, int wrong);
113
114 int right_;
115 int wrong_;
72 }; 116 };
73 117
74}; 118};
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 @@
1#ifndef VERSION_H_3CACFC4B
2#define VERSION_H_3CACFC4B
3
4namespace verbly {
5
6 const int DATABASE_MAJOR_VERSION = 1;
7 const int DATABASE_MINOR_VERSION = 0;
8
9};
10
11#endif /* end of include guard: VERSION_H_3CACFC4B */