· last year · Feb 20, 2024, 01:50 PM
1/*
2 Nome: lazdermos_gsl.sql
3 Função: criar e atualizar os objetos dentro do banco de dados "lazdemos_gsl.fdb"
4 Data: 20/02/2024
5 Autor: Hamacker <sirhamacker [em] gmail.com
6*/
7
8set sql dialect 3;
9set clientlib 'fbclient.dll';
10connect 'C:\Projetos-fpc\lazdemos_gsl\db\LAZDEMOS_GSL.FDB';
11set autoddl on;
12set names iso8859_1;
13set transaction
14 read write
15 isolation level read committed
16 wait
17 lock timeout 180;
18
19CREATE or alter EXCEPTION ERR 'Erro generico, favor verificar COM ATENÇÃO';
20GRANT USAGE ON EXCEPTION ERR TO PUBLIC;
21
22SET TERM ^;
23execute block as
24declare variable existe boolean;
25begin
26 existe=false;
27 if (EXISTS(SELECT * from RDB$RELATIONS WHERE RDB$RELATION_NAME='CLIENTES' )) then
28 existe=true;
29 if (not existe) then
30 BEGIN
31 execute statement
32 '
33 CREATE TABLE CLIENTES (
34 ID_CLIENTE BIGINT generated by default as identity not null,
35 NOME_COMPLETO CHAR(120),
36 STATUS CHAR(1) DEFAULT ''A'') ;
37 ';
38 END
39END
40^
41
42COMMIT;^
43
44-- Comentar a tabela
45COMMENT ON TABLE CLIENTES IS 'Relação de clientes';^
46COMMENT ON COLUMN CLIENTES.ID_CLIENTE IS 'Codigo do cliente';^
47COMMENT ON COLUMN CLIENTES.NOME_COMPLETO IS 'Nome do cliente';^
48COMMENT ON COLUMN CLIENTES.STATUS IS 'Situação do cliente(A=Ativo, C=Cancelado)';^
49
50-- Permissão a tabela
51grant select on clientes to PUBLIC; ^
52grant select, delete, insert, update on clientes to SYSDBA; ^
53
54COMMIT;^
55
56-- Apagando chave prinmaria antiga
57execute block as
58declare variable existe boolean;
59begin
60 existe=false;
61 if (EXISTS(
62 select * from rdb$relation_constraints
63 where rdb$constraint_name='PK2_CLIENTES' )) then
64 existe=true;
65 if (existe) then
66 BEGIN
67 execute statement
68 '
69 ALTER TABLE CLIENTES DROP CONSTRAINT PK2_CLIENTES;
70 ';
71 END
72END
73^
74
75COMMIT;^
76
77-- Chave prinmaria
78execute block as
79declare variable existe boolean;
80begin
81 existe=false;
82 if (EXISTS(
83 select * from rdb$relation_constraints
84 where rdb$constraint_name='PK_CLIENTES' )) then
85 existe=true;
86 if (not existe) then
87 BEGIN
88 execute statement
89 '
90 ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES PRIMARY KEY (ID_CLIENTE);
91 ';
92 END
93END
94^
95
96COMMIT;^
97
98-- Constraint status apenas A ou C
99execute block as
100declare variable existe boolean;
101begin
102 existe=false;
103 if (EXISTS(
104 select * from rdb$relation_constraints
105 where rdb$constraint_name='CHK_CLIENTES_STATUS' )) then
106 existe=true;
107 if (existe) then
108 BEGIN
109 execute statement
110 '
111 ALTER TABLE CLIENTES
112 DROP CONSTRAINT CHK_CLIENTES_STATUS
113 ';
114 END
115END
116^
117
118COMMIT;^
119
120
121-- Constraint status apenas A ou C
122execute block as
123declare variable existe boolean;
124begin
125 existe=false;
126 if (EXISTS(
127 select * from rdb$relation_constraints
128 where rdb$constraint_name='CHK_CLIENTES_STATUS'
129 )) then
130 existe=true;
131 if (not existe) then
132 BEGIN
133 execute statement
134 '
135 ALTER TABLE CLIENTES
136 ADD CONSTRAINT CHK_CLIENTES_STATUS
137 CHECK (
138 STATUS IN (''A'',''C'',''P'')
139 );
140 ';
141 END
142END
143^
144
145COMMIT;^
146
147-- Adicionando campo CNPJ
148execute block as
149declare variable existe boolean;
150begin
151 existe=false;
152 if (EXISTS(
153 select * from rdb$relation_fields rf
154 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='CNPJ'
155 )) then
156 existe=true;
157 if (not existe) then
158 BEGIN
159 execute statement
160 '
161 ALTER TABLE CLIENTES
162 ADD CNPJ CHAR(14)
163 ';
164 END
165END
166^
167
168COMMIT;^
169
170-- CNPJ não pode ter nulos
171execute block as
172declare variable existe boolean;
173begin
174 existe=false;
175 if (EXISTS(
176 select * from rdb$relation_fields rf
177 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='CNPJ'
178 )) then
179 existe=true;
180 if (existe) then
181 BEGIN
182 execute statement
183 '
184 UPDATE CLIENTES SET CNPJ='''' WHERE CNPJ IS NULL;
185 ';
186 END
187END
188^
189
190COMMIT;^
191
192-- CNPJ não pode ter nulos not nulll
193execute block as
194declare variable existe boolean;
195begin
196 existe=false;
197 if (EXISTS(
198 select * from rdb$relation_fields rf
199 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='CNPJ'
200 )) then
201 existe=true;
202 if (existe) then
203 BEGIN
204 execute statement
205 '
206 ALTER TABLE CLIENTES ALTER CNPJ SET NOT NULL
207 ';
208 END
209END
210^
211
212COMMIT;^
213
214-- Adicionando campo LAST_UPDATE
215execute block as
216declare variable existe boolean;
217begin
218 existe=false;
219 if (EXISTS(
220 select * from rdb$relation_fields rf
221 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='LAST_UPDATE'
222 )) then
223 existe=true;
224 if (not existe) then
225 BEGIN
226 execute statement
227 '
228 ALTER TABLE CLIENTES
229 ADD LAST_UPDATE timestamp
230 DEFAULT current_timestamp;
231 ';
232 END
233END
234^
235
236COMMIT;^
237
238-- Adicionando campo LAST_UPDATE
239execute block as
240declare variable existe boolean;
241begin
242 existe=false;
243 if (EXISTS(
244 select * from rdb$relation_fields rf
245 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='LAST_OWNER'
246 )) then
247 existe=true;
248 if (not existe) then
249 BEGIN
250 execute statement
251 '
252 ALTER TABLE CLIENTES
253 ADD LAST_OWNER varchar(60)
254 DEFAULT current_user;
255 ';
256 END
257END
258^
259
260COMMIT;^
261
262-- Criando campo STATUS_COM (compute by) - FANTASIA
263execute block as
264declare variable existe boolean;
265begin
266 existe=false;
267 if (EXISTS(
268 select * from rdb$relation_fields rf
269 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='STATUS_COM'
270 )) then
271 existe=true;
272 if (not existe) then
273 BEGIN
274 execute statement
275 '
276 ALTER TABLE CLIENTES
277 ADD STATUS_COM COMPUTED BY ((CAST(''*'' AS VARCHAR(15))))
278 ';
279 END
280END
281^
282
283COMMIT;^
284
285-- Criando campo STATUS_COM (compute by) - REAL
286execute block as
287declare variable existe boolean;
288begin
289 existe=false;
290 if (EXISTS(
291 select * from rdb$relation_fields rf
292 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='STATUS_COM'
293 )) then
294 existe=true;
295 if (existe) then
296 BEGIN
297 execute statement
298 '
299 ALTER TABLE CLIENTES
300 ALTER STATUS_COM COMPUTED BY (
301 CAST(
302 CASE
303 WHEN STATUS=''A'' THEN ''Ativo''
304 WHEN STATUS=''C'' THEN ''Cancelado''
305 WHEN STATUS=''P'' THEN ''Pàralisado''
306 ELSE ''Indefinido''
307 END AS VARCHAR(15)
308 )
309 )
310 ';
311 END
312END
313
314^
315
316COMMIT;^
317
318-- Criando campo STATUS_COM (compute by) - FANTASIA
319execute block as
320declare variable existe boolean;
321begin
322 existe=false;
323 if (EXISTS(
324 select * from rdb$relation_fields rf
325 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='MODIFICADO_COM'
326 )) then
327 existe=true;
328 if (not existe) then
329 BEGIN
330 execute statement
331 '
332 ALTER TABLE CLIENTES
333 ADD MODIFICADO_COM COMPUTED BY ((CAST(''*'' AS VARCHAR(40))))
334 ';
335 END
336END
337^
338
339COMMIT;^
340
341-- Criando campo STATUS_COM (compute by) - REAL
342execute block as
343declare variable existe boolean;
344begin
345 existe=false;
346 if (EXISTS(
347 select * from rdb$relation_fields rf
348 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='MODIFICADO_COM'
349 )) then
350 existe=true;
351 if (existe) then
352 BEGIN
353 execute statement
354 '
355 ALTER TABLE CLIENTES ALTER MODIFICADO_COM COMPUTED BY (
356 CAST(
357 CASE
358 WHEN (last_update<=''01.01.1970'') THEN ''Inalterado''
359 WHEN (coalesce(last_owner,'''')='''') then left(cast(last_update as varchar(24))||'' anonimo'', 40)
360 ELSE left(cast(last_update as varchar(24))||'' ''||last_owner, 40)
361 END AS VARCHAR(40)
362 )
363 )
364 ';
365 END
366END
367^
368
369COMMIT;^
370
371-- Criando campo STATUS_COM (compute by) - REAL
372execute block as
373declare variable existe boolean;
374begin
375 existe=false;
376 if (EXISTS(
377 select * from rdb$relation_fields rf
378 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='CNPJ'
379 )) then
380 existe=true;
381 if (existe) then
382 BEGIN
383 existe=false;
384 if (EXISTS(
385 select *
386 from clientes c
387 where
388 (
389 (
390 select count(*) from clientes c2
391 where c2.cnpj=c.cnpj
392 )>1
393 )
394 ) ) then
395 existe=true;
396 if (existe) then
397 BEGIN
398 execute statement
399 '
400 update clientes set
401 cnpj=cast(substring(nome_completo||''#'' similar ''%#"[[:DIGIT:]]+#"%'' escape ''#'') as varchar(14))
402 where coalesce(cnpj,'''')=''''
403 ';
404 END
405 END
406END
407^
408
409COMMIT;^
410
411-- Criando um indice de campo unico CNPJ
412execute block as
413declare variable existe boolean;
414begin
415 existe=false;
416 if (EXISTS(
417 select * from rdb$relation_fields rf
418 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='CNPJ'
419 )) then
420 existe=true;
421 if (existe) then
422 BEGIN
423 existe=false;
424 if (EXISTS(
425 select * from rdb$relation_constraints
426 where rdb$constraint_name='UNQ1_CLIENTES'
427 )) then
428 existe=true;
429 if (not existe) then
430 BEGIN
431 execute statement
432 '
433 ALTER TABLE CLIENTES
434 ADD CONSTRAINT UNQ1_CLIENTES
435 UNIQUE (CNPJ);
436 ';
437 END
438 END
439END
440^
441
442COMMIT;^
443
444-- Criando um indice de campo unico CNPJ
445execute block as
446declare variable existe boolean;
447begin
448 existe=false;
449 if (EXISTS(
450 select * from rdb$relation_fields rf
451 where rf.rdb$relation_name='CLIENTES' and rdb$field_name='NOME_COMPLETO'
452 )) then
453 existe=true;
454 if (existe) then
455 BEGIN
456 existe=false;
457 if (EXISTS(
458 select * from rdb$relation_constraints
459 where rdb$constraint_name='IDX_CLIENTES_NOME_COMPLETO'
460 )) then
461 existe=true;
462 if (not existe) then
463 BEGIN
464 execute statement
465 '
466 CREATE INDEX IDX_CLIENTES_NOME_COMPLETO
467 ON CLIENTES (NOME_COMPLETO)
468 ';
469 END
470 END
471END
472^
473
474commit; ^
475
476SET TERM ; ^