| -- database.sql |
| -- Script to create the tables for process_records.php script to perform. |
| -- IMPORTANT NOTE: No constraint and almost no index are created by this script. |
| -- One should consider adding the appropriate indexes according to their utilization of the data. |
| |
| -- Incoming table table. |
| -- This table is used by app_acct.fdx to store the Accounting records. |
| CREATE TABLE incoming ( |
| "Origin-Host" bytea NOT NULL, |
| "Origin-Realm" bytea NOT NULL, |
| "Destination-Realm" bytea, |
| "Destination-Host" bytea, |
| "Session-Id" bytea NOT NULL, |
| "Origin-State-Id" integer, |
| "Accounting-Record-Type" integer NOT NULL, |
| "Accounting-Record-Number" integer NOT NULL, |
| "User-Name" bytea, |
| "Event-Timestamp" bytea, |
| "Acct-Application-Id" integer, |
| "Accounting-Sub-Session-Id" bigint, |
| "Acct-Session-Id" bytea, |
| "Acct-Multi-Session-Id" bytea, |
| "Origin-AAA-Protocol" integer, |
| "Acct-Delay-Time" integer, |
| "NAS-Identifier" bytea, |
| "NAS-IP-Address" bytea, |
| "NAS-IPv6-Address" bytea, |
| "NAS-Port" integer, |
| "NAS-Port-Id" bytea, |
| "NAS-Port-Type" integer, |
| "Service-Type" integer, |
| "Termination-Cause" integer, |
| "Accounting-Input-Octets" bigint, |
| "Accounting-Input-Packets" bigint, |
| "Accounting-Output-Octets" bigint, |
| "Accounting-Output-Packets" bigint, |
| "Acct-Authentic" integer, |
| "Acct-Link-Count" integer, |
| "Acct-Session-Time" integer, |
| "Acct-Tunnel-Connection" bytea, |
| "Acct-Tunnel-Packets-Lost" integer, |
| "Callback-Id" bytea, |
| "Callback-Number" bytea, |
| "Called-Station-Id" bytea, |
| "Calling-Station-Id" bytea, |
| "Connect-Info" bytea, |
| "Originating-Line-Info" bytea, |
| "Authorization-Lifetime" integer, |
| "Session-Timeout" integer, |
| "Idle-Timeout" integer, |
| "Port-Limit" integer, |
| "Accounting-Realtime-Required" integer, |
| "Acct-Interim-Interval" integer, |
| "Filter-Id" bytea, |
| "NAS-Filter-Rule" bytea, |
| "QoS-Filter-Rule" bytea, |
| "Login-IP-Host" bytea, |
| "Login-IPv6-Host" bytea, |
| "Login-LAT-Group" bytea, |
| "Login-LAT-Node" bytea, |
| "Login-LAT-Port" bytea, |
| "Login-LAT-Service" bytea, |
| "Login-Service" integer, |
| "Login-TCP-Port" integer, |
| "Route-Record1" bytea, |
| "Route-Record2" bytea, |
| "Route-Record3" bytea, |
| "Route-Record4" bytea, |
| "Route-Record5" bytea, |
| "recorded_on" timestamp with time zone NOT NULL, |
| "recorded_serv" bytea |
| ); |
| |
| |
| -- Accounting Data. |
| -- This is is the processed data that is used also by display_results.php |
| CREATE TABLE processed ( |
| user_name bytea, |
| user_device bytea, |
| nas_info bytea, |
| sess_start timestamp with time zone, |
| sess_duration interval, |
| downl_bytes bigint, |
| upl_bytes bigint, |
| downl_packets bigint, |
| upl_packets bigint |
| ); |
| CREATE INDEX un_index ON processed (user_name); |
| |
| |
| |
| -- Orphans table. |
| -- This is optional, and it must match the structure of your incoming table. |
| CREATE TABLE orphans ( |
| "Origin-Host" bytea NOT NULL, |
| "Origin-Realm" bytea NOT NULL, |
| "Destination-Realm" bytea, |
| "Destination-Host" bytea, |
| "Session-Id" bytea NOT NULL, |
| "Origin-State-Id" integer, |
| "Accounting-Record-Type" integer NOT NULL, |
| "Accounting-Record-Number" integer NOT NULL, |
| "User-Name" bytea, |
| "Event-Timestamp" bytea, |
| "Acct-Application-Id" integer, |
| "Accounting-Sub-Session-Id" bigint, |
| "Acct-Session-Id" bytea, |
| "Acct-Multi-Session-Id" bytea, |
| "Origin-AAA-Protocol" integer, |
| "Acct-Delay-Time" integer, |
| "NAS-Identifier" bytea, |
| "NAS-IP-Address" bytea, |
| "NAS-IPv6-Address" bytea, |
| "NAS-Port" integer, |
| "NAS-Port-Id" bytea, |
| "NAS-Port-Type" integer, |
| "Service-Type" integer, |
| "Termination-Cause" integer, |
| "Accounting-Input-Octets" bigint, |
| "Accounting-Input-Packets" bigint, |
| "Accounting-Output-Octets" bigint, |
| "Accounting-Output-Packets" bigint, |
| "Acct-Authentic" integer, |
| "Acct-Link-Count" integer, |
| "Acct-Session-Time" integer, |
| "Acct-Tunnel-Connection" bytea, |
| "Acct-Tunnel-Packets-Lost" integer, |
| "Callback-Id" bytea, |
| "Callback-Number" bytea, |
| "Called-Station-Id" bytea, |
| "Calling-Station-Id" bytea, |
| "Connect-Info" bytea, |
| "Originating-Line-Info" bytea, |
| "Authorization-Lifetime" integer, |
| "Session-Timeout" integer, |
| "Idle-Timeout" integer, |
| "Port-Limit" integer, |
| "Accounting-Realtime-Required" integer, |
| "Acct-Interim-Interval" integer, |
| "Filter-Id" bytea, |
| "NAS-Filter-Rule" bytea, |
| "QoS-Filter-Rule" bytea, |
| "Login-IP-Host" bytea, |
| "Login-IPv6-Host" bytea, |
| "Login-LAT-Group" bytea, |
| "Login-LAT-Node" bytea, |
| "Login-LAT-Port" bytea, |
| "Login-LAT-Service" bytea, |
| "Login-Service" integer, |
| "Login-TCP-Port" integer, |
| "Route-Record1" bytea, |
| "Route-Record2" bytea, |
| "Route-Record3" bytea, |
| "Route-Record4" bytea, |
| "Route-Record5" bytea, |
| "recorded_on" timestamp with time zone NOT NULL, |
| "recorded_serv" bytea |
| ); |
| |
| -- Archives table. |
| -- This is also optional, and it must match the structure of your incoming table. |
| CREATE TABLE archived ( |
| "Origin-Host" bytea NOT NULL, |
| "Origin-Realm" bytea NOT NULL, |
| "Destination-Realm" bytea, |
| "Destination-Host" bytea, |
| "Session-Id" bytea NOT NULL, |
| "Origin-State-Id" integer, |
| "Accounting-Record-Type" integer NOT NULL, |
| "Accounting-Record-Number" integer NOT NULL, |
| "User-Name" bytea, |
| "Event-Timestamp" bytea, |
| "Acct-Application-Id" integer, |
| "Accounting-Sub-Session-Id" bigint, |
| "Acct-Session-Id" bytea, |
| "Acct-Multi-Session-Id" bytea, |
| "Origin-AAA-Protocol" integer, |
| "Acct-Delay-Time" integer, |
| "NAS-Identifier" bytea, |
| "NAS-IP-Address" bytea, |
| "NAS-IPv6-Address" bytea, |
| "NAS-Port" integer, |
| "NAS-Port-Id" bytea, |
| "NAS-Port-Type" integer, |
| "Service-Type" integer, |
| "Termination-Cause" integer, |
| "Accounting-Input-Octets" bigint, |
| "Accounting-Input-Packets" bigint, |
| "Accounting-Output-Octets" bigint, |
| "Accounting-Output-Packets" bigint, |
| "Acct-Authentic" integer, |
| "Acct-Link-Count" integer, |
| "Acct-Session-Time" integer, |
| "Acct-Tunnel-Connection" bytea, |
| "Acct-Tunnel-Packets-Lost" integer, |
| "Callback-Id" bytea, |
| "Callback-Number" bytea, |
| "Called-Station-Id" bytea, |
| "Calling-Station-Id" bytea, |
| "Connect-Info" bytea, |
| "Originating-Line-Info" bytea, |
| "Authorization-Lifetime" integer, |
| "Session-Timeout" integer, |
| "Idle-Timeout" integer, |
| "Port-Limit" integer, |
| "Accounting-Realtime-Required" integer, |
| "Acct-Interim-Interval" integer, |
| "Filter-Id" bytea, |
| "NAS-Filter-Rule" bytea, |
| "QoS-Filter-Rule" bytea, |
| "Login-IP-Host" bytea, |
| "Login-IPv6-Host" bytea, |
| "Login-LAT-Group" bytea, |
| "Login-LAT-Node" bytea, |
| "Login-LAT-Port" bytea, |
| "Login-LAT-Service" bytea, |
| "Login-Service" integer, |
| "Login-TCP-Port" integer, |
| "Route-Record1" bytea, |
| "Route-Record2" bytea, |
| "Route-Record3" bytea, |
| "Route-Record4" bytea, |
| "Route-Record5" bytea, |
| "recorded_on" timestamp with time zone NOT NULL, |
| "recorded_serv" bytea |
| ); |
| |