func AddPost(requesterID string, post *types.Post) httperr.Error {
jsonFields, err := json.Marshal(post.Fields)
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
err = db.Exec("INSERT INTO posts "+
"(p_postid, p_userid_creator, p_channelname, p_title, p_thumbnail, p_time, p_location, p_fields) "+
"VALUES (?, ?, ?, ?, ?, ?, ST_MakePoint(?,?), ?)",
post.ID, post.CreatorID, post.Channel, post.Title, post.Thumbnail, post.Time,
post.Location.Longitude, post.Location.Latitude, jsonFields).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
func AddComment(requesterID string, comment *types.Comment) httperr.Error {
err := db.Create(comment).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
func RemoveComment(requesterID, commentID string) httperr.Error {
err := db.Where("cmd_commentid = ?", commentID).Delete(&types.Comment{}).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
func AddChannel(channel *types.Channel) httperr.Error {
err := db.Create(channel).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
// AddUser adds the given User to the database, or returns an error if the
// insertion was unsuccessful.
func AddUser(user *types.User) httperr.Error {
err := db.Create(user).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
func GetComments(requesterID, postID string) ([]*types.Comment, httperr.Error) {
var comments []*types.Comment
err := db.Where("cmt_postid = ?", postID).Order("cmt_time").Find(&comments).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return comments, nil
}
func IsCommentCreator(requesterID, userID, commentID string) (bool, httperr.Error) {
var comment types.Comment
err := db.Where("cmt_commentid = ?", commentID).First(&comment).Error
if err == gorm.ErrRecordNotFound {
return false, nil
} else if err != nil {
return false, httperr.New(err.Error(), http.StatusInternalServerError)
}
return true, nil
}
// GetComment returns a comment with the given ID, or nil if it does not exist.
// An error is returned if some issue occurred with the database.
func GetComment(requesterID, commentID string) (*types.Comment, httperr.Error) {
var comment types.Comment
err := db.Where("cmt_commentid = ?", commentID).First(&comment).Error
if err != nil {
if err == gorm.ErrRecordNotFound {
return nil, nil
}
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return &comment, nil
}
// GetUserByID returns the user with the given ID if one exists, nil otherwise.
// Returns an error if some error occurs within the database.
func GetUserByID(userID string) (*types.User, httperr.Error) {
var user types.User
err := db.Where("u_userid = ?", userID).First(&user).Error
if err != nil {
if err == gorm.ErrRecordNotFound {
return nil, nil
}
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return &user, nil
}
func GetChannels(requesterID string) ([]*types.PersonalizedChannelInfo, httperr.Error) {
var channels []*types.PersonalizedChannelInfo
err := db.Table("channels").
Joins("LEFT JOIN user_subscriptions ON (ch_channelname = us_channelname AND us_userid = ?)", requesterID).
Joins("LEFT JOIN users ON (u_userid = ch_userid_creator)").
Select("*, (us_channelname IS NOT NULL) AS subscribed").
Order("ch_channelname").Find(&channels).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return channels, nil
}
func GetPost(requesterID, postID string) (*types.PersonalizedPost, httperr.Error) {
var post types.PersonalizedPost
err := db.Table("posts").
Where("p_postid = ?", postID).
Joins("LEFT JOIN user_favorites ON (p_postid = uf_postid AND uf_userid = ?)", requesterID).
Joins("LEFT JOIN users ON (u_userid = p_userid_creator)").
Select("*, (uf_postid IS NOT NULL) AS favorited, ST_AsText(p_location) AS location").
First(&post).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return &post, nil
}
// RemoveFavorite removes the given post from the user with ID userID's set of
// favorites. This transaction is executed under the permission level of the
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func RemoveFavorite(requesterID, userID, postID string) httperr.Error {
permission, httpErr := CanModifyFavorites(requesterID, userID)
if httpErr != nil {
return httpErr
} else if !permission {
return ErrInsufficientPermission
}
err := db.Exec("DELETE FROM user_favorites WHERE uf_userid = ? AND uf_postid = ?", requesterID, postID).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
// AddFavorite adds the given post to the set of favorites for the user with ID
// userID. This transaction is executed under the permission level of the
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func AddFavorite(requesterID, userID, postID string) httperr.Error {
permission, httpErr := CanModifyFavorites(requesterID, userID)
if httpErr != nil {
return httpErr
} else if !permission {
return ErrInsufficientPermission
}
err := db.Exec("INSERT INTO user_favorites (uf_userid, uf_postid) VALUES (?, ?)", requesterID, postID).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
func RemoveSubscription(requesterID, userID, channelname string) httperr.Error {
permission, httpErr := CanModifySubscriptions(requesterID, userID)
if httpErr != nil {
return httpErr
} else if !permission {
return ErrInsufficientPermission
}
err := db.Exec("DELETE FROM user_subscriptions WHERE us_userid = ? AND us_channelname = ?", requesterID, channelname).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
// RemoveBan removes the user with ID userID from the ban list for the given
// channel. This transaction is executed under the permission level of the given
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func RemoveBan(requesterID, userID, channelname string) error {
permission, httpErr := CanModifyBans(requesterID, channelname)
if httpErr != nil {
return httpErr
} else if !permission {
return ErrInsufficientPermission
}
err := db.Exec("DELETE FROM channel_bans WHERE chb_userid = ? and chb_channelname = ?", userID, channelname).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
func AddSubscription(requesterID, userID, channelname string) httperr.Error {
permission, httpErr := CanModifySubscriptions(requesterID, userID)
if httpErr != nil {
return httpErr
} else if !permission {
return ErrInsufficientPermission
}
err := db.Exec("INSERT INTO user_subscriptions (us_userid, us_channelname) VALUES (?, ?)", requesterID, channelname).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
// AddBan adds the user with ID userID to the ban list for the given channel.
// This transaction is executed under the permission level of the given
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func AddBan(requesterID, userID, channelname string) httperr.Error {
permission, httpErr := CanModifyBans(requesterID, channelname)
if httpErr != nil {
return httpErr
} else if !permission {
return ErrInsufficientPermission
}
err := db.Exec("INSERT INTO channel_bans (chb_userid, chb_channelname) VALUES (?, ?);", userID, channelname).Error
if err != nil {
return httperr.New(err.Error(), http.StatusInternalServerError)
}
return nil
}
// GetBans returns the list of users which are banned from the given channel.
// This transaction is executed under the permission level of the given
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func GetBans(requesterID, channelname string) ([]string, httperr.Error) {
permission, httpErr := CanViewBans(requesterID, channelname)
if httpErr != nil {
return nil, httpErr
} else if !permission {
return nil, ErrInsufficientPermission
}
var bans []string
err := db.Table("channel_bans").Select("chb_userid").Where("chb_channelname = ?", channelname).Scan(&bans).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return bans, nil
}
func GetChannel(requesterID, channelname string) (*types.PersonalizedChannel, httperr.Error) {
// TODO: Account for requester permission
var channel types.PersonalizedChannel
err := db.Table("channels").
Joins("LEFT JOIN user_subscriptions ON (ch_channelname = us_channelname AND us_userid = ?)", requesterID).
Joins("LEFT JOIN users ON (u_userid = ch_userid_creator)").
Select("*, (us_channelname IS NOT NULL) AS subscribed").
Where("ch_channelname = ?", channelname).
First(&channel).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return &channel, nil
}
func GetPosts(requesterID string, postQueryParams *PostQueryParams) ([]*types.PersonalizedPostInfo, httperr.Error) {
var posts []*types.PersonalizedPostInfo
query := db.Table("posts").
Joins("LEFT JOIN user_favorites ON (p_postid = uf_postid AND uf_userid = ?)", requesterID).
Joins("LEFT JOIN user_subscriptions ON (p_channelname = us_channelname AND us_userid = ?)", requesterID).
Joins("LEFT JOIN users ON (u_userid = p_userid_creator)").
Select("*, (uf_postid IS NOT NULL) AS favorited, ST_AsText(p_location) AS location, (us_channelname IS NOT NULL) AS subscribed").
Order("p_time desc")
if postQueryParams.Flags != nil {
if postQueryParams.Flags.Mine {
query = query.Where("p_userid_creator = ?", requesterID)
}
if postQueryParams.Flags.Favorites {
query = query.Where("uf_postid IS NOT NULL")
}
if postQueryParams.Flags.Subscriptions {
query = query.Where("us_channelname IS NOT NULL")
}
}
if postQueryParams.LocationRange != nil {
query = query.Where("p_location && ST_MakeEnvelope (?,?,?,?)",
postQueryParams.LocationRange.Min.Longitude,
postQueryParams.LocationRange.Min.Latitude,
postQueryParams.LocationRange.Max.Longitude,
postQueryParams.LocationRange.Max.Latitude)
}
if postQueryParams.TimeRange != nil {
query = query.Where("p_time > ? AND p_time < ?",
postQueryParams.TimeRange.Min,
postQueryParams.TimeRange.Max)
}
if postQueryParams.Limit != nil {
query = query.Limit(*postQueryParams.Limit)
}
if postQueryParams.Offset != nil {
query = query.Offset(*postQueryParams.Offset)
}
err := query.Find(&posts).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
return posts, nil
}
// GetFavorites returns the set of favorites for the user with ID userID. This
// transaction is executed under the permission level of the requester. Returns
// an error if the requester does not have sufficient permission, or if some
// other error occurs within the database.
func GetFavorites(requesterID, userID string) ([]string, httperr.Error) {
permission, httpErr := CanViewFavorites(requesterID, userID)
if httpErr != nil {
return nil, httpErr
} else if !permission {
return nil, ErrInsufficientPermission
}
var favorites []struct {
PostID string `gorm:"column:uf_postid"`
}
err := db.Table("user_favorites").Where("uf_userid = ?", userID).Find(&favorites).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
postIDs := make([]string, len(favorites))
for i, favorite := range favorites {
postIDs[i] = favorite.PostID
}
return postIDs, nil
}
func GetSubscriptions(requesterID, userID string) ([]string, httperr.Error) {
permission, httpErr := CanViewSubscriptions(requesterID, userID)
if httpErr != nil {
return nil, httpErr
} else if !permission {
return nil, ErrInsufficientPermission
}
var subscriptions []struct {
PostID string `gorm:"column:us_channelname"`
}
err := db.Table("user_subscriptions").
Where("us_userid = ?", userID).
Find(&subscriptions).Error
if err != nil {
return nil, httperr.New(err.Error(), http.StatusInternalServerError)
}
channelnames := make([]string, len(subscriptions))
for i, subscription := range subscriptions {
channelnames[i] = subscription.PostID
}
return channelnames, nil
}
import (
"fmt"
"net/http"
"github.com/jinzhu/gorm"
"github.com/joshheinrichs/geosource/server/config"
"github.com/joshheinrichs/httperr"
// This is not imported in main to keep all logic about the database inside the transactions package
_ "github.com/lib/pq"
)
var db *gorm.DB
var ErrInsufficientPermission = httperr.New("Insufficient permission.", http.StatusForbidden)
var ErrNotImplemented = httperr.New("function has not yet been implemented", http.StatusInternalServerError)
// Init opens a connection to the database based on the information in the
// given config. Returns an error if the connection could not be established.
func Init(config *config.Config) (err error) {
arguments := ""
if len(config.Database.Host) > 0 {
arguments += fmt.Sprintf("host=%s ", config.Database.Host)
}
if len(config.Database.Database) > 0 {
arguments += fmt.Sprintf("dbname=%s ", config.Database.Database)
}
if len(config.Database.User) > 0 {
arguments += fmt.Sprintf("user=%s ", config.Database.User)
}
