However, there is no feature to do the reverse - ie, check that physical files exist for every database record.
This script addresses that.
This script is for databases using an external MYSQL database and utilises both SQL commands and the Com API to ensure things are done whilst maintaining Hmailserver integrity. It should work with other flavours of external SQL database but you would have to make the necessary amendments to paths and executable in the BAT file to suit your system. Note: there is a similar script for INTERNAL DATABASE here
It comes in 2 parts:
1, the dedicated VBS script (called 'HMSdatabaseRecordCheck.vbs')
2, A calling BAT file ('HMSdatabaseRecordCheck.bat')
IMPLEMENTATION
A, Save both scripts (with the relevant VBS and BAT extensions) into the same storage directory of your machine where you would require them to run from.
B, Modify the BAT file and enter the necessary parameters at the top of the script (there are 7 to review).
Note: Modify all parameter values in the BAT script where marked " & :<----- INPUT blah ", and delete all whitespace/spaces and the actual " & :<----- INPUT blah " instruction from the line (after your parameter value).
C, Run the BAT script.
The code
First, the VBS script called HMSdatabaseRecordCheck.vbs (that will be called from a BAT file):
Code: Select all
Option Explicit
Const AdminUser = "administrator"
Dim Elog, FSO, DataDir, Mode, HMSpass
Dim MessageCount, ErrorCount,DeletedCount
Mode = 0
MessageCount = 0
ErrorCount = 0
DeletedCount = 0
Dim obApp, oDomain, oAccount
Dim oReadObj, oRead, SearchFile, MessageInputLine, SearchfileFolder, CheckFilePath, FoundIt, AccountAddr, MessageID
HMSpass = WScript.Arguments.Named("HMSpassword")
DataDir = WScript.Arguments.Named("DataDir")
Mode = WScript.Arguments.Named("ActionFlag")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Elog = CreateObject("hMailServer.EventLog")
Set obApp = CreateObject("hMailServer.Application")
Call obApp.Authenticate(AdminUser, HMSpass)
Dim obDatabase, sSQL
Set obDatabase = obApp.Database
Elog.Write ("Start process hm_messages")
Elog.Write ("")
Set oReadObj = CreateObject("Scripting.FileSystemObject")
Set oRead = oReadObj.OpenTextFile(DataDir&"\MessageList.txt", 1)
Do Until oRead.AtEndOfStream
MessageInputLine = oRead.ReadLine
MessageInputLine = Replace(MessageInputLine,vbTab," ")
FoundIt = 0
SearchFile = left(MessageInputLine,42)
MessageID = trim(mid(MessageInputLine,44,10))
AccountAddr = trim(mid(MessageInputLine,54))
MessageCount = MessageCount + 1
If MessageCount > 1 then ' ignores first HEADER row
oDomain = mid(AccountAddr,InStr(AccountAddr,"@")+1)
oAccount = left(AccountAddr,InStr(AccountAddr,"@")-1)
SearchfileFolder = mid(SearchFile,2,2)
CheckFilePath = DataDir & "\" & oDomain & "\" & oAccount &"\"&SearchfileFolder&"\"&SearchFile
If not (FSO.FileExists(CheckFilePath)) then
Elog.Write ("File not found: " & SearchFile & ", Account: " & AccountAddr & ", MessageID: " & MessageID )
ErrorCount = ErrorCount + 1
If (Mode = 1) then
sSQL = "DELETE FROM hm_messages WHERE messageid = " & MessageID
Call obDatabase.ExecuteSQL(sSQL)
DeletedCount = DeletedCount + 1
End If
End If
If MessageCount / 1000 = INT(MessageCount / 1000) Then
Elog.Write ("Message checked upto: " & MessageCount)
wscript.echo ("Message checked upto: " & MessageCount)
End IF
End If
Loop
oRead.Close() ' Close input file
Elog.Write ("")
Elog.Write ("Missing eml files : " & ErrorCount)
Elog.Write ("Deleted DB Rows : " & DeletedCount)
Elog.Write ("Rows checked : " & MessageCount - 1)
Elog.Write ("")
Elog.Write ("End process hm_Messages")
wscript.echo ("Missing eml files : " & ErrorCount)
wscript.echo ("Deleted DB Rows : " & DeletedCount)
wscript.echo ("Rows checked : " & MessageCount - 1)
wscript.echo ("See hmailserver_events.log for more detail on the missing files.")
Code: Select all
@echo off
rem ***************************************************************************************
rem
rem Modify all parameter values below where marked " & :<----- blah blah " and delete
rem all whitespace/spaces and the " & :<----- blah blah " instruction from the line.
rem There are 7 of them to review and update.
rem
rem ENSURE ALL WHITESPACE/spaces are deleted at the end of your parameter value
rem
rem ***************************************************************************************
set ScriptPath=C:\Users\Administrator\Documents\BatchFiles & :<--- Enter the path where your HMSdatabaseRecordCheck.vbs script resides
set HMSpassword=HMSsecretpassword & :<--- Enter Hmailserver admin password
rem ------------
rem MYSQL vars
rem ------------
set mysqlexe="C:\Program Files\MySQL\MySQL Server 5.5\bin\Mysql.exe" & :<-- INPUT YOUR MYSQL.exe path/executable here
set mysqluser=root & :<-- INPUT YOUR MySQL db user
set mysqlpass=sqlpass & :<-- INPUT YOUR MySQL db password
set mysqldb=mymaildb & :<-- INPUT YOUR database name
rem ------------
rem Hmail Vars
rem ------------
set HMSProgDir=C:\Program Files (x86)\hmailserver
rem ------------------------------------------------------------------------
rem Do not modify below this line
rem ------------------------------------------------------------------------
cls
color 0e
set ActionFlag=0
set /p ActionFlag="Enter Action flag 0=Report Only, 1=Update (default=0): "
SET INIpath=""
If exist "%HMSProgDir%"\bin set INIpath="%HMSProgDir%"\bin
if %INIpath% == "" CLS & color 0c & echo.&echo. !!! Hmailserver bin directory not found! (%HMSProgDir%\bin) & goto eof
set INIFILE=%INIpath%\hmailserver.ini
FOR /F "eol=; eol=[ tokens=1,2* delims==" %%i in ('findstr /b /l /i DataFolder= %INIFILE%') DO set DataDir=%%~j
rem ' Main, squish sql controls
set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb%
rem ' Main, empty var space
set mysqlexe=
set mysqluser=
set mysqlpass=
set mysqldb=
rem ' Main, create sql script to collect file names and IDs of contents
rem - the following is one line, and [b]should not be word-wrapped[/b]
echo SELECT hm_messages.messagefilename,hm_messages.messageid,SPACE(8-length(hm_messages.messageid)),hm_accounts.accountaddress FROM hm_messages,hm_accounts WHERE hm_messages.messageaccountid = hm_accounts.accountid;>%DataDir%\buildsql.sql
%mysql%<%DataDir%\buildsql.sql>%DataDir%\MessageList.txt
cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:%DataDir% /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%
rem Cleanup
del %DataDir%\MessageList.txt
del %DataDir%\buildsql.sql
echo.
echo.
echo.
color 0a
@echo off
echo FINISHED!
pause
:eof
I ran this and it took 17 seconds (on read only check) to check nearly 32900 database records. However, running it a second time, it took TWO seconds (probably due to caching). This should give some idea of timings based on your message count.
With 'Action Flag'=1 (ie, update mode), the time might be a little longer to run if it has records to delete as the Hmailserver API is considerably slow (it is only called on each missing message). Obviously the speed deficit depends on actually how many records it finds that need deleting. (Rough example, allow 1 second per deletion required).
Also, I have the Hmailserver data folder indexed (with windows INDEX OPTIONS). Maybe this adds added benefit in speed file lookups (although I cannot clarify either way).
FOOTNOTE:
Other scripts are also available throughout this forum. Noticeably, there is one here that simply uses SQL commands and not the Com API (but should be equally as quick). Caution should be taken with such methods that do not use the Com API for updates: viewtopic.php?t=19341