[chore/performance] Update media prune logic, add extra CLI command (#1474)v0.7.0-rc2

* start updating media prune stuff a wee bit

* continue prune / uncache work

* more tidying + consistency stuff

* add prune CLI command

* docs

* arg

2 files changed, 41 insertions, 7 deletions

diff --git a/internal/db/bundb/media.go b/internal/db/bundb/media.go
index 6e3a31b40..3350f9584 100644
--- a/internal/db/bundb/media.go
+++ b/internal/db/bundb/media.go
@@ -72,6 +72,23 @@ func (m *mediaDB) GetRemoteOlderThan(ctx context.Context, olderThan time.Time, l
 	return attachments, nil
 }
 
+func (m *mediaDB) CountRemoteOlderThan(ctx context.Context, olderThan time.Time) (int, db.Error) {
+	q := m.conn.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("media_attachments"), bun.Ident("media_attachment")).
+		Column("media_attachment.id").
+		Where("? = ?", bun.Ident("media_attachment.cached"), true).
+		Where("? < ?", bun.Ident("media_attachment.created_at"), olderThan).
+		WhereGroup(" AND ", whereNotEmptyAndNotNull("media_attachment.remote_url"))
+
+	count, err := q.Count(ctx)
+	if err != nil {
+		return 0, m.conn.ProcessError(err)
+	}
+
+	return count, nil
+}
+
 func (m *mediaDB) GetAvatarsAndHeaders(ctx context.Context, maxID string, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
 	attachments := []*gtsmodel.MediaAttachment{}
 
@@ -98,7 +115,7 @@ func (m *mediaDB) GetAvatarsAndHeaders(ctx context.Context, maxID string, limit
 	return attachments, nil
 }
 
-func (m *mediaDB) GetLocalUnattachedOlderThan(ctx context.Context, olderThan time.Time, maxID string, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
+func (m *mediaDB) GetLocalUnattachedOlderThan(ctx context.Context, olderThan time.Time, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
 	attachments := []*gtsmodel.MediaAttachment{}
 
 	q := m.newMediaQ(&attachments).
@@ -107,11 +124,8 @@ func (m *mediaDB) GetLocalUnattachedOlderThan(ctx context.Context, olderThan tim
 		Where("? = ?", bun.Ident("media_attachment.header"), false).
 		Where("? < ?", bun.Ident("media_attachment.created_at"), olderThan).
 		Where("? IS NULL", bun.Ident("media_attachment.remote_url")).
-		Where("? IS NULL", bun.Ident("media_attachment.status_id"))
-
-	if maxID != "" {
-		q = q.Where("? < ?", bun.Ident("media_attachment.id"), maxID)
-	}
+		Where("? IS NULL", bun.Ident("media_attachment.status_id")).
+		Order("media_attachment.created_at DESC")
 
 	if limit != 0 {
 		q = q.Limit(limit)
@@ -123,3 +137,23 @@ func (m *mediaDB) GetLocalUnattachedOlderThan(ctx context.Context, olderThan tim
 
 	return attachments, nil
 }
+
+func (m *mediaDB) CountLocalUnattachedOlderThan(ctx context.Context, olderThan time.Time) (int, db.Error) {
+	q := m.conn.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("media_attachments"), bun.Ident("media_attachment")).
+		Column("media_attachment.id").
+		Where("? = ?", bun.Ident("media_attachment.cached"), true).
+		Where("? = ?", bun.Ident("media_attachment.avatar"), false).
+		Where("? = ?", bun.Ident("media_attachment.header"), false).
+		Where("? < ?", bun.Ident("media_attachment.created_at"), olderThan).
+		Where("? IS NULL", bun.Ident("media_attachment.remote_url")).
+		Where("? IS NULL", bun.Ident("media_attachment.status_id"))
+
+	count, err := q.Count(ctx)
+	if err != nil {
+		return 0, m.conn.ProcessError(err)
+	}
+
+	return count, nil
+}
diff --git a/internal/db/bundb/media_test.go b/internal/db/bundb/media_test.go
index 139a4b780..02915a9e1 100644
--- a/internal/db/bundb/media_test.go
+++ b/internal/db/bundb/media_test.go
@@ -55,7 +55,7 @@ func (suite *MediaTestSuite) TestGetAvisAndHeaders() {
 func (suite *MediaTestSuite) TestGetLocalUnattachedOlderThan() {
 	ctx := context.Background()
 
-	attachments, err := suite.db.GetLocalUnattachedOlderThan(ctx, testrig.TimeMustParse("2090-06-04T13:12:00Z"), "", 10)
+	attachments, err := suite.db.GetLocalUnattachedOlderThan(ctx, testrig.TimeMustParse("2090-06-04T13:12:00Z"), 10)
 	suite.NoError(err)
 	suite.Len(attachments, 1)
 }