upgrade/0.9.16/04_ip.sql

   1 USE logger;
   2
   3 ALTER TABLE sessions
   4 ADD COLUMN ip2 INT(11) UNSIGNED NOT NULL,
   5 ADD COLUMN forward_ip2 INT(11) UNSIGNED DEFAULT NULL;
   6
   7 UPDATE sessions
   8    SET ip2 = SUBSTRING_INDEX(ip, ".", 1) * 256 * 256 * 256
   9            + SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ".", 2), ".", -1) * 256 * 256
  10            + SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ".", 3), ".", -1) * 256
  11            + SUBSTRING_INDEX(ip, ".", -1),
  12        forward_ip2 = SUBSTRING_INDEX(forward_ip, ".", 1) * 256 * 256 * 256
  13                    + SUBSTRING_INDEX(SUBSTRING_INDEX(forward_ip, ".", 2), ".", -1) * 256 * 256
  14                    + SUBSTRING_INDEX(SUBSTRING_INDEX(forward_ip, ".", 3), ".", -1) * 256
  15                    + SUBSTRING_INDEX(forward_ip, ".", -1);
  16
  17 ALTER TABLE sessions
  18 DROP COLUMN ip,
  19 DROP COLUMN forward_ip,
  20 CHANGE COLUMN ip2 ip INT(11) UNSIGNED NOT NULL,
  21 CHANGE COLUMN forward_ip2 forward_ip INT(11) UNSIGNED DEFAULT NULL,
  22 ADD INDEX ip (ip),
  23 ADD INDEX forward_ip (forward_ip);
  24
  25 USE x4dat;
  26
  27 ALTER TABLE ip_watch
  28 ADD COLUMN ip2 INT(11) UNSIGNED NOT NULL;
  29
  30 UPDATE ip_watch
  31    SET ip2 = SUBSTRING_INDEX(ip, ".", 1) * 256 * 256 * 256
  32            + SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ".", 2), ".", -1) * 256 * 256
  33            + SUBSTRING_INDEX(SUBSTRING_INDEX(ip, ".", 3), ".", -1) * 256
  34            + SUBSTRING_INDEX(ip, ".", -1);
  35
  36 ALTER TABLE ip_watch
  37 DROP PRIMARY KEY,
  38 DROP COLUMN ip,
  39 CHANGE COLUMN ip2 ip INT(11) UNSIGNED NOT NULL,
  40 ADD PRIMARY KEY ip (ip);
  41
  42 # vim:set syntax=mysql: