1--
2-- A stored procedure to reallocate a user's previous address, otherwise
3-- provide a free address.
4--
5-- Using this SP reduces the usual set dialogue of queries to a single
6-- query:
7--
8--   BEGIN TRAN; "SELECT FOR UPDATE"; UPDATE; COMMIT TRAN;  ->  EXEC sp
9--
10-- The stored procedure is executed on an database instance within a single
11-- round trip which often leads to reduced deadlocking and significant
12-- performance improvements especially on multi-master clusters, perhaps even
13-- by an order of magnitude or more.
14--
15-- To use this stored procedure the corresponding queries.conf statements must
16-- be configured as follows:
17--
18-- allocate_begin = ""
19-- allocate_find = "\
20--      EXEC fr_dhcp_allocate_previous_or_new_framedipaddress \
21--              @v_pool_name = '%{control:${pool_name}}', \
22--              @v_gateway = '%{DHCP-Gateway-IP-Address}', \
23--              @v_pool_key = '${pool_key}', \
24--              @v_lease_duration = ${lease_duration}, \
25--              @v_requested_address = '%{%{${req_attribute_name}}:-0.0.0.0}' \
26--      "
27-- allocate_update = ""
28-- allocate_commit = ""
29--
30
31CREATE OR ALTER PROCEDURE fr_dhcp_allocate_previous_or_new_framedipaddress
32	@v_pool_name VARCHAR(64),
33	@v_gateway VARCHAR(15),
34	@v_pool_key VARCHAR(64),
35	@v_lease_duration INT,
36	@v_requested_address VARCHAR(15)
37AS
38	BEGIN
39
40		-- MS SQL lacks a "SELECT FOR UPDATE" statement, and its table
41		-- hints do not provide a direct means to implement the row-level
42		-- read lock needed to guarentee that concurrent queries do not
43		-- select the same Framed-IP-Address for allocation to distinct
44		-- users.
45		--
46		-- The "WITH cte AS ( SELECT ... ) UPDATE cte ... OUTPUT INTO"
47		-- patterns in this procedure body compensate by wrapping
48		-- the SELECT in a synthetic UPDATE which locks the row.
49
50		DECLARE @r_address_tab TABLE(id VARCHAR(15));
51		DECLARE @r_address VARCHAR(15);
52
53		BEGIN TRAN;
54
55		-- Reissue an existing IP address lease when re-authenticating a session
56		--
57		WITH cte AS (
58			SELECT TOP(1) FramedIPAddress
59			FROM dhcpippool WITH (rowlock, readpast)
60			JOIN dhcpstatus
61			ON dhcpstatus.status_id = dhcpippool.status_id
62			WHERE pool_name = @v_pool_name
63				AND expiry_time > CURRENT_TIMESTAMP
64				AND pool_key = @v_pool_key
65				AND dhcpstatus.status IN ('dynamic', 'static')
66		)
67		UPDATE cte
68		SET FramedIPAddress = FramedIPAddress
69		OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab;
70		SELECT @r_address = id FROM @r_address_tab;
71
72		-- Reissue an user's previous IP address, provided that the lease is
73		-- available (i.e. enable sticky IPs)
74		--
75		-- When using this SELECT you should delete the one above. You must also
76		-- set allocate_clear = "" in queries.conf to persist the associations
77		-- for expired leases.
78		--
79		-- WITH cte AS (
80		-- 	SELECT TOP(1) FramedIPAddress
81		-- 	FROM dhcpippool WITH (rowlock, readpast)
82		--	JOIN dhcpstatus
83		--	ON dhcpstatus.status_id = dhcpippool.status_id
84		-- 	WHERE pool_name = @v_pool_name
85		-- 		AND pool_key = @v_pool_key
86		--		AND dhcpstatus.status IN ('dynamic', 'static')
87		-- )
88		-- UPDATE cte
89		-- SET FramedIPAddress = FramedIPAddress
90		-- OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab;
91		-- SELECT @r_address = id FROM @r_address_tab;
92
93		-- Issue the requested IP address if it is available
94		--
95		IF @r_address IS NULL AND @v_requested_address <> '0.0.0.0'
96		BEGIN
97			WITH cte AS (
98				SELECT TOP(1) FramedIPAddress
99				FROM dhcpippool WITH (rowlock, readpast)
100				JOIN dhcpstatus
101				ON dhcpstatus.status_id = dhcpippool.status_id
102				WHERE pool_name = @v_pool_name
103					AND framedipaddress = @v_requested_address
104					AND dhcpstatus.status = 'dynamic'
105					AND ( pool_key = @v_pool_name OR expiry_time < CURRENT_TIMESTAMP )
106			)
107			UPDATE cte
108			SET FramedIPAddress = FramedIPAddress
109			OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab;
110			SELECT @r_address = id FROM @r_address_tab;
111		END
112
113		-- If we didn't reallocate a previous address then pick the least
114		-- recently used address from the pool which maximises the likelihood
115		-- of re-assigning the other addresses to their recent user
116		--
117		IF @r_address IS NULL
118		BEGIN
119			WITH cte AS (
120				SELECT TOP(1) FramedIPAddress
121				FROM dhcpippool WITH (rowlock, readpast)
122				JOIN dhcpstatus
123				ON dhcpstatus.status_id = dhcpippool.status_id
124				WHERE pool_name = @v_pool_name
125					AND expiry_time < CURRENT_TIMESTAMP
126					AND dhcpstatus.status = 'dynamic'
127				ORDER BY
128					expiry_time
129			)
130			UPDATE cte
131			SET FramedIPAddress = FramedIPAddress
132			OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab;
133			SELECT @r_address = id FROM @r_address_tab;
134		END
135
136		-- Return nothing if we failed to allocated an address
137		--
138		IF @r_address IS NULL
139		BEGIN
140			COMMIT TRAN;
141			RETURN;
142		END
143
144		-- Update the pool having allocated an IP address
145		--
146		UPDATE dhcpippool
147		SET
148			gateway = @v_gateway,
149			pool_key = @v_pool_key,
150			expiry_time = DATEADD(SECOND,@v_lease_duration,CURRENT_TIMESTAMP)
151		WHERE framedipaddress = @r_address;
152
153		COMMIT TRAN;
154
155		-- Return the address that we allocated
156		SELECT @r_address;
157
158	END
159GO
160