void GetRiskModTestSiteAndForum(IDnaDataReader reader, out int siteid, out int forumid) { reader.ExecuteWithinATransaction(@" select top 1 g.forumid,g.siteid from topics t join guideentries g on g.h2g2id=t.h2g2id order by topicid"); reader.Read(); forumid = reader.GetInt32("forumid"); siteid = reader.GetInt32("siteid"); reader.Close(); }
int CountEventQueueEntries(IDnaDataReader reader) { reader.ExecuteWithinATransaction("select count(*) as C from eventqueue"); reader.Read(); int c = reader.GetInt32("C"); reader.Close(); return c; }
void ClearRiskModThreadEntryQueue(IDnaDataReader reader) { reader.ExecuteWithinATransaction("delete from RiskModThreadEntryQueue"); reader.Close(); }
void SetUpEmailEventQueue(IDnaDataReader reader) { string sql = @" DECLARE @NodeType int, @ArticleType int, @ClubType int, @ForumType int, @ThreadType int, @PostType int, @UserType int, @VoteType int, @LinkType int, @TeamType int, @URLType int EXEC SetItemTypeValInternal 'IT_NODE', @NodeType OUTPUT EXEC SetItemTypeValInternal 'IT_H2G2', @ArticleType OUTPUT EXEC SetItemTypeValInternal 'IT_CLUB', @ClubType OUTPUT EXEC SetItemTypeValInternal 'IT_FORUM', @ForumType OUTPUT EXEC SetItemTypeValInternal 'IT_THREAD', @ThreadType OUTPUT EXEC SetItemTypeValInternal 'IT_POST', @PostType OUTPUT EXEC SetItemTypeValInternal 'IT_USER', @UserType OUTPUT EXEC SetItemTypeValInternal 'IT_VOTE', @VoteType OUTPUT EXEC SetItemTypeValInternal 'IT_LINK', @LinkType OUTPUT EXEC SetItemTypeValInternal 'IT_CLUB_MEMBERS', @TeamType OUTPUT EXEC SetItemTypeValInternal 'IT_URL', @URLType OUTPUT -- Now get all the values for the different events that can happen DECLARE @ArticleEdit int, @ArticleTagged int, @TaggedArticleEdited int, @ForumEdit int, @NewTeamMember int, @PostRepliedTo int, @NewThread int, @ThreadTagged int DECLARE @UserTagged int, @ClubTagged int, @LinkAdded int, @VoteAdded int, @VoteRemoved int, @OwnerTeamChange int, @MemberTeamChange int, @MemberApplication int, @ClubEdit int, @NodeHidden int EXEC SetEventTypeValInternal 'ET_ARTICLEEDITED', @ArticleEdit OUTPUT EXEC SetEventTypeValInternal 'ET_CATEGORYARTICLETAGGED', @ArticleTagged OUTPUT EXEC SetEventTypeValInternal 'ET_CATEGORYARTICLEEDITED', @TaggedArticleEdited OUTPUT EXEC SetEventTypeValInternal 'ET_FORUMEDITED', @ForumEdit OUTPUT EXEC SetEventTypeValInternal 'ET_NEWTEAMMEMBER', @NewTeamMember OUTPUT EXEC SetEventTypeValInternal 'ET_POSTREPLIEDTO', @PostRepliedTo OUTPUT EXEC SetEventTypeValInternal 'ET_POSTNEWTHREAD', @NewThread OUTPUT EXEC SetEventTypeValInternal 'ET_CATEGORYTHREADTAGGED', @ThreadTagged OUTPUT EXEC SetEventTypeValInternal 'ET_CATEGORYUSERTAGGED', @UserTagged OUTPUT EXEC SetEventTypeValInternal 'ET_CATEGORYCLUBTAGGED', @ClubTagged OUTPUT EXEC SetEventTypeValInternal 'ET_NEWLINKADDED', @LinkAdded OUTPUT EXEC SetEventTypeValInternal 'ET_VOTEADDED', @VoteAdded OUTPUT EXEC SetEventTypeValInternal 'ET_VOTEREMOVED', @VoteRemoved OUTPUT EXEC SetEventTypeValInternal 'ET_CLUBOWNERTEAMCHANGE', @OwnerTeamChange OUTPUT EXEC SetEventTypeValInternal 'ET_CLUBMEMBERTEAMCHANGE', @MemberTeamChange OUTPUT EXEC SetEventTypeValInternal 'ET_CLUBMEMBERAPPLICATIONCHANGE', @MemberApplication OUTPUT EXEC SetEventTypeValInternal 'ET_CLUBEDITED', @ClubEdit OUTPUT EXEC SetEventTypeValInternal 'ET_CATEGORYHIDDEN', @NodeHidden OUTPUT declare @uid uniqueidentifier,@ItemID int,@ItemType int,@EventType int,@ItemID2 int,@ItemType2 int,@NotifyType int set @uid=newid() -- Set up the email alert lists INSERT INTO [dbo].[EMailAlertList] ([EMailAlertListID],[UserID],[CreatedDate],[LastUpdated],[SiteID]) VALUES (@uid, 6, getdate(), getdate(),1) INSERT INTO [dbo].InstantEMailAlertList (InstantEMailAlertListID,[UserID],[CreatedDate],[LastUpdated],[SiteID]) VALUES (@uid, 6, getdate(), getdate(),1) -- Now insert a bunch of email event queue items to test the various branches -- case 1 & 2 select top 1 @ItemID =h.nodeid,@ItemType =@NodeType,@EventType =@UserTagged,@ItemID2 =0,@ItemType2 =0,@NotifyType =0 from Hierarchy h INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1) -- case 3 & 4 -- can't be done because inner joins on threads where threadid=0 --case 5 & 6 select top 1 @ItemID =f.forumid,@ItemType =@ForumType,@EventType =@ForumEdit,@ItemID2 =t.threadid,@ItemType2 =@ThreadType,@NotifyType =0 from forums f join threads t on t.forumid=f.forumid where t.VisibleTo IS NULL INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1) -- case 7 & 8 select top 1 @ItemID =t.threadid,@ItemType =@ThreadType,@EventType =@PostRepliedTo,@ItemID2 =te.entryid,@ItemType2 =@PostType,@NotifyType =0 from forums f join threads t on t.forumid=f.forumid join threadentries te on te.threadid=t.threadid where t.VisibleTo IS NULL and te.hidden is null INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1) -- case 9 & 10 select top 1 @ItemID =h.nodeid,@ItemType =@NodeType,@EventType =@ThreadTagged,@ItemID2 =t.threadid,@ItemType2 =@ThreadType,@NotifyType =0 from forums f join hierarchy h on nodeid=nodeid join threads t on t.forumid=f.forumid where t.VisibleTo IS NULL INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1) -- case 11, 12, 13, 14, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28 - clubs, not in use so not tested -- case 15 & 16 select top 1 @ItemID =h.nodeid,@ItemType =@NodeType,@EventType =@ArticleTagged,@ItemID2 =g.h2g2id,@ItemType2 =@ArticleType,@NotifyType =0 from hierarchy h, guideentries g where g.hidden IS NULL INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1) -- case 17 & 18 select top 1 @ItemID =g.h2g2id,@ItemType =@ArticleType,@EventType =@ArticleEdit,@ItemID2 =0,@ItemType2 =0,@NotifyType =0 from guideentries g where g.hidden IS NULL INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1) -- case 19 & 20 select top 1 @ItemID =g.h2g2id,@ItemType =@ArticleType,@EventType =@ArticleEdit,@ItemID2 =0,@ItemType2 =0,@NotifyType =0 from guideentries g where g.hidden IS NULL INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1) -- case 29 & 30 select top 1 @ItemID =h.nodeid,@ItemType =0,@EventType =@NodeHidden,@ItemID2 =h.nodeid,@ItemType2 =0,@NotifyType =0 from hierarchy h INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner]) VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)"; reader.ExecuteWithinATransaction(sql); reader.Close(); }
void ClearEventQueue(IDnaDataReader reader) { reader.ExecuteWithinATransaction("delete from eventqueue"); reader.Close(); }
string GetNextIndentityId(IDnaDataReader reader) { string sql = @"select IdentityUserID from signinuseridmapping where cast(identityuserid as bigint) = (select max(cast(identityuserid as bigint)) from signinuseridmapping)"; reader.ExecuteWithinATransaction(sql); reader.Read(); string identityId = reader.GetString("IdentityUserID"); reader.Close(); Int64 id = Int64.Parse(identityId); return (id+1).ToString(); }
void EmailEnc_SetUp_fetchrecommendationdetails(IDnaDataReader reader, out int recId, out int entryId) { // Find a Scout Recommendation that matches the SP's join requirements string sql = @"select top 1 RecommendationID, SR.EntryId" + NL + "from ScoutRecommendations SR" + NL + "inner join GuideEntries G on G.EntryID = SR.EntryID" + NL + "inner join Users U1 on U1.UserID = SR.ScoutID" + NL + "inner join Users U2 on U2.UserID = G.Editor" + NL + "INNER JOIN Journals J1 on J1.UserID = U1.UserID and J1.SiteID = G.SiteID" + NL + "INNER JOIN Journals J2 on J2.UserID = U2.UserID and J2.SiteID = G.SiteID"; reader.ExecuteWithinATransaction(sql); reader.Read(); recId = reader.GetInt32("RecommendationID"); entryId = reader.GetInt32("EntryId"); reader.Close(); // Set up the scout and editor user ids to something predictable reader.ExecuteWithinATransaction("UPDATE ScoutRecommendations SET ScoutId=42 WHERE RecommendationID=" + recId); reader.ExecuteWithinATransaction("UPDATE GuideEntries SET Editor=6 WHERE EntryId=" + entryId); // Set up the email adresses in the user accounts UpdateUserEmailAddress(reader, 42, "*****@*****.**"); UpdateUserEmailAddress(reader, 6, "*****@*****.**"); reader.Close(); }
SqlBinary HashEmailAddress(IDnaDataReader reader,string email) { reader.ExecuteWithOpenKey("select dbo.udf_hashemailaddress('" + email + "') AS HashedEmail"); reader.Read(); var hashedEmail = reader.GetSqlBinary("HashedEmail"); reader.Close(); return hashedEmail; }
void AddEmailToBannedList(IDnaDataReader reader, string email, int siginBanned, int complaintBanned) { string sql = @"EXEC addemailtobannedlist @email='" + email + "', @signinbanned =" + siginBanned + ", @complaintbanned =" + complaintBanned + ", @editorid = 6"; reader.ExecuteWithinATransaction(sql); reader.Close(); }
GuideEntryInfo GetGuideEntryInfo(IDnaDataReader reader,int entryId) { string sql = @"select * from guideentries where entryid="+entryId; reader.ExecuteWithinATransaction(sql); reader.Read(); var info = new GuideEntryInfo(); info.EntryId = reader.GetInt32("entryid"); info.Editor = reader.GetInt32("editor"); reader.Close(); return info; }
void UpdateUserEmailAddress(IDnaDataReader reader, int userId, string email) { string sql; if (email != null) sql = string.Format(@"update users set encryptedemail=dbo.udf_encryptemailaddress('{0}',{1}) where userid={1}",email,userId); else sql = string.Format(@"update users set encryptedemail=dbo.udf_encryptemailaddress(NULL,{0}) where userid={0}",userId); reader.ExecuteWithOpenKey(sql); reader.Close(); }
int FindGuideEntry(IDnaDataReader reader) { string sql=@"select top 1 * from guideentries where text like '<GUIDE%' and siteid=1"; reader.ExecuteWithinATransaction(sql); reader.Read(); int entryId = reader.GetInt32("entryid"); reader.Close(); return entryId; }
int CreateNewUser(IDnaDataReader reader, string email) { string id = GetNextIndentityId(reader); string sql = string.Format(@"EXEC [dbo].[createnewuserfromidentityid] @identityuserid ='{0}',"+NL+ "@legacyssoid = null,"+NL+ "@username = '******',"+NL+ "@email = '{1}'",id, email); reader.ExecuteWithinATransaction(sql); sql = "SELECT DnaUserId FROM SignInUserIdMapping WHERE IdentityUserId='" + id + "'"; reader.ExecuteWithinATransaction(sql); reader.Read(); int userId = reader.GetInt32("DnaUserId"); reader.Close(); return userId; }
int GetNextUserId(IDnaDataReader reader) { reader.ExecuteWithinATransaction(@"select max(userid) AS maxuserid from users"); reader.Read(); int maxuserId = reader.GetInt32("maxuserid"); reader.Close(); return maxuserId + 1; }
void RiskModTestPostHelper(IDnaDataReader reader, int forumid, int? threadid, int? inreplyto, int userid, string content, out int? newthreadid, out int? newthreadentryid, bool ignoremoderation, bool forcepremodposting) { string sql = string.Format(@" -- Pretend this user has never posted to this forum, to make sure the check in posttoforuminternal -- that stops the same user from creating a new conversation within a minute of the last one, doesn't stop the post update threadentries set forumid=-{0} where forumid={0} and userid={3} declare @hash uniqueidentifier, @returnthread int, @returnpost int, @premodpostingmodid int, @ispremoderated int set @hash=newid() exec posttoforuminternal @userid= {3}, @forumid = {0}, @inreplyto = {2}, @threadid = {1}, @subject ='The Cabinet', @content ='{4}', @poststyle =1, @hash =@hash, @keywords ='', @nickname ='the furry one', @returnthread = @returnthread OUTPUT, @returnpost = @returnpost OUTPUT, @type = NULL, @eventdate = NULL, @forcemoderate = 0, @forcepremoderation = 0, @ignoremoderation = {6}, @allowevententries = 1, @nodeid = 0, @ipaddress = 'testip', @queueid = null, @clubid = 0, @premodpostingmodid =@premodpostingmodid OUTPUT, @ispremoderated =@ispremoderated OUTPUT, @bbcuid = '{5}', @isnotable = 0, @iscomment = 0, @modnotes = NULL, @isthreadedcomment = 0, @ignoreriskmoderation = 0, @forcepremodposting = {7}, @forcepremodpostingdate = NULL, @riskmodthreadentryqueueid = NULL; -- put the userids back update threadentries set forumid={0} where forumid =-{0} and userid={3} select @returnthread AS returnthread, @returnpost as returnpost", forumid, threadid.HasValue ? threadid.ToString() : "NULL", inreplyto.HasValue ? inreplyto.ToString() : "NULL", userid, content, testGUID.ToString(), ignoremoderation ? 1 : 0, forcepremodposting ? 1 : 0); reader.ExecuteWithinATransaction(sql); reader.Read(); newthreadid = reader.GetNullableInt32("returnthread"); newthreadentryid = reader.GetNullableInt32("returnpost"); reader.Close(); }
void FixUpModActionTable(IDnaDataReader reader) { reader.ExecuteWithinATransaction("delete ModAction"); reader.ExecuteWithinATransaction("insert ModAction select 0,'None' UNION select 1,'Edited' UNION select 2,'Hidden' UNION select 3,'Unhidden'"); reader.Close(); }
void CheckRiskModThreadEntryQueue(IDnaDataReader reader, int? ThreadEntryId, char PublishMethod, bool? IsRisky, TestDate DateAssessed, int SiteId, int ForumId, int? ThreadId, int UserId, string UserName, int? InReplyTo, string Subject, string Text, byte PostStyle, string IPAddress, string BBCUID, DateTime EventDate, byte AllowEventEntries, int NodeId, int? QueueId, int ClubId, byte IsNotable, byte IsComment, string ModNotes, byte IsThreadedComment) { reader.ExecuteWithinATransaction(@"SELECT rm.* FROM RiskModThreadEntryQueue rm WHERE RiskModThreadEntryQueueId=" + GetLatestRiskModThreadEntryQueueId(reader)); reader.Read(); // Nasty tweak. The RiskModThreadEntryQueue table always stores undefined ThreadId and ThreadEntryId values // as NULL. We treat zero values as NULL for comparision purposes ThreadId = NullIf(ThreadId, 0); ThreadEntryId = NullIf(ThreadEntryId, 0); TestNullableIntField(reader, "ThreadEntryId", ThreadEntryId); TestPublishMethod(reader, PublishMethod); TestNullableBoolField(reader, "IsRisky", IsRisky); TestNullableDateField(reader, "DateAssessed", DateAssessed); Assert.AreEqual(SiteId,reader.GetInt32("SiteId")); Assert.AreEqual(ForumId,reader.GetInt32("ForumId")); TestNullableIntField(reader, "ThreadId", ThreadId); Assert.AreEqual(UserId,reader.GetInt32("UserId")); Assert.AreEqual(UserName, reader.GetString("UserName")); TestNullableIntField(reader, "InReplyTo", InReplyTo); Assert.AreEqual(Subject,reader.GetString("Subject")); Assert.AreEqual(Text,reader.GetString("Text")); Assert.AreEqual(PostStyle, reader.GetByte("PostStyle")); Assert.AreEqual(IPAddress,reader.GetString("IPAddress")); Assert.AreEqual(BBCUID,reader.GetGuidAsStringOrEmpty("BBCUID")); //Assert.AreEqual(EventDate,reader.GetInt32("EventDate")); Assert.AreEqual(AllowEventEntries,reader.GetByte("AllowEventEntries")); //Assert.AreEqual(NodeId,reader.GetInt32("NodeId")); TestNullableIntField(reader, "QueueId", QueueId); Assert.AreEqual(ClubId,reader.GetInt32("ClubId")); Assert.AreEqual(IsNotable, reader.GetByte("IsNotable")); Assert.AreEqual(IsComment, reader.GetByte("IsComment")); TestNullableStringField(reader, "ModNotes", ModNotes); Assert.AreEqual(IsThreadedComment, reader.GetByte("IsThreadedComment")); reader.Close(); }
// create procedure fetcharticlemoderationhistory @h2g2id int /* use smallguide begin tran select j1.forumid,u1.* FROM Users u1 INNER JOIN Journals J1 ON J1.UserID = u1.UserID and J1.SiteID = 1 --INNER JOIN FaveForums f ON f.ForumID = J1.ForumID --INNER JOIN Users u ON f.UserID = u.UserID --INNER JOIN Journals J on J.UserID = u.UserID --and J.SiteID = 1 --INNER JOIN Forums fo ON fo.ForumID = J.ForumID insert FaveForums values (5819,5) exec watchingusers @userid =5, @siteid =1, @skip = 0, @show = 100000 rollback tran use master */ #endregion #region Helper methods EmailValues GetUserEmailValues(IDnaDataReader reader, int userId) { string sql = string.Format(@"SELECT DecryptedEmail = dbo.udf_decryptemailaddress(EncryptedEmail,UserID), EncryptedEmail, HashedEmail FROM Users WHERE UserId=" + userId); reader.ExecuteWithOpenKey(sql); reader.Read(); EmailValues emailValues = new EmailValues(); emailValues.DecryptedEmail = reader.GetString("DecryptedEmail"); emailValues.EncryptedEmail = reader.GetSqlBinary("EncryptedEmail"); emailValues.HashedEmail = reader.GetSqlBinary("HashedEmail"); reader.Close(); return emailValues; }