28878367588

Committed 07 Jul 2026 03:30PM UTC coverage: 82.185% (+5.7%) from 76.45%

Build # 28878367588

Build Type

Pull #22

github

Committed by

et-nik

Commit Message

e2e fixes

Pull Request Pull Request #22: Develop into master

Coverage Stats

47018 of 57210 relevant lines covered (82.18%)

34985.35 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

94.7

/internal/cache/postgres.go

package cache

import (
        "context"
        "database/sql"
        "encoding/json"
        "errors"
        "fmt"
        "strings"
        "time"

        sq "github.com/Masterminds/squirrel"
        "github.com/gameap/gameap/internal/repositories/base"
)

const postgresKVStoreTable = "kv_store"

const postgresDatabaseKeyPrefix = "cache:"

const postgresCreateTableQuery = `
CREATE UNLOGGED TABLE IF NOT EXISTS kv_store (
  key VARCHAR(128) PRIMARY KEY,
  value BYTEA NOT NULL,
  expires_at TIMESTAMPTZ NULL DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS idx_kv_store_key_hash ON kv_store USING HASH (key);
CREATE INDEX IF NOT EXISTS idx_kv_store_expires ON kv_store (expires_at) WHERE expires_at IS NOT NULL;
`

type PostgreSQL struct {
        db     base.DB
        prefix string
}

func NewPostgreSQL(db base.DB) *PostgreSQL {
        cache := &PostgreSQL{
                db:     db,
                prefix: postgresDatabaseKeyPrefix,
        }

        if err := cache.ensureTable(context.TODO()); err != nil {
                panic(
                        fmt.Errorf(
                                "failed to ensure kv_store table: %w \n"+
                                        "You can manually create the table by executing the following SQL statement:\n %s",
                                err,
                                postgresCreateTableQuery,
                        ),
                )
        }

        return cache
}

func (c *PostgreSQL) ensureTable(ctx context.Context) error {
        // Check if table exists
        var exists bool
        query := `
                SELECT EXISTS (
                        SELECT FROM information_schema.tables
                        WHERE table_schema = 'public'
                        AND table_name = $1
                )
        `
        err := c.db.QueryRowContext(ctx, query, postgresKVStoreTable).Scan(&exists)
        if err != nil {
                return fmt.Errorf("failed to check if table exists: %w", err)
        }

        if exists {
                return nil
        }

        // Table doesn't exist, create it
        _, err = c.db.ExecContext(ctx, postgresCreateTableQuery)
        if err != nil {
                return fmt.Errorf("failed to create kv_store table: %w", err)
        }

        return nil
}

func (c *PostgreSQL) buildKey(key string) string {
        var sb strings.Builder
        sb.Grow(len(c.prefix) + len(key))
        sb.WriteString(c.prefix)
        sb.WriteString(key)

        return sb.String()
}

func (c *PostgreSQL) Get(ctx context.Context, key string) (any, error) {
        fullKey := c.buildKey(key)

        query, args, err := sq.Select("value", "expires_at").
                From(postgresKVStoreTable).
                Where(sq.Eq{"key": fullKey}).
                PlaceholderFormat(sq.Dollar).
                ToSql()
        if err != nil {
                return nil, fmt.Errorf("failed to build query: %w", err)
        }

        var valueJSON string
        var expiresAt sql.NullTime

        err = c.db.QueryRowContext(ctx, query, args...).Scan(&valueJSON, &expiresAt)
        if err != nil {
                if errors.Is(err, sql.ErrNoRows) {
                        return nil, ErrNotFound
                }

                return nil, fmt.Errorf("failed to query row: %w", err)
        }

        // Treat an expired row as absent. We deliberately do NOT delete it inline:
        // that turns every read of an expired key into a write (amplified under a
        // stampede of concurrent readers). The background CleanupExpired sweep
        // reclaims the row.
        if expiresAt.Valid && expiresAt.Time.Before(time.Now()) {
                return nil, ErrNotFound
        }

        var value any
        if err := json.Unmarshal([]byte(valueJSON), &value); err != nil {
                return nil, fmt.Errorf("failed to unmarshal value: %w", err)
        }

        return value, nil
}

