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 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<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 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 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 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 static SelectQuery GetSelectQueryStub(Core core, MusicianLoadOptions loadOptions)
{
SelectQuery query = new SelectQuery(GetTable(typeof(Musician)));
query.AddFields(Musician.GetFieldsPrefixed(core, typeof(Musician)));
if ((loadOptions & MusicianLoadOptions.Icon) == MusicianLoadOptions.Icon)
{
query.AddJoin(JoinTypes.Left, new DataField(typeof(Musician), "musician_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"));
}
return query;
}
private DataTable SelectQuery(SelectQuery query)
{
Stopwatch timer = new Stopwatch();
timer.Start();
string q = query.ToString();
timer.Stop();
queryTime += timer.ElapsedTicks;
#if DEBUG
if (HttpContext.Current != null)
{
//HttpContext.Current.Response.Write(string.Format("<!-- Query rendered in {0} seconds ({1}) -->\r\n", timer.ElapsedTicks / 10000000.0, query.Tables[0]));
}
#endif
return SelectQuery(q);
}
public override System.Data.Common.DbDataReader ReaderQuery(SelectQuery query)
{
#if DEBUG
Stopwatch timer = new Stopwatch();
timer.Start();
#endif
string q = query.ToString();
#if DEBUG
timer.Stop();
queryTime += timer.ElapsedTicks;
#endif
return SelectReaderQuery(q);
}
public override DataTable Query(SelectQuery query)
{
DataTable selectDataTable = SelectQuery(query);
return selectDataTable;
}
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();
}
}
}
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 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;
}
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<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;
}
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();
}
}
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="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;
}
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;
}
/// <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 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 static new SelectQuery GetSelectQueryStub(Core core, UserLoadOptions loadOptions)
{
SelectQuery query = new SelectQuery("user_relations");
query.AddFields(UserRelation.GetFieldsPrefixed(core, typeof(UserRelation)));
query.AddFields(User.GetFieldsPrefixed(core, typeof(User)));
query.AddFields(ItemInfo.GetFieldsPrefixed(core, typeof(ItemInfo)));
query.AddJoin(JoinTypes.Inner, User.GetTable(typeof(User)), "relation_you", "user_id");
TableJoin join = query.AddJoin(JoinTypes.Left, new DataField(typeof(UserRelation), "relation_you"), new DataField(typeof(ItemInfo), "info_item_id"));
join.AddCondition(new DataField(typeof(ItemInfo), "info_item_type_id"), ItemKey.GetTypeId(core, typeof(User)));
if ((loadOptions & UserLoadOptions.Info) == UserLoadOptions.Info)
{
query.AddFields(UserInfo.GetFieldsPrefixed(core, typeof(UserInfo)));
query.AddJoin(JoinTypes.Inner, UserInfo.GetTable(typeof(UserInfo)), "relation_you", "user_id");
}
if ((loadOptions & UserLoadOptions.Profile) == UserLoadOptions.Profile)
{
query.AddFields(UserProfile.GetFieldsPrefixed(core, typeof(UserProfile)));
query.AddJoin(JoinTypes.Inner, UserProfile.GetTable(typeof(UserProfile)), "relation_you", "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"));
}
/*if ((loadOptions & UserLoadOptions.Icon) == UserLoadOptions.Icon)
{
query.AddField(new DataField("gallery_items", "gallery_item_uri"));
query.AddField(new DataField("gallery_items", "gallery_item_parent_path"));
query.AddJoin(JoinTypes.Left, new DataField("user_info", "user_icon"), new DataField("gallery_items", "gallery_item_id"));
}*/
return query;
}
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;
}
public static SelectQuery GetSelectQueryStub(Core core, NetworkLoadOptions loadOptions)
{
SelectQuery query = new SelectQuery(Network.GetTable(typeof(Network)));
query.AddFields(Network.GetFieldsPrefixed(core, typeof(Network)));
if ((loadOptions & NetworkLoadOptions.Info) == NetworkLoadOptions.Info)
{
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"));*/
}
}
return query;
}
// 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 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();
}
}
}
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;
}
}
