· 8 years ago · Mar 22, 2018, 06:56 PM
1CREATE TABLE `ip_pool` (
2 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3 `ip` int(10) unsigned NOT NULL DEFAULT '0',
4 `type` enum('static','dynamic','reserved') NOT NULL,
5 `realip` tinyint(3) unsigned NOT NULL DEFAULT '0',
6 `release` int(10) unsigned NOT NULL DEFAULT '0',
7 `uid` bigint(20) unsigned NOT NULL,
8 `tags` varchar(255) NOT NULL DEFAULT '',
9 PRIMARY KEY (`id`),
10 UNIQUE KEY `ip` (`ip`),
11 KEY `type` (`type`),
12 KEY `release` (`release`),
13 KEY `uid` (`uid`),
14 KEY `realip` (`realip`),
15 KEY `tags` (`tags`)
16) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17
18CREATE PROCEDURE radreply(IN login VARCHAR(64), IN tag VARCHAR(64))
19 BEGIN
20 DECLARE usr_mac VARCHAR(12);
21 DECLARE usr_ip VARCHAR(15);
22 DECLARE usr_state VARCHAR(15);
23 DECLARE usr_id INT;
24 DECLARE add_attr MEDIUMTEXT;
25 DECLARE line MEDIUMTEXT;
26 DECLARE i INT DEFAULT 1;
27
28 SELECT REPLACE(login, ':', '') INTO usr_mac;
29 SELECT uid INTO usr_id FROM mac_uid WHERE mac=usr_mac;
30 IF usr_id IS NOT NULL AND usr_id>0 THEN
31 SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;
32 SELECT state INTO usr_state FROM users WHERE id = usr_id;
33 UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
34 UPDATE mac_uid SET ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE uid=usr_id;
35 SELECT radius_attr INTO add_attr FROM users_services
36 WHERE uid=usr_id AND tags LIKE '%,inet,%' LIMIT 1;
37 ELSE
38 UPDATE mac_uid SET ip=0 WHERE uid=0 AND time<(UNIX_TIMESTAMP()-3600);
39 START TRANSACTION;
40 SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p
41 WHERE uid=0 AND type='dynamic' AND tags LIKE CONCAT('%,', tag ,',%')
42 AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
43 ORDER BY RAND() LIMIT 1 FOR UPDATE;
44 INSERT INTO mac_uid VALUES(
45 NULL, usr_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), 0, 0, 0)
46 ON DUPLICATE KEY
47 UPDATE ip=IF(ip>0,ip,INET_ATON(usr_ip)), time=UNIX_TIMESTAMP();
48 COMMIT;
49 SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid WHERE mac=usr_mac;
50 END IF;
51 SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
52 IF usr_id IS NULL OR usr_id = 0 OR usr_state = 'off' OR ISNULL(add_attr) THEN
53 SELECT NULL, login, 'L4-Redirect', '1', '=';
54 SELECT NULL, login, 'Filter-Id', '"1234/1234"', '=';
55 END IF;
56 END;
57
58
59CREATE FUNCTION get_ip_by_tag(user_id INT UNSIGNED, tag VARCHAR(64))
60 RETURNS VARCHAR(15)
61 BEGIN
62 DECLARE user_ip VARCHAR(15);
63 DECLARE real_ip VARCHAR(15) DEFAULT 0;
64 DECLARE row_cnt INTEGER;
65 DECLARE ip_id INTEGER;
66 DECLARE tries INTEGER DEFAULT 30;
67 DECLARE id_min INTEGER;
68 DECLARE id_max INTEGER;
69
70 SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
71 WHERE uid = user_id AND type='static' LIMIT 1;
72 IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;
73
74 SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
75
76 SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
77 WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
78 AND tags LIKE CONCAT('%,', tag, ',%')
79 LIMIT 1;
80
81 IF( ip_id IS NOT NULL) THEN
82 UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
83 WHERE id = ip_id AND uid = user_id;
84 SELECT ROW_COUNT() INTO row_cnt;
85 IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
86 END IF;
87
88 SELECT MAX(id), MIN(id) INTO id_max, id_min
89 FROM ip_pool
90 WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0)
91 AND tags LIKE CONCAT('%,', tag, ',%');
92
93 sel_ip: WHILE tries > 0 DO
94 SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
95 FROM ip_pool
96 WHERE uid = 0
97 AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
98 AND id <= id_max
99 AND `release` < UNIX_TIMESTAMP()
100 AND tags LIKE CONCAT('%,', tag, ',%')
101 LIMIT 1;
102 IF( user_ip IS NOT NULL) THEN
103 UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
104 WHERE id = ip_id AND uid = 0 AND `release` < UNIX_TIMESTAMP();
105 SELECT ROW_COUNT() INTO row_cnt;
106 IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
107 SET tries = tries - 5;
108 END IF;
109 SET tries = tries - 1;
110 END WHILE;
111
112 END;