4 files changed, 49 insertions, 10 deletions
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;