144 lines
2.8 KiB
Go
144 lines
2.8 KiB
Go
package data
|
|
|
|
import (
|
|
"context"
|
|
"errors"
|
|
|
|
"tea.kareha.org/pot/himewiki/internal/config"
|
|
)
|
|
|
|
func LoadImage(name string) (int, []byte, error) {
|
|
var id int
|
|
var content []byte
|
|
err := db.QueryRow(context.Background(),
|
|
"SELECT revision_id, content FROM images WHERE name=$1", name).
|
|
Scan(&id, &content)
|
|
if err != nil {
|
|
return 0, nil, err
|
|
}
|
|
|
|
return id, content, nil
|
|
}
|
|
|
|
func SaveImage(cfg *config.Config, name string, content []byte) error {
|
|
ctx := context.Background()
|
|
tx, err := db.Begin(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback(ctx)
|
|
|
|
var newRevID int
|
|
err = tx.QueryRow(ctx,
|
|
`INSERT INTO image_revisions (name, content, created_at)
|
|
VALUES ($1, $2, now())
|
|
RETURNING id`,
|
|
name, content).Scan(&newRevID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = tx.Exec(ctx,
|
|
`INSERT INTO images (name, content, revision_id, updated_at)
|
|
VALUES ($1, $2, $3, now())
|
|
ON CONFLICT (name) DO UPDATE
|
|
SET content=EXCLUDED.content,
|
|
revision_id=EXCLUDED.revision_id,
|
|
updated_at=now()`,
|
|
name, content, newRevID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
var imageCount int64
|
|
err = tx.QueryRow(ctx, `
|
|
UPDATE state
|
|
SET image_counter = image_counter + 1
|
|
WHERE id = 1
|
|
RETURNING image_counter
|
|
`).Scan(&imageCount)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
err = tx.Commit(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
if imageCount%int64(cfg.Vacuum.CheckEvery) == 0 {
|
|
var sizeBytes int64
|
|
err = db.QueryRow(ctx, `
|
|
SELECT pg_total_relation_size('images') +
|
|
pg_total_relation_size('image_revisions')
|
|
`).Scan(&sizeBytes)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
if sizeBytes >= cfg.Vacuum.ImageThreshold {
|
|
_, err = db.Exec(ctx, "VACUUM FULL images")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = db.Exec(ctx, `
|
|
WITH cutoff AS (
|
|
SELECT COUNT(*) / 2 AS limit_count
|
|
FROM image_revisions
|
|
GROUP BY name
|
|
ORDER BY COUNT(*) DESC
|
|
LIMIT 1
|
|
)
|
|
DELETE FROM image_revisions r
|
|
USING cutoff
|
|
WHERE r.id IN (
|
|
SELECT id
|
|
FROM image_revisions r2, cutoff
|
|
WHERE r2.name = r.name
|
|
ORDER BY r2.created_at ASC
|
|
OFFSET cutoff.limit_count
|
|
)
|
|
`)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
_, err = db.Exec(ctx, "VACUUM FULL image_revisions")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func LoadAllImages(page int, perPage int) ([]string, error) {
|
|
if page < 1 {
|
|
return nil, errors.New("invalid page")
|
|
}
|
|
if perPage < 1 {
|
|
return nil, errors.New("invalid perPage")
|
|
}
|
|
offset := (page - 1) * perPage
|
|
|
|
rows, err := db.Query(context.Background(),
|
|
"SELECT name FROM images ORDER BY name LIMIT $1 OFFSET $2",
|
|
perPage, offset)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var results []string
|
|
for rows.Next() {
|
|
var name string
|
|
if err := rows.Scan(&name); err != nil {
|
|
return nil, err
|
|
}
|
|
results = append(results, name)
|
|
}
|
|
return results, nil
|
|
}
|