· 7 years ago · Nov 12, 2018, 11:52 PM
1DROP FUNCTION IF EXISTS GetTableContent(INTEGER);
2DROP TABLE IF EXISTS registro;
3DROP TABLE IF EXISTS permite_cena;
4DROP TABLE IF EXISTS agendamento;
5DROP TABLE IF EXISTS rotina;
6DROP TABLE IF EXISTS cena;
7DROP TABLE IF EXISTS permissao;
8DROP TABLE IF EXISTS alerta;
9DROP TABLE IF EXISTS controle;
10DROP TABLE IF EXISTS ambiente;
11DROP TABLE IF EXISTS usuario;
12DROP TABLE IF EXISTS nivel_acesso;
13DROP TABLE IF EXISTS grupo;
14DROP TABLE IF EXISTS familia;
15DROP TABLE IF EXISTS tipo;
16
17CREATE TABLE tipo(
18 codigo SERIAL NOT NULL PRIMARY KEY,
19 nome VARCHAR NOT NULL,
20 cor VARCHAR(45) NOT NULL
21);
22
23CREATE TABLE familia(
24 codigo SERIAL NOT NULL PRIMARY KEY,
25 nome_familia VARCHAR NOT NULL,
26 aplicacao VARCHAR NOT NULL,
27 exclusao TIMESTAMP
28);
29
30CREATE TABLE grupo(
31 codigo SERIAL NOT NULL PRIMARY KEY,
32 codigo_familia INTEGER NOT NULL,
33 nome VARCHAR NOT NULL,
34 FOREIGN KEY (codigo_familia) REFERENCES familia (codigo)
35);
36
37CREATE TABLE nivel_acesso(
38 codigo SERIAL NOT NULL PRIMARY KEY,
39 nome VARCHAR NOT NULL
40);
41
42CREATE TABLE usuario(
43 codigo SERIAL NOT NULL PRIMARY KEY,
44 nome VARCHAR NOT NULL,
45 sobrenome VARCHAR NOT NULL,
46 login VARCHAR UNIQUE NOT NULL,
47 senha CHAR(32) NOT NULL,
48 email VARCHAR UNIQUE NOT NULL,
49 codigo_familia INTEGER NOT NULL,
50 codigo_grupo INTEGER NOT NULL,
51 codigo_acesso INTEGER NOT NULL,
52 data_adicionado TIMESTAMP WITHOUT TIME ZONE NOT NULL,
53 exclusao TIMESTAMP,
54 FOREIGN KEY (codigo_familia) REFERENCES familia (codigo),
55 FOREIGN KEY (codigo_grupo) REFERENCES grupo (codigo),
56 FOREIGN KEY (codigo_acesso) REFERENCES nivel_acesso (codigo)
57);
58
59CREATE TABLE ambiente(
60 codigo SERIAL NOT NULL PRIMARY KEY,
61 codigo_familia INTEGER NOT NULL,
62 nome VARCHAR NOT NULL,
63 FOREIGN KEY (codigo_familia) REFERENCES familia (codigo)
64);
65
66CREATE TABLE controle(
67 codigo SERIAL NOT NULL PRIMARY KEY,
68 codigo_ambiente INTEGER NOT NULL,
69 codigo_tipo INTEGER NOT NULL,
70 nome VARCHAR(12) NOT NULL,
71 icone VARCHAR NOT NULL,
72 status DECIMAL DEFAULT 0,
73 FOREIGN KEY (codigo_ambiente) REFERENCES ambiente (codigo),
74 FOREIGN KEY (codigo_tipo) REFERENCES tipo (codigo)
75);
76
77CREATE TABLE alerta(
78 codigo SERIAL NOT NULL,
79 codigo_controle INTEGER NOT NULL,
80 texto TEXT NOT NULL,
81 data TIMESTAMP NOT NULL,
82 visto BOOLEAN NOT NULL DEFAULT FALSE,
83 PRIMARY KEY (codigo),
84 FOREIGN KEY (codigo_controle) REFERENCES controle (codigo)
85);
86
87CREATE TABLE permissao(
88 codigo_grupo INTEGER NOT NULL REFERENCES grupo (codigo),
89 codigo_controle INTEGER NOT NULL REFERENCES controle (codigo),
90 PRIMARY KEY (codigo_grupo, codigo_controle)
91);
92
93CREATE TABLE cena(
94 codigo SERIAL NOT NULL PRIMARY KEY,
95 nome VARCHAR NOT NULL,
96 codigo_familia INTEGER NOT NULL,
97 FOREIGN KEY (codigo_familia) REFERENCES familia (codigo)
98);
99
100CREATE TABLE rotina(
101 codigo SERIAL NOT NULL PRIMARY KEY,
102 codigo_familia INTEGER NOT NULL,
103 codigo_cena INTEGER NOT NULL,
104 descricao VARCHAR NOT NULL,
105 dia_semana INTEGER,
106 dia_mes INTEGER,
107 mes INTEGER,
108 ano INTEGER,
109 hora INTEGER,
110 minuto INTEGER,
111 FOREIGN KEY (codigo_familia) REFERENCES familia (codigo),
112 FOREIGN KEY (codigo_cena) REFERENCES cena (codigo)
113);
114
115
116
117CREATE TABLE permite_cena(
118 codigo_cena INTEGER NOT NULL,
119 codigo_controle INTEGER NOT NULL,
120 status NUMERIC NOT NULL,
121 PRIMARY KEY (codigo_cena, codigo_controle),
122 FOREIGN KEY (codigo_cena) REFERENCES cena (codigo),
123 FOREIGN KEY (codigo_controle) REFERENCES controle (codigo)
124);
125
126CREATE TABLE registro(
127 codigo SERIAL NOT NULL,
128 texto TEXT NOT NULL,
129 session_data TEXT NOT NULL,
130 data TIMESTAMP NOT NULL
131);
132
133CREATE OR REPLACE FUNCTION GetTableContent(INTEGER) RETURNS TABLE (codigo INTEGER, nome_completo TEXT, grupo_nome VARCHAR, nivel_usuario VARCHAR, data_adicionado TEXT) AS $$
134BEGIN
135 RETURN QUERY SELECT
136 usuario.codigo as codigo,
137 usuario.nome || ' ' || usuario.sobrenome as nome_completo,
138 grupo.nome as grupo_nome,
139 nivel_acesso.nome as nivel_acesso,
140 to_char(usuario.data_adicionado, 'DD/MM/YYYY Ã s HH24:MI') as data_adicionado
141 FROM usuario, grupo, nivel_acesso, familia
142 WHERE usuario.codigo_acesso = nivel_acesso.codigo
143 AND usuario.codigo_familia = $1
144 AND usuario.codigo_grupo = grupo.codigo
145 AND grupo.codigo_familia = familia.codigo;
146END;
147$$ LANGUAGE 'plpgsql';
148
149
150ALTER TABLE cena ADD COLUMN icone VARCHAR;
151ALTER TABLE cena ADD COLUMN cor CHAR(6);
152
153
154
155INSERT INTO nivel_acesso VALUES(DEFAULT, 'Administrador');
156INSERT INTO nivel_acesso VALUES(DEFAULT, 'Moderador');
157INSERT INTO nivel_acesso VALUES(DEFAULT, 'Usuário');
158INSERT INTO nivel_acesso VALUES(DEFAULT, 'Visitante');
159INSERT INTO familia VALUES(DEFAULT, 'Alunos TCC', 'CTI - "Profº Isaac Portal Roldán"');
160INSERT INTO grupo VALUES(DEFAULT, 1, 'Admin');
161INSERT INTO tipo VALUES(DEFAULT, 'Temperatura', 'temp');
162INSERT INTO tipo VALUES(DEFAULT, 'Umidade', 'humidity');
163INSERT INTO tipo VALUES(DEFAULT, 'Lâmpada', 'lamp');
164INSERT INTO tipo VALUES(DEFAULT, 'Porta', 'door');
165INSERT INTO tipo VALUES(DEFAULT, 'Miscelânea', 'misc');
166INSERT INTO ambiente VALUES(DEFAULT, 1, 'Sala de TV');
167INSERT INTO ambiente VALUES(DEFAULT, 1, 'Sala de jantar');
168INSERT INTO ambiente VALUES(DEFAULT, 1, 'Cozinha');
169INSERT INTO ambiente VALUES(DEFAULT, 1, 'Quarto');
170INSERT INTO ambiente VALUES(DEFAULT, 1, 'Banheiro');
171INSERT INTO ambiente VALUES(DEFAULT, 1, 'Quintal');
172INSERT INTO ambiente VALUES(DEFAULT, 1, 'Geral');
173INSERT INTO controle VALUES(DEFAULT, 1, 3, 'LUZ-TV', 'lightbulb', DEFAULT);
174INSERT INTO controle VALUES(DEFAULT, 2, 3, 'LUZ-Jantar', 'lightbulb', DEFAULT);
175INSERT INTO controle VALUES(DEFAULT, 3, 3, 'LUZ-Cozinha', 'lightbulb', DEFAULT);
176INSERT INTO controle VALUES(DEFAULT, 4, 3, 'LUZ-Quarto', 'lightbulb', DEFAULT);
177INSERT INTO controle VALUES(DEFAULT, 5, 3, 'LUZ-Banheiro', 'lightbulb', DEFAULT);
178INSERT INTO controle VALUES(DEFAULT, 6, 3, 'LUZ-Caminho', 'lightbulb', DEFAULT);
179INSERT INTO controle VALUES(DEFAULT, 6, 4, 'Portão', 'key', DEFAULT);
180INSERT INTO controle VALUES(DEFAULT, 7, 5, 'Ventiladores', 'snowflake', DEFAULT);
181INSERT INTO controle VALUES(DEFAULT, 7, 1, 'Temperatura', 'thermometer-half', DEFAULT);
182INSERT INTO controle VALUES(DEFAULT, 7, 4, 'Umidade', 'tint', DEFAULT);
183INSERT INTO permissao VALUES(1, 1);
184INSERT INTO permissao VALUES(1, 2);
185INSERT INTO permissao VALUES(1, 3);
186INSERT INTO permissao VALUES(1, 4);
187INSERT INTO permissao VALUES(1, 5);
188INSERT INTO permissao VALUES(1, 6);
189INSERT INTO permissao VALUES(1, 7);
190INSERT INTO permissao VALUES(1, 8);
191INSERT INTO permissao VALUES(1, 9);
192INSERT INTO permissao VALUES(1, 10);
193INSERT INTO usuario VALUES (DEFAULT, 'Administrador', 'Master', 'admin', '202cb962ac59075b964b07152d234b70', 'admin@hotmail.com', 1, 1, 1, '2018-01-01 10:30:10', NULL);