|
1
|
SELECT TSystem.Settings__Set('PRODAT', '23.24.08.06'); -- für DBUpdates Sync
|
|
2
|
|
|
3
|
|
|
4
|
--DELETE FROM fieldalias;
|
|
5
|
--SELECT tsystem.syncro__fieldalias();
|
|
6
|
--SELECT tsystem.syncro__dbupdates();
|
|
7
|
|
|
8
|
DO $do$
|
|
9
|
BEGIN
|
|
10
|
|
|
11
|
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS foreign_schema varchar(100);
|
|
12
|
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS foreign_table VARCHAR(100); -- Die Spalte referenziert auf diese Tabelle ...
|
|
13
|
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS foreign_column VARCHAR(50); -- ... und Spalte
|
|
14
|
ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS isWritable BOOLEAN DEFAULT false;
|
|
15
|
|
|
16
|
ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_gruppe_textno INTEGER;
|
|
17
|
ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_bez_textno INTEGER;
|
|
18
|
|
|
19
|
ALTER TABLE SettingsDyn ADD COLUMN IF NOT EXISTS sd_deleted BOOLEAN DEFAULT False;
|
|
20
|
|
|
21
|
ALTER TABLE gridlayout ADD COLUMN IF NOT EXISTS gl_default BOOLEAN NOT NULL DEFAULT true;
|
|
22
|
|
|
23
|
ALTER TABLE adk ADD COLUMN IF NOT EXISTS ad_dokversand INTEGER;
|
|
24
|
|
|
25
|
ALTER TABLE abzu ADD COLUMN IF NOT EXISTS abz_visible BOOLEAN NOT NULL DEFAULT TRUE;
|
|
26
|
ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_visible BOOLEAN NOT NULL DEFAULT TRUE;
|
|
27
|
|
|
28
|
|
|
29
|
BEGIN
|
|
30
|
CREATE EXTENSION IF NOT EXISTS dblink;
|
|
31
|
EXCEPTION
|
|
32
|
WHEN others THEN
|
|
33
|
RAISE WARNING '%', SQLERRM;
|
|
34
|
END;
|
|
35
|
|
|
36
|
BEGIN
|
|
37
|
|
|
38
|
CREATE FUNCTION TSystem.Settings__ENUM__Set(vari VARCHAR, value VARCHAR) RETURNS VOID AS $$
|
|
39
|
BEGIN
|
|
40
|
PERFORM TSystem.Settings__Set(vari, TSystem.ENUM_SetValue(TSystem.Settings__Get(vari), value));
|
|
41
|
RETURN;
|
|
42
|
END$$LANGUAGE plpgsql;
|
|
43
|
|
|
44
|
EXCEPTION
|
|
45
|
WHEN others THEN
|
|
46
|
RAISE WARNING '%', SQLERRM;
|
|
47
|
END;
|
|
48
|
|
|
49
|
BEGIN
|
|
50
|
|
|
51
|
CREATE FUNCTION TSystem.Settings__ENUM__Set(vari VARCHAR, value VARCHAR, enabled BOOL) RETURNS VOID AS $$
|
|
52
|
BEGIN
|
|
53
|
IF enabled THEN
|
|
54
|
PERFORM TSystem.Settings__Set(vari, TSystem.ENUM_SetValue(TSystem.Settings__Get(vari), value));
|
|
55
|
ELSE
|
|
56
|
PERFORM TSystem.Settings__Set(vari, TSystem.ENUM_DelValue(TSystem.Settings__Get(vari), value));
|
|
57
|
END IF;
|
|
58
|
RETURN;
|
|
59
|
END$$LANGUAGE plpgsql;
|
|
60
|
|
|
61
|
EXCEPTION
|
|
62
|
WHEN others THEN
|
|
63
|
RAISE WARNING '%', SQLERRM;
|
|
64
|
END;
|
|
65
|
|
|
66
|
BEGIN
|
|
67
|
|
|
68
|
CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha BOOL) RETURNS VOID AS $$
|
|
69
|
BEGIN
|
|
70
|
IF inha IS NULL THEN
|
|
71
|
PERFORM TSystem.Settings__Delete(vari);
|
|
72
|
RETURN;
|
|
73
|
END IF;
|
|
74
|
IF inha THEN
|
|
75
|
PERFORM TSystem.Settings__Set(vari, '-1'); -- true
|
|
76
|
ELSE
|
|
77
|
PERFORM TSystem.Settings__Set(vari, '0'); -- false
|
|
78
|
END IF;
|
|
79
|
|
|
80
|
RETURN;
|
|
81
|
END $$ LANGUAGE plpgsql;
|
|
82
|
|
|
83
|
EXCEPTION
|
|
84
|
WHEN others THEN
|
|
85
|
RAISE WARNING '%', SQLERRM;
|
|
86
|
END;
|
|
87
|
|
|
88
|
BEGIN
|
|
89
|
|
|
90
|
-- Setting als VARCHAR setzen
|
|
91
|
CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha VARCHAR) RETURNS VOID AS $$
|
|
92
|
DECLARE num NUMERIC;
|
|
93
|
BEGIN
|
|
94
|
IF inha IS NULL THEN
|
|
95
|
PERFORM TSystem.Settings__Delete(vari);
|
|
96
|
RETURN;
|
|
97
|
END IF;
|
|
98
|
IF EXISTS(SELECT true FROM public.settings WHERE s_inha = inha AND s_vari = vari) THEN
|
|
99
|
RETURN;
|
|
100
|
END IF;
|
|
101
|
BEGIN
|
|
102
|
num:= inha::NUMERIC(20,8); -- Cast auf max. Numeric versuchen. Länge entspr. Standard für interne Werte.
|
|
103
|
EXCEPTION
|
|
104
|
WHEN OTHERS THEN num:= NULL;
|
|
105
|
END;
|
|
106
|
UPDATE settings SET s_inha= inha, s_num_inha= num WHERE s_vari = vari;
|
|
107
|
IF NOT FOUND THEN
|
|
108
|
INSERT INTO settings (s_vari, s_inha, s_num_inha) VALUES (vari, inha, num);
|
|
109
|
END IF;
|
|
110
|
|
|
111
|
RETURN;
|
|
112
|
END $$ LANGUAGE plpgsql;
|
|
113
|
|
|
114
|
EXCEPTION
|
|
115
|
WHEN others THEN
|
|
116
|
RAISE WARNING '%', SQLERRM;
|
|
117
|
END;
|
|
118
|
|
|
119
|
BEGIN
|
|
120
|
|
|
121
|
CREATE FUNCTION TSystem.Settings__GetText(vari varchar, defvalue varchar DEFAULT '')
|
|
122
|
RETURNS text
|
|
123
|
AS $$
|
|
124
|
SELECT coalesce(
|
|
125
|
(SELECT s_inha FROM public.settings WHERE s_vari = vari)
|
|
126
|
, defvalue
|
|
127
|
);
|
|
128
|
$$ LANGUAGE sql PARALLEL SAFE;
|
|
129
|
|
|
130
|
EXCEPTION
|
|
131
|
WHEN others THEN
|
|
132
|
RAISE WARNING '%', SQLERRM;
|
|
133
|
END;
|
|
134
|
|
|
135
|
BEGIN
|
|
136
|
|
|
137
|
-- Hinweis: Begrenzung von den 8191 Zeichen, im PgDAC, bei vom VARCHAR ohne Längenangabe.
|
|
138
|
CREATE FUNCTION TSystem.Settings__Get(vari varchar)
|
|
139
|
RETURNS varchar
|
|
140
|
AS $$
|
|
141
|
SELECT TSystem.Settings__GetText(vari);
|
|
142
|
$$LANGUAGE sql PARALLEL SAFE;
|
|
143
|
|
|
144
|
EXCEPTION
|
|
145
|
WHEN others THEN
|
|
146
|
RAISE WARNING '%', SQLERRM;
|
|
147
|
END;
|
|
148
|
|
|
149
|
BEGIN
|
|
150
|
|
|
151
|
CREATE FUNCTION TSystem.Settings__ENUM__Get(vari varchar, value varchar) RETURNS bool AS $$
|
|
152
|
BEGIN
|
|
153
|
RETURN TSystem.ENUM_GetValue(TSystem.Settings__Get(vari), value);
|
|
154
|
END$$LANGUAGE plpgsql PARALLEL SAFE;
|
|
155
|
|
|
156
|
EXCEPTION
|
|
157
|
WHEN others THEN
|
|
158
|
RAISE WARNING '%', SQLERRM;
|
|
159
|
END;
|
|
160
|
|
|
161
|
BEGIN
|
|
162
|
|
|
163
|
CREATE FUNCTION TSystem.Settings__ENUM__Get(vari varchar) RETURNS SETOF varchar AS $$
|
|
164
|
BEGIN
|
|
165
|
RETURN QUERY SELECT unnest(string_to_array(coalesce(TSystem.Settings__Get(vari), ''), ',')::varchar[]);
|
|
166
|
END$$LANGUAGE plpgsql PARALLEL SAFE;
|
|
167
|
|
|
168
|
EXCEPTION
|
|
169
|
WHEN others THEN
|
|
170
|
RAISE WARNING '%', SQLERRM;
|
|
171
|
END;
|
|
172
|
|
|
173
|
BEGIN
|
|
174
|
|
|
175
|
CREATE FUNCTION TSystem.Settings__GetInteger(vari varchar, defvalue integer DEFAULT 0) RETURNS integer AS $$
|
|
176
|
DECLARE r varchar;
|
|
177
|
BEGIN
|
|
178
|
SELECT s_inha INTO r FROM public.settings WHERE s_vari=vari;
|
|
179
|
IF NOT public.isnumeric(r) THEN --public wegen dump/restore
|
|
180
|
RETURN defvalue;
|
|
181
|
END IF;
|
|
182
|
|
|
183
|
RETURN coalesce(CAST(r AS integer), defvalue);
|
|
184
|
-- ist "parallel unsafe", da es die parallel unsafe Funktion IsNumeric verwendet (#21507)
|
|
185
|
END $$ LANGUAGE plpgsql; -- TODO ab PG16 auf parallel safe zurück siehe IsNumeric (https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-VALIDITY)
|
|
186
|
|
|
187
|
EXCEPTION
|
|
188
|
WHEN others THEN
|
|
189
|
RAISE WARNING '%', SQLERRM;
|
|
190
|
END;
|
|
191
|
|
|
192
|
BEGIN
|
|
193
|
|
|
194
|
CREATE FUNCTION TSystem.Settings__GetNumeric(vari varchar) RETURNS numeric AS $$
|
|
195
|
DECLARE r numeric;
|
|
196
|
BEGIN
|
|
197
|
BEGIN
|
|
198
|
SELECT coalesce(s_num_inha, s_inha::numeric) INTO r FROM public.settings WHERE s_vari = vari;
|
|
199
|
EXCEPTION
|
|
200
|
WHEN OTHERS THEN
|
|
201
|
BEGIN
|
|
202
|
RETURN 0;
|
|
203
|
END;
|
|
204
|
END;
|
|
205
|
|
|
206
|
RETURN coalesce(r, 0);
|
|
207
|
END $$ LANGUAGE plpgsql PARALLEL SAFE;
|
|
208
|
|
|
209
|
EXCEPTION
|
|
210
|
WHEN others THEN
|
|
211
|
RAISE WARNING '%', SQLERRM;
|
|
212
|
END;
|
|
213
|
|
|
214
|
BEGIN
|
|
215
|
|
|
216
|
CREATE FUNCTION TSystem.Settings__GetBool(vari varchar, defvalue boolean DEFAULT false) RETURNS boolean AS $$
|
|
217
|
DECLARE r boolean;
|
|
218
|
s varchar;
|
|
219
|
BEGIN
|
|
220
|
SELECT s_inha INTO s FROM public.settings WHERE s_vari = vari;
|
|
221
|
--
|
|
222
|
IF NOT FOUND THEN
|
|
223
|
RETURN defvalue;
|
|
224
|
END IF;
|
|
225
|
--
|
|
226
|
IF UPPER(s) IN ('-1','1', 'T', 'TRUE') THEN
|
|
227
|
RETURN true;
|
|
228
|
ELSE
|
|
229
|
RETURN false;
|
|
230
|
END IF;
|
|
231
|
END$$LANGUAGE plpgsql PARALLEL SAFE;
|
|
232
|
|
|
233
|
EXCEPTION
|
|
234
|
WHEN others THEN
|
|
235
|
RAISE WARNING '%', SQLERRM;
|
|
236
|
END;
|
|
237
|
|
|
238
|
BEGIN
|
|
239
|
|
|
240
|
|
|
241
|
|
|
242
|
EXCEPTION
|
|
243
|
WHEN others THEN
|
|
244
|
RAISE WARNING '%', SQLERRM;
|
|
245
|
END;
|
|
246
|
|
|
247
|
BEGIN
|
|
248
|
|
|
249
|
ALTER TABLE dbupdates ALTER COLUMN upd_bez TYPE varchar(150);
|
|
250
|
|
|
251
|
EXCEPTION
|
|
252
|
WHEN others THEN
|
|
253
|
RAISE WARNING '%', SQLERRM;
|
|
254
|
END;
|
|
255
|
|
|
256
|
BEGIN
|
|
257
|
|
|
258
|
CREATE OR REPLACE FUNCTION tsystem.quote_literal__connstr_param(
|
|
259
|
IN _text varchar
|
|
260
|
) RETURNS varchar AS $$
|
|
261
|
DECLARE
|
|
262
|
_result varchar;
|
|
263
|
BEGIN
|
|
264
|
_result := REPLACE( REPLACE( _text, E'\\', E'\\\\' ), E'\'', E'\\\'');
|
|
265
|
_result := concat( '''',
|
|
266
|
_result,
|
|
267
|
''''
|
|
268
|
);
|
|
269
|
RETURN _result;
|
|
270
|
END; $$ LANGUAGE plpgsql;
|
|
271
|
|
|
272
|
EXCEPTION
|
|
273
|
WHEN others THEN
|
|
274
|
RAISE WARNING '%', SQLERRM;
|
|
275
|
END;
|
|
276
|
|
|
277
|
BEGIN
|
|
278
|
|
|
279
|
CREATE FUNCTION TSystem.views__all__drop() RETURNS void AS $$
|
|
280
|
BEGIN
|
|
281
|
RETURN;
|
|
282
|
END $$ LANGUAGE plpgsql;
|
|
283
|
|
|
284
|
EXCEPTION
|
|
285
|
WHEN others THEN
|
|
286
|
RAISE WARNING '%', SQLERRM;
|
|
287
|
END;
|
|
288
|
|
|
289
|
BEGIN
|
|
290
|
|
|
291
|
CREATE FUNCTION TSystem.views__all__create() RETURNS VOID AS $$
|
|
292
|
BEGIN
|
|
293
|
RETURN;
|
|
294
|
END $$ LANGUAGE plpgsql;
|
|
295
|
|
|
296
|
EXCEPTION
|
|
297
|
WHEN others THEN
|
|
298
|
RAISE WARNING '%', SQLERRM;
|
|
299
|
END;
|
|
300
|
|
|
301
|
BEGIN
|
|
302
|
|
|
303
|
CREATE FUNCTION TSystem.views__Adressen__recreate() RETURNS VOID AS $$
|
|
304
|
BEGIN
|
|
305
|
DROP VIEW IF EXISTS adressen_view CASCADE;
|
|
306
|
|
|
307
|
CREATE OR REPLACE VIEW adressen_view AS
|
|
308
|
SELECT
|
|
309
|
dbrid,
|
|
310
|
ad_krz AS adk_ad_krz,
|
|
311
|
ad_krz,
|
|
312
|
ad_fa1,
|
|
313
|
ad_fa2,
|
|
314
|
ad_adrzus,
|
|
315
|
ad_anr,
|
|
316
|
ad_titel,
|
|
317
|
ad_name,
|
|
318
|
ad_vorn,
|
|
319
|
ad_str,
|
|
320
|
ad_plz,
|
|
321
|
ad_ort,
|
|
322
|
ad_landiso::VARCHAR(5),
|
|
323
|
ad_land,
|
|
324
|
ad_pfc,
|
|
325
|
ad_fax,
|
|
326
|
ad_tel1,
|
|
327
|
ad_tel2,
|
|
328
|
ad_email1,
|
|
329
|
ad_email2,
|
|
330
|
ad_such,
|
|
331
|
ad_ustidnr,
|
|
332
|
ad_branche,
|
|
333
|
ad_stat,
|
|
334
|
ad_bem,
|
|
335
|
adk.modified_date,
|
|
336
|
ad_auslauf
|
|
337
|
FROM adk;
|
|
338
|
RETURN;
|
|
339
|
END $$ LANGUAGE plpgsql;
|
|
340
|
|
|
341
|
EXCEPTION
|
|
342
|
WHEN others THEN
|
|
343
|
RAISE WARNING '%', SQLERRM;
|
|
344
|
END;
|
|
345
|
|
|
346
|
BEGIN
|
|
347
|
|
|
348
|
SELECT TSystem.views__Adressen__recreate();
|
|
349
|
|
|
350
|
EXCEPTION
|
|
351
|
WHEN others THEN
|
|
352
|
RAISE WARNING '%', SQLERRM;
|
|
353
|
END;
|
|
354
|
|
|
355
|
|
|
356
|
BEGIN
|
|
357
|
|
|
358
|
CREATE SCHEMA IF NOT EXISTS tCache;
|
|
359
|
|
|
360
|
EXCEPTION
|
|
361
|
WHEN others THEN
|
|
362
|
RAISE WARNING '%', SQLERRM;
|
|
363
|
END;
|
|
364
|
|
|
365
|
BEGIN
|
|
366
|
|
|
367
|
CREATE FUNCTION TCache.CachedViews_Clear() RETURNS BOOLEAN AS $$
|
|
368
|
DECLARE
|
|
369
|
r RECORD;
|
|
370
|
e BOOLEAN;
|
|
371
|
BEGIN
|
|
372
|
RETURN false;
|
|
373
|
END $$ LANGUAGE plpgsql;
|
|
374
|
|
|
375
|
EXCEPTION
|
|
376
|
WHEN others THEN
|
|
377
|
RAISE WARNING '%', SQLERRM;
|
|
378
|
END;
|
|
379
|
|
|
380
|
BEGIN
|
|
381
|
|
|
382
|
CREATE FUNCTION table_delete() RETURNS TRIGGER AS $$
|
|
383
|
BEGIN
|
|
384
|
RETURN old;
|
|
385
|
END $$ LANGUAGE plpgsql;
|
|
386
|
|
|
387
|
EXCEPTION
|
|
388
|
WHEN others THEN
|
|
389
|
RAISE WARNING '%', SQLERRM;
|
|
390
|
END;
|
|
391
|
|
|
392
|
BEGIN
|
|
393
|
|
|
394
|
CREATE FUNCTION TSystem.views__ReportingAuftg__recreate() RETURNS VOID AS $$
|
|
395
|
BEGIN
|
|
396
|
DROP VIEW IF EXISTS treporting.auftg_beleg_positionennachtrag;
|
|
397
|
DROP VIEW IF EXISTS treporting.auftg_beleg_positionen;
|
|
398
|
END $$ LANGUAGE plpgsql;
|
|
399
|
|
|
400
|
EXCEPTION
|
|
401
|
WHEN others THEN
|
|
402
|
RAISE WARNING '%', SQLERRM;
|
|
403
|
END;
|
|
404
|
|
|
405
|
BEGIN
|
|
406
|
|
|
407
|
CREATE FUNCTION treporting.recreatereportingauftgviews( ) RETURNS void AS $$
|
|
408
|
BEGIN
|
|
409
|
RETURN;
|
|
410
|
END $$ LANGUAGE plpgsql;
|
|
411
|
|
|
412
|
EXCEPTION
|
|
413
|
WHEN others THEN
|
|
414
|
RAISE WARNING '%', SQLERRM;
|
|
415
|
END;
|
|
416
|
|
|
417
|
BEGIN
|
|
418
|
|
|
419
|
CREATE OR REPLACE FUNCTION treporting.RecreateViews() RETURNS void AS $$
|
|
420
|
BEGIN
|
|
421
|
RETURN;
|
|
422
|
END $$ LANGUAGE plpgsql;
|
|
423
|
|
|
424
|
EXCEPTION
|
|
425
|
WHEN others THEN
|
|
426
|
RAISE WARNING '%', SQLERRM;
|
|
427
|
END;
|
|
428
|
|
|
429
|
BEGIN
|
|
430
|
|
|
431
|
CREATE OR REPLACE FUNCTION TWawi.RecreateViews() RETURNS void AS $$
|
|
432
|
BEGIN
|
|
433
|
RETURN;
|
|
434
|
END $$ LANGUAGE plpgsql;
|
|
435
|
|
|
436
|
EXCEPTION
|
|
437
|
WHEN others THEN
|
|
438
|
RAISE WARNING '%', SQLERRM;
|
|
439
|
END;
|
|
440
|
|
|
441
|
BEGIN
|
|
442
|
|
|
443
|
CREATE OR REPLACE FUNCTION DropBelegViews() RETURNS VOID AS $$
|
|
444
|
BEGIN
|
|
445
|
DROP VIEW IF EXISTS eingrech;
|
|
446
|
DROP VIEW IF EXISTS eingrech_pos;
|
|
447
|
DROP VIEW IF EXISTS lieferschein;
|
|
448
|
DROP VIEW IF EXISTS lieferschein_pos;
|
|
449
|
END $$ LANGUAGE plpgsql;
|
|
450
|
|
|
451
|
EXCEPTION
|
|
452
|
WHEN others THEN
|
|
453
|
RAISE WARNING '%', SQLERRM;
|
|
454
|
END;
|
|
455
|
|
|
456
|
BEGIN
|
|
457
|
|
|
458
|
CREATE FUNCTION lang_versart(INTEGER, VARCHAR) RETURNS VARCHAR AS $$ -- #7137
|
|
459
|
BEGIN
|
|
460
|
RETURN null;
|
|
461
|
END $$ LANGUAGE plpgsql;
|
|
462
|
|
|
463
|
EXCEPTION
|
|
464
|
WHEN others THEN
|
|
465
|
RAISE WARNING '%', SQLERRM;
|
|
466
|
END;
|
|
467
|
|
|
468
|
BEGIN
|
|
469
|
|
|
470
|
-- Value aus einem INI-TEXT auslesen -> [Section] Name=Value
|
|
471
|
CREATE FUNCTION TSystem.INI_GetValue(ini TEXT, section VARCHAR, name VARCHAR, defvalue VARCHAR DEFAULT NULL) RETURNS VARCHAR AS $$
|
|
472
|
DECLARE
|
|
473
|
sectionsinhalt TEXT;
|
|
474
|
val VARCHAR;
|
|
475
|
BEGIN
|
|
476
|
--Inhalt der eingegebenen 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden
|
|
477
|
SELECT trim(substring(ini FROM E'\\[' || section || E'\\]([^\\[]*)'), E'\r\n\t ') INTO sectionsinhalt;
|
|
478
|
IF sectionsinhalt IS NULL THEN
|
|
479
|
RETURN defvalue;
|
|
480
|
END IF;
|
|
481
|
--Value mit dem 'name' aus der 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden
|
|
482
|
SELECT COALESCE(trim(substring(sectionsinhalt FROM E'[$\r\n]*' || name || E'[ \t]*=[ \t]*([^\r\n$]*)'), E'\r\n\t '), defvalue) INTO val;
|
|
483
|
RETURN val;
|
|
484
|
END $$ LANGUAGE plpgsql IMMUTABLE;
|
|
485
|
|
|
486
|
EXCEPTION
|
|
487
|
WHEN others THEN
|
|
488
|
RAISE WARNING '%', SQLERRM;
|
|
489
|
END;
|
|
490
|
|
|
491
|
BEGIN
|
|
492
|
|
|
493
|
CREATE FUNCTION tsystem.dokutypes__find__by__parentnode_id( _parentnode_id varchar )
|
|
494
|
RETURNS varchar
|
|
495
|
AS $$
|
|
496
|
SELECT null
|
|
497
|
$$ LANGUAGE sql;
|
|
498
|
|
|
499
|
|
|
500
|
EXCEPTION
|
|
501
|
WHEN others THEN
|
|
502
|
RAISE WARNING '%', SQLERRM;
|
|
503
|
END;
|
|
504
|
|
|
505
|
|
|
506
|
BEGIN
|
|
507
|
|
|
508
|
CREATE FUNCTION tsystem.database__triggers_inaktiv__get(
|
|
509
|
IN _all_schemas boolean = false
|
|
510
|
)
|
|
511
|
RETURNS TABLE (
|
|
512
|
tgname varchar,
|
|
513
|
src_schemaname varchar,
|
|
514
|
src_tablename varchar,
|
|
515
|
trg_schemaname varchar,
|
|
516
|
trg_tablename varchar
|
|
517
|
)
|
|
518
|
AS $$
|
|
519
|
-- hinweis: cast as varchar nur in pg kleiner 13?!
|
|
520
|
SELECT tgname::varchar,
|
|
521
|
src_schema.nspname::varchar AS src_schemaname,
|
|
522
|
src_table.relname::varchar AS src_tablename,
|
|
523
|
trg_schema.nspname::varchar AS trg_schemaname,
|
|
524
|
trg_table.relname::varchar AS trg_tablename
|
|
525
|
FROM pg_trigger
|
|
526
|
LEFT JOIN pg_class AS src_table ON src_table.oid = pg_trigger.tgrelid
|
|
527
|
LEFT JOIN pg_class AS trg_table ON trg_table.oid = pg_trigger.tgconstrrelid
|
|
528
|
LEFT JOIN pg_namespace AS src_schema ON src_schema.oid = src_table.relnamespace
|
|
529
|
LEFT JOIN pg_namespace AS trg_schema ON trg_schema.oid = trg_table.relnamespace
|
|
530
|
WHERE tgenabled = 'D'
|
|
531
|
AND (
|
|
532
|
-- Prüfung auf deaktivierte Trigger für alle Schemata
|
|
533
|
_all_schemas
|
|
534
|
-- Prüfung auf deaktivierte Trigger gilt nicht für folg. Schemata
|
|
535
|
-- ignore: trigger on drop, fkey: public -> drop, drop -> public, drop -> drop
|
|
536
|
-- relevant: trigger on public, fkey: public -> public
|
|
537
|
OR (
|
|
538
|
-- Quelle des deaktivierten Triggers bzw. Contraint-Triggers (fKey) ist nicht in folg. Schemata
|
|
539
|
coalesce( src_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )
|
|
540
|
-- analog für Ziel des Contraint-Triggers (fKey)
|
|
541
|
AND coalesce( trg_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )
|
|
542
|
)
|
|
543
|
)
|
|
544
|
ORDER BY tgname
|
|
545
|
$$ LANGUAGE sql;
|
|
546
|
|
|
547
|
|
|
548
|
EXCEPTION
|
|
549
|
WHEN others THEN
|
|
550
|
RAISE WARNING '%', SQLERRM;
|
|
551
|
END;
|
|
552
|
|
|
553
|
|
|
554
|
BEGIN
|
|
555
|
|
|
556
|
CREATE FUNCTION TSystem.triggers__all__disable() RETURNS VOID AS $$
|
|
557
|
DECLARE r RECORD;
|
|
558
|
BEGIN
|
|
559
|
BEGIN
|
|
560
|
PERFORM TSystem.Settings__Set('triggersdisabled', True);
|
|
561
|
EXCEPTION WHEN OTHERS THEN
|
|
562
|
PERFORM TSystem.Settings__Set('triggersdisabled', True);
|
|
563
|
END;
|
|
564
|
|
|
565
|
FOR r IN
|
|
566
|
--SELECT DISTINCT schemaname, tablename
|
|
567
|
--FROM tablefieldinfo
|
|
568
|
--WHERE schemaname NOT IN ('z_99_drop', 'import')
|
|
569
|
-- AND tablename NOT IN ('tablefieldinfo') -- es gibt ein SELECT darauf, drum lässt sich kein ALTER TABLE machen
|
|
570
|
-- AND NOT isView
|
|
571
|
SELECT DISTINCT table_schema AS schemaname, table_name AS tablename
|
|
572
|
FROM information_schema.tables
|
|
573
|
WHERE table_catalog = current_database()
|
|
574
|
AND table_type = 'BASE TABLE'
|
|
575
|
AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')
|
|
576
|
LOOP
|
|
577
|
EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' DISABLE TRIGGER ALL';
|
|
578
|
END LOOP;
|
|
579
|
END $$ LANGUAGE plpgsql;
|
|
580
|
|
|
581
|
|
|
582
|
EXCEPTION
|
|
583
|
WHEN others THEN
|
|
584
|
RAISE WARNING '%', SQLERRM;
|
|
585
|
END;
|
|
586
|
|
|
587
|
|
|
588
|
BEGIN
|
|
589
|
|
|
590
|
CREATE FUNCTION TSystem.triggers__all__enable() RETURNS VOID AS $$
|
|
591
|
DECLARE r RECORD;
|
|
592
|
BEGIN
|
|
593
|
FOR r IN -- aus TFormDBUpdates.Up_EnableTrigger -> lokales CodeSelect
|
|
594
|
SELECT DISTINCT table_schema AS schemaname, table_name AS tablename
|
|
595
|
FROM information_schema.tables
|
|
596
|
WHERE table_catalog = current_database()
|
|
597
|
AND table_type = 'BASE TABLE'
|
|
598
|
AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')
|
|
599
|
LOOP
|
|
600
|
EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' ENABLE TRIGGER ALL';
|
|
601
|
END LOOP;
|
|
602
|
|
|
603
|
BEGIN
|
|
604
|
PERFORM TSystem.Settings__Set('triggersdisabled', False);
|
|
605
|
EXCEPTION WHEN OTHERS THEN
|
|
606
|
PERFORM TSystem.Settings__Set('triggersdisabled', False);
|
|
607
|
END;
|
|
608
|
END $$ LANGUAGE plpgsql;
|
|
609
|
|
|
610
|
|
|
611
|
EXCEPTION
|
|
612
|
WHEN others THEN
|
|
613
|
RAISE WARNING '%', SQLERRM;
|
|
614
|
END;
|
|
615
|
|
|
616
|
|
|
617
|
BEGIN
|
|
618
|
|
|
619
|
CREATE FUNCTION TSystem.LogDebug(source VARCHAR, message TEXT, category INTEGER DEFAULT 0, context TEXT DEFAULT NULL) RETURNS VOID AS $$
|
|
620
|
BEGIN
|
|
621
|
RETURN;
|
|
622
|
END $$ LANGUAGE plpgsql;
|
|
623
|
|
|
624
|
|
|
625
|
EXCEPTION
|
|
626
|
WHEN others THEN
|
|
627
|
RAISE WARNING '%', SQLERRM;
|
|
628
|
END;
|
|
629
|
|
|
630
|
|
|
631
|
BEGIN
|
|
632
|
|
|
633
|
|
|
634
|
|
|
635
|
EXCEPTION
|
|
636
|
WHEN others THEN
|
|
637
|
RAISE WARNING '%', SQLERRM;
|
|
638
|
END;
|
|
639
|
|
|
640
|
|
|
641
|
BEGIN
|
|
642
|
|
|
643
|
|
|
644
|
|
|
645
|
EXCEPTION
|
|
646
|
WHEN others THEN
|
|
647
|
RAISE WARNING '%', SQLERRM;
|
|
648
|
END;
|
|
649
|
|
|
650
|
|
|
651
|
BEGIN
|
|
652
|
|
|
653
|
|
|
654
|
|
|
655
|
EXCEPTION
|
|
656
|
WHEN others THEN
|
|
657
|
RAISE WARNING '%', SQLERRM;
|
|
658
|
END;
|
|
659
|
|
|
660
|
|
|
661
|
BEGIN
|
|
662
|
|
|
663
|
|
|
664
|
|
|
665
|
EXCEPTION
|
|
666
|
WHEN others THEN
|
|
667
|
RAISE WARNING '%', SQLERRM;
|
|
668
|
END;
|
|
669
|
|
|
670
|
|
|
671
|
BEGIN
|
|
672
|
|
|
673
|
EXCEPTION
|
|
674
|
WHEN others THEN
|
|
675
|
RAISE WARNING '%', SQLERRM;
|
|
676
|
END;
|
|
677
|
|
|
678
|
|
|
679
|
BEGIN
|
|
680
|
|
|
681
|
DROP VIEW IF EXISTS TSystem.tablefieldinfo_view;
|
|
682
|
CREATE VIEW TSystem.tablefieldinfo_view AS
|
|
683
|
|
|
684
|
SELECT
|
|
685
|
-- this filters out composite indicies and duplicated FKs on same columns
|
|
686
|
DISTINCT ON ( n.nspname, c.relname, a.attnum )
|
|
687
|
|
|
688
|
-- this function is used by the information schema views
|
|
689
|
-- the bitmask works like this:
|
|
690
|
-- b'00000100' = deleteable
|
|
691
|
-- b'00001000' = updateable
|
|
692
|
-- b'00010000' = insertable
|
|
693
|
(pg_relation_is_updatable(c.oid::regclass, false)::bit(8) & b'00011100' = b'00011100' ) AS isWritable,
|
|
694
|
|
|
695
|
c.relkind = 'v' AS isview,
|
|
696
|
n.nspname AS schemaname,
|
|
697
|
c.relname AS tablename,
|
|
698
|
a.attnum AS index,
|
|
699
|
a.attname AS field,
|
|
700
|
|
|
701
|
-- t.typcategory,
|
|
702
|
format_type( a.atttypid, null ) AS type,
|
|
703
|
|
|
704
|
-- length only for string types
|
|
705
|
CASE WHEN
|
|
706
|
-- S = varchars und chars
|
|
707
|
t.typcategory IN ( 'S' )
|
|
708
|
THEN nullif(a.atttypmod,-1) - 4
|
|
709
|
ELSE null
|
|
710
|
END
|
|
711
|
AS length,
|
|
712
|
|
|
713
|
-- adsrc was removed in pg12
|
|
714
|
pg_get_expr( d.adbin, d.adrelid ) AS "default",
|
|
715
|
|
|
716
|
fn.nspname AS foreign_table_schema,
|
|
717
|
ft.relname AS foreign_table_name,
|
|
718
|
fc.attname AS foreign_column_name
|
|
719
|
|
|
720
|
FROM
|
|
721
|
pg_class AS c
|
|
722
|
|
|
723
|
-- schemata
|
|
724
|
LEFT JOIN pg_namespace AS n ON
|
|
725
|
n.oid = c.relnamespace
|
|
726
|
|
|
727
|
-- columns
|
|
728
|
LEFT JOIN pg_attribute AS a ON
|
|
729
|
c.oid = a.attrelid
|
|
730
|
|
|
731
|
-- columntypes
|
|
732
|
LEFT JOIN pg_type AS t ON
|
|
733
|
t.oid = a.atttypid
|
|
734
|
|
|
735
|
-- default values of columns
|
|
736
|
LEFT JOIN pg_attrdef AS d ON
|
|
737
|
d.adrelid = c.oid
|
|
738
|
AND d.adnum = a.attnum
|
|
739
|
|
|
740
|
-- FKs
|
|
741
|
LEFT JOIN pg_constraint AS f ON
|
|
742
|
f.contype = 'f' -- foreignkey
|
|
743
|
AND f.conrelid = c.oid -- local table
|
|
744
|
AND array_length ( f.confkey , 1 ) < 2 -- exclude composite keys
|
|
745
|
AND f.conkey[1] = a.attnum -- conkey is a array over the attributes ordinal positions
|
|
746
|
|
|
747
|
-- destination of FKs
|
|
748
|
LEFT JOIN pg_attribute AS fc ON
|
|
749
|
f.confrelid = fc.attrelid -- foreign table
|
|
750
|
AND f.confkey[1] = fc.attnum
|
|
751
|
|
|
752
|
LEFT JOIN pg_class AS ft ON
|
|
753
|
ft.oid = f.confrelid
|
|
754
|
|
|
755
|
LEFT JOIN pg_namespace AS fn ON
|
|
756
|
fn.oid = ft.relnamespace
|
|
757
|
|
|
758
|
WHERE
|
|
759
|
-- only tables and views ( including materialized views )
|
|
760
|
c.relkind IN ('r','v','m')
|
|
761
|
-- p = permanent table ( default tables)
|
|
762
|
-- u = unlogged table ( eg. tlog.auditlog )
|
|
763
|
-- t = temporary table ( pg_temp_%, create temp table )
|
|
764
|
AND c.relpersistence IN ('p','u')
|
|
765
|
-- exclude certain namespaces; they are lowercase by default, except for doubleQuoted Identifier
|
|
766
|
AND n.nspname NOT IN (
|
|
767
|
'pg_catalog', 'information_schema', 'tcache', 'tsystem', 'sunext', 'import', 'z_99_drop', 'x_900_export'
|
|
768
|
)
|
|
769
|
AND a.attnum > 0
|
|
770
|
-- they have been deleted anyways by the TSystem.tablefieldinfo__recreate() function
|
|
771
|
AND a.attname NOT IN ('insert_by', 'modified_by', 'insert_date', 'modified_date')
|
|
772
|
-- we must omit dropped columns
|
|
773
|
AND a.attisdropped IS false
|
|
774
|
|
|
775
|
ORDER BY schemaname, tablename, index
|
|
776
|
;
|
|
777
|
--
|
|
778
|
|
|
779
|
EXCEPTION
|
|
780
|
WHEN others THEN
|
|
781
|
RAISE WARNING '%', SQLERRM;
|
|
782
|
END;
|
|
783
|
|
|
784
|
|
|
785
|
BEGIN
|
|
786
|
|
|
787
|
-- Ausgelagert aus Delphi. Ergänzt um Weitergabe der Constraints zugrundeliegender Tabelle an die Views.
|
|
788
|
|
|
789
|
CREATE FUNCTION TSystem.tablefieldinfo__recreate(OUT fieldCountBefore INTEGER, OUT fieldCountAfter INTEGER) RETURNS RECORD AS $$
|
|
790
|
BEGIN
|
|
791
|
FieldCountBefore := COUNT(*) FROM tablefieldinfo;
|
|
792
|
-- Einfach alles rauswerfen und neu aufbauen aus den Information-Schema Katalogen.
|
|
793
|
TRUNCATE tablefieldinfo;
|
|
794
|
INSERT INTO tablefieldinfo ( isWritable, isView, schemaname, tablename, index, field, type, length, def, foreign_schema, foreign_table, foreign_column)
|
|
795
|
SELECT * FROM TSystem.TableFieldInfo_view ORDER BY schemaname, tablename, field;
|
|
796
|
-- View ist Updatable und wird als ChildLink benutzt. Also brauchen wir Defaults und Foreign-Keys.
|
|
797
|
PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_data', 'public', 'oplpm');
|
|
798
|
PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_stam', 'public', 'oplpm_data');
|
|
799
|
-- view dbrid default wert setzen
|
|
800
|
UPDATE tablefieldinfo SET def = 'nextval(''db_id_seq'')' WHERE isView AND isWritable AND field = 'dbrid';
|
|
801
|
FieldCountAfter := COUNT(*) FROM tablefieldinfo;
|
|
802
|
--- #18294 Auditlog-Config-FieldList Refresh
|
|
803
|
--PERFORM tlog.AuditlogConfig_OF__all__recreate();
|
|
804
|
RETURN;
|
|
805
|
END $$ LANGUAGE plpgsql VOLATILE;
|
|
806
|
|
|
807
|
|
|
808
|
EXCEPTION
|
|
809
|
WHEN others THEN
|
|
810
|
RAISE WARNING '%', SQLERRM;
|
|
811
|
END;
|
|
812
|
|
|
813
|
|
|
814
|
BEGIN
|
|
815
|
|
|
816
|
CREATE FUNCTION dbupdates__filtered__nodes__upd_ids__get() RETURNS SETOF VARCHAR AS $$
|
|
817
|
WITH _nodes AS (
|
|
818
|
SELECT upd_id FROM dbupdates
|
|
819
|
WHERE upd_donedat >= current_timestamp - '90 days'::INTERVAL
|
|
820
|
OR upd_donedat IS NULL
|
|
821
|
OR coalesce(modified_date, insert_date::TIMESTAMP) >= current_timestamp - '90 days'::INTERVAL
|
|
822
|
OR upd_sperr
|
|
823
|
),
|
|
824
|
_parents AS (
|
|
825
|
WITH RECURSIVE _tree AS (
|
|
826
|
SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, 0 AS depth FROM dbupdates a WHERE upd_id IN (SELECT upd_id FROM _nodes)
|
|
827
|
UNION
|
|
828
|
SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, depth - 1 FROM dbupdates a JOIN _tree p ON a.upd_id = p.upd_parent
|
|
829
|
)
|
|
830
|
SELECT upd_id FROM _tree
|
|
831
|
WHERE depth < 0
|
|
832
|
ORDER BY depth
|
|
833
|
)
|
|
834
|
SELECT * FROM _nodes
|
|
835
|
UNION
|
|
836
|
SELECT * FROM _parents
|
|
837
|
$$ LANGUAGE SQL;
|
|
838
|
|
|
839
|
EXCEPTION
|
|
840
|
WHEN others THEN
|
|
841
|
RAISE WARNING '%', SQLERRM;
|
|
842
|
END;
|
|
843
|
|
|
844
|
BEGIN
|
|
845
|
|
|
846
|
CREATE FUNCTION tsystem.syncro__dbupdates(IN _dblink VARCHAR DEFAULT 'host=pg.prodat-erp.de port=5432 dbname=PRODAT-18.08 user=syncro password=syncro') RETURNS void AS $$
|
|
847
|
BEGIN
|
|
848
|
SET LOCAL SESSION AUTHORIZATION syncro;
|
|
849
|
|
|
850
|
-- NotThisFields: dbrid,upd_donedat
|
|
851
|
INSERT INTO dbupdates ( upd_id,
|
|
852
|
upd_parent,
|
|
853
|
upd_minver,
|
|
854
|
upd_kunde,
|
|
855
|
--upd_donedat,
|
|
856
|
upd_bez,
|
|
857
|
upd_txt,
|
|
858
|
upd_sql,
|
|
859
|
upd_projekt,
|
|
860
|
upd_noerr,
|
|
861
|
upd_sperr,
|
|
862
|
--
|
|
863
|
insert_date,
|
|
864
|
insert_by,
|
|
865
|
modified_by,
|
|
866
|
modified_date
|
|
867
|
)
|
|
868
|
|
|
869
|
SELECT *
|
|
870
|
FROM dblink(_dblink,
|
|
871
|
'SELECT upd_id,
|
|
872
|
upd_parent,
|
|
873
|
upd_minver,
|
|
874
|
upd_kunde,
|
|
875
|
--upd_donedat,
|
|
876
|
upd_bez,
|
|
877
|
upd_txt,
|
|
878
|
upd_sql,
|
|
879
|
upd_projekt,
|
|
880
|
upd_noerr,
|
|
881
|
upd_sperr,
|
|
882
|
--
|
|
883
|
insert_date,
|
|
884
|
insert_by,
|
|
885
|
modified_by,
|
|
886
|
modified_date
|
|
887
|
FROM dbupdates
|
|
888
|
WHERE
|
|
889
|
(upd_kunde IS NULL OR upd_kunde LIKE ' || TSystem.quote_literal__connstr_param( TSystem.Settings__Get( 'KUNDE' ) ) || ')
|
|
890
|
AND upd_minver <= ' || TSystem.quote_literal__connstr_param( TSystem.Settings__Get( 'ProdatVersion' ) ) || '
|
|
891
|
')
|
|
892
|
AS ( upd_id_ varchar(30),
|
|
893
|
upd_parent varchar(30), -- parent (Baumstruktur)
|
|
894
|
upd_minver varchar(11), -- [SYNCRO:Version] Mindest-Programmversion
|
|
895
|
upd_kunde varchar(20), -- [SYNCRO:Kunde]
|
|
896
|
--upd_donedat timestamp(0), -- [SYNCRO:NotThisFields] update eingespielt am
|
|
897
|
upd_bez varchar(150),
|
|
898
|
upd_txt text,
|
|
899
|
upd_sql text,
|
|
900
|
upd_projekt varchar(100),
|
|
901
|
upd_noerr boolean,
|
|
902
|
upd_sperr boolean,
|
|
903
|
-- System (tables__generate_missing_fields)
|
|
904
|
insert_date date,
|
|
905
|
insert_by varchar(32),
|
|
906
|
modified_by varchar(32),
|
|
907
|
modified_date timestamp(0) -- [SYNCRO:Modified]
|
|
908
|
)
|
|
909
|
WHERE NOT EXISTS( SELECT true FROM dbupdates WHERE upd_id = upd_id_ );
|
|
910
|
---
|
|
911
|
|
|
912
|
RAISE NOTICE 'dbupdates %', (SELECT count(*) FROM dbupdates);
|
|
913
|
END $$ LANGUAGE plpgsql;
|
|
914
|
|
|
915
|
EXCEPTION
|
|
916
|
WHEN others THEN
|
|
917
|
RAISE WARNING '%', SQLERRM;
|
|
918
|
END;
|
|
919
|
|
|
920
|
BEGIN
|
|
921
|
|
|
922
|
PERFORM tsystem.syncro__dbupdates();
|
|
923
|
|
|
924
|
EXCEPTION
|
|
925
|
WHEN others THEN
|
|
926
|
RAISE WARNING '%', SQLERRM;
|
|
927
|
END;
|
|
928
|
|
|
929
|
|
|
930
|
END $do$ LANGUAGE plpgsql;
|