core.tsql

@PRELOAD@


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

	Username		VARCHAR(255) NOT NULL,

	Description		TEXT,

	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@,

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

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	UNIQUE (Name)
) @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),

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


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

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

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	UNIQUE (Name)
) @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);