Beyond Compare - File Comparison Tool

Sometimes it's the simplest activities that take the most time. If you're in the game of software development making changes in multiple versions of source files is a frequent and not very interesting activity. You'll often be working on your next major version then need to make a new minor version release either due to a problem in the current release or a need to support some new feature (like an unexpected tax rate change).

I'm showing my age here but in the past I've always used Windiff (in my Visual C++ 6 days). Windiff used to let me find out what the differences were between files. I could then merge file contents using a text editor. This process works but it was sometimes difficult to get files to match exactly as there were often extra spaces of line breaks.

Windiff disappeared from Visual Studio 2005 so more recently I've even restored to the old DOS FC command. I did have a quick play with Visual SourceSafe but the solution wasn't really flexible enough - often I want to compare and merge files without locking something down in a configuration management system.

However, I recently came across a package called Beyond Compare from Scooter Software. This package is absolutely fantastic and is now my second favourite software tool (after Visual Studio).

There are a number of good things about Beyond Compare.

Firstly it remembers folders you've compared in the past. This means you can do a new comparison and merge more easily the second time.

Secondly it lets you move updates from one file to another with a single mouse-click and shows you the results. Any extra/ missing whitespace differences are easily ignored. This makes merging versions take minutes rather than hours.

Thirdly, the software tool itself presents a trustworthy user interface. As you're doing a compare and merge with Beyond Compare, you can refresh the file comparison on the fly and check that things are absolutely identical. Knowing things are the same - bar the differences you want to keep - is vital to ongoing software development.

If merging files and versions of software is something you do regularly I'd recommend going to the Scooter Software site and taking a look at Beyond Compare.

PHP, MySQL date and time formats

PHP and MySQL have different formats for storing dates and times. This can cause confusion when trying to compare a date in PHP and a date stored in a MySQL database.

PHP stores dates and times in Unix or UTC format. This format encodes a time as an integer count of the number of seconds since New Years Day began in 1970 (1st Jan 1970). MySQL on the other hand stores dates and times in string format. All three MySQL date/ time types DATETIME, DATE and TIMESTAMP store data in string format.

MySQL DATETIME and TIMESTAMP columns store in the format:

YYYY-MM-DD HH:MM:SS

MySQL DATE columns store in the format:

YYYY-MM-DD

PHP and MySQL date/ time comparisons

Obviously, this difference in format causes problems when inserting dates and times into MySQL databases and especially when comparing MySQL and PHP dates for example trying to select entries from a table where a date value in the table is older than a PHP date. The easiest way to do this is to use the UNIX_TIMESTAMP MySQL function. For example:

// You could get the date as three integer values from a querystring on the PHP page:
$day = $_GET['day'];
$month = $_GET['month'];
$year = $_GET['year'];

// Then use the day, month and year to construct a PHP time (in seconds since
// 1st Jan 1970 format).
$datetime = strtotime($year.'-'.$month.'-'.$day.' 00:00:00');

// Then find all entries newer than the date given by the querystring arguments.
$sql = "SELECT * FROM MyTable WHERE UNIX_TIMESTAMP(UpdateDate) >= '" . $datetime . "'";

$result=mysql_query($sql);

The above SQL query causes MySQL to convert the value of the DATETIME (or DATE or TIMESTAMP) value in the UpdateDate column in the hypothetical table into a PHP format date before doing the compare.

Storing a PHP date/ time value in MySQL

To convert a PHP format date/ time into MySQL's format, the MySQL FROM_UNIXTIME function can be used. For example:

$currenttime = strtotime("now");

$sql = "UPDATE MyTable SET UpdateDate = FROM_UNIXTIME(" . $currenttime . ") WHERE ... ";

$result=mysql_query($sql);

Don't use strings for PHP/ MySQL date > or <>

You might find some PHP code examples, trying to use the PHP date() function to format PP dates and times as strings in the correct format to match MySQL values. If you can remember the correct format strings for the conversions this will work as long as you are setting values in rows. For comparisons however, strings aren't any good as the comparison will be an alphabetic/ string comparion and not the required date comparison.

VistaDB, SQLite and Microsoft Access

Recently I've been carrying on my investigations into databases. SQLite originally looked promising as an alternative to Microsoft Access - read my SQLite investigation post for more info. SQLite looked small, fast, robust and very easy to install. However, I eventually decided it wasn't going to work as an Access replacement for me.

The main reason I decided not to replace Microsoft Access with SQLite was that I'd got too used to ease with which I could get when using the .Net datatypes seamlessly with Microsoft Access, e.g. I could read and write the .Net Decimal type straight to Access. With SQLite it seemed that I might have to convert to and from strings to preserve accuracy. This wasn't a big showstopper but I'm afraid I like my programming to be as easy as possible.

