core.tsql 6.12 KB
Newer Older
1 2
@PRELOAD@

3 4


5 6 7 8 9 10
/* Users */
CREATE TABLE @PREFIX@users (
	ID			@SERIAL_TYPE@,

	Username		VARCHAR(255) NOT NULL,

11
	Disabled		SMALLINT NOT NULL DEFAULT '0',
12 13 14 15 16

	UNIQUE (Username)
) @CREATE_TABLE_SUFFIX@;
CREATE INDEX @PREFIX@users_idx1 ON @PREFIX@users (Username);

17 18
/* User attributes */
CREATE TABLE @PREFIX@user_attributes (
19 20
	ID			@SERIAL_TYPE@,

21 22
	UserID			@SERIAL_REF_TYPE@ NOT NULL,

23
	Name			VARCHAR(255) NOT NULL,
24
	Operator		VARCHAR(4) NOT NULL,
25 26
	Value			VARCHAR(255),

27
	Disabled		SMALLINT NOT NULL DEFAULT '0',
28

29
	FOREIGN KEY (UserID) REFERENCES @PREFIX@users(ID)
30 31 32
) @CREATE_TABLE_SUFFIX@;


33

34 35 36 37 38 39 40 41 42 43
/* Groups */
CREATE TABLE @PREFIX@groups (
	ID			@SERIAL_TYPE@,

	Name			VARCHAR(255) NOT NULL,

	Priority		SMALLINT NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0',

44
	Comment			VARCHAR(1024)
45 46

)  @CREATE_TABLE_SUFFIX@;
47
CREATE INDEX @PREFIX@groups_idx1 ON @PREFIX@groups (Name);
48

49
/* Group attributes */
50
CREATE TABLE @PREFIX@group_attributes (
51 52
	ID			@SERIAL_TYPE@,

53 54
	GroupID			@SERIAL_REF_TYPE@ NOT NULL,

55
	Name			VARCHAR(255) NOT NULL,
56
	Operator		VARCHAR(4) NOT NULL,
57 58
	Value			VARCHAR(255),

59
	Disabled		SMALLINT NOT NULL DEFAULT '0',
60

61
	FOREIGN KEY (GroupID) REFERENCES @PREFIX@groups(ID)
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
) @CREATE_TABLE_SUFFIX@;

/* User to group mapping */
CREATE TABLE @PREFIX@users_to_groups (
	ID			@SERIAL_TYPE@,

	UserID			@SERIAL_REF_TYPE@ NOT NULL,
	GroupID			@SERIAL_REF_TYPE@ NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0',
	Comment			VARCHAR(1024),

	UNIQUE (UserID,GroupID),
	FOREIGN KEY (UserID) REFERENCES @PREFIX@users(ID),
	FOREIGN KEY (GroupID) REFERENCES @PREFIX@groups(ID)
)  @CREATE_TABLE_SUFFIX@;
CREATE INDEX @PREFIX@users_to_groups_idx1 ON @PREFIX@users_to_groups (UserID,GroupID);


81 82 83 84 85 86 87

/* Realms */
CREATE TABLE @PREFIX@realms (
	ID			@SERIAL_TYPE@,

	Name			VARCHAR(255) NOT NULL,

Robert Anderson's avatar
Robert Anderson committed
88
	Disabled		SMALLINT NOT NULL DEFAULT '0'
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106

)  @CREATE_TABLE_SUFFIX@;
CREATE INDEX @PREFIX@realms_idx1 ON @PREFIX@realms (Name);

/* Realm attributes */
CREATE TABLE @PREFIX@realm_attributes (
	ID			@SERIAL_TYPE@,

	RealmID			@SERIAL_REF_TYPE@ NOT NULL,

	Name			VARCHAR(255) NOT NULL,
	Operator		VARCHAR(4) NOT NULL,
	Value			VARCHAR(255),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	FOREIGN KEY (RealmID) REFERENCES @PREFIX@realms(ID)
) @CREATE_TABLE_SUFFIX@;
107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150

/* Client to realm mapping */
CREATE TABLE @PREFIX@clients_to_realms (
	ID			@SERIAL_TYPE@,

	ClientID		@SERIAL_REF_TYPE@ NOT NULL,
	RealmID			@SERIAL_REF_TYPE@ NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0',
	Comment			VARCHAR(1024),

	UNIQUE (ClientID,RealmID),
	FOREIGN KEY (ClientID) REFERENCES @PREFIX@clients(ID),
	FOREIGN KEY (RealmID) REFERENCES @PREFIX@realms(ID)
)  @CREATE_TABLE_SUFFIX@;



/* Clients */
CREATE TABLE @PREFIX@clients (
	ID			@SERIAL_TYPE@,

	Name			VARCHAR(255) NOT NULL,
	AccessList		VARCHAR(255),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	UNIQUE (Name)
) @CREATE_TABLE_SUFFIX@;

/* Client attributes */
CREATE TABLE @PREFIX@client_attributes (
	ID			@SERIAL_TYPE@,

	ClientID		@SERIAL_REF_TYPE@ NOT NULL,

	Name			VARCHAR(255) NOT NULL,
	Operator		VARCHAR(4) NOT NULL,
	Value			VARCHAR(255),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	FOREIGN KEY (ClientID) REFERENCES @PREFIX@clients(ID)
) @CREATE_TABLE_SUFFIX@;
151 152 153



Robert Anderson's avatar
Robert Anderson committed
154 155
/* Topups */
CREATE TABLE @PREFIX@topups (
156 157
	ID			@SERIAL_TYPE@,

Robert Anderson's avatar
Robert Anderson committed
158
	UserID			@SERIAL_REF_TYPE@ NOT NULL,
159

Robert Anderson's avatar
Robert Anderson committed
160
	Timestamp		DATETIME,
161

162
				/* 1 = traffic topup, 2 = uptime topup, 4 = auto-topup */
Robert Anderson's avatar
Robert Anderson committed
163 164 165 166 167 168
	Type			@INT_UNSIGNED@,

	ValidFrom		DATETIME,
	ValidTo			DATETIME,

	Value			@INT_UNSIGNED@,
169 170

	Depleted		SMALLINT NOT NULL DEFAULT '0',
171
	SMAdminDepletedOn	DATETIME,
172

Robert Anderson's avatar
Robert Anderson committed
173
	FOREIGN KEY (UserID) REFERENCES @PREFIX@users(ID)
174 175
) @CREATE_TABLE_SUFFIX@;

Robert Anderson's avatar
Robert Anderson committed
176 177
/* Topups Summary */
CREATE TABLE @PREFIX@topups_summary (
178 179
	ID			@SERIAL_TYPE@,

Robert Anderson's avatar
Robert Anderson committed
180
	TopupID			@SERIAL_REF_TYPE@ NOT NULL,
181

Nigel Kukard's avatar
Nigel Kukard committed
182
	PeriodKey		VARCHAR(255) NOT NULL,
183

Robert Anderson's avatar
Robert Anderson committed
184
	Balance			@INT_UNSIGNED@,
185

Robert Anderson's avatar
Robert Anderson committed
186
	Depleted		SMALLINT NOT NULL DEFAULT '0',
187
	SMAdminDepletedOn	DATETIME,
188

Robert Anderson's avatar
Robert Anderson committed
189
	FOREIGN KEY (TopupID) REFERENCES @PREFIX@topups(ID)
190 191 192 193
) @CREATE_TABLE_SUFFIX@;



