aboutsummaryrefslogtreecommitdiffstats
path: root/extra/sql_dumps
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2020-07-30 18:16:52 -0700
committerBryan Newbold <bnewbold@robocracy.org>2020-08-04 18:03:33 -0700
commiteabab2e2a44b715c914182544e57899323234f86 (patch)
tree851bc11e31a5c2467a80fc5f414e90ba1bdac246 /extra/sql_dumps
parent4702bee24dde8bae64df76ad411a6d8329cc9bdf (diff)
downloadfatcat-eabab2e2a44b715c914182544e57899323234f86.tar.gz
fatcat-eabab2e2a44b715c914182544e57899323234f86.zip
WIP: sorted release ident dumps
Diffstat (limited to 'extra/sql_dumps')
-rw-r--r--extra/sql_dumps/dump_idents.sql16
1 files changed, 16 insertions, 0 deletions
diff --git a/extra/sql_dumps/dump_idents.sql b/extra/sql_dumps/dump_idents.sql
index e8126347..d9777ea1 100644
--- a/extra/sql_dumps/dump_idents.sql
+++ b/extra/sql_dumps/dump_idents.sql
@@ -10,5 +10,21 @@ COPY (SELECT id, rev_id, redirect_id FROM webcapture_ident WHERE is_live=true)
COPY (SELECT id, rev_id, redirect_id FROM release_ident WHERE is_live=true) TO '/tmp/fatcat_ident_releases.tsv' WITH NULL '';
COPY (SELECT id, rev_id, redirect_id FROM work_ident WHERE is_live=true) TO '/tmp/fatcat_ident_works.tsv' WITH NULL '';
COPY (SELECT id, editgroup_id, timestamp FROM changelog) TO '/tmp/fatcat_ident_changelog.tsv' WITH NULL '';
+COPY (
+ SELECT
+ release_ident.id,
+ release_ident.rev_id,
+ release_ident.redirect_id,
+ release_rev.work_ident_id
+ FROM
+ release_ident
+ LEFT JOIN release_rev ON release_ident.rev_id = release_rev.id
+ WHERE
+ release_ident.is_live=true
+ AND release_ident.redirect_id IS NULL
+ AND release_ident.rev_id IS NOT NULL
+ ORDER BY
+ release_rev.work_ident_id ASC NULLS LAST
+) TO '/tmp/fatcat_ident_releases_by_work.tsv' WITH NULL '';
ROLLBACK;