From 3ed9fe3aac4b79d16ffa9d8008fefb0363a88468 Mon Sep 17 00:00:00 2001
From: Bryan Newbold <bnewbold@archive.org>
Date: Fri, 15 May 2020 18:06:55 -0700
Subject: first pass at issue-db tool

---
 schema/issue_db.sql | 35 +++++++++++++++++++++++++++++++++++
 1 file changed, 35 insertions(+)
 create mode 100644 schema/issue_db.sql

(limited to 'schema/issue_db.sql')

diff --git a/schema/issue_db.sql b/schema/issue_db.sql
new file mode 100644
index 0000000..e07e97e
--- /dev/null
+++ b/schema/issue_db.sql
@@ -0,0 +1,35 @@
+
+CREATE TABLE IF NOT EXISTS sim_pub (
+    sim_pubid TEXT NOT NULL PRIMARY KEY,
+    pub_collection TEXT NOT NULL UNIQUE,
+    title TEXT NOT NULL,
+    issn TEXT,
+    pub_type TEXT,
+    publisher TEXT,
+    container_issnl TEXT,
+    container_ident TEXT,
+    wikidata_qid TEXT
+);
+
+CREATE TABLE IF NOT EXISTS sim_issue (
+    issue_item TEXT NOT NULL PRIMARY KEY,
+    sim_pubid NOT NULL,
+    year INTEGER,
+    volume TEXT,
+    issue TEXT,
+    first_page INTEGER,
+    last_page INTEGER,
+    release_count INTEGER,
+    FOREIGN KEY(sim_pubid) REFERENCES sim_pub(sim_pubid)
+);
+
+-- intent here is to capture how many releases are just not getting matched due
+-- to missing issue metadata
+CREATE TABLE IF NOT EXISTS release_counts (
+    sim_pubid TEXT NOT NULL PRIMARY KEY,
+    year TEXT,
+    volume TEXT,
+    year_in_sim BOOLEAN,
+    release_count INTEGER,
+    FOREIGN KEY(sim_pubid) REFERENCES sim_pub(sim_pubid)
+);
-- 
cgit v1.2.3

