From 59af5ddd0a9587eaf53b4f6965c0d6290295ce55 Mon Sep 17 00:00:00 2001
From: Bryan Newbold <bnewbold@archive.org>
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/2019-12-19-060141_init/up.sql')

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

