Smartsite Documentatie > Install & Config > Database Server > Nvarchar conversion

Nvarchar conversion

Smartsite 7.4 - ...

2015-07-06 Nvarchar / Unicode conversie readme

2015-11-26 Nagelopen.

Achtergrond

De CMS database t/m Smartsite 7.3 gebruikt een 1-byte codering voor tekstvelden. De bijbehorende datatypes zijn char, varchar en text. De database collation is SQL_Latin1_General_CP1_CI_AS. Dit een code page 1, case insensitive, accent sensitive collation. Codepage 1 is Windows-1252. Hiermee kunnen zo'n 220 tekens gerepresenteerd worden. De overige tekens moeten door middel van HTML character entities gerepresenteerd worden, bijvoorbeeld de lange ij zit er niet in en moet als ij of ij gerepresenteerd worden.

De CMS database van Smartsite 7.4 en later gebruikt een 2-byte codering voor tekstvelden. De bijbehorende datatypes zijn nchar, nvarchar en ntext. De codering is UCS-2 met de mogelijkheid voor UTF-16. Met UCS-2 kunnen zo'n 63.500 tekens gerepresenteerd worden. Elk teken is gecodeerd met een Unicode.

De Unicode aanpak biedt voordelen:

  • In het product en in de toepassingen zijn vereenvoudigingen mogelijk doordat in een aantal gevallen de HTML character entity encoding/decoding stap kan vervallen.
  • Tekstvelden waarvoor het product of de toepassingen geen HTML character entity encoding/decoding uitvoeren konden voorheen alleen gebruikt worden voor de tekens uit de Windows-1252 code page en kunnen nu gebruikt worden voor alle in de praktijk benodigde tekens.
  • De robuustheid van het product en de toepassingen neemt toe doordat codepages niet meer nodig zijn, en hiermee dat er geen verschillen meer zijn zoals bijvoorbeeld tussen Windows-1252 en ISO-8859-1.

De nvarchar conversie is eenmalig nodig bij een update van Smartsite 7.3 of eerder naar Smartsite 7.4 of later. De conversie bestaat uit:

  • Een database conversie
  • Een update van de software. De software omvat:
    • Het legacy CMS, door middel van SmartsiteCMS_Setup_7.4_BuildX.exe
    • De publicatie engine, CMS server en manager (dashboard), door middel van UpdatePublication_7.4_BuildX.exe.

De nvarchar conversie is een handmatige stap en is geen onderdeel van de publication update. De volgorde is:

  • Nvarchar conversie, door middel van de scripts in Setup\To nvarchar\Scripts Dit script probeert zo veel als mogelijk de database geautomatiseerd te converteren naar nvarchar. Het is mogelijk dat na het draaien van dit script, er nog handmatige stappen gedaan moeten worden. Het script zal aangeven waar dit nodig is.
  • De software updates.

De nvarchar conversie betreft in principe al het gebruik van de database datatypes char, varchar en text, ook als de conversie in voorkomende gevallen niet strict noodzakelijk is. Op deze manier is eenvoudiger na te gaan waar nog conversie nodig is.

  • Database kolommen van het datatype char, varchar en text zijn na de conversie van het datatype nchar, nvarchar en ntext. Het gebruik van text/ntext komt overigens niet of nauwelijks voor.
  • Transact-SQL char constants zoals CHAR(10) zijn na de conversie NCHAR(10).
  • Transact-SQL string constants 'x' zijn na de conversie wanneer nodig N'x'. Dit is nodig wanneer de constante "groot" is, meerdere kBytes. In de praktijk wordt aangehouden dat de meerregelige tekst van stored procedures, functions en andere database objecten ongeacht de grootte als N'x' genoteerd wordt.

Na de conversie blijft de database collation SQL_Latin1_General_CP1_CI_AS. Applicaties en andere toepassingen die nog de char, varchar, text, 1-byte codering en Windows-1252 codepage gebruiken blijven werken. Het product gebruikt Unicode codering en in toenemende mate de modules gebruiken de Unicode codering.

