How to remove entries from Firefox Smartbar

I really got to love the Smartbar aka Awesomebar, introduced in Firefox 3. But in some way it annoyed me that there is no really obvious way to remove entries from it. Sure, after a bit of research via Google I found out, that you can press shift+delete while hovering with your mouse over an entry. But this doesn't help much if you want to get rid of all entries for a given domain or containing a specific keyword - so this may get kind of tedious. It also seems, that there isn't even a single Add-on which can deal with this. If I missed something, please drop me a quick note.

Nevertheless, being a part-time hacker I also couldn't overlook, that Firefox is saving lots of the data it stores on your workstation in SQLite databases. And where is SQL, there is rescue!

So, first step - where are those database files? On Mac OS X, they are usually located in

~/Library/Application\ Support/Firefox/Profiles/<some string>.default/

The one you want to hassle with is named


Note, from now on you have to work while Firefox isn't running. Otherwise, the SQLite database is locked and you can't access it. But let's get to business - we're interested in the following table:

  1. fh$ sqlite3 places.sqlite
  2. SQLite version 3.4.0
  3. Enter ".help" for instructions
  4. sqlite> .schema moz_places
  6. title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, \
  8. favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, \
  9.  last_visit_date INTEGER);
  10. CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id);
  11. CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);
  12. CREATE INDEX moz_places_hostindex ON moz_places (rev_host);
  13. CREATE INDEX moz_places_lastvisitdateindex ON moz_places (last_visit_date);
  14. CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url);
  15. CREATE INDEX moz_places_visitcount ON moz_places (visit_count);
  16. sqlite>

The url column holds the complete URL Firefox shows in the Smartbar, so we can simply write a quick query:

  1. sqlite> SELECT url FROM moz_places WHERE url LIKE '';

If I wanted to get rid of them this query would do:

  1. DELETE FROM moz_places WHERE url LIKE '';

Replace the domain accordingly, backup your places.sqlite before changing anything and you're good.

The location of places.sqlite on other OS:

Windows XP

C:\Documents and Settings\<username>\Application Data\Mozilla\Firefox\
  Profiles\<profile folder>\places.sqlite

Windows Vista

  <profile folder>\places.sqlite


/home/<user>/.mozilla/firefox/<profile folder>/places.sqlite

If you're on an OS which doesn't ship with any SQLite interface, go to the SQLite website and download it. Or, if that is to much hassle for you, there is a Firefox Add-on, called SQLite Manager, which can access and change SQLite databases too. But remember: you have to work on a copy of places.sqlite as the file is locked while Firefox itself is running.

A better CONCAT for PostgreSQL

Concatination is one thing you need quite often when working with databases and especially when you have to generate some reports. And even as it seems to be a simple problem looking for an easy solution it's a pithole. The natural way would be to do something like this:

  1. CREATE TABLE persons (
  2. id        SERIAL      PRIMARY KEY,
  3. firstname TEXT        NOT NULL,
  4. surname   TEXT        NOT NULL,
  5. title     TEXT
  6. );
  8. INSERT INTO persons (firstname, surname, title)
  9. VALUES ('Edward', 'Hyde', NULL);
  10. INSERT INTO persons (firstname, surname, title)
  11. VALUES ('Henry', 'Jekyll', 'Dr.');
  13. SELECT * FROM persons;
  15. id | firstname | surname | title
  16. ----+-----------+---------+-------
  17. 1 | Edward    | Hyde    |
  18. 2 | Henry     | Jekyll  | Dr.
  19. (2 rows)
  21. SELECT title || ' ' || firstname || ' ' || surname FROM persons;
  23. ?COLUMN?
  24. ------------------
  26. Dr. Henry Jekyll
  27. (2 rows)

As you see, the row containing the NULL value is omitted. So how to fix that? Of course we might do something like using COALESCE to return at least an empty string instead of a NULL value, but this way we'd get a whitespace we might not want (on the web not that bad because normaly the browser renders multiple whitespace as one).

So, if you research this issue a bit via google you'll find many solutions. From PL/pgSQL CONCAT functions with might handle two, three or four arguments (CONCAT2, CONCAT3, you get the picture) to solutions using custom aggregate functions. But most of them don't care about NULL values and will break.

Let's try this with a simple CONCAT function:

  1. CREATE OR REPLACE FUNCTION concat(text, text, text) RETURNS text AS $$
  2. SELECT $1 || ' ' || $2 || ' ' || $3;
  3. $$ LANGUAGE 'sql';
  5. SELECT CONCAT(title, firstname, surname) FROM persons;
  6. concat
  7. ----------------
  9. Dr. Henry Jekyll
  10. (2 rows)

Still, the same problem with the row containing the NULL value.

While looking into this, I ran over the buildin function ARRAY_TO_STRING which leads the path to a very straight forward solution:

  1. SELECT ARRAY_TO_STRING(ARRAY[title, firstname, surname], ' ') FROM persons;
  3. array_to_string
  4. ------------------
  5. Edward Hyde
  6. Dr. Henry Jekyll
  7. (2 rows)

Problem solved!

Filtermasken für Radfahrer

Interessanter Artikel zum Thema Filtermasken für Radfahrer. Besonders "interessant" sind die Fotos nach Verwendung in unterschiedlichen Städten. Ein Test in Wien würde mich auch schwer interessieren.

Note to self about class methods

Don't use them unless you are knowing what you are doing. I recently ran into something strange and was quite busy fixing the problem using much database mojo.

For the calculation of some MD5 sums I was using Digest::MD5. And for some reason I used it this way:

  1. perl -MDigest::MD5 -le 'print Digest::MD5->md5_hex("foo")'
  2. 3200a4cda22a4a935412da8113b4139b

Looks good doesn't it? But the MD5 digest is incorrect. Just try it yourself and enter 'foo' in any given web MD5 generator out there.

The right way to use Digest::MD5 (and Digest::SHA1 too) would be

  1. perl -M'Digest::MD5 "md5_hex"' -le 'print md5_hex("foo")'
  2. acbd18db4cc2f85cedef654fccc4a4d8

Okay, my fault. But this annoys me a bit:

  1. perl -w -MDigest::MD5 -le 'print Digest::MD5->md5_hex("foo")'
  2. &Digest::MD5::md5_hex function probably called as class method at -e line 1.
  3. 3200a4cda22a4a935412da8113b4139b
  4. fh$ perl -Mstrict -Mwarnings -MDigest::MD5 -le \
  5. 'print Digest::MD5->md5_hex("foo")'
  6. 3200a4cda22a4a935412da8113b4139b

To be honest I have no idea, why -w produces a warning (which is a good thing) and use warning doesn't.


In the process of fixing our code for an upcoming upgrade of one database version for one of our $-projects I encountered a strange behaviour. Initiual situation:

  • we're moving from PostgreSQL 8.1.3 to the 8.3.5
  • we're moving from database encoding LATIN1 to UTF8
  • in our code we're using the TO_ASCII function a few times.

And this combination produces some headaches.

But first a gentle introduction to the TO_ASCII function. It converts any given text into it's ASCII representation. Folks bound to languages with german umlauts or some kind of apostrophes encounter many problems. For example: what should you do if you have to build some kind of index based on the first character of the lastname. Certainly you don't want to have an extra entry with 'Ü', instead you want to but them into the 'U' list. Grand entrance TO_ASCII:

  1. SELECT to_ascii('Übermeier');
  2. Ubermeie

Works like a charm. Caveat: TO_ASCII only supports LATIN1, LATIN2, LATIN9 and WIN1250 encodings but no UTF8.

Okay, the first guess would be to do something like this:

  1. SELECT to_ascii(convert_to('Übermeier', 'latin1'));
  2. ERROR:  FUNCTION to_ascii(bytea) does NOT exist

Bummer. CONVERT_TO returnes BYTEA, TO_ASCII only wants TEXT.

There has been some discussion going on on the pgsql.hackers mailinglist and frankly I can follow both parties in their point of view. But thanks to Pavel Stehule we have some kind of a hack to sidestep this issue:

  1. CREATE FUNCTION to_ascii(bytea, name)
  2. RETURNS text STRICT AS 'to_ascii_encname' LANGUAGE internal;

This version gladly accepts the BYTEA data returned by CONVERT_TO so we can just use it in this way:

  1. SELECT to_ascii(convert_to('Übermeier', 'latin1'), 'latin1');
  2. Ubermeie

Problem solved.

Edit: Added fix by eMerzh. Thanks!