Mercurial > hg > freeDiameter

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