Conversiestappen

  1. Database backup. Overweeg de conversie uit te voeren op een krachtige database server (native hardware, 8-16 GB RAM) anders kan het lang duren.
  2. Verwijder database tabel ToNVarchar, mocht die vanuit eerdere conversies nog aanwezig zijn. IF EXISTS (SELECT 1 FROM sys.tables WHERE name='ToNVarchar') DROP TABLE ToNVarchar;
  3. Inventariseer.
    • Plaats de map Setup\To nvarchar uit de 7.4 installatiemedia op een systeem dat toegang heeft tot de database.
    • Cmd box
    • Ga naar: Setup\To nvarchar\Scripts\Precheck
    • Voer uit: Precheck.cmd
    Dit maakt tabel ToNVarchar aan en vult de tabel, nog zonder verdere database wijzigingen aan te brengen.
  4. Beoordeel de meldingen die in de cmd box teruggekoppeld worden. Deze informatie staat ook in tabel ToNVarchar: SELECT * FROM ToNVarchar ORDER BY ObjectName;
    • ObjectName: naam van een tabel, stored procedure of ander database object gevonden in de database, beperkt tot schema dbo.
    • ObjectType: USER_TABLE, SQL_STORED_PROCEDURE of ander object type.
    • ObjectDefinition: definitietekst van de stored procedure, view, trigger of ander database object. Het gaat om een tekst waarin het niet-relevante gebruik van "char", "varchar" en "text" vervangen is door "chr", "varchr" en "txt". Het overige gebruik van "char", "varchar" en "text" duidt op niet-geconverteerd materiaal en kan gevonden worden door bijvoorbeeld met een LIKE '%char%' in de definitietekst te zoeken.
    • IsKnown geeft met 1 aan dat het database object bekend is als Smartsite core object of als object van een Smartsite module of toepassing. Dit is ter informatie en dit is verder niet van invloed op de conversie.
    • IsEnabled betekent: is enabled for conversion, en geeft met 1 aan dat de rest van het conversiescript mag proberen het object te converteren. Het inventarisatiesscript zet dit standaard op 1. Zet dit handmatig op 0 om het object voor conversie uit te sluiten.
    • IsCompliant: geeft met 1 aan dat het object geen char, varchar en text (meer) gebruikt en hiermee nvarchar compliant is. Voor de conversie zal dit voor een Smartsite 7.3 database voor veel objecten op 0 staan. Bij de laatste conversiestap wordt de tabel ververst en zullen de meeste of alle objecten op 1 staan.
  5. Sluit zonodig objecten uit voor conversie. UPDATE ToNVarchar SET IsEnabled=0 WHERE IsEnabled=1 AND ObjectName IN ('LongPrint', 'Users_OLD', 'Visitors_OLD');
    • Het conversiescript beperkt zich tot objecten in het dbo schema. Dit betreft alle objecten van het product en de toepassingen, maar bijvoorbeeld niet de .Net workflow objecten in schema System.Activities.DurableInstancing.
    • Het conversiescript converteert geautomatiseerd alle kolommen van alle dbo tabellen, tenzij een tabel via IsEnabled is uitgesloten.
    • Het conversiescript vervangt Smartsite stored procedures, functions, views en triggers door een nvarchar compliant versie van deze objecten, tenzij een object via IsEnabled is uitgesloten. Opmerkingen:
      • Dit is geen generiek zoek & vervang mechanisme; voor ieder op deze manier geconverteerd object staat er expliciet een nvarchar compliant object definition in het conversiescript.
      • Alhoewel het mogelijk is een object voor conversie uit te sluiten is het voor de Smartsite objecten de bedoeling om de factory versie te gebruiken.
      • De versie uit het conversiescript is niet per definitie de nieuwste versie. Na de nvarchar conversie wordt de publication update gedraaid en die vervangt waar nodig de Smarsite objecten door nieuwere/andere versies.
    • Het conversiescript vervangt de stored procedures en functions van de Faceted Search module, tenzij een object via IsEnabled is uitgesloten. Het conversiescript gebruikt daarbij de volgende versies: 
      				fn_tsCountWord             v1.0
				fn_tsGetFieldsCompositeKey v1.0
				fn_tsContainsThesTerms     v1.0
				pr_tsFacetedSearch         v2.2.1
				pr_tsExportThesaurus       v1.0
				fn_tsAgeInDays             v1.0
      Sluit zonodig een selectie van deze objecten uit, als er customized versies behouden moeten worden. Na de conversie wordt de publicatie update gedraaid. Hierin kan optioneel de Faceted Search update uitgevoerd worden. Deze zal de objecten uit de conversie vervangen door nieuwere / andere versies.
  6. Voer de conversie uit.
    • Cmd box
    • Ga naar: Setup\To nvarchar\Scripts
    • Voer uit: ExecuteScript.cmd
      Stap 3_GenericFields.sql kan meerdere minuten duren doordat het script kolommen van tabel Contents één voor één converteert. Ook stap 6_Validation.sql kan meerdere minuten duren, wanneer Faceted Search views aangemaakt en geïndexeerd worden.
    • Het is mogelijk dat de conversie faalt op het omzetten van een varchar kolom naar een nvarchar kolom omdat de kolom onderdeel is van een index. In dit geval is het mogelijk de tabelnaam en indexnaam toe te voegen aan het script 3_GenericFields.sql. Het script zal dan de index opslaan, verwijderen, de nvarchar conversie doen, en de index opnieuw aanmaken. Dit werkt voor een clustered en niet-clustered index, maar werkt niet als de index in gebruik is vanuit een foreign key. Let op: als de recreate van de index faalt dan is de index inmiddels weg. In dat geval is een restore van de database nodig.
  7. Beoordeel de meldingen die in de cmd box teruggekoppeld worden.
    De informatie staat ook in tabel ToNVarchar. De inhoud van de tabel is door het conversiescript bijgewerkt. SELECT * FROM ToNVarchar WHERE IsCompliant=0 ORDER BY ObjectName;
    Besluit per gemeld object om het object zo te laten of om het object nvarchar compliant te maken. In het kader van dit punt kunnen de volgende niet-compliant objecten gemeld worden, die vooralsnog zo blijven staan:
    • pr_ContentByContentTypeViews (SmartInstant)
    • pr_siConfigureDesign (SmartInstant)
    • vwsiLayouts (SmartInstant)
  8. Maak een selectie van de objecten of maak alle objecten nvarchar compliant.
    • Onderzoek per object waar nog het gebruik van char, varchar en text zit: kopieer veld ObjectDefinition uit tabel ToNVarchar naar een text editor en zoek naar char, varchar en text. Match whole word only, case insensitive.
    • Voer de inventarisatiestap opnieuw uit: Setup\To nvarchar\Scripts\Precheck\Precheck.cmd

