first pass at issue-db tool

1 files changed, 35 insertions, 0 deletions

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)
+);