Test 123

Тут заметки о переезде.

Дополнительные таблички

dhcphosts_hosts_new

Нужна копия. Потом сделаем вьюху старая --> новая. Пока так.

CREATE TABLE `dhcphosts_hosts_new` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `ip` int(10) unsigned NOT NULL DEFAULT '0',
  `hostname` varchar(40) NOT NULL DEFAULT '',
  `network` smallint(5) unsigned NOT NULL DEFAULT '0',
  `mac` varchar(17) NOT NULL DEFAULT '00:00:00:00:00:00',
  `disable` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `forced` int(1) NOT NULL DEFAULT '0',
  `blocktime` int(3) unsigned NOT NULL DEFAULT '3',
  `expire` date NOT NULL DEFAULT '0000-00-00',
  `seen` int(1) NOT NULL DEFAULT '0',
  `comments` varchar(250) NOT NULL DEFAULT '',
  `vid` smallint(6) unsigned NOT NULL DEFAULT '0',
  `nas` bigint(20) DEFAULT NULL,
  `option_82` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ports` varchar(100) NOT NULL DEFAULT '',
  `boot_file` varchar(150) NOT NULL DEFAULT '',
  `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cid` (`ip`),
  UNIQUE KEY `host_network` (`hostname`,`network`),
  UNIQUE KEY `ip` (`ip`,`mac`)
) ENGINE=MyISAM AUTO_INCREMENT=3157 DEFAULT CHARSET=latin1 COMMENT='Dhcphosts hosts';

dhcphosts_networks_vlans

Сопоставление пары сеть-влан

REATE TABLE `dhcphosts_networks_vlans` (
  `id` int(11) DEFAULT NULL,
  `vlan` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

view_active_users

DROP VIEW view_active_users;
CREATE VIEW view_active_users AS 
SELECT 
users.uid AS uid,
users.id  AS user,
inet_ntoa(dhcphosts_hosts_new.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan 
FROM 
( dhcphosts_networks_vlans join dhcphosts_hosts_new on ((dhcphosts_networks_vlans.vlan = dhcphosts_hosts_new.vid)) 
join 
dhcphosts_networks on (dhcphosts_networks.id = dhcphosts_networks_vlans.id ))    
join 
users on((users.uid = dhcphosts_hosts_new.uid))  
join
bills on ((users.bill_id = bills.id)) 
WHERE ( bills.deposit > 0 );