navprofiles.sql

Network Administration Visualized 网络管理可视化源码

                    0 No queue, imediate delivery of alert
                    1 Enqueued (daily), daily delivery of alert digest
                    2 Enqueued (weekly), weekly delivery of alert digest
                    3 Enqueued (profile switch), alert is enqueued, and every time alertengine detects that
                a new time period is entered, this queue will be checked and delivered to the user or another
                queue if it match the new time periods alert settings.
*/
CREATE TABLE Varsle (
	alarmadresseid integer NOT NULL,
	tidsperiodeid integer NOT NULL,
	utstyrgruppeid integer NOT NULL,
	vent integer,

	CONSTRAINT varsleadresse_pk PRIMARY KEY(alarmadresseid, tidsperiodeid, utstyrgruppeid),
	CONSTRAINT alarmadresse_eksisterer
		FOREIGN KEY (alarmadresseid) REFERENCES Alarmadresse(id)
			ON DELETE CASCADE
			ON UPDATE CASCADE,

	CONSTRAINT tidsperiode_eksisterer
		FOREIGN KEY(tidsperiodeid) REFERENCES Tidsperiode(id)
			ON DELETE CASCADE
			ON UPDATE CASCADE,
	CONSTRAINT utstyrgruppe_eksisterer
		FOREIGN KEY(utstyrgruppeid) REFERENCES Utstyrgruppe(id)
			ON DELETE CASCADE
			ON UPDATE CASCADE  
);

