navprofiles.sql

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

/*
=============================================
        NAVprofiles
    SQL Initialization script
    
    Run the command:
    psql navprofiles -f navprofiles.sql
    
    
	!! WARNING !!
	This SQL script is encoded as unicode (UTF-8),
	before you do changes and commit, be 100% sure
	that your editor do not mess it up.
    
    Check 1 : These norwegian letters looks nice:
    ! æøåÆØÅ !
    Check 2 : This is the Euro currency sign: 
    ! € !
=============================================
*/


/*
------------------------------------------------------
 TABLE DEFINITIONS
------------------------------------------------------
*/

/*
-- 1 ACCOUNT

Table for users

login		usally 3-8 characters
name		Real name of user
password	password for local authentication
ext_sync	external syncronization, reserved for future use, 
            null means local authentication
*/
CREATE SEQUENCE account_id_seq START 1000;
CREATE TABLE Account (
    id integer NOT NULL DEFAULT nextval('account_id_seq'),
    login varchar CONSTRAINT brukernavn_uniq UNIQUE NOT NULL,
    name varchar DEFAULT 'Noname',
    password varchar,
    ext_sync varchar,

    CONSTRAINT account_pk PRIMARY KEY (id)
);
CREATE INDEX account_idx ON Account(login);


/*
-- 2 ACCOUNTGROUP

Table for usergroup

name		Name of usergroup
descr		Longer description
*/
CREATE SEQUENCE accountgroup_id_seq START 1000;
CREATE TABLE AccountGroup (
       id integer NOT NULL DEFAULT nextval('accountgroup_id_seq'),
       name varchar DEFAULT 'Noname',
       descr varchar,

       CONSTRAINT accountgroup_pk PRIMARY KEY (id)
);


