Wing FTP Software

Code: Select all

#Requires -Modules pssqlite
<#
    Used to keep WingFTP weblink database sync with partner server
    Will remove any entries from databases that does not have a corresponding file in the _Weblinks folder on either server
    Will add entries to database if missing and found on partner server.
    Shares must be created to allow account running scripts access to _Weblinks folder and databases    	 
#>
 
#set variables
$CurDir = $PSScriptRoot
$ServerA = "xxx-ftpx-pp-a01"
$ServerB = "xxx-ftpx-pp-b01"
$DatabaseA = "\\$ServerA\WingFTPData\bookmark_db"
$DatabaseB = "\\$serverB\WingFTPData\bookmark_db"
$WeblinksFolderA = "\\$ServerA\WingFTPData\_WEBLINK"
$WeblinksFolderB = "\\$ServerB\WingFTPData\_WEBLINK"
$logfile = "$CurDir\Replicate Weblinks Database.csv"

$SelectQuery = "SELECT * from wftp_weblink"
$UpdateQuery = "INSERT INTO wftp_weblink (f_weblink, f_domain, f_username, f_is_uplink, f_create_time, f_update_time, f_count, f_filename, f_filepath, f_filesize, f_clientinfo, f_email, f_expire_time) 
    VALUES (@q_weblink, @q_domain, @q_username, @q_is_uplink, @q_create_time, @q_update_time, @q_count, @q_filename, @q_filepath, @q_filesize, @q_clientinfo, @q_email, @q_expire_time)"


#remove deleted Weblinks from Database
try {
    #get entries from databases on both servers
    $DatabaseALinks = Invoke-SqliteQuery -DataSource $DatabaseA -Query $SelectQuery
    $DatabaseBLinks = Invoke-SqliteQuery -DataSource $DatabaseB -Query $SelectQuery
} Catch {
    #exit if any errors are encountered
    throw $_
}
If ((Test-Path $WeblinksFolderA) -and (Test-Path $WeblinksFolderB)){ #check it can reach both weblink file locations
    $LiveWeblinkFiles=@{} #create hash table for files
    gci $WeblinksFolderA | % {$LiveWeblinkFiles[$_.name]=1} # Add found files in _weblinks to hash table
    gci $WeblinksFolderB | % {$LiveWeblinkFiles[$_.name]=1} # Add found files in _weblinks to hash table
    $RemoveFromServerA = $DatabaseALinks | ? {!$LiveWeblinkFiles.ContainsKey($_.f_weblink)} #find entries in database where there isn't a _weblink file
    $RemoveFromServerB = $DatabaseBLinks | ? {!$LiveWeblinkFiles.ContainsKey($_.f_weblink)} #find entries in database where there isn't a _weblink file
    $RemoveFromServerA | % { #remove entries from database
        Invoke-SqliteQuery -DataSource $DatabaseA -Query "DELETE from wftp_weblink WHERE f_weblink = ""$($_.f_weblink)"""
    }
     $RemoveFromServerB | % { #remove entries from database
        Invoke-SqliteQuery -DataSource $DatabaseB -Query "DELETE from wftp_weblink WHERE f_weblink = ""$($_.f_weblink)"""
    }
}

#add new entries from database
try {
    #get entries from databases on both servers
    $DatabaseALinks = Invoke-SqliteQuery -DataSource $DatabaseA -Query $SelectQuery
    $DatabaseBLinks = Invoke-SqliteQuery -DataSource $Databaseb -Query $SelectQuery
} Catch {
    #exit if any errors are encountered
    throw $_
}

$ToAddtoDatabaseA = $DatabaseBLinks | ? {$DatabaseALinks.f_weblink -notcontains $_.f_weblink} # find entries that are on one server and not the other
$ToAddtoDatabaseB = $DatabaseALinks | ? {$DatabaseBLinks.f_weblink -notcontains $_.f_weblink} # find entries that are on one server and not the other


$ToAddtoDatabaseA | % { #insert missing entries
    Invoke-SqliteQuery -DataSource $DatabaseA -Query $UpdateQuery -SQLParameters @{
		q_weblink = $_.f_weblink
        q_domain = $_.f_domain
        q_username = $_.f_username
        q_is_uplink = $_.f_is_uplink
        q_create_time = $_.f_create_time
        q_update_time = $_.f_update_time
        q_count = $_.f_count
        q_filename = $_.f_filename
        q_filepath = $_.f_filepath
        q_filesize = $_.f_filesize
        q_clientinfo = $_.f_clientinfo
        q_email = $_.f_email
        q_expire_time = $_.f_expire_time

	}
}

$ToAddtoDatabaseB | % {#insert missing entries
    Invoke-SqliteQuery -DataSource $DatabaseB -Query $UpdateQuery -SQLParameters @{
		q_weblink = $_.f_weblink
        q_domain = $_.f_domain
        q_username = $_.f_username
        q_is_uplink = $_.f_is_uplink
        q_create_time = $_.f_create_time
        q_update_time = $_.f_update_time
        q_count = $_.f_count
        q_filename = $_.f_filename
        q_filepath = $_.f_filepath
        q_filesize = $_.f_filesize
        q_clientinfo = $_.f_clientinfo
        q_email = $_.f_email
        q_expire_time = $_.f_expire_time

	}
}

#update log files
$logs=@()
$logs += $ToAddtoDatabaseA | select @{l="Data";e={get-date}},*,@{l="Action";e={"Added to $WeblinksFolderA"}} 
$logs += $ToAddtoDatabaseB | select @{l="Data";e={get-date}},*,@{l="Action";e={"Added to $WeblinksFolderB"}}
$logs += $RemoveFromServerA | select @{l="Data";e={get-date}},*,@{l="Action";e={"Removed from $WeblinksFolderA"}}
$logs += $RemoveFromServerB | select @{l="Data";e={get-date}},*,@{l="Action";e={"Removed from $WeblinksFolderB"}}
if ($logs) {
    $logs  | Export-Csv $logfile -Append
    $logs | select f_filepath ,action
}