// 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
}
// GetHuddle returns a huddle
func GetHuddle(
siteID int64,
profileID int64,
id int64,
) (
HuddleType,
int,
error,
) {
// Get from cache if it's available
mcKey := fmt.Sprintf(mcHuddleKeys[c.CacheDetail], id)
if val, ok := c.Get(mcKey, HuddleType{}); ok {
m := val.(HuddleType)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
return HuddleType{}, http.StatusInternalServerError, err
}
rows, err := db.Query(`
SELECT h.huddle_id
,h.title
,h.created
,h.created_by
,is_confidential
,hp.profile_id
FROM huddles h
JOIN huddle_profiles hp ON h.huddle_id = hp.huddle_id
WHERE h.site_id = $1
AND h.huddle_id = $2`,
siteID,
id,
)
if err != nil {
return HuddleType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
defer rows.Close()
var m HuddleType
m = HuddleType{}
for rows.Next() {
var participantID int64
err = rows.Scan(
&m.ID,
&m.Title,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.IsConfidential,
&participantID,
)
if err != nil {
return HuddleType{}, http.StatusInternalServerError,
fmt.Errorf("Row parsing error: %v", err.Error())
}
m.Participants = append(
m.Participants,
ProfileSummaryType{ID: participantID},
)
}
err = rows.Err()
if err != nil {
return HuddleType{}, http.StatusInternalServerError,
fmt.Errorf("Error fetching rows: %v", err.Error())
}
rows.Close()
if m.ID == 0 {
return HuddleType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", id)
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeHuddle, m.ID),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// GetAttendee returns an attendee
func GetAttendee(siteID int64, id int64) (AttendeeType, int, error) {
// Get from cache if it's available
mcKey := fmt.Sprintf(mcAttendeeKeys[c.CacheDetail], id)
if val, ok := c.Get(mcKey, AttendeeType{}); ok {
m := val.(AttendeeType)
m.FetchProfileSummaries(siteID)
return m, 0, nil
}
// Open db connection and retrieve resource
db, err := h.GetConnection()
if err != nil {
glog.Errorf("h.GetConnection() %+v", err)
return AttendeeType{}, http.StatusInternalServerError, err
}
var m AttendeeType
err = db.QueryRow(`
SELECT attendee_id
,event_id
,profile_id
,created
,created_by
,edited
,edited_by
,edit_reason
,state_id
,state_date
FROM attendees
WHERE attendee_id = $1`,
id,
).Scan(
&m.ID,
&m.EventID,
&m.ProfileID,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.EditedNullable,
&m.Meta.EditedByNullable,
&m.Meta.EditReasonNullable,
&m.RSVPID,
&m.RSVPd,
)
if err == sql.ErrNoRows {
return AttendeeType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", id)
} else if err != nil {
glog.Errorf("db.QueryRow(%d) %+v", id, err)
return AttendeeType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed")
}
if m.Meta.EditReasonNullable.Valid {
m.Meta.EditReason = m.Meta.EditReasonNullable.String
}
if m.Meta.EditedNullable.Valid {
m.Meta.Edited = m.Meta.EditedNullable.Time.Format(time.RFC3339Nano)
}
if m.RSVPd.Valid {
m.RSVPdOn = m.RSVPd.Time.Format(time.RFC3339Nano)
}
m.RSVP, err = h.GetMapStringFromInt(RSVPStates, m.RSVPID)
if err != nil {
return AttendeeType{}, http.StatusInternalServerError, err
}
m.Meta.Links = []h.LinkType{
h.GetExtendedLink(
"self",
"",
h.ItemTypeAttendee,
m.EventID,
m.ProfileID,
),
h.GetLink("profile", "", h.ItemTypeProfile, m.ProfileID),
h.GetLink("event", "", h.ItemTypeEvent, m.EventID),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// GetUser will fetch a user for a given ID
func GetUser(id int64) (UserType, int, error) {
if id == 0 {
return UserType{}, http.StatusNotFound, fmt.Errorf("User not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcProfileKeys[c.CacheUser], id)
if val, ok := c.Get(mcKey, UserType{}); ok {
return val.(UserType), http.StatusOK, nil
}
db, err := h.GetConnection()
if err != nil {
return UserType{}, http.StatusInternalServerError, err
}
var m UserType
err = db.QueryRow(`
SELECT user_id
,email
,gender
,language
,created
,state
,is_banned
,password
,password_date
,dob_day
,dob_month
,dob_year
FROM users
WHERE user_id = $1`,
id,
).Scan(
&m.ID,
&m.Email,
&m.Gender,
&m.Language,
&m.Created,
&m.State,
&m.Banned,
&m.Password,
&m.PasswordDate,
&m.DobDay,
&m.DobMonth,
&m.DobYear,
)
if err == sql.ErrNoRows {
return UserType{}, http.StatusNotFound,
fmt.Errorf("Resource with id %v not found", id)
} else if err != nil {
return UserType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeUser, m.ID),
}
c.Set(mcKey, m, mcTTL)
return m, http.StatusOK, nil
}
// GetEvent returns an event
func GetEvent(siteID int64, id int64, profileID int64) (EventType, int, error) {
if id == 0 {
return EventType{}, http.StatusNotFound, fmt.Errorf("Event not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcEventKeys[c.CacheDetail], id)
if val, ok := c.Get(mcKey, EventType{}); ok {
m := val.(EventType)
// TODO(buro9) 2014-05-05: We are not verifying that the cached
// event belongs to this siteId
status, err := m.FetchProfileSummaries(siteID)
if err != nil {
glog.Errorf("m.FetchProfileSummaries(%d) %+v", siteID, err)
return EventType{}, status, err
}
status, err = m.GetAttending(profileID)
if err != nil {
glog.Errorf("m.GetAttending(%d) %+v", profileID, err)
return EventType{}, status, err
}
return m, 0, nil
}
// Open db connection and retrieve resource
db, err := h.GetConnection()
if err != nil {
glog.Errorf("h.GetConnection() %+v", err)
return EventType{}, http.StatusInternalServerError, err
}
var m EventType
err = db.QueryRow(`
SELECT e.event_id
,e.microcosm_id
,e.title
,e.created
,e.created_by
,e.edited
,e.edited_by
,e.edit_reason
,e.is_sticky
,e.is_open
,e.is_visible
,e.is_moderated
,e.is_deleted
,e."when"
,e.duration
,e."where"
,e.lat
,e.lon
,e.bounds_north
,e.bounds_east
,e.bounds_south
,e.bounds_west
,e.status
,e.rsvp_limit
,e.rsvp_attending
,e.rsvp_spaces
FROM events e
JOIN flags f ON f.site_id = $2
AND f.item_type_id = 9
AND f.item_id = e.event_id
WHERE e.event_id = $1
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`,
id,
siteID,
).Scan(
&m.ID,
&m.MicrocosmID,
&m.Title,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.EditedNullable,
&m.Meta.EditedByNullable,
&m.Meta.EditReasonNullable,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Visible,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Deleted,
&m.WhenNullable,
&m.Duration,
&m.WhereNullable,
&m.Lat,
&m.Lon,
&m.North,
&m.East,
&m.South,
&m.West,
&m.Status,
&m.RSVPLimit,
&m.RSVPAttending,
&m.RSVPSpaces,
)
if err == sql.ErrNoRows {
return EventType{}, http.StatusNotFound,
fmt.Errorf("Event not found")
} else if err != nil {
glog.Errorf("db.QueryRow(%d) %+v", id, err)
return EventType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed")
}
if m.Meta.EditReasonNullable.Valid {
m.Meta.EditReason = m.Meta.EditReasonNullable.String
}
if m.Meta.EditedNullable.Valid {
m.Meta.Edited = m.Meta.EditedNullable.Time.Format(time.RFC3339Nano)
}
if m.WhenNullable.Valid {
m.When = m.WhenNullable.Time.Format(time.RFC3339Nano)
}
if m.WhereNullable.Valid {
m.Where = m.WhereNullable.String
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeEvent, m.ID),
h.GetLink(
"microcosm",
GetMicrocosmTitle(m.MicrocosmID),
h.ItemTypeMicrocosm,
m.MicrocosmID,
),
}
// Add meta links
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeEvent, m.ID),
h.GetLink(
"microcosm",
GetMicrocosmTitle(m.MicrocosmID),
h.ItemTypeMicrocosm,
m.MicrocosmID,
),
}
// Update cache
c.Set(mcKey, m, mcTTL)
status, err := m.FetchProfileSummaries(siteID)
if err != nil {
glog.Errorf("m.FetchProfileSummaries(%d) %+v", siteID, err)
return EventType{}, status, err
}
status, err = m.GetAttending(profileID)
if err != nil {
glog.Errorf("m.GetAttending(%d) %+v", profileID, err)
return EventType{}, status, err
}
return m, http.StatusOK, nil
}
// GetEventSummary returns a summary of an event
func GetEventSummary(
siteID int64,
id int64,
profileID int64,
) (
EventSummaryType,
int,
error,
) {
if id == 0 {
return EventSummaryType{}, http.StatusNotFound,
fmt.Errorf("Event not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcEventKeys[c.CacheSummary], id)
if val, ok := c.Get(mcKey, EventSummaryType{}); ok {
m := val.(EventSummaryType)
status, err := m.FetchProfileSummaries(siteID)
if err != nil {
glog.Errorf("m.FetchProfileSummaries(%d) %+v", siteID, err)
return EventSummaryType{}, status, err
}
status, err = m.GetAttending(profileID)
if err != nil {
glog.Errorf("m.GetAttending(%d) %+v", profileID, err)
return EventSummaryType{}, status, err
}
return m, http.StatusOK, nil
}
// Open db connection and retrieve resource
db, err := h.GetConnection()
if err != nil {
glog.Errorf("h.GetConnection() %+v", err)
return EventSummaryType{}, http.StatusInternalServerError, err
}
var m EventSummaryType
err = db.QueryRow(`
SELECT event_id
,microcosm_id
,title
,created
,created_by
,is_sticky
,is_open
,is_visible
,is_moderated
,is_deleted
,"when"
,duration
,"where"
,lat
,lon
,bounds_north
,bounds_east
,bounds_south
,bounds_west
,status
,rsvp_limit
,rsvp_attending
,rsvp_spaces
,(SELECT COUNT(*) AS total_comments
FROM flags
WHERE parent_item_type_id = 9
AND parent_item_id = $1
AND item_is_deleted IS NOT TRUE
AND item_is_moderated IS NOT TRUE) AS comment_count
,view_count
FROM events
WHERE event_id = $1
AND is_deleted(9, event_id) IS FALSE`,
id,
).Scan(
&m.ID,
&m.MicrocosmID,
&m.Title,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Visible,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Deleted,
&m.WhenNullable,
&m.Duration,
&m.WhereNullable,
&m.Lat,
&m.Lon,
&m.North,
&m.East,
&m.South,
&m.West,
&m.Status,
&m.RSVPLimit,
&m.RSVPAttending,
&m.RSVPSpaces,
&m.CommentCount,
&m.ViewCount,
)
if err == sql.ErrNoRows {
return EventSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Event with ID %d not found", id)
} else if err != nil {
glog.Errorf("db.QueryRow(%d, %d) %+v", siteID, id, err)
return EventSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed")
}
if m.WhenNullable.Valid {
m.When = m.WhenNullable.Time.Format(time.RFC3339Nano)
}
if m.WhereNullable.Valid {
m.Where = m.WhereNullable.String
}
lastComment, status, err :=
GetLastComment(h.ItemTypes[h.ItemTypeEvent], m.ID)
if err != nil {
return EventSummaryType{}, status,
fmt.Errorf("Error fetching last comment: %v", err.Error())
}
if lastComment.Valid {
m.LastComment = lastComment
}
// Add meta links
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeEvent, m.ID),
h.GetLink(
"microcosm",
GetMicrocosmTitle(m.MicrocosmID),
h.ItemTypeMicrocosm, m.MicrocosmID,
),
}
// Update cache
c.Set(mcKey, m, mcTTL)
status, err = m.FetchProfileSummaries(siteID)
if err != nil {
glog.Errorf("m.FetchProfileSummaries(%d) %+v", siteID, err)
return EventSummaryType{}, status, err
}
status, err = m.GetAttending(profileID)
if err != nil {
glog.Errorf("m.GetAttending(%d) %+v", profileID, err)
return EventSummaryType{}, status, err
}
return m, http.StatusOK, nil
}
// GetPoll fetches a poll
func GetPoll(siteID int64, id int64, profileID int64) (PollType, int, error) {
// Get from cache if it's available
mcKey := fmt.Sprintf(mcPollKeys[c.CacheDetail], id)
if val, ok := c.Get(mcKey, PollType{}); ok {
m := val.(PollType)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
return PollType{}, http.StatusInternalServerError, err
}
// TODO(buro9): admins and mods could see this with isDeleted=true in the querystring
var m PollType
err = db.QueryRow(`
SELECT p.poll_id
,p.microcosm_id
,p.title
,p.question
,p.created
,p.created_by
,p.edited
,p.edited_by
,p.edit_reason
,p.voter_count
,p.voting_ends
,p.is_sticky
,p.is_open
,p.is_deleted
,p.is_moderated
,p.is_visible
,p.is_poll_open
,p.is_multiple_choice
FROM polls p
,microcosms m
WHERE p.microcosm_id = m.microcosm_id
AND m.site_id = $1
AND m.is_deleted IS NOT TRUE
AND m.is_moderated IS NOT TRUE
AND p.poll_id = $2
AND p.is_deleted IS NOT TRUE
AND p.is_moderated IS NOT TRUE`,
siteID,
id,
).Scan(
&m.ID,
&m.MicrocosmID,
&m.Title,
&m.PollQuestion,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.EditedNullable,
&m.Meta.EditedByNullable,
&m.Meta.EditReasonNullable,
&m.VoterCount,
&m.VotingEndsNullable,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Deleted,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Visible,
&m.PollOpen,
&m.Multi,
)
if err == sql.ErrNoRows {
return PollType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", id)
} else if err != nil {
return PollType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
if m.Meta.EditReasonNullable.Valid {
m.Meta.EditReason = m.Meta.EditReasonNullable.String
}
if m.Meta.EditedNullable.Valid {
m.Meta.Edited = m.Meta.EditedNullable.Time.Format(time.RFC3339Nano)
}
if m.VotingEndsNullable.Valid {
m.VotingEnds = m.VotingEndsNullable.Time.Format(time.RFC3339Nano)
}
rows2, err := db.Query(`
SELECT choice_id,
title,
vote_count,
voter_count,
sequence
FROM choices
WHERE poll_id = $1
ORDER BY sequence ASC`,
m.ID,
)
if err != nil {
return PollType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
defer rows2.Close()
var choices []PollChoiceType
for rows2.Next() {
choice := PollChoiceType{}
err = rows2.Scan(
&choice.ID,
&choice.Choice,
&choice.Votes,
&choice.VoterCount,
&choice.Order,
)
choices = append(choices, choice)
if err != nil {
return PollType{}, http.StatusInternalServerError,
fmt.Errorf("Row parsing error: %v", err.Error())
}
}
err = rows2.Err()
if err != nil {
return PollType{}, http.StatusInternalServerError,
fmt.Errorf("Error fetching rows2: %v", err.Error())
}
rows2.Close()
m.Choices = choices
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypePoll, m.ID),
h.GetLink(
"microcosm",
GetMicrocosmTitle(m.MicrocosmID),
h.ItemTypeMicrocosm,
m.MicrocosmID,
),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// GetProfile fetches a single profile
func GetProfile(siteID int64, id int64) (ProfileType, int, error) {
if id == 0 {
return ProfileType{}, http.StatusNotFound,
fmt.Errorf("Profile not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcProfileKeys[c.CacheDetail], id)
if val, ok := c.Get(mcKey, ProfileType{}); ok {
m := val.(ProfileType)
if m.SiteID != siteID {
return ProfileType{}, http.StatusNotFound, fmt.Errorf("Not found")
}
return m, http.StatusOK, nil
}
db, err := h.GetConnection()
if err != nil {
glog.Error(err)
return ProfileType{}, http.StatusInternalServerError, err
}
var m ProfileType
var profileCommentID int64
err = db.QueryRow(`--GetProfile
SELECT p.profile_id
,p.site_id
,p.user_id
,p.profile_name
,p.gender
,p.is_visible
,si.item_count
,p.comment_count
,COALESCE(
(SELECT item_id
FROM flags
WHERE item_type_id = 4
AND parent_item_type_id = 3
AND parent_item_id = p.profile_id
AND microcosm_is_deleted IS NOT TRUE
AND microcosm_is_moderated IS NOT TRUE
AND parent_is_deleted IS NOT TRUE
AND parent_is_moderated IS NOT TRUE
AND item_is_deleted IS NOT TRUE
AND item_is_moderated IS NOT TRUE
ORDER BY last_modified DESC
LIMIT 1),
0
) as profile_comment_id
,p.created
,p.last_active
,p.avatar_url
,p.avatar_id
FROM profiles p,
(
SELECT COUNT(*) as item_count
FROM flags
WHERE site_id = $1
AND created_by = $2
AND item_type_id = 6
AND item_is_deleted IS NOT TRUE
AND item_is_moderated IS NOT TRUE
AND parent_is_deleted IS NOT TRUE
AND parent_is_moderated IS NOT TRUE
AND microcosm_is_deleted IS NOT TRUE
AND microcosm_is_moderated IS NOT TRUE
) AS si
WHERE p.site_id = $1
AND p.profile_id = $2`,
siteID,
id,
).Scan(
&m.ID,
&m.SiteID,
&m.UserID,
&m.ProfileName,
&m.GenderNullable,
&m.Visible,
&m.ItemCount,
&m.CommentCount,
&profileCommentID,
&m.Created,
&m.LastActive,
&m.AvatarURLNullable,
&m.AvatarIDNullable,
)
if err == sql.ErrNoRows {
return ProfileType{}, http.StatusNotFound,
fmt.Errorf("Resource with profile ID %d not found", id)
} else if err != nil {
glog.Error(err)
return ProfileType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
if m.GenderNullable.Valid {
m.Gender = m.GenderNullable.String
}
if m.AvatarIDNullable.Valid {
m.AvatarID = m.AvatarIDNullable.Int64
}
if m.AvatarURLNullable.Valid {
m.AvatarURL = m.AvatarURLNullable.String
}
if profileCommentID > 0 {
comment, status, err := GetCommentSummary(siteID, profileCommentID)
if err != nil {
glog.Error(err)
return ProfileType{}, status, err
}
m.ProfileComment = comment
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeProfile, m.ID),
h.GetLink("site", "", h.ItemTypeSite, m.SiteID),
}
// Update cache
c.Set(mcKey, m, mcTTL)
return m, http.StatusOK, nil
}
// GetConversation fetches a conversation
func GetConversation(
siteID int64,
id int64,
profileID int64,
) (
ConversationType,
int,
error,
) {
if id == 0 {
return ConversationType{}, http.StatusNotFound,
fmt.Errorf("Conversation not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcConversationKeys[c.CacheDetail], id)
if val, ok := c.Get(mcKey, ConversationType{}); ok {
m := val.(ConversationType)
// TODO(buro9) 2014-05-05: We are not verifying that the cached
// conversation belongs to this siteId
m.FetchSummaries(siteID)
return m, http.StatusOK, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
glog.Errorf("h.GetConnection() %+v", err)
return ConversationType{}, http.StatusInternalServerError, err
}
// TODO(buro9): admins and mods could see this with isDeleted=true in the
// querystring
var m ConversationType
err = db.QueryRow(`--GetConversation
SELECT c.conversation_id
,c.microcosm_id
,c.title
,c.created
,c.created_by
,c.edited
,c.edited_by
,c.edit_reason
,c.is_sticky
,c.is_open
,c.is_deleted
,c.is_moderated
,c.is_visible
FROM conversations c
JOIN flags f ON f.site_id = $2
AND f.item_type_id = 6
AND f.item_id = c.conversation_id
WHERE c.conversation_id = $1
AND is_deleted(6, c.conversation_id) IS FALSE`,
id,
siteID,
).Scan(
&m.ID,
&m.MicrocosmID,
&m.Title,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.EditedNullable,
&m.Meta.EditedByNullable,
&m.Meta.EditReasonNullable,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Deleted,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Visible,
)
if err == sql.ErrNoRows {
glog.Warningf("Conversation not found for id %d", id)
return ConversationType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", id)
} else if err != nil {
glog.Errorf("db.Query(%d) %+v", id, err)
return ConversationType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed")
}
if m.Meta.EditReasonNullable.Valid {
m.Meta.EditReason = m.Meta.EditReasonNullable.String
}
if m.Meta.EditedNullable.Valid {
m.Meta.Edited =
m.Meta.EditedNullable.Time.Format(time.RFC3339Nano)
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeConversation, m.ID),
h.GetLink(
"microcosm",
GetMicrocosmTitle(m.MicrocosmID),
h.ItemTypeMicrocosm,
m.MicrocosmID,
),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchSummaries(siteID)
return m, http.StatusOK, nil
}
// GetConversationSummary fetches a summary of a conversation
func GetConversationSummary(
siteID int64,
id int64,
profileID int64,
) (
ConversationSummaryType,
int,
error,
) {
// Get from cache if it's available
mcKey := fmt.Sprintf(mcConversationKeys[c.CacheSummary], id)
if val, ok := c.Get(mcKey, ConversationSummaryType{}); ok {
m := val.(ConversationSummaryType)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
return ConversationSummaryType{}, http.StatusInternalServerError, err
}
// TODO(buro9): admins and mods could see this with isDeleted=true in the
// querystring
var m ConversationSummaryType
err = db.QueryRow(`--GetConversationSummary
SELECT conversation_id
,microcosm_id
,title
,created
,created_by
,is_sticky
,is_open
,is_deleted
,is_moderated
,is_visible
,(SELECT COUNT(*) AS total_comments
FROM flags
WHERE parent_item_type_id = 6
AND parent_item_id = $1
AND microcosm_is_deleted IS NOT TRUE
AND microcosm_is_moderated IS NOT TRUE
AND parent_is_deleted IS NOT TRUE
AND parent_is_moderated IS NOT TRUE
AND item_is_deleted IS NOT TRUE
AND item_is_moderated IS NOT TRUE) AS comment_count
,view_count
FROM conversations
WHERE conversation_id = $1
AND is_deleted(6, $1) IS FALSE`,
id,
).Scan(
&m.ID,
&m.MicrocosmID,
&m.Title,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Deleted,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Visible,
&m.CommentCount,
&m.ViewCount,
)
if err == sql.ErrNoRows {
return ConversationSummaryType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", id)
} else if err != nil {
return ConversationSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
lastComment, status, err :=
GetLastComment(h.ItemTypes[h.ItemTypeConversation], m.ID)
if err != nil {
return ConversationSummaryType{}, status,
fmt.Errorf("Error fetching last comment: %v", err.Error())
}
if lastComment.Valid {
m.LastComment = lastComment
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeConversation, m.ID),
h.GetLink(
"microcosm",
GetMicrocosmTitle(m.MicrocosmID),
h.ItemTypeMicrocosm,
m.MicrocosmID,
),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// GetMicrocosmSummary fetches a summary of a microcosm
func GetMicrocosmSummary(
siteID int64,
id int64,
profileID int64,
) (
MicrocosmSummaryType,
int,
error,
) {
if id == 0 {
return MicrocosmSummaryType{}, http.StatusNotFound,
fmt.Errorf("Microcosm not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcMicrocosmKeys[c.CacheSummary], id)
if val, ok := c.Get(mcKey, MicrocosmSummaryType{}); ok {
m := val.(MicrocosmSummaryType)
if m.SiteID != siteID {
return MicrocosmSummaryType{}, http.StatusNotFound,
fmt.Errorf("Not found")
}
m.FetchSummaries(siteID)
return m, http.StatusOK, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
glog.Error(err)
return MicrocosmSummaryType{}, http.StatusInternalServerError, err
}
// TODO(buro9): admins and mods could see this with isDeleted=true in the querystring
var m MicrocosmSummaryType
err = db.QueryRow(`--GetMicrocosmSummary
SELECT microcosm_id
,site_id
,visibility
,title
,description
,created
,created_by
,is_sticky
,is_open
,is_deleted
,is_moderated
,is_visible
,item_count
,comment_count
FROM microcosms
WHERE site_id = $1
AND microcosm_id = $2
AND is_deleted IS NOT TRUE
AND is_moderated IS NOT TRUE`,
siteID,
id,
).Scan(
&m.ID,
&m.SiteID,
&m.Visibility,
&m.Title,
&m.Description,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Deleted,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Visible,
&m.ItemCount,
&m.CommentCount,
)
if err == sql.ErrNoRows {
glog.Warning(err)
return MicrocosmSummaryType{},
http.StatusNotFound,
fmt.Errorf("Microcosm with ID %d not found", id)
} else if err != nil {
glog.Error(err)
return MicrocosmSummaryType{},
http.StatusInternalServerError,
fmt.Errorf("Database query failed")
}
mru, status, err := GetMostRecentItem(siteID, m.ID, profileID)
if err != nil {
glog.Error(err)
return MicrocosmSummaryType{}, status, fmt.Errorf("Row parsing error")
}
if mru.Valid {
m.MRU = mru
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeMicrocosm, m.ID),
h.GetLink("site", "", h.ItemTypeSite, m.SiteID),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchSummaries(siteID)
return m, http.StatusOK, nil
}
// GetMicrocosm fetches a microcosm
func GetMicrocosm(
siteID int64,
id int64,
profileID int64,
) (
MicrocosmType,
int,
error,
) {
if id == 0 {
return MicrocosmType{}, http.StatusNotFound,
fmt.Errorf("Microcosm not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcMicrocosmKeys[c.CacheDetail], id)
if val, ok := c.Get(mcKey, MicrocosmType{}); ok {
m := val.(MicrocosmType)
if m.SiteID != siteID {
return MicrocosmType{}, http.StatusNotFound, fmt.Errorf("Not found")
}
m.FetchSummaries(siteID, profileID)
return m, 0, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
return MicrocosmType{}, http.StatusInternalServerError, err
}
// TODO(buro9): admins and mods could see this with isDeleted=true in the querystring
var m MicrocosmType
err = db.QueryRow(`--GetMicrocosm
SELECT microcosm_id,
site_id,
visibility,
title,
description,
created,
created_by,
edited,
edited_by,
edit_reason,
is_sticky,
is_open,
is_deleted,
is_moderated,
is_visible
FROM microcosms
WHERE site_id = $1
AND microcosm_id = $2
AND is_deleted IS NOT TRUE
AND is_moderated IS NOT TRUE`,
siteID,
id,
).Scan(
&m.ID,
&m.SiteID,
&m.Visibility,
&m.Title,
&m.Description,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.Meta.EditedNullable,
&m.Meta.EditedByNullable,
&m.Meta.EditReasonNullable,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Deleted,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Visible,
)
if err == sql.ErrNoRows {
return MicrocosmType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", id)
} else if err != nil {
return MicrocosmType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
if m.Meta.EditReasonNullable.Valid {
m.Meta.EditReason = m.Meta.EditReasonNullable.String
}
if m.Meta.EditedNullable.Valid {
m.Meta.Edited =
m.Meta.EditedNullable.Time.Format(time.RFC3339Nano)
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeMicrocosm, m.ID),
h.GetLink("site", "", h.ItemTypeSite, m.SiteID),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchSummaries(siteID, profileID)
return m, http.StatusOK, nil
}
// GetProfileSummary fetches a summary of a profile
func GetProfileSummary(
siteID int64,
id int64,
) (
ProfileSummaryType,
int,
error,
) {
if id == 0 {
return ProfileSummaryType{}, http.StatusNotFound,
fmt.Errorf("Profile not found")
}
// Get from cache if it's available
mcKey := fmt.Sprintf(mcProfileKeys[c.CacheSummary], id)
if val, ok := c.Get(mcKey, ProfileSummaryType{}); ok {
m := val.(ProfileSummaryType)
if m.SiteID != siteID {
return ProfileSummaryType{}, http.StatusNotFound,
fmt.Errorf("Not found")
}
return m, http.StatusOK, nil
}
db, err := h.GetConnection()
if err != nil {
glog.Error(err)
return ProfileSummaryType{}, http.StatusInternalServerError, err
}
var m ProfileSummaryType
err = db.QueryRow(`--GetProfileSummary
SELECT profile_id
,site_id
,user_id
,profile_name
,is_visible
,avatar_url
,avatar_id
FROM profiles
WHERE site_id = $1
AND profile_id = $2`,
siteID,
id,
).Scan(
&m.ID,
&m.SiteID,
&m.UserID,
&m.ProfileName,
&m.Visible,
&m.AvatarURLNullable,
&m.AvatarIDNullable,
)
if err == sql.ErrNoRows {
glog.Warning(err)
return ProfileSummaryType{}, http.StatusNotFound,
fmt.Errorf("Resource with profile ID %d not found", id)
} else if err != nil {
glog.Error(err)
return ProfileSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
if m.AvatarIDNullable.Valid {
m.AvatarID = m.AvatarIDNullable.Int64
}
if m.AvatarURLNullable.Valid {
m.AvatarURL = m.AvatarURLNullable.String
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeProfile, m.ID),
h.GetLink("site", "", h.ItemTypeSite, m.SiteID),
}
// Update cache
c.Set(mcKey, m, mcTTL)
return m, http.StatusOK, nil
}
// GetHuddleSummary returns a summary of a huddle
func GetHuddleSummary(
siteID int64,
profileID int64,
id int64,
) (
HuddleSummaryType,
int,
error,
) {
// Get from cache if it's available
mcKey := fmt.Sprintf(mcHuddleKeys[c.CacheSummary], id)
if val, ok := c.Get(mcKey, HuddleSummaryType{}); ok {
m := val.(HuddleSummaryType)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
glog.Errorf("h.GetConnection() %+v", err)
return HuddleSummaryType{}, http.StatusInternalServerError, err
}
rows, err := db.Query(`
SELECT h.huddle_id
,h.title
,h.created
,h.created_by
,hp.profile_id
,lc.comment_id
,lc.created
,lc.profile_id
,(SELECT COUNT(*) AS total_comments
FROM flags
WHERE parent_item_type_id = 5
AND parent_item_id = $2
AND site_id = $1
AND microcosm_is_deleted IS NOT TRUE
AND microcosm_is_moderated IS NOT TRUE
AND parent_is_deleted IS NOT TRUE
AND parent_is_moderated IS NOT TRUE
AND item_is_deleted IS NOT TRUE
AND item_is_moderated IS NOT TRUE) AS comment_count
FROM huddles h
LEFT OUTER JOIN (
SELECT c.created
,c.comment_id
,c.profile_id
,c.item_id
FROM comments c
JOIN revisions r ON c.comment_id = r.comment_id
WHERE r.is_current
AND c.is_deleted IS NOT TRUE
AND c.item_type_id = $4
AND c.item_id = $2
ORDER BY 1 DESC, 2 DESC
LIMIT 1
) lc ON lc.item_id = h.huddle_id
,huddle_profiles hp
,(
SELECT *
FROM huddle_profiles
WHERE huddle_id = $2
AND profile_id = $3
) AS hp2
WHERE h.site_id = $1
AND h.huddle_id = $2
AND h.huddle_id = hp.huddle_id`,
siteID,
id,
profileID,
h.ItemTypes[h.ItemTypeHuddle],
)
if err != nil {
glog.Errorf(
"db.Query(%d, %d, %d, h.ItemTypes[h.ItemTypeHuddle]) %+v",
siteID, id, profileID, err,
)
return HuddleSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed")
}
defer rows.Close()
var m HuddleSummaryType
m = HuddleSummaryType{}
for rows.Next() {
var participantID int64
err = rows.Scan(
&m.ID,
&m.Title,
&m.Meta.Created,
&m.Meta.CreatedByID,
&participantID,
&m.LastCommentIDNullable,
&m.LastCommentCreatedNullable,
&m.LastCommentCreatedByNullable,
&m.CommentCount,
)
if err != nil {
glog.Errorf("rows.Scan() %+v", err)
return HuddleSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Row parsing error")
}
m.Participants = append(
m.Participants,
ProfileSummaryType{ID: participantID},
)
if m.LastCommentIDNullable.Valid {
m.LastCommentID = m.LastCommentIDNullable.Int64
}
if m.LastCommentCreatedNullable.Valid {
m.LastCommentCreated =
m.LastCommentCreatedNullable.Time.Format(time.RFC3339Nano)
}
}
err = rows.Err()
if err != nil {
glog.Errorf("rows.Err() %+v", err)
return HuddleSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Error fetching rows")
}
rows.Close()
if m.ID == 0 {
glog.Warningf("m.Id == 0")
return HuddleSummaryType{}, http.StatusNotFound,
fmt.Errorf("Resource not found")
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypeHuddle, m.ID),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// GetPollSummary fetches a summary of a poll
func GetPollSummary(
siteID int64,
id int64,
profileID int64,
) (
PollSummaryType,
int,
error,
) {
// Get from cache if it's available
mcKey := fmt.Sprintf(mcPollKeys[c.CacheSummary], id)
if val, ok := c.Get(mcKey, PollSummaryType{}); ok {
m := val.(PollSummaryType)
_, status, err := GetMicrocosmSummary(siteID, m.MicrocosmID, profileID)
if err != nil {
return PollSummaryType{}, status, err
}
m.FetchProfileSummaries(siteID)
return m, 0, nil
}
// Retrieve resource
db, err := h.GetConnection()
if err != nil {
return PollSummaryType{}, http.StatusInternalServerError, err
}
// TODO(buro9): admins and mods could see this with isDeleted=true in the querystring
var m PollSummaryType
err = db.QueryRow(`
SELECT poll_id
,microcosm_id
,title
,question
,created
,created_by
,voting_ends
,is_sticky
,is_open
,is_deleted
,is_moderated
,is_visible
,is_poll_open
,is_multiple_choice
,(SELECT COUNT(*) AS total_comments
FROM flags
WHERE parent_item_type_id = 7
AND parent_item_id = $1
AND item_is_deleted IS NOT TRUE
AND item_is_moderated IS NOT TRUE) AS comment_count
,view_count
FROM polls
WHERE poll_id = $1
AND is_deleted(7, poll_id) IS FALSE`,
id,
).Scan(
&m.ID,
&m.MicrocosmID,
&m.Title,
&m.PollQuestion,
&m.Meta.Created,
&m.Meta.CreatedByID,
&m.VotingEndsNullable,
&m.Meta.Flags.Sticky,
&m.Meta.Flags.Open,
&m.Meta.Flags.Deleted,
&m.Meta.Flags.Moderated,
&m.Meta.Flags.Visible,
&m.PollOpen,
&m.Multi,
&m.CommentCount,
&m.ViewCount,
)
if err == sql.ErrNoRows {
return PollSummaryType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", id)
} else if err != nil {
return PollSummaryType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
if m.VotingEndsNullable.Valid {
m.VotingEnds = m.VotingEndsNullable.Time.Format(time.RFC3339Nano)
}
m.Meta.Links =
[]h.LinkType{
h.GetLink("self", "", h.ItemTypePoll, m.ID),
h.GetLink(
"microcosm",
GetMicrocosmTitle(m.MicrocosmID),
h.ItemTypeMicrocosm,
m.MicrocosmID,
),
}
// Update cache
c.Set(mcKey, m, mcTTL)
m.FetchProfileSummaries(siteID)
return m, http.StatusOK, nil
}
// 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
}
// GetWatcher returns a given watcher
func GetWatcher(watcherID int64, siteID int64) (WatcherType, int, error) {
// Get from cache if it's available
mcKey := fmt.Sprintf(mcWatcherKeys[c.CacheDetail], watcherID)
if val, ok := c.Get(mcKey, WatcherType{}); ok {
return val.(WatcherType), http.StatusOK, nil
}
db, err := h.GetConnection()
if err != nil {
glog.Error(err)
return WatcherType{}, http.StatusInternalServerError, err
}
var m WatcherType
err = db.QueryRow(`
SELECT watcher_id,
profile_id,
item_type_id,
item_id,
last_notified,
send_email,
send_sms
FROM watchers
WHERE watcher_id = $1`,
watcherID,
).Scan(
&m.ID,
&m.ProfileID,
&m.ItemTypeID,
&m.ItemID,
&m.LastNotifiedNullable,
&m.SendEmail,
&m.SendSMS,
)
if err == sql.ErrNoRows {
return WatcherType{}, http.StatusNotFound,
fmt.Errorf("Resource with ID %d not found", watcherID)
} else if err != nil {
glog.Error(err)
return WatcherType{}, http.StatusInternalServerError,
fmt.Errorf("Database query failed: %v", err.Error())
}
if m.LastNotifiedNullable.Valid {
m.LastNotified = m.LastNotifiedNullable.Time
}
// Fetch item data
itemType, err := h.GetItemTypeFromInt(m.ItemTypeID)
if err != nil {
glog.Error(err)
return WatcherType{}, http.StatusInternalServerError, err
}
m.ItemType = itemType
// Only fetch the item itself if valid siteId is given
if siteID > 0 {
if m.ItemTypeID != 2 {
item, _, err := GetSummary(
siteID,
m.ItemTypeID,
m.ItemID,
m.ProfileID,
)
if err != nil {
glog.Error(err)
return WatcherType{}, http.StatusInternalServerError, err
}
m.Item = item
} else {
microcosm, _, err := GetMicrocosmSummary(
siteID,
m.ItemID,
m.ProfileID,
)
if err != nil {
glog.Error(err)
return WatcherType{}, http.StatusInternalServerError, err
}
m.Item = microcosm
}
}
// Update cache
c.Set(mcKey, m, mcTTL)
return m, http.StatusOK, nil
}