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 COLUMN mask int(11) unsigned NOT NULL DEFAULT 4294967295,
  41 ADD PRIMARY KEY ip (ip),
  42 ADD INDEX network (ip, mask);
  43
  44 # vim:set syntax=mysql: