2
* $Id: oracle.sql,v 1.1.2.1 2006/04/11 13:14:41 pnixon Exp $
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(32) NOT NULL,
48
nasipaddress VARCHAR(15) NOT NULL,
49
nasportid VARCHAR(15),
50
nasporttype VARCHAR(32),
51
acctstarttime TIMESTAMP WITH TIME ZONE,
52
acctstoptime TIMESTAMP WITH TIME ZONE,
53
acctsessiontime NUMERIC(12),
54
acctauthentic VARCHAR(32),
55
connectinfo_start VARCHAR(50),
56
connectinfo_stop VARCHAR(50),
57
acctinputoctets NUMERIC(12),
58
acctoutputoctets NUMERIC(12),
59
calledstationid VARCHAR(50),
60
callingstationid VARCHAR(50),
61
acctterminatecause VARCHAR(32),
62
servicetype VARCHAR(32),
63
framedprotocol VARCHAR(32),
64
framedipaddress VARCHAR(15),
65
acctstartdelay NUMERIC(12),
66
acctstopdelay NUMERIC(12)
68
CREATE UNIQUE INDEX radacct_idx1
69
ON radacct(acctsessionid,username,acctstarttime,
70
acctstoptime,nasipaddress,framedipaddress);
72
CREATE SEQUENCE radacct_seq START WITH 1 INCREMENT BY 1;
74
/* Trigger to emulate a serial # on the primary key */
75
CREATE OR REPLACE TRIGGER radacct_serialnumber
76
BEFORE INSERT OR UPDATE OF radacctid ON radacct
79
if ( :new.radacctid = 0 or :new.radacctid is null ) then
80
SELECT radacct_seq.nextval into :new.radacctid from dual;
86
* Table structure for table 'radcheck'
88
CREATE TABLE radcheck (
90
username VARCHAR(30) NOT NULL,
91
attribute VARCHAR(30),
92
op VARCHAR(2) NOT NULL,
95
CREATE SEQUENCE radcheck_seq START WITH 1 INCREMENT BY 1;
97
/* Trigger to emulate a serial # on the primary key */
98
CREATE OR REPLACE TRIGGER radcheck_serialnumber
99
BEFORE INSERT OR UPDATE OF id ON radcheck
102
if ( :new.id = 0 or :new.id is null ) then
103
SELECT radcheck_seq.nextval into :new.id from dual;
109
* Table structure for table 'radgroupcheck'
111
CREATE TABLE radgroupcheck (
113
groupname VARCHAR(20) UNIQUE NOT NULL,
114
attribute VARCHAR(40),
115
op VARCHAR(2) NOT NULL,
118
CREATE SEQUENCE radgroupcheck_seq START WITH 1 INCREMENT BY 1;
121
* Table structure for table 'radgroupreply'
123
CREATE TABLE radgroupreply (
125
GroupName VARCHAR(20) UNIQUE NOT NULL,
126
Attribute VARCHAR(40),
127
op VARCHAR(2) NOT NULL,
130
CREATE SEQUENCE radgroupreply_seq START WITH 1 INCREMENT BY 1;
133
* Table structure for table 'radreply'
135
CREATE TABLE radreply (
137
UserName VARCHAR(30) NOT NULL,
138
Attribute VARCHAR(30),
139
op VARCHAR(2) NOT NULL,
142
CREATE INDEX radreply_idx1 ON radreply(UserName);
143
CREATE SEQUENCE radreply_seq START WITH 1 INCREMENT BY 1;
145
/* Trigger to emulate a serial # on the primary key */
146
CREATE OR REPLACE TRIGGER radreply_serialnumber
147
BEFORE INSERT OR UPDATE OF id ON radreply
150
if ( :new.id = 0 or :new.id is null ) then
151
SELECT radreply_seq.nextval into :new.id from dual;
157
* Table structure for table 'usergroup'
159
CREATE TABLE usergroup (
161
UserName VARCHAR(30) UNIQUE NOT NULL,
162
GroupName VARCHAR(30)
164
CREATE SEQUENCE usergroup_seq START WITH 1 INCREMENT BY 1;
166
/* Trigger to emulate a serial # on the primary key */
167
CREATE OR REPLACE TRIGGER usergroup_serialnumber
168
BEFORE INSERT OR UPDATE OF id ON usergroup
171
if ( :new.id = 0 or :new.id is null ) then
172
SELECT usergroup_seq.nextval into :new.id from dual;
179
* Table structure for table 'realmgroup'
181
CREATE TABLE realmgroup (
183
RealmName VARCHAR(30) UNIQUE NOT NULL,
184
GroupName VARCHAR(30)
186
CREATE SEQUENCE realmgroup_seq START WITH 1 INCREMENT BY 1;
188
CREATE TABLE realms (
190
realmname VARCHAR(64),
195
CREATE SEQUENCE realms_seq START WITH 1 INCREMENT BY 1;