1 -----------------------------------------------------------------------------
4 -- schema.sql rlm_sql - FreeRADIUS SQLite Module --
6 -- Database schema for SQLite rlm_sql module --
9 -- mysql -uroot -prootpass radius < schema.sql --
11 -----------------------------------------------------------------------------
14 -- Table structure for table 'radacct'
16 CREATE TABLE radacct (
17 radacctid bigint(21) PRIMARY KEY,
18 acctsessionid varchar(64) NOT NULL default '',
19 acctuniqueid varchar(32) NOT NULL default '',
20 username varchar(64) NOT NULL default '',
21 groupname varchar(64) NOT NULL default '',
22 realm varchar(64) default '',
23 nasipaddress varchar(15) NOT NULL default '',
24 nasportid varchar(15) default NULL,
25 nasporttype varchar(32) default NULL,
26 acctstarttime datetime NULL default NULL,
27 acctupdatetime datetime NULL default NULL,
28 acctstoptime datetime NULL default NULL,
29 acctinterval int(12) default NULL,
30 acctsessiontime int(12) default NULL,
31 acctauthentic varchar(32) default NULL,
32 connectinfo_start varchar(50) default NULL,
33 connectinfo_stop varchar(50) default NULL,
34 acctinputoctets bigint(20) default NULL,
35 acctoutputoctets bigint(20) default NULL,
36 calledstationid varchar(50) NOT NULL default '',
37 callingstationid varchar(50) NOT NULL default '',
38 acctterminatecause varchar(32) NOT NULL default '',
39 servicetype varchar(32) default NULL,
40 framedprotocol varchar(32) default NULL,
41 framedipaddress varchar(15) NOT NULL default ''
44 CREATE UNIQUE INDEX acctuniqueid ON radacct(acctuniqueid);
45 CREATE INDEX username ON radacct(username);
46 CREATE INDEX framedipaddress ON radacct (framedipaddress);
47 CREATE INDEX acctsessionid ON radacct(acctsessionid);
48 CREATE INDEX acctsessiontime ON radacct(acctsessiontime);
49 CREATE INDEX acctstarttime ON radacct(acctstarttime);
50 CREATE INDEX acctinterval ON radacct(acctinterval);
51 CREATE INDEX acctstoptime ON radacct(acctstoptime);
52 CREATE INDEX nasipaddress ON radacct(nasipaddress);
55 -- Table structure for table 'radcheck'
57 CREATE TABLE radcheck (
58 id int(11) PRIMARY KEY,
59 username varchar(64) NOT NULL default '',
60 attribute varchar(64) NOT NULL default '',
61 op char(2) NOT NULL DEFAULT '==',
62 value varchar(253) NOT NULL default ''
64 CREATE INDEX check_username ON radcheck(username);
67 -- Table structure for table 'radgroupcheck'
69 CREATE TABLE radgroupcheck (
70 id int(11) PRIMARY KEY,
71 groupname varchar(64) NOT NULL default '',
72 attribute varchar(64) NOT NULL default '',
73 op char(2) NOT NULL DEFAULT '==',
74 value varchar(253) NOT NULL default ''
76 CREATE INDEX check_groupname ON radgroupcheck(groupname);
79 -- Table structure for table 'radgroupreply'
81 CREATE TABLE radgroupreply (
82 id int(11) PRIMARY KEY,
83 groupname varchar(64) NOT NULL default '',
84 attribute varchar(64) NOT NULL default '',
85 op char(2) NOT NULL DEFAULT '=',
86 value varchar(253) NOT NULL default ''
88 CREATE INDEX reply_groupname ON radgroupreply(groupname);
91 -- Table structure for table 'radreply'
93 CREATE TABLE radreply (
94 id int(11) PRIMARY KEY,
95 username varchar(64) NOT NULL default '',
96 attribute varchar(64) NOT NULL default '',
97 op char(2) NOT NULL DEFAULT '=',
98 value varchar(253) NOT NULL default ''
100 CREATE INDEX reply_username ON radreply(username);
103 -- Table structure for table 'radusergroup'
105 CREATE TABLE radusergroup (
106 username varchar(64) NOT NULL default '',
107 groupname varchar(64) NOT NULL default '',
108 priority int(11) NOT NULL default '1'
110 CREATE INDEX usergroup_username ON radusergroup(username);
113 -- Table structure for table 'radpostauth'
115 CREATE TABLE radpostauth (
116 id int(11) PRIMARY KEY,
117 username varchar(64) NOT NULL default '',
118 pass varchar(64) NOT NULL default '',
119 reply varchar(32) NOT NULL default '',
120 authdate timestamp NOT NULL
124 -- Table structure for table 'nas'
127 id int(11) PRIMARY KEY,
128 nasname varchar(128) NOT NULL,
129 shortname varchar(32),
130 type varchar(30) DEFAULT 'other',
132 secret varchar(60) DEFAULT 'secret' NOT NULL,
134 community varchar(50),
135 description varchar(200) DEFAULT 'RADIUS Client'
137 CREATE INDEX nasname ON nas(nasname);