Using PowerShell, we’ll step through the RSS feeds, parse out the important bits, then store those bits in the database. INSERT INTO anthology.Archive (PostURL, BlogName, PostTitle,PostAuthor, PostPublishDate, TweetText, IsTweeted) ON anthology.Archive (BlogName, PostTitle, PostAuthor, PostUrl)ĬREATE OR ALTER PROCEDURE varchar(30) = nvarchar(150) = varchar(50) = datetime2(0) = nvarchar(340) = bit = NULL Twitter doesn't count the full URL length, but I need to consider it.ĬREATE INDEX IX_anthologyArchiveUntweeted TweetText is 340 because I'm not storing the shortened URL. IsTweeted bit NOT NULL CONSTRAINT DF_anthologyIsTweeted DEFAULT 0,ĬONSTRAINT PK_anthologyArchive PRIMARY KEY CLUSTERED (PostUrl) WITH (DATA_COMPRESSION=PAGE), TweetText nvarchar(340) COLLATE Latin1_General_CI_AS, -Make sure we support emoji PostAuthor nvarchar(50) COLLATE Latin1_General_CI_AS, PostTitle nvarchar(150) COLLATE Latin1_General_CI_AS, PostUrl nvarchar(200) COLLATE Latin1_General_CI_AS,īlogName nvarchar(30) COLLATE Latin1_General_CI_AS, So, I’ll create another table (anthology.Archive) to keep track of what I’ve tweeted, as well as an upsert procedure (anthology.Archive_Upsert) to insert/update rows: -Create a table To accomplish this, I want to store the individual blog post URLs and metadata(not the post content) in my database, too. I’m going to rate-limit my bot, so that I only tweet once every half hour–so I need to keep track of what’s been tweeted, and what hasn’t. A procedure to use to fetch the list when I need itĬREATE OR ALTER PROCEDURE anthology.BlogList_Get INSERT INTO anthology.Blog (DisplayName,RssUri) RssUri nvarchar(200) COLLATE Latin1_General_CI_AS,ĬONSTRAINT PK_anthologyBlog PRIMARY KEY CLUSTERED (BlogID) I’m a DBA, so I created a table (anthology.Blog) in an Azure SQL DB, and a wee little stored procedure (anthology.BlogList_Get) to get the list of blogs: -Create a tableĭisplayName nvarchar(30) COLLATE Latin1_General_CI_AS, What feeds do I care about? ( aside: What feeds do you care about? Comment below and help me build my reading list.) I need a way to configure which feeds the job should care about. That’s four big ugly strings of gibberish. Ultimately you want to get two key/secret pairs: a “Consumer Key and Secret” and “Access Token and Secret”. The only special step is that you’ll need to go to, and do some setup. The process is pretty straightforward, so I won’t go into details. Step 1) Create & configure a twitter accountĬreate a Twitter account, just like any other. Best of all, I don’t need to check a dedicated app. I can do it regardless of what device I am using at the time. I’ll also be able to go directly to that twitter handle and get to a reading list. Host it in Azure on some existing resourcesīy using Twitter, I’ll still have the posts thrust in front of me during my normal tweeting.Use PowerShell to tweet those blog posts from my dedicated account Throttle my tweeting so I don’t spam myself.Create a dedicated twitter account to act as my aggregated feed: Use PowerShell to scrape RSS feeds that I care about.I decided I would turn Twitter into an RSS aggregator. I was inspired by Chrissy LeMaire’s ( blog| twitter) tweetbot where her bot tweets when Connect items are closed as fixed. Twitter isn’t working for me, but rather than jumping ship, I want to make it better. Twitter isn’t good enough, so I decided twitter was the answer. (I know, I was pretty shocked to find out about this, too.) Or sometimes, I want to read someone’s blog without having to actually follow them. I miss stuff that I want to see.Īnother problem is that not everyone is on twitter. My feed is filled with dog pictures, politics, dumb jokes, random conversation….oh, and SQL blogs. I only read a fraction of what gets posted. One problem with twitter is that my feed moves fast. They tweet blog posts from others that are interesting. I follow people on twitter that I want to hear from. If you know me, this won’t be a surprise. How I find read blogs today, and why that’s not good enough When I have to go hunt for blogs to read, I tend to not do it. I need blogs to be organized in a way that they are right in my face. Its actually quite simple: I can’t be bothered to deal with yet another single-purpose app. Its time to find a solution that really works.īefore I can find something that works, I need to think about Why RSS readers don’t work for me Its time to stop doing the same thing over and over again, hoping it will work this time. Come up with a list of blogs that I like to read.Dig through reviews to find an app that I think I will love.For some reason, about once a year, I go hunting for a new RSS reader so I can keep track of blogs that I like to read.
0 Comments
Leave a Reply. |