@Shazwazza

Shannon Deminick's blog all about web development

Sql script for changing media paths or virtual directories in Umbraco

May 15, 2013 01:11

When you upload media in Umbraco it stores the absolute path to the media item in the database. By default the path will look something like:

/media/12335/MyImage.jpg

However, if you are running Umbraco in a virtual directory the path will also include the virtual directly prefix. For example:

/MyVirtualDirectory/media/12335/MyImage.jpg

So you can imagine that some issues might arise if you already have data in your Umbraco install and then wanted to change virtual directory paths, or even change the media location path (umbracoMediaPath) in the web.config.

Luckily it’s easily solved with a quick SQL script. These scripts will update the path stored in Umbraco created by any property type that is of a data type with an ‘upload control’ property editor.

When moving to a virtual directory

Here’s a quick script to run against your current install if you are moving from a normal installation to using a virtual directory. Note: You will need to replace all instances of ‘MyVirtualDirectory’ to be your vdir path!

update cmsPropertyData 
set dataNvarchar = '/MyVirtualDirectory' + dataNvarchar
where id in
(select cmsPropertyData.id from cmsPropertyData
inner join cmsPropertyType on cmsPropertyData.propertytypeid = cmsPropertyType.id
inner join cmsDataType on cmsPropertyType.dataTypeId = cmsDataType.nodeId
where cmsDataType.controlId = '5032a6e6-69e3-491d-bb28-cd31cd11086c'
and cmsPropertyData.dataNvarchar is not null
and SUBSTRING(cmsPropertyData.dataNvarchar, 0, LEN('/MyVirtualDirectory') + 1) <> '/MyVirtualDirectory')

When moving from a virtual directory

Here’s the script to run if you are currently running in a virtual directory and want to move to a non-virtual directory. Note: You will need to replace all instances of ‘MyVirtualDirectory’ to be your vdir path!

update cmsPropertyData 
set dataNvarchar = SUBSTRING(dataNvarchar, LEN('/MyVirtualDirectory') + 1, LEN(dataNvarchar) - LEN('/MyVirtualDirectory'))
where id in
(select cmsPropertyData.id from cmsPropertyData
inner join cmsPropertyType on cmsPropertyData.propertytypeid = cmsPropertyType.id
inner join cmsDataType on cmsPropertyType.dataTypeId = cmsDataType.nodeId
where cmsDataType.controlId = '5032a6e6-69e3-491d-bb28-cd31cd11086c'
and cmsPropertyData.dataNvarchar is not null
and SUBSTRING(cmsPropertyData.dataNvarchar, 0, LEN('/MyVirtualDirectory') + 1) = '/MyVirtualDirectory' )

Moving to a different virtual directory?

If you want to move to a different virtual directory, you can combine the above 2 scripts… first move from a virtual directory back to normal, then move to the new virtual directory.

The above procedures would work as well if you were to change the umbracoMediaPath app setting in the web.config … though most people wont change that or even know about it ;)

Umbraco 4.1 Benchmarks Part 2 (Back Office Database Queries)

April 29, 2010 10:03
This post was imported from FARMCode.org which has been discontinued. These posts now exist here as an archive. They may contain broken links and images.
This is part 2 in a series of Umbraco 4.1 benchmarks created by various members of the core team in the lead up to launch. See Part 1 here on request/response peformance in the Umbraco back office.

This benchmark report looks at the data layer improvements in 4.1 by comparing query counts in 4.1 to 4.0.3. Not only has the data layer improved but there’s been significant improvements in the consumption of the data layer API made by many of the 4.1 pages and controls.

The stats below are represented as a percentage of the total calls of 4.0.3 where the number of queries in 4.0.3 are 100% and the number of queries in 4.1 are a percentage in relation to this. These results are based on the procedures listed at the bottom of this post and on averages run over 3 separate trials.

