public PrimitiveApplicationInfo(Core core, Primitive owner, long applicationId) : base(core) { this.owner = owner; ItemLoad += new ItemLoadHandler(PrimitiveApplicationInfo_ItemLoad); SelectQuery query = new SelectQuery(PrimitiveApplicationInfo.GetTable(typeof(PrimitiveApplicationInfo))); query.AddFields(PrimitiveApplicationInfo.GetFieldsPrefixed(core, typeof(PrimitiveApplicationInfo))); query.AddCondition("application_id", applicationId); query.AddCondition("item_id", owner.Id); query.AddCondition("item_type_id", owner.TypeId); DataTable appDataTable = db.Query(query); if (appDataTable.Rows.Count == 1) { DataRow appRow = appDataTable.Rows[0]; try { loadItemInfo(appRow); } catch (InvalidItemException) { throw new InvalidPrimitiveAppInfoException(); } } else { throw new InvalidPrimitiveAppInfoException(); } }
public static Dictionary<string, ReferralKey> GetReferrals(Core core, string key) { Dictionary<string, ReferralKey> keys = new Dictionary<string, ReferralKey>(StringComparer.Ordinal); SelectQuery query = new SelectQuery(typeof(ReferralKey)); query.AddCondition("referral_key", key); DataTable inviteDataTable = core.Db.Query(query); foreach (DataRow row in inviteDataTable.Rows) { ReferralKey newKey = new ReferralKey(core, row); keys.Add(newKey.key, newKey); } return keys; }
public List<NetworkMember> GetMembers(int page, int perPage, string filter) { List<NetworkMember> members = new List<NetworkMember>(); SelectQuery query = new SelectQuery(NetworkMember.GetTable(typeof(NetworkMember))); query.AddFields(NetworkMember.GetFieldsPrefixed(core, typeof(NetworkMember))); query.AddCondition("network_id", networkId); query.AddSort(SortOrder.Ascending, "member_join_date_ut"); if (!string.IsNullOrEmpty(filter)) { query.AddCondition(new DataField("user_keys", "user_name_first"), filter[0]); } query.LimitStart = (page - 1) * perPage; query.LimitCount = perPage; DataTable membersTable = db.Query(query); List<long> memberIds = new List<long>(); foreach (DataRow dr in membersTable.Rows) { memberIds.Add((long)dr["user_id"]); } core.LoadUserProfiles(memberIds); foreach (DataRow dr in membersTable.Rows) { members.Add(new NetworkMember(core, dr)); } return members; }
public static long GetUnseenNotificationCount(Core core) { if (core == null) { throw new NullCoreException(); } List<Notification> notificationItems = new List<Notification>(); SelectQuery query = new SelectQuery(GetTable(typeof(Notification))); query.AddFields("COUNT(*) as total"); query.AddCondition("notification_read", false); query.AddCondition("notification_seen", false); query.AddCondition("notification_primitive_id", core.LoggedInMemberId); query.AddCondition("notification_primitive_type_id", ItemKey.GetTypeId(core, typeof(User))); query.AddSort(SortOrder.Descending, "notification_time_ut"); System.Data.Common.DbDataReader notificationsReader = core.Db.ReaderQuery(query); long newNotifications = 0; if (notificationsReader.HasRows) { notificationsReader.Read(); newNotifications = (long)notificationsReader["total"]; } notificationsReader.Close(); notificationsReader.Dispose(); return newNotifications; }
void AccountPassword_Save(object sender, EventArgs e) { AuthoriseRequestSid(); string password = core.Http.Form["old-password"]; password = User.HashPassword(password); SelectQuery query = new SelectQuery(User.GetTable(typeof(User))); query.AddFields(User.GetFieldsPrefixed(core, typeof(User))); query.AddJoin(JoinTypes.Inner, new DataField(typeof(User), "user_id"), new DataField("user_info", "user_id")); query.AddCondition("user_keys.user_id", core.LoggedInMemberId); query.AddCondition("user_password", password); DataTable userTable = db.Query(query); if (userTable.Rows.Count != 1) { SetError("The old password you entered does not match your old password, make sure you have entered your old password correctly."); return; } else if (core.Http.Form["new-password"] != core.Http.Form["confirm-password"]) { SetError("The passwords you entered do not match, make sure you have entered your desired password correctly."); return; } else if (((string)core.Http.Form["new-password"]).Length < 6) { SetError("The password you entered is too short. Please choose a strong password of 6 characters or more."); return; } UpdateQuery uquery = new UpdateQuery("user_info"); uquery.AddField("user_password", User.HashPassword(core.Http.Form["new-password"])); uquery.AddCondition("user_id", core.LoggedInMemberId); long rowsChanged = db.Query(uquery); if (rowsChanged == 1) { SetInformation("You have successfully changed your password. Keep your password safe and do not share it with anyone."); //SetRedirectUri(BuildUri()); //Display.ShowMessage("Changed Password", "You have successfully changed your password. Keep your password safe and do not share it with anyone."); } else { DisplayGenericError(); return; } }
public static bool IsValidListType(Core core, short listType) { // verify that the type exists SelectQuery query = new SelectQuery("list_types"); query.AddFields("list_type_id"); query.AddCondition("list_type_id", listType); DataTable listTypeTable = core.Db.Query(query); if (listTypeTable.Rows.Count == 1) { return true; } return false; }
private void RequestOAuthAccessToken() { // Final step in oauth handshake OAuthApplication oae = null; string nonce = null; string verifier = core.Http.Form["oauth_verifier"]; OAuthVerifier oAuthVerifier = null; OAuthToken oauthToken = null; try { oAuthVerifier = new OAuthVerifier(core, verifier); } catch (InvalidOAuthVerifierException) { core.Http.StatusCode = 401; NameValueCollection response = new NameValueCollection(); response.Add("error", "unauthorised, invalid verifier"); core.Http.WriteAndEndResponse(response); return; } if (oAuthVerifier.Expired) { core.Http.StatusCode = 401; NameValueCollection response = new NameValueCollection(); response.Add("error", "unauthorised, verifier expired"); core.Http.WriteAndEndResponse(response); return; } try { oauthToken = new OAuthToken(core, oAuthVerifier.TokenId); } catch (InvalidOAuthTokenException) { core.Http.StatusCode = 401; NameValueCollection response = new NameValueCollection(); response.Add("error", "unauthorised, invalid token"); core.Http.WriteAndEndResponse(response); return; } if (AuthoriseRequest("/oauth/access_token", oauthToken, out oae, out nonce)) { oAuthVerifier.UseVerifier(); // TODO: check application is not already installed SelectQuery query = new SelectQuery(typeof(PrimitiveApplicationInfo)); query.AddCondition("application_id", oauthToken.ApplicationId); query.AddCondition("item_id", oAuthVerifier.UserId); query.AddCondition("item_type_id", ItemKey.GetTypeId(core, typeof(User))); System.Data.Common.DbDataReader appReader = db.ReaderQuery(query); if (!appReader.HasRows) { appReader.Close(); appReader.Dispose(); OAuthToken oauthAuthToken = OAuthToken.Create(core, oae, nonce); InsertQuery iQuery = new InsertQuery("primitive_apps"); iQuery.AddField("application_id", oauthToken.ApplicationId); iQuery.AddField("item_id", oAuthVerifier.UserId); iQuery.AddField("item_type_id", ItemKey.GetTypeId(core, typeof(User))); iQuery.AddField("app_email_notifications", true); iQuery.AddField("app_oauth_access_token", oauthAuthToken.Token); iQuery.AddField("app_oauth_access_token_secret", oauthAuthToken.TokenSecret); if (core.Db.Query(iQuery) > 0) { // successfull } db.CommitTransaction(); NameValueCollection response = new NameValueCollection(); response.Add("oauth_token", oauthAuthToken.Token); response.Add("oauth_token_secret", oauthAuthToken.TokenSecret); core.Http.WriteAndEndResponse(response); } else { appReader.Read(); PrimitiveApplicationInfo pai = new PrimitiveApplicationInfo(core, appReader); appReader.Close(); appReader.Dispose(); NameValueCollection response = new NameValueCollection(); response.Add("oauth_token", pai.OAuthAccessToken); response.Add("oauth_token_secret", pai.OAuthAccessTokenSecret); core.Http.WriteAndEndResponse(response); } } else { // FAIL core.Http.StatusCode = 401; NameValueCollection response = new NameValueCollection(); response.Add("error", "unauthorised, access token rejected"); core.Http.WriteAndEndResponse(response); core.Http.End(); return; } }
public static OAuthToken Create(Core core, OAuthApplication oae, string nonce) { if (core == null) { throw new NullCoreException(); } SelectQuery query = new SelectQuery(typeof(OAuthToken)); query.AddCondition("oauth_token_nonce", nonce); System.Data.Common.DbDataReader queryReader = core.Db.ReaderQuery(query); if (queryReader.HasRows) { queryReader.Close(); queryReader.Dispose(); throw new NonceViolationException(); } else { queryReader.Close(); queryReader.Dispose(); } string newToken = string.Empty; do { newToken = OAuth.GeneratePublic(); } while (!CheckTokenUnique(core, newToken)); string newSecret = OAuth.GenerateSecret(); Item item = Item.Create(core, typeof(OAuthToken), new FieldValuePair("oauth_application_id", oae.Id), new FieldValuePair("oauth_token", newToken), new FieldValuePair("oauth_token_secret", newSecret), new FieldValuePair("oauth_token_nonce", nonce), new FieldValuePair("oauth_token_ip", core.Http.IpAddress), new FieldValuePair("oauth_token_created_ut", UnixTime.UnixTimeStamp()), new FieldValuePair("oauth_token_expires_ut", UnixTime.UnixTimeStamp() + 15 * 60), /* Expire after 15 minutes */ new FieldValuePair("oauth_token_expired", false)); OAuthToken newOAuthToken = (OAuthToken)item; return newOAuthToken; }
private void preLoadMemberCache(ItemKey itemKey) { SelectQuery query = new SelectQuery("group_members"); query.AddFields("user_id", "group_member_approved"); query.AddCondition("group_id", groupId); query.AddCondition("user_id", itemKey.Id); System.Data.Common.DbDataReader memberReader = db.ReaderQuery(query); if (memberReader.HasRows) { memberReader.Read(); switch ((GroupMemberApproval)(byte)memberReader["group_member_approved"]) { case GroupMemberApproval.Pending: groupMemberCache.Add(itemKey, false); groupMemberPendingCache.Add(itemKey, true); groupMemberBannedCache.Add(itemKey, false); groupMemberAbsoluteCache.Add(itemKey, true); break; case GroupMemberApproval.Member: groupMemberCache.Add(itemKey, true); groupMemberPendingCache.Add(itemKey, false); groupMemberBannedCache.Add(itemKey, false); groupMemberAbsoluteCache.Add(itemKey, true); break; case GroupMemberApproval.Banned: groupMemberCache.Add(itemKey, false); groupMemberPendingCache.Add(itemKey, false); groupMemberBannedCache.Add(itemKey, true); groupMemberAbsoluteCache.Add(itemKey, false); break; } } else { groupMemberCache.Add(itemKey, false); groupMemberPendingCache.Add(itemKey, false); groupMemberBannedCache.Add(itemKey, false); groupMemberAbsoluteCache.Add(itemKey, false); } memberReader.Close(); memberReader.Dispose(); }
public bool IsGroupOperator(ItemKey member) { if (member != null) { if (groupOperatorCache.ContainsKey(member)) { return groupOperatorCache[member]; } else { SelectQuery query = new SelectQuery(typeof(GroupOperator)); query.AddField(new DataField(typeof(GroupOperator), "user_id")); query.AddCondition(new DataField(typeof(GroupOperator), "group_id"), groupId); query.AddCondition(new DataField(typeof(GroupOperator), "user_id"), member.Id); System.Data.Common.DbDataReader operatorReader = db.ReaderQuery(query); /* string.Format("SELECT user_id FROM group_operators WHERE group_id = {0} AND user_id = {1}", groupId, member.Id) */ if (operatorReader.HasRows) { operatorReader.Close(); operatorReader.Dispose(); groupOperatorCache.Add(member, true); return true; } else { operatorReader.Close(); operatorReader.Dispose(); groupOperatorCache.Add(member, false); return false; } } } return false; }
public List<GroupMember> GetOperators(int page, int perPage) { List<GroupMember> operators = new List<GroupMember>(); SelectQuery query = new SelectQuery("group_operators"); query.AddFields(GroupMember.GetFieldsPrefixed(core, typeof(GroupMember))); query.AddFields(UserInfo.GetFieldsPrefixed(core, typeof(User))); query.AddJoin(JoinTypes.Inner, UserInfo.GetTable(typeof(User)), "user_id", "user_id"); query.AddField(new DataField("group_operators", "user_id")); TableJoin tj = query.AddJoin(JoinTypes.Left, new DataField("group_operators", "user_id"), new DataField("group_members", "user_id")); tj.AddCondition(new DataField("group_members", "group_id"), groupId); query.AddCondition(new DataField("group_operators", "group_id"), groupId); System.Data.Common.DbDataReader membersReader = db.ReaderQuery(query); while(membersReader.Read()) { operators.Add(new GroupMember(core, membersReader)); } membersReader.Close(); membersReader.Dispose(); return operators; }
public List<GroupMember> GetMembers(int page, int perPage, string filter) { List<GroupMember> members = new List<GroupMember>(); SelectQuery query = new SelectQuery("group_members"); query.AddFields(UserInfo.GetFieldsPrefixed(core, typeof(User))); query.AddJoin(JoinTypes.Inner, UserInfo.GetTable(typeof(User)), "user_id", "user_id"); query.AddFields(GroupMember.GetFieldsPrefixed(core, typeof(GroupMember))); query.AddField(new DataField("group_operators", "user_id", "user_id_go")); TableJoin tj = query.AddJoin(JoinTypes.Left, new DataField("group_members", "user_id"), new DataField("group_operators", "user_id")); tj.AddCondition(new DataField("group_members", "group_id"), new DataField("group_operators", "group_id")); query.AddCondition("`group_members`.`group_id`", groupId); query.AddCondition("group_member_approved", true); if (!string.IsNullOrEmpty(filter)) { query.AddCondition(new DataField("user_keys", "user_name_first"), filter[0]); } query.AddSort(SortOrder.Ascending, "group_member_date_ut"); query.LimitStart = (page - 1) * perPage; query.LimitCount = perPage; System.Data.Common.DbDataReader membersReader = db.ReaderQuery(query); while (membersReader.Read()) { members.Add(new GroupMember(core, membersReader)); } membersReader.Close(); membersReader.Dispose(); return members; }
public bool CheckSubGroupNameUnique(string groupName) { SelectQuery query = new SelectQuery(typeof(SubUserGroup)); query.AddField(new DataField(typeof(SubUserGroup), "sub_group_name")); query.AddCondition(new QueryFunction("sub_group_name", QueryFunctions.ToLowerCase), groupName); query.AddCondition("sub_group_parent_id", Id); if (Query(query).Rows.Count > 0) { return false; } return true; }
public UserGroup(Core core, string groupName, UserGroupLoadOptions loadOptions) : base(core) { ItemLoad += new ItemLoadHandler(UserGroup_ItemLoad); bool containsInfoData = false; bool containsIconData = false; if (loadOptions == UserGroupLoadOptions.Key) { try { LoadItem("group_name", groupName); } catch (InvalidItemException) { throw new InvalidGroupException(); } } else { SelectQuery query = new SelectQuery(UserGroup.GetTable(typeof(UserGroup))); query.AddFields(UserGroup.GetFieldsPrefixed(core, typeof(UserGroup))); query.AddCondition("`group_keys`.`group_name`", groupName); if ((loadOptions & UserGroupLoadOptions.Info) == UserGroupLoadOptions.Info) { containsInfoData = true; query.AddJoin(JoinTypes.Inner, UserGroupInfo.GetTable(typeof(UserGroupInfo)), "group_id", "group_id"); query.AddFields(UserGroupInfo.GetFieldsPrefixed(core, typeof(UserGroupInfo))); if ((loadOptions & UserGroupLoadOptions.Icon) == UserGroupLoadOptions.Icon) { containsIconData = true; query.AddJoin(JoinTypes.Left, new DataField("group_info", "group_icon"), new DataField("gallery_items", "gallery_item_id")); query.AddField(new DataField("gallery_items", "gallery_item_uri")); query.AddField(new DataField("gallery_items", "gallery_item_parent_path")); } } System.Data.Common.DbDataReader groupReader = db.ReaderQuery(query); if (groupReader.HasRows) { groupReader.Read(); loadItemInfo(typeof(UserGroup), groupReader); if (containsInfoData) { groupInfo = new UserGroupInfo(core, groupReader); } if (containsIconData) { loadUserGroupIcon(groupReader); } groupReader.Close(); groupReader.Dispose(); } else { groupReader.Close(); groupReader.Dispose(); throw new InvalidGroupException(); } } }
public List<long> GetInviteeIds() { List<long> ids = new List<long>(); SelectQuery query = new SelectQuery("event_invites"); query.AddFields("item_id", "item_type_id", "inviter_id", "event_id"); query.AddCondition("event_id", EventId); DataTable invitees = db.Query(query); foreach (DataRow dr in invitees.Rows) { if ((long)dr["item_type_id"] == ItemKey.GetTypeId(core, typeof(User))) { ids.Add((long)dr["item_id"]); } } return ids; }
// TODO: use user public static void Show(Core core, UPage page, string user) { core.Template.SetTemplate("GuestBook", "viewguestbook"); page.User.LoadProfileInfo(); if (!page.User.Access.Can("VIEW")) { core.Functions.Generate403(); return; } /* pages */ core.Display.ParsePageList(page.Owner, true); core.Template.Parse("PAGE_TITLE", string.Format(core.Prose.GetString("USERS_GUEST_BOOK"), page.Owner.DisplayNameOwnership)); if (core.Session.IsLoggedIn) { if (page.User.Access.Can("COMMENT")) { core.Template.Parse("CAN_COMMENT", "TRUE"); } } core.Template.Parse("IS_USER_GUESTBOOK", "TRUE"); long userId = core.LoadUserProfile(user); List<User> commenters = new List<User>(); commenters.Add(page.User); commenters.Add(core.PrimitiveCache[userId]); List<string[]> breadCrumbParts = new List<string[]>(); breadCrumbParts.Add(new string[] { "profile", core.Prose.GetString("PROFILE") }); breadCrumbParts.Add(new string[] { "comments", core.Prose.GetString("COMMENTS") }); breadCrumbParts.Add(new string[] {core.PrimitiveCache[userId].Key, core.PrimitiveCache[userId].DisplayName}); // Load the comment count long comments = 0; SelectQuery query = new SelectQuery("guestbook_comment_counts"); query.AddField(new QueryFunction("comment_comments", QueryFunctions.Sum, "comments")); QueryCondition qc1 = query.AddCondition("owner_id", commenters[0].Id); qc1.AddCondition("user_id", commenters[1].Id); QueryCondition qc2 = query.AddCondition(ConditionRelations.Or, "owner_id", commenters[1].Id); qc2.AddCondition("user_id", commenters[0].Id); DataTable commentCountDataTable = core.Db.Query(query); if (commentCountDataTable.Rows.Count > 0) { if (!(commentCountDataTable.Rows[0]["comments"] is DBNull)) { comments = (long)(Decimal)commentCountDataTable.Rows[0]["comments"]; } } core.Display.DisplayComments(core.Template, page.User, page.User, commenters, comments, UserGuestBookHook); core.Display.ParsePagination("COMMENT_PAGINATION", core.Hyperlink.BuildGuestBookUri(page.User, core.PrimitiveCache[userId]), 10, comments); page.User.ParseBreadCrumbs(breadCrumbParts); }
public static bool CheckTokenUnique(Core core, string token) { SelectQuery query = new SelectQuery(typeof(OAuthToken)); query.AddCondition("oauth_token", token); System.Data.Common.DbDataReader tokenReader = core.Db.ReaderQuery(query); if (tokenReader.HasRows) { tokenReader.Close(); tokenReader.Dispose(); return false; } else { tokenReader.Close(); tokenReader.Dispose(); return true; } }
public UserGroup(Core core, long groupId, UserGroupLoadOptions loadOptions) : base(core) { ItemLoad += new ItemLoadHandler(UserGroup_ItemLoad); bool containsInfoData = false; bool containsIconData = false; if (loadOptions == UserGroupLoadOptions.Key) { try { LoadItem(groupId); } catch (InvalidItemException) { throw new InvalidGroupException(); } } else { SelectQuery query = new SelectQuery(UserGroup.GetTable(typeof(UserGroup))); query.AddFields(UserGroup.GetFieldsPrefixed(core, typeof(UserGroup))); query.AddCondition("`group_keys`.`group_id`", groupId); if ((loadOptions & UserGroupLoadOptions.Info) == UserGroupLoadOptions.Info) { containsInfoData = true; query.AddJoin(JoinTypes.Inner, UserGroupInfo.GetTable(typeof(UserGroupInfo)), "group_id", "group_id"); query.AddFields(UserGroupInfo.GetFieldsPrefixed(core, typeof(UserGroupInfo))); if ((loadOptions & UserGroupLoadOptions.Icon) == UserGroupLoadOptions.Icon) { containsIconData = true; query.AddJoin(JoinTypes.Left, new DataField("group_info", "group_icon"), new DataField("gallery_items", "gallery_item_id")); query.AddField(new DataField("gallery_items", "gallery_item_uri")); query.AddField(new DataField("gallery_items", "gallery_item_parent_path")); } } DataTable groupTable = db.Query(query); if (groupTable.Rows.Count > 0) { loadItemInfo(typeof(UserGroup), groupTable.Rows[0]); if (containsInfoData) { groupInfo = new UserGroupInfo(core, groupTable.Rows[0]); } if (containsIconData) { loadUserGroupIcon(groupTable.Rows[0]); } } else { throw new InvalidGroupException(); } } }
public static bool AuthorisedRequest(Core core) { if (core == null) { throw new NullCoreException(); } if (!core.Session.IsLoggedIn) { return false; } if (!string.IsNullOrEmpty(core.Http.Query["nvid"])) { SelectQuery query = new SelectQuery(typeof(Notification)); query.AddFields("notification_primitive_id"); query.AddFields("notification_primitive_type_id"); query.AddCondition("notification_verification_string", core.Http.Query["nvid"]); query.AddCondition("notification_primitive_id", core.Session.LoggedInMember.Id); query.AddCondition("notification_primitive_type_id", ItemType.GetTypeId(core, typeof(User))); if (core.Db.Query(query).Rows.Count == 0) { return false; } else { // This is OK return true; } } if (core.Http.Query["sid"] != core.Session.SessionId) { if (string.IsNullOrEmpty(core.Http.Query["sid"])) { return false; } SelectQuery query = new SelectQuery("user_sessions"); query.AddFields("user_id"); query.AddCondition("session_string", core.Http.Query["sid"]); query.AddCondition("user_id", core.Session.LoggedInMember.Id); query.AddCondition("session_signed_in", true); query.AddCondition("session_ip", core.Session.IPAddress.ToString()); if (core.Db.Query(query).Rows.Count == 0) { return false; } } return true; }
public List<Fan> GetFans(int page, int perPage, string filter) { List<Fan> fans = new List<Fan>(); SelectQuery query = new SelectQuery(typeof(Fan)); query.AddJoin(JoinTypes.Inner, "user_keys", "user_id", "user_id"); query.AddFields(Fan.GetFieldsPrefixed(core, typeof(Fan))); query.AddCondition("musician_id", musicianId); if (!string.IsNullOrEmpty(filter)) { query.AddCondition("user_keys.user_name_first", filter); } query.AddSort(SortOrder.Ascending, "fan_date_ut"); query.LimitStart = (page - 1) * perPage; query.LimitCount = perPage; DataTable fansTable = db.Query(query); List<long> fanIds = new List<long>(); foreach (DataRow dr in fansTable.Rows) { fanIds.Add((long)dr["user_id"]); } core.LoadUserProfiles(fanIds); foreach (DataRow dr in fansTable.Rows) { fans.Add(new Fan(core, dr)); } return fans; }
public List(Core core, User owner, string listName) : base(core) { this.owner = owner; ItemLoad += new ItemLoadHandler(List_ItemLoad); SelectQuery query = new SelectQuery(List.GetTable(typeof(List))); query.AddFields(List.GetFieldsPrefixed(core, typeof(List))); query.AddCondition("list_path", listName); query.AddCondition("user_id", owner.Id); DataTable listTable = db.Query(query); if (listTable.Rows.Count == 1) { loadItemInfo(listTable.Rows[0]); } else { throw new InvalidListException(); } }
public List<MusicianMember> GetMembers() { List<MusicianMember> members = new List<MusicianMember>(); SelectQuery query = new SelectQuery(typeof(MusicianMember)); query.AddField(new DataField(typeof(MusicianMember), "user_id")); query.AddCondition("musician_id", musicianId); DataTable membersTable = db.Query(query); List<long> memberIds = new List<long>(); foreach (DataRow dr in membersTable.Rows) { memberIds.Add((long)dr["user_id"]); } core.LoadUserProfiles(memberIds); foreach (DataRow dr in membersTable.Rows) { members.Add(new MusicianMember(core, dr)); } return members; }
/// <summary> /// /// </summary> /// <param name="core"></param> /// <param name="itemType"></param> /// <param name="itemId"></param> /// <param name="rating"></param> /// <remarks>ItemRated should implement a transaction.</remarks> public static void Vote(Core core, ItemKey itemKey, int rating) { if (core == null) { throw new NullCoreException(); } if (itemKey.Id < 1) { throw new InvalidItemException(); } if (rating < 1 || rating > 5) { throw new InvalidRatingException(); } /* after 7 days release the IP for dynamics ip fairness */ SelectQuery query = new SelectQuery("ratings r"); query.AddFields("user_id"); query.AddCondition("rate_item_id", itemKey.Id); query.AddCondition("rate_item_type_id", itemKey.TypeId); QueryCondition qc1 = query.AddCondition("user_id", core.LoggedInMemberId); QueryCondition qc2 = qc1.AddCondition(ConditionRelations.Or, "rate_ip", core.Session.IPAddress.ToString()); qc2.AddCondition("rate_time_ut", ConditionEquality.GreaterThan, UnixTime.UnixTimeStamp() - 60 * 60 * 24 * 7); /*DataTable ratingsTable = db.Query(string.Format("SELECT user_id FROM ratings WHERE rate_item_id = {0} AND rate_item_type = '{1}' AND (user_id = {2} OR (rate_ip = '{3}' AND rate_time_ut > UNIX_TIMESTAMP() - (60 * 60 * 24 * 7)))", itemId, Mysql.Escape(itemType), loggedInMember.UserId, session.IPAddress.ToString()));*/ DataTable ratingsTable = core.Db.Query(query); if (ratingsTable.Rows.Count > 0) { throw new AlreadyRatedException(); } /* Impossible using object query model */ /*UpdateQuery uQuery = new UpdateQuery(typeof(ItemInfo)); uQuery.AddField("rating", new QueryOperation("info_rating", QueryOperations.Division, new QueryOperation(QueryOperations.Addition, new QueryOperation("info_rating", QueryOperations.Multiplication, new DataField(typeof(ItemInfo), "info_ratings")))); uQuery.AddCondition("info_item_id", itemKey.Id); uQuery.AddCondition("info_item_type_id", itemKey.TypeId);*/ core.Db.UpdateQuery(string.Format("UPDATE item_info SET info_rating = (info_rating * info_ratings + {0}) / (info_ratings + 1), info_ratings = info_ratings + 1 WHERE info_item_id = {1} AND info_item_type_id = {2}", rating, itemKey.Id, itemKey.TypeId)); InsertQuery iQuery = new InsertQuery("ratings"); iQuery.AddField("rate_item_id", itemKey.Id); iQuery.AddField("rate_item_type_id", itemKey.TypeId); iQuery.AddField("user_id", core.LoggedInMemberId); iQuery.AddField("rate_time_ut", UnixTime.UnixTimeStamp()); iQuery.AddField("rate_rating", rating); iQuery.AddField("rate_ip", core.Session.IPAddress.ToString()); // commit the transaction core.Db.Query(iQuery); return; }
private void preLoadMemberCache(ItemKey key) { SelectQuery query = new SelectQuery("musician_members"); query.AddCondition("musician_id", musicianId); query.AddCondition("user_id", key.Id); DataTable memberTable = db.Query(query); if (memberTable.Rows.Count > 0) { musicianMemberCache.Add(key, true); } else { musicianMemberCache.Add(key, false); } }
/// <summary> /// /// </summary> /// <param name="m">Page with message id#</param> /// <param name="perPage"></param> /// <returns></returns> public List<TopicPost> GetPosts(long m, int perPage) { int p = 1; // currentPage if (m > 0) { SelectQuery query = new SelectQuery(TopicPost.GetTable(typeof(TopicPost))); query.AddFields("COUNT(*) AS total"); query.AddCondition("topic_id", topicId); query.AddCondition("post_id", ConditionEquality.LessThan, m); query.AddSort(SortOrder.Ascending, "post_time_ut"); System.Data.Common.DbDataReader postsReader = db.ReaderQuery(query); long before = 0; long after = 0; if (postsReader.HasRows) { postsReader.Read(); before = (long)postsReader["total"]; after = Posts - before; } postsReader.Close(); postsReader.Dispose(); /*if (item.CommentSortOrder == SortOrder.Ascending) {*/ p = (int)(before / perPage + 1); /*} else { p = (int)(after / perPage + 1); }*/ } return GetPosts(p, perPage); }
public void SessionEnd(string sessionId, long userId, DnsRecord record) { string cookieName = "hailToTheChef"; //XmlSerializer xs; //StringWriter stw; if (!string.IsNullOrEmpty(sessionId)) { if (!IsValidSid(sessionId)) { return; } } else { return; } // // Delete existing session // if (record == null) { db.UpdateQuery(string.Format("DELETE FROM user_sessions WHERE (session_string = '{0}' OR session_root_string = '{0}') AND user_id = {1};", sessionId, userId)); } else { SelectQuery query = new SelectQuery(typeof(Session)); query.AddCondition("session_string", sessionId); query.AddCondition("user_id", userId); query.AddCondition("session_domain", record.Domain); System.Data.Common.DbDataReader sessionReader = db.ReaderQuery(query); List<string> rootSessionIds = new List<string>(); while (sessionReader.Read()) { rootSessionIds.Add((string)sessionReader["session_root_string"]); } sessionReader.Close(); sessionReader.Dispose(); if (rootSessionIds.Count > 0) { DeleteQuery dQuery = new DeleteQuery(typeof(Session)); QueryCondition qc1 = dQuery.AddCondition("session_string", ConditionEquality.In, rootSessionIds); qc1.AddCondition(ConditionRelations.Or, "session_root_string", ConditionEquality.In, rootSessionIds); dQuery.AddCondition("user_id", userId); db.Query(dQuery); } } // // Remove this auto-login entry (if applicable) // // // We expect that message_die will be called after this function, // but just in case it isn't, reset $userdata to the details for a guest // if (record == null) { Response.Cookies.Clear(); SelectQuery query = User.GetSelectQueryStub(core, UserLoadOptions.Info); query.AddCondition("user_keys.user_id", 0); DataTable userTable = db.Query(query); Response.Cookies.Clear(); if (userTable.Rows.Count == 1) { loggedInMember = new User(core, userTable.Rows[0], UserLoadOptions.Info); } HttpCookie newSessionDataCookie = new HttpCookie(cookieName + "_data"); newSessionDataCookie.Path = "/"; newSessionDataCookie.Value = ""; newSessionDataCookie.Expires = DateTime.Now.AddYears(-1); newSessionDataCookie.Secure = core.Settings.UseSecureCookies && core.Hyperlink.CurrentDomain == Hyperlink.Domain; newSessionDataCookie.HttpOnly = true; Response.Cookies.Add(newSessionDataCookie); HttpCookie newSessionSidCookie = new HttpCookie(cookieName + "_sid"); newSessionSidCookie.Path = "/"; newSessionSidCookie.Value = ""; newSessionSidCookie.Expires = DateTime.Now.AddYears(-1); newSessionSidCookie.Secure = core.Settings.UseSecureCookies && core.Hyperlink.CurrentDomain == Hyperlink.Domain; newSessionSidCookie.HttpOnly = true; Response.Cookies.Add(newSessionSidCookie); if (Request.Cookies[cookieName + "_sid"] == null && signInState != SessionSignInState.Bot) { core.Hyperlink.SidUrls = true; } } return; }
public Dictionary<string, long> LoadUserProfiles(List<string> usernames) { long userTypeId = ItemKey.GetTypeId(core, typeof(User)); List<string> usernameList = new List<string>(); Dictionary<string, long> userIds = new Dictionary<string, long>(8, StringComparer.Ordinal); foreach (string username in usernames) { PrimitiveKey key = new PrimitiveKey(username.ToLower(), userTypeId); if (!primitivesKeysCached.ContainsKey(key)) { usernameList.Add(username.ToLower()); } } if (usernameList.Count > 0) { SelectQuery query = new SelectQuery("user_keys"); query.AddFields(User.GetFieldsPrefixed(core, typeof(User))); query.AddFields(UserInfo.GetFieldsPrefixed(core, typeof(UserInfo))); query.AddFields(UserProfile.GetFieldsPrefixed(core, typeof(UserProfile))); query.AddJoin(JoinTypes.Inner, UserInfo.GetTable(typeof(UserInfo)), "user_id", "user_id"); query.AddJoin(JoinTypes.Inner, UserProfile.GetTable(typeof(UserProfile)), "user_id", "user_id"); query.AddJoin(JoinTypes.Left, new DataField("user_profile", "profile_country"), new DataField("countries", "country_iso")); query.AddJoin(JoinTypes.Left, new DataField("user_profile", "profile_religion"), new DataField("religions", "religion_id")); query.AddCondition(new DataField("user_keys", "user_name_lower"), ConditionEquality.In, usernameList); System.Data.Common.DbDataReader usersReader = db.ReaderQuery(query); while(usersReader.Read()) { User newUser = new User(core, usersReader, UserLoadOptions.All); // This will automatically cache itself when loadUser is called PrimitiveId pid = new PrimitiveId(newUser.Id, userTypeId); PrimitiveKey kid = new PrimitiveKey(newUser.UserName.ToLower(), userTypeId); if (!userIds.ContainsValue(newUser.Id)) { userIds.Add(newUser.UserName, newUser.Id); } } usersReader.Close(); usersReader.Dispose(); } return userIds; }
public SessionState(Core core, Mysql db, OAuthToken token, HttpRequest Request, HttpResponse Response) { if (core == null) { throw new NullCoreException(); } this.Request = Request; this.Response = Response; this.db = db; this.core = core; applicationId = token.ApplicationId; SelectQuery query = new SelectQuery(typeof(PrimitiveApplicationInfo)); query.AddCondition("application_id", token.ApplicationId); query.AddCondition("app_oauth_access_token", token.Token); System.Data.Common.DbDataReader appReader = core.Db.ReaderQuery(query); if (appReader.HasRows) { appReader.Read(); PrimitiveApplicationInfo pai = new PrimitiveApplicationInfo(core, appReader); appReader.Close(); appReader.Dispose(); if (pai.Owner is User) { this.core = core; this.db = core.Db; isLoggedIn = true; this.signInState = SessionSignInState.SignedIn; loggedInMember = (User)pai.Owner; ipAddress = IPAddress.Parse(SessionState.ReturnRealIPAddress(Request.ServerVariables)); this.sessionMethod = SessionMethods.OAuth; } } else { appReader.Close(); appReader.Dispose(); this.core = core; this.db = core.Db; isLoggedIn = false; this.signInState = SessionSignInState.SignedOut; ipAddress = IPAddress.Parse(SessionState.ReturnRealIPAddress(Request.ServerVariables)); this.sessionMethod = SessionMethods.OAuth; } }
public Network(Core core, long networkId, NetworkLoadOptions loadOptions) : base(core) { ItemLoad += new ItemLoadHandler(Network_ItemLoad); bool containsInfoData = false; bool containsIconData = false; if (loadOptions == NetworkLoadOptions.Key) { try { LoadItem(networkId); } catch (InvalidItemException) { throw new InvalidNetworkException(); } } else { SelectQuery query = new SelectQuery(Network.GetTable(typeof(Network))); query.AddFields(Network.GetFieldsPrefixed(core, typeof(Network))); query.AddCondition("`network_keys`.`network_id`", networkId); if ((loadOptions & NetworkLoadOptions.Info) == NetworkLoadOptions.Info) { containsInfoData = true; query.AddJoin(JoinTypes.Inner, NetworkInfo.GetTable(typeof(NetworkInfo)), "network_id", "network_id"); query.AddFields(NetworkInfo.GetFieldsPrefixed(core, typeof(NetworkInfo))); if ((loadOptions & NetworkLoadOptions.Icon) == NetworkLoadOptions.Icon) { // TODO: Network Icon /*containsIconData = true; query.AddJoin(JoinTypes.Left, new DataField("network_info", "network_icon"), new DataField("gallery_items", "gallery_item_id")); query.AddField(new DataField("gallery_items", "gallery_item_uri")); query.AddField(new DataField("gallery_items", "gallery_item_parent_path"));*/ } } DataTable networkTable = db.Query(query); if (networkTable.Rows.Count > 0) { loadItemInfo(typeof(Network), networkTable.Rows[0]); if (containsInfoData) { networkInfo = new NetworkInfo(core, networkTable.Rows[0]); } if (containsIconData) { // TODO: Network Icon //loadNetworkIcon(networkTable.Rows[0]); } } else { throw new InvalidNetworkException(); } } }
private static ItemViewDiscountedReason VerifyView(Core core, ItemView view) { ItemViewDiscountedReason returnValue = ItemViewDiscountedReason.None; if (SessionState.IsBotUserAgent(view.HttpUserAgent) != null) { return ItemViewDiscountedReason.BotDetected; } // Select the number of views within 24 hours of the view SelectQuery query = new SelectQuery(typeof(ItemView)); query.AddField(new QueryFunction("view_id", QueryFunctions.Count, "real_views")); query.AddCondition("view_item_id", view.ViewKey.Id); query.AddCondition("view_item_type_id", view.ViewKey.TypeId); //query.AddCondition("view_processed", true); QueryCondition qc1 = query.AddCondition("view_ip", view.viewIp); QueryCondition qc2 = qc1.AddCondition("view_time_ut", ConditionEquality.GreaterThan, view.viewTimeRaw - 60 * 60 * 24); // last 24 hours qc2.AddCondition("view_time_ut", ConditionEquality.LessThan, view.viewTimeRaw + 60 * 5); // any in the next 5 minutes discounts the whole set qc1.AddCondition(ConditionRelations.Or, "view_session_id", view.viewSessionId); if (view.userId > 0) { qc1.AddCondition(ConditionRelations.Or, "user_id", view.userId); } if ((long)core.Db.Query(query).Rows[0]["real_views"] > 1) { returnValue = returnValue | ItemViewDiscountedReason.RateLimited; } if ((!view.viewHttpUserAgent.ToLower().Contains("mozilla/") && !view.viewHttpUserAgent.ToLower().Contains("gecko")) || view.viewHttpUserAgent.Length < 32) { returnValue = returnValue | ItemViewDiscountedReason.BadUserAgent; } // query = new SelectQuery(typeof(ItemView)); query.AddField(new QueryFunction("view_id", QueryFunctions.Count, "real_views")); query.AddCondition("view_ip", view.viewIp); query.AddCondition("view_time_ut", ConditionEquality.GreaterThan, view.viewTimeRaw - 10); query.AddCondition("view_time_ut", ConditionEquality.LessThan, view.viewTimeRaw + 10); if ((long)core.Db.Query(query).Rows[0]["real_views"] > 8) { returnValue = returnValue | ItemViewDiscountedReason.RateLimited; } // ensure that the session is only used by ONE browser, no session hijacking query = new SelectQuery(typeof(ItemView)); query.AddField(new DataField(typeof(ItemView), "view_http_user_agent")); query.AddCondition("view_session_id", view.viewSessionId); query.AddCondition("view_time_ut", ConditionEquality.GreaterThan, view.viewTimeRaw - 3600); query.AddCondition("view_time_ut", ConditionEquality.LessThan, view.viewTimeRaw + 3600); query.Distinct = true; if ((long)core.Db.Query(query).Rows.Count > 1) { returnValue = returnValue | ItemViewDiscountedReason.BotDetected; } long viewQuality = 0; if (view.viewTimespan > 60) { viewQuality += 3; } else if (view.viewTimespan > 30) { viewQuality += 2; } else if (view.viewTimespan > 5) { viewQuality++; } if (view.viewJavascript) { viewQuality++; } if (view.viewCookies) { viewQuality++; } else { // cookies can't be detected on the landing page, but a legit IP with a cookie is unlikely to go bad even if shared query = new SelectQuery(typeof(ItemView)); query.AddField(new QueryFunction("view_id", QueryFunctions.Count, "real_views")); query.AddCondition("view_ip", view.viewIp); query.AddCondition("view_cookies", true); if ((long)core.Db.Query(query).Rows[0]["real_views"] > 0) { viewQuality++; } } if (IsRecentBrowser(view.viewHttpUserAgent)) { viewQuality += 2; } else { returnValue = returnValue | ItemViewDiscountedReason.OldUserAgent; } if (!string.IsNullOrEmpty(view.viewHttpReferer)) { viewQuality++; } if (viewQuality < 3) { returnValue = returnValue | ItemViewDiscountedReason.LowQuality; } return returnValue; }