Test 123: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
(Новая: =Тут заметки о переезде.= ==Дополнительные таблички== ===dhcphosts_hosts_new=== <PRE> CREATE TABLE `dhcphosts_hosts_new` ( `id` int(11) unsign...)
 
 
(не показано 29 промежуточных версий этого же участника)
Строка 1: Строка 1:
  +
[[Категория:Notes]]
  +
[[Категория:Abills]]
 
=Тут заметки о переезде.=
  
=Тут заметки о переезде.=
−
==Дополнительные таблички==
 +
==Дополнительные функции==
  +
===mac_ntoa===
  +
<PRE>
  +
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 ;
  +
</PRE>
   
  +
==GetBroadcast==
  +
<PRE>
  +
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 ;
  +
</PRE>
  +
  +
==Дополнительные таблички==
 
===dhcphosts_hosts_new===
  
===dhcphosts_hosts_new===
  +
Нужна копия. Потом сделаем вьюху старая --> новая.
  +
Пока так.
 
<PRE>
  
<PRE>
 
CREATE TABLE `dhcphosts_hosts_new` (
  
CREATE TABLE `dhcphosts_hosts_new` (
Строка 27: Строка 74:
 
UNIQUE KEY `host_network` (`hostname`,`network`),
  
UNIQUE KEY `host_network` (`hostname`,`network`),
 
UNIQUE KEY `ip` (`ip`,`mac`)
  
UNIQUE KEY `ip` (`ip`,`mac`)
−
) ENGINE=MyISAM AUTO_INCREMENT=3157 DEFAULT CHARSET=latin1 COMMENT='Dhcphosts hosts'
 +
) ENGINE=MyISAM AUTO_INCREMENT=3157 DEFAULT CHARSET=latin1 COMMENT='Dhcphosts hosts';
  +
</PRE>
  +
  +
  +
===dhcphosts_networks_vlans===
  +
Сопоставление пары сеть-влан
  +
<PRE>
  +
REATE TABLE `dhcphosts_networks_vlans` (
  +
`id` int(11) DEFAULT NULL,
  +
`vlan` int(11) DEFAULT NULL
  +
) ENGINE=MyISAM DEFAULT CHARSET=utf8
  +
</PRE>
  +
  +
===view_active_users===
  +
<PRE>
  +
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 );
  +
</PRE>
  +
  +
  +
===view_dhcp_config_authorized_classes_mac===
  +
<PRE>
  +
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 );
  +
</PRE>
  +
  +
===view_dhcp_config_authorized_pools_mac===
  +
<PRE>
  +
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 36000; max-lease-time 72000; }') 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 );
  +
</PRE>
  +
  +
===view_dhcp_config_authorized_nopay_classes_mac===
  +
<PRE>
  +
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 );
  +
</PRE>
  +
  +
===view_dhcp_config_generic_classes===
  +
<PRE>
  +
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;
  +
</PRE>
  +
  +
===nmslog===
  +
<PRE>
  +
CREATE TABLE `nmslog` (
  +
`seq` int(10) unsigned NOT NULL auto_increment,
  +
`local_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  +
`client_ip` varchar(32) default NULL,
  +
`uid` int(11) default NULL,
  +
`VLAN` int(11) default NULL,
  +
`MODULE` int(11) default NULL,
  +
`PORT` int(11) default NULL,
  +
`MAC` varchar(32) default NULL,
  +
`SWITCH` varchar(32) default NULL,
  +
`username` varchar(32) default NULL,
  +
`client_net` int(11) default NULL,
  +
`msg` text,
  +
`log_reason` varchar(255) default 'info',
  +
PRIMARY KEY (`seq`)
  +
) ENGINE=MyISAM;
  +
</PRE>

Текущая версия на 15:55, 28 сентября 2012

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

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

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 36000; max-lease-time 72000; }') 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;

nmslog

CREATE TABLE `nmslog` (
  `seq` int(10) unsigned NOT NULL auto_increment,
  `local_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `client_ip` varchar(32) default NULL,
  `uid` int(11) default NULL,
  `VLAN` int(11) default NULL,
  `MODULE` int(11) default NULL,
  `PORT` int(11) default NULL,
  `MAC` varchar(32) default NULL,
  `SWITCH` varchar(32) default NULL,
  `username` varchar(32) default NULL,
  `client_net` int(11) default NULL,
  `msg` text,
  `log_reason` varchar(255) default 'info',
  PRIMARY KEY  (`seq`)
) ENGINE=MyISAM;
Источник — https://noname.com.ua/mediawiki/index.php?title=Test_123&oldid=5763