Mercurial > hg > freeDiameter

annotate contrib/app_acct_tools/database.sql @ 533:4cdf146f11d5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added a set of example PHP scripts to parse the app_acct.fdx data.
author Sebastien Decugis <sdecugis@nict.go.jp>
date Tue, 07 Sep 2010 16:57:48 +0900
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
533
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
1 -- database.sql
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
2 -- Script to create the tables for process_records.php script to perform.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
3 -- IMPORTANT NOTE: No constraint and almost no index are created by this script.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
4 -- One should consider adding the appropriate indexes according to their utilization of the data.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
5
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
6 -- Incoming table table.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
7 -- This table is used by app_acct.fdx to store the Accounting records.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
8 CREATE TABLE incoming (
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
9 "Origin-Host" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
10 "Origin-Realm" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
11 "Destination-Realm" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
12 "Destination-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
13 "Session-Id" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
14 "Origin-State-Id" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
15 "Accounting-Record-Type" integer NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
16 "Accounting-Record-Number" integer NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
17 "User-Name" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
18 "Event-Timestamp" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
19 "Acct-Application-Id" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
20 "Accounting-Sub-Session-Id" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
21 "Acct-Session-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
22 "Acct-Multi-Session-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
23 "Origin-AAA-Protocol" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
24 "Acct-Delay-Time" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
25 "NAS-Identifier" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
26 "NAS-IP-Address" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
27 "NAS-IPv6-Address" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
28 "NAS-Port" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
29 "NAS-Port-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
30 "NAS-Port-Type" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
31 "Service-Type" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
32 "Termination-Cause" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
33 "Accounting-Input-Octets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
34 "Accounting-Input-Packets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
35 "Accounting-Output-Octets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
36 "Accounting-Output-Packets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
37 "Acct-Authentic" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
38 "Acct-Link-Count" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
39 "Acct-Session-Time" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
40 "Acct-Tunnel-Connection" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
41 "Acct-Tunnel-Packets-Lost" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
42 "Callback-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
43 "Callback-Number" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
44 "Called-Station-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
45 "Calling-Station-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
46 "Connect-Info" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
47 "Originating-Line-Info" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
48 "Authorization-Lifetime" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
49 "Session-Timeout" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
50 "Idle-Timeout" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
51 "Port-Limit" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
52 "Accounting-Realtime-Required" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
53 "Acct-Interim-Interval" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
54 "Filter-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
55 "NAS-Filter-Rule" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
56 "QoS-Filter-Rule" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
57 "Login-IP-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
58 "Login-IPv6-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
59 "Login-LAT-Group" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
60 "Login-LAT-Node" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
61 "Login-LAT-Port" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
62 "Login-LAT-Service" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
63 "Login-Service" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
64 "Login-TCP-Port" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
65 "Route-Record1" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
66 "Route-Record2" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
67 "Route-Record3" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
68 "Route-Record4" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
69 "Route-Record5" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
70 "recorded_on" timestamp with time zone NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
71 "recorded_serv" bytea
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
72 );
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
73
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
74
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
75 -- Accounting Data.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
76 -- This is is the processed data that is used also by display_results.php
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
77 CREATE TABLE processed (
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
78 user_name bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
79 user_device bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
80 nas_info bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
81 sess_start timestamp with time zone,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
82 sess_duration interval,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
83 downl_bytes bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
84 upl_bytes bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
85 downl_packets bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
86 upl_packets bigint
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
87 );
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
88 CREATE INDEX un_index ON processed (user_name);
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
89
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
90
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
91
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
92 -- Orphans table.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
93 -- This is optional, and it must match the structure of your incoming table.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
94 CREATE TABLE orphans (
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
95 "Origin-Host" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
96 "Origin-Realm" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
97 "Destination-Realm" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
98 "Destination-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
99 "Session-Id" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
100 "Origin-State-Id" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
101 "Accounting-Record-Type" integer NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
102 "Accounting-Record-Number" integer NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
103 "User-Name" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
104 "Event-Timestamp" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
105 "Acct-Application-Id" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
106 "Accounting-Sub-Session-Id" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
107 "Acct-Session-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
108 "Acct-Multi-Session-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
109 "Origin-AAA-Protocol" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
110 "Acct-Delay-Time" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
111 "NAS-Identifier" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
112 "NAS-IP-Address" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
113 "NAS-IPv6-Address" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
114 "NAS-Port" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
115 "NAS-Port-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
116 "NAS-Port-Type" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
117 "Service-Type" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
118 "Termination-Cause" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
119 "Accounting-Input-Octets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
120 "Accounting-Input-Packets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
121 "Accounting-Output-Octets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
122 "Accounting-Output-Packets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
123 "Acct-Authentic" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
124 "Acct-Link-Count" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
125 "Acct-Session-Time" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
126 "Acct-Tunnel-Connection" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
127 "Acct-Tunnel-Packets-Lost" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
128 "Callback-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
129 "Callback-Number" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
130 "Called-Station-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
131 "Calling-Station-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
132 "Connect-Info" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
133 "Originating-Line-Info" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
134 "Authorization-Lifetime" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
135 "Session-Timeout" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
136 "Idle-Timeout" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
137 "Port-Limit" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
138 "Accounting-Realtime-Required" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
139 "Acct-Interim-Interval" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
140 "Filter-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
141 "NAS-Filter-Rule" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
142 "QoS-Filter-Rule" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
143 "Login-IP-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
144 "Login-IPv6-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
145 "Login-LAT-Group" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
146 "Login-LAT-Node" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
147 "Login-LAT-Port" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
148 "Login-LAT-Service" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
149 "Login-Service" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
150 "Login-TCP-Port" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
151 "Route-Record1" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
152 "Route-Record2" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
153 "Route-Record3" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
154 "Route-Record4" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
155 "Route-Record5" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
156 "recorded_on" timestamp with time zone NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
157 "recorded_serv" bytea
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
158 );
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
159
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
160 -- Archives table.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
161 -- This is also optional, and it must match the structure of your incoming table.
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
162 CREATE TABLE archived (
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
163 "Origin-Host" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
164 "Origin-Realm" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
165 "Destination-Realm" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
166 "Destination-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
167 "Session-Id" bytea NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
168 "Origin-State-Id" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
169 "Accounting-Record-Type" integer NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
170 "Accounting-Record-Number" integer NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
171 "User-Name" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
172 "Event-Timestamp" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
173 "Acct-Application-Id" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
174 "Accounting-Sub-Session-Id" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
175 "Acct-Session-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
176 "Acct-Multi-Session-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
177 "Origin-AAA-Protocol" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
178 "Acct-Delay-Time" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
179 "NAS-Identifier" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
180 "NAS-IP-Address" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
181 "NAS-IPv6-Address" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
182 "NAS-Port" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
183 "NAS-Port-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
184 "NAS-Port-Type" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
185 "Service-Type" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
186 "Termination-Cause" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
187 "Accounting-Input-Octets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
188 "Accounting-Input-Packets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
189 "Accounting-Output-Octets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
190 "Accounting-Output-Packets" bigint,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
191 "Acct-Authentic" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
192 "Acct-Link-Count" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
193 "Acct-Session-Time" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
194 "Acct-Tunnel-Connection" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
195 "Acct-Tunnel-Packets-Lost" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
196 "Callback-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
197 "Callback-Number" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
198 "Called-Station-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
199 "Calling-Station-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
200 "Connect-Info" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
201 "Originating-Line-Info" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
202 "Authorization-Lifetime" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
203 "Session-Timeout" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
204 "Idle-Timeout" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
205 "Port-Limit" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
206 "Accounting-Realtime-Required" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
207 "Acct-Interim-Interval" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
208 "Filter-Id" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
209 "NAS-Filter-Rule" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
210 "QoS-Filter-Rule" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
211 "Login-IP-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
212 "Login-IPv6-Host" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
213 "Login-LAT-Group" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
214 "Login-LAT-Node" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
215 "Login-LAT-Port" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
216 "Login-LAT-Service" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
217 "Login-Service" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
218 "Login-TCP-Port" integer,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
219 "Route-Record1" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
220 "Route-Record2" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
221 "Route-Record3" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
222 "Route-Record4" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
223 "Route-Record5" bytea,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
224 "recorded_on" timestamp with time zone NOT NULL,
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
225 "recorded_serv" bytea
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
226 );
4cdf146f11d5 Added a set of example PHP scripts to parse the app_acct.fdx data.
Sebastien Decugis <sdecugis@nict.go.jp>
parents:
diff changeset
227
"Welcome to our mercurial repository"