I think this Job is a must for any web designer who uses Joomla, Drupal or any other CMS (Content Management Systems) that needs to be updated every so often!
Imagine having 10 websites that you are in charge of and you need to keep them up-to-date with any latest security –and not only- patches…Even just 5 would still be too many to handle..
What do you do? Spend a week updating them all manually?
How does the Job Work?
Each time we place a new zip file within the specified folder we are monitoring for zip file updates, our Job fires. What does the job do? It simply extracts the zip file into a folder, and then, upload the folder's contents to each website you have listed within the "Websites to Update" List.
How does the Job know where to get all the information?
It reads the information we have set up for each website from an Excel file. Using that information (host, username, password, root folder) it uploads the update folder in each website’s folder.
But we'd better check out step by step how this works..
Let’s suppose that the CMS we are talking about is a Joomla installation.
Before we start building our job we need to:
-
Set up the "Joomla_Update_Files" folder in which we shall be placing our update files.
- Set up the "Joomla Installed Updates" folder in which the zip file will be moved within once the zip has been extracted.
- Attach a File Monitor Trigger to the job to monitor that folder for all zip files that match the following pattern: Joomla_*_to_*-Stable-Patch_Package.zip
- Append our websites' information to the excel file.
Setting up our Excel File
We will name the first Worksheet FTPDetails and set the following columns:
-
Website: The name of our website for example: www.example.com
-
Host: The name of the FTP Server
-
Website Root Folder: The root folder of the website.
-
Website Parent Directory: The folder that contains the folder that holds the website's files (eg. if the root was /myserver/www/public_html/example.com the directory needed is "public_html")
-
User Name: The User Name to connect to the FTP Server
-
Password: The Password used by the User to connect to to the FTP Server
Managing the Update File
To make things simple so that this sample job can run on any machine we'll be using the Documents folder. This can be retrieved using the "Get Special Folder" action, where from the drop down list we select the "Personal" option which stands for the Documents folder of the logged in user.
To get the name of the zip file that was placed within the folder, we use the %FileTriggerFilePath% variable, whose value is defined by the File Monitor Trigger that triggers the job.
Using the "FilePath Part" action we can seperate any piece of information we want to retrieve from the file's path. In this case, we will only be using the %FileNameNoExtension% variable which holds the name of the triggered file alone (not followed by the file's extension)
Once the update file is downloaded a folder is created, named after the %FileNameNoExtension% variable, which holds the file's name not followed by the extension.
Eg. if the zip file we downloaded is: Joomla_1.5.21_to_1.5.22-Stable-Patch_Package.zip
then the folder that will be created will be: Joomla_1.5.21_to_1.5.22-Stable-Patch_Package
Within the newly created folder, we extract the zip file using the "Unzip Files" action. The file we shall be extracting is the one that initially triggered the job.
Getting a table containing all websites’ Details from Excel
We launch Excel, get it to open the excel file that contains our websites’ details and set the active worksheet to be the one that holds our sites' details. Since this file holds sensible data, we make sure that the file is not visible, but that the information is retrieved in the background.
Since we do not know the number of websites the excel file contains we use the Get First Free Column/Row Action to get the first free cell. This action is needed in order to specify the last used row and column of the data table table.
What we need now is to retrieve all the information that is stored in our table. So we will set it to retrieve all the values that are included from the first row and first column till the last row and last column. To get the last row and column we will use the variables set by our previous action (%FirstFreeRow% and %FirstFreeColumn%) and since these variables hold the number of our first free row and first free column, the row and column that is prior to them are the last row and column of our table, that can be achieved by using %FirstFreeRow-1% and %FirstFreeColumn-1%.
So the range of cells from which we want to get the values of will start from column 1, row 1 and end at %FirstFreeRow-1% and %FirstFreeColumn-1%. With WinAutomation you can also determine whether the first rows contain the names of the columns, and if set to yes you can use the column names instead of the column number to retrieve the information you want.
The information we retrieved will be stored in a variable named %ExcelData% that holds a table of values.
The Excel File is no longer needed, the values retrieved will remained stored within the %DataTable% variable even if we do close the Excel file. So using the "Close Excel" action, we close the excel file.
Uploading the Update Files to each website on our list
Now that we have all the information we need, we can start using it to achieve our purpose. We shall use the “For Each” Action, to repeat a series of actions for each row stored in our %ExcelData% table.
So for Each Row (that would mean for each website we have on the Excel Worksheet) we want to:
- Rename the Folder created that contains the update files, to hold the name of the website's folder we shall be updating.
-
Once we have done that, we need to fill in all the details for the "Open FTP Connection" action, using the details we retrieved from our Excel File.
o we get the FTP Server, using the name of the Host column: %CurrentRow["Host"]%
o we get the User Name, using the name of the User Name Column: %CurrentRow["User Name"]%
o we get the Password, using the name of the Password Column: %CurrentRow["Password"]%
(Needless to say that in order to use the password retrieved from excel we need to select the “enter password as variable” option in the “Open FTP Connection” action properties)
-
Upload the folder we previously renamed, using the %RenamedFolder% variable
-
Close the FTP Connection, using the corresponding action
-
Rename the folder back to holding to %FileNameNoExtension%. This is done so that next time the loop runs, we can still rename the folder to the name of the current website being updated.
Once the loop has finished, we then simply use the "Delete Folder" action and delete the folder that contains the update files, and also move the zip file to the "Joomla Installed Updates" Folder
Attaching the File Monitor Trigger to the Job
Since the job is ready, all we need to do now is make sure we monitor this folder for a specific filename type.
Checking at Joomla previous update files, we can see there's a specific pattern on each update's filename. So, we attach a file monitor trigger to this job, and according to the pattern we should look for all zip files, that match the following criteria:
Files to monitor: Joomla_*_to_*-Stable-Patch_Package.zip
File Events to monitor: created, changed, renamed.
Since some files might be downloaded directly within that folder, they do not have that filename when the file is created, so we monitor the folder for any changes even after the file has been created.
***** Before running this job, please make sure that the FTP details you have entered in the Excel, have access to the parent folder of each folder that contains each website's files. *****
Comments
Greg
Feb 18th, 2011 0:58
Hey this was a great tut.
So that you know, Akeeba has a built in tool that practically does the same thing in the Control Panel of Akeeba. Two clicks and your done. .... Of course you will want to do a back up before updating anytime as a safety net.
Cheers,
Greg