Educating the world

Our blog has over 10,000 readers a month

Set conditions in a MySQL database

November 1st, 2010

Ever come across the situation where you need to search for sets of values in a (MySQL) database? SQL along the lines of:

SELECT col1, col2
FROM table1
(col3 = 5 and col4 = 6) or
(col3 = 10 and col4 = 12) or
(col3 = 15 and col4 = 3)

I come across it all the time and it occurred to me that I am searching for a set of data and all of SQL is based on sets and subsets of data, so why can’t I search for a set in the same way as I search for a column? and you can! Here’s an example of how to do it.

First up we’ll need some test data. I’m using MySQL but it’s standard SQL and should work on anything. So we’ll create a database, table an add some data:

CREATE DATABASE bigsoft_set_test;
USE bigsoft_set_test;
INSERT INTO tab(a, b) VALUES("a", “b");
INSERT INTO tab(a, b) VALUES("b", “c");
INSERT INTO tab(a, b) VALUES("c", “d");
INSERT INTO tab(a, b) VALUES("d", “e");

Ok, lets start with an example of the old method:

FROM tab
WHERE (a = “b” and b = “c") or (a = “c” and b = “d");

This gives us the middle 2 rows from our table:

| a    | b    |
| b    | c    |
| c    | d    |
2 rows in set (0.00 sec)

Now let’s try it using sets:

FROM tab
WHERE (a, b) in (("b", “c"), ("c", “d"));

Surrounding a comma separated list of values with braces creates a set. Our set could equally be a sub-query for example:

FROM tab
WHERE (a, b) in
(SELECT * FROM tab WHERE a = “b” OR a = “c");

The star “*” in the query means all columns which on this table is “a” and “b". The sub query returns a narrowed list of values to the outer query which is just passed upwards. Similarly we can create a 3 step set query as follows:

FROM tab
WHERE (a, b) in
(SELECT * FROM tab WHERE a in ("b", “c"));

But why would you want this? Well there could be a couple of reasons:

  1. The main one is that of verboseness. Having to define the fields in a AND/OR/braces takes up a lot of space on your query line which is limited.
  2. Parse speed. The smaller the query, the less time it takes to parse, and yes it does make more difference than you think. Pre-parsing slowness is the reason we have the prepareStatement commands in all the connectors.
  3. Code simplicity. I deal with ranges of values and by dealing with them as sets, not caring about there individual components I have simplified my code base significantly.

Remember this is the conditional part of the query, so it can be used for DELETEs as well as SELECTs. Some databases only have a fixed amount of space for a query and the more conjoined conditions you can squeeze into your buffer the faster throughput your database can achieve.

What folders do I have shared?

October 21st, 2010

September 2010 was a very busy month for us so I didn’t really have time to write my usual 4 blogs a month. I’m 2 down at the moment so I thought I’d publish this one as it’s been sitting in my system for a while waiting for me to pad it out with one of my nice stories you like &#59;)

Windows is a bit of a pain when it comes to networking. All that additional security they’ve added is a bit pointless and to be honest I don’t really know anyone who uses it. Anything more than user, group and other adds unnecessary complication. Un*x has survived for the last 30 years without much problem and has helped administrators build working environments which are easy to manage and straightforward to look after. When was the last time you were shouting “Why are you not working” at your Micro$oft machine. It’s seems like a daily event. Don’t even get me started on MicrosoftNLB with multiple NICs - Jesus! But that’s a story for another time.

Anyway before I get too carried away I’m adding this to my pet gripes. How do I list all the folders (or shares) I have? In the wonderful world of Windows where there’s no such thing as a command line there’s no way to get this information - you just have to remember yourself. Well if you manage more than 1 machine then things start to get a little more difficult. So here’s what you do, drop to a command line - that’s the black box thing which lets you do all the really useful stuff and type:

C:\>net share
Share name   Resource                        Remark
IPC$                                         Remote IPC
D$           D:\                             Default share
ADMIN$       C:\WINDOWS                      Remote Admin
C$           C:\                             Default share
projects     C:\projects
installer    C:\MyStuff\Installer\AdvInst
q            C:\Shared
The command completed successfully.

The Whats and Whys to creating project descriptions

October 18th, 2010

I was reading the QMail wiki and came across a project called Qmail Toaster. I thought “ooh… that looks interesting” and wondered what it is. The QMail wiki page doesn’t say anything about what the project is for or what problem it solves. It just tells you to read the following web sites:

  1. Qmailtoaster home
  2. Qmailtoaster documentation wiki
  3. Qmailtoaster Plus project page
  4. Video tutorials

The Qmailtoaster home page is basically a list of features presented in no particular order. The Qmailtoaster documentation wiki’s introduction and history page just goes on about how easy it is to install even for an inexperienced user. The Qmailtoaster Plus project page says it’s an extension to Qmailtoaster - whoopy - we’re still not getting anywhere! I pinned all my hope on the Video tutorials but the site is down.

I read around the web sites and after 10 minutes, I was still none the wiser as to what the project was actually for and what job it did inside QMail.

Hunted around a little bit more and found out that “QmailToaster-Plus is an RPM package that contains a menu and supporting scripts which add additional features and utilities to your QmailToaster” - great so what is QmailToaster?

Another page says “Miguel’s goals were to provide a very stable rpm based Qmail MTA” - ok now we are getting somewhere. So what is a “Qmail MTA” and how does that fit into QMail?

I don’t mean to be so flippant, but it took 10 minutes to find out what this project is actually for once I had got passed all the easy-to-use, rpm-packed, more-features-than-you-can-shake-a-stick-at style adverbs.

It constantly surprises me how many projects miss this out and think we can deduce what the project is for by reading a stream of features.

I’m not psychic, and I don’t want to spend ages hunting around for answers. Documentation comes in 2 parts. The first part is essential: what is the project for, and the second is optional: how do you use it. The second part maybe deduced by reading source code or forums and so can be omitted. Once you’ve seen enough open source projects you will realise that how-to-use documentation is a luxury but you can get by without it. Whereas the 1 or 2 sentences that open a project are the most important. They tell you whether or not you need the project. If you don’t then you can stop reading straight away and move on to the next project.

Some guidelines

Here are some guidelines to help you with defining your project.

  1. Give the section its proper title
    The best example of this is a section header called “What is Blar". Most people who are looking for information will sooner or later end up at Google’s front door and they are going to type “What is Blar?". Do you want them to be given a link to the front page because Google can’t actually tell you what it is, or would you like a link taking you to the section of the documentation which describes what Blar is? Do you want the punter’s question to be answered straight away or do you want them to have to hunt around your web site trying to figure it out; all the while wondering why Google didn’t display the “What is Blar” page!
  2. Have a proper start to the What-Is section
    Your What-Is section should start in the standard format beginning with “Blar is a …". Once you have said what it is (in one sentence), you can follow it up with another sentence to say what it might be used for or what problem it solves.
  3. Don’t use acronyms
    Every time you use an acronym you are giving the reader a chance to leave because they have to go away and look up the acronym. You could argue that if the reader doesn’t understand what JPA stands for then they have no business reading your documentation, but you will find that in every given area (especially in computing) about 75% of your readers will be beginners. If they were experts they probably wouldn’t be looking for help in your documentation!
  4. Don’t use jargon
    This is the section of your documentation that users are going to read first. They are going to use the outline drawn up from this section to base all their subsequent research on, so make it simple. Anyone should be able to read your What-Is section and know if it is something they would like to invest their time in.
  5. Keep it short
    If you can’t condense your project down into a couple of sentences how are you going to organise your real documentation.
  6. Don’t reuse words from your project name to describe the project
    A nice example of this is “A toaster toasts your bread” - hurray! If I don’t understand the concept of toasting then I’m not going to get anywhere.

Here are a couple of examples to describe what I’m getting at.
1. What is a Toaster?

“A Toaster is a device for cooking bread.”

One sentence that really says it all.

2. What is Apache Camel?

“Apache Camel is a rule-based routing and mediation engine which provides a Java object based implementation of the Enterprise Integration Patterns using an API (or declarative Java Domain Specific Language) to configure routing and mediation rules. The domain specific language means that Apache Camel can support type-safe smart completion of routing rules in your IDE using regular Java code without huge amounts of XML configuration files; though XML configuration inside Spring is also supported.”

Only 2 sentences. Even a brick layer or an accountant could have an idea about what this project does:- something about writing industry plugin’able standards.

3. What is MantisBT?

“MantisBT is a web based bug tracking system that was first made available to the public in November 2000. Over time it has matured and gained a lot of popularity, and now it has become one of the most popular open source bug tracking systems. MantisBT is developed in PHP, with support to multiple database backends including MySQL, MS SQL, PostgreSQL and DB2.”

The first part of the first sentence outlines what the project is, then follows a brief elaboration. Still only 3 sentences, but it tells you everything you need to know to decide if you want it or could use it.

I wrote to the project lead of QMailToaster explaining to him how difficult I found it to figure out what his project was for. So just to show there is no hard feelings here’s their new version. It still needs a bit of work (IMO) but it’s a start, “What is QMailToaster?”

“QmailToaster is a full blown mail server distribution for use with most rpm based Linux distributions. QmailToaster is distributed as source rpm’s which require compiling on the host system before installation. This is done to meet licensing requirements set forth by the original creator of qmail, D. J. Bernstein.”

Free Conference Call Service

October 12th, 2010

Before any free conference call service existed, calls between three or more people, were reserved for the senior-level administration of the world’s mega-corporations. The technology was new, the hardware expensive and most people did not make enough conference calls to justify the cost. However, due to developments in the technology world and the emergence of free conference call services, conference calls are now available to pretty much anyone.

If you think about it, most businesses are relying less and less on regular phone lines and are using some type of free conference call service on a daily basis. This service allows them to hold meetings, make presentations and conduct day-to-day business with remote offices and clients for free. The conference call itself and the free conference call services have changed the way business is done, telephones and faxes are now a thing of the past and the role of email has changed all due to the popularity of conference calling.

As a result a growing number of businesses are offering free conference call services. The technology is easy to use, reliable and safe. In a period of economic uncertainty, finding a free conference call service just makes good business sense. Yet, sometimes you do have to pay for quality. That’s why it is good to shop around when looking for a conference call provider. Some providers charge more for premium services than others do. You need to first assess your teleconferencing needs before you make any commitments.

Options for handling colleagues GMail

October 7th, 2010

Support question:
For outlook users they are trying to find how to set out of office notifications and where the tools and rules are for setting email forwarding so that if one member of staff is out of the office their emails can be directed to whoever is covering their work.

Settings for these features are not related to Outlook or any other mail application. All the settings needed to set these features are set up via the GMail web site.

GMail provides several options for handling mail when a colleague is away.

Out of Office notifications

  1. Log in to GMail.
  2. Click the Settings link in the top left of the page.
  3. Go to the General tab.
  4. Scroll down to the Out of Office AutoReply section.

This section allows you to define the start and end of your out of office period. There is also a space for you to specify the automatic email’s Subject and message body. You can also narrow the list of people you would like to send out-of-office responses to. The options are everybody, people in my contacts or people in my company.

After you have made your changes go down to the bottom of the page and click Save Changes.
More information here:

Forwarding to another user

  1. Log in to GMail.
  2. Click the Settings link in the top left of the page.
  3. Go to the Forwarding and POP/IMAP tab.
  4. Scroll down to the Forwarding section.
  5. Click the Add a forwarding address button.
  6. Enter the email address you would like to forward to and click Next. An email will be sent to that address asking them if they want to accept forwarded messages from you.
  7. The forward recipient must log-in to their mailbox and accept the request.
  8. Go back to Settings->Forwarding and POP/IMAP->Forwarding and set the options which say what you would like to do with the email after it has been forwarded e.g. move, delete

Delegate (or share) access to mailbox
Administrators must switch this feature on in the Manage this domain->Settings->Email->Mail Delegation

  1. Log in to GMail.
  2. Click the Settings link in the top left of the page.
  3. Go to the Accounts tab.
  4. Scroll down to the Grant access to your account section.
  5. Watch the Google Support video explaining everything.