I'd pretty much resigned myself to using Microsoft Access but then I came across a really neat database system called VistaDB. VistaDB looks (I suspect) like it may have been originally based on SQLite but the great thing about it is that it has the rich data type support you get with Microsoft Access - plus extras like stored procedures - without losing the no hassle deployment of SQLite. I've spent a few hours evaluating VistaDB and so far it looks ace. For my particular application it solves all the downsides of both SQLite and Microsoft Access.

SQLite and ADO.Net: Acting as an Access replacement

I've had a bit of a play recently with SQLite and ADO.Net to see if I could slot in SQLite instead of MS Access in my desktop application. As yet I haven't made my mind up, but it's been a very interesting exercise. Here's a quick summary of my findings so far.

DataType Support

With my limited experience, SQLite seems to support fewer data types than MS Access. On the whole this isn't a problem apart from two areas - the .Net Decimal type and the .Net DateTime type. In both these cases I think you end up having to encode the data as strings and take care of any localisation issues yourself, e.g. making sure dates stored in France can be read in the UK and vice-versa. I haven't delved into Decimal storage too much but I think strings will need to be used instead of squeezing data into the Double data type and experiencing potential rounding errors.

Complete ADO.Net Support

The SQLite support is pretty comprehensive but misses out in one or two areas like the RowStatusUpdated event handling.

Error Handling

If you open a connection to a non-existent DB, SQLite seems to create an empty database at the specified path. This isn't a big issue but it means that the "DB doesn't exist" error turns into a "Table doesn't exist" error when you try to access a table in the DB.

Note: Since I wrote this post, I've found out that you can use connection strings to alter the default behaviour of creating an empty DB when the target DB can't be found. Read SQLite Connection Strings for more info.

Performance

On first impressions, data access with SQLite is very quick and certainly quicker than MS Access. However, if you have to start encoding and decoding data into strings (DateTime for example), I'm not sure that the performance wouldn't start to degrade.

Installation

With SQLite there is essentially nothing to install. All you need to do is copy the assembly into your application folder and you are up and running. SQLite wins hands down here.

Conclusions

None for now, except to say that SQLite looks very tempting as an Access replacement especially as it's so easy to deploy. I'll post more info as I gain more experience.

SliQ Invoicing 1.6 Released

SliQ Invoicing 1.6 has now been released. Although it's only 6 weeks since version 1.5.1 of SliQTools invoicing software was released, 1.6 is quite a significant update. In 1.6, a number of options have been added to allow customers to modify the provided templates for invoices, quotes and credit notes.

As of 1.6, templates can now optionally include a tax rate column. If desired, the tax value column can now be ommitted and users who are VAT/ tax registered can now choose whether the total column is the gross or net amount. One of the biggest additions to version 1.6 however is a label editor.

The label editor is provided on the Template Setup tab. The editor lists all the fixed text labels on the selected template preview and lets the user enter their own value. For example, US customers might like to change the Delivery Address label to Shipping Address. Using the new label editor it is even possible to fully translate the standard templates into Spanish, French or German for example.

For futher details of the modifications and additions in SliQ 1.6 and to download the latest copy, visit the SliQ Release History page.

Free Software Downloads

In the past couple of weeks, SofwareLode, a shareware downloads site has been growing in popularity. More and more pages are being pulled out of the supplemental index and the number of page views per day is now into the thousands.

There are a number of reasons for the increase in traffic.

• Being a shareware directory, SoftwareLode naturally builds links over time as authors link either to the homepage or to the details page for their software packages.
• As well as an increasing number of links, better internal linking has also helped. Each software details page now links to up to 10 related programs, i.e. programs with the same keywords. Getting more, relevant links to the program details pages helps pull pages out of the supplemental index.
• Better linking from the homepage into the rest of the site spreads the homepage PR around more efficiently. The homepage now lists top selections in a number of categories. The details pages for the top selections then link to related programs and so improve the rank of lots of the inner pages.

Breaking away from the usual software downloads site categories also seems to be showing some benefit. The antivirus software and free dvd software pages seems to be attracting a decent amount of traffic these days.

As SoftwareLode was launched only 7 months old, I'm pretty pleased with its performance and hopeful of further increases in traffic in the months ahead.

ADO.Net, DataAdapters & DataSets: What are they?

ADO.Net is Microsoft's .Net interface to databases. Traditionally, to work with databases like Access, SQL Server and the like, you needed to know a fair bit about SQL. With ADO.Net you still need to know SQL commands but some pretty near classes are provided that allow you to hive off the SQL stuff and work with a much easier set of objects when adding, deleting or updating items in a database.

