40 lines
1.3 KiB
SQL
40 lines
1.3 KiB
SQL
CREATE TABLE IF NOT EXISTS employee_publications (
|
|
id SERIAL PRIMARY KEY,
|
|
employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
|
|
publication_id VARCHAR(64),
|
|
title TEXT NOT NULL,
|
|
year INTEGER,
|
|
publication_type VARCHAR(64),
|
|
language VARCHAR(16),
|
|
status INTEGER,
|
|
url TEXT,
|
|
doi_url TEXT,
|
|
other_url TEXT,
|
|
document_url TEXT,
|
|
citation_text TEXT,
|
|
annotation JSONB,
|
|
description JSONB,
|
|
authors JSONB,
|
|
raw_data JSONB,
|
|
source_hash VARCHAR(64) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT uq_employee_publications_employee_publication UNIQUE (employee_id, publication_id),
|
|
CONSTRAINT uq_employee_publications_employee_source_hash UNIQUE (employee_id, source_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_publications_employee_id
|
|
ON employee_publications (employee_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_publications_publication_id
|
|
ON employee_publications (publication_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_publications_doi_url
|
|
ON employee_publications (doi_url);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_publications_year
|
|
ON employee_publications (year);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_employee_publications_publication_type
|
|
ON employee_publications (publication_type);
|