#  Configuration for the SQL module, when using Postgresql and GNU Gatekeeper.
#
#  The VoIP database schema is available at:
#

sql {

	driver = "rlm_sql_postgresql"

	server = "localhost"
	login = "gkradius"
	password = "gkradius"
	
	radius_db = "voipdb"
		
	acct_table1 = "voipcall"
	acct_table2 = "voipcall"
		
	authcheck_table = "voipradattr"
	authreply_table = "voipradattr"
	
	deletestalesessions = yes

	sqltrace = yes
	sqltracefile = ${logdir}/sqltrace.sql

	# increase this value if you want more than 10 simultaneous
	# requests to be served by the module
	num_sql_socks = 10

	sql_user_name = "%{Stripped-User-Name:-%{User-Name:-none}}"

	authorize_check_query = "SELECT id, '%{SQL-User-Name}', attrname, attrvalue, attrop FROM \
		radius_get_check_attrs('%{SQL-User-Name}', NULLIF('%{Framed-IP-Address}', '')::INET, \
			NULLIF('%{NAS-IP-Address}', '')::INET, \
			CASE \
				WHEN '%{Called-Station-Id}' = '' THEN TRUE \
				ELSE FALSE \
			END, \
			CASE WHEN '%{Service-Type}' = 'Call-Check' THEN TRUE ELSE FALSE END, \
			'%{Calling-Station-Id}', NULLIF('%{Called-Station-Id}',''), \
			'%Z' \
			)"

	authorize_reply_query = "SELECT id, '%{SQL-User-Name}', attrname, attrvalue, attrop FROM \
		radius_get_reply_attrs('%{SQL-User-Name}', NULLIF('%{Framed-IP-Address}', '')::INET, \
			NULLIF('%{NAS-IP-Address}', '')::INET, \
			CASE \
				WHEN '%{Called-Station-Id}' = '' THEN TRUE \
				ELSE FALSE \
			END, \
			CASE WHEN '%{Service-Type}' = 'Call-Check' THEN TRUE ELSE FALSE END, \
			'%{Calling-Station-Id}', NULLIF('%{Called-Station-Id}',''), \
			'%Z' \
			)"

	#######################################################################
	#  Accounting Queries
	#######################################################################
	# accounting_onoff_query	- query for Accounting On/Off packets 
	# accounting_update_query	- query for Accounting update packets 
	# accounting_start_query	- query for Accounting start packets 
	# accounting_start_query_alt	- query for Accounting start packets 
	#                               (alternate in case first query fails)
	# accounting_stop_query		- query for Accounting stop packets 
	# accounting_stop_query_alt	- query for Accounting start packets 
	#                               (alternate in case first query doesn't
	#                                affect any existing rows in the table)
	#######################################################################
	
	accounting_start_query = "INSERT INTO ${acct_table1} \
		(id, h323id, acctsessionid, h323confid, gkip, gkid, \
		 callingstationip, callingstationid, calledstationip, calledstationid, \
		 setuptime, \
		 acctstarttime, acctstartdelay, acctupdatetime \
		 ) \
		VALUES \
		(DEFAULT, '%{SQL-User-Name}', '%{Acct-Session-Id}', '%{h323-conf-id}', \
		 '%{NAS-IP-Address}', '%{NAS-Identifier}', \
		 COALESCE(NULLIF('%{Framed-IP-Address}', ''), radius_get_ciscoavpair('%Z', 'h323-gw-address'), '127.0.0.1')::INET, \
		 '%{Calling-Station-Id}', \
		 '%{h323-remote-address:-127.0.0.1}', '%{Called-Station-Id}', \
		 NULLIF('%{h323-setup-time}', '')::TIMESTAMP(0) WITH TIME ZONE, \
		 (now() - '%{Acct-Delay-Time:-0}'::INTERVAL), '%{Acct-Delay-Time:-0}', \
		 (now() - '%{Acct-Delay-Time:-0}'::INTERVAL) \
		 )"

	accounting_start_query_alt = "SELECT throwex()"
	
	accounting_update_query = "UPDATE ${acct_table1} \
		SET duration = '%{Acct-Session-Time:-duration}', \
			connecttime = NULLIF('%{h323-connect-time}','')::TIMESTAMP(0) WITH TIME ZONE, \
			acctupdatetime = (now() - '%{Acct-Delay-Time:-0}'::INTERVAL) \
		WHERE acctsessionid = '%{Acct-Session-Id}' \
			AND gkip = '%{NAS-IP-Address}'::INET AND acctstoptime IS NULL"

	
	accounting_stop_query = "UPDATE ${acct_table1} \
		SET acctstoptime = (now() - '%{Acct-Delay-Time:-0}'::INTERVAL), \
			duration = '%{Acct-Session-Time:-duration}', \
			terminatecause = '%{h323-disconnect-cause:-10}', \
			acctstopdelay = '%{Acct-Delay-Time:-0}', \
			setuptime = NULLIF('%{h323-setup-time}', '')::TIMESTAMP(0) WITH TIME ZONE, \
			connecttime = NULLIF('%{h323-connect-time}', '')::TIMESTAMP(0) WITH TIME ZONE, \
			disconnecttime = NULLIF('%{h323-disconnect-time}', '')::TIMESTAMP(0) WITH TIME ZONE \
		WHERE acctsessionid = '%{Acct-Session-Id}' \
			AND gkip = '%{NAS-IP-Address}'::INET AND acctstoptime IS NULL"

	accounting_stop_query_alt = "INSERT INTO ${acct_table1} \
		(id, h323id, acctsessionid, h323confid, gkip, gkid, \
		 callingstationip, callingstationid, calledstationip, calledstationid, \
		 setuptime, connecttime, disconnecttime, terminatecause, duration, \
		 acctstarttime, acctstartdelay, acctupdatetime, acctstoptime, acctstopdelay \
		) \
		VALUES \
		(DEFAULT,'%{SQL-User-Name}', '%{Acct-Session-Id}', '%{h323-conf-id}', \
		 '%{NAS-IP-Address}', '%{NAS-Identifier}', \
		 COALESCE(NULLIF('%{Framed-IP-Address}', ''), radius_get_ciscoavpair('%Z', 'h323-gw-address'), '127.0.0.1')::INET, \
		 '%{Calling-Station-Id}', \
		 '%{h323-remote-address:-127.0.0.1}', '%{Called-Station-Id}', \
		 NULLIF('%{h323-setup-time}','')::TIMESTAMP(0) WITH TIME ZONE, \
		 NULLIF('%{h323-connect-time}','')::TIMESTAMP(0) WITH TIME ZONE, \
		 NULLIF('%{h323-disconnect-time}','')::TIMESTAMP(0) WITH TIME ZONE, \
		 '%{h323-disconnect-cause:-10}', '%{Acct-Session-Time:-0}', \
		 (now() - '%{Acct-Delay-Time:-0}'::INTERVAL - '%{Acct-Session-Time:-0}'::INTERVAL),0, \
		 (now() - '%{Acct-Delay-Time:-0}'::INTERVAL), \
		 (now() - '%{Acct-Delay-Time:-0}'::INTERVAL), '%{Acct-Delay-Time:-0}' \
		)"
}