Peterbe.com

There are several ways to do case insensitive string matching in SQL. Here are two ways that I've tried and analyzed on a table that doesn't have any indices.

Option 1:

(
 LOWER(u.first_name) = LOWER('Lazy') OR 
 LOWER(u.last_name) = LOWER('Lazy') OR
 LOWER(u.first_name || u.last_name) = LOWER('Lazy')
)

Option 2:

(
 u.first_name ILIKE 'Lazy' OR 
 u.last_name ILIKE 'Lazy' OR
 u.first_name || u.last_name ILIKE 'Lazy'
)

A potentially third option is to make sure that the parameters sent to the SQL code is cooked, in this case we make the parameter into lower case before sent to the SQL code

Option 1b:

(
 LOWER(u.first_name) = 'lazy' OR 
 LOWER(u.last_name) = 'lazy' OR
 LOWER(u.first_name || u.last_name) = 'lazy'
)

Which one do you think is fastest?

The results are:

Option 1:  2.0ms - 2.5ms (average 2.25ms)
Option 1b: 2.0ms - 2.1ms (average 2.05ms)
Option 2: 1.7ms - 2.0ms (average 1.85ms)

Conclusion: the ILIKE operator method is the fastest. Not only is it faster, it also supports regular expressions.

I've always thought that the LIKE and ILIKE were sinfully slow (yet useful when time isn't an issue). I should perhaps redo these tests with an index on the first_name and last_name columns.

Here at Fry-IT we use timesheets, like so many other companies, to track the time we spend on each client project. Despite being a very "web modern" company we still don't use a web application to do this. What we use is a python script that I wrote that uses raw_input() to get the details in on the command line. The script then saves all data in a big semicolon separated CSV file and is stored in cvs. This works quite well for us. It's in fact all we need in terms of actually entering our times which is usually very easy to forget.

But, here's an idea for a timesheet tracker that will not guarantee but will really help in not forgetting to fill in your timesheets. The idea is that you have a web application of some sort that is able to send out emails to registered individuals. These emails will be sent at (a configurable time) the end of the work day when you're about to leave for the day. You might have seen this before on other timesheet tracker applications; it's not new. What is new is that the email would contain lots of intelligent URLs that when clicked fills in your timesheets for that day.

Everybody can click on URLs in emails to open them in a nearby web browser. Obviously all of these URLs need to contain information about the day and the login credentials of the user so that you don't have to login on some site after you click the URL. Every URL would thus contain login stuff and a particular entry to the timesheet tracker. Something like this:

http://timesheeting.com/Xgt4q/_8_hours_Project_ABC
http://timesheeting.com/FpE26/_6_hours_Project_ABC
http://timesheeting.com/2Jt9a/_4_hours_Project_OCH

The first part of the URL is an encoding of the user's login and the date (date of when the email was created) and the second part is so readable that you can find which one suits you by simply reading the URLs. If you need to enter a comment for every piece of work you do, that comment form can be shown when you click the URL on the site.

Another very important detail is that the system has to be smart enough to know which links it should offer. It can do this by cleverly looking back at what the user entered the last time and the time before that etc. It should require much, you hash every different combination of hours and project and sort by last usage date. If you need to start tracking a new project or an exceptional number of hours then the email alert isn't for you. Remember, it's just a clever improvement to the usual "Don't forget to fill in your timesheet!".

Now, feel free to steal this idea on your own timesheet tracker applications. I've got too many other dreams that I need to try first. Writing about it means that I at least won't forget about the idea.

UPDATE

This comic is soo relevant and soo funny that I just have to include it

Since I always forget how to do this and have to reside to testing back and forth or to open a book I will now post an example patch that alters a table; correctly. Perhaps this is done differently in Oracle, MySQL, etc. but in PostgreSQL 7.4 you can't add a new column and set its default and constraints in the same command. You have to do that separately for it to work and most likely you'll have to run a normal update once the column has been added.

Just doing:

ALTER TABLE users ADD mobile VARCHAR(20) NOT NULL DEFAULT '';

...won't work and you'll get an error.

Suppose for example that you created a table like this:

