--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/contrib/app_acct_tools/database.sql	Tue Sep 07 16:57:48 2010 +0900
@@ -0,0 +1,227 @@
+-- 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
+ );
+