The content you're reading is getting on in years
This post is on the older side and its content may be out of date.
Be sure to visit our blogs homepage for our latest news, updates and information.
Media content has become increasingly important and preferred element for almost every content manager who aims for attracting large audience to their website. Therefore, content managers try to create unique media materials (video, audio, images) that will give them the vital competitive advantage in the dynamic business environment they operate in. Some digital media is available on a paid membership basis and it is important to protect those assets from abusive users.
Recently, Jack Ferreira, CEO and founder of Xkudos, offered an interesting approach to adding another layer of protection to digital assets in Sitefinity CMS. He created a SQL script that changes the URL of each media item, so the download url expires in a given period. The script attaches to the Sitefinity’s database and modifies the URL parameters for media items and prevents URL sharing between users.
As a website owner, Jack wanted to manage only one large video file per video asset and use a custom video player to play the videos. The custom player allows anonymous users to play only 10 seconds of the video, but the same player plays the full length video to authenticated website members.
And while it’s clear that the approach of Mr. Ferreira is interesting in the way that it directly manipulates the Sitefinity database, it can lead to unforeseen issues. Bypassing the Open Access ORM, which controls the Sitefinity data layer could eventually cause issues related with data integrity and dependency in case there is a change in the database structure
Therefore, I decided to share with the community another two approaches to the same scenario by leveraging the Sitefinity build-in native and fluent APIs. We recommend you use the Sitefinity APIs to manipulate data control by Sitefinity. Using the APIs will save you a lot of time and ensure the integrity of your data. Sitefinity product team strived to make everything consistent and intuitive in the API so it can make developers’ life easier and increase their productivity. Bellow you will find examples of how to update Sitefinity video URLs using the Fluent API, Native API, and Jack’s T-SQL script.
The fluent API code snippet below gets all published videos and updates their UrlName property to a random Guid. When working with the API you should execute the code with an authenticated user, who has permissions to modify the respective assets (e.g admin user).
Fluent API code snippet
App.WorkWith()
.Videos()
.Where(v => v.Status.Equals(Telerik.Sitefinity.GenericContent.Model.ContentLifecycleStatus.Live))
.ForEach(v =>
{
v.UrlName = Guid.NewGuid().ToString();
})
.SaveChanges();
The same task can also be accomplished using the native Sitefinity API.
Native API code snippet
//instantiate libraries manager
LibrariesManager manager = LibrariesManager.GetManager();
// get all videos that are in live state
IQueryable<Video> videosList = manager.GetVideos()
.Where(v => v.Status == Telerik.Sitefinity.GenericContent.Model.ContentLifecycleStatus.Live);
foreach (Video video_item in videosList)
{
video_item.UrlName = Guid.NewGuid().ToString();
manager.RecompileItemUrls<Video>(video_item);
}
manager.SaveChanges();
Alternatively to using the API, you can use the Jack’s T-SQL script. The script scans the Sitefinity database for media content and instead of the user-friendly URL that Sitefinity gives to each content item, the script changes these URLs with global unique identifiers that are generated by the SQL server.
Script example
DECLARE @File_Name nvarchar(255)
DECLARE @Content_ID nvarchar(255),@url_name nvarchar(255)
DECLARE @OldUniqueFieNameTemp nvarchar(255)
DECLARE @NewUniqueFieNameTemp uniqueidentifier
-- Find all the Video File types im interested on
DECLARE FileName_Cursor CURSOR FOR ( SELECT DISTINCT file_id FROM sf_media_content WHERE (app_name = '/Libraries') AND (mime_type IN ('application/octet-stream', 'video/x-msvideo', 'video/mpeg')))
OPEN FileName_Cursor;
FETCH FileName_Cursor INTO @File_Name
WHILE @@FETCH_STATUS = 0
BEGIN
set @NewUniqueFieNameTemp = NEWID()
----***********************************************************************
DECLARE FileName_Parts CURSOR FOR (
SELECT content_id,url_name_
FROM sf_media_content
WHERE file_id
in(
@File_Name
)
)
OPEN FileName_Parts;
FETCH FileName_Parts INTO @Content_ID,@url_name
WHILE @@FETCH_STATUS = 0
BEGIN
Set @OldUniqueFieNameTemp = @url_name
--print ':........File Parts:' + @Content_ID + ' - Url_Name' + @url_name + '- Newname: -' + cast(@NewUniqueFieNameTemp as nvarchar(40)) + '.'
-- Run update statement to update Url_name from @OldUniqueFieNameTemp to new @NewUniqueFieNameTemp
UPDATE [dbo].[sf_media_content] SET [url_name_] = Replace(url_name_,@OldUniqueFieNameTemp,cast(@NewUniqueFieNameTemp as nvarchar(40)))
WHERE content_id in(@Content_ID)
----***********************************************************************
DECLARE @Url nvarchar(500)
DECLARE Page_Url CURSOR FOR (
select url
FROM sf_url_data
where app_name='/Libraries'
and content_id
in(
@Content_ID
)
)
OPEN Page_Url;
FETCH Page_Url INTO @Url
WHILE @@FETCH_STATUS = 0
BEGIN
--print ':............' + @Url + '.... ||| NEw URl:' + Replace(@Url,@OldUniqueFieNameTemp,cast(@NewUniqueFieNameTemp as nvarchar(40)))
-- Update oldtemp with the newtemp part
UPDATE [dbo].[sf_url_data]
SET [url] = Replace(@Url,@OldUniqueFieNameTemp,cast(@NewUniqueFieNameTemp as nvarchar(40)))
where app_name='/Libraries' and content_id in(@Content_ID)
FETCH Page_Url INTO @Url
END;
CLOSE Page_Url;
DEALLOCATE Page_Url;
----***********************************************************************
FETCH FileName_Parts INTO @Content_ID,@url_name
END;
CLOSE FileName_Parts;
DEALLOCATE FileName_Parts;
----***********************************************************************
FETCH FileName_Cursor INTO @File_Name
END;
CLOSE FileName_Cursor;
DEALLOCATE FileName_Cursor;
GO
Jack Ferreira suggests that you should implement the script in the following way:
1) Download the SQL script file from the site of its creator and extract the file:
http://www.xkudos.com/Sitefinity_MarketPlace/DailyGenerateUrlForVideosAndImages.rar
2) Open the just extracted DailyGenerateUrlForVideosAndImages.sql file and attach the script to the database of your Sitefinity application via SQL Server Management Studio.
3) Modify the script to update the URLs of the media types you wish, by adding new types of media to the scope of the script. The default media types, that are loaded in the script are: ‘application/octet-stream', 'video/x-msvideo' and 'video/mpeg. Of course, you can easily add more types to associate the script with by modifying line 6 of the script (just under the comment on line 5 that says: “-- Find all the Video File types im interested on”. Here are also the different types of media files and their syntax according to the MIME (Multipurpose Internet Mail Extensions) convention which is also used by Sitefinity and its database:
MIME common media types |
| Description |
video/mpeg | MPEG-1 video with multiplexed audio | |
video/mp4 | MP4 video | |
video/ogg | Ogg Theora or other video | |
video/quicktime | QuickTime video | |
video/webm | WebM open media format | |
video/x-ms-wmv | Windows Media Video | |
application/octet-stream | Arbitrary binary data | |
application/ogg | Ogg, a multimedia bitstream container format; | |
application/pdf | Portable Document Format | |
application/x-shockwave-flash | Adobe Flash files | |
image/gif | GIF image | |
image/jpeg | JPEG JFIF image | |
image/png | Portable Network Graphics |
More information on: http://en.wikipedia.org/wiki/Internet_media_type#Type_video
4) You can run the script manually, but you can also automate this process. To programmatically schedule the SQL driven generation of unique URLs, we need a bat file that does nothing more but the execution of the .sql file.
Here is the content to insert in the .bat file:
echo on
rem First BACKUP FILES
sqlcmd -S . -i "c:\YourPath\RegenerateVideosPicturesDailyIds.sql"
rem pause
Then all we have to do is to schedule our OS to run this file over a desired interval of time according to our requirements. Just remember to change “c:\YourPath“ with the actual location the extracted RegenerateVideosPicturesDailyIds.sql.
Note that you can also schedule the execution of the API code snippets or trigger them on demand.
I want to once again thank Jack Ferreira, who created the SQL script and provided Sitefinity users with an interesting approach to update the URLs of digital assets in Sitefinity.
Having seen the three different approaches above, it’s up to you to decide which one works best for you. Just keep in mind that manipulating the Sitefinity database directly can lead to unwanted results and it can prevent you from upgrading to newer Sitefinity versions.
View all posts from The Progress Team on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.
Learn MoreSubscribe to get all the news, info and tutorials you need to build better business apps and sites
Progress collects the Personal Information set out in our Privacy Policy and the Supplemental Privacy notice for residents of California and other US States and uses it for the purposes stated in that policy.
You can also ask us not to share your Personal Information to third parties here: Do Not Sell or Share My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.