ja_db/migrations/0004_KANJIDIC2.sql

CREATE TABLE "KANJIDIC_Character" (
  "literal" CHAR(1) NOT NULL PRIMARY KEY,
  "grade" INTEGER CHECK ("grade" BETWEEN 1 AND 10),
  "strokeCount" INTEGER NOT NULL,
  "frequency" INTEGER,
  "jlpt" INTEGER
) WITHOUT ROWID;

CREATE TABLE "KANJIDIC_RadicalName" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "name" TEXT NOT NULL,
  PRIMARY KEY("kanji", "name")
) WITHOUT ROWID;

CREATE TABLE "KANJIDIC_Codepoint" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "type" VARCHAR(6) NOT NULL CHECK ("type" IN ('jis208', 'jis212', 'jis213', 'ucs')),
  "codepoint" VARCHAR(7) NOT NULL,
  PRIMARY KEY ("kanji", "type")
) WITHOUT ROWID;

CREATE INDEX "KANJIDIC_Codepoint_byCharacter" ON "KANJIDIC_Codepoint"("kanji");

CREATE TABLE "KANJIDIC_Radical" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "type" VARCHAR(9) NOT NULL CHECK ("type" IN ('classical', 'nelson_c')),
  "radical" INTEGER NOT NULL CHECK ("radical" BETWEEN 1 AND IIF("type" = 'classical', 214, 212)),
  PRIMARY KEY("kanji", "type")
) WITHOUT ROWID;

CREATE TABLE "KANJIDIC_StrokeMiscount" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "strokeCount" INTEGER NOT NULL,
  PRIMARY KEY("kanji", "strokeCount")
) WITHOUT ROWID;

CREATE TABLE "KANJIDIC_Variant" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "variant" TEXT NOT NULL,
  "type" VARCHAR(8) NOT NULL CHECK (
    "type" IN (
      'jis208',
      'jis212',
      'jis213',
      'deroo',
      'njecd',
      's_h',
      'nelson_c',
      'oneill',
      'ucs'
    )
  ),
  PRIMARY KEY ("kanji", "type", "variant")
) WITHOUT ROWID;

CREATE TABLE "_KANJIDIC_DictionaryReference_Part1" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "ref" VARCHAR(9) NOT NULL,
  "type" VARCHAR(16) NOT NULL CHECK(
    "type" IN (
      'nelson_c',
      'nelson_n',
      'halpern_njecd',
      'halpern_kkd',
      'halpern_kkld',
      'halpern_kkld_2ed',
      'heisig',
      'heisig6',
      'gakken',
      'oneill_names',
      'oneill_kk',
      'henshall',
      'sh_kk',
      'sh_kk2',
      'sakade',
      'jf_cards',
      'henshall3',
      'tutt_cards',
      'crowley',
      'kanji_in_context',
      'busy_people',
      'kodansha_compact',
      'maniette'
    )
  ),
  PRIMARY KEY("kanji", "type")
) WITHOUT ROWID;

CREATE TABLE "_KANJIDIC_DictionaryReference_Moro" (
  "kanji" CHAR(1) NOT NULL PRIMARY KEY REFERENCES "KANJIDIC_Character"("literal"),
  "ref" VARCHAR(7) NOT NULL,
  "volume" INTEGER,
  "page" INTEGER
) WITHOUT ROWID;

CREATE INDEX "KANJIDIC_DictionaryReference_byPart1" ON "_KANJIDIC_DictionaryReference_Part1"("kanji", "ref", "type");
CREATE INDEX "KANJIDIC_DictionaryReference_byMoro" ON "_KANJIDIC_DictionaryReference_Moro"("kanji", "ref", "volume", "page");

CREATE VIEW "KANJIDIC_DictionaryReference" AS
SELECT "kanji", "ref", "type", NULL AS "volume", NULL AS "page" FROM "_KANJIDIC_DictionaryReference_Part1"
UNION
SELECT "kanji", "ref", 'moro' AS "type", "volume", "page" FROM "_KANJIDIC_DictionaryReference_Moro";

CREATE TABLE "KANJIDIC_QueryCode" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "code" VARCHAR(7) NOT NULL,
  "type" VARCHAR(11) NOT NULL CHECK ("type" IN ('skip', 'sh_desc', 'four_corner', 'deroo', 'misclass')),
  "SKIPMisclassification" VARCHAR(15),
  PRIMARY KEY ("kanji", "type", "code")
) WITHOUT ROWID;

CREATE TABLE "KANJIDIC_Reading" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "type" VARCHAR(8) NOT NULL CHECK ("type" IN ('korean_h', 'korean_r', 'pinyin')),
  "reading" TEXT NOT NULL,
  PRIMARY KEY ("kanji", "type", "reading")
) WITHOUT ROWID;

CREATE INDEX "KANJIDIC_Reading_byReading" ON "KANJIDIC_Reading"("reading");

CREATE TABLE "KANJIDIC_Kunyomi" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "yomi" TEXT NOT NULL,
  "isJouyou" BOOLEAN,
  PRIMARY KEY ("kanji", "yomi")
) WITHOUT ROWID;

CREATE INDEX "KANJIDIC_Kunyomi_byYomi" ON "KANJIDIC_Kunyomi"("yomi");

CREATE TABLE "KANJIDIC_Onyomi" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "yomi" TEXT NOT NULL,
  "type" VARCHAR(7) CHECK ("type" IN ('kan', 'go', 'tou', 'kan''you')),
  "isJouyou" BOOLEAN,
  PRIMARY KEY ("kanji", "yomi")
) WITHOUT ROWID;

CREATE INDEX "KANJIDIC_Onyomi_byYomi" ON "KANJIDIC_Onyomi"("yomi");

CREATE TABLE "KANJIDIC_Meaning" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "language" CHAR(3) NOT NULL DEFAULT "eng",
  "meaning" TEXT NOT NULL,
  PRIMARY KEY ("kanji", "language", "meaning")
) WITHOUT ROWID;

CREATE INDEX "KANJIDIC_Meaning_byMeaning" ON "KANJIDIC_Meaning"("meaning");

CREATE TABLE "KANJIDIC_Nanori" (
  "kanji" CHAR(1) NOT NULL REFERENCES "KANJIDIC_Character"("literal"),
  "nanori" TEXT NOT NULL,
  PRIMARY KEY ("kanji", "nanori")
) WITHOUT ROWID;

CREATE INDEX "KANJIDIC_Nanori_byNanori" ON "KANJIDIC_Nanori"("nanori");