Vervolgstappen

  1. Draai na de conversie zowel SmartsiteCMS_Setup_7.4_BuildX.exe als UpdatePublication_7.4_BuildX.exe. De database conversie en de software updates vormen één geheel.
  2. Pas de site WWW\Web.config aan. <location path="cms"> - <system.web>: haal weg: <globalization requestEncoding="iso-8859-1"/> Hierdoor wordt de default "utf-8" van toepassing. Het is niet nodig hiervoor een element te laten staan.
  3. Ter informatie: de CMS update heeft de registry aangepast. - Manager - DefaultCharSet: utf-8 Hier stond iso-8859-1 Handmatige actie is niet nodig.
  4. Test. Als alles in orde is kan tabel ToNVarchar opgeruimd worden. IF EXISTS (SELECT 1 FROM sys.tables WHERE name='ToNVarchar') DROP TABLE ToNVarchar;

Troubleshooting

  • Het inventarisatiescript Setup\To nvarchar\Scripts\Precheck\Precheck.cmd kan stored procedures, functions en dergelijke onterecht aanmerken als niet nvarchar compliant. Verfijn dan functie fn_CharToChrTextToTxt. De functiedefinitie hiervan staat in Setup\To nvarchar\Scripts\Helper Scripts\DataToSQL Helpers.sql.
  • Procedure pr_RebuildViews roept procedure pr_tsFacetedSearch aan. Als pr_tsFacetedSearch niet of niet correct is bijgewerkt dan kan als fout optreden: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    • Maatregel: spoor in pr_tsFacetedSearch de twee plekken op waar xtype vergeleken wordt met system_type_id, en vervang dit door een vergelijking van xusertype met user_type_id.
    • Ter informatie: doordat het type van bijvoorbeeld kolom Contents.Title van varchar naar nvarchar is gegaan ontstaat er inderdaad een subquery die eerst 1 en nu 2 resultaten oplevert: SELECT c.name, t.name FROM sys.syscolumns c INNER JOIN sys.types t ON c.xtype=t.system_type_id WHERE id=OBJECT_ID('Contents') and c.name='Title'; Met de user types werken lost dit op: SELECT c.name, t.name FROM sys.syscolumns c INNER JOIN sys.types t ON c.xusertype=t.user_type_id WHERE id=OBJECT_ID('Contents') and c.name='Title';
Install & Config