|
Tänk dig att du får följande uppgift:
“Vi behöver göra en liten ändring i den här databasen, som har hundratals tabeller och tusentals kolumner. Vi behöver ett bättre stöd för olika språk i applikationen så alla kolumner måste ändras till unicode (dvs varchar blir nvarchar, char blir nchar osv).”
Med små datamängder kan man alltid använda SQL Server Management Studio och göra ändringarna där. Men det är knappast ett alternativ när man har utvecklingsmiljöer, test-miljöer och produktionsmiljöer med flera installerade instanser av liknande databaser osv. När produktionssättningen av en sådan här ändring ska göras vill man inte behöva stänga sina system två veckor för att manuellt ändra kolumner.
En andra tanke skulle vara att just göra manuellt ändringar i en test-miljö och via Script-knappen i Management Studio få det SQL-script som används för att göra förändringen och spara ändringen till en text-fil. Men det skulle också vara extremt tidsödande. Jag skrev ovan om produktionsmiljöer med liknande databaser. Det är ett problem med ändra-manuellt-i-Management-Studio-och-spara-undan-scriptet-metoden. Är inte databaserna identiska måste samma procedur utföras för varje databas.
En tredje idé är ju förstås att scripta ut databaserna och göra ändringar med Sök/Ersätt i valfri text-editor. Så tänkte jag först när jag ställdes inför uppgiften. Men ganska snabbt slog jag idén ur hågen. I mitt fall har flera tabeller kolumnnamn som är datatyper (“Text” till exempel). Det gjorde enkel Sök/Ersätt i text ganska svårt. Dessutom skulle scriptet behöva omstruktureras en hel del för att saker och ting skulle göras i rätt ordning (droppa constraints och index, ändra tabellerna, återskapa constraints och index).
Därför testade jag mig fram med Microsoft.SqlServer.Management.Smo i VB.NET och kom fram till ganska lite kod som gör ett väldigt stort jobb. Jag älskar verkligen SMO! På första försöket blev jag 95% färdig. Två-tre försök senare hade jag en lösning som fungerade “universellt” för alla databaser.
Jag har även använt SMO och reguljära uttryck för att ändra alla stored procedures i en databas till att använda unicode-ekvivalenter, men den funktionen har så mycket buggar än så länge, så jag väntar lite med den. De två största problem jag haft med den är: Jag har inte ett korrekt reguljärt uttryck för att känna igen strängar. Det blir fel när det finns fyra eller sex enkla citattecken invid varandra i koden. Jag har inte heller fått till igenkänning av alla variabler och parametrar. Parametrarna är OK, det är rätt enkelt att matcha “@någonting<ett eller flera blanksteg>variabeltyp”. Men det är inte alls lika enkelt att hitta alla förekomster av varchar, text eller char i löpande SQL-kod (exempelvis “SELECT CAST(kolumnen AS varchar(10)) FROM tabellen” osv. Men jag kanske finslipar lite, och återkommer när den är “fulländad” (as if..). Tills vidare körde jag min funktion och blev sedan sittande i nästan två dagar med rättning av koden. Men det sparade ändå massor av tid, jämfört med att helt manuellt göra förändringarna.
Här är VB.NET-koden för att ändra tabeller till unicode. En klass, med en publik och en privat metod:
Imports Microsoft.SqlServer.Management
Public Class TableUnicodyfier
'Sex globaler, två för att lagra fel och constraints, två som är namn på SMO-objekt
'och två som är självaste SMO-objekten. Inga publika variabler.
Private clConstraints As Collection
Private clErrors As Collection
Private s As Smo.Server
Private d As Smo.Database
Private sDBName As String
Private sInstanceName As String
Public Sub New(ByVal DBName As String, ByVal InstanceName As String)
sDBName = DBName
sInstanceName = InstanceName
End Sub
'Publik metod som returnerar ett SQL-script för att ändra alla text-kolumner till Unicode.
Public Function ReplaceAllTablesWithUnicode() As String
'Variabler
Dim sError As String = ""
Dim j As Integer = 0
Dim i As Integer = 0
Dim str As String = ""
'Initiera object
clConstraints = New Collection()
clErrors = New Collection()
s = New Smo.Server(sInstanceName)
d = s.Databases(sDBName)
'Viktigt!! Jag föredrar att kunna granska alla ändringar i text innan de genomförs (jag kanske är feg?).
'Om inte raden nedan finns med görs alla ändringar "på riktigt". Finns den med sparas bara alla SQL-anrop
'och kan hämtas sedan.
s.ConnectionContext.SqlExecutionModes = Common.SqlExecutionModes.CaptureSql
'Loopa igenom alla tabeller och kör replaceTableWithUnicode
For Each t As Smo.Table In d.Tables
If Not t.IsSystemObject Then
replaceTableWithUnicode(t.Name, True)
End If
i += 1
Application.DoEvents()
Next
'Eftersom replaceTableWithUnicode bara droppar Foreign Keys men inte återskapar dem så gör vi det sist av allt.
'Anledningen är förstås att alla tabeller måste ändras innan vi kan återskapa FK:n igen. Annars riskerar vi
'att försöka skapa en FK mellan en varchar-kolumn i tabell A och en nvarchar-kolumn i tabell B.
'Därför loopar vi igenom clConstraints och kör Create på varje FK.
For Each f2 As Object In clConstraints
f2.create()
Next
'Loopa igenom all SQL-text som SMO sparat åt oss istället för att genomföra förändringarna på riktigt.
'Bygg upp en resultat-sträng
For Each str2 As String In s.ConnectionContext.CapturedSql.Text
str &= str2 & vbCrLf & "GO" & vbCrLf
Next
'Om några fel uppstod under körningen så har de objekten lagrats i clErrors. Loopa igenom den och
'lägg till även den SQL-koden sist i resultat-strängen.
If clErrors.Count > 0 Then
str &= "--Errors occured while saving scripts" & vbCrLf
For Each o As Object In clErrors
For Each strScript As String In o.script()
str &= strScript & vbCrLf
Next
Next
End If
'str innehåller nu:
'- Drop statements för alla Foreign Key constraints
'- Drop-statements för alla index, primärnycklar, defaults och triggers
'- Alter statements för alla tabeller
'- Create-statements för alla index, primärnycklar, defaults och triggers
'- Create-statements för alla Foreign Key constraints
Return str
End Function
Private Function replaceTableWithUnicode(ByVal tableName As String, ByVal commit As Boolean) As String
Dim cl As New Collection
Dim bChange As Boolean
Dim t As Smo.Table = d.Tables(tableName)
Dim c As Smo.Column
Dim str As String = ""
'Loopa igenom alla kolumner. Ändra dem till respektive Unicode-ekvivalent
For Each c In t.Columns
bChange = False
Select Case c.DataType.Name
Case "varchar"
bChange = True
If c.DataType.MaximumLength > 4000 Then
c.DataType = Smo.DataType.NVarCharMax
Else
c.DataType = Smo.DataType.NVarChar(c.DataType.MaximumLength)
End If
Case "char"
bChange = True
c.DataType = Smo.DataType.NChar(c.DataType.MaximumLength)
Case "text"
bChange = True
c.DataType = Smo.DataType.NVarCharMax
End Select
'Notera att vi inte har gjort Alter på tabellen ännu. Än så länge har vi bara ändrat i kolumn-objekten.
'Om någon kolumn ska ändras så är det säkrast att återskapa alla default-constraints, annars kommer
'SQL Server att gnälla. Vi loopar igenom dem, lägger till dem i cl och droppar dem sedan. På så sätt
'genererar ett DROP-statement till vår SQL-kod, och vi kan göra Create senare när tabellen är ändrad.
If bChange Then
If Not c.DefaultConstraint Is Nothing Then
cl.Add(c.DefaultConstraint)
c.DefaultConstraint.Drop()
End If
End If
Next
Try
'Nu ska vi hitta alla FK-constraints som refererar till den här tabellen.
'För varje FK som refererar till den här tabellen - lägg till den i både funktionens interna
'cl2 och i klassvariablen clConstraints
'Sist - loopa igenom alla constraints i cl2 och droppa. Resultatet blir att alla FK kommer att droppas
'men själva objekten kommer att finnas kvar i clConstraints, för att anropande funktion ska kunna återskapa dem
For Each t2 As Smo.Table In d.Tables
Dim cl2 As New Collection
For Each f2 As Smo.ForeignKey In t2.ForeignKeys
If f2.ReferencedTable.ToUpper() = tableName.ToUpper() Then
cl2.Add(f2)
clConstraints.Add(f2)
End If
Next
For Each f2 As Smo.ForeignKey In cl2
f2.Drop()
Next
Next
'Droppa även alla FK som är skapade i aktuell tabell och som refererar andra tabeller
Do Until t.ForeignKeys.Count = 0
clConstraints.Add(t.ForeignKeys(0))
t.ForeignKeys(0).Drop()
Loop
'Droppa alla index. Lägg först till indexet i den privata variabeln cl
Do Until t.Indexes.Count = 0
'Om inte den här raden är med funkar inte koden för Primärnycklar. Raden gör ingenting mer än att
'läsa indextypen, men tydligen görs något i själva Index-objektet som initierar den korrekt för primärnycklar.
Dim it As Smo.IndexKeyType = t.Indexes(0).IndexKeyType
cl.Add(t.Indexes(0))
t.Indexes(0).Drop()
Loop
'Och slutligen samma sak med alla triggers i tabellen
Do Until t.Triggers.Count = 0
cl.Add(t.Triggers(0))
t.Triggers(0).Drop()
Loop
'Sådärja, då är alla beroende objekt droppade. Då gör vi Alter på tabellen för att ge genomslag
'åt våra förändringar av kolumn-datatyper
Try
t.Alter()
Catch ex As Exception
'Och det här kan ju gå fel om vi glömt något, så då sparar vi i sådant fall undan tabellen i clErrors
MsgBox("Error when altering table " & t.Name)
clErrors.Add(t)
End Try
'Nu är tabellen ändrad, men den har varken index, primärnycklar defaults eller triggers.
'Tur att vi sparat undan de objekten i privata variabeln cl. Loopa och återskapa.
For Each o As Object In cl
Try
o.Create()
Catch ex As Exception
'Det här är fult, men jag vill direkt få reda på när något går fel, och få se en Stacktrace.
'I normala fall ska naturligtvis inte en sådan här klass försöka sig på att kommunicera interaktivt.
'Den som orkar fixar självklart en ErrorMessages-collection som public property i klassen.
MsgBox("Error when creating object(s)" & vbCrLf & ex.ToString())
clErrors.Add(o)
End Try
Next
Catch ex As Exception
MsgBox(ex.ToString())
End Try
Return str
End Function
End Class
Andra intressanta blogginlägg om: Unicode, SQL Server, Microsoft, Microsoft.SqlServer.Management.Smo
Kategorier: .NET | DBA
|