Lucien Cartier-Tilet
c5f5e770e2
All checks were successful
continuous-integration/drone/push Build is passing
This commit changes the primary key of words to a serial number. That way, two words with the same normalized value will not collide with one another. It also adds two new tables in the database: - Users following languages - Users learning words The former can represent two stages of learning a word: - Either the user is currently learning it - Or they consider they know it and don’t need to work on it anymore These two new tables now have their API query available through the GraphQL API. This commit also fixes the issue of word-related tables and types not being dropped when resetting the database.
58 lines
1.5 KiB
SQL
58 lines
1.5 KiB
SQL
-- Your SQL goes here
|
|
CREATE TYPE PartOfSpeech as ENUM ('ADJ', 'ADP', 'ADV', 'AUX', 'CCONJ', 'DET', 'INTJ', 'NOUN', 'NUM', 'PART', 'PRON', 'PROPN', 'PUNCT', 'SCONJ', 'SYM', 'VERB', 'X');
|
|
CREATE TYPE WordRelationship as ENUM('def', 'related');
|
|
CREATE TYPE WordLearningStatus as ENUM('learning', 'learned');
|
|
|
|
CREATE TABLE Words (
|
|
id UUID DEFAULT uuid_generate_v4 () PRIMARY KEY,
|
|
norm VARCHAR(255) NOT NULL, -- normalized word, generally in latin alphabet
|
|
native VARCHAR(255),
|
|
lemma UUID
|
|
REFERENCES Words(id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE SET NULL,
|
|
language UUID
|
|
REFERENCES Languages(id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
NOT NULL,
|
|
partofspeech PartOfSpeech NOT NULL,
|
|
audio VARCHAR(511),
|
|
video VARCHAR(511),
|
|
image VARCHAR(511),
|
|
description TEXT, -- Markdown
|
|
etymology TEXT, -- Markdown
|
|
lusage TEXT, -- Markdown
|
|
morphology TEXT -- Markdown
|
|
);
|
|
|
|
CREATE TABLE WordRelation (
|
|
id SERIAL PRIMARY KEY,
|
|
wordsource UUID
|
|
REFERENCES Words(id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
NOT NULL,
|
|
wordtarget UUID
|
|
REFERENCES Words(id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
NOT NULL,
|
|
relationship WordRelationship NOT NULL
|
|
);
|
|
|
|
CREATE TABLE WordLearning (
|
|
id SERIAL PRIMARY KEY,
|
|
word UUID
|
|
REFERENCES Words(id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
NOT NULL,
|
|
userid VARCHAR(31)
|
|
REFERENCES Users(id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE
|
|
NOT NULL,
|
|
status WordLearningStatus DEFAULT 'learning' NOT NULL
|
|
);
|