Clearing submission attachment for edited submissions does not allow DB space to be reclaimed

[ktuite]

Forum posts like these demonstrate how some users are interested in reclaiming database space by deleting submission attachments rather than deleting entire submissions.

• https://forum.getodk.org/t/some-ideas-about-submissions-attachments-management/57296
• https://forum.getodk.org/t/struggles-in-keeping-the-database-to-a-reasonable-size-aka-deleting-submissions-deleting-clearing-submissions-attachments/57286

We allow users to "delete" an attachment by first clearing it (see API docs) and then having the purge script that runs daily delete any unused blobs.

We just noticed that only the current version of a submission can have its attachments cleared. That means older versions could still have links to the attachment blobs and thus not ever be purged by the purge script.

Tests showing how clearing a submission attachment allows it to be purged from the DB unless the submission has been edited:

    it.only('should purge cleared submission attachments', testService(async (service, { Blobs, oneFirst }) => {
      const asAlice = await service.login('alice');

      await asAlice.post('/v1/projects/1/forms?publish=true')
        .set('Content-Type', 'application/xml')
        .send(testData.forms.binaryType)
        .expect(200);

      await asAlice.post('/v1/projects/1/submission')
        .set('X-OpenRosa-Version', '1.0')
        .attach('xml_submission_file', Buffer.from(testData.instances.binaryType.both), { filename: 'data.xml' })
        .attach('here_is_file2.jpg', Buffer.from('this is test file two'), { filename: 'here_is_file2.jpg' })
        .attach('my_file1.mp4', Buffer.from('this is test file one'), { filename: 'my_file1.mp4' })
        .expect(201);

      let attachments = await oneFirst(sql`select count(*) from submission_attachments`);
      attachments.should.equal(2);

      let blobs = await oneFirst(sql`select count(*) from blobs`);
      blobs.should.equal(2);

      await asAlice.delete('/v1/projects/1/forms/binaryType/submissions/both/attachments/my_file1.mp4')
        .expect(200);

      blobs = await oneFirst(sql`select count(*) from blobs`);
      blobs.should.equal(2);

      await Blobs.purgeUnattached();

      attachments = await oneFirst(sql`select count(*) from submission_attachments where "blobId" is null`);
      attachments.should.equal(1);

      blobs = await oneFirst(sql`select count(*) from blobs`);
      blobs.should.equal(1);
    }));

    it.only('should purge cleared submission attachments for edited submissions', testService(async (service, { Blobs, oneFirst }) => {
      const asAlice = await service.login('alice');

      await asAlice.post('/v1/projects/1/forms?publish=true')
        .set('Content-Type', 'application/xml')
        .send(testData.forms.binaryType)
        .expect(200);

      await asAlice.post('/v1/projects/1/submission')
        .set('X-OpenRosa-Version', '1.0')
        .attach('xml_submission_file', Buffer.from(testData.instances.binaryType.both), { filename: 'data.xml' })
        .attach('here_is_file2.jpg', Buffer.from('this is test file two'), { filename: 'here_is_file2.jpg' })
        .attach('my_file1.mp4', Buffer.from('this is test file one'), { filename: 'my_file1.mp4' })
        .expect(201);

      let attachments = await oneFirst(sql`select count(*) from submission_attachments`);
      attachments.should.equal(2);

      let blobs = await oneFirst(sql`select count(*) from blobs`);
      blobs.should.equal(2);

      // attempt to update submission, will carry blobs forward to new submission def
      await asAlice.post('/v1/projects/1/submission')
        .set('X-OpenRosa-Version', '1.0')
        .attach('xml_submission_file', Buffer.from(testData.instances.binaryType.both
          .replace('id="binaryType"', 'id="binaryType"')
          .replace('<instanceID>both', '<deprecatedID>both</deprecatedID><instanceID>both2')),
        { filename: 'data.xml' })
        .expect(201);

      attachments = await oneFirst(sql`select count(*) from submission_attachments`);
      attachments.should.equal(4);

      blobs = await oneFirst(sql`select count(*) from blobs`);
      blobs.should.equal(2);

      // Clear out submission attachment for current version
      await asAlice.delete('/v1/projects/1/forms/binaryType/submissions/both/attachments/my_file1.mp4')
        .expect(200);

      // Cannot clear attachment of other version of submission (both2 is actually the current one)
      await asAlice.delete('/v1/projects/1/forms/binaryType/submissions/both2/attachments/my_file1.mp4')
        .expect(404);

      // Both blobs still exist after deleting, before purging
      blobs = await oneFirst(sql`select count(*) from blobs`);
      blobs.should.equal(2);

      // Purge unattached blobs
      await Blobs.purgeUnattached();

      // both blobs are still there
      blobs = await oneFirst(sql`select count(*) from blobs`);
      blobs.should.equal(2);
    }));