BEGIN;


CREATE TEMPORARY TABLE tmp_oversize_contents (sha1 sha1 PRIMARY KEY);

COPY tmp_oversize_contents
FROM PROGRAM 'xzcat /tmp/oversize-contents.txt.xz | sed ''s/^/\\\\x/''';

INSERT INTO skipped_content
        (sha1, sha1_git, sha256, blake2s256, length, ctime, status, reason)
    SELECT sha1, sha1_git, sha256, blake2s256, length, ctime,
           'absent', 'Content too large'
    FROM content
    WHERE sha1 IN (SELECT sha1 FROM tmp_oversize_contents)
ON CONFLICT (sha1_git) DO UPDATE SET
    sha1 = EXCLUDED.sha1,
    sha1_git = EXCLUDED.sha1_git,
    sha256 = EXCLUDED.sha256,
    blake2s256 = EXCLUDED.blake2s256,
    length = EXCLUDED.length,
    ctime = LEAST(skipped_content.ctime, EXCLUDED.ctime)
;

DELETE FROM content_ctags
    WHERE id in (SELECT sha1 FROM tmp_oversize_contents);
DELETE FROM content_fossology_license
    WHERE id in (SELECT sha1 FROM tmp_oversize_contents);
DELETE FROM content_language
    WHERE id in (SELECT sha1 FROM tmp_oversize_contents);
DELETE FROM content_mimetype
    WHERE id in (SELECT sha1 FROM tmp_oversize_contents);
DELETE FROM cache_content_revision
    WHERE content in (SELECT sha1_git
                      FROM tmp_oversize_contents NATURAL LEFT JOIN content);

DELETE FROM content WHERE sha1 IN (SELECT sha1 FROM tmp_oversize_contents);


COMMIT;