CREATE TABLE users (
"uid"                  SERIAL PRIMARY KEY,
"password"             VARCHAR(20) NOT NULL,
"email"                VARCHAR(100) NOT NULL DEFAULT ''
);

But what you really want is this:

CREATE TABLE users (
"uid"                  SERIAL PRIMARY KEY,
"password"             VARCHAR(20) NOT NULL,
"email"                VARCHAR(100) NOT NULL DEFAULT '',
"mobile"               VARCHAR(20) NOT NULL DEFAULT ''
);

Either you can drop the table and all its content and start again or else you can do the following:

BEGIN;

ALTER TABLE users 
 ADD mobile VARCHAR(20);

ALTER TABLE users
 ALTER mobile SET DEFAULT '';

UPDATE users
 SET mobile = '' WHERE mobile IS NULL;

ALTER TABLE users 
 ALTER mobile SET NOT NULL;

COMMIT;

There's lots of small pieces of knowledge in our company. Not the kind of knowledge that requires thinking but stuff like,

• where's the black stapler
• how to add a domain to the xyz-server apache config
• who to call to sort out the airconditioner

Most of this core "knowledge" we have tried to store in a relatively structured Wiki (we use zwiki) which has been a really good start. It's good because whenever I need to refresh my memory on some IP address or how to install a printer I can go to our company wiki and search for it there.

The problem is that it's such a choir to maintain the wiki. It takes several seconds to go there, log in and (biggest bore) to find the most appropriate places to write anything new or where to update something old. I know I sound disgustingly lazy, but when you have to do it many times per day you want the software to help you rather than being an obstacle. I'm now instead looking for a different solution: a blog!

Blogs are great because they feel familiar and there are generic tools surrounding them such as neatly designed templates and RSS readers. There are more ready Wiki solutions than good Knowledge Base solutions; and there are more blog solutions than there are Wiki solutions. This means that there are better options for getting really good software and keeping that software maintained. An added benefit of using a blog for maintaining team knowledge is that there's a natural chronological order to it. That means that old blog posts are less relevant than new ones which becomes a useful benefit when you search for pieces of knowledge. I want something like this for my company. All people should post small blog items every day for all tasks they do that might happen again. It could also be used just to think loud and to let other people know what you're working on.

Can you help? I need some suggestions on good blogging tools for a closed group (Blogger.com don't offer password protected blogs). Here's what we'd need:

• ability to quickly post to it with GUI apps and good web admin for posting
• ability to secure (or even better, to host it ourselfs) access
• non-proprietary storage/ ability to export & back up content
• not Microsoft
• fast and with great sorting/filtering functions and a clever search tool

Any suggestions? I'm confident that we're ready to pay for it so it doesn't have to be GPL (for once :).

Sorry about the cryptic title. My actual salary has not gone down but if you are to believe this chart the salary (in the US) for "Web programmer/developer (back end systems)" has gone down with 2.2% in the last five years but actually gone up by 8.2% in the last year.

What is quite interesting is that of "Content developer" which has seen a rise of 6.5% in the last five years. I guess that's the blogging. More and more people get employed now just to blog about a particular industry. This seems to be a modern trend that we'll see more and more of now that setting up a blog of your own is so much easier (I wrote mine from scratch :). I wonder where these "content developers" come from; their individual technical industries such as programming or design or if they come from literary background like book writing.

Anyway, looks like the general trend is that all salaries have gone up from 2004 to 2005.

A new feature has just recently been added to MOBi Phonebook that I developed for our client (the owners of MOBi) for free in my spare time.

The reason for this is that I use MOBi myself a lot and I needed an easy way to export my contacts and later be able to import it again. So I used the Excel HTML solution and simply added a bit of formatting to it. It opens in right up in Excel (or like in my case, OpenOffice oocalc) which makes it damn easy to edit the contacts.

If you need to put this back into MOBi you have to save your spreadsheet as a comma separated CSV file. Fortunately that importing is pretty smart. It can add and update new data and everything it can't handle (conflicts) it shows you for manual editing.

This is not the first time I add things for free to clients. I do not want any compensation for it because I do it for a purpose. In the case of MOBi, I do it for me, myself and I. All I had to do was to check with the owners if they ok'ed it.