Биллинговая система Nodeny

radtest 00:11:22:33:44:55 '' 127.0.0.1 0 hardpass5
Sent Access-Request Id 207 from 0.0.0.0:20280 to 127.0.0.1:1812 length 69
	User-Name = "00:11:22:33:44:55"
	User-Password = ""
	NAS-IP-Address = 127.0.0.1
	NAS-Port = 0
	Message-Authenticator = 0x00
	Cleartext-Password = ""
Received Access-Accept Id 207 from 127.0.0.1:1812 to 127.0.0.1:20280 length 32
	Framed-IP-Address = 10.2.1.9
	Session-Timeout = 600

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN svlan_cvlan VARCHAR(64))
BEGIN
    DECLARE svlan VARCHAR(12);
    DECLARE cvlan VARCHAR(12);
    SELECT strSplit(svlan_cvlan, '_', 1) INTO svlan;
    SELECT strSplit(svlan_cvlan, '_', 2) INTO cvlan;
    SELECT uid,svlan_cvlan AS name,'Cleartext-Password' AS Attribute,svlan_cvlan AS Value,':='
    FROM data0 WHERE _ipoe_svlan=svlan and _ipoe_cvlan=cvlan;
END$$
DELIMITER ;


call accel_radcheck('1000_10');

SET GLOBAL log_bin_trust_function_creators = 1;

DROP FUNCTION strSplit;
CREATE FUNCTION strSplit(x MEDIUMTEXT, delim MEDIUMTEXT, pos int) RETURNS MEDIUMTEXT
RETURN 
    TRIM(BOTH '\r' FROM TRIM(
    REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1), delim, '')
    ));

radtest '1000_11' '' 127.0.0.1 0 hardpass5
Sent Access-Request Id 171 from 0.0.0.0:38952 to 127.0.0.1:1812 length 59
	User-Name = "1000_11"
	User-Password = ""
	NAS-IP-Address = 127.0.0.1
	NAS-Port = 0
	Message-Authenticator = 0x00
	Cleartext-Password = ""
Received Access-Accept Id 171 from 127.0.0.1:1812 to 127.0.0.1:38952 length 20

	User-Name = "1000_10"
	User-Password = ""
	NAS-IP-Address = 127.0.0.1
	NAS-Port = 0
	Message-Authenticator = 0x00
	Cleartext-Password = ""
Received Access-Accept Id 69 from 127.0.0.1:1812 to 127.0.0.1:16741 length 38
	Framed-IP-Address = 10.0.0.4
	Framed-IP-Netmask = 255.255.255.255
	Framed-Protocol = PPP

DROP DATABASE nodeny;
create database nodeny;
use nodeny;
source /usr/local/nodeny/bill.sql;
source /home/nodeny.sql;

ALTER TABLE `admin`   CHANGE COLUMN `name` `name` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci' AFTER `passwd`;
ALTER TABLE `admin`   CHANGE COLUMN `post` `post` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci' AFTER `passwd`;
ALTER TABLE `admin`   CHANGE COLUMN `privil` `privil` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci' AFTER `passwd`;
ALTER TABLE `admin`   CHANGE COLUMN `usr_grps` `usr_grps` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci' AFTER `passwd`;
ALTER TABLE `admin`   CHANGE COLUMN `tunes` `tunes` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci' AFTER `passwd`;
ALTER TABLE `admin`   CHANGE COLUMN `ext` `ext` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci' AFTER `passwd`;
ALTER TABLE `admin`   CHANGE COLUMN `balance` `balance` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci' AFTER `passwd`;
ALTER DATABASE nodeny CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE services ADD `plain_description` varchar(512) NOT NULL DEFAULT '';
ALTER TABLE admin MODIFY COLUMN passwd varbinary(64) NOT NULL;
ALTER TABLE users MODIFY COLUMN comment varchar(8192) NOT NULL DEFAULT '';
ALTER TABLE `users_trf` ENGINE=InnoDB;
ALTER TABLE `data1` ENGINE=InnoDB;
ALTER TABLE `websessions` ENGINE=InnoDB;
ALTER TABLE `config` ENGINE=InnoDB;
ALTER TABLE `datasetup` ENGINE=InnoDB;
ALTER TABLE `dictionary` ENGINE=InnoDB;
ALTER TABLE `nets` ENGINE=InnoDB;
ALTER TABLE `ses_traf` ENGINE=InnoDB;
ALTER TABLE `user_grp` ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT Null, login, 'Cleartext-Password' AS Attribute, '' AS Value,':=';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;

  SELECT uid INTO usr_id FROM data0 WHERE _name_radius=login LIMIT 1;
  SELECT get_ip(usr_id) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
  SELECT NULL,login,'Acct-Interim-Interval','120','=';

END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16), IN properties VARCHAR(255))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  SELECT uid INTO usr_id FROM data0 WHERE _name_radius=login LIMIT 1;
  SELECT get_ip(usr_id) INTO usr_ip;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
  UPDATE data0 SET _name_radius=login WHERE _name_radius=login LIMIT 1;
END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$
CREATE PROCEDURE `radstop`(IN login VARCHAR(64))
BEGIN
 DECLARE usr_id INT;
  SELECT uid INTO usr_id FROM data0 WHERE _name_radius=login LIMIT 1;
  DELETE FROM auth_now WHERE ip = get_ip(usr_id) LIMIT 1;
END$$
DELIMITER ;

DROP FUNCTION IF EXISTS `normalize_ippool`;
DELIMITER $$
CREATE FUNCTION `normalize_ippool` ( )
    RETURNS TINYINT NO SQL
BEGIN
    DECLARE mid INTEGER UNSIGNED;

    SELECT MAX(id) into mid FROM ip_pool;
    UPDATE ip_pool SET id = id + mid;
    SET @i = 0;
    UPDATE ip_pool SET id = @i:=@i+1 ORDER BY realip, type, tags;

    RETURN 1;
END$$
DELIMITER ;

ALTER TABLE places CHANGE `img` `img` VARCHAR(5) NOT NULL DEFAULT '';
ALTER TABLE places CHANGE `descr` `descr` VARCHAR(4096) NOT NULL DEFAULT '';
ALTER TABLE places CHANGE `location` `location` VARCHAR(4096) NOT NULL DEFAULT '';
ALTER TABLE places CHANGE `gpsX` `gpsX` FLOAT NOT NULL DEFAULT 0;
ALTER TABLE places CHANGE `gpsY` `gpsY` FLOAT NOT NULL DEFAULT 0;