The two major classes described in this post are the DataAdapter and DataSet classes. It wasn't until I'd actually coded some example that the ease of use of these classes became clear to me. I'll expand on the classes in later posts and give some code examples but for now I'm just going to give an overview of their purpose. This picture gives an idea of how the classes interact to allow you (the programmer/ user) to work with a database.

The four items in the picture are:-

The Database

This is something like an Access or SQL Server database. ADO.Net provides classes to handle many different types of database. All the classes inherit from a set of base classes so to a degree you can hide the details of the specific database type from your code.

The DataAdapter

The DataAdapter class is the SQL workhorse. There are a number of different DataAdapter classes for different databases, e.g. OldDataAdapter for working with an Access database. It's the DataAdapter class that does all the work - reading, inserting, deleting and updating - in interacting with the database. All you have to do is build the SQL commands for the DataAdapter to do the work and then let it get on with it's job.

The DataSet

The DataSet is the class you interact with when manipulating data values in the database. The data within a table in the actual database can be thought of as a collection of rows, with each row containing a number of named field values. The DataSet mirrors this view of the database. The DataSet is a collection of DataRow objects, with each DataRow begin a Dictionary of the values in the row where the dictionary keys are the field names.

How do the classes interact?

To work with the data, you configure a DataAdapter instance with the SQL commands to read data, insert, update and delete data in the database. You then ask the DataAdapter to fill a DataSet. You can then change values in rows in the DataSet, add new rows or delete rows. When you've made the changes, you ask the DataSet to get the DataAdapter to reflect your changes into the actual database.

In my next post, I'll give some code examples using the DataSet and DataAdapter classes.

Error reading setup initialization file: InstallShield Problem

Yesterday I had a support mail from a user who was unable to install on Windows Vista Home Premium. I was rather worried about the report since we'd just release SliQ 1.5.1 the evening before and I always get a bit nervous when we make a new software release.

The error the user was getting was "Error reading setup initialization file". Googling for info I found information saying that the error was sometimes reported if the InstallShield package had been corrupted. I tried downloading the lastest installer from SliQTools and tested it successfully on Vista and XP machines here in the office so I knew the live release wasn't corrupt. More research on Google indicated that the problem sometimes occurred if the installer took a long time to download -it was taking over 20 minutes on the user's Vista machine. Luckily the user was very technically aware and was very helpful in trying out different things.

I asked the user to download the installer to a Windows XP machine. This time the download took only 5 minutes over the same office broadband connection as with the Vista machine. The installation ran perfectly. The user then copied the installer on a flash drive and installed correctly on the original Vista machine. That evening the user download and installed correctly on his home Vista machine.

I find it hard to believe but the finger is pointing at the installer package being corrupted during the download process. There's not a lot I can do about people's broadband connection but I may have to think up some strategies for reducing the size of the download.

Getting out of the supplemental index

Back in late summer I reevaluated the linking strategy between my websites. Up until then I'd used my main site to feed link juice into my newer sites - softwarelode and so on. I decided this was a bad thing to keep on doing, since my original intention was to feed link juice back into my main money-earning site and not do things the other way around.

It was interesting though to see how well sites like softwarelode responded to getting a few links from my main site. Basically within 3 weeks softwarelode started getting a few hundred visitors a day even though it was a new site. Predictably, when I removed the links the visitor numbers began to fall but at a much slower pace than the visitor numbers grew in the first place. Rather than the 3 weeks or so for the visitor numbers to peak, it took 2 to 3 months for the visitor numbers to fall away. During those 3 months, Google did some mini toolbar PageRank exports and some of the inner pages of softwarelode started showing PRs of 2 or 3. By early December though all pages apart from the homepage were showing PR N/A and visitor numbers were 20% of the peak.

As the visitors fell away, more of the softwarelode pages were falling into Google's supplemental index. When a page is in the supplemental index it's not going to turn up in SERPS execept for very specific/ obscure search phrases. The supplemental index is purgatory for web pages. I had a look around the web to see what advice I could find. As to be expected the advice was that old chestnut - build backlinks. So, before Christmas I did a spurt of backlink building and I'm pleased to say that since the New Year visitors are returning to softwarelode and the Adsense income is beginning to climb again. Since yesterday (5th Jan), an extra 350 pages are marked as being in the main index. I know of similar sites to softwarelode with about 2500 pages in the main index that make a decent amount of Adsense income (few hundred dollars a month) so hopefully I'm on target to making softwarelode an earning website by the middle of 2009.