init.sql 1.24 KB
Newer Older
1 2 3 4 5 6 7 8
--
-- create new Captive Portal database
--

-- connected clients
create table cp_clients (
      zoneid int
,     sessionid varchar
9
,     authenticated_via varchar
10 11 12 13
,     username varchar
,     ip_address varchar
,     mac_address varchar
,     created number
14
,     deleted integer default (0)
15 16 17 18 19 20 21 22 23 24
,     primary key (zoneid, sessionid)
);

create index cp_clients_ip ON cp_clients (ip_address);
create index cp_clients_zone ON cp_clients (zoneid);

-- session (accounting) info
create table session_info (
      zoneid int
,     sessionid varchar
25 26 27 28 29 30 31 32 33
,     prev_packets_in integer
,     prev_bytes_in   integer
,     prev_packets_out integer
,     prev_bytes_out   integer
,     packets_in integer default (0)
,     packets_out integer default (0)
,     bytes_in integer default (0)
,     bytes_out integer default (0)
,     last_accessed integer
34 35
,     primary key (zoneid, sessionid)
);
36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

-- session (accounting) restrictions
create table session_restrictions (
      zoneid int
,     sessionid varchar
,     session_timeout int
,     primary key (zoneid, sessionid)
) ;

--  accounting state, record the state of (radius) accounting messages
create table accounting_state (
      zoneid int
,     sessionid varchar
,     state varchar
,     primary key (zoneid, sessionid)
) ;