"Fossies" - the Fresh Open Source Software Archive

Member "freeradius-server-3.0.23/raddb/mods-config/sql/main/postgresql/schema.sql" (10 Jun 2021, 4937 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: 73b059f26bd08eda209ecb2ba1b8d5ccbfdebc09 $
    3  *
    4  * Postgresql schema for FreeRADIUS
    5  *
    6  * All field lengths need checking as some are still suboptimal. -pnixon 2003-07-13
    7  *
    8  */
    9 
   10 /*
   11  * Table structure for table 'radacct'
   12  *
   13  * Note: Column type bigserial does not exist prior to Postgres 7.2
   14  *       If you run an older version you need to change this to serial
   15  */
   16 CREATE TABLE IF NOT EXISTS radacct (
   17     RadAcctId       bigserial PRIMARY KEY,
   18     AcctSessionId       text NOT NULL,
   19     AcctUniqueId        text NOT NULL UNIQUE,
   20     UserName        text,
   21     Realm           text,
   22     NASIPAddress        inet NOT NULL,
   23     NASPortId       text,
   24     NASPortType     text,
   25     AcctStartTime       timestamp with time zone,
   26     AcctUpdateTime      timestamp with time zone,
   27     AcctStopTime        timestamp with time zone,
   28     AcctInterval        bigint,
   29     AcctSessionTime     bigint,
   30     AcctAuthentic       text,
   31     ConnectInfo_start   text,
   32     ConnectInfo_stop    text,
   33     AcctInputOctets     bigint,
   34     AcctOutputOctets    bigint,
   35     CalledStationId     text,
   36     CallingStationId    text,
   37     AcctTerminateCause  text,
   38     ServiceType     text,
   39     FramedProtocol      text,
   40     FramedIPAddress     inet,
   41     FramedIPv6Address   inet,
   42     FramedIPv6Prefix    inet,
   43     FramedInterfaceId   text,
   44     DelegatedIPv6Prefix inet,
   45     Class           text
   46 );
   47 -- This index may be useful..
   48 -- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
   49 
   50 -- For use by update-, stop- and simul_* queries
   51 CREATE INDEX radacct_active_session_idx ON radacct (AcctUniqueId) WHERE AcctStopTime IS NULL;
   52 
   53 -- Add if you you regularly have to replay packets
   54 -- CREATE INDEX radacct_session_idx ON radacct (AcctUniqueId);
   55 
   56 -- For backwards compatibility
   57 -- CREATE INDEX radacct_active_user_idx ON radacct (AcctSessionId, UserName, NASIPAddress) WHERE AcctStopTime IS NULL;
   58 
   59 -- For use by onoff-
   60 CREATE INDEX radacct_bulk_close ON radacct (NASIPAddress, AcctStartTime) WHERE AcctStopTime IS NULL;
   61 
   62 -- and for common statistic queries:
   63 CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);
   64 
   65 -- and, optionally
   66 -- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
   67 
   68 -- and for Class
   69 CREATE INDEX radacct_calss_idx ON radacct (Class);
   70 
   71 
   72 /*
   73  * Table structure for table 'radcheck'
   74  */
   75 CREATE TABLE IF NOT EXISTS radcheck (
   76     id          serial PRIMARY KEY,
   77     UserName        text NOT NULL DEFAULT '',
   78     Attribute       text NOT NULL DEFAULT '',
   79     op          VARCHAR(2) NOT NULL DEFAULT '==',
   80     Value           text NOT NULL DEFAULT ''
   81 );
   82 create index radcheck_UserName on radcheck (UserName,Attribute);
   83 /*
   84  * Use this index if you use case insensitive queries
   85  */
   86 -- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
   87 
   88 /*
   89  * Table structure for table 'radgroupcheck'
   90  */
   91 CREATE TABLE IF NOT EXISTS radgroupcheck (
   92     id          serial PRIMARY KEY,
   93     GroupName       text NOT NULL DEFAULT '',
   94     Attribute       text NOT NULL DEFAULT '',
   95     op          VARCHAR(2) NOT NULL DEFAULT '==',
   96     Value           text NOT NULL DEFAULT ''
   97 );
   98 create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
   99 
  100 /*
  101  * Table structure for table 'radgroupreply'
  102  */
  103 CREATE TABLE IF NOT EXISTS radgroupreply (
  104     id          serial PRIMARY KEY,
  105     GroupName       text NOT NULL DEFAULT '',
  106     Attribute       text NOT NULL DEFAULT '',
  107     op          VARCHAR(2) NOT NULL DEFAULT '=',
  108     Value           text NOT NULL DEFAULT ''
  109 );
  110 create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
  111 
  112 /*
  113  * Table structure for table 'radreply'
  114  */
  115 CREATE TABLE IF NOT EXISTS radreply (
  116     id          serial PRIMARY KEY,
  117     UserName        text NOT NULL DEFAULT '',
  118     Attribute       text NOT NULL DEFAULT '',
  119     op          VARCHAR(2) NOT NULL DEFAULT '=',
  120     Value           text NOT NULL DEFAULT ''
  121 );
  122 create index radreply_UserName on radreply (UserName,Attribute);
  123 /*
  124  * Use this index if you use case insensitive queries
  125  */
  126 -- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
  127 
  128 /*
  129  * Table structure for table 'radusergroup'
  130  */
  131 CREATE TABLE IF NOT EXISTS radusergroup (
  132     id          serial PRIMARY KEY,
  133     UserName        text NOT NULL DEFAULT '',
  134     GroupName       text NOT NULL DEFAULT '',
  135     priority        integer NOT NULL DEFAULT 0
  136 );
  137 create index radusergroup_UserName on radusergroup (UserName);
  138 /*
  139  * Use this index if you use case insensitive queries
  140  */
  141 -- create index radusergroup_UserName_lower on radusergroup (lower(UserName));
  142 
  143 --
  144 -- Table structure for table 'radpostauth'
  145 --
  146 
  147 CREATE TABLE IF NOT EXISTS radpostauth (
  148     id          bigserial PRIMARY KEY,
  149     username        text NOT NULL,
  150     pass            text,
  151     reply           text,
  152     CalledStationId     text,
  153     CallingStationId    text,
  154     authdate        timestamp with time zone NOT NULL default now(),
  155     Class           text
  156 );
  157 CREATE INDEX radpostauth_username_idx ON radpostauth (username);
  158 CREATE INDEX radpostauth_class_idx ON radpostauth (Class);
  159 
  160 /*
  161  * Table structure for table 'nas'
  162  */
  163 CREATE TABLE IF NOT EXISTS nas (
  164     id          serial PRIMARY KEY,
  165     nasname         text NOT NULL,
  166     shortname       text NOT NULL,
  167     type            text NOT NULL DEFAULT 'other',
  168     ports           integer,
  169     secret          text NOT NULL,
  170     server          text,
  171     community       text,
  172     description     text
  173 );
  174 create index nas_nasname on nas (nasname);