houzion-database.sql

DROP FUNCTION IF EXISTS GetTableContent(INTEGER);
DROP TABLE IF EXISTS registro;
DROP TABLE IF EXISTS permite_cena;
DROP TABLE IF EXISTS agendamento;
DROP TABLE IF EXISTS rotina;
DROP TABLE IF EXISTS cena;
DROP TABLE IF EXISTS permissao;
DROP TABLE IF EXISTS alerta;
DROP TABLE IF EXISTS controle;
DROP TABLE IF EXISTS ambiente;
DROP TABLE IF EXISTS usuario;
DROP TABLE IF EXISTS nivel_acesso;
DROP TABLE IF EXISTS grupo;
DROP TABLE IF EXISTS familia;
DROP TABLE IF EXISTS tipo;

CREATE TABLE tipo(
	codigo SERIAL NOT NULL PRIMARY KEY,
	nome VARCHAR NOT NULL,
	cor VARCHAR(45) NOT NULL
);

CREATE TABLE familia(
	codigo SERIAL NOT NULL PRIMARY KEY,
	nome_familia VARCHAR NOT NULL,
	aplicacao VARCHAR NOT NULL,
	exclusao TIMESTAMP
);

CREATE TABLE grupo(
	codigo SERIAL NOT NULL PRIMARY KEY,
	codigo_familia INTEGER NOT NULL,
	nome VARCHAR NOT NULL,
	FOREIGN KEY (codigo_familia) REFERENCES familia (codigo)
);

CREATE TABLE nivel_acesso(
	codigo SERIAL NOT NULL PRIMARY KEY,
	nome VARCHAR NOT NULL
);

CREATE TABLE usuario(
	codigo SERIAL NOT NULL PRIMARY KEY,
	nome VARCHAR NOT NULL,
	sobrenome VARCHAR NOT NULL,
	login VARCHAR UNIQUE NOT NULL,
	senha CHAR(32) NOT NULL,
	email VARCHAR UNIQUE NOT NULL,
	codigo_familia INTEGER NOT NULL,
	codigo_grupo INTEGER NOT NULL,
	codigo_acesso INTEGER NOT NULL,
	data_adicionado TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	exclusao TIMESTAMP,
	FOREIGN KEY (codigo_familia) REFERENCES familia (codigo),
	FOREIGN KEY (codigo_grupo) REFERENCES grupo (codigo),
	FOREIGN KEY (codigo_acesso) REFERENCES nivel_acesso (codigo)
);

CREATE TABLE ambiente(
	codigo SERIAL NOT NULL PRIMARY KEY,
	codigo_familia INTEGER NOT NULL,
	nome VARCHAR NOT NULL,
	FOREIGN KEY (codigo_familia) REFERENCES familia (codigo)
);

CREATE TABLE controle(
	codigo SERIAL NOT NULL PRIMARY KEY,
	codigo_ambiente INTEGER NOT NULL,
	codigo_tipo INTEGER NOT NULL,
	nome VARCHAR(12) NOT NULL,
	icone VARCHAR NOT NULL,
	status DECIMAL DEFAULT 0,
	FOREIGN KEY (codigo_ambiente) REFERENCES ambiente (codigo),
	FOREIGN KEY (codigo_tipo) REFERENCES tipo (codigo)
);

CREATE TABLE alerta(
	codigo SERIAL NOT NULL,
	codigo_controle INTEGER NOT NULL,
	texto TEXT NOT NULL,
	data TIMESTAMP NOT NULL,
	visto BOOLEAN NOT NULL DEFAULT FALSE,
	PRIMARY KEY (codigo),
	FOREIGN KEY (codigo_controle) REFERENCES controle (codigo)
);

CREATE TABLE permissao(
	codigo_grupo INTEGER NOT NULL REFERENCES grupo (codigo),
	codigo_controle INTEGER NOT NULL REFERENCES controle (codigo),
	PRIMARY KEY (codigo_grupo, codigo_controle)
);

CREATE TABLE cena(
	codigo SERIAL NOT NULL PRIMARY KEY,
	nome VARCHAR NOT NULL,
	codigo_familia INTEGER NOT NULL,
	FOREIGN KEY (codigo_familia) REFERENCES familia (codigo)
);

CREATE TABLE rotina(
	codigo SERIAL NOT NULL PRIMARY KEY,
	codigo_familia INTEGER NOT NULL,
	codigo_cena INTEGER NOT NULL,
	descricao VARCHAR NOT NULL,
	dia_semana INTEGER,
	dia_mes INTEGER,
	mes INTEGER,
	ano INTEGER,
	hora INTEGER,
	minuto INTEGER,
	FOREIGN KEY (codigo_familia) REFERENCES familia (codigo),
	FOREIGN KEY (codigo_cena) REFERENCES cena (codigo)
);



