54 lines
2.0 KiB
MySQL
54 lines
2.0 KiB
MySQL
|
-- Migration: Add dictionaries table and dictionary_id to words
|
|||
|
|
-- This script creates the dictionaries table and adds dictionary_id field to words table
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Table: dictionaries
|
|||
|
|
-- ============================================
|
|||
|
|
CREATE TABLE IF NOT EXISTS dictionaries (
|
|||
|
|
id SERIAL PRIMARY KEY,
|
|||
|
|
name VARCHAR(255) NOT NULL
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- Insert default dictionary "Все слова" with id = 0
|
|||
|
|
-- Note: PostgreSQL SERIAL starts from 1, so we need to use a workaround
|
|||
|
|
-- First, set the sequence to allow inserting 0, then insert, then reset sequence
|
|||
|
|
DO $$
|
|||
|
|
BEGIN
|
|||
|
|
-- Set sequence to -1 so next value will be 0
|
|||
|
|
PERFORM setval('dictionaries_id_seq', -1, false);
|
|||
|
|
|
|||
|
|
-- Insert the default dictionary with id = 0
|
|||
|
|
INSERT INTO dictionaries (id, name)
|
|||
|
|
VALUES (0, 'Все слова')
|
|||
|
|
ON CONFLICT (id) DO NOTHING;
|
|||
|
|
|
|||
|
|
-- Set the sequence to start from 1 (so next auto-increment will be 1)
|
|||
|
|
PERFORM setval('dictionaries_id_seq', 1, false);
|
|||
|
|
END $$;
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Alter words table: Add dictionary_id column
|
|||
|
|
-- ============================================
|
|||
|
|
ALTER TABLE words
|
|||
|
|
ADD COLUMN IF NOT EXISTS dictionary_id INTEGER DEFAULT 0 REFERENCES dictionaries(id);
|
|||
|
|
|
|||
|
|
-- Update all existing words to have dictionary_id = 0
|
|||
|
|
UPDATE words
|
|||
|
|
SET dictionary_id = 0
|
|||
|
|
WHERE dictionary_id IS NULL;
|
|||
|
|
|
|||
|
|
-- Make dictionary_id NOT NULL after setting default values
|
|||
|
|
ALTER TABLE words
|
|||
|
|
ALTER COLUMN dictionary_id SET NOT NULL,
|
|||
|
|
ALTER COLUMN dictionary_id SET DEFAULT 0;
|
|||
|
|
|
|||
|
|
-- Create index on dictionary_id for better join performance
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_words_dictionary_id ON words(dictionary_id);
|
|||
|
|
|
|||
|
|
-- ============================================
|
|||
|
|
-- Comments for documentation
|
|||
|
|
-- ============================================
|
|||
|
|
COMMENT ON TABLE dictionaries IS 'Dictionaries table storing dictionary information';
|
|||
|
|
COMMENT ON COLUMN words.dictionary_id IS 'Reference to dictionary. Default is 0 (Все слова)';
|
|||
|
|
|