aboutsummaryrefslogtreecommitdiffstats
path: root/sql/migrations
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@archive.org>2021-10-29 18:38:17 -0700
committerBryan Newbold <bnewbold@archive.org>2021-11-04 17:19:52 -0700
commit59af5ddd0a9587eaf53b4f6965c0d6290295ce55 (patch)
tree34a8ab774f99e204d5875483e0520e66fc159a6c /sql/migrations
parenta859fddb227872ce52f06af1dd9fb80987f348c4 (diff)
downloadsandcrawler-59af5ddd0a9587eaf53b4f6965c0d6290295ce55.tar.gz
sandcrawler-59af5ddd0a9587eaf53b4f6965c0d6290295ce55.zip
add grobid_refs and crossref_with_refs to sandcrawler-db SQL schema
Diffstat (limited to 'sql/migrations')
-rw-r--r--sql/migrations/2019-12-19-060141_init/up.sql21
1 files changed, 21 insertions, 0 deletions
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';