+1-888-365-2779
Try Now
More in this section
Categories
Bloggers
Blogs RSS feed

Change Your Sitefinity Media URLs. Three ways of doing it

by Peter Marinov

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.

Leave a comment