1
0
Fork 0
This repository has been archived on 2023-03-28. You can view files and clone it, but cannot push or open issues or pull requests.
fedihub-website/migrations/2020-10-21-044318_create_em.../up.sql

67 lines
1.9 KiB
SQL

-- Your SQL goes here
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
image VARCHAR NOT NULL,
name VARCHAR NOT NULL,
role VARCHAR NOT NULL
);
CREATE TABLE employee_infos (
id SERIAL PRIMARY KEY,
employee_id SERIAL NOT NULL,
locale VARCHAR NOT NULL,
name VARCHAR NOT NULL,
role VARCHAR NOT NULL,
CONSTRAINT employee_id_fk FOREIGN KEY (employee_id) REFERENCES employees(id)
);
CREATE TABLE employee_contacts (
id SERIAL PRIMARY KEY,
employee_id SERIAL NOT NULL,
name VARCHAR NOT NULL,
link VARCHAR NOT NULL,
CONSTRAINT employee_id_fk FOREIGN KEY (employee_id) REFERENCES employees(id)
);
CREATE UNIQUE INDEX index_employee_infos_on_employee_id_and_locale
ON employee_infos USING btree (employee_id, locale);
CREATE UNIQUE INDEX index_employee_contacts_on_employee_id
ON employee_contacts USING btree (employee_id, name);
-- Insert data
WITH employee_ids AS (
INSERT INTO employees (image, name, role)
VALUES ('/database_files/kotovalexarian.jpg', 'Alex Kotov', 'Founder')
RETURNING id
AS employee_id
)
INSERT INTO employee_contacts (employee_id, name, link) VALUES
((SELECT employee_id FROM employee_ids),
'Matrix',
'https://matrix.to/#/@kotovalexarian:fedihub.com'),
((SELECT employee_id FROM employee_ids),
'Twitter',
'https://twitter.com/kotovalexarian'),
((SELECT employee_id FROM employee_ids),
'Facebook',
'https://fb.com/kotovalexarian');
WITH employee_ids AS (
INSERT INTO employees (image, name, role)
VALUES ('/database_files/xuhcc.png', 'Kirill Goncharov', 'System administrator')
RETURNING id
AS employee_id
)
INSERT INTO employee_contacts (employee_id, name, link) VALUES
((SELECT employee_id FROM employee_ids),
'Matrix',
'https://matrix.to/#/@xuhcc:matrix.org'),
((SELECT employee_id FROM employee_ids),
'GitHub',
'https://github.com/xuhcc');