CREATE TABLE "words" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"word" TEXT NOT NULL COLLATE NOCASE,
"word_linked_id" INTEGER,
"word_linked" TEXT COLLATE NOCASE,
"lemma" TEXT,
"collins_frequency" integer DEFAULT 0,
"coca" integer DEFAULT 0,
"ielts" integer DEFAULT 0,
"gre" integer DEFAULT 0,
"toefl" integer DEFAULT 0,
"cet4" integer DEFAULT 0,
"cet6" integer DEFAULT 0,
"tem4" integer DEFAULT 0,
"tem8" integer DEFAULT 0,
"kaoyan" integer DEFAULT 0,
"sense_proportion" TEXT,
"concise_sense" TEXT,
"inflection" TEXT
);
CREATE TABLE "coca" (
"id" INTEGER NOT NULL,
"word_id" INTEGER NOT NULL,
"pos" TEXT,
"pos_coca_rank" integer,
PRIMARY KEY ("id"),
FOREIGN KEY ("word_id") REFERENCES "words" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE ("word_id", "pos", "pos_coca_rank") ON CONFLICT IGNORE
);
CREATE TABLE "concise_dict" (
"id" INTEGER NOT NULL,
"word_id" INTEGER,
"pos" TEXT,
"concise_definition" TEXT,
PRIMARY KEY ("id"),
FOREIGN KEY ("word_id") REFERENCES "words" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE ("word_id", "pos", "concise_definition") ON CONFLICT IGNORE
);
CREATE TABLE "dictionaries" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL,
"number_of_entries" INTEGER,
"create_time" DATE
);
CREATE TABLE "entries" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"word_id" INTEGER NOT NULL,
"dictionary_id" INTEGER NOT NULL,
"pos" TEXT NOT NULL,
"frequency" TEXT,
"pronunciation_form" TEXT,
"pronunciation" TEXT,
"pronunciation_mp3_bre" TEXT,
"pronunciation_mp3_ame" TEXT,
FOREIGN KEY ("word_id") REFERENCES "words" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "family" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"word_id" INTEGER NOT NULL,
"dictionary_id" INTEGER NOT NULL,
"family_pos" TEXT,
"family_words" TEXT,
FOREIGN KEY ("word_id") REFERENCES "words" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "inflections" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"entry_id" INTEGER NOT NULL,
"inflection_type" TEXT,
"inflection" TEXT,
FOREIGN KEY ("entry_id") REFERENCES "entries" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "pronunciations" (
"id" INTEGER NOT NULL,
"word_id" INTEGER NOT NULL,
"type" TEXT,
"url" TEXT,
PRIMARY KEY ("id"),
FOREIGN KEY ("word_id") REFERENCES "words" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE ("word_id", "type", "url") ON CONFLICT IGNORE
);
CREATE TABLE "senses" (
"id" INTEGER NOT NULL,
"entry_id" INTEGER NOT NULL,
"signpost_original" TEXT,
"signpost_translated" TEXT,
"gram" TEXT,
"sense_original" TEXT NOT NULL,
"sense_translated" TEXT,
"sense_collocation" TEXT,
"sense_synonyms" TEXT,
"sense_antonyms" TEXT,
PRIMARY KEY ("id"),
FOREIGN KEY ("entry_id") REFERENCES "entries" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "sense_examples" (
"id" INTEGER NOT NULL,
"sense_id" INTEGER NOT NULL,
"example_original" TEXT NOT NULL,
"example_translated" TEXT,
"example_mp3" TEXT,
"example_collocation" TEXT,
"example_collocation_gloss" TEXT,
"example_collocation_cn" TEXT,
PRIMARY KEY ("id"),
FOREIGN KEY ("sense_id") REFERENCES "senses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "users" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"username" TEXT NOT NULL COLLATE NOCASE,
"password" TEXT NOT NULL,
"phone_number" TEXT,
"email" TEXT COLLATE NOCASE,
"create_time" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"validity_date" DATE,
"identity_type" integer,
"last_login_time" TIMESTAMP,
UNIQUE ("username" ASC, "phone_number" ASC, "email" ASC) ON CONFLICT FAIL
);
CREATE TABLE "subtitles" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"user_id" INTEGER NOT NULL,
"subtitle_path" TEXT,
"subtitle_title" TEXT,
"video_id" INTEGER,
"create_time" TIMESTAMP DEFAULT (datetime('now', 'localtime')),
FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
UNIQUE ("subtitle_path" ASC) ON CONFLICT FAIL
);
CREATE TABLE "sentences" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"subtitle_id" INTEGER NOT NULL,
"text_original" TEXT NOT NULL,
"text_translated" TEXT,
"start_time" REAL,
"end_time" REAL,
FOREIGN KEY ("subtitle_id") REFERENCES "subtitles" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "thesaurus" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"entry_id" INTEGER NOT NULL,
"thesaurus_word" TEXT NOT NULL,
"definition_original" TEXT,
"definition_translated" TEXT,
FOREIGN KEY ("entry_id") REFERENCES "entries" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "thesaurus_examples" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"thesaurus_id" INTEGER NOT NULL,
"example_original" TEXT NOT NULL,
"example_translated" TEXT,
FOREIGN KEY ("thesaurus_id") REFERENCES "thesaurus" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "user_ignored_words" (
"id" INTEGER NOT NULL,
"user_id" INTEGER NOT NULL,
"word_id" INTEGER NOT NULL,
PRIMARY KEY ("id"),
FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("word_id") REFERENCES "words" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "user_sentences" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"user_id" INTEGER NOT NULL,
"video_path" TEXT COLLATE NOCASE,
"video_title" TEXT COLLATE NOCASE,
"subtitle_path" TEXT COLLATE NOCASE,
"subtitle_title" TEXT COLLATE NOCASE,
"text_original" TEXT NOT NULL COLLATE NOCASE,
"text_translated" TEXT,
"video_clip" TEXT COLLATE NOCASE,
"audio_clip" TEXT COLLATE NOCASE,
"start_time" REAL,
"end_time" REAL,
"sentence_note" TEXT COLLATE NOCASE,
"create_time" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "user_sentence_sense_ref" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"user_id" INTEGER NOT NULL,
"user_sentence_id" INTEGER NOT NULL,
"word_id" INTEGER NOT NULL,
"sense_id" INTEGER NOT NULL,
"example_id" INTEGER,
"create_time" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY ("sense_id") REFERENCES "senses" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("example_id") REFERENCES "sense_examples" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("user_sentence_id") REFERENCES "user_sentences" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT "fk_user_sentence_sense_ref_words_5" FOREIGN KEY ("word_id") REFERENCES "words" ("id")
);
CREATE TABLE "user_words" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"user_id" INTEGER NOT NULL,
"word_id" INTEGER NOT NULL,
"word" TEXT NOT NULL COLLATE NOCASE,
"word_note" TEXT,
"familiarity" integer,
"query_count" integer,
"create_time" DATE,
FOREIGN KEY ("word_id") REFERENCES "words" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "videos" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"user_id" integer,
"video_path" TEXT,
"video_title" TEXT,
"video_description" TEXT,
"video_duration" integer,
"create_time" TEXT,
FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
UNIQUE ("video_path" ASC) ON CONFLICT FAIL
);
CREATE INDEX "idx_ignored_user_id_word_id"
ON "user_ignored_words" (
"user_id" ASC,
"word_id" ASC
);
CREATE INDEX "idx_user_id_word"
ON "user_words" (
"user_id" ASC,
"word" ASC
);
CREATE UNIQUE INDEX "idx_user_id_word_id"
ON "user_words" (
"user_id" ASC,
"word_id" ASC
);
CREATE INDEX "idx_word"
ON "words" (
"word" ASC
);
CREATE UNIQUE INDEX "unique_sentence"
ON "user_sentences" (
"user_id" ASC,
"text_original" ASC
);
CREATE UNIQUE INDEX "unique_sentence_sense"
ON "user_sentence_sense_ref" (
"user_id",
"user_sentence_id",
"word_id",
"sense_id",
"example_id"
);
CREATE UNIQUE INDEX "unique_subtitle_path"
ON "subtitles" (
"subtitle_path" ASC,
"user_id" ASC
);
CREATE UNIQUE INDEX "unique_subtitle_sentence"
ON "sentences" (
"text_original" ASC
);
CREATE UNIQUE INDEX "unique_video_path"
ON "videos" (
"video_path" ASC,
"user_id" ASC
);
生成ER关系图