Changeset 1303

Timestamp:

01/16/07 16:50:25 (6 years ago)

Author:

mglb1

Message:

Rework radius traffic handling to be more efficient

• Update the customer_state table using only completed session information from the radius_state table
• Create a new view current_customer_state which will also pull in data from the radius_traffic table if up to the minute information is required.

Some care will need to be put into rollover logic. In particular, when a
rollover occurs, the usage of the currently active session for each user
needs to be inserted as a negative value into the used_mb field of the
customer_state table, so that when the session ends and the usage is recorded
the mb from the previous plan period are not erroneously counted again.

This change has not been made to any of the logic modules yet.

Location:

ccsd/trunk/dbschema

Files:

• ccs_billing.schema (modified) (2 diffs)
• radius.schema (modified) (3 diffs)

ccsd/trunk/dbschema/ccs_billing.schema

@@ -66 +66 @@
 
 -- Current state of the customer
+-- This table is updated regularly to track how much traffic the customer has
+-- used. Only completed sessions are added to the traffic used in this table.
 CREATE TABLE customer_state (
     contact_id      integer         NOT NULL REFERENCES contact (contact_id)
@@ -85 +87 @@
                                                 ON DELETE RESTRICT
 );
+
+-- The real current state of this customer 
+-- This takes into accunt the used_mb's recorded in the above state table, plus
+-- the number of megabytes used in the currently active session
+CREATE VIEW current_customer_state AS SELECT 
+    cs.contact_id, cs.plan_start, cs.used_mb, 
+    cs.used_mb+COALESCE(t.acctinputoctets+t.acctoutputoctets,0)/1024/1024 AS
+    current_mb, ras.acctsessionid FROM customer_state cs, contact c LEFT JOIN
+    radius_active_sessions ras ON c.username=ras.username LEFT JOIN 
+    radius_latest_traffic t ON ras.acctsessionid=t.acctsessionid WHERE 
+    cs.contact_id=c.contact_id;
 
 -- Billing records for the customer

ccsd/trunk/dbschema/radius.schema

@@ -162 +162 @@
 CREATE INDEX radacct_stop_user_idx ON radius_session (acctStopTime, UserName);
 
+-- Helper views to return only active or completed sessions
+CREATE VIEW radius_active_sessions AS SELECT * FROM radius_session WHERE
+    acctstoptime IS NULL;
+CREATE VIEW radius_completed_sessions AS SELECT * FROM radius_session WHERE
+    acctstoptime IS NOT NULL;
+
 -- Create a table that RADIUS will store interim accounting updates into
 -- This table is used to determine how much traffic the user has done
@@ -175 +181 @@
 );
 CREATE INDEX radiustraffic_idx ON radius_traffic (AcctSessionId, UserName);
+CREATE INDEX radiustraffic_session_idx ON radius_traffic (acctsessionid,
+    accttime); 
+CREATE INDEX radiustraffic_id_idx ON radius_traffic (acctsessionid);
+
+-- Views to filter the radius_traffic updates down to the latest update for
+-- each session
+CREATE VIEW radius_latest_updates AS SELECT acctsessionid, 
+    max(accttime) AS latest FROM radius_traffic GROUP BY acctsessionid;
+CREATE VIEW radius_latest_traffic AS select t.acctsessionid, l.latest, 
+    t.acctinputoctets, t.acctoutputoctets FROM radius_latest_updates l 
+    LEFT JOIN radius_traffic t ON l.acctsessionid=t.acctsessionid AND
+    l.latest=t.accttime ORDER BY latest, acctinputoctets, acctoutputoctets;
 
 -- Create a function that can be called to update the customer state table
@@ -183 +201 @@
     rows integer;
 BEGIN
-UPDATE customer_state SET last_updated=NOW(), used_mb=COALESCE((
-SELECT (SUM(t1.acctinputoctets+t1.acctoutputoctets) -
-    SUM(COALESCE(t2.acctinputoctets+t2.acctoutputoctets,0))) /1024 /1024
-FROM radius_traffic t1 LEFT JOIN radius_traffic t2 
-ON t1.acctsessionid = t2.acctsessionid AND t2.accttime=( 
-    SELECT MAX(t4.accttime) FROM radius_traffic t4 
-    WHERE t4.acctsessionid=t2.acctsessionid 
-    AND t4.accttime<customer_state.plan_start), contact c
-WHERE t1.accttime=( 
-    SELECT MAX(t3.accttime) FROM radius_traffic t3 
-    WHERE t3.acctsessionid=t1.acctsessionid 
-    AND t3.accttime>customer_state.plan_start)
-AND t1.username=c.username AND c.contact_id=customer_state.contact_id
-GROUP BY t1.username),0);
+-- Update usage for sessions that have completed
+    UPDATE customer_state SET last_updated=NOW(), used_mb=used_mb+COALESCE((
+        SELECT SUM(s.acctinputoctets+s.acctoutputoctets)/1024/1024 as mb FROM
+        radius_completed_sessions s, contact c WHERE s.username=c.username
+        AND c.contact_id=customer_state.contact_id 
+        AND s.acctstoptime>customer_state.last_updated), 0);
     GET DIAGNOSTICS rows = ROW_COUNT;
     RETURN rows;