core.tsql 2.81 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
@PRELOAD@

/* Users */
CREATE TABLE @PREFIX@users (
	ID			@SERIAL_TYPE@,

	Username		VARCHAR(255) NOT NULL,

	Description		TEXT,

Nigel Kukard's avatar
Nigel Kukard committed
11
	Disabled		SMALLINT NOT NULL DEFAULT '0',
12
13
14
15
16
17

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


Nigel Kukard's avatar
Nigel Kukard committed
18
19
/* User attributes */
CREATE TABLE @PREFIX@user_attributes (
20
21
	ID			@SERIAL_TYPE@,

22
23
	UserID			@SERIAL_REF_TYPE@ NOT NULL,

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

Nigel Kukard's avatar
Nigel Kukard committed
28
	Disabled		SMALLINT NOT NULL DEFAULT '0',
29

30
	FOREIGN KEY (UserID) REFERENCES @PREFIX@users(ID)
31
32
33
34
35
36
37
38
39
40
41
42
43
) @CREATE_TABLE_SUFFIX@;


/* 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
49


Nigel Kukard's avatar
Nigel Kukard committed
50
/* Group attributes */
51
CREATE TABLE @PREFIX@group_attributes (
52
53
	ID			@SERIAL_TYPE@,

54
55
	GroupID			@SERIAL_REF_TYPE@ NOT NULL,

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

Nigel Kukard's avatar
Nigel Kukard committed
60
	Disabled		SMALLINT NOT NULL DEFAULT '0',
61

62
	FOREIGN KEY (GroupID) REFERENCES @PREFIX@groups(ID)
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
) @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);


Robert Anderson's avatar
Robert Anderson committed
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
/* Accounting */
CREATE TABLE @PREFIX@accounting (
	ID			@SERIAL_TYPE@,

	Username		VARCHAR(255) NOT NULL,

	ServiceType		INT UNSIGNED NOT NULL,

	FramedProtocol		INT UNSIGNED NOT NULL, 

	NASPort			VARCHAR(255) NOT NULL,
		
	NASPortType		INT UNSIGNED NOT NULL,

	CallingStationID	VARCHAR(255) NOT NULL, 

	CalledStationID		VARCHAR(255) NOT NULL,

	NASPortID		VARCHAR(255) NOT NULL,

	AcctSessionID		VARCHAR(255) NOT NULL,
	
	FramedIPAddress		VARCHAR(16) NOT NULL,

	AcctAuthentic		INT UNSIGNED NOT NULL,
108

Robert Anderson's avatar
Robert Anderson committed
109
	EventTimestamp		INT UNSIGNED NOT NULL,
110

Robert Anderson's avatar
Robert Anderson committed
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
	NASIdentifier		VARCHAR(255) NOT NULL,	

	NASIPAddress		VARCHAR(16) NOT NULL,

	AcctDelayTime		INT UNSIGNED NOT NULL,

	AcctSessionTime		INT UNSIGNED NOT NULL,

	AcctInputOctets		INT UNSIGNED NOT NULL,

	AcctInputGigawords	INT UNSIGNED NOT NULL,

	AcctInputPackets	INT UNSIGNED NOT NULL,	

	AcctOutputOctets	INT UNSIGNED NOT NULL,

	AcctOutputGigawords	INT UNSIGNED NOT NULL,

	AcctOutputPackets	INT UNSIGNED NOT NULL,

	AcctStatusType		INT UNSIGNED NOT NULL,

	AcctTerminateCause	INT UNSIGNED NOT NULL
) @CREATE_TABLE_SUFFIX@;
CREATE INDEX @PREFIX@accounting_idx1 ON @PREFIX@accounting (Username);
136

Robert Anderson's avatar
Robert Anderson committed
137
/* FIXME: indexes */