Test 123

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску

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

Дополнительные функции

mac_ntoa

DROP FUNCTION mac_ntoa;
delimiter //
CREATE FUNCTION mac_ntoa(mac bigint UNSIGNED) RETURNS char(32)
BEGIN
DECLARE res char(32);
   SELECT 
   concat 
   (
      lower(hex( ( mac  >> 40  )& 0x0000000000ff ) ),
      ":",
      lower(hex( ( mac  >> 32  )& 0x0000000000ff ) ), 
      ":",
      lower(hex( ( mac  >> 24  )& 0x0000000000ff ) ),
      ":",
      lower(hex( ( mac  >> 16  )& 0x0000000000ff ) ), 
      ":",
      lower(hex( ( mac  >> 8  )& 0x0000000000ff ) ), 
      ":",
      lower(hex( ( mac  >> 0  )& 0x0000000000ff ) )
   )
INTO res;
RETURN res;
END
//
delimiter ;

GetBroadcast

DROP FUNCTION GetBroadcast;
delimiter //
CREATE FUNCTION GetBroadcast(n1 int UNSIGNED, m1 INT UNSIGNED) RETURNS INT UNSIGNED
BEGIN
    DECLARE b1 INT UNSIGNED;
    SELECT  n1+POWER(2,32-BIT_COUNT(m1))-1 INTO b1;
    RETURN b1;
END
//
delimiter ;

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

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 );


view_dhcp_config_authorized_classes_mac

DROP VIEW view_dhcp_config_authorized_classes_mac;
CREATE VIEW view_dhcp_config_authorized_classes_mac AS 
SELECT
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts_new.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts_new.mac AS mac,
dhcphosts_hosts_new.ports AS port,
mac_ntoa(dhcphosts_hosts_new.nas) AS switch,
concat('match_vlan__',dhcphosts_networks_vlans.vlan,'__port__',dhcphosts_hosts_new.ports,'__MAC__',dhcphosts_hosts_new.mac,'__switch__',mac_ntoa(dhcphosts_hosts_new.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts_new.ports,
'__MAC__',
dhcphosts_hosts_new.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts_new.nas),
'"  {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts_new.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts_new.mac,
'"        and        binary-to-ascii(16,  8, ":", substring(option agent.remote-id, 2, 15)) = "'
,mac_ntoa(dhcphosts_hosts_new.nas),
'"    );}'
) AS class 
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 );

view_dhcp_config_authorized_pools_mac

DROP VIEW view_dhcp_config_authorized_pools_mac;
CREATE  VIEW view_dhcp_config_authorized_pools_mac
AS
select 
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts_new.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts_new.mac AS mac,
dhcphosts_hosts_new.ports AS port,
mac_ntoa(dhcphosts_hosts_new.nas) AS switch,
concat(
'pool { range ',
inet_ntoa(dhcphosts_hosts_new.ip),
'; allow members of "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts_new.ports,
'__MAC__',
dhcphosts_hosts_new.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts_new.nas),
'"; default-lease-time 300; max-lease-time 300; }') AS pool
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 );


view_dhcp_config_authorized_nopay_classes_mac

DROP VIEW view_dhcp_config_authorized_nopay_classes_mac;
CREATE VIEW view_dhcp_config_authorized_nopay_classes_mac
AS
SELECT
dhcphosts_networks.id AS network,
inet_ntoa(dhcphosts_hosts_new.ip) AS ip,
dhcphosts_networks_vlans.vlan AS vlan,
dhcphosts_hosts_new.mac AS mac,
dhcphosts_hosts_new.ports AS port,
mac_ntoa(dhcphosts_hosts_new.nas) AS switch,
concat(
'match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts_new.ports,
'__MAC__',
dhcphosts_hosts_new.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts_new.nas)) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'__port__',
dhcphosts_hosts_new.ports,
'__MAC__',dhcphosts_hosts_new.mac,
'__switch__',
mac_ntoa(dhcphosts_hosts_new.nas),
'" {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"        and        binary-to-ascii(10, 8, "",  substring(option agent.circuit-id, 5, 1)) = "',
dhcphosts_hosts_new.ports,
'"        and        binary-to-ascii(16,  8, ":", substring(hardware,                1, 6)) = "',
dhcphosts_hosts_new.mac,
'"        and        binary-to-ascii(16,  8, ":",substring(option agent.remote-id, 2, 15)) = "',
mac_ntoa(`dhcphosts_hosts_new`.`nas`),
_utf8'"    );}') 
AS class 
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 );

view_dhcp_config_generic_classes

CREATE  VIEW  view_dhcp_config_generic_classes 
AS 
select 
dhcphosts_networks_vlans.vlan AS vlan,
concat('match_vlan__',dhcphosts_networks_vlans.vlan) AS classname,
concat(
'class "match_vlan__',
dhcphosts_networks_vlans.vlan,
'"  {    match if (        binary-to-ascii(10, 16, "",  substring(option agent.circuit-id, 2, 2)) = "',
dhcphosts_networks_vlans.vlan,
'"    ); } '
) AS class
from dhcphosts_networks_vlans;
Источник — https://noname.com.ua/mediawiki/index.php?title=Test_123&oldid=5417