Set your Reader Posts to expire automatically
If you're using the Reader application in Community Server, did you know that the posts never expire and will over time add to the size of your database?
Last Friday I wrote an article about how you can create a CSJob that lets you run a SQL Stored Procedure on your CS Database on a scheduled basis. After that, it has been rather quiet in my blog for a little over a week. It has been one of those weeks again, which seems to come every now and then, where I almost had no time to even breath. [:-)] In that article I also said that I will try to post some SQL Scripts that you could use with your new CSJob. Initially I thought I'd create a bunch of them and put in a kind of script pack. But later decided to publish one by one here, and also put a brief explanation along which each script. Some scripts will be made by me, others by other people in the community, in which case I will refer to their blog.
In this post I wanted to talk about a script to delete old posts that are stored for the Reader application in CS. If you have used other RSS readers before, you are probably used to that they expire their posts after a configurable time. Or in a different Reader it might not matter so much since maybe you're running a local client on your machine, where all is stored on your hard disk, or you might run a hosted application (like Google Reader) where you yourself don't have to bother about the size of the posts.
If you're running CS at a hoster, you probably want to keep the DB Size as low as you can so you don't have to pay for extra DB space. To clean up the DB from Reader Posts, you would probably want to delete posts of a certain age and then schedule that to run each week (or whatever time period you'd want to choose).
The Tables
The tables involved in this operation is cs_FeedPost and cs_UserReadPost.
Click on the picture to view it in original size
As you can see in the picture above, there is a one to many relation between the two. And in this case you'd want to delete rows in the cs_UserReadPost first. After that the corresponding rows in cs_FeedPost.
The Script
The following sample script will do the work.
Click on the picture to view it in original size
The cs_UserReadPost table has only 2 columns, UserID and FeedPostID. So first we need to get which FeedPostID's are old enough to delete. In this script this is accomplished by first creating a temporary table to fill up with those old FeedPostID's. A variable for number of days is declared, and in this case a value of 60 is set. This mean this script will delete all data related to Reader Posts older than 60 days from today. Change that value to what you prefer.
The script inserts FeedPostID's from cs_FeedPost that are older than 60 days. Remember cs_UserReadPost don't have a date value, so this is a way to get those FeedPostID's.
From here, the script has a DELETE statement to delete all rows in cs_UserReadPost that has corresponding ID's in the temp table created earlier. And after that another DELETE statement to delete all posts in the cs_FeedPost that are older than 60 days.
In the end, it's good practice to DROP the temporary table we created in the beginning of the script. We don't need it anymore.
So, if you use the Reader application in CS, for yourself, or maybe even for several users, you know have a sample SQL Script to use i.e. together with i.e the SQL Stored Procedure CSJob, to set expiration for old Reader Posts.
If you enjoyed this post Subscribe to my feed via RSS or e-mail!