/*
-- 11 RETTIGHET

Permissions.

This table contatins a relation from a user group to an equipment group. It gives all members of the 
actual user group permission to set up notofication for alerts matching this filter. The relation 
usergroup <-> equipment group is many to many.
*/
CREATE TABLE Rettighet (
       accountgroupid integer NOT NULL,
       utstyrgruppeid integer NOT NULL,
       CONSTRAINT rettighet_pk PRIMARY KEY(accountgroupid, utstyrgruppeid),
       CONSTRAINT accountgroup_exist
		  FOREIGN KEY(accountgroupid) REFERENCES accountgroup(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE,
       CONSTRAINT utstyrgruppe_eksisterer
		  FOREIGN KEY(utstyrgruppeid) REFERENCES Utstyrgruppe(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);

/*
-- 12 BRUKERRETTIGHET

This is a permission relation table reserved for future use.

This adds permission from a user to quipmentgroups. In webfrontend, adding permission is still only available per user group. This is a suggested feature to be added later.. [10. juni 2003]

*/
CREATE TABLE BrukerRettighet (
       accountid integer NOT NULL,
       utstyrgruppeid integer NOT NULL,
       
       CONSTRAINT brukerrettighet_pk PRIMARY KEY(accountid, utstyrgruppeid),
       CONSTRAINT account_exist
		  FOREIGN KEY(accountid) REFERENCES Account(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE,
       CONSTRAINT utstyrgruppe_eksisterer
		  FOREIGN KEY(utstyrgruppeid) REFERENCES Utstyrgruppe(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);

/*
-- 13 DEFAULTUTSTYR

Default equipment is a table adding default quipmentgroups to user groups. Default equipment groups will be avaibale for the user through the webinterface to use for notifications/alerts.

The relation can be only to equipment groups shared by administrators, not to equipment groups owned by someone.
*/
CREATE TABLE DefaultUtstyr (
       accountgroupid integer NOT NULL,
       utstyrgruppeid integer NOT NULL,

       CONSTRAINT defaultutstyr_pk PRIMARY KEY (accountgroupid, utstyrgruppeid),
       CONSTRAINT utstyrgruppe_eksisterer
		  FOREIGN KEY(utstyrgruppeid) REFERENCES Utstyrgruppe(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE,
       CONSTRAINT accountgroup_exist
		  FOREIGN KEY(accountgroupid) REFERENCES AccountGroup(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);




/*
-- 14 UTSTYRFILTER

Equipment filter, is a list of matches. An equipment filter will for a given alarm evaluate to either true
or false. For a filter to evaluate as true, all related filter matches has to evaluate to true.

The equipment filter could either be owned by an user, or shared among administrators. When accountid is null, the filter is shared among adminstrators.

navn		The name of the equipmentfilter
*/
CREATE SEQUENCE utstyrfilter_id_seq START 1000;
CREATE TABLE Utstyrfilter (
       id integer NOT NULL DEFAULT nextval('utstyrfilter_id_seq'),
       accountid integer,
       navn varchar,

       CONSTRAINT utstyrfilter_pk PRIMARY KEY(id),
       CONSTRAINT user_Exist
		  FOREIGN KEY(accountid) REFERENCES Account(id)
		  ON DELETE SET NULL 
		  ON UPDATE CASCADE
);

/*
-- 15 GRUPPETILFILTER

This table is an realtion from equipment group to eqipment filter. It is an enumerated relation, which
means that each row has a priority.

inkluder	include. If true the related filter is included in the group.
positiv		positive. If this is false, the filter is inverted, which implies that true is false, and
            false is true.
prioritet	priority. The list will be traversed in ascending priority order. Which means that the higher 
            number, the higher priority.
*/
CREATE TABLE GruppeTilFilter (
       inkluder boolean NOT NULL DEFAULT true,
       positiv boolean NOT NULL DEFAULT true,
       prioritet integer NOT NULL,
       utstyrfilterid integer NOT NULL,
       utstyrgruppeid integer NOT NULL,

       CONSTRAINT gruppetilfilter_pk PRIMARY KEY(utstyrfilterid, utstyrgruppeid),
       CONSTRAINT utstyrgruppeid_eksisterer 
		  FOREIGN KEY(utstyrgruppeid) REFERENCES Utstyrgruppe(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE,
       CONSTRAINT utstyrfilter_eksisterer 
		  FOREIGN KEY(utstyrfilterid) REFERENCES Utstyrfilter(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);

/*
-- 16 MATCHFIELD

Matchfield, is a relation to the Manage database. Each filtermatch could be related to a single matchfield.

name		The name of the filter
descr		Longer description
valuehelp	Help text in html about how to choose values.
valueid		Realtion to manage: table.field that defines the id
valuename	Realtion to manage: table.field describing the attribute name
valuecategory	Realtion to manage: table.field that categorize values
valuesort	Realtion to manage: table.field by which the values will be sorted in the option list.
listlimit	Max number of values shown in the drop down list.
datatype	Defining the datatype, this is a helper attribute for the alertengine.
showlist	boolean, true: show list of values from manage. false: show html input field.
*/
CREATE SEQUENCE matchfield_id_seq START 1000;
CREATE TABLE MatchField (
    matchfieldid integer NOT NULL DEFAULT nextval('matchfield_id_seq'),
    name varchar,
    descr varchar,
    valuehelp varchar,
    valueid varchar,
    valuename varchar,
    valuecategory varchar,
    valuesort varchar,
    listlimit integer DEFAULT 300,
    datatype integer NOT NULL DEFAULT 0,
    showlist boolean,
    
    CONSTRAINT matchfield_pk PRIMARY KEY(matchfieldid)
);

/*
-- 17 FILTERMATCH

Filtermatch is a single condition. It consist of a matchfield, a operator and a value.

matchfelt	This is a relation to matchfield
matchtype	This specifies the operator used. This a static list.
                Temporarily this list is used in the webinterface:
                    $type[0] = gettext('er lik');
                    $type[1] = gettext('er større enn');
                    $type[2] = gettext('er større eller lik');
                    $type[3] = gettext('er mindre enn');
                    $type[4] = gettext('er mindre eller lik');
                    $type[5] = gettext('er ulik');
                    $type[6] = gettext('starter med');
                    $type[7] = gettext('slutter med');
                    $type[8] = gettext('inneholder');
                    $type[9] = gettext('regexp');
                    $type[10] = gettext('wildcard (? og *)');
verdi		The value
*/
CREATE SEQUENCE filtermatch_id_seq START 1000;
CREATE TABLE FilterMatch (
       id integer NOT NULL DEFAULT nextval('filtermatch_id_seq'),
       utstyrfilterid integer NOT NULL,
       matchfelt integer NOT NULL,
       matchtype integer,
       verdi varchar,
       
    CONSTRAINT filtermatch_pk PRIMARY KEY(id),
    CONSTRAINT matchfield_Exist 
        FOREIGN KEY(matchfelt) REFERENCES matchfield(matchfieldid)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT utstyrfilter_eksisterer
        FOREIGN KEY(utstyrfilterid) REFERENCES Utstyrfilter(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);


/*
-- 18 OPERATOR

Operator, this is a list related to each matchfield, specifying which operator should be availbale to
choose from, form the given matchfield.

*/
CREATE SEQUENCE operator_id_seq START 1000;
CREATE TABLE Operator (
    operatorid integer NOT NULL DEFAULT nextval('operator_id_seq'),
    matchfieldid integer NOT NULL,
    
    CONSTRAINT operator_pk PRIMARY KEY (operatorid, matchfieldid),
    CONSTRAINT matchfield_eksisterer 
	FOREIGN KEY(matchfieldid) REFERENCES MatchField(matchfieldid)
            ON DELETE CASCADE
            ON UPDATE CASCADE
);


/*
-- 19 LOGG

Logg is a table where log events are collected.

type	type of log event.
        Static list:
            1 User logs in
            2 User logs out
            3 New
            4 Delete
            5 Edit
            6 New
            7 Delete
            8 Edit
            9 Profile switch via wap
tid	the time when event took place
descr	a general textstring, could be used to describe the event

*/
CREATE SEQUENCE logg_id_seq START 1000;
CREATE TABLE Logg (
    id integer NOT NULL DEFAULT nextval('logg_id_seq'),
       accountid integer NOT NULL,
       type integer,
       tid timestamptz,       
       descr varchar,
       
       CONSTRAINT loggid_pk PRIMARY KEY(id),     
       
       CONSTRAINT user_exist
		  FOREIGN KEY(accountid) REFERENCES Account(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);