// ReadMany handles GET for the collection func (ctl *TrendingController) ReadMany(c *models.Context) { limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } trending, total, pages, status, err := models.GetTrending(c.Site.ID, c.Auth.ProfileID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } response := models.TrendingItems{} response.Items = h.ConstructArray( trending, "items", total, limit, offset, pages, c.Request.URL, ) thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) response.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } c.RespondWithData(response) }
// GetItems fetches items for a microcosm func GetItems( siteID int64, microcosmID int64, profileID int64, reqURL *url.URL, ) ( h.ArrayType, int, error, ) { query := reqURL.Query() limit, offset, status, err := h.GetLimitAndOffset(query) if err != nil { return h.ArrayType{}, status, err } ems, total, pages, status, err := GetAllItems(siteID, microcosmID, profileID, limit, offset) if err != nil { return h.ArrayType{}, status, err } m := h.ConstructArray( ems, h.APITypeComment, total, limit, offset, pages, reqURL, ) return m, http.StatusOK, nil }
// ReadMany handles GET // If microcosm_id is provided in request args then these are the roles for this // microcosm, otherwise this is a list of the default roles on this site func (ctl *RolesController) ReadMany(c *models.Context) { var microcosmID int64 if sid, exists := c.RouteVars["microcosm_id"]; exists { id, err := strconv.ParseInt(sid, 10, 64) if err != nil { c.RespondWithErrorMessage("microcosm_id in URL is not a number", http.StatusBadRequest) return } microcosmID = id } perms := models.GetPermission( models.MakeAuthorisationContext(c, 0, h.ItemTypes[h.ItemTypeMicrocosm], microcosmID), ) if microcosmID > 0 { // Related to a Microcosm if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } } else { // Default role for the site if !c.Auth.IsSiteOwner { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } } // Fetch query string args if any exist limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetRoles(c.Site.ID, microcosmID, c.Auth.ProfileID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response m := models.RolesType{} m.Roles = h.ConstructArray( ems, h.APITypeRole, total, limit, offset, pages, c.Request.URL, ) c.RespondWithData(m) }
// ReadMany handles GET func (ctl *EventsController) ReadMany(c *models.Context) { // Start Authorisation perms := models.GetPermission( models.MakeAuthorisationContext( c, 0, h.ItemTypes[h.ItemTypeEvent], 0), ) if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } // End Authorisation // Fetch query string args if any exist query := c.Request.URL.Query() limit, offset, status, err := h.GetLimitAndOffset(query) if err != nil { c.RespondWithErrorDetail(err, status) return } attending, status, err := h.GetAttending(query) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetEvents(c.Site.ID, c.Auth.ProfileID, attending, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.EventsType{} m.Events = h.ConstructArray( ems, h.APITypeEvent, total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } m.Meta.Permissions = perms c.RespondWithData(m) }
// ReadMany handles GET for the collection func (ctl *HuddleParticipantsController) ReadMany(c *models.Context) { // Validate inputs huddleID, err := strconv.ParseInt(c.RouteVars["huddle_id"], 10, 64) if err != nil { c.RespondWithErrorMessage("huddle_id in URL is not a number", http.StatusBadRequest) return } r, status, err := models.GetHuddle(c.Site.ID, c.Auth.ProfileID, huddleID) if err != nil { c.RespondWithErrorDetail(err, status) return } // Start Authorisation perms := models.GetPermission( models.MakeAuthorisationContext( c, 0, h.ItemTypes[h.ItemTypeHuddle], huddleID), ) if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } // End Authorisation limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetHuddleParticipants(c.Site.ID, huddleID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response m := models.HuddleParticipantsType{} m.HuddleParticipants = h.ConstructArray( ems, fmt.Sprintf("%s/participants", r.GetLink()), total, limit, offset, pages, c.Request.URL, ) c.RespondWithData(m) }
// ReadMany handles GET for the collection func (ctl *AttachmentsController) ReadMany(c *models.Context) { itemTypeID, itemID, perms, status, err := ParseItemInfo(c) if err != nil { c.RespondWithErrorDetail(err, status) return } if !perms.CanRead { c.RespondWithErrorMessage( h.NoAuthMessage, http.StatusForbidden, ) return } query := c.Request.URL.Query() limit, offset, status, err := h.GetLimitAndOffset(query) if err != nil { c.RespondWithErrorDetail(err, status) return } attachments, total, pages, status, err := models.GetAttachments(itemTypeID, itemID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.AttachmentsType{} m.Attachments = h.ConstructArray( attachments, h.APITypeAttachment, total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } m.Meta.Permissions = perms c.RespondWithData(m) }
// Read handles GET func (ctl *CommentContextController) Read(c *models.Context) { _, itemTypeID, itemID, status, err := c.GetItemTypeAndItemID() if err != nil { c.RespondWithErrorDetail(err, status) return } // Start Authorisation perms := models.GetPermission( models.MakeAuthorisationContext( c, 0, itemTypeID, itemID), ) if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } // End Authorisation limit, _, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } m, status, err := models.GetCommentSummary(c.Site.ID, itemID) if err != nil { c.RespondWithErrorDetail(err, status) return } link, status, err := m.GetPageLink(limit, c.Auth.ProfileID) if err != nil { c.RespondWithErrorDetail(err, status) return } pageURL, err := url.Parse(link.Href) if err != nil { c.RespondWithErrorMessage(err.Error(), http.StatusInternalServerError) return } queryString := pageURL.Query() queryString.Add("comment_id", strconv.FormatInt(m.ID, 10)) pageURL.RawQuery = queryString.Encode() c.RespondWithLocation(pageURL.String()) }
// ReadMany handles GET for a collection func (ctl *SitesController) ReadMany(c *models.Context) { limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } var userID int64 // Passing ?filter=owned returns sites owned by logged-in user. if c.Request.FormValue("filter") == "owned" { if c.Auth.UserID == 0 { c.RespondWithErrorMessage( fmt.Sprintf("You must be logged in to list your own sites"), http.StatusForbidden, ) return } userID = c.Auth.UserID } // Passing 0 as userID means return all sites. ems, total, pages, status, err := models.GetSites(userID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.SitesType{} m.Sites = h.ConstructArray( ems, h.APITypeSite, total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } c.RespondWithData(m) }
// ReadMany handles GET for the collection func (ctl *WatchersController) ReadMany(c *models.Context) { if c.Auth.ProfileID < 1 { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } query := c.Request.URL.Query() limit, offset, status, err := h.GetLimitAndOffset(query) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetProfileWatchers( c.Auth.ProfileID, c.Site.ID, limit, offset, ) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.WatchersType{} m.Watchers = h.ConstructArray( ems, h.APITypeWatcher, total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } c.RespondWithData(m) }
// ReadMany handles GET func (ctl *IgnoredController) ReadMany(c *models.Context) { if c.Auth.ProfileID < 1 { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetIgnored( c.Site.ID, c.Auth.ProfileID, limit, offset, ) if err != nil { c.RespondWithErrorDetail(err, status) return } thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.IgnoredType{} m.Ignored = h.ConstructArray( ems, `/api/v1/ignored`, total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{ Rel: "self", Href: thisLink.String(), }, } c.RespondWithData(m) }
// ReadMany handles GET for a collection func (ctl *AttributesController) ReadMany(c *models.Context) { _, itemTypeID, itemID, status, err := c.GetItemTypeAndItemID() if err != nil { c.RespondWithErrorDetail(err, status) return } perms := models.GetPermission(models.MakeAuthorisationContext(c, 0, itemTypeID, itemID)) if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } // Fetch query string args if any exist limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetAttributes(itemTypeID, itemID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response m := models.AttributesType{} m.Attributes = h.ConstructArray( ems, fmt.Sprintf(h.APITypeAttribute, c.RouteVars["type"], 0), total, limit, offset, pages, c.Request.URL, ) c.RespondWithData(m) }
// ReadMany handles GET func (ctl *MicrocosmsController) ReadMany(c *models.Context) { perms := models.GetPermission( models.MakeAuthorisationContext(c, 0, h.ItemTypes[h.ItemTypeSite], c.Site.ID), ) // Fetch query string args if any exist limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } // Fetch list of microcosms ems, total, pages, status, err := models.GetMicrocosms(c.Site.ID, c.Auth.ProfileID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.MicrocosmsType{} m.Microcosms = h.ConstructArray( ems, h.APITypeMicrocosm, total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } m.Meta.Permissions = perms c.RespondWithData(m) }
// Read handles GET func (ctl *CommentController) Read(c *models.Context) { _, itemTypeID, itemID, status, err := c.GetItemTypeAndItemID() if err != nil { c.RespondWithErrorDetail(err, status) return } // Start Authorisation perms := models.GetPermission( models.MakeAuthorisationContext( c, 0, itemTypeID, itemID), ) if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } // End Authorisation limit, _, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } m, status, err := models.GetComment(c.Site.ID, itemID, c.Auth.ProfileID, limit) if err != nil { c.RespondWithErrorDetail(err, status) return } m.Meta.Permissions = perms if c.Auth.ProfileID > 0 { go models.MarkAsRead(m.ItemTypeID, m.ItemID, c.Auth.ProfileID, m.Meta.Created) } c.RespondWithData(m) }
// ReadMany handles GET for the collection func (ctl *HuddlesController) ReadMany(c *models.Context) { // NOTE: Auth check skipped, permissions are enforced by limiting the scope // of the underlying queries in the model to only show huddles you are a // participant in // Fetch query string args if any exist limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetHuddles(c.Site.ID, c.Auth.ProfileID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.HuddlesType{} m.Huddles = h.ConstructArray( ems, h.APITypeHuddle, total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } c.RespondWithData(m) }
// ReadMany handles GET for the collection // Returns an array of all members that are included in this role by either // implicit (role criteria) or explicit (role profiles) inclusion func (ctl *RoleMembersController) ReadMany(c *models.Context) { // Validate inputs var microcosmID int64 if sid, exists := c.RouteVars["microcosm_id"]; exists { id, err := strconv.ParseInt(sid, 10, 64) if err != nil { c.RespondWithErrorMessage("microcosm_id in URL is not a number", http.StatusBadRequest) return } microcosmID = id } roleID, err := strconv.ParseInt(c.RouteVars["role_id"], 10, 64) if err != nil { c.RespondWithErrorMessage("microcosm_id in URL is not a number", http.StatusBadRequest) return } r, status, err := models.GetRole(c.Site.ID, microcosmID, roleID, c.Auth.ProfileID) if err != nil { c.RespondWithErrorDetail(err, status) return } // Authorisation perms := models.GetPermission( models.MakeAuthorisationContext(c, microcosmID, h.ItemTypes[h.ItemTypeMicrocosm], microcosmID), ) if microcosmID > 0 { // Related to a Microcosm if !perms.IsModerator && !c.Auth.IsSiteOwner { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } } else { // Default role for the site if !c.Auth.IsSiteOwner { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } } limit, offset, status, err := h.GetLimitAndOffset(c.Request.URL.Query()) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetRoleProfiles(c.Site.ID, roleID, limit, offset) if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response m := models.RoleProfilesType{} m.RoleProfiles = h.ConstructArray( ems, fmt.Sprintf("%s/profiles", r.GetLink()), total, limit, offset, pages, c.Request.URL, ) c.RespondWithData(m) }
// ReadMany handles GET for the collection func (ctl *AttendeesController) ReadMany(c *models.Context) { eventID, err := strconv.ParseInt(c.RouteVars["event_id"], 10, 64) if err != nil { c.RespondWithErrorMessage( fmt.Sprintf("The supplied event_id ('%s') is not a number.", c.RouteVars["event_id"]), http.StatusBadRequest, ) return } // Start Authorisation perms := models.GetPermission( models.MakeAuthorisationContext( c, 0, h.ItemTypes[h.ItemTypeEvent], eventID), ) if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } // End Authorisation // Fetch query string args if any exist query := c.Request.URL.Query() limit, offset, status, err := h.GetLimitAndOffset(query) if err != nil { c.RespondWithErrorDetail(err, status) return } attending, status, err := h.AttendanceStatus(query) if err != nil { c.RespondWithErrorDetail(err, status) return } ems, total, pages, status, err := models.GetAttendees(c.Site.ID, eventID, limit, offset, attending == "attending") if err != nil { c.RespondWithErrorDetail(err, status) return } // Construct the response thisLink := h.GetLinkToThisPage(*c.Request.URL, offset, limit, total) m := models.AttendeesType{} m.Attendees = h.ConstructArray( ems, fmt.Sprintf(h.APITypeAttendee, 0), total, limit, offset, pages, c.Request.URL, ) m.Meta.Links = []h.LinkType{ h.LinkType{Rel: "self", Href: thisLink.String()}, } m.Meta.Permissions = perms c.RespondWithData(m) }
func searchMetaData( siteID int64, searchURL url.URL, profileID int64, m SearchResults, ) ( SearchResults, int, error, ) { limit, offset, status, err := h.GetLimitAndOffset(searchURL.Query()) if err != nil { glog.Errorf("h.GetLimitAndOffset(searchUrl.Query()) %+v", err) return m, status, err } start := time.Now() // The goal is to produce a piece of SQL that looks at just the flags table // and fetches a list of the items that we care about. // // Our target SQL should look roughly like this (fetches all viewable comments): // // WITH m AS ( // SELECT microcosm_id // FROM microcosms // WHERE site_id = 2 // AND (get_effective_permissions(2,microcosm_id,2,microcosm_id,7)).can_read IS TRUE // ), h AS ( // SELECT huddle_id // FROM huddle_profiles // WHERE profile_id = 7 // ) // SELECT item_type_id // ,item_id // FROM flags // WHERE item_type_id = 4 // AND ( // site_is_deleted // AND microcosm_is_deleted // AND parent_is_deleted // AND item_is_deleted // ) IS NOT TRUE // AND ( // (-- Things that are public by default and low in quantity // item_type_id IN (1,3) // OR parent_item_type_id IN (3) // ) // OR (-- Things directly in microcosms // item_type_id IN (2,6,7,9) // AND COALESCE(microcosm_id, item_id) IN (SELECT microcosm_id FROM m) // ) // OR (-- Comments on things in microcosms // item_type_id = 4 // AND parent_item_type_id IN (6,7,9) // AND microcosm_id IN (SELECT microcosm_id FROM m) // ) // OR (-- Huddles // item_type_id = 5 // AND item_id IN (SELECT huddle_id FROM h) // ) // OR (-- Comments on things in huddles // item_type_id = 4 // AND parent_item_type_id = 5 // AND parent_item_id IN (SELECT huddle_id FROM h) // ) // ) // ORDER BY last_modified DESC // LIMIT 25; // // The key is to only put into the query the bits that will definitely be // used. // Process search options var filterFollowing string var filterItemTypes string var filterItems string var includeHuddles bool var includeComments bool var joinEvents bool orderBy := `rank DESC ,f.last_modified DESC` switch m.Query.Sort { case "date": orderBy = `f.last_modified DESC` case "oldest": joinEvents = true orderBy = `e."when" ASC` case "newest": joinEvents = true orderBy = `e."when" DESC` } if m.Query.Following { filterFollowing = ` JOIN watchers w ON w.item_type_id = f.item_type_id AND w.item_id = f.item_id AND w.profile_id = $2` } if len(m.Query.ItemTypeIDs) > 0 { var inList string // Take care of the item types for i, v := range m.Query.ItemTypeIDs { switch v { case h.ItemTypes[h.ItemTypeComment]: includeComments = true case h.ItemTypes[h.ItemTypeHuddle]: includeHuddles = true } inList += strconv.FormatInt(v, 10) if i < len(m.Query.ItemTypeIDs)-1 { inList += `,` } } if len(m.Query.ItemTypeIDs) == 1 { filterItemTypes = fmt.Sprintf(` AND f.item_type_id = %d`, m.Query.ItemTypeIDs[0], ) } else { if includeComments { filterItemTypes = ` AND ( (f.item_type_id <> 4 AND f.item_type_id IN (` + inList + `)) OR (f.item_type_id = 4 AND f.parent_item_type_id IN (` + inList + `)) )` } else { filterItemTypes = ` AND f.item_type_id IN (` + inList + `)` } } // Take care of the item ids, which are only valid when we have item // types if len(m.Query.ItemIDs) > 0 { if len(m.Query.ItemIDs) == 1 { if includeComments { filterItems = fmt.Sprintf(` AND ( (f.item_type_id <> 4 AND f.item_id = %d) OR (f.item_type_id = 4 AND f.parent_item_id = %d) )`, m.Query.ItemIDs[0], m.Query.ItemIDs[0], ) } else { filterItems = fmt.Sprintf(` AND f.item_id = %d`, m.Query.ItemIDs[0], ) } } else { var inList = `` for i, v := range m.Query.ItemIDs { inList += strconv.FormatInt(v, 10) if i < len(m.Query.ItemIDs)-1 { inList += `,` } } if includeComments { filterItems = ` AND ( (f.item_type_id <> 4 AND f.item_id IN (` + inList + `)) OR (f.item_type_id = 4 AND f.parent_item_id IN (` + inList + `)) )` } else { filterItems = ` AND f.item_id IN (` + inList + `)` } } } } var filterProfileID string if m.Query.ProfileID > 0 { filterProfileID = fmt.Sprintf(` AND f.created_by = %d`, m.Query.ProfileID) } var filterMicrocosmIDs string if len(m.Query.MicrocosmIDs) > 0 { if len(m.Query.MicrocosmIDs) == 1 { filterMicrocosmIDs = fmt.Sprintf(` AND f.microcosm_id = %d`, m.Query.MicrocosmIDs[0]) includeHuddles = false } else { var inList = `` for i, v := range m.Query.MicrocosmIDs { inList += strconv.FormatInt(v, 10) if i < len(m.Query.MicrocosmIDs)-1 { inList += `,` } } filterMicrocosmIDs = ` AND f.microcosm_id IN (` + inList + `)` } } var filterModified string if !m.Query.SinceTime.IsZero() || !m.Query.UntilTime.IsZero() { if m.Query.UntilTime.IsZero() { filterModified = fmt.Sprintf(` AND f.last_modified > to_timestamp(%d)`, m.Query.SinceTime.Unix(), ) } else if m.Query.SinceTime.IsZero() { filterModified = fmt.Sprintf(` AND f.last_modified < to_timestamp(%d)`, m.Query.UntilTime.Unix(), ) } else { filterModified = fmt.Sprintf(` AND f.last_modified BETWEEN to_timestamp(%d) AND to_timestamp(%d)`, m.Query.SinceTime.Unix(), m.Query.UntilTime.Unix(), ) } } var ( filterEventsJoin string filterEventsWhere string ) if !m.Query.EventAfterTime.IsZero() || !m.Query.EventBeforeTime.IsZero() { joinEvents = true if m.Query.EventBeforeTime.IsZero() { filterModified = fmt.Sprintf(` AND e."when" > to_timestamp(%d)`, m.Query.EventAfterTime.Unix(), ) } else if m.Query.EventAfterTime.IsZero() { filterModified = fmt.Sprintf(` AND e."when" < to_timestamp(%d)`, m.Query.EventBeforeTime.Unix(), ) } else { filterModified = fmt.Sprintf(` AND e."when" BETWEEN to_timestamp(%d) AND to_timestamp(%d)`, m.Query.EventAfterTime.Unix(), m.Query.EventBeforeTime.Unix(), ) } } if joinEvents || m.Query.Attendee { filterEventsJoin = ` JOIN events e ON e.event_id = f.item_id` if m.Query.Attendee { filterEventsJoin += ` JOIN attendees a ON a.event_id = e.event_id AND a.profile_id = ` + strconv.FormatInt(profileID, 10) + ` AND a.state_id = 1` } } // These make up our SQL query sqlSelect := ` SELECT 0,0,0,NULL,NULL,NOW(),0,''` sqlFromWhere := ` FROM flags WHERE 1=2` // Query with only meta data sqlWith := ` WITH m AS ( SELECT m.microcosm_id FROM microcosms m LEFT JOIN permissions_cache p ON p.site_id = m.site_id AND p.item_type_id = 2 AND p.item_id = m.microcosm_id AND p.profile_id = $2 LEFT JOIN ignores i ON i.profile_id = $2 AND i.item_type_id = 2 AND i.item_id = m.microcosm_id WHERE m.site_id = $1 AND m.is_deleted IS NOT TRUE AND m.is_moderated IS NOT TRUE AND i.profile_id IS NULL AND ( (p.can_read IS NOT NULL AND p.can_read IS TRUE) OR (get_effective_permissions($1,m.microcosm_id,2,m.microcosm_id,$2)).can_read IS TRUE ) )` if includeHuddles || includeComments { if filterModified != "" { sqlWith += `, h AS ( SELECT hp.huddle_id FROM huddle_profiles hp JOIN flags f ON f.item_type_id = 5 AND f.item_id = hp.huddle_id WHERE hp.profile_id = $2` + filterModified + ` )` } else { sqlWith += `, h AS ( SELECT huddle_id FROM huddle_profiles WHERE profile_id = $2 )` } } sqlSelect = ` SELECT f.item_type_id ,f.item_id ,f.parent_item_type_id ,f.parent_item_id ,f.last_modified ,0.5 AS rank ,'' AS highlight` sqlFromWhere = ` FROM flags f LEFT JOIN ignores i ON i.profile_id = $2 AND i.item_type_id = f.item_type_id AND i.item_id = f.item_id` + filterFollowing + filterEventsJoin + ` WHERE f.site_id = $1 AND i.profile_id IS NULL` + filterModified + filterMicrocosmIDs + filterItemTypes + filterItems + filterProfileID + filterEventsWhere + ` AND f.microcosm_is_deleted IS NOT TRUE AND f.microcosm_is_moderated IS NOT TRUE AND f.parent_is_deleted IS NOT TRUE AND f.parent_is_moderated IS NOT TRUE AND f.item_is_deleted IS NOT TRUE AND f.item_is_moderated IS NOT TRUE AND ( (-- Things that are public by default and low in quantity f.item_type_id IN (1,3) OR f.parent_item_type_id IN (3) ) OR (-- Things directly in microcosms f.item_type_id IN (2,6,7,9) AND COALESCE(f.microcosm_id, f.item_id) IN (SELECT microcosm_id FROM m) )` if includeComments { sqlFromWhere += ` OR (-- Comments on things in microcosms f.item_type_id = 4 AND f.parent_item_type_id IN (6,7,9) AND f.microcosm_id IN (SELECT microcosm_id FROM m) ) OR (-- Comments on things in huddles f.item_type_id = 4 AND f.parent_item_type_id = 5 AND f.parent_item_id IN (SELECT huddle_id FROM h) )` } if includeHuddles { sqlFromWhere += ` OR (-- Huddles f.item_type_id = 5 AND f.item_id IN (SELECT huddle_id FROM h) )` } sqlFromWhere += ` )` sqlOrderLimit := ` ORDER BY ` + orderBy + ` LIMIT $3 OFFSET $4` db, err := h.GetConnection() if err != nil { glog.Errorf("h.GetConnection() %+v", err) return m, http.StatusInternalServerError, err } var total int64 err = db.QueryRow( sqlWith+`SELECT COUNT(*)`+sqlFromWhere, siteID, profileID, ).Scan(&total) if err != nil { glog.Error(err) return m, http.StatusInternalServerError, err } // This nested query is used to run the `has_unread` query on only the rows // that are returned, rather than on all rows in the underlying query before // limit has been applied. rows, err := db.Query(` SELECT item_type_id ,item_id ,parent_item_type_id ,parent_item_id ,last_modified ,rank ,highlight ,has_unread(item_type_id, item_id, $2) FROM (`+ sqlWith+ sqlSelect+ sqlFromWhere+ sqlOrderLimit+ `) r`, siteID, profileID, limit, offset, ) if err != nil { glog.Errorf( "stmt.Query(%d, %s, %d, %d, %d) %+v", siteID, m.Query.Query, profileID, limit, offset, err, ) return m, http.StatusInternalServerError, fmt.Errorf("Database query failed") } defer rows.Close() rs := []SearchResult{} for rows.Next() { var r SearchResult err = rows.Scan( &r.ItemTypeID, &r.ItemID, &r.ParentItemTypeID, &r.ParentItemID, &r.LastModified, &r.Rank, &r.Highlight, &r.Unread, ) if err != nil { glog.Errorf("rows.Scan() %+v", err) return m, http.StatusInternalServerError, fmt.Errorf("Row parsing error") } itemType, err := h.GetMapStringFromInt(h.ItemTypes, r.ItemTypeID) if err != nil { glog.Errorf( "h.GetMapStringFromInt(h.ItemTypes, %d) %+v", r.ItemTypeID, err, ) return m, http.StatusInternalServerError, err } r.ItemType = itemType if r.ParentItemTypeID.Valid { parentItemType, err := h.GetMapStringFromInt(h.ItemTypes, r.ParentItemTypeID.Int64) if err != nil { glog.Errorf( "h.GetMapStringFromInt(h.ItemTypes, %d) %+v", r.ParentItemTypeID.Int64, err, ) return m, http.StatusInternalServerError, err } r.ParentItemType = parentItemType } rs = append(rs, r) } err = rows.Err() if err != nil { glog.Errorf("rows.Err() %+v", err) return m, http.StatusInternalServerError, fmt.Errorf("Error fetching rows") } rows.Close() pages := h.GetPageCount(total, limit) maxOffset := h.GetMaxOffset(total, limit) if offset > maxOffset { glog.Infoln("offset > maxOffset") return m, http.StatusBadRequest, fmt.Errorf("not enough records, "+ "offset (%d) would return an empty page.", offset) } // Extract the summaries var wg1 sync.WaitGroup req := make(chan SummaryContainerRequest) defer close(req) seq := 0 for i := 0; i < len(rs); i++ { go HandleSummaryContainerRequest( siteID, rs[i].ItemTypeID, rs[i].ItemID, profileID, seq, req, ) seq++ wg1.Add(1) if rs[i].ParentItemID.Valid && rs[i].ParentItemID.Int64 > 0 { go HandleSummaryContainerRequest( siteID, rs[i].ParentItemTypeID.Int64, rs[i].ParentItemID.Int64, profileID, seq, req, ) seq++ wg1.Add(1) } } resps := []SummaryContainerRequest{} for i := 0; i < seq; i++ { resp := <-req wg1.Done() resps = append(resps, resp) } wg1.Wait() for _, resp := range resps { if resp.Err != nil { return m, resp.Status, resp.Err } } sort.Sort(SummaryContainerRequestsBySeq(resps)) seq = 0 for i := 0; i < len(rs); i++ { rs[i].Item = resps[seq].Item.Summary seq++ if rs[i].ParentItemID.Valid && rs[i].ParentItemID.Int64 > 0 { rs[i].ParentItem = resps[seq].Item.Summary seq++ } } m.Results = h.ConstructArray( rs, "result", total, limit, offset, pages, &searchURL, ) // return milliseconds m.TimeTaken = time.Now().Sub(start).Nanoseconds() / 1000000 return m, http.StatusOK, nil }
func searchFullText( siteID int64, searchURL url.URL, profileID int64, m SearchResults, ) ( SearchResults, int, error, ) { limit, offset, status, err := h.GetLimitAndOffset(searchURL.Query()) if err != nil { glog.Errorf("h.GetLimitAndOffset(searchURL.Query()) %+v", err) return m, status, err } start := time.Now() // Search options var joinEvents bool orderBy := `rank DESC ,f.last_modified DESC` switch m.Query.Sort { case "date": orderBy = `f.last_modified DESC` case "oldest": joinEvents = true orderBy = `e."when" ASC` case "newest": joinEvents = true orderBy = `e."when" DESC` } var filterFollowing string if m.Query.Following { filterFollowing = ` JOIN watchers w ON w.item_type_id = f.item_type_id AND w.item_id = f.item_id AND w.profile_id = $2` } fullTextScope := `document` var filterTitle string if m.Query.InTitle { fullTextScope = `title` filterTitle = ` AND f.item_type_id <> 4` } var filterItemTypes string var filterItems string var includeComments bool if !m.Query.InTitle { includeComments = true } if len(m.Query.ItemTypeIDs) > 0 { var itemTypeInList []string var itemTypeSansCommentsInList []string // Take care of the item types for _, v := range m.Query.ItemTypeIDs { switch v { case h.ItemTypes[h.ItemTypeComment]: includeComments = true itemTypeInList = append(itemTypeInList, strconv.FormatInt(v, 10)) default: itemTypeInList = append(itemTypeInList, strconv.FormatInt(v, 10)) itemTypeSansCommentsInList = append(itemTypeSansCommentsInList, strconv.FormatInt(v, 10)) } } if len(m.Query.ItemIDs) == 0 { if len(m.Query.ItemTypeIDs) == 1 { filterItemTypes = fmt.Sprintf(` AND f.item_type_id = %d`, m.Query.ItemTypeIDs[0], ) } else { if includeComments { filterItemTypes = ` AND ( (f.item_type_id IN (` + strings.Join(itemTypeSansCommentsInList, `,`) + `)) OR (f.item_type_id = 4 AND f.parent_item_type_id IN (` + strings.Join(itemTypeSansCommentsInList, `,`) + `)) )` } else { filterItemTypes = ` AND f.item_type_id IN (` + strings.Join(itemTypeInList, `,`) + `)` } } } // Take care of the item ids, which are only valid when we have item // types if len(m.Query.ItemIDs) > 0 { var itemIdsInList []string for _, v := range m.Query.ItemIDs { itemIdsInList = append(itemIdsInList, strconv.FormatInt(v, 10)) } if len(m.Query.ItemIDs) == 1 { if includeComments { filterItems = fmt.Sprintf(` AND ( (si.item_type_id IN (`+strings.Join(itemTypeSansCommentsInList, `,`)+`) AND si.item_id = %d) OR (si.item_type_id = 4 AND si.parent_item_id = %d AND si.parent_item_type_id IN (`+strings.Join(itemTypeSansCommentsInList, `,`)+`)) )`, m.Query.ItemIDs[0], m.Query.ItemIDs[0], ) } else { filterItems = fmt.Sprintf(` AND si.item_id = %d`, m.Query.ItemIDs[0], ) } } else { if includeComments { filterItems = ` AND ( (si.item_type_id IN (` + strings.Join(itemTypeSansCommentsInList, `,`) + `) AND si.item_id IN (` + strings.Join(itemIdsInList, `,`) + `)) OR (si.item_type_id = 4 AND si.parent_item_type_id IN (` + strings.Join(itemTypeSansCommentsInList, `,`) + `) AND si.parent_item_id IN (` + strings.Join(itemIdsInList, `,`) + `)) )` } else { filterItems = ` AND si.item_type_id IN (` + strings.Join(itemTypeInList, `,`) + `) AND si.item_id IN (` + strings.Join(itemIdsInList, `,`) + `)` } } } } // Note: hashtags being inserted into the query this way may appear // initially to be a vector for a SQL injection attack. However the // source of these hashtags is a regexp in hashtags.go which only // matches contiguous alphanum strings and does not permit spaces, // quotes, semicolons or any other escapable sequence that can be // utilised to create an attack. var filterHashTag string for _, hashtag := range m.Query.Hashtags { filterHashTag += ` AND si.` + fullTextScope + `_text ~* '\W` + hashtag + `\W'` } var filterProfileID string if m.Query.ProfileID > 0 { filterProfileID = fmt.Sprintf(` AND si.profile_id = %d`, m.Query.ProfileID) } var filterMicrocosmIDs string if len(m.Query.MicrocosmIDs) > 0 { if len(m.Query.MicrocosmIDs) == 1 { filterMicrocosmIDs = fmt.Sprintf(` AND f.microcosm_id = %d`, m.Query.MicrocosmIDs[0]) } else { var inList = `` for i, v := range m.Query.MicrocosmIDs { inList += strconv.FormatInt(v, 10) if i < len(m.Query.MicrocosmIDs)-1 { inList += `,` } } filterMicrocosmIDs = ` AND f.microcosm_id IN (` + inList + `)` } } var filterModified string if !m.Query.SinceTime.IsZero() || !m.Query.UntilTime.IsZero() { if m.Query.UntilTime.IsZero() { filterModified = fmt.Sprintf(` AND f.last_modified > to_timestamp(%d)`, m.Query.SinceTime.Unix(), ) } else if m.Query.SinceTime.IsZero() { filterModified = fmt.Sprintf(` AND f.last_modified < to_timestamp(%d)`, m.Query.UntilTime.Unix(), ) } else { filterModified = fmt.Sprintf(` AND f.last_modified BETWEEN to_timestamp(%d) AND to_timestamp(%d)`, m.Query.SinceTime.Unix(), m.Query.UntilTime.Unix(), ) } } var ( filterEventsJoin string filterEventsWhere string ) if !m.Query.EventAfterTime.IsZero() || !m.Query.EventBeforeTime.IsZero() { joinEvents = true if m.Query.EventBeforeTime.IsZero() { filterModified = fmt.Sprintf(` AND e."when" > to_timestamp(%d)`, m.Query.EventAfterTime.Unix(), ) } else if m.Query.EventAfterTime.IsZero() { filterModified = fmt.Sprintf(` AND e."when" < to_timestamp(%d)`, m.Query.EventBeforeTime.Unix(), ) } else { filterModified = fmt.Sprintf(` AND e."when" BETWEEN to_timestamp(%d) AND to_timestamp(%d)`, m.Query.EventAfterTime.Unix(), m.Query.EventBeforeTime.Unix(), ) } } if joinEvents || m.Query.Attendee { filterEventsJoin = ` JOIN events e ON e.event_id = f.item_id` if m.Query.Attendee { filterEventsJoin += ` JOIN attendees a ON a.event_id = e.event_id AND a.profile_id = ` + strconv.FormatInt(profileID, 10) + ` AND a.state_id = 1` } } sqlQuery := ` WITH m AS ( SELECT m.microcosm_id FROM microcosms m LEFT JOIN permissions_cache p ON p.site_id = m.site_id AND p.item_type_id = 2 AND p.item_id = m.microcosm_id AND p.profile_id = $2 LEFT JOIN ignores i ON i.profile_id = $2 AND i.item_type_id = 2 AND i.item_id = m.microcosm_id WHERE m.site_id = $1 AND m.is_deleted IS NOT TRUE AND m.is_moderated IS NOT TRUE AND i.profile_id IS NULL AND ( (p.can_read IS NOT NULL AND p.can_read IS TRUE) OR (get_effective_permissions($1,m.microcosm_id,2,m.microcosm_id,$2)).can_read IS TRUE ) ) SELECT total ,item_type_id ,item_id ,parent_item_type_id ,parent_item_id ,last_modified ,rank ,ts_headline(` + fullTextScope + `_text, query) AS highlight ,has_unread(item_type_id, item_id, $2) FROM ( SELECT COUNT(*) OVER() AS total ,f.item_type_id ,f.item_id ,f.parent_item_type_id ,f.parent_item_id ,f.last_modified ,ts_rank_cd(si.` + fullTextScope + `_vector, query, 8) AS rank ,si.` + fullTextScope + `_text ,query.query FROM search_index si JOIN flags f ON f.item_type_id = si.item_type_id AND f.item_id = si.item_id LEFT JOIN ignores i ON i.profile_id = $2 AND i.item_type_id = f.item_type_id AND i.item_id = f.item_id` + filterEventsJoin + filterFollowing + ` LEFT JOIN huddle_profiles h ON (f.parent_item_type_id = 5 OR f.item_type_id = 5) AND h.huddle_id = COALESCE(f.parent_item_id, f.item_id) AND h.profile_id = $2 ,plainto_tsquery($3) AS query WHERE f.site_id = $1 AND i.profile_id IS NULL` + filterModified + filterMicrocosmIDs + filterTitle + filterItemTypes + filterItems + filterHashTag + filterEventsWhere + filterProfileID + ` AND f.microcosm_is_deleted IS NOT TRUE AND f.microcosm_is_moderated IS NOT TRUE AND f.parent_is_deleted IS NOT TRUE AND f.parent_is_moderated IS NOT TRUE AND f.item_is_deleted IS NOT TRUE AND f.item_is_moderated IS NOT TRUE AND si.` + fullTextScope + `_vector @@ query` + ` AND ( -- Things that are public by default COALESCE(f.parent_item_type_id, f.item_type_id) = 3 OR -- Things in microcosms COALESCE(f.microcosm_id, f.item_id) IN (SELECT microcosm_id FROM m) OR -- Things in huddles COALESCE(f.parent_item_id, f.item_id) = h.huddle_id ) ORDER BY ` + orderBy + ` LIMIT $4 OFFSET $5 ) r ` db, err := h.GetConnection() if err != nil { glog.Errorf("h.GetConnection() %+v", err) return m, http.StatusInternalServerError, err } queryID := `Search` + randomString() queryTimer := time.NewTimer(searchTimeout) go func() { <-queryTimer.C db.Exec(`SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query LIKE '--` + queryID + `%'`) }() // This nested query is used to run the `has_unread` query on only the rows // that are returned, rather than on all rows in the underlying query before // limit has been applied. rows, err := db.Query( `--`+queryID+ sqlQuery, siteID, profileID, m.Query.Query, limit, offset, ) queryTimer.Stop() if err != nil { e, ok := err.(*pq.Error) if !ok { glog.Errorf( "stmt.Query(%d, %s, %d, %d, %d) %+v", siteID, m.Query.Query, profileID, limit, offset, err, ) return m, http.StatusInternalServerError, fmt.Errorf("Database query failed") } switch e.Code.Name() { case "query_canceled": glog.Errorf( "Query for '%s' took too long", m.Query.Query, ) return m, http.StatusInternalServerError, merrors.MicrocosmError{ ErrorCode: 24, ErrorMessage: "The search query took too long and has been cancelled", } default: glog.Errorf( "stmt.Query(%d, %s, %d, %d, %d) %+v", siteID, m.Query.Query, profileID, limit, offset, err, ) return m, http.StatusInternalServerError, fmt.Errorf("Database query failed") } } defer rows.Close() var total int64 rs := []SearchResult{} for rows.Next() { var r SearchResult err = rows.Scan( &total, &r.ItemTypeID, &r.ItemID, &r.ParentItemTypeID, &r.ParentItemID, &r.LastModified, &r.Rank, &r.Highlight, &r.Unread, ) if err != nil { glog.Errorf("rows.Scan() %+v", err) return m, http.StatusInternalServerError, fmt.Errorf("Row parsing error") } itemType, err := h.GetMapStringFromInt(h.ItemTypes, r.ItemTypeID) if err != nil { glog.Errorf( "h.GetMapStringFromInt(h.ItemTypes, %d) %+v", r.ItemTypeID, err, ) return m, http.StatusInternalServerError, err } r.ItemType = itemType if r.ParentItemTypeID.Valid { parentItemType, err := h.GetMapStringFromInt(h.ItemTypes, r.ParentItemTypeID.Int64) if err != nil { glog.Errorf( "h.GetMapStringFromInt(h.ItemTypes, %d) %+v", r.ParentItemTypeID.Int64, err, ) return m, http.StatusInternalServerError, err } r.ParentItemType = parentItemType } rs = append(rs, r) } err = rows.Err() if err != nil { glog.Errorf("rows.Err() %+v", err) return m, http.StatusInternalServerError, fmt.Errorf("Error fetching rows") } rows.Close() pages := h.GetPageCount(total, limit) maxOffset := h.GetMaxOffset(total, limit) if offset > maxOffset { glog.Infoln("offset > maxOffset") return m, http.StatusBadRequest, fmt.Errorf("not enough records, "+ "offset (%d) would return an empty page.", offset) } // Extract the summaries var wg1 sync.WaitGroup req := make(chan SummaryContainerRequest) defer close(req) seq := 0 for i := 0; i < len(rs); i++ { go HandleSummaryContainerRequest( siteID, rs[i].ItemTypeID, rs[i].ItemID, profileID, seq, req, ) seq++ wg1.Add(1) if rs[i].ParentItemID.Valid && rs[i].ParentItemID.Int64 > 0 { go HandleSummaryContainerRequest( siteID, rs[i].ParentItemTypeID.Int64, rs[i].ParentItemID.Int64, profileID, seq, req, ) seq++ wg1.Add(1) } } resps := []SummaryContainerRequest{} for i := 0; i < seq; i++ { resp := <-req wg1.Done() resps = append(resps, resp) } wg1.Wait() for _, resp := range resps { if resp.Err != nil { return m, resp.Status, resp.Err } } sort.Sort(SummaryContainerRequestsBySeq(resps)) seq = 0 for i := 0; i < len(rs); i++ { rs[i].Item = resps[seq].Item.Summary seq++ if rs[i].ParentItemID.Valid && rs[i].ParentItemID.Int64 > 0 { rs[i].ParentItem = resps[seq].Item.Summary seq++ } } m.Results = h.ConstructArray( rs, "result", total, limit, offset, pages, &searchURL, ) // return milliseconds m.TimeTaken = time.Now().Sub(start).Nanoseconds() / 1000000 return m, http.StatusOK, nil }
// Read handles GET func (ctl *LastCommentController) Read(c *models.Context) { itemType, itemTypeID, itemID, status, err := c.GetItemTypeAndItemID() if err != nil { c.RespondWithErrorDetail(err, status) return } perms := models.GetPermission(models.MakeAuthorisationContext(c, 0, itemTypeID, itemID)) if !perms.CanRead { c.RespondWithErrorMessage(h.NoAuthMessage, http.StatusForbidden) return } parsed := c.Request.URL query := parsed.Query() limit, _, status, err := h.GetLimitAndOffset(query) if err != nil { c.RespondWithErrorDetail(err, status) return } query.Del("limit") if limit != h.DefaultQueryLimit { query.Set("limit", strconv.FormatInt(limit, 10)) } lastComment, status, err := models.GetLastComment(itemTypeID, itemID) if err != nil { c.RespondWithErrorDetail(err, status) return } location := fmt.Sprintf( "%s/%d", h.ItemTypesToAPIItem[itemType], itemID, ) parsed.Path = location // Construct location of the last comment on the item. if lastComment.Valid { _, _, offset, _, err := models.GetPageNumber( lastComment.ID, limit, c.Auth.ProfileID, ) if err != nil { query.Del("offset") if offset != h.DefaultQueryOffset { query.Set("offset", strconv.FormatInt(offset, 10)) } query.Del("comment_id") query.Set("comment_id", strconv.FormatInt(lastComment.ID, 10)) parsed.RawQuery = query.Encode() c.RespondWithLocation(parsed.String()) return } } parsed.RawQuery = query.Encode() c.RespondWithLocation(parsed.String()) }