Description:
This script will delete unused directories in the Directory table which are unused
Usage
CScript.exe {Script} {MSI}
Script
'set up log file
Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
'create a name/path for log file
Dim MSIPath : Set MSIPath = fso.GetFile(WScript.Arguments(0))
Dim logFile : logFile = Left(MSIPath.Path, InStrRev(MSIPath.Path, ".") - 1) & ".log"
Dim objLogFile : Set objLogFile = fso.OpenTextFile(logFile, ForAppending, True)
WriteLog "Removing Unused Directories"
WriteLog "Processing: " & MSIPath.Name
'create 2 constants - one for when we want to just query the MSI (read) and one for when we want to make changes (write)
Const msiOpenDatabaseModeReadOnly = 0
Const msiOpenDatabaseModeTransact = 1
Const msiViewModifyReplace = 4
'create WindowsInstaller.Installer object
Dim oInstaller : Set oInstaller = CreateObject("WindowsInstaller.Installer")
'open the MSI (the first argument supplied to the vbscript)
Dim oDatabase : Set oDatabase = oInstaller.OpenDatabase(WScript.Arguments(0),msiOpenDatabaseModeTransact)
Dim sql, dirView, dirRecord, directoryView, directoryRecord, execView, tableView, tableRec, directoryParentRec, directoryParentView, tempTable, tempColumn, dirsToDeleteView, dirsToDeleteRec
If oDatabase.TablePersistent("Directory") = 1 Then
' alter table to add a temporary column for storing a marker
Set execView = oDatabase.OpenView("ALTER TABLE `Directory` ADD `InUse` SHORT TEMPORARY HOLD")
execView.Execute
' set all the markers to 0
Set execView = oDatabase.OpenView("UPDATE `Directory` SET `InUse`=0")
execView.Execute
'-- Try all the Directory_ Foreign key tables
Set tableView = oDatabase.OpenView("SELECT `Table` FROM `_Columns` WHERE `Name`= 'Directory_' ") 'this lists the tables that have 'Directory_' columns
tableView.Execute
Set tableRec = tableView.Fetch
Do While Not tableRec is Nothing
Set directoryView = oDatabase.OpenView("SELECT DISTINCT `Directory_` FROM `" & tableRec.StringData(1) & "`")
directoryView.Execute
Set directoryRecord = directoryView.Fetch
While not directoryRecord is nothing
MarkDir(directoryRecord.Stringdata(1))
Set directoryRecord = directoryView.Fetch
Wend
Set tableRec = tableView.Fetch
Loop
Set tableView = Nothing
Set tableRec = Nothing
Set tableView = oDatabase.OpenView("SELECT `Table`,`Column` FROM `_Validation` WHERE `Column`= 'DestFolder' OR `Column`= 'DirProperty' OR `Column`= 'SourceFolder' OR `Column`= 'ReserveFolder'")
tableView.Execute
Set tableRec = tableView.Fetch
Do While Not tableRec Is Nothing
tempTable = tableRec.StringData(1)
tempColumn = tableRec.StringData(2)
If oDatabase.TablePersistent(tempTable) = 1 Then
Set directoryView = oDatabase.OpenView("SELECT DISTINCT `"& tempColumn &"` FROM `" & tempTable & "`")
directoryView.Execute
Set directoryRecord = directoryView.Fetch
While Not directoryRecord is Nothing
MarkDir(directoryRecord.StringData(1))
Set directoryRecord = directoryView.Fetch
Wend
End If
Set tableRec = tableView.Fetch
Loop
Set tableView = Nothing
Set tableRec = Nothing
Set dirsToDeleteView = oDatabase.OpenView("SELECT `Directory`, `DefaultDir` FROM `Directory` WHERE `InUse`=0 ")
dirsToDeleteView.Execute
Set dirsToDeleteRec = dirsToDeleteView.Fetch
While not dirsToDeleteRec is nothing
If Left(dirsToDeleteRec.StringData(2), 2) <> ".:" And Not isMSMData(dirsToDeleteRec.StringData(1)) And Not childIsMSMData(dirsToDeleteRec.StringData(1)) Then
WriteLog "DELETE FROM `Directory` WHERE `Directory`= '" & dirsToDeleteRec.StringData(1) & "'"
oDatabase.OpenView("DELETE FROM `Directory` WHERE `Directory`= '" & dirsToDeleteRec.StringData(1) & "'").Execute
End If
Set dirsToDeleteRec = dirsToDeleteView.Fetch
Wend
Set execView = oDatabase.OpenView("ALTER TABLE `Directory` FREE ") '--remove the temporary column
execView.Execute
End If
objLogFile.Close
Set fso = Nothing
Set objLogFile = Nothing
Set execView = Nothing
Set tableRec = Nothing
Set tableView = Nothing
Set directoryView = Nothing
Set directoryRecord = Nothing
Set dirsToDeleteView = Nothing
Set dirsToDeleteRec = Nothing
oDatabase.Commit
Set oDatabase = Nothing
Set oInstaller = Nothing
Sub WriteLog(LogMessage)
WScript.echo Now() & ": " & LogMessage
objLogFile.Writeline(Now() & ": " & LogMessage)
End Sub
Sub MarkDir(sDirName)
Set execView = oDatabase.OpenView("UPDATE `Directory` SET `InUse`=1 WHERE `Directory` = '" & sDirName & "'")
execView.Execute ' set the marker to 1 to indicate that the directory table entry is in use
Set directoryParentView = oDatabase.OpenView("SELECT `Directory_Parent` FROM `Directory` WHERE `Directory` = '" & sDirName & "'") '--Done this one, now mark the parent directories
directoryParentView.Execute
Set directoryParentRec = directoryParentView.Fetch
If not directoryParentRec is nothing Then
MarkDir(directoryParentRec.StringData(1)) '-- recurse up the tree
End If
Set execView = Nothing
Set directoryParentView = Nothing
Set directoryParentRec = Nothing
End Sub
'this function is used to check child entries in the directory table, to make sure they're not from a merge module before
'the directory gets deleted
Function childIsMSMData(sDirectory)
childIsMSMData = False
sql = "SELECT `Directory` FROM `Directory` WHERE `Directory_Parent` = '" & sDirectory & "'"
Set dirView= oDatabase.OpenView(sql)
dirView.Execute
Set dirRecord = dirView.Fetch
Do Until dirRecord Is Nothing
If isMSMData(dirRecord.StringData(1)) Then
childIsMSMData = True
Exit Do
End If
Set dirRecord = dirView.Fetch
Loop
Set dirView = Nothing
Set dirRecord = Nothing
End Function
'returns true if sData contains MSM decoration
Function isMSMData(sData)
isMSMData = False
Dim Match
Dim regEx : Set regEx = New RegExp
regEx.MultiLine = vbTrue
regEx.global = vbTrue
regEx.Pattern = "[A-Za-z0-9]{8}_[A-Za-z0-9]{4}_[A-Za-z0-9]{4}_[A-Za-z0-9]{4}_[A-Za-z0-9]{12}"
For Each Match in regEx.Execute(sData)
isMSMData = True
Next
Set regEx = Nothing
End Function
