Simples Idéias.

É incrível mas tudo o que a Microsoft faz é um lixo. Não consigo lembrar de nada que me faça dizer "Nossa, eles detonaram com ____ (seu aplicativo aqui)".

Estou fazendo um job que, infelizmente, deve ser feito em SQL Server. Como não conheço muito sobre ele, estou fazendo as coisas direto em SQL, sem usar procedures, view ou outra feature mais complicada/avançada.

Em um certo momento, precisei fazer um LEFT JOIN com COUNT. O problema é que o SQL Server é tão burro que preciso definir cada campo que não faz parte da agregação na cláusula GROUP BY. Mas adivinha… Isso modifica o resultado!

Para ilustrar, veja um exemplo de "tópicos" e "respostas".

CREATE TABLE topics (
	id INT(11) AUTO_INCREMENT NOT NULL,
	title VARCHAR(255)
);
 
CREATE TABLE replies (
	id INT(11) AUTO_INCREMENT NOT NULL,
	topic_id INT(11)
	title VARCHAR(255)
);
 
 
INSERT INTO topics (title) VALUES ('Topic #1');
INSERT INTO topics (title) VALUES ('Topic #2');
INSERT INTO replies (title, topic_id) VALUES ('Reply topic #2', 2);

Se eu fizesse a seguinte seleção, deveria me retornar o tópico com a quantidade de respostas:

SELECT
    topics.title AS title,
    COUNT(replies.id) AS replies
FROM 
    topics
LEFT JOIN replies ON
    topics.id = replies.topic_id
GROUP BY
    replies.id;

title: Topic #1, replies: 0
title: Topic #2, replies: 1

Isso funciona no MySQL e provavelmente deve funcionar no PostgreSQL. Mas no MS-SQL Server recebo a seguinte mensagem:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'topics.title' is invalid in the select list because it is not 
contained in either an aggregate function or the GROUP BY clause.

E tem gente que ainda paga caro por isso!

Se você hospeda seu projeto em um servidor que não está alocado no Brasil, pode ter problemas com o fuso horário. Uma solução é fazer esta definição manualmente, tanto para o Rails, quanto para o MySQL.

No arquivo "environment.rb", adicione a seguinte linha:

ENV['TZ'] = 'America/Sao_Paulo'

No arquivo "application.rb", presente no diretório "app/controllers", adicione:

before_filter :set_timezone
 
def set_timezone
    ActiveRecord::Base.connection.execute "SET GLOBAL time_zone='-3:00'"
end

Uma outra solução possível, caso você seja o dono do servidor, é definir isso no arquivo "my.cfn". O grande problema é que isso não pode ser feito por usuários em planos compartilhados (isso é o que me foi dito pelo suporte da RailsPlayground). Veja mais informações sobre isto nos links abaixo.

• http://dev.mysql.com/doc/refman/5.0/en/environment-variables.html
• http://dev.mysql.com/doc/refman/5.0/en/option-files.html

No Timesheet, tenho 3 tabelas (jobs, tasks, tracking). Quando excluo um trabalho, devo remover todas as suas tarefas e trackings. Isso é muito chato de fazer porque preciso propagar a exclusão manualmente. Até onde eu sei, o SQLite não tem suporte às chaves estrangeiras, apesar de aceitar sua definição na cláusula SQL.

Mas graças ao excelente suporte à triggers, consigo fazer essa propagação toda através do banco de dados.

Primeiro, vamos criar 2 tabelas.

CREATE TABLE groups (
    id INTEGER PRIMARY KEY, 
    name VARCHAR
);
 
CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    group_id INTEGER NOT NULL
        CONSTRAINT group_id REFERENCES groups (id) ON DELETE CASCADE
);

Agora, iremos criar as nossas triggers que irão forçar a integridade referencial. Precisamos definir uma trigger para INSERT, outra para UPDATE e uma terceira para DELETE.

CREATE TRIGGER insert_groups_items
BEFORE INSERT ON items
    FOR EACH ROW BEGIN
        SELECT CASE
            WHEN ((SELECT id FROM groups WHERE id = NEW.group_id) IS NULL)
            THEN RAISE (ABORT, 'INSERT on table "items" violates foreign key')
        END;
    END;
 
CREATE TRIGGER update_groups_items
BEFORE UPDATE ON items
    FOR EACH ROW BEGIN 
        SELECT CASE
            WHEN ((SELECT id FROM groups WHERE id = NEW.group_id) IS NULL)
            THEN RAISE(ABORT, 'UPDATE on table "items" violates foreign key')
        END;
    END;
 
CREATE TRIGGER delete_groups_items
BEFORE DELETE ON groups
    FOR EACH ROW BEGIN
        DELETE FROM items WHERE group_id = OLD.id;
    END;

As triggers "insert_groups_items" e "update_groups_items" verificam se o grupo realmente existe. Já a trigger "delete_groups_items" remove todos os registros pertencentes ao grupo que estamos tentando remover. Para vermos como isso funciona, vamos adicionar alguns registros.

INSERT INTO groups (name) VALUES ('Fruits');
INSERT INTO groups (name) VALUES ('Cars');
INSERT INTO items (name, group_id) VALUES ('Apple', 1);
INSERT INTO items (name, group_id) VALUES ('Orange', 1);
INSERT INTO items (name, group_id) VALUES ('Pineapple', 1);
INSERT INTO items (name, group_id) VALUES ('Ferrari', 2);
INSERT INTO items (name, group_id) VALUES ('Porsche', 2);

Listando os grupos e itens:

SELECT
    groups.name "group",
    items.name "item"
FROM
    groups, items
WHERE
    groups.id = items.group_id;

Isso irá exibir os registros que inserimos. Agora, vamos remover o grupo "Cars" e fazer a consulta novamente.

DELETE FROM groups WHERE id = 2;
 
SELECT
    groups.name "group",
    items.name "item"
FROM
    groups, items
WHERE
    groups.id = items.group_id;

Muito bom, né? Visto aqui.