sql/upgrade/schema_12_to_13.pl

   1 sub upgrade {
   2     my $s = shift;
   3
   4     $s->prepare_execute(<<'SQL');
   5 ALTER TABLE correspondent_full_name RENAME TO correspondent_full_name_old;
   6
   7 CREATE TABLE correspondent_full_name(
   8 correspondent INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE,
   9 full_addr TEXT NOT NULL,
  10 "name" TEXT NOT NULL,
  11 last_seen TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
  12 );
  13
  14 CREATE UNIQUE INDEX ON correspondent_full_name(correspondent,full_addr);
  15 CREATE INDEX ON correspondent_full_name(full_addr);
  16
  17 INSERT INTO correspondent_full_name
  18 SELECT cfn.correspondent,CONCAT(cfn.full_name,' <',c.addr,'>') AS full_addr,
  19 cfn.full_name, cfn.last_seen
  20 FROM correspondent_full_name_old cfn
  21 JOIN correspondent c ON cfn.correspondent=c.id;
  22
  23 SQL
  24 }
  25
  26 1;