Major rewrite. Among other things got rid of several unneeded CASE statements

diff --git a/raddb/postgresql.conf b/raddb/postgresql.conf
index f73c2db..60e2489 100644 (file)
--- a/raddb/postgresql.conf
+++ b/raddb/postgresql.conf
@@ -121,23 +121,16 @@ sql {
        #                                affect any existing rows in the table)
        #######################################################################
        
-# I've changed this section... removed inserting of radacctid (it's autoinctremented by database)
-# And unix_timestamp is mysql fuction... extract (epoch.. seem to work, but situation when
-# NAS stops/starts accounting is quite rare so i never tested it (but i tried it as an sql request, it works :) )
-# I also removed insertition of AcctStopTime from "Start" query (it fails with postgres)
-# and added NAS-IP-Address to Stop query (strange, but radius can not determine username when updating field with ip address and query is empty)
-# also i changed NAS-Port-id to NAS-Port (cisco nas gives me NAS-Port)
-# Hmmm... please let me know if i forgot somthing... and if i made mistake :)
-       accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S', AcctSessionTime=date_part('epoch'::text, ('%S'::timestamp without time zone - AcctStartTime::timestamp without time zone)), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time:-0} WHERE AcctSessionTime=0 AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
+       accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime = now(), AcctSessionTime=date_part('epoch'::text, (now()::timestamp with time zone - AcctStartTime::timestamp with time zone)), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time:-0} WHERE AcctSessionTime=0 AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= now()"
 
        accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress = '%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
 
-       accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', now(), '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time:-0}', '0')"
+       accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', now(), '%{Acct-Authentic}', '%{Connect-Info}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time:-0}')"
 
        accounting_start_query_alt  = "UPDATE ${acct_table1} SET AcctStartTime = '%S', AcctStartDelay = '%{Acct-Delay-Time:-0}', ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
 
-       accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = now(), AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = CASE WHEN '%{Acct-Input-Octets}' = '' THEN 0 ELSE  '%{Acct-Input-Octets}' END, AcctOutputOctets = CASE WHEN '%{Acct-Output-Octets}' = '' THEN 0 ELSE '%{Acct-Output-Octets}' END, AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}', FramedIPAddress = '%{Framed-IP-Address}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
+       accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = now(), AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets:-0}', AcctOutputOctets = '%{Acct-Output-Octets:-0}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}', FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
 
-       accounting_stop_query_alt = "INSERT into ${acct_table2} ( AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', now(), '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Connect-Info}', CASE WHEN '%{Acct-Input-Octets}' = '' THEN 0 ELSE  '%{Acct-Input-Octets}' END, CASE WHEN '%{Acct-Output-Octets}' = '' THEN 0 ELSE '%{Acct-Output-Octets}' END, '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time:-0}')"
+       accounting_stop_query_alt = "INSERT into ${acct_table2} ( AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', (now() - 'AcctStopDelay'::interval - 'AcctSessionTime'::interval), now(), '%{Acct-Session-Time}', '%{Acct-Authentic}', '%{Connect-Info}', '%{Acct-Input-Octets}:-0', '%{Acct-Output-Octets:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')"
 
 }