Merge the databases

[caarmen]

Currently the app has 3 database files, for:

• rhymer
• thesaurus
• dictionary

Perhaps the app size can be reduced by merging the three into a single database file with tables:

• word_variants (for rhymes)
  • maybe get rid of the "has_definition" column, but not sure. We want to know which rhymes have a definition (or have a stem for which a dictionary entry exists). Maybe this can be achieved with a join. However, keeping this column can make lookups faster.
• thesaurus
  • change the "word" column to a "word_id" column, referring to stems.rowid
  • remove the "stem" column
• dictionary
  • change the "word" column to a "word_id" column, referring to stems.rowid
• stems:
  • add stems from the old thesaurus stem column that aren't already here

Crossed out suggestions (regarding joins) make the db queries slower. This was the attempt to reduce redundancy and make the word_variants table reference a "word" table instead of duplicating the words:

CREATE TABLE word (wordid INTEGER PRIMARY KEY, word VARCHAR (80) NOT NULL, stem VARCHAR (80), google_ngram_frequency INTEGER);
CREATE TABLE word_variants (wordid integer, variant_number integer, stress_syllables text, last_syllable text, last_two_syllables text, last_three_syllables text, has_definition integer, foreign key (wordid) references word(wordid));
CREATE INDEX word_variants_fk on word_variants (wordid);
CREATE UNIQUE INDEX word_word_id_pk on word(wordid);