-- 
-- Copyright (C) 2001, Hitesh Patel <hitesh@presys.com>
-- 
-- This file is part of NorthStar.
--
-- NorthStar is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- NorthStar is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
-- 
-- You should have received a copy of the GNU General Public License
-- along with NorthStar; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
--
--


\c template1
CREATE USER northstar;
CREATE DATABASE northstar;
\c northstar northstar
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
	id		INT4 NOT NULL DEFAULT nextval('users_id_seq') PRIMARY KEY,  	
	username	VARCHAR(32) NOT NULL UNIQUE,
	passwd		VARCHAR(32) NOT NULL,
	realname	VARCHAR(128) NOT NULL,
	enabled		BOOL DEFAULT 'T',
	perm_addnet	BOOL DEFAULT 'F',
	perm_updnet	BOOL DEFAULT 'F',
	perm_delnet	BOOL DEFAULT 'F',
	perm_addaddr	BOOL DEFAULT 'F',
	perm_updaddr	BOOL DEFAULT 'F',
	perm_deladdr	BOOL DEFAULT 'F',
	perm_adddev	BOOL DEFAULT 'F',
	perm_upddev	BOOL DEFAULT 'F',
	perm_deldev	BOOL DEFAULT 'F',
	perm_adduser	BOOL DEFAULT 'F',
	perm_upduser	BOOL DEFAULT 'F',
	perm_deluser	BOOL DEFAULT 'F',
	perm_addowner	BOOL DEFAULT 'F',
	perm_updowner	BOOL DEFAULT 'F',
	perm_delowner	BOOL DEFAULT 'F',
	perm_addloc	BOOL DEFAULT 'F',
	perm_updloc	BOOL DEFAULT 'F',
	perm_delloc	BOOL DEFAULT 'F'
);
GRANT ALL ON users TO northstar;

CREATE SEQUENCE owners_id_seq;
CREATE TABLE owners (
	id              INT4 NOT NULL DEFAULT nextval('owners_id_seq') PRIMARY KEY,  	
	name		VARCHAR(256) NOT NULL,
	descr		TEXT,
	address		VARCHAR(128) NOT NULL,
	city		VARCHAR(128) NOT NULL,
	state		VARCHAR(40) NOT NULL,
	zip		VARCHAR(12) NOT NULL,
	pocname		VARCHAR(128) NOT NULL,
	pocemail	VARCHAR(128) NOT NULL,
	pocphone	VARCHAR(128) NOT NULL,
	pocpager	VARCHAR(128),
	poccell		VARCHAR(128),
	techname	VARCHAR(128),
	techemail	VARCHAR(128),
	techphone	VARCHAR(128),
	techpager	VARCHAR(128),
	techcell	VARCHAR(128)
);
GRANT ALL ON owners TO northstar;

CREATE SEQUENCE locations_id_seq;
CREATE TABLE locations (
	id              INT4 NOT NULL DEFAULT nextval('locations_id_seq') PRIMARY KEY,    
	name		VARCHAR(256) NOT NULL,
	descr		TEXT,
	assignuser	INT4 REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	assigndate	TIMESTAMP DEFAULT now(),
	updateuser	INT4 REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	upddate		TIMESTAMP DEFAULT now()
);
GRANT ALL ON locations TO northstar;

CREATE SEQUENCE devices_id_seq;
CREATE TABLE devices (
	id		INT4 NOT NULL DEFAULT nextval('devices_id_seq') PRIMARY KEY, 	
	name		VARCHAR(256) NOT NULL,
	descr		TEXT,
	owner		INT4 NOT NULL REFERENCES owners(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, 
	location	INT4 NOT NULL REFERENCES locations(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, 
	assignuser	INT4 NOT NULL REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	assigndate	TIMESTAMP DEFAULT now(),
	updateuser	INT4 REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	upddate		TIMESTAMP DEFAULT now()
);
GRANT ALL ON devices TO northstar;


CREATE SEQUENCE networks_id_seq;
CREATE TABLE networks (
	id		INT4 NOT NULL DEFAULT nextval('networks_id_seq') PRIMARY KEY,
	parent		INT4 NOT NULL REFERENCES networks(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE DEFAULT '0',
	name		VARCHAR(256) NOT NULL,
	descr		TEXT,
	network		CIDR NOT NULL,
	owner		INT4 NOT NULL REFERENCES owners(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
	device		INT4 NOT NULL REFERENCES devices(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
	assignuser	INT4 NOT NULL REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	assigndate	TIMESTAMP DEFAULT now(),
	updateuser	INT4 REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	upddate		TIMESTAMP DEFAULT now()
);
GRANT ALL ON networks TO northstar;

CREATE SEQUENCE addresses_id_seq;	
CREATE TABLE addresses (
	id              INT4 NOT NULL DEFAULT nextval('addresses_id_seq') PRIMARY KEY,        
	parent		INT4 NOT NULL REFERENCES networks(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
	address		inet NOT NULL,
	owner		INT4 NOT NULL REFERENCES owners(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,  
	device		INT4 NOT NULL REFERENCES devices(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,     
	dnsname		VARCHAR(128),
	macaddr		MACADDR,
	misc		TEXT,
	assignuser	INT4 NOT NULL REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	assigndate	TIMESTAMP DEFAULT now(),
	updateuser	INT4 REFERENCES users(id) MATCH FULL ON UPDATE CASCADE,
	upddate		TIMESTAMP DEFAULT now()
);
GRANT ALL ON addresses TO northstar;

INSERT INTO users VALUES (0,'admin','UNUSED','Administrator','t','t','t','t','t','t','t','t','t','t','t','t','t','t','t','t','t','t','t');
INSERT INTO locations VALUES('0','Default Location','Default Location','0','now','0','now');
INSERT INTO owners VALUES('0','Default Owner','Default Owner',' ',' ',' ',' ',' ',' ',' ','','','','','','','');
INSERT INTO devices VALUES('0','Default Device','Default Device','0','0','0','now','0','now');
INSERT INTO networks VALUES('0','0','Default Network','Default Network','0.0.0.0/0','0','0','0','now','0','now');
