diff options
Diffstat (limited to 'extra/sql_dumps')
| -rw-r--r-- | extra/sql_dumps/README.md | 2 | ||||
| -rw-r--r-- | extra/sql_dumps/dump_idents.sql | 16 | ||||
| -rwxr-xr-x | extra/sql_dumps/ident_table_snapshot.sh | 3 |
3 files changed, 19 insertions, 2 deletions
diff --git a/extra/sql_dumps/README.md b/extra/sql_dumps/README.md index f24d3d92..1fa37981 100644 --- a/extra/sql_dumps/README.md +++ b/extra/sql_dumps/README.md @@ -30,7 +30,7 @@ Dump locally to stdout, eg: Or, in production: # production, as 'fatcat' user, in /srv/fatcat/src/rust: - cat /tmp/fatcat_ident_releases.tsv | ./target/release/fatcat-export release --expand files,filesets,webcaptures,container -j8 | pigz > /srv/fatcat/snapshots/release_export_expanded.json.gz + cat /tmp/fatcat_ident_releases_by_work.tsv | ./target/release/fatcat-export releasebywork --expand files,filesets,webcaptures,container -j8 | pigz > /srv/fatcat/snapshots/release_export_expanded.json.gz cat /tmp/fatcat_ident_creators.tsv | ./target/release/fatcat-export creator -j8 | pigz > /srv/fatcat/snapshots/creator_export.json.gz cat /tmp/fatcat_ident_containers.tsv | ./target/release/fatcat-export container -j8 | pigz > /srv/fatcat/snapshots/container_export.json.gz cat /tmp/fatcat_ident_files.tsv | ./target/release/fatcat-export file -j8 | pigz > /srv/fatcat/snapshots/file_export.json.gz 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; diff --git a/extra/sql_dumps/ident_table_snapshot.sh b/extra/sql_dumps/ident_table_snapshot.sh index dbd4caf0..b287a0ce 100755 --- a/extra/sql_dumps/ident_table_snapshot.sh +++ b/extra/sql_dumps/ident_table_snapshot.sh @@ -32,6 +32,7 @@ tar -C /tmp -c --verbose \ fatcat_ident_filesets.tsv \ fatcat_ident_webcaptures.tsv \ fatcat_ident_releases.tsv \ - fatcat_ident_works.tsv + fatcat_ident_works.tsv \ + fatcat_ident_releases_by_work.tsv echo "Done: $OUTFILE" |