func (c *PostgreSQL) Set(ctx context.Context, key string, value any, options ...Option) error {
        opts := ApplyOptions(options...)
        fullKey := c.buildKey(key)

        valueJSON, err := json.Marshal(value)
        if err != nil {
                return fmt.Errorf("failed to marshal value: %w", err)
        }

        var expiresAt sql.NullTime
        if opts.Expiration > 0 {
                expiresAt = sql.NullTime{
                        Time:  time.Now().Add(opts.Expiration),
                        Valid: true,
                }
        }

        // PostgreSQL UPSERT using ON CONFLICT
        query := `
                INSERT INTO ` + postgresKVStoreTable + ` (key, value, expires_at)
                VALUES ($1, $2, $3)
                ON CONFLICT (key) DO UPDATE
                SET value = EXCLUDED.value,
                    expires_at = EXCLUDED.expires_at
        `

        _, err = c.db.ExecContext(ctx, query, fullKey, string(valueJSON), expiresAt)
        if err != nil {
                return fmt.Errorf("failed to set cache value: %w", err)
        }

        return nil
}

func (c *PostgreSQL) Delete(ctx context.Context, key string) error {
        fullKey := c.buildKey(key)

        query, args, err := sq.Delete(postgresKVStoreTable).
                Where(sq.Eq{"key": fullKey}).
                PlaceholderFormat(sq.Dollar).
                ToSql()
        if err != nil {
                return fmt.Errorf("failed to build query: %w", err)
        }

        _, err = c.db.ExecContext(ctx, query, args...)
        if err != nil {
                return fmt.Errorf("failed to delete cache value: %w", err)
        }

        return nil
}

func (c *PostgreSQL) Clear(ctx context.Context) error {
        builder := sq.Delete(postgresKVStoreTable)

        if c.prefix != "" {
                var sb strings.Builder
                sb.WriteString(c.prefix)
                sb.WriteString("%")
                builder = builder.Where(sq.Like{"key": sb.String()})
        }

        query, args, err := builder.PlaceholderFormat(sq.Dollar).ToSql()
        if err != nil {
                return fmt.Errorf("failed to build query: %w", err)
        }

        _, err = c.db.ExecContext(ctx, query, args...)
        if err != nil {
                return fmt.Errorf("failed to clear cache: %w", err)
        }

        return nil
}

// CleanupExpired removes all expired entries from the cache.
func (c *PostgreSQL) CleanupExpired(ctx context.Context) error {
        query, args, err := sq.Delete(postgresKVStoreTable).
                Where(sq.And{
                        sq.NotEq{"expires_at": nil},
                        sq.Lt{"expires_at": time.Now()},
                }).
                PlaceholderFormat(sq.Dollar).
                ToSql()
        if err != nil {
                return fmt.Errorf("failed to build query: %w", err)
        }

        _, err = c.db.ExecContext(ctx, query, args...)
        if err != nil {
                return fmt.Errorf("failed to cleanup expired cache entries: %w", err)
        }

        return nil
}

