#
#  Configuration for the Freeradius SQL module, when using MySQL stored procedures 
#  that rotate automatically tables in the format radacctYYYYMM
#
#  It requires:
#
#      1. mediaproxy >=1.8.0 available from http://mediaproxy.ag-projects.com
#
#      2. patch to freeradius to support stored procedures available in
#         setup/radius/freeradius/freeradius.patch
#
#      3. MySQL server >= 5.0.13
#
#      4. MySQL stored procedures created by Andrei Magureanu available in
#         setup/radius/OpenSIPS/radius_accounting.proc
#
#      5. If usec should be used, %S needs to be removed and it just needs to be
#         ''. Mysql will determine the time with now_usec
#
#
sql {
    driver                      = "rlm_sql_mysql"
    server                      = "sipdb"
    login                       = "radius"
    password                    = "PASSWORD"
    radius_db                   = "radius"
    sqltrace                    = no
    sqltracefile                = ${logdir}/sqltrace-%Y%m%d.log
    num_sql_socks               = 25
    connect_failure_retry_delay = 60

    accounting_start_query      = "\
          CALL insert_radacct_record( \
                'radius', \
                '%{Acct-Session-Id}', \
                '%{Acct-Unique-Session-Id}', \
                '%{Billing-Party}', \
                '%{Billing-Party}', \
                '%{SIP-Proxy-IP}', \
                '%{NAS-Port}', \
                FROM_UNIXTIME(%{Event-Timestamp}), \
                '0', \
                '0', \
                '0', \
                '0', \
                '%{TLS-Common-Name}', \                
                '%{Called-Station-Id}', \
                '%{Calling-Station-Id}', \
                '%{Sip-Response-Code}', \
                '%{Service-Type}', \
                '%{ENUM-TLD}', \
                '%{Framed-IP-Address}', \
                 UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \
                 UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \
                '%{Sip-Response-Code}', \
                '%{Sip-Method}', \
                '%{Sip-Translated-Request-URI}', \
                '%{Sip-To-Tag}', \
                '%{Sip-From-Tag}', \
                '%{Sip-RPId}', \
                '', \
                '%{Source-IP}', \
                '%{Source-Port}', \
                '%{Canonical-URI}', \
                '', \
                '', \
                '%{Sip-Application-Type}', \
                '%{User-Agent}', \
                '%{From-Header}' \
                )"

    accounting_stop_query       = "\
	  CALL update_radacct_record( \
                'radius', \
		FROM_UNIXTIME(%{Event-Timestamp}), \
                UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \
                '', \
                '%{X-RTP-Stat}', \
		'%{Acct-Session-Id}', \
                '%{Sip-To-Tag}', \
                '%{Sip-From-Tag}' \
                )"

    accounting_update_query       = "\
	  CALL update_raddact_record_mediaproxy( \
                'radius', \
		'%{Acct-Session-Time}', \
                '%{Acct-Input-Octets}', \
                '%{Acct-Output-Octets}', \
                '%{NAS-IP-Address}',\
                '%{Sip-User-Agents}', \
		'%{Media-Codecs}', \
		'%{Sip-Applications}', \
		'%{Media-Info}', \
		'0', \
		'%{Acct-Session-Id}', \
                '%{Sip-To-Tag}', \
                '%{Sip-From-Tag}' \
                )"

    accounting_failed_query     = "\
          CALL insert_radacct_record( \
                'radius', \
                '%{Acct-Session-Id}', \
                '%{Acct-Unique-Session-Id}', \
                '%{Billing-Party}', \
                '%{Billing-Party}', \
                '%{SIP-Proxy-IP}', \
                '%{NAS-Port}', \
                FROM_UNIXTIME(%{Event-Timestamp}), \
                FROM_UNIXTIME(%{Event-Timestamp}), \
                '0', \
                '0', \
                '0', \
                '%{TLS-Common-Name}', \
                '%{Called-Station-Id}', \
                '%{Calling-Station-Id}', \
                '%{Sip-Response-Code}', \
                '%{Service-Type}', \
                '%{ENUM-TLD}', \
                '%{Framed-IP-Address}', \
                UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \
                UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \
                '%{Sip-Response-Code}', \
                '%{Sip-Method}', \
	        '%{Sip-Translated-Request-URI}', \
                MD5(RAND()), \
                '%{Sip-From-Tag}', \
                '%{Sip-RPId}', \
                '', \
                '%{Source-IP}', \
                '%{Source-Port}', \
                '%{Canonical-URI}', \
                '', \
                '', \
                '%{Sip-Application-Type}', \
                '%{User-Agent}', \
                '%{From-Header}' \
                )"
}
