navprofiles.sql

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

/*
-- 20 SMSQ

SMSQ Description

*/
CREATE TABLE smsq (
    id serial primary key, 
    accountid int references
        account(id) on update cascade on delete cascade, 
        
    time timestamp not null,
    phone varchar(15) not null,
    msg varchar(145) not null, 
    sent char(1) not null default 'N' 
        check (sent='Y' or sent='N' or sent='I'), 
    smsid int, 
    timesent timestamp, 
    severity int
);


/*
-- 21 Queue

QUEUE Description

*/
CREATE TABLE queue (
    id serial primary key, 
    accountid int references
        account(id) on update cascade on delete cascade, 
        
    addrid int references
        alarmadresse(id) on update cascade on delete cascade, 
        
    alertid int,
    time timestamp not null    
);

/*
-- 20 NAVBARLINK

Table for links in the navigation bar and dropdown menus. Links with
accountid 0 is shared by all.

accountid          owner of the link, id 0 means link is shared by all
name               one or two words describing the link, eg. Network
Explorer
uri                address of the link, eg. /vlanplot/index.html

*/
CREATE SEQUENCE navbarlink_id_seq START 1000;
CREATE TABLE NavbarLink (
    id integer NOT NULL DEFAULT nextval('navbarlink_id_seq'),
    accountid integer NOT NULL DEFAULT 0,
    name varchar,
    uri varchar,

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

/*
-- 21 ACCOUNTNAVBAR

Relation between account and navbarlinks, describing where the user wants
the link to be.

positions      'navbar', 'qlink1', 'qlink2' or a combination of these.

*/
CREATE TABLE AccountNavbar (
    accountid integer NOT NULL,
    navbarlinkid integer NOT NULL,
    positions varchar,

    CONSTRAINT accountnavbar_pk PRIMARY KEY (accountid, navbarlinkid),
    CONSTRAINT account_exists
               FOREIGN KEY (accountid) REFERENCES Account(id)
               ON DELETE CASCADE
               ON UPDATE CASCADE,
    CONSTRAINT navbarlink_exists
               FOREIGN KEY (navbarlinkid) REFERENCES NavbarLink(id)
               ON DELETE CASCADE
               ON UPDATE CASCADE
);


/*
-- 23 DEFAULTFILTER

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

The relation can be only to filters shared by administrators, not to filters owned by someone.
*/
CREATE TABLE DefaultFilter (
       accountgroupid integer NOT NULL,
       utstyrfilterid integer NOT NULL,

       CONSTRAINT defaultfilter_pk PRIMARY KEY (accountgroupid, utstyrfilterid),
       CONSTRAINT utstyrfilter_eksisterer
		  FOREIGN KEY(utstyrfilterid) REFERENCES Utstyrfilter(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE,
       CONSTRAINT accountgroup_exist
		  FOREIGN KEY(accountgroupid) REFERENCES AccountGroup(id)
		  ON DELETE CASCADE
		  ON UPDATE CASCADE
);


/*
-- AccountOrg

This table associates accounts with organizations.  Unfortunately, the
entity describing organizations is contained in the manage database,
so referential integrity must be enforced outside of the database
server.

*/
CREATE TABLE AccountOrg (
       accountid integer NOT NULL,
       orgid varchar(10) NOT NULL,

       CONSTRAINT accountorg_pk
                  PRIMARY KEY (accountid, orgid),
       CONSTRAINT account_exists
                  FOREIGN KEY(accountid) REFERENCES Account(id)
                  ON DELETE CASCADE
                  ON UPDATE CASCADE
);

/*
-- Privilege

This table contains valid privilege names and their id numbers for
reference from the AccountGroupPrivilege table

*/
CREATE SEQUENCE privilege_id_seq START 10000;
CREATE TABLE Privilege (
       privilegeid integer NOT NULL DEFAULT nextval('privilege_id_seq'),
       privilegename varchar(30) NOT NULL CONSTRAINT privilegename_uniq UNIQUE,

       CONSTRAINT privilege_pk PRIMARY KEY (privilegeid)
);

/*
-- AccountGroupPrivilege

This table defines privileges granted to AccountGroups.

*/
CREATE TABLE AccountGroupPrivilege (
       accountgroupid integer NOT NULL,
       privilegeid integer NOT NULL,
       target varchar NOT NULL,

       CONSTRAINT agprivilege_pk PRIMARY KEY (accountgroupid, privilegeid, target),
       CONSTRAINT accountgroup_exists
                  FOREIGN KEY(accountgroupid) REFERENCES AccountGroup(id)
                  ON DELETE CASCADE
                  ON UPDATE CASCADE,
       CONSTRAINT privilege_exists
                  FOREIGN KEY(privilegeid) REFERENCES Privilege
                  ON DELETE CASCADE
                  ON UPDATE CASCADE
);

/*
-- PrivilegeByGroup

This is a view that is similar to AccountGroupPrivilege, except that privilege names have been resolved from the privilege id
*/
CREATE VIEW PrivilegeByGroup AS (
       SELECT a.accountgroupid, b.privilegename AS action, a.target
       FROM AccountgroupPrivilege AS a NATURAL JOIN Privilege AS b
);
















/*
------------------------------------------------------
 INSERT INITIAL DATA
------------------------------------------------------
*/


-- Accounts and Accountgroups

INSERT INTO AccountGroup (id, name, descr) VALUES (1, 'NAV Administrators', 'Full access to everything');
INSERT INTO AccountGroup (id, name, descr) VALUES (2, 'Anonymous users', 'Unauthenticated users (not logged in)');
INSERT INTO AccountGroup (id, name, descr) VALUES (3, 'Authenticated users', 'Any authenticated user (logged in)');

-- Some default example groups
INSERT INTO AccountGroup (name, descr) VALUES ('SMS', 'Allowed to receive SMS alerts');

-- Default system accounts
INSERT INTO Account (id, login, name, password) VALUES (0, 'default', 'Default User', '');
INSERT INTO Account (id, login, name, password) VALUES (1, 'admin', 'NAV Administrator', '{sha1}s3F6XX/D$L3vU8Rs2bTJ4zArBLVIPbh7cN9Q=');

INSERT INTO AccountInGroup (accountid, groupid) VALUES (1, 1);

INSERT INTO Preference (accountid, queuelength) VALUES (1, '14 days');
INSERT INTO Preference (accountid, queuelength) VALUES (0, '14 days');

-- Default preference rows are now inserted, so we create the trigger
-- on the account table
CREATE TRIGGER insert_account AFTER INSERT ON account FOR EACH ROW EXECUTE PROCEDURE copy_default_preferences();

-- NAVBAR PREFERENCES

INSERT INTO NavbarLink (id, accountid, name, uri) VALUES (1, 0, 'Preferences', '/preferences');
INSERT INTO NavbarLink (id, accountid, name, uri) VALUES (2, 0, 'Toolbox', '/toolbox');
INSERT INTO NavbarLink (id, accountid, name, uri) VALUES (3, 0, 'Useradmin', '/useradmin/index');
INSERT INTO NavbarLink (id, accountid, name, uri) VALUES (4, 0, 'Userinfo', '/index/userinfo');

INSERT INTO AccountNavbar (accountid, navbarlinkid, positions) VALUES (1, 1, 'navbar');
INSERT INTO AccountNavbar (accountid, navbarlinkid, positions) VALUES (1, 2, 'navbar');
INSERT INTO AccountNavbar (accountid, navbarlinkid, positions) VALUES (1, 3, 'navbar');
INSERT INTO AccountNavbar (accountid, navbarlinkid, positions) VALUES (1, 4, 'navbar');

INSERT INTO AccountNavbar (accountid, navbarlinkid, positions) VALUES (0, 1, 'navbar');
INSERT INTO AccountNavbar (accountid, navbarlinkid, positions) VALUES (0, 2, 'navbar');
INSERT INTO AccountNavbar (accountid, navbarlinkid, positions) VALUES (0, 4, 'navbar');


-- Privileges

-- INSERT INTO Privilege VALUES (1, 'empty_privilege');
INSERT INTO Privilege VALUES (2, 'web_access');
INSERT INTO Privilege VALUES (3, 'alert_by');
INSERT INTO Privilege VALUES (4, 'report_access');

/*
  Set some default web_access privileges
*/
-- Anonymous users need access to a few things, like the login page and images
-- and soforth
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/about/.*');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/images/.*');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/js/.*');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/style/.*');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/alertprofiles/wap/.*');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/$');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/toolbox\\b');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/index(.py)?/(index|login|logout|userinfo|passwd)\\b');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/messages/(active|historic|planned|view|rss)\\b');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/maintenance/(calendar|active|historic|planned|view)\\b');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/vlanPlot\\b');
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (2, 2, '^/vPServer/servlet/vPServer\\b');

-- Define minimum privileges for authenticated users
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) VALUES (3, 2, '^/(report|status|messages|alertprofiles|machinetracker|browse|preferences|cricket)/?');

-- Give alert_by privilege to SMS group
INSERT INTO AccountGroupPrivilege (accountgroupid, privilegeid, target) 
       VALUES ((SELECT id FROM AccountGroup WHERE name='SMS'), 3, 'sms');


-- Matchfields
/* 
Matchfield.Datatype