Deleting Sitecore Items in SQL (PROCEED WITH CAUSION!)

Recently we had to delete a large number of items from Sitecore, and as we all know the task usually takes a long time to run when you kick it off from Content Editor.

If you absolutely must delete items quickly, you can try doing it in SQL. Bare in mind that it is not advisable to manipulate item data directly in the database, and if you are not very experienced in Sitecore, I would advise you not to use this script. However, if you know what you are doing, it might be handy.

So here you go:

DECLARE @parentId as uniqueidentifier;
DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     uniqueidentifier
DECLARE @Items2Delete TABLE (RowID int not null primary key identity(1,1),ID uniqueidentifier)

set @parentId = '{38994C59-E601-4823-8377-3903940FCFCC}'

INSERT INTO @Items2Delete (ID)
SELECT [Descendant] as ID
  FROM [dbo].[Descendants]
  where Ancestor = @parentId
SET @RowsToProcess=@@ROWCOUNT
print  @RowsToProcess

INSERT INTO @Items2Delete (ID)
select ID from [dbo].[Items] where ParentID = @parentId
SET @RowsToProcess=@@ROWCOUNT
print  @RowsToProcess

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT
        @SelectCol1=ID
        FROM @Items2Delete
        WHERE RowID=@CurrentRow

declare @id as uniqueidentifier;

set @id = @SelectCol1
print @id

exec sp_executesql N'DELETE FROM [Items]
  WHERE [ID] = @itemId

  DELETE FROM [SharedFields]
  WHERE [ItemId] = @itemId

  DELETE FROM [UnversionedFields]
  WHERE [ItemId] = @itemId

  DELETE FROM [VersionedFields]
  WHERE [ItemId] = @itemId',N'@itemId uniqueidentifier',@itemId=@id

exec sp_executesql N'DELETE FROM [Descendants] WHERE [Descendant] = @itemId',N'@itemId uniqueidentifier',@itemId=@id
exec sp_executesql N' DELETE FROM [Links] WHERE [SourceItemID] = @itemID AND [SourceDatabase] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
exec sp_executesql N'DELETE FROM [Tasks] WHERE [ItemID] = @itemID AND [Database] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'

END

If you have done the same thing before and see any issue with this script, don't hesitate to comment. :)

Sitecore Data source creation automation

There are plenty of different ways architects and developers organize data sources for Sitecore page items. I prefer to reserve tree structure under Home item only for page item types, and have data source in a separate structure. If you don't have a predictable pattern to how your data source items are stored, it might be very difficult to find things. If your solution rely mostly on Experience Editor, it might not be such a big deal, but if you do use Content Editor a lot, having clear structure to your data sources, might save you time in the long run.

I have been using my own module for several projects now, that allows to control data source structure and their live cycle. You can download the module either from SitecoreMarketplace or from GitHub at https://github.com/jcore/JCore.Sitecore.DatasourceAutomation

Sitecore Datasource Automation Module creates datasource folders for corresponding page items outside of Website tree structure. When pages are created, renamed deleted or moved, the datasource folders follow the page items they are attached to.

Configuration

After the package is installed or unicorn sync from the solution you should see three new templates under Template/Foundation/Datasources:

In addition to templates you’ll find a new branch under Branches/Foundation/Datasources:

To allow an easy start, there is a sample Data Sources folder created under sitecore/Content. You don’t have to use this folder.

To switch the datasource folder the module uses, either change JCore.Foundation.Datasources.DefaultRenderingDatasourceLocation setting in Website/App_Config/Include/Project/JCore.Common.Website.config or set datasourceRootItem attribute of your site definition like so:

<site name="sample_datasources_site" patch:after="site[@name='modules_website']"

                  targetHostName="$(rootHostName)"

                  database="master"

                  virtualFolder="/"

                  physicalFolder="/"

                  rootPath="/sitecore/content/Sample"

                  startItem="/home"

...

                  datasourceRootItem="{3C1FB34D-6821-4D26-A4DF-1840AF5CFD2D}"/>

To allow for an item to have automated datasource, you would have to add _NonChildDatasourceSupport template to the list of base template for the item.

Now when you create an item based on updated template you should see the datasource folder created for your item, Datasource Folder field update to point to the datasource folder location and a new contextual menu at the tom with grayed out “Datasource” chunk.

If your page item has any presentation settings defined and any rendering there have Datasource Template set, the module will create items under datasource folder for that rendering and update datasource location in page item presentation settings to point to the newly created datasource item.

If you have existing items that you would like to have the same datasource automation functionality enabled, you can still add template inheritance. However, Datasource Folder field will be empty for all items that inherit this template. If you have datasource folders for these item, simply update the Datasource Folder field to point to these folders, otherwise, click “Create Data Source” at the top in Data Source chunk, and the datasource folder will be created for you.

The structure of datasource folders always follows the same pattern and follows the tree structure of page items.

If you choose to delete a page item, corresponding datasource folder will be deleted as well, but only if no other pages use its datasource items.If you move or rename page item, datasource folder will be renamed as well.