How toRemove a Pre-Installed Language

Remove a Pre-Installed Language

Sana Commerce 8.2
Your provider

How to Remove a Webshop Language

When not all pre-installed languages of the webshop are needed, they can be removed. To remove a webshop language you can execute the SQL script on your database. This script will remove the content written in the specific language, the references to the channels, the channels and the language itself.

Step 1: Locate the variable '@LanguageId' in the script below and specify the locale ID of the language which should be removed. The language IDs can be found in the 'Languages' table of the Sana Commerce SQL database. This script can be also downloaded as a sql file.

Step 2: Execute the SQL script on the Sana Commerce database to remove the specified webshop language:

CREATE FUNCTION remfld19823
(
 @xml XML,
 @field nvarchar(50)
)
RETURNS nvarchar(MAX)
AS
BEGIN
 DECLARE @value nvarchar(MAX);
 SELECT @value = N'<FieldsDictionary>'+CONVERT(nvarchar(max),@xml.query('
        for $v in /FieldsDictionary/*[@name != sql:variable("@field")]
        return $v'))+N'</FieldsDictionary>';
 RETURN @value;
END
GO
CREATE PROCEDURE remove_localized_field_21122011
(
 @fieldName nvarchar(255)
)
AS
BEGIN
 UPDATE Products SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE ProductCategories SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE VariantComponentGroups SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE VariantComponents SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
END
GO
BEGIN TRANSACTION
 BEGIN TRY
  DECLARE @languageId int;
  SET @languageId = [languageId]; /* the Locale ID of the language which should be removed */
  IF ((SELECT count(id) FROM Websites WHERE DefaultLanguageId = @languageId) > 0)
  BEGIN
   raiserror('ERROR: language %d is used as default for website!', 11, 1, @languageId)
  END
  --Delete NavigationItems
  DELETE FROM dbo.NavigationItems
  WHERE NavigationId IN (SELECT Id
         FROM dbo.Navigations
         WHERE LanguageId = @languageId);
  --Delete Navigations
  DELETE FROM dbo.Navigations
  WHERE LanguageId = @languageId;
  --Delete MobileNavigationItems
  DELETE FROM dbo.MobileNavigationItems
  WHERE NavigationId IN (SELECT Id
         FROM dbo.MobileNavigations
         WHERE LanguageId = @languageId);
    --Delete MobileNavigations
  DELETE FROM dbo.MobileNavigations
  WHERE LanguageId = @languageId;
    --Delete ProductEnrichments
  DELETE FROM ProductEnrichments
  WHERE LanguageId = @languageId;
  --Delete ChannelReferences
  DELETE FROM ChannelReferences
  WHERE (WebsiteLanguageId IN (SELECT Id
            FROM WebsiteLanguages
            WHERE LanguageId = @languageId))
  --Delete ContentLandingPages
  DELETE FROM ContentLandingPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ContentLandingPages.Id) = 0)
  --Delete WebsiteLanguages
  DELETE FROM WebsiteLanguages
  WHERE LanguageId = @languageId
  --Delete SanaTexts
  DELETE FROM SanaTexts
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = SanaTexts.Id) = 0)
  --Delete NewsItems
  DELETE FROM NewsItems
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = NewsItems.Id) = 0)
  --Delete ContentPages
  DELETE FROM ContentPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ContentPages.Id) = 0)
  --Delete ProductLandingPages
  DELETE FROM ProductLandingPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ProductLandingPages.Id) = 0)
  --Delete ProductListPages
  DElETE FROM ProductListPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ProductListPages.Id) = 0)
  --Delete MailTemplates
  DELETE FROM MailTemplates
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = MailTemplates.Id) = 0)
  -- remove localized fields
  DECLARE @stringLanguageId nvarchar(255)
  SET @stringLanguageId = CAST (@languageId as nvarchar(255));
  DECLARE @fieldName nvarchar(255)
  SET @fieldName = 'Description_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'Title_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'Name_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'HorizontalText_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'VerticalText_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'VariantDescription_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  ------------
  --Delete Translations
  DELETE FROM Translations
  WHERE LanguageId = @languageId
  --Delete LanguagePreference from ShopAccount
  UPDATE dbo.ShopAccounts SET Fields = dbo.remfld19823(Fields,'LanguagePreference')
  WHERE CAST( Fields
    .query('
     for $descr in /FieldsDictionary/*[@name = "LanguagePreference"]
     return string($descr)
    ')
    .value('/', 'nvarchar(max)') AS xml)
    .value('/', 'nvarchar(max)') = @languageId
  --Delete Languages
  DELETE FROM Languages
  WHERE Id = @languageId;
 END TRY
 BEGIN CATCH
  PRINT ERROR_MESSAGE() + ' Transaction rolled back.'
  IF @@TRANCOUNT > 0
   ROLLBACK TRANSACTION
 END CATCH
GO
IF @@TRANCOUNT > 0
 COMMIT TRANSACTION
GO
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'remfld19823'
    AND ROUTINE_TYPE = 'FUNCTION'
)
    DROP FUNCTION remfld19823
GO
GO
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'remove_localized_field_21122011'
    AND ROUTINE_TYPE = 'PROCEDURE'
)
    DROP PROCEDURE remove_localized_field_21122011
GO
How toRemove a Pre-Installed Language