From af140307a025738767e740fea8da8d15e20fb983 Mon Sep 17 00:00:00 2001
From: Bryan Newbold <bnewbold@archive.org>
Date: Wed, 5 Aug 2020 13:10:56 -0700
Subject: grobid+pdftext missing catch-up commands

---
 sql/dump_unextracted_pdf.sql         | 11 ++++++-----
 sql/dump_unextracted_pdf_petabox.sql | 18 ++++++++++++++++++
 sql/dump_ungrobid_pdf.sql            | 13 ++++++++-----
 sql/dump_ungrobid_pdf_petabox.sql    | 17 +++++++++++++++++
 4 files changed, 49 insertions(+), 10 deletions(-)
 create mode 100644 sql/dump_unextracted_pdf_petabox.sql
 create mode 100644 sql/dump_ungrobid_pdf_petabox.sql

(limited to 'sql')

diff --git a/sql/dump_unextracted_pdf.sql b/sql/dump_unextracted_pdf.sql
index 5c7e7a7..7b5e823 100644
--- a/sql/dump_unextracted_pdf.sql
+++ b/sql/dump_unextracted_pdf.sql
@@ -1,19 +1,20 @@
 
 -- Run like:
---   psql sandcrawler < dump_unextracted_pdf.sql > dump_unextracted_pdf.2019-09-23.json
+--   psql sandcrawler < dump_unextracted_pdf.sql
 
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
 
 COPY (
-  SELECT row_to_json(cdx)
+  SELECT DISTINCT ON (cdx.sha1hex) row_to_json(cdx)
   FROM grobid
   LEFT JOIN cdx ON grobid.sha1hex = cdx.sha1hex
   --LEFT JOIN fatcat_file ON grobid.sha1hex = fatcat_file.sha1hex
+  LEFT JOIN pdf_meta ON grobid.sha1hex = pdf_meta.sha1hex
   WHERE cdx.sha1hex IS NOT NULL
-  --AND fatcat_file.sha1hex IS NOT NULL
+    --AND fatcat_file.sha1hex IS NOT NULL
+    AND pdf_meta.sha1hex IS NULL
 )
---TO '/grande/snapshots/dump_unextracted_pdf.2020-06-25.json';
-TO STDOUT
+TO '/grande/snapshots/dump_unextracted_pdf.fatcat.2020-07-22.json'
 WITH NULL '';
 
 ROLLBACK;
diff --git a/sql/dump_unextracted_pdf_petabox.sql b/sql/dump_unextracted_pdf_petabox.sql
new file mode 100644
index 0000000..7db34fb
--- /dev/null
+++ b/sql/dump_unextracted_pdf_petabox.sql
@@ -0,0 +1,18 @@
+
+-- Run like:
+--   psql sandcrawler < dump_unextracted_pdf_petabox.sql
+
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
+
+COPY (
+  SELECT DISTINCT ON (petabox.sha1hex) row_to_json(petabox)
+  FROM grobid
+  LEFT JOIN petabox ON grobid.sha1hex = petabox.sha1hex
+  LEFT JOIN pdf_meta ON grobid.sha1hex = pdf_meta.sha1hex
+  WHERE petabox.sha1hex IS NOT NULL
+    AND pdf_meta.sha1hex IS NULL
+)
+TO '/grande/snapshots/dump_unextracted_pdf_petabox.2020-07-22.json'
+WITH NULL '';
+
+ROLLBACK;
diff --git a/sql/dump_ungrobid_pdf.sql b/sql/dump_ungrobid_pdf.sql
index 3e6d782..e65edd5 100644
--- a/sql/dump_ungrobid_pdf.sql
+++ b/sql/dump_ungrobid_pdf.sql
@@ -1,15 +1,18 @@
 
 -- Run like:
---   psql sandcrawler < dump_ungrobid_pdf.sql | sort -S 4G | uniq -w 40 | cut -f2 > dump_ungrobid_pdf.2019-09-23.json
+--   psql sandcrawler < dump_ungrobid_pdf.sql
 
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
 
 COPY (
-    SELECT cdx.sha1hex, row_to_json(cdx) FROM cdx
-    WHERE cdx.mimetype = 'application/pdf'
-    AND NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex)
+  SELECT DISTINCT ON (cdx.sha1hex) row_to_json(cdx)
+  FROM cdx
+  WHERE cdx.mimetype = 'application/pdf'
+  AND NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex AND grobid.status IS NOT NULL)
+  -- uncomment/comment this to control whether only fatcat files are included
+  --AND EXISTS (SELECT fatcat_file.sha1hex FROM fatcat_file WHERE cdx.sha1hex = fatcat_file.sha1hex)
 )
-TO STDOUT
+TO '/grande/snapshots/dump_ungrobided_pdf.fatcat.2020-08-04.json'
 WITH NULL '';
 
 ROLLBACK;
diff --git a/sql/dump_ungrobid_pdf_petabox.sql b/sql/dump_ungrobid_pdf_petabox.sql
new file mode 100644
index 0000000..f758ec2
--- /dev/null
+++ b/sql/dump_ungrobid_pdf_petabox.sql
@@ -0,0 +1,17 @@
+
+-- Run like:
+--   psql sandcrawler < dump_ungrobid_pdf_petabox.sql
+
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
+
+COPY (
+  SELECT DISTINCT ON (petabox.sha1hex) row_to_json(petabox)
+  FROM petabox
+  WHERE NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE petabox.sha1hex = grobid.sha1hex AND grobid.status IS NOT NULL)
+  -- uncomment/comment this to control whether only fatcat files are included
+  AND EXISTS (SELECT fatcat_file.sha1hex FROM fatcat_file WHERE petabox.sha1hex = fatcat_file.sha1hex)
+)
+TO '/grande/snapshots/dump_ungrobided_pdf_petabox.2020-08-04.json'
+WITH NULL '';
+
+ROLLBACK;
-- 
cgit v1.2.3