public static async Task <UserPostingHistory> GetUserPostingHistory(string userName) { using (var con = DirtBagConnection.GetConn()) { var query = "" + "Select up.ChannelID, up.ThingID " + "FROM dirtbag.UserPosts up " + //"left JOIN dirtbag.PostRemovals rem on up.PostID = rem.PostID " + "where username like @UserName " + "and up.ChannelID is not null " + //"group by channelid"; ""; UserPostingHistory results = new UserPostingHistory(); results.UserName = userName; results.PostingHistory = new Dictionary <string, List <string> >(); var vals = await con.QueryAsync(query, param : new { userName }); foreach (dynamic kv in vals) { if (!results.PostingHistory.ContainsKey(kv.ChannelID)) { results.PostingHistory.Add(kv.ChannelID, new List <string>()); } results.PostingHistory[kv.ChannelID].Add(kv.ThingID); } return(results); } }
static void Main( string[] args ) { ActiveModules = new List<IModule>(); //Instantiate and throw away a Reddit instance so the static constructor won't interfere with the WebAgent later. new Reddit(); var conn = new DirtBagConnection(); var sub = ConfigurationManager.AppSettings["Subreddit"]; conn.InitializeConnection( new[] { sub } ); Initialize(); string baseAddresses = System.Configuration.ConfigurationManager.AppSettings["ApiListeningUrls"]; baseAddresses += "," + string.Join(",",args); if ( !string.IsNullOrWhiteSpace( baseAddresses ) ) { var opts = new StartOptions(); foreach ( string address in baseAddresses.Split( ',' ) ) { if ( !string.IsNullOrWhiteSpace( address ) ) { opts.Urls.Add( address ); } } _app = WebApp.Start<Startup>( opts ); } WaitHandle.WaitOne(); //Go the f**k to sleep }
public static UserPost AddRemoval(PostRemoval removal) { using (var conn = DirtBagConnection.GetConn()) { var query = "" + //"declare @theUserPost Table( " + //"[PostID] INTEGER, " + ////"[UserID] INTEGER NOT NULL, " + //"[UserName] varchar(50), " + //"[Link] varchar(200), " + //"[ChannelID] varchar(100), " + //"[ChannelName] varchar(200), " + //"[Subreddit] varchar(100) ) " + //"" + "insert into UserPosts (UserName,ThingID,Link,PostTime,ChannelID,ChannelName,Subreddit) " + "select @UserName, @ThingID, @Link, @PostTime, @ChannelID, @ChannelName, @Subreddit " + "WHERE NOT EXISTS " + "(select PostID from UserPosts where Link = @Link) " + "; " + //"insert into @theUserPostTable " + //"select top 1 PostID, UserName, Link, ChannelID, ChannelName, Subreddit " + //"FROM UserPosts " + //"Where Link = @Link " + "; " + "insert into PostRemovals (TimeStamp,ModName,Reason,PostID) " + "select @TimeStamp, @ModName, @Reason, PostID " + "from UserPosts " + "WHERE Link = @Link " + "; " + "select * from UserPosts where Link = @Link;"; var toReturn = conn.Query <UserPost>(query, new { removal.Post.UserName, removal.Post.ThingID, removal.Post.Link, removal.Post.PostTime, removal.Post.ChannelID, removal.Post.ChannelName, removal.Post.Subreddit, removal.TimeStamp, removal.ModName, removal.Reason }).Single(); if (string.IsNullOrEmpty(toReturn.ChannelName)) { return(toReturn); } return(null); } }
public static void UpdateProcessedPost(ProcessedPost post) { byte[] serialized = Helpers.ProcessedPostHelpers.SerializeAndCompressResults(post); var query = "" + "Update ProcessedPosts " + "Set ActionID = (select ID from Actions where ActionName = @Action), AnalysisResults = @AnalysisResults, SeenByModules = @SeenByModules " + //"FROM ProcessedPosts pp " + //"inner join Actions act on act.ActionName = @Action " + "where PostID like @PostID " + ";"; using (var conn = DirtBagConnection.GetConn()) { conn.Execute(query, new { AnalysisResults = serialized, post.Action, post.PostID, post.SeenByModules }); } }
public static void UpdatePost(UserPost post) { if (post.Link.Length > 200) { post.Link = post.Link.Substring(0, 200); } using (var conn = DirtBagConnection.GetConn()) { var query = "" + "update UserPosts " + "set UserName = @UserName, ThingID = @ThingID, Link = @Link, PostTime = @PostTime, ChannelID = @ChannelID, " + "ChannelName = @ChannelName, Subreddit = @Subreddit " + "WHERE PostID = @PostID"; conn.Execute(query, new { post.UserName, post.ThingID, post.Link, post.PostTime, post.ChannelID, post.ChannelName, post.Subreddit, post.PostID }); } }
public static void InsertPost(UserPost post) { if (post.Link.Length > 200) { post.Link = post.Link.Substring(0, 200); } using (var conn = DirtBagConnection.GetConn()) { var query = "" + "insert into UserPosts (UserName,ThingID,Link,PostTime,ChannelID,ChannelName,Subreddit) " + "select @UserName, @ThingID, @Link, @PostTime, @ChannelID, @ChannelName, @Subreddit " + "WHERE NOT EXISTS " + "(select PostID from UserPosts where Link = @Link) " + ";"; conn.Execute(query, new { post.UserName, post.ThingID, post.Link, post.PostTime, post.ChannelID, post.ChannelName, post.Subreddit }); } }
public static void AddProcessedPost(ProcessedPost post) { //string jsonAnalysisResults = JsonConvert.SerializeObject( post.AnalysisResults,Formatting.None ); byte[] serialized = Helpers.ProcessedPostHelpers.SerializeAndCompressResults(post); //System.Text.Encoding.ASCII.GetBytes( jsonAnalysisResults ); //This would need to change to support unicode report reasons & full explanations var query = "" + "insert into ProcessedPosts (SubredditID,PostID,ActionID,SeenByModules,AnalysisResults) " + "select sub.ID, @PostID, act.ID, @SeenByModules, @AnalysisResults " + "from Subreddits sub " + "inner join Actions act on act.ActionName = @Action " + "where sub.SubName like @SubName" + ";"; using (var conn = DirtBagConnection.GetConn()) { conn.Execute(query, new { post.SubName, post.PostID, post.Action, post.SeenByModules, AnalysisResults = serialized }); } }
public static UserAnalysis GetUserAnalysis(string UserName) { using (var con = DirtBagConnection.GetConn()) { var query = "" + "Select up.ChannelID,count( up.postID ), count( distinct rem.PostID ) " + "FROM dirtbag.UserPosts up " + "left JOIN dirtbag.PostRemovals rem on up.PostID = rem.PostID " + "where username like @UserName " + "group by channelid"; var analysis = new UserAnalysis(); analysis.UserName = UserName; analysis.Posts = new List <UserPost>(); var posts = new Dictionary <int, UserPost>(); var result = con.Query <UserPost, PostRemoval, UserAnalysis>(query, (up, pr) => { UserPost post; if (!posts.ContainsKey(up.PostID)) { up.Removals = new List <PostRemoval>(); posts.Add(up.PostID, up); } post = posts[up.PostID]; if (pr != null) { post.Removals.Add(pr); } return(analysis); }, splitOn: "RemovalID", param: new { UserName }); analysis.Posts = posts.Values.ToList(); return(analysis); } }
public static List <ProcessedPost> GetProcessed(List <string> postIDs) { var tableParam = new DataTable(); tableParam.Columns.Add("postID", typeof(string)); foreach (string postID in postIDs) { tableParam.Rows.Add(postID); } var query = @" select sub.SubName, p.PostID, act.ActionName as ""Action"",p.SeenByModules, p.AnalysisResults from ProcessedPosts p inner join Subreddits sub on sub.ID = p.SubredditID inner join Actions act on act.ID = p.ActionID inner join @postIDs pids on pids.PostID = p.PostID ;"; using (var conn = DirtBagConnection.GetConn()) { return(conn.Query <ProcessedPost, byte[], ProcessedPost>(query, (pp, b) => { pp.AnalysisResults = Helpers.ProcessedPostHelpers.InflateAndDeserializeResults(b); return pp; }, splitOn: "AnalysisResults", param: new { postIDs = tableParam.AsTableValuedParameter("postIDs") }).ToList()); } }
public static DateTime?GetLastProcessedRemovalDate(string sub) { using (var conn = DirtBagConnection.GetConn()) { var query = "" + "select MAX(TimeStamp) " + "FROM PostRemovals pr " + "inner join UserPosts up on pr.PostID = up.PostID " + "WHERE " + "up.Subreddit = @sub "; var time = conn.Query <DateTime?>(query, new { sub }).Single(); if (!time.HasValue) { return(null); } if (DirtBagConnection.UseLocalDB) { return(time.Value.ToUniversalTime()); } else { return(time.Value); } } }