Feature: command to clean up the database

[orchardbot]

@bleroy created: https://orchard.codeplex.com/workitem/17317

Deleted items, data related to inactive or absent modules could be cleaned out of the database.

[orchardbot]

@pszmyd commented:

Would be great - doing it by hand and hoping not to corrupt the db isn't something to dream about...

[orchardbot]

gramana commented:

As an interim measure, it would be nice to have documentation for the manual process (or a link to it, if it's there already, can't seem to find it).

[orchardbot]

@bleroy commented:

Migrations can now have an Uninstall method where some cleanup can be done. This could be implemented in a module, looking at the version manager module as a starting point.

[orchardbot]

latentlone commented:

It would be nice to be able to delete all the records and everything that goes with it for all content items which are removed via content manager. We worked on a import feature for a custom content type, had a bug, and removed all the wrong items, but their residue is still in the db. Would be nice to be able to clean up the db. Can somebody point me to a manual deletion process (there seem to be many tables affected).

[orchardbot]

@csurieux commented:

It could be easier to duplicate all the 'working items' in a new database by a kind of improved import/export exploiting relations between objects rather than finding and cleaning all the broken items left by incomplete delete or normal 'history process' ?

[Skrypt]

Until it gets implemented here is a SQL code snippet that display's all the records from a module. Could be enhanced eventually to clean the database.

USE DatabaseName
DECLARE @moduleName varchar(255);
DECLARE @tableName varchar(255);
DECLARE @tablePrefix varchar(255);

SET @moduleName = '%Orchard.ModuleName%';
SET @tablePrefix = 'YourTablePrefix_';

SET @tableName = @tablePrefix + 'Orchard_Framework_ContentTypeRecord';
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT * ' +
              ' FROM dbo.' + quotename(@tableName) +
              ' WHERE Name LIKE @moduleName'
EXEC sp_executesql @sql, N'@moduleName varchar(255)', @moduleName;

SET @tableName = @tablePrefix + 'Settings_ContentTypeDefinitionRecord';
SELECT @sql = ' SELECT * ' +
              ' FROM dbo.' + quotename(@tableName) +
              ' WHERE Name LIKE @moduleName'
EXEC sp_executesql @sql, N'@moduleName varchar(255)', @moduleName;

SET @tableName = @tablePrefix + 'Settings_ContentPartDefinitionRecord';
SELECT @sql = ' SELECT * ' +
              ' FROM dbo.' + quotename(@tableName) +
              ' WHERE Name LIKE @moduleName'
EXEC sp_executesql @sql, N'@moduleName varchar(255)', @moduleName;

SET @tableName = @tablePrefix + 'Settings_ShellFeatureRecord';
SELECT @sql = ' SELECT * ' +
              ' FROM dbo.' + quotename(@tableName) +
              ' WHERE Name LIKE @moduleName'
EXEC sp_executesql @sql, N'@moduleName varchar(255)', @moduleName;

SET @tableName = @tablePrefix + 'Settings_ShellFeatureStateRecord';
SELECT @sql = ' SELECT * ' +
              ' FROM dbo.' + quotename(@tableName) +
              ' WHERE Name LIKE @moduleName'
EXEC sp_executesql @sql, N'@moduleName varchar(255)', @moduleName;

SET @tableName = @tablePrefix + 'Orchard_Framework_DataMigrationRecord';
SELECT @sql = ' SELECT * ' +
              ' FROM dbo.' + quotename(@tableName) +
              ' WHERE DataMigrationClass LIKE @moduleName'
EXEC sp_executesql @sql, N'@moduleName varchar(255)', @moduleName;