4 * Oracle schema for FreeRADIUS
7 * NOTE: Which columns are NULLable??
11 * Table structure for table 'dictionary'
13 CREATE TABLE dictionary (
16 attribute VARCHAR(32),
21 CREATE SEQUENCE dictionary_seq START WITH 1 INCREMENT BY 1;
24 * Table structure for table 'nas'
30 shortname VARCHAR(32),
34 community VARCHAR(50),
35 description VARCHAR(200)
37 CREATE SEQUENCE nas_seq START WITH 1 INCREMENT BY 1;
40 * Table structure for table 'radacct'
42 CREATE TABLE radacct (
43 radacctid INT PRIMARY KEY,
44 acctsessionid VARCHAR(32) NOT NULL,
45 acctuniqueid VARCHAR(32),
46 username VARCHAR(64) NOT NULL,
47 groupname VARCHAR(32) NOT NULL,
49 nasipaddress VARCHAR(15) NOT NULL,
50 nasportid VARCHAR(15),
51 nasporttype VARCHAR(32),
52 acctstarttime TIMESTAMP WITH TIME ZONE,
53 acctstoptime TIMESTAMP WITH TIME ZONE,
54 acctsessiontime NUMERIC(19),
55 acctauthentic VARCHAR(32),
56 connectinfo_start VARCHAR(50),
57 connectinfo_stop VARCHAR(50),
58 acctinputoctets NUMERIC(19),
59 acctoutputoctets NUMERIC(19),
60 calledstationid VARCHAR(50),
61 callingstationid VARCHAR(50),
62 acctterminatecause VARCHAR(32),
63 servicetype VARCHAR(32),
64 framedprotocol VARCHAR(32),
65 framedipaddress VARCHAR(15),
66 acctstartdelay NUMERIC(12),
67 acctstopdelay NUMERIC(12),
68 XAscendSessionSvrKey VARCHAR(10)
70 CREATE UNIQUE INDEX radacct_idx1
71 ON radacct(acctsessionid,username,acctstarttime,
72 acctstoptime,nasipaddress,framedipaddress);
74 CREATE SEQUENCE radacct_seq START WITH 1 INCREMENT BY 1;
76 /* Trigger to emulate a serial # on the primary key */
77 CREATE OR REPLACE TRIGGER radacct_serialnumber
78 BEFORE INSERT OR UPDATE OF radacctid ON radacct
81 if ( :new.radacctid = 0 or :new.radacctid is null ) then
82 SELECT radacct_seq.nextval into :new.radacctid from dual;
88 * Table structure for table 'radcheck'
90 CREATE TABLE radcheck (
92 username VARCHAR(30) NOT NULL,
93 attribute VARCHAR(30),
94 op VARCHAR(2) NOT NULL,
97 CREATE SEQUENCE radcheck_seq START WITH 1 INCREMENT BY 1;
99 /* Trigger to emulate a serial # on the primary key */
100 CREATE OR REPLACE TRIGGER radcheck_serialnumber
101 BEFORE INSERT OR UPDATE OF id ON radcheck
104 if ( :new.id = 0 or :new.id is null ) then
105 SELECT radcheck_seq.nextval into :new.id from dual;
111 * Table structure for table 'radgroupcheck'
113 CREATE TABLE radgroupcheck (
115 groupname VARCHAR(20) UNIQUE NOT NULL,
116 attribute VARCHAR(40),
117 op VARCHAR(2) NOT NULL,
120 CREATE SEQUENCE radgroupcheck_seq START WITH 1 INCREMENT BY 1;
123 * Table structure for table 'radgroupreply'
125 CREATE TABLE radgroupreply (
127 GroupName VARCHAR(20) UNIQUE NOT NULL,
128 Attribute VARCHAR(40),
129 op VARCHAR(2) NOT NULL,
132 CREATE SEQUENCE radgroupreply_seq START WITH 1 INCREMENT BY 1;
135 * Table structure for table 'radreply'
137 CREATE TABLE radreply (
139 UserName VARCHAR(30) NOT NULL,
140 Attribute VARCHAR(30),
141 op VARCHAR(2) NOT NULL,
144 CREATE INDEX radreply_idx1 ON radreply(UserName);
145 CREATE SEQUENCE radreply_seq START WITH 1 INCREMENT BY 1;
147 /* Trigger to emulate a serial # on the primary key */
148 CREATE OR REPLACE TRIGGER radreply_serialnumber
149 BEFORE INSERT OR UPDATE OF id ON radreply
152 if ( :new.id = 0 or :new.id is null ) then
153 SELECT radreply_seq.nextval into :new.id from dual;
159 * Table structure for table 'radusergroup'
161 CREATE TABLE radusergroup (
163 UserName VARCHAR(30) UNIQUE NOT NULL,
164 GroupName VARCHAR(30)
166 CREATE SEQUENCE radusergroup_seq START WITH 1 INCREMENT BY 1;
168 /* Trigger to emulate a serial # on the primary key */
169 CREATE OR REPLACE TRIGGER radusergroup_serialnumber
170 BEFORE INSERT OR UPDATE OF id ON radusergroup
173 if ( :new.id = 0 or :new.id is null ) then
174 SELECT radusergroup_seq.nextval into :new.id from dual;
181 * Table structure for table 'realmgroup'
183 CREATE TABLE realmgroup (
185 RealmName VARCHAR(30) UNIQUE NOT NULL,
186 GroupName VARCHAR(30)
188 CREATE SEQUENCE realmgroup_seq START WITH 1 INCREMENT BY 1;
190 CREATE TABLE realms (
192 realmname VARCHAR(64),
197 CREATE SEQUENCE realms_seq START WITH 1 INCREMENT BY 1;
199 CREATE TABLE radippool (
201 pool_name VARCHAR(30) NOT NULL,
202 framedipaddress VARCHAR(30) NOT NULL,
203 nasipaddress VARCHAR(30) NOT NULL,
204 pool_key INT NOT NULL,
205 CalledStationId VARCHAR(64),
206 CallingStationId VARCHAR(64) NOT NULL,
207 expiry_time timestamp(0) NOT NULL,
208 username VARCHAR(100)
211 CREATE INDEX radippool_poolname_ipaadr ON radippool (pool_name, framedipaddress);
212 CREATE INDEX radippool_poolname_expire ON radippool (pool_name, expiry_time);
213 CREATE INDEX radippool_nasipaddr_key ON radippool (nasipaddress, pool_key);
214 CREATE INDEX radippool_nasipaddr_calling ON radippool (nasipaddress, callingstationid);
216 CREATE SEQUENCE radippool_seq START WITH 1 INCREMENT BY 1;
218 CREATE OR REPLACE TRIGGER radippool_serialnumber
219 BEFORE INSERT OR UPDATE OF id ON radippool
222 if ( :new.id = 0 or :new.id is null ) then
223 SELECT radippool_seq.nextval into :new.id from dual;