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,

Nigel Kukard's avatar
Nigel Kukard committed
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);

Nigel Kukard's avatar
Nigel Kukard committed
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),

Nigel Kukard's avatar
Nigel Kukard committed
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@;
Nigel Kukard's avatar
Nigel Kukard committed
47
CREATE INDEX @PREFIX@groups_idx1 ON @PREFIX@groups (Name);
48

Nigel Kukard's avatar
Nigel Kukard committed
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),

Nigel Kukard's avatar
Nigel Kukard committed
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@;



Robert Anderson's avatar
Robert Anderson committed
194
195
196
197
/* Accounting */
CREATE TABLE @PREFIX@accounting (
	ID			@SERIAL_TYPE@,

198
	Username		VARCHAR(255),
Robert Anderson's avatar
Robert Anderson committed
199

200
	ServiceType		@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
201

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

204
	NASPort			VARCHAR(255),
205

206
	NASPortType		@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
207

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

210
	CalledStationID		VARCHAR(255),
Robert Anderson's avatar
Robert Anderson committed
211

212
	NASPortID		VARCHAR(255),
Robert Anderson's avatar
Robert Anderson committed
213

214
	AcctSessionID		VARCHAR(255),
215

216
	FramedIPAddress		VARCHAR(16),
Robert Anderson's avatar
Robert Anderson committed
217

218
	AcctAuthentic		@INT_UNSIGNED@,
219

220
	EventTimestamp		DATETIME,
221

222
	NASIdentifier		VARCHAR(255),
Robert Anderson's avatar
Robert Anderson committed
223

224
	NASIPAddress		VARCHAR(16),
Robert Anderson's avatar
Robert Anderson committed
225

226
	AcctDelayTime		@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
227

228
	AcctSessionTime		@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
229

230
	AcctInputOctets		@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
231

232
	AcctInputGigawords	@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
233

234
	AcctInputPackets	@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
235

236
	AcctOutputOctets	@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
237

238
	AcctOutputGigawords	@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
239

240
	AcctOutputPackets	@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
241

242
	AcctStatusType		@INT_UNSIGNED@,
Robert Anderson's avatar
Robert Anderson committed
243

244
245
246
	AcctTerminateCause	@INT_UNSIGNED@,

	PeriodKey		VARCHAR(255)
Robert Anderson's avatar
Robert Anderson committed
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@;