DictionaryDB.db.sql

BEGIN TRANSACTION;
DROP TABLE IF EXISTS "TrainingStatistics";
CREATE TABLE IF NOT EXISTS "TrainingStatistics" (
	"TestType"	INTEGER NOT NULL,
	"ClauseId"	INTEGER NOT NULL,
	"Success"	INTEGER NOT NULL,
	"Fail"	INTEGER NOT NULL,
	"LastTraining"	TEXT NOT NULL,
	PRIMARY KEY("TestType","ClauseId"),
	FOREIGN KEY("ClauseId") REFERENCES "Clauses"("Id") ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS "Clauses";
CREATE TABLE IF NOT EXISTS "Clauses" (
	"Id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"Sound"	TEXT,
	"Word"	TEXT NOT NULL,
	"Transcription"	TEXT,
	"Context"	TEXT,
	"Added"	TEXT NOT NULL,
	"Updated"	TEXT NOT NULL,
	"Watched"	TEXT NOT NULL,
	"WatchedCount"	INTEGER NOT NULL,
	"Group"	INTEGER NOT NULL
);
DROP TABLE IF EXISTS "Relations";
CREATE TABLE IF NOT EXISTS "Relations" (
	"Id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"FromClauseId"	INTEGER NOT NULL,
	"ToClauseId"	INTEGER NOT NULL,
	"Description"	TEXT NOT NULL,
	FOREIGN KEY("FromClauseId") REFERENCES "Clauses"("Id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("ToClauseId") REFERENCES "Clauses"("Id") ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS "Translations";
CREATE TABLE IF NOT EXISTS "Translations" (
	"Id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"Index"	INTEGER NOT NULL,
	"Text"	TEXT NOT NULL,
	"Part"	INTEGER NOT NULL,
	"ClauseId"	INTEGER NOT NULL,
	FOREIGN KEY("ClauseId") REFERENCES "Clauses"("Id") ON DELETE CASCADE ON UPDATE CASCADE
);
DROP INDEX IF EXISTS "IDX_ClauseId";
CREATE INDEX IF NOT EXISTS "IDX_ClauseId" ON "Translations" (
	"ClauseId"
);
DROP INDEX IF EXISTS "IDX_ToClauseId";
CREATE INDEX IF NOT EXISTS "IDX_ToClauseId" ON "Relations" (
	"ToClauseId"
);
DROP INDEX IF EXISTS "IDX_FromClauseId";
CREATE INDEX IF NOT EXISTS "IDX_FromClauseId" ON "Relations" (
	"FromClauseId"
);
COMMIT;