· 8 years ago · Dec 24, 2017, 11:54 AM
1drop database if exists SeboVirtual_beta;
2create database if not exists SeboVirtual_beta;
3use SeboVirtual_beta;
4
5drop table if exists Clientes;
6create table Clientes (
7 id int not null auto_increment,
8 nome varchar(30) not null,
9 cidade varchar(30) not null,
10 primary key(id)
11)engine = InnoDB;
12
13drop table if exists Fornecedores;
14create table Fornecedores (
15 id int not null auto_increment,
16 nome varchar(30) not null,
17 cidade varchar(30) not null,
18 primary key(id)
19)engine = InnoDB;
20
21drop table if exists Livros;
22create table Livros (
23 id int not null auto_increment,
24 titulo varchar(30) not null,
25 preco decimal(10,2) not null,
26 quantidade int not null,
27 primary key(id)
28)engine = InnoDB;
29
30drop table if exists Pedidos;
31create table Pedidos (
32 id int not null auto_increment,
33 id_ator int not null,
34 tipo varchar(10) not null,
35 data_pedido date not null,
36 primary key(id)
37)engine = InnoDB;
38
39drop table if exists Livros_Pedidos;
40create table Livros_Pedidos (
41 id_livro int not null,
42 id_pedido int not null,
43 id_ator int not null,
44 primary key(id_livro, id_pedido),
45 foreign key(id_livro) references Livros(id),
46 foreign key(id_pedido) references Pedidos(id)
47)engine = InnoDB;
48
49
50
51
52
53
54
55create or replace view v_clientes as
56 select titulo, preco, quantidade from Livros;
57
58create or replace view v_clientes_pedidos as
59 select c.nome, l.titulo, lp.id_pedido from Clientes c, Livros l, Livros_Pedidos lp
60 where c.id = lp.id_ator and l.id = lp.id_livro;
61
62drop user 'cliente'@'localhost';
63create user 'cliente'@'localhost' identified by 'identifier';
64
65drop user 'funcion_pedidos'@'localhost';
66create user 'funcion_pedidos'@'localhost' identified by 'identifier';
67
68
69grant select on v_clientes to 'cliente'@'localhost';
70grant select on v_clientes_pedidos to 'funcion_pedidos'@'localhost';
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85drop trigger if exists atualiza_data_pedido;
86create trigger atualiza_data_pedido
87before insert on Pedidos
88for each row
89set new.data_pedido = now();
90
91
92drop procedure if exists venda;
93delimiter []
94create procedure venda(titulo_ varchar(30), nome_ varchar(30), tempo int)
95begin
96 declare id_livro int;
97 declare id_cliente int;
98 declare id_pedido int;
99 declare quant int;
100 select sleep(tempo);
101 select quantidade into quant from Livros where titulo = titulo_ for update;
102 select id into id_cliente from Clientes where nome = nome_ lock in share mode;
103 select sleep(tempo);
104 select id into id_livro from Livros where titulo = titulo_;
105 if id_livro > 0 and id_cliente > 0 and quant > 0 then
106 begin
107 select sleep(tempo);
108 update Livros set quantidade = quantidade - 1 where id = id_livro;
109 insert into Pedidos (id, id_ator, tipo) values (id, id_cliente, 'venda');
110 select id into id_pedido from Pedidos where id >= all (select id from Pedidos);
111 insert into Livros_Pedidos (id_livro, id_pedido, id_ator) values (id_livro, id_pedido, id_cliente);
112 commit;
113 end;
114 else
115 select 'rollback ... ';
116 rollback;
117 end if;
118end;
119[]
120delimiter ;
121
122
123 drop procedure if exists compra;
124 delimiter []
125 create procedure compra(titulo_ varchar(30), preco decimal(10,2), nome_ varchar(30), tempo int)
126 begin
127 declare id_livro int;
128 declare id_fornecedor int;
129 declare id_pedido int;
130 select sleep(tempo);
131 select id into id_livro from Livros where titulo = titulo_;
132 if id_livro is NULL then
133 insert into Livros (id, titulo, preco, quantidade) values (id, titulo_, preco+preco*0.2, 0);
134 end if;
135
136 select quantidade from Livros where titulo = titulo_ for update;
137 select id from Fornecedores where nome = nome_ lock in share mode;
138 select sleep(tempo);
139 select id into id_livro from Livros where titulo = titulo_;
140 select id into id_fornecedor from Fornecedores where nome = nome_;
141 if id_livro > 0 and id_fornecedor > 0 then
142 begin
143 select sleep(tempo);
144 update Livros set quantidade = quantidade + 1 where id = id_livro;
145 insert into Pedidos (id, id_ator, tipo) values (id, id_fornecedor, 'compra');
146 select id into id_pedido from Pedidos where id >= all (select id from Pedidos);
147 insert into Livros_Pedidos (id_livro, id_pedido, id_ator) values (id_livro, id_pedido, 0);
148 commit;
149 end;
150 else
151 select 'rollback ...';
152 rollback;
153 end if;
154 end;
155 []
156 delimiter ;
157
158
159drop procedure if exists mostrar;
160delimiter []
161create procedure mostrar()
162begin
163 select * from Pedidos;
164 select * from Livros_Pedidos;
165 select * from Livros;
166end;
167[]
168delimiter ;
169
170
171
172
173
174
175
176--------------------------------------------------------------------------------
177--============================================================================--
178--================--------------------------------------------================--
179--================--------------------------------------------================--
180--================--------------------------------------------================--
181--============================================================================--
182--------------------------------------------------------------------------------
183
184
185drop procedure if exists falha;
186delimiter []
187create procedure falha(mensagem varchar(30))
188begin
189 select mensagem as 'Falha';
190end;
191[]
192delimiter ;
193
194
195delimiter []
196drop trigger if exists verifica_quantidade;
197create trigger verifica_quantidade
198 before update on Livros
199 for each row
200 begin
201 if old.quantidade = 0 then
202 call falha('estoque insuficiente');
203 end if;
204 end;
205[]
206delimiter ;