Kigtek Solutions

November 5, 2016 | Company News

Kigtek Logo

Kigtek Logo

We are very happy to be working with Kigtek Solutions, a leading Systems Integrator and Electrical Installation Services company based in the central belt of Scotland.  Work on this project is ongoing and is expected to be completed by the end of November 2016.


ISBN13 to ISBN10 Converter for SQL Server

October 24, 2014 | Blog Technical Solutions

ISBN13 -> ISBN10

Here’s a handy function for converting a 13-digit ISBN to its 10-digit equivalent in SQL Server T-SQL.

CREATE FUNCTION TO_ISBN10 ( @isbn varchar(13) )
    DECLARE @isbn10 varchar(10);
    DECLARE @forwards int;
    DECLARE @backwards int;
    DECLARE @check int;
    DECLARE @thisval int;

    SET @isbn10 =substring(@isbn,4,9);
    SET @forwards = 1;
    SET @backwards = 10;
    SET @check = 0;

    WHILE @forwards <= 9
        SET @thisval =cast(substring(@isbn10, @forwards, 1) AS INT)* @backwards;
        SET @check = @check + @thisval;
        SET @forwards = @forwards + 1;
        SET @backwards = @backwards - 1;

    SET @check = 11 -(@check % 11);

    IF @check = 10 begin
        SET @isbn10 = @isbn10 +'X';
        SET @isbn10 = @isbn10 +cast(@check AS VARCHAR(1));

    RETURN @isbn10;


Listing Outlook mailbox rules for all users

June 25, 2014 | Technical Solutions

Does your company use Outlook email forwarding? As your organisation has grown, have the rules become hard to manage?

We have a customer using Exchange Server for a few dozen users. They use mailbox rules so they can delegate various activities when staff are out of the office. Occasionally they lose track of what’s being forwarded, and it’s a pain to get this information from each user one by one. Here’s a little PowerShell script I threw together to automate it. I ran this on their server from the “Exchange Management Shell”. It’s my first PowerShell script ever; I’m used to Linux shell scripts and up till now haven’t made complex scripts under Windows. That may change:

PowerShell Script to list Outlook mailbox rules for users in an organisation

$users = get-mailbox

