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')),
UNIQUE ("subtitle_path" ASC) ON CONFLICT FAIL
);
CREATE UNIQUE INDEX "unique_subtitle_path"
ON "subtitles" (
"subtitle_path" ASC,
"user_id" ASC
);
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 INDEX "idx_ignored_user_id_word_id"
ON "user_ignored_words" (
"user_id" ASC,
"word_id" ASC
);
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") ON DELETE NO ACTION ON UPDATE NO ACTION
);
INSERT INTO "sqlite_sequence" (name, seq) VALUES ('user_sentence_sense_ref', '310');
CREATE UNIQUE INDEX "unique_sentence_sense"
ON "user_sentence_sense_ref" (
"user_id" ASC,
"user_sentence_id" ASC,
"word_id" ASC,
"sense_id" ASC,
"example_id" ASC
);
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
);
INSERT INTO "sqlite_sequence" (name, seq) VALUES ('user_sentences', '432');
CREATE UNIQUE INDEX "unique_sentence"
ON "user_sentences" (
"user_id" ASC,
"text_original" ASC
);
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
);
INSERT INTO "sqlite_sequence" (name, seq) VALUES ('user_words', '313');
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 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
);
INSERT INTO "sqlite_sequence" (name, seq) VALUES ('videos', '412');
CREATE UNIQUE INDEX "unique_video_path"
ON "videos" (
"video_path" ASC,
"user_id" ASC
);
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,
"inflection" TEXT
);
INSERT INTO "sqlite_sequence" (name, seq) VALUES ('words', '917009');
CREATE INDEX "idx_word"
ON "words" (
"word" ASC
);
生成ER关系图,svg格式