Golang helpers.GetConnection函數代碼示例

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}

// 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
}