1	package cache
2
3	import (
4	"context"
5	"database/sql"
6	"encoding/json"
7	"errors"
8	"fmt"
9	"strings"
10	"time"
11
12	sq "github.com/Masterminds/squirrel"
13	"github.com/gameap/gameap/internal/repositories/base"
14	)
15
16	const postgresKVStoreTable = "kv_store"
17
18	const postgresDatabaseKeyPrefix = "cache:"
19
20	const postgresCreateTableQuery = `
21	CREATE UNLOGGED TABLE IF NOT EXISTS kv_store (
22	key VARCHAR(128) PRIMARY KEY,
23	value BYTEA NOT NULL,
24	expires_at TIMESTAMPTZ NULL DEFAULT NULL
25	);
26
27	CREATE INDEX IF NOT EXISTS idx_kv_store_key_hash ON kv_store USING HASH (key);
28	CREATE INDEX IF NOT EXISTS idx_kv_store_expires ON kv_store (expires_at) WHERE expires_at IS NOT NULL;
29	`
30
31	type PostgreSQL struct {
32	db base.DB
33	prefix string
34	}
35
36	func NewPostgreSQL(db base.DB) *PostgreSQL {	26✔
37	cache := &PostgreSQL{	26✔
38	db: db,	26✔
39	prefix: postgresDatabaseKeyPrefix,	26✔
40	}	26✔
41		26✔
42	if err := cache.ensureTable(context.TODO()); err != nil {	28✔
43	panic(	2✔
44	fmt.Errorf(	2✔
45	"failed to ensure kv_store table: %w \n"+	2✔
46	"You can manually create the table by executing the following SQL statement:\n %s",	2✔
47	err,	2✔
48	postgresCreateTableQuery,	2✔
49	),	2✔
50	)	2✔
51	}
52
53	return cache	24✔
54	}
55
56	func (c *PostgreSQL) ensureTable(ctx context.Context) error {	26✔
57	// Check if table exists	26✔
58	var exists bool	26✔
59	query := `	26✔
60	SELECT EXISTS (	26✔
61	SELECT FROM information_schema.tables	26✔
62	WHERE table_schema = 'public'	26✔
63	AND table_name = $1	26✔
64	)	26✔
65	`	26✔
66	err := c.db.QueryRowContext(ctx, query, postgresKVStoreTable).Scan(&exists)	26✔
67	if err != nil {	27✔
68	return fmt.Errorf("failed to check if table exists: %w", err)	1✔
69	}	1✔
70
71	if exists {	46✔
72	return nil	21✔
73	}	21✔
74
75	// Table doesn't exist, create it
76	_, err = c.db.ExecContext(ctx, postgresCreateTableQuery)	4✔
77	if err != nil {	5✔
78	return fmt.Errorf("failed to create kv_store table: %w", err)	1✔
79	}	1✔
80
81	return nil	3✔
82	}
83
84	func (c *PostgreSQL) buildKey(key string) string {	128✔
85	var sb strings.Builder	128✔
86	sb.Grow(len(c.prefix) + len(key))	128✔
87	sb.WriteString(c.prefix)	128✔
88	sb.WriteString(key)	128✔
89		128✔
90	return sb.String()	128✔
91	}	128✔
92
93	func (c *PostgreSQL) Get(ctx context.Context, key string) (any, error) {	63✔
94	fullKey := c.buildKey(key)	63✔
95		63✔
96	query, args, err := sq.Select("value", "expires_at").	63✔
97	From(postgresKVStoreTable).	63✔
98	Where(sq.Eq{"key": fullKey}).	63✔
99	PlaceholderFormat(sq.Dollar).	63✔
100	ToSql()	63✔
101	if err != nil {	63✔
102	return nil, fmt.Errorf("failed to build query: %w", err)	×
103	}	×
104
105	var valueJSON string	63✔
106	var expiresAt sql.NullTime	63✔
107		63✔
108	err = c.db.QueryRowContext(ctx, query, args...).Scan(&valueJSON, &expiresAt)	63✔
109	if err != nil {	72✔
110	if errors.Is(err, sql.ErrNoRows) {	17✔
111	return nil, ErrNotFound	8✔
112	}	8✔
113
114	return nil, fmt.Errorf("failed to query row: %w", err)	1✔
115	}
116
117	// Treat an expired row as absent. We deliberately do NOT delete it inline:
118	// that turns every read of an expired key into a write (amplified under a
119	// stampede of concurrent readers). The background CleanupExpired sweep
120	// reclaims the row.
121	if expiresAt.Valid && expiresAt.Time.Before(time.Now()) {	56✔
122	return nil, ErrNotFound	2✔
123	}	2✔
124
125	var value any	52✔
126	if err := json.Unmarshal([]byte(valueJSON), &value); err != nil {	53✔
127	return nil, fmt.Errorf("failed to unmarshal value: %w", err)	1✔
128	}	1✔
129
130	return value, nil	51✔
131	}
132
133	func (c *PostgreSQL) Set(ctx context.Context, key string, value any, options ...Option) error {	53✔
134	opts := ApplyOptions(options...)	53✔
135	fullKey := c.buildKey(key)	53✔
136		53✔
137	valueJSON, err := json.Marshal(value)	53✔
138	if err != nil {	54✔
139	return fmt.Errorf("failed to marshal value: %w", err)	1✔
140	}	1✔
141
142	var expiresAt sql.NullTime	52✔
143	if opts.Expiration > 0 {	56✔
144	expiresAt = sql.NullTime{	4✔
145	Time: time.Now().Add(opts.Expiration),	4✔
146	Valid: true,	4✔
147	}	4✔
148	}	4✔
149
150	// PostgreSQL UPSERT using ON CONFLICT
151	query := `	52✔
152	INSERT INTO ` + postgresKVStoreTable + ` (key, value, expires_at)	52✔
153	VALUES ($1, $2, $3)	52✔
154	ON CONFLICT (key) DO UPDATE	52✔
155	SET value = EXCLUDED.value,	52✔
156	expires_at = EXCLUDED.expires_at	52✔
157	`	52✔
158		52✔
159	_, err = c.db.ExecContext(ctx, query, fullKey, string(valueJSON), expiresAt)	52✔
160	if err != nil {	53✔
161	return fmt.Errorf("failed to set cache value: %w", err)	1✔
162	}	1✔
163
164	return nil	51✔
165	}
166
167	func (c *PostgreSQL) Delete(ctx context.Context, key string) error {	12✔
168	fullKey := c.buildKey(key)	12✔
169		12✔
170	query, args, err := sq.Delete(postgresKVStoreTable).	12✔
171	Where(sq.Eq{"key": fullKey}).	12✔
172	PlaceholderFormat(sq.Dollar).	12✔
173	ToSql()	12✔
174	if err != nil {	12✔
175	return fmt.Errorf("failed to build query: %w", err)	×
176	}	×
177
178	_, err = c.db.ExecContext(ctx, query, args...)	12✔
179	if err != nil {	13✔
180	return fmt.Errorf("failed to delete cache value: %w", err)	1✔
181	}	1✔
182
183	return nil	11✔
184	}
185
186	func (c *PostgreSQL) Clear(ctx context.Context) error {	27✔
187	builder := sq.Delete(postgresKVStoreTable)	27✔
188		27✔
189	if c.prefix != "" {	54✔
190	var sb strings.Builder	27✔
191	sb.WriteString(c.prefix)	27✔
192	sb.WriteString("%")	27✔
193	builder = builder.Where(sq.Like{"key": sb.String()})	27✔
194	}	27✔
195
196	query, args, err := builder.PlaceholderFormat(sq.Dollar).ToSql()	27✔
197	if err != nil {	27✔
198	return fmt.Errorf("failed to build query: %w", err)	×
199	}	×
200
201	_, err = c.db.ExecContext(ctx, query, args...)	27✔
202	if err != nil {	28✔
203	return fmt.Errorf("failed to clear cache: %w", err)	1✔
204	}	1✔
205
206	return nil	26✔
207	}
208
209	// CleanupExpired removes all expired entries from the cache.
210	func (c *PostgreSQL) CleanupExpired(ctx context.Context) error {	3✔
211	query, args, err := sq.Delete(postgresKVStoreTable).	3✔
212	Where(sq.And{	3✔
213	sq.NotEq{"expires_at": nil},	3✔
214	sq.Lt{"expires_at": time.Now()},	3✔
215	}).	3✔
216	PlaceholderFormat(sq.Dollar).	3✔
217	ToSql()	3✔
218	if err != nil {	3✔
219	return fmt.Errorf("failed to build query: %w", err)	×
220	}	×
221
222	_, err = c.db.ExecContext(ctx, query, args...)	3✔
223	if err != nil {	4✔
224	return fmt.Errorf("failed to cleanup expired cache entries: %w", err)	1✔
225	}	1✔
226
227	return nil	2✔
228	}

gameap / gameap / 28878367588

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous