Subversion Repositories ALCASAR

Rev

Rev 3202 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log

Rev 3202 Rev 3278
Line -... Line 1...
- 
 
 1 
-----------------------------------------
- 
 
 2 
--
- 
 
 3 
-- Database radius schema for MariaDB
- 
 
 4 
--
- 
 
 5 
--  Adapted by Rexy for ALCASAR NAC
- 
 
 6 
--
- 
 
 7 
-----------------------------------------
- 
 
 8 
 
1 
--
 9 
--
2 
-- Table structure for table `radacct`
 10 
-- Table structure for table `radacct`
3 
--
 11 
--
4 
 
 12 
 
5 
DROP TABLE IF EXISTS `radacct`;
 13 
DROP TABLE IF EXISTS radacct;
6 
CREATE TABLE `radacct` (
 14 
CREATE TABLE radacct (
7 
  `radacctid`            bigint(21)   NOT NULL AUTO_INCREMENT,
 15 
  radacctid            bigint(21)   NOT NULL AUTO_INCREMENT,
8 
  `acctsessionid`        varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 16 
  acctsessionid        varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
9 
  `acctuniqueid`         varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
 17 
  acctuniqueid         varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
10 
  `username`             varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 18 
  username             varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
11 
  `realm`                varchar(64)  COLLATE utf8_bin DEFAULT '',
 19 
  realm                varchar(64)  COLLATE utf8_bin DEFAULT '',
12 
  `nasipaddress`         varchar(15)  COLLATE utf8_bin NOT NULL DEFAULT '',
 20 
  nasipaddress         varchar(15)  COLLATE utf8_bin NOT NULL DEFAULT '',
13 
  `nasportid`            varchar(32)  COLLATE utf8_bin DEFAULT NULL,
 21 
  nasportid            varchar(32)  COLLATE utf8_bin DEFAULT NULL,
14 
  `nasporttype`          varchar(32)  COLLATE utf8_bin DEFAULT NULL,
 22 
  nasporttype          varchar(32)  COLLATE utf8_bin DEFAULT NULL,
15 
  `acctstarttime`        datetime     DEFAULT NULL,
 23 
  acctstarttime        datetime     DEFAULT NULL,
16 
  `acctupdatetime`       datetime     DEFAULT NULL,
 24 
  acctupdatetime       datetime     DEFAULT NULL,
17 
  `acctstoptime`         datetime     DEFAULT NULL,
 25 
  acctstoptime         datetime     DEFAULT NULL,
18 
  `acctinterval`         int(12)      DEFAULT NULL,
 26 
  acctinterval         int(12)      DEFAULT NULL,
19 
  `acctsessiontime`      int(12)      DEFAULT NULL,
 27 
  acctsessiontime      int(12)      DEFAULT NULL,
20 
  `acctauthentic`        varchar(32)  COLLATE utf8_bin DEFAULT NULL,
 28 
  acctauthentic        varchar(32)  COLLATE utf8_bin DEFAULT NULL,
21 
  `connectinfo_start`    varchar(128) COLLATE utf8_bin DEFAULT NULL,
 29 
  connectinfo_start    varchar(128) COLLATE utf8_bin DEFAULT NULL,
22 
  `connectinfo_stop`     varchar(128) COLLATE utf8_bin DEFAULT NULL,
 30 
  connectinfo_stop     varchar(128) COLLATE utf8_bin DEFAULT NULL,
23 
  `acctinputoctets`      bigint(20)   DEFAULT NULL,
 31 
  acctinputoctets      bigint(20)   DEFAULT NULL,
24 
  `acctoutputoctets`     bigint(20)   DEFAULT NULL,
 32 
  acctoutputoctets     bigint(20)   DEFAULT NULL,
25 
  `calledstationid`      varchar(50)  COLLATE utf8_bin NOT NULL DEFAULT '',
 33 
  calledstationid      varchar(50)  COLLATE utf8_bin NOT NULL DEFAULT '',
26 
  `callingstationid`     varchar(50)  COLLATE utf8_bin NOT NULL DEFAULT '',
 34 
  callingstationid     varchar(50)  COLLATE utf8_bin NOT NULL DEFAULT '',
27 
  `acctterminatecause`   varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
 35 
  acctterminatecause   varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
28 
  `servicetype`          varchar(32)  COLLATE utf8_bin DEFAULT NULL,
 36 
  servicetype          varchar(32)  COLLATE utf8_bin DEFAULT NULL,
29 
  `framedprotocol`       varchar(32)  COLLATE utf8_bin DEFAULT NULL,
 37 
  framedprotocol       varchar(32)  COLLATE utf8_bin DEFAULT NULL,
30 
  `framedipaddress`      varchar(15)  COLLATE utf8_bin NOT NULL DEFAULT '',
 38 
  framedipaddress      varchar(15)  COLLATE utf8_bin NOT NULL DEFAULT '',
31 
  `framedipv6address`    varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
 39 
  framedipv6address    varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
32 
  `framedipv6prefix`     varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
 40 
  framedipv6prefix     varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
33 
  `framedinterfaceid`    varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
 41 
  framedinterfaceid    varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
34 
  `delegatedipv6prefix`  varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
 42 
  delegatedipv6prefix  varchar(45)  COLLATE utf8_bin NOT NULL DEFAULT '',
35 
  `class`                varchar(64)  DEFAULT NULL,
 43 
  class                varchar(64)  DEFAULT NULL,
36 
  PRIMARY KEY (`radacctid`),
 44 
  PRIMARY KEY (radacctid),
37 
  UNIQUE KEY `acctuniqueid` (`acctuniqueid`),
 45 
  UNIQUE KEY acctuniqueid (acctuniqueid),
38 
  KEY `username` (`username`),
 46 
  KEY username (username),
39 
  KEY `framedipaddress` (`framedipaddress`),
 47 
  KEY framedipaddress (framedipaddress),
40 
  KEY `framedipv6address` (`framedipv6address`),
 48 
  KEY framedipv6address (framedipv6address),
41 
  KEY `framedipv6prefix` (`framedipv6prefix`),
 49 
  KEY framedipv6prefix (framedipv6prefix),
42 
  KEY `framedinterfaceid` (`framedinterfaceid`),
 50 
  KEY framedinterfaceid (framedinterfaceid),
43 
  KEY `delegatedipv6prefix` (`delegatedipv6prefix`),
 51 
  KEY delegatedipv6prefix (delegatedipv6prefix),
44 
  KEY `acctsessionid` (`acctsessionid`),
 52 
  KEY acctsessionid (acctsessionid),
45 
  KEY `acctsessiontime` (`acctsessiontime`),
 53 
  KEY acctsessiontime (acctsessiontime),
46 
  KEY `acctstarttime` (`acctstarttime`),
 54 
  KEY acctstarttime (acctstarttime),
47 
  KEY `acctinterval` (`acctinterval`),
 55 
  KEY acctinterval (acctinterval),
48 
  KEY `acctstoptime` (`acctstoptime`),
 56 
  KEY acctstoptime (acctstoptime),
49 
  KEY `nasipaddress` (`nasipaddress`),
 57 
  KEY nasipaddress (nasipaddress),
50 
  KEY `class` (`class`)
 58 
  KEY class (class)
51 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 59 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
52 
 
 60 
 
53 
--
 61 
--
54 
-- Table structure for table `radcheck`
 62 
-- Table structure for table `radcheck`
55 
--
 63 
--
56 
 
 64 
 
57 
DROP TABLE IF EXISTS `radcheck`;
 65 
DROP TABLE IF EXISTS radcheck;
58 
CREATE TABLE `radcheck` (
 66 
CREATE TABLE radcheck (
59 
  `id`        int(11)      unsigned NOT NULL AUTO_INCREMENT,
 67 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
60 
  `username`  varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 68 
  username  varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
61 
  `attribute` varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 69 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
62 
  `op`        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '==',
 70 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '==',
63 
  `value`     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
 71 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
64 
  PRIMARY KEY (`id`),
 72 
  PRIMARY KEY (id),
65 
  KEY `username` (`username`(32))
 73 
  KEY username (username)
66 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 74 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
67 
 
 75 
 
68 
--
 76 
--
69 
-- Table structure for table `radgroupcheck`
 77 
-- Table structure for table `radgroupcheck`
70 
--
 78 
--
71 
 
 79 
 
72 
DROP TABLE IF EXISTS `radgroupcheck`;
 80 
DROP TABLE IF EXISTS radgroupcheck;
73 
CREATE TABLE `radgroupcheck` (
 81 
CREATE TABLE radgroupcheck (
74 
  `id`        int(11)      unsigned NOT NULL AUTO_INCREMENT,
 82 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
75 
  `groupname` varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 83 
  groupname varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
76 
  `attribute` varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 84 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
77 
  `op`        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '==',
 85 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '==',
78 
  `value`     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
 86 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
79 
  PRIMARY KEY (`id`),
 87 
  PRIMARY KEY (id),
80 
  KEY `groupname` (`groupname`(32))
 88 
  KEY groupname (groupname(32))
81 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 89 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
82 
 
 90 
 
83 
--
 91 
--
84 
-- Table structure for table `radgroupreply`
 92 
-- Table structure for table `radgroupreply`
85 
--
 93 
--
86 
 
 94 
 
87 
DROP TABLE IF EXISTS `radgroupreply`;
 95 
DROP TABLE IF EXISTS radgroupreply;
88 
CREATE TABLE `radgroupreply` (
 96 
CREATE TABLE radgroupreply (
89 
  `id`        int(11)      unsigned NOT NULL AUTO_INCREMENT,
 97 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
90 
  `groupname` varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 98 
  groupname varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
91 
  `attribute` varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 99 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
92 
  `op`        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '=',
 100 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '=',
93 
  `value`     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
 101 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
94 
  PRIMARY KEY (`id`),
 102 
  PRIMARY KEY (id),
95 
  KEY `groupname` (`groupname`(32))
 103 
  KEY groupname (groupname(32))
96 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 104 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
97 
 
 105 
 
98 
--
 106 
--
99 
-- Table structure for table `radreply`
 107 
-- Table structure for table `radreply`
100 
--
 108 
--
101 
 
 109 
 
102 
DROP TABLE IF EXISTS `radreply`;
 110 
DROP TABLE IF EXISTS radreply;
103 
CREATE TABLE `radreply` (
 111 
CREATE TABLE radreply (
104 
  `id`        int(11)      unsigned NOT NULL AUTO_INCREMENT,
 112 
  id        int(11)      unsigned NOT NULL AUTO_INCREMENT,
105 
  `username`  varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 113 
  username  varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
106 
  `attribute` varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 114 
  attribute varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
107 
  `op`        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '=',
 115 
  op        char(2)      COLLATE utf8_bin NOT NULL DEFAULT '=',
108 
  `value`     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
 116 
  value     varchar(253) COLLATE utf8_bin NOT NULL DEFAULT '',
109 
  PRIMARY KEY (`id`),
 117 
  PRIMARY KEY (id),
110 
  KEY `username` (`username`(32))
 118 
  KEY username (username(32))
111 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 119 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
112 
 
 120 
 
113 
--
 121 
--
114 
-- Table structure for table `radusergroup`
 122 
-- Table structure for table `radusergroup`
115 
--
 123 
--
116 
 
 124 
 
117 
DROP TABLE IF EXISTS `radusergroup`;
 125 
DROP TABLE IF EXISTS radusergroup;
118 
CREATE TABLE `radusergroup` (
 126 
CREATE TABLE radusergroup (
119 
  `username`  varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 127 
  username  varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
120 
  `groupname` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 128 
  groupname varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
121 
  `priority`  int(11)     NOT NULL DEFAULT '1',
 129 
  priority  int(11)     NOT NULL DEFAULT '1',
122 
  KEY `username` (`username`(32))
 130 
  KEY username (username(32))
123 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 131 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
124 
 
 132 
 
125 
--
 133 
--
126 
-- Table structure for table `radpostauth`
 134 
-- Table structure for table `radpostauth`
127 
--
 135 
--
128 
 
 136 
 
129 
DROP TABLE IF EXISTS `radpostauth`;
 137 
DROP TABLE IF EXISTS radpostauth;
130 
CREATE TABLE `radpostauth` (
 138 
CREATE TABLE radpostauth (
131 
  `id`       int(11)      NOT NULL AUTO_INCREMENT,
 139 
  id       int(11)      NOT NULL AUTO_INCREMENT,
132 
  `username` varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 140 
  username varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
133 
  `pass`     varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
 141 
  pass     varchar(64)  COLLATE utf8_bin NOT NULL DEFAULT '',
134 
  `reply`    varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
 142 
  reply    varchar(32)  COLLATE utf8_bin NOT NULL DEFAULT '',
135 
  `authdate` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
 143 
  authdate timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
136 
  `class`    varchar(64)  DEFAULT NULL,
 144 
  class    varchar(64)  DEFAULT NULL,
137 
  PRIMARY KEY (`id`),
 145 
  PRIMARY KEY (id),
138 
  KEY `username` (`username`),
 146 
  KEY username (username),
139 
  KEY `class` (`class`)
 147 
  KEY class (class)
140 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 148 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
141 
 
 149 
 
142 
--
 150 
--
143 
-- Table structure for table `nas`
 151 
-- Table structure for table `nas`
144 
--
 152 
--
145 
 
 153 
 
146 
DROP TABLE IF EXISTS `nas`;
 154 
DROP TABLE IF EXISTS nas;
147 
CREATE TABLE `nas` (
 155 
CREATE TABLE nas (
148 
  `id`          int(10)      NOT NULL AUTO_INCREMENT,
 156 
  id          int(10)      NOT NULL AUTO_INCREMENT,
149 
  `nasname`     varchar(128) NOT NULL,
 157 
  nasname     varchar(128) NOT NULL,
150 
  `shortname`   varchar(32),
 158 
  shortname   varchar(32),
151 
  `type`        varchar(30)  DEFAULT 'other',
 159 
  type        varchar(30)  DEFAULT 'other',
152 
  `ports`       int(5),
 160 
  ports       int(5),
153 
  `secret`      varchar(60)  DEFAULT 'secret' NOT NULL,
 161 
  secret      varchar(60)  DEFAULT 'secret' NOT NULL,
154 
  `server`      varchar(64),
 162 
  server      varchar(64),
155 
  `community`   varchar(50),
 163 
  community   varchar(50),
156 
  `description` varchar(200) DEFAULT 'RADIUS Client',
 164 
  description varchar(200) DEFAULT 'RADIUS Client',
157 
  PRIMARY KEY (id),
 165 
  PRIMARY KEY (id),
158 
  KEY `nasname` (`nasname`)
 166 
  KEY nasname (nasname)
159 
);
 - 
 
- 
 
 167 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
160 
 
 168 
 
161 
--
 169 
--
162 
-- Table structure for table `mtotacct`
 170 
-- Table structure for table `mtotacct`
163 
--
 171 
--
164 
 
 172 
 
165 
DROP TABLE IF EXISTS `mtotacct`;
 173 
DROP TABLE IF EXISTS mtotacct;
166 
CREATE TABLE `mtotacct` (
 174 
CREATE TABLE mtotacct (
167 
  `mtotacctid`      bigint(21)  NOT NULL AUTO_INCREMENT,
 175 
  mtotacctid      bigint(21)  NOT NULL AUTO_INCREMENT,
168 
  `username`        varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 176 
  username        varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
169 
  `acctdate`        date        NOT NULL DEFAULT '0000-00-00',
 177 
  acctdate        date        NOT NULL DEFAULT '0000-00-00',
170 
  `connnum`         bigint(12)  DEFAULT NULL,
 178 
  connnum         bigint(12)  DEFAULT NULL,
171 
  `conntotduration` bigint(12)  DEFAULT NULL,
 179 
  conntotduration bigint(12)  DEFAULT NULL,
172 
  `connmaxduration` bigint(12)  DEFAULT NULL,
 180 
  connmaxduration bigint(12)  DEFAULT NULL,
173 
  `connminduration` bigint(12)  DEFAULT NULL,
 181 
  connminduration bigint(12)  DEFAULT NULL,
174 
  `inputoctets`     bigint(12)  DEFAULT NULL,
 182 
  inputoctets     bigint(12)  DEFAULT NULL,
175 
  `outputoctets`    bigint(12)  DEFAULT NULL,
 183 
  outputoctets    bigint(12)  DEFAULT NULL,
176 
  `nasipaddress`    varchar(15) COLLATE utf8_bin DEFAULT NULL,
 184 
  nasipaddress    varchar(15) COLLATE utf8_bin DEFAULT NULL,
177 
  PRIMARY KEY (`mtotacctid`),
 185 
  PRIMARY KEY (mtotacctid),
178 
  KEY `username` (`username`),
 186 
  KEY username (username),
179 
  KEY `acctdate` (`acctdate`),
 187 
  KEY acctdate (acctdate),
180 
  KEY `UserOnDate` (`username`,`acctdate`),
 188 
  KEY UserOnDate (username,acctdate),
181 
  KEY `nasipaddress` (`nasipaddress`)
 189 
  KEY nasipaddress (nasipaddress)
182 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 190 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
183 
 
 191 
 
184 
--
 192 
--
185 
-- Table structure for table `totacct`
 193 
-- Table structure for table `totacct`
186 
--
 194 
--
187 
 
 195 
 
188 
DROP TABLE IF EXISTS `totacct`;
 196 
DROP TABLE IF EXISTS totacct;
189 
CREATE TABLE `totacct` (
 197 
CREATE TABLE totacct (
190 
  `totacctid`       bigint(21)  NOT NULL AUTO_INCREMENT,
 198 
  totacctid       bigint(21)  NOT NULL AUTO_INCREMENT,
191 
  `username`        varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 199 
  username        varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
192 
  `acctdate`        date        NOT NULL DEFAULT '0000-00-00',
 200 
  acctdate        date        NOT NULL DEFAULT '0000-00-00',
193 
  `connnum`         bigint(12)  DEFAULT NULL,
 201 
  connnum         bigint(12)  DEFAULT NULL,
194 
  `conntotduration` bigint(12)  DEFAULT NULL,
 202 
  conntotduration bigint(12)  DEFAULT NULL,
195 
  `connmaxduration` bigint(12)  DEFAULT NULL,
 203 
  connmaxduration bigint(12)  DEFAULT NULL,
196 
  `connminduration` bigint(12)  DEFAULT NULL,
 204 
  connminduration bigint(12)  DEFAULT NULL,
197 
  `inputoctets`     bigint(12)  DEFAULT NULL,
 205 
  inputoctets     bigint(12)  DEFAULT NULL,
198 
  `outputoctets`    bigint(12)  DEFAULT NULL,
 206 
  outputoctets    bigint(12)  DEFAULT NULL,
199 
  `nasipaddress`    varchar(15) COLLATE utf8_bin DEFAULT NULL,
 207 
  nasipaddress    varchar(15) COLLATE utf8_bin DEFAULT NULL,
200 
  PRIMARY KEY (`totacctid`),
 208 
  PRIMARY KEY (totacctid),
201 
  KEY `username` (`username`),
 209 
  KEY username (username),
202 
  KEY `acctdate` (`acctdate`),
 210 
  KEY acctdate (acctdate),
203 
  KEY `UserOnDate` (`username`,`acctdate`),
 211 
  KEY UserOnDate (username,acctdate),
204 
  KEY `nasipaddress` (`nasipaddress`),
 212 
  KEY nasipaddress (nasipaddress),
205 
  KEY `nasipaddressOnDate` (`acctdate`,`nasipaddress`)
 213 
  KEY nasipaddressOnDate (acctdate,nasipaddress)
206 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 214 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
207 
 
 215 
 
208 
--
 216 
--
209 
-- Table structure for table `userinfo`
 217 
-- Table structure for table `userinfo`
210 
--
 218 
--
211 
 
 219 
 
212 
DROP TABLE IF EXISTS `userinfo`;
 220 
DROP TABLE IF EXISTS userinfo;
213 
CREATE TABLE `userinfo` (
 221 
CREATE TABLE userinfo (
214 
  `id`         int(10)      NOT NULL AUTO_INCREMENT,
 222 
  id         int(10)      NOT NULL AUTO_INCREMENT,
215 
  `username`   varchar(64)  COLLATE utf8_bin DEFAULT NULL,
 223 
  username   varchar(64)  COLLATE utf8_bin DEFAULT NULL,
216 
  `name`       varchar(200) COLLATE utf8_bin DEFAULT NULL,
 224 
  name       varchar(200) COLLATE utf8_bin DEFAULT NULL,
217 
  `mail`       varchar(200) COLLATE utf8_bin DEFAULT NULL,
 225 
  mail       varchar(200) COLLATE utf8_bin DEFAULT NULL,
218 
  `department` varchar(200) COLLATE utf8_bin DEFAULT NULL,
 226 
  department varchar(200) COLLATE utf8_bin DEFAULT NULL,
219 
  `workphone`  varchar(200) COLLATE utf8_bin DEFAULT NULL,
 227 
  workphone  varchar(200) COLLATE utf8_bin DEFAULT NULL,
220 
  `homephone`  varchar(200) COLLATE utf8_bin DEFAULT NULL,
 228 
  homephone  varchar(200) COLLATE utf8_bin DEFAULT NULL,
221 
  `mobile`     varchar(200) COLLATE utf8_bin DEFAULT NULL,
 229 
  mobile     varchar(200) COLLATE utf8_bin DEFAULT NULL,
222 
  PRIMARY KEY (`id`),
 230 
  PRIMARY KEY (id),
223 
  KEY `username` (`username`),
 231 
  KEY username (username),
224 
  KEY `department` (`department`)
 232 
  KEY department (department)
225 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 233 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;