"Fossies" - the Fresh Open Source Software Archive

Member "freeradius-server-3.0.23/raddb/mods-config/sql/main/oracle/schema.sql" (10 Jun 2021, 5138 Bytes) of package /linux/misc/freeradius-server-3.0.23.tar.bz2:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PL/SQL source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. See also the latest Fossies "Diffs" side-by-side code changes report for "schema.sql": 3.0.22_vs_3.0.23.

    1 /*
    2  * $Id: 96cde18d1c42057dfff65df9fff5f664790c4fcb $
    3  *
    4  * Oracle schema for FreeRADIUS
    5  *
    6  *
    7  * NOTE: Which columns are NULLable??
    8  */
    9 
   10 /*
   11  * Table structure for table 'radacct'
   12  */
   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     realm           VARCHAR(64),
   19     nasipaddress        VARCHAR(15) NOT NULL,
   20     nasportid       VARCHAR(32),
   21     nasporttype     VARCHAR(32),
   22     acctstarttime       TIMESTAMP WITH TIME ZONE,
   23     acctstoptime        TIMESTAMP WITH TIME ZONE,
   24     acctsessiontime     NUMERIC(19),
   25     acctauthentic       VARCHAR(32),
   26     connectinfo_start   VARCHAR(128),
   27     connectinfo_stop    VARCHAR(128),
   28     acctinputoctets     NUMERIC(19),
   29     acctoutputoctets    NUMERIC(19),
   30     calledstationid     VARCHAR(50),
   31     callingstationid    VARCHAR(50),
   32     acctterminatecause  VARCHAR(32),
   33     servicetype     VARCHAR(32),
   34     framedprotocol      VARCHAR(32),
   35     framedipaddress     VARCHAR(15),
   36     framedipv6address   VARCHAR(45),
   37     framedipv6prefix    VARCHAR(45),
   38     framedinterfaceid   VARCHAR(44),
   39     delegatedipv6prefix VARCHAR(45),
   40     acctstartdelay      NUMERIC(12),
   41     acctstopdelay       NUMERIC(12),
   42     XAscendSessionSvrKey    VARCHAR(10),
   43     Class           VARCHAR(64)
   44 );
   45 
   46 CREATE UNIUQE INDEX radacct_idx0
   47     ON radacct(acctuniqueid);
   48 CREATE UNIQUE INDEX radacct_idx1
   49     ON radacct(acctsessionid,username,acctstarttime,
   50         acctstoptime,nasipaddress,framedipaddress,framedipv6address,framedipv6prefix,framedinterfaceid,delegatedipv6prefix);
   51 CREATE INDEX radacct_idx2
   52     ON radacct(class);
   53 
   54 CREATE SEQUENCE radacct_seq START WITH 1 INCREMENT BY 1;
   55 
   56 /* Trigger to emulate a serial # on the primary key */
   57 CREATE OR REPLACE TRIGGER radacct_serialnumber
   58     BEFORE INSERT OR UPDATE OF radacctid ON radacct
   59     FOR EACH ROW
   60     BEGIN
   61         if ( :new.radacctid = 0 or :new.radacctid is null ) then
   62             SELECT radacct_seq.nextval into :new.radacctid from dual;
   63         end if;
   64     END;
   65 /
   66 
   67 /*
   68  * Table structure for table 'radcheck'
   69  */
   70 CREATE TABLE radcheck (
   71     id      INT PRIMARY KEY,
   72     username    VARCHAR(30) NOT NULL,
   73     attribute   VARCHAR(64),
   74     op      VARCHAR(2) NOT NULL,
   75     value       VARCHAR(40)
   76 );
   77 CREATE SEQUENCE radcheck_seq START WITH 1 INCREMENT BY 1;
   78 
   79 /* Trigger to emulate a serial # on the primary key */
   80 CREATE OR REPLACE TRIGGER radcheck_serialnumber
   81     BEFORE INSERT OR UPDATE OF id ON radcheck
   82     FOR EACH ROW
   83     BEGIN
   84         if ( :new.id = 0 or :new.id is null ) then
   85             SELECT radcheck_seq.nextval into :new.id from dual;
   86         end if;
   87     END;
   88 /
   89 
   90 /*
   91  * Table structure for table 'radgroupcheck'
   92  */
   93 CREATE TABLE radgroupcheck (
   94     id      INT PRIMARY KEY,
   95     groupname   VARCHAR(20) NOT NULL,
   96     attribute   VARCHAR(64),
   97     op      CHAR(2) NOT NULL,
   98     value       VARCHAR(40)
   99 );
  100 CREATE SEQUENCE radgroupcheck_seq START WITH 1 INCREMENT BY 1;
  101 
  102 /*
  103  * Table structure for table 'radgroupreply'
  104  */
  105 CREATE TABLE radgroupreply (
  106     id      INT PRIMARY KEY,
  107     GroupName   VARCHAR(20) NOT NULL,
  108     Attribute   VARCHAR(64),
  109     op      CHAR(2) NOT NULL,
  110     Value       VARCHAR(40)
  111 );
  112 CREATE SEQUENCE radgroupreply_seq START WITH 1 INCREMENT BY 1;
  113 
  114 /*
  115  * Table structure for table 'radreply'
  116  */
  117 CREATE TABLE radreply (
  118     id      INT PRIMARY KEY,
  119     UserName    VARCHAR(30) NOT NULL,
  120     Attribute   VARCHAR(64),
  121     op      CHAR(2) NOT NULL,
  122     Value       VARCHAR(40)
  123 );
  124 CREATE INDEX radreply_idx1 ON radreply(UserName);
  125 CREATE SEQUENCE radreply_seq START WITH 1 INCREMENT BY 1;
  126 
  127 /* Trigger to emulate a serial # on the primary key */
  128 CREATE OR REPLACE TRIGGER radreply_serialnumber
  129     BEFORE INSERT OR UPDATE OF id ON radreply
  130     FOR EACH ROW
  131     BEGIN
  132         if ( :new.id = 0 or :new.id is null ) then
  133             SELECT radreply_seq.nextval into :new.id from dual;
  134         end if;
  135     END;
  136 /
  137 
  138 /*
  139  * Table structure for table 'radusergroup'
  140  */
  141 CREATE TABLE radusergroup (
  142     id      INT PRIMARY KEY,
  143     UserName    VARCHAR(30) NOT NULL,
  144     GroupName   VARCHAR(30)
  145 );
  146 CREATE SEQUENCE radusergroup_seq START WITH 1 INCREMENT BY 1;
  147 
  148 /* Trigger to emulate a serial # on the primary key */
  149 CREATE OR REPLACE TRIGGER radusergroup_serialnumber
  150     BEFORE INSERT OR UPDATE OF id ON radusergroup
  151     FOR EACH ROW
  152     BEGIN
  153         if ( :new.id = 0 or :new.id is null ) then
  154             SELECT radusergroup_seq.nextval into :new.id from dual;
  155         end if;
  156     END;
  157 /
  158 
  159 
  160 CREATE TABLE radpostauth (
  161       id            INT PRIMARY KEY,
  162       UserName      VARCHAR(64) NOT NULL,
  163       Pass          VARCHAR(64),
  164       Reply         VARCHAR(64),
  165       AuthDate  TIMESTAMP(6) WITH TIME ZONE,
  166       Class     VARCHAR(64)
  167 );
  168 CREATE INDEX radpostauth_idx0
  169     ON radpostauth(UserName);
  170 CREATE INDEX radpostauth_idx1
  171     ON radpostauth(class);
  172 
  173 CREATE SEQUENCE radpostauth_seq START WITH 1 INCREMENT BY 1;
  174 
  175 CREATE OR REPLACE TRIGGER radpostauth_TRIG
  176     BEFORE INSERT OR UPDATE OF id ON radpostauth
  177     FOR EACH ROW
  178     BEGIN
  179         if ( :new.id = 0 or :new.id is null ) then
  180             SELECT radpostauth_seq.nextval into :new.id from dual;
  181         end if;
  182         if (:new.AuthDate is null) then
  183           select systimestamp into :new.AuthDate from dual;
  184         end if;
  185     END;
  186 
  187 /
  188 
  189 /*
  190  * Table structure for table 'nas'
  191  */
  192 CREATE TABLE nas (
  193     id              INT PRIMARY KEY,
  194     nasname         VARCHAR(128),
  195     shortname       VARCHAR(32),
  196     type            VARCHAR(30),
  197     ports           INT,
  198     secret          VARCHAR(60),
  199     server          VARCHAR(64),
  200     community       VARCHAR(50),
  201     description     VARCHAR(200)
  202 );
  203 CREATE SEQUENCE nas_seq START WITH 1 INCREMENT BY 1;
  204