aboutsummaryrefslogtreecommitdiff
path: root/pkg/app
diff options
context:
space:
mode:
authorMax Magorsch <arzano@gentoo.org>2020-06-22 00:45:14 +0000
committerMax Magorsch <arzano@gentoo.org>2020-06-22 00:45:14 +0000
commit2149bb7fb6b6f732ac8364e45d072a22921957db (patch)
treeb72b5b876489df7cbec280b06440b89f9fe46a2d /pkg/app
parentImprove the error handling during the import (diff)
downloadarchives-2149bb7fb6b6f732ac8364e45d072a22921957db.tar.gz
archives-2149bb7fb6b6f732ac8364e45d072a22921957db.tar.bz2
archives-2149bb7fb6b6f732ac8364e45d072a22921957db.zip
Rework the data model to improve the performance
Signed-off-by: Max Magorsch <arzano@gentoo.org>
Diffstat (limited to 'pkg/app')
-rw-r--r--pkg/app/home/home.go20
-rw-r--r--pkg/app/home/utils.go4
-rw-r--r--pkg/app/list/messages.go20
-rw-r--r--pkg/app/list/show.go18
-rw-r--r--pkg/app/list/threads.go23
-rw-r--r--pkg/app/message/show.go8
-rw-r--r--pkg/app/message/utils.go53
-rw-r--r--pkg/app/popular/utils.go17
-rw-r--r--pkg/app/search/search.go8
9 files changed, 90 insertions, 81 deletions
diff --git a/pkg/app/home/home.go b/pkg/app/home/home.go
index e2d3955..fe8a56e 100644
--- a/pkg/app/home/home.go
+++ b/pkg/app/home/home.go
@@ -21,17 +21,17 @@ func Show(w http.ResponseWriter, r *http.Request) {
var messages []*models.Message
database.DBCon.Model(&messages).
WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE '[` + mailingList[0] + `]%'`).
- WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE 'Re: [` + mailingList[0] + `]%'`)
- return q, nil
- }).
- WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`headers::jsonb->>'To' LIKE '%` + mailingList[0] + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + mailingList[0] + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'To' LIKE '%` + mailingList[0] + `@gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + mailingList[0] + `@gentoo.org%'`)
+ q = q.WhereOr(`subject LIKE '[` + mailingList[0] + `]%'`).
+ WhereOr(`subject LIKE 'Re: [` + mailingList[0] + `]%'`)
return q, nil
}).
+ //WhereGroup(func(q *orm.Query) (*orm.Query, error) {
+ // q = q.WhereOr(`headers::jsonb->>'To' LIKE '%` + mailingList[0] + `@lists.gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'Cc' LIKE '%` + mailingList[0] + `@lists.gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'To' LIKE '%` + mailingList[0] + `@gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'Cc' LIKE '%` + mailingList[0] + `@gentoo.org%'`)
+ // return q, nil
+ //}).
Order("date DESC").
Limit(5).
Select()
@@ -57,7 +57,7 @@ func Show(w http.ResponseWriter, r *http.Request) {
templateData := struct {
MailingLists []models.MailingList
- PopularThreads models.Threads
+ PopularThreads []*models.Message
MessageCount string
CurrentMonth string
}{
diff --git a/pkg/app/home/utils.go b/pkg/app/home/utils.go
index f854549..767b60d 100644
--- a/pkg/app/home/utils.go
+++ b/pkg/app/home/utils.go
@@ -19,7 +19,7 @@ func renderIndexTemplate(w http.ResponseWriter, templateData interface{}) {
Funcs(template.FuncMap{
"makeMessage": func(headers map[string][]string) models.Message {
return models.Message{
- Headers: headers,
+ //Headers: headers,
}
},
}).
@@ -35,7 +35,7 @@ func getAllMessagesCount() int {
var messsageCount int
database.DBCon.Model((*models.Message)(nil)).QueryOne(pg.Scan(&messsageCount), `
SELECT
- count(DISTINCT messages.headers->>'Message-Id')
+ count(DISTINCT messages.message_id)
FROM
messages;
`)
diff --git a/pkg/app/list/messages.go b/pkg/app/list/messages.go
index 383e891..7a78811 100644
--- a/pkg/app/list/messages.go
+++ b/pkg/app/list/messages.go
@@ -33,20 +33,20 @@ func Messages(w http.ResponseWriter, r *http.Request) {
var messages []*models.Message
query := database.DBCon.Model(&messages).
- Column("id", "headers", "date").
+ Column("id", "subject", "from", "date").
Where("to_char(date, 'YYYY-MM') = ?", combinedDate).
WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE '[` + listName + `]%'`).
- WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE 'Re: [` + listName + `]%'`)
- return q, nil
- }).
- WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@gentoo.org%'`)
+ q = q.WhereOr(`subject LIKE '[` + listName + `]%'`).
+ WhereOr(`subject LIKE 'Re: [` + listName + `]%'`)
return q, nil
}).
+ //WhereGroup(func(q *orm.Query) (*orm.Query, error) {
+ // q = q.WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@lists.gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@lists.gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@gentoo.org%'`)
+ // return q, nil
+ //}).
Order("date DESC")
messagesCount, _ := query.Count()
diff --git a/pkg/app/list/show.go b/pkg/app/list/show.go
index 8db8778..d90f236 100644
--- a/pkg/app/list/show.go
+++ b/pkg/app/list/show.go
@@ -18,17 +18,17 @@ func Show(w http.ResponseWriter, r *http.Request) {
}
err := database.DBCon.Model((*models.Message)(nil)).
WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE '[` + listName + `]%'`).
- WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE 'Re: [` + listName + `]%'`)
- return q, nil
- }).
- WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@gentoo.org%'`)
+ q = q.WhereOr(`subject LIKE '[` + listName + `]%'`).
+ WhereOr(`subject LIKE 'Re: [` + listName + `]%'`)
return q, nil
}).
+ //WhereGroup(func(q *orm.Query) (*orm.Query, error) {
+ // q = q.WhereOr(`to LIKE '%` + listName + `@lists.gentoo.org%'`).
+ // WhereOr(`cc LIKE '%` + listName + `@lists.gentoo.org%'`).
+ // WhereOr(`to LIKE '%` + listName + `@gentoo.org%'`).
+ // WhereOr(`cc LIKE '%` + listName + `@gentoo.org%'`)
+ // return q, nil
+ //}).
ColumnExpr("to_char(date, 'YYYY-MM') AS combined_date").
ColumnExpr("count(*) AS message_count").
Group("combined_date").
diff --git a/pkg/app/list/threads.go b/pkg/app/list/threads.go
index 33ade3c..c069673 100644
--- a/pkg/app/list/threads.go
+++ b/pkg/app/list/threads.go
@@ -32,22 +32,21 @@ func Threads(w http.ResponseWriter, r *http.Request) {
var messages []*models.Message
query := database.DBCon.Model(&messages).
- Column("id", "headers", "date").
+ Column("id", "subject", "from", "date").
Where("to_char(date, 'YYYY-MM') = ?", combinedDate).
- Where(`NOT headers::jsonb ? 'References'`).
- Where(`NOT headers::jsonb ? 'In-Reply-To'`).
+ Where(`starts_thread = TRUE`).
WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE '[` + listName + `]%'`).
- WhereOr(`(headers::jsonb->>'Subject')::jsonb->>0 LIKE 'Re: [` + listName + `]%'`)
- return q, nil
- }).
- WhereGroup(func(q *orm.Query) (*orm.Query, error) {
- q = q.WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@lists.gentoo.org%'`).
- WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@gentoo.org%'`).
- WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@gentoo.org%'`)
+ q = q.WhereOr(`subject LIKE '[` + listName + `]%'`).
+ WhereOr(`subject LIKE 'Re: [` + listName + `]%'`)
return q, nil
}).
+ //WhereGroup(func(q *orm.Query) (*orm.Query, error) {
+ // q = q.WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@lists.gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@lists.gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'To' LIKE '%` + listName + `@gentoo.org%'`).
+ // WhereOr(`headers::jsonb->>'Cc' LIKE '%` + listName + `@gentoo.org%'`)
+ // return q, nil
+ //}).
Order("date DESC")
messagesCount, _ := query.Count()
diff --git a/pkg/app/message/show.go b/pkg/app/message/show.go
index a027596..3d56544 100644
--- a/pkg/app/message/show.go
+++ b/pkg/app/message/show.go
@@ -26,9 +26,9 @@ func Show(w http.ResponseWriter, r *http.Request) {
var inReplyTos []*models.Message
var inReplyTo *models.Message
- if message.HasHeaderField("In-Reply-To") {
+ if message.InReplyTo != nil {
err = database.DBCon.Model(&inReplyTos).
- Where(`(headers::jsonb->>'Message-Id')::jsonb ? '` + message.GetHeaderField("In-Reply-To") + `'`).
+ Where(`(headers::jsonb->>'Message-Id')::jsonb ? '` + message.InReplyTo.Id + `'`).
Select()
if err != nil || len(inReplyTos) < 1 {
inReplyTo = nil
@@ -41,8 +41,8 @@ func Show(w http.ResponseWriter, r *http.Request) {
var replies []*models.Message
database.DBCon.Model(&replies).
- Where(`(headers::jsonb->>'References')::jsonb ? '` + message.GetHeaderField("Message-Id") + `'`).
- WhereOr(`(headers::jsonb->>'In-Reply-To')::jsonb ? '` + message.GetHeaderField("Message-Id") + `'`).
+ Where(`(headers::jsonb->>'References')::jsonb ? '` + message.Id + `'`).
+ WhereOr(`(headers::jsonb->>'In-Reply-To')::jsonb ? '` + message.Id + `'`).
Order("date ASC").Select()
renderMessageTemplate(w, listName, message, inReplyTo, replies)
diff --git a/pkg/app/message/utils.go b/pkg/app/message/utils.go
index 0cb40f5..f79e6f3 100644
--- a/pkg/app/message/utils.go
+++ b/pkg/app/message/utils.go
@@ -35,29 +35,40 @@ func renderMessageTemplate(w http.ResponseWriter, listName string, message *mode
func getFuncMap() template.FuncMap {
return template.FuncMap{
- "formatAddr": func(addr string) string {
- if strings.Contains(addr, "@lists.gentoo.org") || strings.Contains(addr, "@gentoo.org") {
- addr = strings.ReplaceAll(addr, "@lists.gentoo.org", "@l.g.o")
- addr = strings.ReplaceAll(addr, "@gentoo.org", "@g.o")
- } else {
- start := false
- for i := len(addr) - 1; i > 0; i-- {
- if addr[i] == '@' {
- break
- }
- if start {
- out := []rune(addr)
- out[i] = '×'
- addr = string(out)
- }
- if addr[i] == '.' {
- start = true
- }
- }
+ "formatAddr": formatAddr,
+ "formatAddrList": formatAddrList,
+ }
+}
+
+func formatAddr(addr string) string {
+ if strings.Contains(addr, "@lists.gentoo.org") || strings.Contains(addr, "@gentoo.org") {
+ addr = strings.ReplaceAll(addr, "@lists.gentoo.org", "@l.g.o")
+ addr = strings.ReplaceAll(addr, "@gentoo.org", "@g.o")
+ } else {
+ start := false
+ for i := len(addr) - 1; i > 0; i-- {
+ if addr[i] == '@' {
+ break
+ }
+ if start {
+ out := []rune(addr)
+ out[i] = '×'
+ addr = string(out)
}
- return addr
- },
+ if addr[i] == '.' {
+ start = true
+ }
+ }
+ }
+ return addr
+}
+
+func formatAddrList(addrList []string) string {
+ var formatedAddrList []string
+ for _, addr := range addrList {
+ formatedAddrList = append(formatedAddrList, formatAddr(addr))
}
+ return strings.Join(formatedAddrList, ", ")
}
func replaceAtIndex(in string, r rune, i int) string {
diff --git a/pkg/app/popular/utils.go b/pkg/app/popular/utils.go
index c5772d7..bc2a12f 100644
--- a/pkg/app/popular/utils.go
+++ b/pkg/app/popular/utils.go
@@ -16,7 +16,7 @@ func renderPopularThreads(w http.ResponseWriter, templateData interface{}) {
Funcs(template.FuncMap{
"makeMessage": func(headers map[string][]string) models.Message {
return models.Message{
- Headers: headers,
+ //Headers: headers,
}
},
}).
@@ -28,17 +28,16 @@ func renderPopularThreads(w http.ResponseWriter, templateData interface{}) {
// utility methods
-func GetPopularThreads(n int, date string) (models.Threads, error) {
- var popularThreads models.Threads
- err := database.DBCon.Model(&popularThreads).
- TableExpr(`(SELECT id, headers, regexp_replace(regexp_replace(regexp_replace(regexp_replace(headers::jsonb->>'Subject','^\["',''),'"\]$',''),'^Re:\s',''), '^\[.*\]', '') AS c FROM messages WHERE date >= '2020-06-12'::date) t`).
- ColumnExpr(`c as Subject, jsonb_agg(id)->>0 as Id, jsonb_agg(headers)->>0 as Headers, Count(*) as Count`).
- GroupExpr(`c`).
- OrderExpr(`count DESC`).
+func GetPopularThreads(n int, date string) ([]*models.Message, error) {
+
+ var recentMessages []*models.Message
+
+ err := database.DBCon.Model(&recentMessages).
+ OrderExpr("date DESC").
Limit(n).
Select()
- return popularThreads, err
+ return recentMessages, err
}
func GetMessagesFromPopularThreads(threads models.Threads) []*models.Message {
diff --git a/pkg/app/search/search.go b/pkg/app/search/search.go
index f6498e9..072bf2f 100644
--- a/pkg/app/search/search.go
+++ b/pkg/app/search/search.go
@@ -44,10 +44,10 @@ func Search(w http.ResponseWriter, r *http.Request) {
//
var searchResults []*models.Message
query := database.DBCon.Model(&searchResults).
- WhereOr(`headers::jsonb->>'From' LIKE ?`, "%"+searchTerm+"%").
+ WhereOr(`message.from LIKE ?`, "%"+searchTerm+"%").
Order("date DESC")
if showThreads {
- query = query.Where(`NOT headers::jsonb ? 'References'`).Where(`NOT headers::jsonb ? 'In-Reply-To'`)
+ query = query.Where(`starts_thread = TRUE`)
}
messagesCount, _ := query.Count()
@@ -65,7 +65,7 @@ func Search(w http.ResponseWriter, r *http.Request) {
query = database.DBCon.Model(&searchResults).
Where(`tsv_subject @@ to_tsquery(''?'')`, searchTerm)
if showThreads {
- query = query.Where(`NOT headers::jsonb ? 'References'`).Where(`NOT headers::jsonb ? 'In-Reply-To'`)
+ query = query.Where(`starts_thread = TRUE`)
}
messagesCount, _ = query.Count()
@@ -83,7 +83,7 @@ func Search(w http.ResponseWriter, r *http.Request) {
query = database.DBCon.Model(&searchResults).
Where(`tsv_body @@ to_tsquery(''?'')`, searchTerm)
if showThreads {
- query = query.Where(`NOT headers::jsonb ? 'References'`).Where(`NOT headers::jsonb ? 'In-Reply-To'`)
+ query = query.Where(`starts_thread = TRUE`)
}
messagesCount, _ = query.Count()