ForEach ($user in $users)
        $rules = get-InboxRule -Mailbox $
        if ($rules.length -gt 0) {
                echo ""
                echo $
                echo ""
                $rules | select name, priority, description | fl
                echo ""

Save this in a file with the extension “ps1” (that’s a one on the end) and run it from PowerShell to get the output – use > to redirect to a file if necessary. If you put the script in the current directory, you may need to explicitly specify the path, for example: .\run.ps1 > rules.txt Note that this only shows the server-side rules, such as placing messages in a folder, forwarding, etc. Anything that interacts with the client machine such as a popup will not appear in this report – this suits our customer because they want the rules to work when a user is not even logged in. The customer is pleased they can see the server-side rules in one report, and I’ve learned that it’s not just Linux that can support powerful scripting!


Oracle Function-based Index

May 7, 2014 | Technical Solutions


In this post, we give an example to show how a function-based index in Oracle can increase the speed of a query where null and not null values are being compared.

Our customer asked us to modify their application to enable an ETL tool to collect invoice records from an Oracle database. The invoice table contained upwards of 8 million records, but we were really only interested in looking at records which had been flagged for collection but not yet flagged as collected. However, the ETL tool’s method of flagging these records did not allow us to use a simple bitmap index to quickly retrieve the rows we needed.

What is ETL?

Extract, Transform, Load refers to a process that extracts data from outside sources (e.g. a file produced by a customer), transforms it to fit operational needs, and then loads it into your target database.

You can read about an example ETL tool, Pentaho Data Integration on our Technologies page.

Invoices example

Consider the following example data set for our Invoices table
Invoices table rows

In this example, the two columns with names starting ETL_ behave like flags. The possible values are null or a varchar2 value which happens to be based on a timestamp. For the purposes of this exercise, we are only interested in whether the values are null or not null.


which would retrieve the following rows

Queried rows from the Invoices table

Optimising the query’s performance using an index

In a table containing several million rows, this query would take too long to run. Adding a B-Tree index on ETL_collect and ETL_done wouldn’t help because the null values would not be included in the index. Bitmap indexes do include nulls but we have such a range of different not null values in our table, that a bitmap index is not the best choice.

We need to transform our ETL columns from null / not null values to 0 / 1 and then use an index to optimise the query. First we create a function that tests if a value is null, and returns a 1 or 0 accordingly –

    field_in IN INTEGER
  if field_in is not null then

By declaring this function to be DETERMINISTIC, we indicate that the function returns the same result value whenever it is called with the same argument.

Now we create an index on our ETL columns which makes use of this function on both of the columns we are testing –

ON Invoices (ETL_Match(ETL_collect), ETL_Match(ETL_done));

And now to revisit our original query. To force it to use this index, we need to reference the indexed columns in the where clause, and we can do that as follows:

WHERE ETL_Match(ETL_collect) = 1
AND ETL_Match(ETL_done) = 0;


With an underlying invoices table containing more than 8 million records, the original query took 17 seconds to retrieve 70 rows. The new query using a function-based index returns 70 rows in less than 3 seconds.

Can we help you?

If you have a database or application which is running slowly, we can help you make it perform better. Please give us a call or use the contact us form on the right, and we will get in touch.


Photos from the London Book Fair

April 9, 2014 | Company News

Maria visited the London Book Fair on Tuesday this week. Buzzing with activity and enthusiasm, it’s great to see our thriving book industry in action. Lots of familiar faces too, and chocolate frogs!

Maria with some of the Batch team

Maria with some of the Batch Team at the Book Fair

LBF International Stands

The London Book Fair 2014 at Earls Court


April 8th was International Terry Pratchett Day


Hop along to the Batch stand for a chocolate frog




Backups affecting system performance – our LVM2 solution

April 4, 2014 | Technical Solutions


Our customer had a vendor product which used a MySQL database.  The nightly backup of the database was preventing users from accessing the data for the duration of the backup.  As the data grew, the backup was taking longer and longer, and this was causing problems with other system activity – timeouts and failures had become an issue.

There are various approaches to backing up MySQL which we don’t cover here.  In this case, it was not feasible to change the customer’s backup procedure at the time, so this workaround, which took just a couple of hours to implement and test, helped them avoid regular downtime for a few months until an alternative backup approach could be rolled out.

We reduced downtime from over an hour to a few seconds

Before we addressed the problem, our customer’s data would be inaccessible for at least one hour each night.  After we implemented our solution, the downtime was reduced to a few seconds.

If you are interested in reading the technical details of this solution, we share them below.  If you’d prefer to speak to us about a similar problem in your organisation, please fill in the contact form on the right and we will get in touch.


Ewan’s Technical Notes

A redeployment of the vendor software was already on the horizon, including database replication as the vendor’s preferred backup strategy. In the meantime, what was the most cost effective way to prevent the old backup from disabling the system?

LVM2 – Logical Volume Management

I had previously used LVM2 snapshots to perform low level file system backups on a running system. The commands are well documented elsewhere, so a summary should suffice: If a file system (volume) has been created in a volume group with enough free space left over, an instantaneous snapshot can be taken and maintained by LVM2, using copy-on-write.  My previous use of LVM2 was to create such snapshots and run “dump” on those to perform a backup, in the knowledge that if I’d got my sums right, the snapshot would remain valid long enough to complete the backup, without interrupting any continued attempts to change the real file system.

Of course, database backups should not normally be a simple backup of the file system. Even if a snapshot is taken to guarantee no changes to the data being backed up during the process, a restore does involve an element of recovery – to the database, it looks like the system was shut down uncleanly. How could the instant snapshot to be used to take a conventional database backup?

The solution was simple, and had been hinted at elsewhere in my Google searches: mount the LVM2 snapshot as a normal file system, and create a second MySQL configuration to allow it to be accessed, albeit through a non-standard MySQL port. Then a backup could be done by pointing “mysqldump” to this copy. After the backup, the second MySQL would be shut down, the file system unmounted and the snapshot deleted.

Digram of how we used LVM Snapshots to backup MySQL

Using LVM Snapshots to backup MySQL

So that the second MySQL would not “wake up” to a file system requiring recovery, there was the need to lock the tables just like the previous “mysqldump” would do – but only for long enough to create the LVM2 snapshot, which is just a few seconds. Reducing the system pause to such a short time meant there were no longer any timeouts or failures.

This change to the backups was quick to implement and bought us time to properly roll out new deployments of the vendor software with an entirely different mechanism for backups. Having previously enjoyed using LVM2 for more conventional uses, this was an interesting and novel use which gave us a few months to breathe more easily.


User Testing with Real Users – the Icing on the Cake

April 4, 2014 | Software Development

When we develop software, we are guided by our customers to tell us what they need. Quite often, however, the people who will be using the final product are not our customers, but their customers. To put the icing on the cake, we need to do some Usability Testing with real users. Such as Chris.

Chris Who?

Chris McLaughlin is a former colleague of mine who now runs a a busy academic bookshop at Glasgow Caledonian University. He has a button named after him. The Chris Button. Well that’s what we call it, anyway.

Testing, Testing …

As software developers, we’re testing things all the time. As soon as we have written a chunk of code to do something, we will test it to see if it does what we want. The bigger the system gets, the more we need to test, and of course, if we go back and change something, we need to test that the change hasn’t broken something else. You can’t really get away from that kind of testing. However, that’s not what I want to talk about today. This article is about the importance of usability testing, which can often be overlooked.

What is Usability Testing?

Usability testing involves evaluating a working piece of software by presenting it to a user – a real user, preferably someone who is going to be using the finished product – and observing what they do with it. There are no rights or wrongs for the tester, we just want to know what they make of the software, so we resist the urge to jump in and correct them when they do something unexpected. We can learn a huge amount by watching (and listening, if they are happy to think aloud as they test), and this can feed back into the software design to make the next release better.

A New Returns Solution for the Book Industry

In 2009 I was working on a project to implement a returns system for books. The book industry has an agreed process whereby bookshops can return books to the suppliers if they don’t sell within an agreed timescale. First they have request permission from the supplier. The supplier responds with an authorisation message, which indicates how many copies they will accept, and then, all being well, the books are returned, and the shop sends a third message to the supplier to let them know how many copies have actually been sent. This whole process can be done manually via email, fax or (in the old days, when I was a bookseller) by post. However, it is time consuming, and tedious. The Batch Returns service provides a standard way for booksellers and suppliers to send and receive electronic Returns messages, resulting in a turnaround time of a few hours. The only manual bit is putting the books into boxes.

Giving Something Back

I was delighted to be working on this project, given my bookselling background; I must have handled thousands of book returns, and reams of associated paperwork, twenty-odd years ago before Batch Returns even existed. And here I was, giving something back. However, mindful of the fact that I hadn’t worked in bookselling for several years, I was keen to get real users involved in evaluating the system as development progressed. I made a few phone calls and arranged to visit Chris at his shop to show him what I had so far. I think I met with him three times in total, and each time I would come away with a few ideas on how to improve the interface and the workflow. During one of our meetings I learned that he sometimes needed to return a long list of books, each with the same quantity, and that using the arrow keys to move down the list adjusting the quantities one by one was a pain. I took this on board and on the next visit I introduced him to the Chris Button which was well received.

Usability Testing inspired the Chris Button in Batch Returns

The Chris button allows users to quickly set all quantities in a long list of books to the same value.

On seeing the Chris button, Batch recognised its potential on other screens, and we added a variation to the suppliers’ interface to speed up the process of authorising returns.

The Set All Button in Batch Returns

The “Set All” Button on the Suppliers’ Authorisation screen offers a choice of fields to set.

The idea of a button which allows the user to apply the same action to multiple lines is by no means groundbreaking. We see it in software all the time. But this story highlights the importance of spending time with end users, learning how to make their experience better. Never assume you know better than a real user.

Don't Make Me Think book by Steve KrugFurther Reading

Tags: , , ,