zPmMM9wV

1# Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TRIGGER friendship_prevention_tr BEFORE INSERT ON friendsh'
2
3
4class CreateFriendships < ActiveRecord::Migration
5  def self.up
6    execute <<-EOF
7      create table friendships (
8        person_id         integer not null references user(id),
9        friend_id         integer not null references user(id),
10        approved          boolean not null default false,
11        check (person_id <> friend_id),
12        primary key(person_id, friend_id)
13      );
14      
15      CREATE TRIGGER friendship_prevention_tr BEFORE INSERT ON friendships
16      for each row
17      BEGIN
18      if select count(*) from friendships where (person_id = new.friend_id and friend_id = new.person_id) or (person_id = new.person_id and friend_id = new.friend_id) > 0 then
19      call ERROR_INSERT_NOT_ALLOWED();
20      // Friendship record already exists with person_id and friend_id
21      else
22      // Do nothing - record is good
23      end if;
24      end;
25    EOF
26  end
27
28  def self.down
29    drop_table :friendships
30  end
31end