40 lines
No EOL
2.9 KiB
SQL
40 lines
No EOL
2.9 KiB
SQL
CREATE TABLE "term_topics" (
|
|
"term_id" uuid NOT NULL,
|
|
"topic_id" uuid NOT NULL,
|
|
CONSTRAINT "term_topics_term_id_topic_id_pk" PRIMARY KEY("term_id","topic_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "topics" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"slug" varchar(50) NOT NULL,
|
|
"label" text NOT NULL,
|
|
"description" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "topics_slug_unique" UNIQUE("slug")
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "language_pairs" DISABLE ROW LEVEL SECURITY;--> statement-breakpoint
|
|
DROP TABLE "language_pairs" CASCADE;--> statement-breakpoint
|
|
ALTER TABLE "terms" DROP CONSTRAINT "terms_synset_id_unique";--> statement-breakpoint
|
|
ALTER TABLE "terms" DROP CONSTRAINT "pos_check";--> statement-breakpoint
|
|
DROP INDEX "idx_term_glosses_term";--> statement-breakpoint
|
|
DROP INDEX "idx_terms_pos";--> statement-breakpoint
|
|
DROP INDEX "idx_translations_lang";--> statement-breakpoint
|
|
ALTER TABLE "decks" ADD COLUMN "type" varchar(20) NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "terms" ADD COLUMN "source" varchar(50);--> statement-breakpoint
|
|
ALTER TABLE "terms" ADD COLUMN "source_id" text;--> statement-breakpoint
|
|
ALTER TABLE "translations" ADD COLUMN "cefr_level" varchar(2);--> statement-breakpoint
|
|
ALTER TABLE "term_topics" ADD CONSTRAINT "term_topics_term_id_terms_id_fk" FOREIGN KEY ("term_id") REFERENCES "public"."terms"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "term_topics" ADD CONSTRAINT "term_topics_topic_id_topics_id_fk" FOREIGN KEY ("topic_id") REFERENCES "public"."topics"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "idx_decks_type" ON "decks" USING btree ("type","source_language");--> statement-breakpoint
|
|
CREATE INDEX "idx_terms_source_pos" ON "terms" USING btree ("source","pos");--> statement-breakpoint
|
|
CREATE INDEX "idx_translations_lang" ON "translations" USING btree ("language_code","cefr_level","term_id");--> statement-breakpoint
|
|
ALTER TABLE "deck_terms" DROP COLUMN "added_at";--> statement-breakpoint
|
|
ALTER TABLE "decks" DROP COLUMN "is_public";--> statement-breakpoint
|
|
ALTER TABLE "terms" DROP COLUMN "synset_id";--> statement-breakpoint
|
|
ALTER TABLE "terms" ADD CONSTRAINT "unique_source_id" UNIQUE("source","source_id");--> statement-breakpoint
|
|
ALTER TABLE "decks" ADD CONSTRAINT "deck_type_check" CHECK ("decks"."type" IN ('grammar', 'media'));--> statement-breakpoint
|
|
ALTER TABLE "term_glosses" ADD CONSTRAINT "language_code_check" CHECK ("term_glosses"."language_code" IN ('en', 'it'));--> statement-breakpoint
|
|
ALTER TABLE "terms" ADD CONSTRAINT "pos_check" CHECK ("terms"."pos" IN ('noun', 'verb'));--> statement-breakpoint
|
|
ALTER TABLE "translations" ADD CONSTRAINT "language_code_check" CHECK ("translations"."language_code" IN ('en', 'it'));--> statement-breakpoint
|
|
ALTER TABLE "translations" ADD CONSTRAINT "cefr_check" CHECK ("translations"."cefr_level" IN ('A1', 'A2', 'B1', 'B2', 'C1', 'C2')); |