/*
-- 3 ACCOUNTINGROUP

Relation between user and usergroup. A user can be member of arbitrary many usergroups. 

*/
CREATE TABLE AccountInGroup (
       accountid integer NOT NULL,
       groupid integer NOT NULL,
       
       CONSTRAINT accountingroup_pk PRIMARY KEY(accountid, groupid),
       CONSTRAINT account_exist 
		  FOREIGN KEY(accountid) REFERENCES Account(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE, 
       CONSTRAINT group_exist 
		  FOREIGN KEY(groupid) REFERENCES Accountgroup(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);

/*
-- 4 ACCOUNTPROPERTY

A general table related to a single user. An user can have many account properties.
This is a general way that allows applications to add key-value pairs to a user.

In example NAVprofile web frontend use key 'language' to save language preferences for an user.
*/
CREATE TABLE AccountProperty (
    accountid integer,
    property varchar,
    value varchar,
    
    CONSTRAINT account_exist 
        FOREIGN KEY(accountid) REFERENCES Account(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE  
);

/*
-- 5 ALARMADRESSE

Addresses related to a user. A user can have arbitrary many addresses.

type		Specifies what kind of address this is.
                Predefined values:
                    1 e-mail [e-mail]
                    2 sms 99887766
                    3 irc nick![e-mail]
                    4 icq 123456789
adrese		The address
*/
CREATE SEQUENCE alarmadresse_id_seq START 1000;
CREATE TABLE Alarmadresse (
       id integer NOT NULL DEFAULT nextval('alarmadresse_id_seq'),
       accountid integer NOT NULL,
       type integer NOT NULL,
       adresse varchar,

       CONSTRAINT alarmadresse_pk PRIMARY KEY(id),
       CONSTRAINT account_exist 
		  FOREIGN KEY(accountid) REFERENCES Account(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);


/*
-- 6 BRUKERPROFIL

A table for alertprofile. Only one profile can be active simultanously. It is possible that zero profiles are active. One user may have arbitrary many profiles. A profile is a composistion of a set of timeperiods which define a timetable.

navn		The name of the profile
tid		Related to queueing. When daily queueing is selected, this attrubute specify when on a day
            enqueued alerts will be sent.
ukedag		Related to queueing. When weekly queueing is selected, this attribute specify which 
            weekday enqueued alerts will be sent on. 0 is monday, 6 is sunday.
uketid		Related to queueing. When weekly queueing is selected, this attribute specify which time
            on the day enqueued alerts will be sent.

*/
CREATE SEQUENCE brukerprofil_id_seq START 1000;
CREATE TABLE Brukerprofil (
       id integer NOT NULL DEFAULT nextval('brukerprofil_id_seq'),
       accountid integer NOT NULL,
       navn varchar,
       tid time NOT NULL DEFAULT '08:00:00',
       ukedag integer NOT NULL DEFAULT 0,
       uketid time NOT NULL DEFAULT '08:30:00',

       CONSTRAINT brukerprofil_pk PRIMARY KEY(id),
       CONSTRAINT bruker_eksisterer
		  FOREIGN KEY(accountid) REFERENCES Account(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);


/*
-- 7 PREFERENCE

queuelength	Related to queuing. When user select queueing this attributes speficies the highest number of
            days the user is allowed to queue an alert. Alerts that are older will be deleted by the 
            alertengine.
            
admin		an integer specifying wether the user is administrator or not.
            Defined values are (0 disabled, 1 normal user, 100 administrator)
            
activeprofile	Defines the active profile at the moment. null means no profiles is active.

sms		Is the user allowed to get alerts on sms.
*/
CREATE TABLE Preference (
    accountid integer NOT NULL,
    queuelength interval,
/*    admin integer NOT NULL DEFAULT 1,       */
    activeprofile integer,
/*    sms boolean NOT NULL DEFAULT true, */
    
    lastsentday timestamp,
    lastsentweek timestamp,

   CONSTRAINT preference_pk PRIMARY KEY(accountid),

    CONSTRAINT account_Exist
        FOREIGN KEY(accountid) REFERENCES Account(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
                  
    CONSTRAINT brukerprofil_eksisterer
        FOREIGN KEY(activeprofile) REFERENCES Brukerprofil(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Trigger function to copy the preference row of the default user
-- whenever a new account is inserted.  I would like to insert a
-- composite row variable, but couldn't find any way to do so, so this
-- function needs to be updated whenever the schema of the preference
-- table is updated!  We don't attach the trigger until after we
-- insert some default accounts and privileges (further down in this
-- script)
CREATE OR REPLACE FUNCTION copy_default_preferences () RETURNS TRIGGER AS '
  DECLARE
    pref preference%ROWTYPE;
  BEGIN
    SELECT INTO pref * FROM preference WHERE accountid = 0;
    pref.accountid := NEW.id;
    INSERT INTO preference (accountid, queuelength, activeprofile, lastsentday, lastsentweek)
      VALUES (pref.accountid, pref.queuelength, pref.activeprofile, pref.lastsentday, pref.lastsentweek);
    RETURN NEW;
  END' LANGUAGE 'plpgsql';

/*
-- 8 TIDSPERIODE

A table specifying a time period. This could be though of as an element in a timetable. A time period is related to a set of relation between equipmentgroups and alertaddresses.

starttid	this attribute speficies the start time of this time period. The time period end time is 
            implicit given by the start time by the next time period.

helg		Speficies wether this time period is for weekdays or weekend or both.
				1 all week
				2 weekdays Monday-Friday
				3 weekend Saturday-Sunday
*/
CREATE SEQUENCE tidsperiode_id_seq START 1000;
CREATE TABLE Tidsperiode (
       id integer NOT NULL DEFAULT nextval('tidsperiode_id_seq'),
       brukerprofilid integer NOT NULL,
       starttid time NOT NULL,
       helg integer NOT NULL,

       CONSTRAINT tidsperiode_pk PRIMARY KEY(id),
       CONSTRAINT brukerprofil_eksisterer
		  FOREIGN KEY(brukerprofilid) REFERENCES Brukerprofil(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);

/*
-- 9 UTSTYRGRUPPE

Equipment group. An equipment is a composite of equipment filters. Equipment group is specified by a 
ennumerated (by priority) list of equipment filters. An equipment group could either be owned by an user, or shared among administrators.

navn	The name of the equipment group
descr	Longer description

*/
CREATE SEQUENCE utstyrgruppe_id_seq START 1000;
CREATE TABLE Utstyrgruppe (
    id integer NOT NULL DEFAULT nextval('utstyrgruppe_id_seq'),
    accountid integer,
    navn varchar,
    descr varchar,

    CONSTRAINT utstyrgruppe_pk PRIMARY KEY(id),
       
    CONSTRAINT account_Exist
        FOREIGN KEY(accountid) REFERENCES Account(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE  
);

/*
-- 10 VARSLE

Varsle is the relation between alert address, time period and equipment group.

vent		an integer specifying the queueing settings on this alert.