Пример #1
0
// ProfileMetrics builds metrics about profiles
func ProfileMetrics(start time.Time) (created int, edited int, total int, err error) {

	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("ProfileMetrics: %s", err.Error())
		return
	}

	err = db.QueryRow(`SELECT COUNT(*) FROM profiles`).Scan(&total)
	if err != nil {
		return
	}

	err = db.QueryRow(`SELECT COUNT(*) FROM profiles WHERE created >= $1`,
		start,
	).Scan(
		&created,
	)
	if err != nil {
		return
	}

	err = db.QueryRow(`
SELECT COUNT(*)
  FROM profiles
 WHERE profile_name !~ 'user*'
   AND avatar_id IS NOT NULL`,
	).Scan(
		&edited,
	)

	return
}
Пример #2
0
// GetAttendeeID returns the attendee id of a profile
func GetAttendeeID(eventID int64, profileID int64) (int64, int, error) {
	// Open db connection and retrieve resource
	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("h.GetConnection() %+v", err)
		return 0, http.StatusInternalServerError, err
	}

	var attendeeID int64

	err = db.QueryRow(`
SELECT attendee_id
  FROM attendees
 WHERE event_id = $1
   AND profile_id = $2`,
		eventID,
		profileID,
	).Scan(
		&attendeeID,
	)
	if err == sql.ErrNoRows {
		return 0, http.StatusNotFound, fmt.Errorf("attendee not found")

	} else if err != nil {
		glog.Errorf("db.QueryRow(%d, %d) %+v", eventID, profileID, err)
		return 0, http.StatusInternalServerError,
			fmt.Errorf("Database query failed")
	}

	return attendeeID, http.StatusOK, nil
}
Пример #3
0
// GetHuddleTitle returns the title of the huddle
func GetHuddleTitle(id int64) string {
	// Get from cache if it's available
	mcKey := fmt.Sprintf(mcHuddleKeys[c.CacheTitle], id)
	if val, ok := c.GetString(mcKey); ok {
		return val
	}

	// Retrieve resource
	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("h.GetConmection() %+v", err)
		return ""
	}

	var title string
	err = db.QueryRow(`
SELECT title
  FROM huddles
 WHERE huddle_id = $1`,
		id,
	).Scan(
		&title,
	)
	if err != nil {
		glog.Errorf("row.Scan() %+v", err)
		return ""
	}

	// Update cache
	c.SetString(mcKey, title, mcTTL)

	return title
}
Пример #4
0
// GetMicrocosmIDForItem provides a cheap way to fetch an id for an item
func GetMicrocosmIDForItem(itemTypeID int64, itemID int64) int64 {
	db, err := h.GetConnection()
	if err != nil {
		glog.Error(err)
		return 0
	}

	var microcosmID int64
	err = db.QueryRow(`--GetMicrocosmIdForItem
SELECT microcosm_id
  FROM flags
 WHERE item_type_id = $1
   AND item_id = $2`,
		itemTypeID,
		itemID,
	).Scan(
		&microcosmID,
	)
	if err != nil {
		glog.Error(err)
		return 0
	}

	return microcosmID
}
Пример #5
0
// GetMenu returns a menu for a site
func GetMenu(siteID int64) ([]h.LinkType, int, error) {
	db, err := h.GetConnection()
	if err != nil {
		return []h.LinkType{}, http.StatusInternalServerError, err
	}

	rows, err := db.Query(`
SELECT href
      ,title
      ,"text"
  FROM menus
 WHERE site_id = $1
 ORDER BY sequence ASC`,
		siteID,
	)
	if err != nil {
		glog.Errorf("tx.Query(%d) %+v", siteID, err)
		return []h.LinkType{}, http.StatusInternalServerError,
			fmt.Errorf("Database query failed")
	}
	defer rows.Close()

	ems := []h.LinkType{}
	for rows.Next() {
		m := h.LinkType{}
		s := sql.NullString{}
		err = rows.Scan(
			&m.Href,
			&s,
			&m.Text,
		)
		if err != nil {
			glog.Errorf("rows.Scan() %+v", err)
			return []h.LinkType{}, http.StatusInternalServerError,
				fmt.Errorf("Row parsing error")
		}

		if s.Valid {
			m.Title = s.String
		}

		ems = append(ems, m)
	}
	err = rows.Err()
	if err != nil {
		glog.Errorf("rows.Err() %+v", err)
		return []h.LinkType{}, http.StatusInternalServerError,
			fmt.Errorf("Error fetching rows")
	}
	rows.Close()

	return ems, http.StatusOK, nil
}
Пример #6
0
// GetProfileID fetches a profileID given a userID
func GetProfileID(siteID int64, userID int64) (int64, int, error) {

	if siteID == 0 || userID == 0 {
		return 0, http.StatusOK, nil
	}

	// Get from cache if it's available
	//
	// This map of siteId+userId = profileId is never expected to change, so
	// this cache key is unique and does not conform to the cache flushing
	// mechanism
	mcKey := fmt.Sprintf("s%d_u%d", siteID, userID)
	if val, ok := c.GetInt64(mcKey); ok {
		return val, http.StatusOK, nil
	}

	var profileID int64
	db, err := h.GetConnection()
	if err != nil {
		glog.Error(err)
		return profileID, http.StatusInternalServerError, err
	}

	err = db.QueryRow(`--GetProfileId
SELECT profile_id
  FROM profiles
 WHERE site_id = $1
   AND user_id = $2`,
		siteID,
		userID,
	).Scan(
		&profileID,
	)
	if err == sql.ErrNoRows {
		glog.Warning(err)
		return profileID, http.StatusNotFound,
			fmt.Errorf(
				"Profile for site (%d) and user (%d) not found.",
				siteID,
				userID,
			)

	} else if err != nil {
		glog.Error(err)
		return profileID, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %v", err.Error())
	}

	c.SetInt64(mcKey, profileID, mcTTL)

	return profileID, http.StatusOK, nil
}
Пример #7
0
// GetProfileOptionsDefaults returns the default options for this site
func GetProfileOptionsDefaults(siteID int64) (ProfileOptionType, int, error) {

	db, err := h.GetConnection()
	if err != nil {
		return ProfileOptionType{}, http.StatusInternalServerError, err
	}

	rows, err := db.Query(`
SELECT COALESCE(s.send_email, p.send_email) AS send_email
      ,COALESCE(s.send_sms, p.send_sms) AS send_sms
  FROM platform_options p
       LEFT JOIN (
           SELECT send_email
                 ,send_sms
             FROM site_options
            WHERE site_id = $1
       ) s ON 1=1`,
		siteID,
	)
	if err != nil {
		return ProfileOptionType{}, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %v", err.Error())
	}
	defer rows.Close()

	var m ProfileOptionType

	for rows.Next() {
		m = ProfileOptionType{}
		err = rows.Scan(
			&m.SendEMail,
			&m.SendSMS,
		)
		if err != nil {
			return ProfileOptionType{}, http.StatusInternalServerError,
				fmt.Errorf("Row parsing error: %v", err.Error())
		}
	}
	err = rows.Err()
	if err != nil {
		return ProfileOptionType{}, http.StatusInternalServerError,
			fmt.Errorf("Error fetching rows: %v", err.Error())
	}
	rows.Close()

	m.IsDiscouraged = false
	m.ShowDOB = false
	m.ShowDOBYear = false

	return m, http.StatusOK, nil
}
Пример #8
0
// GetProfileOptions returns the options for a profile
func GetProfileOptions(profileID int64) (ProfileOptionType, int, error) {

	// Get from cache if it's available
	mcKey := fmt.Sprintf(mcProfileKeys[c.CacheOptions], profileID)
	if val, ok := c.Get(mcKey, ProfileOptionType{}); ok {
		m := val.(ProfileOptionType)
		return m, http.StatusOK, nil
	}

	db, err := h.GetConnection()
	if err != nil {
		return ProfileOptionType{}, http.StatusInternalServerError, err
	}

	var m ProfileOptionType
	err = db.QueryRow(`
SELECT profile_id
      ,show_dob_date
      ,show_dob_year
      ,send_email
      ,send_sms
      ,is_discouraged
  FROM profile_options
 WHERE profile_id = $1`,
		profileID,
	).Scan(
		&m.ProfileID,
		&m.ShowDOB,
		&m.ShowDOBYear,
		&m.SendEMail,
		&m.SendSMS,
		&m.IsDiscouraged,
	)
	if err == sql.ErrNoRows {
		return ProfileOptionType{}, http.StatusNotFound,
			fmt.Errorf("Resource with profile ID %d not found", profileID)

	} else if err != nil {
		return ProfileOptionType{}, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %v", err.Error())
	}

	// Update cache
	c.Set(mcKey, m, mcTTL)

	return m, http.StatusOK, nil
}
Пример #9
0
// GetUpdateOptionByUpdateType returns the notification settings (email, sms)
// for a given user and update type.
func GetUpdateOptionByUpdateType(
	profileID int64,
	updateTypeID int64,
) (
	UpdateOptionType,
	int,
	error,
) {

	db, err := h.GetConnection()
	if err != nil {
		return UpdateOptionType{}, http.StatusInternalServerError, err
	}

	var m UpdateOptionType
	err = db.QueryRow(`
SELECT uo.profile_id
      ,uo.update_type_id
      ,ut.description
      ,uo.send_email
      ,uo.send_sms
  FROM update_options uo
       LEFT JOIN update_types ut ON uo.update_type_id = ut.update_type_id
 WHERE uo.profile_id = $1
   AND uo.update_type_id = $2`,
		profileID,
		updateTypeID,
	).Scan(
		&m.ProfileID,
		&m.UpdateTypeID,
		&m.Description,
		&m.SendEmail,
		&m.SendSMS,
	)
	if err == sql.ErrNoRows {
		return UpdateOptionType{}, http.StatusNotFound,
			fmt.Errorf("Update options for profile ID %d not found", profileID)
	} else if err != nil {
		return UpdateOptionType{}, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %v", err.Error())
	}

	return m, http.StatusOK, nil
}
Пример #10
0
// IsAttending indicates whether the given profile is attending the event
func IsAttending(profileID int64, eventID int64) (bool, error) {
	if profileID == 0 || eventID == 0 {
		return false, nil
	}

	var attendeeIDs []int64

	key := fmt.Sprintf(mcEventKeys[c.CacheProfileIds], eventID)
	attendeeIDs, ok := c.GetInt64Slice(key)

	if !ok {
		db, err := h.GetConnection()
		if err != nil {
			return false, err
		}

		rows, err := db.Query(`
SELECT profile_id
  FROM attendees
 WHERE event_id = $1
   AND state_id = 1`,
			eventID,
		)
		if err != nil {
			return false, err
		}

		for rows.Next() {
			var attendeeID int64
			err = rows.Scan(&attendeeID)
			attendeeIDs = append(attendeeIDs, attendeeID)
		}

		c.SetInt64Slice(key, attendeeIDs, mcTTL)
	}

	for _, id := range attendeeIDs {
		if profileID == id {
			return true, nil
		}
	}

	return false, nil
}
Пример #11
0
// FetchSummaries populates a partially populated microcosm struct
func (m *MicrocosmType) FetchSummaries(
	siteID int64,
	profileID int64,
) (
	int,
	error,
) {

	profile, status, err := GetProfileSummary(siteID, m.Meta.CreatedByID)
	if err != nil {
		return status, err
	}
	m.Meta.CreatedBy = profile

	if m.Meta.EditedByNullable.Valid {
		profile, status, err :=
			GetProfileSummary(siteID, m.Meta.EditedByNullable.Int64)
		if err != nil {
			return status, err
		}
		m.Meta.EditedBy = profile
	}

	db, err := h.GetConnection()
	if err != nil {
		return http.StatusInternalServerError, err
	}

	var unread bool
	err = db.QueryRow(
		`SELECT has_unread(2, $1, $2)`,
		m.ID,
		profileID,
	).Scan(
		&unread,
	)
	if err != nil {
		glog.Errorf("db.QueryRow() %+v", err)
		return http.StatusInternalServerError, err
	}
	m.Meta.Flags.Unread = unread

	return http.StatusOK, nil
}
Пример #12
0
// IsBanned returns true if the user is banned for the given site
func IsBanned(siteID int64, userID int64) bool {

	if siteID == 0 || userID == 0 {
		return false
	}

	// Get from cache if it's available
	//
	// This map of siteID+userID = profileId is never expected to change, so
	// this cache key is unique and does not conform to the cache flushing
	// mechanism
	mcKey := fmt.Sprintf(banCacheKey, siteID, userID)
	if val, ok := c.GetBool(mcKey); ok {
		return val
	}

	var isBanned bool
	db, err := h.GetConnection()
	if err != nil {
		return false
	}

	err = db.QueryRow(`--IsBanned
SELECT EXISTS(
SELECT 1
  FROM bans
 WHERE site_id = $1
   AND user_id = $2
)`,
		siteID,
		userID,
	).Scan(
		&isBanned,
	)
	if err == sql.ErrNoRows {
		return false
	} else if err != nil {
		return false
	}

	c.SetBool(mcKey, isBanned, mcTTL)

	return isBanned
}
Пример #13
0
// UserGenMetrics builds metrics about user activity
func UserGenMetrics(
	start time.Time,
) (
	signins int,
	comments int,
	convs int,
	err error,
) {

	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("UserGenMetrics: %s", err.Error())
		return
	}

	err = db.QueryRow(
		`SELECT COUNT(*) FROM profiles where profiles.last_active > $1`,
		start,
	).Scan(
		&signins,
	)
	if err != nil {
		return
	}

	err = db.QueryRow(
		`SELECT COUNT(*) FROM comments WHERE created >= $1`,
		start,
	).Scan(
		&comments,
	)
	if err != nil {
		return
	}

	err = db.QueryRow(
		`SELECT COUNT(*) FROM conversations WHERE created >= $1`,
		start,
	).Scan(
		&convs,
	)
	return
}
Пример #14
0
// UpdateLastActive marks a profile as being active
func UpdateLastActive(profileID int64, lastActive time.Time) (int, error) {

	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("h.GetConnection() %+v", err)
		return http.StatusInternalServerError,
			fmt.Errorf("Could not get a database connection: %v", err.Error())
	}

	tx, err := db.Begin()
	if err != nil {
		return http.StatusInternalServerError,
			fmt.Errorf("Could not start transaction: %v", err.Error())
	}
	defer tx.Rollback()

	_, err = tx.Exec(`--UpdateLastActive
UPDATE profiles
   SET last_active = $2
 WHERE profile_id = $1;`,
		profileID,
		lastActive,
	)
	if err != nil {
		nerr := tx.Rollback()
		if nerr != nil {
			glog.Errorf("Cannot rollback: %+v", nerr)
		}

		return http.StatusInternalServerError,
			fmt.Errorf("Update of last active failed: %v", err.Error())
	}

	err = tx.Commit()
	if err != nil {
		return http.StatusInternalServerError,
			fmt.Errorf("Transaction failed: %v", err.Error())
	}

	PurgeCacheByScope(c.CacheDetail, h.ItemTypes[h.ItemTypeProfile], profileID)

	return http.StatusOK, nil
}
Пример #15
0
// GetUnreadHuddleCount fetches the current unread huddle count
func (m *ProfileType) GetUnreadHuddleCount() (int, error) {

	// Get from cache if it's available
	mcKey := fmt.Sprintf(mcProfileKeys[c.CacheCounts], m.ID)
	if i, ok := c.GetInt64(mcKey); ok {

		m.Meta.Stats = append(
			m.Meta.Stats,
			h.StatType{Metric: "unreadHuddles", Value: i},
		)

		return http.StatusOK, nil
	}

	db, err := h.GetConnection()
	if err != nil {
		return http.StatusInternalServerError, err
	}

	var unreadHuddles int64
	err = db.QueryRow(`--GetUnreadHuddleCount
SELECT unread_huddles
  FROM profiles
 WHERE profile_id = $1`,
		m.ID,
	).Scan(
		&unreadHuddles,
	)
	if err != nil {
		return http.StatusInternalServerError,
			fmt.Errorf("Error fetching row: %v", err.Error())
	}

	m.Meta.Stats = append(
		m.Meta.Stats,
		h.StatType{Metric: "unreadHuddles", Value: unreadHuddles},
	)

	c.SetInt64(mcKey, unreadHuddles, mcTTL)

	return http.StatusOK, nil
}
Пример #16
0
// GetUserByEmailAddress performs a case-insensitive search for any matching
// user and returns it.
func GetUserByEmailAddress(email string) (UserType, int, error) {

	if strings.Trim(email, " ") == "" {
		return UserType{}, http.StatusBadRequest,
			fmt.Errorf("You must specify an email address")
	}

	db, err := h.GetConnection()
	if err != nil {
		return UserType{}, http.StatusInternalServerError, err
	}

	// Note that we match emails based on full case-insensitivity.
	// The design decision behind this is that there are no major email
	// providers out there that honour case sensitivity on the local part
	// (before the @) of an email address, and that the benefits to the
	// end user that incorrectly enters their email address (either with
	// CAPS LOCK on, or using a mobile device that upper-cased the first
	// char) far outweighs the risk to security.
	//
	// This scenario is far more likely when the users email has been
	// provided and the user stubbed rather than created as a by-product
	// of logging into the system
	var m UserType
	err = db.QueryRow(`
SELECT user_id
  FROM users
 WHERE LOWER(email) = LOWER($1)`,
		email,
	).Scan(
		&m.ID,
	)
	if err == sql.ErrNoRows {
		return UserType{}, http.StatusNotFound,
			fmt.Errorf("Resource with email %v not found", email)
	} else if err != nil {
		return UserType{}, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %+v", err)
	}

	return GetUser(m.ID)
}
Пример #17
0
// UserIsOnSite returns true if the given userId exists as a profile on the
// given site.
func UserIsOnSite(userID int64, siteID int64) bool {
	db, err := h.GetConnection()
	if err != nil {
		return false
	}

	var val bool
	err = db.QueryRow(`--UserIsOnSite
SELECT COUNT(*) > 0
  FROM profiles
 WHERE site_id = $1
   AND user_id = $2`,
		siteID,
		userID,
	).Scan(&val)
	if err != nil {
		return false
	}

	return val
}
Пример #18
0
// DecrementProfileCommentCount decrements the comment count
func DecrementProfileCommentCount(profileID int64) {

	db, err := h.GetConnection()
	if err != nil {
		glog.Error(err)
		return
	}

	_, err = db.Exec(`--Update Profile Comment Count
UPDATE profiles
   SET comment_count = comment_count - 1
 WHERE profile_id = $1`,
		profileID,
	)
	if err != nil {
		glog.Error(err)
		return
	}

	PurgeCacheByScope(c.CacheDetail, h.ItemTypes[h.ItemTypeProfile], profileID)
}
Пример #19
0
// ForumMetrics builds metrics about sites
func ForumMetrics() (total int, engaged int, err error) {
	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("ForumMetrics: %s", err.Error())
		return
	}

	err = db.QueryRow(`SELECT COUNT(*) FROM sites`).Scan(&total)
	if err != nil {
		return
	}

	err = db.QueryRow(`
SELECT COUNT(*) FROM (
           SELECT s.site_id
             FROM sites s
                  JOIN microcosms m ON s.site_id = m.site_id
                  JOIN conversations c ON m.microcosm_id = c.microcosm_id
                  JOIN comments cm ON c.conversation_id = cm.item_id
            UNION
           SELECT s.site_id
             FROM sites s
                  JOIN microcosms m ON s.site_id = m.site_id
                  JOIN events e ON m.microcosm_id = e.microcosm_id
                  JOIN comments cm ON e.event_id = cm.item_id	
            WHERE logo_url !~ $1
              AND (
                      SELECT COUNT(*)
                        FROM profiles p
                       WHERE p.site_id = s.site_id
                  ) > 1
            GROUP BY s.site_id
       ) AS t`,
		defaultLogoURL,
	).Scan(
		&engaged,
	)

	return
}
Пример #20
0
// IncrementViewCount increments the views of an item
func IncrementViewCount(itemTypeID int64, itemID int64) {

	// No transaction as we don't care for accuracy on these updates
	// Note: This function doesn't even return errors, we don't even care
	// if the occasional INSERT fails.
	db, err := h.GetConnection()
	if err != nil {
		glog.Error(err)
		return
	}

	// Integrity insert, gets rolled up and updated on cron
	_, err = db.Exec(
		`INSERT INTO views(item_type_id, item_id) VALUES ($1, $2)`,
		itemTypeID,
		itemID,
	)
	if err != nil {
		glog.Error(err)
		return
	}
}
Пример #21
0
// GetAttributeID fetches the id of an attribute
func GetAttributeID(
	itemTypeID int64,
	itemID int64,
	key string,
) (
	int64,
	int,
	error,
) {
	db, err := h.GetConnection()
	if err != nil {
		return 0, http.StatusInternalServerError, err
	}

	var attrID int64

	err = db.QueryRow(`
SELECT attribute_id
  FROM attribute_keys
 WHERE item_type_id = $1
   AND item_id = $2
   AND key = $3
`,
		itemTypeID,
		itemID,
		key,
	).Scan(
		&attrID,
	)
	if err == sql.ErrNoRows {
		return attrID, http.StatusNotFound, fmt.Errorf("Attribute not found.")

	} else if err != nil {
		return attrID, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %v", err.Error())
	}

	return attrID, http.StatusOK, nil
}
Пример #22
0
// GetRoleProfiles fetches multiple profiles belonging to a role
func GetRoleProfiles(
	siteID int64,
	roleID int64,
	limit int64,
	offset int64,
) (
	[]ProfileSummaryType,
	int64,
	int64,
	int,
	error,
) {

	db, err := h.GetConnection()
	if err != nil {
		return []ProfileSummaryType{}, 0, 0, http.StatusInternalServerError, err
	}

	rows, err := db.Query(`
SELECT COUNT(*) OVER() AS total
      ,rp.profile_id
  FROM role_profiles rp,
       roles r
 WHERE r.role_id = rp.role_id
   AND r.role_id = $1
 ORDER BY rp.profile_id ASC
 LIMIT $2
OFFSET $3`,
		roleID,
		limit,
		offset,
	)
	if err != nil {
		return []ProfileSummaryType{}, 0, 0, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %v", err.Error())
	}
	defer rows.Close()

	// Get a list of the identifiers of the items to return
	var total int64
	ids := []int64{}
	for rows.Next() {
		var id int64
		err = rows.Scan(
			&total,
			&id,
		)
		if err != nil {
			return []ProfileSummaryType{}, 0, 0, http.StatusInternalServerError,
				fmt.Errorf("Row parsing error: %v", err.Error())
		}

		ids = append(ids, id)
	}
	err = rows.Err()
	if err != nil {
		return []ProfileSummaryType{}, 0, 0, http.StatusInternalServerError,
			fmt.Errorf("Error fetching rows: %v", err.Error())
	}
	rows.Close()

	// Make a request for each identifier
	var wg1 sync.WaitGroup
	req := make(chan ProfileSummaryRequest)
	defer close(req)

	for seq, id := range ids {
		go HandleProfileSummaryRequest(siteID, id, seq, req)
		wg1.Add(1)
	}

	// Receive the responses and check for errors
	resps := []ProfileSummaryRequest{}
	for i := 0; i < len(ids); i++ {
		resp := <-req
		wg1.Done()
		resps = append(resps, resp)
	}
	wg1.Wait()

	for _, resp := range resps {
		if resp.Err != nil {
			return []ProfileSummaryType{}, 0, 0, resp.Status, resp.Err
		}
	}

	// Sort them
	sort.Sort(ProfileSummaryRequestBySeq(resps))

	// Extract the values
	ems := []ProfileSummaryType{}
	for _, resp := range resps {
		ems = append(ems, resp.Item)
	}

	pages := h.GetPageCount(total, limit)
	maxOffset := h.GetMaxOffset(total, limit)

	if offset > maxOffset {
		return []ProfileSummaryType{}, 0, 0, http.StatusBadRequest,
			fmt.Errorf(
				"not enough records, offset (%d) would return an empty page",
				offset,
			)
	}

	return ems, total, pages, http.StatusOK, nil

}
Пример #23
0
// GetRoleProfile returns a single profile for a role
func GetRoleProfile(
	siteID int64,
	roleID int64,
	profileID int64,
) (
	ProfileSummaryType,
	int,
	error,
) {

	// Retrieve resources
	db, err := h.GetConnection()
	if err != nil {
		return ProfileSummaryType{}, http.StatusInternalServerError, err
	}

	rows, err := db.Query(`
SELECT profile_id
  FROM role_profiles
 WHERE role_id = $1
   AND profile_id = $2`,
		roleID,
		profileID,
	)
	if err != nil {
		return ProfileSummaryType{}, http.StatusInternalServerError,
			fmt.Errorf("Database query failed: %v", err.Error())
	}
	defer rows.Close()

	m := ProfileSummaryType{}
	for rows.Next() {
		err = rows.Scan(
			&m.ID,
		)
		if err != nil {
			return ProfileSummaryType{}, http.StatusInternalServerError,
				fmt.Errorf("Error fetching row: %v", err.Error())
		}

		// Make a request the profile summary
		req := make(chan ProfileSummaryRequest)
		defer close(req)

		go HandleProfileSummaryRequest(siteID, m.ID, 0, req)

		// Receive the response
		resp := <-req
		if resp.Err != nil {
			return ProfileSummaryType{}, resp.Status, resp.Err
		}

		m = resp.Item
	}
	err = rows.Err()
	if err != nil {
		return ProfileSummaryType{}, http.StatusInternalServerError,
			fmt.Errorf("Error fetching rows: %v", err.Error())
	}
	rows.Close()

	return m, http.StatusOK, nil
}
// GetCommunicationOptions returns a user's update options if present,
// otherwise it returns the default preference for the given update type.
func GetCommunicationOptions(
	siteID int64,
	profileID int64,
	updateTypeID int64,
	itemTypeID int64,
	itemID int64,
) (
	UpdateOptionType,
	int,
	error,
) {

	_, status, err := GetProfileOptions(profileID)
	if err != nil {
		glog.Errorf("GetProfileOptions(%d) %+v", profileID, err)
		// Can't do anything here as the profile_id fkey constraint will fail
		return UpdateOptionType{}, status, fmt.Errorf("Insert of update options failed")
	}

	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("h.GetConnection() %+v", err)
		return UpdateOptionType{}, http.StatusInternalServerError, err
	}

	rows, err := db.Query(`
SELECT send_email
      ,send_sms
      ,description
  FROM get_communication_options($1, $2, $3, $4, $5)
       LEFT JOIN update_types a ON update_type_id = $5`,
		siteID,
		itemID,
		itemTypeID,
		profileID,
		updateTypeID,
	)
	if err != nil {
		glog.Errorf(
			"db.Query(%d, %d, %d, %d, %d) %+v",
			siteID,
			itemID,
			itemTypeID,
			profileID,
			updateTypeID,
			err,
		)
		return UpdateOptionType{},
			http.StatusInternalServerError,
			fmt.Errorf("Database query failed")
	}
	defer rows.Close()

	var m UpdateOptionType

	for rows.Next() {
		m = UpdateOptionType{}
		err = rows.Scan(
			&m.SendEmail,
			&m.SendSMS,
			&m.Description,
		)
		if err != nil {
			glog.Errorf("rows.Scan() %+v", err)
			return UpdateOptionType{},
				http.StatusInternalServerError,
				fmt.Errorf("Row parsing error")
		}
	}
	err = rows.Err()
	if err != nil {
		glog.Errorf("rows.Err() %+v", err)
		return UpdateOptionType{},
			http.StatusInternalServerError,
			fmt.Errorf("Error fetching rows")
	}
	rows.Close()

	m.ProfileID = int64(profileID)
	m.UpdateTypeID = int64(updateTypeID)

	return m, http.StatusOK, nil
}
Пример #25
0
func resolveVbulletinURL(redirect Redirect, profileID int64) Redirect {

	// Query string checks are cheap, so we do those first
	qs := redirect.ParsedURL.Query()

	for _, q := range vbqs {
		i := atoi64(qs.Get(q.key))
		if i > 0 {
			i = getNewID(redirect.Origin.OriginID, q.itemTypeID, i)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}

			redirect.ItemTypeID = q.itemTypeID
			redirect.ItemID = i

			break
		}
	}

	i := atoi64(qs.Get("page"))
	if i > 0 {
		switch redirect.ItemTypeID {
		case h.ItemTypes[h.ItemTypeConversation]:
			redirect.Offset = pageToOffset(i, vbPostsPerThreadPage)
		case h.ItemTypes[h.ItemTypeMicrocosm]:
			redirect.Offset = pageToOffset(i, vbThreadsPerForumPage)
		default:
			redirect.Offset = pageToOffset(i, vbPostsPerThreadPage)
		}
	}

	action := qs.Get("goto")
	if action != "" {
		switch action {
		case "newpost":
			action = ActionNewComment
		default:
		}
	}

	// Move on to path based searches, but only if we haven't found anything.
	// These are potentially expensive, so these are ordered by the most likely
	// to the least likely... threads first, posts next, forums after, and then
	// the rest.

	// Look at the URL itself
	path := redirect.ParsedURL.Path

	// Thread redirects
	if redirect.ItemTypeID == 0 {
		matches := vbLastPostInThread.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeConversation]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
			redirect.Action = ActionNewComment
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbNewPostInThread.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeConversation]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
			redirect.Action = ActionNewComment
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbThreadPrintPage.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeConversation]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
			redirect.Offset = pageToOffset(atoi64(matches[2]), vbPostsPerThreadPage)
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbThreadPrint.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeConversation]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbThreadPage.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeConversation]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i

			// This is no longer done, we just send people to the first page
			// This is due to a massive increase in errors as it turned out there
			// were a lot of bad links in people's posts that vBulletin was more
			// forgiving about (it would auto-send people to the last page)
			//redirect.Offset = pageToOffset(atoi64(matches[2]), vbPostsPerThreadPage)
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbThread.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeConversation]

			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
		}
	}

	// Comment redirects
	if redirect.ItemTypeID == 0 {
		matches := vbPostPosition.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeComment]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
			redirect.Action = ActionCommentInContext
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbPost.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeComment]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
			redirect.Action = ActionCommentInContext
		}
	}

	// Microcosm redirects
	if redirect.ItemTypeID == 0 {
		matches := vbForumPage.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeMicrocosm]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
			redirect.Offset = pageToOffset(atoi64(matches[2]), vbThreadsPerForumPage)
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbForum.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeMicrocosm]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbAnnouncement.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeMicrocosm]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
		}
	}

	// Profile redirects
	if redirect.ItemTypeID == 0 {
		matches := vbMemberListLetter.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeProfile]
			redirect.Action = ActionSearch
			redirect.Search = matches[1]
		}
	}

	if redirect.ItemTypeID == 0 {
		matches := vbMember.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeProfile]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
		}
	}

	// Attachment redirects
	if redirect.ItemTypeID == 0 {
		matches := vbAttachment.FindStringSubmatch(path)
		if len(matches) > 0 {
			redirect.ItemTypeID = h.ItemTypes[h.ItemTypeAttachment]
			i = getNewID(
				redirect.Origin.OriginID,
				redirect.ItemTypeID,
				atoi64(matches[1]),
			)
			if i == 0 {
				redirect.Status = http.StatusNotFound
				return redirect
			}
			redirect.ItemID = i
		}
	}

	// Random URLs
	if redirect.ItemTypeID == 0 && vbMemberList.MatchString(path) {
		redirect.ItemTypeID = h.ItemTypes[h.ItemTypeProfile]
	}

	if redirect.ItemTypeID == 0 && vbOnline.MatchString(path) {
		redirect.ItemTypeID = h.ItemTypes[h.ItemTypeProfile]
		redirect.Action = ActionWhoIsOnline
	}

	if redirect.ItemTypeID == 0 && vbPMs.MatchString(path) {
		redirect.ItemTypeID = h.ItemTypes[h.ItemTypeHuddle]
	}

	if redirect.ItemTypeID == 0 && vbSubscription.MatchString(path) {
		redirect.ItemTypeID = h.ItemTypes[h.ItemTypeUpdate]
	}

	if redirect.ItemTypeID == 0 && vbUserCP.MatchString(path) {
		redirect.ItemTypeID = h.ItemTypes[h.ItemTypeUpdate]
	}

	// Construct the actual URLs to send people to
	canPaginate := false
	switch redirect.ItemTypeID {
	case h.ItemTypes[h.ItemTypeMicrocosm]:
		redirect.ItemType = h.ItemTypeMicrocosm

		if redirect.ItemID > 0 {
			redirect.URL.Href = fmt.Sprintf(
				"%s/%d",
				h.APITypeMicrocosm,
				redirect.ItemID,
			)
		} else {
			redirect.URL.Href = h.APITypeMicrocosm
		}
		redirect.URL.Rel = redirect.ItemType
		canPaginate = true

	case h.ItemTypes[h.ItemTypeConversation]:
		redirect.ItemType = h.ItemTypeConversation

		switch redirect.Action {
		case ActionNewComment:
			if redirect.ItemID > 0 {

				t, _, err := models.GetLastReadTime(
					h.ItemTypes[h.ItemTypeConversation],
					redirect.ItemID,
					profileID,
				)
				if err != nil {
					redirect.Status = http.StatusNotFound
					return redirect
				}

				commentID, _, err := models.GetNextOrLastCommentID(
					h.ItemTypes[h.ItemTypeConversation],
					redirect.ItemID,
					t,
					profileID,
				)
				if err != nil {
					redirect.Status = http.StatusNotFound
					return redirect
				}

				redirect.ItemTypeID = h.ItemTypes[h.ItemTypeComment]
				redirect.ItemType = h.ItemTypeComment
				redirect.ItemID = commentID

				redirect.URL.Href = fmt.Sprintf(
					"%s/%d",
					h.APITypeComment,
					redirect.ItemID,
				)
				redirect.URL.Rel = redirect.ItemType
				redirect.Action = ActionCommentInContext

			} else {
				redirect.Status = http.StatusNotFound
				return redirect
			}
		default:
			if redirect.ItemID > 0 {
				redirect.URL.Href = fmt.Sprintf(
					"%s/%d",
					h.APITypeConversation,
					redirect.ItemID,
				)
				redirect.URL.Rel = redirect.ItemType
				canPaginate = true
			} else {
				redirect.Status = http.StatusNotFound
				return redirect
			}
		}

	case h.ItemTypes[h.ItemTypeComment]:
		redirect.ItemType = h.ItemTypeComment

		if redirect.ItemID > 0 {
			redirect.URL.Href = fmt.Sprintf(
				"%s/%d",
				h.APITypeComment,
				redirect.ItemID,
			)
			redirect.URL.Rel = redirect.ItemType
		} else {
			redirect.Status = http.StatusNotFound
			return redirect
		}

	case h.ItemTypes[h.ItemTypeAttachment]:
		redirect.ItemType = h.ItemTypeAttachment

		db, err := h.GetConnection()
		if err != nil {
			redirect.Status = http.StatusNotFound
			return redirect
		}

		var fileSha1 string
		err = db.QueryRow(`--Get Attachment ID
SELECT file_sha1
  FROM attachments
 WHERE attachment_meta_id = $1`,
			redirect.ItemID,
		).Scan(&fileSha1)
		if err != nil {
			redirect.Status = http.StatusNotFound
			return redirect
		}

		site, _, err := models.GetSite(redirect.Origin.SiteID)
		if err != nil {
			redirect.Status = http.StatusNotFound
			return redirect
		}

		redirect.URL.Href = fmt.Sprintf(
			"https://%s.microco.sm%s/%s",
			site.SubdomainKey,
			h.APITypeFile,
			fileSha1,
		)

	case h.ItemTypes[h.ItemTypeHuddle]:
		redirect.ItemType = h.ItemTypeHuddle

		if redirect.ItemID > 0 {
			redirect.URL.Href = fmt.Sprintf(
				"%s/%d",
				h.APITypeHuddle,
				redirect.ItemID,
			)
		} else {
			redirect.URL.Href = h.APITypeHuddle
		}
		redirect.URL.Rel = redirect.ItemType

	case h.ItemTypes[h.ItemTypeProfile]:
		redirect.ItemType = h.ItemTypeProfile

		if redirect.ItemID > 0 {
			redirect.URL.Href = fmt.Sprintf(
				"%s/%d",
				h.APITypeProfile,
				redirect.ItemID,
			)
		} else {
			switch redirect.Action {
			case ActionSearch:
				redirect.URL.Href = h.APITypeProfile + "?q=" + redirect.Search
			case ActionWhoIsOnline:
				redirect.URL.Href = h.APITypeProfile + "?online=true"
			default:
				redirect.URL.Href = h.APITypeProfile
			}
			canPaginate = true
		}
		redirect.URL.Rel = redirect.ItemType

	case h.ItemTypes[h.ItemTypeUpdate]:
		redirect.ItemType = h.ItemTypeUpdate
		redirect.URL.Href = h.APITypeUpdate
		redirect.URL.Rel = redirect.ItemType

	default:
		redirect.Status = http.StatusNotFound
		return redirect
	}

	// Append offset if applicable
	if canPaginate && redirect.Offset > 0 {
		url, err := url.Parse(redirect.URL.Href)
		if err != nil {
			redirect.Status = http.StatusNotFound
			return redirect
		}

		q := url.Query()
		q.Add("offset", strconv.FormatInt(redirect.Offset, 10))
		url.RawQuery = q.Encode()

		redirect.URL.Href = url.String()
	} else {
		redirect.Offset = 0
	}

	redirect.Status = http.StatusMovedPermanently

	return redirect
}
Пример #26
0
// EmbedAllMedia is called after a revision has been created to perform media
// embedding and gets all links in the revision and processes them all
func EmbedAllMedia(revisionID int64) (int, error) {

	db, err := h.GetConnection()
	if err != nil {
		return http.StatusInternalServerError, err
	}

	// Are there any external links in this revision?
	rows, err := db.Query(`--EmbedAllMedia
SELECT l.link_id
      ,l.short_url
      ,l.domain
      ,l.url
      ,l.inner_text
      ,l.created
      ,l.resolved_url
      ,l.resolved
      ,l.hits
  FROM revision_links r
  JOIN links l ON l.link_id = r.link_id
 WHERE r.revision_id = $1
 GROUP BY l.link_id
         ,l.short_url
         ,l.domain
         ,l.url
         ,l.inner_text
         ,l.created
         ,l.resolved_url
         ,l.resolved
         ,l.hits
`,
		revisionID,
	)
	if err != nil {
		return http.StatusInternalServerError,
			fmt.Errorf("Get links failed: %v", err.Error())
	}
	defer rows.Close()

	links := []Link{}
	for rows.Next() {
		link := Link{}
		err = rows.Scan(
			&link.ID,
			&link.ShortURL,
			&link.Domain,
			&link.URL,
			&link.Text,
			&link.Created,
			&link.ResolvedURL,
			&link.Resolved,
			&link.Hits,
		)
		if err != nil {
			return http.StatusInternalServerError,
				fmt.Errorf("Row parsing error: %v", err.Error())
		}

		links = append(links, link)
	}
	err = rows.Err()
	if err != nil {
		return http.StatusInternalServerError,
			fmt.Errorf("Error fetching rows: %v", err.Error())
	}
	rows.Close()

	// Now process each one
	for _, link := range links {
		embedMediaForLink(link, revisionID)
	}

	return http.StatusOK, nil
}
Пример #27
0
func (m *Link) fetchRewriteRule() (RewriteRule, int, error) {
	rewriteRule := RewriteRule{}

	if !m.rewriteRuleMayExist() {
		return rewriteRule, http.StatusOK, nil
	}

	db, err := h.GetConnection()
	if err != nil {
		return rewriteRule, http.StatusInternalServerError, err
	}

	rows, err := db.Query(`
SELECT r.rule_id
      ,r.name As title
      ,r.match_regex
      ,r.replace_regex
      ,r.is_enabled
      ,r.sequence
  FROM rewrite_domains d
       JOIN rewrite_domain_rules dr ON dr.domain_id = d.domain_id
       JOIN rewrite_rules r ON r.rule_id = dr.rule_id
 WHERE r.is_enabled IS NOT FALSE
   AND $1 ~ d.domain_regex
 ORDER BY r.sequence`,
		m.Domain,
	)
	if err != nil {
		return rewriteRule, http.StatusInternalServerError,
			fmt.Errorf("Get links failed: %+v", err)
	}
	defer rows.Close()

	rules := []RewriteRule{}
	for rows.Next() {
		rule := RewriteRule{}
		err = rows.Scan(
			&rule.ID,
			&rule.Title,
			&rule.RegexMatch,
			&rule.RegexReplace,
			&rule.Enabled,
			&rule.Sequence,
		)
		if err != nil {
			return rewriteRule, http.StatusInternalServerError,
				fmt.Errorf("Row parsing error: %+v", err)
		}

		rules = append(rules, rule)
	}
	err = rows.Err()
	if err != nil {
		return rewriteRule, http.StatusInternalServerError,
			fmt.Errorf("Error fetching rows: %+v", err)
	}
	rows.Close()

	for _, rule := range rules {
		matched, err := regexp.Match(`(?i)`+rule.RegexMatch, []byte(m.URL))
		if err != nil {
			glog.Errorf("%s %+v", "regexp.Compile(rule.RegexMatch)", err)
			continue
		}

		if matched {
			rule.Valid = true

			return rule, http.StatusOK, nil
		}
	}

	return rewriteRule, http.StatusOK, nil
}
Пример #28
0
// GetUpdates retieves the list of updates for the given profile
func GetUpdates(
	siteID int64,
	profileID int64,
	limit int64,
	offset int64,
) (
	[]UpdateType,
	int64,
	int64,
	int,
	error,
) {

	db, err := h.GetConnection()
	if err != nil {
		glog.Errorf("h.GetConnection() %+v", err)
		return []UpdateType{}, 0, 0, http.StatusInternalServerError, err
	}

	sqlQuery := `--GetUpdates
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
      ,update_id
      ,for_profile_id
      ,update_type_id
      ,item_type_id
      ,item_id
      ,created_by
      ,created
      ,site_id
      ,has_unread(COALESCE(parent_item_type_id, item_type_id), COALESCE(parent_item_id, item_id), $2)
  FROM (
          SELECT COUNT(*) OVER() AS total
                ,rollup.update_id
                ,rollup.for_profile_id
                ,rollup.update_type_id
                ,rollup.item_type_id
                ,rollup.item_id
                ,rollup.created_by
                ,rollup.created
                ,rollup.site_id
                ,f.parent_item_type_id
                ,f.parent_item_id
            FROM flags f
                 JOIN (
                          -- 1;'new_comment';'When a comment has been posted in an item you are watching'
                          -- 4;'new_comment_in_huddle';'When you receive a new comment in a private message'
                          SELECT u.update_id
                                ,u.for_profile_id
                                ,u.update_type_id
                                ,u.item_type_id
                                ,u.item_id
                                ,u.created_by
                                ,u.created
                                ,$1 AS site_id
                            FROM updates u
                                 JOIN (
                                          SELECT MAX(u.update_id) AS update_id
                                                ,f.parent_item_type_id AS item_type_id
                                                ,f.parent_item_id AS item_id
                                            FROM updates u
                                                 JOIN flags f ON f.item_type_id = u.item_type_id
                                                             AND f.item_id = u.item_id
                                            LEFT JOIN ignores i ON i.profile_id = $2
                                                               AND (
                                                                       (i.item_type_id = 3 AND i.item_id = u.created_by)
                                                                    OR (i.item_type_id = f.parent_item_type_id AND i.item_id = f.parent_item_id)
                                                                   )
                                            LEFT JOIN huddle_profiles hp ON hp.huddle_id = f.parent_item_id
                                                                        AND hp.profile_id = u.for_profile_id
                                                                        AND f.parent_item_type_id = 5
                                           WHERE u.for_profile_id = $2
                                             AND i.profile_id IS NULL
                                             AND u.update_type_id IN (1, 4)
                                             AND f.microcosm_is_deleted IS NOT TRUE
                                             AND f.microcosm_is_moderated IS NOT TRUE
                                             AND f.item_is_deleted IS NOT TRUE
                                             AND f.item_is_moderated IS NOT TRUE
                                             AND f.parent_is_deleted IS NOT TRUE
                                             AND f.parent_is_moderated IS NOT TRUE
                                             AND (
                                                     f.microcosm_id IN (SELECT microcosm_id FROM m)
                                                  OR hp.profile_id = u.for_profile_id
                                                 )
                                           GROUP BY f.parent_item_type_id
                                                   ,f.parent_item_id
                                                   ,f.site_id
                                      ) r ON r.update_id = u.update_id
                                 JOIN watchers w ON w.profile_id = $2
                                                AND w.item_type_id = r.item_type_id
                                                AND w.item_id = r.item_id
                           UNION
                          -- 2;'reply_to_comment';'When a comment of yours is replied to'
                          -- 3;'mentioned';'When you are @mentioned in a comment'
                          SELECT u.update_id
                                ,u.for_profile_id
                                ,u.update_type_id
                                ,u.item_type_id
                                ,u.item_id
                                ,u.created_by
                                ,u.created
                                ,$1 AS site_id
                            FROM updates u
                           WHERE update_id IN (
                                     SELECT MAX(u.update_id)
                                       FROM updates u
                                            JOIN flags f ON f.item_type_id = u.item_type_id
                                                        AND f.item_id = u.item_id
                                            LEFT JOIN huddle_profiles hp ON hp.huddle_id = f.parent_item_id
                                                                        AND hp.profile_id = u.for_profile_id
                                                                        AND f.parent_item_type_id = 5
                                            LEFT JOIN ignores i ON i.profile_id = $2
                                                               AND (
                                                                       (i.item_type_id = 3 AND i.item_id = u.created_by)
                                                                    OR (i.item_type_id = f.parent_item_type_id AND i.item_id = f.parent_item_id)
                                                                   )
                                      WHERE u.for_profile_id = $2
                                        AND i.profile_id IS NULL
                                        AND (u.update_type_id = 2 OR u.update_type_id = 3) -- replies (2) & mentions (3)
                                        AND f.site_id = $1
                                        AND f.microcosm_is_deleted IS NOT TRUE
                                        AND f.microcosm_is_moderated IS NOT TRUE
                                        AND f.item_is_deleted IS NOT TRUE
                                        AND f.item_is_moderated IS NOT TRUE
                                        AND f.parent_is_deleted IS NOT TRUE
                                        AND f.parent_is_moderated IS NOT TRUE
                                        AND (
                                                f.microcosm_id IN (SELECT microcosm_id FROM m)
                                             OR hp.profile_id = u.for_profile_id
                                            )
                                      GROUP BY u.update_type_id
                                              ,u.item_type_id
                                              ,u.item_id
                                     )
                           UNION
                          -- 8;'new_item';'When a new item is created in a microcosm you are watching'
                          SELECT u.update_id
                                ,u.for_profile_id
                                ,u.update_type_id
                                ,u.item_type_id
                                ,u.item_id
                                ,u.created_by
                                ,u.created
                                ,$1 AS site_id
                            FROM updates u
                           WHERE update_id IN (
                                     SELECT MAX(u.update_id)
                                       FROM updates u
                                            JOIN flags f ON f.item_type_id = u.item_type_id
                                                        AND f.item_id = u.item_id
                                                        AND f.microcosm_id IN (SELECT microcosm_id FROM m)
                                            JOIN watchers w ON w.profile_id = $2
                                                           AND w.item_type_id = 2
                                                           AND w.item_id = f.microcosm_id
                                            LEFT JOIN ignores i ON i.profile_id = $2
                                                               AND i.item_type_id = 3
                                                               AND i.item_id = u.created_by
                                      WHERE u.for_profile_id = $2
                                        AND i.profile_id IS NULL
                                        AND u.update_type_id = 8
                                        AND f.microcosm_is_deleted IS NOT TRUE
                                        AND f.microcosm_is_moderated IS NOT TRUE
                                        AND f.item_is_deleted IS NOT TRUE
                                        AND f.item_is_moderated IS NOT TRUE
                                        AND f.parent_is_deleted IS NOT TRUE
                                        AND f.parent_is_moderated IS NOT TRUE
                                      GROUP BY u.item_type_id, u.item_id
                                 )
                          ) AS rollup ON rollup.item_type_id = f.item_type_id
                                     AND rollup.item_id = f.item_id
           ORDER BY created DESC
           LIMIT $3
          OFFSET $4
          ) final_rollup`

	rows, err := db.Query(sqlQuery, siteID, profileID, limit, offset)
	if err != nil {
		glog.Errorf(
			"db.Query(%d, %d, %d, %d) %+v",
			profileID,
			siteID,
			limit,
			offset,
			err,
		)
		return []UpdateType{}, 0, 0, http.StatusInternalServerError,
			fmt.Errorf("Database query failed")
	}
	defer rows.Close()

	var total int64
	ems := []UpdateType{}
	for rows.Next() {
		var unread bool
		m := UpdateType{}
		err = rows.Scan(
			&total,
			&m.ID,
			&m.ForProfileID,
			&m.UpdateTypeID,
			&m.ItemTypeID,
			&m.ItemID,
			&m.Meta.CreatedByID,
			&m.Meta.Created,
			&m.SiteID,
			&unread,
		)
		if err != nil {
			glog.Errorf("rows.Scan() %+v", err)
			return []UpdateType{}, 0, 0, http.StatusInternalServerError,
				fmt.Errorf("Row parsing error")
		}

		itemType, err := h.GetItemTypeFromInt(m.ItemTypeID)
		if err != nil {
			glog.Errorf("h.GetItemTypeFromInt(%d) %+v", m.ItemTypeID, err)
			return []UpdateType{}, 0, 0, http.StatusInternalServerError, err
		}
		m.ItemType = itemType

		m.Meta.Flags.Unread = unread

		ems = append(ems, m)
	}
	err = rows.Err()
	if err != nil {
		glog.Errorf("rows.Err() %+v", err)
		return []UpdateType{}, 0, 0, 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 []UpdateType{}, 0, 0, http.StatusBadRequest,
			fmt.Errorf("not enough records, "+
				"offset (%d) would return an empty page.", offset)
	}

	// Get the first round of summaries
	var wg1 sync.WaitGroup
	chan1 := make(chan SummaryContainerRequest)
	defer close(chan1)

	seq := 0
	for i := 0; i < len(ems); i++ {
		go HandleSummaryContainerRequest(
			siteID,
			h.ItemTypes[h.ItemTypeProfile],
			ems[i].Meta.CreatedByID,
			ems[i].ForProfileID,
			seq,
			chan1,
		)
		wg1.Add(1)
		seq++

		go HandleSummaryContainerRequest(
			siteID,
			ems[i].ItemTypeID,
			ems[i].ItemID,
			ems[i].ForProfileID,
			seq,
			chan1,
		)
		wg1.Add(1)
		seq++

		updateType, status, err := GetUpdateType(ems[i].UpdateTypeID)
		if err != nil {
			return []UpdateType{}, 0, 0, status, err
		}
		ems[i].UpdateType = updateType.Title
	}

	resps := []SummaryContainerRequest{}
	for i := 0; i < seq; i++ {
		resp := <-chan1
		wg1.Done()

		resps = append(resps, resp)
	}
	wg1.Wait()

	for _, resp := range resps {
		if resp.Err != nil {
			return []UpdateType{}, 0, 0, resp.Status, resp.Err
		}
	}

	sort.Sort(SummaryContainerRequestsBySeq(resps))

	// Insert the first round of summaries, and get the summaries for the
	// comments
	var wg2 sync.WaitGroup
	chan2 := make(chan SummaryContainerRequest)
	defer close(chan2)

	seq = 0
	parentSeq := 0
	for i := 0; i < len(ems); i++ {

		ems[i].Meta.CreatedBy = resps[seq].Item.Summary
		seq++

		ems[i].Item = resps[seq].Item.Summary
		seq++

		if ems[i].ItemTypeID == h.ItemTypes[h.ItemTypeComment] {
			comment := ems[i].Item.(CommentSummaryType)

			go HandleSummaryContainerRequest(
				siteID,
				comment.ItemTypeID,
				comment.ItemID,
				ems[i].ForProfileID,
				seq,
				chan2,
			)
			parentSeq++
			wg2.Add(1)
		}
	}

	parentResps := []SummaryContainerRequest{}
	for i := 0; i < parentSeq; i++ {
		resp := <-chan2
		wg2.Done()
		parentResps = append(parentResps, resp)
	}
	wg2.Wait()

	for _, resp := range parentResps {
		if resp.Err != nil {
			return []UpdateType{}, 0, 0, resp.Status, resp.Err
		}
	}

	sort.Sort(SummaryContainerRequestsBySeq(parentResps))

	// Insert the comment summaries, and get the summaries of the items the
	// comments are attached to
	var wg3 sync.WaitGroup
	chan3 := make(chan SummaryContainerRequest)
	defer close(chan3)

	parentSeq = 0
	commentItemSeq := 0
	for i := 0; i < len(ems); i++ {

		if ems[i].ItemTypeID == h.ItemTypes[h.ItemTypeComment] {
			comment := ems[i].Item.(CommentSummaryType)

			go HandleSummaryContainerRequest(
				siteID,
				comment.ItemTypeID,
				comment.ItemID,
				ems[i].ForProfileID,
				commentItemSeq,
				chan3,
			)
			parentSeq++
			commentItemSeq++
			wg3.Add(1)

			ems[i].ParentItemTypeID = comment.ItemTypeID
			parentItemType, err := h.GetMapStringFromInt(
				h.ItemTypes,
				comment.ItemTypeID,
			)
			if err != nil {
				return []UpdateType{}, 0, 0, http.StatusInternalServerError, err
			}
			ems[i].ParentItemType = parentItemType
			ems[i].ParentItemID = comment.ItemID
		}
	}

	commentResps := []SummaryContainerRequest{}
	for i := 0; i < commentItemSeq; i++ {
		resp := <-chan3
		wg3.Done()
		commentResps = append(commentResps, resp)
	}
	wg3.Wait()

	for _, resp := range commentResps {
		if resp.Err != nil {
			return []UpdateType{}, 0, 0, resp.Status, resp.Err
		}
	}

	sort.Sort(SummaryContainerRequestsBySeq(commentResps))

	commentItemSeq = 0
	for i := 0; i < len(ems); i++ {
		if ems[i].ItemTypeID == h.ItemTypes[h.ItemTypeComment] {
			ems[i].ParentItem = commentResps[commentItemSeq].Item.Summary
			commentItemSeq++
		}
	}

	return ems, total, pages, http.StatusOK, nil
}
Пример #29
0
// GetUpdate fetches the detail of a single update for a profile
func GetUpdate(
	siteID int64,
	updateID int64,
	profileID int64,
) (
	UpdateType,
	int,
	error,
) {

	// Try fetching from cache
	mcKey := fmt.Sprintf(mcUpdateKeys[c.CacheDetail], updateID)
	if val, ok := c.Get(mcKey, UpdateType{}); ok {
		m := val.(UpdateType)
		m.FetchSummaries(siteID)
		return m, http.StatusOK, nil
	}

	db, err := h.GetConnection()
	if err != nil {
		return UpdateType{}, http.StatusInternalServerError, err
	}

	var m UpdateType
	err = db.QueryRow(`
SELECT update_id
      ,for_profile_id
      ,update_type_id
      ,item_type_id
      ,item_id
      ,created_by
      ,created
      ,site_id
  FROM updates
 WHERE site_id = $1
   AND update_id = $2
   AND for_profile_id = $3`,
		siteID,
		updateID,
		profileID,
	).Scan(
		&m.ID,
		&m.ForProfileID,
		&m.UpdateTypeID,
		&m.ItemTypeID,
		&m.ItemID,
		&m.Meta.CreatedByID,
		&m.Meta.Created,
		&m.SiteID,
	)
	if err == sql.ErrNoRows {
		return UpdateType{}, http.StatusNotFound,
			fmt.Errorf("Update not found: %v", err.Error())
	} else if err != nil {
		return UpdateType{}, http.StatusInternalServerError,
			fmt.Errorf("Error fetching update: %v", err.Error())
	}

	itemType, err := h.GetItemTypeFromInt(m.ItemTypeID)
	if err != nil {
		return UpdateType{}, http.StatusInternalServerError, err
	}
	m.ItemType = itemType
	m.FetchSummaries(siteID)

	c.Set(mcKey, m, mcTTL)
	return m, http.StatusOK, nil
}
Пример #30
0
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

}