forked from janek/compareware
22 lines
1.1 KiB
MySQL
22 lines
1.1 KiB
MySQL
|
ALTER TABLE tags DROP COLUMN item_id;
|
||
|
|
ALTER TABLE tags ADD COLUMN item_id TEXT NOT NULL;
|
||
|
|
ALTER TABLE tags ADD COLUMN created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL;
|
||
|
|
ALTER TABLE tags ADD COLUMN updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL;
|
||
|
|
DROP TABLE items;
|
||
|
|
CREATE FUNCTION set_updated_at_to_now() RETURNS TRIGGER AS $$BEGIN
|
||
|
|
NEW.updated_at = NOW();
|
||
|
|
RETURN NEW;
|
||
|
|
END;$$ language PLPGSQL;
|
||
|
|
CREATE TABLE items (
|
||
|
|
wikidataid TEXT NOT NULL,
|
||
|
|
description TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
CREATE INDEX tags_item_id_index ON tags (item_id);
|
||
|
|
CREATE INDEX tags_created_at_index ON tags (created_at);
|
||
|
|
CREATE TRIGGER update_tags_updated_at BEFORE UPDATE ON tags FOR EACH ROW EXECUTE FUNCTION set_updated_at_to_now();
|
||
|
|
CREATE INDEX items_created_at_index ON items (created_at);
|
||
|
|
CREATE TRIGGER update_items_updated_at BEFORE UPDATE ON items FOR EACH ROW EXECUTE FUNCTION set_updated_at_to_now();
|
||
|
|
ALTER TABLE tags ADD CONSTRAINT tags_ref_item_id FOREIGN KEY (item_id) REFERENCES items (id) ON DELETE CASCADE;
|