core.tsql

@PRELOAD@

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

	Username		VARCHAR(255) NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0',

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


/* User attributes */
CREATE TABLE @PREFIX@user_attributes (
	ID			@SERIAL_TYPE@,

	UserID			@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 (UserID) REFERENCES @PREFIX@users(ID)
) @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',

	Comment			VARCHAR(1024)

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


/* Group attributes */
CREATE TABLE @PREFIX@group_attributes (
	ID			@SERIAL_TYPE@,

	GroupID			@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 (GroupID) REFERENCES @PREFIX@groups(ID)
) @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);


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

	EventTimestamp		DATETIME NOT NULL,

	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);

/* FIXME: indexes */