sysvitals/web/schema.sql

DROP TABLE IF EXISTS user_groups;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS servers;
DROP TABLE IF EXISTS groups;

CREATE TABLE groups (
	id serial PRIMARY KEY,
	name varchar(32) NOT NULL
);

CREATE TABLE users (
	id serial PRIMARY KEY,
	username varchar(32) NOT NULL,
	password char(64) NOT NULL,
	salt char(32) NOT NULL,
	UNIQUE (username)
);

CREATE TABLE user_groups (
	user_id integer NOT NULL references users(id),
	group_id integer NOT NULL references groups(id),
	UNIQUE (user_id, group_id)
);

CREATE TABLE servers (
	id serial PRIMARY KEY references groups(id),
	group_id integer,
	hostname varchar(256) NOT NULL
);