Skin Spider : Tracking Video Usage Statics And Mass Deletion
Posted November 6, 2006 at 3:28 PM by Ben Nadel
To learn all about Skin Spider, click here.
To view the most updated application, click here.
To view the current code base, click here.
Originally, the application was providing the user with links directly to the downloaded video files (ex. 15.mwv). This worked fine from a user's standpoint, but provided the application with no useful information. We (the application) had no sense of how often the user was viewing videos or even if a video had ever been accessed. To remedy this, I have rerouted all the video file requests through an intermediary page, view_video.cfm. This view_video.cfm file stores the video request in the database and then forwards the user onto the appropriate video file.
From the user's point of view, nothing has changed; they clicked on a link and got the video. From our standpoint, however, the world has changed. We now know all videos the user has asked for, how often a video has been viewed, which videos are viewed, which are not, and pretty much anything else that has to do with video access. While we don't really care about the user's application usage, we can now provide the user with the ability to delete videos in mass. This should give the user a very efficient and meaningful way to clean the video library.
I have updated the primary navigation in the _header_standard.cfm ColdFusion template to include a section titled "Clean Library." This section allows the user to delete multiple videos at one time based on several different types of criteria.
But, before I get into that, let me just go through a few changes. For starters, I had to add a database table to keep track of video viewing. In the Config.cfc ColdFusion component, I have added the table "video_viewing" to the XML database schema. This just stores the video ID that was requested and the date on which it was requested.
Then, I created the view_video.cfm ColdFusion template. If you take a look at that code, you will see it is quite simple. I get the requested video, add the video request to the video_viewing database table, and then perform a CFLocation to the video file. I have reworked the links on home.cfm, search.cfm, and favorites.cfm to link to this view_video.cfm tmeplate rather than directly to the video file.
So that's all I had to do to start tracking video usage. Now, we can go into the Clean Library functionality. On this page, clean_library.cfm, I have two types of delete criteria: the user can delete all videos that have been viewed less that X times or the user can delete all videos that have not been viewed in the past X weeks.
Due do the limitations of ColdFusion query of queries, I have to perform some extra queries on this page. Where I would normally do some sort of nested or sub query, I now have to perform multiple queries to get the target videos. I don't want to go into it to much here as the code is quite well commented, but basically, I have to figure out the list of video IDs that I am NOT deleting so that I can then get the videos whose IDs are NOT in the previously gotten list. Just check out clean_library.cfm and you will see what I am talking about.
What you may or may not notice is that this page pretty much duplicates the code from the delete_video.cfm template. This is really bad. This is a HUGE violation of the DRY (don't repeat yourself) principle. Now, instead of maintaining delete code in one template, we have to maintain it in two templates. This leaves us open to inconsistencies and errors. This is where we are going to start getting into Controllers, Service Layers, and Data Access Objects (DAO) ... but not yet. Basically, we are going to need a way to abstract out the code that deletes the videos in such a way that we can just invoke it rather than duplicating it in a procedural fashion.
More on this to come when we start to refactor the framework of the application.
