Create a gist now

Instantly share code, notes, and snippets.

Converts an MSSQL database creating script to PostgreSQL. Uses a VBScript implementation of preg_replace for the heavy lifting.
Option Explicit
Const ForWriting = 2
Const TristateTrue = -1
Dim sFile, sOutputFile, oOutputStream, sText
sFile = WScript.Arguments.Named("i")
sOutputFile = WScript.Arguments.Named("o")
If LenB(sFile) = 0 Then
WScript.echo "usage: pg_conv.vbs /i:intput_file.sql [/o:output_file.sql]"
WScript.Quit 1
End If
If LenB(sOutputFile) = 0 Or LCase(sOutputFile) = "con" Then
Set oOutputStream = WScript.StdOut
Else
Set oOutputStream = CreateObject("Scripting.FileSystemObject").OpenTextFile(sOutputFile, ForWriting, True, TristateTrue)
End If
sText = ReadFile(sFile)
sText = preg_replace("/^SET\s+NOCOUNT\s+ON$/mi", "-- @@Automatically generated by pg_conv.vbs on " & Now() & "@@", sText)
sText = preg_replace("/(\[|\])/mi", """", sText)
sText = preg_replace("/\s+(NONCLUSTERED|CLUSTERED|COLLATE\s+\w+)/mi", vbNullString, sText)
sText = preg_replace("/,\r\n\tCONSTRAINT/mi", "," & vbCrLf & vbTab & "ADD CONSTRAINT", sText)
sText = preg_replace("/\r\nGO\r\n/mi", ";" & vbCrLf, sText)
sText = preg_replace("/""?\buniqueidentifier\b""?/mi", "uuid", sText)
sText = preg_replace("/""?\bdatetime\b""?/mi", "timestamp", sText)
sText = preg_replace("/""?\btinyint\b""?/mi", "smallint", sText)
sText = preg_replace("/""?\bsmallint\b""?/mi", "smallint", sText)
sText = preg_replace("/""?int""? IDENTITY.*\)/mi", "serial", sText)
sText = preg_replace("/""?\bimage\b""?/mi", "bytea", sText)
sText = preg_replace("/""?\bsysname\b""?/mi", "varchar(128)", sText)
sText = preg_replace("/""?\bnvarchar\b""?/mi", "varchar", sText)
sText = preg_replace("/""?\bbit\b""?/mi", "boolean", sText)
sText = preg_replace("/""?\bdecimal\b""?/mi", "numeric", sText)
sText = preg_replace("/""?\bint\b""?/mi", "integer", sText)
sText = preg_replace("/""?\binteger\b""?/mi", "integer", sText)
sText = preg_replace("/""?\bvarchar\b""?/mi", "varchar", sText)
sText = preg_replace("/""?\bchar\b""?/mi", "char", sText)
sText = preg_replace("/""?\btext\b""?/mi", "text", sText)
sText = preg_replace("/INSERT\t/mi", "INSERT INTO", sText)
sText = preg_replace("/GETDATE\(\)/mi", "now()", sText)
sText = preg_replace("/NEWID\(\)/mi", "uuid_generate_v1()", sText)
sText = preg_replace("/-- { begin_create_stats }/mi", "/* { begin_create_stats } --", sText)
sText = preg_replace("/-- { end_create_stats }/mi", "-- { begin_create_stats } */", sText)
sText = preg_replace("/ADD(\s+)CONSTRAINT\s+([^\r\n]*)\s+DEFAULT\s+([^\r\n]*)\s+FOR\s+([^\r\n]*)([,;])/mi", _
"$1ALTER COLUMN $4 SET DEFAULT $3$5", sText)
sText = preg_replace_callback("/SET DEFAULT \((0|1)\)/mi", _
preg_substitute("SET DEFAULT (CAST(\{1} AS boolean))"), sText)
sText = preg_replace_callback("/datediff\(day,0,(""?\w[\w\d]*""?)\) = 0/mi", _
preg_substitute("(\1 >= TIMESTAMP '1900-01-01' AND \1 < TIMESTAMP '1900-01-02')"), sText)
oOutputStream.Write sText
Private Function preg_init(find_re)
Set preg_init = New RegExp
With preg_init
.Global = True
If Left(find_re, 1) = "/" Then
Dim pos: pos = InStrRev(find_re, "/")
.Pattern = Mid(find_re, 2, pos - 2)
.IgnoreCase = (InStr(pos, find_re, "i") > 0)
.Multiline = (InStr(pos, find_re, "m") > 0)
Else
.Pattern = find_re
End If
End With
End Function
Function preg_match(find_re, text)
preg_match = preg_init(find_re).Test(text)
End Function
Function preg_replace(find_re, replace_arg, text)
preg_replace = preg_init(find_re).Replace(text, replace_arg)
End Function
Function preg_split(find_re, text)
Dim esc: esc = ChrW(&HE1B6) '-- U+E000 to U+F8FF - Private Use Area (PUA)
preg_split = Split(preg_init(find_re).Replace(text, esc), esc)
End Function
Function preg_replace_callback(find_re, replace_arg, text)
Dim matches, match, count, offset, retval
Set matches = preg_init(find_re).Execute(text)
If matches.Count = 0 Then
preg_replace_callback = text
Exit Function
End If
ReDim retval(matches.Count * (1 - IsObject(replace_arg)))
For Each match In matches
With match
retval(count) = Mid(text, 1 + offset, .FirstIndex - offset)
count = count + 1
If IsObject(replace_arg) Then
retval(count) = replace_arg(match)
count = count + 1
End If
offset = .FirstIndex + .Length
End With
Next
retval(count) = Mid(text, 1 + offset)
If IsObject(replace_arg) Then
preg_replace_callback = Join(retval, vbNullString)
Else
preg_replace_callback = Join(retval, replace_arg)
End If
End Function
Function preg_substitute(replace_arg)
Set preg_substitute = New preg_substitute_class.init(replace_arg)
End Function
Class preg_substitute_class
private m_esc
Private m_replace
Public Function init(replace_arg)
m_esc = ChrW(&HE1B6) '-- U+E000 to U+F8FF - Private Use Area (PUA)
m_replace = Replace(replace_arg, "\", m_esc)
Set init = Me
End Function
Public Default Function callback(match)
Dim idx, replace_str
replace_str = match.Value
callback = Replace(Replace(m_replace, m_esc & "{0}", replace_str), m_esc & "0", replace_str)
With match.SubMatches
For idx = .Count To 1 Step -1
replace_str = .Item(idx - 1)
callback = Replace(Replace(callback, m_esc & "{" & idx & "}", replace_str), m_esc & idx, replace_str)
Next
End With
callback = Replace(callback, m_esc, "\")
End Function
End Class
Function preg_split_manual(find_re, text)
Dim matches, match, count, offset, retval
Set matches = preg_init(find_re).Execute(text)
ReDim retval(matches.Count)
For Each match In matches
With match
retval(count) = Mid(text, 1 + offset, .FirstIndex - offset)
count = count + 1
offset = .FirstIndex + .Length
End With
Next
retval(count) = Mid(text, 1 + offset)
preg_split_manual = retval
End Function
Private Function ReadFile(sFile)
Const ForReading = 1
With CreateObject("Scripting.FileSystemObject")
ReadFile = .OpenTextFile(sFile, ForReading, False, _
.OpenTextFile(sFile, ForReading, False, False).Read(2) = Chr(&HFF) & Chr(&HFE)).ReadAll()
End With
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment