b.larik

When you want to remove a domain from PowerDNS and you’re using postgresql, you can save your self one query:

Normally you’ll do something like:

DELETE FROM rules where domain_id=666
DELETE FROM domains where id=666;

But when you create a rule in postgresql, you’ll only have to do this:

DELETE FROM domains where id=666;

Here’s how to create the rule.

CREATE OR REPLACE RULE remove_records AS 
   ON DELETE TO domains DO      
DELETE FROM records WHERE records.domain_id = old.id; 

For more information, see the PostgreSQL documentation about rules

I assembled a list of Mac OS X Applications I use regularly.

If you’re new to a mac, this list will be very useful. If you’re a long time Mac user, well there is probably something in it for you.

You can find the list on the Mac OS X Applications page.

About time! The finalists were:

• Hibernate
• Java DB
• MySQL
• Oracle Database 10g
• PostgreSQL

See PostgreSQL news or the item on developer.com for more info.

A while ago Navicat released Navicat Lite for PostgreSQL. This is a “free for non-commercial use” version of their Navicat for PostgreSQL. You might like it.

While I prefer pgAdmin III, there are some issues with standard pgAdmin on PowerPC Macs with Leopard on them. See also this thread.

The solution below only works for people who are also running PostgreSQL on their Macs.

While I was setting up a new iMac G5 with builtin isight, I ended up playing around with iChat AV and Bonjour connections.

Somehow it didn’t work on my iMac, I was getting an error message after the connection seemed to be initiated. The error message was:

“Can’t get video from the camera”.

I forgot I had this problem before, and more imporantly how I solved it.

Thanks to a link on http://www.ralphjohnsuk.dsl.pipex.com/page16.html I found out that the problem was due to a shared memory problem (or shared port problem) between iChat and PostgreSQL.

There are two ways of solving this problem, choose one wich applies to your situation:

1. Decrease shared memory on postgresql
2. Recompile PostgreSQL for use with a different port

Recently, I had the fine oppertunity to upgrade all my CMS powered PostgreSQL databases and perl powered websites from SQL_ASCII to Unicode.

If you don’t know what unicode is, have a look at this article: The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

It was less painfull then I thought, in fact there are 6 steps

1. Dump your database
2. Remove invalid characters from the dump
3. Make a diff from the dump
4. Remove current database
5. Create a new databse
6. Load the data into the database you’ve created in step 2

Below is a simple bash script to manage this stuff. Edit it to your needs.

#!/bin/bash

db=$1

# This script updates all dbs to use unicode

dbhost='localhost'
username='larik'
odir=${HOME}/CMS_UPGRADE

if [ "${db}X" == "X" ]
then
   echo "I need a db for host ${dbhost} and username ${username} $db"
   exit
fi

if [ ! -d $odir ]
then
   mkdir $odir || exit "Exit at mkdir"
fi

dump_sql=${odir}/${db}_out.sql
conv_sql=${odir}/${db}_conv.sql
result_sql=${odir}/${db}_result.txt
sql_diff=${odir}/${db}.diff

extra_string="database: ${db}, host: ${dbhost}, user: ${username}"

# Wat dient er te gebeuren:

# 1. Dump database

pg_dump --host=$dbhost --username=$username -D --file=$dump_sql --format=p $db || exit "exit at pg_dump ${extra_string}"

# 2. Remove invalid characters from the dump

/sw/bin/iconv -c -f UTF-8 -t UTF-8 ${dump_sql} > ${conv_sql}  || exit "exit at iconv ${extra_string}"

# 3 Make a diff from the dump

diff $dump_sql $conv_sql > $sql_diff

# 4. Remove current database

dropdb --host=$dbhost --username=$username $db || exit "exit at dropdb ${extra_string}"

# 5. Create a new databse

createdb --encoding='unicode' --host=$dbhost --username=$username $db || exit "exit at createdb ${extra_string}"

# 6. Load the data into the database you've created in step 2
psql -f $conv_sql -o $result_sql -h $dbhost -U $username $db || exit "exit at psql ${extra_string}"

Notes about this script:

• Make sure you have iconv installed. It is standard on Linux and Mac OS X
• Make sure that the pg_dump you run is the same version as the database you’re dumping
• Check your diff files after the script has run, invalid UTF-8 characters will be removed from and won’t be available in the new sql script

Have fun!