-
Notifications
You must be signed in to change notification settings - Fork 16
/
create_pgversion_schema.sql
2291 lines (1893 loc) · 71.2 KB
/
create_pgversion_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- Prepended SQL commands --
DO $$
BEGIN
CREATE ROLE versions WITH
INHERIT
ENCRYPTED PASSWORD '********';
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;
DO $$
BEGIN
CREATE EXTENSION POSTGIS;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;
--
-- -- Appended SQL commands --
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public, versions GRANT ALL ON TABLES TO versions;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA versions, public GRANT EXECUTE ON FUNCTIONS TO versions;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA versions, public GRANT USAGE, SELECT ON SEQUENCES TO versions;
-- GRANT ALL ON ALL TABLES IN SCHEMA public, versions TO versions;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA versions, public TO versions;
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA versions, public TO versions;
-- -- ddl-end --
--
SET check_function_bodies = false;
-- ddl-end --
SET search_path TO pg_catalog,public,versions;
-- ddl-end --
-- object: versions | type: SCHEMA --
-- DROP SCHEMA IF EXISTS versions CASCADE;
CREATE SCHEMA IF NOT EXISTS versions;
-- ddl-end --
ALTER SCHEMA versions OWNER TO versions;
-- ddl-end --
-- object: versions.pgvsrevision | type: FUNCTION --
-- DROP FUNCTION IF EXISTS versions.pgvsrevision() CASCADE;
CREATE OR REPLACE FUNCTION versions.pgvsrevision ()
RETURNS text
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100
AS $$
DECLARE
revision TEXT;
BEGIN
revision := '2.1.20';
RETURN revision;
END;
$$;
DROP TYPE IF EXISTS versions.checkout CASCADE;
CREATE TYPE versions.checkout AS
(
systime bigint,
revision integer,
mykey integer,
action character varying
);
-- ddl-end --
ALTER TYPE versions.checkout OWNER TO versions;
-- ddl-end --
-- object: versions.conflicts | type: TYPE --
DROP TYPE IF EXISTS versions.conflicts CASCADE;
CREATE TYPE versions.conflicts AS
(
objectkey bigint,
mysystime bigint,
myuser text,
myversion_log_id bigint,
conflict_systime bigint,
conflict_user text,
conflict_version_log_id bigint
);
-- ddl-end --
ALTER TYPE versions.conflicts OWNER TO versions;
-- ddl-end --
-- object: versions.logview | type: TYPE --
DROP TYPE IF EXISTS versions.logview CASCADE;
CREATE TYPE versions.logview AS
(
revision integer,
datum timestamp,
logmsg text,
project text
);
-- ddl-end --
ALTER TYPE versions.logview OWNER TO versions;
-- ddl-end --
-- object: versions._hasserial | type: FUNCTION --
-- DROP FUNCTION IF EXISTS versions._hasserial(character varying) CASCADE;
CREATE OR REPLACE FUNCTION versions._hasserial (in_table character varying)
RETURNS boolean
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 1
AS $$
DECLARE
qry TEXT;
pos INTEGER;
my_schema TEXT;
my_table TEXT;
my_serial_rec RECORD;
BEGIN
pos := strpos(in_table,'.');
if pos=0 then
my_schema := 'public';
my_table := in_table;
else
my_schema := substr(in_table,0,pos);
pos := pos + 1;
my_table := substr(in_table,pos);
END IF;
-- Check if SERIAL exists and which column represents it
select into my_serial_rec column_name as att,
data_type as typ, column_default
from information_schema.columns as col
where table_schema = my_schema::name
and table_name = my_table::name
and (position('nextval' in lower(column_default)) is NOT NULL
or position('nextval' in lower(column_default)) <> 0);
IF FOUND THEN
RETURN 'true';
else
RAISE EXCEPTION 'Table %.% does not has a serial defined', my_schema, my_table;
END IF;
END;
$$;
-- ddl-end --
ALTER FUNCTION versions._hasserial(character varying) OWNER TO versions;
-- ddl-end --
-- object: versions.pgvscheck | type: FUNCTION --
-- DROP FUNCTION IF EXISTS versions.pgvscheck(character varying) CASCADE;
CREATE OR REPLACE FUNCTION versions.pgvscheck(
_param1 character varying)
RETURNS SETOF versions.conflicts
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
inTable ALIAS FOR $1;
mySchema TEXT;
myTable TEXT;
myPkey TEXT;
message TEXT;
myDebug TEXT;
confExists record;
myPkeyRec record;
conflict BOOLEAN;
conflictCheck versions.conflicts%rowtype;
pos integer;
versionLogTable TEXT;
BEGIN
pos := strpos(inTable,'.');
conflict := False;
if pos=0 then
mySchema := 'public';
myTable := inTable;
else
mySchema := substr(inTable,0,pos);
pos := pos + 1;
myTable := substr(inTable,pos);
END IF;
versionLogTable := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log');
-- Pruefen ob und welche Spalte der Primarykey der Tabelle ist
select into myPkeyRec * from versions._primarykey(inTable);
myPkey := quote_ident(myPkeyRec.pkey_column);
/*
Check for conflicts before committing. When conflicts are existing stop the commit process
with a listing of the conflicting objects.
*/
message := '';
myDebug := 'with
foo as (select '||myPkey||', max(systime) as systime, max(version_log_id)
from '||versionLogTable||'
where commit
and project <> current_user
group by '||myPkey||'),
a as (select '||myPkey||', max(systime) as systime, max(version_log_id), project
from '||versionLogTable||'
where project = current_user
and not commit
group by '||myPkey||', project),
b as (select foo.*, v.project, v.action
from '||versionLogTable||' as v, foo
where v.version_log_id = foo.max)
select a.'||myPkey||' as objectkey,
a.systime as mysystime,
a.project as myuser,
a.max as myversion_log_id,
b.systime as conflict_systime,
b.project as conflict_user,
b.max as conflict_version_log_id
from a, b
where a.systime < b.systime
and a.'||myPkey||' = b.'||myPkey;
--RAISE EXCEPTION '%',myDebug;
EXECUTE myDebug into confExists;
IF COUNT(confExists) > 1 THEN
for conflictCheck IN EXECUTE myDebug
LOOP
return next conflictCheck;
conflict := True;
message := message||E'\n'||'WARNING! The object with '||myPkey||'='||conflictCheck.objectkey||' is also changed by user '||conflictCheck.conflict_user||'.';
END LOOP;
message := message||E'\n\n';
message := message||'Changes are not committed!'||E'\n\n';
RAISE NOTICE '%', message;
END IF;
END;
$BODY$;
-- ddl-end --
ALTER FUNCTION versions.pgvscheck(character varying) OWNER TO versions;
-- ddl-end --
-- object: versions.pgvscommit | type: FUNCTION --
-- DROP FUNCTION IF EXISTS versions.pgvscommit(character varying,text) CASCADE;
CREATE FUNCTION versions.pgvscommit (_param1 character varying, _param2 text)
RETURNS SETOF versions.conflicts
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100
ROWS 1000
AS $$
DECLARE
inTable ALIAS FOR $1;
logMessage ALIAS FOR $2;
mySchema TEXT;
myTable TEXT;
myPkey TEXT;
fields TEXT;
insFields TEXT;
message TEXT;
myDebug TEXT;
commitQuery TEXT;
checkQuery TEXT;
myPkeyRec record;
attributes record;
testRec record;
conflict BOOLEAN;
conflictCheck versions.conflicts%rowtype;
pos integer;
versionLogTable TEXT;
revision integer;
BEGIN
pos := strpos(inTable,'.');
fields := '';
insFields := '';
conflict := False;
if pos=0 then
mySchema := 'public';
myTable := inTable;
else
mySchema := substr(inTable,0,pos);
pos := pos + 1;
myTable := substr(inTable,pos);
END IF;
versionLogTable := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log');
-- Pruefen ob und welche Spalte der Primarykey der Tabelle ist
select into myPkeyRec * from versions._primarykey(inTable);
myPkey := quote_ident(myPkeyRec.pkey_column);
/*
Check for conflicts before committing. When conflicts are existing stop the commit process
with a listing of the conflicting objects.
*/
message := '';
for conflictCheck IN EXECUTE 'select * from versions.pgvscheck('''||mySchema||'.'||myTable||''')'
LOOP
return next conflictCheck;
conflict := True;
message := message||E'\n'||'WARNING! The object with '||myPkey||'='||conflictCheck.objectkey||' is also changed by user '||conflictCheck.project||'.';
END LOOP;
message := message||E'\n\n';
message := message||'Changes are not committed!'||E'\n\n';
IF conflict THEN
RAISE NOTICE '%', message;
ELSE
execute 'create temp table tmp_tab as
select project
from '||versionLogTable||'
where not commit ';
select into testRec project from tmp_tab;
IF NOT FOUND THEN
execute 'drop table tmp_tab';
RETURN;
ELSE
execute 'drop table tmp_tab';
for attributes in select *
from information_schema.columns
where table_schema=mySchema::name
and table_name = myTable::name
LOOP
if attributes.column_name <> 'OID'
and attributes.column_name <> 'versionarchive' then
fields := fields||',log.'||quote_ident(attributes.column_name);
insFields := insFields||','||quote_ident(attributes.column_name);
END IF;
END LOOP;
fields := substring(fields,2);
insFields := substring(insFields,2);
revision := nextval('versions.'||quote_ident(mySchema||'_'||myTable||'_revision_seq'));
commitQuery := 'delete from '||versionLogTable||'
using (
select log.* from '||versionLogTable||' as log,
(
select '||myPkey||', systime
from '||versionLogTable||'
where not commit and project = current_user
except
select '||myPkey||', max(systime) as systime
from '||versionLogTable||'
where not commit and project = current_user
group by '||myPkey||') as foo
where log.project = current_user
and foo.'||myPkey||' = log.'||myPkey||'
and foo.systime = log.systime
and not commit) as foo
where '||versionLogTable||'.'||myPkey||' = foo.'||myPkey||'
and '||versionLogTable||'.systime = foo.systime
and '||versionLogTable||'.project = foo.project;
delete from '||quote_ident(mySchema)||'.'||quote_ident(myTable)||' where '||myPkey||' in
(select '||myPkey||'
from '||versionLogTable||'
where not commit
and project = current_user
group by '||myPkey||', project);
insert into '||quote_ident(mySchema)||'.'||quote_ident(myTable)||' ('||insFields||')
select '||fields||' from '||versionLogTable||' as log,
(
select '||myPKey||', max(systime) as systime, max(revision) as revision
from '||versionLogTable||'
where project = current_user
and not commit
and action <> ''delete''
group by '||myPKey||'
) as foo
where log.'||myPkey||'= foo.'||myPkey||'
and log.systime = foo.systime
and log.action <> ''delete'';
update '||versionLogTable||' set
commit = True,
revision = '||revision||',
logmsg = '''||logMessage ||''',
systime = ' || EXTRACT(EPOCH FROM now()::TIMESTAMP WITH TIME ZONE)*1000 || '
where not commit
and project = current_user;';
--RAISE EXCEPTION '%',commitQuery;
execute 'INSERT INTO versions.version_tables_logmsg(
version_table_id, revision, logmsg)
SELECT version_table_id, '||revision||', '''||logMessage||''' as logmsg
FROM versions.version_tables
where version_table_schema = '''||mySchema||'''
and version_table_name = '''|| myTable||'''';
execute commitQuery;
END IF;
END IF;
RETURN;
END;
$$;
-- ddl-end --
ALTER FUNCTION versions.pgvscommit(character varying,text) OWNER TO versions;
-- ddl-end --
-- object: versions.pgvsdrop | type: FUNCTION --
-- DROP FUNCTION IF EXISTS versions.pgvsdrop(character varying) CASCADE;
CREATE OR REPLACE FUNCTION versions.pgvsdrop (_param1 character varying)
RETURNS boolean
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100
AS $$
DECLARE
inTable ALIAS FOR $1;
pos INTEGER;
mySchema TEXT;
myTable TEXT;
versionTableRec record;
versionTable TEXT;
versionView TEXT;
versionLogTable TEXT;
versionLogTableType TEXT;
versionLogTableSeq TEXT;
versionRevisionSeq TEXT;
versionPkeySeq TEXT;
geomCol TEXT;
geomType TEXT;
geomDIM INTEGER;
geomSRID INTEGER;
testRec record;
uncommitRec record;
testTab TEXT;
myPkeyRec RECORD;
myPkey TEXT;
BEGIN
pos := strpos(inTable,'.');
geomCol := '';
geomDIM := 2;
geomSRID := -1;
geomType := '';
if pos=0 then
mySchema := 'public';
myTable := inTable;
else
mySchema = substr(inTable,0,pos);
pos := pos + 1;
myTable = substr(inTable,pos);
END IF;
select into myPkeyRec * from versions._primarykey(inTable);
myPkey := quote_ident(myPkeyRec.pkey_column);
versionView := quote_ident(mySchema)||'.'||quote_ident(myTable||'_version');
versionLogTable := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log');
versionLogTableType := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log_type');
versionLogTableSeq := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log_version_log_id_seq');
versionRevisionSeq := 'versions.'||quote_ident(mySchema||'_'||myTable||'_revision_seq');
versionPkeySeq := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log'||'_'||myPkey||'_seq');
-- Feststellen ob die Tabelle existiert
select into testRec table_name
from information_schema.tables
where table_schema = mySchema::name
and table_name = myTable::name;
IF NOT FOUND THEN
RAISE EXCEPTION 'Table %.% does not exist', mySchema,myTable;
RETURN False;
END IF;
-- Feststellen ob die Log-Tabelle existiert
testTab := mySchema||'_'||myTable||'_version_log';
select into testRec table_name
from information_schema.tables
where table_schema = 'versions'
and table_name = testTab::name;
IF NOT FOUND THEN
RAISE EXCEPTION 'Log Table %.% does not exist', mySchema,myTable;
RETURN False;
END IF;
-- Die grundlegenden Geometrieparameter des Ausgangslayers ermitteln
select into testRec f_geometry_column, coord_dimension, srid, type
from geometry_columns
where f_table_schema = mySchema::name
and f_table_name = myTable::name;
geomCol := testRec.f_geometry_column;
geomDIM := testRec.coord_dimension;
geomSRID := testRec.srid;
geomType := testRec.type;
execute 'create temp table tmp_tab on commit drop as
select project
from '||versionLogTable||'
where not commit ';
select into testRec project from tmp_tab;
IF FOUND THEN
for uncommitRec in select distinct project from tmp_tab loop
RAISE NOTICE 'Uncommitted Records are existing for user %. Please commit all changes before use pgvsdrop()', uncommitRec.project;
end loop;
-- execute 'drop table tmp_tab';
RETURN False;
END IF;
IF FOUND THEN
RAISE EXCEPTION 'Uncommitted Records are existing. Please commit all changes before use pgvsdrop()';
RETURN False;
END IF;
select into versionTableRec version_table_id as vtid
from versions.version_tables as vt
where version_table_schema = mySchema::name
and version_table_name = myTable::name;
execute 'drop table if exists '||versionLogTable||' cascade;';
execute 'drop type if exists '||versionLogTableType;
execute 'DROP SEQUENCE if exists '||versionLogTableSeq;
execute 'DROP SEQUENCE if exists '||versionRevisionSeq;
execute 'DROP SEQUENCE if exists '||versionPkeySeq;
execute 'delete from versions.version_tables
where version_table_id = '''||versionTableRec.vtid||''';';
execute 'delete from versions.version_tables_logmsg
where version_table_id = '''||versionTableRec.vtid||''';';
execute 'delete from versions.version_tags
where version_table_id = '''||versionTableRec.vtid||''';';
RETURN true ;
END;
$$;
-- ddl-end --
ALTER FUNCTION versions.pgvsdrop(character varying) OWNER TO versions;
-- ddl-end --
-- FUNCTION: versions.pgvsinit(character varying)
-- DROP FUNCTION IF EXISTS versions.pgvsinit(character varying);
CREATE OR REPLACE FUNCTION versions.pgvsinit(
_param1 character varying)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $$
DECLARE
inTable ALIAS FOR $1;
pos INTEGER;
mySchema TEXT;
myTable TEXT;
versionTable TEXT;
versionView TEXT;
versionLogTable TEXT;
versionLogTableType TEXT;
versionLogTableSeq TEXT;
versionLogTableTmp TEXT;
geomCol TEXT;
geomType TEXT;
geomDIM INTEGER;
geomSRID INTEGER;
attributes record;
testRec record;
testPKey record;
fields TEXT;
time_fields TEXT;
type_fields TEXT;
newFields TEXT;
oldFields TEXT;
updateFields TEXT;
mySequence TEXT;
myPkey TEXT;
myPkeyRec record;
testTab TEXT;
archiveWhere TEXT;
sql TEXT;
BEGIN
pos := strpos(inTable,'.');
fields := '';
time_fields := '';
type_fields := '';
newFields := '';
oldFields := '';
updateFields := '';
geomCol := '';
geomDIM := 2;
geomSRID := -1;
geomType := '';
mySequence := '';
archiveWhere := '';
if pos=0 then
mySchema := 'public';
myTable := inTable;
else
mySchema = substr(inTable,0,pos);
pos := pos + 1;
myTable = substr(inTable,pos);
END IF;
execute 'select * from versions._hasserial('''||mySchema||'.'||myTable||''')';
versionTable := quote_ident(mySchema||'.'||myTable||'_version_t');
versionView := quote_ident(mySchema)||'.'||quote_ident(myTable||'_version');
versionLogTable := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log');
versionLogTableSeq := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log_version_log_id_seq');
versionLogTableTmp := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log_tmp');
-- Check if the table or view exists
select into testRec table_name
from information_schema.tables
where table_schema = mySchema::name
and table_name = myTable::name;
IF NOT FOUND THEN
select into testRec table_name
from information_schema.views
where table_schema = mySchema::name
and table_name = myTable::name;
IF NOT FOUND THEN
RAISE EXCEPTION 'Table %.% does not exist', mySchema,myTable;
RETURN False;
END IF;
END IF;
-- Obtain the basic geometry parameters of the initial layer
select into testRec f_geometry_column, coord_dimension, srid, type
from geometry_columns
where f_table_schema = mySchema::name
and f_table_name = myTable::name;
IF NOT FOUND THEN
RAISE EXCEPTION 'Table %.% is not registered in geometry_columns', mySchema, myTable;
RETURN False;
END IF;
geomCol := testRec.f_geometry_column;
geomDIM := testRec.coord_dimension;
geomSRID := testRec.srid;
geomType := testRec.type;
-- Check if the table already exists
testTab := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log');
select into testRec table_name
from information_schema.tables
where table_schema = mySchema::name
and table_name = testTab::name;
IF FOUND THEN
RAISE NOTICE 'Table versions.% has been deleted', testTab;
execute 'drop table '||quote_ident(mySchema)||'.'||quote_ident(testTab)||' cascade';
END IF;
-- Check if and which column is the primary key of the table
select into myPkeyRec * from versions._primarykey(inTable);
myPkey := quote_ident(myPkeyRec.pkey_column);
sql := format('select max(%1$s) FROM %2$s.%3$s', myPkey, quote_ident(mySchema), quote_ident(myTable));
execute sql into testRec;
IF testRec.max is Null THEN
RAISE EXCEPTION 'The table %.% is empty, but must contain at least one record for correct initialization.', mySchema, myTable;
END IF;
sql := 'create table '||versionLogTable||' (LIKE '||quote_ident(mySchema)||'.'||quote_ident(myTable)||');
ALTER TABLE '||versionLogTable||' OWNER TO versions;
create sequence versions.'||quote_ident(mySchema||'_'||myTable||'_revision_seq')||' INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
alter sequence versions.'||quote_ident(mySchema||'_'||myTable||'_revision_seq')||' owner to versions;
create sequence versions.'||quote_ident(mySchema||'_'||myTable||'_version_log'||'_'||myPkey||'_seq')||' INCREMENT 1 MINVALUE '||testRec.max||' MAXVALUE 9223372036854775807 START '||testRec.max+1||' CACHE 1;
alter sequence versions.'||quote_ident(mySchema||'_'||myTable||'_version_log'||'_'||myPkey||'_seq')||' owner to versions;
alter table '||versionLogTable||' ALTER COLUMN '||myPkey||' SET DEFAULT nextval(''versions.'||quote_ident(mySchema||'_'||myTable||'_version_log_'||myPkey||'_seq')||''');
alter table '||versionLogTable||' add column version_log_id bigserial;
alter table '||versionLogTable||' add column action character varying;
alter table '||versionLogTable||' add column project character varying default current_user;
alter table '||versionLogTable||' add column systime bigint default extract(epoch from now()::TIMESTAMP WITH TIME ZONE)*1000;
alter table '||versionLogTable||' add column revision bigint;
alter table '||versionLogTable||' add column logmsg text;
alter table '||versionLogTable||' add column commit boolean DEFAULT False;
alter table '||versionLogTable||' add constraint '||myTable||'_pkey primary key ('||myPkey||',project,systime,action);
CREATE INDEX '||quote_ident(mySchema||'_'||myTable||'_version_log_id_idx') ||' ON '||versionLogTable||' USING btree (version_log_id) where not commit;
CREATE INDEX '||quote_ident(mySchema||'_'||myTable||'_systime_idx') ||' ON '||versionLogTable||' USING btree (systime) where not commit;
CREATE INDEX '||quote_ident(mySchema||'_'||myTable||'_project_idx') ||' ON '||versionLogTable||' USING btree (project) where not commit;
create index '||quote_ident(mySchema||'_'||myTable||'_version_geo_idx') ||' on '||versionLogTable||' USING GIST ('||geomCol||') where not commit;
insert into versions.version_tables (version_table_schema,version_table_name,version_view_schema,version_view_name,version_view_pkey,version_view_geometry_column)
values('''||mySchema||''','''||myTable||''','''||mySchema||''','''||myTable||'_version'','''||myPkey||''','''||geomCol||''');';
--RAISE EXCEPTION '%', sql;
EXECUTE sql;
for attributes in select *
from information_schema.columns
where table_schema=mySchema::name
and table_name = myTable::name
LOOP
if attributes.column_default LIKE 'nextval%' then
mySequence := attributes.column_default;
ELSE
if myPkey <> attributes.column_name then
fields := fields||','||quote_ident(attributes.column_name);
time_fields := time_fields||',v1.'||quote_ident(attributes.column_name);
type_fields := type_fields||','||quote_ident(attributes.column_name)||' '||attributes.udt_name||'';
newFields := newFields||',new.'||quote_ident(attributes.column_name);
oldFields := oldFields||',old.'||quote_ident(attributes.column_name);
updateFields := updateFields||','||quote_ident(attributes.column_name)||'=new.'||quote_ident(attributes.column_name);
END IF;
END IF;
END LOOP;
-- Das erste Komma aus dem String entfernen
fields := substring(fields,2);
newFields := substring(newFields,2);
oldFields := substring(oldFields,2);
updateFields := substring(updateFields,2);
IF length(mySequence)=0 THEN
RAISE EXCEPTION 'No Sequence defined for Table %.%', mySchema,myTable;
RETURN False;
END IF;
execute 'create or replace view '||versionView||' as
SELECT v.'||myPkey||', '||fields||'
FROM '||quote_ident(mySchema)||'.'||quote_ident(myTable)||' v,
( SELECT '||quote_ident(mySchema)||'.'||quote_ident(myTable)||'.'||myPkey||'
FROM '||quote_ident(mySchema)||'.'||quote_ident(myTable)||'
EXCEPT
SELECT v_1.'||myPkey||'
FROM '||versionLogTable||' v_1,
( SELECT v_2.'||myPkey||',
max(v_2.version_log_id) AS version_log_id, min(action) as action
FROM '||versionLogTable||' v_2
WHERE NOT v_2.commit AND v_2.project::name = "current_user"()
GROUP BY v_2.'||myPkey||') foo_1
WHERE v_1.version_log_id = foo_1.version_log_id) foo
WHERE v.'||myPkey||' = foo.'||myPkey||'
UNION
SELECT v.'||myPkey||', '||fields||'
FROM '||versionLogTable||' v,
( SELECT v_1.'||myPkey||',
max(v_1.version_log_id) AS version_log_id, min(action) as action
FROM '||versionLogTable||' v_1
WHERE NOT v_1.commit AND v_1.project::name = "current_user"()
GROUP BY v_1.'||myPkey||') foo
WHERE v.version_log_id = foo.version_log_id and foo.action <> ''delete''';
execute 'ALTER VIEW '||versionView||' owner to versions';
execute 'GRANT ALL PRIVILEGES ON TABLE '||versionView||' to versions';
execute 'GRANT ALL PRIVILEGES ON TABLE '||quote_ident(mySchema)||'.'||quote_ident(myTable)||' to versions';
execute 'create or replace view '||versionView||'_time as
SELECT row_number() OVER () AS rownum,
to_timestamp(v1.systime/1000)::TIMESTAMP WITHOUT TIME ZONE as start_time,
CASE WHEN v2.systime IS NULL THEN
to_timestamp(''9999-01-01 00:00:00'', ''YYYY-MM-DD HH:MI:SS'')
else
to_timestamp(v2.systime/1000)::TIMESTAMP WITHOUT TIME ZONE
END as end_time,
v1.*
FROM '||versionLogTable||' v1
LEFT JOIN '||versionLogTable||' v2 ON v2.'||myPkey||'=v1.'||myPkey||' AND v2.action=''delete'' and v1.revision <> v2.revision
WHERE (v1.action=''insert'' or v1.action = ''update'') and v1.commit = True';
execute 'ALTER VIEW '||versionView||'_time owner to versions';
execute 'GRANT ALL PRIVILEGES ON TABLE '||versionView||'_time to versions';
execute 'CREATE TRIGGER pgvs_version_record_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE
ON '||versionView||'
FOR EACH ROW
EXECUTE PROCEDURE versions.pgvs_version_record();';
execute 'INSERT INTO '||versionLogTable||' ('||myPkey||','||fields||', action, revision, logmsg, commit )
select '||myPkey||','||fields||', ''insert'' as action, 0 as revision, ''initial commit revision 0'' as logmsg, ''t'' as commit
from '||quote_ident(mySchema)||'.'||quote_ident(myTable);
execute 'INSERT INTO versions.version_tables_logmsg(
version_table_id, revision, logmsg)
SELECT version_table_id, 0 as revision, ''initial commit revision 0'' as logmsg
FROM versions.version_tables
where version_table_schema = '''||mySchema||''' and version_table_name = '''|| myTable||'''';
execute 'INSERT INTO versions.version_tags(
version_table_id, revision, tag_text)
SELECT version_table_id, 0 as revision, ''initial commit revision 0'' as tag_text FROM versions.version_tables where version_table_schema = '''||mySchema||''' and version_table_name = '''|| myTable||'''';
RETURN true ;
END;
$$;
-- ddl-end --
ALTER FUNCTION versions.pgvsinit(character varying) OWNER TO versions;
-- ddl-end --
-- object: versions.pgvslogview | type: FUNCTION --
-- DROP FUNCTION IF EXISTS versions.pgvslogview(character varying) CASCADE;
CREATE OR REPLACE FUNCTION versions.pgvslogview (_param1 character varying)
RETURNS SETOF versions.logview
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100
ROWS 1000
AS $$
DECLARE
inTable ALIAS FOR $1;
mySchema TEXT;
myTable TEXT;
logViewQry TEXT;
versionLogTable TEXT;
pos integer;
logs versions.logview%rowtype;
BEGIN
pos := strpos(inTable,'.');
if pos=0 then
mySchema := 'public';
myTable := inTable;
else
mySchema := substr(inTable,0,pos);
pos := pos + 1;
myTable := substr(inTable,pos);
END IF;
versionLogTable := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log');
logViewQry := format('select logt.revision, to_timestamp(logt.systime/1000), logt.logmsg, logt.project
from versions.version_tables as vt left join versions.version_tables_logmsg as logt on (vt.version_table_id = logt.version_table_id)
where vt.version_table_schema = ''%1$s''
and vt.version_table_name = ''%2$s''
order by revision desc', mySchema, myTable);
--RAISE EXCEPTION '%', logViewQry;
for logs IN EXECUTE logViewQry
LOOP
return next logs;
end loop;
END;
$$;
-- ddl-end --
ALTER FUNCTION versions.pgvslogview(character varying) OWNER TO versions;
-- ddl-end --
-- object: versions.pgvsmerge | type: FUNCTION --
-- DROP FUNCTION IF EXISTS versions.pgvsmerge(character varying,integer,character varying) CASCADE;
CREATE OR REPLACE FUNCTION versions.pgvsmerge ("inTable" character varying, "targetGid" integer, "targetProject" character varying)
RETURNS boolean
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100
AS $$
DECLARE
inTable ALIAS FOR $1;
targetGid ALIAS FOR $2;
targetProject ALIAS FOR $3;
sourceProject TEXT;
mySchema TEXT;
myTable TEXT;
myPkey TEXT;
myDebug TEXT;
myMerge TEXT;
myDelete TEXT;
myPkeyRec record;
myTimestamp bigint;
conflict BOOLEAN;
merged BOOLEAN;
conflictCheck record;
cols TEXT;
pos integer;
versionLogTable TEXT;
BEGIN
sourceProject := current_user;
pos := strpos(inTable,'.');
conflict := False;
merged := False;
if pos=0 then
mySchema := 'public';
myTable := inTable;
else
mySchema := substr(inTable,0,pos);
pos := pos + 1;
myTable := substr(inTable,pos);
END IF;
versionLogTable := 'versions.'||quote_ident(mySchema||'_'||myTable||'_version_log');
-- Pruefen ob und welche Spalte der Primarykey der Tabelle ist
select into myPkeyRec * from versions._primarykey(inTable);
myPkey := quote_ident(myPkeyRec.pkey_column);
myDebug := 'select a.'||myPkey||' as objectkey,
a.systime as mysystime,
a.project as myuser,
b.systime as conflict_systime,
b.project as conflict_user,
b.max as conflict_version_log_id
from (select '||myPkey||', max(systime) as systime, max(version_log_id), project
from '||versionLogTable||'
where project = '''||targetProject||'''
and not commit
and '||myPkey||' = '||targetGid||'
group by '||myPkey||', project) as a,
(
select '||myPkey||', max(systime) as systime, max(version_log_id), project
from '||versionLogTable||'
where commit
and project <> '''||targetProject||'''
group by '||myPkey||', project
) as b
where a.systime < b.systime
and a.'||myPkey||' = b.'||myPkey||'
and a.'||myPkey||' = '||targetGid;