mirror of https://github.com/jlelse/GoBlog
You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
222 lines
5.3 KiB
Go
222 lines
5.3 KiB
Go
package main
|
|
|
|
import (
|
|
"bytes"
|
|
"database/sql"
|
|
"encoding/gob"
|
|
"net/http"
|
|
|
|
"go.goblog.app/app/pkgs/bufferpool"
|
|
)
|
|
|
|
const (
|
|
defaultBlogStatsPath = "/statistics"
|
|
blogStatsTablePath = ".table.html"
|
|
)
|
|
|
|
func (a *goBlog) initBlogStats() {
|
|
f := func(p *post) {
|
|
a.db.resetBlogStats(p.Blog)
|
|
}
|
|
a.pPostHooks = append(a.pPostHooks, f)
|
|
a.pUpdateHooks = append(a.pUpdateHooks, f)
|
|
a.pDeleteHooks = append(a.pDeleteHooks, f)
|
|
a.pUndeleteHooks = append(a.pUndeleteHooks, f)
|
|
}
|
|
|
|
func (a *goBlog) serveBlogStats(w http.ResponseWriter, r *http.Request) {
|
|
_, bc := a.getBlog(r)
|
|
canonical := bc.getRelativePath(defaultIfEmpty(bc.BlogStats.Path, defaultBlogStatsPath))
|
|
a.render(w, r, a.renderBlogStats, &renderData{
|
|
Canonical: a.getFullAddress(canonical),
|
|
Data: &blogStatsRenderData{
|
|
tableUrl: canonical + blogStatsTablePath,
|
|
},
|
|
})
|
|
}
|
|
|
|
func (a *goBlog) serveBlogStatsTable(w http.ResponseWriter, r *http.Request) {
|
|
blog, _ := a.getBlog(r)
|
|
data, err, _ := a.blogStatsCacheGroup.Do(blog, func() (any, error) {
|
|
return a.db.getBlogStats(blog)
|
|
})
|
|
if err != nil {
|
|
a.serveError(w, r, err.Error(), http.StatusInternalServerError)
|
|
return
|
|
}
|
|
// Render
|
|
a.render(w, r, a.renderBlogStatsTable, &renderData{
|
|
Data: data,
|
|
})
|
|
}
|
|
|
|
const blogStatsSql = `
|
|
with filtered as (
|
|
select
|
|
path,
|
|
pub,
|
|
substr(pub, 1, 4) as year,
|
|
substr(pub, 6, 2) as month,
|
|
wordcount(content) as words,
|
|
charcount(content) as chars
|
|
from (
|
|
select
|
|
path,
|
|
tolocal(published) as pub,
|
|
mdtext(coalesce(content, '')) as content
|
|
from posts
|
|
where status = @status and visibility = @visibility and blog = @blog
|
|
)
|
|
)
|
|
select *
|
|
from (
|
|
select *
|
|
from (
|
|
select
|
|
year,
|
|
'A',
|
|
coalesce(count(path), 0) as pc,
|
|
coalesce(sum(words), 0) as wc,
|
|
coalesce(sum(chars), 0) as cc,
|
|
coalesce(round(sum(words)/count(path), 0), 0) as wpp
|
|
from filtered
|
|
where pub != ''
|
|
group by year
|
|
order by year desc
|
|
)
|
|
union all
|
|
select *
|
|
from (
|
|
select
|
|
year,
|
|
month,
|
|
coalesce(count(path), 0) as pc,
|
|
coalesce(sum(words), 0) as wc,
|
|
coalesce(sum(chars), 0) as cc,
|
|
coalesce(round(sum(words)/count(path), 0), 0) as wpp
|
|
from filtered
|
|
where pub != ''
|
|
group by year, month
|
|
order by year desc, month desc
|
|
)
|
|
union all
|
|
select *
|
|
from (
|
|
select
|
|
'N',
|
|
'N',
|
|
coalesce(count(path), 0) as pc,
|
|
coalesce(sum(words), 0) as wc,
|
|
coalesce(sum(chars), 0) as cc,
|
|
coalesce(round(sum(words)/count(path), 0), 0) as wpp
|
|
from filtered
|
|
where pub == ''
|
|
)
|
|
union all
|
|
select *
|
|
from (
|
|
select
|
|
'A',
|
|
'A',
|
|
coalesce(count(path), 0) as pc,
|
|
coalesce(sum(words), 0) as wc,
|
|
coalesce(sum(chars), 0) as cc,
|
|
coalesce(round(sum(words)/count(path), 0), 0) as wpp
|
|
from filtered
|
|
)
|
|
);
|
|
`
|
|
|
|
type blogStatsRow struct {
|
|
Name, Posts, Chars, Words, WordsPerPost string
|
|
}
|
|
|
|
type blogStatsData struct {
|
|
Total blogStatsRow
|
|
NoDate blogStatsRow
|
|
Years []blogStatsRow
|
|
Months map[string][]blogStatsRow
|
|
}
|
|
|
|
func (db *database) getBlogStats(blog string) (data *blogStatsData, err error) {
|
|
// Check cache
|
|
if stats := db.loadBlogStatsCache(blog); stats != nil {
|
|
return stats, nil
|
|
}
|
|
// Prevent creating posts while getting stats
|
|
db.pcm.Lock()
|
|
defer db.pcm.Unlock()
|
|
// Scan objects
|
|
currentStats := blogStatsRow{}
|
|
var currentMonth, currentYear string
|
|
// Data to later return
|
|
data = &blogStatsData{
|
|
Months: map[string][]blogStatsRow{},
|
|
}
|
|
// Query and scan
|
|
rows, err := db.Query(blogStatsSql, sql.Named("status", statusPublished), sql.Named("visibility", visibilityPublic), sql.Named("blog", blog))
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for rows.Next() {
|
|
err = rows.Scan(¤tYear, ¤tMonth, ¤tStats.Posts, ¤tStats.Words, ¤tStats.Chars, ¤tStats.WordsPerPost)
|
|
if currentYear == "A" && currentMonth == "A" {
|
|
data.Total = blogStatsRow{
|
|
Posts: currentStats.Posts,
|
|
Words: currentStats.Words,
|
|
Chars: currentStats.Chars,
|
|
WordsPerPost: currentStats.WordsPerPost,
|
|
}
|
|
} else if currentYear == "N" && currentMonth == "N" {
|
|
data.NoDate = blogStatsRow{
|
|
Posts: currentStats.Posts,
|
|
Words: currentStats.Words,
|
|
Chars: currentStats.Chars,
|
|
WordsPerPost: currentStats.WordsPerPost,
|
|
}
|
|
} else if currentMonth == "A" {
|
|
data.Years = append(data.Years, blogStatsRow{
|
|
Name: currentYear,
|
|
Posts: currentStats.Posts,
|
|
Words: currentStats.Words,
|
|
Chars: currentStats.Chars,
|
|
WordsPerPost: currentStats.WordsPerPost,
|
|
})
|
|
} else {
|
|
data.Months[currentYear] = append(data.Months[currentYear], blogStatsRow{
|
|
Name: currentMonth,
|
|
Posts: currentStats.Posts,
|
|
Words: currentStats.Words,
|
|
Chars: currentStats.Chars,
|
|
WordsPerPost: currentStats.WordsPerPost,
|
|
})
|
|
}
|
|
}
|
|
db.cacheBlogStats(blog, data)
|
|
return data, nil
|
|
}
|
|
|
|
func (db *database) cacheBlogStats(blog string, stats *blogStatsData) {
|
|
buf := bufferpool.Get()
|
|
_ = gob.NewEncoder(buf).Encode(stats)
|
|
_ = db.cachePersistently("blogstats_"+blog, buf.Bytes())
|
|
bufferpool.Put(buf)
|
|
}
|
|
|
|
func (db *database) loadBlogStatsCache(blog string) (stats *blogStatsData) {
|
|
data, err := db.retrievePersistentCache("blogstats_" + blog)
|
|
if err != nil || data == nil {
|
|
return nil
|
|
}
|
|
stats = &blogStatsData{}
|
|
err = gob.NewDecoder(bytes.NewReader(data)).Decode(stats)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
return stats
|
|
}
|
|
|
|
func (db *database) resetBlogStats(blog string) {
|
|
_ = db.clearPersistentCache("blogstats_" + blog)
|
|
}
|