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