Step 4.0.3 4.1.0
Login 100% 68%
Expand all Content nodes 100% 23%
Edit Home node 100% 49%
Publishing Home node 100% 55%
Edit About Umbraco node 100% 49%
Go to Settings App 100% 100%
Expand Document types tree 100% 100%
Edit Home document type 100% 61%
Save Home document type 100% 67%
Go to Media app 100% 50%
Create new folder labeled ‘Test’ 100% 88%
Create new image under new ‘Test’ folder labeled ‘test1’ 100% 64%
Upload new image file to ‘test1’ and save the node 100% 49%
Go to Content app (and in the case of 4.0.3, expand the tree and select the About Umbraco node since in 4.1 this will already be selected and loaded) 100% 41%
Edit Home node 100% 43%
Add ‘test1’ image to the ‘Text’ WYSIWYG property with the image picker and Publish node 100% 49%
Average of averages above   60%
Complete run through of the above steps 100% 66%

 

So based on averages, Umbraco 4.1 is looking to have around 40% less queries made than 4.0.3!!! Thats HUGE!

The following steps were taken on each trial of the above steps:

  • New instances of both 4.0.3 and 4.1
  • Install CWS package on both instances
  • Log out of both instances
  • Bump web.config for both instances (clear out all data cache)
  • Use SQL Profiler to determine query counts for each step listed above

Also, SQL debugging has been added to 4.1 for MS SQL instances. If you compile the source in Debug mode you can get the SQL command output by adding a trace listener to your web.config. Underneath the configuration node you can add this xml block:

<system.diagnostics>
	<trace autoflush="true">
	  <listeners>
		<add name="SqlListener" 
			type="System.Diagnostics.TextWriterTraceListener" 
			initializeData="trace.log" />
	  </listeners>
	</trace>
</system.diagnostics>

This will create a trace.log file in the root of your web app SQL debugging.

TSQL CASE statement in WHERE clause for NOT IN or IN filter

April 17, 2010 02:58
This post was imported from FARMCode.org which has been discontinued. These posts now exist here as an archive. They may contain broken links and images.
There’s a ton of articles out there on how to implement a case statement in a WHERE clause but couldn’t find one on how to implement a CASE statement in a WHERE clause that gives you the ability to use a NOT IN or IN filter. I guess the only way to explain this is to use an example, and I am fully aware that the use of this may not be the best practice and is most likely required because of poor database design/implementation but hey, when you inherit code, there’s really no other choice :)

Suppose I have a stored proc that has an optional value:

@OnlyNonExported bit = 0

I want to return all items from MYTRANSACTIONS table if @OnlyNonExported  = 0, but if this value is 1 I want to return all items from MYTRANSACTIONS that have not been tracked in my TRACKEDTRANSACTIONS table. The original theory is to use a NOT IN clause to acheive the latter requirement:

SELECT * FROM mytransactions m 
WHERE mytransactions.id NOT IN (SELECT id FROM trackedtransactions)

So if I wanted to use a case statement for this query, one would think you could do something like this:

SELECT * FROM mytransactions m 
WHERE mytransactions.id NOT IN 
	CASE WHEN @OnlyNonExported = 0 
		THEN  (SELECT -1) 
		ELSE  (SELECT id FROM trackedtransactions) 
	END

But SQL doesn’t like this syntax and it turns out that you cannot use IN or NOT IN conditions with CASE statement in a WHERE clause, you can only use = or != conditions. So how do you achieve the above? Well the answer is even more dodgy that the above:

SELECT * FROM mytransactions m 
WHERE mytransactions.id != 
	CASE WHEN @OnlyNonExported = 0 
		THEN  (SELECT -1) 
		ELSE  COALESCE((SELECT id FROM trackedtransactions t WHERE t.id = m.id), -1)
	END

So basically, when we want to return all transactions, return all rows where the id equals –1 (assuming that your IDs start at 1) and when we want to filter the results based on whether or not these IDs exist in another table, we only return rows who’s IDs don’t match the same ID in the tracked table. BUT if this ID doesn’t exist in the tracked table, then an empty result set is returned and the id won’t be matched against it, so we need a COALESCE function will will return a –1 value if there is an empty result set.

Hopefully you’ll never have to use this but if you do, hope this saves you some headaches :)