From 59af5ddd0a9587eaf53b4f6965c0d6290295ce55 Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Fri, 29 Oct 2021 18:38:17 -0700 Subject: add grobid_refs and crossref_with_refs to sandcrawler-db SQL schema --- sql/migrations/2019-12-19-060141_init/up.sql | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'sql/migrations') diff --git a/sql/migrations/2019-12-19-060141_init/up.sql b/sql/migrations/2019-12-19-060141_init/up.sql index 3e08ffd..254c08a 100644 --- a/sql/migrations/2019-12-19-060141_init/up.sql +++ b/sql/migrations/2019-12-19-060141_init/up.sql @@ -219,3 +219,24 @@ CREATE TABLE IF NOT EXISTS crossref ( record JSON NOT NULL, PRIMARY KEY(doi) ); + +CREATE TABLE IF NOT EXISTS grobid_refs ( + source TEXT NOT NULL CHECK (octet_length(source) >= 1), + source_id TEXT NOT NULL CHECK (octet_length(source_id) >= 1), + source_ts TIMESTAMP WITH TIME ZONE, + updated TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + refs_json JSONB NOT NULL, + PRIMARY KEY(source, source_id) +); + +CREATE OR REPLACE VIEW crossref_with_refs (doi, indexed, record, source_ts, refs_json) AS + SELECT + crossref.doi as doi, + crossref.indexed as indexed, + crossref.record as record, + grobid_refs.source_ts as source_ts, + grobid_refs.refs_json as refs_json + FROM crossref + LEFT JOIN grobid_refs ON + grobid_refs.source_id = crossref.doi + AND grobid_refs.source = 'crossref'; -- cgit v1.2.3