From 3ed9fe3aac4b79d16ffa9d8008fefb0363a88468 Mon Sep 17 00:00:00 2001 From: Bryan Newbold 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