summaryrefslogtreecommitdiff
path: root/internal/db/bundb
diff options
context:
space:
mode:
Diffstat (limited to 'internal/db/bundb')
-rw-r--r--internal/db/bundb/account.go49
-rw-r--r--internal/db/bundb/emoji.go86
-rw-r--r--internal/db/bundb/media.go78
-rw-r--r--internal/db/bundb/media_test.go2
-rw-r--r--internal/db/bundb/migrations/20230724100000_emoji_cleanup.go55
-rw-r--r--internal/db/bundb/report.go2
-rw-r--r--internal/db/bundb/search.go39
-rw-r--r--internal/db/bundb/status.go34
-rw-r--r--internal/db/bundb/status_test.go4
-rw-r--r--internal/db/bundb/util.go36
10 files changed, 302 insertions, 83 deletions
diff --git a/internal/db/bundb/account.go b/internal/db/bundb/account.go
index 17339732e..179db6bb3 100644
--- a/internal/db/bundb/account.go
+++ b/internal/db/bundb/account.go
@@ -56,6 +56,27 @@ func (a *accountDB) GetAccountByID(ctx context.Context, id string) (*gtsmodel.Ac
)
}
+func (a *accountDB) GetAccountsByIDs(ctx context.Context, ids []string) ([]*gtsmodel.Account, error) {
+ accounts := make([]*gtsmodel.Account, 0, len(ids))
+
+ for _, id := range ids {
+ // Attempt to fetch account from DB.
+ account, err := a.GetAccountByID(
+ gtscontext.SetBarebones(ctx),
+ id,
+ )
+ if err != nil {
+ log.Errorf(ctx, "error getting account %q: %v", id, err)
+ continue
+ }
+
+ // Append account to return slice.
+ accounts = append(accounts, account)
+ }
+
+ return accounts, nil
+}
+
func (a *accountDB) GetAccountByURI(ctx context.Context, uri string) (*gtsmodel.Account, db.Error) {
return a.getAccount(
ctx,
@@ -444,6 +465,34 @@ func (a *accountDB) GetAccountCustomCSSByUsername(ctx context.Context, username
return account.CustomCSS, nil
}
+func (a *accountDB) GetAccountsUsingEmoji(ctx context.Context, emojiID string) ([]*gtsmodel.Account, error) {
+ var accountIDs []string
+
+ // Create SELECT account query.
+ q := a.conn.NewSelect().
+ Table("accounts").
+ Column("id")
+
+ // Append a WHERE LIKE clause to the query
+ // that checks the `emoji` column for any
+ // text containing this specific emoji ID.
+ //
+ // The reason we do this instead of doing a
+ // `WHERE ? IN (emojis)` is that the latter
+ // ends up being much MUCH slower, and the
+ // database stores this ID-array-column as
+ // text anyways, allowing a simple LIKE query.
+ q = whereLike(q, "emojis", emojiID)
+
+ // Execute the query, scanning destination into accountIDs.
+ if _, err := q.Exec(ctx, &accountIDs); err != nil {
+ return nil, a.conn.ProcessError(err)
+ }
+
+ // Convert account IDs into account objects.
+ return a.GetAccountsByIDs(ctx, accountIDs)
+}
+
func (a *accountDB) GetAccountFaves(ctx context.Context, accountID string) ([]*gtsmodel.StatusFave, db.Error) {
faves := new([]*gtsmodel.StatusFave)
diff --git a/internal/db/bundb/emoji.go b/internal/db/bundb/emoji.go
index 60c140264..321b5c0e7 100644
--- a/internal/db/bundb/emoji.go
+++ b/internal/db/bundb/emoji.go
@@ -126,12 +126,20 @@ func (e *emojiDB) DeleteEmojiByID(ctx context.Context, id string) db.Error {
return err
}
- // Select all accounts using this emoji.
- if _, err := tx.NewSelect().
+ // Prepare SELECT accounts query.
+ aq := tx.NewSelect().
Table("accounts").
- Column("id").
- Where("? IN (emojis)", id).
- Exec(ctx, &accountIDs); err != nil {
+ Column("id")
+
+ // Append a WHERE LIKE clause to the query
+ // that checks the `emoji` column for any
+ // text containing this specific emoji ID.
+ //
+ // (see GetStatusesUsingEmoji() for details.)
+ aq = whereLike(aq, "emojis", id)
+
+ // Select all accounts using this emoji into accountIDss.
+ if _, err := aq.Exec(ctx, &accountIDs); err != nil {
return err
}
@@ -162,12 +170,20 @@ func (e *emojiDB) DeleteEmojiByID(ctx context.Context, id string) db.Error {
}
}
- // Select all statuses using this emoji.
- if _, err := tx.NewSelect().
+ // Prepare SELECT statuses query.
+ sq := tx.NewSelect().
Table("statuses").
- Column("id").
- Where("? IN (emojis)", id).
- Exec(ctx, &statusIDs); err != nil {
+ Column("id")
+
+ // Append a WHERE LIKE clause to the query
+ // that checks the `emoji` column for any
+ // text containing this specific emoji ID.
+ //
+ // (see GetStatusesUsingEmoji() for details.)
+ sq = whereLike(sq, "emojis", id)
+
+ // Select all statuses using this emoji into statusIDs.
+ if _, err := sq.Exec(ctx, &statusIDs); err != nil {
return err
}
@@ -328,7 +344,7 @@ func (e *emojiDB) GetEmojisBy(ctx context.Context, domain string, includeDisable
}
func (e *emojiDB) GetEmojis(ctx context.Context, maxID string, limit int) ([]*gtsmodel.Emoji, error) {
- emojiIDs := []string{}
+ var emojiIDs []string
q := e.conn.NewSelect().
Table("emojis").
@@ -336,9 +352,55 @@ func (e *emojiDB) GetEmojis(ctx context.Context, maxID string, limit int) ([]*gt
Order("id DESC")
if maxID != "" {
- q = q.Where("? < ?", bun.Ident("id"), maxID)
+ q = q.Where("id < ?", maxID)
+ }
+
+ if limit != 0 {
+ q = q.Limit(limit)
}
+ if err := q.Scan(ctx, &emojiIDs); err != nil {
+ return nil, e.conn.ProcessError(err)
+ }
+
+ return e.GetEmojisByIDs(ctx, emojiIDs)
+}
+
+func (e *emojiDB) GetRemoteEmojis(ctx context.Context, maxID string, limit int) ([]*gtsmodel.Emoji, error) {
+ var emojiIDs []string
+
+ q := e.conn.NewSelect().
+ Table("emojis").
+ Column("id").
+ Where("domain IS NOT NULL").
+ Order("id DESC")
+
+ if maxID != "" {
+ q = q.Where("id < ?", maxID)
+ }
+
+ if limit != 0 {
+ q = q.Limit(limit)
+ }
+
+ if err := q.Scan(ctx, &emojiIDs); err != nil {
+ return nil, e.conn.ProcessError(err)
+ }
+
+ return e.GetEmojisByIDs(ctx, emojiIDs)
+}
+
+func (e *emojiDB) GetCachedEmojisOlderThan(ctx context.Context, olderThan time.Time, limit int) ([]*gtsmodel.Emoji, error) {
+ var emojiIDs []string
+
+ q := e.conn.NewSelect().
+ Table("emojis").
+ Column("id").
+ Where("cached = true").
+ Where("domain IS NOT NULL").
+ Where("created_at < ?", olderThan).
+ Order("created_at DESC")
+
if limit != 0 {
q = q.Limit(limit)
}
diff --git a/internal/db/bundb/media.go b/internal/db/bundb/media.go
index 80a4f8bbe..c190df44a 100644
--- a/internal/db/bundb/media.go
+++ b/internal/db/bundb/media.go
@@ -232,29 +232,6 @@ func (m *mediaDB) DeleteAttachment(ctx context.Context, id string) error {
return m.conn.ProcessError(err)
}
-func (m *mediaDB) GetRemoteOlderThan(ctx context.Context, olderThan time.Time, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
- attachmentIDs := []string{}
-
- 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).
- Where("? IS NOT NULL", bun.Ident("media_attachment.remote_url")).
- Order("media_attachment.created_at DESC")
-
- if limit != 0 {
- q = q.Limit(limit)
- }
-
- if err := q.Scan(ctx, &attachmentIDs); err != nil {
- return nil, m.conn.ProcessError(err)
- }
-
- return m.GetAttachmentsByIDs(ctx, attachmentIDs)
-}
-
func (m *mediaDB) CountRemoteOlderThan(ctx context.Context, olderThan time.Time) (int, db.Error) {
q := m.conn.
NewSelect().
@@ -273,15 +250,39 @@ func (m *mediaDB) CountRemoteOlderThan(ctx context.Context, olderThan time.Time)
}
func (m *mediaDB) GetAttachments(ctx context.Context, maxID string, limit int) ([]*gtsmodel.MediaAttachment, error) {
- attachmentIDs := []string{}
+ attachmentIDs := make([]string, 0, limit)
+
+ q := m.conn.NewSelect().
+ Table("media_attachments").
+ Column("id").
+ Order("id DESC")
+
+ if maxID != "" {
+ q = q.Where("id < ?", maxID)
+ }
+
+ if limit != 0 {
+ q = q.Limit(limit)
+ }
+
+ if err := q.Scan(ctx, &attachmentIDs); err != nil {
+ return nil, m.conn.ProcessError(err)
+ }
+
+ return m.GetAttachmentsByIDs(ctx, attachmentIDs)
+}
+
+func (m *mediaDB) GetRemoteAttachments(ctx context.Context, maxID string, limit int) ([]*gtsmodel.MediaAttachment, error) {
+ attachmentIDs := make([]string, 0, limit)
q := m.conn.NewSelect().
Table("media_attachments").
Column("id").
+ Where("remote_url IS NOT NULL").
Order("id DESC")
if maxID != "" {
- q = q.Where("? < ?", bun.Ident("id"), maxID)
+ q = q.Where("id < ?", maxID)
}
if limit != 0 {
@@ -295,8 +296,31 @@ func (m *mediaDB) GetAttachments(ctx context.Context, maxID string, limit int) (
return m.GetAttachmentsByIDs(ctx, attachmentIDs)
}
+func (m *mediaDB) GetCachedAttachmentsOlderThan(ctx context.Context, olderThan time.Time, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
+ attachmentIDs := make([]string, 0, limit)
+
+ q := m.conn.
+ NewSelect().
+ Table("media_attachments").
+ Column("id").
+ Where("cached = true").
+ Where("remote_url IS NOT NULL").
+ Where("created_at < ?", olderThan).
+ Order("created_at DESC")
+
+ if limit != 0 {
+ q = q.Limit(limit)
+ }
+
+ if err := q.Scan(ctx, &attachmentIDs); err != nil {
+ return nil, m.conn.ProcessError(err)
+ }
+
+ return m.GetAttachmentsByIDs(ctx, attachmentIDs)
+}
+
func (m *mediaDB) GetAvatarsAndHeaders(ctx context.Context, maxID string, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
- attachmentIDs := []string{}
+ attachmentIDs := make([]string, 0, limit)
q := m.conn.NewSelect().
TableExpr("? AS ?", bun.Ident("media_attachments"), bun.Ident("media_attachment")).
@@ -324,7 +348,7 @@ func (m *mediaDB) GetAvatarsAndHeaders(ctx context.Context, maxID string, limit
}
func (m *mediaDB) GetLocalUnattachedOlderThan(ctx context.Context, olderThan time.Time, limit int) ([]*gtsmodel.MediaAttachment, db.Error) {
- attachmentIDs := []string{}
+ attachmentIDs := make([]string, 0, limit)
q := m.conn.
NewSelect().
diff --git a/internal/db/bundb/media_test.go b/internal/db/bundb/media_test.go
index 6b419022a..59b927119 100644
--- a/internal/db/bundb/media_test.go
+++ b/internal/db/bundb/media_test.go
@@ -38,7 +38,7 @@ func (suite *MediaTestSuite) TestGetAttachmentByID() {
}
func (suite *MediaTestSuite) TestGetOlder() {
- attachments, err := suite.db.GetRemoteOlderThan(context.Background(), time.Now(), 20)
+ attachments, err := suite.db.GetCachedAttachmentsOlderThan(context.Background(), time.Now(), 20)
suite.NoError(err)
suite.Len(attachments, 2)
}
diff --git a/internal/db/bundb/migrations/20230724100000_emoji_cleanup.go b/internal/db/bundb/migrations/20230724100000_emoji_cleanup.go
new file mode 100644
index 000000000..0ee501395
--- /dev/null
+++ b/internal/db/bundb/migrations/20230724100000_emoji_cleanup.go
@@ -0,0 +1,55 @@
+// GoToSocial
+// Copyright (C) GoToSocial Authors admin@gotosocial.org
+// SPDX-License-Identifier: AGPL-3.0-or-later
+//
+// This program is free software: you can redistribute it and/or modify
+// it under the terms of the GNU Affero General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+// GNU Affero General Public License for more details.
+//
+// You should have received a copy of the GNU Affero General Public License
+// along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+package migrations
+
+import (
+ "context"
+ "strings"
+
+ "github.com/uptrace/bun"
+)
+
+func init() {
+ up := func(ctx context.Context, db *bun.DB) error {
+ _, err := db.ExecContext(ctx, "ALTER TABLE emojis ADD COLUMN cached BOOLEAN DEFAULT false")
+
+ if err != nil && !(strings.Contains(err.Error(), "already exists") || strings.Contains(err.Error(), "duplicate column name") || strings.Contains(err.Error(), "SQLSTATE 42701")) {
+ return err
+ }
+
+ if _, err := db.NewUpdate().
+ Table("emojis").
+ Where("disabled = false").
+ Set("cached = true").
+ Exec(ctx); err != nil {
+ return err
+ }
+
+ return nil
+ }
+
+ down := func(ctx context.Context, db *bun.DB) error {
+ return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+ return nil
+ })
+ }
+
+ if err := Migrations.Register(up, down); err != nil {
+ panic(err)
+ }
+}
diff --git a/internal/db/bundb/report.go b/internal/db/bundb/report.go
index e017a8906..ee8aa1cb3 100644
--- a/internal/db/bundb/report.go
+++ b/internal/db/bundb/report.go
@@ -149,7 +149,7 @@ func (r *reportDB) getReport(ctx context.Context, lookup string, dbQuery func(*g
if len(report.StatusIDs) > 0 {
// Fetch reported statuses
- report.Statuses, err = r.state.DB.GetStatuses(ctx, report.StatusIDs)
+ report.Statuses, err = r.state.DB.GetStatusesByIDs(ctx, report.StatusIDs)
if err != nil {
return nil, fmt.Errorf("error getting status mentions: %w", err)
}
diff --git a/internal/db/bundb/search.go b/internal/db/bundb/search.go
index c05ebb8b1..1d7eefd48 100644
--- a/internal/db/bundb/search.go
+++ b/internal/db/bundb/search.go
@@ -19,7 +19,6 @@ package bundb
import (
"context"
- "strings"
"github.com/superseriousbusiness/gotosocial/internal/gtsmodel"
"github.com/superseriousbusiness/gotosocial/internal/id"
@@ -61,40 +60,6 @@ type searchDB struct {
state *state.State
}
-// replacer is a thread-safe string replacer which escapes
-// common SQLite + Postgres `LIKE` wildcard chars using the
-// escape character `\`. Initialized as a var in this package
-// so it can be reused.
-var replacer = strings.NewReplacer(
- `\`, `\\`, // Escape char.
- `%`, `\%`, // Zero or more char.
- `_`, `\_`, // Exactly one char.
-)
-
-// whereSubqueryLike appends a WHERE clause to the
-// given SelectQuery q, which searches for matches
-// of searchQuery in the given subQuery using LIKE.
-func whereSubqueryLike(
- q *bun.SelectQuery,
- subQuery *bun.SelectQuery,
- searchQuery string,
-) *bun.SelectQuery {
- // Escape existing wildcard + escape
- // chars in the search query string.
- searchQuery = replacer.Replace(searchQuery)
-
- // Add our own wildcards back in; search
- // zero or more chars around the query.
- searchQuery = `%` + searchQuery + `%`
-
- // Append resulting WHERE
- // clause to the main query.
- return q.Where(
- "(?) LIKE ? ESCAPE ?",
- subQuery, searchQuery, `\`,
- )
-}
-
// Query example (SQLite):
//
// SELECT "account"."id" FROM "accounts" AS "account"
@@ -167,7 +132,7 @@ func (s *searchDB) SearchForAccounts(
// Search using LIKE for matches of query
// string within accountText subquery.
- q = whereSubqueryLike(q, accountTextSubq, query)
+ q = whereLike(q, accountTextSubq, query)
if limit > 0 {
// Limit amount of accounts returned.
@@ -345,7 +310,7 @@ func (s *searchDB) SearchForStatuses(
// Search using LIKE for matches of query
// string within statusText subquery.
- q = whereSubqueryLike(q, statusTextSubq, query)
+ q = whereLike(q, statusTextSubq, query)
if limit > 0 {
// Limit amount of statuses returned.
diff --git a/internal/db/bundb/status.go b/internal/db/bundb/status.go
index 0dffbabcc..ccfc9fd4b 100644
--- a/internal/db/bundb/status.go
+++ b/internal/db/bundb/status.go
@@ -58,18 +58,18 @@ func (s *statusDB) GetStatusByID(ctx context.Context, id string) (*gtsmodel.Stat
)
}
-func (s *statusDB) GetStatuses(ctx context.Context, ids []string) ([]*gtsmodel.Status, db.Error) {
+func (s *statusDB) GetStatusesByIDs(ctx context.Context, ids []string) ([]*gtsmodel.Status, error) {
statuses := make([]*gtsmodel.Status, 0, len(ids))
for _, id := range ids {
- // Attempt fetch from DB
+ // Attempt to fetch status from DB.
status, err := s.GetStatusByID(ctx, id)
if err != nil {
log.Errorf(ctx, "error getting status %q: %v", id, err)
continue
}
- // Append status
+ // Append status to return slice.
statuses = append(statuses, status)
}
@@ -429,6 +429,34 @@ func (s *statusDB) DeleteStatusByID(ctx context.Context, id string) db.Error {
})
}
+func (s *statusDB) GetStatusesUsingEmoji(ctx context.Context, emojiID string) ([]*gtsmodel.Status, error) {
+ var statusIDs []string
+
+ // Create SELECT status query.
+ q := s.conn.NewSelect().
+ Table("statuses").
+ Column("id")
+
+ // Append a WHERE LIKE clause to the query
+ // that checks the `emoji` column for any
+ // text containing this specific emoji ID.
+ //
+ // The reason we do this instead of doing a
+ // `WHERE ? IN (emojis)` is that the latter
+ // ends up being much MUCH slower, and the
+ // database stores this ID-array-column as
+ // text anyways, allowing a simple LIKE query.
+ q = whereLike(q, "emojis", emojiID)
+
+ // Execute the query, scanning destination into statusIDs.
+ if _, err := q.Exec(ctx, &statusIDs); err != nil {
+ return nil, s.conn.ProcessError(err)
+ }
+
+ // Convert status IDs into status objects.
+ return s.GetStatusesByIDs(ctx, statusIDs)
+}
+
func (s *statusDB) GetStatusParents(ctx context.Context, status *gtsmodel.Status, onlyDirect bool) ([]*gtsmodel.Status, db.Error) {
if onlyDirect {
// Only want the direct parent, no further than first level
diff --git a/internal/db/bundb/status_test.go b/internal/db/bundb/status_test.go
index cab7501f9..a69608796 100644
--- a/internal/db/bundb/status_test.go
+++ b/internal/db/bundb/status_test.go
@@ -50,13 +50,13 @@ func (suite *StatusTestSuite) TestGetStatusByID() {
suite.True(*status.Likeable)
}
-func (suite *StatusTestSuite) TestGetStatusesByID() {
+func (suite *StatusTestSuite) TestGetStatusesByIDs() {
ids := []string{
suite.testStatuses["local_account_1_status_1"].ID,
suite.testStatuses["local_account_2_status_3"].ID,
}
- statuses, err := suite.db.GetStatuses(context.Background(), ids)
+ statuses, err := suite.db.GetStatusesByIDs(context.Background(), ids)
if err != nil {
suite.FailNow(err.Error())
}
diff --git a/internal/db/bundb/util.go b/internal/db/bundb/util.go
index 06bb289d3..bdd45d1e7 100644
--- a/internal/db/bundb/util.go
+++ b/internal/db/bundb/util.go
@@ -18,10 +18,46 @@
package bundb
import (
+ "strings"
+
"github.com/superseriousbusiness/gotosocial/internal/db"
"github.com/uptrace/bun"
)
+// likeEscaper is a thread-safe string replacer which escapes
+// common SQLite + Postgres `LIKE` wildcard chars using the
+// escape character `\`. Initialized as a var in this package
+// so it can be reused.
+var likeEscaper = strings.NewReplacer(
+ `\`, `\\`, // Escape char.
+ `%`, `\%`, // Zero or more char.
+ `_`, `\_`, // Exactly one char.
+)
+
+// whereSubqueryLike appends a WHERE clause to the
+// given SelectQuery, which searches for matches
+// of `search` in the given subQuery using LIKE.
+func whereLike(
+ query *bun.SelectQuery,
+ subject interface{},
+ search string,
+) *bun.SelectQuery {
+ // Escape existing wildcard + escape
+ // chars in the search query string.
+ search = likeEscaper.Replace(search)
+
+ // Add our own wildcards back in; search
+ // zero or more chars around the query.
+ search = `%` + search + `%`
+
+ // Append resulting WHERE
+ // clause to the main query.
+ return query.Where(
+ "(?) LIKE ? ESCAPE ?",
+ subject, search, `\`,
+ )
+}
+
// updateWhere parses []db.Where and adds it to the given update query.
func updateWhere(q *bun.UpdateQuery, where []db.Where) {
for _, w := range where {
generated by cgit v1.2.3 (git 2.46.0) at 2025-10-31 09:13:51 +0000