CREATE TABLE permite_cena(
	codigo_cena INTEGER NOT NULL,
	codigo_controle INTEGER NOT NULL,
	status NUMERIC NOT NULL,
	PRIMARY KEY (codigo_cena, codigo_controle),
	FOREIGN KEY (codigo_cena) REFERENCES cena (codigo),
	FOREIGN KEY (codigo_controle) REFERENCES controle (codigo)
);

CREATE TABLE registro(
	codigo SERIAL NOT NULL,
	texto TEXT NOT NULL,
	session_data TEXT NOT NULL,
	data TIMESTAMP NOT NULL
);

CREATE OR REPLACE FUNCTION GetTableContent(INTEGER) RETURNS TABLE (codigo INTEGER, nome_completo TEXT, grupo_nome VARCHAR, nivel_usuario VARCHAR, data_adicionado TEXT) AS $$
BEGIN
	RETURN QUERY SELECT
		usuario.codigo as codigo,
		usuario.nome || ' ' || usuario.sobrenome as nome_completo,
		grupo.nome as grupo_nome,
		nivel_acesso.nome as nivel_acesso,
		to_char(usuario.data_adicionado, 'DD/MM/YYYY às HH24:MI') as data_adicionado
	FROM usuario, grupo, nivel_acesso, familia
	WHERE usuario.codigo_acesso = nivel_acesso.codigo
		AND usuario.codigo_familia = $1
		AND usuario.codigo_grupo = grupo.codigo
		AND grupo.codigo_familia = familia.codigo;
END;
$$ LANGUAGE 'plpgsql';


ALTER TABLE cena ADD COLUMN icone VARCHAR;
ALTER TABLE cena ADD COLUMN cor CHAR(6);



INSERT INTO nivel_acesso VALUES(DEFAULT, 'Administrador');
INSERT INTO nivel_acesso VALUES(DEFAULT, 'Moderador');
INSERT INTO nivel_acesso VALUES(DEFAULT, 'Usuário');
INSERT INTO nivel_acesso VALUES(DEFAULT, 'Visitante');
INSERT INTO familia VALUES(DEFAULT, 'Alunos TCC', 'CTI - "Profº Isaac Portal Roldán"');
INSERT INTO grupo VALUES(DEFAULT, 1, 'Admin');
INSERT INTO tipo VALUES(DEFAULT, 'Temperatura', 'temp');
INSERT INTO tipo VALUES(DEFAULT, 'Umidade', 'humidity');
INSERT INTO tipo VALUES(DEFAULT, 'Lâmpada', 'lamp');
INSERT INTO tipo VALUES(DEFAULT, 'Porta', 'door');
INSERT INTO tipo VALUES(DEFAULT, 'Miscelânea', 'misc');
INSERT INTO ambiente VALUES(DEFAULT, 1, 'Sala de TV');
INSERT INTO ambiente VALUES(DEFAULT, 1, 'Sala de jantar');
INSERT INTO ambiente VALUES(DEFAULT, 1, 'Cozinha');
INSERT INTO ambiente VALUES(DEFAULT, 1, 'Quarto');
INSERT INTO ambiente VALUES(DEFAULT, 1, 'Banheiro');
INSERT INTO ambiente VALUES(DEFAULT, 1, 'Quintal');
INSERT INTO ambiente VALUES(DEFAULT, 1, 'Geral');
INSERT INTO controle VALUES(DEFAULT, 1, 3, 'LUZ-TV', 'lightbulb', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 2, 3, 'LUZ-Jantar', 'lightbulb', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 3, 3, 'LUZ-Cozinha', 'lightbulb', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 4, 3, 'LUZ-Quarto', 'lightbulb', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 5, 3, 'LUZ-Banheiro', 'lightbulb', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 6, 3, 'LUZ-Caminho', 'lightbulb', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 6, 4, 'Portão', 'key', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 7, 5, 'Ventiladores', 'snowflake', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 7, 1, 'Temperatura', 'thermometer-half', DEFAULT);
INSERT INTO controle VALUES(DEFAULT, 7, 4, 'Umidade', 'tint', DEFAULT);
INSERT INTO permissao VALUES(1, 1);
INSERT INTO permissao VALUES(1, 2);
INSERT INTO permissao VALUES(1, 3);
INSERT INTO permissao VALUES(1, 4);
INSERT INTO permissao VALUES(1, 5);
INSERT INTO permissao VALUES(1, 6);
INSERT INTO permissao VALUES(1, 7);
INSERT INTO permissao VALUES(1, 8);
INSERT INTO permissao VALUES(1, 9);
INSERT INTO permissao VALUES(1, 10);
INSERT INTO usuario VALUES (DEFAULT, 'Administrador', 'Master', 'admin', '202cb962ac59075b964b07152d234b70', '[email protected]', 1, 1, 1, '2018-01-01 10:30:10', NULL);