upgrade/1.0.2/02_modifications.sql

   1 DROP TABLE IF EXISTS tmp_profile_modifications;
   2 CREATE TEMPORARY TABLE tmp_profile_modifications LIKE profile_modifications;
   3 INSERT INTO tmp_profile_modifications SELECT * FROM profile_modifications;
   4 DROP TABLE profile_modifications;
   5 CREATE TABLE profile_modifications (
   6   pid INT(11) UNSIGNED NOT NULL DEFAULT 0,
   7   uid INT(11) UNSIGNED NOT NULL DEFAULT 0,
   8   field VARCHAR(60) NOT NULL,
   9   oldText TEXT NOT NULL,
  10   newText TEXT NOT NULL,
  11   type ENUM('self', 'third_party') NOT NULL DEFAULT 'self',
  12   timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  13   PRIMARY KEY  (pid, field),
  14   KEY uid (uid),
  15   FOREIGN KEY (uid) REFERENCES accounts (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  16   FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE
  17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  18 INSERT INTO  profile_modifications (pid, uid, field, oldText, newText, type)
  19      SELECT  pid, uid, field, oldText, newText, 'third_party'
  20        FROM  tmp_profile_modifications;
  21 DROP TABLE IF EXISTS tmp_profile_modifications;
  22
  23 -- vim:set syntax=mysql: