Software is not Jewellery

A study of syncing databases that are simultaneously being updated by multiple clients where the clients are unaware of each other.

---

It is quite simple to build client server based application that has only one client accessing data at a time. Only one client has access to a database at a time and the diagram below shows how that happens.

A database server that is accessed simultaneously by multiple clients will have issues while the client and server data is being synced. The diagram below shows a client updating a server.

The figure above demonstrates how client C1 could potentially overwrite modifications made to the database by client C2. Client C1 is unaware of client C2.

Design Requirements for Syncing

The following fields are required for every table by the application for syncing:

1. Auto incrementing integer ID
2. Last Modified Time (LMT) field that is updated with current time whenever
   1. A row is ADDED or UPDATED at client end
   2. A row is uploaded to database server

Algorithm for syncing database

• Before downloading data the server and client clocks are to be synchronized

• When data is downloaded by client at time Download Time (DT) a copy of the server database (DBsv1) is stored locally. And DT is noted

• Whenever a row is ADDED or UPDATED by the client the LMT field is to be updated with current time

• After modifications are done, the client copy of the database (DBcl) is saved locally. Another copy of server data is to be downloaded (DBsv2)

• Create a list of [ID, LMT] for every table in DBsv1, DBcl and DBsv2. Lock DB at server from accepting any modification

• The following conditions need to be checked for every table in the database. Comparing DBsv1 and DBcl

Rows Modified at Client (By client C1)	Named List 1
ROW ADDED 1. If ID present in DBcl but not in DBsv1	RAcl (ROW ADDED at Clinet)
ROW DELETED 1. If ID present in DBsv1 but not in DBcl	RDcl (ROW DELETED at Clinet)
ROW UPDATED 1. If ID present in DBcl and DBsv1 && LMT in DBcl > LMT in DBsv1	RUcl (ROW UPDATED at Client)

• Comparing Named List 1 and DBsv2

Rows modified at Server (By client C2 or other)	Named List 2
RDcl ROWS UPDATED 1. If ID present in RDcl and DBsv2 && LMT in DBsv2 > LMT in DBsv1	DRUsv (DELETED ROW UPDATED at Server)
RDcl ROWS DELETED 1. If ID present in RDcl and not in DBsv2	DRDsv (DELETED ROW DETELED at Server)
RUcl ROWS UPDATED 1. If ID present in RUcl and DBsv2 && LMT in DBsv2 > LMT in DBsv1	URUsv (UPDATED ROW UPDATED at Server)
RUcl ROWS DELETED 1. If ID present in RUcl and not in DBsv2	URDsv (UPDATED ROW DELETED at Server)

• Start making changes to server. ADD all ROWS in RAcl to database server

• Delete ROWS in DRDsv from database server

• For each ROW in DRUsv alert the user. If possible show the contents at the server and ask for action to be taken

• For each row in URUsv alert user. If possible show the contents at the server and client side by side and ask for action to be taken

• For each row in URDsv alert the user. If possible show the contents at the server and ask for action to be taken

• Ask the user if new data is to be downloaded from the server. If yes load DBsv2 into the client

• Unlock the Database

Here are some standards that most users are accustomed to see in windows applications.

A standard menu bar. Keep the menu bar as standard as possible. Here is a standard menu item strip. Of course you will have much more in your menu bar. But here is the bare minimum.

File: New, Open, Save, Save As.., Print, Page Setup, Print Preview, Exit

Edit: Cut, Copy, Paste

Tools: Customize, Options

Help: Check for Updates, About

Display the opened file name in the title bar.

Ask for confirmation when the user deletes anything.

Ask for saving the active file if the user has unsaved changes.

A document processor (like Word or AutoCAD) should open a new file by default. A project management system (like Visual Studio) should not load any project by default.

While the user is to select an option use Radio button instead of combo boxes if there are only three or fewer options and if the number of options are not going to increase in the future. For example see the difference between the Hotmail User Sign Up form and the Yahoo! sign up form.

VS

Validate data only where the lack of validations will crash the application or make a feature unusable. For example validate all email ids in a mailing list app. Do not validate phone umbers in mailing list app.

A great thing about web applications is that there is no application setup required at the client end. Plus updates are automatically available as soon as they are deployed on the server. Windows applications however are not so fortunate in this area. Many a times we need to send software updates to windows applications. This could be a new feature that one has added to the application or a bug fix that was written after the software was released. Most windows based applications are distributed by MSI installers. But it would be really irritating for the user to uninstall and install new version every time a bug fix is released. There is however a new internet based approach to windows application updates. This post examines and explains the same.

Most applications distribute a separate application to along with the main app to check for updates. For the sake of reference we can call it an Update Manager or Just UM. Here is how a Mozilla Firefox also uses one called Software Updater (updater.exe) that can be accessed by clicking on Help->Check For updates.

Here is how it looks.

In reality an Update Manager will need to control the client environment. Here is the minimum set of requirements for an update manager.

Requirements

Check for Updates. The software publisher will have to maintain an updates file on a web site. This file will keep the filenames and latest version numbers of all files that are to be deployed in the client system to install the update completely. An XML file is most convenient for this purpose. Also a similar XML file needs to be placed in the client system that contains file names and version numbers of the files currently installed on the client machine. Updates will be checked by comparing the two XML files.

Replace files in the installed path. Most updates are just bug fixes or minor features that can be made available to the user by replacing the main executable in the application’s root directory. Wether a file is to be replaced or not is decided based on version number of the files.

Add new files to the install path. Some features or bug fixes need more than just one new executable file. For example the new build will depend on an XML file or a DLL.

Download and run patch files. Finally there could some changes required in the registry or elsewhere in the client environment as well. This is typically done by running patch files. Patch files will contain the word “patch” (or any other keyword) in the file names so the UM knows that it has to run these files after downloading them.

Automatically close and restart the main application. Most updates are deployed by replacing files; mostly the main exe file. So the UM will have to close the main application before replacing and restart the app after updates are downloaded.

Create back up of all files before replacing. There is no guarantee that the app’s new features are bug free. In fact the user may not want them at all. So the user should be able to “roll back” to his previous set up. This can be done if the UM creates back up of all files that are to be replaced.

Working

All application updates will be placed in a website location along with the Updates XML file. The UM will first download the updates XML file from a pre-define URL. The URL for the Updates file could be kept in a configuration file or hard coded in the application (I prefer the later). The Updates file contains three columns for each file namely File Name, Version Number, URL location.

UM will then compare the update files on the sever and files on the local machine and prepare of list of files to be downloaded. This list will contain

1. Newer versions of existing files 2. New files 3. Patch files

UM will then create new “temp” folder and download the files to this folder. It will then check the files that are to be replaced and create a back up of these files. Finally the UM will kill the main app, copy the downloaded files to the application path, delete the temp folder, run patch files if any and restart the main app.

Updates installed!

This method of publishing windows application updates is almost as hassle free as deploying web applications.