194 195 196 197
/* Accounting */
CREATE TABLE @PREFIX@accounting (
	ID			@SERIAL_TYPE@,

198
	Username		VARCHAR(255),
199

200
	ServiceType		@INT_UNSIGNED@,
201

Nigel Kukard's avatar
Nigel Kukard committed
202
	FramedProtocol		@INT_UNSIGNED@,
203

204
	NASPort			VARCHAR(255),
205

206
	NASPortType		@INT_UNSIGNED@,
207

Nigel Kukard's avatar
Nigel Kukard committed
208
	CallingStationID	VARCHAR(255),
209

210
	CalledStationID		VARCHAR(255),
211

212
	NASPortID		VARCHAR(255),
213

214
	AcctSessionID		VARCHAR(255),
215

216
	FramedIPAddress		VARCHAR(16),
217

218
	AcctAuthentic		@INT_UNSIGNED@,
219

220
	EventTimestamp		DATETIME,
221

222
	NASIdentifier		VARCHAR(255),
223

224
	NASIPAddress		VARCHAR(16),
225

226
	AcctDelayTime		@INT_UNSIGNED@,
227

228
	AcctSessionTime		@INT_UNSIGNED@,
229

230
	AcctInputOctets		@INT_UNSIGNED@,
231

232
	AcctInputGigawords	@INT_UNSIGNED@,
233

234
	AcctInputPackets	@INT_UNSIGNED@,
235

236
	AcctOutputOctets	@INT_UNSIGNED@,
237

238
	AcctOutputGigawords	@INT_UNSIGNED@,
239

240
	AcctOutputPackets	@INT_UNSIGNED@,
241

242
	AcctStatusType		@INT_UNSIGNED@,
243

244 245 246
	AcctTerminateCause	@INT_UNSIGNED@,

	PeriodKey		VARCHAR(255)
247 248
) @CREATE_TABLE_SUFFIX@;
CREATE INDEX @PREFIX@accounting_idx1 ON @PREFIX@accounting (Username);
249
CREATE INDEX @PREFIX@accounting_idx2 ON @PREFIX@accounting (PeriodKey);
250
/* accounting_stop_status_query */
251
CREATE INDEX @PREFIX@accounting_idx4 ON @PREFIX@accounting (Username,AcctSessionID,NASIPAddress,NASPort);
252
/* accounting_update_query */
253
CREATE INDEX @PREFIX@accounting_idx5 ON @PREFIX@accounting (Username,AcctSessionID,NASIPAddress,NASPort,PeriodKey);
254 255 256
/* Index for the EventTimestamp */
CREATE INDEX @PREFIX@accounting_idx7 ON @PREFIX@accounting (EventTimestamp);
CREATE INDEX @PREFIX@accounting_idx8 ON @PREFIX@accounting (Username,EventTimestamp);
Robert Anderson's avatar
Robert Anderson committed
257 258 259 260 261 262 263 264 265



/* Accounting Summary */
CREATE TABLE @PREFIX@accounting_summary (
	ID			@SERIAL_TYPE@,

	Username		VARCHAR(255),

266
	PeriodKey		VARCHAR(255),
Robert Anderson's avatar
Robert Anderson committed
267

268
	TotalSessionTime	@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
269

270
	TotalInput		@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
271

272
	TotalOutput		@INT_UNSIGNED@
Robert Anderson's avatar
Robert Anderson committed
273
) @CREATE_TABLE_SUFFIX@;
274

275 276 277
CREATE INDEX @PREFIX@accounting_summary_idx1 ON @PREFIX@accounting_summary (Username);
CREATE INDEX @PREFIX@accounting_summary_idx2 ON @PREFIX@accounting_summary (PeriodKey);
CREATE INDEX @PREFIX@accounting_summary_idx3 ON @PREFIX@accounting_summary (Username,PeriodKey);
278 279 280 281


/* Users data */
CREATE TABLE @PREFIX@users_data (
Nigel Kukard's avatar
Nigel Kukard committed
282
	ID				@SERIAL_TYPE@,
283 284 285 286 287 288 289 290 291 292

	UserID			@INT_UNSIGNED@,

	LastUpdated		DATETIME,

	Name			VARCHAR(255),

	Value			VARCHAR(255),

	UNIQUE (UserID,Name)
Nigel Kukard's avatar
Nigel Kukard committed
293
) @CREATE_TABLE_SUFFIX@;