4 * Oracle schema for FreeRADIUS
7 * NOTE: Which columns are NULLable??
11 * Table structure for table 'radacct'
13 CREATE TABLE radacct (
14 radacctid INT PRIMARY KEY,
15 acctsessionid VARCHAR(96) NOT NULL,
16 acctuniqueid VARCHAR(32),
17 username VARCHAR(64) NOT NULL,
18 groupname VARCHAR(32),
20 nasipaddress VARCHAR(15) NOT NULL,
21 nasportid VARCHAR(32),
22 nasporttype VARCHAR(32),
23 acctstarttime TIMESTAMP WITH TIME ZONE,
24 acctstoptime TIMESTAMP WITH TIME ZONE,
25 acctsessiontime NUMERIC(19),
26 acctauthentic VARCHAR(32),
27 connectinfo_start VARCHAR(50),
28 connectinfo_stop VARCHAR(50),
29 acctinputoctets NUMERIC(19),
30 acctoutputoctets NUMERIC(19),
31 calledstationid VARCHAR(50),
32 callingstationid VARCHAR(50),
33 acctterminatecause VARCHAR(32),
34 servicetype VARCHAR(32),
35 framedprotocol VARCHAR(32),
36 framedipaddress VARCHAR(15),
37 acctstartdelay NUMERIC(12),
38 acctstopdelay NUMERIC(12),
39 XAscendSessionSvrKey VARCHAR(10)
42 CREATE UNIUQE INDEX radacct_idx0
43 ON radacct(acctuniqueid);
44 CREATE UNIQUE INDEX radacct_idx1
45 ON radacct(acctsessionid,username,acctstarttime,
46 acctstoptime,nasipaddress,framedipaddress);
48 CREATE SEQUENCE radacct_seq START WITH 1 INCREMENT BY 1;
50 /* Trigger to emulate a serial # on the primary key */
51 CREATE OR REPLACE TRIGGER radacct_serialnumber
52 BEFORE INSERT OR UPDATE OF radacctid ON radacct
55 if ( :new.radacctid = 0 or :new.radacctid is null ) then
56 SELECT radacct_seq.nextval into :new.radacctid from dual;
62 * Table structure for table 'radcheck'
64 CREATE TABLE radcheck (
66 username VARCHAR(30) NOT NULL,
67 attribute VARCHAR(64),
68 op VARCHAR(2) NOT NULL,
71 CREATE SEQUENCE radcheck_seq START WITH 1 INCREMENT BY 1;
73 /* Trigger to emulate a serial # on the primary key */
74 CREATE OR REPLACE TRIGGER radcheck_serialnumber
75 BEFORE INSERT OR UPDATE OF id ON radcheck
78 if ( :new.id = 0 or :new.id is null ) then
79 SELECT radcheck_seq.nextval into :new.id from dual;
85 * Table structure for table 'radgroupcheck'
87 CREATE TABLE radgroupcheck (
89 groupname VARCHAR(20) UNIQUE NOT NULL,
90 attribute VARCHAR(64),
94 CREATE SEQUENCE radgroupcheck_seq START WITH 1 INCREMENT BY 1;
97 * Table structure for table 'radgroupreply'
99 CREATE TABLE radgroupreply (
101 GroupName VARCHAR(20) UNIQUE NOT NULL,
102 Attribute VARCHAR(64),
106 CREATE SEQUENCE radgroupreply_seq START WITH 1 INCREMENT BY 1;
109 * Table structure for table 'radreply'
111 CREATE TABLE radreply (
113 UserName VARCHAR(30) NOT NULL,
114 Attribute VARCHAR(64),
118 CREATE INDEX radreply_idx1 ON radreply(UserName);
119 CREATE SEQUENCE radreply_seq START WITH 1 INCREMENT BY 1;
121 /* Trigger to emulate a serial # on the primary key */
122 CREATE OR REPLACE TRIGGER radreply_serialnumber
123 BEFORE INSERT OR UPDATE OF id ON radreply
126 if ( :new.id = 0 or :new.id is null ) then
127 SELECT radreply_seq.nextval into :new.id from dual;
133 * Table structure for table 'radusergroup'
135 CREATE TABLE radusergroup (
137 UserName VARCHAR(30) UNIQUE NOT NULL,
138 GroupName VARCHAR(30)
140 CREATE SEQUENCE radusergroup_seq START WITH 1 INCREMENT BY 1;
142 /* Trigger to emulate a serial # on the primary key */
143 CREATE OR REPLACE TRIGGER radusergroup_serialnumber
144 BEFORE INSERT OR UPDATE OF id ON radusergroup
147 if ( :new.id = 0 or :new.id is null ) then
148 SELECT radusergroup_seq.nextval into :new.id from dual;
155 * Table structure for table 'realmgroup'
157 CREATE TABLE realmgroup (
159 RealmName VARCHAR(30) UNIQUE NOT NULL,
160 GroupName VARCHAR(30)
162 CREATE SEQUENCE realmgroup_seq START WITH 1 INCREMENT BY 1;
164 CREATE TABLE realms (
166 realmname VARCHAR(64),
171 CREATE SEQUENCE realms_seq START WITH 1 INCREMENT BY 1;
173 CREATE TABLE radhuntgroup (
175 GroupName VARCHAR(64) NOT NULL,
176 Nasipaddress VARCHAR(15) UNIQUE NOT NULL,
177 NASPortID VARCHAR(15)
180 CREATE SEQUENCE radhuntgroup_seq START WITH 1 INCREMENT BY 1;
182 CREATE OR REPLACE TRIGGER radhuntgroup_serialnumber
183 BEFORE INSERT OR UPDATE OF id ON radhuntgroup
186 if ( :new.id = 0 or :new.id is null ) then
187 SELECT radhuntgroup_seq.nextval into :new.id from dual;
191 CREATE TABLE radpostauth (
193 UserName VARCHAR(64) NOT NULL,
199 CREATE SEQUENCE radpostauth_seq START WITH 1 INCREMENT BY 1;
201 CREATE OR REPLACE TRIGGER radpostauth_TRIG
202 BEFORE INSERT OR UPDATE OF id ON radpostauth
205 if ( :new.id = 0 or :new.id is null ) then
206 SELECT radpostauth_seq.nextval into :new.id from dual;
208 if (:new.AuthDate is null) then
209 select sysdate into :new.AuthDate from dual;
216 * Table structure for table 'nas'
220 nasname VARCHAR(128),
221 shortname VARCHAR(32),
226 community VARCHAR(50),
227 description VARCHAR(200)
229 CREATE SEQUENCE nas_seq START WITH 1 INCREMENT BY 1;