From 28e256f18f96d9afdcb903b3bc3daf93747c2195 Mon Sep 17 00:00:00 2001 From: FChannel <=> Date: Fri, 19 Feb 2021 01:00:53 -0800 Subject: cleaned up db functions --- CacheDatabase.go | 410 ++++--------------------------------------------------- 1 file changed, 23 insertions(+), 387 deletions(-) (limited to 'CacheDatabase.go') diff --git a/CacheDatabase.go b/CacheDatabase.go index 010ad67..ec3c409 100644 --- a/CacheDatabase.go +++ b/CacheDatabase.go @@ -4,7 +4,6 @@ import "fmt" import "time" import "database/sql" import _ "github.com/lib/pq" -import "sort" func WriteObjectToCache(db *sql.DB, obj ObjectBase) ObjectBase { if len(obj.Attachment) > 0 { @@ -21,7 +20,7 @@ func WriteObjectToCache(db *sql.DB, obj ObjectBase) ObjectBase { WriteActivitytoCache(db, obj) } - writeObjectReplyToDB(db, obj) + WriteObjectReplyToDB(db, obj) if obj.Replies != nil { for _, e := range obj.Replies.OrderedItems { @@ -159,188 +158,6 @@ func WritePreviewToCache(db *sql.DB, obj NestedObjectBase) { } } -func GetActivityFromCache(db *sql.DB, id string) Collection { - var nColl Collection - var nActor Actor - var result []ObjectBase - - nColl.Actor = &nActor - - query := `select actor, id, name, content, type, published, updated, attributedto, attachment, preview, actor from activitystream where id=$1 order by updated asc` - - rows, err := db.Query(query, id) - - CheckError(err, "error query object from db") - - defer rows.Close() - for rows.Next(){ - var post ObjectBase - var actor Actor - var attachID string - var previewID string - - - err = rows.Scan(&nColl.Actor.Id, &post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id) - - CheckError(err, "error scan object into post struct") - - post.Actor = &actor - - // var postCnt int - // var imgCnt int - - post.Replies, _, _ = GetObjectRepliesCache(db, post) - - // post.Replies.TotalItems, post.Replies.TotalImgs = GetObjectRepliesCacheCount(db, post) - - // post.Replies.TotalItems = post.Replies.TotalItems + postCnt - // post.Replies.TotalImgs = post.Replies.TotalImgs + imgCnt - - post.Attachment = GetObjectAttachmentCache(db, attachID) - - post.Preview = GetObjectPreviewCache(db, previewID) - - result = append(result, post) - } - - nColl.OrderedItems = result - - return nColl -} - -func GetObjectFromCache(db *sql.DB, id string) Collection { - var nColl Collection - var result []ObjectBase - - query := `select id, name, content, type, published, updated, attributedto, attachment, preview, actor from cacheactivitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' order by updated asc` - - rows, err := db.Query(query, id) - - CheckError(err, "error query object from db cache") - - defer rows.Close() - for rows.Next(){ - var post ObjectBase - var actor Actor - var attachID string - var previewID string - - err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id) - - CheckError(err, "error scan object into post struct cache") - - post.Actor = &actor - - // var postCnt int - // var imgCnt int - post.Replies, _, _ = GetObjectRepliesDB(db, post) - - // post.Replies.TotalItems, post.Replies.TotalImgs = GetObjectRepliesCacheCount(db, post) - - // post.Replies.TotalItems = post.Replies.TotalItems + postCnt - // post.Replies.TotalImgs = post.Replies.TotalImgs + imgCnt - - post.Attachment = GetObjectAttachmentCache(db, attachID) - - post.Preview = GetObjectPreviewCache(db, previewID) - - result = append(result, post) - } - - nColl.OrderedItems = result - - return nColl -} - -func GetObjectFromCacheCatalog(db *sql.DB, id string) Collection { - var nColl Collection - var result []ObjectBase - - query := `select id, name, content, type, published, updated, attributedto, attachment, preview, actor from cacheactivitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' order by updated asc` - - rows, err := db.Query(query, id) - - CheckError(err, "error query object from db cache") - - defer rows.Close() - for rows.Next(){ - var post ObjectBase - var actor Actor - var attachID string - var previewID string - - err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id) - - CheckError(err, "error scan object into post struct cache") - - post.Actor = &actor - - var replies CollectionBase - - post.Replies = &replies - - post.Replies.TotalItems, post.Replies.TotalImgs = GetObjectRepliesCount(db, post) - - post.Attachment = GetObjectAttachmentCache(db, attachID) - - post.Preview = GetObjectPreviewCache(db, previewID) - - result = append(result, post) - } - - nColl.OrderedItems = result - - return nColl -} - -func GetObjectByIDFromCache(db *sql.DB, postID string) Collection { - var nColl Collection - var result []ObjectBase - - query := `select id, name, content, type, published, attributedto, attachment, preview, actor from cacheactivitystream where id=$1 order by published desc` - - rows, err := db.Query(query, postID) - - CheckError(err, "error query object from db cache") - - defer rows.Close() - for rows.Next(){ - var post ObjectBase - var actor Actor - var attachID string - var previewID string - - err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.AttributedTo, &attachID, &previewID, &actor.Id) - - CheckError(err, "error scan object into post struct cache") - - actor = GetRemoteActor(actor.Id) - - post.Actor = &actor - - nColl.Actor = &actor - - // var postCnt int - // var imgCnt int - post.Replies, _, _ = GetObjectRepliesDB(db, post) - - // post.Replies.TotalItems, post.Replies.TotalImgs = GetObjectRepliesCacheCount(db, post) - - // post.Replies.TotalItems = post.Replies.TotalItems + postCnt - // post.Replies.TotalImgs = post.Replies.TotalImgs + imgCnt - - post.Attachment = GetObjectAttachmentCache(db, attachID) - - post.Preview = GetObjectPreviewCache(db, previewID) - - result = append(result, post) - } - - nColl.OrderedItems = result - - return nColl -} - func WriteObjectReplyToCache(db *sql.DB, obj ObjectBase) { for i, e := range obj.InReplyTo { @@ -422,146 +239,13 @@ func WriteObjectReplyCache(db *sql.DB, obj ObjectBase) { } } -func GetObjectRepliesCache(db *sql.DB, parent ObjectBase) (*CollectionBase, int, int) { - - var nColl CollectionBase - var result []ObjectBase - - query := `select id, name, content, type, published, attributedto, attachment, preview, actor from cacheactivitystream WHERE id in (select id from replies where inreplyto=$1) and type='Note' order by published asc` - - rows, err := db.Query(query, parent.Id) - - CheckError(err, "error with replies db query") - - defer rows.Close() - for rows.Next() { - var post ObjectBase - var actor Actor - var attachID string - var previewID string - - post.InReplyTo = append(post.InReplyTo, parent) - - err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.AttributedTo, &attachID, &previewID, &actor.Id) - - CheckError(err, "error with replies db scan") - - post.Actor = &actor - - // var postCnt int - // var imgCnt int - post.Replies, _, _ = GetObjectRepliesRepliesCache(db, post) - - // post.Replies.TotalItems, post.Replies.TotalImgs = GetObjectRepliesCacheCount(db, post) - - // post.Replies.TotalItems = post.Replies.TotalItems + postCnt - // post.Replies.TotalImgs = post.Replies.TotalImgs + imgCnt - - post.Attachment = GetObjectAttachmentCache(db, attachID) - - post.Preview = GetObjectPreviewCache(db, previewID) - - result = append(result, post) - } - - nColl.OrderedItems = result - - return &nColl, 0, 0 -} - -func GetObjectRepliesRepliesCache(db *sql.DB, parent ObjectBase) (*CollectionBase, int, int) { - - var nColl CollectionBase - var result []ObjectBase - - query := `select id, name, content, type, published, attributedto, attachment, preview, actor from cacheactivitystream where id in (select id from replies where inreplyto=$1) and type='Note' order by published asc` - - rows, err := db.Query(query, parent.Id) - - CheckError(err, "error with replies replies cache query") - - defer rows.Close() - for rows.Next() { - var post ObjectBase - var actor Actor - var attachID string - var previewID string - - post.InReplyTo = append(post.InReplyTo, parent) - - err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.AttributedTo, &attachID, &previewID, &actor.Id) - - CheckError(err, "error with replies replies cache scan") - - post.Actor = &actor - - post.Attachment = GetObjectAttachmentCache(db, attachID) - - post.Preview = GetObjectPreviewCache(db, previewID) - - result = append(result, post) - } - - nColl.OrderedItems = result - - remoteCollection, postc, imgc := GetObjectRepliesReplies(db, parent) - - for _, e := range remoteCollection.OrderedItems { - - nColl.OrderedItems = append(nColl.OrderedItems, e) - postc = postc + 1 - if len(e.Attachment) > 0 { - imgc = imgc + 1 - } - } - - sort.Sort(ObjectBaseSortAsc(nColl.OrderedItems)) - - return &nColl, 0, 0 -} - -func GetObjectAttachmentCache(db *sql.DB, id string) []ObjectBase { - - var attachments []ObjectBase - - query := `select id, type, name, href, mediatype, size, published from cacheactivitystream where id=$1` - - rows, err := db.Query(query, id) - - CheckError(err, "could not select object attachment query") - - defer rows.Close() - for rows.Next() { - var attachment = new(ObjectBase) - - err = rows.Scan(&attachment.Id, &attachment.Type, &attachment.Name, &attachment.Href, &attachment.MediaType, &attachment.Size, &attachment.Published) - if err !=nil{ - fmt.Println("error with attachment db query") - panic(err) - } - - attachments = append(attachments, *attachment) - } - - return attachments -} - -func GetObjectPreviewCache(db *sql.DB, id string) *NestedObjectBase { - - var preview NestedObjectBase - - query := `select id, type, name, href, mediatype, size, published from cacheactivitystream where id=$1` - - rows, err := db.Query(query, id) - - CheckError(err, "could not select object preview query") +func WriteActorToCache(db *sql.DB, actorID string) { + actor := GetActor(actorID) + collection := GetActorCollection(actor.Outbox) - defer rows.Close() - for rows.Next() { - err = rows.Scan(&preview.Id, &preview.Type, &preview.Name, &preview.Href, &preview.MediaType, &preview.Size, &preview.Published) + for _, e := range collection.OrderedItems { + WriteObjectToCache(db, e) } - - return &preview } func DeleteObjectFromCache(db *sql.DB, id string) { @@ -594,6 +278,23 @@ func DeleteObjectFromCache(db *sql.DB, id string) { CheckError(err, "could not delete cache replies activitystream") } +func DeleteActorCache(db *sql.DB, actorID string) { + query := `select id from cacheactivitystream where id in (select id from cacheactivitystream where actor=$1)` + + rows, err := db.Query(query, actorID) + + CheckError(err, "error selecting actors activity from cache") + + defer rows.Close() + + for rows.Next() { + var id string + rows.Scan(&id) + + DeleteObjectFromCache(db, id) + } +} + func TombstoneObjectFromCache(db *sql.DB, id string) { datetime := time.Now().Format(time.RFC3339) @@ -621,68 +322,3 @@ func TombstoneObjectFromCache(db *sql.DB, id string) { CheckError(err, "could not delete cache replies activitystream") } - -func GetObjectPostsTotalCache(db *sql.DB, actor Actor) int{ - - count := 0 - query := `select count(id) from cacheactivitystream where actor=$1 and id in (select id from replies where inreplyto='' and type='Note')` - - rows, err := db.Query(query, actor.Id) - - CheckError(err, "could not select post total count query") - - defer rows.Close() - for rows.Next() { - err = rows.Scan(&count) - CheckError(err, "error with total post db scan") - } - - return count -} - -func GetObjectImgsTotalCache(db *sql.DB, actor Actor) int{ - - count := 0 - query := `select count(attachment) from cacheactivitystream where actor=$1 and id in (select id from replies where inreplyto='' and type='Note' )` - - rows, err := db.Query(query, actor.Id) - - CheckError(err, "error with posts total db query") - - defer rows.Close() - for rows.Next() { - err = rows.Scan(&count) - - CheckError(err, "error with total post db scan") - } - - return count -} - -func DeleteActorCache(db *sql.DB, actorID string) { - query := `select id from cacheactivitystream where id in (select id from cacheactivitystream where actor=$1)` - - rows, err := db.Query(query, actorID) - - - - CheckError(err, "error selecting actors activity from cache") - - defer rows.Close() - - for rows.Next() { - var id string - rows.Scan(&id) - - DeleteObjectFromCache(db, id) - } -} - -func WriteActorToCache(db *sql.DB, actorID string) { - actor := GetActor(actorID) - collection := GetActorCollection(actor.Outbox) - - for _, e := range collection.OrderedItems { - WriteObjectToCache(db, e) - } -} -- cgit v1.2.3