[feature] Custom emoji updates (serve emoji via s2s api, tune db models) (#805)

* migrate emojis

* add get emoji to s2s (federation) API

* add new emoji db + cache functions

* add shortcodeDomain lookup for emojis

* check existing emojis w/cache, not w/constraints

* go fmt

* add putEmoji func

* use new db emoji funcs instead of where

* remove emojistringstotags func

* add unique constraint back in

* fix up broken migration

* update index

6 files changed, 279 insertions, 35 deletions

diff --git a/internal/db/bundb/bundb.go b/internal/db/bundb/bundb.go
index 6bf3571b4..b944ae3ea 100644
--- a/internal/db/bundb/bundb.go
+++ b/internal/db/bundb/bundb.go
@@ -154,6 +154,7 @@ func NewBunDBService(ctx context.Context) (db.DB, error) {
 	// Create DB structs that require ptrs to each other
 	accounts := &accountDB{conn: conn, cache: cache.NewAccountCache()}
 	status := &statusDB{conn: conn, cache: cache.NewStatusCache()}
+	emoji := &emojiDB{conn: conn, cache: cache.NewEmojiCache()}
 	timeline := &timelineDB{conn: conn}
 
 	// Setup DB cross-referencing
@@ -188,9 +189,7 @@ func NewBunDBService(ctx context.Context) (db.DB, error) {
 			conn:  conn,
 			cache: blockCache,
 		},
-		Emoji: &emojiDB{
-			conn: conn,
-		},
+		Emoji: emoji,
 		Instance: &instanceDB{
 			conn: conn,
 		},
@@ -440,22 +439,3 @@ func (ps *bunDBService) TagStringsToTags(ctx context.Context, tags []string, ori
 	}
 	return newTags, nil
 }
-
-func (ps *bunDBService) EmojiStringsToEmojis(ctx context.Context, emojis []string) ([]*gtsmodel.Emoji, error) {
-	newEmojis := []*gtsmodel.Emoji{}
-	for _, e := range emojis {
-		emoji := &gtsmodel.Emoji{}
-		err := ps.conn.NewSelect().Model(emoji).Where("shortcode = ?", e).Where("visible_in_picker = true").Where("disabled = false").Scan(ctx)
-		if err != nil {
-			if err == sql.ErrNoRows {
-				// no result found for this username/domain so just don't include it as an emoji and carry on about our business
-				log.Debugf("no emoji found with shortcode %s, skipping it", e)
-				continue
-			}
-			// a serious error has happened so bail
-			return nil, fmt.Errorf("error getting emoji with shortcode %s: %s", e, err)
-		}
-		newEmojis = append(newEmojis, emoji)
-	}
-	return newEmojis, nil
-}
diff --git a/internal/db/bundb/emoji.go b/internal/db/bundb/emoji.go
index 55bc71e1e..758da0feb 100644
--- a/internal/db/bundb/emoji.go
+++ b/internal/db/bundb/emoji.go
@@ -20,27 +20,136 @@ package bundb
 
 import (
 	"context"
+	"strings"
 
+	"github.com/superseriousbusiness/gotosocial/internal/cache"
 	"github.com/superseriousbusiness/gotosocial/internal/db"
 	"github.com/superseriousbusiness/gotosocial/internal/gtsmodel"
+	"github.com/superseriousbusiness/gotosocial/internal/log"
+	"github.com/uptrace/bun"
 )
 
 type emojiDB struct {
-	conn *DBConn
+	conn  *DBConn
+	cache *cache.EmojiCache
 }
 
-func (e emojiDB) GetCustomEmojis(ctx context.Context) ([]*gtsmodel.Emoji, db.Error) {
-	emojis := []*gtsmodel.Emoji{}
+func (e *emojiDB) newEmojiQ(emoji *gtsmodel.Emoji) *bun.SelectQuery {
+	return e.conn.
+		NewSelect().
+		Model(emoji)
+}
+
+func (e *emojiDB) PutEmoji(ctx context.Context, emoji *gtsmodel.Emoji) db.Error {
+	if _, err := e.conn.NewInsert().Model(emoji).Exec(ctx); err != nil {
+		return e.conn.ProcessError(err)
+	}
+
+	e.cache.Put(emoji)
+	return nil
+}
+
+func (e *emojiDB) GetCustomEmojis(ctx context.Context) ([]*gtsmodel.Emoji, db.Error) {
+	emojiIDs := []string{}
 
 	q := e.conn.
 		NewSelect().
-		Model(&emojis).
+		Table("emojis").
+		Column("id").
 		Where("visible_in_picker = true").
 		Where("disabled = false").
+		Where("domain IS NULL").
 		Order("shortcode ASC")
 
-	if err := q.Scan(ctx); err != nil {
+	if err := q.Scan(ctx, &emojiIDs); err != nil {
 		return nil, e.conn.ProcessError(err)
 	}
+
+	return e.emojisFromIDs(ctx, emojiIDs)
+}
+
+func (e *emojiDB) GetEmojiByID(ctx context.Context, id string) (*gtsmodel.Emoji, db.Error) {
+	return e.getEmoji(
+		ctx,
+		func() (*gtsmodel.Emoji, bool) {
+			return e.cache.GetByID(id)
+		},
+		func(emoji *gtsmodel.Emoji) error {
+			return e.newEmojiQ(emoji).Where("emoji.id = ?", id).Scan(ctx)
+		},
+	)
+}
+
+func (e *emojiDB) GetEmojiByURI(ctx context.Context, uri string) (*gtsmodel.Emoji, db.Error) {
+	return e.getEmoji(
+		ctx,
+		func() (*gtsmodel.Emoji, bool) {
+			return e.cache.GetByURI(uri)
+		},
+		func(emoji *gtsmodel.Emoji) error {
+			return e.newEmojiQ(emoji).Where("emoji.uri = ?", uri).Scan(ctx)
+		},
+	)
+}
+
+func (e *emojiDB) GetEmojiByShortcodeDomain(ctx context.Context, shortcode string, domain string) (*gtsmodel.Emoji, db.Error) {
+	return e.getEmoji(
+		ctx,
+		func() (*gtsmodel.Emoji, bool) {
+			return e.cache.GetByShortcodeDomain(shortcode, domain)
+		},
+		func(emoji *gtsmodel.Emoji) error {
+			q := e.newEmojiQ(emoji)
+
+			if domain != "" {
+				q = q.Where("emoji.shortcode = ?", shortcode)
+				q = q.Where("emoji.domain = ?", domain)
+			} else {
+				q = q.Where("emoji.shortcode = ?", strings.ToLower(shortcode))
+				q = q.Where("emoji.domain IS NULL")
+			}
+
+			return q.Scan(ctx)
+		},
+	)
+}
+
+func (e *emojiDB) getEmoji(ctx context.Context, cacheGet func() (*gtsmodel.Emoji, bool), dbQuery func(*gtsmodel.Emoji) error) (*gtsmodel.Emoji, db.Error) {
+	// Attempt to fetch cached emoji
+	emoji, cached := cacheGet()
+
+	if !cached {
+		emoji = &gtsmodel.Emoji{}
+
+		// Not cached! Perform database query
+		err := dbQuery(emoji)
+		if err != nil {
+			return nil, e.conn.ProcessError(err)
+		}
+
+		// Place in the cache
+		e.cache.Put(emoji)
+	}
+
+	return emoji, nil
+}
+
+func (e *emojiDB) emojisFromIDs(ctx context.Context, emojiIDs []string) ([]*gtsmodel.Emoji, db.Error) {
+	// Catch case of no emojis early
+	if len(emojiIDs) == 0 {
+		return nil, db.ErrNoEntries
+	}
+
+	emojis := make([]*gtsmodel.Emoji, 0, len(emojiIDs))
+
+	for _, id := range emojiIDs {
+		emoji, err := e.GetEmojiByID(ctx, id)
+		if err != nil {
+			log.Errorf("emojisFromIDs: error getting emoji %q: %v", id, err)
+		}
+
+		emojis = append(emojis, emoji)
+	}
+
 	return emojis, nil
 }
diff --git a/internal/db/bundb/migrations/20220905150505_custom_emoji_updates.go b/internal/db/bundb/migrations/20220905150505_custom_emoji_updates.go
new file mode 100644
index 000000000..71df49692
--- /dev/null
+++ b/internal/db/bundb/migrations/20220905150505_custom_emoji_updates.go
@@ -0,0 +1,111 @@
+/*
+   GoToSocial
+   Copyright (C) 2021-2022 GoToSocial Authors admin@gotosocial.org
+
+   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"
+	"database/sql"
+
+	"github.com/superseriousbusiness/gotosocial/internal/db/bundb/migrations/20220905150505_custom_emoji_updates"
+	"github.com/uptrace/bun"
+)
+
+func init() {
+	up := func(ctx context.Context, db *bun.DB) error {
+		return db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			// create the new emojis table
+			if _, err := tx.
+				NewCreateTable().
+				Model(&gtsmodel.Emoji{}).
+				ModelTableExpr("new_emojis").
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			// move all old emojis to the new table
+			currentEmojis := []*gtsmodel.Emoji{}
+			if err := tx.
+				NewSelect().
+				Model(&currentEmojis).
+				Scan(ctx); err != nil && err != sql.ErrNoRows {
+				return err
+			}
+
+			for _, currentEmoji := range currentEmojis {
+				if _, err := tx.
+					NewInsert().
+					Model(currentEmoji).
+					ModelTableExpr("new_emojis").
+					Exec(ctx); err != nil {
+					return err
+				}
+			}
+
+			// we have all the data we need from the old table, so we can safely drop it now
+			if _, err := tx.NewDropTable().Model(&gtsmodel.Emoji{}).Exec(ctx); err != nil {
+				return err
+			}
+
+			// rename the new table to the same name as the old table was
+			if _, err := tx.ExecContext(ctx, "ALTER TABLE new_emojis RENAME TO emojis;"); err != nil {
+				return err
+			}
+
+			// add indexes to the new table
+			if _, err := tx.
+				NewCreateIndex().
+				Model(&gtsmodel.Emoji{}).
+				Index("emojis_id_idx").
+				Column("id").
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			if _, err := tx.
+				NewCreateIndex().
+				Model(&gtsmodel.Emoji{}).
+				Index("emojis_uri_idx").
+				Column("uri").
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			if _, err := tx.
+				NewCreateIndex().
+				Model(&gtsmodel.Emoji{}).
+				Index("emojis_available_custom_idx").
+				Column("visible_in_picker", "disabled", "shortcode").
+				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/migrations/20220905150505_custom_emoji_updates/emoji.go b/internal/db/bundb/migrations/20220905150505_custom_emoji_updates/emoji.go
new file mode 100644
index 000000000..106301041
--- /dev/null
+++ b/internal/db/bundb/migrations/20220905150505_custom_emoji_updates/emoji.go
@@ -0,0 +1,45 @@
+/*
+   GoToSocial
+   Copyright (C) 2021-2022 GoToSocial Authors admin@gotosocial.org
+
+   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 gtsmodel
+
+import "time"
+
+// Emoji represents a custom emoji that's been uploaded through the admin UI, and is useable by instance denizens.
+type Emoji struct {
+	ID                     string    `validate:"required,ulid" bun:"type:CHAR(26),pk,nullzero,notnull,unique"`                                // id of this item in the database
+	CreatedAt              time.Time `validate:"-" bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`                         // when was item created
+	UpdatedAt              time.Time `validate:"-" bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`                         // when was item last updated
+	Shortcode              string    `validate:"required" bun:",nullzero,notnull,unique:shortcodedomain"`                                     // String shortcode for this emoji -- the part that's between colons. This should be lowercase a-z_  eg., 'blob_hug' 'purple_heart' Must be unique with domain.
+	Domain                 string    `validate:"omitempty,fqdn" bun:",nullzero,unique:shortcodedomain"`                                       // Origin domain of this emoji, eg 'example.org', 'queer.party'. empty string for local emojis.
+	ImageRemoteURL         string    `validate:"required_without=ImageURL,omitempty,url" bun:",nullzero"`                                     // Where can this emoji be retrieved remotely? Null for local emojis.
+	ImageStaticRemoteURL   string    `validate:"required_without=ImageStaticURL,omitempty,url" bun:",nullzero"`                               // Where can a static / non-animated version of this emoji be retrieved remotely? Null for local emojis.
+	ImageURL               string    `validate:"required_without=ImageRemoteURL,required_without=Domain,omitempty,url" bun:",nullzero"`       // Where can this emoji be retrieved from the local server? Null for remote emojis.
+	ImageStaticURL         string    `validate:"required_without=ImageStaticRemoteURL,required_without=Domain,omitempty,url" bun:",nullzero"` // Where can a static version of this emoji be retrieved from the local server? Null for remote emojis.
+	ImagePath              string    `validate:"required,file" bun:",nullzero,notnull"`                                                       // Path of the emoji image in the server storage system.
+	ImageStaticPath        string    `validate:"required,file" bun:",nullzero,notnull"`                                                       // Path of a static version of the emoji image in the server storage system
+	ImageContentType       string    `validate:"required" bun:",nullzero,notnull"`                                                            // MIME content type of the emoji image
+	ImageStaticContentType string    `validate:"required" bun:",nullzero,notnull"`                                                            // MIME content type of the static version of the emoji image.
+	ImageFileSize          int       `validate:"required,min=1" bun:",nullzero,notnull"`                                                      // Size of the emoji image file in bytes, for serving purposes.
+	ImageStaticFileSize    int       `validate:"required,min=1" bun:",nullzero,notnull"`                                                      // Size of the static version of the emoji image file in bytes, for serving purposes.
+	ImageUpdatedAt         time.Time `validate:"-" bun:"type:timestamptz,nullzero,notnull,default:current_timestamp"`                         // When was the emoji image last updated?
+	Disabled               *bool     `validate:"-" bun:",nullzero,notnull,default:false"`                                                     // Has a moderation action disabled this emoji from being shown?
+	URI                    string    `validate:"url" bun:",nullzero,notnull,unique"`                                                          // ActivityPub uri of this emoji. Something like 'https://example.org/emojis/1234'
+	VisibleInPicker        *bool     `validate:"-" bun:",nullzero,notnull,default:true"`                                                      // Is this emoji visible in the admin emoji picker?
+	CategoryID             string    `validate:"omitempty,ulid" bun:"type:CHAR(26),nullzero"`                                                 // In which emoji category is this emoji visible?
+}
diff --git a/internal/db/db.go b/internal/db/db.go
index 57549f588..0c1f2602a 100644
--- a/internal/db/db.go
+++ b/internal/db/db.go
@@ -57,12 +57,4 @@ type DB interface {
 	// Note: this func doesn't/shouldn't do any manipulation of the tags in the DB, it's just for checking
 	// if they exist in the db already, and conveniently returning them, or creating new tag structs.
 	TagStringsToTags(ctx context.Context, tags []string, originAccountID string) ([]*gtsmodel.Tag, error)
-
-	// EmojiStringsToEmojis takes a slice of deduplicated, lowercase emojis in the form ":emojiname:", which have been
-	// used in a status. It takes the id of the account that wrote the status, and the id of the status itself, and then
-	// returns a slice of *model.Emoji corresponding to the given emojis.
-	//
-	// Note: this func doesn't/shouldn't do any manipulation of the emoji in the DB, it's just for checking
-	// if they exist in the db and conveniently returning them if they do.
-	EmojiStringsToEmojis(ctx context.Context, emojis []string) ([]*gtsmodel.Emoji, error)
 }
diff --git a/internal/db/emoji.go b/internal/db/emoji.go
index f2694b460..0038e10e4 100644
--- a/internal/db/emoji.go
+++ b/internal/db/emoji.go
@@ -26,6 +26,13 @@ import (
 
 // Emoji contains functions for getting emoji in the database.
 type Emoji interface {
+	// PutEmoji puts one emoji in the database.
+	PutEmoji(ctx context.Context, emoji *gtsmodel.Emoji) Error
 	// GetCustomEmojis gets all custom emoji for the instance
 	GetCustomEmojis(ctx context.Context) ([]*gtsmodel.Emoji, Error)
+	// GetEmojiByID gets a specific emoji by its database ID.
+	GetEmojiByID(ctx context.Context, id string) (*gtsmodel.Emoji, Error)
+	// GetEmojiByShortcodeDomain gets an emoji based on its shortcode and domain.
+	// For local emoji, domain should be an empty string.
+	GetEmojiByShortcodeDomain(ctx context.Context, shortcode string, domain string) (*gtsmodel.Emoji, Error)
 }