Removed unnecessary ROWIDs from database schema

The generator also now sorts and uniq's the WordNet files for antonymy, classification, and pertainymy/mannernymy, because those files contained duplicate rows, and the join tables without ROWIDs now enforce a uniqueness constraint.

This constitutes a minor database update -- the new database is compatible with d1.0, but is ~12MB smaller.

refs #6

3 files changed, 91 insertions, 79 deletions

@@ -696,7 +696,8 @@ namespace verbly {
 
     void generator::readWordNetAntonymy()
     {
-      std::list<std::string> lines(readFile(wordNetPath_ + "wn_ant.pl"));
+      std::list<std::string> lines(readFile(wordNetPath_ + "wn_ant.pl", true));
+
       hatkirby::progress ppgs("Writing antonyms...", lines.size());
       for (auto line : lines)
       {
@@ -770,7 +771,7 @@ namespace verbly {
 
     void generator::readWordNetClasses()
     {
-      std::list<std::string> lines(readFile(wordNetPath_ + "wn_cls.pl"));
+      std::list<std::string> lines(readFile(wordNetPath_ + "wn_cls.pl", true));
 
       hatkirby::progress ppgs(
         "Writing usage, topicality, and regionality...",
@@ -1092,7 +1093,7 @@ namespace verbly {
 
     void generator::readWordNetPertainymy()
     {
-      std::list<std::string> lines(readFile(wordNetPath_ + "wn_per.pl"));
+      std::list<std::string> lines(readFile(wordNetPath_ + "wn_per.pl", true));
 
       hatkirby::progress ppgs(
         "Writing pertainymy and mannernymy...",
@@ -1228,7 +1229,7 @@ namespace verbly {
       db_.execute("ANALYZE");
     }
 
-    std::list<std::string> generator::readFile(std::string path)
+    std::list<std::string> generator::readFile(std::string path, bool uniq)
     {
       std::ifstream file(path);
       if (!file)
@@ -1248,6 +1249,18 @@ namespace verbly {
         lines.push_back(line);
       }
 
+      if (uniq)
+      {
+        std::vector<std::string> uniq(std::begin(lines), std::end(lines));
+        lines.clear();
+
+        std::sort(std::begin(uniq), std::end(uniq));
+        std::unique_copy(
+          std::begin(uniq),
+          std::end(uniq),
+          std::back_inserter(lines));
+      }
+
       return lines;
     }
 
@@ -94,7 +94,7 @@ namespace verbly {
 
       // Helpers
 
-      std::list<std::string> readFile(std::string path);
+      std::list<std::string> readFile(std::string path, bool uniq = false);
 
       inline part_of_speech partOfSpeechByWnid(int wnid);
 
@@ -14,79 +14,79 @@ CREATE UNIQUE INDEX `notion_by_wnid` ON `notions`(`wnid`);
 
 CREATE TABLE `hypernymy` (
   `hypernym_id` INTEGER NOT NULL,
-  `hyponym_id` INTEGER NOT NULL
-);
+  `hyponym_id` INTEGER NOT NULL,
+  PRIMARY KEY (`hypernym_id`,`hyponym_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `hyponym_of` ON `hypernymy`(`hypernym_id`,`hyponym_id`);
-CREATE INDEX `hypernym_of` ON `hypernymy`(`hyponym_id`,`hypernym_id`);
+CREATE INDEX `reverse_hypernymy` ON `hypernymy`(`hyponym_id`,`hypernym_id`);
 
 CREATE TABLE `instantiation` (
   `class_id` INTEGER NOT NULL,
-  `instance_id` INTEGER NOT NULL
-);
+  `instance_id` INTEGER NOT NULL,
+  PRIMARY KEY (`class_id`,`instance_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `instance_of` ON `instantiation`(`class_id`,`instance_id`);
-CREATE INDEX `class_of` ON `instantiation`(`instance_id`,`class_id`);
+CREATE INDEX `reverse_instantiation` ON `instantiation`(`instance_id`,`class_id`);
 
 CREATE TABLE `member_meronymy` (
   `meronym_id` INTEGER NOT NULL,
-  `holonym_id` INTEGER NOT NULL
-);
+  `holonym_id` INTEGER NOT NULL,
+  PRIMARY KEY (`meronym_id`,`holonym_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `member_holonym_of` ON `member_meronymy`(`meronym_id`,`holonym_id`);
-CREATE INDEX `member_meronym_of` ON `member_meronymy`(`holonym_id`,`meronym_id`);
+CREATE INDEX `reverse_member_meronymy` ON `member_meronymy`(`holonym_id`,`meronym_id`);
 
 CREATE TABLE `part_meronymy` (
   `meronym_id` INTEGER NOT NULL,
-  `holonym_id` INTEGER NOT NULL
-);
+  `holonym_id` INTEGER NOT NULL,
+  PRIMARY KEY (`meronym_id`,`holonym_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `part_holonym_of` ON `part_meronymy`(`meronym_id`,`holonym_id`);
-CREATE INDEX `part_meronym_of` ON `part_meronymy`(`holonym_id`,`meronym_id`);
+CREATE INDEX `reverse_part_meronymy` ON `part_meronymy`(`holonym_id`,`meronym_id`);
 
 CREATE TABLE `substance_meronymy` (
   `meronym_id` INTEGER NOT NULL,
-  `holonym_id` INTEGER NOT NULL
-);
+  `holonym_id` INTEGER NOT NULL,
+  PRIMARY KEY (`meronym_id`,`holonym_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `substance_holonym_of` ON `substance_meronymy`(`meronym_id`,`holonym_id`);
-CREATE INDEX `substance_meronym_of` ON `substance_meronymy`(`holonym_id`,`meronym_id`);
+CREATE INDEX `reverse_substance_meronymy` ON `substance_meronymy`(`holonym_id`,`meronym_id`);
 
 CREATE TABLE `variation` (
   `noun_id` INTEGER NOT NULL,
-  `adjective_id` INTEGER NOT NULL
-);
+  `adjective_id` INTEGER NOT NULL,
+  PRIMARY KEY (`noun_id`,`adjective_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `variant_of` ON `variation`(`noun_id`,`adjective_id`);
-CREATE INDEX `attribute_of` ON `variation`(`adjective_id`,`noun_id`);
+CREATE INDEX `reverse_variation` ON `variation`(`adjective_id`,`noun_id`);
 
 CREATE TABLE `similarity` (
   `adjective_1_id` INTEGER NOT NULL,
-  `adjective_2_id` INTEGER NOT NULL
-);
-
-CREATE INDEX `similar_to` ON `similarity`(`adjective_1_id`,`adjective_2_id`);
+  `adjective_2_id` INTEGER NOT NULL,
+  PRIMARY KEY (`adjective_1_id`,`adjective_2_id`)
+) WITHOUT ROWID;
 
 CREATE TABLE `is_a` (
   `notion_id` INTEGER NOT NULL,
-  `groupname` VARCHAR(32) NOT NULL
-);
+  `groupname` VARCHAR(32) NOT NULL,
+  PRIMARY KEY (`notion_id`,`groupname`)
+) WITHOUT ROWID;
 
 CREATE TABLE `entailment` (
   `given_id` INTEGER NOT NULL,
-  `entailment_id` INTEGER NOT NULL
-);
+  `entailment_id` INTEGER NOT NULL,
+  PRIMARY KEY (`given_id`,`entailment_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `entailment_of` ON `entailment`(`given_id`,`entailment_id`);
-CREATE INDEX `entailed_by` ON `entailment`(`entailment_id`,`given_id`);
+CREATE INDEX `reverse_entailment` ON `entailment`(`entailment_id`,`given_id`);
 
 CREATE TABLE `causality` (
   `cause_id` INTEGER NOT NULL,
-  `effect_id` INTEGER NOT NULL
-);
+  `effect_id` INTEGER NOT NULL,
+  PRIMARY KEY (`cause_id`,`effect_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `effect_of` ON `causality`(`cause_id`,`effect_id`);
-CREATE INDEX `cause_of` ON `causality`(`effect_id`,`cause_id`);
+CREATE INDEX `reverse_causality` ON `causality`(`effect_id`,`cause_id`);
 
 CREATE TABLE `words` (
   `word_id` INTEGER PRIMARY KEY,
@@ -103,58 +103,57 @@ CREATE INDEX `group_words` ON `words`(`group_id`);
 
 CREATE TABLE `antonymy` (
   `antonym_1_id` INTEGER NOT NULL,
-  `antonym_2_id` INTEGER NOT NULL
-);
-
-CREATE INDEX `antonym_of` ON `antonymy`(`antonym_1_id`,`antonym_2_id`);
+  `antonym_2_id` INTEGER NOT NULL,
+  PRIMARY KEY (`antonym_1_id`,`antonym_2_id`)
+) WITHOUT ROWID;
 
 CREATE TABLE `specification` (
   `general_id` INTEGER NOT NULL,
-  `specific_id` INTEGER NOT NULL
-);
+  `specific_id` INTEGER NOT NULL,
+  PRIMARY KEY (`general_id`,`specific_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `specification_of` ON `specification`(`general_id`,`specific_id`);
-CREATE INDEX `generalization_of` ON `specification`(`specific_id`,`general_id`);
+CREATE INDEX `reverse_specification` ON `specification`(`specific_id`,`general_id`);
 
 CREATE TABLE `pertainymy` (
   `noun_id` INTEGER NOT NULL,
-  `pertainym_id` INTEGER NOT NULL
-);
+  `pertainym_id` INTEGER NOT NULL,
+  PRIMARY KEY (`noun_id`,`pertainym_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `pertainym_of` ON `pertainymy`(`noun_id`,`pertainym_id`);
-CREATE INDEX `anti_pertainym_of` ON `pertainymy`(`pertainym_id`,`noun_id`);
+CREATE INDEX `reverse_pertainymy` ON `pertainymy`(`pertainym_id`,`noun_id`);
 
 CREATE TABLE `mannernymy` (
   `adjective_id` INTEGER NOT NULL,
-  `mannernym_id` INTEGER NOT NULL
-);
+  `mannernym_id` INTEGER NOT NULL,
+  PRIMARY KEY (`adjective_id`,`mannernym_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `mannernym_of` ON `mannernymy`(`adjective_id`,`mannernym_id`);
-CREATE INDEX `anti_mannernym_of` ON `mannernymy`(`mannernym_id`,`adjective_id`);
+CREATE INDEX `reverse_mannernymy` ON `mannernymy`(`mannernym_id`,`adjective_id`);
 
 CREATE TABLE `usage` (
   `domain_id` INTEGER NOT NULL,
-  `term_id` INTEGER NOT NULL
-);
+  `term_id` INTEGER NOT NULL,
+  PRIMARY KEY (`domain_id`,`term_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `usage_term_of` ON `usage`(`domain_id`,`term_id`);
-CREATE INDEX `usage_domain_of` ON `usage`(`term_id`,`domain_id`);
+CREATE INDEX `reverse_usage` ON `usage`(`term_id`,`domain_id`);
 
 CREATE TABLE `topicality` (
   `domain_id` INTEGER NOT NULL,
-  `term_id` INTEGER NOT NULL
-);
+  `term_id` INTEGER NOT NULL,
+  PRIMARY KEY (`domain_id`,`term_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `topical_term_of` ON `topicality`(`domain_id`,`term_id`);
-CREATE INDEX `topical_domain_of` ON `topicality`(`term_id`,`domain_id`);
+CREATE INDEX `reverse_topicality` ON `topicality`(`term_id`,`domain_id`);
 
 CREATE TABLE `regionality` (
   `domain_id` INTEGER NOT NULL,
-  `term_id` INTEGER NOT NULL
-);
+  `term_id` INTEGER NOT NULL,
+  PRIMARY KEY (`domain_id`,`term_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `regional_term_of` ON `regionality`(`domain_id`,`term_id`);
-CREATE INDEX `regional_domain_of` ON `regionality`(`term_id`,`domain_id`);
+CREATE INDEX `reverse_regionality` ON `regionality`(`term_id`,`domain_id`);
 
 CREATE TABLE `forms` (
   `form_id` INTEGER PRIMARY KEY,
@@ -169,11 +168,11 @@ CREATE UNIQUE INDEX `form_by_string` ON `forms`(`form`);
 CREATE TABLE `lemmas_forms` (
   `lemma_id` INTEGER NOT NULL,
   `form_id` INTEGER NOT NULL,
-  `category` SMALLINT NOT NULL
-);
+  `category` SMALLINT NOT NULL,
+  PRIMARY KEY (`lemma_id`,`category`,`form_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `form_of` ON `lemmas_forms`(`lemma_id`,`category`,`form_id`);
-CREATE INDEX `lemma_of` ON `lemmas_forms`(`form_id`,`category`,`lemma_id`);
+CREATE INDEX `forms_lemmas` ON `lemmas_forms`(`form_id`,`category`,`lemma_id`);
 
 CREATE TABLE `pronunciations` (
   `pronunciation_id` INTEGER PRIMARY KEY,
@@ -188,11 +187,11 @@ CREATE INDEX `rhymes_with` ON `pronunciations`(`rhyme`,`prerhyme`);
 
 CREATE TABLE `forms_pronunciations` (
   `form_id` INTEGER NOT NULL,
-  `pronunciation_id` INTEGER NOT NULL
-);
+  `pronunciation_id` INTEGER NOT NULL,
+  PRIMARY KEY (`form_id`,`pronunciation_id`)
+) WITHOUT ROWID;
 
-CREATE INDEX `pronunciation_of` ON `forms_pronunciations`(`form_id`,`pronunciation_id`);
-CREATE INDEX `spelling_of` ON `forms_pronunciations`(`pronunciation_id`,`form_id`);
+CREATE INDEX `pronunciations_forms` ON `forms_pronunciations`(`pronunciation_id`,`form_id`);
 
 CREATE TABLE `frames` (
   `frame_id` INTEGER NOT NULL,