Why Ruby on Rails Migrations Don't Work

Migrations in Ruby on Rails provide some nice faetures for manipulating database structure. It is used mainly for two reasons:

• this is a nice way of keeping database structure changes
• you don’t need to know SQL
• the generated SQL code can be used for many database engines

The first point is OK (this is a must, you really should keep such a track of database changes). The second would be nice… but that simply doesn’t work. I don’t think that not knowing SQL is such a huge advantage.

Why many programmers think that it’s a virtue that they don’t know something?

Knowing SQL is really a need when you write some database software using relational databases.

The Worst Database Design

In this blog post I will describe the worst database design I have ever seen in a production database.

Let’s create tables like these:

CREATE TABLE A
(
    ID PRIMARY KEY
    ...
);

CREATE TABLE B
(
    ID PRIMARY KEY
    ...
);

The whole idea is that (due to some reasons that I don’t remember) there is a one-to-one relation between those two tables. It means that A.id = B.id.

The one-to-one relation is not quite normal and generally should be avoided. Avoided means only: “don’t do that unless you know what you’re doing”.

The first table looked like this:

PostgreSQL Collation - 8.4

In the last PostgreSQL version (8.4) there is a small change for the problem with collation. The collation can be set per database, not per cluster. This means that using only one PostgreSQL instance it is possible to have many databases with different language settings.

How To Check PostgreSQL Privileges

Someone asked me recently how to list all tables that a given user can select from. The problem they had was that they had a special user used by a client, so the client can make its own sql queries. However due to the security policy, that user can only perform SELECT queries and only from some tables.

They wanted to give the client a list of tables which can be selected.

PostgreSQL FOUND Problem

FOUND is a global variable which exists in the plpgsql procedural language used in the PostgreSQL database. Last time I was writing some complicated procedures for moving many records to archive schema, just for having only the fresh data the main schema so it would be faster. During that I noticed a very strange error which suddenly turned out just to be a stupid mistake.

The manual says that:

A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.

So I used it… that way, but not with a simple query, I used EXECUTE for some custom string created by some arguments concatenation.

Here is a very simple test sql showing the problem: